java - 如何使用 Apache POI 使用自定义列表中的 XDDFDataSource 值创建饼图和条形图

Question

我是 Apache-poi 的新手，我正在开发一个 java 项目，使用 Apache-poi 将数据导出到 Excel 电子表格。目前我可以绘制具有固定值的饼图。
我需要用Apache POI在java中创建一个饼图，使用列表中的系列的自定义值，而不从工作表中读取它。

我尝试过的示例代码如下。

import java.io.FileNotFoundException;
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.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.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


public class PieChartSecond {

    public static void main(String[] args) throws FileNotFoundException, IOException {
        try (XSSFWorkbook wb = new XSSFWorkbook()) {

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

            // 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("Russia("+17098242+")"
);

            cell = row.createCell((short) 1);
            cell.setCellValue("Canada(" + 9984670 +")"
);

            cell = row.createCell((short) 2);
            cell.setCellValue("USA(" +9826675+")"
);

            row = sheet.createRow((short) 1);

            cell = row.createCell((short) 0);
            cell.setCellValue(17098242);

            cell = row.createCell((short) 1);
            cell.setCellValue(9984670);

            cell = row.createCell((short) 2);
            cell.setCellValue(9826675);

            XSSFDrawing drawing = sheet.createDrawingPatriarch();
            XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 4, 7, 20);

            XSSFChart chart = drawing.createChart(anchor);
            chart.setTitleText("Countries");
            chart.setTitleOverlay(false);

            XDDFChartLegend legend = chart.getOrAddLegend();
            legend.setPosition(LegendPosition.TOP_RIGHT);

            XDDFDataSource<String> countries = XDDFDataSourcesFactory.fromStringCellRange(sheet,
                    new CellRangeAddress(0, 0, 0, 2));

            XDDFNumericalDataSource<Double> values = XDDFDataSourcesFactory.fromNumericCellRange(sheet,
                    new CellRangeAddress(1, 1, 0, 2));

//          XDDFChartData data = chart.createData(ChartTypes.PIE, null, null);
            XDDFChartData data = new XDDFPieChartData(chart.getCTChart().getPlotArea().addNewPieChart());
              data.setVaryColors(true);

            data.setVaryColors(true);
            data.addSeries(countries, values);
            chart.plot(data);

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

    }

}

下面是我的Excel快照

问题就在这里，我将值设置为工作表，然后将其提取到图表中。我不想创建一个表，我只是想直接将我的值作为列表插入(因为它可以动态更改)即
我需要从 List<String> 获取它，而不是从单元格中获取值的下面几行国家(国家列表)& List<Double>值并将其设置为 data.addSeries(countries, values)

XDDFDataSource<String> countries = XDDFDataSourcesFactory.fromStringCellRange(sheet,
                    new CellRangeAddress(0, 0, 0, 2));

            XDDFNumericalDataSource<Double> values = XDDFDataSourcesFactory.fromNumericCellRange(sheet,
                    new CellRangeAddress(1, 1, 0, 2));

由于值随着 Db 的变化而不断变化，因此他们有什么方法可以实现这一目标。

Answer 1

XDDFDataSourcesFactory也提供从数组创建数据源。但这主要用于在 Word 或 PowerPoint 中创建图表，这些图表将其数据源保存在嵌入的 Excel 工作表中，该工作表是根据该数组在内部创建的，同时创建 XWPFChart 或 XSLFChart。

电子表格中的 XSSFChart 应从工作表中的单元格获取其图表数据源。也许来自隐藏的工作表，但不是来自数组。

但这当然是可能的。

使用apache poi 4.1.1的示例。

对于饼图:

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

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xddf.usermodel.*;
import org.apache.poi.xddf.usermodel.chart.*;
import org.apache.poi.xssf.usermodel.*;

public class PieChartFromArray {

    public static void main(String[] args) throws IOException {
        try (XSSFWorkbook wb = new XSSFWorkbook()) {
            XSSFSheet sheet = wb.createSheet("barchart");

            XSSFDrawing drawing = sheet.createDrawingPatriarch();
            XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 0, 6, 15);

            XSSFChart chart = drawing.createChart(anchor);
            chart.setTitleText("Chart title");
            chart.setTitleOverlay(false);

            XDDFChartLegend legend = chart.getOrAddLegend();
            legend.setPosition(LegendPosition.RIGHT);

            XDDFDataSource<String> cat = XDDFDataSourcesFactory.fromArray(new String[]{"Russia","Canada","USA"});
            XDDFNumericalDataSource<Double> val = XDDFDataSourcesFactory.fromArray(new Double[]{170d, 99d, 98d});

            //XDDFChartData data = new XDDFPieChartData(chart.getCTChart().getPlotArea().addNewPieChart());
            XDDFChartData data = chart.createData(ChartTypes.PIE, null, null);
            data.setVaryColors(true);
            XDDFChartData.Series series = data.addSeries(cat, val);
            series.setTitle("Series", null);
            chart.plot(data);

            try (FileOutputStream fileOut = new FileOutputStream("ooxml-pie-chart.xlsx")) {
                wb.write(fileOut);
            }
        }
    }
}

