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!!



Subscribe to GET LATEST ARTICLES!


advertise here

Related

Java 2096067824376335812

Post a Comment

  1. Hi

    This article is very much suited for my requirement, i am able to populate chart in excel with dynamic values.But now the problem i am facing
    is in this article the legend series name sales we added in template but i need to update the legend series name dynamically and even i need to set the dynamic axis labels.
    can you help if you have any idea how to set legend series value and axis label name dynamically using Apache POI.

    ReplyDelete
  2. Can any one help/suggest with above post ASAP ..

    ReplyDelete
    Replies
    1. Hi Ria,

      There is no way you can manipulate or modify an excel chart directly from Apache POI. But you can have the series name and axis labels in your template to display dynamic values. I mean to say you can place series name in any cell and when specifying series name you can point to that cell instead of writing a name. From Apache POI, modify the value of cell that is pointing to the series name.

      Check this post, this might help,

      http://www.techrepublic.com/blog/msoffice/two-ways-to-build-dynamic-charts-in-excel/7836

      Thanks,
      Priya

      Delete
    2. Hi Priya,

      Thanks for your response as we are looking for this one.We will try this approach.
      One question for you can you have any idea about Aspose library..as this will be used for Excel,ppt operations how this library will be efficient.


      Thanks,
      Chandra Shekar.k

      Delete
    3. I have not tried Aspose library yet and the only reason is,"IT IS NOT FREE". Try searching for reviews on that library.

      Delete
  3. HI,

    My requirement is to read the excel and convert print area to HTML using apache POI. I am able to convert text and numeric values to html by using POI. Is there any way read graphs from excel sheet using POI? also I am able to get the pictures from excel sheet but i am not able to get the start and end column/row of the image.

    Can anybody please help me is there a way to get images and graphs.

    ReplyDelete
  4. Hi, About the point 3, i can't add in the 'Series values' field type, =Sheet1!Sales , when i click Ok a message is displayed about of not valid references. I did the point 2.

    ReplyDelete
  5. Hi thank u very much for this example ,can u please upload creating this excel also file though java code...
    Then its very useful to generate monthly or daily report through excel...

    Hope u ll ..

    thank uuu

    ReplyDelete
  6. You can also create chart by using Java library for Excel from Aspose. This library offers many other features and many sample codes that java developers can use in their API.

    ReplyDelete
    Replies
    1. Yes you are right - It is not open source though. This post is meant for those who cannot afford Aspose.

      Delete
  7. Hi,
    I am stuck with my client wanting to export editable chart to excel from a web page.
    I am using JQuery to display the chart on web and I know there is no way to export such a chart with data points on to an excel.

    I ran your example, it worked beautifully. My question though is, that you created a named range from 2-13.
    I want my range to be dynamic. say from 2 to N, depending upon my data set. Is this possible using your example. Coz , If I am thinking right maximum rows your example will support will be 12?? Is it so??

    ReplyDelete
  8. Just extended the loop, it is adding up the data and also set the rowNum to 15.. NOw both the chart and teh data are extending... Gr8 example... Now I have to see , if I can create line graphs(plotting times), as required by my client

    ReplyDelete
  9. Hi Priya, your example is wonderful. But I have a problem when I tried to create multiple series chart where the series itself could be added dynamically. Always get a reference error. The table sample is in http://goo.gl/DlyXB9
    And from the table I'd like to add Category(Kategori) Dynamically. How can I do this? Thanks.

    ReplyDelete
  10. Hi priya, does the example works for excel 2003 as well? I can export the chart out to excel. I was wondering when updating the named range, do we have to add in the offset function as well?
    And how to go about editing the source data of the exported chart using poi?
    Thanks,
    Justin

    ReplyDelete
  11. Hi Priya,

    Can you please let me know if I want to change width of the chart(which is present in the template) using java code then how we can achieve it??

    ReplyDelete
  12. Hi priya, finally I have found a solution to export the chart to excel. I am using java poi 3.8. Initially, I have followed your example:

    1) Created a template with the chart object, setting the named range of the chart (hard coded)
    2) Populate the data using the java poi
    3) Set the named ranges using the java poi
    4) Export the chart data
    5) The excel chart is displayed based on the named ranges

    It works perfectly, but I have a problem adding the chart series using java poi. This is because my x-axis
    names are always different, thus hard coding the named range is not that suitable for me. I have found that
    it is really difficult to edit the chart properties using java poi, e.g. adding of the chart series, rename the name
    of the legend etc. In the end, I have written a excel macro, to create chart series based on the populated data.
    Upon activate the excel workbook, the macro is called and created the chart series automatically.
    It works perfectly, the users can change the data, save the file and when open the file, the chart data is
    updated, with the help of the macro.

    1) Created a excel template xlsm with the empty chart object (no name range set)
    2) Excel macro is written in the template itself to create chart series automatically
    2) Populate the data using the java poi
    4) Export the chart data
    5) The excel chart with the data is displayed with the help of the macro

    Thanks priya for your wonderful example once again.
    Hope this will benefit other peope out there. Cheers!



    ReplyDelete
  13. Thanks for the tutorial.

    I'm having an issue with ChartSample creation. Instead of Dates columns, I have Strings.

    I'm unable to select Horizontal Axis Labels when selecting chart data source.

    Any help

    ReplyDelete
    Replies
    1. Solved it!

      Had to use formula COUNTA when creating OFFSET:

      =OFFSET($A$2,,,COUNTA($A$2:$A$13))

      Hope it helps someone :)

      Delete

emo-but-icon

Currency Converter

Built using AngularJS and ASP.NET Web API

SUBSCRIBE


item