Reading Excel Files from Java

Microsoft Excel is one of the common was of storing data in business. There fore when we develop applications, most of the times we have to extract data from an Excel sheet. There are many methods to extract data from Excel file, both commercial and free libraries are developed for this purpose. One of such library is Apache POI HSSF.

I came across this library few weeks back when I was researching on a methods of reading data from Excel and load it to a Java application that we are developing.  The project I'm working on is a development of Retirement Planning System, which our client is going to use for consultation purposes. He wanted to give his clients an Excel template where they put their current financial information such as incomes, expenses and our software should be able to read that file and load that data for projection purposes.

Apache POI provides good API to access Excel files, not only reading but writing as well. Here I'm only using reading functionality only. When we are panning to use Excel as data input method first thing is to develop good template with all required field. This is the sample Excel file I used in my testing.

incomes 

 

 

 

When reading the data we are reading from cell by cell, so we have to know the exact cell that contains the data we need. I'm using a common interface to read data from Excel sheet.  According to our requirement we can implement that to read data in to our java objects.

public interface RowProcessor
{
public Object[] process(HSSFSheet sheet) throws Exception;
}


Here we are passing the excel sheet to our process method and get set of objects after processing it. By implementing this interface I created a class called IncomeProcessor to read the Excel sheet and get an array of Income.



public class IncomeProcessor implements RowProcessor
{
//Row columns
private static final short COLUMN_NAME = 1;
private static final short COLUMN_OWNER = 2;
private static final short COLUMN_AMOUNT = 3;
private static final short COLUMN_INCREASE_RATE = 4;
/**
* The singleton instance of this class.
*/
private static IncomeProcessor thisProcessor;

/**
* Default constructor
* Created on: Nov 8, 2007
* @Author: Sandarenu
*/
private IncomeProcessor()
{
//Private so no outside instantiation
}

/**
* Get an instance of this row processor.
* Created on: Nov 8, 2007
* @Author Sandarenu
* @return instance of this row processor.
*/
public static RowProcessor getInstance()
{
if(thisProcessor == null)
{
thisProcessor = new IncomeProcessor();
}
return thisProcessor;
}

/**
* Do required processing for the Incomes.
*/
public Object[] process(HSSFSheet sheet) throws Exception
{
if(sheet != null)
{
int first = sheet.getFirstRowNum();
int last = sheet.getLastRowNum();
HSSFRow row = null;
List<Income> incomeList = new ArrayList<Income>(5);
Income income = null;
String owner;
first += 2; //Ignore first 2 rows - they are headers
for(int i= first; i<=last; i++)
{
row = sheet.getRow(i);
if(row != null && row.getCell(COLUMN_NAME) != null)
{
income = new Income();
income.setName(row.getCell(COLUMN_NAME).getRichStringCellValue().getString());
income.setStartingValue(row.getCell(COLUMN_AMOUNT).getNumericCellValue());
income.setIncreaseRate((float)row.getCell(COLUMN_INCREASE_RATE).getNumericCellValue());
owner = row.getCell(COLUMN_OWNER).getRichStringCellValue().getString();
if(owner.startsWith("C")|| owner.startsWith("c"))
income.setClientPercentage(100);
else if(owner.startsWith("S")|| owner.startsWith("s"))
income.setSpousePercentage(100);

incomeList.add(income);
}
}

return incomeList.toArray(new Income[incomeList.size()]);
}

return null;
}

}




This is the main class I used to test my data reading. Here first I read the excel file and then get relevant Sheet for processing. Using this technique we can easily populate our java objects using the data from Excel sheet.  



public class Test {

/**
* @param args
*/
public static void main(String[] args)
{
TestListner tl = new TestListner();
ImportHandler ih = new ImportHandler();
ih.addStatusListner(tl);

try {
POIFSFileSystem fs =
new POIFSFileSystem(new FileInputStream("Book1.xls"));
HSSFWorkbook wb = new HSSFWorkbook(fs);


HSSFSheet sheet =wb.getSheet("Income");
RowProcessor ip = IncomeProcessor.getInstance();
Object [] incomes = ip.process(sheet);
for (Object object : incomes)
{
Income income = (Income)object;
System.out.println( income.getName() + " " + income.getStartingValue());
}

} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}


}

}


Output of the code.



output



 



 



 



You can download complete source code and sample Excel workbook here.




 

Reader Comments

thanks buddy..
that was a wonderful explanation of how to read excel file in java. i got a lot to understand from this article

This is great,
but i would like to know this api is capable of reading Excel 2007.
If not then is there any other api with this functionality.

Amit, unfortunately current version of POI-HSSF do not support new Excel 2007 file format(.xlsx OOXML). http://poi.apache.org/hssf/index.html
But a document published by Apache POI say that they are working on a new version that is capable of reading and writing new Excel 2007 formats. You better check that out.

new poi version 3.5 support xlsx OOXML format

how can i include the POI in the library??
its always said that "package org.apache.poi.ss.usermodel does not exist
import org.apache.poi.ss.usermodel.Font;"

how can i include the POI in the library??
its always said that "package org.apache.poi.ss.usermodel does not exist
import org.apache.poi.ss.usermodel.Font;"

Thanks for the article, but we have some users that want to use "smart" spreadsheets. Sheets that contains dropdown lists, etc. Is it possible to process these with the POI API?

Well, I'm not sure about that. Sometimes it may be possible, better check with Apace POI site. Now they also have a new version with can read excel 2007 format as well.
If you find come thing share that with us as well :)

Nice blog. I am trying to read excel 2007 version but didnt get any solution. If you have any, plese share with us.

Thanks,

Binod Suman
http://binodsuman.blogspot.com

good work! it helped me :D

That was very good piece of information. I was struggling doing this since morning. Reading Excel Files from Java is very easy with your article.

Thanks for sharing.
Vidhya

Great..this works like a charm. I also tried an JExcel example to modify Excel document in Java and kind of stuck now between this and JExcel..

helloo ,
My name is Kinjan.
I also developed application which reads excel file.
But my requirement is somewhat different.
I want to read file which is currently open.
If i write any thing in excel file then that modification should be read with out manually saving that excel file ..

If you know how to do this please let me know ..

Thanks and Regards
Kinjan Ajudiya.



Tribune to Heros

Tribune to Heros
We will never forget the sacrifices you made to protect our country....

Subscribe


Get Google Toolbar Button

Enter your email address:

Delivered by FeedBurner