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();

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("出错，无法下载！");

}