OutOfMemoryException when creating a large Excel table

I am using NPOI 1.2.3.0 in an ASP.NET application to export the results of a fairly large SQL query to an Excel Excel XLS file.

In short, query results are populated in an ADO.NET DataTable. Then I have a procedure that traverses rows in a DataTable, and for each row, a row is added to the NPOI spreadsheet. It is reasonably enough that after 65,000 rows are exceeded in one sheet, a new sheet is created, and the lines continue there, starting from the first line on a new sheet.

This approach works well for some of my small database queries that include, say, 30,000 rows and 50 columns, but I have this one query that returns north from 125,000 rows and has approximately 50 columns, many of which have good deal text.

I can create the table without problems, but when I try to transfer the generated table to the browser, I get it OutOfMemoryExceptionwhen the HSSFWorkbookclass method is called Write. (An internal error occurs when the Write method calls a class method GetBytes.)

If I run the debugger and stopped before calling the Write method, I see that the book-size property returns a value of (approximately) 65 million.

This error was noted in the NPOI project in CodePlex - see the discussion entitled Memory Problems - but, unfortunately, no solution was found.

, ( , 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

!