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

[@HYRTutorials]

📌 Apache POI Playlist Link: bit.ly/2RrngrH ✴ Checkout my other playlists: bit.ly/3gLIAVL ☕ Buy me a coffee: bit.ly/33ljBWc

[@swapnilpujari9079]

Your explanation is very appreciable. So neat and easy to understand. Really its helping me a lot in getting my concepts clear. Thank you very much for letting us understand in very well manner.

[@HYRTutorials]

Glad to hear that

[@unmeshinde]

I found this topic a little bit hard, and where I learnt it before, I haven't had much in depth clarity. But after watching your video it's getting clearer a bit ...

[@HYRTutorials]

Super buddy

[@prasadnastik9239]

Thankyou so much sir....you are the best teacher for testing on KZbin

[@HYRTutorials]

So nice of you

[@tejuchintakani4215]

It is truely very clear and easy to understand thank you and keep going

[@HYRTutorials]

Thank you, I will

[@Dinesh-zs8yk]

Bro nee videos chala bagunnai, thanks you so much ❤️.

[@HYRTutorials]

Thankyou buddy 😊

[@gayathrik5746]

Hi HYR gaaru, i learnt a lot from your videos till now. now am trying to learn frameworks. can you please make a videos on frameworks please

[@HYRTutorials]

Super buddy

[@banusha]

Thank you for the clear explanation!!!

[@HYRTutorials]

Happy learning buddy 😊

[@kishoresrinivas2104]

Thank you very much for sharing your knowledge. Thank you for it.

[@HYRTutorials]

Happy learning buddy 😊

[@lakshmankokku43]

Concept is crystal clear .Thanks

[@HYRTutorials]

Thank you. Keep Watching

[@fatihgul77]

Thank you. Really helped me to understand POI

[@HYRTutorials]

Glad to hear that!

[@indrajitkhandalkar1858]

Thank u sir for providing valuable knowledge... Sir plz make video on how to write in excel sheet by using selenium webdriver

[@HYRTutorials]

Thankyou 😊 Checkout the next video in this playlist. kzbin.info/aero/PLacgMXFs7kl9yyK8YqzEzXy9Zg3JE0eWW

[@amarbhairat8288]

Your explanation is very good Sir... If I want to use HashMap to store this data as a key-value pair as a First Row is Keys And remaining Rows are Value Respectively then which changes I have to do in this code??

[@HYRTutorials]

Thanks buddy 🙂 How can you create that key value pair buddy? Every row is unique ryt? may be you can consider one cell value as key and then the entire row as value.

[@pranavkhurud2291]

I'm getting [ error statuslogger log4j2 could not find a logging implementation. please add log4j-core to the classpath. using simplelogger to log to the console... ]this error msg in console what does this mean??

[@HYRTutorials]

It must be a simple warning kind of error message buddy. Which means it won't block your execution

[@TonyStark-rj3ms]

thanks bro..l loved the lecture

[@HYRTutorials]

Thankyou buddy 😊

[@ashwinjayaraj6645]

My file is in .csv format. Please suggest on the same.. Its as excel but in .csv format

[@HYRTutorials]

Try converting the csv to excel file and then perform this operation buddy

[@NAZRULISLAM-zy6qf]

It is really helpful for me. Thanks

[@HYRTutorials]

Glad to hear that

[@kshipra4504]

Is it possible to work with online excel data sheet in my selenium framework? I don't have ms office in my system.

[@HYRTutorials]

I don't think we can work with the online excel sheet. For working with excel sheet, you don't have to install the ms office.

[@BharuChowdary2020]

how to add in string n= sheet.getrow().getcell().getstring value() ;

[@HYRTutorials]

Check the TestNG playlist buddy

[@swarnasharma3836]

Very well explained..

[@HYRTutorials]

Thankyou

[@jitendraRao-bh2kr]

super sir

[@HYRTutorials]

Thanks buddy 😊

