文章/答案/技术大牛

发布

社区首页 >问答首页 >创建oooxml图表。用org.apache.poi

问创建oooxml图表。用org.apache.poi
EN

Stack Overflow用户

提问于 2021-12-29 07:26:51

回答 1查看 658关注 0票数 0

我目前正在使用org.apache.poi和oooxml库制作一个Excel图表。我把下面的图片画好了。

在这里输入图像描述

XSSFDrawing drawing = (XSSFDrawing)sheet.createDrawingPatriarch();
ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 35, 20, 57);

XSSFChart chart = drawing.createChart(anchor);

CTChart ctChart = ((XSSFChart)chart).getCTChart();
CTPlotArea ctPlotArea = ctChart.getPlotArea();

//여기까지는 똑같음

//the first bar chart
CTBarChart ctBarChart = ctPlotArea.addNewBarChart();
CTBoolean ctBoolean = ctBarChart.addNewVaryColors();
ctBoolean.setVal(true);
ctBarChart.addNewBarDir().setVal(STBarDir.COL);

//the first chart series
CTBarSer ctBarSer = ctBarChart.addNewSer();
CTSerTx ctSerTx = ctBarSer.addNewTx();
CTStrRef ctStrRef = ctSerTx.addNewStrRef();
ctStrRef.setF("Sheet1!$B$10");
ctBarSer.addNewIdx().setVal(0);
CTAxDataSource ctAxDataSource = ctBarSer.addNewCat();
ctStrRef = ctAxDataSource.addNewStrRef();
ctStrRef.setF("Sheet1!$A$11:$A$34");
CTNumDataSource ctNumDataSource = ctBarSer.addNewVal();
CTNumRef ctNumRef = ctNumDataSource.addNewNumRef();
ctNumRef.setF("Sheet1!$B$11:$B$34");

//the second chart series
CTBarSer ctBarSer1 = ctBarChart.addNewSer();
CTSerTx ctSerTx1 = ctBarSer1.addNewTx();
CTStrRef ctStrRef1 = ctSerTx1.addNewStrRef();
ctStrRef1.setF("Sheet1!$C$10");
ctBarSer1.addNewIdx().setVal(1);
CTAxDataSource ctAxDataSource1 = ctBarSer1.addNewCat();
ctStrRef1 = ctAxDataSource1.addNewStrRef();
ctStrRef1.setF("Sheet1!$A$11:$A$34");
CTNumDataSource ctNumDataSource1 = ctBarSer1.addNewVal();
CTNumRef ctNumRef1 = ctNumDataSource1.addNewNumRef();
ctNumRef1.setF("Sheet1!$C$11:$C$34");

// 3번째
CTBarSer ctBarSer2 = ctBarChart.addNewSer();
CTSerTx ctSerTx2 = ctBarSer2.addNewTx();
CTStrRef ctStrRef2 = ctSerTx2.addNewStrRef();
ctStrRef2.setF("Sheet1!$D$10");
ctBarSer2.addNewIdx().setVal(2);
CTAxDataSource ctAxDataSource2 = ctBarSer2.addNewCat();
ctStrRef2 = ctAxDataSource2.addNewStrRef();
ctStrRef2.setF("Sheet1!$A$11:$A$34");
CTNumDataSource ctNumDataSource2 = ctBarSer2.addNewVal();
CTNumRef ctNumRef2 = ctNumDataSource2.addNewNumRef();
ctNumRef2.setF("Sheet1!$D$11:$D$34");

//at least the border lines in Libreoffice Calc ;-)
ctBarSer.addNewSpPr().addNewLn().addNewSolidFill().addNewSrgbClr().setVal(new byte[] {0,0,0});
ctBarSer1.addNewSpPr().addNewLn().addNewSolidFill().addNewSrgbClr().setVal(new byte[] {0,0,0});
ctBarSer2.addNewSpPr().addNewLn().addNewSolidFill().addNewSrgbClr().setVal(new byte[] {0,0,0});

ctBarChart.addNewAxId().setVal(123456); //cat axis 1 (lines)
ctBarChart.addNewAxId().setVal(123457); //val axis 1 (left)
ctBarChart.addNewAxId().setVal(123458); //val axis 1 (right)
ctBarChart.addNewAxId().setVal(123459); //val axis 1 (right)

//cat axis 1
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);

//val axis 1 (left)
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.addNewCrosses().setVal(STCrosses.AUTO_ZERO); //this val axis crosses the cat axis at zero
ctValAx.addNewTickLblPos().setVal(STTickLblPos.NEXT_TO);

至于"transmission= C单元格“的数量，这个单位很大，所以我试图在右边建立另一个值轴，但是我一直有错误，所以我问。如何在右边创建一个值轴，基于右边的“传输”的数量，以及“用户D单元，传输计数B单元”生成一个基于左侧的图表？

apache-poi

openxml

java

spring

回答 1

Stack Overflow用户

回答已采纳

发布于 2021-12-29 09:54:55

从apache poi 4开始，您应该使用新的XDDF类创建图表。使用低级别的CT*类非常复杂，而且容易出错。

