Apache POI生成多XSSF环形图的两个问题求助

问题说明 #

使用Apache POI在Excel中生成多个环形图，已通过XSSFClientAnchor实现并排摆放，但存在两个问题：

• 环形图切片未显示数值标签；
• 每个图表对应独立边框，需将并排图表合并为单一边框，且无法在单个Anchor中创建多图表。

原始实现代码 #

public String picChart7() throws IOException {

    try (XSSFWorkbook wb = new XSSFWorkbook()) {
        XSSFSheet sheet = wb.createSheet("doughnutChart");
        sheet.setDisplayGridlines(false);
        final int NUM_OF_ROWS = 2;
        final int NUM_OF_COLUMNS = 10;

        XSSFDrawing drawing = sheet.createDrawingPatriarch();
        XSSFClientAnchor anchor1 = drawing.createAnchor(0, 0, 0, 0, 1, 5, 4, 28);
        anchor1.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE);
        anchor1.getTo().setCol(4);
        XSSFClientAnchor anchor2 = drawing.createAnchor(0, 0, 0, 0, 4, 5, 7, 28);
        anchor2.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE);

        XSSFChart chart1 = drawing.createChart(anchor1);
        chart1.setTitleText("chart1");
        chart1.setTitleOverlay(false);

        XSSFChart chart2 = drawing.createChart(anchor2);
        chart2.setTitleText("chart2");
        chart2.setTitleOverlay(false);
        
        XDDFChartLegend legend1 = chart1.getOrAddLegend();
        legend1.setPosition(LegendPosition.BOTTOM);

        XDDFChartLegend legend2 = chart2.getOrAddLegend();
        legend2.setPosition(LegendPosition.BOTTOM);
        
        String[] stringArray1 = new String[]{"one","two","three","four","five","six","seven","eight","nine","ten"};
        XDDFDataSource<String> stringValue1 = XDDFDataSourcesFactory.fromArray(stringArray1);
        Long[] longArray1 = new Long[]{ 11L,2L,3L,4L,5L,6L,7L,8L,9L,10L };
        XDDFNumericalDataSource<Long> longValue1 = XDDFDataSourcesFactory.fromArray(longArray1);

        String[] stringArray2 = new String[]{"one","two"};
        XDDFDataSource<String> stringValue2 = XDDFDataSourcesFactory.fromArray(stringArray2);
        Long[] longArray2 = new Long[]{ 60L,40L };
        XDDFNumericalDataSource<Long> longValue2 = XDDFDataSourcesFactory.fromArray(longArray2);
        
        XDDFDoughnutChartData data1 = new XDDFDoughnutChartData(chart1, chart1.getCTChart().getPlotArea().addNewDoughnutChart());
        data1.setVaryColors(true);
        data1.setHoleSize((short) 50);
        data1.setFirstSliceAngle(10);

        XDDFDoughnutChartData data2 = new XDDFDoughnutChartData(chart2, chart2.getCTChart().getPlotArea().addNewDoughnutChart());
        data2.setVaryColors(true);
        data2.setHoleSize((short) 50);
        data2.setFirstSliceAngle(10);
        
        XDDFChartData.Series series1 = data1.addSeries(stringValue1, longValue1);
        XDDFChartData.Series series2 = data2.addSeries(stringValue2, longValue2);

        // 设置数据点颜色
        int pointCount1 = series1.getCategoryData().getPointCount();
        for (int p = 0; p < pointCount1; p++) {
            chart1.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).addNewDPt().addNewIdx().setVal(p);
            chart1.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).getDPtArray(p)
                    .addNewSpPr().addNewSolidFill().addNewSrgbClr().setVal(getColor(p));
        }
        int pointCount2 = series2.getCategoryData().getPointCount();
        for (int p = 0; p < pointCount2; p++) {
            chart2.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).addNewDPt().addNewIdx().setVal(p);
            chart2.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).getDPtArray(p)
                    .addNewSpPr().addNewSolidFill().addNewSrgbClr().setVal(getColor(p));
        }
        
        chart1.plot(data1);
        chart2.plot(data2);

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

问题解决 #

1. 显示环形图数值标签 #

通过操作CTSer的DLbls属性，给每个数据系列开启数值显示：

