Sponsored Links
It's POI-fect
Use the Apache POI API to read and write from Java to MS Excel files
- Feedback
- Discuss
May 3, 2002; Updated January 18, 2003
n "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!
About the author
Tony Sintes is an independent consultant and founder of First Class Consulting, Inc., a consulting firm that specializes in bridging disparate enterprise systems and training. Outside of First Class Consulting, Tony is an active freelance writer, as well as author of Sams Teach Yourself Object-Oriented Programming in 21 Days (Sams, 2001; ISBN: 0672321092).- Feedback
Resources
- 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
About Us | Advertise | Contact Us | Terms of Service/Privacy
Copyright, 2006-2008 Network World, Inc. All rights reserved.
poi sucksBy Anonymous on September 21, 2009, 6:27 amit reads in positive integers with trailing precision...someone please tell me why I would do that...
Reply | Read entire comment
Here you can download the libraryBy Anonymous on August 12, 2009, 1:51 pmhttp://datadispensary.com/apache/poi/release/bin/
Reply | Read entire comment
Can any one let me know where to get the jar file?By Prasanna Kumar on July 31, 2009, 1:40 pmHi, Can any one help me locate the jar files for the POI project? I would like to develop some application to read/write excel files. Thanks for your help...
Reply | Read entire comment
Insert my object into a InputStreamBy Anonymous on June 26, 2009, 12:11 pmHi. Is possible to create a workbook and convert it to a InputStream. In my case I create a workbook file in memory, and I populate with data from my database,...
Reply | Read entire comment
Where do i get the libraries?By Anonymous on June 23, 2009, 11:16 amHi , i have downloaded the jexcel api and added it to my application but tell me lucks a org.apache.poi.hssf.usermodel.HSSFWorkbook; e-mail:wilfolightfire18?hotmail.com
Reply | Read entire comment
View all comments