使用线条图，我已经在这里提供了一个示例：具有独立轴的Apache-POI图中的第二行。

但是使用条形图还有一个额外的问题要解决。如果一个条形图系列是在主轴上，另一个是在第二轴上，那么这些条形图就会相互重叠。这是因为每个系列只在使用相同轴的这些系列上定向。换句话说，主轴上的序列在图上占用空间时不尊重次轴上的序列。对于线条图来说，这不是一个问题，但是对于条形图来说是一个问题。

要解决这个附加的系列，可以在两个轴上使用虚拟数据(全部为0)。这些额外的系列导致无形条(高度0)，这也占用空间，因此可以用来移动可见条。

下面的完整示例说明了这一点。它使用apache poi 4.1.2和apache poi 5.1.0进行测试和工作。

import java.io.*;

import org.apache.poi.xwpf.usermodel.*;

import org.apache.poi.ss.util.CellRangeAddress;

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, 16, 22);
   XDDFChart chart = drawing.createChart(anchor);
   
   // create data sources
   int numOfPoints = categories.length;
   // dummy 0-values for the pad data source
   Double[] dummyValuesForPad = new Double[numOfPoints];
   for (int i = 0; i < numOfPoints; i++) {
    dummyValuesForPad[i] = 0d;  
   }
   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));
   // data source for the pad series
   XDDFNumericalDataSource<Double> pad = XDDFDataSourcesFactory.fromArray(dummyValuesForPad);

   // first bar chart
   XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
   XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
   leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);
   leftAxis.setCrossBetween(AxisCrossBetween.BETWEEN);
   
   XDDFChartData data = chart.createData(ChartTypes.BAR, bottomAxis, leftAxis);
   XDDFBarChartData bar = (XDDFBarChartData) data;
   bar.setBarDirection(BarDirection.COL);
   
   XDDFChartData.Series series = data.addSeries(categoriesData, valuesData1);
   series.setTitle("Series 1", null);
   // additional pad series - takes space at right side for primary axis
   series = data.addSeries(categoriesData, pad);
   series.setTitle("pad", null);
   
   chart.plot(data);

   // second bar 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);
   rightAxis.setCrossBetween(AxisCrossBetween.BETWEEN);

   // set correct cross axis
   bottomAxis.crossAxis(rightAxis);
   rightAxis.crossAxis(bottomAxis);

   data = chart.createData(ChartTypes.BAR, bottomAxis, rightAxis);
   bar = (XDDFBarChartData) data;
   bar.setBarDirection(BarDirection.COL);
   
   // additional pad series - takes space at left side for secondary axis
   series = data.addSeries(categoriesData, pad);
   series.setTitle("pad", null);
   series = data.addSeries(categoriesData, valuesData2);
   series.setTitle("Series 2", null);
 
   chart.plot(data);

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

当使用Microsoft显示结果的*.xlsx文件时，上面的示例工作。但是，其他一些电子表格计算应用程序无法使用数组文字({0,0,0,0,0,0,0,0,0})作为图表系列的数据源。因此，更兼容的解决方案将是使用一个未使用的单元格范围作为附加虚拟系列的数据源。例如，为此使用列IV如下：

...
   // dummy 0-values for the pad data source in column IV
   for (int r = 0; r < numOfPoints; r++) {
    XSSFRow row = dataSheet.getRow(r); if (row == null) row = dataSheet.createRow(r);
    XSSFCell cell = row.createCell(255);
    cell.setCellValue(0);
   }
   // data source for the pad series
   XDDFNumericalDataSource<Double> pad = XDDFDataSourcesFactory.fromNumericCellRange(dataSheet, new CellRangeAddress(0, numOfPoints-1, 255, 255));
...

此外，出于兼容性原因，您应该为条形图设置颜色，就像BarChart.java中的BarChart.java示例中所做的那样，这在https://svn.apache.org/repos/asf/poi/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/usermodel/中是可用的。其他一些电子表格计算应用程序不会像Excel那样自动选择颜色。

如果在一个条形图中有多个系列，则在另一个使用另一个轴的条形图中需要更多的pad系列。所述衬垫系列用于移动可见条形系列并确定它们的条宽。因此，如果在第一张图上有两个可见的条形系列使用左轴，第二张图上有一个使用右轴的可见条形系列，那么在第一个条形图中需要一个额外的pad系列作为最后一个系列，在第二个条形图中需要两个pad系列作为第一个系列。换句话说，您需要相同的垫系列，两个条形图有相同的计数系列，有相同的条宽在这两个。所述不可见垫系列的位置决定所述可见条系列的移动方式。

如果你需要一个传奇，那么垫系列会打扰。所以你需要从传说中删除它们。在删除特定图例apache poi excel图XDDFChartLegend中，我已经向您展示了如何做到这一点。

我将提供另一个完整的例子，说明所有这些：

import java.io.*;

import org.apache.poi.xwpf.usermodel.*;

