Pivot Table Support

[George S.]

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!

[Greg Woolsey]

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.

Post by George S.
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.]

Greg,

Post by Greg Woolsey
There is very limited support for pivot tables yet in POI, as you've
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.

Post by Greg Woolsey
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.

Post by George S.
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