XSSFClientAnchor 中的 Apache poi 多个圆环图-解网

问：

我正在编写一个代码，该代码在 excel 文件中创建多个 DoughnutChart。我可以创建多个圆环图并使用多个锚点将它们并排放置。现在我有两个问题

值不显示在 DoughnutChart 上。
每个锚点都有自己的边界。对于多个图表，我需要使用多个 Anchore.I 不希望这些图表被边框分隔。我的任务是所有图表都应该并排，一个边框，没有分隔。而且我认为可能无法在单个锚点中创建多个图表。

public String picChart7（）抛出 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);
        anchor1.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE);

        XSSFChart chart1 = drawing.createChart(anchor1);
        chart1.setTitleText("chart1");
        chart1.setTitleOverlay(false);
        //chart1.createValueAxis(AxisPosition.TOP);

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

        XDDFChartLegend legend2 = chart2.getOrAddLegend();
        legend2.setPosition(LegendPosition.BOTTOM);
        
        //{1.5,2.5,3.5,4.5,5.5,6.5,7.6,8.5,9.5};
        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);

        // Do not auto delete the title; is necessary for showing title in Calc
        
        // Data point colors; is necessary for showing data points in Calc
        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));//get custom coloring
        }
        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));//get custom coloring
        }
        
        chart1.plot(data1);
        chart2.plot(data2);

        // Write the output to a file
        try (FileOutputStream fileOut = new FileOutputStream("picChart7.xlsx")) {
            wb.write(fileOut);
        }
    }
    return null;
}

这是我的代码。它创建一个带有多个圆环图的 excel 文件。如果您不明白我的问题，请告诉我。我是Apache POI的新手。提前致谢。

java excel spring-boot apache-poi

答：

2赞 Axel Richter 11/10/2023 #1

若要在饼图或圆环图的段中显示值，必须在图表中设置数据标签的设置。

让它成为图表，图表是一个甜甜圈图，然后：XSSFChart chart

// Add data labels
if (!chart.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).isSetDLbls()) {
    chart.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).addNewDLbls();
}
chart.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).getDLbls().addNewShowVal().setVal(true);
chart.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).getDLbls().addNewShowSerName().setVal(false);
chart.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).getDLbls().addNewShowCatName().setVal(false);
chart.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).getDLbls().addNewShowPercent().setVal(false);
chart.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).getDLbls().addNewShowLegendKey().setVal(false);

并删除图表空间代码周围的边框，如下所示：

// chart area (chartspace) without border line
chart.getCTChartSpace().addNewSpPr().addNewLn().addNewNoFill();

这会将没有填充的线设置为图表空间的形状属性。

完整的示例，可使用当前的 Apache POI 5.2.4。

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

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
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.XDDFDoughnutChartData;
import org.apache.poi.xddf.usermodel.chart.ChartTypes;
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.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.DefaultIndexedColorMap;

public class DoughnutChartsXDDF {
    
  static XSSFChart createDoughnutChart(XSSFSheet sheet, String[] categories, Number[] values, String titleText, int col1, int row1, int col2, int row2) {
    XSSFDrawing drawing = sheet.createDrawingPatriarch();
    XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, col1, row1, col2, row2);

    XSSFChart chart = drawing.createChart(anchor);
    chart.setTitleText(titleText);
    chart.setTitleOverlay(false);
    XDDFChartLegend legend = chart.getOrAddLegend();
    legend.setPosition(LegendPosition.BOTTOM);

    XDDFDataSource<String> cat = XDDFDataSourcesFactory.fromArray(categories);
    XDDFNumericalDataSource<Number> val = XDDFDataSourcesFactory.fromArray(values);

    XDDFDoughnutChartData data = (XDDFDoughnutChartData)chart.createData(ChartTypes.DOUGHNUT, null, null);
    data.setVaryColors(true);
    data.setHoleSize(50);
    XDDFChartData.Series series = data.addSeries(cat, val);
    chart.plot(data);

    // Do not auto delete the title; is necessary for showing title in Calc
    if (chart.getCTChart().getAutoTitleDeleted() == null) chart.getCTChart().addNewAutoTitleDeleted();
    chart.getCTChart().getAutoTitleDeleted().setVal(false);

    // Data point colors; is necessary for showing data points in Calc
    int pointCount = series.getCategoryData().getPointCount(); 
    for (int p = 0; p < pointCount; p++) {
      chart.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).addNewDPt().addNewIdx().setVal(p);
      chart.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).getDPtArray(p)
        .addNewSpPr().addNewSolidFill().addNewSrgbClr().setVal(DefaultIndexedColorMap.getDefaultRGB(p+10));
    }
    
    // Add data labels
    if (!chart.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).isSetDLbls()) {
        chart.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).addNewDLbls();
    }
    chart.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).getDLbls().addNewShowVal().setVal(true);
    chart.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).getDLbls().addNewShowSerName().setVal(false);
    chart.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).getDLbls().addNewShowCatName().setVal(false);
    chart.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).getDLbls().addNewShowPercent().setVal(false);
    chart.getCTChart().getPlotArea().getDoughnutChartArray(0).getSerArray(0).getDLbls().addNewShowLegendKey().setVal(false);    
    
    // chart area (chartspace) without border line
    chart.getCTChartSpace().addNewSpPr().addNewLn().addNewNoFill();

    return chart;
  }   
  
  public static void main(String[] args) throws IOException {
    try (XSSFWorkbook wb = new XSSFWorkbook()) {
      XSSFSheet sheet = wb.createSheet("doughnutChart");
      
      String[] categories = new String[]{"one","two","three","four","five","six","seven","eight","nine","ten"};
      Number[] values = new Number[]{ 11L,2L,3L,4L,5L,6L,7L,8L,9L,10L };
      XSSFChart chart1 = createDoughnutChart(sheet, categories, values, "Doughnut-Chart 1", 0, 4, 4, 20);

      categories = new String[]{"one","two"};
      values = new Number[]{ 60L,40L };
      XSSFChart chart2 = createDoughnutChart(sheet, categories, values, "Doughnut-Chart 2", 4, 4, 8, 20);
       
      // Write the output to a file
      try (FileOutputStream fileOut = new FileOutputStream("ooxml-doughnut-chart.xlsx")) {
        wb.write(fileOut);
      }
    }
  }
}

0赞 raymax 11/10/2023

嗨@Axel里希特。您的解决方案对我有用。谢谢。

上一个：添加更多标签时，Apache POI 图表位置垂直变化

下一个：NPOI 在工作表数据验证中读取错误的公式值

XSSFClientAnchor 中的 Apache poi 多个圆环图

Apache poi multiple Doughnut Chart in XSSFClientAnchor

评论

评论