在chart1.plot(data1);和chart2.plot(data2);之前，添加以下代码：

// 给chart1添加数值标签
CTSer ctSer1 = chart1.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0);
CTDLbls ctdLbls1 = ctSer1.addNewDLbls();
ctdLbls1.setShowVal(true); // 显示数值
ctdLbls1.setShowSerName(false); // 隐藏系列名
ctdLbls1.setShowCatName(false); // 隐藏分类名

// 给chart2添加数值标签
CTSer ctSer2 = chart2.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0);
CTDLbls ctdLbls2 = ctSer2.addNewDLbls();
ctdLbls2.setShowVal(true);
ctdLbls2.setShowSerName(false);
ctdLbls2.setShowCatName(false);

2. 合并图表边框为单一框 #

由于每个图表对应独立的绘图对象，无法直接合并边框，提供两种可行方案：

方案一：隐藏图表边框，添加外层统一边框

隐藏两个图表的原有边框，然后创建一个覆盖整个区域的矩形形状作为统一边框：

// 隐藏chart1和chart2的边框
chart1.getCTChartSpace().addNewSpPr().addNewLn().addNewNoFill();
chart2.getCTChartSpace().addNewSpPr().addNewLn().addNewNoFill();

// 创建覆盖两个图表的矩形边框
XSSFClientAnchor borderAnchor = drawing.createAnchor(0, 0, 0, 0, 1, 5, 7, 28);
XSSFSimpleShape borderShape = drawing.createSimpleShape(borderAnchor);
borderShape.setShapeType(ShapeTypes.RECTANGLE);
// 设置边框样式：黑色实线，宽度1pt
borderShape.setLineStyle(LineStyle.SOLID);
borderShape.setLineWidth(1.0);
borderShape.setLineColor(new XSSFColor(new byte[]{0,0,0}, null));
// 取消填充，避免遮挡图表
borderShape.setNoFill(true);

方案二：将多个环形图放在同一个图表中

在单个XSSFChart中添加多个环形图，通过手动布局调整位置实现并排，天然共享一个图表边框：

// 创建单个图表和覆盖整个区域的Anchor
XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 1, 5, 7, 28);
XSSFChart chart = drawing.createChart(anchor);
chart.setTitleText("Combined Charts");
chart.setTitleOverlay(false);

// 添加第一个环形图，布局在左侧
CTDoughnutChart ctDoughnut1 = chart.getCTChart().getPlotArea().addNewDoughnutChart();
XDDFDoughnutChartData data1 = new XDDFDoughnutChartData(chart, ctDoughnut1);
data1.setVaryColors(true);
data1.setHoleSize((short) 50);
data1.setFirstSliceAngle(10);
// 设置左侧环形图的布局位置和大小
CTLayout ctLayout1 = ctDoughnut1.addNewPlotArea().addNewLayout().addNewManualLayout();
ctLayout1.addNewX().setVal(0.0);
ctLayout1.addNewY().setVal(0.0);
ctLayout1.addNewW().setVal(0.45); // 宽度占45%
ctLayout1.addNewH().setVal(1.0);

// 添加第二个环形图，布局在右侧
CTDoughnutChart ctDoughnut2 = chart.getCTChart().getPlotArea().addNewDoughnutChart();
XDDFDoughnutChartData data2 = new XDDFDoughnutChartData(chart, ctDoughnut2);
data2.setVaryColors(true);
data2.setHoleSize((short) 50);
data2.setFirstSliceAngle(10);
// 设置右侧环形图的布局位置和大小
CTLayout ctLayout2 = ctDoughnut2.addNewPlotArea().addNewLayout().addNewManualLayout();
ctLayout2.addNewX().setVal(0.55); // 从55%位置开始
ctLayout2.addNewY().setVal(0.0);
ctLayout2.addNewW().setVal(0.45);
ctLayout2.addNewH().setVal(1.0);

// 添加系列、设置颜色、数据标签等操作和之前逻辑一致
XDDFChartData.Series series1 = data1.addSeries(stringValue1, longValue1);
XDDFChartData.Series series2 = data2.addSeries(stringValue2, longValue2);

// 设置数据点颜色...（省略原有颜色设置代码）

// 添加数值标签...（省略原有标签设置代码）

chart.plot(data1);
chart.plot(data2);

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