Read Excel file in Selenium using JXL

How Selenium supports reading of Excel File?

Reading of Excel file can be achieved with the help of API called JXL. Currently JXL only support xls type file.

Steps to download jxl2.6 jar file and configure in the Project

Step1: Go to the URL:-http://www.findjar.com/jar/net.sourceforge.jexcelapi/jars/jxl-2.6.jar.html and down load the jar file
Step 2: Extract the jar file at the location of download
Step3: On Eclipse>Go to Project and right Click on it >Go to build path>Select Configure build pat>Under Libraries ,click on Add External Jar to add the Jar file


Steps to Read the Excel File

• Specify the Location of File
• Load WorkBook
• Load Sheet: Sheet can be loaded via getSheet() method , sheet name /index can be passed as argument.By default Sheet1 has zero index and so on
• Load Cell: getCell() method used with 2 arguments, first argument points to column of excel sheet and second argument points to row of excel sheet 
• Read Data

 import java.io.File;  
 import java.io.IOException;  
 import org.apache.commons.io.FileUtils;  
 import org.openqa.selenium.By;  
 import org.openqa.selenium.OutputType;  
 import org.openqa.selenium.TakesScreenshot;  
 import org.openqa.selenium.WebDriver;  
 import org.openqa.selenium.chrome.ChromeDriver;  
 import org.openqa.selenium.firefox.FirefoxDriver;  
 import org.testng.annotations.Test;  
 import org.testng.asserts.*;  
 import jxl.Workbook;  
 import jxl.read.biff.BiffException;  
 public class ExcelRead {  
      public static void main(String[] args) throws BiffException, IOException {  
           File f = new File("D:/TestTest.xls");  
           Workbook w = Workbook.getWorkbook(f);  
           String c =w.getSheet("Sheet1").getCell(0, 0).getContents();  
           System.out.print(c);  
      }  
 }  

Notes

1-Eclipse support one forward slash or two backward slash ,so we have used forward slash in order to locate the file

2- Workbook is a class so we have created one reference variable "w"

3- getWorkbook() is a static method which is used to load the Work Book.As getWorkbook() is a static method it has been called by its class name

4-getContents() method return a string value so we have stored in a string called "c"

Output

Column 0 and Row 0 value of Excel file has been Printed 

Excel Sheet

Excel Sheet Used