使用 apache poi 和 ooxml-schemas 实现具有 2 列的条形图的最简单方法 [英] The easiest way to implement a barchart with 2 columns using apache poi and ooxml-schemas
本文介绍了使用 apache poi 和 ooxml-schemas 实现具有 2 列的条形图的最简单方法的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在尝试在如下所示的 xlsx 文件中创建 2 列的 n 个条形图.
但是我很困惑，要了解 org.openxmlformats.schemas.drawingml.x2006.chart 中的类是如何工作的.
我已经试过了，但是生成文件没有得到我绘制的图表.
我有这个代码:
绘图绘图 = planilha.createDrawingPatriarch();ClientAnchor 锚点 =drawing.createAnchor(0, 0, 0, 0, 0, 5, 5, 15);图表图表=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());//下面的这段代码我复制了一个例子，我不知道什么是必要的ctBarChart.addNewAxId().setVal(123456);ctBarChart.addNewAxId().setVal(123457);CTCatAx ctCatAx = ctPlotArea.addNewCatAx();ctCatAx.addNewAxId().setVal(123456);//猫轴的idCTScaling ctScaling = ctCatAx.addNewScaling();ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);ctCatAx.addNewDelete().setVal(false);ctCatAx.addNewAxPos().setVal(STAxPos.B);ctCatAx.addNewCrossAx().setVal(123457);//val轴的idctCatAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);CTValAx ctValAx = ctPlotArea.addNewValAx();ctValAx.addNewAxId().setVal(123457);//val轴的idctScaling = ctValAx.addNewScaling();ctScaling.addNewOrientation().setVal(STOrientation.MIN_MAX);ctValAx.addNewDelete().setVal(false);ctValAx.addNewAxPos().setVal(STAxPos.L);ctValAx.addNewCrossAx().setVal(123456);//猫轴的idctValAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO); 解决方案
使用 apache poi 4.0.0，最后一个稳定版本，可以在不使用底层底层 bean 的情况下创建条形图.对于这个
代码:
import java.io.FileOutputStream;导入 java.io.FileInputStream;导入 java.io.IOException;导入 org.apache.poi.ss.usermodel.WorkbookFactory;导入 org.apache.poi.ss.usermodel.Cell;导入 org.apache.poi.ss.usermodel.Row;导入 org.apache.poi.ss.util.CellRangeAddress；导入 org.apache.poi.xddf.usermodel.PresetColor;导入 org.apache.poi.xddf.usermodel.XDDFColor;导入 org.apache.poi.xddf.usermodel.XDDFShapeProperties;导入 org.apache.poi.xddf.usermodel.XDDFSolidFillProperties;导入 org.apache.poi.xddf.usermodel.chart.AxisCrosses；导入 org.apache.poi.xddf.usermodel.chart.AxisCrossBetween；导入 org.apache.poi.xddf.usermodel.chart.AxisPosition;导入 org.apache.poi.xddf.usermodel.chart.ChartTypes；导入 org.apache.poi.xddf.usermodel.chart.LegendPosition;导入 org.apache.poi.xddf.usermodel.chart.XDDFCategoryAxis;导入 org.apache.poi.xddf.usermodel.chart.XDDFChartData;导入 org.apache.poi.xddf.usermodel.chart.XDDFChartLegend；导入 org.apache.poi.xddf.usermodel.chart.XDDFDataSource;导入 org.apache.poi.xddf.usermodel.chart.XDDFDataSourcesFactory;导入 org.apache.poi.xddf.usermodel.chart.XDDFNumericalDataSource；导入 org.apache.poi.xddf.usermodel.chart.XDDFValueAxis;导入 org.apache.poi.xssf.usermodel.XSSFChart;导入 org.apache.poi.xssf.usermodel.XSSFClientAnchor;导入 org.apache.poi.xssf.usermodel.XSSFDrawing；导入 org.apache.poi.xssf.usermodel.XSSFSheet;导入 org.apache.poi.xssf.usermodel.XSSFWorkbook；公共类 ExcelBarChartFromExistingData {public static void main(String[] args) 抛出 IOException {尝试 (XSSFWorkbook wb = (XSSFWorkbook)WorkbookFactory.create(new FileInputStream("Workbook.xlsx"))) {XSSFSheet sheet = wb.getSheet("Sheet1");XSSFDrawing 绘图 = sheet.createDrawingPatriarch();XSSFClientAnchor 锚点 =drawing.createAnchor(0, 0, 0, 0, 0, 5, 10, 15);XSSFChart 图表 =drawing.createChart(anchor);XDDFChartLegend 图例 = chart.getOrAddLegend();Legend.setPosition(LegendPosition.TOP_RIGHT);//使用类别轴作为底部轴.XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);leftAxis.setCrossBetween(AxisCrossBetween.BETWEEN);XDDFDataSourcexs = XDDFDataSourcesFactory.fromStringCellRange(sheet,新的 CellRangeAddress(1, 3, 1, 1));XDDFNumericalDataSourceys = XDDFDataSourcesFactory.fromNumericCellRange(sheet,新的 CellRangeAddress(1, 3, 5, 5));XDDFChartData 数据 = chart.createData(ChartTypes.BAR, bottomAxis, leftAxis);data.addSeries(xs, ys);图表(数据)；//由于XDDF的东西到现在为止都是bug，我们需要修复一些东西.//修复设置条形图的种类，条形图或柱形图: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);//修复告诉条形图中的轴ID: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());}//没有标题时修复没有Txif (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 属性 = firstSeries.getShapeProperties();如果(属性 == 空){属性 = 新 XDDFShapeProperties();}properties.setFillProperties(fill);firstSeries.setShapeProperties(properties);//将输出写入文件试试 (FileOutputStream fileOut = new FileOutputStream("WorkbookNew.xlsx")) {wb.write(fileOut);}}}} 结果:
回答你隐含的问题如何理解org.openxmlformats.schemas.drawingml.x2006.chart中的类:
要了解这一点，我们需要知道 Excel 如何存储数据.*.xlsx 文件只是 ZIP 档案.所以我们可以简单地解压缩它们并查看.
在那里我们可以找到 XML 文件.例如，对于图表 /xl/charts/chart1.xml.现在首先我们需要了解 XML.
然后我们需要有关 org.openxmlformats.schemas.drawingml.x2006.chart 包的信息.我们可以下载ooxml-schemas-1.4-sources.jar 然后执行 javadoc 这个.现在我们有一个 API 文档，用于包含所有底层 bean 的包org.openxmlformats.schemas.drawingml.x2006.chart.
I'm trying to create n bar chart of 2 columns in a xlsx file like below.
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.
I have this code:
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);
解决方案
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.
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.
Example for your requirement:
Source:
Code:
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);
}
}
}
}
Result:
To answer your implicit question how to understand the classes inside the org.openxmlformats.schemas.drawingml.x2006.chart:
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.
There we find XML files. For charts /xl/charts/chart1.xml for example. Now first we need understanding that XML.
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屋！
查看全文
