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):
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!
Learn more about this topic
- The Apache POI Website
http://jakarta.apache.org/poi/ - For HSSF, POI's pure Java implementation for manipulating Microsoft Excel files from Java
http://jakarta.apache.org/poi/hssf/index.html - Tony Sintes's Java/Microsoft Excel series (JavaWorld):
- "It's Excel-lent" (June 2001)
- "The Java-Excel Solution Revisited" (August 2001)
- "It's POI-fect" (May 2002)
- For more practical solutions to difficult Java problems, visit the Applied Java section of JavaWorld's Topical Index
http://www.javaworld.com/channel_content/jw-applied-index.shtml - Want more? See the Java Q&A index page for the full Q&A catalog
http://www.javaworld.com/columns/jw-qna-index.shtml - For more than 100 insightful Java tips from some of the best minds in the business, visit JavaWorld's Java Tips index page
http://www.javaworld.com/columns/jw-tips-index.shtml - Learn Java basics in our Java Beginner discussion
http://forums.idg.net/webx?50@@.ee6b804 - Sign up for JavaWorld's free weekly email newsletters
http://www.javaworld.com/subscribe - You'll find a wealth of IT-related articles from our sister publications at IDG.net