poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Chris Stillwell <chris.stillw...@farecompare.com>
Subject Creating pivot table and summation column
Date Thu, 11 Aug 2016 15:30:35 GMT
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

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message