This project is read-only.

Saving large workbooks

Dec 21, 2010 at 4:25 PM

Is there another way to save the workbook besides using workbook.Write(Stream)? If the workbook is very large, having to duplicate it in a byte array to feed to the file stream runs the system out of memory. If we had direct access to the data being returned by GetBytes() we may be able write the file without running out of memory.

The current error being generated is:

Message: Exception of type 'System.OutOfMemoryException' was thrown.

Data: System.Collections.ListDictionaryInternal

TargetSite: Byte[] GetBytes()

HelpLink: NULL

Source: NPOI

STACK TRACE

at NPOI.HSSF.UserModel.HSSFWorkbook.GetBytes()

at NPOI.HSSF.UserModel.HSSFWorkbook.Write(Stream stream)

UtilityWorker.WriteToFile(HSSFWorkbook hssfWorkbook, String fileName)

 

Dec 21, 2010 at 4:57 PM

Ted,

Just by looking at your question i am not sure if you are implementing your stream correctly? Have you tried this?

MemoryStream file = new MemoryStream();

//........building workbook

hssfworkbook.Write(file);

Dec 21, 2010 at 11:07 PM

I am using the sample code proveded by NPOI

 private void WriteToFile(HSSFWorkbook hssfWorkbook, string fileName)
{
     if (File.Exists(fileName)) File.Delete(fileName);
    //Write the stream data of workbook to the root directory
     using (FileStream fileStream = new FileStream(fileName, FileMode.Create))
     {
           hssfWorkbook.Write(fileStream);
     }
}

Apr 19, 2013 at 9:54 PM
I have the same problem :(

MemoryStream WriteToStream()
{
    //Write the stream data of workbook to the root directory
    MemoryStream file = new MemoryStream();
    hssfworkbook.Write(file);
    return file;
}
generate the error in the line:
 hssfworkbook.Write(file);
constructs the workbook but in the moment of make the write... failure!!

no way I finished building the excel?
Coordinator
Oct 8, 2013 at 6:25 PM
TedHLewis wrote:
Is there another way to save the workbook besides using workbook.Write(Stream)? If the workbook is very large, having to duplicate it in a byte array to feed to the file stream runs the system out of memory. If we had direct access to the data being returned by GetBytes() we may be able write the file without running out of memory. The current error being generated is: Message: Exception of type 'System.OutOfMemoryException' was thrown. Data: System.Collections.ListDictionaryInternal TargetSite: Byte[] GetBytes() HelpLink: NULL Source: NPOI STACK TRACE at NPOI.HSSF.UserModel.HSSFWorkbook.GetBytes() at NPOI.HSSF.UserModel.HSSFWorkbook.Write(Stream stream) UtilityWorker.WriteToFile(HSSFWorkbook hssfWorkbook, String fileName)  
You are right. But this requires a big change to HSSF namespace. We will consider to fix it in the future.
Mar 24, 2014 at 9:17 AM
Is there a fix for this problem?
Coordinator
Mar 24, 2014 at 3:11 PM
We will try to fix it this year. I'm creating a company to support NPOI development.
Jul 1, 2014 at 11:12 AM
I also need this fix. I see it is implemented in the Java POI -
org.apache.poi.xssf.streaming.SXSSFSheet
org.apache.poi.xssf.streaming.SXSSFWorkbook

Then a large Xlsx file export can be performed in this Java code:

http://stackoverflow.com/questions/7274076/writing-a-large-resultset-to-an-excel-file-using-poi
Jan 13, 2016 at 1:54 PM
What is the status of streaming.SXSSF support? Are we able to use SAX way of reading/writing spreadsheets? If not, when it is planned to be implemented?

Thanks for the update in advance.
Apr 11, 2016 at 9:27 PM
I'm having a similar problem but on the reading side with large XSSF (*.xlsx) workbooks.

This runs out of memory on a 50 megabyte excel file I'm trying to read (uses over 1.7 gigs before running out of memory):
        FileStream fs = new FileStream("foo.xlsx", FileMode.Open);
        NPOI.SS.UserModel.IWorkbook wb = new NPOI.XSSF.UserModel.XSSFWorkbook(fs);
Note: this same file opens in Excel with only 150 megs of memory getting used.

PS - I'm also unable to find the XSSF package when I browse the on-line web interface for SOURCE code. Am I looking in the wrong online codebase?