Reading excel sheet in Java

 


In our earlier tutorial we have seen how to create excel sheet using Apache POI library in Java. As same we will see how to read excel sheet file using Apache POI. We are using same Employee Details class and excel sheet which used in our earlier tutorial for this demo. So before looking into this tutorial please take a look on earlier tutorial as how we have created the Excel sheet using POI and bean class which we have used. 

POI jar file (Download latest version)


import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

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 ReadExcel {
 
 public static void main(String[] args) {
  
  List<EmployeeDetails> list = readExcelSheet("D://test.xls");
  
  displayEmployeeDetails(list);
 }
 
 public static List<EmployeeDetails> readExcelSheet(String fileName){
  
  List<EmployeeDetails> list = new ArrayList<EmployeeDetails>();
  
  try{
   FileInputStream file = new FileInputStream(new File(fileName));
  
   //Creating Work Book
      HSSFWorkbook workBook = new HSSFWorkbook(file);
   
      //Read first sheet from Excel 
      HSSFSheet sheet = workBook.getSheetAt(0);
       
      //Reading sheet rows 
      Iterator<Row> rows = sheet.iterator();
      
      // Moving to next row to get employee details. Excluding headers 
      rows.next();
      
      while(rows.hasNext()) {
       int empId;
       String empName;
       String designation;
       
       Row row = rows.next();
          
          Iterator<Cell> cellIterator = row.cellIterator();

          empId = (int) cellIterator.next().getNumericCellValue();
          empName = cellIterator.next().getStringCellValue();
          designation = cellIterator.next().getStringCellValue();
          
          list.add(new EmployeeDetails(empId, empName, designation));          
      }   
  }catch (Exception e) {
   e.printStackTrace();
  }
  return list;
 }
 
 public static void displayEmployeeDetails(List<EmployeeDetails> list){
  
  System.out.println("Employee ID \t Employee Name \t Designation");
  for(int i=0;i<list.size();i++){
   EmployeeDetails obj = list.get(i);
   System.out.print(obj.getEmpId()+"\t\t");
   System.out.print(obj.getEmpName()+"\t\t");
   System.out.print(obj.getDesignation()+"\n");
  }
 }
}

OUTPUT:


Employee ID   Employee Name   Designation
1  Raj  Software Engineer
2  Kamal  Technical Lead
3  Steve  Senior Software Engineer
4  David  Quality Engineer
5  John  Field Engineer

No comments:
Write comments