Read excel file in selenium using JExcel
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
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.
How to integrate bamboo with eclipse
Hi Arhun,
Kindly check with Bamboo APIs https://docs.atlassian.com/atlassian-bamboo/REST/5.5.0/
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
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);
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
Hi Sumana,
If you are using TestNG framework then you can use @dataProvider annotation. I have posted a scenario in which I am using two different fb credentials. You can use this mechanism. Please check this link https://vistasadprojects.com/mukeshotwani-blogs-v2/data-driven-framework-in-selenium-webdriver/
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
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
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
Hi Saurav,
You can set a loop for column increment in order to read data.
HI Mukesh,
Can we write data to excel using JExcel API, like we do it using Apache POI.
Hi Saikrishna,
Yes, JExcel provides support for both read & write.
how to pass xpath value read from excel file to the driver.findElement()?
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.
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?
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.
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
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.
Thanks alot very helpful post.
Welcome dear. keep visiting and let me know for any help.
Sir, thanks a tonn for these useful tutorials. very well explained. I am beginner for Selenium.
Can we write excel files using JExcel API?
Hi Nidhi,
Thank you. I would suggest you to go throw Apache POI which is used in market.
Kindly go through below post and let me know if any help required.
https://vistasadprojects.com/mukeshotwani-blogs-v2/readwrite-excel-files-in-selenium/
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?
HI,
You can use getPhysicalCellNumber to get Column count dynamically.
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.
Hi Ram,
kindly go through below post as well https://vistasadprojects.com/mukeshotwani-blogs-v2/readwrite-excel-files-in-selenium/
Thanks a ton dear for sharing your knowledge.
Your most welcome Heena
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.
Yes Brahma, Here is the link https://vistasadprojects.com/mukeshotwani-blogs-v2/readwrite-excel-files-in-selenium/
Excellent tutorial .. kudos bro 🙂
Thanks Arcillas
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.
Thanks Kiran 🙂 keep learning and let me know if any doubt.
Thanks for the information. It is very useful……………..
Your most welcome Ravi and let me know if any help from my side.
Thanks Mukesh
Its very clear and informative…Thanks for posting this video
Thanks Priya 🙂 Let me know if any help required from my end.
Hi sir
how to a program using excel sheet but that program should be in one method only please guide me sir
Hi Maheeh,
Please follow below video https://www.youtube.com/watch?v=sbBdj4zIMqY
Excellent work…Great Explanation…THANKS A LOTTT
Thanks Paramesh 🙂 Keep visiting