You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by "George S." <ge...@mhsoftware.com> on 2018/06/27 22:38:30 UTC
Pivot Table Support
I'm using POI 3.17 and I've got a basic Pivot table working. Right now,
I have rows, and I'm summing a column.
I'd like to add columns. E.G. one of my source data columns is a period
label. For example, 2018-05 or 2018-07. I'd like each period to be
grouped, and the sums broken out by the Row/Period Label.
It doesn't seem like this is possible in 3.17. Is support for this in
the 4.0 snapshot?
If it is, could someone give me a hint to how to add the columns?
If it's not, I can get by, because it's just them dragging a column
heading into the Columns segment of the Pivot Table editor, but I
thought I'd ask.
BTW, this works REALLY well. I've always limited myself to CSV output,
but the ability to create sheets is really cool!
--
George S.
*MH Software, Inc.*
Voice: 303 438 9585
http://www.mhsoftware.com
Re: Pivot Table Support
Posted by "George S." <ge...@mhsoftware.com>.
Greg,
On 6/27/2018 7:51 PM, Greg Woolsey wrote:
> There is very limited support for pivot tables yet in POI, as you've
> found. The avenues I can think of for you are:
>
> 1. compare before and after XLSX output (it's zipped XML files) to see what
> Excel is doing, then replicate that in POI - not typically a simple task.
Thanks for replying. I'll take a stab at diffing an xlsx file and see
what I can come up with.
Since it's pretty easy for the users to do the last bit by dragging a
field into the columns area, I don't want to introduce all the extra
complexity of template files.
>
> 2. Start with a "template" file you create in Excel - not an Excel
> Template, but a plain file with the pivot table defined how you want it.
> Then instead of creating a new blank workbook in POI, open that one.
> Populate your data, update any range references if needed, and write the
> updated version to a new file/stream.
>
> 3. Submit a patch adding what you need to POI. Always welcome and super
> helpful for us and the rest of the community, but for something like this,
> probably not a cost/benefit most employers would agree to.
>
> I've done #2 many times for lots of different purposes, and a couple of
> those involved pivot tables. It worked great. POI can read and write all
> sorts of content it doesn't have a high-level API to manipulate, as long as
> the file complies with the OOXML standard.
>
>
>
> On Wed, Jun 27, 2018 at 3:38 PM George S. <ge...@mhsoftware.com> wrote:
>
>> I'm using POI 3.17 and I've got a basic Pivot table working. Right now,
>> I have rows, and I'm summing a column.
>>
>> I'd like to add columns. E.G. one of my source data columns is a period
>> label. For example, 2018-05 or 2018-07. I'd like each period to be
>> grouped, and the sums broken out by the Row/Period Label.
>>
>> It doesn't seem like this is possible in 3.17. Is support for this in
>> the 4.0 snapshot?
>>
>> If it is, could someone give me a hint to how to add the columns?
>>
>> If it's not, I can get by, because it's just them dragging a column
>> heading into the Columns segment of the Pivot Table editor, but I
>> thought I'd ask.
>>
>> BTW, this works REALLY well. I've always limited myself to CSV output,
>> but the ability to create sheets is really cool!
>>
>> --
>> George S.
>> *MH Software, Inc.*
>> Voice: 303 438 9585 <(303)%20438-9585>
>> http://www.mhsoftware.com
>>
--
George S.
*MH Software, Inc.*
Voice: 303 438 9585
http://www.mhsoftware.com
Re: Pivot Table Support
Posted by Greg Woolsey <gr...@gmail.com>.
There is very limited support for pivot tables yet in POI, as you've
found. The avenues I can think of for you are:
1. compare before and after XLSX output (it's zipped XML files) to see what
Excel is doing, then replicate that in POI - not typically a simple task.
2. Start with a "template" file you create in Excel - not an Excel
Template, but a plain file with the pivot table defined how you want it.
Then instead of creating a new blank workbook in POI, open that one.
Populate your data, update any range references if needed, and write the
updated version to a new file/stream.
3. Submit a patch adding what you need to POI. Always welcome and super
helpful for us and the rest of the community, but for something like this,
probably not a cost/benefit most employers would agree to.
I've done #2 many times for lots of different purposes, and a couple of
those involved pivot tables. It worked great. POI can read and write all
sorts of content it doesn't have a high-level API to manipulate, as long as
the file complies with the OOXML standard.
On Wed, Jun 27, 2018 at 3:38 PM George S. <ge...@mhsoftware.com> wrote:
> I'm using POI 3.17 and I've got a basic Pivot table working. Right now,
> I have rows, and I'm summing a column.
>
> I'd like to add columns. E.G. one of my source data columns is a period
> label. For example, 2018-05 or 2018-07. I'd like each period to be
> grouped, and the sums broken out by the Row/Period Label.
>
> It doesn't seem like this is possible in 3.17. Is support for this in
> the 4.0 snapshot?
>
> If it is, could someone give me a hint to how to add the columns?
>
> If it's not, I can get by, because it's just them dragging a column
> heading into the Columns segment of the Pivot Table editor, but I
> thought I'd ask.
>
> BTW, this works REALLY well. I've always limited myself to CSV output,
> but the ability to create sheets is really cool!
>
> --
> George S.
> *MH Software, Inc.*
> Voice: 303 438 9585 <(303)%20438-9585>
> http://www.mhsoftware.com
>