Work with multiple sheet on Excel file

How can I call data in multiple sheet from my excel file in one script

For example: my excel file has two sheets A and B, and I need to get data from them to put in my script. However, Katalon just loads data from one sheet at once ?

Hi Nguyen,

Currently, a instance of TestData only loads one sheet that was specified in your Test Data.

To deal with your case, we can do this:

ExcelData myData = (ExcelData) ObjectRepository.findTestData(“your_test_data_id”)

def dataOfSheetA = myData .changeSheet(“sheet_A”).getAllData()

def dataOfSheetB = myData .changeSheet(“sheet_B”).getAllData()

To list all sheet names of a test data, please try:

myData.getSheetNames() (String)

Please remember that ExcelData is an implementation of TestData and only works for Excel file.

Hopefully, my sharing will help you.

1 Like

DO we have any updates of have mutiple sheets ?

How can I call data which is in multiple sheets from my excel file(testData file) in one script??

duyluong i tried your code ,ObjectRepository is not showing me findTestData class

ExcelData myData = (ExcelData) findTestData(“your_test_data_id”)
myData .changeSheet(“sheet_B”)
And now you can work with sheet_B but instead of using “findTestData(‘your_test_data_id’).” you will use “myData.”.
If you want to go back to Sheet_A : myData .changeSheet(“sheet_A”)

In Katalon 5.7.0 version you can select, at the Data File (if Excel File), the sheet you want to use for the Data File. Unfortunatelly, we cannot select the desired specific sheet directly at the Variable Binding.

You just need to create multiple instances of same file in the ‘Data Files’ folder and keep every sheet as default for that instance. I am giving you example of 2 sheets I have, one sheet is ProducerPro and another is Contract.

Call them like this:

  1. WebUI.setText(findTestObject(‘Page_PM - Management/input_Agent Id_Producer_Produc’), findTestData(‘Data Files/DCMS_ProducerPro_New’).getValue(
    ‘AgentID’, row))

  2. WebUI.setText(findTestObject(‘Page_PM - Management/input_Agent Id_Producer_Produc’), findTestData(‘Data Files/DCMS_Contract_New’).getValue(
    ‘AgentID’, row))

Hi Nguyen,

You can refer to this link:

Hope that helps you. . . :slight_smile:

1 Like

Hi,
How I can read multiple sheets present in an excel in single test case and in single test suit.

  1. I have an excel file which contains 2 sheets “Sheet1” and “Sheet2”. I have created a test suit which contains only one test case and for that I want to bind the excel data with the variables using this excel.
  2. But when I try to bind the data, it shows me only “Sheet1” columns and not of “Sheet2”.
  3. I want to bind the variable with these 2 sheet’s columns. Like this: Variable1-Sheet1(Column1), Variable2-Sheet1(Column2), Variable3-Sheet2 (Column1), Variable4-Sheet2 (Column2) etc.

So how I can achieve this in Katalon 7.3.0?