This project is read-only.

out of memory problems

Mar 10, 2010 at 7:56 PM

I am trying to use 1.2.1 NPOI to export very large sets of data from Oracle to a 2003 xls spreadsheet file.

Approximately 490,000 rows are being exported.  An out of memory exception error occurs when the "hssfworkbook.Write(file)" method is called.  This method expects an input stream (file stream) and then is supposed to write the contents of the workbook object to the file system.

However, in my case, an out of memory exception error happens inside of this method call.

Is there any way to incrementally write the contents of the workbook to the file system (or some other way, ie. threading/chunking, etc.) to lower the amount of memory that is used?  I understand that this is not what NPOI was designed for, but I can't seem to find a good piece of software that creates Excel files without out of memory errors.  Even ExcelPackage (for 2007 file formats) fails at this too.

(BTW, I understand that the row limit prohibits more then 65536 rows in one sheet, so I have been putting the rows in more than one sheet (8 the last time I ran my code for 490,000 rows.  Also, creating 8 separate excel files is not an option due to management requirements.)

I have 4 Gigs of memory on a P4 xenon processor.  By no means a slow computer.

Help!

Thanks,

Ben

Mar 10, 2010 at 7:58 PM
I believe Excel has a limit of 64K rows and 64K columns.

On Wed, Mar 10, 2010 at 12:57 PM, njitben <notifications@codeplex.com> wrote:
> From: njitben
>
> I am trying to use 1.2.1 NPOI to export very large sets of data from Oracle
> to a 2003 xls spreadsheet file.
>
> Approximately 490,000 rows are being exported.  An out of memory exception
> error occurs when the "hssfworkbook.Write(file)" method is called.  This
> method expects an input stream (file stream) and then is supposed to write
> the contents of the workbook object to the file system.
>
> However, in my case, an out of memory exception error happens inside of this
> method call.
>
> Is there any way to incrementally write the contents of the workbook to the
> file system (or some other way, ie. threading/chunking, etc.) to lower the
> amount of memory that is used?  I understand that this is not what NPOI was
> designed for, but I can't seem to find a good piece of software that creates
> Excel files without out of memory errors.  Even ExcelPackage (for 2007 file
> formats) fails at this too.
>
> (BTW, I understand that the row limit prohibits more then 65536 rows in one
> sheet, so I have been putting the rows in more than one sheet (8 the last
> time I ran my code for 490,000 rows.  Also, creating 8 separate excel files
> is not an option due to management requirements.)
>
> I have 4 Gigs of memory on a P4 xenon processor.  By no means a slow
> computer.
>
> Help!
>
> Thanks,
>
> Ben
>
> Read the full discussion online.
>
> To add a post to this discussion, reply to this email
> ([email removed])
>
> To start a new discussion for this project, email
> [email removed]
>
> You are receiving this email because you subscribed to this discussion on
> CodePlex. You can unsubscribe or change your settings on codePlex.com.
>
> Please note: Images and attachments will be removed from emails. Any posts
> to this discussion will also be available online at codeplex.com



--
slide-o-blog
http://slide-o-blog.blogspot.com/
Mar 10, 2010 at 8:08 PM
@slide_o_mix:

See here for Excel 2003 format specifications:
http://office.microsoft.com/en-us/excel/hp051992911033.aspx

I have successfully used the NPOI API to create an excel file with 8
sheets, 450,000 rows of data total, with sample data. When trying to
use export using real data and slightly more rows (490,000 rows, and
more string data in the data since it is not sample data), this is
where I get the runtime error, out of memory.

I'm nowhere near the column limit, and the row limit is bypassed
because I only add 65536 rows to each sheet.
Mar 10, 2010 at 8:09 PM
Ah, ok, that makes sense then (multiple sheets). Can you run with a
profiler to see what objects are taking up heap space?

On Wed, Mar 10, 2010 at 1:08 PM, [email removed] wrote:
> From: njitben
>
> @slide_o_mix:
>
> See here for Excel 2003 format specifications:
> http://office.microsoft.com/en-us/excel/hp051992911033.aspx
>
> I have successfully used the NPOI API to create an excel file with 8
> sheets, 450,000 rows of data total, with sample data. When trying to
> use export using real data and slightly more rows (490,000 rows, and
> more string data in the data since it is not sample data), this is
> where I get the runtime error, out of memory.
>
> I'm nowhere near the column limit, and the row limit is bypassed
> because I only add 65536 rows to each sheet.
>
> Read the full discussion online.
>
> To add a post to this discussion, reply to this email
> ([email removed])
>
> To start a new discussion for this project, email
> [email removed]
>
> You are receiving this email because you subscribed to this discussion on
> CodePlex. You can unsubscribe or change your settings on codePlex.com.
>
> Please note: Images and attachments will be removed from emails. Any posts
> to this discussion will also be available online at codeplex.com



--
slide-o-blog
http://slide-o-blog.blogspot.com/
Mar 10, 2010 at 8:19 PM
@slide_o_mix:

Would you recommend a free profiler? I never used one before.

(In any case, the problem lies insides of the NPOI "Write(stream)"
method, which I already examined in the source code of NPOI. And, I
feel that I am not up to the challenge to rewrite the "magic" that is
done."
Mar 10, 2010 at 8:30 PM
Try this one: http://www.eqatec.com/tools/profiler

I haven't used it, but it IS free :-)

On Wed, Mar 10, 2010 at 1:19 PM, [email removed] wrote:
> From: njitben
>
> @slide_o_mix:
>
> Would you recommend a free profiler? I never used one before.
>
> (In any case, the problem lies insides of the NPOI "Write(stream)"
> method, which I already examined in the source code of NPOI. And, I
> feel that I am not up to the challenge to rewrite the "magic" that is
> done."
>
> Read the full discussion online.
>
> To add a post to this discussion, reply to this email
> ([email removed])
>
> To start a new discussion for this project, email
> [email removed]
>
> You are receiving this email because you subscribed to this discussion on
> CodePlex. You can unsubscribe or change your settings on codePlex.com.
>
> Please note: Images and attachments will be removed from emails. Any posts
> to this discussion will also be available online at codeplex.com



--
slide-o-blog
http://slide-o-blog.blogspot.com/
Aug 13, 2010 at 3:31 PM
Edited Aug 13, 2010 at 4:36 PM

Hi!

I have a similar problem. I'm trying to export excels (2003) using NPOI 1.2.1 with more than one sheet (when more than 65536 rows) on a web project. I'm doing this:

 

 

FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read);

HSSFWorkbook npoiWb = new HSSFWorkbook(fs, true);

HSSFSheet npoiSheet = npoiWb.GetSheetAt(iNumeroAbaAtual);

 

 

...and then I create my rows and cells (many cells by the way). After, I try to display the file to the user, that's when the error occur:

 

 

MemoryStream ms = new MemoryStream();

npoiWb.Write(ms); //the error occur here!!!
Then I get a System.OutOfMemoryException.
Can someone help me?
Thanks!

 

Aug 13, 2010 at 4:35 PM
Edited Aug 13, 2010 at 4:36 PM
I was not able to fix this error. The program ends up doing way to much at one time using the memorystream. This should be "chunked" so it writes some data to a file and then flushes its buffers and continues.

I ended up dropping NPOI as a solution and instead focusing on Excel2007ReadWrite found on CodeProject. http://www.codeproject.com/KB/office/OpenXML.aspx

This ended up working much better, but required more coding on my part to get it to work. Plus, it exports to the newest format of excel 2007 (xlsx) format, so rows of 65,000 or more is OK. I ended up using this in a production project, handling a full Bio export of a db of 490,000 rows, file size around 200 MB (using a console program kicked off by a windows service).

No other solution I researched could export that big a file without out of memory or "invalid pointer" errors, including Crystal Reports. This is using .net framework 2.0. I believe the codeproject download is C#, but I converted to use VB.net as well.

NPOI is great for lightweight stuff, and skipping writing to the file system (writing directly to memory stream and response stream for web apps). But, can't handle the VERY large datasets. The CodeProject one can handle huge files, the latest format, but will need to write to a temporary directory to create the individual xml files that make up an open xml office file, and then Zip them back up.
Aug 13, 2010 at 5:37 PM

You've sad that you were able to generate an Excel with NPOI with 450.000 rows. Do you have any code for that?

Aug 13, 2010 at 6:05 PM
Edited Sep 22, 2010 at 2:46 AM

I stopped using NPOI, and switched solutions to one I found on CodeProject. Can't supply code, its in-house.

Aug 13, 2010 at 6:12 PM

I know you quit NPOI, but on a previous post you have sad that. But, be cool, I'll figure out what to do here.

Thanks a lot!

Oct 19, 2011 at 12:55 PM

Hi,

 

i have the same problem. I fill the excel sheet based. I have many sheets, cause the 65k problem.  In my case i have 65k records

.Write(this._fs); // here comes the System.OutOfMemoryException

Here is my code

public override void AppdenDataTableToWorksheet(DataTable dt, string sheetName)
        { 
            // Getting the worksheet by its name...
            Sheet sheet = this._workBook.CreateSheet(
                sheetName + " (" + dt.Rows.Count + ")"
            );
            
            // handling header.
            Row headerRow = sheet.CreateRow(0);
            foreach (DataColumn column in dt.Columns)
            {
                headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
            }

            // handling value.
            int rowIndex = 1;

            foreach (DataRow row in dt.Rows)
            {
                Row dataRow = sheet.CreateRow(rowIndex);

                foreach (DataColumn column in dt.Columns)
                {
                    dataRow.CreateCell(column.Ordinal).SetCellValue(
                        row[column].ToString().Replace("'", "").Replace("\"", "").Replace("\\", "").Replace("\\0", "")
                    );
                }

                rowIndex++;
            }

            //
            this.OpenExcelFile();
            this._workBook.Write(this._fs);
            this.CloseExcelFile();
        }

Aug 9, 2012 at 6:51 AM
Edited Aug 9, 2012 at 6:56 AM

Is there's an update regarding this matter? I also am facing the same dilemma.

Thanks and regards,

JC

Oct 8, 2013 at 6:03 PM
I'm afraid that the main problem here is GetBytes() method called inside HSSFWorkbook.Write. It will generate a byte array which will cause OutOfMemory. But to fix this issue, it needs a lot of efforts. We need to change the serialize interface of Record class. This will be a big change. We will consider to fix it in the future.