POI pivot tables

Discussion:

POI pivot tables

I***@barclayscapital.com

2005-08-01 09:59:14 UTC

Hello all,

Apologies, I know similar questions come up often. However I have looked
in the archives and bugzilla, and I haven't found anything that seemed
directly relevant.

I was trying to open a (Excel 2002) XLS which contains a pivot table.
I was hoping to just fill in data, and when I re-opened Excel, it would
refresh the pivot table.

Trying this with POI 2.5.1 gives:

java.lang.reflect.InvocationTargetException
...
at
java.lang.reflect.Constructor.newInstance(Constructor.java:274)
at
org.apache.poi.hssf.record.RecordFactory.createRecord(RecordFactory.java
:224)
at
org.apache.poi.hssf.record.RecordFactory.createRecords(RecordFactory.jav
a:160)
at
org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:163)
...
Caused by: java.lang.ArrayIndexOutOfBoundsException
at java.lang.System.arraycopy(Native Method)
at
org.apache.poi.hssf.record.UnknownRecord.<init>(UnknownRecord.java:62)
at
org.apache.poi.hssf.record.SubRecord.createSubRecord(SubRecord.java:57)
at
org.apache.poi.hssf.record.ObjRecord.fillFields(ObjRecord.java:99)
at org.apache.poi.hssf.record.Record.fillFields(Record.java:90)
at org.apache.poi.hssf.record.Record.<init>(Record.java:55)
at
org.apache.poi.hssf.record.ObjRecord.<init>(ObjRecord.java:61)

which I think is a known issue?

So, I've tried poi-3.0-alpha1-20050801.jar.
This doesn't give any error. But when I load the file in Excel (2002
SP3), it discards the PivotTable 'due to integrity problems'.

Can POI be used in this way? Is it a known problem?

Appreciate any advice.

Iain Shepherd

------------------------------------------------------------------------
For more information about Barclays Capital, please
visit our web site at http://www.barcap.com.

Internet communications are not secure and therefore the Barclays
Group does not accept legal responsibility for the contents of this
message. Although the Barclays Group operates anti-virus programmes,
it does not accept responsibility for any damage whatsoever that is
caused by viruses being passed. Any views or opinions presented are
solely those of the author and do not necessarily represent those of the
Barclays Group. Replies to this email may be monitored by the Barclays
Group for operational or business reasons.

------------------------------------------------------------------------

---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-***@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/

a***@apache.org

2005-08-01 13:34:27 UTC

Permalink

I posted awhile back on changes that can be made to the record factory
for (ignoring) images. Those same changes would seem to apply here.

Doing it in an API fashion via some toggle of sorts is on my list of todos.

Post by I***@barclayscapital.com
Hello all,
Apologies, I know similar questions come up often. However I have looked
in the archives and bugzilla, and I haven't found anything that seemed
directly relevant.
I was trying to open a (Excel 2002) XLS which contains a pivot table.
I was hoping to just fill in data, and when I re-opened Excel, it would
refresh the pivot table.
java.lang.reflect.InvocationTargetException
...
at
java.lang.reflect.Constructor.newInstance(Constructor.java:274)
at
org.apache.poi.hssf.record.RecordFactory.createRecord(RecordFactory.java
:224)
at
org.apache.poi.hssf.record.RecordFactory.createRecords(RecordFactory.jav
a:160)
at
org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:163)
...
Caused by: java.lang.ArrayIndexOutOfBoundsException
at java.lang.System.arraycopy(Native Method)
at
org.apache.poi.hssf.record.UnknownRecord.<init>(UnknownRecord.java:62)
at
org.apache.poi.hssf.record.SubRecord.createSubRecord(SubRecord.java:57)
at
org.apache.poi.hssf.record.ObjRecord.fillFields(ObjRecord.java:99)
at org.apache.poi.hssf.record.Record.fillFields(Record.java:90)
at org.apache.poi.hssf.record.Record.<init>(Record.java:55)
at
org.apache.poi.hssf.record.ObjRecord.<init>(ObjRecord.java:61)
which I think is a known issue?
So, I've tried poi-3.0-alpha1-20050801.jar.
This doesn't give any error. But when I load the file in Excel (2002
SP3), it discards the PivotTable 'due to integrity problems'.
Can POI be used in this way? Is it a known problem?
Appreciate any advice.
Iain Shepherd
------------------------------------------------------------------------
For more information about Barclays Capital, please
visit our web site at http://www.barcap.com.
Internet communications are not secure and therefore the Barclays
Group does not accept legal responsibility for the contents of this
message. Although the Barclays Group operates anti-virus programmes,
it does not accept responsibility for any damage whatsoever that is
caused by viruses being passed. Any views or opinions presented are
solely those of the author and do not necessarily represent those of the
Barclays Group. Replies to this email may be monitored by the Barclays
Group for operational or business reasons.
------------------------------------------------------------------------
---------------------------------------------------------------------
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
.

