Apache POI生成Excel数据透视表的两类问题求助

问题1：首列表头显示'Row Label'而非实际列名 #

原因是默认的紧凑布局（Compact Layout）会将第一个行标签的表头统一显示为'Row Label'，需要切换为表格布局（Tabular Layout），让每个行标签直接显示源数据的列名。

问题2：无法关闭行小计 #

需要针对每个行标签对应的CTPivotField，明确关闭默认小计配置，而非遍历所有字段设置无效属性。

修改后的完整代码 #

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

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

            // -------------------------- 新增修改部分 --------------------------
            // 1. 切换为表格布局，解决Row Label显示问题
            pivotTable.getCTPivotTableDefinition().getLayout().setType(STLayoutType.TABULAR);

            // 2. 关闭行标签的默认小计
            List<CTPivotField> pivotFields = pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldList();
            // 第0列（Employee ID）对应的透视字段
            CTPivotField empIdField = pivotFields.get(0);
            empIdField.setDefaultSubtotal(false);
            empIdField.getSubtotal().clear();

            // 第1列（Employee Name）对应的透视字段
            CTPivotField empNameField = pivotFields.get(1);
            empNameField.setDefaultSubtotal(false);
            empNameField.getSubtotal().clear();
            // ----------------------------------------------------------------

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

            try (FileOutputStream fileOut = new FileOutputStream("employee_template.xlsx")) {
                wb.write(fileOut);
            }
        } 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;
        }

    }
}

关键修改说明 #

1. 切换表格布局：

   pivotTable.getCTPivotTableDefinition().getLayout().setType(STLayoutType.TABULAR);
   

   紧凑布局会将所有行标签合并到一列，表头显示统一的'Row Label'；表格布局会为每个行标签单独分配列，直接显示源数据的列标题（如'Employee ID'、'Employee Name'）。

2. 关闭行小计：
   针对行标签对应的字段索引（这里是0和1，对应源数据的第0、1列），执行两个操作：

   • setDefaultSubtotal(false)：关闭该字段的默认小计功能
   • getSubtotal().clear()：清空所有已配置的小计类型，确保没有残留的小计规则

内容的提问来源于stack exchange，提问作者Keerthi K