如何一个DataTable变量导出到EXCEL和下载Excel
问题描述:
我试图使用NPOI这样的:如何一个DataTable变量导出到EXCEL和下载Excel
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 = s as MemoryStream;
Response.AddHeader("Content-Disposition", string.Format("attachment;filename=" + HttpUtility.UrlEncode(reportName) + DateTime.Now.ToString("yyyyMMdd") + ".xlsx"));
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Length", ms.ToArray().Length.ToString());
Response.BinaryWrite(ms.ToArray());
Response.Flush();
ms.Close();
ms.Dispose();
}
else
Response.Write("Error!Connot Download");
}
我有二进制流,而不是MS-Excel文件。 PS:我真的想知道如何生成一个文件进行下载,也就是说,为什么你的代码工作,浏览器生成文件或服务器?
答
NPOI是一个辅助模块,用于创建四个excel文件。这是在服务器端和内存中创建的。 (workbook.Write(ms)将excel文件写入内存)excel文件以字节[]的形式通过网络,浏览器根据文件和内容类型决定如何处理它。
当u使用经典的asp.net然后把一个链接在你的aspx页面像下面
<a target="_blank" href="Handler.ashx" >download...</a>
创建Handler.ashx并把代码FOM DownloadExcel成的ProcessRequest从Handler.ashx。
public void ProcessRequest (HttpContext context)
{
//create dumy data, or in youre case the data form somewhere else
DataTable table = new DataTable();
table.Columns.AddRange(new[]
{
new DataColumn("Name")
});
table.Rows.Add("david");
table.Rows.Add("Ruud");
// your code
Stream s = RenderDataTableToExcel(dt);
if (s != null)
{
MemoryStream ms = s as MemoryStream;
Response.AddHeader("Content-Disposition", string.Format("attachment;filename=" + HttpUtility.UrlEncode(reportName) + DateTime.Now.ToString("yyyyMMdd") + ".xlsx"));
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Length", ms.ToArray().Length.ToString());
Response.BinaryWrite(ms.ToArray());
Response.Flush();
ms.Close();
ms.Dispose();
}
else
Response.Write("Error!Connot Download");
}
}
在MVC中就像下面这样。
[HttpGet]
public ActionResult ExportToExcel(string reportName)
{
byte [] reportDocument = RenderDataTableToExcel().ToArray();
Response.StatusCode = (int)HttpStatusCode.OK;
return File(reportDocument, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", reportName);
}
+0
我已经将contenttype设置为“application/vnd.ms-excel”,但我仍然得到二进制流。 –
答
我用过ajax TVT。
window.location.href = "?action=DownloadAll";
//$.ajax({
// url: "?action=DownloadAll",
// type: "get",
// success: function() { }
//});
答
你可以把一个DataTable到Excel工作表有一些非常可读的代码:
XLWorkbook wb = new XLWorkbook();
DataTable dt = GetDataTableOrWhatever();
wb.Worksheets.Add(dt,"WorksheetName");
它将运行速度超快呢!
so ..你的代码不工作,或者你有一个问题吗? – lordkain
@lordkain我的代码无效。如果你有代码的作品,我想知道它是如何工作:) –
youre使用经典的asp.net或MVC – lordkain