NPOI Excel模板填充数据后导出 港控/mmm° 2022-09-24 14:23 223阅读 0赞 ### 引入Npoi包 添加引用dotnet2 或dotnet4 ### ![Center][] ### 代码示例 ### //导出入口 public void NpoiExportExcel() { DataTable dt = new DataTable(); //此处得到dt类型数据,暂以new DataTable()代替 var fileName = "test"; var exportTemplatePath = "~/FileTemplate/test.xlsx"; DownloadExcel(fileName, dt, exportTemplatePath); } public void DownloadExcel(string reportName, DataTable dt, string exportTemplatePath) { Stream s = RenderDataTableToExcel(dt, exportTemplatePath); if (s != null) { MemoryStream ms = s as MemoryStream; HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename=" + HttpUtility.UrlEncode(reportName) + DateTime.Now.ToString("yyyyMMdd") + ".xlsx")); HttpContext.Current.Response.AddHeader("Content-Length", ms.ToArray().Length.ToString()); HttpContext.Current.Response.BinaryWrite(ms.ToArray()); HttpContext.Current.Response.Flush(); ms.Close(); ms.Dispose(); } else HttpContext.Current.Response.Write("出错,无法下载!"); } //使用引入的npoi生成excel public Stream RenderDataTableToExcel(DataTable sourceTable, string exportTemplatePath) { XSSFWorkbook workbook = null; MemoryStream ms = null; ISheet sheet = null; XSSFRow headerRow = null; string templetFileName = HttpContext.Current.Server.MapPath(exportTemplatePath); FileStream file = new FileStream(templetFileName, FileMode.Open, FileAccess.Read); workbook = new XSSFWorkbook(file); try { ms = new MemoryStream(); sheet = workbook.GetSheet("Sheet1"); int rowIndex = sheet.LastRowNum; //数据源DataTable,填充到excel foreach (DataRow row in sourceTable.Rows) { XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex); foreach (DataColumn column in sourceTable.Columns) dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString()); ++rowIndex; } //列宽自适应,只对英文和数字有效 for (int i = 0; i <= sourceTable.Columns.Count; ++i) sheet.AutoSizeColumn(i); workbook.Write(ms); ms.Flush(); } catch (Exception ex) { throw; return null; } finally { ms.Close(); sheet = null; headerRow = null; workbook = null; } return ms; } ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzE2OTk4OTQ1_size_16_color_FFFFFF_t_70][] [Center]: /images/20220719/122be5d765374922ae9a8b9a658f4893.png [watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzE2OTk4OTQ1_size_16_color_FFFFFF_t_70]: /images/20220719/9622b47e61244e5c968d064a043dd7f5.png
还没有评论,来说两句吧...