You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Carlos Arroyo <ca...@gmail.com> on 2015/03/19 20:16:33 UTC
POI Memory issue
Hi, I would like to subscribe on this page to share knowledge and ask
questions about POI. I'm a developer and I'm using POI to create a very
large Excel file. The problem is that I'm having memory issues because I'm
using XSSFWorkbook and this uses a lot of memory. I could do it with
SXSSFWorkbook to solve the memory issue but I have to create pivot tables
and these tables are only available within XSSFWorkbook sheets, so I want
to know if there's a way to solve this problem.1- Maybe implement pivot
tables in SXSSFWorkbooks 2- Fix the memory issue with XSSFWorkbooks. Thanks
for your help.
Re: POI Memory issue
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Forgive me please. it is a very long time since I worked with pivot tables
but is it not possible to use either named areas or dynamic data sources to
declare the data source for the table and apply the technique Jim is
suggesting?
By dynamic data source, I mean a formula that effectively says all of the
data in the sheet and is explianed far better than can I here -
http://www.contextures.com/xlPivot01.html.
Named ranges are well, ranges of cells that you ascribe a name to. I am
pretty sure that if you create an empty workbook with the names defined and
the pivot table based on them then POI can be used to open that workbook,
populate the sheet and alter/amend the range of cells the name refers. That
should, I think, work to refresh the pivot table but cannot say for certain
that this will be the case. Should be easy enough to work up a test though.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/POI-Memory-issue-tp5718281p5719426.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: POI Memory issue
Posted by Carlos Arroyo <ca...@gmail.com>.
Hi Jim,
Thanks for your answer, I did that but how do you refresh the pivot table?
When you build a pivotTable you need to specify the AreaReference, and at
the beginning the AreaReference of my pivot table is only the first two
rows, how can i change it to be for all the rows that I made with SXSSF and
also I think that's the reason of the memory increase. Is not SXSSF or
XSSF, the main reason of the amount usage of memory is that the
AreaReference for the pivot Table is huge. Am I right? What do you think?
On Sat, Mar 21, 2015 at 3:59 AM, Jim Talbut <jt...@spudsoft.co.uk> wrote:
> Hi Carlos,
>
> One technique that might help for you is to separate the pivot table work
> from the output of the data.
> Create the pivot table up front (ideally using Excel, but with POI if you
> have to) using an input sheet that has the structure of your final data but
> that only contains two rows (header row plus one data row).
> Then, as a completely separate pass, replace all the cells in the input
> sheet with your real data using SXXF.
>
> Jim
>
>
> On 20/03/2015 15:40, Carlos Arroyo wrote:
>
>> My files aren't too heavy, they're like 2MB, but they have like 2000 rows
>> and 95 columns. I found the memory values for XSSF: about 630 bytes/cell
>> and maybe that's why my files consume a lot of memory and I get "Java out
>> of memory error". Could you please help me or give me advice on what can i
>> do with this? Thanks for your help and consideration.
>>
>> On Thu, Mar 19, 2015 at 2:51 PM, Dominik Stadler <do...@gmx.at>
>> wrote:
>>
>> Hi,
>>>
>>> As the XSSF implementation is fundamentally based on having all
>>> elements in memory, there will always be a file size which exceeds
>>> that available memory. How big are your files? I.e. are you just a bit
>>> over the available memory or a lot?
>>>
>>> Maybe you can do a heapdump using the Java flags as explained at
>>> http://stackoverflow.com/a/543037/411846. Then using a tool like
>>> Eclipse MAT you can analyze the resulting hprof-dump to find out which
>>> parts use up most of the memory, this might at least tell us if it is
>>> feasible to try to reduce memory usage.
>>>
>>> Don't know about Pivot table functionality, so cannot say how much
>>> effort it would be to add it to the SXSSF implementation.
>>>
>>> Thanks... Dominik.
>>>
>>> On Thu, Mar 19, 2015 at 8:16 PM, Carlos Arroyo
>>> <ca...@gmail.com> wrote:
>>>
>>>> Hi, I would like to subscribe on this page to share knowledge and ask
>>>> questions about POI. I'm a developer and I'm using POI to create a very
>>>> large Excel file. The problem is that I'm having memory issues because
>>>>
>>> I'm
>>>
>>>> using XSSFWorkbook and this uses a lot of memory. I could do it with
>>>> SXSSFWorkbook to solve the memory issue but I have to create pivot
>>>> tables
>>>> and these tables are only available within XSSFWorkbook sheets, so I
>>>> want
>>>> to know if there's a way to solve this problem.1- Maybe implement pivot
>>>> tables in SXSSFWorkbooks 2- Fix the memory issue with XSSFWorkbooks.
>>>>
>>> Thanks
>>>
>>>> for your help.
>>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@poi.apache.org
>>>
>>>
>>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: POI Memory issue
Posted by Jim Talbut <jt...@spudsoft.co.uk>.
Hi Carlos,
One technique that might help for you is to separate the pivot table
work from the output of the data.
Create the pivot table up front (ideally using Excel, but with POI if
you have to) using an input sheet that has the structure of your final
data but that only contains two rows (header row plus one data row).
Then, as a completely separate pass, replace all the cells in the input
sheet with your real data using SXXF.
Jim
On 20/03/2015 15:40, Carlos Arroyo wrote:
> My files aren't too heavy, they're like 2MB, but they have like 2000 rows
> and 95 columns. I found the memory values for XSSF: about 630 bytes/cell
> and maybe that's why my files consume a lot of memory and I get "Java out
> of memory error". Could you please help me or give me advice on what can i
> do with this? Thanks for your help and consideration.
>
> On Thu, Mar 19, 2015 at 2:51 PM, Dominik Stadler <do...@gmx.at>
> wrote:
>
>> Hi,
>>
>> As the XSSF implementation is fundamentally based on having all
>> elements in memory, there will always be a file size which exceeds
>> that available memory. How big are your files? I.e. are you just a bit
>> over the available memory or a lot?
>>
>> Maybe you can do a heapdump using the Java flags as explained at
>> http://stackoverflow.com/a/543037/411846. Then using a tool like
>> Eclipse MAT you can analyze the resulting hprof-dump to find out which
>> parts use up most of the memory, this might at least tell us if it is
>> feasible to try to reduce memory usage.
>>
>> Don't know about Pivot table functionality, so cannot say how much
>> effort it would be to add it to the SXSSF implementation.
>>
>> Thanks... Dominik.
>>
>> On Thu, Mar 19, 2015 at 8:16 PM, Carlos Arroyo
>> <ca...@gmail.com> wrote:
>>> Hi, I would like to subscribe on this page to share knowledge and ask
>>> questions about POI. I'm a developer and I'm using POI to create a very
>>> large Excel file. The problem is that I'm having memory issues because
>> I'm
>>> using XSSFWorkbook and this uses a lot of memory. I could do it with
>>> SXSSFWorkbook to solve the memory issue but I have to create pivot tables
>>> and these tables are only available within XSSFWorkbook sheets, so I want
>>> to know if there's a way to solve this problem.1- Maybe implement pivot
>>> tables in SXSSFWorkbooks 2- Fix the memory issue with XSSFWorkbooks.
>> Thanks
>>> for your help.
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: POI Memory issue
Posted by Carlos Arroyo <ca...@gmail.com>.
My files aren't too heavy, they're like 2MB, but they have like 2000 rows
and 95 columns. I found the memory values for XSSF: about 630 bytes/cell
and maybe that's why my files consume a lot of memory and I get "Java out
of memory error". Could you please help me or give me advice on what can i
do with this? Thanks for your help and consideration.
On Thu, Mar 19, 2015 at 2:51 PM, Dominik Stadler <do...@gmx.at>
wrote:
> Hi,
>
> As the XSSF implementation is fundamentally based on having all
> elements in memory, there will always be a file size which exceeds
> that available memory. How big are your files? I.e. are you just a bit
> over the available memory or a lot?
>
> Maybe you can do a heapdump using the Java flags as explained at
> http://stackoverflow.com/a/543037/411846. Then using a tool like
> Eclipse MAT you can analyze the resulting hprof-dump to find out which
> parts use up most of the memory, this might at least tell us if it is
> feasible to try to reduce memory usage.
>
> Don't know about Pivot table functionality, so cannot say how much
> effort it would be to add it to the SXSSF implementation.
>
> Thanks... Dominik.
>
> On Thu, Mar 19, 2015 at 8:16 PM, Carlos Arroyo
> <ca...@gmail.com> wrote:
> > Hi, I would like to subscribe on this page to share knowledge and ask
> > questions about POI. I'm a developer and I'm using POI to create a very
> > large Excel file. The problem is that I'm having memory issues because
> I'm
> > using XSSFWorkbook and this uses a lot of memory. I could do it with
> > SXSSFWorkbook to solve the memory issue but I have to create pivot tables
> > and these tables are only available within XSSFWorkbook sheets, so I want
> > to know if there's a way to solve this problem.1- Maybe implement pivot
> > tables in SXSSFWorkbooks 2- Fix the memory issue with XSSFWorkbooks.
> Thanks
> > for your help.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: POI Memory issue
Posted by Dominik Stadler <do...@gmx.at>.
Hi,
As the XSSF implementation is fundamentally based on having all
elements in memory, there will always be a file size which exceeds
that available memory. How big are your files? I.e. are you just a bit
over the available memory or a lot?
Maybe you can do a heapdump using the Java flags as explained at
http://stackoverflow.com/a/543037/411846. Then using a tool like
Eclipse MAT you can analyze the resulting hprof-dump to find out which
parts use up most of the memory, this might at least tell us if it is
feasible to try to reduce memory usage.
Don't know about Pivot table functionality, so cannot say how much
effort it would be to add it to the SXSSF implementation.
Thanks... Dominik.
On Thu, Mar 19, 2015 at 8:16 PM, Carlos Arroyo
<ca...@gmail.com> wrote:
> Hi, I would like to subscribe on this page to share knowledge and ask
> questions about POI. I'm a developer and I'm using POI to create a very
> large Excel file. The problem is that I'm having memory issues because I'm
> using XSSFWorkbook and this uses a lot of memory. I could do it with
> SXSSFWorkbook to solve the memory issue but I have to create pivot tables
> and these tables are only available within XSSFWorkbook sheets, so I want
> to know if there's a way to solve this problem.1- Maybe implement pivot
> tables in SXSSFWorkbooks 2- Fix the memory issue with XSSFWorkbooks. Thanks
> for your help.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org