java - Apache POI XSSFPivotTable setDefaultSubtotal

Question

I am having an issue with the setDefaultSubtotal method for axisRow pivotFields. When I set the value to false for a RowLabel field Excel doesn't like it. Manually setting things up how I want them in Excel and then saving produces dramatically different XML, too different for me to contemplate fixing behind the scenes. I can't (so far) manipulate the XML to get the POI spreadsheet to open cleanly in Excel.

The actual code is a little long and unwieldy to post, but here is a runnable piece with the same problem:

private static void sample() throws IOException{
    Workbook wb = new XSSFWorkbook();
    String[][] data = new String[][]{{"STATUS","PASSED","VALUE"},{"BLUE","Y","20"},{"RED","N","10"},{"BLUE","N","30"}};

    XSSFSheet sheet = (XSSFSheet) wb.createSheet("data");
    XSSFSheet pivot = (XSSFSheet) wb.createSheet("summary");
    for(String[] dataRow : data){
        XSSFRow row = sheet.createRow(sheet.getPhysicalNumberOfRows());
        for(String dataCell : dataRow){
            XSSFCell cell = row.createCell(row.getPhysicalNumberOfCells());
            cell.setCellValue(dataCell);
        }
    }

    XSSFTable table = sheet.createTable();    
    CTTable cttable = table.getCTTable();
    table.setDisplayName("table");
    cttable.setRef("A1:C4");
    cttable.setId(1);

    CTTableColumns columns = cttable.addNewTableColumns();
    columns.setCount(3);

    int i = 1;
    for (String colName : data[0]){
        CTTableColumn column = columns.addNewTableColumn();
        column.setId(++i);
        column.setName(colName);      
    }

    XSSFPivotTable pivotTable =  pivot.createPivotTable(new AreaReference("A1:C4", SpreadsheetVersion.EXCEL2007), new CellReference("A4"), sheet);

    pivotTable.addRowLabel(0);
    CTPivotField fld = pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldList().get(0);
    fld.setOutline(false);

    //fld.setDefaultSubtotal(false); // uncomment and Excel has problems

    pivotTable.addRowLabel(1);
    fld = pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldList().get(1);
    fld.setOutline(false);

    //fld.setDefaultSubtotal(false); // uncomment and Excel has problems

    pivotTable.addColumnLabel(DataConsolidateFunction.COUNT, 2, "test");

    FileOutputStream fileOut = new FileOutputStream("c:/temp/pivotsample.xlsx");
    wb.write(fileOut);
    wb.close();

}

When I generate the POI version, these are the location and pivotFields elements of the pivotTable XML inside the archive:

<location firstDataCol="1" firstDataRow="1" firstHeaderRow="1" ref="F10:G11" colPageCount="1"/>
<pivotFields count="5">
    <pivotField axis="axisRow" showAll="false" compact="true" outline="false" defaultSubtotal="false">
        <items count="4">
            <item t="default"/>
            <item t="default"/>
            <item t="default"/>
            <item t="default"/>
        </items>
    </pivotField>
    <pivotField dataField="true" showAll="false"/>
    <pivotField dataField="true" showAll="false"/>
    <pivotField axis="axisPage" showAll="false">
        <items count="4">
            <item t="default"/>
            <item t="default"/>
            <item t="default"/>
            <item t="default"/>
        </items>
    </pivotField>
    <pivotField axis="axisRow" showAll="false" compact="true" outline="false" defaultSubtotal="false">
        <items count="4">
            <item t="default"/>
            <item t="default"/>
            <item t="default"/>
            <item t="default"/>
        </items>
    </pivotField>
</pivotFields>

When I open in Excel and allow it to recover the sheet, I then make the changes in Excel to do what I'd like, namely two row labels without subtotals in tabular form, this is what Excel saves:

<location ref="F10:I15" firstHeaderRow="1" firstDataRow="2" firstDataCol="2" rowPageCount="1" colPageCount="1"/>
<pivotFields count="5">
    <pivotField axis="axisRow" outline="0" showAll="0" defaultSubtotal="0">
        <items count="3">
            <item x="0"/>
            <item x="1"/>
            <item x="2"/>
        </items>
    </pivotField>
    <pivotField dataField="1" showAll="0"/>
    <pivotField dataField="1" showAll="0"/>
    <pivotField axis="axisPage" showAll="0">
        <items count="3">
            <item x="0"/>
            <item x="1"/>
            <item t="default"/>
        </items>
    </pivotField>
    <pivotField axis="axisRow" outline="0" showAll="0" defaultSubtotal="0">
        <items count="2">
            <item x="0"/>
            <item x="1"/>
        </items>
    </pivotField>
