Java class to export ResultSet into Excelsheet

How to export ResultSet into Excel worksheet in Java?

Hi guys, have you ever tried to export an object of ResultSet directly into an Excel sheet ?
I found the below helper class to do the job. After playing sometime with the below code, I could get the job done. But most of the examples of this class uses the Format Type dependency (data types of the columns) to write into excel sheet. but I could overcome that dependency as you may not know which column is in which data type while exporting. so I hope this will save your time.

ResultSetToExcel.java

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.sql.Timestamp;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.contrib.HSSFCellUtil;

import com.cimbbank.database.JDBCBridge;
//import org.apache.poi.hssf.util.HSSFCellUtil;


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.getColumnLabel(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 Exception {
      writeCell(row, col, value, formatType, null, null);
    }
    private void writeCell(HSSFRow row, int col, Object value, FormatType formatType, HSSFFont font) throws Exception {
      writeCell(row, col, value, formatType, null, font);
    }
    private void writeCell(HSSFRow row, int col, Object value, FormatType formatType,
                           Short bgColor, HSSFFont font) throws Exception {
      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
  }   
 

}

Usage

Let's say you need to generate the excelsheet on the fly and prompt user to 

download it through the browser? use below code segment to accomplish it.

//Generating ExcelSheet
ResultSetToExcel resultSetToExcel = new ResultSetToExcel(resultSet,"Report");
String excelfileName = "report"+System.currentTimeMillis()+".xls";

final int DEFAULT_BUFFER_SIZE = 15360; //15KB
try {
       response.setContentType("application/vnd.ms-excel");
       response.setContentType("Cache-Control: cache");
       response.setContentType("Pragma: public");
       response.setContentType("Content-Transfer-Encoding: binary");
       
       response.setHeader("Content-Disposition", "attachment; 

                                        filename=\"" + excelfileName + "\"");

     BufferedInputStream input = null;
     BufferedOutputStream output = null;

     try {
        output = new BufferedOutputStream(response.getOutputStream(), 

                                    DEFAULT_BUFFER_SIZE);
       resultSetToExcel.generate(output);

     } finally {
       if (output != null) 

try { 

    output.close(); 

}catch (IOException ignore) {}

   if (input != null) 

     try { 

       input.close(); 

     }catch (IOException ignore) {}

   System.out.println("Excel written successfully..");

 } catch (Exception e) {
       throw new RuntimeException(e);
 }

This will prompt the user to download the excel sheet generate via above helper

class. If you find this post saved your time, please leave your valuable comment.