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:
...
XDDFChartData.Series series1 = data.addSeries(months, values);
...
((XDDFBarChartData.Series) series1).setInvertIfNegative(false);
...
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 AxisCrossBetween for 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.
...
XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
...
leftAxis.setCrossBetween(AxisCrossBetween.BETWEEN);
...
Complete example:
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xddf.usermodel.PresetColor;
import org.apache.poi.xddf.usermodel.XDDFColor;
import org.apache.poi.xddf.usermodel.XDDFShapeProperties;
import org.apache.poi.xddf.usermodel.XDDFSolidFillProperties;
import org.apache.poi.xddf.usermodel.chart.AxisCrosses;
import org.apache.poi.xddf.usermodel.chart.AxisCrossBetween;
import org.apache.poi.xddf.usermodel.chart.AxisPosition;
import org.apache.poi.xddf.usermodel.chart.BarDirection;
import org.apache.poi.xddf.usermodel.chart.ChartTypes;
import org.apache.poi.xddf.usermodel.chart.LegendPosition;
import org.apache.poi.xddf.usermodel.chart.XDDFChart;
import org.apache.poi.xddf.usermodel.chart.XDDFBarChartData;
import org.apache.poi.xddf.usermodel.chart.XDDFLineChartData;
import org.apache.poi.xddf.usermodel.chart.XDDFPieChartData;
import org.apache.poi.xddf.usermodel.chart.XDDFCategoryAxis;
import org.apache.poi.xddf.usermodel.chart.XDDFChartData;
import org.apache.poi.xddf.usermodel.chart.XDDFChartLegend;
import org.apache.poi.xddf.usermodel.chart.XDDFDataSource;
import org.apache.poi.xddf.usermodel.chart.XDDFDataSourcesFactory;
import org.apache.poi.xddf.usermodel.chart.XDDFNumericalDataSource;
import org.apache.poi.xddf.usermodel.chart.XDDFValueAxis;
import org.apache.poi.xddf.usermodel.chart.XDDFTitle;
import org.apache.poi.xssf.usermodel.XSSFChart;
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 BarChart2 {
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);
}
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);
leftAxis.setCrossBetween(AxisCrossBetween.BETWEEN);
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);
((XDDFBarChartData.Series) series1).setInvertIfNegative(false);
// set series fill color
solidFillSeries(data, 0, PresetColor.BLUE);
// 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 = "column-chart-top-seven-countries.xlsx";
try (FileOutputStream fileOut = new FileOutputStream("./" + filename)) {
wb.write(fileOut);
}
}
}
}
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:
static void setInvertedColor(XDDFBarChartData.Series series, String rgb) throws Exception {
String extXML = ""
+ "<c:ext uri=\"{6F2FDCE9-48DA-4B69-8628-5D25D57E5C99}\" xmlns:a=\"http://schemas.openxmlformats.org/drawingml/2006/main\" xmlns:c=\"http://schemas.openxmlformats.org/drawingml/2006/chart\" xmlns:c14=\"http://schemas.microsoft.com/office/drawing/2007/8/2/chart\">"
+ " <c14:invertSolidFillFmt>"
+ " <c14:spPr>"
+ " <a:solidFill>"
+ " <a:srgbClr val=\"" + rgb + "\"/>"
+ " </a:solidFill>"
+ " </c14:spPr>"
+ " </c14:invertSolidFillFmt>"
+ "</c:ext>";
org.openxmlformats.schemas.drawingml.x2006.chart.CTBarSer cTBarSer = series.getCTBarSer();
if (cTBarSer.isSetExtLst()) cTBarSer.unsetExtLst();
org.openxmlformats.schemas.drawingml.x2006.chart.CTExtensionList cTExtensionList = cTBarSer.addNewExtLst();
org.apache.xmlbeans.XmlObject xlmObject = org.apache.xmlbeans.XmlObject.Factory.parse(extXML);
cTExtensionList.set(xlmObject);
}
And then in main code:
...
((XDDFBarChartData.Series) series1).setInvertIfNegative(true);
setInvertedColor((XDDFBarChartData.Series) series1, "FF0000");
...
That leads to red color for the negative bar in current Excel versions.