</pivotFields>

I have tried just about everything, and I understand the Excel format, but it depends on pivotCacheRecords, so I'd end up having to write code to populate that. If there's anyone who can maybe see why this code fails, I'd appreciate a pointer.

Answer 1

See also How to set default value in Apache POI pivot table report filter.

Until now apache poi adds as much pivot field items of type "default" (<item t="default"/>) as rows are present in the data range, if the pivot fields where used as axis fields. This is because they don't want to have a look at the data, and so they are assuming as much different values as rows are in the data.

This is fine because Excel will rebuild its pivot cache while opening. But if we want changing defaults, then this is not fine. Then we must know what items there are.

So we need at least as much items, as are different ones in the data, as numbered items: <item x="0"/><item x="1"/>

And we need to build a cache definition which has shared elements for those items.

Example:

import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;
import org.apache.poi.ss.*;

import java.io.*;

import org.openxmlformats.schemas.spreadsheetml.x2006.main.*;

class PivotTableTest6 {

 public static void main(String[] args) throws IOException{
    Workbook wb = new XSSFWorkbook();
    String[][] data = new String[][]{{"STATUS","PASSED","VALUE"},{"BLUE","Y","20"},{"RED","N","10"},{"BLUE","N","30"}};

    XSSFSheet sheet = (XSSFSheet) wb.createSheet("data");
    XSSFSheet pivot = (XSSFSheet) wb.createSheet("summary");
    for(String[] dataRow : data){
        XSSFRow row = sheet.createRow(sheet.getPhysicalNumberOfRows());
        for(String dataCell : dataRow){
            XSSFCell cell = row.createCell(row.getPhysicalNumberOfCells());
            cell.setCellValue(dataCell);
        }
    }

    XSSFTable table = sheet.createTable();    
    CTTable cttable = table.getCTTable();
    table.setDisplayName("table");
    cttable.setRef("A1:C4");
    cttable.setId(1);

    CTTableColumns columns = cttable.addNewTableColumns();
    columns.setCount(3);

    int i = 1;
    for (String colName : data[0]){
        CTTableColumn column = columns.addNewTableColumn();
        column.setId(++i);
        column.setName(colName);      
    }

    XSSFPivotTable pivotTable =  pivot.createPivotTable(new AreaReference("A1:C4", SpreadsheetVersion.EXCEL2007), new CellReference("A4"), sheet);

    pivotTable.addRowLabel(0);
    CTPivotField fld = pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldList().get(0);
    fld.setOutline(false);

    /*   
    Apache poi adds 4 pivot field items of type "default" (<item t="default"/>) here. 
    This is because there are 4 rows (A1:C4) and, because they don't have a look at the data, 
    they are assuming max 4 different values. This is fine because Excel will rebuild its pivot cache while opening. 

    But if we want changing defaults, then this is not fine. Then we must know what items there are.

    So we need at least as much items, as are different ones in the data, as numbered items: <item x="0"/><item x="1"/> 

    And we must build a cache definition which has shared elements for those items.
    */

    for (i = 0; i < 2; i++) {
     //take the first 2 items as numbered items: <item x="0"/><item x="1"/>
     fld.getItems().getItemArray(i).unsetT();
     fld.getItems().getItemArray(i).setX((long)i);
    }
    for (i = 3; i > 1; i--) {
     //remove further items
     fld.getItems().removeItem(i);
    }
    //set new items count
    fld.getItems().setCount(2);

    //build a cache definition which has shared elements for those items 
    //<sharedItems><s v="Y"/><s v="N"/></sharedItems>
    pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields().getCacheFieldList().get(0).getSharedItems().addNewS().setV("Y");
    pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields().getCacheFieldList().get(0).getSharedItems().addNewS().setV("N");

    fld.setDefaultSubtotal(false);

    pivotTable.addRowLabel(1);
    fld = pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldList().get(1);
    fld.setOutline(false);

    pivotTable.addColumnLabel(DataConsolidateFunction.COUNT, 2, "test");

    FileOutputStream fileOut = new FileOutputStream("pivotsample6.xlsx");
    wb.write(fileOut);
    wb.close();

 }
}