P20 - Integration of Excel with Dataprovider | TestNG | Testing Framework |

In this video, I have explained about "Integration of Excel with Dataprovider ".
I have discussed about these below points:
1. Libraries available for reading the data from Excel file
2. Downloading and Installation of Apache POI java library
3. How to read the data from excel file dynamically?
4. How to create the dataprovider by reading the data from excel file?
5. How to maintain the dataprovider in a separate class?
=======================================
▶️ Next Video Link: • P21 - Parallel executi...
◀️ Previous Video Link: • P19 - How to create th...
↔️ TestNG Playlist Link: bit.ly/3wXyuXB
🔗 Code used in this video: www.hyrtutoria...
✴ Checkout my other playlists: bit.ly/3gLIAVL
☕ Buy me a coffee: bit.ly/33ljBWc
👑 Join my youtube channel to get access to perks:👇
/ @hyrtutorials
========================================
========================================
Connect me @
🔗 Website - www.hyrtutoria...
🔗 Telegram - t.me/hyrtutorials
🔗 Facebook - / hyrtutorials
🔗 LinkedIn - / hyrtutorials
🔗 Twitter - / hyrtutorials
🔗 Instagram - / hyrtutorials
========================================
========================================
🙏 Please Subscribe🔔 to start learning for FREE now, Also help your friends in learning the best by suggesting this channel.
#hyrtutorials #selenium #testng
TestNG by Yadagiri Reddy

[@HYRTutorials]

↔️ TestNG Playlist Link: bit.ly/3wXyuXB ✴ Checkout my other playlists: bit.ly/3gLIAVL ☕ Buy me a coffee: bit.ly/33ljBWc

[@kishore2115]

Bro report portal gurunchi explain chesthara?

[@HYRTutorials]

Em report portal buddy?

[@TusharMohite-w9s]

I watch all your videos helps me a lot thank you sir....

[@prabasrini3099]

Your explanation is really awesome and we can compare it with real time scenario that not found in many other videos.. thank you so much

[@TusharMohite-w9s]

thanx dude ...i got it now also clear my java concepts ......

[@ramanimoharil7991]

Just amazing explaination man... You told what is required ..long way to go..best luck for your career and lots of blessings

[@HYRTutorials]

Thankyou so much buddy

[@marlynj7930]

Your videos are so informative----Your videos should have more views from aspirants like me!Thank You!I will share this video as much as I can.

[@HYRTutorials]

Thankyou buddy 😊

[@radhesaran1761]

amazing way.....u have proper concept and very easy and understandable steps .heads of to u man.

[@HYRTutorials]

Thanks buddy 😊

[@Sumedha_N_Bandara]

Very informative video. Learnt lot from this video. Thank you very much * wish you all the very best to make more leaning materials.

[@HYRTutorials]

Thanks buddy 😊

[@Nagasai-ri9wk]

Amazing explanation bro. Your playlist is enough to learn things in a easy way.

[@Aryan-fk5iy]

Very much informtative......God bless u brother...Keep posting..

[@HYRTutorials]

Thanks buddy 😊

[@ranapratap8480]

Crystal clear explanation, thanks for making this useful video, thank you❤❤🙏🙏

[@HYRTutorials]

Glad you liked it

[@bharathpybodi2842]

Amazing videos Bro

[@nagasaimaddula56]

Amazing explanation bro. Thank you:)

[@naveenmunnelli]

Top Class explanation, I love your all videos 😍

[@HYRTutorials]

Happy to hear that buddy

[@atulanand4164]

Excellent Explanation!!

[@goutamnayak3685]

Excellent explanation sir, thank you so much for covering this concept

[@HYRTutorials]

Thanks buddy 😊

[@BN-bn9fk]

Thank you buddy! Great approach!

[@user-hc9lq7ui8h]

agile, cucumber,apitesting,jira tool .... please explain all these

[@indiantourism975]

excellent explanation bro

[@HYRTutorials]

Thanks buddy 😊

[@knjaswanthi9683]

Excellent sir

[@DebunkSensibly]

Thank you so much!

[@HYRTutorials]

Happy learning buddy 😊

[@Bell4Fun]

Bro ...you are doing great Job ...could you provide something on Design Patterns as this now trends during interview for SDET's 😉

[@HYRTutorials]

Thankyou buddy. Yes now a days it's being asked in many interviews even though some of them are not really used in the projects. I will make the videos on those.

[@dileepvraj86]

@@HYRTutorials waiting sir....

[@padminivenkataram524]

Hello, very nicely explained. Can we merge two data providers if we need to merge data from two excel files??

[@HYRTutorials]

I don't think we can do that buddy

[@mangeshpanchwagh9742]

Sir can we add parallel= true in dataprovider so all will run simultaneously. It is very nice video.you explained nicely. Thank you sir

