天天看点

C# NPOI 批量导出Excel 打包下载

   public void NopiPack()

        {

            //下载

            int colCount = 43;//列数

            Response.ContentType = "application/zip";

            Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("压缩包名称", Encoding.UTF8) + ".zip");

            MemoryStream st = new MemoryStream();

            using (ZipFile zip = ZipFile.Create(st))

            {

                zip.BeginUpdate();

           //list 需要循环的集合

                for (int i = 0; i < list.Count; i++)

                {

                    if (ProducedPointList != null && ProducedPointList.Count > 0)

                    {

                        HSSFWorkbook[] workbook = new HSSFWorkbook[list.Count];  //创建多个 workbook

                        workbook[i] = new HSSFWorkbook();

                        //单元格样式

                        ICellStyle style = new NPOIHelper().Getcellstyle(workbook[i], NPOIHelper.stylexls.默认);

                        style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;

                        style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;

                        style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;

                        style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;

                        style.BottomBorderColor = HSSFColor.Black.Index;

                        style.LeftBorderColor = HSSFColor.Black.Index;

                        style.RightBorderColor = HSSFColor.Black.Index;

                        style.TopBorderColor = HSSFColor.Black.Index;

                        style.Alignment = HorizontalAlignment.Center;

                        style.VerticalAlignment = VerticalAlignment.Justify;

                        ISheet sheet = workbook[i].CreateSheet("xx日报"); //创建一个Sheet页

                        IRow row = sheet.CreateRow(rowID); //行

                        row.HeightInPoints = 25; //行高

                        sheet.AddMergedRegion(new CellRangeAddress(rowID, rowID, 0, colCount - 1)); //横向合并单元格

                        ICell cell = row.CreateCell(0);  //列位置

                        cell.SetCellValue("xxx报表");   //表头内容

                        //单独设定表头样式

                        ICellStyle styleTop = new NPOIHelper().Getcellstyle(workbook[i], NPOIHelper.stylexls.默认);

                        styleTop.Alignment = HorizontalAlignment.Center;

                        styleTop.VerticalAlignment = VerticalAlignment.Center;

                        HSSFFont fontsss = (HSSFFont)workbook[i].CreateFont();

                        fontsss.FontHeightInPoints = 18;//字体大小

                        fontsss.Boldweight = (short)FontBoldWeight.Bold;//粗体显示

                        fontsss.FontName = "宋体";//设置字体

                        styleTop.SetFont(fontsss);

                        cell.CellStyle = styleTop;

                        //构建二级表头

                        row = sheet.CreateRow(++rowID);

                        sheet.AddMergedRegion(new CellRangeAddress(rowID, rowID, 0, colCount - 1));

                        cell = row.CreateCell(0);

                        cell.SetCellValue(Convert.ToDateTime(itme).ToString("yyyy年MM月dd日"));

                        cell.CellStyle = new NPOIHelper().GetcellDDstyle(workbook[i], NPOIHelper.stylexls.默认);

                        cell.CellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.None;

                        cell.CellStyle.Alignment = HorizontalAlignment.Right;

                        cell.CellStyle.VerticalAlignment = VerticalAlignment.Center;

#region ///这里构建Excel其它内容

//构建剩下内容

#endregion

                        //控制列宽

                       for (int cellcount = 0; cellcount < colCount; cellcount++)

                        {

                            if (cellcount == 1)

                            {

                                sheet.SetColumnWidth(cellcount, 11 * 256);

                            }

                            else

                            {

                                sheet.SetColumnWidth(cellcount, 9 * 256);

                            }

                        }

                       //写入zip

                        MemoryStream ms = WriteToStream(workbook[i]);

                        StreamDataSource sds = new StreamDataSource(ms);

                        zip.Add(sds, "XX日报".xls");

                }

                zip.CommitUpdate();

            }

            Response.BinaryWrite(st.GetBuffer());

            Response.End();

        }