NPOI 将多个DataTable数据导入到excel中，并输出到浏览器下载

        /// <summary>
        /// 将多个DataTable数据导入到excel中
        /// </summary>
        /// <param name="dts">要导入的数据集合</param>
        /// <param name="strExcelFileName">定义Excel文件名</param>
        /// <param name="indexType">给个 1 就行</param>
        public static bool DataTableToExcels(List<DataTable> dts, string strExcelFileName, int indexType)
        {
            bool BSave = false;
            try
            {
                HSSFWorkbook workbook = new HSSFWorkbook();
                DataSet set = new DataSet();
                ICellStyle HeadercellStyle = workbook.CreateCellStyle();
                foreach (DataTable dt in dts)
                {
                    ISheet sheet = workbook.CreateSheet(dt.TableName);


                    HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                    HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                    HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                    HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                    HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                    //字体
                    NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
                    headerfont.Boldweight = (short)FontBoldWeight.Bold;
                    HeadercellStyle.SetFont(headerfont);


                    //用column name 作为列名
                    int icolIndex = 0;
                    IRow headerRow = sheet.CreateRow(0);
                    foreach (DataColumn item in dt.Columns)
                    {
                        ICell cell = headerRow.CreateCell(icolIndex);
                        cell.SetCellValue(item.ColumnName);
                        cell.CellStyle = HeadercellStyle; 
                        icolIndex++;
                    }


                    ICellStyle cellStyle = workbook.CreateCellStyle();
                    //为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text来看
                    cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("¥#,##0.00");
                    cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                    cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                    cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                    cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                    cellStyle.VerticalAlignment = VerticalAlignment.Center;
                    cellStyle.Alignment = HorizontalAlignment.Center;
                    NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
                    cellfont.Boldweight = (short)FontBoldWeight.Normal; 
                    cellStyle.SetFont(cellfont);
                    if (indexType == 1)
                    {
                        //建立内容行
                        int iRowIndex = 1;
                        int iCellIndex = 0;
                        foreach (DataRow Rowitem in dt.Rows)
                        {
                            IRow DataRow = sheet.CreateRow(iRowIndex);
                            foreach (DataColumn Colitem in dt.Columns)
                            {
                                ICell cell = DataRow.CreateCell(iCellIndex);
                                cell.SetCellValue(Rowitem[Colitem].ToString());
                                cell.CellStyle = cellStyle; 
                                iCellIndex++;
                            }
                            iCellIndex = 0;
                            iRowIndex++;
                        }
                        //自适应列宽
                        for (int i = 0; i < icolIndex; i++)
                        {
                            sheet.AutoSizeColumn(i);
                        }
                    }
                    if (dt.TableName == "工程款统计表")
                    {
                        sheet.ShiftRows(0, sheet.LastRowNum, 1); 
                        var newrow = sheet.CreateRow(0);
                        newrow.CreateCell(1).SetCellValue("工程来款统计"); 
                        var cell1 = sheet.GetRow(0).GetCell(1);
                        cell1.CellStyle = HeadercellStyle; 
                        newrow.CreateCell(11).SetCellValue("内包结算");
                        var cell2 = sheet.GetRow(0).GetCell(11);
                        cell2.CellStyle = HeadercellStyle; 
                        newrow.CreateCell(17).SetCellValue("付款");
                        var cell3 = sheet.GetRow(0).GetCell(17);
                        cell3.CellStyle = HeadercellStyle;
                        newrow.CreateCell(18).SetCellValue("剩余可用资金");
                        var cell4 = sheet.GetRow(0).GetCell(18);
                        cell4.CellStyle = HeadercellStyle;
                        sheet.AddMergedRegion(new CellRangeAddress(0, 0, 1, 10));//起始行，结束行，起始列，结束列 
                        sheet.AddMergedRegion(new CellRangeAddress(0, 0, 18, 21));//起始行，结束行，起始列，结束列 
                        sheet.AddMergedRegion(new CellRangeAddress(0, 0, 11, 16));//起始行，结束行，起始列，结束列 
                    }
                    if (dt.TableName == "项目经理来款和其他来款统计表")
                    {
                        sheet.ShiftRows(0, sheet.LastRowNum, 1);
                        var newrow = sheet.CreateRow(0);
                        newrow.CreateCell(1).SetCellValue("项目经理来款统计");
                        var cell1 = sheet.GetRow(0).GetCell(1);
                        cell1.CellStyle = HeadercellStyle;
                        newrow.CreateCell(7).SetCellValue("其他来款统计");
                        var cell2 = sheet.GetRow(0).GetCell(7);
                        cell2.CellStyle = HeadercellStyle;
                       
                        sheet.AddMergedRegion(new CellRangeAddress(0, 0, 1, 6));//起始行，结束行，起始列，结束列 
                        sheet.AddMergedRegion(new CellRangeAddress(0, 0, 7, 10));//起始行，结束行，起始列，结束列  
                    }
                }

                //设置导出文件路径
                string path = HttpContext.Current.Server.MapPath("Export/");
                //设置新建文件路径及名称
                //string savePath = path + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss") + ".xls";
                string savePath = new BaseManage.Attach.UploadFile().getExcelPath() + strExcelFileName + DateTime.Now.ToString("yyyy_MM_dd_HH_mm") + ".xls";

                FileStream file = new FileStream(strExcelFileName, FileMode.OpenOrCreate);
                workbook.Write(file);

                //创建一个 IO 流
                MemoryStream ms = new MemoryStream();
                //写入到流
                workbook.Write(ms);
                //转换为字节数组
                byte[] bytes = ms.ToArray();
                file.Write(bytes, 0, bytes.Length);
                file.Flush();
                //还可以调用下面的方法，把流输出到浏览器下载
                OutputClient(bytes, strExcelFileName);
                //释放资源
                bytes = null;
                ms.Close();
                ms.Dispose();
                file.Close();
                file.Dispose();
                File.Delete(savePath);

                workbook.Close();
                // sheet = null;
                workbook = null;


                file.Flush();
                file.Close();
                BSave = true;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return BSave;
        }
        /// <summary>
        /// 流输出到浏览器下载
        /// </summary>
        /// <param name="bytes"></param>
        public static void OutputClient(byte[] bytes, string name)
        {
            HttpResponse response = HttpContext.Current.Response;

            response.Buffer = true;

            response.Clear();
            response.ClearHeaders();
            response.ClearContent();

            response.ContentType = "application/vnd.ms-excel";
            response.AddHeader("Content-Length", bytes.Length.ToString());
            response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", name + DateTime.Now.ToString("yyyyMMddHHmm")));

            response.Charset = "GB2312";
            response.ContentEncoding = Encoding.GetEncoding("GB2312");

            response.BinaryWrite(bytes);
            response.Flush();

            response.Close();
        }