带有单独轴的 Apache-POI 图表中的第二行 [英] Second Line in an Apache-POI chart with seperate axis
本文介绍了带有单独轴的 Apache-POI 图表中的第二行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
此代码取自答案
包eu.flexsolution.task.excel；导入 java.io.*;导入 org.apache.poi.xwpf.usermodel.*;导入 org.apache.poi.ss.util.CellRangeAddress；导入 org.apache.poi.util.Units;导入 org.apache.poi.xddf.usermodel.*;导入 org.apache.poi.xddf.usermodel.chart.*;导入 org.apache.poi.xssf.usermodel.XSSFClientAnchor;导入 org.apache.poi.xssf.usermodel.XSSFDrawing；导入 org.apache.poi.xssf.usermodel.XSSFSheet;导入 org.apache.poi.xssf.usermodel.XSSFWorkbook；公共类测试{public static void main(String[] args) 抛出异常 {尝试(XSSFWorkbook 文档 = 新 XSSFWorkbook()){XSSFSheet chartSheet = document.createSheet(chart");//创建数据String[] 类别 = new String[] { 1"、2"、3"、4"、5"、6"、7"、8"、9"};Double[] values1 = new Double[] { 1d, 2d, 3d, 4d, 5d, 6d, 7d, 8d, 9d };Double[] values2 = new Double[] { 200d, 300d, 400d, 500d, 600d, 700d, 800d, 900d, 1000d };//创建图表XSSFDrawing 绘图 = chartSheet.createDrawingPatriarch();XSSFClientAnchor 锚点 =drawing.createAnchor(0, 0, 0, 0, 0, 0, 26, 40);XDDFChart 图表 =drawing.createChart(anchor);//创建数据源int numOfPoints = 类别长度；String categoryDataRange = chart.formatRange(new CellRangeAddress(1, numOfPoints, 0, 0));String valuesDataRange1 = chart.formatRange(new CellRangeAddress(1, numOfPoints, 1, 1));String valuesDataRange2 = chart.formatRange(new CellRangeAddress(1, numOfPoints, 2, 2));XDDFDataSourcecategoryData = XDDFDataSourcesFactory.fromArray(categories, categoryDataRange, 0);XDDFNumericalDataSourcevaluesData1 = XDDFDataSourcesFactory.fromArray(values1, valuesDataRange1,1);XDDFNumericalDataSourcevaluesData2 = XDDFDataSourcesFactory.fromArray(values2, valuesDataRange2,2);//第一个折线图XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);XDDFChartData 数据 = chart.createData(ChartTypes.LINE, bottomAxis, leftAxis);XDDFChartData.Series series = data.addSeries(categoriesData, valuesData1);图表(数据)；solidLineSeries(data, 0, PresetColor.BLUE);//第二个折线图//底部轴必须在那里，但必须不可见bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);bottomAxis.setVisible(false);XDDFValueAxis rightAxis = chart.createValueAxis(AxisPosition.RIGHT);rightAxis.setCrosses(AxisCrosses.MAX);//设置正确的交叉轴bottomAxis.crossAxis(rightAxis);rightAxis.crossAxis(bottomAxis);数据 = chart.createData(ChartTypes.LINE, bottomAxis, rightAxis);series = data.addSeries(categoriesData, valuesData2);图表(数据)；//更正id和顺序，不能再次为0，因为只有一行//系列已经chart.getCTChart().getPlotArea().getLineChartArray(1).getSerArray(0).getIdx().setVal(1);chart.getCTChart().getPlotArea().getLineChartArray(1).getSerArray(0).getOrder().setVal(1);solidLineSeries(data, 0, PresetColor.RED);//将输出写入文件尝试(FileOutputStream fileOut = new FileOutputStream(CreateWordXDDFChart.xlsx")){文档写(文件输出)；}}}私有静态无效solidLineSeries(XDDFChartData数据，int索引，PresetColor颜色){XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(color));XDDFLineProperties line = new XDDFLineProperties();line.setFillProperties(fill);XDDFChartData.Series 系列 = data.getSeries().get(index);XDDFShapeProperties 属性 = series.getShapeProperties();如果(属性 == 空){属性 = 新 XDDFShapeProperties();}properties.setLineProperties(line);series.setShapeProperties(properties);}} 解决方案
问题是对于 Word (XWPF) 图表，数据存储在 <嵌入在 Word 文件中的 code>Excel 工作簿.那里的数据可以作为数组给出并通过 XDDFDataSourcesFactory.fromArray 处理.然后填充嵌入的 Excel 数据表.
但是对于 Excel(XSSF) 图表，数据需要在 Excel 数据表中.当然 Excel 不会在它的文件中嵌入 Excel 工作表，因为它已经有工作表了.因此，对于 Excel，数据需要在工作表中，然后需要通过 XDDFDataSourcesFactory.fromStringCellRange 或 XDDFDataSourcesFactory.fromNumericCellRange 进行处理.
创建 Excel XSSFChart 的完整示例:
import java.io.*;导入 org.apache.poi.xwpf.usermodel.*;导入 org.apache.poi.ss.util.CellRangeAddress；导入 org.apache.poi.util.Units;导入 org.apache.poi.xddf.usermodel.*;导入 org.apache.poi.xddf.usermodel.chart.*;导入 org.apache.poi.xssf.usermodel.XSSFClientAnchor;导入 org.apache.poi.xssf.usermodel.XSSFDrawing；导入 org.apache.poi.xssf.usermodel.XSSFSheet;导入 org.apache.poi.xssf.usermodel.XSSFWorkbook；公共类 CreateExcelXDDFChart {public static void main(String[] args) 抛出异常 {尝试(XSSFWorkbook 文档 = 新 XSSFWorkbook()){XSSFSheet chartSheet = document.createSheet(chart");XSSFSheet dataSheet = document.createSheet("data");//创建数据String[] 类别 = new String[] { c1"、c2"、c3"、c4"、c5"、c6"、c7"、c8"、c9"};Double[] values1 = new Double[] { 1d, 2d, 3d, 4d, 5d, 6d, 7d, 8d, 9d };Double[] values2 = new Double[] { 200d, 300d, 400d, 500d, 600d, 700d, 800d, 900d, 1000d };整数 r = 0;对于(字符串猫:类别){dataSheet.createRow(r).createCell(0).setCellValue(cat);dataSheet.getRow(r).createCell(1).setCellValue(values1[r]);dataSheet.getRow(r).createCell(2).setCellValue(values2[r]);r++;}//创建图表XSSFDrawing 绘图 = chartSheet.createDrawingPatriarch();XSSFClientAnchor 锚点 =drawing.createAnchor(0, 0, 0, 0, 0, 0, 26, 40);XDDFChart 图表 =drawing.createChart(anchor);//创建数据源int numOfPoints = 类别长度；XDDFDataSourcecategoryData = XDDFDataSourcesFactory.fromStringCellRange(dataSheet, new CellRangeAddress(0, numOfPoints-1, 0, 0));XDDFNumericalDataSourcevaluesData1 = XDDFDataSourcesFactory.fromNumericCellRange(dataSheet, new CellRangeAddress(0, numOfPoints-1, 1, 1));XDDFNumericalDataSourcevaluesData2 = XDDFDataSourcesFactory.fromNumericCellRange(dataSheet, new CellRangeAddress(0, numOfPoints-1, 2, 2));//第一个折线图XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);XDDFChartData 数据 = chart.createData(ChartTypes.LINE, bottomAxis, leftAxis);XDDFChartData.Series series = data.addSeries(categoriesData, valuesData1);图表(数据)；solidLineSeries(data, 0, PresetColor.BLUE);//第二个折线图//底部轴必须在那里，但必须不可见bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);bottomAxis.setVisible(false);XDDFValueAxis rightAxis = chart.createValueAxis(AxisPosition.RIGHT);rightAxis.setCrosses(AxisCrosses.MAX);//设置正确的交叉轴bottomAxis.crossAxis(rightAxis);rightAxis.crossAxis(bottomAxis);数据 = chart.createData(ChartTypes.LINE, bottomAxis, rightAxis);series = data.addSeries(categoriesData, valuesData2);图表(数据)；//更正id和顺序，不能再为0了，因为只有一行//系列已经chart.getCTChart().getPlotArea().getLineChartArray(1).getSerArray(0).getIdx().setVal(1);chart.getCTChart().getPlotArea().getLineChartArray(1).getSerArray(0).getOrder().setVal(1);solidLineSeries(data, 0, PresetColor.RED);//将输出写入文件尝试(FileOutputStream fileOut = new FileOutputStream(CreateExcelXDDFChart.xlsx")){文档写(文件输出)；}}}私有静态无效solidLineSeries(XDDFChartData数据，int索引，PresetColor颜色){XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(color));XDDFLineProperties line = new XDDFLineProperties();line.setFillProperties(fill);//XDDFChartData.Series series = data.getSeries().get(index);XDDFChartData.Series 系列 = data.getSeries(index);XDDFShapeProperties 属性 = series.getShapeProperties();如果(属性 == 空){属性 = 新 XDDFShapeProperties();}properties.setLineProperties(line);series.setShapeProperties(properties);}} 使用当前的 apache poi 4.1.2 工作.
Hi this code taken from the answer here is working as expected, but I want exactly the same Chart but in an Excel-Sheet
package eu.flexsolution.task.excel;
import java.io.*;
import org.apache.poi.xwpf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.Units;
import org.apache.poi.xddf.usermodel.*;
import org.apache.poi.xddf.usermodel.chart.*;
public class TEst {
public static void main(String[] args) throws Exception {
try (XWPFDocument document = new XWPFDocument()) {
// create the data
String[] categories = new String[] { "1", "2", "3", "4", "5", "6", "7", "8", "9" };
Double[] values1 = new Double[] { 1d, 2d, 3d, 4d, 5d, 6d, 7d, 8d, 9d };
Double[] values2 = new Double[] { 200d, 300d, 400d, 500d, 600d, 700d, 800d, 900d, 1000d };
// create the chart
XWPFChart chart = document.createChart(15 * Units.EMU_PER_CENTIMETER, 10 * Units.EMU_PER_CENTIMETER);
// create data sources
int numOfPoints = categories.length;
String categoryDataRange = chart.formatRange(new CellRangeAddress(1, numOfPoints, 0, 0));
String valuesDataRange1 = chart.formatRange(new CellRangeAddress(1, numOfPoints, 1, 1));
String valuesDataRange2 = chart.formatRange(new CellRangeAddress(1, numOfPoints, 2, 2));
XDDFDataSource<String> categoriesData = XDDFDataSourcesFactory.fromArray(categories, categoryDataRange, 0);
XDDFNumericalDataSource<Double> valuesData1 = XDDFDataSourcesFactory.fromArray(values1, valuesDataRange1,
1);
XDDFNumericalDataSource<Double> valuesData2 = XDDFDataSourcesFactory.fromArray(values2, valuesDataRange2,
2);
// first line chart
XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);
XDDFChartData data = chart.createData(ChartTypes.LINE, bottomAxis, leftAxis);
XDDFChartData.Series series = data.addSeries(categoriesData, valuesData1);
chart.plot(data);
solidLineSeries(data, 0, PresetColor.BLUE);
// second line chart
// bottom axis must be there but must not be visible
bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
bottomAxis.setVisible(false);
XDDFValueAxis rightAxis = chart.createValueAxis(AxisPosition.RIGHT);
rightAxis.setCrosses(AxisCrosses.MAX);
// set correct cross axis
bottomAxis.crossAxis(rightAxis);
rightAxis.crossAxis(bottomAxis);
data = chart.createData(ChartTypes.LINE, bottomAxis, rightAxis);
series = data.addSeries(categoriesData, valuesData2);
chart.plot(data);
// correct the id and order, must not be 0 again because there is one line
// series already
chart.getCTChart().getPlotArea().getLineChartArray(1).getSerArray(0).getIdx().setVal(1);
chart.getCTChart().getPlotArea().getLineChartArray(1).getSerArray(0).getOrder().setVal(1);
solidLineSeries(data, 0, PresetColor.RED);
// Write the output to a file
try (FileOutputStream fileOut = new FileOutputStream("CreateWordXDDFChart.docx")) {
document.write(fileOut);
}
}
}
private static void solidLineSeries(XDDFChartData data, int index, PresetColor color) {
XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(color));
XDDFLineProperties line = new XDDFLineProperties();
line.setFillProperties(fill);
XDDFChartData.Series series = data.getSeries().get(index);
XDDFShapeProperties properties = series.getShapeProperties();
if (properties == null) {
properties = new XDDFShapeProperties();
}
properties.setLineProperties(line);
series.setShapeProperties(properties);
}
}
So I modified the code like this to get an XLSX document, but the Chart isn't the same
package eu.flexsolution.task.excel;
import java.io.*;
import org.apache.poi.xwpf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.Units;
import org.apache.poi.xddf.usermodel.*;
import org.apache.poi.xddf.usermodel.chart.*;
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 TEst {
public static void main(String[] args) throws Exception {
try (XSSFWorkbook document = new XSSFWorkbook()) {
XSSFSheet chartSheet = document.createSheet("chart");
// create the data
String[] categories = new String[] { "1", "2", "3", "4", "5", "6", "7", "8", "9" };
Double[] values1 = new Double[] { 1d, 2d, 3d, 4d, 5d, 6d, 7d, 8d, 9d };
Double[] values2 = new Double[] { 200d, 300d, 400d, 500d, 600d, 700d, 800d, 900d, 1000d };
// create the chart
XSSFDrawing drawing = chartSheet.createDrawingPatriarch();
XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 0, 26, 40);
XDDFChart chart = drawing.createChart(anchor);
// create data sources
int numOfPoints = categories.length;
String categoryDataRange = chart.formatRange(new CellRangeAddress(1, numOfPoints, 0, 0));
String valuesDataRange1 = chart.formatRange(new CellRangeAddress(1, numOfPoints, 1, 1));
String valuesDataRange2 = chart.formatRange(new CellRangeAddress(1, numOfPoints, 2, 2));
XDDFDataSource<String> categoriesData = XDDFDataSourcesFactory.fromArray(categories, categoryDataRange, 0);
XDDFNumericalDataSource<Double> valuesData1 = XDDFDataSourcesFactory.fromArray(values1, valuesDataRange1,
1);
XDDFNumericalDataSource<Double> valuesData2 = XDDFDataSourcesFactory.fromArray(values2, valuesDataRange2,
2);
// first line chart
XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);
XDDFChartData data = chart.createData(ChartTypes.LINE, bottomAxis, leftAxis);
XDDFChartData.Series series = data.addSeries(categoriesData, valuesData1);
chart.plot(data);
solidLineSeries(data, 0, PresetColor.BLUE);
// second line chart
// bottom axis must be there but must not be visible
bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
bottomAxis.setVisible(false);
XDDFValueAxis rightAxis = chart.createValueAxis(AxisPosition.RIGHT);
rightAxis.setCrosses(AxisCrosses.MAX);
// set correct cross axis
bottomAxis.crossAxis(rightAxis);
rightAxis.crossAxis(bottomAxis);
data = chart.createData(ChartTypes.LINE, bottomAxis, rightAxis);
series = data.addSeries(categoriesData, valuesData2);
chart.plot(data);
// correct the id and order, must not be 0 again because there is one line
// series already
chart.getCTChart().getPlotArea().getLineChartArray(1).getSerArray(0).getIdx().setVal(1);
chart.getCTChart().getPlotArea().getLineChartArray(1).getSerArray(0).getOrder().setVal(1);
solidLineSeries(data, 0, PresetColor.RED);
// Write the output to a file
try (FileOutputStream fileOut = new FileOutputStream("CreateWordXDDFChart.xlsx")) {
document.write(fileOut);
}
}
}
private static void solidLineSeries(XDDFChartData data, int index, PresetColor color) {
XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(color));
XDDFLineProperties line = new XDDFLineProperties();
line.setFillProperties(fill);
XDDFChartData.Series series = data.getSeries().get(index);
XDDFShapeProperties properties = series.getShapeProperties();
if (properties == null) {
properties = new XDDFShapeProperties();
}
properties.setLineProperties(line);
series.setShapeProperties(properties);
}
}
解决方案
The problem is that for a Word (XWPF) chart, the data are stored in a Excel workbook which is embedded in the Word file. There the data can be given as arrays and handled via XDDFDataSourcesFactory.fromArray. This then fills the embedded Excel data sheet.
But for a Excel(XSSF) chart the data needs to be in a Excel data sheet. Of course Excel will not embedding a Excel sheet in it's files as it has worksheets already. So for Excel the data needs to be in a worksheet and needs to be handled via XDDFDataSourcesFactory.fromStringCellRange or XDDFDataSourcesFactory.fromNumericCellRange then.
Complete example which creates the Excel XSSFChart:
import java.io.*;
import org.apache.poi.xwpf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.Units;
import org.apache.poi.xddf.usermodel.*;
import org.apache.poi.xddf.usermodel.chart.*;
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 CreateExcelXDDFChart {
public static void main(String[] args) throws Exception {
try (XSSFWorkbook document = new XSSFWorkbook()) {
XSSFSheet chartSheet = document.createSheet("chart");
XSSFSheet dataSheet = document.createSheet("data");
// create the data
String[] categories = new String[] { "c1", "c2", "c3", "c4", "c5", "c6", "c7", "c8", "c9" };
Double[] values1 = new Double[] { 1d, 2d, 3d, 4d, 5d, 6d, 7d, 8d, 9d };
Double[] values2 = new Double[] { 200d, 300d, 400d, 500d, 600d, 700d, 800d, 900d, 1000d };
int r = 0;
for (String cat : categories) {
dataSheet.createRow(r).createCell(0).setCellValue(cat);
dataSheet.getRow(r).createCell(1).setCellValue(values1[r]);
dataSheet.getRow(r).createCell(2).setCellValue(values2[r]);
r++;
}
// create the chart
XSSFDrawing drawing = chartSheet.createDrawingPatriarch();
XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 0, 26, 40);
XDDFChart chart = drawing.createChart(anchor);
// create data sources
int numOfPoints = categories.length;
XDDFDataSource<String> categoriesData = XDDFDataSourcesFactory.fromStringCellRange(dataSheet, new CellRangeAddress(0, numOfPoints-1, 0, 0));
XDDFNumericalDataSource<Double> valuesData1 = XDDFDataSourcesFactory.fromNumericCellRange(dataSheet, new CellRangeAddress(0, numOfPoints-1, 1, 1));
XDDFNumericalDataSource<Double> valuesData2 = XDDFDataSourcesFactory.fromNumericCellRange(dataSheet, new CellRangeAddress(0, numOfPoints-1, 2, 2));
// first line chart
XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);
XDDFChartData data = chart.createData(ChartTypes.LINE, bottomAxis, leftAxis);
XDDFChartData.Series series = data.addSeries(categoriesData, valuesData1);
chart.plot(data);
solidLineSeries(data, 0, PresetColor.BLUE);
// second line chart
// bottom axis must be there but must not be visible
bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
bottomAxis.setVisible(false);
XDDFValueAxis rightAxis = chart.createValueAxis(AxisPosition.RIGHT);
rightAxis.setCrosses(AxisCrosses.MAX);
// set correct cross axis
bottomAxis.crossAxis(rightAxis);
rightAxis.crossAxis(bottomAxis);
data = chart.createData(ChartTypes.LINE, bottomAxis, rightAxis);
series = data.addSeries(categoriesData, valuesData2);
chart.plot(data);
// correct the id and order, must not be 0 again because there is one line
// series already
chart.getCTChart().getPlotArea().getLineChartArray(1).getSerArray(0).getIdx().setVal(1);
chart.getCTChart().getPlotArea().getLineChartArray(1).getSerArray(0).getOrder().setVal(1);
solidLineSeries(data, 0, PresetColor.RED);
// Write the output to a file
try (FileOutputStream fileOut = new FileOutputStream("CreateExcelXDDFChart.xlsx")) {
document.write(fileOut);
}
}
}
private static void solidLineSeries(XDDFChartData data, int index, PresetColor color) {
XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(color));
XDDFLineProperties line = new XDDFLineProperties();
line.setFillProperties(fill);
//XDDFChartData.Series series = data.getSeries().get(index);
XDDFChartData.Series series = data.getSeries(index);
XDDFShapeProperties properties = series.getShapeProperties();
if (properties == null) {
properties = new XDDFShapeProperties();
}
properties.setLineProperties(line);
series.setShapeProperties(properties);
}
}
Works using current apache poi 4.1.2.
这篇关于带有单独轴的 Apache-POI 图表中的第二行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
