Data Driven Framework in Selenium WebDriver(Using Apache POI) - Part 1

[@ksdnsdkumar1375]

@1:28 creating a package for utility @2:53 importing opensource Excel Util into utility package @3:22 viewing all methods present in Excel Util @4:58 Downloading and extracting Apache POI library @7:49 importing JAR files of Apache POI library into project (@8:57 Ignore this JAR file.) @9:34 Renaming package of Excel Util @10:50 Creating a package for Datadriven Test. @11:09 Creating a Datadriven Test class under the package. @11:45 Creating a Excel file @12:04 Renaming Excel sheet @13:06 Choosing a site to automate @13:34 Entering data to Excel according to site fields @15:06 Entering our desired data on Excel according to the site fields. @16:20 saving Excel file @17:24 creating a package and moving a Excel file into it. @17:51 closing Excel file and opening Excel file which was present in the project. @19:13 Writing code of invoking browser and opening the site in the Datadriven Test. @19:20 Running the code. @20:03 Creating a object for Excel JAVA file in the Datadriven Test. @20:42 Giving Excel file path to the Datadriven Test. @23:10 Writing a code inside Datadriven Test to fetch data of single row of a column from Excel file. @25:28 Printing that single cell data into console without invoking browser. @26:44 Writing code for fetching the data from remaining rows of that same column and importing into the Datadriven Test. @27:43 Giving names for all String variables. @29:51 Writing code for locators of site input fields into the Datadriven Test. @30:23 Passing String variables to locators. @31:43 Writing locator code for dropdown menu into the Datadriven Test. @35:19 Running the code. @37:22 Adding multiple entries into Excel file. @40:13 Creating a class for parameterizing those multiple entries under package consisting Datadriven Test. @42:23 Using "for loop" to fetch the data from multiple cells from single column of Excel file into this parameterizing class @46:47 Printing the data of multiple cells from single column. @48:31 Writing and printing the code to fetch the data from multiple cells from remaining columns of Excel file into this parameterizing class @50:02 Printing the data from multiple cells in a organized manner. @50:57 Inside "for" loop, writing code for locators of site input fields into this parameterizing class @51:26 Running the code. @52:40 Optizimizing the code to stop multiple invokings of a browser for entering the data into site input fields. @57:29 Running the code.

[@naveenautomationlabs]

You can find the latest Excel Reader util with apache poi api 5.x versio: github.com/naveenanimation20/ExcelUtil

[@vaishnavijaiswal1190]

@@naveenautomationlabs Hi Naveen I follow all the steps but not able to read some fields from Excel sheet can you please help me with that?

[@HitManav]

@@vaishnavijaiswal1190 You can check other video of him

[@iyerakhila123]

Naveen you have explained this concept brilliantly! I regret not having watched this video last week when I had an interview. They asked me this question- "How do you read the data from excel file?".

[@naveenautomationlabs]

Thanks for watching Akhila

[@parvathidevi9289]

what u answered for this question

[@ksdnsdkumar1375]

@@parvathidevi9289 Answer is APACHE POI library.

[@nasruddinmaniyar5325]

How simple your explanation was... Wanna hug you for the beautiful explanation....

[@naveenautomationlabs]

Thanks for watching buddy

[@nasruddinmaniyar5325]

@@naveenautomationlabs that was the first video I just watched...need to finish all your videos asap... Thanks alot...

[@nikitaagrawal790]

Hi Naveen, Your explanation is so simple even I used same explanation in my all interviews and all the interviewers are so impressed by the way I explain the concepts. Thank you so much for all the videos. kudos to you sir!

[@harish3787]

Excellent.... you are the best selenium teacher on KZbin .. You give real world example I learned a lot from you.. hope will get placed soon..

[@naveenautomationlabs]

So nice of you

[@priyasai935]

Thank you so much Naveen. I'm your new subscriber. These days, finding a good tutor online is a major first step in the learning process. I'm so happy that i found you. Thank you.

[@shravankumar-ro9uk]

agree

[@user-qy2kv9sx9m]

Thanks for the concept explanation. It will solve my problem in automation.

[@SubbuTube]

Hi Naveen, thank God I found your channel. You explain things so simple and in detailed way that any person can understand and learn selenium. Thanks for the good work

[@jannatulnayeem6858]

Just thinking, how do you explain so easily and clearly, it's very useful, Thanks Naveen for the videos....

[@mariajurovata]

Great video, great utility, great explanation, thank you very much. Only problem was with Apache POI, but downloading older version (3.6) fixed all the errors.

[@naveenautomationlabs]