import org.apache.poi.ss.util.CellRangeAddress;

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.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFCell;
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 };
   Double[] values3 = new Double[] { 4.5d, 4d, 3.5d, 3d, 2.5d, 2d, 1.5d, 1d, 0.5d };
   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]);
    dataSheet.getRow(r).createCell(3).setCellValue(values3[r]);
    r++;
   }

   // create the chart
   XSSFDrawing drawing = chartSheet.createDrawingPatriarch();
   XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 0, 16, 22);
   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));
   XDDFNumericalDataSource<Double> valuesData3 = XDDFDataSourcesFactory.fromNumericCellRange(dataSheet, new CellRangeAddress(0, numOfPoints-1, 3, 3));
   /*
   // dummy 0-values for the pad data source
   Double[] dummyValuesForPad = new Double[numOfPoints];
   for (int i = 0; i < numOfPoints; i++) {
    dummyValuesForPad[i] = 0d;  
   }
   */
   // data source for the pad series
   //XDDFNumericalDataSource<Double> pad = XDDFDataSourcesFactory.fromArray(dummyValuesForPad);
   //XDDFNumericalDataSource<Double> pad = XDDFDataSourcesFactory.fromArray(dummyValuesForPad, null);
   
   // dummy 0-values for the pad data source in column IV
   for (int i = 0; i < numOfPoints; i++) {
    XSSFRow row = dataSheet.getRow(i); if (row == null) row = dataSheet.createRow(i);
    XSSFCell cell = row.createCell(255);
    cell.setCellValue(0);
   }
   // data source for the pad series
   XDDFNumericalDataSource<Double> pad = XDDFDataSourcesFactory.fromNumericCellRange(dataSheet, new CellRangeAddress(0, numOfPoints-1, 255, 255));

   // first bar chart
   XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
   XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
   leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);
   leftAxis.setCrossBetween(AxisCrossBetween.BETWEEN);
   
   XDDFChartData data = chart.createData(ChartTypes.BAR, bottomAxis, leftAxis);
   XDDFBarChartData bar = (XDDFBarChartData) data;
   bar.setBarDirection(BarDirection.COL);
   
   XDDFChartData.Series series = data.addSeries(categoriesData, valuesData1);
   series.setTitle("Series 1", null);
   series = data.addSeries(categoriesData, valuesData3);
   series.setTitle("Series 3", null);
   // additional pad series - takes space at right side for primary axis
   series = data.addSeries(categoriesData, pad);
   series.setTitle("pad", null);
   
   chart.plot(data);
   
   // set bar colors
   solidFillSeries(data, 0, PresetColor.GREEN);
   solidFillSeries(data, 1, PresetColor.BLUE);

   // second bar 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);
   rightAxis.setCrossBetween(AxisCrossBetween.BETWEEN);

   // set correct cross axis
   bottomAxis.crossAxis(rightAxis);
   rightAxis.crossAxis(bottomAxis);

   data = chart.createData(ChartTypes.BAR, bottomAxis, rightAxis);
   bar = (XDDFBarChartData) data;
   bar.setBarDirection(BarDirection.COL);
   
   // additional pad series - takes space at left side for secondary axis
   series = data.addSeries(categoriesData, pad);
   series.setTitle("pad", null);
   series = data.addSeries(categoriesData, pad);
   series.setTitle("pad", null);
   series = data.addSeries(categoriesData, valuesData2);
   series.setTitle("Series 2", null);
 
   chart.plot(data);
   
   // set bar colors
   solidFillSeries(data, 2, PresetColor.RED);
   
   // set legend 
   XDDFChartLegend legend = chart.getOrAddLegend();
   legend.setPosition(LegendPosition.BOTTOM);
   // set legend entries for pad series deleted
   XDDFLegendEntry legendEntry = getOrAddLegendEntry(legend, 2);
   legendEntry.setDelete(true);
   legendEntry = getOrAddLegendEntry(legend, 3);
   legendEntry.setDelete(true);
   legendEntry = getOrAddLegendEntry(legend, 4);
   legendEntry.setDelete(true);

   // Write the output to a file
   try (FileOutputStream fileOut = new FileOutputStream("CreateExcelXDDFChart.xlsx")) {
    document.write(fileOut);
   }
  }
 }
 
 private static XDDFLegendEntry getOrAddLegendEntry(XDDFChartLegend legend, long index) {
  XDDFLegendEntry legendEntry = null;
  for (XDDFLegendEntry storedLegendEntry : legend.getEntries()) {
   if (storedLegendEntry.getIndex() == index) {
    legendEntry = storedLegendEntry;
    break;
   }   
  }
  if (legendEntry == null) {
   legendEntry = legend.addEntry();
   legendEntry.setIndex(index); 
  }
  return legendEntry;
 } 
 
 private static void solidFillSeries(XDDFChartData data, int index, PresetColor color) {
  XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(color));
  XDDFChartData.Series series = data.getSeries(index);
  XDDFShapeProperties properties = series.getShapeProperties();
  if (properties == null) {
   properties = new XDDFShapeProperties();
  }
  properties.setFillProperties(fill);
  series.setShapeProperties(properties);
 }
}

票数 2

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/70516339

复制

相似问题

问创建oooxml图表。用org.apache.poi
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问创建oooxml图表。用org.apache.poiEN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问创建oooxml图表。用org.apache.poi
EN