Selenium Framework for Beginners 18 | How to use Excel for getting data in Selenium Java Framework

[@sandhyaeisha7817]

I'm new to automation, this was perfect for beginners. Whoever made this thanks a tons!!👍🏼🙏🏼🔥

[@RaghavPal]

Glad you liked it Sandhya

[@dheerajsingh6568]

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

[@RaghavPal]

Hi Dheeraj, sure I will do it soon.

[@shreyazarkar]

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 :)

[@RaghavPal]

You're welcome Shreya. Do share with others

[@rohangawade5291]

its very helpful and the way you teach is also great.

[@RaghavPal]

Glad to hear that Rohan

[@C.Sakthivignesh]

Thank you so much sir for made this video. Your teaching skills are awesome sir.

[@RaghavPal]

You are most welcome

[@user-tj7gu3xv5k]

Thanks Sir this Configuration Helped , Thanks a Lot for Your Efforts which is helping many people.

[@RaghavPal]

Most welcome Uday

[@jahangiralikhan2410]

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]

Thanks a lot for your message Jahangir. I am humbled

[@YogeshC-qv1kg]

Its Amazing way to describe the Excel part with additional information , thanks

[@RaghavPal]

Glad it was helpful!

[@tekkenfight3006]

Damn your explanation is on point! And your voice is clear and not irritating! U got my sub! Thank you!!!

[@RaghavPal]

Glad to know this was helpful.

[@101_twentySomethings]

I was able to do it on my macbook seamlessly. Thanks a tonne!

[@RaghavPal]

Most welcome Deepali

[@paridapolat8491]

Your video is truly step by step, very beneficial. Thank you so much for posting the video.

[@RaghavPal]

You are welcome!

[@ummulwara5542]

You did a super job in term of explaining the topic ,easily explained with right sequence .

[@RaghavPal]

Thanks Ummul

[@fitnessdaily7288]

I am learning all his videos very interesting and learned a lot from his courses. Thanks a lot

[@RaghavPal]

You are welcome!

[@zuzanafarkasova5244]

Great tutorial from great teacher. Raghav, your tutorials are really helpful. Thanks!

[@RaghavPal]

Glad it was helpful Zuzana

[@monicagoyal8225]

Each and every concept is so nicely explained.Thanks for creating it.

[@RaghavPal]

Most welcome Monica

[@AnalysisWithVinay]

Thanks for the videos.Everything is explained in detail in all the videos.

[@RaghavPal]

You're welcome Vinay

[@ashvinmonani1839]

Great work sir🙏 very easy explanation. i'm learning selenium automation from your videos. Thank you so much sir.

[@RaghavPal]

So nice of you

[@kishore76dhaka]

I recommend everyone to visit your channel.

[@RaghavPal]

Thanks a ton

[@rayan4203]

Very neat and detailed explanation, Thanks Raghav.

[@RaghavPal]

Most welcome Mohsin

[@kishorgupta3241]

Thanks for sharing your knowledge. I learned many new things from your tutorials. Thank you very much @Raghav Sir :)

[@RaghavPal]

Most welcome Kishor

[@divyadeshmukh3335]

Best tutorial I could find to understand working on Excel with Java. Thank you so much Raghav Sir!

[@RaghavPal]

So happy to know this Divya, this can also help - kzbin.info/www/bejne/eGWqY6eDeb6diLM

[@deeparanchi]

explained in a brilliant way!!! Thank you so much

[@RaghavPal]

You're welcome Deepshikha

[@keshavdwivedi5192]

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

[@RaghavPal]

Hi Keshav, I will post a video for that on Monday

[@TheNishi42]

Waiting for same video with loops where we can use this excel data with real test scenario !!

[@sachin21890]

+1

[@vickyyang7393]

It is very clear and I can understand everything. Thx.

[@RaghavPal]

You're welcome Vicky

[@onlineaccount9073]

superb explanation ......

[@RaghavPal]

Thanks a lot

[@jatothkavitha6012]

This class very useful the video thank you so much sir, exlent explain sir, very very thanks

[@RaghavPal]

You are most welcome

[@mzamomahaeng268]

Wow talk about a masterclass 👌👌👌thank you

[@RaghavPal]

Thanks, Mzamo

