How to read and Write Data into Excel sheet in Katalon

Description:
In this video we will see
1. How to solve common problems such as
i)Cannot invoke method setCellValue() on null object
ii)Cannot invoke method getCell() on null object
while handling data in Excel in Katalon Studio
2. How to read data from excel sheet in katalon Studio using Katalon Keyword and also using Java classes
3. How to Write data into excel sheet in Katalon Studio
########/#/Relevant Videos########
1. How to migrate a project from selenium to katalon studio
• How to migrate a proje...
2. Page Object Model/POM implementation in katalon
• Page Object Model/POM ...
3. Improve the execution performance in katalon- Method#1
• Improve the execution ...
• Improve the execution ...
###############################

[@_PhamHongThang]

"Nice" Video :))

[@_PhamHongThang]

very very "NICE" :))

[@testtube-tkf]

thank you phamhongthang

[@maharaja3009]

Thank you so much for the video. Do we need to load any jar to support the xlsx read and write functionality.

[@testtube-tkf]

you don't have to load any jars separately, they are already part of katalon library. when you type XSSFWorkbook, xssfsheet classes/it's methods you can right click on that text and select source>organize imports and it will load the relevant katalon/selenium libraries. In this case it will load the below apache poi libraries when you "organize imports" ..... import org.apache.poi.xssf.usermodel.XSSFSheet import org.apache.poi.xssf.usermodel.XSSFWorkbook

[@bhavpatel2196]

Thanks for such informative video I'm trying to use excel data, could you tell me how can I avoid using duplicate data plz? So basically if I used coloum 1 (username) and coloum 2 (password) I want to avoid same row next time and want katalon to pick next username and password ( not the one used before) plz. Hope that make sense.

[@aseplamri2290]

If you want me to try, I get an error, why?

[@gamingragnarok1945]

How to read the data on the different sheet? I got 3 data sheet on excel i want to get the sheet1 value row 1 ,column 1 and the sheet2 value row 1 and column 1. How can i be specific in reading the value on the different sheet?

[@testtube-tkf]

Many ways to do this. 1) if you want to use katalon keyword findTestData() to achieve this refer to 5:00 - 6:00 timeline. You can see that orangeHRM.xlsx excel having multiple sheets sheet1(login),sheet2(add) which gives an option to choose. All you have to do is create different katalon data sheets like add,login in this case and map it to whichever sheet you want in the given excel file. 2) As explained in 11:40 - 15:30 you can use workbook.getSheetAT(sheetno.) method to choose different sheets in the same excel file and then read the data. Hope this helps........

[@Focus-Automation]

How to skip a row, based on the COLUMN name - Execute with value yes or no. Thanks.

[@testtube-tkf]

There are many ways to do this and the following is just one of many ways............ ####################### your code if(sheet.getRow(rowno).getCell(columnno).getStringCellValue()=="no") rowno=rowno+1 else rowno=rowno your code ######################

[@Focus-Automation]

@@testtube-tkf i.stack.imgur.com/5scf7.png How to handle this using Kataton inbuilt methods.

[@testtube-tkf]

@@Focus-Automation "findTestData" is katalon inbuilt keyword that you can use and there are many ways to do this and following is just one way of doing it........ And more over you don't have to make an excel sheet which will decide a test to be executed in Katalon, you just have to create a testsuite with the test cases you wanted to run whenever you need. ##################################################### if(findTestData(dataPath).getValue('To Be Executed', testrow)=="No") // Do Nothing else // write the code for your test execution ######################################

[@Supercalifragilisticexpi-wq9bx]

audio is very very less

[@testtube-tkf]

Yeah, the old videos has less volume. Sorry for that