Reading xls files from Java using apache POI

Ever wondered reading/writing xls, doc files through Java.  Its now (probably since long time) possible using apache POI.  I was specifically looking at reading xls files in order to receive some messages for our application.

I know couple of alternatives, but after finding POI I ignored the rest.  The library for handling excel formats is called HSSF (Horrible SpreadSheet Format).  I wonder why something is ever looked at as bad, maybe it is just the case that we are not able to see the hidden (rather other-side) benefits/workings?

Anyways, HSSF provides two models for reading xls files, usermodel and eventmodel.  Usermodel is the old one where one can visualize the workbook as number of sheets, each sheet as number of rows and each row as number of columns.  As one can guess memory usage would be more in this model.

Eventmodel on the other hand is like SAX parsing, where in one would get notified of various events as parsing/reading of the file progresses.  As per the developers comments this is more efficient in terms of memory consumption, processing speed and provides finer control of reading and thus better handling of data within xls files.

To start with I just went ahead with usermodel and successfully converted xls to an xml file.  My sample xls file contains a number of contact details, first row contains headers and remaining rows contain the actual data.  Below is the sample code for converting this data into xml.

import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class XLS2XML
{
  HSSFWorkbook workbook = null;
  HSSFSheet sheet = null;
  ArrayList headers = new ArrayList();
 
  public XLS2XML(InputStream in) throws Exception
  {
    workbook = new HSSFWorkbook(in);
    sheet = workbook.getSheetAt(0);
  }
 
  public String xmlExtractor() throws Exception
  {
    StringBuffer ret = new StringBuffer();
    Iterator rows = sheet.rowIterator();
    initialiseHeader((HSSFRow)rows.next());
   
    ret.append("<" + workbook.getSheetName(0) + ">");
    while(rows.hasNext())
    {
      HSSFRow row = (HSSFRow)rows.next();
      ret.append(rowExtractor(row));
    }
    ret.append("");
   
    return ret.toString();
  }
 
  private void initialiseHeader(HSSFRow row)
  {
    short minColIdx = row.getFirstCellNum();
    short maxColIdx = row.getLastCellNum();

    for(short colIdx = minColIdx; colIdx < maxColIdx; colIdx++)
    {
      HSSFCell cell = row.getCell(colIdx);
      if(cell == null)
      {
        continue;
      }
      headers.add(cell.getRichStringCellValue().getString());
    }
  }
 
  private String rowExtractor(HSSFRow row)
  {
    StringBuffer ret = new StringBuffer();
   
    short minColIdx = row.getFirstCellNum();
    short maxColIdx = row.getLastCellNum();

    ret.append("");
    for(short colIdx = minColIdx; colIdx < maxColIdx; colIdx++)
    {
      HSSFCell cell = row.getCell(colIdx);
      if(cell == null)
      {
        continue;
      }
      ret.append(getPrefix(colIdx));
      ret.append(getCellValue(cell));
      ret.append(getSuffix(colIdx));
    }
    ret.append("");
   
    return ret.toString();
  }
 
  private String getPrefix(short i)
  {
    return "<" + headers.get(i).toString() + ">";
  }

  private String getSuffix(short i)
  {
    return "";
  }
 
  private String getCellValue(HSSFCell cell)
  {
    String ret = null;
   
    switch(cell.getCellType())
    {
      case HSSFCell.CELL_TYPE_NUMERIC:
        ret = Double.toString(cell.getNumericCellValue());
        break;
      case HSSFCell.CELL_TYPE_STRING:
        ret = cell.getRichStringCellValue().getString();
        break;
      default:
        ret = "";
    }
   
    return ret;
  }
}

Set SP params by name - new feature added in oracle 10g jdbc driver

Everyone would try not to hard-code the signature of stored procedure (SP) in all java applications requiring database interaction through SPs. One of many ways could be having the signature defined as an xml config and application can intelligently manage any changes to xml config without any code changes. Most of the complexity in doing so would result from managing the sequence numbers of parameters.

From Oracle10g onwards this shall not be the case anymore as 10g jdbc drivers support SP invocation by param names along with sequence numbers. Unfortunately this is not the case with Sybase yet (have tested with jconn3.jar). Below is the sample code I have used for testing this feature with Oracle driver ver.10.2.0.1.0

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;

public class SPTester
{
  private static CallableStatement setParamsByName(CallableStatement sp) throws Exception
  {
    sp.registerOutParameter("param1", Types.NUMERIC);
    sp.registerOutParameter("param2", Types.VARCHAR);
    sp.registerOutParameter("param3", Types.VARCHAR);
    sp.registerOutParameter("param4", Types.VARCHAR);
    sp.registerOutParameter("param5", Types.VARCHAR);
    sp.setString("param5", "12345");

    return sp;
  }
 
  private static CallableStatement setParamsBySeq(CallableStatement sp) throws Exception
  {
    sp.registerOutParameter(1, Types.NUMERIC);
    sp.registerOutParameter(2, Types.VARCHAR);
    sp.registerOutParameter(3, Types.VARCHAR);
    sp.registerOutParameter(5, Types.VARCHAR);
    sp.registerOutParameter(6, Types.VARCHAR);
    sp.setString(4, "12345");
   
    return sp;
  }

  private static void getParamsByName(CallableStatement sp) throws Exception
  {
    System.out.println("param1: " + sp.getString("param1"));
    System.out.println("param2: " + sp.getString("param2"));
    System.out.println("param3: " + sp.getString("param3"));
    System.out.println("param4: " + sp.getString("param4"));
    System.out.println("param5: " + sp.getString("param5"));
  }
 
  private static void getParamsBySeq(CallableStatement sp) throws Exception
  {
    System.out.println("param1: " + sp.getString(1));
    System.out.println("param2: " + sp.getString(2));
    System.out.println("param3: " + sp.getString(3));
    System.out.println("param4: " + sp.getString(5));
    System.out.println("param5: " + sp.getString(6));
  }

  public static void main(String[] args) throws Exception
  {
    Connection con = null;
    CallableStatement sp = null;
   
    try
    {
      Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
      con = DriverManager.getConnection("jdbc:oracle:thin:@<server>:<port>:<sid>", "usr", "pass");
      sp = con.prepareCall("{call <sp_name> (?, ?, ?, ?, ?, ?)}");

      int i = 10;
      if(i == 0) // by sequence
      {
        setParamsBySeq(sp);
        sp.execute();
        getParamsBySeq(sp);
      }
      else
      {
        setParamsByName(sp);
        sp.execute();
        getParamsByName(sp);
      }
    }
    catch(Exception e)
    {
      throw e;
    }
    finally
    {
      try
      {
        if(sp != null)
        {
          sp.close();       
        }
        if(con != null)
        {
          con.close();         
        }
      }
      catch(Exception e)
      {
        e.printStackTrace();
      }
    }
  }
}