It's POI-fect

May 3, 2002; Updated January 18, 2003

In "It's Excel-lent" (JavaWorld, June 2001) I showed how to read and write to Microsoft Excel spreadsheet documents using Java's JDBC (Java Database Connectivity). As a follow-up, in "The Java-Excel Solution Revisited" (JavaWorld, August 2001) I answered additional questions and presented a few JDBC alternatives.

Read Tony Sintes's entire Java/Microsoft Excel series (JavaWorld):

• "It's Excel-lent"
• "The Java-Excel Solution Revisited"
• "It's POI-fect"

The reader interest I have generated about a Java/Excel solution has been unprecedented. In the two years I have written the Java Q&A column, no single topic has come up as much as this one. I had no idea so many programmers faced problems manipulating Excel files from Java.

Truth be told, I was never happy with the JDBC-based solution to manipulate Excel files; the approach always seemed too limited.

Well, I'm here to tell you about POI (Poor Obfuscation Implementation), the Apache Java API for manipulating file formats based on the OLE 2 (Object Linking and Embedding) Compound Document format.

POI itself is an API collection. If you're interested in Excel, as many of you are, check out HSSF (Horrible Spreadsheet Format). Why? The site says it best: "HSSF is the POI Project's pure Java implementation of the Excel '97(-2002) file format."

That's right, you can use that API to do everything from creating new workbooks to setting cell fonts and colors. I imagine you could even write your own spreadsheet frontend, then the API to write out the data.

The POI homepage features decent tutorials and Javadocs—reading both should get you started. In this article, I don't offer a complete POI tutorial because I'm new to POI too. However, below I present a quick example that opens a spreadsheet and writes the contents to the screen. I leave learning and applying the API to you, my capable readers.

Here's the example code:

import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFRow;
/**
 * A simple POI example of opening an Excel spreadsheet
 * and writing its contents to the command line.
 * @author  Tony Sintes
 */
public class POIExample {
 
    public static void main( String [] args ) {
        try {
            InputStream input = POIExample.class.getResourceAsStream( "qa.xls" );
            POIFSFileSystem fs = new POIFSFileSystem( input );
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            HSSFSheet sheet = wb.getSheetAt(0);
            
            // Iterate over each row in the sheet
            Iterator rows = sheet.rowIterator(); 
            while( rows.hasNext() ) {           
                HSSFRow row = (HSSFRow) rows.next();
                System.out.println( "Row #" + row.getRowNum() );
 
                // Iterate over each cell in the row and print out the cell's content
                Iterator cells = row.cellIterator();
                while( cells.hasNext() ) {
                    HSSFCell cell = (HSSFCell) cells.next();
                    System.out.println( "Cell #" + cell.getCellNum() );
                    switch ( cell.getCellType() ) {
                        case HSSFCell.CELL_TYPE_NUMERIC:
                            System.out.println( cell.getNumericCellValue() );
                            break;
                        case HSSFCell.CELL_TYPE_STRING: 
                            System.out.println( cell.getStringCellValue() );
                            break;
                        default:
                            System.out.println( "unsuported sell type" );
                            break;
                    }
                }
                
            }
            
        } catch ( IOException ex ) {
            ex.printStackTrace();
        }
    }
    
}

The code example opens a workbook, grabs the first sheet, and iterates over each row. For each row, the example iterates over each cell, determines the cell's type, and prints the cell's contents to the screen. Have fun!