Excel read-writable Reports through Generics, Reflection and Apache POI

Introduction

In this article we use generics, reflection and Apache POI to construct a general utility to read and write a generic collection of objects to an Excel spreadsheet.

I was recently in a project that required a lot of data to be made available from the object model to both view and modify.  There was already a utility in place that would dump the information into text files.  This was nice but the number of columns of information that were required made text files difficult to use.  The information would be far easier to view and manipulate in a spreadsheet.  This led to the creation of the tool described in this article.

This is part one of a two part series.  In the second part we will utilize Annotations to refine the information exported to the Excel spreadsheet.

Apache POI

The Apache POI project provides a very simple tool for interacting with the Excel format.  The POI project extends beyond Excel servicing the Office Open XML standards (OOXML) and Microsoft’s OLE 2 Compound Document format (OLE2).  These formats also include those for MS Powerpoint and Microsoft Word.

The Object Model for interacting with Excel spreadsheets is very intuitive.  The primary classes from POI that we use are:

  • org.apache.poi.ss.usermodel.Cell;
  • org.apache.poi.ss.usermodel.Row;
  • org.apache.poi.ss.usermodel.Sheet;
  • org.apache.poi.ss.usermodel.Workbook;

 In the code snippet below these classes are utilized with the facilities of Reflection to write the contents of a Generic collection to an Excel file.  The code is fairly intuitive.

public <T> void writeReportToExcel(List<T> data) throws Exception {
                Sheet sheet = getWorkbook().createSheet(
                                data.get(0).getClass().getName());
                setupFieldsForClass(data.get(0).getClass());
                // Create a row and put some cells in it. Rows are 0 based.
                int rowCount = 0;
                int columnCount = 0;

                Row row = sheet.createRow(rowCount++);
                for (String fieldName : fieldNames) {
                        Cell cel = row.createCell(columnCount++);
                        cel.setCellValue(fieldName);
                }
                Class<? extends Object> classz = data.get(0).getClass();
                for (T t : data) {
                        row = sheet.createRow(rowCount++);
                        columnCount = 0;
                        for (String fieldName : fieldNames) {
                                Cell cel = row.createCell(columnCount);
                                Method method = classz.getMethod("get" + capitalize(fieldName));
                                Object value = method.invoke(t, (Object[]) null);
                                if (value != null) {
                                        if (value instanceof String) {
                                                cel.setCellValue((String) value);
                                        } else if (value instanceof Long) {
                                                cel.setCellValue((Long) value);
                                        } else if (value instanceof Integer) {
                                                cel.setCellValue((Integer)value);
                                        }else if (value instanceof Double) {
                                                cel.setCellValue((Double) value);
                                        }
                                }
                                columnCount++;
                        }
                }
        }

Reflection

We utilize reflection to set-up the columns.  Reflection is used to gather the fields associated to the target class.  These field names will also serve as the column headings too.  The information is retained and used to obtain the getter and setters for each field as we obtain and set these values.  There are two rules for classes to work with this utility :

  1. For each field there must be a semantically standard getter and setter associated with it.
  2. The fields must be of the supported domain types, currently: String, Long, Double and Integer.

Below is the code for gathering the field information.

private boolean setupFieldsForClass(Class<?> clazz) throws Exception {
        Field[] fields = clazz.getDeclaredFields();
        for (int i = 0; i < fields.length; i++) {
                fieldNames.add(fields[i].getName());
        }
        return true;
}

 

 

Generic

Below is the JUnit test case for writing a generic collection to the Excel spreadsheet.  As can be seen the only requirements made of the parameter to writeReportToExcel(List<T>) is that the collection be generic and the class of the generic collection follow the rules stated above above.  Internal to the utility, as described, we use the information from the parameter to extract the information required to construct the Excel table.

 The test harness  for this code is as follows.  Note that we can dump any class consisting of simple types: String, Integer, Long and Double.  It can be easily extended. The generic collection that is being exported to an Excel spread sheet is List<TestClass>.

public void testReadData() {
        ExcelReporter ExcelReport = new ExcelReporter("TestCaseData.xsl");
        try {
                List<TestClass> results = ExcelReport
                                .readData(TestClass.class.getName());
                for (TestClass testClass : results) {
                        System.out.println(testClass);
                }
        } catch (Exception e) {
                e.printStackTrace();
        }
}

