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
