Hello and welcome to the session I am Raghav
and today we are going to learn how to get data from Excel using test Angie and how to
make our test data driven So we are going to go very basic step by step and we will
learn how to get data from Excel file how to create Excel data provider function How
do we use test NG at data provider and notation? We will also learn how do we refer our test
data from the data provider And finally we will see how How can we make our selenium
test data ribbon using test NG and during the session I will also share some useful
tips with you So let's get started and the prerequisite for this session is you must
already have the Excel libraries added and we have used Apache POI and you should already
have created the functions to read data from Excel Now, this is something we have already
learnt in our last session and I will also provide the link for that video in the description
on Section of this video so you can watch that in case you have not already watched
that in that session we learned from scratch How do we add a Apache POI libraries? And
how do we create Excel reading functions? So that will be prerequisite and once you
have watched that let us see the first thing we have to do is how to get test data from
Excel So basic four things we have to do here is we should have a function to get row count
a function to get column count from Excel a function to get the string data and a In
to get the numerical cell data, so let me just take you to eclipse and this is the framework
that we have been creating and also in case you have not watched the earlier sessions
you can still continue with this session in case you already have some basic knowledge
You can continue here So in the last session, we already created this functions in the utilities
So if I go to my SRC test Java we have the Utils package and here we created Excel utils
class And in this class, we created a function to get row count We also created a function
to get cell data string We also created a function to get cell data number I think we
did not create a function for get column count So I will just copy this and just paste it
here and I will say this is get column count and here I will have to use the function sheet
dot get Rows so you can get the first row with the zero index and then you can say Get
Physical number of cells and I will say this is column count and this is the function to
get the column count. Now this we have already done in the last session So I am not going
(in) too much details here So this you can alte- also see if you have not seen earlier
You can see the last session and now we will start with how to create an Excel data provider
function So here we have basic four steps very simple steps The first one is we have
to create a new class called Excel data provider and you can name it anything so I will again
go to my utils folder utils package do a right click new class and here I will name it Excel
data provider You can give any name here and I will say finish So this is the new class
we have created and the second step is we have to create a function and create object
for Excel utils class We have we have added above functions for reading Excel file So
here I will say I will create a function I will say public void Let us say test data
and here inside the function I will create an object for Excel utils class So I will
say Excel utils I can give any variable named x l equals new Excel utils And you see it
is asking us for our Excel path and the sheet name because in our Excel utils we had created
a Constructor that takes Excel path and Sheet name to string parameters So what I will do
is instead of passing it or instead of defining these two variables here I will take this
as input to this particular function, so I will say get string Excel path and get string
Sheet name so this should be sheet name here And I think here I will have to I will save
this as sheet name. Yeah Okay So a very simple function where I'm creating an object for
the Excel utils class and now then we will get the row count and column count using the
Excel functions So now using this reference to the class, which is Excel I can get I can
call the function So I will say Excel dot get row count And then excel dot get column
count And what I will do is I will store them into integer values I will say int row count
equals this and nth column count equals this so whatever be the number of rows that will
be stored here The number of columns will be stored in column count now here I am getting
error And here it is saying change writte type of get row count int So now because we
are storing this the whatever is the output from this function into a variable integer
variable So we have to give the return type here as integer So I will go back to my Excel
utils and wherever I have these functions, so let me go so here I will say return type
instead of void is int and I will declare int row count Outside the try block so that I can
return it and I will say this is zero and inside the function inside a try block It
is getting the Row count from this function and at the end I will say has returned row
count same thing I will do with get column count function This int column count I will
declare above try block and I will initialize it to zero and inside try block It will get
its value and at the int after the catch block, I will return column count And the return
type instead of void, I will say int and save and now if I come back to here this class
you can see there are no errors now Okay So now we have got the row count and column count
and now we have to run a loop to get all the data into an object array. So let us see what
to do Now I have got this do count and column count What I will do is I will start a loop
and I will say for Loop int I equals 1 so I have to start with the number of rows and
I'm starting with one and not 0 why because let me just show you if I go to my Excel and
these are the excels We already created in our earlier session Let me just open one of
these Excel So you can see here We have two variables username and password and this is
the value here So here the first row is a header row So I have to skip this row and
I have to take I have to start getting data from the second row, which is actually row
index number one So it starts with a zero index So this is room number 0 rule number
one rule number 2 and so on similarly column number zero column number one column number
2 and so on so in case of column It is Ok We will start with zero and then one but in
case of row because the first way is header row we have to start with one So therefore
I will say int equals 1 and I i have to go until so I less than row count I will keep
this here row count and again semicolon I plus plus so this Loop will run starting with
the row index 1 and until the The number of rows we have so as of now if you look at our
Excel as of now, it has only just one row of data So this will run only once and then
inside this for Loop We have to create another for Loop So here I am doing nesting of for
loops and here we have to run a for Loop equal to the number of columns So here I will initialize
another variable J equals 0 now in case of column we can start with zero and then J is
less than this column count and J plus plus okay, and again curly braces start and stop
and now inside this for loop I can get the data So I will use Excel.get data string This
is the function we had created earlier and row number will be I column number will be
J So this will get the data So for the first time it will run it will get the data from
row number 1 and column a number 0 so that will so that will be number one and column
number 0 is this so the first time we should get admin second time rule number one and
column number one, that will be this value Okay So let us also print it or I will first
save it in a string I will say string cell data equals this and I am getting an error
here because I don't have a return type I think they're let me just check Yeah, so in
my method which is get cell data string, I will again convert the return type instead
of To sting and here I will say string Cell data equals null and here I am getting the
value and at the end after catch block, I will return.cell data Okay. So now if I go
back there are no errors and now I can print this out.I will say as far as so control space
bar to autocomplete and I will say this is cell data Okay.Now let us try to run this
and to run this for now.I will have to add a main method in this class.So I will add
a main method control space bar to autocomplete and here I also have to make this method as
static so that it can be called from main which is a static method and I will say test
data and here I have to provide the excel in sheet name So for now, what I will do is
I will just for a temporary basis. I will say I will provide Excel path and this is
the location. I will right click on my Excel go to properties and copy this location And
here I will give this here to excel path So this is the location of Excel and Sheet name.
So if I go to my Excel the sheet name is sheet one I have not renamed it So let me just use
this I will say it is Sheet1 and in the early session, we have already seen how to get the
relative path within the project so that you know but for now I am just doing it for a
temporary purpose to run this program and check so here now.Let me just try to run this.I
will right-click run as a Java application.And yes, you can see we are getting number of
rows to number of rows to admin and this is getting printed two times because we are also
I think it inside the actual function so you can see here.We are using the sys-out statements
here.So let me just comment out the seeds out statements from the functions calling
this functions.So this is get column count This is Yeah I think this is fine.The only
thing is I forgot to make it as columns number of columns now, this is fine.So you can see
we are getting the data here.And also what I can do here is in the sys out I can say
cell data and I can say plus here and I will just do this and instead of system.out.printetln
I will say system dot out dot print Ellen is for new line And after this loop I will
say system.out.println so that after one Loop it will go to the next line and if I run this
now again, you can see the So you can see number of rows are to number of columns are
too and it is now printing our data here Okay, so our program is running fine.And also we
have to now get the data into an object array So let us Define an object array here so I
will say object and I will say let us say this is data and I will create two dimensional
object So this means this is a two-dimensional object And actually we can also create a Two
dimensional array of string type of any other type but because we are not sure that the
data into our Excel can be of string can be data of numbers or can be anything so it is
safe to declare a object array, which is a two dimensional array and I will say equals
new Object and here I will give the size So the size should be equal to row count and
column count so it should be like this So this will be a two dimensional array where
the size the row will be equal to the row count here and the column will be equal to
the column count here But as we have seen the first row, we are taking as header so
I have to decrease one from here So here rowcount I will share account minus 1 so you can understand
right if I have ten rows of data The total number of rows will be 11 because we have
the first row as header So that is why I am subtracting one from the row count and column
count is fine Okay So now we have created this object and inside this loop I will keep
on adding the data into our object array so I will say data Here, I will say I and J equals
to cell data And also here I will have to say I minus 1 because our I starts with one
but the index in our array starts with 0 so when it is 1 it should actually be 0 when
it is 2 it should actually be 1 so that is why I am saying I minus 1 and J is fine and
I am keeping on adding the data into this array and at the end of this function, I will
return this Object array, so I will say return data and it will create an error because our
return type is void So again here I will say return type is object array, which is a two
dimensional object array Okay So now we are done with all our four steps.We have created
an Excel data provider function.Let us now see how to use test NG data provider and here
we will again use Simple three steps.The first step is we have to create a function and provide
the testNG Annotation data provider So here I will go up and create a new function here
I will say public static I will just say get data and here I will use The annotation data
Provider from testNG So that AP and I will press control space bar and I should take
this from org test NG dot annotations and in the brackets, I will say name equals to
you can give any name.So for example, this is the data for test one.I will just use test
one data.Okay, and here this is the data and so this should be some return type here void
And yes So I have created a function in provided testNG annotation and then here inside the
function I have to call the Excel data provider function with Excel and Sheet name So now
from here I will provide I will call this test data function so I will say Test data
control spacebar to autocomplete and now from the main method I will take this here Okay,
and Sheet name is Sheet1 and actually, I no longer need the main method now so I can remove
this and also now even if I do not make these methods as static that will work I made it
static so that I can use the main method so here I'm calling the test data function, which
is this one I am providing the Excel path, which is the path to this Excel file And then
I am providing the sheet name as sheet 1 Okay So this will help you because you can have
multiple sheets Different test cases So now I am just using for test one which is sheet1
And again, I will take this into an object I will say object data equals this and I will
return this Data object So here again return type I have to make for this function as well
as object array So here you can see simply we have created a function provided it as
an annotation for testNG which is a data provider given it some name and then calling this function
and then whatever data I have got from this function in the object array. I will return
that data here Okay So these are the simple steps I have done and now We have already
written the data object So we have now completed this section how to use testNG at data provider
And now we have to do the main part which is how to refer test data from data provider
and here the first step is we have to create a function and provide the testNG annotation
at test and give it the data provider So now here I will create another function here So
I will say public void let me say this is test one And here I will say this is I will
give The TestNG annotation test So make sure this is from org testNG annotations, and I
will also give a parameter here So I will say Data provider equals test data or whatever
is the data provider name here So you have to say data provider Equals, so make sure
that you give the same name whatever you have given to your data provider function I will
copy this from here and paste it here Now We have done the first step The second step
is in the function arguments get input parameters equal to the parameters in your Excel file
So here in the arguments or input parameters You will declare arguments equivalent to the
number of arguments or number of parameters you have in your Excel data So here I have
to so I will say string Both are string type So I will sustain username and I will say
string password Okay, and now you actually do not have to do anything now because now
all the things will be taken care by test NG and our functions that we have already
created So if I inside this function if I just give us a shout statement and say username
and print username here and let me also print the password Here separating it with a pipe
symbol and Password and I will just run this I will say I will right click run as test
Angie test and let us see what happens So everything is passed If I go to the console,
do you see this It is now printing this admin admin one two, three And then do you also
see this past test one admin Admin 1 2 3 and this is coming from testNG This is something
we have not printed on the console So now our test will run with Equivalent to the number
of times that we have rows in our test data or our Excel file So let me just show you
again Also, let me just go inside this and comment out the system.out.print statement
so that now we have only a single statement here Okay, so now you can see if I just add
more data here I will not have to do anything in my code I will add one more data here admin2
and admin 1234 and save it and let me run this again run as test NG test And yes, do
you see now so it is now running the test with two sets of data This was the first set
admin admin 1 2 3 and this was the second set So now you can see it is very easy You
can keep on adding data to your Excel files, whatever updates addition deletion you have
to do to your data you can do here and here you don't have to worry everything will be
referred from your data and you can make your test data driven. So third step use values
in your test which we have Redone and now we will see a sample data driven selenium
tests using this approach So I will use the same function which is this test one and here
I will just create some selenium code So what I will do is I will get some data or some
before test method from an earlier Class that we have created earlier, so I will go to testNG
demo and here we have this the four test method where we are initializing the browser and
setting it to a particular browser I will copy this go to my Excel data provider and
just paste it here and here we actually do not need this This was a example I showed
earlier using properties file and log4j and here I have to declare Webdriver at the class
level so I will say Webdriver driver equals null here and then here it should be deferred
from there And let me import Webdriver from selenium So yeah, this should be fine so we
are getting a driver instance here And now let me just create a simple test I will go
to let me go to this orange HRM website So I will copy this URL and I will say driver
Get to this URL and then in this I will add a username So I will inspect this element
and let us see what values we have So we have an ID here So let us find this with ID I will
say driver. find element by.ID and give the ID here and here I will say Do not send keys
and I will use our username from our Excel file here and same thing We will do with the
password So here this is the password box I will right click inspect and the id here
is txt password So let me copy this again and here I will say driver.find element.ID
again and ID is this and I will say .Send keys and I will use our password And also
I will just wait for some time I will use thread dot sleep so that it is visible what
it is doing Red Dot sleep for let us say 2 seconds and it will ask us to enclose with
try catch or add throws I will just add throws here So I will just say throws exception So
here you can see a very simple test and G test where we are going to this URL and adding
user name and password and now this Come from our Excel file And also let us do one thing
I will add one more data set here Admin 3 and admin 12345 and save this and let us now
see what happens I will say right click run as testNG test And let us see it goes to a
Chrome browser goes to our URL and this is the first set admin This is the second set
and this is the third set so you can see it is now running equivalent to the number of
data sets we have provided Okay, so this is how you can make your selenium tests as data-driven.
So let us do a very quick recap today, we learned how to get test data from Excel how
to create Excel data provider functions How do we use test NG at data provider annotation
How do we refer test data from data provider And how do we run a simple data-driven test
in selenium I hope this session was very useful for you I will also recommend that you do
some practice with some different examples and some selenium tests and I will meet you
in the next episode Thank you for watching.
