Read Data from Excel file in java.

We cas read or write the data in Excel using apache poi apis. In below example I'm going to use "poi-3.12-20150511.jar".

Step 1- Create Excel file with some data.

Year    Country    By
1975    West Indies    17 run
1979    West Indies    92 run
1983    India    43 run
1987    Australia    7 run
1992    Pakistan    22 run
1996    Sri Lanka    7 wicket
1999    Australia    8 wicket
2003    Australia    125 run
2007    Australia    53 run
2001    India    6 wicket
2015    Australia    7 wicket

Step 2- Create
String fileName = "D:\\Ashish\\Personal\\WebApplication1\\iccworlcuplist.xls";
FileInputStream file = new FileInputStream(new File(fileName));



Step 3- Create HSSFWorkbook class object and get the first sheet using workbook.getSheetAt(0) method.
HSSFWorkbook workbook = new HSSFWorkbook(file);
HSSFSheet sheet = workbook.getSheetAt(0);

Step 4- Get all the rows in form of List and get Row object which one having all the cell value of each row.
Iterator rowIterator = sheet.iterator();
Row row = rowIterator.next();

Step 5- Iterate this Row object and get all the cell values.
Iterator cellIterator = row.cellIterator();

Step 6- Retrieve all the cell value based on their types.
switch (cell.getCellType()) {
    case Cell.CELL_TYPE_BOOLEAN:
        System.out.print(cell.getBooleanCellValue() + "\t\t");
        break;
    case Cell.CELL_TYPE_NUMERIC:
        System.out.print(cell.getNumericCellValue() + "\t\t");
        break;
    case Cell.CELL_TYPE_STRING:
        System.out.print(cell.getStringCellValue() + "\t\t");
        break;
}

Example:

ReadFromExcel.java

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51

package com.atozjavatutorials; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.util.Iterator; 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 class ReadFromExcel { public static void main(String[] args) { String fileName = "D:\\Ashish\\Personal\\WebApplication1\\iccworlcuplist.xls"; try { FileInputStream file = new FileInputStream(new File(fileName)); //Get the workbook instance for XLS file HSSFWorkbook workbook = new HSSFWorkbook(file); //Get first sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(0); //Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); //For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: System.out.print(cell.getBooleanCellValue() + "\t\t"); break; case Cell.CELL_TYPE_NUMERIC: System.out.print(cell.getNumericCellValue() + "\t\t"); break; case Cell.CELL_TYPE_STRING: System.out.print(cell.getStringCellValue() + "\t\t"); break; } } System.out.println(""); } file.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } }

Output:

Year            Country             By       
1975.0        West Indies        17 run       
1979.0        West Indies        92 run       
1983.0        India                   43 run       
1987.0        Australia            7 run       
1992.0        Pakistan             22 run       
1996.0        Sri Lanka          7 wicket       
1999.0        Australia           8 wicket       
2003.0        Australia           125 run       
2007.0        Australia           53 run       
2001.0        India                 6 wicket       
2015.0        Australia          7 wicket