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

XML Namespaces

The concept of namespaces is similar to defining a variable with same name in different classes and accessing them by prefixing the object name to resolve the naming conflict. In xml document also if you have to define two elements with same tag (which would probably indicate two different things like html table & dining table) then we can use namespaces to resolve the naming conflict.

Namespace is defined by the xmlns attribute either in the start tag of an element or in the document root element as xmlns:prefix="URI". Below is the example:

<root>
<h:table h="http://www.w3.org/TR/html4/">
<h:tr>
<h:td>Apples</h:td>
<h:td>Bananas</h:td>
</h:tr>
</h:table>
<f:table f="http://www.w3schools.com/furniture">
<f:name>African Coffee Table</f:name>
<f:width>80</f:width>
<f:length>120</f:length>
</f:table>
</root>

<root
xmlns:h="http://www.w3.org/TR/html4/"
xmlns:f="http://www.w3schools.com/furniture">
<!-- Same as above -->
</root>

The namespace URI is not used by the parser to look up information. The purpose is just to give the namespace a unique name.

Defining a default namespace for an element saves us from using prefixes in all the child elements, the syntax is xmlns="namespaceURI". Below is an example:

<table xmlns="http://www.w3.org/TR/html4/">
<tr>
<td>Apples</td>
<td>Bananas</td>
</tr>
</table>

Converting DOM objects to XML

These days its rare to find any application not using XML for communicating with other applications. While we all use SAX or DOM parsers either directly or through JAX api, most of us are not aware of org.apache.xml.serialize.XMLSerializer class, which creates an xml string effortlessly. This supports both DOM and SAX. DOM serializing is done by calling serialize(Document) and SAX serializing is done by firing SAX events and using the serializer as document handler. Below is an example of converting a DOM object into xml string:

java.io.ByteArrayOutputStream outStream = new java.io.ByteArrayOutputStream();
org.apache.xml.serialize.OutputFormat outFormat = new org.apache.xml.serialize.OutputFormat();
org.apache.xml.serialize.XMLSerializer serializer = new org.apache.xml.serialize.XMLSerializer();
serializer.setOutputFormat(outFormat);
serializer.setOutputByteStream(outStream);
serializer.asDOMSerializer();
// document is the DOM object created elsewhere
serializer.serialize(document.getDocumentElement());
String outMessage = outStream.toString();

Careful with SAX parser

When parsing xml documents using SAX parser one need to attach an implementation of org.xml.sax.DocumentHandler to receive notifications from the parser. One of the tricky methods in this interface is public void characters(char[] ch, int start, int length) throws org.xml.sax.SAXException. Below is the extract from java docs:

The Parser will call this method to report each chunk of character data. SAX parsers may return all contiguous character data in a single chunk, or they may split it into several chunks; however, all of the characters in any single event must come from the same external entity so that the Locator provides useful information.

The underlined part above is the thing to be handled carefully. One can not simply read the data and allocate to a String as this method might get called repeatedly!! Below is one example of handling this:

// Buffer for holding the element data
StringBuffer dataBuffer = new StringBuffer();
public void characters(char[] ch, int start, int length) throws SAXException
{
dataBuffer.append(ch, start, length);
}

// Get the data from the buffer in endElement method call

One more quirk is that the characters method removes all encoding for special characters like &, <, > etc. So if the xml data contains these special characters outside CDATA section, one need to handle them explicitly !!

DecimalFormat issue in jdk 1.4

Any message handling application need to maintain an unique id for each message to track the status. We are generating ids in Oracle database, which are 24 chars long. As this is done with a sequence the id we get is having some zeros followed by a number like '000000001234567890123456'. To remove the leading zeros we are simply converting this to BigDecimal and then again back to string using DecimalFormat as shown in the below code. While doing this we realised that the max number that DecimalFormat can format successfully is 15 digits if all 9's else 16 digits. Any value beyond that is causing the number to be incremented as shown in the below output.

import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.DecimalFormatSymbols;
import java.util.StringTokenizer;

public class Test
{
public static void main(String[] args)
{
String[] data = {"999999999999999", "9999999999999998", "9999999999999999", "18411028910519619"};
BigDecimal num = null;
DecimalFormat format = new DecimalFormat();

for(int i = 0;i < data.length; i++)
{
num = new BigDecimal(data[i]);
System.out.println("Bignum: " + num.toString() + " converted to: " + format.format(num));
}
}
}

Output with JDK 1.4:
Bignum: 999999999999999 converted to: 999,999,999,999,999
Bignum: 9999999999999998 converted to: 9,999,999,999,999,998
Bignum: 9999999999999999 converted to: 10,000,000,000,000,000
Bignum: 18411028910519619 converted to: 18,411,028,910,519,620

This is not an issue in JDK 1.6:
Bignum: 999999999999999 converted to: 999,999,999,999,999
Bignum: 9999999999999998 converted to: 9,999,999,999,999,998
Bignum: 9999999999999999 converted to: 9,999,999,999,999,999
Bignum: 18411028910519619 converted to: 18,411,028,910,519,619