SXSSF cannot work with large excel files

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

SXSSF cannot work with large excel files

Shevchuk Vasyl
Hello Dev team,

According to your limitations notes (
http://poi.apache.org/components/spreadsheet/limitations.html) I should use
SXSSFWorkbook <http://poi.apache.org/components/spreadsheet/> to save big
amount of data to avoid huge memory usage.
My current task is to load big excel file (with at least 2 big worksheets)
and add new rows to one of those big sheet. I can read data from big
worksheet using the same limitaions page notes (using OPCPackage and
XSSFReader) but I cannot add new rows to those worksheets.

I cannot find any way to use XSSFWorkbook no SXSSFWorkbook because of first
try to load all data from excel file in constructor (this cause of
OutOfMemory exception on my environment) and second one can only use first
one as an constructor parameter, it cannot take excel file name and try to
load it using rowAccessWindowSize.

Because of this I have 2 questions.
1. Is SXSSFWorkbook class usable at all in it's current implementation? As
for me most of developers need to update existing excel sheets instead of
creating new. Maybe it's time to add some possibility to load big files
using the same rowAccessWindowSize idea?
2. Could you please provide me some workaround/proposal to add new rows to
big excel file sheet without OutOfMemoryException?


Best regards,
Senior Software Developer,
Skype: vasylshevchuk
Cell: +380661044415
Vasyl Shevchuk
Reply | Threaded
Open this post in threaded view
|

Re: SXSSF cannot work with large excel files

pj.fanning
For reading large documents use
https://github.com/monitorjbl/excel-streaming-reader or
https://github.com/apache/poi/blob/trunk/src/examples/src/org/apache/poi/xssf/eventusermodel/XLSX2CSV.java

If you run into issues with shared strings table being too large, you might
also want https://github.com/pjfanning/poi-shared-strings

You can then use SXSSFWorkbook to save the modified sheets.

This is obviously not as straightforward as parsing into an XSSFWorkbook and
modifying it in place before saving it.



--
Sent from: http://apache-poi.1045710.n5.nabble.com/POI-Dev-f2312866.html

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: SXSSF cannot work with large excel files

Axel Howind-2
In reply to this post by Shevchuk Vasyl
Hi,

SXSSF can definitely work with large Excel sheets. But SXSSF is for writing. Your problem is with XSSF because you have to read in the large file first.

SXSSF is really very helpful and usable, and I don’t know what most developers need, but I mostly need it for writing new large excel files.

Now to your problem:
- the quick solution might be to use a JVM arg (-Xmx…) to increase Java heap size. If that’s ok, you are done.
- If the requirement is to append two rows to an existing file, I’d say excel is not the optimal format.Using simple CSV files is both faster, uses less memory, and if you don’t need colours and cell styles can be as short s a one liner in a shell script (and just a little more in Java). Excel by default opens CSV files that are stored in the correct format when you just double click the file.

Regards,
Axel

On 2019/01/16 16:28:34, Shevchuk Vasyl <[hidden email]> wrote:

> Hello Dev team,>
>
> According to your limitations notes (>
> http://poi.apache.org/components/spreadsheet/limitations.html) I should use>
> SXSSFWorkbook <http://poi.apache.org/components/spreadsheet/> to save big>
> amount of data to avoid huge memory usage.>
> My current task is to load big excel file (with at least 2 big worksheets)>
> and add new rows to one of those big sheet. I can read data from big>
> worksheet using the same limitaions page notes (using OPCPackage and>
> XSSFReader) but I cannot add new rows to those worksheets.>
>
> I cannot find any way to use XSSFWorkbook no SXSSFWorkbook because of first>
> try to load all data from excel file in constructor (this cause of>
> OutOfMemory exception on my environment) and second one can only use first>
> one as an constructor parameter, it cannot take excel file name and try to>
> load it using rowAccessWindowSize.>
>
> Because of this I have 2 questions.>
> 1. Is SXSSFWorkbook class usable at all in it's current implementation? As>
> for me most of developers need to update existing excel sheets instead of>
> creating new. Maybe it's time to add some possibility to load big files>
> using the same rowAccessWindowSize idea?>
> 2. Could you please provide me some workaround/proposal to add new rows to>
> big excel file sheet without OutOfMemoryException?>
>
>
> Best regards,>
> Senior Software Developer,>
> Skype: vasylshevchuk>
> Cell: +380661044415>
> Vasyl Shevchuk>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]