I***@barclayscapital.com

2005-08-01 14:14:54 UTC

Permalink

There are problems with
*reading* images in the 2.5 series. It writes them just fine, just
has issues reading them. Drop to 2.0.x. Shortly I'll add a "ignore
images" bit twiddle if no one beats me to it.

In that post you were talking about the InvocationTargetException on
loading a workbook.

Just to say, that problem went away when I tried today's 3.0 nightly
build. (Or, at least, it didn't throw anything...) But when I saved
using POI and reloaded in Excel, it complained of 'integrity problems'
and discarded the PivotTable.

(Maybe I should try 2.0.x also...)

Iain

-----Original Message-----
From: ***@apache.org [mailto:***@apache.org]
Sent: 01 August 2005 14:34
To: poi-***@jakarta.apache.org
Subject: Re: POI pivot tables

I posted awhile back on changes that can be made to the record factory
for (ignoring) images. Those same changes would seem to apply here.

Doing it in an API fashion via some toggle of sorts is on my list of
todos.

org.apache.poi.hssf.record.RecordFactory.createRecords(RecordFactory.jav

a:160)
at

org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:163)

...
Caused by: java.lang.ArrayIndexOutOfBoundsException
at java.lang.System.arraycopy(Native Method)
at
org.apache.poi.hssf.record.UnknownRecord.<init>(UnknownRecord.java:62)
at

org.apache.poi.hssf.record.SubRecord.createSubRecord(SubRecord.java:57)

at
org.apache.poi.hssf.record.ObjRecord.fillFields(ObjRecord.java:99)
at

org.apache.poi.hssf.record.Record.fillFields(Record.java:90)

at org.apache.poi.hssf.record.Record.<init>(Record.java:55)
at
org.apache.poi.hssf.record.ObjRecord.<init>(ObjRecord.java:61)
which I think is a known issue?
So, I've tried poi-3.0-alpha1-20050801.jar.
This doesn't give any error. But when I load the file in Excel (2002
SP3), it discards the PivotTable 'due to integrity problems'.
Can POI be used in this way? Is it a known problem?
Appreciate any advice.
Iain Shepherd
----------------------------------------------------------------------
--
For more information about Barclays Capital, please
visit our web site at http://www.barcap.com.
Internet communications are not secure and therefore the Barclays
Group does not accept legal responsibility for the contents of this
message. Although the Barclays Group operates anti-virus programmes,
it does not accept responsibility for any damage whatsoever that is
caused by viruses being passed. Any views or opinions presented are
solely those of the author and do not necessarily represent those of

the

Barclays Group. Replies to this email may be monitored by the

Barclays

Group for operational or business reasons.
----------------------------------------------------------------------
--
---------------------------------------------------------------------
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/ .

--
Andrew C. Oliver
SuperLink Software, Inc.

Java to Excel using POI http://www.superlinksoftware.com/services/poi
Commercial support including features added/implemented, bugs fixed.

---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-***@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/

---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-***@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/

a***@apache.org

2005-08-01 14:20:35 UTC

Permalink

Beats me, had lots of diffs and stuff.

So using 2.0.x achieved the same as the diffs I posted. I assume 3.0
probably tries to fix problems with the
imaging code. I'm having you comment it out :-). POI should leave
records it doesn't understand where they lie.
Thus I suspect that will make Excel happy.

-Andy

Post by I***@barclayscapital.com

