Export to excel throwing System.OutOfMemoryException

本文关键字：System OutOfMemoryException throwing excel to Export | 更新日期: 2023-09-27 18:33:03

尝试将数据表从内存流写入输出流时，网页在尝试向用户传递 excel 文件时会抛出 System.OutOfMemoryException。我正在使用封闭的XML将文件保存在Excel中，数据表大约有40K行和150列大，主要包含小数，导出的文件通常为10MB或更大。在导出到 excel 时破解大型数据集的建议技巧是什么？

这是我正在使用的封闭 XML 中的代码 http://closedxml.codeplex.com/wikipage?title=How%20do%20I%20deliver%20an%20Excel%20file%20in%20ASP.NET%3f&referringTitle=Documentation

            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;
            }

Export to excel throwing System.OutOfMemoryException

我偶然发现了这个链接 OpenXML 库（ClosedXML 的替代品） 当大量数据导出到 Excel 时，EPPlus https://epplus.codeplex.com/比 ClosedXML 工作得更好。至少没有更多的"内存外"异常，因为EPPlus似乎避开了内存流，尽管我仍然有兴趣知道他们是如何做到这一点的，甚至了解封闭XML和EPPlus之间的区别。

嗨，

您可以通过以下类型来避免内存溢出异常。

  1. 使用通货再膨胀方法，它将减少内存并获得良好的性能。
  2. 通过使用下面的代码片段仅将值加载到 Excel 工作表。

代码片段[C#]：

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;
                }
            }
相关文章：