Friday, September 4, 2009

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"));
  }

23 comments:

  1. God bless u for this, awesome work...

    ReplyDelete
  2. Hey it is for xls. i want it for xlsx.

    ReplyDelete
    Replies
    1. You can easily convert it to XLSX. You can check how it is done in the POI project.

      Delete
  3. Please give the link to download POI Contrib
    Thanks

    ReplyDelete
    Replies
    1. The link probably changes all the time, since version are being released. The POI project can be easily find using Google.

      Delete
  4. Sir,i am getting this error please help !!!!!!

    cannot find symbol
    symbol: variable DATA_FORMAT
    location: class org.apache.poi.hssf.usermodel.contrib.HSSFCellUtil

    ReplyDelete

  5. use these in ResultSetToExcel class file adn remove reference of HSSFCellUtil
    public static final String DATA_FORMAT = "dataFormat";
    public static final String FILL_BACKGROUND_COLOR = "fillBackgroundColor";
    public static final String FILL_FOREGROUND_COLOR = "fillForegroundColor";
    public static final String FILL_PATTERN = "fillPattern";

    ReplyDelete
  6. Hi Sir,

    I have downloaded the above given jar file poi-contrib-3.2-FINAL.jar.

    i am unable to find the org.apache.poi.hssf.usermodel.HSSFCell file.
    so i downloaded the latest jar file from apache(poi-3.8-20120326.jar).
    This file contains all the files except the org.apache.poi.hssf.usermodel.contrib.HSSFCellUtil file.

    So i copied both the jar files in the classpath.
    Now i am getting the exception as "Caused by: java.lang.NoSuchMethodError: org.apache.poi.hssf.usermodel.HSSFCell.setCellValue(Lorg/apache/poi/hssf/usermodel/HSSFRichTextString;)V "

    How can i solve the above issues. Please advise me on this.

    Thanks.
    After

    ReplyDelete
  7. Hi Sreedhar,

    This exception happens usually when using some older or newer JAR. The class was either refactored, renamed or passed some other transformation. There is no way around it: You will have to dig around and try to figure out the missing class.

    ReplyDelete
  8. Hi,

    I'm unable to import org.apache.commons.lang.exception.NestableException;

    can you please provide me the jar name of this one.

    ReplyDelete
  9. I believe it will be in the Commons Lang project of Apache.

    ReplyDelete
  10. Hi,
    I am getting this error please help !
    Cannot find symbol method generate(java.io.FileOutputStream) in:
    public void generate(File file) throws Exception {
    generate(new FileOutputStream(file));
    }

    ReplyDelete
    Replies
    1. Sorry, this post was written long time ago. I can not longer support it.

      Delete
  11. Thanks for sharing.. Awesome Explanation!

    ReplyDelete
  12. Thanks for Sharing.. Awesome Explanation!

    ReplyDelete
  13. Hi Guy.

    I am getting below error
    Exception Caused is::- class java.sql.SQLException
    Exception Occured In Below Lines::
    java.sql.SQLException: Result set already closed

    please help

    ReplyDelete
  14. Do you send to the class an open connection with a resultset that was not already used?

    ReplyDelete
  15. I think you shouldn't ZIP a PDF file since it is already compressed quite well from the beginning. Trying to compress it will just cause your CPU to work harder.

    ReplyDelete