npoi2.0将datatable对象转换为excel2007示例
NPOI 2.0将DataTable对象转换为Excel 2007文件提供下载
代码如下:
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.IO;
private Stream RenderDataTableToExcel(DataTable SourceTable)
{
XSSFWorkbook workbook = null;
MemoryStream ms = null;
ISheet sheet = null;
XSSFRow headerRow = null;
try
{
workbook = new XSSFWorkbook();
ms = new MemoryStream();
sheet = workbook.CreateSheet();
headerRow = (XSSFRow)sheet.CreateRow(0);
foreach (DataColumn column in SourceTable.Columns)
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
int rowIndex = 1;
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)
{
return null;
}
finally
{
ms.Close();
&n
bsp; sheet = null;
headerRow = null;
workbook = null;
}
return ms;
}
private void DownloadExcel(DataTable dt,string reportName)
{
Stream s = RenderDataTableToExcel(dt);
if (s != null)
{
MemoryStream ms = resultStream.result as MemoryStream;
Response.AddHeader("Content-Disposition", string.Format("attachment;filename=" + HttpUtility.UrlEncode(reportName) + DateTime.Now.ToString("yyyyMMdd") + ".xlsx"));
Response.AddHeader("Content-Length", ms.ToArray().Length.ToString());
Response.BinaryWrite(ms.ToArray());
Response.Flush();
ms.Close();
ms.Dispose();
}
else
Response.Write("出错，无法下载！");
}
