NPOI Excel模板填充数据后导出

港控/mmm° 2022-09-24 14:23 313阅读 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