如何为Java生成的Excel透视表数据字段设置条件格式？

一、Apache POI 开源方案 #

Apache POI目前没有直接操作透视表条件格式的高层API，但可以通过操作底层XML来实现——Excel的透视表条件格式存储在CTPivotTableDefinition的<conditionalFormatting>节点中，具体代码示例如下：

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.*;

import java.io.FileOutputStream;
import java.io.IOException;

public class PivotTableConditionalFormat {
    public static void main(String[] args) throws IOException {
        // 创建工作簿和工作表
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet dataSheet = workbook.createSheet("Data");
        XSSFSheet pivotSheet = workbook.createSheet("Pivot");

        // 填充测试数据
        String[] headers = {"Region", "Product", "Sales"};
        String[][] data = {
                {"North", "A", "100"},
                {"North", "B", "200"},
                {"South", "A", "150"},
                {"South", "B", "250"},
                {"East", "A", "300"},
                {"East", "B", "50"}
        };
        Row headerRow = dataSheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
        }
        for (int i = 0; i < data.length; i++) {
            Row row = dataSheet.createRow(i + 1);
            for (int j = 0; j < data[i].length; j++) {
                Cell cell = row.createCell(j);
                if (j == 2) {
                    cell.setCellValue(Integer.parseInt(data[i][j]));
                } else {
                    cell.setCellValue(data[i][j]);
                }
            }
        }

        // 创建透视表
        XSSFPivotTable pivotTable = pivotSheet.createPivotTable(new AreaReference("A1:C7", SpreadsheetVersion.EXCEL2007), new CellReference("A1"), dataSheet);
        pivotTable.addRowLabel(0); // Region字段作为行标签
        pivotTable.addRowLabel(1); // Product字段作为行标签
        XSSFPivotField dataField = pivotTable.addDataColumn(2, true); // Sales作为数据字段
        dataField.setFunction(PivotFieldFunction.SUM);

        // 底层XML操作添加条件格式（销售值大于200时，字体变红、单元格浅红填充）
        CTPivotTableDefinition ctPivot = pivotTable.getCTPivotTableDefinition();
        CTConditionalFormatting ctCF = ctPivot.addNewConditionalFormatting();
        ctCF.setId(1);

        CTCfRule ctRule = ctCF.addNewCfRule();
        ctRule.setType(STCfType.CELL_IS);
        ctRule.setOperator(STComparisonOperator.GT);
        ctRule.addNewFormula().setStringValue("200");

        // 设置字体格式
        CTFont ctFont = ctRule.addNewFont();
        ctFont.setColor(STColorIndex.RED);
        // 设置填充格式
        CTFill ctFill = ctRule.addNewFill();
        CTPatternFill patternFill = ctFill.addNewPatternFill();
        patternFill.setPatternType(STPatternType.SOLID_FOREGROUND);
        patternFill.setFgColor(STColorIndex.LIGHT_RED);

        // 保存文件
        try (FileOutputStream fos = new FileOutputStream("PivotWithCF.xlsx")) {
            workbook.write(fos);
        }
        workbook.close();
    }
}

注意：该方案需要引入POI的ooxml-schemas依赖，需保证与POI主版本兼容（比如POI 5.x对应ooxml-schemas:4.1.2）。

二、Spire.XLS 付费方案 #

Spire.XLS提供了原生的透视表条件格式API，代码更简洁易维护，示例如下：

import com.spire.xls.*;
import com.spire.xls.core.IPivotTable;

import java.awt.*;

