Discussion:
Pivot tables
Blake Watson
2018-03-16 17:25:20 UTC
Permalink
Hi, guys—

Saw a bug about pivot tables and was wondering: Has pivot table support
advanced beyond the "not really supported" level documented on the
"limitations" page?

https://poi.apache.org/spreadsheet/limitations.html
--
*Blake Watson*

*PNMAC*
Application Development Manager
5898 Condor Drive
Moorpark, CA 93021
(805) 330.4911 x7742
***@pnmac.com
www.PennyMacUSA.com <http://www.pennymacusa.com/>
Greg Woolsey
2018-03-16 18:35:30 UTC
Permalink
There still isn't much, if any, support beyond exposing the CT* classes.
That question also sounded like they wanted "live" manipulation, e.g.
applying sorting. That's an entirely different level of feature support
POI doesn't do for much beyond formula evaluation and recently shifting
cells by rows/columns.
Post by Blake Watson
Hi, guys—
Saw a bug about pivot tables and was wondering: Has pivot table support
advanced beyond the "not really supported" level documented on the
"limitations" page?
https://poi.apache.org/spreadsheet/limitations.html
--
*Blake Watson*
*PNMAC*
Application Development Manager
5898 Condor Drive
Moorpark, CA 93021
(805) 330.4911 x7742 <(805)%20330-4911>
www.PennyMacUSA.com <http://www.pennymacusa.com/>
Blake Watson
2018-03-19 20:33:05 UTC
Permalink
Post by Greg Woolsey
There still isn't much, if any, support beyond exposing the CT* classes.
That's what I figured.​
Post by Greg Woolsey
That question also sounded like they wanted "live" manipulation, e.g.
applying sorting. That's an entirely different level of feature support
POI doesn't do for much beyond formula evaluation and recently shifting
cells by rows/columns.
​ I'm not sure it would even be appropriate for POI to do this, would it?
It's kind of an interactive feature.

Everything I do can be summed up as "putting values into cells,
re-calculating, re-displaying spreadsheet...." so I'm not even sure that
kind of interactivity fits into what =I= do.
Greg Woolsey
2018-03-19 21:07:32 UTC
Permalink
I agree. POI is all about manipulating the contents of the Office files,
not recreating the UI. The closest is formula evaluation, but that can be
seen as facilitating document updates, as changing cell values obviously
means needing to recalculate the cached values of dependent formulas, and
workbooks marked as "recalculate on open" need formula evaluation to
properly read cell values.

I for example have a bunch of code that dynamically updates QueryTables
based on their ODBC SQL statements, translating connections to JDBC,
handling parameters, resizing tables based on results, filling calculated
columns, updating dependent values, etc. But not much if any of that is
applicable or desired in the POI codebase in my opinion.
Post by Blake Watson
Post by Greg Woolsey
There still isn't much, if any, support beyond exposing the CT* classes.
That's what I figured.​
Post by Greg Woolsey
That question also sounded like they wanted "live" manipulation, e.g.
applying sorting. That's an entirely different level of feature support
POI doesn't do for much beyond formula evaluation and recently shifting
cells by rows/columns.
​ I'm not sure it would even be appropriate for POI to do this, would it?
It's kind of an interactive feature.
Everything I do can be summed up as "putting values into cells,
re-calculating, re-displaying spreadsheet...." so I'm not even sure that
kind of interactivity fits into what =I= do.
Blake Watson
2018-03-21 17:25:24 UTC
Permalink
It looks like there's been some ... planned? dreamed? =) ...support for
pivot tables here:

https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFPivotTable.html

Although it looks like none of those "get"s are working. I guess that's a
pretty good roadmap, though, for what could be developed.
​
Dave Fisher
2018-03-21 18:11:38 UTC
Permalink
Hi -

The initial formula evaluation contributor had a use case of a website where client insurance rates were calculated based on an actuarial model that changed annually.

Regards,
Dave
Post by Greg Woolsey
I agree. POI is all about manipulating the contents of the Office files,
not recreating the UI. The closest is formula evaluation, but that can be
seen as facilitating document updates, as changing cell values obviously
means needing to recalculate the cached values of dependent formulas, and
workbooks marked as "recalculate on open" need formula evaluation to
properly read cell values.
I for example have a bunch of code that dynamically updates QueryTables
based on their ODBC SQL statements, translating connections to JDBC,
handling parameters, resizing tables based on results, filling calculated
columns, updating dependent values, etc. But not much if any of that is
applicable or desired in the POI codebase in my opinion.
Post by Blake Watson
Post by Greg Woolsey
There still isn't much, if any, support beyond exposing the CT* classes.
That's what I figured.​
Post by Greg Woolsey
That question also sounded like they wanted "live" manipulation, e.g.
applying sorting. That's an entirely different level of feature support
POI doesn't do for much beyond formula evaluation and recently shifting
cells by rows/columns.
​ I'm not sure it would even be appropriate for POI to do this, would it?
It's kind of an interactive feature.
Everything I do can be summed up as "putting values into cells,
re-calculating, re-displaying spreadsheet...." so I'm not even sure that
kind of interactivity fits into what =I= do.
Blake Watson
2018-03-21 20:33:41 UTC
Permalink
The CT for a (simple) pivot table section seems simple enough. I'm seeing
this part, though, which I believe is for the data source:

<main:location firstDataCol=\"1\" firstDataRow=\"2\" firstHeaderRow=\"1\"
ref=\"A3:D7\"/>

​
​firstDataCol and firstDataRow (with firstHeaderRow) are the source, and
​A3:D7 is the target. The A3:D7 sheet is the one I pulled the CT from. I
don't see where firstDataCol, firstDataRow and firstHeaderRow are mapped to
a specific sheet!

===Blake===
Greg Woolsey
2018-03-21 20:43:29 UTC
Permalink
The structure is actually quite convoluted, and involves multiple
components of the Zip structure, connected by relationship IDs (rId
attributes, _rels files):

Workbook.xml
Sheet#.xml
PivotTable#.xml
PivotCacheDefinition#.xml
PivotCacheRecords#.xml

If cell data is modified the corresponding PivotCacheRecords file needs to
be updated as well. If the definition is changed (selecting different
metrics, etc.) again the cache needs to be rebuilt/updated. Changing the
definition can (and most likely will) change the PivotTable area reference.

Excel has a ton of management code and functionality around keeping all
these in sync as the user makes changes. POI has none of that yet.
Post by Blake Watson
The CT for a (simple) pivot table section seems simple enough. I'm seeing
<main:location firstDataCol=\"1\" firstDataRow=\"2\" firstHeaderRow=\"1\"
ref=\"A3:D7\"/>
​
​firstDataCol and firstDataRow (with firstHeaderRow) are the source, and
​A3:D7 is the target. The A3:D7 sheet is the one I pulled the CT from. I
don't see where firstDataCol, firstDataRow and firstHeaderRow are mapped to
a specific sheet!
===Blake===
Blake Watson
2018-03-21 21:08:56 UTC
Permalink
The structure is actually quite convoluted, <<
I figured the "seems simple" was just that I was missing the pieces. =)
and involves multiple components of the Zip structure, connected by
relationship IDs (rIdattributes, _rels files):

Workbook.xml
Sheet#.xml
PivotTable#.xml
PivotCacheDefinition#.xml
PivotCacheRecords#.xml<<

Yeah, I couldn't find a way back to the actual field from the *Cache*
structures.
If cell data is modified the corresponding PivotCacheRecords file needs
to be updated as well.

I would need to do that.
If the definition is changed (selecting different metrics, etc.) again
the cache needs to be rebuilt/updated. Changing the definition can (and
most likely will) change the PivotTable area reference.

I wouldn't need to do that.
Excel has a ton of management code and functionality around keeping all these
in sync as the user makes changes. POI has none of that yet.

I'm gonna hope this comes in a lot lower on the priorities than some
functions I need to add. =P
Greg Woolsey
2018-03-21 22:45:46 UTC
Permalink
Post by Blake Watson
I'm gonna hope this comes in a lot lower on the priorities than some
functions I need to add. =P
I hear that. When I first looked into it, I got very creative in finding
ways to do what we needed without using Pivot Tables :)

In the past, the only time I used them with POI was through a template
built in Excel where the pivot referenced a full sheet of data, and I could
update that sheet with however many rows of data the specific instance
needed. It worked specifically because I didn't have to touch the pivot
table structures at all.

If your pivot table references an Excel structured table, those are also
not as bad to manipulate, even changing the number of rows.
Blake Watson
2018-03-22 17:05:50 UTC
Permalink
Good to know. I can enforce a certain discipline on the users like "You
must use a table", so there's that.
Post by Greg Woolsey
Post by Blake Watson
I'm gonna hope this comes in a lot lower on the priorities than some
functions I need to add. =P
I hear that. When I first looked into it, I got very creative in finding
ways to do what we needed without using Pivot Tables :)
In the past, the only time I used them with POI was through a template
built in Excel where the pivot referenced a full sheet of data, and I could
update that sheet with however many rows of data the specific instance
needed. It worked specifically because I didn't have to touch the pivot
table structures at all.
If your pivot table references an Excel structured table, those are also
not as bad to manipulate, even changing the number of rows.
--
*Blake Watson*

*PNMAC*
Application Development Manager
5898 Condor Drive
Moorpark, CA 93021
(805) 330.4911 x7742
***@pnmac.com
www.PennyMacUSA.com <http://www.pennymacusa.com/>
Loading...