I'm currently making an Excel chart using org.apache.poi and oooxml library. I developed the picture below.
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);
As for the amount of "transmission= C cell", the unit is large, so I'm trying to make another value axis on the right, but I kept getting errors, so I asked. How can I create a valueaxis on the right, the number of "transmissions" based on the right, and the "user D cell , transfer count B cell" generates a chart based on the left?
CodePudding user response：
From apache poi 4 on you should use the new XDDF classes to create charts. Playing around with the low level CT* classes is very complex and error prone.
Using line charts I have provided an example already here: Second Line in an Apache-POI chart with seperate axis.
But using bar charts there is an additional problem to solve. If one bar chart series is on primary axis and another is on secondary axis, then the bars will overlap each other. This is because each of the series are oriented only on those series using the same axis. In other words the series on primary axis do not respect the series on the secondary axis when it comes to taking space on the chart. For line charts this is not a problem but for bar charts it is.
To solve this additional series having dummy data (all 0) can be used on both the axes. Those additional series lead to invisible bars (height 0) which also take space and so can be used to shift the visible bars.
The following complete example shows this. It is tested and works using apache poi 4.1.2 and 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);
}
}
}
}