I'm trying to populate bar chart for positive and negative values using apache poi. I'm able to populate positive bar with different color but now able to populate negative bar with different color. It is coming empty bar. I'm referring this example.
Please help me to populate negative bar with color.
here is my code:
public class BarChart2 {
public static void main(String[] args) throws Exception {
try (XSSFWorkbook wb = new XSSFWorkbook()) {
String sheetName = "test";//"CountryColumnChart";
XSSFSheet sheet = wb.createSheet(sheetName);
// Create row and put some cells in it. Rows and cells are 0 based.
Row row = sheet.createRow((short) 0);
Cell cell = row.createCell((short) 0);
cell = row.createCell((short) 0);
cell.setCellValue("Jan");
cell = row.createCell((short) 1);
cell.setCellValue(23);
row = sheet.createRow((short) 1);
cell = row.createCell((short) 0);
cell.setCellValue("Feb");
cell = row.createCell((short) 1);
cell.setCellValue(-5);
row = sheet.createRow((short) 2);
cell = row.createCell((short) 0);
cell.setCellValue("March");
cell = row.createCell((short) 1);
cell.setCellValue(15);
XSSFDrawing drawing = sheet.createDrawingPatriarch();
XSSFClientAnchor anchor = drawing.createAnchor(5, 5, 5, 5, 5, 0, 12, 20);
XSSFChart chart = drawing.createChart(anchor);
chart.setTitleText("Report" );
chart.setTitleOverlay(false);
//XDDFChartLegend legend = chart.getOrAddLegend();
//legend.setPosition(LegendPosition.TOP_RIGHT);
XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
bottomAxis.setTitle("Month");
//bottomAxis.isSetMinimum();
XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
leftAxis.setTitle("Area");
leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);
//leftAxis.setCrossBetween(AxisCrossBetween.MIDPOINT_CATEGORY);
XDDFDataSource<String> months = XDDFDataSourcesFactory.fromStringCellRange(sheet,
new CellRangeAddress(0, 2, 0, 0));
XDDFNumericalDataSource<Double> values = XDDFDataSourcesFactory.fromNumericCellRange(sheet,
new CellRangeAddress(0, 2, 1, 1));
XDDFChartData data = chart.createData(ChartTypes.BAR, bottomAxis, leftAxis);
XDDFChartData.Series series1 = data.addSeries(months, values);
series1.setTitle("Month", null);
data.setVaryColors(false);
chart.plot(data);
// in order to transform a bar chart into a column chart, you just need to change the bar direction
XDDFBarChartData bar = (XDDFBarChartData) data;
//bar.setBarDirection(BarDirection.BAR);
bar.setBarDirection(BarDirection.COL);
// Write output to an excel file
String filename = "test.xlsx";//"column-chart-top-seven-countries.xlsx";
try (FileOutputStream fileOut = new FileOutputStream("C:\\"+filename)) {
wb.write(fileOut);
}
}
}
}
Excel bar chart series have a setting invert if negative. That means it uses inverted bar color for negative values. For current Excel versions that seems to be the default. So we need set that false explicitly:
But additionally one should explicit set the bar color instead of hoping for a properly automatically set one. The official Apache POI BarChart example shows how.
And for a bar chart you additional need set
AxisCrossBetweenfor the value axis, so the value axis crosses the category axis between the categories. Else first and last category is exactly on cross points and the bars are only half visible.Complete example:
If the need is having a separate color for negative values in bar chart, then one could use that setting "invert if negative" together with a defined inverted color. But that is an extension for Excel versions after 2007. Up to Excel 2007 the inverted color was simply white always. And since the Apache POI XML soures are on Excel 2007 level, that extension is not directly available.
One could have following method, which sets the XML for that extension:
And then in main code:
That leads to red color for the negative bar in current Excel versions.