How to create pie & bar chart with Apache POI with XDDFDataSource values from custom List

0
votes

I'm new in Apache-poi, I'm working on a java project using Apache-poi to export data to excel spreadsheet.currently I am able to draw pie chart with fixed values.
I need to create a pie-chart in java with Apache POI, with custom values for series from list, without reading it from sheet.

Example code I have tried is below.

import java.io.FileNotFoundException;
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.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.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 PieChartSecond {

    public static void main(String[] args) throws FileNotFoundException, IOException {
        try (XSSFWorkbook wb = new XSSFWorkbook()) {

            XSSFSheet sheet = wb.createSheet("CountryPieChart");

            // 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.setCellValue("Russia("+17098242+")"
);

            cell = row.createCell((short) 1);
            cell.setCellValue("Canada(" + 9984670 +")"
);

            cell = row.createCell((short) 2);
            cell.setCellValue("USA(" +9826675+")"
);

            row = sheet.createRow((short) 1);

            cell = row.createCell((short) 0);
            cell.setCellValue(17098242);

            cell = row.createCell((short) 1);
            cell.setCellValue(9984670);

            cell = row.createCell((short) 2);
            cell.setCellValue(9826675);

            XSSFDrawing drawing = sheet.createDrawingPatriarch();
            XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 4, 7, 20);

            XSSFChart chart = drawing.createChart(anchor);
            chart.setTitleText("Countries");
            chart.setTitleOverlay(false);

            XDDFChartLegend legend = chart.getOrAddLegend();
            legend.setPosition(LegendPosition.TOP_RIGHT);

            XDDFDataSource<String> countries = XDDFDataSourcesFactory.fromStringCellRange(sheet,
                    new CellRangeAddress(0, 0, 0, 2));

            XDDFNumericalDataSource<Double> values = XDDFDataSourcesFactory.fromNumericCellRange(sheet,
                    new CellRangeAddress(1, 1, 0, 2));

//          XDDFChartData data = chart.createData(ChartTypes.PIE, null, null);
            XDDFChartData data = new XDDFPieChartData(chart.getCTChart().getPlotArea().addNewPieChart());
              data.setVaryColors(true);

            data.setVaryColors(true);
            data.addSeries(countries, values);
            chart.plot(data);

            // Write output to an excel file
            try (FileOutputStream fileOut = new FileOutputStream("pie-chart-countries.xlsx")) {
                wb.write(fileOut);
            }
        }

    }

}

below is my excel snapshotExcel Snapshot of above code

problem is here i'm setting the values to sheet and then fetching it to chart. i don't want to create a table, and i just directly want to insert my values as a list(since it can be changed dynamically) i.e
instead of below lines where i'm fetching values from cell, i need to fetch it from List<String> countries( list of countries) & List<Double> values and set it to data.addSeries(countries, values)

XDDFDataSource<String> countries = XDDFDataSourcesFactory.fromStringCellRange(sheet,
                    new CellRangeAddress(0, 0, 0, 2));

            XDDFNumericalDataSource<Double> values = XDDFDataSourcesFactory.fromNumericCellRange(sheet,
                    new CellRangeAddress(1, 1, 0, 2));

Since Values keep on changing as per changes in Db,is their any way to achieve this.

1
javaexcelapache-poi
XDDFDataSourcesFactory provides creating data sources from arrays too. But a spreadsheet should get it's chart data sources from cells in a sheet. And Google Sheets will not show a chart in a *.xlsx which gets its data not from a sheet. - Axel Richter
@AxelRichter could you please provide some more information, i'm very new to Apache poi. i tried with String[] elements = new String[] {"Russia","Canada","USA"}; String dataRange = "dataRange"; XDDFDataSource<String> countries = XDDFDataSourcesFactory.fromArray(elements, dataRange); but it's not correct and not getting any further - Ullas Sharma

1 Answers

2
votes

XDDFDataSourcesFactory provides creating data sources from arrays too. But that is mainly for creating charts in Word or PowerPoint which holds it's data sources in a embedded Excel sheet which is internally created from that arrays while creating the XWPFChartor XSLFChart.

A XSSFChart in a spreadsheet should get it's chart data sources from cells in a sheet. Maybe from a hidden sheet, but not from an array.

But of course it is possible.

Examples using apache poi 4.1.1.

For a pie chart:

import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.ss.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.*;

public class PieChartFromArray {

    public static void main(String[] args) throws IOException {
        try (XSSFWorkbook wb = new XSSFWorkbook()) {
            XSSFSheet sheet = wb.createSheet("barchart");

            XSSFDrawing drawing = sheet.createDrawingPatriarch();
            XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 0, 6, 15);

            XSSFChart chart = drawing.createChart(anchor);
            chart.setTitleText("Chart title");
            chart.setTitleOverlay(false);

            XDDFChartLegend legend = chart.getOrAddLegend();
            legend.setPosition(LegendPosition.RIGHT);

            XDDFDataSource<String> cat = XDDFDataSourcesFactory.fromArray(new String[]{"Russia","Canada","USA"});
            XDDFNumericalDataSource<Double> val = XDDFDataSourcesFactory.fromArray(new Double[]{170d, 99d, 98d});

            //XDDFChartData data = new XDDFPieChartData(chart.getCTChart().getPlotArea().addNewPieChart());
            XDDFChartData data = chart.createData(ChartTypes.PIE, null, null);
            data.setVaryColors(true);
            XDDFChartData.Series series = data.addSeries(cat, val);
            series.setTitle("Series", null);
            chart.plot(data);

            try (FileOutputStream fileOut = new FileOutputStream("ooxml-pie-chart.xlsx")) {
                wb.write(fileOut);
            }
        }
    }
}

And for a bar chart:

import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.ss.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.*;

public class BarChartFromArray {

    public static void main(String[] args) throws IOException {
        try (XSSFWorkbook wb = new XSSFWorkbook()) {
            XSSFSheet sheet = wb.createSheet("barchart");

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

            XSSFChart chart = drawing.createChart(anchor);
            chart.setTitleText("Chart title");
            chart.setTitleOverlay(false);

            XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
            bottomAxis.setTitle("cat");
            XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
            leftAxis.setTitle("val");
            leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);
            leftAxis.setCrossBetween(AxisCrossBetween.BETWEEN);

            XDDFDataSource<String> cat = XDDFDataSourcesFactory.fromArray(new String[]{"Russia","Canada","USA"});
            XDDFNumericalDataSource<Double> val = XDDFDataSourcesFactory.fromArray(new Double[]{170d, 99d, 98d});

            XDDFChartData data = chart.createData(ChartTypes.BAR, bottomAxis, leftAxis);
            data.setVaryColors(false);
            XDDFChartData.Series series = data.addSeries(cat, val);
            series.setTitle("Series", null);
            chart.plot(data);

            XDDFBarChartData bar = (XDDFBarChartData) data;
            bar.setBarDirection(BarDirection.COL);

            try (FileOutputStream fileOut = new FileOutputStream("ooxml-bar-chart.xlsx")) {
                wb.write(fileOut);
            }
        }
    }
}

Those charts hold their data as arrays in form of string literal formulas. Such as ={"Russia","Canada","USA"} and ={170,99,98} for example. This is a bad form of chart data storage. Google Sheets will not even show such a chart which gets its data not from a sheet but from such string literal formulas.

So don't do that. Instead put the data in a sheet. Put it in a hidden sheet, if the data shall not be visible.

Examples how to put chart's data in a separate sheet which possibly can be hidden then. This code also is using apache poi 4.1.1.

For a pie chart:

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.ss.util.CellReference;
import org.apache.poi.xddf.usermodel.chart.LegendPosition;
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.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;

import org.apache.poi.xssf.usermodel.DefaultIndexedColorMap;

public class PieChart {