@Test
public void testWriteReportToExcel() {
        setTestClasses(TestClass.getTestData());
        ExcelReporter excelReport = new ExcelReporter("TestCaseData.xsl");
        try {
                logger.info("The number of data elements is: "+getTestClasses().size());
                excelReport.writeReportToExcel(getTestClasses());
                excelReport.closeWorksheet();
        } catch (Exception e) {
                logger.error("Error: "+e.toString());
                fail();
        }
}

public List<TestClass> getTestClasses() {
        return testClasses;
}

 

Below is the the TestClass which is the class used in the Generic List.  Note that it generates data from the files/directories in the current directory of the executing program.

 

package com.persistent.utils.excel;

import java.io.File;
import java.util.ArrayList;
import java.util.List;

public class TestClass {
        private String name;
        private String path;
        private String directory;
        private Integer containedFiles;
        private Double doubleField;

        public TestClass() {
        }

        public TestClass(String name, String path, String directory,
                        Integer containedFiles, Double doubleField) {
                super();
                this.name = name;
                this.path = path;
                this.directory = directory;
                this.containedFiles = containedFiles;
                this.doubleField = doubleField;
        }

        public String getName() {
                return name;
        }

        public void setName(String name) {
                this.name = name;
        }

        public String getPath() {
                return path;
        }

        public void setPath(String path) {
                this.path = path;
        }

        public String getDirectory() {
                return directory;
        }

        public void setDirectory(String directory) {
                this.directory = directory;
        }

        public Integer getContainedFiles() {
                return containedFiles;
        }

        public void setContainedFiles(Integer containedFiles) {
                this.containedFiles = containedFiles;
        }

        public Double getDoubleField() {
                return doubleField;
        }
        public void setDoubleField(Double doubleField) {
                this.doubleField = doubleField;
        }

        @Override
        public String toString() {
                return "TestClass [containedFiles=" + containedFiles + ", directory="
                                + directory + ", doubleField=" + doubleField + ", name=" + name
                                + ", path=" + path + "]";
        }

        // Generate some sample data.
        public static List<TestClass> getTestData() {
                List<TestClass> testClasses = new ArrayList<TestClass>();

                String currentDirectory = System.getProperty("user.dir");
                File currentDirectoryFile = new File(currentDirectory);
                File[] files = currentDirectoryFile.listFiles();
                double dbl = 0;
                for (File file : files) {
                        TestClass testClass = null;
                        if (file.isDirectory()) {
                                testClass = new TestClass(file.getName(), file.getPath(),
                                                "directory", file.list().length,dbl++);
                        } else {
                                testClass = new TestClass(file.getName(), file.getPath(),
                                                "file", 0,dbl++);
                        }
                        testClasses.add(testClass);
                }
                return testClasses;
        }
}

 

Running the JUnit Tests

To run this program download the jar file from the link at the bottom of the article.  It was built in an Eclipse environment.  If you are using that IDE then import it and run the unit tests.  The output of the program is Spartan: yes there is lots of room here for enhancements but still it is useful as a tool in development as is.

As one is developing you can create classes for the purpose of reporting, according to the rules, and dump them to Excel spreadsheets.  Another feature of this utility is that each Collection writes to a tab which holds the name of its’ class. This means that you can write several different class collections to the same workbook and see their data on separate tabs identified by their class name.

Here is the resulting output of the JUnit test:

Excel Ouput

The program is also capable of reading the information back from the Excel spread sheet and populating the Generic collection.  In the case of multiple tabs the program uses the class name to select the appropriate tab.  The details are left to interested developers.

Installing

The program requires the following jar files:

  • poi-3.5-FINAL-20090928.jar
  • poi-ooxml-3.5-FINAL-20090928.jar
  • log4j-1.2.15.jar
  • JUnit 4

The POI file are available here.  Log4J is available here.

The source code for this project is here.

 Enjoy!

 

About The Author

David Sells is a computer consultant in Toronto, Ontario who specializes in Java Enterprise Development. He has  provided innovative solutions for clients including: IBM, Goldman Sachs, Merrill Lynch and Deutsche Bank.

He holds the following certifications:

  • Sun Certified Enterprise Architect for the Java Platform, Enterprise Edition 5 (2009)
  • Sun Certified Web Component Developer
  • Sun Certified Java Programmer.

Contact: david@persistentdesigns.com

 

 

1 comment to Excel read-writable Reports through Generics, Reflection and Apache POI

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>