Hi
We have a requirement to generate .xlsx file with more than 500000 rows and each row contains 35 to 40 columns in it . Since SXSSFWorkbook has the ability to do without large memory occupation we tried with following approach. SXSSFWorkbook workbook = new SXSSFWorkbook(xssfWorkbook, 100); Until 3 lacs record it is working fine but if we go beyond such limit based on server load some time we are experiencing the Out Of Memory error . Also even during the success scenario we feel like it is taking large time to generate the file . From another discussion here we understood like there is another approach like this ********************************** */ " 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. "/* ******************************** Is it recommended to go with above highlighted approach ? Please provide some suggestions here . Thanks In Advance ...! -- Sent from: http://apache-poi.1045710.n5.nabble.com/POI-User-f2280730.html --------------------------------------------------------------------- To unsubscribe, e-mail: [hidden email] For additional commands, e-mail: [hidden email] |
Hi,
for the out-of-memory, I'd run it on my/your local machine and launch a profiler, to find out where the memory is gone. I'm not too familiar with SXSSF, so take it with a grain of salt: I'd suspect two things: the SharedStringsTable or your binding code filling the SXSSF workbook. For the other approach of generating the sheet.xml data directly ... it's a valid approach and depending on your data, it might be implemented in few lines of code (50-100). You might need to change a range argument in the xmls, but that's easy to verify by taking a template .xlsx, adding a few rows in Excel and then unzip the before/after version and comparing their content. Do you know how to do the second approach yourself? (i.e. generate a text (.xml) file and import it into a .zip file) Andi -- Sent from: http://apache-poi.1045710.n5.nabble.com/POI-User-f2280730.html --------------------------------------------------------------------- To unsubscribe, e-mail: [hidden email] For additional commands, e-mail: [hidden email] |
Hi,
Thanks for the response. 1.I can create .xlsx template using POI 2.I can unzip the created template.xlsx file *** 3.generate a text (.xml) file and import it into a .zip file - i need to check on this . Because when I unzip Book1.xlsx file Book1\xl\sharedStrings.xml (has the text field informations) Book1\xl\worksheets\sheet1.xml (has the text field ref ID and numeric field values) It would be better if I get some concrete step to generate the file from text file -- Sent from: http://apache-poi.1045710.n5.nabble.com/POI-User-f2280730.html --------------------------------------------------------------------- To unsubscribe, e-mail: [hidden email] For additional commands, e-mail: [hidden email] |
In reply to this post by rdhanara
Can you share the code?
What is the time and what time do you expect? > Am 21.05.2019 um 12:58 schrieb rdhanara <[hidden email]>: > > Hi > We have a requirement to generate .xlsx file with more than 500000 rows > and each row contains 35 to 40 columns in it . > > Since SXSSFWorkbook has the ability to do without large memory occupation we > tried with following approach. > > SXSSFWorkbook workbook = new SXSSFWorkbook(xssfWorkbook, 100); > > > Until 3 lacs record it is working fine but if we go beyond such limit based > on server load some time we are experiencing the Out Of Memory error . > Also even during the success scenario we feel like it is taking large time > to generate the file . > > From another discussion here we understood like there is another approach > like this > ********************************** > */ > " > 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. "/* > > ******************************** > > Is it recommended to go with above highlighted approach ? > > Please provide some suggestions here . > > Thanks In Advance ...! > > > > -- > Sent from: http://apache-poi.1045710.n5.nabble.com/POI-User-f2280730.html > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [hidden email] > For additional commands, e-mail: [hidden email] > --------------------------------------------------------------------- To unsubscribe, e-mail: [hidden email] For additional commands, e-mail: [hidden email] |
Free forum by Nabble | Edit this page |