You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Chris Stillwell <ch...@farecompare.com> on 2016/08/11 15:30:35 UTC
Creating pivot table and summation column
I have a sheet with 4 columns of data that I am wanting to create 2 pivot
tables with.
The first column has a classification label.
The second column has a start of week date.
The third and fourth columns contain integer values.
I want each pivot table to have a row for each classification and then a
column for each week start date, under which is the sum of one of the
integer columns.
So far I can only get it to work when I add the date column as a filter
like the code snippet below:
XSSFSheet pivotSheet = (XSSFSheet) wb.createSheet("Pivot");
XSSFPivotTable pivotTable1 = pivotSheet.createPivotTable(
new AreaReference(sheet.getSheetName()+"!A1:"+cellName,
SpreadsheetVersion.EXCEL2007),
new CellReference("A4"));
pivotTable1.addRowLabel(0); // classification
pivotTable1.addReportFilter(1); // weekStartDate
pivotTable1.addColumnLabel(DataConsolidateFunction.SUM,2,"uniquepages");
When I open the workbook in Excel and open the Pivot Table Builder I
can drag the weekStartDate from the Filters box to the Columns box and
that gives me the result I want, but I have not been able to do it
using poi.
I tried replacing the addReportFilter with pivotTable1.addDataColumn(1, false);
That just removed the filter and only displayed a column for the
classification rows and one summation column.
When I try pivotTable1.addDataColumn(1, true);
The worksheet will not open in Excel. I get an error dialog that says,
"Excel could not open seo_traffic_pivot.xlsx because some content is
unreadable. Do you want to open and repair this workbook?"
It seems like there should be a simple solution, but I have not been
able to figure it out. If anybody can point me in the right direction
I would appreciate it.
Chris
Re: Creating pivot table and summation column
Posted by Javen O'Neal <ja...@gmail.com>.
The release version in bugzilla is usually used to indicate a version of
POI which demonstrates the problem, preferably the most recent version that
demonstrates the problem.
If a bug gets fixed, you will see a comment referencing the commit that
fixed the bug, the bug status will change to fixed, and an entry added to
the change log (https://poi.apache.org/changes.html)
I'm guessing this will be fixed in 3.15 final, but no promises.
On Aug 11, 2016 9:32 AM, "Chris Stillwell" <ch...@farecompare.com>
wrote:
> Thanks, that very much looks like it could be the problem. I didn't see
> that there is a target release for the fix. I tried 3.15-beta2 which looks
> like the most current build and did not see the fix in there. Any
> expectations?
>
> Thanks again,
>
> Chris
>
> On Thu, Aug 11, 2016 at 10:45 AM, Javen O'Neal <ja...@gmail.com>
> wrote:
>
>> Read these two bugs to make sure that isn't the problem first. I have not
>> verified either bug yet.
>> https://bz.apache.org/bugzilla/show_bug.cgi?id=59705
>> https://bz.apache.org/bugzilla/show_bug.cgi?id=59368
>>
>> On Aug 11, 2016 8:30 AM, "Chris Stillwell" <chris.stillwell@farecompare.c
>> om> wrote:
>>
>>> I have a sheet with 4 columns of data that I am wanting to create 2 pivot
>>> tables with.
>>> The first column has a classification label.
>>> The second column has a start of week date.
>>> The third and fourth columns contain integer values.
>>>
>>> I want each pivot table to have a row for each classification and then a
>>> column for each week start date, under which is the sum of one of the
>>> integer columns.
>>> So far I can only get it to work when I add the date column as a filter
>>> like the code snippet below:
>>>
>>> XSSFSheet pivotSheet = (XSSFSheet) wb.createSheet("Pivot");
>>>
>>> XSSFPivotTable pivotTable1 = pivotSheet.createPivotTable(
>>> new AreaReference(sheet.getSheetName()+"!A1:"+cellName,
>>> SpreadsheetVersion.EXCEL2007),
>>> new CellReference("A4"));
>>> pivotTable1.addRowLabel(0); // classification
>>> pivotTable1.addReportFilter(1); // weekStartDate
>>> pivotTable1.addColumnLabel(DataConsolidateFunction.SUM,2,"uniquepages");
>>>
>>>
>>> When I open the workbook in Excel and open the Pivot Table Builder I
>>> can drag the weekStartDate from the Filters box to the Columns box and
>>> that gives me the result I want, but I have not been able to do it
>>> using poi.
>>>
>>> I tried replacing the addReportFilter with pivotTable1.addDataColumn(1,
>>> false);
>>>
>>> That just removed the filter and only displayed a column for the
>>> classification rows and one summation column.
>>>
>>> When I try pivotTable1.addDataColumn(1, true);
>>>
>>> The worksheet will not open in Excel. I get an error dialog that says,
>>> "Excel could not open seo_traffic_pivot.xlsx because some content is
>>> unreadable. Do you want to open and repair this workbook?"
>>>
>>> It seems like there should be a simple solution, but I have not been
>>> able to figure it out. If anybody can point me in the right direction
>>> I would appreciate it.
>>>
>>>
>>> Chris
>>>
>>
>
Re: Creating pivot table and summation column
Posted by Chris Stillwell <ch...@farecompare.com>.
Thanks, that very much looks like it could be the problem. I didn't see
that there is a target release for the fix. I tried 3.15-beta2 which looks
like the most current build and did not see the fix in there. Any
expectations?
Thanks again,
Chris
On Thu, Aug 11, 2016 at 10:45 AM, Javen O'Neal <ja...@gmail.com> wrote:
> Read these two bugs to make sure that isn't the problem first. I have not
> verified either bug yet.
> https://bz.apache.org/bugzilla/show_bug.cgi?id=59705
> https://bz.apache.org/bugzilla/show_bug.cgi?id=59368
>
> On Aug 11, 2016 8:30 AM, "Chris Stillwell" <chris.stillwell@farecompare.c
> om> wrote:
>
>> I have a sheet with 4 columns of data that I am wanting to create 2 pivot
>> tables with.
>> The first column has a classification label.
>> The second column has a start of week date.
>> The third and fourth columns contain integer values.
>>
>> I want each pivot table to have a row for each classification and then a
>> column for each week start date, under which is the sum of one of the
>> integer columns.
>> So far I can only get it to work when I add the date column as a filter
>> like the code snippet below:
>>
>> XSSFSheet pivotSheet = (XSSFSheet) wb.createSheet("Pivot");
>>
>> XSSFPivotTable pivotTable1 = pivotSheet.createPivotTable(
>> new AreaReference(sheet.getSheetName()+"!A1:"+cellName,
>> SpreadsheetVersion.EXCEL2007),
>> new CellReference("A4"));
>> pivotTable1.addRowLabel(0); // classification
>> pivotTable1.addReportFilter(1); // weekStartDate
>> pivotTable1.addColumnLabel(DataConsolidateFunction.SUM,2,"uniquepages");
>>
>>
>> When I open the workbook in Excel and open the Pivot Table Builder I
>> can drag the weekStartDate from the Filters box to the Columns box and
>> that gives me the result I want, but I have not been able to do it
>> using poi.
>>
>> I tried replacing the addReportFilter with pivotTable1.addDataColumn(1,
>> false);
>>
>> That just removed the filter and only displayed a column for the
>> classification rows and one summation column.
>>
>> When I try pivotTable1.addDataColumn(1, true);
>>
>> The worksheet will not open in Excel. I get an error dialog that says,
>> "Excel could not open seo_traffic_pivot.xlsx because some content is
>> unreadable. Do you want to open and repair this workbook?"
>>
>> It seems like there should be a simple solution, but I have not been
>> able to figure it out. If anybody can point me in the right direction
>> I would appreciate it.
>>
>>
>> Chris
>>
>
Re: Creating pivot table and summation column
Posted by Javen O'Neal <ja...@gmail.com>.
Read these two bugs to make sure that isn't the problem first. I have not
verified either bug yet.
https://bz.apache.org/bugzilla/show_bug.cgi?id=59705
https://bz.apache.org/bugzilla/show_bug.cgi?id=59368
On Aug 11, 2016 8:30 AM, "Chris Stillwell" <ch...@farecompare.com>
wrote:
> I have a sheet with 4 columns of data that I am wanting to create 2 pivot
> tables with.
> The first column has a classification label.
> The second column has a start of week date.
> The third and fourth columns contain integer values.
>
> I want each pivot table to have a row for each classification and then a
> column for each week start date, under which is the sum of one of the
> integer columns.
> So far I can only get it to work when I add the date column as a filter
> like the code snippet below:
>
> XSSFSheet pivotSheet = (XSSFSheet) wb.createSheet("Pivot");
>
> XSSFPivotTable pivotTable1 = pivotSheet.createPivotTable(
> new AreaReference(sheet.getSheetName()+"!A1:"+cellName,
> SpreadsheetVersion.EXCEL2007),
> new CellReference("A4"));
> pivotTable1.addRowLabel(0); // classification
> pivotTable1.addReportFilter(1); // weekStartDate
> pivotTable1.addColumnLabel(DataConsolidateFunction.SUM,2,"uniquepages");
>
>
> When I open the workbook in Excel and open the Pivot Table Builder I
> can drag the weekStartDate from the Filters box to the Columns box and
> that gives me the result I want, but I have not been able to do it
> using poi.
>
> I tried replacing the addReportFilter with pivotTable1.addDataColumn(1,
> false);
>
> That just removed the filter and only displayed a column for the
> classification rows and one summation column.
>
> When I try pivotTable1.addDataColumn(1, true);
>
> The worksheet will not open in Excel. I get an error dialog that says,
> "Excel could not open seo_traffic_pivot.xlsx because some content is
> unreadable. Do you want to open and repair this workbook?"
>
> It seems like there should be a simple solution, but I have not been
> able to figure it out. If anybody can point me in the right direction
> I would appreciate it.
>
>
> Chris
>