Converting ResultSet to Excel table in Java using Apache POI

You can make a great reporting system with impressive UI, but there will always be someone that will ask you: Can you export to excel?

Excel offers many ways of easily manipulating and viewing data out of the box. Therefore, no matters how your reporting system is sophisticated, exporting the result to Excel is quite a basic feature.

Using Apache POI Project to produce Excel output is an easy task. This great project offers a wide support for generating Microsoft Format Files. We will use this open source project to dynamically generate Excel file from a ResultSet.

We will construct a class named: ResultSetToExcel. It’s constructor will get: ResultSet and Excel sheet name. A function named: generate will produce Excel sheet to an OutputStream or to a file. The content of the generated Excel sheet will be a table with headers and ResultSet data.

Building the ResultSetToExcel class introduces 2 main issues:

• From where do we get the titles for the report?
• How do we format Excel cells?

This is how we will deal with these 2 issues:

• Report titles will be taken from query columns by using the ResultSetMetaData class. For example, let’s have a look at this query:

  select first_name 'First Name', last_name 'Last Name', age 'Age' from person

  The names that will be used for our report are: First Name, Last Name and Age. If no specific name is given to a column, then the column name itself will be used.
• We will use 2 ways for formatting Excel columns:
• Explicit: In the class constructor, In addition to the ResultSet, we supply a parameter named: formatTypes. This parameter is an array of the static inner enum class named: FormatType. It contains all commonly used format types:
  
  
  • Text
  • Integer
  • Float
  • Date
  • Money
  Of course, the items in this array should correspond one-to-one to the columns in our ResultSet. We use this array to format Excel cells according to their nature. This makes the resulted Excel file more neat and professional. Note that these are the most commonly and basic types used. You can add more types quite easily.
• Automatic: If no formatTypes parameter supplied, we try to “guess” the type of the column, by examining the Java class used for every specific column. We do it by using the private method: getFormatType. Pay attention to 2 important things:
• The getFormatType method is pretty basic, it doesn’t handle all possible java classes, but you can easily refine it.
• The “automatic guessing” way is less preferable, because it is not able to give us more fine grained control over the way our cell is formatted. For example: we cannot format a cell as Money or Percentage.

2 last things before moving to code is:

• The method: writeCell. This method is used to write an excel cell in a formatted way. It formats the cell according to the values in the parameter: formatType. Note, that in order to format the cell the class: HSSFCellUtil is used. This class is not at the core of the POI project, but in additional JAR file named: POI Contrib. This class allows formatting Excel cells in a way that existing formats are automatically reused. It prevents from Excel to generate a new format for each cell causing an Excel formatting error in cases of big tables.
• After writing ResultSet cells we use the method: autoSizeColumn of the class: HSSFSheet. This method automatically sets the size of each column according to the longest (in that column). This makes the resulted Excel file ready to be viewed.
  

Well, finally, let’s have a look at the code of our ResultSetToExcel class:

