so in today's session uh we'll see how we can do data driven testing using Excel file so but before uh discussing data driven testing uh we have to know how we can work with the Excel sheet like how we can read data from Excel sheet and if you want to write a new data in the Excel sheet how we can write data in Excel sheet so these are the things we have to know first and then we'll see data R testing so data R testing is basically uh we will specify or we will prepare test data in Excel files and uh we read the data from Excel and we use it in our automation testing we will pass the same data to the application and we will do testing so that is basically called as data driven testing the testing is driven by the data so before that we need to know how to work with the Excel files Microsoft Excel files so by default selinum is doesn't support any Excel files okay selinum is only meant for web automation means what it will interact with the different type of web elements on the web page and it will perform certain actions and we can locate an elements we can perform actions so that's the only part web driver will take care but other than this selenium doesn't support anything else so to support the things so what we can do is we can integrate some third party apis or some third party libraries we have to integrate with the selenium and then we will able to achieve it so Excel file or Excel Microsoft Excel doesn't support by selenium web driver by default so we need to use a third party Library which is called Apachi P Library so that name of the library is Apachi POI library or we can say apachi po Library so this is also an a third party library or we can say a API and which we need to integrate with existing project so whatever project we are currently working with this is a purely selinum web driver project right and underlaying is Java so the underlaying technology is Java only so this basically Java project which also having Maven integration right so because in the pom.xml we already added selenium uh selenium drivers so now in on top of it we will also add Apachi Li so then we will able to access different type of classes some additional classes and methods from Apachi POI library and by using those classes and methods we can work with the Excel files okay so first let us try to see how we can configure this Library into our current project is very simple just like how we have configured uh selenium by just adding one dependency similarly we should also add another dependency another dependency for Apachi POI so now let me show you how we can add another dependency for Apachi POI so from where we will get all the dependencies what is a website mvn because we are working with mavan project so if you go back to the MAV mvn repository and uh from the MN repository we can get all the third party libraries just type with mvn repository and this is a website actual mavan Global repository so from from it we can download everything now we need to search for Apachi POI and uh you can find everything and if you want to know the documentation and everything from apach Pui you can search for the Google you can search forp and it will first go to the their official website so if I look at here this is AP POI the Java API for Microsoft documents Java API for Microsoft documents it is not only for Excel uh it will also support word PowerPoint other tools which are there in Microsoft Office suit and this is the main website and all the download information okay documentation more examples we can find in this particular website this is official website of apachi POI so this is also free open- source API which is developed from Apachi foundation so Apachi is provided so many third party apis not only this so this is the documentation page and if you go to download section and suppose uh if you create a normal Java project if you don't have a one then we can also download specific jar file and we can attach so if you want to download the jar file you can just click on this link it will give the jar file that we need to add but we are not following that approach we're following Mayan project so we have to just add one dependency so to add dependency we need to just go with FN repository search for apach POI and you can find so many things here so which one we need to add so in Apache POI Library so these are the options you can see in this there are two libraries which we have to add actually two libraries which are related to this apach POI which we need to add so the first library is uh just apach POI uh AP GPI common this is the first one and 525 so this is the first one we need to add in the depend dependency section previously we added selenium Java dependency now I'm adding another dependency inside the dependency so this is one this is the link if you want to directly get this can open this link and you can get it can directly go to this link and go to the latest version 525 copy this and add it okay this is the one dependency which we need to add Apache POI and uh there is another dependency also we need to add related to POI that is uh Apachi POI W XML you can see something called wo XML the second one this is the one apach API based on OPC and O XML schemas okay so just uh get this second one again same version 525 just copy this so these are the two dependencies which we need to add in Apachi POI so these are the two dependencies are required so what will happen when you add dependencies it will automatically download required Char files okay now if I look at under reference libraries uh sorry yeah we already had this friend libraries so once you added these to now let me save it and then refresh your project as soon as you refresh under uh May dependencies apart from the selenium jar files you will also see APAC P related jar files so it is start downloaded you can see here p525 p o XML so these are the jar files which are automatically downloaded by Mayan project just by adding dependencies in the bomb. XM okay now that's it this is simple configuration we have to do so the first step we need to add these two additional dependencies in pom.xml I'll put this dependency here just you need to add them in existing form. XML dependencies in pom.xml okay that's it so once you added these two dependencies we will get some additional classes some additional methods through them we can deal with the Excel files okay so now our project is ready now let me close this and make sure all the dependencies this is one dependency this is another dependency and this is another dependency so these are all dependencies are part of dependencies stag so at the time of adding uh you need to just make sure all the dependency should be part of dependency okay now let me close this so you can just get this URL and browse it in mvn repository you will get this dependency if you not able to navigate or if you're confusing which one you have to select because there are so many apach are there so directly go to this link directly go to this link it will show you all the versions Apachi POI common and another one is this one directly go here and open this URL you can see the same version again 525 okay so you can get it okay so if you need I can just put in the chart box all right so now we have added the dependencies in form. XML now our project is ready to deal with the Excel files okay now by adding this uh apach P jar files we are ready to handle Excel files okay so now before handling Excel files so we need to understand something about Excel file Excel sheet and the structure of Excel file how we can organize the data and everything so what we need to do is whenever uh you want to work with Excel file so mainly reading Excel and writing data into Excel these are the only two operations we do and before doing this operations first we need to understand what is an Excel what it contains so let me open a small Excel file a small document this is my Microsoft Excel file okay so and Excel file Excel is a basically what a file Excel is a a file and this is my Excel file if you want to save it you can save it Excel is basically a file Excel file and also we can say this is Excel file Excel file contains what Excel workbook So currently this is one Excel file contains one workbook okay so if you want to create create another workbook you need to go to the file and you need to just go to the new and you can take the blank workbook so most of the times file and workbook both are considered as the same there is no much difference so file or Excel workbook so whenever you want to create a new workbook you can take this blank workbook and you can give another name to that particular workbook so normally for Excel workbook we will provide a name so Excel file and which contains a workbook so the Excel workbook we will provide some name that's called Excel file name so what is workbook contains workbook contains a Excel sheets worksheets we can call sheets okay so workbook contains a work sheets and what the Excel sheet contains every Excel sheet contains number of rows multiple rows and what is a row contains every row contains a multiple set so this is a hierarchy of Excel file so first we will have Excel file Excel file contains a workbook workbook contains a multiple sheets again every sheet contains a multiple rows every row or each row contains multiple cells so this is the hierarchy of the Excel file this is the hierarchy of the Excel file now in this hierarchy where exactly we want to from where we want to read the data from all these things from the cell so cell is an actual container which holds the data right whenever you want to read the data that should happen from the cell whenever you want to write the data that also should happen inside the cell so from the cell we will read the data and also we will write the data into the cell so cell is a a final object but we cannot directly interact with the cells at the time of reading and writing so so first we need to open the file we need to go to the workbook then we need to go to corresponding worksheet because there are a number of sheets will be there so we need to go to exert sheet and also we need to go to exert row on that particular row we will find exact cell in that cell we can write the data or we can read the data so we need to follow the entire hierarchy to reach this particular cell okay and we cannot directly interact with the cell so if you want to read or write data into the cell we need to open the file we need to open the workbook we need to get the sheet we need to get the particular row then we can interact with the cells so this is a process now if you consider every component in Excel is an object let's say Excel file workbook sheet row cell these are the main components are there in Excel sheet Excel file now to representing every component there is a dedicated classes are available what are those classes provided by selenium uh provided by apach py so if you look at the Excel file first component is what Excel file so suppose if you want to read the data from Excel file or write data into the Excel file we need to open the Excel file first so we can open the Excel file either in the reading mode or writing mode so if you open the Excel file in the reading mode we can read the data from Excel and if you open the Excel file in writing mode then we can write data into the Excel file so to open this Excel file in the reading mode or writing mode there are two classes are available they are file input stream and file output scam so basically these two classes are from java itself okay from Java itself so these two classes are not from Apachi POI why because these classes we can use it not only for Excel files whichever file you have so notepad properties file Excel files PDF file any type of file if you want to open we can use file input Steam and any type of file if you want to open in the writing mode we will use file output Stream So these two classes are same for all kinds of document ments all kinds of files so that is the reason Java is already provided file input stream and file output stream by using these two classes we can open the Excel file either in the reading mode or in the writing and apart from these two there are other other classes are provided by Apachi POI which will deal with workbook sheet row and cells now there are four additional classes are provided by apachi poi right xssf workbook xssf sheet xssf row xssf cell so what are those three classes the four classes these four classes are representing the four components of Excel file so file is already represented using file input stream or file output stream depends upon whether you want to read the data or whether you want to write the data based upon that you can use one of the class and once you open Excel file then we have to deal with the workbook the for that we have a special class called xss workbook and to deal with the sheet we have a xsf sheet and to deal with the rows we have xssf row and to deal with the cell we have another class called xssf cell for remaining four components of excel we have four different like kinds of classes provided by Apachi POI so this Apache POI library is provided these four classes which are especially deal with workbook sheet row and cell okay so now by using these classes we can handle rest of the components now we'll see the first operation how to read data from Excel reading data from Excel suppose I already have some data in my Excel file or Excel sheet now I want to read the data from Excel sheet and then I'll print in my console window but in actual scenario is what once you read data from Excel sheet we will use the data for testing purpose we will pass the data to the application then we will do the testing that is the secondary part but before that we will try to understand how to read data from the Excel file okay so this is a these are the components in Excel and if you go back this is an Excel file or workbook which contains a sheet one you can add a number of sheets and every sheet contains a multiple rows every row contains a multiple cells so this is hierarchy of the Excel okay now let's see how we can handle this excels Excel how to read data from the Excel by using these four different classes this is a step by-step process very easy just we need to remember a few classes and methods we can do it so what is a prerequisite before reading the data from Excel file we should have an Excel file already which contains some data now I have already some Excel file I prepared let me show you so you can keep that Excel file somewhere in your system like say an automation automation files now here I have some file called data. XLS so this is my sheet one sheet two multiple sheets are there but in the sheet one I have some data so let's say book name purchase date amount and then location so I want to read this data and I will print in that that data in my console window in our Eclipse okay so normally what happens is when you maintain the test data files in our project we don't keep these files outside of the project so normally we don't keep them in C drive or Drive some external places most of the times whenever you maintain the test data normally what you will do is in the same project we will create a new folder like this and I'll name it as a test data folder and inside the test data we will keep the Excel files so now what I will do is whatever Excel I have shown you just now I'll copy this Excel file and copy contrl C and go back to the test data select the test data folder and simply control V so it is copied so here you can see data. XLS file okay so like this you can open if you if I double click on this and you won't see anything in the eclipse okay so what you need to do is you need to associate this particular file with X LSS extension so when you double click on this it will ask you first time so Associated this XLS file with current editor and do not ask again so you can just select this or else show ID extensions for this file type and let me install them it is asking for this because if you have if you already installed some Excel related plugins it will directly open the Excel file if I double click on this it will directly open okay so otherwise it will just show a small icon like this can see we can open with it will not show anything so now what we need to do is we need to associate it this file with some extension so I'm selecting the first one show ID extensions for the file type and let me install them because currently XLS extensions are not there that's the reason it is not able to recognize now I'm just clicking in okay button so once you click on okay button so it will open Eclipse Marketplace so here you can search for uh Excel something like this it will show you what all related plugins are available to identify that particular extension or if you're not able to see anything here uh another way is simple double click and select the second one because Excel file we already installed Excel already there in our system select the second one say okay so currently it is empty now it's not showing anything here but if you are installed uh excels plugin so then the icon will be different okay let me try to install the plugin so first open this okay so let me go to the help let me find out proper one so I can just go to Eclipse Marketplace so this is the optional step so this is not manded but if you want to see exact Excel file format you need to do it so I'm just typing something called XLS X so do XLS X extension so it could not find you can just type Excel plugin okay so we'll see this Excel plugin later I think we could not find here so but if you install that Excel plugin so this icon will be just like an Excel icon so how you're able to see this icon here same icon will be display just visibility purpose otherwise there is no use of it okay so now I copy this Excel file into this test data folder so once we have this file in the test data folder so this is the data which we have currently in Excel file so now we want to read this data and put this in the console window that's our job so now what you need to do is go to your Eclipse now go to your project create new package for today day 40 and inside this I'm creating a new class limit as reading data from from Excel okay so here we are not dealing with any type of applications for now we just focus only on the Excel part so once you understood about Excel sheet and Excel part then we will try to deal with application then I will show you the entire data D test case so that is a concept tomorrow we'll discuss but today we'll just focus on uh operations on Excel file okay now we'll do step by step so how to read data from Excel file so before doing that you need to remember this hierarchy this is most important so with the same hierarchy or with the same process we do it first Excel file then workbook which contains a sheet sheet contains a rows rows contains a cell so same hierarchy we have to follow okay the first step is what is our what is our goal here is we want to read data from Excel file that's our main objective so before reading the data from Excel sheet we have to open the Excel file so in which mode we have to open reading mode or writing mode because we are reading the data from Excel so we have to open the Excel file in the reading mode so to open the Excel file in the reading mode what is the class we have to use file input stream class we have to use file input stream class now let us use it here I can say file input stream file input stream that is representing a Excel file so I'm creating one variable called file equal to new file input to stream so I have created one file input stream class object and import this file input stream from java. iio this is not come from Apachi POI this is comes from java itself so java. iio is a package which we have to import so when you create file input stream object this will accept this will expect the file from from where we want to read so we already have that file in the test data folder so we need to provide the location of this XLS file so I'm just go to properties of this file so that I can get the location of this file so this is the location that we have to pass it here this is the location of the file which includes a name of the file also okay now this Butler statement will throw some exception file not for exception just add that so this is my first step Excel file we open in the reading mode and suppose if you look at here this is hardcoded path the full path we have given it went to automation my workspaces selum web driver another selum web driver test data and so on so if you want to capture this path dynamically so this current project path is still here so actual location is this one right inside the test data data. XLS file is present so if you want to capture the location till here this part dynamically what we can do is we can use one method in the the last classes we discussed what is that method anyone remember yes system do get property of user. Dr so if you want to get this location dynamically what you can do remove this part and write system. get property of parameter is small case letter user dot d and then conet so this will return return the current project location inside this it will go to test data folder inside this data. XLS file is present so now this file is represented with this variable name or file object so this is my first step so we have to open the file in the reading mode okay now we open the file what's the next step we have to capture the workbook from this particular file we have to get the workbook that's the next step so how to get the workbook we have to use a special class what is the class which you have to use for Workbook the xssf workbook this is a class now xssf workbook workbook equal to new xssf workbook and from which file we have to extract that workbook this particular file so in the bracket we have to specify the file semic and this xssf workbook class we have to import from org. apach POI xssf do user model so this is actual class which is comes from Apachi POI so new xss workbook of file so this statement also will throw some exception just add this IO exception so two steps we have done we open the Excel file in the reading mode we open the workbook from the Excel file so here we pass the Excel file object so obviously what this will do this will extract the workbook from this particular file and once it is extracted workbook from the file we can put that inside the xsf workbook type so this particular variable is representing workbook of the Excel file now we deal with Excel file and then workbook what's the next one sheets so inside the workbook there are multiple sheets but we have to extract a specific sheet from the workbook so how to extract the sheet from the workbook take this workbook object there is a method called get dot get sheet get sheet is a method so get sheet get sheet at there are two methods are there and get sheet index also there so you can use one of the method so when I say get sheet you have to specify the name of the sheet so currently my name of the sheet is what sheet one that is a name you have to provide sheet one and what the statement will do this particular method from the workbook will extract the particular sheet that we are going to store in a variable and what is the type of this variable is what xsf sheet this is a special class is available so we need to refer the variable with xssf sheet and this we have to import okay first we have to open the file in the reading mode then extracted workbook from the file then from the workbook we get the sheet and store into the variable and instead of get sheet we also have another method suppose can use like this instead of get sheet there is another method dot get sheet at and here we can pass the number and it will start from zero 0 1 2 3 4 like this you can sheet number also we can provide index okay this is alternative method so you can use either get sheet or get sheet at both are correct but when I use get sheet you have to provide the name of the sheet when I use get sheet at you need to provide the index of the sheet so but always if you you have 10 sheets and 20 sheets it is very difficult to find the index so most of the times we provide the name of the sheet okay so we extracted the file we extracted workbook from the file then we extracted specific sheet from the file so we have number of sheets but we extracted only sheet one now inside the sheet we have so many rows and in every row there are so many cells so inside the sheet we have so many rows and and in each row there are multiple cells so now we need to extract rows so sheet contains a multiple rows so how to extract the number of rows from a sheet so before we need to read all the rows and all the cells all the rows data and all the cells data in each row we have to read so before doing that we have to count how many number of rows are present how many number of cells are present in each row that we need to find out so for that it is just like a matrix it's just like a two dimensional array so now we need to find out number of rows and number of cells in a particular row so to find out number of rows you need to take the take the sheet object sheet dot there is a method called get last row num sheet Dot and get last row num this is the method and this method will return the last row number so suppose here this is my data this is my first row second row third four five six rows are there okay so so total six will be written so what is that question worksheet and Sheet are the same yeah the terminology is different okay worksheet and sheet is same exactly the same no difference no they are not different they are same when I call worksheet or sheet both are same there is no difference so to represent in the sheet we use xssf sheet is a class there some people will call it as a worksheet some people just call it just a sheet that's it both are same see there are synonyms are always there right so synonyms is always there there will not be a single word for anything so one item we can call them with multiple names same thing here also so the sheet is also called as a worksheet what is the new thing in that same thing workbook is also called as a workbook and row is also called as a working row cell is also called as a working cell that's it all right so now let's go back to the X so to get the number of rows from exert sheet we use a one method called get last number so this will return the last row number from the Excel sheet that is exactly equal to number of rows normally the last row number is exactly equal to number of rows right so here what is the last row number six is the last row number so this is the data which we have in the sixth row so this particular number will be return returned get last row number means six will be return returned so this is the method and that I can store in a variable I can create one integer variable in into I can say total rows or number of rows okay now if you want to extract the number of cells so where exactly the cells are present inside the row okay so if you want to find number of cells first we need to capture the row any Row in that particular row we can count number of cells okay okay from the worksheet or from the sheet we have to extract one particular row on that particular row we have to extract the cell this is the process okay so from that particular sheet because in the sheet there are multiple rows so we need to extract particular Row in that particular row we can find the number of cells we cannot find number of cells directly first we need to extract the row from the sheet then we can find the number of cells now let us has captured the row from the sheet so how we can capture the particular Row from the sheet take the sheet object dot there is a method called get row get row and here we need to provide the row number in which row because almost every row is having same number of cells so I will provide some number let's a row of one so normally the row index will start from zero so you can say Row one first row so this will get the first row sheet. get row of one in this we want to find number of cells so get the last cell num this is the method so from the particular sheet we have to extract the particular Row from this particular row we are finding the last cell number that is exactly equal to number of cells so in total number of cells so I have captured two values I find out the total number of rows and also I find out the total number of cells in each row so by using this information we have to read data from all the rows and all the cells from Excel file so now let us start writing a for Loop so we need to write a two for Loop so first of all let's print this data can say system. pinell a number of rows printing total rows and uh number of cells and here I can say total cells okay now just execute till here and we'll see I'm running the code till here run as Java application yeah now we can see so number of rows are five number of cells are four number of rows are five number of cells are four okay so when you executing this you are getting some kind of an error uh status logger lock for J2 could not find for logging implementation please add log for J core to the class path so you may see you may see this kind of a error this will not block your execution still you can work on the Excel file but if you don't want to see this we need to just add one additional Library which is related to log for J2 so log for J core so how we can do it you can just search for this log for J core and go to m repository but this is not bloger okay so if you just want don't want to see that error we need to add log forj Library so just go to Mayan repository and search for log for j i core now you can see Apache log for J iph core because this particular jar is missing from the dependency that's the reason it's giving some error so go to this Apache lock for core and get the dependency so get the latest dependency these are all still in beta versions so 2231 you can take the stable version and I'm adding this dependency because this log for J core is missing from the existing apach dependency so just go to P XML and additionally add this dependency so this is for eliminating this particular error so once you added this dependency save it and just refresh the project once again so it will download required jar file now if you execute this you don't see the error now I'm running one more time run as Java application so you got another error so no class founder error okay so let's add uh latest one so sometimes if these two dependencies are not compatible for example this apach POI and this one is not compatible also it won't work so let me put another version latest version also try to update this forcefully once again I think still we are getting log for J core okay let's add a log for J2 what say [ __ ] C okay so we'll see this error later I think it is not recognizing that jar files so we are getting this error fine so this is not a blocker so we can still proceed with the rest of the steps so number of rows are five and number of cells are four if I look at this and go to the Excel file so this is Excel file currently how many number of rows we have including header part six rows we have so 1 2 3 4 five six rows we have including header right it is giving only five what does it mean mean it is counting rows from zero it is counting rows from zero so we can say 0 1 2 3 4 5 so rows are counting from zero according to the Excel and four what is this four number of cells are four four means it is counting from one cells are counting from one so this is first cell second third and four four cells are there so rows are counting from zero cells are counting from one okay remember this point because this is most important so once you get number of rows and cells now we'll start reading the data so how we can read it we need to write two different for Loops one for Loop is it for what incrementing the number of rows another for Loop is what incrementing the number of cells in each row is just like a two Dimension array so whenever you see number of rows and columns we have to write a two different looping statements so I'm writing one another for Loop inside the another for Loop so two for Loops I'm writing nested for Loop now outer for Loop is representing rows and inner for Loop is representing the cells or columns now here I'm starting rows number starting from what zero right so here I'm taking a variable int r equal to Z because row numbers are starting from zero and R less than or equal to how many rows we have total number of rows are there so equal to total number of rows and r+ so number of rows are returning five so all five rows we will get so five is nothing but it is also counted from zero so R less than or equal to Total rows and r++ this is incrementation of row now come to the cell part so from where we have to start reading the cell so cell numbers are starting from one actually 1 2 3 4 it is rning four but according to the Java at the time of reading the the cell also it is counting from zero so as per the Excel the row count is start from zero and column count will start from one but according to the Java row count will start from zero only at the same time cell count is also starting from zero only according to the Java so that is a reason here the column cell also we need to start from zero I can say in c equal to0 but here we have to say C less than total cells C less than total cells we should not say equal to C less than total cells and C++ because we starting from zero it should be less than here okay but here we should be less than or equal to why here it is less than or equal to because total number of cells it is returning by counting from zero itself when you count from zero itself number of rows are five that's the reason when you say zero still it should be less than or equal to total number of rows but here it is not like that actually the total cells are there 1 2 3 4 it counted from one but aser Java it will start from zero so we start because we are starting from zero we say less than that is the logic now this is representing outer for Loop is representing the rows inner for Loop is representing the columns or cells now we need to read the data from the particular cell and this is the crucial point so whenever you want to read data from the particular cell any cell suppose I want to read data from the cell we need to go to this particular Row first we need to extract the row entire row then we have to get all the cells once it is done now go to another row then read all the cells once it is done then go to another row then read all the cells that means for every row we have to repeat number of cells and once it is done next go to the next row and then repeat all the cells so before getting the cell and cell data we have to extract the particular row okay in the previous step also we have seen so to count number of cells first we have extracted the particular row and then we find out the number of cells similarly when you want to read the data from the cell first we need to extract the particular row and then we can read all the cells so inner for Loop is representing for what cells so before reading the data from the cell that means before using inner F Loop first we need to get the particular row once you get the row then we will start repeating this inner F loop on that particular row so how to extract the row from the sheet same method sheet dot get row and which row we want to get r r is representing row number so get row sheet do get row so what this will return this will return return a row object that we have to store in a variable so I can create as a current row some variable I'm creating and what is the type of this variable this get row method will return return the row entire row and the type of this row is what xssf Row Third object this this one so row is represented what xssf row only so this variable type is des and xss of row we have to import so what we have done here is before reading the cells using inner for Loop we have to get the entire row and once you get entire row from this particular row we can read all the cells by using inner F okay now by taking this current row here we want to read all the cells so how to read the cells current row dot there is a method called get cell and which cell we want to get we pass the number so C cell in that particular cell there is some data is present that data we have to get so from this particular cell I can get the data by calling another method called two string this is the method I can use so from the current row it will get particular cell from that particular cell two string method will return the data from the cell okay this is a combined statement suppose first I want to extract the cell then I want to get the data then what you should do is as soon as you get a cell from the current row store that cell in a variable and now the type of this variable is xssf cell this also we can import so now all the classes have covered workbook is represented with xsf workbook sheet is represented with the sheet xsf sheet row is represented with xsf row cell is represented with xsf cell if I look at the code here you can notice this is a file workbook sheet and here we capture the current row and here we capture the current cell so from the current row we extracted particular cell and storing into the cell object now from the cell object we can extract the data cell dot get cell get string uh two string this is the common method we use to capture the data from the cell there are other methods also there I will introduce them later two string is a method so this will actually get the data from the cell now we can print the data so by using system dos out. print Ln system out. print Ln you can print cell do to string like this okay so now what will happen let me repeat once again so we have find out the number of rows and cells from the Excel sheet and now first we have to repeat the rows from 0 to five and then we have to repeat the column from uh 1 to 4 so before getting the cells we have to extract the particular row how we can extract the row from the sheet sheet. get row of r r is what representing row number in every iteration in every round of execution we are passing the different row number accordingly it will return the particular row that will become the current row and from the current row so this inner for Loop is representing reading the cells in the particular row so which in which row we want to read the cell current row so current row. get cell of c c is representing the cell number that we are passing it will return the cell object once you return the cell object from that particular cell we are extracting the data by calling two string method that we are printing so first it will get the first row and it will read all the cells once it is done again it will go up outer for loop again get another row and read all the cells third iteration get another row and again read all the cells so like this it will repeat outer for Loop is repeat five times because we have a five rows inner for Loop will repeat four times in every row because we have a four cells so once this Loop is executed we will get all the rows and cells from the Excel sheet so let me execute it and once we have read everything two things we have to mainly we have to do what is that first we need to close the workbook because we open the workbook from the file right once our job is completed workbook Dot close we have to call this method because whenever you open the workbook and worksheet it will occupy certain amount of memory so we need to clear that memory so workbook do close so once you close the workbook then we have to close the file also because here we open the file in the reading mode and after completion of our job close the file also file Dot close so these are the two things we have to do now let us execute and see whether it's reading data or not I can say run as Java application yes so now we can see we got all the data from Excel sheet and if I look at this data Excel sheet this is my data current data which we have and book name purchase date amount location these are the headers actually and it is combined everything just I want to place this in row and columns format just a small design I can do so what I have to do is instead of print Ln you can simply say own only print and after printing the value we can just give one tab space so here I'm giving slity so that it will give one tab space after printing the value and after completion of inner for loop I will jump to the next line for that I'm using only print Ln so this will print the values in single line after printing all the cells in single line then it will go to the next line so now this time you will get the data exactly in the table format let me execute yes now we can see this is the data so we got all the book names purchase dates amounts and location so some basic overlapping is there but that is fine okay so these are the purchase dates we got amounts you got and location also we got so this is how we can simply read the data and here you have to not no is one thing do you see any difference between both data and data which is there in Excel data we have read in the console window do you see any difference do you see any difference guys the data book names are exactly printed no change purchase data also exactly printed no change location is also exactly printed only amount is little bit change so actually the amount it don't have any decimals here but here additionally decimals are added why because two string method we have used actually so two string method this is the common method which will read all the data in a string format so basically Excel also having some data types Excel is also having their own data types for example if you right click on any particular cell go to format cells and here you can see the different data types General number currency okay okay date time percentage there are different kinds of data types are available in Excel file so at the time of reading this data we will use one common method which is called two string so what the two string method will do is two string method will convert any type of data into string format the data can be any it can be date or it can be number integer it can be string whatever it is the whole data will be part of in the form of string only so that is the specialty of the two string method is a one common method which we can use this will read entire all kinds of data into string format so in that particular process the amount whatever we specified in Excel sheet that is also read in the form of string at the time of reading in the form of string by default it will add dot zero decimal point it will add but there is no difference even if you add decimal point do zero no difference it is s again representing 200 200 and 200 are zero there is no difference okay the two string method will add decimals to the existing number okay remember that point so other than this rest of them exactly the same so this is the process of reading the data from the Excel sheet we'll able to read all the rows whatever rows are exist and we are able to read all the columns whichever they are exist so is this clear to everyone so far please confirm in the chat box rows are starting from zero that's the reason it is returning five as per Excel row count will be starting from zero and cell count is starting from one as per Java row count and cell count both will start from zero only that's the reason we started from zero here okay so this is a step by-step process let me repeat once again what we have done first you open the file in the reading mode then we extracted workbook from the file then we extracted specific sheet from the file by using get sheet method and we find out total number of rows by using get last row number we find out total number of cell by using get last cell number we have printed and then we have written a looping statement in that outer for Loop is repeating number of rows inner for Loop is repeating number of cells in each row but before reading the cells from each row we have to extract the particular row then only we will able to read the cells so once we are able to read the cells we are printing the data and that's it so after completion of reading data we are closing the workbook we are closing the file okay so here the classic for Loop must be there because we are dealing with the index okay if you're dealing with the indexes you must use a classic for Loop enhanced for Loop is only for collection types remember that point only when I work with the list collection or set collection hash map hash set in those cases only we have to use a enhanced for Loop in all other cases we use a normal for Loop okay this is all about one example how to read data from Excel sheet so if the row start from zero then line number 28 where is line number 28 yeah if the rows are start from zero then in line 28 why is right get you can put zero also no problem you can put zero also nothing wrong in this I just put some row number you can put zero also nothing wrong okay so still you got a number of rows are five number of rows are four any row because all the rows are having same number of cells in that case you can put any row number number here no problem understood everyone so now I'll show you how to write data into the Excel file so we'll take one empty Excel file then I will write the data new data in the Excel file so we'll see how we can do that exactly opposite so whatever methods we used here exactly opposite methods we will use okay let me show you how we can do this let's close it and create a new class writing data into Excel and take the main method you can also create another empty Excel file in the same test data folder or else we can maintain a separate place so in this I will create one uh a new file so do we have any file here okay so what I can do is here you can create your own Excel before or is through our automation you can create our automation script first it will check the Excel is exist or not Excel file exist if Excel file is exist it will just write the data to the Excel IF the Excel file itself is not exist then it will create a new Excel file then it will create a data so for now uh I don't have any file inside the test data I will create my own Excel file and then I will update the data in the test data okay so now what I will do is writing data into the Excel currently I don't have any Excel file inside the test data other than this data. XLS I will create a new Excel file and then we will write a data so we'll see how we can do step by step same exactly opposite it of the previous one in the previous one we have read the data but this time we have to write our own data now again same hierarchy we have to follow file workbook sheet row and cells okay now first step is what we need to open the Excel file if the file is already exist we can open it if the file is not exist it will automatically create a new Excel file but uh in which mode we have to open the file in the reading mode or writing mode in which mode we have to open the file reading mode or writing mode writing mode so to open the file in the writing mode what is the method we have to use file output stream okay file output stream so let's use it close this file so first step file output stream file equal to new file output stream of so you need to specify the location of the file if you already file is exist empty file that location you can provide here if the file is not exist still no problem it will automatically create a new file so where you want to create a new file inside my test data I will create my new file so how to extract the location of the test data file test data folder and this is the location okay so how to extract this uh default location from the web from the project by using system dot get property of here I can say user DOD okay and concatenate single quotations and I can remove the rest of the thing I just provide only folder name so test data slash and you can provide some name of the file I can say my file dox X okay now this is the file I want to create because currently I don't have such file here so I'm going to create a new file if file is already exist no problem it will override if the file is not exist it will create a new file so my file. XLS I'm going to create inside the test data folder again this is a part of your current project directory now import this file output stream from java. IO package and this will throw some exception just add this yeah so now we created a new file now here we have to create everything we have to create newly we created a file we have to create a workbook we have to create a worksheet we have to create a row we have to create a cell then we can update the data everything is newly creation okay so in this particular file we have to create a new workbook new workbook we have to create how to create a new workbook in the file simply you can take xssf uh workbook class xssf workbook workbook equal to new xssf workbook so the workbook is a class which is representing the workbook right so just create an object for the workbook and don't pass this file inside the workbook for now we will do this at the end of the code because we just created only file still we are not using the file first we have to create a workbook we have to create a row we have to create a cell we have to update the data and finally we have to attach this workbook to the file that is a final step So currently there is no connection between these two but earlier how we have done because already existed file we are reading the data from the file so we have extracted the workbook from the file so we have passed file as a parameter in the previous case but now we are everything creating newly so we should not pass anything here just it will create a new workbook finally after writing the data into the workbook we will attach to that to the file we'll do the Step at the final level now we'll create a workbook inside the workbook we have to create new sheet sheet we have to create so workbook dot create sheet this is the method prev L which method we used to get the sheet from the workbook get sheet method we used and this time create sheet so we can provide some name of the sheet I can provide some name called sheet one sheet two sheet three is a common names but here I'm giving a different name called Data data is my sheet name and this I'm going to store in a variable called sheet and the type of this variable is what xssf sheet and this xsf sheet is also we need to import okay so we created a file we created a workbook we created a sheet and inside the sheet we have to create multiple rows and every row we have to create multiple cells in every cell we have to write some data so let's create a first row in the sheet how to create a row in the sheet sheet dot create row create create row which row zero because row count will start from zero only right create row so once you created this row that row I will store in one variable called Row one and here I'll name it as xssf row object so in this step what I have done in the sheet I have created first row and I given the name as a row one the type of this variable is row xssf row now in this row I want want to create multiple cells currently we are in the first row in the first row I will create multiple cells now let's take this Row one and in the row one how to create a new cell dot create cell and which cell zero zero cell dot inside the cell we have to update some value how we can update the value using another method called set cell value set cell value you can see set cell values having different flavors we can update bullan value date double okay all kinds of string all kinds of data you can update in the cell so set cell value here I'm passing some data let's say I'm passing string data called Welcome this is only one cell in row one now I want to write another cell in the same row again take Row one create cell of one and set cell of and here I'm writing some number welcome I can say 1 2 3 4 and again in the same Row one I write another cell so in the same Row one another cell two and here set cell value here I will write something else I can say I can put another string let's say automation like this and in the first one I can say Java okay so now in the sheet I have created first row and I updated three cells now same thing we repeat for multiple rows so create another row create another row so same thing we'll repeat as per Java we have to start from zero that's the reason I'm starting from zero create cell is zero previously also at the time of reading the data from cell also we started from zero only but according to the Excel Counting will be different Excel will count from 1 2 3 4 but according to the Java start from zero so that's the reason either while reading the row or while reading the cell we always start from zero remember this okay now I'm creating another row so I can say create row of one and here this is a row two now in the row two I will create a new cells everything you have to CH now in the row two I create zero cell and I'll update a new value let's say python and uh in the row two another cell and here update another value python uh let's say this is uh 19 19th version It's a version number 19th and let's say Python 3 is a three version automation so in the zero row I say python another cell I say three and automation this is a second row similarly I can put another row in the same uh Excel sheet third row third row so row number three row number three and here create row of zero create row of one create number of two create row of two and again this we need to change Row three in the row three we are getting cell number 0 1 2 and updating the value here I'm writing another value C sh five and automation like this so how many number of rows we created three rows how many cells we have created in each row three cells so once it is done so we open we create a new file workbook we created sheet we created we created the three rows and as soon as we created the three rows in every row we assigned some data so once it is done what is the final step is we need to attach this workbook to the file so far there is no connection between these two we created a new workbook in inside this we created a sheet inside this we created a rows and cells and finally once your job is done we will attach this workbook to the file so how we can attach this very simple workbook workbook dot write workbook do write and this particular workbook this particular workbook we are going to write into the file this is the command okay so this workbook we can write into the file so this file output stream you can also write here no problem okay you can also put file output stream here because first we need to create a workbook then create a sheet create rows and cells and finally we will write this workbook into the file that's the reason you can also put this file output stream here or you can put in the beginning because even though if you create this in the beginning we are not using this file till end of the script so here we need to do it so once it is written then we need to close workbook so I can say workbook do close I'll show another examples just hold on this we have written simple static data if you want to write a dynamic data into the Excel then we have to go with the looping statement I'll show you another example just hold on workbook do close then we need to close a file also file doose okay okay and finally we will return return some confirmation message I can say simple file is created that's it so this is the final script which will create a new file new workbook new sheet new rows new cells and new data everything we created newly nothing is present so now currently I don't have any file inside my test data but I'm great this one my file. XLS which contains all these data now let us execute and see run as Java application yeah now you can see file is got created now just refresh the folder if you don't refresh you cannot see the file just refresh now you can see my file. exls now if you cannot open this file directly copy this file and put somewhere here okay now I'm opening the same file my file. XLS now you can see this is the data which we have add data three rows three columns okay clear everyone so that file name what is the sheet name we have given data so you can see sheet name is also updated as a data okay so this is how we can write data into the Excel so very simple simple to remember the methods very easy so suppose if you want to open the file in the reading mode file input stream you have to use if you want to open the file in the writing mode file output it which is clear and if you want to get existing sheet we have to use get sheet method if you want to create a sheet we have to use create sheet method similarly if you want to extract the row we have to use get row method earlier we have used but if you want to create a new sheet create a new row we have to use create row okay like this so similarly create cell get cell if cell is already exist we can use get cell and if you want to create a new cell we have to say create cell so this is how we can read the data so now I'll show you another example suppose here I have added static data now this data I want to pass it run time once I executed my program then I will pass data dynamically and same data I will add to the Excel file okay so we'll see how we can add Dynamic data at runtime using for Loop now let me close this everybody's understood this example because based on this example the next one is dependent everybody is clear what are all methods we have to use you need some practice okay so initially you can't remember all these methods but once you start prac in two to three times you will able to remember this reading and writing only methods are different and also it is very easy to remember all the methods all right so here I have written some static rows three rows and three cells in every row now I want to create dynamically n number of rows n number of cells and even data is also some different so how we can do that let me create another example so same class I'm taking one more time and writing data into the Excel writing Dynamic data Dynamic data into the Excel okay now uh same process but I'm just removing this part Dynamic data we want to do everything is same so only thing is now this time we have to accept the data from the user dynamically even number of rows number of cells also automatically create okay now we have to create a new file so my file is already exist now I can say my file one okay I'm taking another or else I can say my file uncore Dynamic new file name I'm giving my file _ Dynamic now we need to create a new workbook new sheet the sheet name is data or I can say Dynamic data I'm changing the sheet name Dynamic data so this is my sheet now randomly we have to provide number of rows number of cells and number of data and everything so dynamically we have to capture the data and what I'm saying here is dynamical say the user has to provide the data so we have to accept the data from the user at the runtime how can we accept the data from the run in the runtime how we can take the from the user using scanner class okay so now here after creating the file workbook and Sheet I'm trying to use one scanner class here scanner SC equal to new scanner of new scanner of what is the parameter we have to use here system do in now because we want to take the data from the user so this scanner class we will import from java. so take this this SE will be used to get the data from the user later now I'm writing one F Loop this is for number of rows and this is for number of cells outer for Loop is for creating the rows inner for Loop is for creating the cells in the particular row now in this I will start from inter r equal to zero row number are representing the row number we are going to create everything newly and how how many rows you want to create that you want to decide here how many rows you want to create so even that value also if you want to accept from the user you can accept the user will enter number of rows and columns okay for example here I'm asking user system.out.print enter how many rows do you want to have how many rows do you want to have or you can say enter how many rows very simple message how many rows and that also we can accept how to accept SE dot what is the method what is the method to accept integers s dot next in right this will get and that I'm storing in a variable in number of rows number of row similarly I will also accept number of cells from the user Dynamic enter how many cells enter how many cells in every row that also I'm capturing storing it the number of cells okay so rows I'm expecting from the user number of cells also I'm expecting from the user now how many times the outer for Loop should repeat depends on the number of rows so r equal to 0 R less than or equal to number of rows R ++ and inner for Loop is representing the cells even cells also we have to start from c equal to Z but C less than we have to use less than number of cells how many cells we want to create and then C++ okay now we have to create new rows and new cells then we will update the data everything we have to create newly so before inner for Loop is representing the cells outer for Loop is representing the rows so before starting inner for loop I will create a row first first we need to create a row inside the row we can create multiple cells so how to create a row here let's take a sheet object whatever we created here sheet dot create row which row we want to create r r is representing no row number so this will create a new row that I'm going to store in a variable called current row and what is the type of this variable xsss F row object now this will create a new row based upon the number we provided and in this particular row we have to start creating the cells now come to Inner for Loop in this particular row how to create a new cell dot create cell cell which cell c c is representing the cell number after creating the cell okay this will just create a cell and inside the cell we have to update the value right so this cell I'm going to store in a variable called cell and what is the type of this variable ex is f cell now we created Cell and inside the cell we have to add some data even that data also we need to accept from the user right user has to provide that value so we need to provide some value so how we can provide the value so in this particular cell dot set cell value set cell value here normally we will hardcode the value right like this but this time we want to get this value from the user from the user we want to get this value so user can provide any value it can be number Boolean whatever so now what I will do is instead of hardcoding this value I can simply say s dot next yes C dot next so whatever the value we provided at the runtime that will be captured by this command and that will be entered into the newly created Cell okay so this is how we need to do so first we have create created a new row here and inside the row we created a new cell inside the cell we added data and this data we have to provide at the run and like this inner F Loop will repeat multiple times because depends upon the number of cells we provided so it will create so many cells once it is done again it will go up depends upon the number of rows it will create multiple rows in every row it will update the number of cells okay after after completion of this we will close the file we will close the workbook and first we need to attach the file to the workbook then here this is the command attach workbook to the file and then we need to close workbook and file and then confirmation message I have written so this is how and here I have used next in here I have used next why is it so because the number of rows and number of celles should be integer value that we already know so I have taken next int but here we don't know what value the user can enter user can enter any value number string bullion anything so common method is next so next method can accept everything as a string form okay can user jump the row or cell number example if the user want to store data directly into the row number three skipping yes yes but you cannot use looping statement in that case in that case you cannot use looping statement in the previous example uh are you talking about reading or writing storing data storing data means is writing in that case you can create like this by create by passing any index number suppose if I don't create like this so if I say one first row will be created if I two two second row will be created inside this again in which cell you want to write the data the cell number you can specify so randomly also you can put I'll show you that another example no problem so randomly in whichever cell and whichever row you want to update the data you can still do that okay so here go back so here this particular F Loop will dynamically get the data dynamically get the number of row dynamically get the cells from the user and accordingly it will create number of cells and rows and even data also we're expecting from the user okay now we will see that and what is the file name I have given my file undor Dynamic let me execute and see run as Java application yeah so now it is asking for how many rows how many rows you want three rows I want how many cells I want let's say five cells I want 3 into 5 15 values we want to enter totally 15 values we want to enter now it is asking for data so I'm giving some data let's say 1 2 3 4 5 integer data now I'm giving some string data a b c XY J again A B C M1 34 pqr some string type of data now this time I will give uh some decimal numbers 10.50 10.12 56. some random data I'm providing some decimal number data okay and yeah you can also give combination let's say 12 ABC 13 XY Z some combination also you can give like this or else a b c 1 2 3 XY Z 1 2 3 so total 15 values we have to give like this again I'm saying 1 2 3 4 5 6 7 yeah done 15 values are done all 15 values we provided so file is got created now we'll see the data is properly updated not randomly I have given so go to test data refresh now we can see my file dynamic. XLS created just copy that file somewhere in the drive so I copi this actually you can directly open from Eclipse but currently it is not opening so so I'm just copying somewhere here now this is a file I just created so let me open it yeah now we can see this is the file see this is the data which we have created so how many how many rows are created four rows are created how many cells are created Five cells are created in every row okay so this is how we can also dynamically enter the data in the Excel sheet using for Loop this is an example suppose if you want to update data randomly whichever row whichever cell you want randomly you want to update so that also we can simply do that so let me show you another [Music] example uh writing data in specific row and cell specific row and cell specific row and cell so in that case you can just remove this part okay first we need to create a new file new workbook and I can call it as a my file random okay different name I gave it my file random so now we open the file open the workbook open the sheet now in the sheet randomly we can create a new row new cell and update that randomly you have to do how we can do first let's create a random row sheet dot create row which Row in which row you want to enter suppose in the third row I want to enter create a row that I'm storing in a variable row so row number three I'm giving or you can just row xssf row so third row in the third row in which cell you want to update the data that you can specify row Dot create cell in the particular row create cell in which cell you want to create let's say in the third row third cell for example or fourth cell let me give four create cell right this will return return the cell object so xss F cell okay this is a row and this is a cell which we created third row and fourth cell now in this I want to update data so cell dot set cell value Val set cell value and whatever value you want to put you can put I can say some value here that's it so we created random row random cell and update the data so now execute this run as Java application file is got created now refresh your folder yeah now my file random. EXL has got created now now just open the file I copy here and open this perfect here it is got created see according to the row and the cell number so which row it is third row fourth cell and if I look at the Excel file this is a zero Row first row second row third row so third row which cell fourth cell a b c d so count is starting from zero so here it is got update so this is how we can also randomly create a row create a cell and then update the value so these are all few examples but actually in the real scenario you don't write all these scripts so we will create a utility file we will create a utility file which contains a reusable methods for creating Excel writing the data reading the data applying the formats colors and everything thing we have some utility file user defined methods so in data driven testing we will try to use that utility file because you have multiple data different test cases in every test case if you want to write this piece of code suppose if this piece of code if you want to write in every test case it will be very complicated so that is the reason we will create a separate utility file which contains a user def methods and we will read those methods and do the data driven testing that is act ual real scenario in the tomorrow session I will show you how we can create that utility file by using that utility file how we can create data driven test cases so that concept we will see tomorrow session but before that you need to be more familiar with this Concepts how to read data how to write data how to work with the cells and rows from an Excel sheet okay clear everyone so tomorrow session we will continue this and how to create utility files I will show you that yes in the DDT data D test case we will have test data in Excel files test cases anyway you will write before itself but test data is always maintained in the Excel files and the test data we will read it and use it in your application for our automation testing so most of the times we don't write anything into the Excel because data driven testing is a one- side concept so data we will prepare manually right we don't create data through automation we will create data manually so always use the data for data testing so most of the time we will focus only on the Reading part we don't do even writing part okay but very rare cases suppose I already read some data from Excel and use it in our application I have done some testing and the results if you want to write back into the same Excel then I can write a results back into the same Excel so in that case writing is coming to picture but that is also very rare case we don't write any results into the Excel file the results we will populate in the reports UI reports if any test cases got passed or failed we will populate the results in the report not in the Excel sheet that is very R case but still if you want to write results back into the same Excel sheet we can still do that but most of the times we will do only Reading part not on the writing part okay so we don't create anything newly we just use existing one existing data which we created manually in the Excel sheet we will read the data use it for automation that is a real use case so what what table you are talking about Shadi scenario where the user need to create a table what is the table you are talking about can you please elaborate what table you are talking about so report generation and all still we have not discussed so in test NG we will discuss about report generation we can generate test NG reports we can generate extern reports so that's a different part everybody everybody's understood this concept it's very very most important concept you will get a lot of questions also and you need to remember few methods and then definitely ask to write a code how to read data from Excel can you write few statements then you should able to write even though if you do some small mistake that's still fine but the process the procedure you have to know so my file. Exel we have just I have just shown you how to write data into the Excel that's it it's not a table or something nothing okay if you want to write the data in Excel how we can do that so that I have shown you just just an example okay but most of the times we don't write anything into Excel we all will prepare the test data manually and that data we will read it and use it in your automation testing that is a use case okay so just practice this for today's session reading and writing get familiar with this and once you play familiar tomorrow session we will continue with the data driven testing so writing data into Excel multiple examples have shown you writing data into Excel so this m is enough apach POI is very huge API we can do lot of things but for automation prospective this is more than enough how to read data how to write data into the Excel and even this is also not much important how to write just if you know how to read that is but tomorrow session I will show you how to create a utility file which contains all user defin methods and uh even if you have a 10 to 20 data test cases we will try to use the utility files so that we can minimize our effort and time and everything while performing data driven testing so that use case we will see tomorrow session okay yeah so I'll stop here for today's session tomorrow we will continue