Search code examples
javapivotapache-poi

'Row Label' appears as first column header as well as subtotals appear, while generating excel pivot table using apache poi

I am generating an excel with pivot table using apache poi. Pivot gets generated. However, there are two issues observed

Source table:

Employee ID Employee Name Location Salary
100 John London 5000
101 Chris New York 6000
102 Mary Los Angeles 10000
103 Lilly London 6000
104 Joe Toronto 3000
105 Dan New York 7500

Issue:

Generated excel pivot shown here

Issue Description:

  1. The first column header of the pivot observed to be as 'Row Label', instead of the actual pivoted column header (here 'Employee ID'). How to ensure the actual pivoted column header ('Employee ID') appears as first column header
  2. Subtotals appear for each row. How to disable subtotal using apache poi?

Here is my code

public class ExcelPivot {

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

        try (FileInputStream fis = new FileInputStream("employee_template.xlsx");
                Workbook wb = new XSSFWorkbook(fis)) {
            List<Employee> employeeList = Arrays.asList(
                    new Employee("100", "John", "London", 5000.00),
                    new Employee("101", "Chris", "New York", 6000.00),
                    new Employee("102", "Mary", "Los Angeles", 10000.00),
                    new Employee("103", "Lilly", "London", 6000.00),
                    new Employee("104", "Joe", "Toronto", 3000.00),
                    new Employee("105", "Dan", "New York", 7500.00)
            );

            fis.close();
            Sheet sheet = wb.getSheet("Employee");

            for (int r = 1; r <= employeeList.size(); r++) {
                Employee obj = employeeList.get(r - 1);
                Row row = sheet.createRow(r);
                Cell cell1 = row.createCell(0);
                cell1.setCellValue(obj.getEmployeeId());
                Cell cell2 = row.createCell(1);
                cell2.setCellValue(obj.getEmployeeName());
                Cell cell3 = row.createCell(2);
                cell3.setCellValue(obj.getLocation());
                Cell cell4 = row.createCell(3);
                cell4.setCellValue(obj.getSalary());
            }

            XSSFSheet pivotSheet = (XSSFSheet) wb.getSheet("Summary");

            AreaReference source = new AreaReference("A1:D7", SpreadsheetVersion.EXCEL2007);
            CellReference position = new CellReference("B10");

            XSSFPivotTable pivotTable = pivotSheet.createPivotTable(source, position, sheet);
            pivotTable.addRowLabel(0);
            pivotTable.addRowLabel(1);
            pivotTable.addColLabel(2);
            pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 3, "Sum Of Salary");

            pivotTable.getCTPivotTableDefinition().setColGrandTotals(false);

            for (CTPivotField ctPivotField : pivotTable.getCTPivotTableDefinition().getPivotFields()
                    .getPivotFieldList()) {
                ctPivotField.setOutline(false);
                ctPivotField.setSubtotalCaption("");
            }

            System.out.println("Pivot Generated");

            FileOutputStream fileOut = new FileOutputStream("employee_template.xlsx");
            wb.write(fileOut);
            fileOut.close();
            wb.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    static class Employee {
        private String employeeId;
        private String employeeName;
        private String location;
        private double salary;

        public Employee(String employeeId, String employeeName, String location, double salary) {
            super();
            this.employeeId = employeeId;
            this.employeeName = employeeName;
            this.location = location;
            this.salary = salary;
        }

        public String getEmployeeId() {
            return employeeId;
        }

        public String getEmployeeName() {
            return employeeName;
        }

        public String getLocation() {
            return location;
        }

        public double getSalary() {
            return salary;
        }

    }

}

I couldn't find any documentation in Apache poi with respect to this. Also, tried various property settings such as For Issue 1 (Row label issue) I tried this: //pivotSheet.getRow(2).getCell(1).setCellValue("Fund Id"); For Issue 2 (Subtotal issue) I tried this: ctPivotField.setSumSubtotal(false); ctPivotField.setDefaultSubtotal(false); ctPivotField.setVarSubtotal(false);

But, nothing seems to be affecting/changing the generated pivot table. Does any of you have any suggestions to fix this issue?

Solution

  • For the first "problem" you might want using the "Classic PivotTable Layout". Thus

    • 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.

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

    Code:

    ...
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);  
}
..

    For the subtotal problem see Pivot unclear grouping / remove result rows. Using my static void addRowLabel(XSSFPivotTable pivotTable, Sheet dataSheet, AreaReference areaReference, int column, boolean defaultSubtotals) in your code instead of XSSFPivotTable.addRowLabel like so:

    ...
XSSFPivotTable pivotTable = pivotSheet.createPivotTable(source, position, sheet);
//pivotTable.addRowLabel(0);
//pivotTable.addRowLabel(1);
addRowLabel(pivotTable, sheet, source, 0, false);
addRowLabel(pivotTable, sheet, source, 1, false);

pivotTable.addColLabel(2);
        
pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 3, "Sum Of Salary");
...

    results in following:

    enter image description here

    Note: This needs the full jar of all of the schemas poi-ooxml-full-XXX.jar (previously known as ooxml-schemas) according to FAQ-N10025.