Loop rows in Exel sheet

[test19]

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

[Arnel]

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…

[test19]

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

[test19]

[Arnel]

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.

[Abhishek_Kumar_Gupta]

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.

[test19]

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?

[Arnel]

Hi Test,

You can deal with that.

Let me provide you some link you can refer to:

(TEST DATA) Define specific Excel Sheets to get Excel Values

Loading test data file programmatically (Dynamic Test Data)

Hope that helps. .

[rainzetroc]

@Arnel

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

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

[Arnel]

Hi @rainzetroc,

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

Thanks.

[rainzetroc]

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.

[Arnel]

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.

[jpereyra]

Hi Arnel,

I know this is an old post, your proposed solution would be useful for me, but seems that attribute “size” doesn’t work anymore. Do you know if it is deprecated? what can we use instead?

Thanks,
Ruben B

[kazurayam]

ExcelFactory.getExcelDataWithDefaultSheet()" returns an instance of com.katalon.core.testdata.ExcelData. You can read its API doc :
https://docs.katalon.com/javadoc/com/kms/katalon/core/testdata/ExcelData.html

As you can see, there is no size() method or the equivalent supported.

I do not know if @Arnel 's code worked years ago or not. At least it does not work in the recent versions (v8.2.0) of Katalon Studio.

---

People may suggest https://store.katalon.com/product/34/Excel-Keywords .

This keyword is good if you want to write data into an Excel file. However I think this keyword is too difficult to use If you just want read-only access to an Excel file.

I checked it and found that the API of this plugin does not provide the “size()” or its similar either. I know the reason why. All of libraries in Java that deals with Excel depend on Apache POI, which does not provide the “size()”. It is because of the special nature of Excel’s data model.

A text file naturally has the maximum number of lines. On the other hand, a spreadsheet is not like a text file. A spreadsheet has conceptually limitless size of rows and columns. A spreadsheet model does not give you the numbers of rows. Therefore the API of Apache POI (and Katalon’s plugin) is not intuitive for people who expects an Excel sheet to be something like a text file.

[kazurayam]

See this:

GitHub - kazurayam/OliverHowardsDataDrivenTestingTutorial

I think you would find the following code interestiing:

kazurayam/OliverHowardsDataDrivenTestingTutorial/blob/master/Scripts/TC2_loopOverTestData/Script1567483355864.groovy

TestData data = findTestData("Demo_Account")

for (def index : (1..data.getRowNumbers())) {
...

The getRowNmbers() method here is something like size() in Armel’s code.

Katalon’s “Data-driven testing” feature wraps the POI API and provide a psuedo read-only view as if an Excel sheet to be a bunch of rows. com.kms.katalon.core.testdata.TestData class implements getRowsNumbers(), which is simiilar to the “size()” method.