Hello All, Sharing the latest Excel Util - Xls_Reader class used to perform read/write operations on Excel files. This util is having all the latest dependencies as per Apache POI api - 4.x.x version. You can get this util from my GIT Repo: github.com/naveenanimation20/NewExcelUtil2020_ApachePOI/blob/master/src/main/java/com/excel/lib/util/Xls_Reader.java

[@jeevanesh25]

Thank you Naveen..

[@piyushmathpal4244]

Hey @naveen Can you please make a quick video on how to perform data driven testing using google spreadsheets. I think this will help a lot to everyone.

[@swapnilkale7039]

hey Naveen - for zip-code here it is reading as 94085.0 instead of 94085, in my code also password field is returning extra .0 could you please help me out on this?

[@naveenautomationlabs]

@@swapnilkale7039 use '94085 in your excel.

[@daxeshshah4982]

Hey @@swapnilkale7039 ,Have you find any solution, I am facing the same issue

[@qal6334]

Thank you from the heart Naveen Sir for your contribution to Tester community

[@omkarpotdar2763]

Thanks a lot Naveen for explaning the data driven approch in simple words. Your videos are always helpful for interviews & project perspectives. Keep it up🙂

[@manishasehgal2401]

Hey Naveen....your videos are quite useful. One thing I really like is your focus is always on to enable people to code/automate rather than just describing how to achieve specific task. Keep it up....Thank you so much.

[@someanimation6807]

I am new To Java and Selenium, I will get a job within one month just watching Naveen videos.

[@rohithreddy8837]

I have watched many videos and still I was in big question mark. But this video really helped me.Thanks Naveen.

[@pravinkokadwar3209]

Super cool explanation !!!

[@vsu1225]

Great Work Naveen,Thanks a lot ,All your videos are useful to my job career .

[@purushothamareddy9396]

Thank You Naveen, Your Explanation is simply amazing...

[@shravankumar-ro9uk]

after searching a lot i got easy explanation Thank you Naveen

[@somukesani0097]

Mind blowing Explanation Naveen garu. Thank you so much.

[@nisharaj9132]

Thank you for your crystal clear explanation Naveen!!

[@amitkhosla8474]

You are great person, God bless you :)

[@akshathadinesh1011]

Amazing Explanation, it's very useful, Thanks Naveen for the videos !!

[@MamtaRani-to4ns]

How beautifully this is explained. Thanks so much, Naveen !!

[@alinakuch3146]

What a wonderful explanation! Thank you.

[@jackft6645]

before watching video i liked beause of u naveen

[@paritapatel2109]

Hi Naveen, you are such an excellent teacher, explaining everything in a simple manner ,so learners can understand easily. Thank you so much for all your support. keep it up.

[@monikkap2308]

Excellent way of teaching...

[@manoranjankumardubey1706]

This is very good and awesome!

[@sarojasaru4473]

Simply superb sir, very easy to understand sir,👌🏻

[@ManishKumar-ow2ew]

Thanks a lot naveen. You have explained it so nicely.

[@sailynagarkar1199]

very helpful video ...easy to understand. Thank you so much sir

[@vishnu-oz5yo]

Dear Naveen, Your videos are awesome and easy to learn. I have a query regarding reading blank cells. I am using apache poi 4.0.0. i am able to get only celltype.BLANK and my test ng code is not taking blank cell as blank and it goes to catch block.please help me resolve this.

[@premtripathi825]

Thanks for ur amazing work... Its really inspiring

[@themusicblaze2692]

HI All, you guys can refer updated xls_reader file. Below is the url. drive.google.com/open?id=0B2i0eXr3_uQDSFpkUElzNGNkYm8

[@naveenautomationlabs]

You can find the latest Excel Reader util with apache poi api 5.x versio: github.com/naveenanimation20/ExcelUtil

[@username12345ghvvv]

Hi Naveen, I am getting below error while trying to run the code: org.apache.poi.poifs.filesystem.OfficeXmlFileException: at org.apache.poi.poifs.storage.HeaderBlock.(HeaderBlock.java:152) at org.apache.poi.poifs.storage.HeaderBlock.(HeaderBlock.java:140) at org.apache.poi.poifs.filesystem.NPOIFSFileSystem.(NPOIFSFileSystem.java:302) at org.apache.poi.hssf.usermodel.HSSFWorkbook.(HSSFWorkbook.java:413) at org.apache.poi.hssf.usermodel.HSSFWorkbook.(HSSFWorkbook.java:394) at Excel.Utility.NALExcelReader.(NALExcelReader.java:56) at DataDriven_Testing.Ebay.main(Ebay.java:33) java.lang.NullPointerException at Excel.Utility.NALExcelReader.getCellData(NALExcelReader.java:84) at DataDriven_Testing.Ebay.main(Ebay.java:34) row 2 or column firstname does not exist in xls Can anyone help me.

