Using Java to Read Excel Files

I recently needed to add support for extracting data from Excel files (which are still ubiquitous in labs) to a Java application.

First I tried using POI-HSSF (a well-known Apache project). The code is reasonably straightforward — though I had to get it from tutorials on the Web as I couldn’t find much in terms of documentation on their site, and the API isn’t exactly self-evident:

POIFSFileSystem fs = new POIFSFileSystem(in);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
Iterator rows = sheet.rowIterator();
while (rows.hasNext()) {
    HSSFRow row = (HSSFRow) rows.next();
    Iterator cells = row.cellIterator();
    List<string> fields = new ArrayList<string>();
    while (cells.hasNext()) {
        HSSFCell cell = (HSSFCell) cells.next();
        switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_STRING:
                fields.add(cell.getStringCellValue());
                break;
            case HSSFCell.CELL_TYPE_NUMERIC:
                fields.add(Double.toString(cell.getNumericCellValue()));
                break;
            default:
                log.warn("Unsupported cell type: " + cell.getCellType());
                break;
        }
    }
    process(fields);
}

This works fine — except that there doesn’t seem to be a way to keep the precision of numbers, i.e. if you don’t want to convert “4″ to “4.0″! Fortunately POI isn’t the only game in town. I ended up using JExcel API which has a much simpler API (and behaves the way I want it to):

Workbook workbook = Workbook.getWorkbook(in);
Sheet sheet = workbook.getSheet(0);
for (int i = 0; i < sheet.getRows(); ++i) {
    List<string> fields = new ArrayList<string>();
    for (int j = 0; j < sheet.getColumns(); ++j) {
        fields.add(sheet.getCell(j, i).getContents());
    }
    process(fields);
}

This entry was posted on Thursday, February 14th, 2008 at 19:06 and is filed under Programming. The content of this entry is licensed under a Creative Commons Attribution 2.5 License. You can follow any responses to this entry through the RSS 2.0 feed. You can skip to the end and leave a response. Pinging is currently not allowed.

2 Responses to “Using Java to Read Excel Files”

  1. Ganesh Says:
    June 19th, 2008 at 11:17

    you can type cast the values in POI-HSSF. i think by default it returns double type,so it will return 4.0 and if u want 4 then type cast and make it int or long

  2. Eric Jain Says:
    June 19th, 2008 at 11:26

    Sure you can cast… However this doesn’t help much if you need to know the precision of the original value in the spreadsheet: Was it 4 or 4.0 (or perhaps even 4.00)? POI-HSSF doesn’t seem to provide this information.

Leave a Reply