NPOI 导出EXCEL

1·.效果图

2.导出触发事件源

 string fileName = System.Web.HttpUtility.UrlEncode(name + ".xls");
                    context.Response.ContentType = "application/vnd.ms-excel";
                    context.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", fileName));
                    context.Response.Clear();
                    context.Response.BinaryWrite(ExportStaticDept(list).GetBuffer());

3.导出组装数据

public MemoryStream ExportStaticDept(List<v_Static_SW_BaseDataDept> list)
        {
            HSSFWorkbook hssfWork = new HSSFWorkbook();
            HSSFSheet sheet = hssfWork.CreateSheet("派发案件统计");

            HSSFFont font = hssfWork.CreateFont();
            font.FontHeightInPoints = 16;

            HSSFFont fontB = hssfWork.CreateFont();
            fontB.FontHeightInPoints = 16;
            fontB.Boldweight = short.MaxValue;

            #region 单元格居中样式
            HSSFCellStyle styleCenter = hssfWork.CreateCellStyle();
            styleCenter.Alignment = HSSFCellStyle.ALIGN_CENTER;
            styleCenter.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER;//垂直居中
            styleCenter.BorderBottom = HSSFBorderFormatting.BORDER_THIN;
            styleCenter.BorderLeft = HSSFBorderFormatting.BORDER_THIN;
            styleCenter.BorderRight = HSSFBorderFormatting.BORDER_THIN;
            styleCenter.BorderTop = HSSFBorderFormatting.BORDER_THIN;
            styleCenter.SetFont(font);
            styleCenter.WrapText = true;
            #endregion 单元格居中样式

            #region 单元格居中样式加粗
            HSSFCellStyle styleCenterB = hssfWork.CreateCellStyle();
            styleCenterB.Alignment = HSSFCellStyle.ALIGN_CENTER;//居中
            styleCenterB.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER;//垂直居中
            styleCenterB.BorderBottom = HSSFBorderFormatting.BORDER_THIN;
            styleCenterB.BorderLeft = HSSFBorderFormatting.BORDER_THIN;
            styleCenterB.BorderRight = HSSFBorderFormatting.BORDER_THIN;
            styleCenterB.BorderTop = HSSFBorderFormatting.BORDER_THIN;
            styleCenterB.SetFont(fontB);
            styleCenterB.WrapText = true;
            #endregion 单元格居中样式

            #region 单元格居左样式
            HSSFCellStyle styleLeft = hssfWork.CreateCellStyle();
            styleLeft.Alignment = HSSFCellStyle.ALIGN_LEFT;
            styleLeft.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER;//垂直居中
            styleLeft.BorderBottom = HSSFBorderFormatting.BORDER_THIN;
            styleLeft.BorderLeft = HSSFBorderFormatting.BORDER_THIN;
            styleLeft.BorderRight = HSSFBorderFormatting.BORDER_THIN;
            styleLeft.BorderTop = HSSFBorderFormatting.BORDER_THIN;
            styleLeft.SetFont(font);
            styleLeft.WrapText = true;
            #endregion 单元格居中样式

            #region 单元格居右样式
            HSSFCellStyle styleRight = hssfWork.CreateCellStyle();
            styleRight.Alignment = HSSFCellStyle.ALIGN_RIGHT;
            styleRight.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER;//垂直居中
            styleRight.BorderBottom = HSSFBorderFormatting.BORDER_THIN;
            styleRight.BorderLeft = HSSFBorderFormatting.BORDER_THIN;
            styleRight.BorderRight = HSSFBorderFormatting.BORDER_THIN;
            styleRight.BorderTop = HSSFBorderFormatting.BORDER_THIN;
            styleRight.SetFont(font);
            styleRight.WrapText = true;
            #endregion 单元格居中样式

            #region 单元格居右样式加粗
            HSSFCellStyle styleRightB = hssfWork.CreateCellStyle();
            styleRightB.Alignment = HSSFCellStyle.ALIGN_RIGHT;
            styleRightB.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER;
            styleRightB.BorderBottom = HSSFBorderFormatting.BORDER_THIN;
            styleRightB.BorderLeft = HSSFBorderFormatting.BORDER_THIN;
            styleRightB.BorderRight = HSSFBorderFormatting.BORDER_THIN;
            styleRightB.BorderTop = HSSFBorderFormatting.BORDER_THIN;
            styleRightB.SetFont(fontB);
            styleRightB.WrapText = true;
            #endregion 单元格居中样式

            int rowIndex = 0;
            #region 表头
            HSSFRow TitleRow2 = sheet.CreateRow(rowIndex);
            HSSFCell projecttypecell = TitleRow2.CreateCell(0);
            projecttypecell.SetCellValue("承办科室");
            projecttypecell.CellStyle = styleCenterB;

            HSSFCell deptcell = TitleRow2.CreateCell(1);
            deptcell.SetCellValue("承办案件数量");
            deptcell.CellStyle = styleCenterB;

            HSSFCell peoplecell = TitleRow2.CreateCell(2);
            peoplecell.SetCellValue("承办案件数量");
            peoplecell.CellStyle = styleCenterB;

            HSSFCell projectnamecell = TitleRow2.CreateCell(3);
            projectnamecell.SetCellValue("承办案件数量");
            projectnamecell.CellStyle = styleCenterB;

            HSSFCell  pnamesl = TitleRow2.CreateCell(4);
            pnamesl.SetCellValue("承办案件数量");
            pnamesl.CellStyle = styleCenterB;


            HSSFCell appnumcell = TitleRow2.CreateCell(5);
            appnumcell.SetCellValue("案件小计");
            appnumcell.CellStyle = styleCenterB;

            HSSFCell totlemoneycell = TitleRow2.CreateCell(6);
            totlemoneycell.SetCellValue("超时小计");
            totlemoneycell.CellStyle = styleCenterB;

            TitleRow2.HeightInPoints = 20; 
            #endregion
            sheet.AddMergedRegion(new Region(rowIndex, 1, rowIndex, 4));


            #region 附表头
            rowIndex++;
            HSSFRow TitleRow21 = sheet.CreateRow(rowIndex);
            HSSFCell projecttypece2 = TitleRow21.CreateCell(0);
            projecttypece2.SetCellValue("承办科室");
            projecttypece2.CellStyle = styleCenterB;

            HSSFCell deptcell1 = TitleRow21.CreateCell(1);
            deptcell1.SetCellValue("已解决");
            deptcell1.CellStyle = styleCenterB;

            HSSFCell peoplecell1 = TitleRow21.CreateCell(2);
            peoplecell1.SetCellValue("已解决超时");
            peoplecell1.CellStyle = styleCenterB;

            HSSFCell projectnamecell1 = TitleRow21.CreateCell(3);
            projectnamecell1.SetCellValue("未解决");
            projectnamecell1.CellStyle = styleCenterB;

            HSSFCell appnumcell1 = TitleRow21.CreateCell(4);
            appnumcell1.SetCellValue("未解决超时");
            appnumcell1.CellStyle = styleCenterB;

            HSSFCell totlemoneycexj = TitleRow21.CreateCell(5);
            totlemoneycexj.SetCellValue("案件小计");
            totlemoneycexj.CellStyle = styleCenterB;

            HSSFCell totlemoneycell1 = TitleRow21.CreateCell(6);
            totlemoneycell1.SetCellValue("超时小计");
            totlemoneycell1.CellStyle = styleCenterB;
            #endregion

            sheet.AddMergedRegion(new Region(0, 0, rowIndex, 0));
            sheet.AddMergedRegion(new Region(0, 5, rowIndex, 5));
            sheet.AddMergedRegion(new Region(0, 6, rowIndex, 6));

            #region 数据

            int t1=0, t2=0, t3=0, t4=0, t5=0, t6=0;
            for (int i = 0; i < list.Count; i++)
            {
                var dr = list[i];
                rowIndex++;
                int beginrow = rowIndex;

                HSSFRow DataRow = sheet.CreateRow(rowIndex);

                HSSFCell projecttypename = DataRow.CreateCell(0);
                projecttypename.SetCellValue(dr.DeptName.ToString());
                projecttypename.CellStyle = styleCenter;

                HSSFCell deptname = DataRow.CreateCell(1);
                deptname.SetCellValue(dr.YJJ.ToString());
                deptname.CellStyle = styleLeft;
                t1 = t1 + Convert.ToInt32(dr.YJJ);

                HSSFCell peoplename = DataRow.CreateCell(2);
                peoplename.SetCellValue(dr.YJJCS.ToString());
                peoplename.CellStyle = styleRight;
                t2 = t2 + Convert.ToInt32(dr.YJJCS);

                HSSFCell projectnamename = DataRow.CreateCell(3);
                projectnamename.SetCellValue(dr.WJJ.ToString());
                projectnamename.CellStyle = styleRight;
                t3 = t3 + Convert.ToInt32(dr.WJJ);

                HSSFCell ProReasonContent = DataRow.CreateCell(4);
                ProReasonContent.SetCellValue(dr.WJJCS.ToString());
                ProReasonContent.CellStyle = styleRight;
                t4 = t4 + Convert.ToInt32(dr.WJJCS);

                HSSFCell totalajxj = DataRow.CreateCell(5);
                var total = Convert.ToInt32(dr.WJJ) + Convert.ToInt32(dr.YJJ);
                totalajxj.SetCellValue(total);
                totalajxj.CellStyle = styleRight;
                t5 = t5 + Convert.ToInt32(total);

                HSSFCell totalcsxj = DataRow.CreateCell(6);
                var total2 = Convert.ToInt32(dr.WJJCS) + Convert.ToInt32(dr.YJJCS);
                totalcsxj.SetCellValue(total2);
                totalcsxj.CellStyle = styleRight;
                t6 = t6 + Convert.ToInt32(total2);
            }
            #endregion

            #region 总计
            rowIndex++;
            HSSFRow DataRowt = sheet.CreateRow(rowIndex);

            HSSFCell tt = DataRowt.CreateCell(0);
            tt.SetCellValue("总计");
            tt.CellStyle = styleCenter;

            HSSFCell tt1 = DataRowt.CreateCell(1);
            tt1.SetCellValue(t1);
            tt1.CellStyle = styleLeft;

            HSSFCell tt2 = DataRowt.CreateCell(2);
            tt2.SetCellValue(t2);
            tt2.CellStyle = styleRight;

            HSSFCell tt3 = DataRowt.CreateCell(3);
            tt3.SetCellValue(t3);
            tt3.CellStyle = styleRight;

            HSSFCell tt4 = DataRowt.CreateCell(4);
            tt4.SetCellValue(t4);
            tt4.CellStyle = styleRight;

            HSSFCell tt5 = DataRowt.CreateCell(5);
            tt5.SetCellValue(t5);
            tt5.CellStyle = styleRight;

            HSSFCell tt6 = DataRowt.CreateCell(6);
            tt6.SetCellValue(t6);
            tt6.CellStyle = styleRight;
            #endregion

            #region 设置宽度
            sheet.SetColumnWidth(0, 100 * 60);
            sheet.SetColumnWidth(1, 100 * 60);
            sheet.SetColumnWidth(2, 100 * 60);
            sheet.SetColumnWidth(3, 100 * 60);
            sheet.SetColumnWidth(4, 100 * 60);
            sheet.SetColumnWidth(5, 100 * 60);
            sheet.SetColumnWidth(6, 100 * 60);
            #endregion
            return MemoryStream(hssfWork);
        }


public MemoryStream MemoryStream(HSSFWorkbook hssfWork)
        {
            MemoryStream file = new MemoryStream();
            hssfWork.Write(file);
            return file;
        }