c# - 导出到 excel 抛出 System.OutOfMemoryException

Question

网页在尝试将数据表从内存流写入输出流时抛出 System.OutOfMemoryException，同时尝试将 excel 文件传递​​给用户。我使用 Closed 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;
            }

Answer 1

我偶然发现了这个链接 OpenXML libraries (alternatives to ClosedXML)和 EPPlus https://epplus.codeplex.com/当大量数据被导出到 Excel 时，它比 ClosedXML 工作得更好。至少没有更多的“OutOfMemory”异常，因为 EPPlus 似乎避开了内存流，尽管我仍然有兴趣知道他们是如何实现这一点的，甚至想了解 Closed XML 和 EPPlus 之间的区别。