对于条形图:

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

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xddf.usermodel.*;
import org.apache.poi.xddf.usermodel.chart.*;
import org.apache.poi.xssf.usermodel.*;

public class BarChartFromArray {

    public static void main(String[] args) throws IOException {
        try (XSSFWorkbook wb = new XSSFWorkbook()) {
            XSSFSheet sheet = wb.createSheet("barchart");

            XSSFDrawing drawing = sheet.createDrawingPatriarch();
            XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 0, 10, 15);

            XSSFChart chart = drawing.createChart(anchor);
            chart.setTitleText("Chart title");
            chart.setTitleOverlay(false);

            XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
            bottomAxis.setTitle("cat");
            XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
            leftAxis.setTitle("val");
            leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);
            leftAxis.setCrossBetween(AxisCrossBetween.BETWEEN);

            XDDFDataSource<String> cat = XDDFDataSourcesFactory.fromArray(new String[]{"Russia","Canada","USA"});
            XDDFNumericalDataSource<Double> val = XDDFDataSourcesFactory.fromArray(new Double[]{170d, 99d, 98d});

            XDDFChartData data = chart.createData(ChartTypes.BAR, bottomAxis, leftAxis);
            data.setVaryColors(false);
            XDDFChartData.Series series = data.addSeries(cat, val);
            series.setTitle("Series", null);
            chart.plot(data);

            XDDFBarChartData bar = (XDDFBarChartData) data;
            bar.setBarDirection(BarDirection.COL);

            try (FileOutputStream fileOut = new FileOutputStream("ooxml-bar-chart.xlsx")) {
                wb.write(fileOut);
            }
        }
    }
}

这些图表将其数据以字符串文字公式形式的数组保存。例如 ={"Russia","Canada","USA"} 和 ={170,99,98}。这是一种糟糕的图表数据存储形式。 Google 表格甚至不会显示这样的图表，该图表不是从工作表而是从此类字符串文字公式获取数据。

所以不要这样做。而是将数据放在表格中。如果数据不可见，请将其放入隐藏工作表中。

示例如何将图表数据放入单独的工作表中，然后可以将其隐藏。此代码也使用 apache poi 4.1.1。

对于饼图:

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.ss.util.CellReference;
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.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 PieChart {

  public static void main(String[] args) throws IOException {

    String[] categories = new String[]{"Russia","Canada","USA"};
    Double[] values = new Double[]{170d, 99d, 98d};

    try (XSSFWorkbook wb = new XSSFWorkbook()) {
      //the sheet for the chart
      XSSFSheet chartSheet = wb.createSheet("piechart");
      //the sheet for the data
      XSSFSheet dataSheet = wb.createSheet("data");
      //maybe hide the data sheet
      //wb.setSheetHidden(1, true);

      Row row;
      Cell cell;
      row = dataSheet.createRow(0);
      cell = row.createCell(1); cell.setCellValue("Series");
      for (int i = 1; i <= categories.length; i++) {
        row = dataSheet.createRow(i);
        cell = row.createCell(0); cell.setCellValue(categories[i-1]);
        cell = row.createCell(1); cell.setCellValue(values[i-1]);
      }

      XSSFDrawing drawing = chartSheet.createDrawingPatriarch();
      XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 0, 6, 15);

      XSSFChart chart = drawing.createChart(anchor);
      chart.setTitleText("Chart title");
      chart.setTitleOverlay(false);

      XDDFChartLegend legend = chart.getOrAddLegend();
      legend.setPosition(LegendPosition.RIGHT);

      XDDFDataSource<String> cat = XDDFDataSourcesFactory.fromStringCellRange(dataSheet,
          new CellRangeAddress(1, categories.length, 0, 0));
      XDDFNumericalDataSource<Double> val = XDDFDataSourcesFactory.fromNumericCellRange(dataSheet,
          new CellRangeAddress(1, categories.length, 1, 1));

      //XDDFChartData data = new XDDFPieChartData(chart.getCTChart().getPlotArea().addNewPieChart());
      XDDFChartData data = chart.createData(ChartTypes.PIE, null, null);
      data.setVaryColors(true);
      XDDFChartData.Series series = data.addSeries(cat, val);
      series.setTitle(dataSheet.getRow(0).getCell(1).getStringCellValue(), new CellReference(dataSheet.getRow(0).getCell(1)));
      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().getPieChartArray(0).getSerArray(0).addNewDPt().addNewIdx().setVal(p);
        chart.getCTChart().getPlotArea().getPieChartArray(0).getSerArray(0).getDPtArray(p)
          .addNewSpPr().addNewSolidFill().addNewSrgbClr().setVal(DefaultIndexedColorMap.getDefaultRGB(p+10));
      }

      // Write the output to a file
      try (FileOutputStream fileOut = new FileOutputStream("ooxml-pie-chart.xlsx")) {
        wb.write(fileOut);
      }
    }
  }
}