[@varunbhardwaj191]

Awesome video... thankyou so much

[@RaghavPal]

Most welcome Varun

[@englishanti]

You are the best

[@RaghavPal]

Humbled

[@nachmeriraninach907]

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

[@RaghavPal]

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.

[@nachmeriraninach907]

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

[@nidaiqbal8949]

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

[@RaghavPal]

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/

[@nidaiqbal8949]

@@RaghavPal thanks

[@rajshriabojwar2535]

Very well explained ☺️. Thankyu Raghav

[@RaghavPal]

Most welcome Rajshri

[@subhasrinayak3670]

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

[@RaghavPal]

will take care Subhasri

[@riteshwankhede3259]

waiting long time for this Chapter..thanks MAN :)

[@RaghavPal]

You're welcome Ritesh

[@rajeshdevan6051]

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.

[@RaghavPal]

Hi Rajesh, thanks for your message. I will do it in some time

[@rajeshdevan6051]

@@RaghavPal ok Raghav. Thank you and am waiting for your new videos ...

[@varshabagewadi3338]

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

[@RaghavPal]

Hi Varsha, check this kzbin.info/www/bejne/eGWqY6eDeb6diLM

[@rameshwarkonale1763]

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

[@RaghavPal]

HI Rameshwar, will need to check, try to do hands-on and check the logs

[@blankbox33]

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) :)

[@RaghavPal]

So happy to know this Shailendra

[@sunilkumarp1630]

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)

[@RaghavPal]

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

[@anupkapale7953]

Thanks for the video. Could you make a video, to read data from Json instead of excel file? Thanks!!

[@RaghavPal]

Hi Anup, I will check it soon

[@arpanbhagat]

@@RaghavPal Hi Raghav, Did you had chance to make a read data from Json ? This will be very helpful when we test API

[@luvpreetsingh8566]

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?

[@RaghavPal]

Hi, there should be a single main method in a project. If its working fine with one main method, its okay to continue

[@krusharthshah3740]

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?

[@RaghavPal]

Hi Krusharth, I will plan to work on this

[@pankajsahu1657]

Thankyou sir ❤

[@RaghavPal]

Most welcome Pankaj

[@srija_sk]

Thank you so much Sir,!!! This helped me a lot. Keep Rocking

[@RaghavPal]

Most welcome!

[@sreeragph2069]

Thank you so much

[@RaghavPal]

You're most welcome

[@rajeshrao5099]

Good explanation

[@RaghavPal]

Thanks Rajesh

[@swapnab7239]

Hi sir...easy explanation...how to get data from excel to fill any application everytime....real-time example please

[@RaghavPal]

Hi Swapna, did not get "fill any application everytime", you will need to do the setup based on the platform

[@orlandodelacruz4027]

Really really really.. I mean really useful! Thank you so much! 😎👌

[@RaghavPal]

Glad it was helpful Orlando

[@dewanshu]

Thanks ❤️

[@RaghavPal]

Most welcome Dewanshu

[@varshabagewadi3338]

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..

[@RaghavPal]

Hi Varsha, sure, check this - kzbin.info/www/bejne/eGWqY6eDeb6diLM

[@varshabagewadi3338]

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

[@RaghavPal]

Yes, if you follow the video, You will get it

[@pavang2827]

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

[@RaghavPal]

I will check Pavan, for now will need to take online help

[@sagarshrestha7418]

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

[@RaghavPal]

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

[@sagarshrestha7418]

@@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?

[@RaghavPal]

Hi Sagar, as I said in earlier comment check the name, path and cells. Also see if you get specific info in logs

[@shaan597]

I have a doubt Raghav, without using Inheritance how you are able to call the functions of Excelutil in Excelutildemo class

[@RaghavPal]

By creating objects

[@K.Ajaykumar]

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

[@RaghavPal]

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

[@DucNguyen-xn5bd]

Hi Raghav, how abt cell with value include string and nummeric?

[@RaghavPal]

Hi Duc, have explained it here - kzbin.info/www/bejne/eYeWhIKlp6usmqc

[@mahikarnati3630]

your calling static method by using reference variable .is it good approach in java? (ex ; excel.getCellDataString(0,0))

[@RaghavPal]

