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