Large Data Excel generation

[rdhanara]

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]

[kiwiwings]

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]

[rdhanara]

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]

[Jörn Franke]

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]