[@HYRTutorials]

Yes buddy. I have explained that in P21 video buddy

[@saurabhkumar5787]

Nice explanation

[@HYRTutorials]

Thanks buddy 😊

[@kishorebabu544]

hii i am work in intellij when i run this file it goes to the another file but current file is not running

[@NamRaj2021]

I have small doubt ...bro you manually run the three test cases or they automatically done ???? Can we run 3 test cases by creating three methods and giving test method priority.

[@HYRTutorials]

Yes we can do that buddy. When u execute the TestNG class, it executes all the test methods present in it. But if you want to execute only few methods as part of one test and few other as part of another test then you can use TestNG xml file buddy

[@munindharr1039]

super excellent

[@HYRTutorials]

Thanks buddy 😊

[@vanajachowdary3998]

please make tutorials on page object model framework, i found no relevant clear tutorials for this

[@sridharr3924]

Very Nice Explanation Bro..

[@HYRTutorials]

Thanks buddy 😊

[@user-lr5qx6lh2i]

If we want to write data to excel cell dynamically how to do it pls explain...

[@chintadakiranraj7893]

Hi sir Can you tell me plz I have the pivot table If I enter the values in the one column cell the some columns in same row was filling automatically because of formule. But while entering values into column through Java code it is not reflecting the formule just it is filling the particular column only. But manually formule is apply but for the same sheet .it is not working through Java code how can we do that can you plz any hint or any suggestions plz

[@sasikalachekuri7335]

Hello Mr. Reddy , I have been watching testNG series from past two days the way you are explaining is too good, I have a question on excel data provider, In this video you have hard coded the excel file name in excelDataSuplier class as static , but I want to pass the excel file name from the main test, Is it possible if yes ? How?

[@HYRTutorials]

That is little difficult, but you can identify which test method is calling that dataprovider and based on that you can decide which excel file you want to use. To achieve this you need to add one parameter called Method m to the data provider. this m variable will give you the information about the test method.

[@sasikalachekuri7335]

@@HYRTutorials Thank you

[@poojajadhav9529]

You are just excellent sir ❤️

[@HYRTutorials]

Thankyou buddy 😊

[@SivaChaitanyaReddySanivarapu]

Good Work Sir

[@HYRTutorials]

Thanks

[@JeyasankarA]

Hi Bro Your teaching was very useful. Can you put one video for json integration with DataProvider?

[@HYRTutorials]

Yeah will plan that buddy

[@amerkhan2934]

@HYR Tutorials . Hi Reddy sir , It is very helpful video for me , can you tell me if we are using standalone Mobile apk and we are passing the credentials from the xl sheet , how we can implement quit method() , Because after running one time its not going for second iteration , I think you have missed this in this video

[@HYRTutorials]

usually every test should start the browser freshly and thats whyi have shown like this buddy. but if we wanted to keep the instance then we can call the quit method in aftersuite annotation

[@pandudamera7211]

Can you please make it one video of how to read data from Json and yaml sir

[@HYRTutorials]

yeah sure buddy

[@srikanthreddy6690]

Hi sir have one query, suppose if you have two test cases one is login and one is log out before test I have initialized the url after test i have used driver.quit method iam running these cases through testng xml file ihave applied the data provider concept like you said how it will execute it's not going to second user

[@HYRTutorials]

Did you mention the data provider to the test method?

[@srikanthreddy6690]

@@HYRTutorials yes mentioned sir can you provide your mail id I'll send the code which I wrote

[@HYRTutorials]

@@srikanthreddy6690 send the code on [e-mail]

[@sonalishelar4242]

Sir i am getting an error : java.lang.OutOfMemoryError: Java heap space. So can you please suggest me what i am doing wrong. the code is same .

[@HYRTutorials]

Try to increase the headspace of your eclipse buddy

[@sairamchinnu3238]

Please tell me how to read it for multiple sheets

[@moneyminded27]

How to provide test data from excel to testcase using testng and pom

[@AnilKumar-sf4em]

Hi Yadagiri, My requirement is, dataprovider having {1}{2}{3} data and I have 3 methods in a class, I want to pass {1} to first method and {2} to second method and {3} to third method, Is it possible, Plz provide the solution. Thanks kumar

[@HYRTutorials]

Thats not how you should be using the dataprovider buddy. But you can do that. Watch the indices concept in this playlist kzbin.info/www/bejne/mqCTc6SilLaJnsk Apply this concept on your test methods and you are good to go.

[@rupaliranotkar8744]

I followed the same steps but getting an error that can't invoke the method due to class mismatch Can you reply it's very urgent

[@HYRTutorials]

Send me the screenshots on [e-mail]

[@srinivas4449]

Good work

[@HYRTutorials]

Thank you so much 😀