[@tayyabzaffar697]

your utility really helps a a lot thanks

[@MegaSoumya12]

Acha padhaya Hai bhai...

[@sridevidurga5317]

Thank you so much for this video. Its help me a lot.

[@shabreentaj1040]

Thank you so much Naveen :) Its an amazing tutorial to learn !

[@naveenautomationlabs]

Thanks for watching :)

[@vishnupandey3513]

Brilliant explanation..... great teaching technique.

[@paramjitbhuller5922]

Just today i got a lecture on it . She did not told all that . Just explained the code that is already written and I was thinking that all that i have to write,

[@santanunayak4402]

Really great explanation....many thanks Naveen...

[@shoyebkhatib8084]

Great explanation Naveen, hats off

[@momentsoflife356]

Thank you so much Naveen!! I couldn't find any better videos than this. You are like an angel for us :) And Please let me know how to purchase. Its declining the transaction even after adding correct details

[@naveenautomationlabs]

Mail me at [e-mail]

[@asmarazaq9122]

Hi Naveen, yu are simply awesome your way of teaching is very impressive indeed. I have got an issue with my test cases I am running on Mac book. The script is accepting neither chrome nor Firefox . It says driver is not executable

[@naveenautomationlabs]

Plz mail me at [e-mail]

[@apoorvachatla2348]

Hi Naveen, thank you so much for such a clear explanation, you rock. I have a doubt when i added Xls_Reader class , i am getting few errors 1. XSSFHyperlink link = createHelper.createHyperlink(XSSFHyperlink.LINK_FILE); error: LINK_FILE cannot be resolved or not a field. 2.style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); error: SOLID_FOREGROUND cannot be resolved or not a field. 3.style.setFillPattern(HSSFCellStyle.NO_FILL); error: NO_FILL cannot be resolved or not a field. Please Help.

[@jeeteshvarma]

download Apachepoi 3.17 version mentioned in the links below the video

[@krishna_raj9331]

Great work sir... Thanks 👌👌

[@PiotrPiotr-mo4qb]

For all who have problems with Xls_Reader just do what Naveen was doing on the video but... I had the same problem as you. To solve problem in InteliJ in the dependencies all POI Jars should be on the top of the list ☺

[@Srhnu449]

i didnt get you. i am getting continuos errors pls help me out ... top of the list means .. r u saying that external jars should be in the top of the list what we have added ?

[@PiotrPiotr-mo4qb]

@@Srhnu449 Yes, I am using Intelij. Watch video carefully to make sure that you are adding all poi dependencies from all folders, and then move them up on the dependencies list.

[@Srhnu449]

r u facing any issues after moving them up. pls tell me .can we connect anydesk session pls . i have an interview after three days . pls help on this.

[@PiotrPiotr-mo4qb]

@@Srhnu449 w8 a second, I am going to work. In some hours I can help you. But please text me.

[@Srhnu449]

@@PiotrPiotr-mo4qb sure man thanks for your help ... will wait here

[@nehamirpagar6393]

Thanks for the video. You are an awesome tutor!!

[@lavanyadasari230]

Amazing teaching Mr Naveen, Thank you for the detailed videos. I subscribed to your channel.

[@tusharkhade10]

Your all videos are good but need to zoom because not snowing some words clearly. Pls always take care about that.

[@alka240280]

Awesome Explanation!! Thanks a lot.

[@Suganthdeepak]

Thanks again for ur amazing work... Its really inspiring

[@footprints2489]

Thank you so much for this video Naveen ...

[@avinashthakur7554]

Superb explanation Naveen...hats off

[@ManojGupta-kx9qv]

Awesome explanation , Thanks a lot Naveen

[@bharathi80205]

HI Naveen, How do you do? I'm continuously following your videos and it's awesome! Can you add the video for that to perform excel operations like copy data from one excel to another, how fetch the specific conditions data(Example: i want fetch the employee names whose salary greater than 1000 like that)

[@ksdnsdkumar1375]

That's not possible.

[@mamathagarlapati]

Excellent explanation...

[@binnymartin1455]

i have to read only 3rd row data from the excel sheet then how can i break the loop and fill only the 3rd row data ??

[@nitingupta82]

Hi Naveen! Please update upon the POI Jar Version to be used so that one does not get Error(s) in Xls_Reader File. Many are facing issues by using the latest version of POI i.e. 4.1.0! Thank you!

[@ritu.a.mullurritu.a.mullur4633]

Hey Naveen thank you , you're videos are very helpfull:)

