Create charts in Excel using Java (Apache POI)

I have used Apache POI java library for reading and writing excel sheets from Java program several times. I was thinking of ways to create column charts in Excel sheets based on the data present in the excel sheet, from a Java program. I learnt that, as of now, there is no straight forward approach to create charts in excel using Apache POI as stated in the limitations section of official POI website. There are two reasons why I wanted to use only Apache POI library among all the java excel libraries that are available. One reason is I wanted the Java library to be free and the other reason is, I wanted the library to be performance effective, simple to use and mainly compatible with Excel 2007 format. So, I tried doing a quick research on this, and found two indirect options to achieve the same.

1. To generate a chart using Jfreechart - Java Charting library and write the chart to the target excel sheet.

2. To create an excel sheet with dynamic chart using excel named ranges(empty datasource) and then use this excel sheet as a template to create excel sheets with charts by just modifying the reference for named ranges to point the chart data from Java program using POI.

I went for the second option because the look & feel plus functionality of Jfreechart is not very convincing as an excel chart is. Still If you want to learn or implement the first option, take a look at this. In this post, I am going to explain how to generate excel charts using another excel sheet as a template or reference from Java program using Apache POI XSSF.

Steps to Reproduce:

1. First we have to create a sample excel sheet with named ranges and a chart using the named range as the data source. We are going to fill this excel sheet with the chart data and then edit these named ranges to point to the cells where we have filled the data for the chart. This video explains how to create named ranges in excel sheet clearly. I recommend you to refer to that video or this post, if you come across any doubts on creating named ranges in excel sheet.

Create a new excel sheet and save it as "ChartSample.xlsx".

2. Identify the data from which you want to make dynamic charts. In our case, we are trying to generate a monthly sales report with data from two columns, 'Date' and 'Sales'. Open the newly created excel sheet and create two named ranges with dummy values using OFFSET function.

For example, say the range of cells for Date range is from $A$2:$A$A$13 and the range of cells for Sales range is from $B$2:$B$B$13, then to create two named ranges namely 'Date' and 'Sales',

Open 'ChartSample.xlsx' sheet and insert a dummy value at cells A2 and B2 for sample Date and Sales as shown below. We will later get rid of this using code.

Press "Ctrl+F3", this will open 'Name Manager' dialogue.

Click on 'New' button and in the "New Name" dialogue box, type 'Date' in the Name textbox and type =OFFSET($A$2,,,COUNT($A$2:$A$13)) in the Refers to textbox and click "OK".

Repeat the above step and create a named range for Sales data, with Name as "Sales" and type =OFFSET($B$2,,,COUNT($B$2:$B$13)) in the Refers to textbox and click "OK".

3. Insert a chart in "ChartSample.xlsx" file and assign the two named ranges that we created in the previous step as the chart data source. In our case, I have inserted a column chart to the excel sheet and assigned the named ranges as data source as explained in the below steps.

Insert a column chart to the excel sheet by clicking on the "Column chart" in the Insert menu tab.

Right click on the chart and click on 'Select Data' option to open the 'Select Data Source' dialogue box.

Click the 'Add' button under the Legend Entries box and this will open the 'Edit Series' dialogue box where we need to provide values for the Series Name, in our case ' Sales' and then the cells that contain data for the series, in our case the named range called 'Sales'. Type "Sales" in the "Series Name" field and in the 'Series values' field type, =Sheet1!Sales and click OK. Note that in the values field, Sheet1 denotes the name of the sheet, change this with the name of the sheet you are currently working on and Sales denotes the named range we created in the second step.

Now add "Date" name range as axis labels by clicking "Edit" button in the "select data source' dialogue and give value of axis value range as =Sheet1!Date and click OK.

Add all the formatting to the chart that is required such as displaying data labels, style of the chart, horizontal alignment of the axis labels etc to the chart in the sample excel sheet, so that it will reflect in the excel sheets you produce using the sample sheet.
Now save the excel sheet and close it.

4. Now the sample/template excel sheet is ready and we can go ahead and produce excel sheets using the sample excel sheet using Apache POI XSSF. To do this, first download the Apache POI java library from here.

5. Create a Java Project and add the following jar files to your class path.

poi-x.x.jar
poi-ooxml-x.x.jar
poi-ooxml-schemas-x.x.jar
xmlbeans-x.x.x.jar
dom4j-x.x.jar

6. Create a class and name it as "CreateExcelFile" and add below code to it.

