在Apache-POI图表中的第二行,具有单独的轴。

huangapple go评论68阅读模式
英文:

Second Line in an Apache-POI chart with seperate axis

问题

这段代码已经被翻译成中文了,以下是翻译好的部分:

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()) {

            // 创建数据
            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 };

            // 创建图表
            XWPFChart chart = document.createChart(15 * Units.EMU_PER_CENTIMETER, 10 * Units.EMU_PER_CENTIMETER);

            // 创建数据源
            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);

            // 创建第一条线图
            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);

            // 创建第二条线图
            // 底部轴必须存在但不可见
            bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
            bottomAxis.setVisible(false);

            XDDFValueAxis rightAxis = chart.createValueAxis(AxisPosition.RIGHT);
            rightAxis.setCrosses(AxisCrosses.MAX);

            // 设置正确的交叉轴
            bottomAxis.crossAxis(rightAxis);
            rightAxis.crossAxis(bottomAxis);

            data = chart.createData(ChartTypes.LINE, bottomAxis, rightAxis);
            series = data.addSeries(categoriesData, valuesData2);
            chart.plot(data);

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

            // 将输出写入文件
            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);
    }
}

如果你需要关于修改后的代码的中文翻译,请提供具体的修改部分。

英文:

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[] { &quot;1&quot;, &quot;2&quot;, &quot;3&quot;, &quot;4&quot;, &quot;5&quot;, &quot;6&quot;, &quot;7&quot;, &quot;8&quot;, &quot;9&quot; };
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&lt;String&gt; categoriesData = XDDFDataSourcesFactory.fromArray(categories, categoryDataRange, 0);
XDDFNumericalDataSource&lt;Double&gt; valuesData1 = XDDFDataSourcesFactory.fromArray(values1, valuesDataRange1,
1);
XDDFNumericalDataSource&lt;Double&gt; 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(&quot;CreateWordXDDFChart.docx&quot;)) {
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
在Apache-POI图表中的第二行,具有单独的轴。

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(&quot;chart&quot;);
// create the data
String[] categories = new String[] { &quot;1&quot;, &quot;2&quot;, &quot;3&quot;, &quot;4&quot;, &quot;5&quot;, &quot;6&quot;, &quot;7&quot;, &quot;8&quot;, &quot;9&quot; };
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&lt;String&gt; categoriesData = XDDFDataSourcesFactory.fromArray(categories, categoryDataRange, 0);
XDDFNumericalDataSource&lt;Double&gt; valuesData1 = XDDFDataSourcesFactory.fromArray(values1, valuesDataRange1,
1);
XDDFNumericalDataSource&lt;Double&gt; 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(&quot;CreateWordXDDFChart.xlsx&quot;)) {
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);
}
}

答案1

得分: 3

以下是翻译好的部分:

问题在于对于Word(XWPF)图表,数据存储在嵌入在Word文件中的Excel工作簿中。在那里,数据可以以数组的形式给出,并通过XDDFDataSourcesFactory.fromArray进行处理。然后,这些数据填充到嵌入的Excel数据表中。

但对于Excel(XSSF)图表,数据需要位于Excel数据表中。当然,Excel不会在其文件中嵌入Excel工作表,因为它已经有工作表了。所以对于Excel,数据需要位于工作表中,并且需要通过XDDFDataSourcesFactory.fromStringCellRangeXDDFDataSourcesFactory.fromNumericCellRange来处理。

创建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");

   // 创建数据
   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++;
   }

   // 创建图表
   XSSFDrawing drawing = chartSheet.createDrawingPatriarch();
   XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 0, 26, 40);
   XDDFChart chart = drawing.createChart(anchor);

   // 创建数据源
   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));

   // 第一个线性图表
   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);

   // 第二个线性图表
   // 底部轴必须存在但不能可见
   bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
   bottomAxis.setVisible(false);

   XDDFValueAxis rightAxis = chart.createValueAxis(AxisPosition.RIGHT);
   rightAxis.setCrosses(AxisCrosses.MAX);

   // 设置正确的交叉轴
   bottomAxis.crossAxis(rightAxis);
   rightAxis.crossAxis(bottomAxis);

   data = chart.createData(ChartTypes.LINE, bottomAxis, rightAxis);
   series = data.addSeries(categoriesData, valuesData2);
   chart.plot(data);

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

   // 将输出写入文件
   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);
 }
}

使用当前的apache poi 4.1.2版本可以正常运行。

英文:

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(&quot;chart&quot;);
XSSFSheet dataSheet = document.createSheet(&quot;data&quot;);
// create the data
String[] categories = new String[] { &quot;c1&quot;, &quot;c2&quot;, &quot;c3&quot;, &quot;c4&quot;, &quot;c5&quot;, &quot;c6&quot;, &quot;c7&quot;, &quot;c8&quot;, &quot;c9&quot; };
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&lt;String&gt; categoriesData = XDDFDataSourcesFactory.fromStringCellRange(dataSheet, new CellRangeAddress(0, numOfPoints-1, 0, 0));
XDDFNumericalDataSource&lt;Double&gt; valuesData1 = XDDFDataSourcesFactory.fromNumericCellRange(dataSheet, new CellRangeAddress(0, numOfPoints-1, 1, 1));
XDDFNumericalDataSource&lt;Double&gt; 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(&quot;CreateExcelXDDFChart.xlsx&quot;)) {
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.

huangapple
  • 本文由 发表于 2020年8月4日 18:39:35
  • 转载请务必保留本文链接:https://go.coder-hub.com/63245147.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定