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.
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.
You can download complete source code and sample Excel workbook here.
