62,046
社区成员
发帖
与我相关
我的任务
分享
/// <summary>
/// 将DataTable数据导出到Excel文件中(xlsx)
/// </summary>
/// <param name="dt"></param>
/// <param name="file"></param>
public static void TableToExcelForXLSX(DataTable dt, string file, string sheetName)
{
XSSFWorkbook xssfworkbook = new XSSFWorkbook();
ISheet sheet = null;
if (!string.IsNullOrEmpty(sheetName))
{
sheet = xssfworkbook.CreateSheet(sheetName);
}
else
{
sheet = xssfworkbook.CreateSheet("Sheet1");
}
//表头
IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
}
//数据
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row1 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
//转为字节数组
MemoryStream stream = new MemoryStream();
xssfworkbook.Write(stream);
HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", file));
HttpContext.Current.Response.AddHeader("Content-Length", stream.ToArray().Length.ToString());
HttpContext.Current.Response.BinaryWrite(stream.ToArray());
xssfworkbook = null;
stream.Close();
stream.Dispose();
HttpContext.Current.Response.End();
}