Apache POI Tutorial Part10 - Data Driven Testing in Selenium | TestNG DataProvider and Excel

hello everyone welcome you all in the today's video we are going to learn a very important topic which is data driven testing in selenium so we can achieve this by using excel file along with test ng data provider annotation now as a first step we will see how we can do data driven testing by using test ng data provider and afterwards i will also show you how we can read the data from excel and how data provider will use that particular data now let's get started so before that let me show you an application which we are going to automate so i have a small uh application so admin dot no commerce dot com dot login so i want to test this login with multiple sets of data so i can pass email address and password and just perform the login test and here i'll perform positive and negative testing so i'll pass some of the inputs are valid some of them are invalid so both the cases i will test this particular test case okay now let us go to eclipse and uh because we are going to write our test engine test case using selenium so we need to set up our project with our selenium so for that what i can do is so we only have apache poi package which contains all the excel related jar files now i'm going to add selenium web driver jars to this particular project so for that what you can do is we need to download selenium webdriver jars just go to selenium official website psyllium.dev and just go to cylinder driver download click on download and here if i just come down you can just download java version of webdriver so you can just click on this download which will download a zip file uh which contains uh so many jar files and uh we need to extract that particular zip file just look at this i have just downloaded and when extracted this and you will get a new folder like this inside that folder you can find two jar files inside this ellipse also you can find multiple java files so we need to add all these jar files to your project now let's go to eclipse and now i'm going to add those jar files so right click on project go to properties so i already downloaded those jar files i'm just attaching them so go to properties and which will give you a new window go to java build path and here we have to add all the charts under library section just go to additional charts now here if i just go to my drivers folder so i already copied earlier my selenium webdriver jars now we can just look at here so this is uh h selenium yeah this is the one so selenium java 3.141 and also have latest versions java 4 so you can take anything no problem at all so i can just take this uh two jar files and adding first then click on add external jars and go to leaps folder select all of them i say okay all right so with this we have done selenium setup on my java project now we are ready to write our selenium test cases so apart from this we need also have our test ng so for that what i can do is i can just add test in the library to this project again go to right click on project properties and in the same java libraries java build path and libraries here i'm adding add library and select text ng as a finish and now we can also download test engine jar file and you can also add here all right so if you are using may uh maven project you can just download dependency for maven pom.xml and you can add the dependency in the palm.xml and you can get that from the maven repository i'll say mvnrepository.com and just type selenium java and just selenium java you can get this and click on this and once you click on this java these are the different versions are available and whichever version you want just click on that version and you can get this dependency and add that into pom.xml okay and similarly you can also get the test ng jar we can just say test ng and this is my test ng click on the test ng now click on this and you can add this dependency in the palm.xml so in that case you don't need to add library to the project so now i have a small project and i already added apache poi libraries and selenium libraries everything is there now i'm going to create a one basic test case data driven test case using data provider annotation from testng so before that uh this is application which i already shown you and this is the login part i want to automate and before that we need to have our test data right so i already have some test data which is prepared so let me just show you the test data how i have prepared so i have just opened my excel file so this is my test data which contains a number of usernames and passwords and here i'm just putting some result like if i pass this combination this is a valid data and this is invalid data this is also invalid this is also invalid so i have used multiple combination because i want to perform positive as well as negative testing like valid username and valid password valid username has invalid password then invalid username and valid password and both are invalid so i have taken four combinations and you can add more number of combinations of data here and you can also put one column here whether they are valid data or invalid data because we are also going to use this uh in our automation along with this data we are also going to check this to perform some negative testing okay so i'll show you at the end of the video you will understand exactly how we can use this data in our test case and this is my excel data which contains and now let's directly go to eclipse and go to src and under this i'm going to create a new package i name it as data driven testing i say finish so this is a new package which i have created and after that inside this package i'm going to write a new test case so right click here new class and here i'm giving i'll say data driven test data driven test and let's say finish now i'm going to create a test ng test case okay so we don't have main method we have only test methods now inside this test case i'm not going to use excel for now um as a first step i'll show you how we can perform data driven testing by using data provider method and after that i'll show you how data provider method get the data from excel file okay now so as a first step what i will do is i'll create a public void setup method and this method is responsible for setting up the driver and launching the browser all these things and i'll execute this method before class i'll say this is before class i'll execute then import this before class from uh test ng org dot testing dot annotations and i'll also create one global variable called webdriver and driver variable i create and import this webdriver from org.opencure.selenium all right so once you created this driver variable i'll initiate this inside the setup method so as a first step what you have to do is we need to also uh set the property of the driver so system dot set property and this is a class or key webdriver.com.driver and the location of my driver so after that i'll initiate this driver driver equal to i'll start with the new chrome chrome browser so i'll say new chrome driver this is my new chrome driver class and this will internally invoke the default constructor and which will launch our browser all right so once it is done so before that we need to download this chromedriver.exe and which is also required to work with the chrome browser okay so these are all prerequisites which we need to have before itself so if you don't know how we can do that i can show you just go to even selenium app shell website and if i just come down to this page you will get browsers list and here psyllium will show you all the water all browsers which is supported and if i just go to chrome click on documentation here and they will provide the links here so can just a download and the latest table values is this one so 87 and before that cross check your browser version just go to help above the google chrome and you can check the version of the google chrome accordingly you can download the driver you can just click on the zip file and it will download the zip file and once you extracted this you can find chrome executable file so you can see here inside this so i just opening the file and inside this folder you can find chromedriver.exe so this is the driver which is required to work with the chrome browser so i have already downloaded it so i just provided the location here along with the key and then this will launch our browser and after that i'll also put some implicit weight because especially working with multiple pages and we are navigating between the pages so i'll say driver dot manage dot timeouts dot i'll say implicitly wait and i'll say 5 seconds max i can say you can give any time so i can just say 5 seconds i'll say time unit dot second size 8 ok so this is how i can put implicit weight so this implicit applicable for or throughout the script so as soon as the driver is alive right so after that i can also maximize the browser i can say simply driver dot manage dot window dot maximize so this will maximize the page so all the basic stuff i have done in the setup method so i won't execute this before class when i say before class this particular method will execute only once before starting the class all right so then here i need to write exact test method so i can say public void uh say login test this is actual test method i'll implement this later so for now i can just define this with the test annotation now import this so once you created this i'll implement this code later so before that i'll implement a data provider method so this login test will execute multiple times i want execute with multiple sets of data so that particular data will be generated by data provider annotation method so now i'm going to create a new method called data provider method so let me just create data provider method so data provider method how we can create just like normal method i'll say public get data and here we need to write the code so here this particular method can read the data from third party files like excel file or notepad or whatever and we can also hard code the data within this method but you have a two to three inputs you can directly hard code the data in the method but sometimes it is doesn't good like in that case you can just have the data in the excel file and you can read the data here but as a first step we will see how we can hard code the data in this method itself and how this is going to provide the data to the other test method okay once we understood this concept then we will see how we can get the data from excel and we use that excel data into the test case so remember data provider method is always written two-dimensional array okay so two dimensional array so here i'm defining a string type of two dimensionary is a written type so inside this what i will do is i'll create a two-dimensionary uh i'm taking string you can take any type of array depends upon your type of data so i'll say username and password i'm going to take so username password is basically strings so i'm just creating the two dimensionary with the string type so string say login data login data this is my two dimensional array and in which directly i'll assign the data okay so if i just look at my excel file so this is the different combinations of data i have the same data i will directly hard code in my data provider method and username password along with this result value because this is my expected data like if i pass this combination this is a valid data and this is invalid data and if i pass this combination my test should fail when i see when i pass this combination of data it is invalid so it should fail and if my test case should fail means my test is passed so this is a kind of a negative testing when i pass invalid data the application should not login if it is should not login means my test case is passed okay there's a kind of a negative testing so i need to also read this so all the data i'll keep in my two dimensional array so what i can do is i can just put this data here okay so this is my data which i kept in my two dimensional array so later i'll show you how we can get this data into this array from the excel file okay now finally what i will do is i will just return this login data written i say login data okay that's it so this is our data provider method but how test engine will know about this this is the data provider method so we have to add a special type of annotation which is data provider annotation data provider and we should also give some name because sometimes we may have multiple data provider methods in your test case so you can just identify them by using name so can just give some name log name equal to i can say login data some name i have to give and what is the use of this name is for example this data provider method is providing the data to the login test method but how the login test method will know about this data provider method maybe there are multiple data provider methods may have but how this login test method will know about which data provider method is providing the data it doesn't know it right so for that what you can do is we have to refer this name inside the test annotation so i'll say name equal to uh here not name data provider as a data provider equal to what's the name we have given is login data so that i have to refer so this way the login test method will get the data from the data provider method so data provider method will provide the data to the another test method okay now we have done with the data provider method and then i should implement this login test so now let us try to implement so how many parameters we are passing this data provider method for example if i just look at this this is a one two three and four so four inputs are providing so how many inputs we are providing here those many times the actual test method will repeat and execute okay so we have to receive these data in three different parameters so let me just create three different parameters so one is i'll say string user and the second parameter is also string password i'll say pwd and the third parameter is something called expected data you can just put let's say expected okay so how much data we are passing four different combinations of data we are passing through data provider so the login test method will execute those many number of times okay now we'll see whether it is working for now or not i'll say string experience so just i'll just test this with by putting this out i can say user and i'm just concatenating for now just for checking whether this data i'm getting or not okay just printing that okay i just put setup method which will execute first and after that login test method will repeat multiple times by taking this data and after that i'll write one method called i'll say teardown and i'll execute this after class so after class and inside this i will close my driver okay that's it so import this after class so just i have not completed i have not implemented this test as of now just i'm checking whether this data is coming here or not and test method should automatically repeat multiple times depends upon the number of inputs which are passed by or which are provided by data provider method so that is a use of data provider method here so without using any looping statements we can repeat this method test method multiple times with multiple sets of data so first time what happened this particular data will be sent to the login test and will receive it in these three variables and i'm just printing here and the second round which will get the another round of input another second record and which will ascend these variables and again print it similarly rest of the data also we are getting into this method and how many combinations of data we are passing there are four combinations of four inputs we are passing so our test method should execute four different times now let us test this and later i'll implement this actual test let let's run as test ng test so now it is starting as a test engine test case but i it is just launching my browser but i am not currently doing anything on the browser yeah now we can just look at this see this we got all the data from the data provider so we can just look at this first round it is taken this data this is a combination of data and second round is this one third round and fourth round so that means four rounds of execution happen so this particular method is executed four times and by taking different sets of data and till now perfectly fine and you can also see the output here every time it is taking different sets of data all right so now this particular data we are getting here now i have to use the same data to perform the data driven testing okay so now what i will do is instead of printing this i'm just removing this now i will try to automate this test case and this is application url let me launch my application here i'll say driver.get and this is my application url and after that there are some fields by default displaying here populating and then i want to clear them then i can pass email any password so for that what i can do is let us inspect this element and you can find something called id you can just get the id and locate that element and say driver dot find element by dot id and say id and store this element in one variable i'll say web element web element and this is my text box email so i just created one variable because i want to perform multiple actions on this element so i'm just storing that into a variable so let's take this element txt email dot first of all i'm just clearing the data existing data and then i'll pass whatever value i'm getting here i'm just passing that into the variable and similarly you can just get the another element and the id is password let's take that id and here i'll say driver dot find element by dot id you can pass id and get this element into some other variable i'll say web element web element this is also text box as a txt let's say password right so now i want to clear this element first dot clear which will clear the existing data and then i'm passing a value whatever i'm getting as an argument so with this we have pass username and password and then what have to do is we need to click on login button and just click on the login button and go to the grow path or whatever you want and get the xpath for that so how to get the xpath for this you can just right click and get the x path if you don't have any idea or anything you can just use any other locator i'm just getting the x path of this element and here i'll say driver dot find element by dot x path and just copying the x bar is giving absolute x path okay no problem and if you use this one uh select as hub that will give you a good number of locators and what i will do is i can just go to select our hub now you can see which is giving some exposure defects path okay which is little bit shorter so i'm just going to use it all right and then i'm performing click action on this so this is a submit button or you can say login button all right so with this we have done with the flow so once it is click on this login button if i provided valid data here and here then it will successfully login and we'll get the dashboard page and if the data is invalid then still be there in the same login page okay so let us assume it is successfully login and then immediately what i will do is i'll get the this is my expected title after successful login and how to capture the actual login so we can say driver dot uh get title so this will give you the title of the page and i can store that in one more variable create one more variable string this is my actual title all right so now i have actual title and expected title so after clicking on the login button and verify these two titles are equal or not and if both titles are equal then i can say my test is passed okay so this is a normal flow but sometimes what happens is we have some negative kind of testing so for example even though we pass some invalid even though if you pass valley data sometimes it may not be successful that time the title will not be matched and sometimes what happens if i provide some invalid data still it is able to log into application so that is also incorrect so to cover positive as well as negative testing so we have to prepare uh data properly okay so which will cover positive as well as negative testing so if i just look at my excel file i have covered positive and negative test cases all kinds of input so these three are invalid data and the first one is a valid data so what is my expectation here when i pass this valid data my login should be successful and title should match when i pass my invalid data and it should not be log into application that means title should not match that's my expectation right so to understand this better i can write simple uh a flow chart here if i just look at this when i pass valid data if the login is successful there are multiple combinations we have covered guys because most of the people will just focus on only positive testing they just pass some validity data and if it is working fine that's it but we should also carefully check the in with invalid data so that we can say the complete kind of data driven testing okay so when i pass valid data if the login is success that means titles got matched if yes then i can say my test is passed when i pass valid data okay login is not successful if the login success is no then i say fail because when i pass valid data login should be successful when i pass invalid data should not be successful so when it checks when i check the first scenario here valid data when i pass login success we need to verify that means title is matched or not we need to verify if the title is matching then i can say it is passed if the title is not matching then i can say file this is a positive testing now come to the negative scenario when i pass invalid data login is successful when i pass invalid data the login is successful that means title is match then what our test should fail that means what our test case is failed because when i pass invalid data login should not be successful but if it is successful that means test cases got failed when i pass invalid data if the login is not successful no that means my test is passed so positive and negative scenarios both are covered so in that way we need to write a conditions here we should not check only the positive no we need to also verify the negative data so for that what i can do is and if i just look at this data we have also passed the third field which is saying validate or invalid data okay so after getting these two titles we need to write proper conditions okay so the first condition i'm writing with this so first let us verify this particular part so when i pass valid data login is successful then my test is passed if the login is failure then if the login is not successful means my test is failed so this is a positive input now go back and the third field is saying valid data or invalid data right so we have order we already captured that into this third variable inside this method username password along with the third variable i'm make i'm just using this particular variable to check whether it is validated or not valid data so here i'll put one condition if expected value dot equals expected value which is equal suppose i'm valid okay expected is valid so when i'm passing the valid data observe this condition so i'm passing the valid data if it is a valid how we will know that whether it's valid or not i am getting the user password along with this valid or not so right so here data itself i mentioned valid or invalid so based on this field i can check this is valid or not as per my expectation so if the expected dot equal to valid so if it is passing this data user and password and if it is the value is valid exp value is valid then what i have to check the login is successful or not i need to check so there are two conditions we need to write so first condition is valid data or not if the data is a valid then i need to again check two conditions login is successful or login is not successful if the login is successful then only i can say my test is pass if the login is not successful then i can say my test is file so i need to verify this condition now so wait data is a valid then one more condition is if expected title expected title dot equals to actual title okay and then what i have to do now so both the conditions are verified that means what valid data or not if it is a valid then login success or not if the login is successful then what i have today i have to say past so that's the one combination so first condition is valid title is also matching so here i can say my test is passed so immediately i can write one assertion so asset dot asset true yes i say true i can pass through so this we have to import from test ng so not from junit so assertion you have to import from test ng and if login is successful right so if the valid date i am passing title is also matching means login is successful and after login is successful then i have to come back and take another input right so after successful login what i have to do so after successful login title will be match so the title of the dashboard page will be matched so after matching it what i have to do again i have to go back to the login page to get another input so i have to click on this logout link right so this is just a link text so if these conditions are crossed means i am able to log into application so before doing assertion itself i say driver dot find element by dot link text and here i'll say log out so log out is my link text and say click so with this condition what we have achieved is this part we have achieved we have validate or not if it is a valid data login is successful yes then passed so this part we have achieved so valid or not if it is a valid then titles are matching then assertion is true test is true and click on the logout and the second can be suppose valid data login is not successful then what i have to do my test is fail so if it is a valid but the titles are not matching here then i will put one else condition else immediately i will fail my test case asset dot asset true off i'll say pass false so this will make my test method failure so with this we have achieved the positive testing so this part we have done this part we have done the upper portion okay now so we need to verify the negative testing now when i pass invalid data if the login is successful yes then my test is failed when i pass invalid data my login is not successful then my test case is passed right if i pass invalid data logging should not be successful if login is not successful means my test is passed because i am passing invalid data so negative testing so this part we have to cover now so now continue with this so if data is evaluate we have verified two conditions here else else if expected dot equals to invalid suppose i am passing invalid okay if this condition is true it will verify these two if this condition is false what then i'm verifying one more condition expected dot equals invalid then i need to verify two more conditions so if i pass invalid data login is successful means it is failure test case and if i pass invalid data login is restricted means login is not successful means my test is passed so two conditions i need to verify now the first condition invalid data so if expected equal to invalid then login success or not we need to verify how we will verify we need to again check the title so if expected title dot equals to actual title what does it mean even though if you pass invalid data titles are matching means what my test is failed so immediately i will fail my test immediately film him because here i'm passing invalid data and titles are matching means successfully logged in it should not happen like this in that case we should make it is fail because it is already logged in i need to come back to the home page to get another login so what i have to do we need to also click on the logout link here so this will come out from the login page come out to the login page and then it will fail the combination so with this we have achieved the first combination we pass invalid data is true login is successful yes then failure so data is invalid titles are matching means login is successful i say fail the first condition matching so when i pass invalid data titles are not matching means not login that means what our test is passed so that the second combination when i pass invalid data login is not successful no login success no then my test is passed because there's an invalid data so here i'll write one else condition else else simply i can write s dot as a true of true this is a negative condition okay so this is how we can validate the login is successful or not with valid data as well as invalid data so in the data the third field will be very very useful so this will says like this data is valid or not so before writing the test case itself we have to prepare this test data all right so after completion of whole thing so with this we have done with the test case so till here this is all about uh login test still here so this login test method will execute multiple times by taking these three inputs from the data provider method so data provider method will return this two-dimensional array suppose if you have multiple type of data like currently i have all of strings then i define this array as a string type and suppose if you have combination of data then you can define this as an object array so i can just make it as object array so which will hold all kinds of data so currently i have only string type of data so i can just look at this string type of data all right so this is how we need to implement data driven test case by using data provider method so this data provider method will provide the data to this login test and which will repeat multiple times depends upon the number of inputs we are passing through data provider method okay so here i haven't used excel file i just taken the data from the data provider i hardcoded some data okay all right so let's try to execute and see how it is going to work so right click run as test ng test all right so now it is launched my webpage is my first input so successfully log into application then it should click on the log out yes now it is taking another inputs because they are invalid not able to log into application yes now we can just look at this or what is saying here all of them all of them are got passed so this is our expected so why all of them are got passed means when i pass this valid data my login is successful that means it is passed when i pass invalid data my login is not successful that means what our test is passed that's our expectation when i pass invalid data login should not be successful right so that's our expectation so it is not login that means indirectly my test cases got passed similarly rest of three inputs i pass invalid data so login is not successful so finally my test case got passed so in all the combinations my test case got passed because these three are negative inputs when i pass invalid data login should not happen if it is logging happen means what our test is fail if the login is not happen means what my test is passed okay so this is how we need to perform the positive as well as negative testing so till this what we have achieved is we just created one testing test case in which we have used data provider method which will provide the data to the another test method and which is executed multiple times depends upon number of inputs we have passed through data provider method okay now i'll just move on to the next step so currently we have put the data in the data provider method directly here now i want to take this data from the excel file i want to read this data from the excel file then how we can do that so whenever you do data driven testing using excel file most of the times we will create a utility file so in the utility file in the sense we will write a common methods like reading the data from excel counting the number of rows and counting the number of cells or writing the data into the excel cells to perform these common operations we will most of the times we will write a utility file okay so to perform those operations we have to create one utility file so i'll tell you why exactly utility file is required because uh which will simplify our script and also it will reduce a lot of effort for us so let me just tell you why exactly that is needed why exactly we need a utility file to work with the excel file okay that's most important to understand before uh directly jumping into the code so let me just give you a small scenario here let's say i have 5 to 10 data driven test cases let's say i have test case 1 here and test case 2 test case 3 and test case 4. so let's say i have multiple data driven test cases normally in the data driven test case what you will do is when i take one test case what this test case contains there are two kinds of scripts i can write one is some piece of code will be interacting with the web ui or application like finding elements performing actions all those things some other piece of code will work with the excel file right opening the excel file or opening the excel file into stream mode or writing excel or reading the data counting rows counting cells so for to do all these things we have to write some piece of code so excel related operations to perform excel related operations we will write some piece of code so if i include everything in one test case your test case will become little complex so we need to write a code for interacting with the application at the same time you should also write some piece of code which will work with the excel file which will operate your excel file so same thing you will have for every test case because all our data driven test cases you will have the same thing so in this problem in this particular approach the problem is normally what are the excel operations you will do is normally what you would do excel operations you will just read the number of rows or you will count the number of rows count the number of cells and reading the data from the excel cell and writing the data so these are the only common operations most of the times we do when you perform the data driven testing so these operations are common in every test case so that what you will do is we will create a separate utility file which contains reusable methods so for every operation we will have a separate methods this is just like a java class in this class we will write a few number of reusable methods for each operation like counting the number of rows counting the number of cells getting the data or writing the data for each operation whatever you want to do we will create some reusable methods in this utility class and now in every test case we will have the code which will just interactive with the ui and whatever excel operations we want to do we have to just call the method from the utility class in every test case we just call the method from the utility class so by this approach we can we can achieve one thing is the same code we are reusing multiple times so reusability we can achieve the second thing is uh the second thing is it will reduce the complexity of your test case if i include both the code in every test case the code will be increased and it will be little become very complex so to avoid the duplication like same number of operations we are doing in every test case so instead of writing the code multiple times you can just write user defined methods in the utility file we can just call those methods in every test case and then we can achieve the data-driven testing so this is the approach most of the times we follow when you automating the data driven test cases now we'll see how we can achieve this so now i'm going to show you first step how we can create a utility file so in the previous videos i have already shown you how we can perform different type of operations on excel file and even in the ud file also we are going to do the same thing but here we are going to write different type of methods now go to our eclipse and let it be this test case like this later i will come and uh update this so currently we hard coded the data here we have to read this data from the excel file okay so before that i'm just going to introduce one utd file now here i'm going to create and which is a new utility file and which will perform excel operations okay so i will name it as excel utility this is the name of the class i'm giving and just say finish and this particular class is just a unity file which contains a reusable methods and we have to just call those methods whenever you want so let me just have that file so i already have this file let me just copy this and i'm just going through this each and every step in this file it is very easy to understand if you watch my previous videos of excel series and it is very easy to understand so excel utility so here i have defined some common variables which he required so most of the times we use file input stream file output stream access is a workbook excess of sheet row cell everything right so i define all the required variables in one list as a beginning itself then i created one constructor here okay so what this constructor will do is whenever i created an object for this class we have to pass the location of the excel file so this constructor will take the path of the excel and assign the path to the local variable which is path so this is a class variable so with this constructor we will initiate the location of the excel file into this variable now there is one more method called get row count so these are all user defined methods we have just created our own methods get row count so what this method will do is which will take a sheet name as an argument and finally it will return the row count how many rows we have in this sheet will be counted and written that row count so how it will do it again we already open the path right we have already assigned the path so that file will be open in the file input stream from that file get the workbook from that workbook get the sheet from that sheet it will find out last row number that is a row count and we are closing the workbook closing the file input stream and finally we are returning the row count simple method okay so when i call this method by passing the sheet name which will return the row count this is one method and the next method we have is get the cell count so when i call this method which will return a cell count but what are the arguments it will take sheet name and row number we have to pass so when i pass a sheet name and row number which will get you number of cells present in this particular row number inside this particular sheet so which will open the file into stream so this path we already initiated through constructor so we no need to pass the location of excel in every method so it will open the file in input stream mode then which will get the workbook here and get the sheet here and get that particular row so we have already passed row number and which will get the row number and from that row it will get the last cell number that is exactly number of cells present in that particular row and finally which will return cell count and before that workbook and file input stream will be closed so this butler method when i call i have to pass sheet name and row number which will return cell count this is one more user defined method and then we have one more method called get cell data so most of the times we use this method get cell data especially when you read the data from excel so this particular method will return the data from the cell and what are the argument which will take sheet name row number column number or cell number so when i pass these three parameters which will return the data which is present in this particular cell so how it is going to return first of all it will open the file in the input stream mode then get the workbook here get the sheet and here it will use the sheet name which we have passed and it will open the sheet from that sheet get the row by passing the row number here from that row it will get the cell or column based on the column number or cell number we passed from that cell we have to extract the data so normally cell contains a different type of data sometimes you may have numeric data sometimes we have like a formula cells sometimes you have a string type of data or d type of data you can find different type of data in the excel cells but while reading the data we will read everything into one string format common format because anyway in our application we are going to pass only string data so that's the reason whatever data we have in excel sheet we can read all the data in the same format which is string format so to do that we have a special class available in apache poi that is data formatter so for data formatter class we have to create one object of formatter equal to new data formatter and inside the formatter method there is a format cell value method and what this method will do is this particular method will return the data from the cell in the string format so this is the description of this method so return the formatted value of the cell as a string regardless of the cell type so cell type can be anything it can be numeric or string boolean formula whatever finally we will get the data in the form of string so i just created one variable called data and from the formatter object format cell value will get the data from the cell and store that into data variable suppose if the cell is empty cell doesn't have data in that case this statement will throw some exception and catch method will handle that exception and internally what i am doing is i am ascending some empty value double double quotations if the data is available in the cells the data will be read and stored into this variable if the data is not available in the cell then this will throw some exception in the catch block i'm adding some empty value and if the data properly ascend the cell is contains the data so catch block will not execute so finally this data will be written okay so here i have handled the empty cell data so if the data is doesn't have sls doesn't have any data and i make it as empty and then i'm returning it so this is a method which will get the cell data if i pass sheet name row number and column number which will return cell data so this is the one more method and uh cell cell data so this is another method i have so in this method what we are going to do is we are writing some value into the cell okay sometimes if you may you may write results back into excel file in that case we can use it set cell data so this particular method will take the sheet name row number column number or cell number and data which data we want to update so it will take four parameters then what this will do is set cell data so if you want to set the data in the cell first of all we need to get the file get the workbook get the sheet get the row here by using this row number and from that row get the cell by using this column number or cell number and inside the cell we have to set the value so take the cell object here set cell value and i'm passing the data whatever data i'm getting here i'm passing the data but once you set the data we have to update this in the excel file right so we need to open the file in the output stream mode so again i am opening the file in the output stream mode then i am writing the workbook into the right file then i am closing the workbook closing the file input stream and output so this particular piece of code will set the data into the cell based upon the argument we passed and the next method we have fill green color sometimes if you want to apply any color on the cells like foreground color we can call this method which will take the sheet name row number and cell number or column number when i pass these three arguments which will apply the color on that particular cell and what this will do basically is you will open the file in input mode it will get the workbook here and which will get the sheet here based on the sheet name and get that particular row based on the row number get the cell based on the column number of cell number and inside the cell i want to apply the foreground color so to apply the foreground color we have a method called workbook dot create a cell style workbook dot create a cell style so this will return cell style object so this style variable we already created at the beginning if i just look at here i already created cell style variable and that variable i'm using it here workbook dot create cell style which will return the cell style store that into this variable now after that by using this style object i can set fill back fill foreground color indexed colors dot green get index so here finally we have to pass the index of the color so how can get the index of the color is indexed colors dot green whichever color you want to apply you can just take the color dot get index so this will get the index number accordingly it will apply the foreground color and how we can make this foreground color solid foreground color so style dot set fill pattern fill pattern type equal to solid foregone color so this will basically apply the foreground color on that particular cell and then the cell style i need to apply to the cell so whatever cell i got it here that cell dot set cell style and whatever style i have applied here the style i have to apply in the cell so whatever style i have configured here that style we have to apply on that particular cell so for that i use set cell style method so after that closing the workbook closing the writing the workbook into the file closing the workbook closing the file into stream closing the file output stream so this is a code which you have written for filling the green color same thing for filling the gray color sometimes if you want to update failure messages in the excel you can apply red color on that cell so the same method again only thing is instead of changing this green i just changed into red and rest of the code is exactly the same okay so these are user defined methods we have created in the utility file and this will very very helpful when you perform the data driven testing now let us use this unity file to get the data from excel file or excel sheet and we use the data as part of data driven testing okay now let us move on to the next step so earlier i have already shown you here called data driven test case we created setup method login test and data provider method also we created and this data provider is hard coded this data and depends upon the number of inputs our test login test is executed multiple times right so we have already seen this now i'm just going next level that is instead of hard coding this data i want to read this data from the excel file okay i want to read the data from the excel file so i already have excel file with the same data username and password and result or expected data valid or invariant so i want to use this data to perform the data-driven testing okay so now what i will do is i'll just copy the same file in my project folder so that whatever the files which are related to project should be part of your project itself so i already have data files folder inside the data files i'll get my login data dot xls now let me open this file okay so now i open this file so here i am not able to see let me just open as a system editor open with system editor okay so now this is my excel file so which contains a number of rows and if you don't have if you have any unnecessary rows and columns you can just remove like this so i have your username password and valid or invalid flag so now i am going to read this data and check my test case so close this file for now all right so i just comment this one till here right so now i will get the data from the excel get the data from excel all right so to get the data from the excel right so what we have to do is we should again maintain the two dimensional array because finally we should have the data in the two dimensional array itself so we need two dimensional array okay so let's see how we can do this so let me just create a string two dimensional array before that so once you created your string with two dimensional array and which is also having same number of rows and columns in excel file how many rows and columns we have in the same size we have to create this two dimensionally so i'll create this later so first of all i will define the location of my excel i'll say string path where is excel file my excel file is present under data files folder so let me just locate that so slash slash dot slash slash which is representing or correct project directory inside this my file name is my folder name is data files and inside this my file name is logindata.xlsx all right so this is my path so now once we get the path the first thing what you have to do is this time we are going to use utd file okay so this time we are going to use utility file so to access the methods from this utd file how we can access them we have to create an object of this class utility class and then we can access all these methods so let me just do that so here here i'm just going to create excel utility okay excel utility i'll name it as excel utils excel util some variable name excel until equal to new excel equity utility and when i create an object for this excel utrid right excel utility this is expecting some parameter here default constructor so it's not default constructor it's taking some argument so what is an argument it is expecting is part of the excel file so now i'm passing this path into this constructor so now i created excel util object and through this object i can access all the methods which are created inside my utility class all right so let us do one by one so before reading the data from excel i need to know how many rows and how many columns we have right for that what you have to do is take this excel util object dot just call the method get number of row get row count so the first method row count when i call this method which is expecting the name of the sheet so i'm passing the name of the sheet then what this will return this will return number of rows present in excel file now let me store that in some variable inter i say total rows total rows similarly i want to get the number of cells in that particular excel or number of columns so which method how to call get cell count from the utd file so excel it is or get cell count and how many parameters it required and which is expecting two parameters sheet name and row number so sheet name is a sheet one and i'll say row number let's say pass one so this will return number of rows so i'll say total columns okay now i got the rows and columns directly so i just call the method from the udd file and which is written get row count and get cell count now with the same size we have to create a two dimensional array because as soon as we read the data from the excel we have to store that data in the two dimensional array and finally we have to return the two dimensional array to the test method okay so now i am going to create a two dimension array with the same name login data two dimensionally equal to new string of and how much size it should be equal size total rows and total columns equal okay equal size like how much data we have how many rows and columns we have in excel the same number of rows and columns we should have in our two dimensional array so i have just created and this is a string type of array as a string so this statement will throw some exceptions just add this okay done so now we got the rows and columns from the excel file and also we created one two dimensionally with the same size so what is the next step we have to do is we have to just read the data from excel and keep the data in the two dimensional array okay so it will be like this guy suppose i have an excel file like this which contains a number of rows and columns and with the same size we created a two dimensional array with the same size we created a two dimensionary and if i just look at this the in excel file the first part is a header part so i just ignore it so from the first row i will start and here index will start from 0 in error 0 1 2 like this but here this is a 1 0 i have eliminated this is one this is two and this is zero one two like this so whatever data we have here i want to store this in the zero zero position and again one and one data i want to store in zero one position similarly by using a loop statement i have to get each and every cell into two dimensional array like this okay because having the same size so let's do how we can do that let's see that so let me just clear this now i am writing uh two different loop statements are required we need two different looping statements now the first loop is representing the rows and the other loop is representing the cells in each row okay now let me write a loop statement here so i will say for loop inside this i am writing one more value now in outer for loop i will start from first row because if i just look at our excel file the first part is a header part right so in excel file the first part is header part we don't need that if i just look at this excel file once again and the first part is header part i don't need from the second row i need second row in the sense index will start from zero so we need to start from one so here in ti equal to one i say i less than or equal to how many times we have to repeat depends upon the total number of rows then i plus plus every time i'll increase the row by one and after that come to inner for loop and this is representing the number of cells so i'll start from enter i'll say j so into j i'll start from 0 itself because we don't need any header nothing so we have to start from 0 because of we are starting from 0 here we have to use only less than total number of columns and then j plus plus so this will increment the cell every time and then here we got that we'll get the data from excel and store the same data into the two-dimensional array so by the side-by-side we have to do now how we can get the data from excel so again we have to use utility.java file if i just go to this utility file we have a method called get cell data so when i pass sheet name row number column number which will give you the data from the excel so that particular method i'm just going to call so here how we can invoke this by using excel util object so excel util dot get cell data get cell data and we have to pass three arguments sheet name what is the sheet name say sheet1 what is the row number here i representing the row number so i'm passing i j representing the column number i'll pass j here fine then as soon as i get the data from the cell where exactly i want to store i want to store in two dimensional array so here i'll say login data this is my two dimensional login data and in which position you have to say so here i and j because we have just ignored the first row in excel file because it contains the header part right so but when storing the data in the login data always we need to do minus one because currently if i just look at this we have ignored the header part so this is one and this is a zero but when you store this data in the array we are going to store in the zero zero position okay so for that what you have to do here i is equal to one and here we have to do minus one then it becomes zero so in the first array we say i minus one and here in the zero position here also it is a zero position so j value as it is we can pass so i minus 1 always it will take and j value will take so here i'll say i minus 1 and here i'll say j because in the excel file we have just ignored the header part so that's the reason we need to do i minus 1 here because here we are starting from 1 which will store in the zero row in the array this is the login data so once this loop is completed so first time row is 1 here and the column is 0. now from the first row and 0 column it will get the value and it will store in the two dimensionary that is 0 and 0 column second round the for loop inner follow will repeat multiple times till it completes all the cells from that particular row once it is completed then outer follow will increment the row and again come inside and read all the cells from that particular row and like that it will repeat multiple times inner for loop and outer for loop which will read all the data from excel and keep the data in the login data which is two dimensional array so by the time this loop is completed the login data is having all the data from excel and once we get the data into the login data array i finally am returning this array because this is a data provider method because this is a data provider method we no need to repeat this we just written the login data which is two dimensional array and our test will receive the data in three parameters then we'll use the data in our test case okay so this is how we can directly write the piece of code in the data provider method which will read the data from excel and pass the same data to the test method so instead of hard coding this data we can get the data from excel and main thing is we have used utility file so how we have created utd file is very very important and the same utility file you can use in multiple data driven test cases okay so that is how we need to do that let me save the file then execute our test case now before that i'm just closing my excel and validations and all we already seen in the first round itself i have shown you how we can validate if the data is a valid title is equal then my test will pass and data is a valid but my title is not matching me is not successful means my test is failed one validation the second validation if data is invalid and still title is matching means what test is failed but data is invalid but still login is successful means so data invalid data is invalid and login is successful log is not successful means my test is passed so this is a positive and negative testing both have covered and when you perform data driven testing especially this is most important okay so we need to cover both positive as well as negative inputs all right so now let us execute the test case and which will read the data from excel now because i have commented this code okay let's try to execute as a test ng test all right now it is started and it is launching my browser yes now it is taking the first input which is a valid data so which is login to application yes now again it is click on the logout now it is taking another inputs basically they are not successful login because i provided invalid data so when i pass invalidator login should not be successful then my test is passed indirectly right the same thing is happen so all the combinations are passed and tested and all the combinations my test is passed if it is not login in the sense my test is passed because i pass the invalid data so when i just check the condition same thing when i pass valid data right login is successful then my test is passed when i pass valid data login is not successful then my test is failed similarly when i pass invalid data login successful overall my test is fail when i pass invalid data login is login is successful login is not successful then my test is passed so valid as well as invalid case which is very very important okay so that's all how we can do data driven testing by using test ng data provider along with excel file and we use apache poi methods and most important thing is utd file so this utd file will help you a lot while performing the data driven test cases okay so that's all for this video guys thanks for watching

Channel: SDET- QA Automation Techie

Views: 22,304

Keywords: Python, Selenium with Python, Selenium webdriver, Automation, Testing tools, Selenium tutorials, Python tutorials, Manual testing, SDET, Java, selenium with java, big data, hadoop, web services, postman, soapui, rest assured, big data testing, hadoop testing, ETL testing, SQL, Programming, Software Testing material, selenium videos, python videos, automation vidios, jira, jmeter, performance testing, functional testing, QA, QC, Agile testing, SDLC, Automation Step by Step, restapi

Id: 1nP9UlwzpgU

Length: 68min 1sec (4081 seconds)

Published: Sat Nov 21 2020