使用apache poi和ooxml-schemas实现具有2列的条形图的最简单方法 [英] The easiest way to implement a barchart with 2 columns using apache poi and ooxml-schemas

问题描述

我正在尝试在如下所示的xlsx文件中创建2列的n条形图.

I'm trying to create n bar chart of 2 columns in a xlsx file like below.

但是我很困惑，以了解org.openxmlformats.schemas.drawingml.x2006.chart中的类是如何工作的.

But It's so confuse to me , to understand how the class inside the org.openxmlformats.schemas.drawingml.x2006.chart works.

我已经尝试过了，但是生成文件没有得到我绘制的图表.

I've already tryied but the generate file does not get the chart that I have drawed.

我有此代码:

        Drawing drawing = planilha.createDrawingPatriarch();
        ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 5, 5, 15);

        Chart chart = drawing.createChart(anchor);

        CTChart ctChart = ((XSSFChart)chart).getCTChart();
        CTPlotArea ctPlotArea = ctChart.getPlotArea();
        CTBarChart ctBarChart = ctPlotArea.addNewBarChart();
        CTBoolean ctBoolean = ctBarChart.addNewVaryColors();
        ctBoolean.setVal(true);
        ctBarChart.addNewBarDir().setVal(STBarDir.COL);

           CellRangeAddress rangeAreas =  new CellRangeAddress(1,3,1,1);
        CellRangeAddress rangeTotais = new CellRangeAddress(1,3,5,5);

        CTBarSer ctBarSer = ctBarChart.addNewSer();
        CTSerTx ctSerTx = ctBarSer.addNewTx();
        CTStrRef ctStrRef = ctSerTx.addNewStrRef();
        ctStrRef.setF("Gráfico!"+rangeAreas.formatAsString());

        CTNumDataSource ctNumDataSource = ctBarSer.addNewVal();
        CTNumRef ctNumRef = ctNumDataSource.addNewNumRef();
        ctNumRef.setF("Gráfico!"+rangeTotais.formatAsString());
        //this code below I copied of an example and I don't know what is necessary 
        ctBarChart.addNewAxId().setVal(123456);
        ctBarChart.addNewAxId().setVal(123457);

        CTCatAx ctCatAx = ctPlotArea.addNewCatAx(); 
        ctCatAx.addNewAxId().setVal(123456); //id of the cat axis
        CTScaling ctScaling = ctCatAx.addNewScaling();
        ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);
        ctCatAx.addNewDelete().setVal(false);
        ctCatAx.addNewAxPos().setVal(STAxPos.B);
        ctCatAx.addNewCrossAx().setVal(123457); //id of the val axis
        ctCatAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);

        CTValAx ctValAx = ctPlotArea.addNewValAx(); 
        ctValAx.addNewAxId().setVal(123457); //id of the val axis
        ctScaling = ctValAx.addNewScaling();
        ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);
        ctValAx.addNewDelete().setVal(false);
        ctValAx.addNewAxPos().setVal(STAxPos.L);
        ctValAx.addNewCrossAx().setVal(123456); //id of the cat axis
        ctValAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);

推荐答案

使用最后一个稳定版本apache poi 4.0.0，可以创建条形图，而无需使用底层的底层bean.为此打包org.apache.poi .xddf.usermodel .

Using apache poi 4.0.0, the last stable version, creating bar charts is possible without using the low level underlying beans. For this Package org.apache.poi.xddf.usermodel is used.

到目前为止，XDDF的某些部分仍存在错误.因此，我们需要修理一些东西.但是，尽管如此，我们应该使用这些类，而不是底层的底层bean.

Some parts of the XDDF stuff are buggy until now. So we need to repair something. But nevertheless we should using those classes rather than the low level underlying beans.

您所需的示例:

来源:

代码:

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

