Loop rows in Exel sheet

Name Age
Person 1      20
Person 2 21
Person 3 22
Person 4 23

How to loop this exel sheet data in katalon
I have to fetch each Names and corresponding Ages using katalon
How i can implement this in katalon

Hi Test,

You can try this code:

import com.kms.katalon.core.testdata.reader.ExcelFactory

//third parameter means if you want the first row as your header or column name.
//In your case, it should be true.
Object excelFile = ExcelFactory.getExcelDataWithDefaultSheet(“your_excelFile_location.xlsx”, “SheetName”, true)

//create a loop
for (int index = 1; index < excelFile.size(); index++)
{
println excelFile.getValue(“Name”, index) + " " + excelFile.getValue(“Age”, index)
}

Check the output in the console.

Hope that helps… :slight_smile:

Hi Arnel I created a variable XX and I bind it with the column Name in the exel sheet and my for loop is as follows, but I am getting only one row of exel file, looping is not working,

for (int i = 1; i <= 2; i++) {

WebUI.setText(findTestObject('Object Repository/New Folder/Page\_test/textarea\_Kindly fill the form'), XX)

}

and I tried the following code also

for (int i = 1; i <= 2; i++) {

WebUI.setText(findTestObject('Object Repository/New Folder/Pagetest/textarea_Kindly fill the form'), XX, i)

}

but got the error

Hi Test,

Do the code I provided.

As I can see in your code, you didn’t define the excelFile and the index of it.

You want to deal with excel file with data in multiple rows then use the XlsReader class available on the internet. Import the jars include with it . Create the object of this class and pass the excel file path and call the getCell Value and SetCell value, getRowCount… etc.

It will be convenient for every situation.

Hi Abhishek Kumar Gupta
Instead of adding any external jar file or third party files can I deal with excel file with data in multiple rows using katalon inbuild methods or functionalities?

Hi Test,

You can deal with that.

Let me provide you some link you can refer to:


Hope that helps. . :slight_smile:

Hi @Arnel can you help me solve my issue here:
This is the result


and this is the expected result, Ijust manually put Server Name , Read and Write as label for those colums

this is my code for keyword:


thank you so much

Hi @rainzetroc,

Could you elaborate what are you trying to achieve here?? Explain the scenario or something??

Thanks.

1 Like

Hi @Arnel I dont know how to elaborate it well. but im doing case to case testing like this is the only details i need from appdynamics:
WebUI.setText(findTestObject(‘Page_Databases - AppDynamics/input_Sort_ads-search-box-input pull-left n_5d09ff’), serverName)

CustomKeywords.‘excel.exportedData.getBrowerFile’(‘C:\Users\laarni.s.cortez\Katalon Studio\AppDynamics7\Excel FIles\customKeywords.xlsx’,
‘Sheet1’)

CustomKeywords.‘excel.exportedData.writeExcel’(1, 0, serverName)

WebUI.click(findTestObject(‘Page_Databases - AppDynamics/div_2764_SCOM_VRTVD25752_PRD’))

not_run: WebUI.click(findTestObject(‘Page_2764_SCOM_VRTVD25752_PRD - AppDynamics/OpenTheSearchServer’))

Result = WebUI.getText(findTestObject(‘Object Repository/Page_2764_SCOM_VRTVD25752_PRD - AppDynamics/div_14140 KBsec’))

println(Result)

CustomKeywords.‘excel.exportedData.getBrowerFile’(‘C:\Users\laarni.s.cortez\Katalon Studio\AppDynamics7\Excel FIles\customKeywords.xlsx’,
‘Sheet1’)

CustomKeywords.‘excel.exportedData.writeExcel’(1, 1, Result)

Result0 = WebUI.getText(findTestObject(‘Object Repository/Page_2764_SCOM_VRTVD25752_PRD - AppDynamics/div_68330 KBsec’))

println(Result0)

CustomKeywords.‘excel.exportedData.getBrowerFile’(‘C:\Users\laarni.s.cortez\Katalon Studio\AppDynamics7\Excel FIles\customKeywords.xlsx’,
‘Sheet1’)

CustomKeywords.‘excel.exportedData.writeExcel’(1, 2, Result0)


and i have total of 4 total servers need to lookup into appdynamics. this is actually just part of the end of day report im trying to automate. i have to access appdynamics, three splunk dashboards, and service now. i cant find way to automate the gathering of those data so i comeup with i will just write the get text into excel file.

Hi @rainzetroc ,

Are you trying to write the data in your appdynamics into your excel?? like are you trying to loop it? since I notice your code is repeating its steps. Do you encounter any errors? … correct me if I’m wrong.

Thanks.