package com.programmingfree.excelexamples;

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

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class CreateExcelFile {
 
 public static void main(String args[]) throws IOException, InvalidFormatException
 {
  generateExcelChart();
 }
 
 
 private static void generateExcelChart() throws IOException, InvalidFormatException {
     
  int deface=1;
  int rowNum=7;
  //Load sample excel file
  Workbook workbook = new XSSFWorkbook(OPCPackage.open(new FileInputStream("d:/ChartSample.xlsx"))); // or sample.xls
  CreationHelper createHelper = workbook.getCreationHelper();
  Sheet sh=workbook.getSheetAt(0);
  String sheetName=sh.getSheetName();
  
  //create cell style for date format
  CellStyle cellStyle = workbook.createCellStyle();
     cellStyle.setDataFormat(
         createHelper.createDataFormat().getFormat("d/m/yyyy"));
     
     //Clear dummy values
    sh.getRow(1).getCell(0).setCellValue("");
    sh.getRow(1).getCell(1).setCellValue("");
    
    //Set headers for the data
    sh.createRow(0).createCell(2).setCellValue("Date");
    sh.getRow(0).createCell(3).setCellValue("Sales");
    
     Cell datecell = null;
  Cell salescell = null;
  
  // Populate C2 to C8 and D2 to D8 with chart data
  for(int i=1;i<=7;i++){
   Row r=sh.getRow(i);
      if(r==null)
       r=sh.createRow(i);
       datecell=r.getCell(2);
       salescell=r.getCell(3);
      switch(i){
     
      case 1:
       if(datecell==null){
       datecell=r.createCell(2);
       datecell.setCellValue("1/1/2012");
       datecell.setCellStyle(cellStyle);
       }
       else{
       
        datecell.setCellValue("1/1/2012");
        datecell.setCellStyle(cellStyle);
       }
       if(salescell==null)
       r.createCell(3).setCellValue(2000);
       else
        salescell.setCellValue(2000);
       break;
      case 2:
       if(datecell==null){
       datecell=r.createCell(2);      
       datecell.setCellValue("1/2/2012");
       datecell.setCellStyle(cellStyle);
       }
        else{
         datecell.setCellValue("1/2/2012");
         datecell.setCellStyle(cellStyle);
        }
        if(salescell==null)
        r.createCell(3).setCellValue(1000);
        else
         salescell.setCellValue(1000);
        break;
      case 3:
       if(datecell==null){
        datecell=r.createCell(2);
        datecell.setCellValue("1/3/2012");
        datecell.setCellStyle(cellStyle);
       }
        else{
         datecell.setCellValue("1/3/2012");
         datecell.setCellStyle(cellStyle);
        }
       
        if(salescell==null)
        r.createCell(3).setCellValue(4000);
        else
         salescell.setCellValue(4000);
        break;
      case 4:
       if(datecell==null){
        datecell=r.createCell(2);
       datecell.setCellValue("1/4/2012");
       datecell.setCellStyle(cellStyle);
       }
        else{
         datecell.setCellValue("1/4/2012");
         datecell.setCellStyle(cellStyle);
        }
        if(salescell==null)
        r.createCell(3).setCellValue(2500);
       else
         salescell.setCellValue(2500);
        break;
      case 5:
       if(datecell==null){
        datecell=r.createCell(2);
      
       datecell.setCellValue("1/5/2012");
       datecell.setCellStyle(cellStyle);
      }
        else{
     
         datecell.setCellValue("1/5/2012");
         datecell.setCellStyle(cellStyle);
        }
        if(salescell==null)
        r.createCell(3).setCellValue(3000);
        else
         salescell.setCellValue(3000);
        break;
      case 6:
       if(datecell==null){
        datecell=r.createCell(2);
        
        datecell.setCellValue("1/6/2012");
        datecell.setCellStyle(cellStyle);
       }
        else{
         
         datecell.setCellValue("1/6/2012");
         datecell.setCellStyle(cellStyle);
        }
        if(salescell==null)
        r.createCell(3).setCellValue(4000);
        else
         salescell.setCellValue(4000);
        break;
      case 7:
       if(datecell==null){
        datecell=r.createCell(2);
       datecell.setCellStyle(cellStyle);
       datecell.setCellValue("1/8/2012");
       }
        else{
         
         datecell.setCellValue("1/8/2012");
         datecell.setCellStyle(cellStyle);
        }
        if(salescell==null)
        r.createCell(3).setCellValue(5000);
        else
         salescell.setCellValue(5000);
        break;
       
        
       default:
        System.out.println("Invalid Input");
        break;
      }
   
     }
  
     //Search for named range
           Name rangeCell = workbook.getName("Date");         
           //Set new range for named range 
           String reference = sheetName + "!$C$" + ( deface+1 ) + ":$C$" + (rowNum+deface);          
           //Assigns range value to named range
           rangeCell.setRefersToFormula(reference);

           rangeCell = workbook.getName("Sales");            
           reference = sheetName + "!$D$"+(deface+1) + ":$D$" + (rowNum+deface);
           rangeCell.setRefersToFormula(reference); 
     
           FileOutputStream f = new FileOutputStream("d:/Monthly_Sales.xlsx");
     workbook.write(f);
     f.close();
     
     System.out.println("Number Of Sheets" + workbook.getNumberOfSheets());
     Sheet s = workbook.getSheetAt(0);
     System.out.println("Number Of Rows:" + s.getLastRowNum());
 }
}

Note that in the above code we are filling chart data in the third and fourth column of excel sheet. We are then changing the named reference we created on first and second column to point to the new data columns. The final output excel sheet generated by this code looks like this,

Please leave your comments and queries about this post in the comment sections in order for me to improve my writing skills and to showcase more useful posts.Thanks for reading!!