You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by "Borys, John" <Jo...@cmegroup.com> on 2008/12/02 16:54:28 UTC
Large Reports
I have been tasked with generating Enterprise Reports and writing them to Excel Spreadsheets. When using POI, the program crashes after about 30,000 records are processed. Our system requires millions of records to be processed. POI's limit is 65K rows and some change. Excel 2007 is now capable of processing over a million rows of data. Is there a tool available either open source or for purchase that can handle writing large quantities of data (over 1 million rows) to an Excel spreadsheet? This data will in turn be used to populate pivot tables or be merged with an Excel Template.
John K. Borys
Projects & Financial Controls
Desk: 312.930.3134
John.Borys@cmegroup.com<ma...@cmegroup.com>
CME Group
A CME/Chicago Board of Trade Company
20 S. Wacker
Chicago, Illinois 60606
http://www.cmegroup.com/
RE: Large Reports
Posted by "Borys, John" <Jo...@cmegroup.com>.
I am using .xlsx and XSSF with POI 3.5.
What should I increase the heap size to? I have tried using 1024MB for my heap max but I'm not sure how much higher I can go. And will it make a difference? I am running out of memory after 30,000 rows and I need to process over 500,000. Is there any way to flush the memory or write to a buffer? I read somewhere that you guys were going to create a separate project to handle enterprise size reporting. Was that Jasper reports? Although I downloaded iReports and set up a Jasper Report and it ran out of memory too.
John K. Borys
Projects & Financial Controls
Desk: 312.930.3134
John.Borys@cmegroup.com
CME Group
A CME/Chicago Board of Trade Company
20 S. Wacker
Chicago, Illinois 60606
http://www.cmegroup.com/
-----Original Message-----
From: Nick Burch [mailto:nick@torchbox.com]
Sent: Tuesday, December 02, 2008 10:26 AM
To: POI Users List
Subject: Re: Large Reports
On Tue, 2 Dec 2008, Borys, John wrote:
> I have been tasked with generating Enterprise Reports and writing them
> to Excel Spreadsheets. When using POI, the program crashes after about
> 30,000 records are processed.
Try increasing your JVM heap size - the excel file format is very memory
hungry to process
> Excel 2007 is now capable of processing over a million rows of data.
Only in .xlsx files though, .xls retains its 65k limit. XSSF (part of poi
3.5) will let you write .xslx files without the row limits
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Large Reports
Posted by "Dobson, Paul L CTR USAF AFMC 416 SCMS/OBN" <Pa...@HILL.af.mil>.
-----Original Message-----
From: Nick Burch [mailto:nick@torchbox.com]
Sent: Tuesday, December 02, 2008 9:26 AM
To: POI Users List
Subject: Re: Large Reports
On Tue, 2 Dec 2008, Borys, John wrote:
> I have been tasked with generating Enterprise Reports and writing them
> to Excel Spreadsheets. When using POI, the program crashes after
about
> 30,000 records are processed.
Try increasing your JVM heap size - the excel file format is very memory
hungry to process
> Excel 2007 is now capable of processing over a million rows of data.
Only in .xlsx files though, .xls retains its 65k limit. XSSF (part of
poi
3.5) will let you write .xslx files without the row limits
Nick
My personal experience is that although the 3.5 may have no physical
limit, storing the data structure in .xlsx is far less efficient than it
is in the .xls format. I have found that I can only create documents
about 1/4 the size in .xlsx as I could in .xls before I run out of
memory. Does this seem to match anyone else's findings?
If you are dealing with millions of rows, I don't think you can get
enough memory to make it work. I am hoping that someday POI will be
enhanced to buffer to a file so everything isn't always in memory at the
same time.
Paul
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Large Reports
Posted by Nick Burch <ni...@torchbox.com>.
On Tue, 2 Dec 2008, Borys, John wrote:
> I have been tasked with generating Enterprise Reports and writing them
> to Excel Spreadsheets. When using POI, the program crashes after about
> 30,000 records are processed.
Try increasing your JVM heap size - the excel file format is very memory
hungry to process
> Excel 2007 is now capable of processing over a million rows of data.
Only in .xlsx files though, .xls retains its 65k limit. XSSF (part of poi
3.5) will let you write .xslx files without the row limits
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Large Reports
Posted by "Dobson, Paul L CTR USAF AFMC 416 SCMS/OBN" <Pa...@HILL.af.mil>.
Yes. Excellent response. Thank you. With all the activity on the user
list related to large excel files and memory, I wonder if adding some
framework to streamline what you described would be a tremendous
enhancement to POI.
Paul
-----Original Message-----
From: Borys, John [mailto:John.Borys@cmegroup.com]
Sent: Tuesday, December 02, 2008 10:00 AM
To: POI Users List
Subject: RE: Large Reports
Yegor,
Thank you for your excellent response. I figured I would have to resort
to something like this.
You mentioned I would need to "create a template file using poi-ooxml".
How do I do this? Are there any documentation or tutorials on the
subject?
John K. Borys
Projects & Financial Controls
Desk: 312.930.3134
John.Borys@cmegroup.com
CME Group
A CME/Chicago Board of Trade Company
20 S. Wacker
Chicago, Illinois 60606
http://www.cmegroup.com/
-----Original Message-----
From: Yegor Kozlov [mailto:yegor@dinom.ru]
Sent: Tuesday, December 02, 2008 10:54 AM
To: POI Users List
Subject: Re: Large Reports
Unfortunately poi-ooxml has quite a good appetite for memory, under same
conditions you will get OutOfMemory on less
number of rows, I would say less in 2x. Increasing JVML heap will help
till a certain limit, if you allocate 2 GB (the
limit for 32-bit JVM), you will be able to generate 100K but not 1
million of rows.
The memory requirement depends of the row-cell grid density. Sparse rows
require less memory then rows with every cell set.
If you need to generate such large worksheets, I would recommend direct
streaming in XML.
The approach would be to create a template file using poi-ooxml, Setup
sheets, number formats, cell styles, etc.
Then write a custom application that streams data in a text file. You
don't need a deep knowledge of SpreadsheetML
format for that, just follow the pattern in the template. The final step
would be to inject this file in the template.
It's not very trivial but should be possible.
Regards,
Yegor
> I have been tasked with generating Enterprise Reports and writing them
to Excel Spreadsheets. When using POI, the program crashes after about
30,000 records are processed. Our system requires millions of records
to be processed. POI's limit is 65K rows and some change. Excel 2007
is now capable of processing over a million rows of data. Is there a
tool available either open source or for purchase that can handle
writing large quantities of data (over 1 million rows) to an Excel
spreadsheet? This data will in turn be used to populate pivot tables or
be merged with an Excel Template.
>
> John K. Borys
> Projects & Financial Controls
> Desk: 312.930.3134
> John.Borys@cmegroup.com<ma...@cmegroup.com>
>
> CME Group
> A CME/Chicago Board of Trade Company
> 20 S. Wacker
> Chicago, Illinois 60606
> http://www.cmegroup.com/
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Large Reports
Posted by Yegor Kozlov <ye...@dinom.ru>.
> You mentioned I would need to "create a template file using poi-ooxml".
>
> How do I do this? Are there any documentation or tutorials on the subject?
The home page of POI-Excel API is http://poi.apache.org/spreadsheet/index.html
I need to play with code to make sure that my approach works. I will let you know in a few days.
Yegor
> Yegor,
>
> Thank you for your excellent response. I figured I would have to resort to something like this.
>
>
> John K. Borys
> Projects & Financial Controls
> Desk: 312.930.3134
> John.Borys@cmegroup.com
>
> CME Group
> A CME/Chicago Board of Trade Company
> 20 S. Wacker
> Chicago, Illinois 60606
> http://www.cmegroup.com/
>
>
> -----Original Message-----
> From: Yegor Kozlov [mailto:yegor@dinom.ru]
> Sent: Tuesday, December 02, 2008 10:54 AM
> To: POI Users List
> Subject: Re: Large Reports
>
> Unfortunately poi-ooxml has quite a good appetite for memory, under same conditions you will get OutOfMemory on less
> number of rows, I would say less in 2x. Increasing JVML heap will help till a certain limit, if you allocate 2 GB (the
> limit for 32-bit JVM), you will be able to generate 100K but not 1 million of rows.
>
> The memory requirement depends of the row-cell grid density. Sparse rows require less memory then rows with every cell set.
>
> If you need to generate such large worksheets, I would recommend direct streaming in XML.
>
> The approach would be to create a template file using poi-ooxml, Setup sheets, number formats, cell styles, etc.
> Then write a custom application that streams data in a text file. You don't need a deep knowledge of SpreadsheetML
> format for that, just follow the pattern in the template. The final step would be to inject this file in the template.
>
> It's not very trivial but should be possible.
>
> Regards,
> Yegor
>
>> I have been tasked with generating Enterprise Reports and writing them to Excel Spreadsheets. When using POI, the program crashes after about 30,000 records are processed. Our system requires millions of records to be processed. POI's limit is 65K rows and some change. Excel 2007 is now capable of processing over a million rows of data. Is there a tool available either open source or for purchase that can handle writing large quantities of data (over 1 million rows) to an Excel spreadsheet? This data will in turn be used to populate pivot tables or be merged with an Excel Template.
>>
>> John K. Borys
>> Projects & Financial Controls
>> Desk: 312.930.3134
>> John.Borys@cmegroup.com<ma...@cmegroup.com>
>>
>> CME Group
>> A CME/Chicago Board of Trade Company
>> 20 S. Wacker
>> Chicago, Illinois 60606
>> http://www.cmegroup.com/
>>
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Large Reports
Posted by Mark Barton2 <ma...@redwood.com>.
A similar question:
I want to simply extract the text from a .xlsx file. I'm reading from a
rather sparse xlsx file. It is a 20 meg file, using a JVM with 990 megs,
with POIXMLTextExtractor. I get Out of Memory. What do you recommend?
Thanks,
Mark Barton
John Borys wrote:
>
> Thank you. This is a tremendous help. Your time is greatly appreciated.
>
> John K. Borys
> Projects & Financial Controls
> Desk: 312.930.3134
> John.Borys@cmegroup.com
>
> CME Group
> A CME/Chicago Board of Trade Company
> 20 S. Wacker
> Chicago, Illinois 60606
> http://www.cmegroup.com/
>
> -----Original Message-----
> From: Yegor Kozlov [mailto:yegor@dinom.ru]
> Sent: Sunday, December 07, 2008 8:24 AM
> To: POI Users List
> Subject: Re: Large Reports
>
> I created an example demonstrating how to generate large workbooks and
> avoid OutOfMemory:
> http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/xssf/usermodel/examples/BigGridDemo.java
>
> It works as I suggested:
> 1. creates a template workbook
> 2. generates a sample XML with random data. It can be a really large XML
> with millions of rows and thousands of columns.
> 3. substitutes the sheet in the template with the generated xml
>
> It's not a ready-to-use API, rather a proof of the concept. To use more
> advanced features (merged cells, custom height
> or width of rows and cells, conditional formats, etc.) you will need to
> study the SpreadsheetML documentation and
> enhance the demo.
>
> Yegor
>
>> Yegor,
>>
>> Thank you for your excellent response. I figured I would have to resort
>> to something like this.
>>
>> You mentioned I would need to "create a template file using poi-ooxml".
>>
>> How do I do this? Are there any documentation or tutorials on the
>> subject?
>>
>> John K. Borys
>> Projects & Financial Controls
>> Desk: 312.930.3134
>> John.Borys@cmegroup.com
>>
>> CME Group
>> A CME/Chicago Board of Trade Company
>> 20 S. Wacker
>> Chicago, Illinois 60606
>> http://www.cmegroup.com/
>>
>>
>> -----Original Message-----
>> From: Yegor Kozlov [mailto:yegor@dinom.ru]
>> Sent: Tuesday, December 02, 2008 10:54 AM
>> To: POI Users List
>> Subject: Re: Large Reports
>>
>> Unfortunately poi-ooxml has quite a good appetite for memory, under same
>> conditions you will get OutOfMemory on less
>> number of rows, I would say less in 2x. Increasing JVML heap will help
>> till a certain limit, if you allocate 2 GB (the
>> limit for 32-bit JVM), you will be able to generate 100K but not 1
>> million of rows.
>>
>> The memory requirement depends of the row-cell grid density. Sparse rows
>> require less memory then rows with every cell set.
>>
>> If you need to generate such large worksheets, I would recommend direct
>> streaming in XML.
>>
>> The approach would be to create a template file using poi-ooxml, Setup
>> sheets, number formats, cell styles, etc.
>> Then write a custom application that streams data in a text file. You
>> don't need a deep knowledge of SpreadsheetML
>> format for that, just follow the pattern in the template. The final step
>> would be to inject this file in the template.
>>
>> It's not very trivial but should be possible.
>>
>> Regards,
>> Yegor
>>
>>> I have been tasked with generating Enterprise Reports and writing them
>>> to Excel Spreadsheets. When using POI, the program crashes after about
>>> 30,000 records are processed. Our system requires millions of records
>>> to be processed. POI's limit is 65K rows and some change. Excel 2007
>>> is now capable of processing over a million rows of data. Is there a
>>> tool available either open source or for purchase that can handle
>>> writing large quantities of data (over 1 million rows) to an Excel
>>> spreadsheet? This data will in turn be used to populate pivot tables or
>>> be merged with an Excel Template.
>>>
>>> John K. Borys
>>> Projects & Financial Controls
>>> Desk: 312.930.3134
>>> John.Borys@cmegroup.com<ma...@cmegroup.com>
>>>
>>> CME Group
>>> A CME/Chicago Board of Trade Company
>>> 20 S. Wacker
>>> Chicago, Illinois 60606
>>> http://www.cmegroup.com/
>>>
>>>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
--
View this message in context: http://www.nabble.com/Large-Reports-tp20795592p23197679.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Large Reports
Posted by "Borys, John" <Jo...@cmegroup.com>.
Thank you. This is a tremendous help. Your time is greatly appreciated.
John K. Borys
Projects & Financial Controls
Desk: 312.930.3134
John.Borys@cmegroup.com
CME Group
A CME/Chicago Board of Trade Company
20 S. Wacker
Chicago, Illinois 60606
http://www.cmegroup.com/
-----Original Message-----
From: Yegor Kozlov [mailto:yegor@dinom.ru]
Sent: Sunday, December 07, 2008 8:24 AM
To: POI Users List
Subject: Re: Large Reports
I created an example demonstrating how to generate large workbooks and avoid OutOfMemory:
http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/xssf/usermodel/examples/BigGridDemo.java
It works as I suggested:
1. creates a template workbook
2. generates a sample XML with random data. It can be a really large XML with millions of rows and thousands of columns.
3. substitutes the sheet in the template with the generated xml
It's not a ready-to-use API, rather a proof of the concept. To use more advanced features (merged cells, custom height
or width of rows and cells, conditional formats, etc.) you will need to study the SpreadsheetML documentation and
enhance the demo.
Yegor
> Yegor,
>
> Thank you for your excellent response. I figured I would have to resort to something like this.
>
> You mentioned I would need to "create a template file using poi-ooxml".
>
> How do I do this? Are there any documentation or tutorials on the subject?
>
> John K. Borys
> Projects & Financial Controls
> Desk: 312.930.3134
> John.Borys@cmegroup.com
>
> CME Group
> A CME/Chicago Board of Trade Company
> 20 S. Wacker
> Chicago, Illinois 60606
> http://www.cmegroup.com/
>
>
> -----Original Message-----
> From: Yegor Kozlov [mailto:yegor@dinom.ru]
> Sent: Tuesday, December 02, 2008 10:54 AM
> To: POI Users List
> Subject: Re: Large Reports
>
> Unfortunately poi-ooxml has quite a good appetite for memory, under same conditions you will get OutOfMemory on less
> number of rows, I would say less in 2x. Increasing JVML heap will help till a certain limit, if you allocate 2 GB (the
> limit for 32-bit JVM), you will be able to generate 100K but not 1 million of rows.
>
> The memory requirement depends of the row-cell grid density. Sparse rows require less memory then rows with every cell set.
>
> If you need to generate such large worksheets, I would recommend direct streaming in XML.
>
> The approach would be to create a template file using poi-ooxml, Setup sheets, number formats, cell styles, etc.
> Then write a custom application that streams data in a text file. You don't need a deep knowledge of SpreadsheetML
> format for that, just follow the pattern in the template. The final step would be to inject this file in the template.
>
> It's not very trivial but should be possible.
>
> Regards,
> Yegor
>
>> I have been tasked with generating Enterprise Reports and writing them to Excel Spreadsheets. When using POI, the program crashes after about 30,000 records are processed. Our system requires millions of records to be processed. POI's limit is 65K rows and some change. Excel 2007 is now capable of processing over a million rows of data. Is there a tool available either open source or for purchase that can handle writing large quantities of data (over 1 million rows) to an Excel spreadsheet? This data will in turn be used to populate pivot tables or be merged with an Excel Template.
>>
>> John K. Borys
>> Projects & Financial Controls
>> Desk: 312.930.3134
>> John.Borys@cmegroup.com<ma...@cmegroup.com>
>>
>> CME Group
>> A CME/Chicago Board of Trade Company
>> 20 S. Wacker
>> Chicago, Illinois 60606
>> http://www.cmegroup.com/
>>
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Large Reports
Posted by Yegor Kozlov <ye...@dinom.ru>.
I created an example demonstrating how to generate large workbooks and avoid OutOfMemory:
http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/xssf/usermodel/examples/BigGridDemo.java
It works as I suggested:
1. creates a template workbook
2. generates a sample XML with random data. It can be a really large XML with millions of rows and thousands of columns.
3. substitutes the sheet in the template with the generated xml
It's not a ready-to-use API, rather a proof of the concept. To use more advanced features (merged cells, custom height
or width of rows and cells, conditional formats, etc.) you will need to study the SpreadsheetML documentation and
enhance the demo.
Yegor
> Yegor,
>
> Thank you for your excellent response. I figured I would have to resort to something like this.
>
> You mentioned I would need to "create a template file using poi-ooxml".
>
> How do I do this? Are there any documentation or tutorials on the subject?
>
> John K. Borys
> Projects & Financial Controls
> Desk: 312.930.3134
> John.Borys@cmegroup.com
>
> CME Group
> A CME/Chicago Board of Trade Company
> 20 S. Wacker
> Chicago, Illinois 60606
> http://www.cmegroup.com/
>
>
> -----Original Message-----
> From: Yegor Kozlov [mailto:yegor@dinom.ru]
> Sent: Tuesday, December 02, 2008 10:54 AM
> To: POI Users List
> Subject: Re: Large Reports
>
> Unfortunately poi-ooxml has quite a good appetite for memory, under same conditions you will get OutOfMemory on less
> number of rows, I would say less in 2x. Increasing JVML heap will help till a certain limit, if you allocate 2 GB (the
> limit for 32-bit JVM), you will be able to generate 100K but not 1 million of rows.
>
> The memory requirement depends of the row-cell grid density. Sparse rows require less memory then rows with every cell set.
>
> If you need to generate such large worksheets, I would recommend direct streaming in XML.
>
> The approach would be to create a template file using poi-ooxml, Setup sheets, number formats, cell styles, etc.
> Then write a custom application that streams data in a text file. You don't need a deep knowledge of SpreadsheetML
> format for that, just follow the pattern in the template. The final step would be to inject this file in the template.
>
> It's not very trivial but should be possible.
>
> Regards,
> Yegor
>
>> I have been tasked with generating Enterprise Reports and writing them to Excel Spreadsheets. When using POI, the program crashes after about 30,000 records are processed. Our system requires millions of records to be processed. POI's limit is 65K rows and some change. Excel 2007 is now capable of processing over a million rows of data. Is there a tool available either open source or for purchase that can handle writing large quantities of data (over 1 million rows) to an Excel spreadsheet? This data will in turn be used to populate pivot tables or be merged with an Excel Template.
>>
>> John K. Borys
>> Projects & Financial Controls
>> Desk: 312.930.3134
>> John.Borys@cmegroup.com<ma...@cmegroup.com>
>>
>> CME Group
>> A CME/Chicago Board of Trade Company
>> 20 S. Wacker
>> Chicago, Illinois 60606
>> http://www.cmegroup.com/
>>
>>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Large Reports
Posted by "Borys, John" <Jo...@cmegroup.com>.
Yegor,
Thank you for your excellent response. I figured I would have to resort to something like this.
You mentioned I would need to "create a template file using poi-ooxml".
How do I do this? Are there any documentation or tutorials on the subject?
John K. Borys
Projects & Financial Controls
Desk: 312.930.3134
John.Borys@cmegroup.com
CME Group
A CME/Chicago Board of Trade Company
20 S. Wacker
Chicago, Illinois 60606
http://www.cmegroup.com/
-----Original Message-----
From: Yegor Kozlov [mailto:yegor@dinom.ru]
Sent: Tuesday, December 02, 2008 10:54 AM
To: POI Users List
Subject: Re: Large Reports
Unfortunately poi-ooxml has quite a good appetite for memory, under same conditions you will get OutOfMemory on less
number of rows, I would say less in 2x. Increasing JVML heap will help till a certain limit, if you allocate 2 GB (the
limit for 32-bit JVM), you will be able to generate 100K but not 1 million of rows.
The memory requirement depends of the row-cell grid density. Sparse rows require less memory then rows with every cell set.
If you need to generate such large worksheets, I would recommend direct streaming in XML.
The approach would be to create a template file using poi-ooxml, Setup sheets, number formats, cell styles, etc.
Then write a custom application that streams data in a text file. You don't need a deep knowledge of SpreadsheetML
format for that, just follow the pattern in the template. The final step would be to inject this file in the template.
It's not very trivial but should be possible.
Regards,
Yegor
> I have been tasked with generating Enterprise Reports and writing them to Excel Spreadsheets. When using POI, the program crashes after about 30,000 records are processed. Our system requires millions of records to be processed. POI's limit is 65K rows and some change. Excel 2007 is now capable of processing over a million rows of data. Is there a tool available either open source or for purchase that can handle writing large quantities of data (over 1 million rows) to an Excel spreadsheet? This data will in turn be used to populate pivot tables or be merged with an Excel Template.
>
> John K. Borys
> Projects & Financial Controls
> Desk: 312.930.3134
> John.Borys@cmegroup.com<ma...@cmegroup.com>
>
> CME Group
> A CME/Chicago Board of Trade Company
> 20 S. Wacker
> Chicago, Illinois 60606
> http://www.cmegroup.com/
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Large Reports
Posted by Yegor Kozlov <ye...@dinom.ru>.
Unfortunately poi-ooxml has quite a good appetite for memory, under same conditions you will get OutOfMemory on less
number of rows, I would say less in 2x. Increasing JVML heap will help till a certain limit, if you allocate 2 GB (the
limit for 32-bit JVM), you will be able to generate 100K but not 1 million of rows.
The memory requirement depends of the row-cell grid density. Sparse rows require less memory then rows with every cell set.
If you need to generate such large worksheets, I would recommend direct streaming in XML.
The approach would be to create a template file using poi-ooxml, Setup sheets, number formats, cell styles, etc.
Then write a custom application that streams data in a text file. You don't need a deep knowledge of SpreadsheetML
format for that, just follow the pattern in the template. The final step would be to inject this file in the template.
It's not very trivial but should be possible.
Regards,
Yegor
> I have been tasked with generating Enterprise Reports and writing them to Excel Spreadsheets. When using POI, the program crashes after about 30,000 records are processed. Our system requires millions of records to be processed. POI's limit is 65K rows and some change. Excel 2007 is now capable of processing over a million rows of data. Is there a tool available either open source or for purchase that can handle writing large quantities of data (over 1 million rows) to an Excel spreadsheet? This data will in turn be used to populate pivot tables or be merged with an Excel Template.
>
> John K. Borys
> Projects & Financial Controls
> Desk: 312.930.3134
> John.Borys@cmegroup.com<ma...@cmegroup.com>
>
> CME Group
> A CME/Chicago Board of Trade Company
> 20 S. Wacker
> Chicago, Illinois 60606
> http://www.cmegroup.com/
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org