[@madoneu111]

Nice and good explanation, videos are awesome and it's very clear. Please let me know how to train from you(Paid), wanted to learn Selenium in more detail.

[@memeguycool4581]

Hi Naveen I follow all your videos because you will cover all end to end concepts so that we cannot miss any important question in the interview. Could you please confirm me Is it possible while executing the selenium scripts I wanted to get all the 6 rows data like(Invoice) from the excel sheet to be executed within a single browser by clearing each data

[@prajaktabadarayani8137]

"CELL_TYPE_BLANK cannot be resolved or is not a field" type error we are facing. Please suggest solution

[@rameshwarkachhave2328]

Hi Naveen, Thank you for making videos and giving knowledge to us. Can you make a video for complete framework design? In so many interviews, they are asking like"Have u involved in framework design"? every time i am saying no only and i don't know how it look like. can you please help on this.

[@atulughade]

Thanks naveen

[@mamtanandurkar8680]

Nice

[@iftekerahmed]

thanks a lot guru. I think this video should be in your testNG playerlist, this one is missing over-there, if I am not wrong.

[@ramkumartalatam5507]

Thankyou so much Naveen.

[@narendrasharma9767]

Replace the errors with XSSFHyperlink link = createHelper.createHyperlink(HyperlinkType.FILE); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setFillPattern(FillPatternType.NO_FILL);

[@poojagoel3365]

I was also getting this error. Thank you for the solution

[@myfocus7447]

useful. thanks for sharing

[@thiery572]

Interesting utilities...

[@desuteja502]

Sir,at the time of entering zip/postal code in the website,it is taking the values as float data type in the website(zipcode=123456.0),, may i know why such thing is happening.

[@gayatriganyarpwar2090]

Awesome videos by you Naveen, yesterday asked same question but was unable to answer. It will help me in Future. My question was, How to get data from 3rs row and 4th column?

[@pragyasahu1991]

Hi Naveen, Can you please upload videos on Automation using Qualitia Tool? It will be really very helpful.

[@tejkumarkempaiah4906]

when i print the value for row_count with rows having data upto 5 rows, The S.O.P statement for row_count gives me 1000. Please suggest. Code: package com.datadriven.test; import com.excel.utility.Xls_Reader; public class ParameterizedTest { public static void main(String[] args) { Xls_Reader reader = new Xls_Reader("C:\\Users\\Tejkumar\\Desktop\\DataDrivenFramework\\contactform.xlsx"); int row_count = reader.getRowCount("contactform"); System.out.println(row_count); } } Output: 1000 Expected Output: 5 Actual Output: 1000

[@rihaskh]

Some of the keywords in XLS_reader.java (Like XSSfF,HSSF)are not working in latest version of POI

[@AbhishekKumarChainy]

Rihas Hassan refer this docs.google.com/document/d/1Qg_-iIKCOe0OO2wMiyBcF3bXStH6PzXg7UM49bAsXmI/edit?usp=sharing

[@ankush8585]

@@AbhishekKumarChainy Can you please the dependencies for this?

[@naveenautomationlabs]

Hello All, Sharing the latest Excel Util - Xls_Reader class used to perform read/write operations on Excel files. This util is having all the latest dependencies as per Apache POI api - 4.x.x version. You can get this util from my GIT Repo: github.com/naveenanimation20/NewExcelUtil2020_ApachePOI/blob/master/src/main/java/com/excel/lib/util/Xls_Reader.java

[@lakshmikanthlucky5559]

Hi Naveen, I am not able to import Xls_Reader package.. can u tell me the solution

[@AbhishekKumarChainy]

docs.google.com/document/d/1Qg_-iIKCOe0OO2wMiyBcF3bXStH6PzXg7UM49bAsXmI/edit?usp=sharing

[@darsh_s]

hi naveen ..Excel Utility java code , there is error as below: CELL_TYPE_STRING cannot be resolved or is not a field CELL_TYPE_NUMERIC cannot be resolved or is not a field CELL_TYPE_FORMULA cannot be resolved or is not a field CELL_TYPE_BLANK cannot be resolved or is not a field please reply as soon as possible, i cant move without error correction

[@venkatabhargav3518]

Hi I got the same error. Are you able to resolve it? If you have, please tell you how you did it?

[@justinlambert7109]

Hi , Here you have entering values from data sheet , but without doing any action my question is how can we entering values and validate by click submit button and then entering next data set from sheet and validate?

[@dada6235]

This is so awesome..

[@deshandaniel5584]

Hi Naveen... pls to upload explaination of data driven framework "Xls_Reader" class inside utility package...i'm begineer ..so concern my test

