In this Java Tutorial, I will show you how to read the content of an Excel File. The tutorial will read a sample excel file and then print the content to the console. Making tutorials simpler to understand is one of my objectives in this blog. Hopefully you will find this tutorial extremely simple to understand how to use Apache POI to read any Excel File [.xls format].
I used the following apache POI API and is available for download at the publisher’s website.
- poi-3.2-FINAL-20081019.jar
/**
* @author Kushal Paudyal
* www.sanjaal.com/java
* Last Modified on 02/16/2008
*/
package com.kushal.util;
import java.io.FileInputStream;
import java.util.Iterator;
import java.util.Vector;
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;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
public class ReadExcelFile {
public static void main( String [] args ) {
String fileName="C:\\temp\\testPOI.xls";
//Read an Excel File and Store in a Vector
Vector dataHolder=readExcelFile(fileName);
//Print the data read
printCellDataToConsole(dataHolder);
}
public static Vector readExcelFile(String fileName)
{
/** --Define a Vector
--Holds Vectors Of Cells
*/
Vector cellVectorHolder = new Vector();
try{
/** Creating Input Stream**/
//InputStream myInput= ReadExcelFile.class.getResourceAsStream( fileName );
FileInputStream myInput = new FileInputStream(fileName);
/** Create a POIFSFileSystem object**/
POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);
/** Create a workbook using the File System**/
HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);
/** Get the first sheet from workbook**/
HSSFSheet mySheet = myWorkBook.getSheetAt(0);
/** We now need something to iterate through the cells.**/
Iterator rowIter = mySheet.rowIterator();
while(rowIter.hasNext()){
HSSFRow myRow = (HSSFRow) rowIter.next();
Iterator cellIter = myRow.cellIterator();
Vector cellStoreVector=new Vector();
while(cellIter.hasNext()){
HSSFCell myCell = (HSSFCell) cellIter.next();
cellStoreVector.addElement(myCell);
}
cellVectorHolder.addElement(cellStoreVector);
}
}catch (Exception e){e.printStackTrace(); }
return cellVectorHolder;
}
private static void printCellDataToConsole(Vector dataHolder) {
for (int i=0;i<dataHolder.size();i++) {
Vector cellStoreVector=(Vector)dataHolder.elementAt(i);
for (int j=0; j< cellStoreVector.size();j++){
HSSFCell myCell = (HSSFCell)cellStoreVector.elementAt(j);
String stringCellValue = myCell.toString();
System.out.print(stringCellValue+"\t");
}
System.out.println();
}
}
}
Originally posted 2009-02-16 15:54:03.
hey, can i know the list of jar files required when we need to read an excel sheet in apache poi?
I have following jar files in my build path. Not sure of all three are required though.
poi-3.2-FINAL-20081019.jar
poi-contrib-3.2-FINAL-20081019.jar
poi-scratchpad-3.2-FINAL-20081019.jar
Hi Kushal, thanks for this example. Very useful one.
To have the value and not the objet just change mycell.toString() by myCell.getStringCellValue();
and thx for this sample, really usefull
HI, Kushal
I copied ur Code and run i get object value not cell value . plz help how to get out from this prblm .
its very urgent …
Nitin
Kushal, Thanks man!
Great post. I copied the code and it worked without modifying anything. u rock!
hello sir ,
i need to extract the contents of word file into excel file using POI.jar .FoR testing purpose i am currently reading from one excel and writing it into another excel file . while compiling it is fine but while running it is giving an exception .even when i tried with your program also same this happened . i have already set the classpath and all .could u please help me out .here is the below code
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
//<%@ page contentType=”application/vnd.ms-excel”
import java.io.*;
public class HELLOEXCEL {
public static void main(String args[]) throws IOException{
try{
POIFSFileSystem fs=new POIFSFileSystem(new FileInputStream(“c:\\excel\\rajesh1.xls”));
System.out.println(“helloworld”);
HSSFWorkbook wb=new HSSFWorkbook(fs);
FileOutputStream fileOut = new FileOutputStream(“c:\\excel\\readingWriting.xls”);
wb.write(fileOut);
fileOut.close();
System.out.println(“Your excel file has been generated”);
} catch ( Exception ex ) {
System.out.println(“EX”+ex);
}
}
}
What exception are you getting?
– I took your code and compiled
– I then created a sample rajesh1.xls in my C:
– Ran the code. It did run. See the attachment below.
Screenshot Attachment
What version of Office you are using? I tried to read Office 2007. I got the following exception, because Office 2007 is not supported by POI. Post your exception log. Maybe I can help.
EXorg.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML. POI only supports OLE2 Office documents
Hi kushal,
I have to state that I have copied the code provided by u regarding reading an Excel file.but I got a major problem intead of data it printed the hashcode of the object like this….
org.apache.poi.hssf.usermodel.HSSFCell@15356d5 org.apache.poi.hssf.usermodel.HSSFCell@69d02b org.apache.poi.hssf.usermodel.HSSFCell@e1eea8 org.apache.poi.hssf.usermodel.HSSFCell@fb6354 org.apache.poi.hssf.usermodel.HSSFCell@364641 org.apache.poi.hssf.usermodel.HSSFCell@13e754f org.apache.poi.hssf.usermodel.HSSFCell@187c55c
org.apache.poi.hssf.usermodel.HSSFCell@ae3364 org.apache.poi.hssf.usermodel.HSSFCell@b02928
what is missing here. Please Let me know…
thanks in advance
gopal
Hey! thanks for the post!!
Can u please tell me how to read content from word document also.
In case of word, how to capture style,fonts etc.., of words written there?
My objective is to read content from word and put that as it is in pdf file.
Thanks in advance.
hi kushal,
i copied the same to my java file and everything is working great. but instead of the values in the excel sheet the hash code of the object is printed to the console..
org.apache.poi.hssf.usermodel.HSSFCell@19134f4
org.apache.poi.hssf.usermodel.HSSFCell@2bbd86
org.apache.poi.hssf.usermodel.HSSFCell@1a7bf11
org.apache.poi.hssf.usermodel.HSSFCell@1f12c4e
i get this as the output. what is the problem here.. please let me know
I retested my code.. it does not print the hash object. What office version you are using?
Thanks Gopal for the correction. I have corrected the code. It was a problem with pasting code into the blog from my workspace.
dear sir,
I have to state that i have copied sample code from here regarding Excel file using java and apache pio(ReadExcelFile.java).Inside this code I find an error,please verify inside the method(printCellDataToConsole).