如何在Apache POI数据透视表中重复使用同一列作为列标签与值字段

使用Apache POI创建数据透视表时，将同一列同时配置为**列标签（ColField）和值字段（DataField）**会导致导出的Excel文件提示修复并自动关闭，无法实现类似将"icecream"列同时用于列分组和计数统计的效果。

问题原因 #

当同一列同时用于多个数据透视表区域（列标签和值字段）时，对应的CTPivotField配置需要兼容多种用途。原代码中硬设置pivotField.setAxis(STAxis.AXIS_COL)会限制该字段只能用于列轴，而值字段需要该字段的axis属性支持多轴使用；同时原代码中循环所有列索引生成Items的逻辑错误，应该基于当前列的唯一值生成条目，否则会导致XML结构不符合Excel规范。

解决方案 #

修改自定义的addColumnLabel方法，调整CTPivotField的配置使其支持多轴使用，并修正Items的生成逻辑；之后正常添加值字段即可。

修正后的代码示例 #

调整后的addColumnLabel方法

private void addColumnLabel(XSSFPivotTable pivotTable, Sheet dataSheet, AreaReference areaReference,
                            int column, boolean isColumn) {
    try {
        DataFormatter formatter = new DataFormatter(java.util.Locale.US);
        java.util.TreeSet<String> uniqueItems = new java.util.TreeSet<>(String.CASE_INSENSITIVE_ORDER);
        
        // 收集当前列的所有唯一值
        int startRow = areaReference.getFirstCell().getRow() + 1;
        int endRow = areaReference.getLastCell().getRow() + 1;
        for (int r = startRow; r < endRow; r++) {
            Row row = dataSheet.getRow(r);
            if (row != null) {
                Cell cell = row.getCell(column);
                if (cell != null) {
                    uniqueItems.add(formatter.formatCellValue(cell));
                }
            }
        }

        if (isColumn) {
            CTPivotFields pivotFields = pivotTable.getCTPivotTableDefinition().getPivotFields();
            CTPivotField pivotField;

            // 检查是否已存在该列的PivotField，避免重复创建
            if (column < pivotFields.sizeOfPivotFieldArray()) {
                pivotField = pivotFields.getPivotFieldArray(column);
            } else {
                pivotField = CTPivotField.Factory.newInstance();
                pivotFields.setPivotFieldArray(column, pivotField);
            }

            // 设置为多轴兼容，支持同时作为列标签和值字段
            pivotField.setAxis(STAxis.AXIS_MULTI);
            pivotField.setShowAll(false);

            // 基于唯一值生成Items，替换原错误的循环逻辑
            CTItems items = pivotField.addNewItems();
            int index = 0;
            for (String item : uniqueItems) {
                CTItem ctItem = items.addNewItem();
                ctItem.setT(STItemType.DATA);
                ctItem.setX(index);
                index++;
            }
            items.setCount(items.sizeOfItemArray());

            // 添加到列标签
            CTColFields colFields = pivotTable.getCTPivotTableDefinition().getColFields();
            if (colFields == null) {
                colFields = pivotTable.getCTPivotTableDefinition().addNewColFields();
            }
            colFields.addNewField().setX(column);
            colFields.setCount(colFields.sizeOfFieldArray());
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
}

调用代码

// 先添加列标签
addColumnLabel(pivotTable, dataSheet, areaReference, 0, true);
// 再添加值字段（计数）
pivotTable.addColumnLabel(DataConsolidateFunction.COUNT, 0, "Count of icecream");

关键说明 #

• 多轴兼容：设置pivotField.setAxis(STAxis.AXIS_MULTI)，允许该字段同时用于列轴和数据字段区域。
• 正确生成Items：基于当前列的实际唯一值生成CTItem，并设置类型为STItemType.DATA，符合Excel对文本列的透视表配置规范。
• 复用PivotField：检查是否已存在该列的CTPivotField，避免重复定义导致XML结构冲突。

验证后，导出的Excel文件将不再提示修复，数据透视表会正常显示：列标签为"icecream"的各个唯一值，对应的值区域显示每个值的计数统计，与预期效果一致。

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