[@BharuChowdary2020]

hello brother it was useful...but i want to know how to add that converted string in data driven framework. please do reply i have a assignment today

[@HYRTutorials]

Checkout my TestNG playlist buddy

[@ktsvideos3372]

Thank you sir

[@HYRTutorials]

Welcome buddy 😊

[@rambabukallathur1413]

Hi, I'm not able to import constants in switch conditon cases please help on same

[@HYRTutorials]

I guess this is resolved ryt?

[@ramutule6321]

Hi.. I want to get the row index value. Like 1,2,3.. Etc .row numbers. Could you please tell me how to get that rown number?

[@HYRTutorials]

Get physical number of rows and then iterate the for loop

[@ERUCSE]

how to read a drop-down list in excel using selenium Apache POI

[@HYRTutorials]

Do you want to read the drop-down list from a web page or excel file?

[@gilsonsouza8159]

Hiiii, I have another question to you : ) Scenario - This is how my excel looks like: 1° column - Customer name 2° column - Customer type So there are many lines with this information! ------------------------------------------------------------------------- My automation will not need "ALL" the excel information. lets say that the automation will find the exact customer name in the web application. Then with this information (Customer name) I need to read the excel to discover the Customer type. My question is: - How can I search for the exact meet customer name in the excel (using Apache POI ore any other API). - After search, discover the index from the cell in the column 1 - And look to the same index but in the column 2 - Bring the content from the cell - column 2- and print it than I will be able to decide what to do E.g. If ( CustomerName == partner) { Send this email }Else{ Send a different email. } Can you help me with that ??? Thank you so much

[@HYRTutorials]

If you understand the concept of how to read the data from Excel then u r good to go. When u r iterating the for loop check for the exact text u want to verify. If you find it, then take that index. Just apply the logics bro.

[@gilsonsouza8159]

@@HYRTutorials Thank you so much! I am still a beginner... I didn't know that it was possible. Have a good day!!!

[@HYRTutorials]

@@gilsonsouza8159 it is possible and easy only if u put a little bit of logic.

[@aiswaryakv6843]

how can we remove or delete the row from excel sheet using apache poi?

[@HYRTutorials]

Xssfrow rowtobedeleted= Sheet.getrow(rowIndex); Sheet.removerow(rowtobedeleted);

[@rahulhirlekar6918]

if we have to choose any particular cell , or rows

[@okk-wp7yl]

getting error in numeric ,string and boolean

[@HYRTutorials]

What error buddy

[@krusharthshah3740]

Hi. While I want to import and read Excel data in Java, "XSSFSheet sheet = workbook.getSheetAt(0);" I am getting an error in this line. The error is: "The method getCell(int) is undefined for the type Object". I don´t know how to solve this error. Please help me out. Thanks in advance!!

[@HYRTutorials]

Send me the code on [e-mail]

[@tarluck]

Hi , first i'll thank you for your video, i tried to import apache hssfworkbook etc... i added the my project many JAR's file POI_...... but unfortunately , the import didin't succed, i use jdev 11g , please guide me to the solution, wich Jar do i use for this version of jdev thanks

[@HYRTutorials]

Have no idea about jdev.

[@apoorvsinha5193]

can you please tell me changes to read ODS instead of XLSX

[@HYRTutorials]

Never tried that buddy

[@SagarPatil-8411]

Sir pls give any ex where we can provide this data directly from Excel sheet

[@HYRTutorials]

There are tons of scenarios where you need to use the excel files buddy. For eg: If you want to store the test data used in test automation then you can go for excel sheet. If the data is stored in excel, you should know how to read it into the program right?

[@SagarPatil-8411]

But how to given data ? Can u make video on orange HR app. Then it's very easily for understanding

[@SagarPatil-8411]

@@HYRTutorials pls can u make

[@HYRTutorials]

@@SagarPatil-8411 can u check the TestNG playlist

[@nilamraval8224]

