Read Excel XLS Files in Java

This tutorial shows how to read Excel .xls files using Java and Apache POI. The Apache POI Project (http://poi.apache.org/) provides Java APIs for manipulating file formats based upon the Office Open XML standards (OOXML) and Microsoft’s OLE 2 Compound Document format (OLE2). In short, it helps read and write MS Excel files using Java.

Download the latest Apache POI from this download page. In Java class, import the relevant packages.

import org.apache.poi.openxml4j.exceptions.*;
import org.apache.poi.ss.usermodel.*;

The following method reads from an xls file row by row and cell by cell.

static void 
readXls(String fname) throws InvalidFormatException, IOException  
{
    InputStream in = new FileInputStream(fname);
    Workbook wb = WorkbookFactory.create(in);
    Sheet sheet = wb.getSheetAt(0);
    
    int line = 0;
    for (Row row : sheet)        // loop through each row
    {
        System.out.print((line++) + ": \t");

        for (Cell cell : row)    // loop through each cell
        {
            switch (cell.getCellType())
            {
                case Cell.CELL_TYPE_STRING:
                    System.out.print(cell.getRichStringCellValue());
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell))
                        System.out.print(cell.getDateCellValue());
                    else
                        System.out.print(cell.getNumericCellValue());
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    System.out.print(cell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    System.out.print(cell.getCellFormula());
                    break;
                default:
                System.out.print(" -- ");
            }
        }
        
        System.out.println();
    }
}

To use this method, simply provide a file name.

String fname = "...";
try {
    readXls(fname);
} catch (Exception e) {
    e.printStackTrace();
}
		

Comments

comments

Tagged with: ,
Posted in Java