Need to enable 'Classic Pivot Table Layout' option in excel using Apache POI JAVA

Question

I have some data in an ExcelSheet, and I am trying to create a pivot table with multiple column labels and row labels. I need to generate the pivot with Classic Pivot Table Layout option enabled. I tried doing it using the below code , but this does not seem to work.

 org.openxmlformats.schemas.spreadsheetml.x2006.main.CTExtensionList extList = pivotTable.getCTPivotTableDefinition().addNewExtLst();
           org.openxmlformats.schemas.spreadsheetml.x2006.main.CTExtension ext = extList.addNewExt();
           String extXML = "<x14:pivotTableDefinition"
                    + " xmlns:x14=\"http://schemas.microsoft.com/office/spreadsheetml/2009/9/main\">"
                    + "<x14:pivotTableStyleInfo showColStripes=\"0\" showRowStripes=\"0\" showLastColumn=\"0\" showRowHeaders=\"1\" showColumnHeaders=\"1\"/></x14:pivotTableDefinition>";
          System.out.println(extXML);
           org.apache.xmlbeans.XmlObject xmlObject = org.apache.xmlbeans.XmlObject.Factory.parse(extXML);          
                ext.set(xmlObject);
                ext.setUri("{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}");

Is there a way to enable Classic Pivot Table Layout in excel using APACHE POI/ooxml .

score 2 · Accepted Answer · answered Feb 22 '23 at 07:37

The "Classic PivotTable Layout" consists of following settings:

In Pivot Table Definition:

Set Grid Drop Zones true to show the drop zones for pivot fields.
Set Compact and Compact Data to false so the whole layout is not a compact layout.
Microsoft Excel itself also sets Multiple Field Filters to false and Item Print Titles to true. But this seems not really necessary.

In Pivot Fields:

For each pivot field set Compact false and set Outline false. Each pivot field layout neither needs to be compact nor outline.

Following complete example shows this. It results in a privot table using the "Classic PivotTable Layout":

import java.io.FileOutputStream;

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

class CreatePivotTable {

 public static void main(String[] args) throws Exception {

  try (Workbook workbook = new XSSFWorkbook(); 
       FileOutputStream fileout = new FileOutputStream("Excel.xlsx") ) {

   Sheet pivotSheet = workbook.createSheet("Pivot");
   Sheet dataSheet = workbook.createSheet("Data");

   Row row;
   Cell cell;
   Object[][] data = new Object[][]{
    new Object[]{"Name", "Country", "Count", "Value"},
    new Object[]{"A", "C1", 2d, 123.56},
    new Object[]{"B", "C1", 4d, 34.56},
    new Object[]{"A", "C2", 1d, 56.78},
    new Object[]{"B", "C2", 7d, 23.45},
    new Object[]{"A", "C1", 3d, 234.56},
    new Object[]{"B", "C1", 5d, 78.90}
   };
   for (int r = 0; r < data.length; r++) {
    row = dataSheet.createRow(r);
    Object[] rowData = data[r];
    for (int c = 0; c < rowData.length; c++) {
     cell = row.createCell(c);
     if (rowData[c] instanceof String) {
      cell.setCellValue((String)rowData[c]);
     } else if (rowData[c] instanceof Double) {
      cell.setCellValue((Double)rowData[c]);
     }
    }
   }

   AreaReference areaReference = new AreaReference(
    new CellReference(0,0),
    new CellReference(data.length-1, data[0].length-1),
    SpreadsheetVersion.EXCEL2007);

   XSSFPivotTable pivotTable = ((XSSFSheet)pivotSheet).createPivotTable(areaReference, new CellReference("A4"), dataSheet);

   pivotTable.addRowLabel(0);
   pivotTable.addRowLabel(1);
   pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 2, "Sum of count");
   pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 3, "Sum of value");

   pivotTable.getCTPivotTableDefinition().getPivotTableStyleInfo().setName("PivotStyleDark7");
   
   pivotTable.getCTPivotTableDefinition().setCompact(false);
   pivotTable.getCTPivotTableDefinition().setCompactData(false);
   pivotTable.getCTPivotTableDefinition().setGridDropZones(true);
   //pivotTable.getCTPivotTableDefinition().setMultipleFieldFilters(false);
   //pivotTable.getCTPivotTableDefinition().setItemPrintTitles(true);
   for (org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPivotField pivotField : pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldList()) {
    pivotField.setCompact(false);  
    pivotField.setOutline(false);  
   }

   workbook.write(fileout);

  }

 }
}

added a similar question, but more about grouping/resulting https://stackoverflow.com/questions/76411982/pivot-unclear-grouping — cilap, Jun 06 '23 at 06:54

Need to enable 'Classic Pivot Table Layout' option in excel using Apache POI JAVA

1 Answers1

Linked