Hello sir, your videos are really helpful.....thank you so much...can you please make a video on the hybrid framework

[@HYRTutorials]

Thankyou buddy 😊 Sure buddy

[@RakeshSaineni]

My file doesn't have any extension like (.xlsx) I have to use filename or filename.xlsx?

[@HYRTutorials]

Go-to the properties of that excel file and you will be able to see the extension buddy

[@abhinavkumar2607]

please upload generic method for check box?

[@HYRTutorials]

You can create it easily brother. First verify whether the checkbox is checked or not and based on that perform the check or uncheck operation. Let me know if you can't figure it out.

[@abhinavkumar2607]

@@HYRTutorials can we create a reusable code for that?

[@HYRTutorials]

@@abhinavkumar2607 if you have created an extra class for all these kind of action methods then you can place this method inside that class and you can use it in your entire project.

[@shilpakothre4893]

How many ways to read input file. I am using workbookfactory with fileinput stream to read input file but it gives error that mark(int ) not supported? Why ? Reply plz

[@HYRTutorials]

You can read data in many ways but the easiest one is i have shown in this video. Did you try this method (whatever i have shown in this video)?

[@shilpakothre4893]

@@HYRTutorials what to do if i want to read both xls and xlsx format in using same java class

[@HYRTutorials]

You can use workbook & sheet instead of XSSFWorkbook/HSSFWorkbook and XSSFSheet/HSSFSheet. Here Workbook is an interface, so it will store both xssf and hssf workbooks. In the starting u need to check for the file extension and based on that extension u can create the workbook. Let me know if u r not able to do it.

[@shilpakothre4893]

@@HYRTutorials I am using same but while using my code here cell = row.getcellType(rowindex, Row.MissingCellPolicy); It gives nosuchfield create_null as blank error when we use missing cell policy.

[@shilpakothre4893]

@@HYRTutorials what will be the solution for no such field error while using missingcellpolicy. If m not using that then it gives input string =" " error

[@sandhyarani-re3cx]

hi i am getting filenotfound error ehat should i do now ?? Help me with this error

[@HYRTutorials]

Check the file path you have given buddy.

[@lakshmipriya69]

Hi Sir, Here I have one doubt.. For writing anything in the workbook means we will create the object for workbook..Here we are reading the file only..then y here we are creating the object and passing the file as parameter.. Existing excel only we are going to read then y we need to create the object and what is the use in it.. It's really confusing me sir.. Pls clarify my doubts...

[@lakshmipriya69]

Also one more doubt which I forget to mention above...Why we are convrrting numeric value into string.. Bcoz if it is numeric value we have to print as it is na.. So that is also confusing me

[@HYRTutorials]

Here we are not creating a new excel file buddy. We are creating file class instance in order to use use the file in java. If you wanted to perform any kind of operation i.e read or write or update, file instance should be created so that we can use that instance to perform the mentioned operations. Coming to your second doubt: I have shown a way to read any kind of value in the form of string. This is purely depending on our need only.

[@mc2672]

please provide the code

[@GenshinGuyy]

Hiii bro, im facing one error, please help me with this... Exception in thread "main" java.lang.NullPointerException: Cannot invoke "org.apache.poi.xssf.usermodel.XSSFRow.getCell(int)" because the return value of "org.apache.poi.xssf.usermodel.XSSFSheet.getRow(int)" is null

[@HYRTutorials]

You are trying to access the cell which is not created budddy

[@shirishmahajan419]

facing error in above code line number 14 at my end and error is "XSSFWorkBook cannot be resolved to a type" and not able able to import what should i do? ... its allow to import as HSSFWorkbook and after run its throw "Exception in thread "main" org.apache.poi.poifs.filesystem.OfficeXmlFileException"

[@HYRTutorials]

U must have missed some libraries while doing the setup. Watch the previous video for setup buddy

[@shirishmahajan419]

@@HYRTutorials Yes its working now thanks