it is legal, although some coding standards may give warnings

[@RAHUL-hq1yl]

Please let me know how to read Alphanumeric values from cell

[@RaghavPal]

Hi Rahul, this can help - kzbin.info/www/bejne/eGWqY6eDeb6diLM

[@Vinodmhetre007]

Can we use just the Apache POI jar directly from MVNRepository?

[@RaghavPal]

Hi, it depends on the kind of documents you are working with, Can try and check

[@Vinodmhetre007]

@@RaghavPal Thank you.

[@lillyberta422]

Great tutorial. One question, this method works for both BDD and TestNG frameworks?

[@RaghavPal]

Yes, its a java code, will work, Can also see this kzbin.info/www/bejne/eGWqY6eDeb6diLM

[@lillyberta422]

@@RaghavPal I really appreciate your response. Thank you for everything you do for us.

[@pravinbirajdar7314]

Hi raghav, how many videos r available of selenium testing

[@RaghavPal]

Hi Pravin, can check here automationstepbystep.com/

[@sagarshrestha7418]

i did the exactly the same but i am getting the error which says premature end of file?

[@RaghavPal]

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

[@Kiranreddy09]

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.

[@RaghavPal]

Hi Kiran, not with Selenium, will need to check other tools

[@surendran8008]

Hello, How to read a combination of string, numberic and special charcter value form the cell. eg: Jan@2021

[@RaghavPal]

can check this - kzbin.info/www/bejne/eGWqY6eDeb6diLM

[@surendran8008]

@@RaghavPal - Thanks much sir for replying

[@aruproy9815]

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.

[@RaghavPal]

Hi Arup, will need to check the code and error logs

[@Vinodmhetre007]

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?

[@RaghavPal]

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

[@Vinodmhetre007]

@@RaghavPal Thank you.

[@purvikala614]

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?

[@RaghavPal]

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

[@VikashKumar-sg9uy]

can you please write a code to fetch the username and inject in test script to login a site ?

[@RaghavPal]

I will plan Vikash

[@broshini5023]

I am getting java.Lang.exceptioninitializer error while trying to import from excel, is that because I am using latest jdk version 15.

[@RaghavPal]

Hi Roshini, not very sure if its java issue, Just check online

[@rangabharath4253]

Awesome

[@RaghavPal]

Thanks for watching

[@NizamNeha]

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 .

[@RaghavPal]

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.

[@syedhannan8588]

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

[@RaghavPal]

Hi Syed, you can add maven dependencies in pom.xml mvnrepository.com/search?q=poi

[@dasarianveshitha7386]

Hi praveen i am getting error on text value from a numeric cell

[@RaghavPal]

Praveen will need to see the details of your steps and error logs

[@arjunms1736]

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

[@RaghavPal]

Hi Arjun, will post a video for that on Monday

[@arjunms1736]

Ok sir waiting for that video.thanks a lot

[@RaghavPal]

Check here kzbin.info/www/bejne/ZmOkf6xopdqSkKM

[@ihrwellyurtas9155]

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'

[@RaghavPal]

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

[@ihrwellyurtas9155]

@@RaghavPal Hi Sir I got the problem :) there is a spacing on the file location thank

[@wasimakram-nz5gq]

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

[@RaghavPal]

Hi Wasim, watch this kzbin.info/www/bejne/eGWqY6eDeb6diLM

[@wasimakram-nz5gq]

Thank you sir

[@subhadeepchatterjee7439]

Is it compulsory to call functions?

[@RaghavPal]

It will be convenient and efficient to keep the logic in separate reusable functions

[@gilsonsouza8159]

Hi, How so save the excel file using Selenium after write and add some rows?

[@RaghavPal]

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

[@gilsonsouza8159]

@@RaghavPal Thank you so much!!!

[@nitinjangra4773]

when i print celldata in syso then it is showing celldata instead of username. how to solve it

[@RaghavPal]

Hi Nitin, pls check this video - kzbin.info/www/bejne/eGWqY6eDeb6diLM

[@jerinthomas3283]

Yeah Really its good and understandable, But i have to know if i have 10 column and 6 rows how will get it?

[@jerinthomas3283]

