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
[EMAIL PROTECTED]
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:[EMAIL PROTECTED]
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
[EMAIL PROTECTED]<mailto:[EMAIL PROTECTED]>
CME Group
A CME/Chicago Board of Trade Company
20 S. Wacker
Chicago, Illinois 60606
http://www.cmegroup.com/
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
