生成大型 Excel 电子表格时出现 OutOfMemoryException
我在 ASP.NET 应用程序中使用 NPOI 1.2.3.0 将相当大的 SQL 查询的结果导出到 Excel 2003 XLS 文件。
简而言之，查询结果被填充到 ADO.NET DataTable 中。然后，我有一个例程循环遍历 DataTable 中的行，并为每一行向 NPOI 电子表格添加一行。它非常智能，一旦单张工作表的行数超过 65,000 行，就会创建一个新工作表，并从新工作表的第一行开始继续其中的行。
这种方法非常适合我的一些较小的数据库查询，例如 30,000 行和 50 列，但我有一个查询返回 125,000 行以北的行和大约 50 列，其中许多都有大量文本。
我能够毫无问题地构建电子表格，但是当我尝试将生成的电子表格流式传输到浏览器时，在调用 HSSFWorkbook 类的 Write 时，我收到 方法时会发生错误。）OutOfMemoryException方法。 （在内部，当 Write 方法调用类的 GetBytes
如果我运行调试器并在调用 Write 方法之前停止，我会看到工作簿的 Size 属性返回值（大约）6500万。
CodePlex 的 NPOI 项目中注意到了此错误 - 请参阅标题为 内存不足问题 的讨论 - 但是不幸的是，没有找到解决方案。
为了完整起见，下面是引发异常的代码（具体来说，它是在 workbook.Write 行上引发的）。
Using exportData As New MemoryStream()
workbook.Write(exportData)
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader("Content-Disposition", "Attachment;Filename=" & saveAsName)
Response.Clear()
Response.BinaryWrite(exportData.GetBuffer())
Response.End()
End Using
谢谢！
I am using NPOI 1.2.3.0 in an ASP.NET application to export the results of a rather large SQL query to an Excel 2003 XLS file.
In short, the query results are populated into an ADO.NET DataTable. I then have a routine that loops through the rows in the DataTable and for each row adds a row to an NPOI spreadsheet. It is intelligent enough that once 65,000 rows are exceeded for a single sheet, a new sheet is created and the rows are continued there, starting at the first row in the new sheet.
This approach works well for some of my smaller database queries that include, say, 30,000 rows and 50 columns, but I have this one query that returns north of 125,000 rows and has roughly 50 columns, many of which have a good deal of text.
I am able to construct the spreadsheet without issue, but when I try to stream the generated spreadsheet down to the browser I get an OutOfMemoryException when calling the HSSFWorkbook class's Write method. (Internally, the error is happening when the Write method calls the class's GetBytes method.)
If I run the debugger and stop before the Write method is called, I see that the workbook's Size property returns a value of (roughly) 65 million.
This error is noted on the NPOI project at CodePlex - see the discussion titled Out of Memory Problems - but no resolution was found, unfortunately.
For completeness, here is the code where the exception is raised (specifically, it's raised on the workbook.Write line).
Using exportData As New MemoryStream()
workbook.Write(exportData)
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader("Content-Disposition", "Attachment;Filename=" & saveAsName)
Response.Clear()
Response.BinaryWrite(exportData.GetBuffer())
Response.End()
End Using
Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
在这种情况下，我会做的事情是，记住 FileStream 对象不会导致错误，并且该错误是由 32 位中的 512MB 容量限制和 64 位中的 2GB 限制引起的，尝试将文件写入 memoryStream，catch错误并在遇到错误时恢复到较大文件的 FileStream。
这里有一个明显的性能权衡，但是如果您的用户正在下载> 2GB 文件，他们可能会期望这会慢一点:-)
我有兴趣知道这是否适合您。
谢谢，
戴夫
What I would do in this scenario, bearing in mind the FileStream object does not cause errors and that the error is caused by the 512MB capacity limitation in 32 bit and 2GB limitation in 64 bit, is try and write the file to the memoryStream, catch the error and revert to the FileStream for the larger files if the error is encountered.
there's an obvious performance trade off here, but if your users are downloading > 2GB files they should probably expect this to be a bit slower :-)
Would be interested to know if this works for you.
thanks,
Dave
NPOI不仅使用MemoryStream，还使用字节数组。主要根本原因是字节数组。但到目前为止，NPOI 必须使用字节数组。目前还没有计划改变这一点。对于造成的任何不便，我们深表歉意。
NPOI doesn't only use MemoryStream but also byte array. The main root cause is the byte array. But NPOI have to use byte array so far. There is no plan to change this yet. Sorry for any inconvenience caused.