Raghav, your videos are very useful and you explain everything in detail. I have been watching all of your videos and they are extremely useful. Thanks for sharing your knowledge :)
You are such a awesome and great tutor and human as well... One request... Can you please put some real interviews question for those who have 2-3 years of exp in automation.... Please Raghav ji
Brother your way of explanation is as simple and easy to understand & you fully justified your channel name i.e Automation step by step- Raghav pal, it is step by step thanks a lot :)
@@RaghavPal thank you very much. I saw so many videos and resources , i found this data provider concept is simple and best for me. WHat do you recommend me to look now?
Very nice tutorial indeed. i have learnt alot from you Raghav pal ji..keep up the good work.. (which in your case ,we can replace good keyword with Excellent) :)
Raghav it was a very helpful approach but please tell how to incorporate loop in this as in real time we have to read data from multiple cells at one go thanks in advance
Hi Raghav, Awesome teaching and thanks for this video and Could you please make video about how to read web table data's and write that data's into excel sheet so please this is one of the real time project concept asking at interviews.
hello Raghav, thanks for making this so easy for us beginners. I hope you are reading ur comments still. I have one question if the Excel sheet has around 5 sets of usernames and passwords how does this work. how can I use them in cucumber log in
To use multiple sets of usernames and passwords for data-driven testing in Selenium with Java and Cucumber, you can follow the below steps: Store the username and password in a data file (such as a CSV, Excel, or JSON file) with each set of credentials in a separate row or object. Read the data file in your Java code using a library such as Apache POI or OpenCSV. Use a loop to iterate through the rows or objects in the data file, and for each iteration, retrieve the username and password values and pass them as parameters to your login step in Cucumber. In your Cucumber feature file, use placeholders for the username and password parameters, such as and . Use the Cucumber Scenario Outline syntax to define the login scenario with placeholders for the username and password, and provide the values for the placeholders in a table underneath the Scenario Outline. In the step definition, retrieve the username and password values from the placeholders using the Cucumber DataTable API. Here is an example of how the Cucumber feature file, step definition, and data file could look like: *Login.feature* Feature: User login Scenario Outline: Login with valid credentials Given I am on the login page When I enter "" and "" And I click the login button Then I should be logged in Examples: | username | password | | user1 | pass1 | | user2 | pass2 | | user3 | pass3 | *LoginSteps.java* @When("I enter {string} and {string}") public void i_enter_username_and_password(String username, String password) { LoginPage loginPage = new LoginPage(driver); loginPage.enterUsername(username); loginPage.enterPassword(password); } @Then("I should be logged in") public void i_should_be_logged_in() { // Assertions to verify login success } *data.csv* username,password user1,pass1 user2,pass2 user3,pass3 In this example, we are using a CSV file as the data source. We read the CSV file in the Java code using the OpenCSV library, iterate through the rows, and pass the username and password values to the Cucumber step definitions. In the Cucumber feature file, we use the Scenario Outline syntax to define the login scenario with placeholders for the username and password, and provide the values for the placeholders in a table underneath the Examples keyword.
Thank you so much Raghav your detailed explanation, I appreciate your time and ur patience u have . Currently working on it , will get back to you with my results/ queries soon.Once again thank you@@RaghavPal
Hi. This is an amazing video. Very detailed step by step explanation. Thank you! But if we have a huge database in Excel, it is inconvenient to add every time the number of row and column in getCellData to print the values. Can you please show how we can use the for loop here so we can get the whole set of values in one go as output?
Hey pls help.. m getting an error.. null pointer exception and getting values as null.. There are two main methods one in excelutils and one in excelutilsdemo.. wen i remove excelutils main method, it works properly! Wat to do?
Hi Raghav, is it possible to compare two Excel sheets by using selenium webdriver or any other tools? I have scenario like I already have one excel file(sheet1) with some data in File Explorer and I need to open my application and download Excel file(sheet2) through browser. Later I need to compare sheet2 to sheet1 without read and write data.
Tq Raghav it worked ... one more doubt..i want to read an empty row, that means i have 3 input fields and all are empty... is it possible? if so please comment the logic
as a beginner in selenium java, whether knowing all syntax, methods, classes like where and when which selenium syntax is used with java. But how to build a tricky program logic by merging selenium and java code fastly as a beginner? This makes conflicts to learn selenium java
Hi Nida, I will suggest that you do a basic Java course and can start with Selenium Java Framework. You will also learn more as you create the framework. Can take help from programming section here automationstepbystep.com/
hi, the OO in ooxml stands for Open Office. ooxml is a XML-based file format developed by Microsoft for representing spreadsheets, charts, presentations and word processing documents. so it is a Java API To Access Office Open XML documents To work with these docs you will need ooxml api
Very usefull video ,tq so much .. but one doubt can u explain a generic mehtod for reading excel containing both string value numeric value and how to read an empty cell.My excel has all type of values.. please makw a video..
Tq Raghav it worked ... one more doubt..i want to read an empty row, that means i have 3 input fields and all are empty... is it possible? if so please comment the logic
Hi Ajay Sure, you can pass Excel data for some login credentials in Chrome using Selenium Java. Here are the steps on how to do it: 1. Create an Excel file and save it in a location that you can access from your Java program. 2. In the Excel file, create a sheet named "LoginCredentials" and add the following columns: * Username * Password 3. Save the Excel file. 4. In your Java program, create a `WebDriver` object and start a new instance of Chrome. 5. Import the `org.apache.poi.ss.usermodel.Workbook` class. 6. Create a `Workbook` object and load the Excel file that you created in Step 1. 7. Get the sheet named "LoginCredentials" from the `Workbook` object. 8. Get the cell values for the `Username` and `Password` columns. 9. Use the `WebDriver` object to login to the website using the credentials that you got from the Excel file. Here is an example of how to do this in Java: ```java import org.apache.poi.ss.usermodel.Workbook; import org.openqa.selenium.WebDriver; public class LoginWithExcelData { public static void main(String[] args) { WebDriver driver = new ChromeDriver(); // Load the Excel file Workbook workbook = WorkbookFactory.create(new File("login_credentials.xlsx")); // Get the sheet named "LoginCredentials" Sheet sheet = workbook.getSheet("LoginCredentials"); // Get the cell values for the "Username" and "Password" columns String username = sheet.getRow(0).getCell(0).getStringCellValue(); String password = sheet.getRow(0).getCell(1).getStringCellValue(); // Login to the website driver.get("www.example.com"); driver.findElementById("username").sendKeys(username); driver.findElementById("password").sendKeys(password); driver.findElementById("login").click(); } } ``` This code will load the Excel file named `login_credentials.xlsx` and get the cell values for the `Username` and `Password` columns. The code will then use the `WebDriver` object to login to the website using the credentials that it got from the Excel file
Hi raghav, I'm getting this error, Could you please help me on it. I have followed the same like how u showed in video. null java.lang.NullPointerException at utils.Excelutils.getRowCount(Excelutils.java:21) at utils.Excelutils.main(Excelutils.java:10)
Hi Sunil, this is not the complete logs. There is some code error at Excelutils.getRowCount(Excelutils.java:21) I will need full logs and your code to check
@@RaghavPal No such software installation is permitted on client machine. Only Google sheets are allowed. Can you suggest solution for this? "Reading/writing data in Google sheet in selenium with Java"
Hi Kshipra, I have not tried, can check this stackoverflow.com/questions/56321292/read-write-data-in-google-spreadsheet medium.com/appgambit/read-data-from-google-sheet-with-selenium-ans-google-sheets-api-c5d10595f781
Nice explanation, but you are writing and removing in between which is little challenging for beginner like me, can you please show the code after completion
Hi Gilson, Selenium does not have any function for this, will need java for this. stackoverflow.com/questions/38750780/how-to-open-and-save-excel-file-in-java
Hi sir, In excel if it is a string we use getstringvalue and get data for numberic we use getnumbericcellvalu and get data if one row is empty and next row has data how to overcome that empty row and get next row value iam facing error with this can you help me on this
Hi Raghav, Im getting this error Exception in thread "main" org.apache.poi.openxml4j.exceptions.InvalidOperationException: Can't open the specified file: 'C:\Users\ihrwell\Desktop\Automation Webdriver\Projects\Sample\E2EProject\testdata\data.xlsx'
Hi Ihrwell, there must be some more information in the logs, as to what exactly is the issue in the file, Check the path is correct and the file is saved and closed
Hi Raghav, I am getting error as - Exception in thread "main" java.lang.NullPointerException I have followed exact same steps as you showed, what I might have done wrong? Or how can I fix this error?
Hi Purvi, generally null pointer exception comes when you try to use a variable that is not pointing to any object. The most common way to handle it is create a try catch block around the code that is throwing this exception and in the catch block write *catch(Exception exp){ system.out.println (exp.getCause()); system.out.println (exp.getMessage()); system.out.println (exp.printStackTrace()); }* Take care of syntax, spelling, I am just writing directly. www.educative.io/edpresso/how-to-resolve-the-javalangnullpointerexception
Thank you so much for the video. but please help me with this Exception : Exception in thread "main" java.lang.NullPointerException: Cannot invoke "org.apache.poi.xssf.usermodel.XSSFSheet.getPhysicalNumberOfRows()" because "sheet" is null at DataProvider.getRowCount(DataProvider.java:14) at DataProvider.main(DataProvider.java:8) when I am running the file above exception is coming in the console window.
Hi Raghav have a doubt,if we have data driven(through excel) and POM in our framework, will.it be called as a hybrid framework or just data driven framework
Hi Jino, POM can be implemented in data-driven, keyword and hybrid framework as well. Do it does not always mean if you have POM and data driven, then it will be called hybrid always
Hey Raghav :) , another awesome tutorial. Iam just wondering why we get "123.0" as ouput in the console. Is there any reason or way to remove this ".0" ? regards Mario
Hello. The way of Ur teaching is good. I'm getting an error while performing to read the data from Excel by using while loop. i.e, Exception in thread "main" java.lang. error; unresolved compilation problem. CELL_TYPE_STRING Cannot be resolved or is not a field. Please help me to resolve this problem. I'm using selenium 3x .
Hi Gagan, this is due to the data type in your excel. you are either using numerical data in excel and trying to get a string in code or vice versa. Pls check. I believe I have also covered this in the video on numeric and string data type.
i use isdisplay to check the presence of element means button On listing page of flight there is so many flight how can i check the presence of element(Button) for every flight
your tutorial seems clean but somehow I am getting class not found exception. I copied the same code as you showed. package AllExcel; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class ExcelUtils { public static void main(String[] args) { getRowCount(); } public static void getRowCount() { try { // String projectPath = System.getProperty("user.dir"); // XSSFWorkbook workbook = new XSSFWorkbook(projectPath+"/testdata/AddDep.xlsx"); XSSFWorkbook workbook = new XSSFWorkbook("absolute path"); XSSFSheet sheet = workbook.getSheetAt(0); int rowCount = sheet.getPhysicalNumberOfRows(); System.out.println("Number of rows : "+rowCount); } catch(Exception e) { System.out.println(e.getMessage()); System.out.println(e.getCause()); e.printStackTrace(); } } } Error = Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/commons/compress/archivers/zip/ZipFile at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:234) at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:167) at org.apache.poi.ooxml.POIXMLDocument.openPackage(POIXMLDocument.java:89) at org.apache.poi.xssf.usermodel.XSSFWorkbook.(XSSFWorkbook.java:340) at AllExcel.ExcelUtils.getRowCount(ExcelUtils.java:17) at AllExcel.ExcelUtils.main(ExcelUtils.java:9) Caused by: java.lang.ClassNotFoundException: org.apache.commons.compress.archivers.zip.ZipFile at java.net.URLClassLoader.findClass(Unknown Source) at java.lang.ClassLoader.loadClass(Unknown Source) at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source) at java.lang.ClassLoader.loadClass(Unknown Source)
Hi Viki, the error logs complaining of some zip format. Caused by: java.lang.ClassNotFoundException: org.apache.commons.compress.archivers.zip.ZipFile Are you using a maven project. Not sure if you are referring to some zip file. Check this - stackoverflow.com/questions/28229095/apache-commons-compress-using-7zip
Hi Raghav, thanks for your response, I had also posted my error on stackoverflow and got the resolution, There was something missing in the latest Apache poi library as I am not using Maven, so had to download it and added to build path. Your tutorial worked for me, I am also looking to use excel data with Testng dataproviders using for loop, do you have any tutorial on that ? Again, appreciate you taking time for resolving public issues.
how to read values from excel for "Dropdown". I wrote the below code but it's not working, It not showing any errors also. Please check the screenshot: drive.google.com/file/d/1cezdMCTauOoQOJJR2hl64kTHV__Aws_L/view?usp=drivesdk
Hello Raghav, Thank you very much for this informative video once again. I just want to know that while executing the program after calling the getCellDataNumber() function in the main method, I am getting nullPointerException with the message "Cannot invoke "org.apache.poi.xssf.usermodel.XSSFRow.getCell(int)" because the return value of "org.apache.poi.xssf.usermodel.XSSFSheet.getRow(int)" is null". Can you please reply how to handle them so that we don't get such exceptions further? Thanks,
Hi Papun The NullPointerException you are getting is because the `XSSFRow.getCell()` method is trying to access a cell that does not exist. This can happen if the `XSSFSheet.getRow()` method returns `null`. There are a few ways to handle this exception. One way is to add a null check before calling the `XSSFRow.getCell()` method. For example: ```java XSSFRow row = sheet.getRow(1); if (row != null) { int cellValue = row.getCell(0).getNumericCellValue(); } else { // Handle the case where the row is null } ``` Another way to handle this exception is to use the `try-catch` block. For example: ```java try { int cellValue = row.getCell(0).getNumericCellValue(); } catch (NullPointerException e) { // Handle the case where the row is null } ``` The `try-catch` block will catch the NullPointerException and you can handle it accordingly. Here are some other things you can do to prevent this exception: * Make sure that the `XSSFSheet.getRow()` method returns a valid row before calling the `XSSFRow.getCell()` method. * Use the `XSSFRow.getPhysicalNumberOfCells()` method to check the number of cells in a row before calling the `XSSFRow.getCell()` method
Hi..am getting below error..How can i fix it? I was following the exact steps mentioned on the video "Cannot invoke "org.apache.poi.xssf.usermodel.XSSFSheet.getPhysicalNumberOfRows()" because "Utils.ExcelUtils.sheet" is null java.lang.NullPointerException: Cannot invoke "org.apache.poi.xssf.usermodel.XSSFSheet.getRow(int)" because "Utils.ExcelUtils.sheet" is null Cannot invoke "org.apache.poi.xssf.usermodel.XSSFSheet.getRow(int)" because "Utils.ExcelUtils.sheet" is null at Utils.ExcelUtils.getCelldataString(ExcelUtils.java:47) at Utils.ExcelUtils.main(ExcelUtils.java:26) Cannot invoke "org.apache.poi.xssf.usermodel.XSSFSheet.getRow(int)" because "Utils.ExcelUtils.sheet" is null"
The error message "Cannot invoke "org.apache.poi.xssf.usermodel.XSSFSheet.getPhysicalNumberOfRows()" because "Utils.ExcelUtils.sheet" is null" means that the `sheet` variable is null. This can happen if you have not initialized the `sheet` variable, or if you have initialized it to a value of null. To fix this error, you need to initialize the `sheet` variable to a valid Excel sheet object. You can do this by opening the Excel sheet file and creating a new XSSFSheet object. Here is an example of how to initialize the `sheet` variable: ```java FileInputStream fis = new FileInputStream("my_excel_file.xlsx"); XSSFWorkbook workbook = new XSSFWorkbook(fis); XSSFSheet sheet = workbook.getSheet("Sheet1"); ``` Once you have initialized the `sheet` variable, you should be able to access the rows and cells in the Excel sheet without any errors. Here is an example of how to access the rows and cells in the Excel sheet: ```java int rowCount = sheet.getPhysicalNumberOfRows(); for (int i = 0; i < rowCount; i++) { XSSFRow row = sheet.getRow(i); int cellCount = row.getPhysicalNumberOfCells(); for (int j = 0; j < cellCount; j++) { Cell cell = row.getCell(j); String cellValue = cell.getStringCellValue(); System.out.println(cellValue); } } ``` This code will print the value of each cell in the Excel sheet to the console. I hope this helps
This is a great session, Raghav. I learned a ton of really useful stuff. Incitentally, where you use a function to get "user.dir", I have found that a dot character does the same thing. So I used this path for my spreadsheet: "./excel/LoginCredentials.xlsx". Is there a particular reason you don't do that?
I'm new to automation, this was perfect for beginners. Whoever made this thanks a tons!!👍🏼🙏🏼🔥
Glad you liked it Sandhya
Raghav, your videos are very useful and you explain everything in detail. I have been watching all of your videos and they are extremely useful. Thanks for sharing your knowledge :)
You're welcome Shreya. Do share with others
Damn your explanation is on point! And your voice is clear and not irritating! U got my sub! Thank you!!!
Glad to know this was helpful.
Thank you so much sir for made this video. Your teaching skills are awesome sir.
You are most welcome
Its Amazing way to describe the Excel part with additional information , thanks
Glad it was helpful!
You are such a awesome and great tutor and human as well... One request... Can you please put some real interviews question for those who have 2-3 years of exp in automation.... Please Raghav ji
Hi Dheeraj, sure I will do it soon.
Brother your way of explanation is as simple and easy to understand & you fully justified your channel name i.e Automation step by step- Raghav pal, it is step by step thanks a lot :)
Thanks a lot for your message Jahangir. I am humbled
its very helpful and the way you teach is also great.
Glad to hear that Rohan
Your video is truly step by step, very beneficial. Thank you so much for posting the video.
You are welcome!
Thanks Sir this Configuration Helped , Thanks a Lot for Your Efforts which is helping many people.
Most welcome Uday
I recommend everyone to visit your channel.
Thanks a ton
I am learning all his videos very interesting and learned a lot from his courses. Thanks a lot
You are welcome!
Very neat and detailed explanation, Thanks Raghav.
Most welcome Mohsin
Great work sir🙏 very easy explanation. i'm learning selenium automation from your videos. Thank you so much sir.
So nice of you
Each and every concept is so nicely explained.Thanks for creating it.
Most welcome Monica
You did a super job in term of explaining the topic ,easily explained with right sequence .
Thanks Ummul
Wow talk about a masterclass 👌👌👌thank you
Thanks, Mzamo
Thanks for sharing your knowledge. I learned many new things from your tutorials.
Thank you very much @Raghav Sir :)
Most welcome Kishor
I was able to do it on my macbook seamlessly. Thanks a tonne!
Most welcome Deepali
Great tutorial from great teacher. Raghav, your tutorials are really helpful. Thanks!
Glad it was helpful Zuzana
This class very useful the video thank you so much sir, exlent explain sir, very very thanks
You are most welcome
superb explanation ......
Thanks a lot
Thanks for the videos.Everything is explained in detail in all the videos.
You're welcome Vinay
It is very clear and I can understand everything.
Thx.
You're welcome Vicky
Very well explained ☺️. Thankyu Raghav
Most welcome Rajshri
Awesome video... thankyou so much
Most welcome Varun
when i tried to create the other class and call the functions there in video 32:59. I got the errror saying utils.Excelutils.sheet is null
Hi Sagar, just check if you have given the sheet path and name correctly and if you are referring to the cells with some data
@@RaghavPal thank you very much. I saw so many videos and resources , i found this data provider concept is simple and best for me. WHat do you recommend me to look now?
Hi Sagar, as I said in earlier comment check the name, path and cells. Also see if you get specific info in logs
waiting long time for this Chapter..thanks MAN :)
You're welcome Ritesh
Best tutorial I could find to understand working on Excel with Java. Thank you so much Raghav Sir!
So happy to know this Divya, this can also help - ua-cam.com/video/B4G2tMDYjRQ/v-deo.html
Very nice tutorial indeed. i have learnt alot from you Raghav pal ji..keep up the good work.. (which in your case ,we can replace good keyword with Excellent) :)
So happy to know this Shailendra
explained in a brilliant way!!! Thank you so much
You're welcome Deepshikha
just trying to learn, at 19:38 why do you make variables static?
mainly for memory management
@@RaghavPal I still don't understand when to use static, I have the idea that the value you assign is fixed and will never change
I will try to do a session on this
@@RaghavPal that would be wonderful, I love your channel
Raghav it was a very helpful approach but please tell how to incorporate loop in this as in real time we have to read data from multiple cells at one go thanks in advance
Hi Keshav, I will post a video for that on Monday
Waiting for same video with loops where we can use this excel data with real test scenario !!
+1
Good explanation
Thanks Rajesh
Thank you so much Sir,!!! This helped me a lot. Keep Rocking
Most welcome!
You are the best
Humbled
Really really really.. I mean really useful! Thank you so much! 😎👌
Glad it was helpful Orlando
Hi Raghav how we can write the test script using selenium java for comparing links(a tags) with the expected link (in Excel) in the UI Page
I will check Pavan, for now will need to take online help
Hi Raghav, Awesome teaching and thanks for this video and Could you please make video about how to read web table data's and write that data's into excel sheet so please this is one of the real time project concept asking at interviews.
Hi Rajesh, thanks for your message. I will do it in some time
@@RaghavPal ok Raghav. Thank you and am waiting for your new videos ...
Hi sir...easy explanation...how to get data from excel to fill any application everytime....real-time example please
Hi Swapna, did not get "fill any application everytime", you will need to do the setup based on the platform
hello Raghav, thanks for making this so easy for us beginners. I hope you are reading ur comments still. I have one question if the Excel sheet has around 5 sets of usernames and passwords how does this work. how can I use them
in cucumber log in
To use multiple sets of usernames and passwords for data-driven testing in Selenium with Java and Cucumber, you can follow the below steps:
Store the username and password in a data file (such as a CSV, Excel, or JSON file) with each set of credentials in a separate row or object.
Read the data file in your Java code using a library such as Apache POI or OpenCSV.
Use a loop to iterate through the rows or objects in the data file, and for each iteration, retrieve the username and password values and pass them as parameters to your login step in Cucumber.
In your Cucumber feature file, use placeholders for the username and password parameters, such as and .
Use the Cucumber Scenario Outline syntax to define the login scenario with placeholders for the username and password, and provide the values for the placeholders in a table underneath the Scenario Outline.
In the step definition, retrieve the username and password values from the placeholders using the Cucumber DataTable API.
Here is an example of how the Cucumber feature file, step definition, and data file could look like:
*Login.feature*
Feature: User login
Scenario Outline: Login with valid credentials
Given I am on the login page
When I enter "" and ""
And I click the login button
Then I should be logged in
Examples:
| username | password |
| user1 | pass1 |
| user2 | pass2 |
| user3 | pass3 |
*LoginSteps.java*
@When("I enter {string} and {string}")
public void i_enter_username_and_password(String username, String password) {
LoginPage loginPage = new LoginPage(driver);
loginPage.enterUsername(username);
loginPage.enterPassword(password);
}
@Then("I should be logged in")
public void i_should_be_logged_in() {
// Assertions to verify login success
}
*data.csv*
username,password
user1,pass1
user2,pass2
user3,pass3
In this example, we are using a CSV file as the data source. We read the CSV file in the Java code using the OpenCSV library, iterate through the rows, and pass the username and password values to the Cucumber step definitions. In the Cucumber feature file, we use the Scenario Outline syntax to define the login scenario with placeholders for the username and password, and provide the values for the placeholders in a table underneath the Examples keyword.
Thank you so much Raghav your detailed explanation, I appreciate your time and ur patience u have . Currently working on it , will get back to you with my results/ queries soon.Once again thank you@@RaghavPal
your calling static method by using reference variable .is it good approach in java?
(ex ; excel.getCellDataString(0,0))
it is legal, although some coding standards may give warnings
Hi. This is an amazing video. Very detailed step by step explanation. Thank you! But if we have a huge database in Excel, it is inconvenient to add every time the number of row and column in getCellData to print the values. Can you please show how we can use the for loop here so we can get the whole set of values in one go as output?
Hi Krusharth, I will plan to work on this
Hey pls help.. m getting an error.. null pointer exception and getting values as null..
There are two main methods one in excelutils and one in excelutilsdemo..
wen i remove excelutils main method, it works properly!
Wat to do?
Hi, there should be a single main method in a project. If its working fine with one main method, its okay to continue
Can we use just the Apache POI jar directly from MVNRepository?
Hi, it depends on the kind of documents you are working with, Can try and check
@@RaghavPal Thank you.
Hi Raghav, is it possible to compare two Excel sheets by using selenium webdriver or any other tools?
I have scenario like I already have one excel file(sheet1) with some data in File Explorer and I need to open my application and download Excel file(sheet2) through browser.
Later I need to compare sheet2 to sheet1 without read and write data.
Hi Kiran, not with Selenium, will need to check other tools
Thankyou sir ❤
Most welcome Pankaj
Tq Raghav it worked ...
one more doubt..i want to read an empty row, that means i have 3 input fields and all are empty... is it possible? if so please comment the logic
Hi Varsha, check this ua-cam.com/video/B4G2tMDYjRQ/v-deo.html
Hi raghav, how many videos r available of selenium testing
Hi Pravin, can check here automationstepbystep.com/
as a beginner in selenium java, whether knowing all syntax, methods, classes like where and when which selenium syntax is used with java.
But how to build a tricky program logic by merging selenium and java code fastly as a beginner? This makes conflicts to learn selenium java
Hi Nida, I will suggest that you do a basic Java course and can start with Selenium Java Framework. You will also learn more as you create the framework. Can take help from programming section here automationstepbystep.com/
@@RaghavPal thanks
Hi Raghav, how abt cell with value include string and nummeric?
Hi Duc, have explained it here - ua-cam.com/video/CV3SOorFydE/v-deo.html
Thanks for the video. Could you make a video, to read data from Json instead of excel file? Thanks!!
Hi Anup, I will check it soon
@@RaghavPal Hi Raghav, Did you had chance to make a read data from Json ? This will be very helpful when we test API
Sir Awesome explained..bu i have question how we can read exel sheet has 1000 Id and 1000 password how we can give input continuesly
HI Rameshwar, will need to check, try to do hands-on and check the logs
Great tutorial. One question, this method works for both BDD and TestNG frameworks?
Yes, its a java code, will work, Can also see this ua-cam.com/video/B4G2tMDYjRQ/v-deo.html
@@RaghavPal I really appreciate your response. Thank you for everything you do for us.
Thank you for the video. I would like to know the difference between poi and poi-ooxml versions in maven repo. Why did we use ooxml?
hi, the OO in ooxml stands for Open Office. ooxml is a XML-based file format developed by Microsoft for representing spreadsheets, charts, presentations and word processing documents.
so it is a Java API To Access Office Open XML documents
To work with these docs you will need ooxml api
@@RaghavPal Thank you.
Very usefull video ,tq so much ..
but one doubt can u explain a generic mehtod for reading excel containing both string value numeric value and how to read an empty cell.My excel has all type of values..
please makw a video..
Hi Varsha, sure, check this - ua-cam.com/video/B4G2tMDYjRQ/v-deo.html
Tq Raghav it worked ...
one more doubt..i want to read an empty row, that means i have 3 input fields and all are empty... is it possible? if so please comment the logic
Yes, if you follow the video, You will get it
Raghav, if I want to pass Excel data for some login credentials in chrome. How to pass this data instead of printing in console?
Please reply to me
Hi Ajay
Sure, you can pass Excel data for some login credentials in Chrome using Selenium Java. Here are the steps on how to do it:
1. Create an Excel file and save it in a location that you can access from your Java program.
2. In the Excel file, create a sheet named "LoginCredentials" and add the following columns:
* Username
* Password
3. Save the Excel file.
4. In your Java program, create a `WebDriver` object and start a new instance of Chrome.
5. Import the `org.apache.poi.ss.usermodel.Workbook` class.
6. Create a `Workbook` object and load the Excel file that you created in Step 1.
7. Get the sheet named "LoginCredentials" from the `Workbook` object.
8. Get the cell values for the `Username` and `Password` columns.
9. Use the `WebDriver` object to login to the website using the credentials that you got from the Excel file.
Here is an example of how to do this in Java:
```java
import org.apache.poi.ss.usermodel.Workbook;
import org.openqa.selenium.WebDriver;
public class LoginWithExcelData {
public static void main(String[] args) {
WebDriver driver = new ChromeDriver();
// Load the Excel file
Workbook workbook = WorkbookFactory.create(new File("login_credentials.xlsx"));
// Get the sheet named "LoginCredentials"
Sheet sheet = workbook.getSheet("LoginCredentials");
// Get the cell values for the "Username" and "Password" columns
String username = sheet.getRow(0).getCell(0).getStringCellValue();
String password = sheet.getRow(0).getCell(1).getStringCellValue();
// Login to the website
driver.get("www.example.com");
driver.findElementById("username").sendKeys(username);
driver.findElementById("password").sendKeys(password);
driver.findElementById("login").click();
}
}
```
This code will load the Excel file named `login_credentials.xlsx` and get the cell values for the `Username` and `Password` columns. The code will then use the `WebDriver` object to login to the website using the credentials that it got from the Excel file
Hi raghav,
I'm getting this error, Could you please help me on it. I have followed the same like how u showed in video.
null
java.lang.NullPointerException
at utils.Excelutils.getRowCount(Excelutils.java:21)
at utils.Excelutils.main(Excelutils.java:10)
Hi Sunil, this is not the complete logs. There is some code error at Excelutils.getRowCount(Excelutils.java:21)
I will need full logs and your code to check
Thank you so much
You're most welcome
Is it possible to work with online excel data sheet in my selenium framework? I don't have ms office in my system.
Yes, you can use open office, KingSoft office or try with other options and check
@@RaghavPal No such software installation is permitted on client machine. Only Google sheets are allowed. Can you suggest solution for this? "Reading/writing data in Google sheet in selenium with Java"
Any solution?
Hi Kshipra, I have not tried, can check this
stackoverflow.com/questions/56321292/read-write-data-in-google-spreadsheet
medium.com/appgambit/read-data-from-google-sheet-with-selenium-ans-google-sheets-api-c5d10595f781
Hello, How to read a combination of string, numberic and special charcter value form the cell. eg: Jan@2021
can check this - ua-cam.com/video/B4G2tMDYjRQ/v-deo.html
@@RaghavPal - Thanks much sir for replying
Thanks ❤
You're welcome Ilavarasan
Please let me know how to read Alphanumeric values from cell
Hi Rahul, this can help - ua-cam.com/video/B4G2tMDYjRQ/v-deo.html
Nice explanation, but you are writing and removing in between which is little challenging for beginner like me, can you please show the code after completion
will take care Subhasri
Hi, How so save the excel file using Selenium after write and add some rows?
Hi Gilson, Selenium does not have any function for this, will need java for this. stackoverflow.com/questions/38750780/how-to-open-and-save-excel-file-in-java
@@RaghavPal Thank you so much!!!
Hi sir, In excel if it is a string we use getstringvalue and get data for numberic we use getnumbericcellvalu and get data if one row is empty and next row has data how to overcome that empty row and get next row value iam facing error with this can you help me on this
Hi Wasim, watch this ua-cam.com/video/B4G2tMDYjRQ/v-deo.html
Thank you sir
Hi Raghav, Im getting this error Exception in thread "main" org.apache.poi.openxml4j.exceptions.InvalidOperationException: Can't open the specified file: 'C:\Users\ihrwell\Desktop\Automation Webdriver\Projects\Sample\E2EProject\testdata\data.xlsx'
Hi Ihrwell, there must be some more information in the logs, as to what exactly is the issue in the file, Check the path is correct and the file is saved and closed
@@RaghavPal Hi Sir I got the problem :) there is a spacing on the file location thank
can you please write a code to fetch the username and inject in test script to login a site ?
I will plan Vikash
Is it compulsory to call functions?
It will be convenient and efficient to keep the logic in separate reusable functions
I am getting java.Lang.exceptioninitializer error while trying to import from excel, is that because I am using latest jdk version 15.
Hi Roshini, not very sure if its java issue, Just check online
I tried adding (external jars) poi API for excel, it's not getting added..can you plz share the link to download the same for excel file
Hi Syed, you can add maven dependencies in pom.xml
mvnrepository.com/search?q=poi
Hi praveen i am getting error on text value from a numeric cell
Praveen
will need to see the details of your steps and error logs
Hi Raghav,
I am getting error as - Exception in thread "main" java.lang.NullPointerException
I have followed exact same steps as you showed, what I might have done wrong? Or how can I fix this error?
Hi Purvi, generally null pointer exception comes when you try to use a variable that is not pointing to any object. The most common way to handle it is create a try catch block around the code that is throwing this exception and in the catch block write
*catch(Exception exp){
system.out.println (exp.getCause());
system.out.println (exp.getMessage());
system.out.println (exp.printStackTrace());
}*
Take care of syntax, spelling, I am just writing directly.
www.educative.io/edpresso/how-to-resolve-the-javalangnullpointerexception
I have a doubt Raghav, without using Inheritance how you are able to call the functions of Excelutil in Excelutildemo class
By creating objects
Hii sir ..while i using @DataProvider... it will not importing. .tells error like create annotation 'DataProvider'.... what I do '
Hi Praveen, Have you added TestNG library in pom.xml. Try to add again and save and check
@@RaghavPal tq for your reply sir... its working. ..
Thank you so much for the video.
but please help me with this Exception :
Exception in thread "main" java.lang.NullPointerException: Cannot invoke "org.apache.poi.xssf.usermodel.XSSFSheet.getPhysicalNumberOfRows()" because "sheet" is null
at DataProvider.getRowCount(DataProvider.java:14)
at DataProvider.main(DataProvider.java:8)
when I am running the file above exception is coming in the console window.
Hi Arup, will need to check the code and error logs
i did the exactly the same but i am getting the error which says premature end of file?
hi Sagar, in your excel select all the rows after the last row with some data and then delete all these empty rows and try again
Hi sir D's is not working to read the data not testing the test data
Check the setup again Asma
@@RaghavPal Follow D's steps but can't working
how can we sort the data from excel sheet sir
We can Vinay, try checking some java tutorials on this
Hi Raghav have a doubt,if we have data driven(through excel) and POM in our framework, will.it be called as a hybrid framework or just data driven framework
Hi Jino, POM can be implemented in data-driven, keyword and hybrid framework as well. Do it does not always mean if you have POM and data driven, then it will be called hybrid always
@@RaghavPal If i.hv only data driven and.pom .what framework would.it be?
it will still be data driven framework
@@RaghavPal Thanks Raghav.
when i print celldata in syso then it is showing celldata instead of username. how to solve it
Hi Nitin, pls check this video - ua-cam.com/video/B4G2tMDYjRQ/v-deo.html
Awesome
Thanks for watching
Hey Raghav :) , another awesome tutorial.
Iam just wondering why we get "123.0" as ouput in the console. Is there any reason or way to remove this ".0" ?
regards
Mario
It is taking as float, you can do casting,
Thank you so much sir...was waiting for this...sir i have one doubt how to fetch all the values of a single row...pls help
Hi Arjun, will post a video for that on Monday
Ok sir waiting for that video.thanks a lot
Check here ua-cam.com/video/02ANy2pu_ZA/v-deo.html
Hello. The way of Ur teaching is good. I'm getting an error while performing to read the data from Excel by using while loop. i.e, Exception in thread "main" java.lang. error; unresolved compilation problem. CELL_TYPE_STRING Cannot be resolved or is not a field.
Please help me to resolve this problem. I'm using selenium 3x .
Hi Gagan, this is due to the data type in your excel. you are either using numerical data in excel and trying to get a string in code or vice versa. Pls check. I believe I have also covered this in the video on numeric and string data type.
Yeah Really its good and understandable, But i have to know if i have 10 column and 6 rows how will get it?
int rowCount = sheet1.getLastRowNum();
//int columncount=shee1.getLast
int lastCellNumber = sheet1.getRow(0).getLastCellNum();
System.out.println(lastCellNumber);
for(int i=1;i
Check if this helps - ua-cam.com/video/B4G2tMDYjRQ/v-deo.html
i use isdisplay to check the presence of element means button
On listing page of flight
there is so many flight
how can i check the presence of element(Button) for every flight
It will mostly be an html table, you can capture and check the presence of button in a specific column
@@RaghavPal how can i find on the google related this situtation
provide me some link
related to this quaries
See in this example code is used to get text from each cell of the table, You can customize this for your needs
@@RaghavPal
i cant understand ur words
give me some progrmme related to this
When I'm trying to this it displaying error msg: "NoClassDeffoundError", How to resolve this issue ?
Hi, will need to see where exactly you are getting this. Pls send logs
@@RaghavPal it working for .xls files but not working for .xlsx files
Okay, you must have used HSSF, try using XSSF workbook libraries of POI
@@RaghavPal yes, I already tried with xssf but it's not working it showing as same error as class not found
Pls try changing version of POI
your tutorial seems clean but somehow I am getting class not found exception. I copied the same code as you showed.
package AllExcel;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelUtils {
public static void main(String[] args) {
getRowCount();
}
public static void getRowCount() {
try {
// String projectPath = System.getProperty("user.dir");
// XSSFWorkbook workbook = new XSSFWorkbook(projectPath+"/testdata/AddDep.xlsx");
XSSFWorkbook workbook = new XSSFWorkbook("absolute path");
XSSFSheet sheet = workbook.getSheetAt(0);
int rowCount = sheet.getPhysicalNumberOfRows();
System.out.println("Number of rows : "+rowCount);
} catch(Exception e) {
System.out.println(e.getMessage());
System.out.println(e.getCause());
e.printStackTrace();
}
}
}
Error = Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/commons/compress/archivers/zip/ZipFile
at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:234)
at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:167)
at org.apache.poi.ooxml.POIXMLDocument.openPackage(POIXMLDocument.java:89)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.(XSSFWorkbook.java:340)
at AllExcel.ExcelUtils.getRowCount(ExcelUtils.java:17)
at AllExcel.ExcelUtils.main(ExcelUtils.java:9)
Caused by: java.lang.ClassNotFoundException: org.apache.commons.compress.archivers.zip.ZipFile
at java.net.URLClassLoader.findClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
Hi Viki, the error logs complaining of some zip format.
Caused by: java.lang.ClassNotFoundException: org.apache.commons.compress.archivers.zip.ZipFile
Are you using a maven project.
Not sure if you are referring to some zip file. Check this - stackoverflow.com/questions/28229095/apache-commons-compress-using-7zip
Hi Raghav, thanks for your response, I had also posted my error on stackoverflow and got the resolution, There was something missing in the latest Apache poi library as I am not using Maven, so had to download it and added to build path. Your tutorial worked for me, I am also looking to use excel data with Testng dataproviders using for loop, do you have any tutorial on that ? Again, appreciate you taking time for resolving public issues.
Glad to know this Viki. Happy Learning...
Can you add a tutorial on TestNG dataprovider using the above excel data driven approach ? Basically Testng Dataprovider with Excel ?
Hi Viki, you can see this ua-cam.com/video/02ANy2pu_ZA/v-deo.html
And other selenium videos here - automationstepbystep.com/ui-testing/
how to read values from excel for "Dropdown". I wrote the below code but it's not working, It not showing any errors also. Please check the screenshot: drive.google.com/file/d/1cezdMCTauOoQOJJR2hl64kTHV__Aws_L/view?usp=drivesdk
Hi Prasad, will need to see more details. Pls try as shown in video
Hi ur GitHub url
github.com/Raghav-Pal/SeleniumJavaFramework1
@@RaghavPal thanks
:)
Hello Raghav, Thank you very much for this informative video once again. I just want to know that while executing the program after calling the getCellDataNumber() function in the main method, I am getting nullPointerException with the message "Cannot invoke "org.apache.poi.xssf.usermodel.XSSFRow.getCell(int)" because the return value of "org.apache.poi.xssf.usermodel.XSSFSheet.getRow(int)" is null". Can you please reply how to handle them so that we don't get such exceptions further? Thanks,
Hi Papun
The NullPointerException you are getting is because the `XSSFRow.getCell()` method is trying to access a cell that does not exist. This can happen if the `XSSFSheet.getRow()` method returns `null`.
There are a few ways to handle this exception. One way is to add a null check before calling the `XSSFRow.getCell()` method. For example:
```java
XSSFRow row = sheet.getRow(1);
if (row != null) {
int cellValue = row.getCell(0).getNumericCellValue();
} else {
// Handle the case where the row is null
}
```
Another way to handle this exception is to use the `try-catch` block. For example:
```java
try {
int cellValue = row.getCell(0).getNumericCellValue();
} catch (NullPointerException e) {
// Handle the case where the row is null
}
```
The `try-catch` block will catch the NullPointerException and you can handle it accordingly.
Here are some other things you can do to prevent this exception:
* Make sure that the `XSSFSheet.getRow()` method returns a valid row before calling the `XSSFRow.getCell()` method.
* Use the `XSSFRow.getPhysicalNumberOfCells()` method to check the number of cells in a row before calling the `XSSFRow.getCell()` method
Hi..am getting below error..How can i fix it? I was following the exact steps mentioned on the video
"Cannot invoke "org.apache.poi.xssf.usermodel.XSSFSheet.getPhysicalNumberOfRows()" because "Utils.ExcelUtils.sheet" is null
java.lang.NullPointerException: Cannot invoke "org.apache.poi.xssf.usermodel.XSSFSheet.getRow(int)" because "Utils.ExcelUtils.sheet" is null
Cannot invoke "org.apache.poi.xssf.usermodel.XSSFSheet.getRow(int)" because "Utils.ExcelUtils.sheet" is null
at Utils.ExcelUtils.getCelldataString(ExcelUtils.java:47)
at Utils.ExcelUtils.main(ExcelUtils.java:26)
Cannot invoke "org.apache.poi.xssf.usermodel.XSSFSheet.getRow(int)" because "Utils.ExcelUtils.sheet" is null"
The error message "Cannot invoke "org.apache.poi.xssf.usermodel.XSSFSheet.getPhysicalNumberOfRows()" because "Utils.ExcelUtils.sheet" is null" means that the `sheet` variable is null. This can happen if you have not initialized the `sheet` variable, or if you have initialized it to a value of null.
To fix this error, you need to initialize the `sheet` variable to a valid Excel sheet object. You can do this by opening the Excel sheet file and creating a new XSSFSheet object.
Here is an example of how to initialize the `sheet` variable:
```java
FileInputStream fis = new FileInputStream("my_excel_file.xlsx");
XSSFWorkbook workbook = new XSSFWorkbook(fis);
XSSFSheet sheet = workbook.getSheet("Sheet1");
```
Once you have initialized the `sheet` variable, you should be able to access the rows and cells in the Excel sheet without any errors.
Here is an example of how to access the rows and cells in the Excel sheet:
```java
int rowCount = sheet.getPhysicalNumberOfRows();
for (int i = 0; i < rowCount; i++) {
XSSFRow row = sheet.getRow(i);
int cellCount = row.getPhysicalNumberOfCells();
for (int j = 0; j < cellCount; j++) {
Cell cell = row.getCell(j);
String cellValue = cell.getStringCellValue();
System.out.println(cellValue);
}
}
```
This code will print the value of each cell in the Excel sheet to the console.
I hope this helps
This is a great session, Raghav. I learned a ton of really useful stuff.
Incitentally, where you use a function to get "user.dir", I have found that a dot character does the same thing. So I used this path for my spreadsheet: "./excel/LoginCredentials.xlsx". Is there a particular reason you don't do that?
Hi Phil, you can do that, I may have missed
Thanks ❤️
Most welcome Dewanshu