Automation

Selenium WebDriver tutorial Step by Step

You are here: Home / Selenium Framework / Read excel file in selenium using JExcel

Read excel file in selenium using JExcel

by 43 Comments

Read excel file in selenium

Hello Welcome in this post we will see how to Read excel file in selenium using Jexcel.

As we, all know Selenium support only browser automation, so for reading and writing with Excel files we have to user third party API like JExcel and Apache POI.

JExcel- JExcel is free API to work with Excel files; it comes with predefined method, classes and interface, which we can directly use.

Limitation of JExcel- It only support .xls files it means files whose extension is .xls files 

 

some useful classes and interface, which we will be using
Workbook (Class) – Will handle workbook.

Sheet (Interface) – Which will handle sheets

Cell (Interface) – Which will handle cell

Download JExcel API

Open any browser and navigate to below URL

http://mirrors.ibiblio.org/pub/mirrors/maven/net.sourceforge.jexcelapi/jars/jxl-2.6.jar

 

Download zip file and extract the same. After extraction, you will get jexcel.jar file.

 

Read excel file in selenium
Read excel file in selenium

 

 Add JExcel jar to project

Right Click on project then Click on Build path> then Click on configure build path then Go to Library Section then
Click on add external jars and now attach jar file click on Apply finally click on  Save button

 

Program- Read excel file in selenium-  Youtube

Create a separate package  into src package

ExcelRead> create a new Class TestRead

package ExcelRead;
import java.io.File;
import java.io.IOException;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import org.testng.annotations.Test;

public class TestRead {

@Test
public void TestReadData(){

// You need to use File class which will ask for file location.I specified  base// directory //using dot(.) operator then inside data folder I have testdata.xls// stored

File src=new File("./data/testdata.xls");

try {
// Workbook is a class in Jexcel which will take file as an argument and getWork//book is a predefined method which will read the workbook and will return the w//Workbook object

Workbook wb=Workbook.getWorkbook(src);

// Workbook is loaded now we have to load sheet so using workbook object (wb) we// can call getSheet method which will take index as an argument and will load t//he sheet, we can also specify the sheetname also

Sheet sh1=    wb.getSheet(0);

// Sheet is loaded then we have to read cell so using sh1 object call getCell me//thod which we take two arguments getCell(column,row)

Cell  c1=sh1.getCell(0,0);

//Cell is loaded then using getContents method we have  to extract the data usin//g getContents() methods
// this method will always return you String.
// now you are done

String data1=c1.getContents();

/ /Print the data
System.out.println(data1);

System.out.println("Sheet data is "+data1);

} catch (BiffException e) {

e.printStackTrace();

} 
catch (IOException e)
{            
e.printStackTrace();
}

}

Thanks for visiting my blog. Keep in touch.

Reader Interactions

Comments

  2. sumana s says

    How can i select drop down values from excel sheet? I have to select gender and rest of the values are being passed from excel sheet. kindly suggest

    Reply

    • Mukesh Otwani says

      Hi Sumana,

      Using Apache POI or JExcel, read data from excel and save it to some variable. Then pass this variable as argument to Select class defined method where you can call selectClassObj.selectByVisibleText(variable_containing_value_from_excel_cell);

      Reply

      • Sumana S says

        Thanks mukesh. Got it. i have one more problem. how can i read all the rows and columns from excel? Actually i need to create 100 users to check for load testing. i have excel file with 100 users with name,email id, user role and gender. how can i create user by reading those files at a time? kindly help with a sample code to understand

        Reply

          • Sumana S says

            Hi Mukesh, how can we come across when we have same xpaths for different p tags. i create a nugget in which i need to add object. it is working fine. when i add next nugget and try to click on add object it is not discovering the element as add object of 1st nugget is also having same path as of second. kindly help

          • Mukesh Otwani says

            Hi Sumana,

            p tags stands for paragraph in html language. On canvas, if you keep on adding new line, p tags also keep on incrementing. So there ultimately you won’t get any concrete locator in this case. I recommend you to not use p tags while xpath creation

  3. saurav kumar says

    Hello Mukesh,
    I went for adp interview there they asked me how to read data from excel but the condition is that even if position of column is change then also it will read the correct data from excel .Let say the position of 2nd column move to 3rd column . so they told me to write the read excel in dynamic way

    Reply

    • Mukesh Otwani says

      Hi,

      Whatever cell content you are reading from excel pass it as argument to driver.findElement(By.xpath()). For this you need to set some loop or define row, column combination in order to read data from excel.

      Reply

      • Sudha says

        Hi Mukesh,
        If i have By.name(“username”) in excel file and i read it as string value but i want to pass it as parameter to driver.findElement(—-) .. String can’t be typecasted to By. can you help me please?

        Reply

        • Mukesh Otwani says

          Hi Sudha,

          As of now it is not possible to typecast String value to By. You need to create a method in your framework which can handle values from excel and pass required locator value corresponding to its type such xpath, id, linktext etc.

          Reply

  6. Vaishnavi says

    Hi I want to take inputs from excel sheet and feed it to username or passwords field in UI. Can you please help me with that

    Reply

    • Mukesh Otwani says

      Hi Vaishnavi,

      This is what I mentioned in my post. Whatever data you read from excel pass it as it is to sendKeys parameter against webelement for username and password fields respectively.

      Reply

  8. nidhi says

    Sir, thanks a tonn for these useful tutorials. very well explained. I am beginner for Selenium.
    Can we write excel files using JExcel API?

    Reply

  9. kanchana says

    hi mekesh sir, In data driven testing you hard coded the columns, could you please give me the code for DDT ratherthan hardcoding for column?

    Reply

  10. Ram Narayan says

    Sir i am getting the “jxl.read.biff.BiffException” exception. I think the file is incompatible as i’ve changed the extension manually from xlsx to xls. Am i correct ? how can i test my piece of code.

    Reply

  12. Brahma says

    HI Mukesh,

    After a lot of searching in the internet, I found a very good website to learn selenium and Iam following your youtube videos regularly. Thanks for your information.
    I have one doubt, I want to read a data from Excel(.xls file) using Apache poi. I dont want to use Jxcel jar.
    please post a code for that.

    Reply

  14. Kiran Bagisetty says

    Hello Mukesh,

    After a lot of search, I think landed at the right place. Thanks a ton for your info and training. I would be a frequent visitor to your site.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.