How to Create Excel File using Apache POI | Selenium WebDriver |

hello guys welcome to my channel hvr tutorials in my earlier video we have already seen what is mean by apache poi and how we can download and set up this apache py with our java projects right so i have already shown how we can setup this with normal java project and also with maven project right so in my own project we use the dependencies to download this apache poi and in the normal project we directly download the zip file and we unzip it and we add all the libraries to the normal java project right so in this video we will see how we can create an excel file using apache py so we will create one excel file and we will write some data into that excel file okay so first how many types of excel files do we have we have two types okay one is old version and the other one is new version okay so when i say old version it is having xls as an extension okay so when it's a new version it is having excel sx so this is the extension right so i have mentioned if you want to work with the older version of excel files you need to import a different module okay the different component so if you want to work with new version of excel then you need to import a different component so for the older version of excel you need to import hssf okay and for newer versions it is xssf so these are the components basically okay so hssf is one component xss is one component so in this video we will see both the things so let me open the eclipse so here i have created one class that is create and write excel so first we'll see with older versions of excel that is xls okay so if you want to create an excel and write some data into it first we should understand the excel structure basically okay so here i have one excel a simple excel so this entire excel file is actually one workbook so inside this workbook we have multiple sheets okay you can see in the bottom we have sheet 1 sheet 2 sheet 3 like this we can create multiple sheets in one workbook you can have multiple sheets okay so in each sheet you have multiple rows basically okay so in each row you have again multiple cell okay so this is the structure basically so let me just write here first it is workbook inside that workbook then sheet inside the sheet we have row inside the row we have cell so this is the order that we need to follow while automating also that means while reading or writing from the apache ui so let me go back to the eclipse so first we need to create one workbook so here we are actually creating the excel file we are creating a new excel file and we are writing the data into it so for creating an excel workbook first i need to choose hssf component hssf workbook here okay so i'll give workbook as my variable name new hssf workbook okay so we are creating a new one basically so inside this workbook we want to create the sheets also right i'll just store that sheet into one sheet variable so hssf sheet okay sheet so the sheet is available inside workbook so we need to use the reference of this workbook to create the sheets okay so instead we have a method create sheet there are two methods with a create sheet name one is simply create sheet and other one is create sheet with sheet name okay so basically when you use create sheet it will create based on the indexes okay if you have already added one sheet in the workable so when you use this method after that sheet it will create the another one okay in the last index after the last index it will try to create the sheets so if you use the create sheet with name then it uses the sheet okay so let me just show you so if you don't mention anything okay if you are just using the create sheet method you are not passing the sheet name right so it will create like this sheet one sheet two sheet three like that okay so if you mention the sheet name then this sheet will be created with that user defined name like this for example test so like this it will create okay so now i will not mention anything so i'll just create the sheet okay i'm not giving any name so inside the sheet we need to create a row right sheet dot create row so for the row we need to actually pass the index which row you want to create so in this apache poi the indexes are actually starts from zero and it starts from zero this is for rows and columns okay so here i need to mention zero because i want to create the first row so i have created the row so in this row i want to actually create the cells so once you create the row next time you don't create it you get it okay because the row is already created so you need to use get row so here the index is 0 dot we need to create the cells inside this row so we have a method called create cell so here also you need to pass the index so i pass 0 so i'm creating first cell in the first row okay so till here the cell is created so i want to set some text in that cell simply i don't want to create write i want to enter some text into that one so for entering the text we have a method called set cell value so you can set any type of value boolean calendar date double local date time and string so i'll simply take string value and i will say hello so then next in the same row i will create another cell okay so we are taking the first only dot create cell the index is one and set cell value and i will enter world so next i will create one more row okay so the syntax will be similar simply i will change this indexes okay so next next so here this will be same and here i will change the content okay search for your tutorials so till here the workbook and sheet and the data everything is ready right so this is a temporary memory so whatever we have created now everything resides inside this workbook but this is a temporary we need to write this workbook data into one file right so we need to actually create the file in our machine so further we have a method okay workbook dot right so in this write method you need to actually pass any of these two parameters you can pass the file or you can pass the file out question so here if you see i haven't mentioned the excel sheet name i mean the workbook name and where it has to create right so that i am going to do with the help of file instance okay so i'll create one file instance here so here i need to pass the path along with the file name okay so let me just create insert this one only so let me go to this one so this is my project so inside this i will create one file one folder i will name it as excel files okay so i'll just copy this path and i'm going to use that path here okay so inside this folder i want to create the file so here we are using the hssf components right so our extension should be xls the older version of excel so now we have created the file so we could write by passing this file name so it will ask you to throw the exception you can simply select the superclass of exception after this you need to close the workbook okay so now let me just execute this see the program execution is completed i am not displaying anything here if there is a exception it would have displayed here in the console right so let me go to the folder so here you can see the test dot xls document is created right so let me open that now you see the test dot xls excel file is actually created and the sheet name is sheet 0 because we haven't mentioned the sheet name and the data is entered in the first row person hello and second word then hyr and tutorials so this is how you need to create the excel file and write the data into that excel file so now let me show you with sheet name also okay we'll do few small changes quickly so here i need to pass the sheet name right so i say sheet name also test sheet okay so here i'll change the file name to test1 we have test.xls now i'm going to create test1.xls so now let me just execute this okay so it is completed you can see test1.xls is created so when i open that the sheet name is coming as test sheet because i have mentioned that name and the data is same okay nothing i have changed except this one okay so let me show you with file output streams also here i have used file right so let me show you with file output file output stream i'll name it as fos new file output stream so for the file output stream the input is file so which file you want to create this one so i'll pass this here so this file output stream instance you need to pass to workbook okay so i'll change this to test it to so let me run this so the execution is completed so you can see test2.xls is created and if you open there's nothing change so it is just created as the previous one right so that means you can actually create it directly by using file or you can use the file output streams also so now we have seen the older version of excel right so if you want to work with newer version of excel here i am going to simply replace all hssf with xss okay so i'll copy this entire content here comment so here i will update okay it will be x everywhere so here also you need to actually change the extension so i'll name it as test3 so here i need to import the references okay so the references are also imported so let me run this the execution is completed you can see test3.xlsx so if i open that you can see we have sheet name and we have the data right so if you want to create any newer version of excel this is how you have to do nothing changes okay everything will stay same except this thing here you will use xssf modules and if you want to use the older version you need to use hssf modules so for example here i am using the older version modules but by mistakenly i put xlsx here so in this case what happens whether the excel sheet actually creates or not or if it creates will be able to open it let's find out so now i'm using the older version of modules and creating the new excel okay so let me run this so it should create test4.xlsx it is completed so if you go here test4.xlsx is created so when i open that it is giving me one warning okay excel cannot open the file why because the format or file extension is not valid okay so here what happens is internally this excel file is has some format right so you are creating older format onto this one so there is a compatibility mismatch so that is the reason it is not creating so you should always use the modules with respect to your excel sheet if it is new one you need to use xssf if it is world 1 hss okay so this is how we actually create and write the data into that excel file okay so in the next video we will see how we can read this data from the already created excel file okay so yeah that's it for this video i hope you enjoy this video if you have any doubts please let me know in the comment section below thank you for watching bye bye

Channel: H Y R Tutorials

Views: 36,818

Keywords: hyr tutorials, h y r tutorials, How to write excel file in Selenium using Apache POI, How to Create Excel File using Apache POI, write data into excel using apache poi, create excel file java apache poi, apache poi write excel file, apache poi, write excel sheet data in Java using Apache POI, write data into excel sheet using java, selenium excel read and write, apache poi excel tutorial in java, write data in excel using selenium webdriver, hssf, xssf, .xlsx, selenium excel, excel

Id: q-fREhjTmNk

Length: 14min 33sec (873 seconds)

Published: Fri Jul 31 2020