  public static void main(String[] args) throws IOException {

    String[] categories = new String[]{"Russia","Canada","USA"};
    Double[] values = new Double[]{170d, 99d, 98d};

    try (XSSFWorkbook wb = new XSSFWorkbook()) {
      //the sheet for the chart
      XSSFSheet chartSheet = wb.createSheet("piechart");
      //the sheet for the data
      XSSFSheet dataSheet = wb.createSheet("data");
      //maybe hide the data sheet
      //wb.setSheetHidden(1, true);

      Row row;
      Cell cell;
      row = dataSheet.createRow(0);
      cell = row.createCell(1); cell.setCellValue("Series");
      for (int i = 1; i <= categories.length; i++) {
        row = dataSheet.createRow(i);
        cell = row.createCell(0); cell.setCellValue(categories[i-1]);
        cell = row.createCell(1); cell.setCellValue(values[i-1]);
      }

      XSSFDrawing drawing = chartSheet.createDrawingPatriarch();
      XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 0, 6, 15);

      XSSFChart chart = drawing.createChart(anchor);
      chart.setTitleText("Chart title");
      chart.setTitleOverlay(false);

      XDDFChartLegend legend = chart.getOrAddLegend();
      legend.setPosition(LegendPosition.RIGHT);

      XDDFDataSource<String> cat = XDDFDataSourcesFactory.fromStringCellRange(dataSheet,
          new CellRangeAddress(1, categories.length, 0, 0));
      XDDFNumericalDataSource<Double> val = XDDFDataSourcesFactory.fromNumericCellRange(dataSheet,
          new CellRangeAddress(1, categories.length, 1, 1));

      //XDDFChartData data = new XDDFPieChartData(chart.getCTChart().getPlotArea().addNewPieChart());
      XDDFChartData data = chart.createData(ChartTypes.PIE, null, null);
      data.setVaryColors(true);
      XDDFChartData.Series series = data.addSeries(cat, val);
      series.setTitle(dataSheet.getRow(0).getCell(1).getStringCellValue(), new CellReference(dataSheet.getRow(0).getCell(1)));
      chart.plot(data);

      // Do not auto delete the title; is necessary for showing title in Calc
      if (chart.getCTChart().getAutoTitleDeleted() == null) chart.getCTChart().addNewAutoTitleDeleted();
      chart.getCTChart().getAutoTitleDeleted().setVal(false);

      // Data point colors; is necessary for showing data points in Calc
      int pointCount = series.getCategoryData().getPointCount(); 
      for (int p = 0; p < pointCount; p++) {
        chart.getCTChart().getPlotArea().getPieChartArray(0).getSerArray(0).addNewDPt().addNewIdx().setVal(p);
        chart.getCTChart().getPlotArea().getPieChartArray(0).getSerArray(0).getDPtArray(p)
          .addNewSpPr().addNewSolidFill().addNewSrgbClr().setVal(DefaultIndexedColorMap.getDefaultRGB(p+10));
      }

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

And for a bar chart:

import java.io.FileOutputStream;

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.ss.util.CellReference;
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.AxisPosition;
import org.apache.poi.xddf.usermodel.chart.AxisCrossBetween;
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.XDDFBarChartData;
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.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 BarChart {

  public static void main(String[] args) throws Exception {

    String[] categories = new String[]{"Russia","Canada","USA"};
    Double[] values = new Double[]{170d, 99d, 98d};

    try (XSSFWorkbook wb = new XSSFWorkbook()) {
      //the sheet for the chart
      XSSFSheet chartSheet = wb.createSheet("barchart");
      //the sheet for the data
      XSSFSheet dataSheet = wb.createSheet("data");
      //maybe hide the data sheet
      //wb.setSheetHidden(1, true);

      Row row;
      Cell cell;
      row = dataSheet.createRow(0);
      cell = row.createCell(1); cell.setCellValue("Series");
      for (int i = 1; i <= categories.length; i++) {
        row = dataSheet.createRow(i);
        cell = row.createCell(0); cell.setCellValue(categories[i-1]);
        cell = row.createCell(1); cell.setCellValue(values[i-1]);
      }

      XSSFDrawing drawing = chartSheet.createDrawingPatriarch();
      XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, 0, 10, 15);

      XSSFChart chart = drawing.createChart(anchor);
      chart.setTitleText("Chart title");
      chart.setTitleOverlay(false);

      XDDFChartLegend legend = chart.getOrAddLegend();
      legend.setPosition(LegendPosition.RIGHT);

      XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
      bottomAxis.setTitle("cat");

      XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
      leftAxis.setTitle("val");
      leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);
      leftAxis.setCrossBetween(AxisCrossBetween.BETWEEN);

      XDDFDataSource<String> cat = XDDFDataSourcesFactory.fromStringCellRange(dataSheet,
          new CellRangeAddress(1, categories.length, 0, 0));
      XDDFNumericalDataSource<Double> val = XDDFDataSourcesFactory.fromNumericCellRange(dataSheet,
          new CellRangeAddress(1, categories.length, 1, 1));

      XDDFChartData data = chart.createData(ChartTypes.BAR, bottomAxis, leftAxis);
      data.setVaryColors(false);
      XDDFChartData.Series series = data.addSeries(cat, val);
      series.setTitle(dataSheet.getRow(0).getCell(1).getStringCellValue(), new CellReference(dataSheet.getRow(0).getCell(1)));
      solidFillSeries(series, PresetColor.BLUE);
      chart.plot(data);

      XDDFBarChartData bar = (XDDFBarChartData) data;
      bar.setBarDirection(BarDirection.COL);

      // Do not auto delete the title; is necessary for showing title in Calc
      if (chart.getCTChart().getAutoTitleDeleted() == null) chart.getCTChart().addNewAutoTitleDeleted();
      chart.getCTChart().getAutoTitleDeleted().setVal(false);

      try (FileOutputStream fileOut = new FileOutputStream("ooxml-bar-chart.xlsx")) {
        wb.write(fileOut);
      }
    }
  }

  private static void solidFillSeries(XDDFChartData.Series series, PresetColor color) {
    XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(color));
    XDDFShapeProperties properties = series.getShapeProperties();
    if (properties == null) {
      properties = new XDDFShapeProperties();
    }
    properties.setFillProperties(fill);
    series.setShapeProperties(properties);
  }
}