对于条形图:

import java.io.FileOutputStream;

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.ss.util.CellReference;
import org.apache.poi.xddf.usermodel.PresetColor;
import org.apache.poi.xddf.usermodel.XDDFColor;
import org.apache.poi.xddf.usermodel.XDDFShapeProperties;
import org.apache.poi.xddf.usermodel.XDDFSolidFillProperties;
import org.apache.poi.xddf.usermodel.chart.AxisCrosses;
import org.apache.poi.xddf.usermodel.chart.AxisPosition;
import org.apache.poi.xddf.usermodel.chart.AxisCrossBetween;
import org.apache.poi.xddf.usermodel.chart.BarDirection;
import org.apache.poi.xddf.usermodel.chart.ChartTypes;
import org.apache.poi.xddf.usermodel.chart.LegendPosition;
import org.apache.poi.xddf.usermodel.chart.XDDFBarChartData;
import org.apache.poi.xddf.usermodel.chart.XDDFCategoryAxis;
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.XDDFValueAxis;
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;

public class BarChart {

  public static void main(String[] args) throws Exception {

    String[] categories = new String[]{"Russia","Canada","USA"};
    Double[] values = new Double[]{170d, 99d, 98d};

    try (XSSFWorkbook wb = new XSSFWorkbook()) {
      //the sheet for the chart
      XSSFSheet chartSheet = wb.createSheet("barchart");
      //the sheet for the data
      XSSFSheet dataSheet = wb.createSheet("data");
      //maybe hide the data sheet
      //wb.setSheetHidden(1, true);

      Row row;
      Cell cell;
      row = dataSheet.createRow(0);
      cell = row.createCell(1); cell.setCellValue("Series");
      for (int i = 1; i <= categories.length; i++) {
        row = dataSheet.createRow(i);
        cell = row.createCell(0); cell.setCellValue(categories[i-1]);
        cell = row.createCell(1); cell.setCellValue(values[i-1]);
      }

      XSSFDrawing drawing = chartSheet.createDrawingPatriarch();
      XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 0, 10, 15);

      XSSFChart chart = drawing.createChart(anchor);
      chart.setTitleText("Chart title");
      chart.setTitleOverlay(false);

      XDDFChartLegend legend = chart.getOrAddLegend();
      legend.setPosition(LegendPosition.RIGHT);

      XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
      bottomAxis.setTitle("cat");

      XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
      leftAxis.setTitle("val");
      leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);
      leftAxis.setCrossBetween(AxisCrossBetween.BETWEEN);

      XDDFDataSource<String> cat = XDDFDataSourcesFactory.fromStringCellRange(dataSheet,
          new CellRangeAddress(1, categories.length, 0, 0));
      XDDFNumericalDataSource<Double> val = XDDFDataSourcesFactory.fromNumericCellRange(dataSheet,
          new CellRangeAddress(1, categories.length, 1, 1));

      XDDFChartData data = chart.createData(ChartTypes.BAR, bottomAxis, leftAxis);
      data.setVaryColors(false);
      XDDFChartData.Series series = data.addSeries(cat, val);
      series.setTitle(dataSheet.getRow(0).getCell(1).getStringCellValue(), new CellReference(dataSheet.getRow(0).getCell(1)));
      solidFillSeries(series, PresetColor.BLUE);
      chart.plot(data);

      XDDFBarChartData bar = (XDDFBarChartData) data;
      bar.setBarDirection(BarDirection.COL);

      // 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);

      try (FileOutputStream fileOut = new FileOutputStream("ooxml-bar-chart.xlsx")) {
        wb.write(fileOut);
      }
    }
  }

  private static void solidFillSeries(XDDFChartData.Series series, PresetColor color) {
    XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(color));
    XDDFShapeProperties properties = series.getShapeProperties();
    if (properties == null) {
      properties = new XDDFShapeProperties();
    }
    properties.setFillProperties(fill);
    series.setShapeProperties(properties);
  }
}