Read excel files in JAVA

February 27, 2016 | Updated last March, 2016 | 620 views | Comments

This is a two step JAVA tutorial on how to read an excel file using Apache POI with Maven as the build manager. Use this to read reports, transactions, records, or any data from an excel sheet.

 

Built and tested with the following:

  • Eclipse Juno IDE
  • Apache Tomcat 7.0.47
  • JDK 1.7.0
  • Maven 3.0.4
  • Apache POI 3.14

 

Step 1. Add dependencies

Open pom.xml and add the following under dependencies.


        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.14</version>
        </dependency>
        
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.14</version>
        </dependency>
        
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.14</version>
        </dependency>

 

Step 2. Read the excel file

Create a new JAVA class and put the following codes.

/*
 * Set the excel filename with it's
 * specific location.
 */
String fileName = "/PATH/FILE.xlsx";

try {
    FileInputStream file = new FileInputStream(new File(fileName));
    
    @SuppressWarnings("resource")
    XSSFWorkbook workbook = new XSSFWorkbook(file);
    
    /*
     * Get the first sheet from the workbook.
     */
    XSSFSheet sheet = workbook.getSheetAt(0);
     
    /*
     * Iterate through each row from the first sheet.
     */
    Iterator<Row> rowIterator = sheet.iterator();
    while(rowIterator.hasNext()) {
        Row row = rowIterator.next();
        
        /*
         * For each row, iterate through each column.
         */
        Iterator<Cell> cellIterator = row.cellIterator();
        while(cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            
            /*
             * Temporarily set all cell values to string.
             */
            cell.setCellType(Cell.CELL_TYPE_STRING);
            
            /*
             * Do whatever is needed on the cell value.
             */
            System.out.println(cell.getStringCellValue());
        }
    }
    file.close();
} catch (FileNotFoundException e) {
    e.printStackTrace();
} catch (IOException e) {
    e.printStackTrace();
}