NPOI Excel模板填充数据后导出

来源：互联网发布：沂南知豆租赁怎么办理编辑：程序博客网时间：2024/04/29 10:50

引入Npoi包添加引用dotnet2 或dotnet4

Excel模板 ( .xlsx)

public void NpoiExportExcel()
{
string sql = "exec ZonePlaza";
DataTable dt = DataFactory.Database().FindTableBySql(sql);
var fileName = "asdasda";
var exportTemplatePath = "~/FileTemplate/汇总表.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("出错，无法下载！");
}

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;
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;
}

0 0