美文网首页
NOPI导出数据到Excel模板形成汇总表

NOPI导出数据到Excel模板形成汇总表

作者: Rinaloving | 来源:发表于2019-07-31 10:59 被阅读0次
工时汇总.png
 public void NpoiExportExcel2(DateTime startWeek, DateTime endWeek)
        {
            DataTable dt = UseTimeService.Instance.GetDataTableApply();
           
            var fileName = "asdasda";
            var exportTemplatePath = "~/ManhourTemplate/工时表.xlsx";
            DownloadExcel(fileName, dt, exportTemplatePath);
        }

 public void DownloadExcel(string reportName, DataTable dt,string exportTemplatePath)
        {
            Stream s = RenderDataTableToExcel2(dt, exportTemplatePath);
            if (s != null)
            {
                MemoryStream ms = s as MemoryStream;
                System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename=" + HttpUtility.UrlEncode(reportName) + DateTime.Now.ToString("yyyyMMdd") + ".xlsx"));
                System.Web.HttpContext.Current.Response.AddHeader("Content-Length", ms.ToArray().Length.ToString());
                System.Web.HttpContext.Current.Response.BinaryWrite(ms.ToArray());
                System.Web.HttpContext.Current.Response.Flush();
                ms.Close();
                ms.Dispose();
            }
            else
            {
                System.Web.HttpContext.Current.Response.Write("出错,无法下载!");
            }
                
        }


  public Stream RenderDataTableToExcel2(DataTable sourceTable, string exportTemplatePath)
        {
            XSSFWorkbook workbook = null;
            MemoryStream ms = null;
            ISheet sheet = null;
            XSSFRow headerRow = null;
            string templetFileName = System.Web.HttpContext.Current.Server.MapPath(exportTemplatePath);
            FileStream file = new FileStream(templetFileName, FileMode.Open, FileAccess.Read);
            workbook = new XSSFWorkbook(file);

            DataTable psdt = UseTimeService.Instance.GetPersonTotalWorkTime(); //个人工时总计
            DataTable pjdt = UseTimeService.Instance.GetProjectTotalUseTime(); //项目耗时总计
            string alltime = UseTimeService.Instance.GetAllUseTime(); //项目耗时总计

            try
            {

                ms = new MemoryStream();
                sheet = workbook.GetSheet("汇总");
                int rowIndex = sheet.LastRowNum;


                IFont font = workbook.CreateFont();//声明字体
                font.Boldweight = (Int16)FontBoldWeight.Bold;//加粗
                font.FontHeightInPoints = 18;//字体大小



                var cellStyle = workbook.CreateCellStyle();

                DefineCellStyle(cellStyle);


                cellStyle.TopBorderColor = HSSFColor.BrightGreen.Index; //边框颜色
                cellStyle.SetFont(font);//加入单元格
                cellStyle.Alignment = HorizontalAlignment.Center; // 水平居中
                cellStyle.VerticalAlignment = VerticalAlignment.Center; // 垂直居中


                List<string> projectlist = new List<string>();
                List<string> stafflist = new List<string>();


                stafflist.Add("工时项目");

                int n = 2;//因为模板有表头,所以从第3行开始写
                for (int j = 0; j < sourceTable.Rows.Count; j++)
                {
                    XSSFRow dataRow = (XSSFRow)sheet.CreateRow(j + n);
                    string ProjectName = sourceTable.Rows[j]["项目名称"].ToString().Trim();
                    string StaffName = sourceTable.Rows[j]["员工姓名"].ToString().Trim();

                    projectlist.Add(ProjectName);
                    stafflist.Add(StaffName);


                }
                stafflist.Add("总计");
                projectlist.Add("总计");


                //设置表头
                XSSFRow headDataRow = sheet.CreateRow(0) as XSSFRow;
                headDataRow.HeightInPoints = 30; // 行高
                XSSFCell headCell = headDataRow.CreateCell(0) as XSSFCell; // 创建单元格
                headCell.SetCellValue("综管部工时汇总( "+DateTime.Now.ToString()+" )");
                headCell.CellStyle = cellStyle;
                sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0,0,0, psdt.Rows.Count+1)); //合并单元格



                XSSFRow dataRow2 = (XSSFRow)sheet.CreateRow(1);

                for (int i = 0; i < projectlist.Distinct().ToArray().Length; i++)
                {
                    XSSFRow dataRow = (XSSFRow)sheet.CreateRow(i+n);
                    DefineCellStyle(dataRow.CreateCell(0).CellStyle);
                   // dataRow.Cells[0].SetCellValue(projectlist.Distinct().ToArray()[i].Trim().ToString());

                    for (int j = 0; j < stafflist.Distinct().ToArray().Length; j++)
                    {

                        XSSFCell newCell = dataRow2.CreateCell(j) as XSSFCell;
                        XSSFCell newCell2 = dataRow.CreateCell(j) as XSSFCell;
                        DefineCellStyle(dataRow2.CreateCell(j).CellStyle);
                        newCell.SetCellValue(stafflist.Distinct().ToArray()[j].Trim().ToString());
                        

                        for (int k = 0; k < sourceTable.Rows.Count; k++)
                        {
                            if (sourceTable.Rows[k]["项目名称"].ToString().Trim().Equals(projectlist.Distinct().ToArray()[i].Trim().ToString()) && sourceTable.Rows[k]["员工姓名"].ToString().Trim().Equals(stafflist.Distinct().ToArray()[j].Trim().ToString()))
                            {
                                newCell2.SetCellValue(sourceTable.Rows[k]["总工时"].ToString().Trim());
                                dataRow.Cells[0].SetCellValue(projectlist.Distinct().ToArray()[i].Trim().ToString());
                            }
                            
                           
                        }



                    }
                    if (i< projectlist.Distinct().ToArray().Length-1)
                    {
                        for (int p = 0; p < pjdt.Rows.Count; p++)
                        {
                            if (pjdt.Rows[p]["项目名称"].ToString().Trim().Equals(projectlist.Distinct().ToArray()[i].Trim().ToString()))
                            {
                                dataRow.Cells[stafflist.Distinct().ToArray().Length - 1].SetCellValue(pjdt.Rows[p]["项目工时总计"].ToString().Trim());
                            }
                        }


                    }
                    else
                    {
                        //alltime
                        dataRow.Cells[stafflist.Distinct().ToArray().Length - 1].SetCellValue(alltime);
                    }
                  
                      
                    


                    if (i== (projectlist.Distinct().ToArray().Length-1))
                    {
                        dataRow.Cells[0].SetCellValue(projectlist.Distinct().ToArray()[projectlist.Distinct().ToArray().Length-1].Trim().ToString());
                        for (int j = 0; j < psdt.Rows.Count; j++)
                        {
                            dataRow.Cells[j+1].SetCellValue(psdt.Rows[j]["个人工时总计"].ToString().Trim());
                        }
                        
                    }

                    

                }
                


                //列宽自适应,只对英文和数字有效
                for (int i = 0; i <= sourceTable.Columns.Count; ++i)
                {
                    sheet.SetDefaultColumnStyle(i,cellStyle);
                    sheet.AutoSizeColumn(i);
                }
                    
                workbook.Write(ms);
                ms.Flush();
            }
            catch (Exception ex)
            {
                throw;
                return null;
            }
            finally
            {
                ms.Close();
                sheet = null;
                headerRow = null;
                workbook = null;
            }
            return ms;
        }


 public void DefineCellStyle(ICellStyle cellStyle)
  {
            cellStyle.BorderBottom = BorderStyle.Thin; //下边框
            cellStyle.BorderLeft = BorderStyle.Thin;//左边框
            cellStyle.BorderTop = BorderStyle.Thin;//上边框
            cellStyle.BorderRight = BorderStyle.Thin;//右边框
  }


// 集合转 DataTable类型
 public static DataTable ToDataTable<T>(IEnumerable<T> collection)
{
            var props = typeof(T).GetProperties();
            var dt = new DataTable();
            dt.Columns.AddRange(props.Select(p => new DataColumn(p.Name, p.PropertyType)).ToArray());
            if (collection.Count() > 0)
            {
                for (int i = 0; i < collection.Count(); i++)
                {
                    ArrayList tempList = new ArrayList();
                    foreach (PropertyInfo pi in props)
                    {
                        object obj = pi.GetValue(collection.ElementAt(i), null);
                        tempList.Add(obj);
                    }
                    object[] array = tempList.ToArray();
                    dt.LoadDataRow(array, true);
                }
            }
            return dt;
        }

相关文章

网友评论

      本文标题:NOPI导出数据到Excel模板形成汇总表

      本文链接:https://www.haomeiwen.com/subject/nfrzrctx.html