Reading excel sheet in Java

Reading excel sheet in Java

Excel Sheet  

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

Creating excel sheet in Java

Creating excel sheet in Java

Excel Sheet  

In this tutorial we see how to create excel sheet in Java using Apache POI library. By using Apache POI library we can create multiple Microsoft files like .xls, .ppt, .doc etc., Lets start this tutorial with creating excel sheet (.xls) file using POI jar. Before starting our program we need POI jar file which can downloaded from below link

POI jar file (Download latest version)

public class EmployeeDetails{
 
 private int empId;
 private String empName;
 private String designation;
 
 public EmployeeDetails(int empId, String empName, String designation){
  this.empId = empId;
  this.empName = empName;
  this.designation = designation;
 }
 
 public int getEmpId() {
  return empId;
 }
 public String getEmpName() {
  return empName;
 }
 public String getDesignation() {
  return designation;
 }
}

import java.io.File;
import java.io.FileOutputStream;
import java.util.ArrayList;
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 CreateExcel {

 public static void main(String[] args) {
  
  List<EmployeeDetails> list = populateData();
  
  createExcelSheet(list, "D:\\test.xls");
 }
 
 public static List<EmployeeDetails> populateData(){
  List<EmployeeDetails> list = new ArrayList<EmployeeDetails>();
  try{
   list.add(new EmployeeDetails(1, "Raj", "Software Engineer"));
   list.add(new EmployeeDetails(2, "Kamal", "Technical Lead"));
   list.add(new EmployeeDetails(3, "Steve", "Senior Software Engineer"));
   list.add(new EmployeeDetails(4, "David", "Quality Engineer"));
   list.add(new EmployeeDetails(5, "John", "Field Engineer"));
  }catch (Exception e) {
   e.printStackTrace();
  }
  return list;
 }
 
 public static void createExcelSheet(List<EmployeeDetails> list, String fileName){
  
  try {
   
   //Creating Work Book
   HSSFWorkbook workBook = new HSSFWorkbook();
   
   //Creating sheet
   HSSFSheet sheet = workBook.createSheet("Employee Details");
    
   
   Row row = sheet.createRow(0);
   Cell cell = row.createCell(0);
   cell.setCellValue("Employee Id");
   cell = row.createCell(1);
   cell.setCellValue("Employee Name");
   cell = row.createCell(2);
   cell.setCellValue("Designation");
   
   for(int i=0;i<list.size();i++){
    
    EmployeeDetails obj = list.get(i); 
    row = sheet.createRow(i+1);
    cell = row.createCell(0);
    cell.setCellValue(obj.getEmpId());
    cell = row.createCell(1);
    cell.setCellValue(obj.getEmpName());
    cell = row.createCell(2);
    cell.setCellValue(obj.getDesignation());
   }
   
      FileOutputStream fos = new FileOutputStream(new File(fileName));
      workBook.write(fos);
      fos.close();
      System.out.println("Excel sheet created successfully..");
       
  } catch (Exception e) {   
      e.printStackTrace();
  }  
 }
}

OUTPUT: