Plot multiple line charts in one excel sheet using Apache poi

Question

I want to generate multiple chart with different series in a one excel sheet. However i can plot one chart with the following code but when i create second XSSFChart chart and XSSFClientAnchor anchor in the following code it fails to plot the chart in excel. How to plot two or more line charts in one excel. What is wrong in the following code?

.

public class MultipleChartWithThreeLines {
    private static XDDFLineChartData.Series addLineSeriesToChartData(XDDFChartData chartData, XSSFSheet sheet, String categoryDataRef, String valueDataRef, String seriesTitleRef, PresetColor lineColor) {

        XDDFDataSource<Double> categoryData = XDDFDataSourcesFactory.fromNumericCellRange(sheet, CellRangeAddress.valueOf(categoryDataRef));
        XDDFNumericalDataSource<Double> valueData = XDDFDataSourcesFactory.fromNumericCellRange(sheet, CellRangeAddress.valueOf(valueDataRef));

        XDDFLineChartData.Series series = (XDDFLineChartData.Series) chartData.addSeries(categoryData, valueData);
        series.setTitle("", new CellReference(seriesTitleRef)); // https://stackoverflow.com/questions/21855842
        series.setSmooth(false); // https://stackoverflow.com/questions/29014848

        // define data-point marker
        series.setMarkerStyle(MarkerStyle.CIRCLE); // https://stackoverflow.com/questions/39636138

        // define line color
        // https://stackoverflow.com/questions/24676460
        XDDFShapeProperties shapeProperties = series.getShapeProperties();
        if (shapeProperties == null) {
            shapeProperties = new XDDFShapeProperties();
        }
        shapeProperties.setLineProperties(solidLineWithColor(lineColor));
        series.setShapeProperties(shapeProperties);

        // if your series have missing values like https://stackoverflow.com/questions/29014848
        // chart.displayBlanksAs(DisplayBlanks.GAP);

        return series;
    }

    private static XDDFLineProperties solidLineWithColor(PresetColor color) {
        XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(color));
        XDDFLineProperties line = new XDDFLineProperties();
        line.setFillProperties(fill);
        return line;
    }

    private static XDDFChartLegend addLegendToChart(XSSFChart chart) {
        XDDFChartLegend legend = chart.getOrAddLegend();
        legend.setPosition(LegendPosition.BOTTOM);

        return legend;
    }

    private static XSSFChart createChartOnSheet(XSSFSheet sheet, int col1, int row1, int col2, int row2) {
        XSSFDrawing drawing = sheet.createDrawingPatriarch();
        XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, col1, row1, col2, row2);
        XSSFChart chart = drawing.createChart(anchor);

        return chart;
    }

    private static XDDFChartAxis[] addAxesToChart(XSSFChart chart, String titleCategoryBottom, String titleValueLeft) {
        XDDFChartAxis[] axesCatVal = new XDDFChartAxis[4];

        // category axis at the bottom
        XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
        bottomAxis.setTitle(titleCategoryBottom); // https://stackoverflow.com/questions/32010765
        axesCatVal[0] = bottomAxis;

        // value axis at the left
        XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
        leftAxis.setTitle(titleValueLeft);
        leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);
        axesCatVal[1] = leftAxis;

        return axesCatVal;
    }


    private static void writeWorkbookToFile(XSSFWorkbook wb, String filename) throws IOException {
        FileOutputStream fileOut = new FileOutputStream(filename);
        wb.write(fileOut);
        fileOut.close();
    }

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

        String workbookFilename = "e:/Graph_5.xlsx"; //"e:/Graph_5.xlsx";

        // open workbook with data
        XSSFWorkbook wb = new XSSFWorkbook(workbookFilename);

        // draw chart with 3 lines
        XSSFSheet sheet = wb.getSheetAt(0);
        String sheetName = sheet.getSheetName();

        System.out.println("Drawing line-chart on sheet: " + sheetName);

        // create chart
        XSSFChart chart = createChartOnSheet(sheet, 6, 0, 25, 15);
        XSSFChart medianAngleChart = createChartOnSheet(sheet,10,17,25,15);

        // add legend to chart
        addLegendToChart(chart);
        addLegendToChart(medianAngleChart);


        // add value (left) and category (bottom) axes
        XDDFChartAxis[] axesCatVal = addAxesToChart(chart, "", "Inscribed Angle");       // Add data (as Line Chart)
        XDDFChartAxis[] axesCatVal_1 = addAxesToChart(medianAngleChart, "", "Median Angle");       // Add data (as Line Chart)

        // add line-chart data-collection to chart
        XDDFLineChartData chartData = (XDDFLineChartData) chart.createData(ChartTypes.LINE, axesCatVal[0], (XDDFValueAxis) axesCatVal[1]);
        XDDFLineChartData chartData_1 = (XDDFLineChartData) medianAngleChart.createData(ChartTypes.LINE, axesCatVal_1[0], (XDDFValueAxis) axesCatVal_1[1]);

        // Line-1
        XDDFLineChartData.Series series1 = addLineSeriesToChartData(chartData
                , sheet
                ,sheetName + "!$B$3:$B$66"
                , sheetName + "!$D$3:$D$66"
                , sheetName + "!$D$2"
                ,  PresetColor.RED
        );
        System.out.println("added line 1: \n" + series1);

        // Line-2
        XDDFLineChartData.Series series2 = addLineSeriesToChartData(chartData
                , sheet
                ,sheetName+"!$B$3:$B$66"
                , sheetName+"!$E$3:$E$66"
                , sheetName+"!$E$2"
                ,  PresetColor.GREEN
        );
        System.out.println("added line 2: \n" + series2);

        // Line-3
        XDDFLineChartData.Series series3 = addLineSeriesToChartData(chartData
                , sheet
                , sheetName+"!$B$3:$B$66"
                , sheetName+"!$F$3:$F$66"
                , sheetName+"!$F$2"
                ,  PresetColor.BLUE
        );
        System.out.println("added line 3: \n" + series3);

        //second chart Line-1
        XDDFLineChartData.Series series4 = addLineSeriesToChartData(chartData_1
                , sheet
                , sheetName+"!$B$3:$B$66"
                , sheetName+"!$G$3:$G$66"
                , sheetName+"!$G$2"
                ,  PresetColor.BLUE
        );
        System.out.println("added line 4: \n" + series4);

        chart.plot(chartData);
        medianAngleChart.plot(chartData_1);

        // save workbook

       writeWorkbookToFile(wb,"ChartWithThreeLines.xlsx");

        // close workbook
        wb.close();
    }
}