In that post you were talking about the InvocationTargetException on
loading a workbook.
Just to say, that problem went away when I tried today's 3.0 nightly
build. (Or, at least, it didn't throw anything...) But when I saved
using POI and reloaded in Excel, it complained of 'integrity problems'
and discarded the PivotTable.
(Maybe I should try 2.0.x also...)
Iain
-----Original Message-----
Sent: 01 August 2005 14:34
Subject: Re: POI pivot tables
I posted awhile back on changes that can be made to the record factory
for (ignoring) images. Those same changes would seem to apply here.
Doing it in an API fashion via some toggle of sorts is on my list of
todos.

org.apache.poi.hssf.record.RecordFactory.createRecords(RecordFactory.jav

a:160)
at

org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:163)

...
Caused by: java.lang.ArrayIndexOutOfBoundsException
at java.lang.System.arraycopy(Native Method)
at
org.apache.poi.hssf.record.UnknownRecord.<init>(UnknownRecord.java:62)
at

org.apache.poi.hssf.record.SubRecord.createSubRecord(SubRecord.java:57)

at
org.apache.poi.hssf.record.ObjRecord.fillFields(ObjRecord.java:99)
at

org.apache.poi.hssf.record.Record.fillFields(Record.java:90)

the

Barclays Group. Replies to this email may be monitored by the

Barclays

Amol Deshmukh

2005-08-01 16:34:57 UTC

Permalink

Ian,

The following steps worked for me:

1. Created a new excel workbook with three cols

2. Created named ranges for the three cols.

3. Created pivot table and chart based on the
named ranges for the three cols.

4. Opened the pivot table using POI api and added
an additional row of data for each of the three cols.

5. Extended the range of the "named range" by 1 row.

6. Wrote out the modified workbook (using poi)

7. Open the workbook using excel and did
"refresh data" on the pivot table. The pivot table
and the pivot chart were updated correctly.

Are you trying something different?
If not then a wild guess is that something else
is causing the problem you are facing.

If you are doing something different could you
post the code?

Regards,
~ amol

Post by I***@barclayscapital.com
-----Original Message-----
Sent: Monday, August 01, 2005 5:59 AM
Subject: POI pivot tables
Hello all,
Apologies, I know similar questions come up often. However I
have looked
in the archives and bugzilla, and I haven't found anything that seemed
directly relevant.
I was trying to open a (Excel 2002) XLS which contains a pivot table.
I was hoping to just fill in data, and when I re-opened
Excel, it would
refresh the pivot table.
java.lang.reflect.InvocationTargetException
...
at
java.lang.reflect.Constructor.newInstance(Constructor.java:274)
at
org.apache.poi.hssf.record.RecordFactory.createRecord(RecordFa
ctory.java
:224)
at
org.apache.poi.hssf.record.RecordFactory.createRecords(RecordF
actory.jav
a:160)
at
org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook
.java:163)
...
Caused by: java.lang.ArrayIndexOutOfBoundsException
at java.lang.System.arraycopy(Native Method)
at
org.apache.poi.hssf.record.UnknownRecord.<init>(UnknownRecord.java:62)
at
org.apache.poi.hssf.record.SubRecord.createSubRecord(SubRecord
.java:57)
at
org.apache.poi.hssf.record.ObjRecord.fillFields(ObjRecord.java:99)
at
org.apache.poi.hssf.record.Record.fillFields(Record.java:90)
at org.apache.poi.hssf.record.Record.<init>(Record.java:55)
at
org.apache.poi.hssf.record.ObjRecord.<init>(ObjRecord.java:61)
which I think is a known issue?
So, I've tried poi-3.0-alpha1-20050801.jar.
This doesn't give any error. But when I load the file in Excel (2002
SP3), it discards the PivotTable 'due to integrity problems'.
Can POI be used in this way? Is it a known problem?
Appreciate any advice.
Iain Shepherd
--------------------------------------------------------------
----------
For more information about Barclays Capital, please
visit our web site at http://www.barcap.com.
Internet communications are not secure and therefore the Barclays
Group does not accept legal responsibility for the contents of this
message. Although the Barclays Group operates anti-virus programmes,
it does not accept responsibility for any damage whatsoever that is
caused by viruses being passed. Any views or opinions presented are
solely those of the author and do not necessarily represent
those of the
Barclays Group. Replies to this email may be monitored by
the Barclays
Group for operational or business reasons.
--------------------------------------------------------------
----------
---------------------------------------------------------------------
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/

---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-***@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/