How to set Fill properties of Chart Area in Apache POI to "NO FILL"?

Question

I created a chart by using Apache POI as below:

// creata anchor
    XSSFDrawing drawing = sheet.createDrawingPatriarch();
    XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, FROM_COLUMN, FROM_ROW, TO_COLUMN, TO_ROW);

    // create chart
    XSSFChart chart = drawing.createChart(anchor);

Now, I need to set the Fill Properties of Chart Area to "NO FILL" in java. Is there any way to do that?

score 2 · Answer 1 · answered Mar 04 '21 at 13:33

A chart is a shape having shape properties. And one of that shape properties is fill property. So we need get the chart's shape properties to set fill properties.

XDDFChart provides XDDFChart.getOrAddShapeProperties. But this gets the shape properties of the chart's plot area. But to set the chart's shape properties we need the shape properties of the chart space. Since there is not a method to get this, one would must get it like that:

private XDDFShapeProperties getOrAddChartSpaceShapeProperties(XDDFChart chart) {
  if (chart.getCTChartSpace().getSpPr() == null) chart.getCTChartSpace().addNewSpPr();
  return new XDDFShapeProperties(chart.getCTChartSpace().getSpPr());
}

If we have XDDFShapeProperties, we can set fill properties to XDDFNoFillProperties.

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.chart.LegendPosition;
import org.apache.poi.xddf.usermodel.chart.XDDFChart;
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.XDDFPieChartData;
import org.apache.poi.xddf.usermodel.chart.ChartTypes;
import org.apache.poi.xddf.usermodel.XDDFNoFillProperties;
import org.apache.poi.xddf.usermodel.XDDFShapeProperties;
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 PieChart {
    
  private static void setRoundedCorners(XDDFChart chart, boolean setVal) {
    if (chart.getCTChartSpace().getRoundedCorners() == null) chart.getCTChartSpace().addNewRoundedCorners();
    chart.getCTChartSpace().getRoundedCorners().setVal(setVal);
  }
  
  private static XDDFShapeProperties getOrAddChartSpaceShapeProperties(XDDFChart chart) {
    if (chart.getCTChartSpace().getSpPr() == null) chart.getCTChartSpace().addNewSpPr();
    return new XDDFShapeProperties(chart.getCTChartSpace().getSpPr());
  }
  
  public static void main(String[] args) throws IOException {
    try (XSSFWorkbook wb = new XSSFWorkbook()) {
      XSSFSheet sheet = wb.createSheet("piechart");
      final int NUM_OF_ROWS = 2;
      final int NUM_OF_COLUMNS = 10;

      // Create a row and put some cells in it. Rows are 0 based.
      Row row;
      Cell cell;
      for (int rowIndex = 0; rowIndex < NUM_OF_ROWS; rowIndex++) {
        row = sheet.createRow((short) rowIndex);
        for (int colIndex = 0; colIndex < NUM_OF_COLUMNS; colIndex++) {
          cell = row.createCell((short) colIndex);
          if (rowIndex == 0) cell.setCellValue("Cat " + (colIndex + 1));
          else cell.setCellValue((colIndex + 1) * (rowIndex + 1));
        }
      }

      XSSFDrawing drawing = sheet.createDrawingPatriarch();
      XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 4, 10, 25);

      XSSFChart chart = drawing.createChart(anchor);
      //XDDFShapeProperties shapeProperties = chart.getOrAddShapeProperties(); //gets shape properties of plot area
      XDDFShapeProperties shapeProperties = getOrAddChartSpaceShapeProperties(chart);
      shapeProperties.setFillProperties(new XDDFNoFillProperties());
      
      chart.setTitleText("Pie Chart");
      chart.setTitleOverlay(false);
      setRoundedCorners(chart, false);
      
      XDDFChartLegend legend = chart.getOrAddLegend();
      legend.setPosition(LegendPosition.TOP_RIGHT);

      XDDFDataSource<String> cat = XDDFDataSourcesFactory.fromStringCellRange(sheet,
          new CellRangeAddress(0, 0, 0, NUM_OF_COLUMNS - 1));
      XDDFNumericalDataSource<Double> val = XDDFDataSourcesFactory.fromNumericCellRange(sheet,
          new CellRangeAddress(1, 1, 0, NUM_OF_COLUMNS - 1));

      XDDFChartData data = chart.createData(ChartTypes.PIE, null, null);
      data.setVaryColors(true);
      data.addSeries(cat, val);
      chart.plot(data);

      // Write the output to a file
      try (FileOutputStream fileOut = new FileOutputStream("ooxml-pie-chart.xlsx")) {
        wb.write(fileOut);
      }
    }
  }
}

This works using current apache poi versions 4.1.2 and 5.0.0.

How to set Fill properties of Chart Area in Apache POI to "NO FILL"?

1 Answers1