@james Z: Axel Richter is my sir... – sachin Mar 16 '19 at 08:36 — sachin, Mar 16 '19 at 08:36

score 1 · Answer 1 · answered Mar 15 '19 at 21:10

Not really clear where the code you are showing makes something wrong. It is really hard to follow the splitting into multiple methods.

But here is an complete example which maybe shows the simplest method to do what you wants.

Code:

import java.io.*;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.util.*;
import org.apache.poi.xddf.usermodel.*;
import org.apache.poi.xddf.usermodel.chart.*;

public class CreateExcelTwoLineCharts {

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

   // create the data
   XSSFSheet sheet = wb.createSheet("Sheet1");
   Row row;
   Cell cell;
   row = sheet.createRow(0);
   row.createCell(0).setCellValue("Months");
   row.createCell(1).setCellValue("Mountain View");
   row.createCell(2).setCellValue("New York");
   row.createCell(3).setCellValue("Washington");
   row.createCell(4).setCellValue("England");
   row.createCell(5).setCellValue("New Zeland");
   row.createCell(6).setCellValue("Australia");
    for (int r = 1; r < 13; r++) {
    row = sheet.createRow(r);
    for (int c = 0; c < 7; c++) {
     cell = row.createCell(c);
     if (c == 0) cell.setCellValue(r);
     else cell.setCellFormula("RANDBETWEEN(50, 99)");
    }
   }

   // create data sources
   XDDFDataSource<Double> months = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(1, 12, 0, 0));
   int c = 1;
   XDDFNumericalDataSource<Double> mView = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(1, 12, c, c++));
   XDDFNumericalDataSource<Double> nYork = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(1, 12, c, c++));
   XDDFNumericalDataSource<Double> washingt = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(1, 12, c, c++));
   XDDFNumericalDataSource<Double> engl = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(1, 12, c, c++));
   XDDFNumericalDataSource<Double> nZeal = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(1, 12, c, c++));
   XDDFNumericalDataSource<Double> austral = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(1, 12, c, c++));

   // create sheets drawing
   XSSFDrawing drawing = sheet.createDrawingPatriarch();

   // needed objeccts for the charts
   XSSFClientAnchor anchor;
   XSSFChart chart;
   XDDFChartLegend legend;
   XDDFCategoryAxis bottomAxis;
   XDDFValueAxis leftAxis;
   XDDFChartData data;
   XDDFChartData.Series series;

