如何通过Apache POI修改基于数据透视表的饼图各元素颜色？

问题描述 #

已通过Java的Apache POI成功创建基于数据透视表的饼图，但无法按照需求修改饼图各元素的自定义颜色，尝试添加数据系列等方法均未生效。

现有代码 #

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

import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataConsolidateFunction;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xddf.usermodel.chart.ChartTypes;
import org.apache.poi.xddf.usermodel.chart.LegendPosition;
import org.apache.poi.xddf.usermodel.chart.XDDFChartData;
import org.apache.poi.xddf.usermodel.chart.XDDFChartLegend;
import org.apache.poi.xddf.usermodel.chart.XDDFDataSource;
import org.apache.poi.xddf.usermodel.chart.XDDFDataSourcesFactory;
import org.apache.poi.xddf.usermodel.chart.XDDFNumericalDataSource;
import org.apache.poi.xddf.usermodel.chart.XDDFPieChartData;
import org.apache.poi.xssf.usermodel.XSSFChart;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFPivotTable;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openxmlformats.schemas.drawingml.x2006.chart.STDLblPos;
import org.openxmlformats.schemas.drawingml.x2006.main.STPresetColorVal;
import org.openxmlformats.schemas.drawingml.x2006.main.STRectAlignment;
import org.openxmlformats.schemas.drawingml.x2006.main.STSchemeColorVal;
import org.openxmlformats.schemas.drawingml.x2006.main.STSystemColorVal;

public class PivotPieChart {

    public static void main(String[] args) throws FileNotFoundException, IOException {
        pieChart();
    }

    public static void pieChart() throws FileNotFoundException, IOException {
        try (XSSFWorkbook wb = new XSSFWorkbook()) {

            XSSFSheet sheet = wb.createSheet("PivotPieChart");

            // Create row and put some cells in it. Rows and cells are 0 based.
            Row row = sheet.createRow((short) 0);
            Cell cell = row.createCell((short) 0);
            cell.setCellValue("Letters");
            cell = row.createCell((short) 1);
            cell.setCellValue("Countries");
            cell = row.createCell((short) 2);
            cell.setCellValue("Data");

            row = sheet.createRow((short) 1);
            cell = row.createCell((short) 0);
            cell.setCellValue("A");
            cell = row.createCell((short) 1);
            cell.setCellValue("Russia");
            cell = row.createCell((short) 2);
            cell.setCellValue(17098242);

            row = sheet.createRow((short) 2);
            cell = row.createCell((short) 0);
            cell.setCellValue("A");
            cell = row.createCell((short) 1);
            cell.setCellValue("Canada");
            cell = row.createCell((short) 2);
            cell.setCellValue(9984670);

            row = sheet.createRow((short) 3);
            cell = row.createCell((short) 0);
            cell.setCellValue("A");
            cell = row.createCell((short) 1);
            cell.setCellValue("USA");
            cell = row.createCell((short) 2);
            cell.setCellValue(9826675);

            row = sheet.createRow((short) 4);
            cell = row.createCell((short) 0);
            cell.setCellValue("B");
            cell = row.createCell((short) 1);
            cell.setCellValue("Australia");
            cell = row.createCell((short) 2);
            cell.setCellValue(9596961);

            row = sheet.createRow((short) 5);
            cell = row.createCell((short) 0);
            cell.setCellValue("B");
            cell = row.createCell((short) 1);
            cell.setCellValue("China");
            cell = row.createCell((short) 2);
            cell.setCellValue(8514877);

            row = sheet.createRow((short) 6);
            cell = row.createCell((short) 0);
            cell.setCellValue("C");
            cell = row.createCell((short) 1);
            cell.setCellValue("Brazil");
            cell = row.createCell((short) 2);
            cell.setCellValue(7741220);

            row = sheet.createRow((short) 7);
            cell = row.createCell((short) 0);
            cell.setCellValue("D");
            cell = row.createCell((short) 1);
            cell.setCellValue("India");
            cell = row.createCell((short) 2);
            cell.setCellValue(3287263);

            AreaReference sourceDataAreaRef = new AreaReference("A1:C7", SpreadsheetVersion.EXCEL2007);
            XSSFPivotTable pivotTable = sheet.createPivotTable(sourceDataAreaRef, new CellReference("A11"));
            pivotTable.addRowLabel(0);
            pivotTable.addRowLabel(1);
            pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 2);


            XSSFSheet pivotSheet = (XSSFSheet)pivotTable.getParentSheet();
            XSSFDrawing drawing = pivotSheet.createDrawingPatriarch();
            XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 4, 2, 10, 20);

