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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182

/// <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(); }