使用NPOI 汇出EXCEL，设置样式，字体等

  MemoryStream ms = new MemoryStream();

 

  XSSFWorkbook workbook = new XSSFWorkbook();//创建Workbook对象

            for (int i = 0; i < li.Count; i++)
            {
                ISheet sheet = workbook.CreateSheet("sheet"+i);//创建工作表

                DataTable syscomment = ec.getDtFromSYSCOMMENT("FD", "PURI09", systemInfo.Locale);

                DataTable tak003 = ec.getDtFromSYSCOMMENT("FD", "PUR-TAK003", systemInfo.Locale);

                DataRow[] dr = dt.Select("TAL002='" + li[i] + "'", "TAL005,TAL007,TAL004");               

                ICellStyle style = workbook.CreateCellStyle();
                style.Alignment = HorizontalAlignment.Center;
                style.WrapText = true;
                IFont font = workbook.CreateFont();
                font.FontHeightInPoints = 16;
                font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
                font.FontName = "標楷體";
                style.SetFont(font);//HEAD 样式

                IRow row = sheet.CreateRow(0);
                row.Height = 200 * 5;
                row.CreateCell(0).SetCellValue(getString(syscomment, "Company") + "\n" + getString(syscomment, "Report"));
             
                //设置Head的样式
                row.GetCell(0).CellStyle = style;

                MagreRange(sheet, 0, 0, 0, 13);//合并单元格

                //正式数据              
                ICellStyle styleCommonLeft= workbook.CreateCellStyle();
                styleCommonLeft.Alignment = HorizontalAlignment.Left;
                styleCommonLeft.WrapText = true;
                IFont fontLeft = workbook.CreateFont();
                fontLeft.FontHeightInPoints = 10;
                fontLeft.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
                fontLeft.FontName = "標楷體";
                styleCommonLeft.SetFont(fontLeft);

 

                ICellStyle styleCommonRight = workbook.CreateCellStyle();
                styleCommonRight.Alignment = HorizontalAlignment.Left;
                styleCommonRight.VerticalAlignment = VerticalAlignment.Center;
                styleCommonRight.WrapText = true;
                IFont fontRight = workbook.CreateFont();
                fontRight.FontHeightInPoints = 10;
                fontRight.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
                fontRight.FontName = "標楷體";
                styleCommonRight.SetFont(fontRight);

                ICellStyle styleBorder = workbook.CreateCellStyle();
                styleBorder.BorderBottom = NPOI.SS.UserModel.BorderStyle.Double;
                styleBorder.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                styleBorder.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                styleBorder.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                styleBorder.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
                styleBorder.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
                styleBorder.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
                styleBorder.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;

                sheet.DefaultRowHeight = 200 * 2;

                sheet.SetColumnWidth(0, 7 * 256);
                sheet.SetColumnWidth(1, 7 * 256);
                sheet.SetColumnWidth(2, 18 * 256);
                sheet.SetColumnWidth(4, 18 * 256);

                IRow row2 = sheet.CreateRow(1);
                row2.CreateCell(0).SetCellValue(getString(syscomment, "TAL001"));
                row2.CreateCell(2).SetCellValue(dr[0]["TAL001"].ToString());
                row2.CreateCell(3).SetCellValue(getString(syscomment, "TAK009"));
                row2.CreateCell(4).SetCellValue(dr[0]["TAK009"].ToString());
                row2.CreateCell(5).SetCellValue(getString(syscomment, "TAK008"));
                row2.CreateCell(6).SetCellValue(dr[0]["TAK008"].ToString());
                row2.CreateCell(8).SetCellValue(getString(syscomment, "MAC012"));
                row2.CreateCell(9).SetCellValue(dr[0]["MAC012"].ToString());
                row2.CreateCell(11).SetCellValue(getString(syscomment, "MAC013"));
                row2.CreateCell(12).SetCellValue(dr[0]["MAC013"].ToString());

                row2.GetCell(0).CellStyle = styleCommonLeft;
                row2.GetCell(2).CellStyle = styleCommonLeft;
                row2.GetCell(3).CellStyle = styleCommonLeft;
                row2.GetCell(4).CellStyle = styleCommonLeft;
                row2.GetCell(5).CellStyle = styleCommonLeft;
                row2.GetCell(6).CellStyle = styleCommonLeft;
                row2.GetCell(8).CellStyle = styleCommonLeft;
                row2.GetCell(9).CellStyle = styleCommonLeft;
                row2.GetCell(11).CellStyle = styleCommonLeft;
                row2.GetCell(12).CellStyle = styleCommonLeft;

                MagreRange(sheet, 1, 1, 0, 1);//合并单元格
                MagreRange(sheet, 1, 1, 6, 7);
                MagreRange(sheet, 1, 1, 9, 10);
                MagreRange(sheet, 1, 1, 12, 13);

                IRow row3 = sheet.CreateRow(2);
                row3.CreateCell(0).SetCellValue(getString(syscomment, "TAL005L"));
                row3.CreateCell(2).SetCellValue(dr[0]["TAL005L"].ToString());
                row3.CreateCell(3).SetCellValue(getString(syscomment, "TAL006L"));
                row3.CreateCell(4).SetCellValue(dr[0]["TAL006L"].ToString());
                row3.CreateCell(8).SetCellValue(getString(syscomment, "TAL007L"));
                row3.CreateCell(9).SetCellValue(dr[0]["TAL007L"].ToString());
                row3.CreateCell(11).SetCellValue(getString(syscomment, "TAK009"));
                row3.CreateCell(12).SetCellValue(dr[0]["TAK003"].ToString());
                row3.GetCell(0).CellStyle = styleCommonLeft;
                row3.GetCell(2).CellStyle = styleCommonLeft;
                row3.GetCell(3).CellStyle = styleCommonLeft;
                row3.GetCell(4).CellStyle = styleCommonLeft;
                row3.GetCell(8).CellStyle = styleCommonLeft;
                row3.GetCell(9).CellStyle = styleCommonLeft;
                row3.GetCell(11).CellStyle = styleCommonLeft;
                row3.GetCell(12).CellStyle = styleCommonLeft;

                MagreRange(sheet, 2, 2, 0, 1);//合并单元格
                MagreRange(sheet, 2, 2, 4, 7);
                MagreRange(sheet, 2, 2, 9, 10);
                MagreRange(sheet, 2, 2, 12, 13);

                IRow row4 = sheet.CreateRow(3);
                row4.CreateCell(0).SetCellValue(getString(syscomment, "ITEM"));
                row4.CreateCell(1).SetCellValue(getString(syscomment, "TAL005"));
                row4.CreateCell(3).SetCellValue(getString(syscomment, "TAL006"));
                row4.CreateCell(7).SetCellValue(getString(syscomment, "TAL007"));
                row4.CreateCell(8).SetCellValue(getString(syscomment, "TAL009"));
                row4.CreateCell(9).SetCellValue(getString(syscomment, "TAL011"));
                row4.CreateCell(10).SetCellValue(getString(syscomment, "TAL014"));
                row4.CreateCell(11).SetCellValue(getString(syscomment, "TAL012"));
                row4.CreateCell(12).SetCellValue(getString(syscomment, "TAL013"));
                row4.CreateCell(13).SetCellValue(getString(syscomment, "AMOUNT"));
                row4.GetCell(0).CellStyle = styleCommonLeft;
                row4.GetCell(1).CellStyle = styleCommonLeft;
                row4.GetCell(3).CellStyle = styleCommonLeft;
                row4.GetCell(7).CellStyle = styleCommonLeft;
                row4.GetCell(8).CellStyle = styleCommonLeft;
                row4.GetCell(9).CellStyle = styleCommonLeft;
                row4.GetCell(10).CellStyle = styleCommonLeft;
                row4.GetCell(11).CellStyle = styleCommonLeft;
                row4.GetCell(12).CellStyle = styleCommonLeft;
                row4.GetCell(13).CellStyle = styleCommonLeft;

                MagreRange(sheet, 3, 3, 1, 2);//合并单元格
                MagreRange(sheet, 3, 3, 3, 6);

                for (int k = 0; k < dr.GetLength(0); k++)
                {
                    IRow rowk = sheet.CreateRow(4 + k);
                    rowk.CreateCell(0).SetCellValue((k + 1).ToString());
                    rowk.CreateCell(1).SetCellValue(dr[k]["TAL005"].ToString());
                    rowk.CreateCell(3).SetCellValue(dr[k]["TAL006"].ToString());
                    rowk.CreateCell(7).SetCellValue(dr[k]["TAL007"].ToString());
                    rowk.CreateCell(8).SetCellValue(dr[k]["TAL009"].ToString());
                    rowk.CreateCell(9).SetCellValue(dr[k]["TAL011"].ToString());
                    rowk.CreateCell(10).SetCellValue(dr[k]["TAL014"].ToString());
                    rowk.CreateCell(11).SetCellValue(dr[k]["TAL012"].ToString());
                    rowk.CreateCell(12).SetCellValue(dr[k]["TAL013"].ToString());
                    rowk.CreateCell(13).SetCellValue(dr[k]["AMOUNT"].ToString());
                    rowk.GetCell(0).CellStyle = styleCommonLeft;
                    rowk.GetCell(1).CellStyle = styleCommonLeft;
                    rowk.GetCell(3).CellStyle = styleCommonLeft;
                    rowk.GetCell(7).CellStyle = styleCommonLeft;
                    rowk.GetCell(8).CellStyle = styleCommonLeft;
                    rowk.GetCell(9).CellStyle = styleCommonLeft;
                    rowk.GetCell(10).CellStyle = styleCommonLeft;
                    rowk.GetCell(11).CellStyle = styleCommonLeft;
                    rowk.GetCell(12).CellStyle = styleCommonLeft;
                    rowk.GetCell(13).CellStyle = styleCommonLeft;

                    MagreRange(sheet, 4 + k, 4 + k, 1, 2);//合并单元格
                    MagreRange(sheet, 4 + k, 4 + k, 3, 6);

                }

                int index = 4 + dr.GetLength(0);

                double s = 0;
                try
                {
                    s = Convert.ToDouble(dt.Compute("sum(MAC013)", "TAL002='" + li[i] + "'"));
                }
                catch (Exception ex)
                {
                }
                IRow rowindex1 = sheet.CreateRow(index);
                rowindex1.CreateCell(11).SetCellValue(getString(syscomment, "TOTAL"));
                rowindex1.CreateCell(12).SetCellValue(s);
                rowindex1.GetCell(11).CellStyle = styleCommonLeft;
                rowindex1.GetCell(12).CellStyle = styleCommonLeft;

                MagreRange(sheet, index, index, 0, 10);
                MagreRange(sheet, index, index, 12, 13);

                string tempPUR0050 = string.Join("。\n", ec.getGlobalValue("PUR-0050").Split('。'));

                IRow rowindex2 = sheet.CreateRow(index + 1);
                rowindex2.CreateCell(0).SetCellValue(getString(syscomment, "TEXT1"));
                rowindex2.CreateCell(2).SetCellValue(tempPUR0050);
                rowindex2.GetCell(0).CellStyle = styleCommonRight;
                rowindex2.GetCell(2).CellStyle = styleCommonLeft;
                rowindex2.Height = 200 * 19;
                MagreRange(sheet, index + 1, index + 1, 0, 1);
                MagreRange(sheet, index + 1, index + 1, 2, 13);

                IRow rowindex3 = sheet.CreateRow(index + 2);
                rowindex3.CreateCell(0).SetCellValue(getString(syscomment, "TEXT3"));
                rowindex3.CreateCell(4).SetCellValue(getString(syscomment, "TEXT4"));
                rowindex3.CreateCell(7).SetCellValue(getString(syscomment, "TEXT5"));
                rowindex3.GetCell(0).CellStyle = styleCommonLeft;
                rowindex3.GetCell(4).CellStyle = styleCommonLeft;
                rowindex3.GetCell(7).CellStyle = styleCommonLeft;
                MagreRange(sheet, index + 2, index + 2, 0, 1);
                MagreRange(sheet, index + 2, index + 2, 2, 3);
                MagreRange(sheet, index + 2, index + 2, 5, 6);
                MagreRange(sheet, index + 2, index + 2, 7, 8);
                MagreRange(sheet, index + 2, index + 2, 9, 13);
            }

            workbook.Write(ms);
            ms.Flush();
            workbook = null;

 

    /// <summary>   
    /// 由DataSet导出Excel
    /// </summary>   
    /// <param name="sourceTable">要导出数据的DataTable</param>  
    /// <param name="fileName">指定Excel工作表名称</param> 
    /// <returns>Excel工作表</returns> 
    public void ExportDataSetToExcel(string fileName)
    {
        MemoryStream ms = exportExcel() as MemoryStream;
        HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;
        HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8));
        HttpContext.Current.Response.BinaryWrite(ms.ToArray());
        HttpContext.Current.Response.End();
        ms.Close();
        ms.Dispose();
        ms = null;
    }