package com.bashan.blog.excel;
import org.apache.commons.lang.exception.NestableException;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.usermodel.contrib.HSSFCellUtil;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Timestamp;
public class ResultSetToExcel {
  private HSSFWorkbook workbook;
  private HSSFSheet sheet;
  private HSSFFont boldFont;
  private HSSFDataFormat format;
  private ResultSet resultSet;
  private FormatType[] formatTypes;
  public ResultSetToExcel(ResultSet resultSet, FormatType[] formatTypes, String sheetName) {
    workbook = new HSSFWorkbook();
    this.resultSet = resultSet;
    sheet = workbook.createSheet(sheetName);
    boldFont = workbook.createFont();
    boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    format = workbook.createDataFormat();
    this.formatTypes = formatTypes;
  }
  public ResultSetToExcel(ResultSet resultSet, String sheetName) {
    this(resultSet, null, sheetName);
  }
  private FormatType getFormatType(Class _class) {
    if (_class == Integer.class || _class == Long.class) {
      return FormatType.INTEGER;
    } else if (_class == Float.class || _class == Double.class) {
      return FormatType.FLOAT;
    } else if (_class == Timestamp.class || _class == java.sql.Date.class) {
      return FormatType.DATE;
    } else {
      return FormatType.TEXT;
    }
  }
  public void generate(OutputStream outputStream) throws Exception {
    try {
      ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
      if (formatTypes != null && formatTypes.length != resultSetMetaData.getColumnCount()) {
        throw new IllegalStateException("Number of types is not identical to number of resultset columns. " +
            "Number of types: " + formatTypes.length + ". Number of columns: " + resultSetMetaData.getColumnCount());
      }
      int currentRow = 0;
      HSSFRow row = sheet.createRow(currentRow);
      int numCols = resultSetMetaData.getColumnCount();
      boolean isAutoDecideFormatTypes;
      if (isAutoDecideFormatTypes = (formatTypes == null)) {
        formatTypes = new FormatType[numCols];
      }
      for (int i = 0; i < numCols; i++) {
        String title = resultSetMetaData.getColumnName(i + 1);
        writeCell(row, i, title, FormatType.TEXT, boldFont);
        if (isAutoDecideFormatTypes) {
          Class _class = Class.forName(resultSetMetaData.getColumnClassName(i + 1));
          formatTypes[i] = getFormatType(_class);
        }
      }
      currentRow++;
      // Write report rows
      while (resultSet.next()) {
        row = sheet.createRow(currentRow++);
        for (int i = 0; i < numCols; i++) {
          Object value = resultSet.getObject(i + 1);
          writeCell(row, i, value, formatTypes[i]);
        }
      }
      // Autosize columns
      for (int i = 0; i < numCols; i++) {
        sheet.autoSizeColumn((short) i);
      }
      workbook.write(outputStream);
    }
    finally {
      outputStream.close();
    }
  }
  public void generate(File file) throws Exception {
    generate(new FileOutputStream(file));
  }
  private void writeCell(HSSFRow row, int col, Object value, FormatType formatType) throws NestableException {
    writeCell(row, col, value, formatType, null, null);
  }
  private void writeCell(HSSFRow row, int col, Object value, FormatType formatType, HSSFFont font) throws NestableException {
    writeCell(row, col, value, formatType, null, font);
  }
  private void writeCell(HSSFRow row, int col, Object value, FormatType formatType,
                         Short bgColor, HSSFFont font) throws NestableException {
    HSSFCell cell = HSSFCellUtil.createCell(row, col, null);
    if (value == null) {
      return;
    }
    if (font != null) {
      HSSFCellStyle style = workbook.createCellStyle();
      style.setFont(font);
      cell.setCellStyle(style);
    }
    switch (formatType) {
      case TEXT:
        cell.setCellValue(value.toString());
        break;
      case INTEGER:
        cell.setCellValue(((Number) value).intValue());
        HSSFCellUtil.setCellStyleProperty(cell, workbook, HSSFCellUtil.DATA_FORMAT,
            HSSFDataFormat.getBuiltinFormat(("#,##0")));
        break;
      case FLOAT:
        cell.setCellValue(((Number) value).doubleValue());
        HSSFCellUtil.setCellStyleProperty(cell, workbook, HSSFCellUtil.DATA_FORMAT,
            HSSFDataFormat.getBuiltinFormat(("#,##0.00")));
        break;
      case DATE:
        cell.setCellValue((Timestamp) value);
        HSSFCellUtil.setCellStyleProperty(cell, workbook, HSSFCellUtil.DATA_FORMAT,
            HSSFDataFormat.getBuiltinFormat(("m/d/yy")));
        break;
      case MONEY:
        cell.setCellValue(((Number) value).intValue());
        HSSFCellUtil.setCellStyleProperty(cell, workbook,
            HSSFCellUtil.DATA_FORMAT, format.getFormat("($#,##0.00);($#,##0.00)"));
        break;
      case PERCENTAGE:
        cell.setCellValue(((Number) value).doubleValue());
        HSSFCellUtil.setCellStyleProperty(cell, workbook,
            HSSFCellUtil.DATA_FORMAT, HSSFDataFormat.getBuiltinFormat("0.00%"));
    }
    if (bgColor != null) {
      HSSFCellUtil.setCellStyleProperty(cell, workbook, HSSFCellUtil.FILL_FOREGROUND_COLOR, bgColor);
      HSSFCellUtil.setCellStyleProperty(cell, workbook, HSSFCellUtil.FILL_PATTERN, HSSFCellStyle.SOLID_FOREGROUND);
    }
  }
  public enum FormatType {
    TEXT,
    INTEGER,
    FLOAT,
    DATE,
    MONEY,
    PERCENTAGE
  }
}

This class can be downloaded here.

The JARs for this class can be downloaded from: Apache POI Project. Both POI and POI Contrib Jars are needed.

This is a small example of how this class can be used:

  public void createExecl(Connection conn) throws Exception
  {
    PreparedStatement stmt = conn.prepareStatement("select first_name 'First Name', last_name 'Last Name', age 'Age', " +
        "salary 'Salary'");
    ResultSet resultSet = stmt.executeQuery();
    ResultSetToExcel resultSetToExcel = new ResultSetToExcel(resultSet,
        new ResultSetToExcel.FormatType[] { ResultSetToExcel.FormatType.TEXT, ResultSetToExcel.FormatType.TEXT,
            ResultSetToExcel.FormatType.INTEGER, ResultSetToExcel.FormatType.MONEY }, "Employee List");
    resultSetToExcel.generate(new File("c:\\employees.xls"));
  }