Excel file with Charts in Java using Apache POI

Apache POI is a powerful Java library to work with different Microsoft Office file formats such as Excel, Power point, Visio, MS Word etc. The Apache POI Project's mission is to create and maintain Java APIs for manipulating various file formats based upon the Office Open XML standards (OOXML) and Microsoft's OLE 2 Compound Document format (OLE2). In short, you can read and write MS Excel files using Java. In addition, you can read and write MS Word and MS PowerPoint files using Java. Apache POI is your Java Excel solution (for Excel 97-2008). We have a complete API for porting other OOXML and OLE2 formats and welcome others to participate.

POI has a limitation that we cannot generate charts in Excel using it. So, we have to use a xls template which contains the desired graph and update the row/cells which are used by the graph.

Purpose of this article :

To demonstrate how to avoid the limitation of POI to generate charts in an Excel.

Tools & Technologies:

1. Java JDK 1.5 or above (jdk download)
2. Apache POI library v3.8 or above (poi download)
3. Eclipse 3.2 above (eclipse download)

Let’s get started.

1. Create a new Java project

Create new java project in eclipse with auto generated main function.

2. Add Apache POI dependency

Make sure to include apache poi and apache commons jar file to your project. Add required JAR files in your classpath.

1. Download poi-3.8.jar file.
2. Download commons- io-2.0.jar
3. Include these files in your projects class path.

3. Read Template Excel File

To read an excel file, Apache POI provides certain easy-to-use APIs. In below sample code we use different classes from POI library to read content of cell from excel file. This is for quick reference.
 

   import org.apache.commons.io.FileUtils;
   import org.apache.poi.ss.usermodel.*; 
   //Get the template Excel file.
     File outputTemplate = new File("C:\\test.xls");
   //Get the workbook instance for XLS file
     Workbook templateWorkbook = WorkbookFactory.create(FileUtils.openInputStream(outputTemplate));
   //Get first sheet from the workbook
      Sheet templateSheet = templateWorkbook.getSheetAt(0); 

4. Update Template Excel File

Updating the template excel file is straight forward. Open the excel using the API that we discussed above and set the cell’s value. One thing we need to note here is that we can update the excel file only when we close it first.

The rows/columns which are used by our graph to show data are specified in integer arrays.

     int rowsToUpdate[] = {
            2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20
        };
     int columnsToUpdate[] = {
      9,10
        };
     for(int i = 0; i < rowsToUpdate.length; i++)
     {
           Row row = templateSheet.getRow(rowsToUpdate[i]);
                for(int j = 0; j < columnsToUpdate.length; j++)
                {                                         // logic to get the count here
                  Cell cell = row.getCell(columnsToUpdate[j]);
                  cell.setCellValue(count);               }
      } 

Once the data is updated, it would automatically reflect in the graph.