

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.