Hi Anthony, Thanks for your pointer. I've managed to create Excel file with around 30K records with 1.5GB heap. At least it works for now.
Cheers, Winarto -----Original Message----- From: Anthony Andrews [mailto:[email protected]] Sent: Saturday, 10 January 2009 00:07 To: POI Users List Subject: RE: Write a (very) huge Excel file. Just had a quick search myself and found the response from Yegor that I was looking for. It will not work for you however as I think he is talking about creating large xml files. Have pasted his reply below; 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 --- On Fri, 1/9/09, Winarto <[email protected]> wrote: From: Winarto <[email protected]> Subject: RE: Write a (very) huge Excel file. To: "POI Users List" <[email protected]> Date: Friday, January 9, 2009, 7:28 AM Hi Karsten, I've managed to reduce the number of objects that I'm creating (i.e. reuse the existing object instead of using 'new' keyword), and it has help to parse more records than before. However outofmemoryerror is now always pointing to the creation of POI object, which I don't have much control. Just for comparison, my XMLSS test file has about 2.5 million lines, and I'm expecting around 6 million lines in production. Hi Anthony, Thanks for your pointer, I'll try to search the archive. Cheers, Winarto ________________________________ From: [email protected] [mailto:[email protected]] Sent: Fri 09-Jan-09 19:13 To: [email protected] Subject: RE: Write a (very) huge Excel file. Hi Winarto, sorry for not seeing that you configured your JVM. I had ASCII-files with about 170.000 lines, which I transferd in an Excel spreadsheet (the *.xls was about 50MB). This worked fine with POI 3.1. Ok your files are about 4 times bigger, but it should work out too. Best regards... Karsten --------------------------------------------------------------------- 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]
