I am using NPOI 220.127.116.11 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
(Internally, the error is happening when the Write method calls the class's
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) 6.5 million. I'm not sure why such a small size would result in an
OutOfMemoryException seeing that there
are gigabytes worth of RAM sitting around on my dev box.
This error is noted in another discussion here - 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
Using exportData As New MemoryStream()
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader("Content-Disposition", "Attachment;Filename=" & saveAsName)