You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Blake Watson <bl...@pnmac.com> on 2018/03/16 17:25:20 UTC
Pivot tables
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
blake.watson@pnmac.com
www.PennyMacUSA.com <http://www.pennymacusa.com/>
Re: Pivot tables
Posted by Blake Watson <bl...@pnmac.com>.
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.
Re: Pivot tables
Posted by Blake Watson <bl...@pnmac.com>.
Good to know. I can enforce a certain discipline on the users like "You
must use a table", so there's that.
On Wed, Mar 21, 2018 at 3:45 PM, Greg Woolsey <gr...@gmail.com>
wrote:
> >
> > 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
blake.watson@pnmac.com
www.PennyMacUSA.com <http://www.pennymacusa.com/>
Re: Pivot tables
Posted by Greg Woolsey <gr...@gmail.com>.
>
> 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.
Re: Pivot tables
Posted by Blake Watson <bl...@pnmac.com>.
>>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
Re: Pivot tables
Posted by Greg Woolsey <gr...@gmail.com>.
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.
On Wed, Mar 21, 2018 at 1:33 PM Blake Watson <bl...@pnmac.com> wrote:
> 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===
>
Re: Pivot tables
Posted by Blake Watson <bl...@pnmac.com>.
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===
Re: Pivot tables
Posted by Dave Fisher <da...@comcast.net>.
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
> On Mar 19, 2018, at 2:07 PM, Greg Woolsey <gr...@gmail.com> wrote:
>
> 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.
>
> On Mon, Mar 19, 2018 at 1:33 PM Blake Watson <bl...@pnmac.com> wrote:
>
>> On Fri, Mar 16, 2018 at 11:35 AM, Greg Woolsey <gr...@gmail.com>
>> wrote:
>>
>>> There still isn't much, if any, support beyond exposing the CT* classes.
>>>
>>
>> That's what I figured.
>>
>>
>>
>>> 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.
>>
Re: Pivot tables
Posted by Greg Woolsey <gr...@gmail.com>.
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.
On Mon, Mar 19, 2018 at 1:33 PM Blake Watson <bl...@pnmac.com> wrote:
> On Fri, Mar 16, 2018 at 11:35 AM, Greg Woolsey <gr...@gmail.com>
> wrote:
>
> > There still isn't much, if any, support beyond exposing the CT* classes.
> >
>
> That's what I figured.
>
>
>
> > 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.
>
Re: Pivot tables
Posted by Blake Watson <bl...@pnmac.com>.
On Fri, Mar 16, 2018 at 11:35 AM, Greg Woolsey <gr...@gmail.com>
wrote:
> There still isn't much, if any, support beyond exposing the CT* classes.
>
That's what I figured.
> 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.
Re: Pivot tables
Posted by Greg Woolsey <gr...@gmail.com>.
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.
On Fri, Mar 16, 2018 at 10:25 AM Blake Watson <bl...@pnmac.com>
wrote:
> 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>
> blake.watson@pnmac.com
> www.PennyMacUSA.com <http://www.pennymacusa.com/>
>