[@archanajain4913]

Thank you so much Naveen for sharing :) I am trying to automate google account creation page but for the field 'Month' in Birthday section i am getting following error when trying to select month:-- Exception in thread "main" org.openqa.selenium.support.ui.UnexpectedTagNameException: Element should have been "select" but was "span" Can you please guide me how to handle this ?Thanks in Advance

[@jaykishoreduvvuri7598]

Hi Naveen, Very nice Explanation! Incredible Job! keep up the good work, Thank You! I have one question how do you get Number/ digits from the excel for example - Telephone number from excel?

[@chundurusukumar3847]

Hi Naveen I have a query. I have 3 sheets in my workbook and 3 methods. Each sheet is for one method first time when I Should be able to read 1 first row from 1st sheet and pass to 1st method , 1st row from 2nd method and pass to method 2 1st row from 3rd sheet and enter to 3rd method like this I want to execute can you please tell me how can we do this ?

[@abraham2961]

Hi Naveen here u r writing column as hard code value if we don't want to hard code this we should take it in global variable just like rowcount you have written in this class as variable and we also need to Tak 2nd for loop for column is it so please rectify me if I m wrong thanks Naveen

[@sainiranjan40]

Never mind I fixed it. - Earlier versions - Cell.CELL_TYPE_STRING - In Latest versions (4.1.0 poi) - CellType.STRING

[@lavanyadasari230]

I tried your solution and that helped, but i still see few more errors. Can you please tell me how to fix the below errors, Thanks in advance style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); and style.setFillPattern(HSSFCellStyle.NO_FILL);

[@devendrapawar2425]

I am also getting the same issue. Please suggest what to do? CELL_TYPE_STRING CELL_TYPE_NUMERIC CELL_TYPE_FORMULA CELL_TYPE_BLANK LINK_FILE GREY_40_PERCENT SOLID_FOREGROUND

[@parvathidevi9289]

sir could pls help me how to resolve the errors

[@stayfit3383]

i also facing this errors

[@avishekbehera]

Why don't we use maven and POM to use dependencies instead importing jar files? amazing work and nicely explained though, thank you.

[@naveenautomationlabs]

In other vdos I have explained with maven as well. Thanks for watching

[@cynoldias7440]

Can you please tell me in which video you have explained?

[@Pravi_FireBreather]

Naveen sir, can you please make a video to read data from powerpoint slides.. Specifically to differentiate the various objects like graph, text, table etc in the slide . Also, wanted to know how to compare the table data from the PPT slide and database table data..

[@sandipb8822]

thank you...!!

[@RehanSayyad-ir7jx]

This Error Will doing Excecution😟😟 import org.apache.poi.hssf.usermodel.HSSFDateUtil; Please Reply

[@tausiffiroj2096]

Hello sir, I have one question suppose there is multiple data in excel sheet but we don't know the count of that data which is store in excel sheet but i want to read one particular data from excel sheet then how we will read particular data from excel sheet .

[@muthinenirajasri336]

XSSFHyperlink link = createHelper.createHyperlink(XSSFHyperlink.LINK_FILE); im getting an error at this line even after adding all the jar files.

[@shrutiprajapati4524]

Hyperlink link = createHelper.createHyperlink(HyperlinkType.FILE); use this

[@aratisingh3851]

Hi Naveen, your video is very good. Have one small question. I have added date column in my excel and passed value "7/12/2020", but i run the code, in console i see the data being displayed as 12/60/20. Can you please help with this

[@909praveen]

Hello Naveen, I have followed all your videos and when it comes to the Excell utilities I am facing issue with the "Xls_Reader" and I have gone through some other videos or websites all the members used " Xssf or Hssf workbook" classes to retrieve the data or to set the data . Could you please let me know whats the difference between Xls_Reader and Xssf workbooks and also let me know why are we unable to import Xls reader class even after adding Apache poi jar files .

[@sandeep123452]

Yes, am also facing same problem. unable to import Xls_reader with Apache POI library

[@SANATANIAMERICA]

Thank you so much naveen ,very useful and explained clearly ! By the way when I am paste the Java file after adding all jar , I am getting error message for few like " can not be a removed type or is not a field " , could you please kindly help

[@closetonature2499]

HI Naveen, Thanks much for sharing wonderful videos on selenium. Please help on how to navigate or the order of learning in posted videos. I see different play lists but not sure on order. Java, selenium locators after this what should I start?

[@ashishmishra7214]

Hi naveen I have a question, What if my registration page is in dyanamic in nature for example: My next input in form will be dependent of my previous values in that case, do I need to write all the possibilities in Excel sheet or any alternatives to that? revert please