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.

How to identify your Operating System through your Java code?

How do you identify in which platform or operating system that your java application run on? below line would do it:

  String _OS = System.getProperty("os.name").toLowerCase(); 

This will represent the name of your operating system such as "windows","unix","mac", etc.
So you can use the "_OS" string to get the correct platform.
                                                                  
 if(_OS.indexOf("win") >= 0){                                                   
       System.out.println("This is Windows");                                        
 }else if (_OS.indexOf("mac") >= 0) {                                            
       System.out.println("This is Mac");                                            
 }else if(_OS.indexOf("nix")>=0||_OS.indexOf("nux")>=0||_OS.indexOf("aix")> 0){
       System.out.println("This is Unix or Linux");                                  
 } else if (_OS.indexOf("sunos") >= 0) {                                          
       System.out.println("This is Solaris");                                        
 } else {                                                                         
       System.out.println("Your OS is not support!!");                               
 }


That's all folks ! hope this small trick will save your time .

How to redirect to the login/index page after the session timeout in JSP

Hi Guys, as the title says...how do you manage your session timeout effectively and redirecting the user to the login page back in a JSP..?
I have seen many examples, work-a-rounds over the Google and I thought of sharing a good technique where you can achieve the goal in a few steps !

Step 1

specify the session timeout interval (in minutes) in the web.xml as follows:

<session-config>
    <session-timeout>15</session-timeout>
</session-config>

 Step 2

Next step is to call the session timeout and the URL or the page that you need to redirect once the timeout occurred in your JSP file. 

ex:

<%


int timeout = session.getMaxInactiveInterval();

response.setHeader("Refresh", timeout + "; URL=index.jsperr=2");




%>

In the above example, when the timeout happens, I am redirecting to the "index.jsp" file with the error code as "2" which I handled inside the application. Therefore you can specify your URL instead of that.

Likewise, you can apply the same code segment every where that you need to specify a session timeout functionality in your application.

That's all folks. Hope it will help you get an idea on "how to handle session timeout and redirect to the login/index page".