NPOI Excel模板填充数据后导出

引入Npoi包  添加引用dotnet2 或dotnet4

 

代码示例

 //导出入口
 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;
 }