[@srinivas4449]

@@HYRTutorials welcome

[@PratikHood]

Hello Sir, Can you create a video where we can fetch the data from excel using the test case name. My team is trying to lot to search such kind of tutorial but we are not getting it. Logic we are looking for : First it will search for the workbook then sheetName and then TestCaseName and the data which is inline with the test case, that will be fetch in the Test case.

[@HYRTutorials]

You can't find the videos as it is custom requirement buddy. But if you understand the excel reading concept, designing this requirement won't be that difficult.

[@chaitanyamutluri7580]

hi bro,thanks for all of your viedos. Naku 1st set data print avtundhi, After that [ java.lang.NullPointerException: Cannot invoke "org.apache.poi.xssf.usermodel.XSSFCell.getStringCellValue()" because the return value of "org.apache.poi.xssf.usermodel.XSSFRow.getCell(int)" is null ] error vastundhi.

[@HYRTutorials]

Which means you are trying to access the empty row or empty cell

[@sajeerk5561]

How to handle the browser closing after the test fails?

[@HYRTutorials]

You can handle it in after test annotated method buddy. Just like how we capture screenshots on test failure. There is one video I have created on the screenshots capturing thing. That gives you the idea

[@sudhakarthangavelu7164]

How to pick data based on header name and store it in two dimensions array

[@HYRTutorials]

We need to implement that logic buddy. First you should know how to read data from excel then you can iterate the rows and store it into the object array

[@ashishkumar-tm9cp]

can you make videos of maven with pom and log 4j ,jenkins,github please

[@HYRTutorials]

Sure will plan buddy

[@chinni_channel.7587]

Nice

[@HYRTutorials]

Thankyou buddy 😊

[@shubhamchinchalkar5010]

great sir

[@HYRTutorials]

Thanks buddy 😊

[@pragyachauhan7377]

I have one doubt, this video is based upon 2-d array having multiple username and password only in Excel sheet. But can you tell me, what IF in Excel there are more than 5-10 columns...what will be the dimension of array object in data provider? Do I have to create object array with 5-10 [][][] brackets in data provider ? What is the approach we should use in this case?

[@dileepvraj86]

No even if we have 10 0r 20 columns in sheet, two-dimensional array will do the job for us, All we have to do is change the parameters type in method which is using data provider to array and use those variables in array in test method.

[@HYRTutorials]

Here it doesnt matter about the number of columns in the excel sheet buddy. Even if you have 1000 columns it is going be a two dimensional array only.

[@RamaKrishna-ng4of]

Sir ,when i try to run its showing Data Provider Mismatch error

[@HYRTutorials]

Did you provide the correct name buddy?

[@mranjith6585]

can You do a video on passing data from a Pojo class of java which returns from a json file ?

[@HYRTutorials]

Sure buddy

[@kalaimohan5421]

Thank u

[@HYRTutorials]

Glad you liked it buddy 😊

[@nagasri3664]

Giri... Do u have videos on POM

[@HYRTutorials]

No, I haven't created them yet

[@nagasri3664]

@@HYRTutorials please do it, when you have time.. that will be helpful to many people.. why I am asking you mean your way of explanation is clear to understand

[@HYRTutorials]

Sure buddy

[@chaithramn6821]

Sir if I gave invalid username and password also it's showing test case pass

[@HYRTutorials]

Are you doing any assertion buddy?

[@yay4meluigi]

DON’T DO IT! It is an absolute nightmare because Git cannot see XLSX files or handle merge conflicts. I’m forced to use a repo with this setup and we can’t see the xlsx updates in Pull Requests and data is always getting mistakenly deleted from the excel sheets when PRs are merged. 😢

[@VishalSingh-we6zx]

Please try with CSV

[@rajeshrao5099]

Can you share code in description?

[@HYRTutorials]

Already the link is available in description buddy. Can u check

[@sayyadimam434]

How can we contact you sir?

[@HYRTutorials]

You can check the about section in my youtube channel buddy

[@shareefnagoor4401]

Hii

[@HYRTutorials]

Hi

[@sowjanyachattu5625]

Hi sir, I'm getting this exception(Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/commons/io/output/UnsynchronizedByteArrayOutputStream). could u please tell me the solution

[@HYRTutorials]

U might have missed some library, check once

[@sowjanyachattu5625]

@@HYRTutorials ok sir, thanks for ur reply

[@kanaktandon1573]

System.out.print(sheet.getPhysicalNumberOfRows()); while executing this , i am getting ACCES IS DENIED Error

[@HYRTutorials]

Did you set any password or special permissions to your excel file buddy? Or close the excel file and try again

[@kishoripatil2302]

Getting exception as java.lang.NoClassDefFoundError

[@HYRTutorials]

Change the jre system library and restart the eclipse buddy