How to create a surface chart with Apache POI?

Question

Does anyone know how to create a surface chart with Apache POI? Unfortunately I could not find an example anywhere. I tried to use other chart examples but without success.

Answer 1

Since XDDF was introduced with apache poi 4, this should be used. And XDDFChartData has a subclass XDDFSurface3DChartData.

What one needs to know when using:

A surface 3D chart needs an additional series axis. And this series axis needs to be defined for the XDDFSurface3DChartData.

Minimal example which creates a Excel sheet having a surface 3D chart. This is tested and works using the current apache poi 5.2.2.

import java.io.FileOutputStream;

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 CreateExcelXDDFSurface3DChart {

 public static void main(String[] args) throws Exception {
  try (XSSFWorkbook document = new XSSFWorkbook()) {
   XSSFSheet sheet = document.createSheet("SurfaceChart");
        
   // create the data
   String[] categories = new String[] { "c1", "c2", "c3", "c4", "c5", "c6", "c7", "c8", "c9" };
   Double[] values1 = new Double[] { 10d, 20d, 10d, 15d, 12d, 20d, 10d, 18d, 19d };
   Double[] values2 = new Double[] { 20d, 10d, 15d, 20d, 11d, 17d, 18d, 20d, 10d };
   Double[] values3 = new Double[] { 14.5d, 14d, 13.5d, 13d, 12.5d, 12d, 11.5d, 11d, 10.5d };
   int r = 0;
   for (String cat : categories) {
    sheet.createRow(r).createCell(0).setCellValue(cat);
    sheet.getRow(r).createCell(1).setCellValue(values1[r]);
    sheet.getRow(r).createCell(2).setCellValue(values2[r]);
    sheet.getRow(r).createCell(3).setCellValue(values3[r]);
    r++;
   }

   // create the chart
   XSSFDrawing drawing = sheet.createDrawingPatriarch();
   XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 5, 0, 20, 30);
   XDDFChart chart = drawing.createChart(anchor);
      
   // create data sources
   int numOfPoints = categories.length;
   XDDFDataSource<String> categoriesData = XDDFDataSourcesFactory.fromStringCellRange(sheet, new CellRangeAddress(0, numOfPoints-1, 0, 0));
   XDDFNumericalDataSource<Double> valuesData1 = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(0, numOfPoints-1, 1, 1));
   XDDFNumericalDataSource<Double> valuesData2 = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(0, numOfPoints-1, 2, 2));
   XDDFNumericalDataSource<Double> valuesData3 = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(0, numOfPoints-1, 3, 3));
   
   // surface 3D chart
   XDDFCategoryAxis categoryAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
   XDDFValueAxis valueAxis = chart.createValueAxis(AxisPosition.LEFT);
   valueAxis.setCrosses(AxisCrosses.AUTO_ZERO);
   // surface 3D chart needs a series axis
   XDDFSeriesAxis seriesAxis = chart.createSeriesAxis(AxisPosition.BOTTOM);
   seriesAxis.setCrosses(AxisCrosses.AUTO_ZERO);
   seriesAxis.crossAxis(valueAxis);
   
   XDDFChartData data = chart.createData(ChartTypes.SURFACE3D, categoryAxis, valueAxis);
   // surface 3D chart needs the series axis applied to the XDDFSurface3DChartData
   ((XDDFSurface3DChartData)data).defineSeriesAxis(seriesAxis);
   
   XDDFChartData.Series series = data.addSeries(categoriesData, valuesData1);
   series.setTitle("Series 1", null);
   series = data.addSeries(categoriesData, valuesData2);
   series.setTitle("Series 2", null);
   series = data.addSeries(categoriesData, valuesData3);
   series.setTitle("Series 3", null);
   
   chart.plot(data);
   
   // set legend 
   XDDFChartLegend legend = chart.getOrAddLegend();
   legend.setPosition(LegendPosition.BOTTOM);

   // Write the output to a file
   try (FileOutputStream fileOut = new FileOutputStream("./CreateExcelXDDFSurface3DChart.xlsx")) {
    document.write(fileOut);
   }
  }
 }
}

Btw.: I have never understood where a surface chart will be helpful for data presentation.