How to read data from excel sheet using Apache POI in Java?

Here is the complete Java code to read and write from the excel sheet in Java. Please note that you will have to download and add POI library to current project from url http://poi.apache.org/download.html

The package org.apache.poi.hssf contains the xls implementations
The package org.apache.poi.xssf contains the xlsx implementations

package framework;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Iterator;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;

public class Excel {

 /**
  * @param args
  */
 public static void main(String[] args) {
    
FileInputStream file = null;
HSSFWorkbook workbook;

Futil.createHtmlHead(1);


try {
 
 
 
   
 file = new FileInputStream(new File("F:\\selenium\\batch.xls"));
 workbook = new HSSFWorkbook(file);
 HSSFSheet sheet = workbook.getSheetAt(0);
 Iterator<Row> rowIterator = sheet.iterator();
 int k=0;
 while (rowIterator.hasNext())  
    {
    rowIterator.next();
    k++;
    }
 
 System.out.println("Total rows in the sheet " + k);
 int intRowCounter = 1;
 Row row =  sheet.getRow(intRowCounter);
 System.out.println("Data in the excel " +  readcell(row,2));
 
 row.getCell(1).setCellValue("salunke");
 FileOutputStream fos = new FileOutputStream("F:\\selenium\\batch.xls");
    workbook.write(fos);
    fos.close();

} 
catch(Exception ex){
System.out.println("Exception occured" + ex.toString());
}

finally{
 
 try{
 file.close();
 }catch(Exception ex){
  
  System.out.println(ex.toString());
 }
 
 
}

 }//main method ends
 
 
 
//to read the data
 
 public static String readcell(Row rowObj,int colNum)
 {
  String x="";
 try{
  if  (rowObj.getCell(colNum).getCellType() == 1)
   x = rowObj.getCell(colNum).getStringCellValue();
  else if  (rowObj.getCell(colNum).getCellType() == 0)
   x = "" + (int)rowObj.getCell(colNum).getNumericCellValue();
 }
 catch(Exception e){
  x = "";
 //System.out.println(e.toString() + " while reading a cell");
  }
  
  return x;
 }
 
 
 //to write the data
 public static void writeCell(Row rowObj,int colNum, String data)
 {
  
  try{
    
    rowObj.getCell(colNum).setCellValue((String) data);
   
  }
  catch(Exception e){
  
   System.out.println(e.toString() + " while writing a cell");
  }
  
  
 }
 
 

} //class ends

You may also like to read below topics.

1. Press key in Selenium Webdriver in Java
2. Read Table Data in Selenium Webdriver in Java
3. Take the screenshot in Selenium Webdriver in Java
4. Execute JavaScript in Selenium Webdriver in Java
5. Handle multiple browser windows in Selenium Webdriver in Java
6. Double-Click on the Element in Selenium Webdriver in Java
7. Exceptions in Selenium Webdriver in Java
8. Synchronization in Selenium Webdriver in Java
9. Frameworks in Selenium

What do you think on above selenium topic. Please provide your inputs and comments. You can write to me at reply2sagar@gmail.com