首頁(yè) 猿問使用 apache poi 和...

使用 apache poi 和 ooxml-schemas 實(shí)現(xiàn)具有 2 列的條形圖的最簡(jiǎn)單方法

Java

qq_笑_17 2021-12-22 16:11:15

我正在嘗試在如下所示的 xlsx 文件中創(chuàng)建 2 列的 n 個(gè)條形圖。但是讓我很困惑，要了解里面的類是如何org.openxmlformats.schemas.drawingml.x2006.chart工作的。我已經(jīng)試過(guò)了，但是生成文件沒有得到我繪制的圖表。我有這個(gè)代碼： 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);

查看完整描述

1 回答

胡子哥哥

TA貢獻(xiàn)1825條經(jīng)驗(yàn) 獲得超6個(gè)贊

使用apache poi 4.0.0，最后一個(gè)穩(wěn)定版本，可以在不使用底層基礎(chǔ) bean 的情況下創(chuàng)建條形圖。對(duì)于這個(gè)包 org.apache.poi.xddf.usermodel被使用。

XDDF直到現(xiàn)在，這些東西的某些部分還是有問題的。所以我們需要修復(fù)一些東西。但是我們應(yīng)該使用這些類而不是底層的底層 bean。

您的要求示例：

來(lái)源：

代碼：

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

}

結(jié)果：

要回答你隱含的問題如何理解里面的類org.openxmlformats.schemas.drawingml.x2006.chart：

要知道這一點(diǎn)，我們需要知道如何Excel存儲(chǔ)它的數(shù)據(jù)。這些*.xlsx文件只是ZIP檔案。所以我們可以簡(jiǎn)單地解壓縮它們并查看。

在那里我們找到XML文件。以圖表/xl/charts/chart1.xml為例?，F(xiàn)在首先我們需要了解 XML.

然后我們需要關(guān)于org.openxmlformats.schemas.drawingml.x2006.chart包的信息。我們可以下載ooxml-schemas-1.4-sources.jar，然后這樣做javadoc?，F(xiàn)在我們有一個(gè)API包含所有底層 bean的文檔包org.openxmlformats.schemas.drawingml.x2006.chart。