NPOI 导出excel

/// <summary>
    /// NPOI导出Excel
    /// </summary>
    /// <param name="dt">数据</param>
    /// <param name="title">文件名称</param>
    /// <param name="loadWhere">导出条件</param>
    /// <param name="type">类别（是否需要合计）type=-1不需要合计</param>
    public void NpoiExcel(DataTable dt, string title, string loadWhere, int type, string fileName = "")
    {
        NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();

        var sheet1 = book.CreateSheet(title);
        int dtRows = dt.Rows.Count;
        int dtColums = dt.Columns.Count;

        //1.标题合并单元格
        sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtColums - 1));
        var row0 = sheet1.CreateRow(0);
        row0.HeightInPoints = 30;
        var cell0 = row0.CreateCell(0);
        cell0.SetCellValue(title);
        var style0 = book.CreateCellStyle();
        style0.Alignment = HorizontalAlignment.Center;//水平居中 
        style0.VerticalAlignment = VerticalAlignment.Center;//垂直居中 
        style0.WrapText = true;//自动换行
        var font0 = book.CreateFont();
        font0.Boldweight = 700;
        font0.FontHeightInPoints = 16;
        style0.SetFont(font0);
        cell0.CellStyle = style0;

        //2.导出条件合并单元格
        sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 0, dtColums - 1));
        var row1 = sheet1.CreateRow(1);
        row1.HeightInPoints = 20;
        var cell1 = row1.CreateCell(0);
        cell1.SetCellValue(loadWhere);

        //3.列名样式
        var style2 = book.CreateCellStyle();
        style2.Alignment = HorizontalAlignment.Center;//水平居中 
        style2.VerticalAlignment = VerticalAlignment.Center;//垂直居中 
        style2.WrapText = true;//自动换行
        var font2 = book.CreateFont();
        font2.Boldweight = 700;
        font2.FontHeightInPoints = 10;
        style2.SetFont(font2);
        style2.BottomBorderColor = IndexedColors.Black.Index;
        style2.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
        style2.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
        style2.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
        style2.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
        style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
        style2.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
        style2.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
        style2.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;

        //列名
        var row = sheet1.CreateRow(2);
        row.HeightInPoints = 20;
        for (int i = 0; i < dtColums; i++)
        {
            var cellCo = row.CreateCell(i);
            cellCo.CellStyle = style2;
            cellCo.SetCellValue(dt.Columns[i].ColumnName);
        }

        //4.行内容样式
        var style3 = book.CreateCellStyle();
        var styleInt = book.CreateCellStyle();
        var styleDecimal = book.CreateCellStyle();

        style3.WrapText = true;
        styleInt.DataFormat = 0;
        style3.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
        style3.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
        style3.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
        style3.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
        style3.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
        style3.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
        style3.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.White.Index;
        style3.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;

        styleInt.CloneStyleFrom(style3);
        styleInt.Alignment = HorizontalAlignment.Right;
        var test = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormats();
        styleInt.DataFormat = 38;
        styleDecimal.CloneStyleFrom(style3);
        styleDecimal.Alignment = HorizontalAlignment.Right;
        styleDecimal.DataFormat = 40;

        //行
        for (int i = 0; i < dtRows; i++)
        {
            var rowtemp = sheet1.CreateRow(i + 3);
            rowtemp.HeightInPoints = 20;
            for (int a = 0; a < dtColums; a++)
            {
                var cellCo = rowtemp.CreateCell(a);
                if (dt.Columns[a].DataType == typeof(string) || dt.Columns[a].DataType == typeof(DateTime))
                {
                    cellCo.SetCellValue(Convert.ToString(dt.Rows[i][a]));
                    cellCo.CellStyle = style3;
                }
                else if (dt.Columns[a].DataType == typeof(decimal) || dt.Columns[a].DataType == typeof(float) || dt.Columns[a].DataType == typeof(double))
                {
                    cellCo.CellStyle = styleDecimal;
                    if (dt.Rows[i][a] != DBNull.Value)
                    {
                        cellCo.SetCellValue(Convert.ToDouble(dt.Rows[i][a]));
                    }
                }
                else // int
                {
                    cellCo.CellStyle = styleInt;
                    if (dt.Rows[i][a] != DBNull.Value)
                    {
                        cellCo.SetCellValue(Convert.ToDouble(dt.Rows[i][a]));
                    }
                }
            }
        }

        //合计行以及备注信息,type=-1时，不计算合计行
        if (type >= 0)
        {
            var rowFooter = sheet1.CreateRow(dtRows + 3);
            rowFooter.HeightInPoints = 20;
            var cellCo = rowFooter.CreateCell(0);
            cellCo.CellStyle = style3;
            cellCo.SetCellValue("合计");
            for (int i = 1; i < dtColums; i++)
            {
                if (i < type)
                {
                    cellCo = rowFooter.CreateCell(i);
                    cellCo.CellStyle = style3;
                }
                else
                {
                    cellCo = rowFooter.CreateCell(i);
                    if (dt.Rows.Count > 0)
                    {
                        cellCo.SetCellFormula(string.Format("SUM({0}:{1})", GetExcelColumnName(i) + 4, GetExcelColumnName(i) + (dt.Rows.Count + 3)));
                        if (dt.Columns[i].DataType == typeof(decimal) || dt.Columns[i].DataType == typeof(float) || dt.Columns[i].DataType == typeof(double))
                        {
                            cellCo.CellStyle = styleDecimal;
                        }
                        else // int
                        {
                            cellCo.CellStyle = styleInt;
                        }
                    }
                }
            }
        }
        this.AutoColumnWidth(sheet1, dtColums);

        // 写入到客户端 
        if (string.IsNullOrEmpty(fileName))
        {
            fileName = title + "(" + DateTime.Now.ToString("yyyy-MM-dd") + ")";
        }
        System.IO.MemoryStream ms = new System.IO.MemoryStream();
        book.Write(ms);
        System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=\"" + HttpUtility.UrlEncode(fileName + ".xls", System.Text.Encoding.UTF8) + "\"");
        System.Web.HttpContext.Current.Response.BinaryWrite(ms.ToArray());
        System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
        book = null;
        ms.Close();
        ms.Dispose();
    }