Wednesday, November 19, 2014

Java : Read from Excel (xls) file

Java : Reaf from Excel (xls) file

Reading from a text file is common. But, reading from an excel(.xls) file is not.
Recently, I got to do so. Little bit Google search and experimentation resulted in the following.

I used HSSF from Apache-POI.
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

public void readFromXlsFile (String pathToFile, String sheetName){
    try {
        FileInputStream file = new FileInputStream(pathToFile);

        HSSFWorkbook workbook = new HSSFWorkbook(file);
        HSSFSheet sheet = workbook.getSheet(sheetName);

        Iterator<Row> rowIterator = sheet.rowIterator();
           
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();

            if (row.getRowNum() == 0) {
                continue;// skip first row, as it contains column names
            }

            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                if (cell.getColumnIndex() == 0) {
                    System.out.print(cell.getStringCellValue() + "\t\t");
                } else if (cell.getColumnIndex() == 1) {
                    System.out.print((long) cell.getNumericCellValue()+ "\t\t");
                } else if (cell.getColumnIndex() == 2) {
                    System.out.print((int) Math.round(cell.getNumericCellValue())+ "\t\t");
                }
            }
            System.out.println("");
        }

        file.close();

    } catch (FileNotFoundException fnfe) {
        fnfe.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
}
To run the above example, create a sheet in excel file with String, Long and Integer columns respectively.

To print column names, run a while loop with cell.getStringCellValue() on every cell in first row.

getStringNumericCellvalue() returns 'double' and you can see from the example that I've type-casted it to 'long' and 'int'.

To get a sheet by its index, use
getSheetAt(index)
in place of
getSheet(sheetName)