title: Apache POI XSSFPivotTable setDefaultSubtotal
body: <p>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.</p>
  
  <p>The actual code is a little long and unwieldy to post, but here is a runnable piece with the same problem:</p>
  
  <pre><code>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();
  
  }
  </code></pre>
  
  <p>When I generate the POI version, these are the location and pivotFields elements of the pivotTable XML inside the archive:</p>
  
  <pre><code>&lt;location firstDataCol="1" firstDataRow="1" firstHeaderRow="1" ref="F10:G11" colPageCount="1"/&gt;
  &lt;pivotFields count="5"&gt;
      &lt;pivotField axis="axisRow" showAll="false" compact="true" outline="false" defaultSubtotal="false"&gt;
          &lt;items count="4"&gt;
              &lt;item t="default"/&gt;
              &lt;item t="default"/&gt;
              &lt;item t="default"/&gt;
              &lt;item t="default"/&gt;
          &lt;/items&gt;
      &lt;/pivotField&gt;
      &lt;pivotField dataField="true" showAll="false"/&gt;
      &lt;pivotField dataField="true" showAll="false"/&gt;
      &lt;pivotField axis="axisPage" showAll="false"&gt;
          &lt;items count="4"&gt;
              &lt;item t="default"/&gt;
              &lt;item t="default"/&gt;
              &lt;item t="default"/&gt;
              &lt;item t="default"/&gt;
          &lt;/items&gt;
      &lt;/pivotField&gt;
      &lt;pivotField axis="axisRow" showAll="false" compact="true" outline="false" defaultSubtotal="false"&gt;
          &lt;items count="4"&gt;
              &lt;item t="default"/&gt;
              &lt;item t="default"/&gt;
              &lt;item t="default"/&gt;
              &lt;item t="default"/&gt;
          &lt;/items&gt;
      &lt;/pivotField&gt;
  &lt;/pivotFields&gt;
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>&lt;location ref="F10:I15" firstHeaderRow="1" firstDataRow="2" firstDataCol="2" rowPageCount="1" colPageCount="1"/&gt;
  &lt;pivotFields count="5"&gt;
      &lt;pivotField axis="axisRow" outline="0" showAll="0" defaultSubtotal="0"&gt;
          &lt;items count="3"&gt;
              &lt;item x="0"/&gt;
              &lt;item x="1"/&gt;
              &lt;item x="2"/&gt;
          &lt;/items&gt;
      &lt;/pivotField&gt;
      &lt;pivotField dataField="1" showAll="0"/&gt;
      &lt;pivotField dataField="1" showAll="0"/&gt;
      &lt;pivotField axis="axisPage" showAll="0"&gt;
          &lt;items count="3"&gt;
              &lt;item x="0"/&gt;
              &lt;item x="1"/&gt;
              &lt;item t="default"/&gt;
          &lt;/items&gt;
      &lt;/pivotField&gt;
      &lt;pivotField axis="axisRow" outline="0" showAll="0" defaultSubtotal="0"&gt;
          &lt;items count="2"&gt;
              &lt;item x="0"/&gt;
              &lt;item x="1"/&gt;
          &lt;/items&gt;
      &lt;/pivotField&gt;
  &lt;/pivotFields&gt;
  </code></pre>
  
  <p>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.</p>