            XSSFChart chart = drawing.createChart(anchor);

            String pivotTableName = pivotTable.getCTPivotTableDefinition().getName();
            String qualifiedPivotSourceName = "[" + null + "]" + pivotSheet.getSheetName() + "!" + pivotTableName;
            chart.getCTChartSpace().addNewPivotSource().setName(qualifiedPivotSourceName);

            XDDFChartData data = chart.createData(ChartTypes.PIE, null, null);

            chart.getCTChart ().getPlotArea ().getPieChartArray (0).addNewVaryColors().setVal(true);
            chart.getCTChart ().getPlotArea ().getPieChartArray (0).addNewDLbls().addNewShowSerName().setVal(true);



            // Write output to an excel file
            try (FileOutputStream fileOut = new FileOutputStream("PivotPieChart.xlsx")) {
                wb.write(fileOut);
            }
        }
    }

}

当前效果与期望效果 #

• 当前输出效果：
• 期望效果：

解决方案 #

由于透视图表的数据是动态关联的，常规XDDF API无法直接设置自定义颜色，需直接操作底层XML对象来定义每个饼块的填充色。以下是修改后的完整代码：

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

import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataConsolidateFunction;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xddf.usermodel.chart.ChartTypes;
import org.apache.poi.xddf.usermodel.chart.XDDFChartData;
import org.apache.poi.xssf.usermodel.XSSFChart;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFPivotTable;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class PivotPieChart {

    public static void main(String[] args) throws FileNotFoundException, IOException {
        pieChart();
    }

    public static void pieChart() throws FileNotFoundException, IOException {
        try (XSSFWorkbook wb = new XSSFWorkbook()) {

            XSSFSheet sheet = wb.createSheet("PivotPieChart");

            // 创建数据源
            Row row = sheet.createRow((short) 0);
            Cell cell = row.createCell((short) 0);
            cell.setCellValue("Letters");
            cell = row.createCell((short) 1);
            cell.setCellValue("Countries");
            cell = row.createCell((short) 2);
            cell.setCellValue("Data");

            row = sheet.createRow((short) 1);
            cell = row.createCell((short) 0);
            cell.setCellValue("A");
            cell = row.createCell((short) 1);
            cell.setCellValue("Russia");
            cell = row.createCell((short) 2);
            cell.setCellValue(17098242);

            row = sheet.createRow((short) 2);
            cell = row.createCell((short) 0);
            cell.setCellValue("A");
            cell = row.createCell((short) 1);
            cell.setCellValue("Canada");
            cell = row.createCell((short) 2);
            cell.setCellValue(9984670);

            row = sheet.createRow((short) 3);
            cell = row.createCell((short) 0);
            cell.setCellValue("A");
            cell = row.createCell((short) 1);
            cell.setCellValue("USA");
            cell = row.createCell((short) 2);
            cell.setCellValue(9826675);

            row = sheet.createRow((short) 4);
            cell = row.createCell((short) 0);
            cell.setCellValue("B");
            cell = row.createCell((short) 1);
            cell.setCellValue("Australia");
            cell = row.createCell((short) 2);
            cell.setCellValue(9596961);

            row = sheet.createRow((short) 5);
            cell = row.createCell((short) 0);
            cell.setCellValue("B");
            cell = row.createCell((short) 1);
            cell.setCellValue("China");
            cell = row.createCell((short) 2);
            cell.setCellValue(8514877);

            row = sheet.createRow((short) 6);
            cell = row.createCell((short) 0);
            cell.setCellValue("C");
            cell = row.createCell((short) 1);
            cell.setCellValue("Brazil");
            cell = row.createCell((short) 2);
            cell.setCellValue(7741220);

            row = sheet.createRow((short) 7);
            cell = row.createCell((short) 0);
            cell.setCellValue("D");
            cell = row.createCell((short) 1);
            cell.setCellValue("India");
            cell = row.createCell((short) 2);
            cell.setCellValue(3287263);

            // 创建数据透视表
            AreaReference sourceDataAreaRef = new AreaReference("A1:C7", SpreadsheetVersion.EXCEL2007);
            XSSFPivotTable pivotTable = sheet.createPivotTable(sourceDataAreaRef, new CellReference("A11"));
            pivotTable.addRowLabel(0);
            pivotTable.addRowLabel(1);
            pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 2);

            // 创建饼图
            XSSFSheet pivotSheet = (XSSFSheet)pivotTable.getParentSheet();
            XSSFDrawing drawing = pivotSheet.createDrawingPatriarch();
            XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 4, 2, 10, 20);
            XSSFChart chart = drawing.createChart(anchor);

            // 关联透视表数据源
            String pivotTableName = pivotTable.getCTPivotTableDefinition().getName();
            String qualifiedPivotSourceName = "[" + wb.getPackage().getName() + "]" + pivotSheet.getSheetName() + "!" + pivotTableName;
            chart.getCTChartSpace().addNewPivotSource().setName(qualifiedPivotSourceName);

            XDDFChartData data = chart.createData(ChartTypes.PIE, null, null);

            // 关闭自动配色，避免覆盖自定义颜色
            chart.getCTChart().getPlotArea().getPieChartArray(0).addNewVaryColors().setVal(false);
            // 设置数据标签显示
            chart.getCTChart().getPlotArea().getPieChartArray(0).addNewDLbls().addNewShowSerName().setVal(true);

            // 自定义颜色数组（对应7个饼块的RGB值）
            String[] customColors = {"FF6384", "#36A2EB", "#FFCE56", "#4BC0C0", "#9966FF", "#FF9F40", "#FF6B6B"};

            // 获取饼图对象并设置自定义颜色
            org.openxmlformats.schemas.drawingml.x2006.chart.CTPieChart ctPieChart = chart.getCTChart().getPlotArea().getPieChartArray(0);
            org.openxmlformats.schemas.drawingml.x2006.chart.CTSer ctSer = ctPieChart.addNewSer();

            for (int i = 0; i < customColors.length; i++) {
                org.openxmlformats.schemas.drawingml.x2006.chart.CTPoint ctPoint = ctSer.addNewPt();
                ctPoint.setIdx(i);
                
                // 设置填充颜色
                org.openxmlformats.schemas.drawingml.x2006.chart.CTDPtProperties ctDPtPr = ctPoint.addNewDPtPr();
                org.openxmlformats.schemas.drawingml.x2006.chart.CTShapeProperties ctShapePr = ctDPtPr.addNewSpPr();
                org.openxmlformats.schemas.drawingml.x2006.main.CTSolidColorFillProperties ctSolidFill = ctShapePr.addNewSolidFill();
                
                // 处理RGB颜色值
                String color = customColors[i].replace("#", "");
                org.openxmlformats.schemas.drawingml.x2006.main.CTSRgbColor ctRgb = ctSolidFill.addNewSrgbClr();
                ctRgb.setVal(color.getBytes());
            }

            // 写入文件
            try (FileOutputStream fileOut = new FileOutputStream("PivotPieChart.xlsx")) {
                wb.write(fileOut);
            }
        }
    }
}

关键修改说明 #

1. 关闭了自动配色功能（VaryColors.setVal(false)），防止系统自动颜色覆盖自定义设置
2. 通过底层XML对象CTPieChart和CTSer为每个饼块定义单独的填充颜色
3. 使用RGB字符串直接设置每个数据点的颜色值，可根据需求调整customColors数组

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