int rowCount = sheet1.getLastRowNum(); //int columncount=shee1.getLast int lastCellNumber = sheet1.getRow(0).getLastCellNum(); System.out.println(lastCellNumber); for(int i=1;i

[@RaghavPal]

Check if this helps - kzbin.info/www/bejne/eGWqY6eDeb6diLM

[@mrolle282]

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

[@RaghavPal]

It is taking as float, you can do casting,

[@jillbv9380]

just trying to learn, at 19:38 why do you make variables static?

[@RaghavPal]

mainly for memory management

[@jillbv9380]

@@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

[@RaghavPal]

I will try to do a session on this

[@jillbv9380]

@@RaghavPal that would be wonderful, I love your channel

[@kshipra4504]

Is it possible to work with online excel data sheet in my selenium framework? I don't have ms office in my system.

[@RaghavPal]

Yes, you can use open office, KingSoft office or try with other options and check

[@kshipra4504]

@@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"

[@kshipra4504]

Any solution?

[@RaghavPal]

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

[@9745754904]

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

[@RaghavPal]

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

[@9745754904]

@@RaghavPal If i.hv only data driven and.pom .what framework would.it be?

[@RaghavPal]

it will still be data driven framework

[@9745754904]

@@RaghavPal Thanks Raghav.

[@VINAYKUMAR-gv4tx]

how can we sort the data from excel sheet sir

[@RaghavPal]

We can Vinay, try checking some java tutorials on this

[@asmashaikshaik7896]

Hi sir D's is not working to read the data not testing the test data

[@RaghavPal]

Check the setup again Asma

[@asmashaikshaik7896]

@@RaghavPal Follow D's steps but can't working

[@socialmediahappy8697]

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

[@RaghavPal]

It will mostly be an html table, you can capture and check the presence of button in a specific column

[@socialmediahappy8697]

@@RaghavPal how can i find on the google related this situtation

[@socialmediahappy8697]

provide me some link related to this quaries

[@RaghavPal]

See in this example code is used to get text from each cell of the table, You can customize this for your needs

[@socialmediahappy8697]

@@RaghavPal i cant understand ur words give me some progrmme related to this

[@gvvprasad4744]

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

[@RaghavPal]

Hi Prasad, will need to see more details. Pls try as shown in video

[@praveenkumarmurugesan296]

Hii sir ..while i using @DataProvider... it will not importing. .tells error like create annotation 'DataProvider'.... what I do '

[@RaghavPal]

Hi Praveen, Have you added TestNG library in pom.xml. Try to add again and save and check

[@praveenkumarmurugesan296]

@@RaghavPal tq for your reply sir... its working. ..

[@sesh07]

When I'm trying to this it displaying error msg: "NoClassDeffoundError", How to resolve this issue ?

[@RaghavPal]

Hi, will need to see where exactly you are getting this. Pls send logs

[@sesh07]

@@RaghavPal it working for .xls files but not working for .xlsx files

[@RaghavPal]

Okay, you must have used HSSF, try using XSSF workbook libraries of POI

[@sesh07]

@@RaghavPal yes, I already tried with xssf but it's not working it showing as same error as class not found

[@RaghavPal]

Pls try changing version of POI

[@learnvik]

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)

[@RaghavPal]

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

[@learnvik]

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.

[@RaghavPal]

Glad to know this Viki. Happy Learning...

[@learnvik]

Can you add a tutorial on TestNG dataprovider using the above excel data driven approach ? Basically Testng Dataprovider with Excel ?

[@RaghavPal]

Hi Viki, you can see this kzbin.info/www/bejne/ZmOkf6xopdqSkKM And other selenium videos here - automationstepbystep.com/ui-testing/

[@mudaseerahmed3944]

Hi ur GitHub url

[@RaghavPal]

github.com/Raghav-Pal/SeleniumJavaFramework1

[@mudaseerahmed3944]

@@RaghavPal thanks

[@Deniz-ss1sv]

:)

[@papunkumar8761]

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,

[@RaghavPal]

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

[@user-jt6bt1jq1x]

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"

[@RaghavPal]

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

[@philb4462]

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?

[@RaghavPal]

Hi Phil, you can do that, I may have missed

[@ilavarasansriraman4140]

Thanks ❤

[@RaghavPal]

You're welcome Ilavarasan