导出到excel抛出System.OutOfMemoryException
导出到excel抛出System.OutOfMemoryException
提问于 2013-09-28 10:06:30
当网页试图将数据表从内存流写入输出流时，在试图将excel文件传递给用户时抛出System.OutOfMemoryException。我使用封闭的XML在Excel中保存文件，数据表大约有40K行和150列大小，大部分是小数，导出的文件通常是10MB或更大。在导出到excel时，有哪些建议的技巧可以绕过大型数据集？
这是我正在使用的http://closedxml.codeplex.com/wikipage?title=How%20do%20I%20deliver%20an%20Excel%20file%20in%20ASP.NET%3f&referringTitle=Documentation中的闭合XML代码
public HttpResponseMessage Get()
{
// Create the workbook
var workbook = new XLWorkbook();
Datatable dt = getDataTable();
workbook.Worksheets.Add(dt);
// Prepare the response
HttpResponseMessage response = new HttpResponseMessage(HttpStatusCode.OK);
var memoryStream = new MemoryStream(); // If I put this in a 'using' construct, I never get the response back in a browser.
workbook.SaveAs(memoryStream);
memoryStream.Seek(0, SeekOrigin.Begin); // Seem to have to manually rewind stream before applying it to the content.
response.Content = new StreamContent(memoryStream);
response.Content.Headers.ContentType = new MediaTypeHeaderValue("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment") { FileName = "HelloWorld.xlsx" };
return response;
}回答 2
Stack Overflow用户
发布于 2013-10-01 00:51:51
我偶然发现了这个链接，当大量数据被导出到OpenXML libraries (alternatives to ClosedXML)时，EPPlus https://epplus.codeplex.com/比ClosedXML工作得更好。至少不会再有"OutOfMemory“异常，因为EPPlus似乎避开了内存流，尽管我仍然有兴趣知道他们是如何做到这一点的，甚至是了解关闭的XML和EPPlus之间的区别。
Stack Overflow用户
发布于 2013-09-28 10:49:10
您好，您可以通过遵循以下类型来避免内存溢出异常。
- 使用reflation的方法，通过使用下面的代码片段，可以减少内存，只将值performance.
- load到excel表格中。
代码snippetC#
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Create(1); //We are using single workbook
IWorksheet sheet = workbook.Worksheets[0]; //In this case we are exporting to single ExcelSheet so we marked Worksheets as 0
for (int i = 0; i < grid.Model.RowCount; i++)
{
//Setting Excel cell height based on Grid Cell height
sheet.SetRowHeightInPixels(i + 1, set heigth here);
for (int j = 0; j < ColumnCount; j++)
{
int width = Convert.ToInt32(ColumnWidths[j]); //Getting Grid Cell column width
sheet.SetColumnWidthInPixels(j + 1, width); //Setting Width for Excel cell
sheet.Range[i + 1, j + 1].Text = dt value here;
}
}页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/19062445
复制相关文章
相似问题
腾讯云开发者
