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(); } |
人的潜能是能够挖掘的,当你说太晚了的时候,你一定要谨慎,它可能是你退却的借口,没有谁能够阻止你成功,除了你自己,该炫自己的时候,千万别对自己手软!
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 分享一个免费、快速、无限量使用的满血 DeepSeek R1 模型,支持深度思考和联网搜索!
· 25岁的心里话
· 基于 Docker 搭建 FRP 内网穿透开源项目(很简单哒)
· ollama系列01:轻松3步本地部署deepseek,普通电脑可用
· 按钮权限的设计及实现