How to read Excel sheet in Java?

An example of reading and parsing an Excel file.


We have a class (HomeObject) and this class has two attributes ('doorNo' and 'ownerName').

public class HomeObject {
   private int doorNo;
   private String ownerName;

   public int getDoorNo() {
       return doorNo;
   }
   public void setDoorNo(int doorNo) {
       this.doorNo = doorNo;
   }
   public String getOwnerName() {
       return ownerName;
   }
   public void setOwnerName(String ownerName) {
       this.ownerName = ownerName;
   }
}

Now we want to read data from Excel sheet columns and add into a list in our ExcelParser class.
For this purpose, we use The Apache POI project library.

First of all, we add this code into import part of your ExcelParser class.

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;

We need a function that controls data compability in sheet columns.

private String getCellValue(HSSFCell cell) {
 if (cell == null) return null;
 String result = null;
 int cellType = cell.getCellType();
 switch (cellType) {
    case HSSFCell.CELL_TYPE_BLANK:
        result = "";
        break;
    case HSSFCell.CELL_TYPE_BOOLEAN:
        result = cell.getBooleanCellValue() ? "true" : "false";
        break;
    case HSSFCell.CELL_TYPE_ERROR:
        result = "ERROR: " + cell.getErrorCellValue();
        break;
    case HSSFCell.CELL_TYPE_FORMULA:
        result = cell.getCellFormula();
        break;
    case HSSFCell.CELL_TYPE_NUMERIC:
        result = String.valueOf((long) cell.getNumericCellValue());
        break;
    case HSSFCell.CELL_TYPE_STRING:
        result = cell.toString();
        break;
    default:
        break;
 }
 return result;
}

and we add that code into main function in ExcelParser class.

try {
 HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(file));
 // we assume we have data only in the first sheet.
 HSSFSheet sheet = workbook.getSheetAt(0);
 List homeObjects = new LinkedList();
 HomeObject homeObject;
 for (Iterator rows = sheet.rowIterator(); rows.hasNext();) {
    HSSFRow row = (HSSFRow) rows.next();
    // we know that the first line is for column names. we won't use it
    if (row.getRowNum() == 0) continue;
    homeObject = new HomeObject();
    homeObject.setDoorNo(getCellValue(row.getCell((short) 0)));
    homeObject.setOwnerName(getCellValue(row.getCell((short) 1)));
    homeObjects.add(homeObject);
 }
} catch (IOException e) {
 // todo : error handling
}

Now, we have a list contains column data as HomeObject objects to use.