//======first line chart============================================================
   // create anchor
   anchor = drawing.createAnchor(0, 0, 0, 0, 8, 0, 14, 12); // anchor col8,row0 to col14,row12
   // create chart
   chart = drawing.createChart(anchor);
   legend = chart.getOrAddLegend();
   legend.setPosition(LegendPosition.BOTTOM);

   // create the axes
   bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
   leftAxis = chart.createValueAxis(AxisPosition.LEFT);
   leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);

   // create chart data
   data = chart.createData(ChartTypes.LINE, bottomAxis, leftAxis);
   // create series
   series = data.addSeries(months, mView);
   series.setTitle("Mountain View", new CellReference(sheet.getSheetName(), 0, 1, true, true));
   series = data.addSeries(months, nYork);
   series.setTitle("New York", new CellReference(sheet.getSheetName(), 0, 2, true, true));
   series = data.addSeries(months, washingt);
   series.setTitle("Washington", new CellReference(sheet.getSheetName(), 0, 3, true, true));
   chart.plot(data);

   solidLineSeries(data, 0, PresetColor.BLUE);            
   solidLineSeries(data, 1, PresetColor.RED);            
   solidLineSeries(data, 2, PresetColor.GREEN); 

//======second line chart============================================================
   // create anchor
   anchor = drawing.createAnchor(0, 0, 0, 0, 8, 13, 14, 25); // anchor col8,row13 to col14,row25
   // create chart
   chart = drawing.createChart(anchor);
   legend = chart.getOrAddLegend();
   legend.setPosition(LegendPosition.BOTTOM);

   // create the axes
   bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
   leftAxis = chart.createValueAxis(AxisPosition.LEFT);
   leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);

   // create chart data
   data = chart.createData(ChartTypes.LINE, bottomAxis, leftAxis);
   // create series
   series = data.addSeries(months, engl);
   series.setTitle("England", new CellReference(sheet.getSheetName(), 0, 4, true, true));
   series = data.addSeries(months, nZeal);
   series.setTitle("New Zeland", new CellReference(sheet.getSheetName(), 0, 5, true, true));
   series = data.addSeries(months, austral);
   series.setTitle("Australia", new CellReference(sheet.getSheetName(), 0, 6, true, true));
   chart.plot(data);

   solidLineSeries(data, 0, PresetColor.BLUE);            
   solidLineSeries(data, 1, PresetColor.RED);            
   solidLineSeries(data, 2, PresetColor.GREEN);            

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

 private static void solidLineSeries(XDDFChartData data, int index, PresetColor color) {
  XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.from(color));
  XDDFLineProperties line = new XDDFLineProperties();
  line.setFillProperties(fill);
  XDDFChartData.Series series = data.getSeries().get(index);
  XDDFShapeProperties properties = series.getShapeProperties();
  if (properties == null) {
   properties = new XDDFShapeProperties();
  }
  properties.setLineProperties(line);
  series.setShapeProperties(properties);
 }
}

Result:

Interesting feedback to [my code](https://stackoverflow.com/a/55150124/5730279). If you find the __divide-and-conquer__ approach (split problem into methods) hard to read and follow, then I really welcome your suggestion for a refactoring as [edit to my solution](https://stackoverflow.com/posts/55150124/edit) :-) — hc_dev, Mar 16 '19 at 19:05
@hc_dev: Nothing against splitting a problem solution into multiple methods. But to understand the principles of a problem solution, one minimal complete working example often is better in my opinion. Once the principles are understand, the divide into multiple general usable methods is the next step then. — Axel Richter, Mar 17 '19 at 14:07

score 0 · Answer 2 · edited Dec 23 '19 at 21:58

Create a separate anchor for each chart. My graphing program wouldn't chart multiple line graphs until I declared each anchor in main

public static void main(String[] args) throws IOException, FileNotFoundException, InvalidFormatException {
    String file = "OriginalWindData - pract2.xlsx";
    XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(file));
    XSSFSheet sh = wb.getSheetAt(0);
    int[] anchor1 = {8,13,14,25};
    int[] anchor2 = {anchor1[2]+1,anchor1[1],anchor1[2]+1+6,anchor1[3]};
    XSSFClientAnchor anchr1 = sh.createDrawingPatriarch().createAnchor(0, 0, 0, 0, anchor1[0] , anchor1[1], anchor1[2], anchor1[3]);
    XSSFClientAnchor anchr2 = sh.createDrawingPatriarch().createAnchor(0, 0, 0, 0, anchor2[0] , anchor2[1], anchor2[2], anchor2[3]);
    //left,top,right,bottom
    int[][] dcoords1= {
            {4,0,4,52129},
            {6,0,6,52129},
            {8,0,8,52129}
    };
    int[][] dcoords2= {
            {4,0,4,52129},
            {6,0,6,52129}
    };
    //left top,right bottom
    //makeChart(sh,4,0,6,52129);
    makeChartv2(sh,dcoords1,anchr1);
    makeChartv2(sh,dcoords2,anchr2);
    try (FileOutputStream fileOut = new FileOutputStream("chartout.xlsx")) {
        wb.write(fileOut);
    }
    wb.close();
}

... hope this helps

Plot multiple line charts in one excel sheet using Apache poi

2 Answers2