import org.apache.poi.ss.usermodel.WorkbookFactory;
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.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.AxisCrossBetween;
import org.apache.poi.xddf.usermodel.chart.AxisPosition;
import org.apache.poi.xddf.usermodel.chart.ChartTypes;
import org.apache.poi.xddf.usermodel.chart.LegendPosition;
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 ExcelBarChartFromExistingData {

  public static void main(String[] args) throws IOException {
    try (XSSFWorkbook wb = (XSSFWorkbook)WorkbookFactory.create(new FileInputStream("Workbook.xlsx"))) {
      XSSFSheet sheet = wb.getSheet("Sheet1");

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

      XSSFChart chart = drawing.createChart(anchor);
      XDDFChartLegend legend = chart.getOrAddLegend();
      legend.setPosition(LegendPosition.TOP_RIGHT);

      // Use a category axis for the bottom axis.
      XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
      XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
      leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);
      leftAxis.setCrossBetween(AxisCrossBetween.BETWEEN);

      XDDFDataSource<String> xs = XDDFDataSourcesFactory.fromStringCellRange(sheet,
          new CellRangeAddress(1, 3, 1, 1));
      XDDFNumericalDataSource<Double> ys = XDDFDataSourcesFactory.fromNumericCellRange(sheet,
          new CellRangeAddress(1, 3, 5, 5));

      XDDFChartData data = chart.createData(ChartTypes.BAR, bottomAxis, leftAxis);
      data.addSeries(xs, ys);
      chart.plot(data);

      //Since XDDF stuff is buggy until now, we need to repair something.

      //repairing set the kind of bar char, either bar chart or column chart:
      if (chart.getCTChart().getPlotArea().getBarChartArray(0).getBarDir() == null) 
       chart.getCTChart().getPlotArea().getBarChartArray(0).addNewBarDir();
      chart.getCTChart().getPlotArea().getBarChartArray(0).getBarDir().setVal(
       org.openxmlformats.schemas.drawingml.x2006.chart.STBarDir.COL);
       //org.openxmlformats.schemas.drawingml.x2006.chart.STBarDir.BAR);

      //repairing telling the axis Ids in bar chart:
      if (chart.getCTChart().getPlotArea().getBarChartArray(0).getAxIdList().size() == 0) {
       chart.getCTChart().getPlotArea().getBarChartArray(0).addNewAxId().setVal(bottomAxis.getId());
       chart.getCTChart().getPlotArea().getBarChartArray(0).addNewAxId().setVal(leftAxis.getId());
      }

      //repairing no Tx when there is no title
      if (chart.getCTChart().getPlotArea().getBarChartArray(0).getSerArray(0).getTx() != null)
       chart.getCTChart().getPlotArea().getBarChartArray(0).getSerArray(0).unsetTx();

      XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(PresetColor.BLUE));
      XDDFChartData.Series firstSeries = data.getSeries().get(0);
      XDDFShapeProperties properties = firstSeries.getShapeProperties();
      if (properties == null) {
        properties = new XDDFShapeProperties();
      }
      properties.setFillProperties(fill);
      firstSeries.setShapeProperties(properties);

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

结果:

要回答您的隐式问题，如何理解org.openxmlformats.schemas.drawingml.x2006.chart中的类:

To answer your implicit question how to understand the classes inside the org.openxmlformats.schemas.drawingml.x2006.chart:

要知道这一点，我们需要知道Excel如何存储其数据. *.xlsx文件只是ZIP存档.因此，我们可以简单地解压缩它们并进行查看.

To get this known we need knowing how Excel stores it's data. The *.xlsx files are simply ZIP archives. So we can simply unzip them and having a look into.

我们找到了XML个文件.例如对于图表/xl/charts/chart1.xml.现在首先我们需要了解XML.

There we find XML files. For charts /xl/charts/chart1.xml for example. Now first we need understanding that XML.

然后，我们需要有关org.openxmlformats.schemas.drawingml.x2006.chart软件包的信息.我们可以下载 ooxml-schemas-1.4-sources.jar ，然后执行此操作的javadoc.现在，我们有一个API文档，用于所有基础bean包含包org.openxmlformats.schemas.drawingml.x2006.chart.

Then we need information about the org.openxmlformats.schemas.drawingml.x2006.chart package. We can download ooxml-schemas-1.4-sources.jar and then doing javadoc of this. Now we have a API documentation for all the underlying beans inclusive package org.openxmlformats.schemas.drawingml.x2006.chart.

这篇关于使用apache poi和ooxml-schemas实现具有2列的条形图的最简单方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！