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);
}
}
}
}
Above example works when Microsoft Excel is used to show the resulting *.xlsx file. But some other spreadsheet calculation applications are not able to use array literals ({0,0,0,0,0,0,0,0,0}) as data source for chart series. So the more compatible solution will be using a unused cell range as datasource for the additional dummy series. For example use column IV for this as so:
...
// 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));
...
And also for compatibility reasons you should set colors for the bars as done in the apache poi example in BarChart.java which is available in https://svn.apache.org/repos/asf/poi/trunk/poi-examples/src/main/java/org/apache/poi/examples/xssf/usermodel/. Some other spreadsheet calculation applications will not automatically choose color if not given, as Excel does.
If you have more than one series in one of the bar chart, you need more pad series in the other bar chart which uses the other axis. The pad series are needed to shift the visible bar series and to determine the bar width of them. So if you have two visible bar series on the first chart which uses the left axis and one visible bar series on the second chart which uses the right axis, then you need one additional pad series as the last series in the first bar chart and you need two pad series as the first series in the second bar chart. In other words, you need as much pad series that both bar charts have the same count of series to have the same bar width in both. And the position of the invisible pad series determines how the visible bar series are shifted.
If you need a legend, then the pad series will disturb. So you need deleting them from the legend. In remove specific legend apache poi excel graph XDDFChartLegend I have shown how to do that.
I will provide another complete example which shows all this:
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);
}
}