public class SpirePivotCF {
    public static void main(String[] args) {
        Workbook workbook = new Workbook();
        Worksheet dataSheet = workbook.getWorksheets().get(0);
        dataSheet.setName("Data");

        // 填充测试数据
        String[] headers = {"Region", "Product", "Sales"};
        String[][] data = {
                {"North", "A", "100"},
                {"North", "B", "200"},
                {"South", "A", "150"},
                {"South", "B", "250"},
                {"East", "A", "300"},
                {"East", "B", "50"}
        };
        for (int i = 0; i < headers.length; i++) {
            dataSheet.getCell(1, i + 1).setValue(headers[i]);
        }
        for (int i = 0; i < data.length; i++) {
            for (int j = 0; j < data[i].length; j++) {
                if (j == 2) {
                    dataSheet.getCell(i + 2, j + 1).setValue(Integer.parseInt(data[i][j]));
                } else {
                    dataSheet.getCell(i + 2, j + 1).setValue(data[i][j]);
                }
            }
        }

        // 创建透视表
        Worksheet pivotSheet = workbook.getWorksheets().add("Pivot");
        IPivotTable pivotTable = pivotSheet.getPivotTables().add("PivotTable", pivotSheet.getCell(1, 1), dataSheet.getCellRange(1, 1, data.length + 1, headers.length));
        pivotTable.getPivotFields().get("Region").setAxis(AxisTypes.Row);
        pivotTable.getPivotFields().get("Product").setAxis(AxisTypes.Row);
        PivotDataField dataField = pivotTable.getDataFields().add(pivotTable.getPivotFields().get("Sales"), "Sum of Sales", SubtotalTypes.Sum);

        // 给数据字段添加条件格式（销售值大于200时字体变红、单元格浅红填充）
        ConditionalFormatWrapper cf = pivotTable.getConditionalFormats().add();
        cf.setRange(pivotTable.getDataRange());
        ConditionalFormatRule rule = cf.getRules().addCellRule();
        rule.setCondition(ConditionType.Greater);
        rule.setFormula1("200");
        rule.getFont().setColor(Color.RED);
        rule.getFill().setForegroundColor(new Color(255, 199, 206));

        // 保存文件
        workbook.saveToFile("SpirePivotCF.xlsx", ExcelVersion.Version2016);
    }
}

Spire.XLS有免费版但存在页数限制，付费版可解锁全部功能。

三、Aspose.Cells 付费方案 #

Aspose.Cells作为成熟的Excel处理库，对透视表条件格式的支持更完善，API设计直观，示例如下：

import com.aspose.cells.*;
import java.awt.*;

public class AsposePivotCF {
    public static void main(String[] args) throws Exception {
        Workbook workbook = new Workbook();
        Worksheet dataSheet = workbook.getWorksheets().get(0);
        dataSheet.setName("Data");

        // 填充测试数据
        String[] headers = {"Region", "Product", "Sales"};
        String[][] data = {
                {"North", "A", "100"},
                {"North", "B", "200"},
                {"South", "A", "150"},
                {"South", "B", "250"},
                {"East", "A", "300"},
                {"East", "B", "50"}
        };
        for (int i = 0; i < headers.length; i++) {
            dataSheet.getCells().get(0, i).putValue(headers[i]);
        }
        for (int i = 0; i < data.length; i++) {
            for (int j = 0; j < data[i].length; j++) {
                if (j == 2) {
                    dataSheet.getCells().get(i + 1, j).putValue(Integer.parseInt(data[i][j]));
                } else {
                    dataSheet.getCells().get(i + 1, j).putValue(data[i][j]);
                }
            }
        }

        // 创建透视表
        Worksheet pivotSheet = workbook.getWorksheets().add("Pivot");
        PivotTable pivotTable = pivotSheet.getPivotTables().add("PivotTable", "A1", dataSheet.getCells().createRange(0, 0, data.length + 1, headers.length));
        pivotTable.addFieldToArea(PivotFieldType.ROW, "Region");
        pivotTable.addFieldToArea(PivotFieldType.ROW, "Product");
        pivotTable.addFieldToArea(PivotFieldType.DATA, "Sales");

        // 给数据字段添加条件格式（销售值大于200时字体变红、单元格浅红填充）
        FormatConditionCollection fcc = pivotTable.getFormatConditions();
        int index = fcc.addCondition(FormatConditionType.CELL_VALUE);
        FormatCondition fc = fcc.get(index);
        fc.setOperator(OperatorType.GREATER_THAN);
        fc.setFormula1("200");
        Style style = fc.getStyle();
        style.getFont().setColor(Color.RED);
        style.setForegroundColor(new Color(255, 199, 206));

        // 保存文件
        workbook.save("AsposePivotCF.xlsx");
    }
}

Aspose.Cells适合企业级场景，功能覆盖全面，文档和社区支持完善。

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