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


#1

Hi there,

I’m going to share something that might help you when it comes to automation which involves excel file as your test data and defining the “SHEETS” you prefer to make your test data even more dynamic.

We have a class named ExcelData but it doesn’t allow us to define what sheet the automation will penetrate. See this link for the ExcelData class for your reference: https://api-docs.katalon.com/index.html?overview-summary.html

Meaning the ExcelData is only good for one sheet which is the default sheet. And ExcelData is an abstract class so we can’t create an instance of that, unlike CSVData and DBData. So, in order to do that, you can refer to the sample code provided below:

//import excel factory first
import com.kms.katalon.core.testdata.reader.ExcelFactory

//create an instance. . . .
//the last parameter (boolean) allows your first row to become a column name so that it //will be easy for you to define which column you are accessing. If false, it will be //counted as an excel record. And you don’t have any identification which column you //want to get. So I suggest making it true (it depends on your test).
Object excelData = ExcelFactory.getExcelDataWithDefaultSheet(“location_of_your_excelFile”, “sheetName”, true)

//define your excel values
excelData.getRowNumbers()
excelData.getValue(“columnName”, row)
excelData.getSheetNames()
excelData.getColumnNames()
excelData.getColumnNumbers()
. . . . and more.

Hope this helps. . . . :slight_smile:


Work with multiple sheet on Excel file
Read data from excel file or csv in Custom keyword
Excel data fetch in katalon
How to read values from another sheet in Test data Excel sheet
How to read within a Data File and used one cell value as comparison in a condition
How to achieve passing multiple values from a data sheet to a Cucumber feature file
Read Test Data from Excel
#2

Arnel said:

Hi there,

I’m going to share something that might help you when it comes to automation which involves excel file as your test data and defining the “SHEETS” you prefer to make your test data even more dynamic.

We have a class named ExcelData but it doesn’t allow us to define what sheet the automation will penetrate. See this link for the ExcelData class for your reference: https://api-docs.katalon.com/index.html?overview-summary.html

Meaning the ExcelData is only good for one sheet which is the default sheet. And ExcelData is an abstract class so we can’t create an instance of that, unlike CSVData and DBData. So, in order to do that, you can refer to the sample code provided below:

//import excel factory first
import com.kms.katalon.core.testdata.reader.ExcelFactory

//create an instance. . . .
//the last parameter (boolean) allows your first row to become a column name so that it //will be easy for you to define which column you are accessing. If false, it will be //counted as an excel record. And you don’t have any identification which column you //want to get. So I suggest making it true (it depends on your test).
Object excelData = ExcelFactory.getExcelDataWithDefaultSheet(“location_of_your_excelFile”, “sheetName”, true)

//define your excel values
excelData.getRowNumbers()
excelData.getValue(“columnName”, row)
excelData.getSheetNames()
excelData.getColumnNames()
excelData.getColumnNumbers()
. . . . and more.

Hope this helps. . . . :slight_smile:

Hi there, you can define which worksheet you want to work with when you create a test data file, which seems easier than this, but that does have the limitation of needing multiple data files for more than one worksheet.

Thanks for your tip, could be useful!


#3

Hi Anthony,

Yes, I got your point but the purpose of that is when you have a moving test data, meaning your data or values in your test data changes while your automation is running. That happens when there’s a third party application involved (like .vbs, . exe, etc. . .) which is updating your test data. This approach allows you to load your test data programmatically. Because by default when you run your automation the test suite will only load what’s inside the test data and it will not refresh if there are any changes. You need to re-run the automation to get that updated value. Sorry, I missed to include that in my introduction. But thanks anyway :slight_smile:


#4

Arnel said:

Hi there,

I’m going to share something that might help you when it comes to automation which involves excel file as your test data and defining the “SHEETS” you prefer to make your test data even more dynamic.

We have a class named ExcelData but it doesn’t allow us to define what sheet the automation will penetrate. See this link for the ExcelData class for your reference: https://api-docs.katalon.com/index.html?overview-summary.html

Meaning the ExcelData is only good for one sheet which is the default sheet. And ExcelData is an abstract class so we can’t create an instance of that, unlike CSVData and DBData. So, in order to do that, you can refer to the sample code provided below:

//import excel factory first
import com.kms.katalon.core.testdata.reader.ExcelFactory

//create an instance. . . .
//the last parameter (boolean) allows your first row to become a column name so that it //will be easy for you to define which column you are accessing. If false, it will be //counted as an excel record. And you don’t have any identification which column you //want to get. So I suggest making it true (it depends on your test).
Object excelData = ExcelFactory.getExcelDataWithDefaultSheet(“location_of_your_excelFile”, “sheetName”, true)

//define your excel values
excelData.getRowNumbers()
excelData.getValue(“columnName”, row)
excelData.getSheetNames()
excelData.getColumnNames()
excelData.getColumnNumbers()
. . . . and more.

Hope this helps. . . . :slight_smile:

Hi Arnel,

will this work for switching the sheets of test data at run time while the test case is getting executed??


#5

Hi Shweta,

Yes, it will just locate your excel file and set your preferred sheet name in the parameter.


#6

Hi Arnel,

I have the excel sheet of name TD1 and have saved the test data with name testdata (1), in

Object excelData = ExcelFactory.getExcelDataWithDefaultSheet(“location_of_your_excelFile”, “sheetName”, true)

what path should I define?? should it be of Source file or test data??

please help… I have given the path of excel sheet [source file] but getting error…file is unsupported


#7

One more thing…

I have to change the sheet in the same test case for test data so do I have to create another object of Exceldata…???

If yes, How…?? if no… then how should I do it… Actually I am new to automation… so there are a lot in my plate to learn…

It would be great if you can help me…
Thanks!


#8

Hi Shweta,

You can go directly to the location of your excel file like this:

//define the excel path location
Object excelData = ExcelFactory.getExcelDataWithDefaultSheet(“C:\temp\testdata.xlsx”, “TD1”, true)


#9

In changing the sheet you can insert that in a loop.

for example you have 5 sheets:
for(int sheet = 1; data <= 5; sheet++)
{
Object excelData = ExcelFactory.getExcelDataWithDefaultSheet(“C:\temp\testdata.xlsx”, “TD” + sheet, true)
}


#10

Used Code -

Object excelData1 = ExcelFactory.getExcelDataWithDefaultSheet(“C:\\Users\\Shweta Sao\\Downloads”, “TD1”, true)

Still getting Error 1 :frowning:

java.lang.IllegalArgumentException: File is unsupported: C:\Users\Shweta Sao\Downloads08-03-2018 11:48:23 AM - [END] - End action : Statement - e.printStackTrace()

at com.kms.katalon.core.testdata.reader.ExcelFactory.getExcelData(ExcelFactory.java:41)

at com.kms.katalon.core.testdata.reader.ExcelFactory.getExcelDataWithDefaultSheet(ExcelFactory.java:17)

at com.kms.katalon.core.testdata.reader.ExcelFactory$getExcelDataWithDefaultSheet.call(Unknown Source)

at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:48)

at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:113)

at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:141)

at Script1533236924988.run(Script1533236924988.groovy:71)

at com.kms.katalon.core.main.ScriptEngine.run(ScriptEngine.java:183)

at com.kms.katalon.core.main.ScriptEngine.runScriptAsRawText(ScriptEngine.java:108)

at com.kms.katalon.core.main.TestCaseExecutor.runScript(TestCaseExecutor.java:294)

at com.kms.katalon.core.main.TestCaseExecutor.doExecute(TestCaseExecutor.java:285)

at com.kms.katalon.core.main.TestCaseExecutor.processExecutionPhase(TestCaseExecutor.java:264)

at com.kms.katalon.core.main.TestCaseExecutor.accessMainPhase(TestCaseExecutor.java:256)

at com.kms.katalon.core.main.TestCaseExecutor.execute(TestCaseExecutor.java:200)

at com.kms.katalon.core.main.TestCaseMain.runTestCase(TestCaseMain.java:98)

at com.kms.katalon.core.main.TestCaseMain.runTestCase(TestCaseMain.java:89)

at com.kms.katalon.core.main.TestCaseMain$runTestCase$0.call(Unknown Source)

at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:48)

at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:113)

at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:149)

at TempTestCase1533277026452.run(TempTestCase1533277026452.groovy:20)

at groovy.lang.GroovyShell.runScriptOrMainOrTestOrRunnable(GroovyShell.java:263)

at groovy.lang.GroovyShell.run(GroovyShell.java:518)

at groovy.lang.GroovyShell.run(GroovyShell.java:507)

at groovy.ui.GroovyMain.processOnce(GroovyMain.java:653)

at groovy.ui.GroovyMain.run(GroovyMain.java:384)

at groovy.ui.GroovyMain.process(GroovyMain.java:370)

at groovy.ui.GroovyMain.processArgs(GroovyMain.java:129)

at groovy.ui.GroovyMain.main(GroovyMain.java:109)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at org.codehaus.groovy.tools.GroovyStarter.rootLoader(GroovyStarter.java:109)

at org.codehaus.groovy.tools.GroovyStarter.main(GroovyStarter.java:131)


#11

You have to include the .xlsx file. It should be the full path like the code I provided above :slight_smile:


#12

Oh yes…silly me… got that… thanks…!


#13

When I am setting text in input field then should I use below mentioned code?? using same sheet…TD1

Mobile.setText(findTestObject(‘Object Repository/CardNumberInputField’), excelData1.getValue(“Card Number”, 1), 5, FailureHandling.STOP_ON_FAILURE)


#14

Yes. . .


#15

Getting this Error…

java.lang.NullPointerException

at com.kms.katalon.core.testdata.reader.SheetPOI.getRowNumbers(SheetPOI.java:296)

at com.kms.katalon.core.testdata.AbstractTestData.verifyRowIndex(AbstractTestData.java:47)

at com.kms.katalon.core.testdata.AbstractTestData.getObjectValue(AbstractTestData.java:130)

at com.kms.katalon.core.testdata.AbstractTestData.getValue(AbstractTestData.java:102)

at com.kms.katalon.core.testdata.TestData$getValue.call(Unknown Source)

at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:48)

at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:113)

at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:133)

at Script1533236924988.run(Script1533236924988.groovy:75)

at com.kms.katalon.core.main.ScriptEngine.run(ScriptEngine.java:183)

at com.kms.katalon.core.main.ScriptEngine.runScriptAsRawText(ScriptEngine.java:108)

at com.kms.katalon.core.main.TestCaseExecutor.runScript(TestCaseExecutor.java:294)

at com.kms.katalon.core.main.TestCaseExecutor.doExecute(TestCaseExecutor.java:285)

at com.kms.katalon.core.main.TestCaseExecutor.processExecutionPhase(TestCaseExecutor.java:264)

at com.kms.katalon.core.main.TestCaseExecutor.accessMainPhase(TestCaseExecutor.java:256)

at com.kms.katalon.core.main.TestCaseExecutor.execute(TestCaseExecutor.java:200)

at com.kms.katalon.core.main.TestCaseMain.runTestCase(TestCaseMain.java:98)

at com.kms.katalon.core.main.TestCaseMain.runTestCase(TestCaseMain.java:89)

at com.kms.katalon.core.main.TestCaseMain$runTestCase$0.call(Unknown Source)

at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:48)

at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:113)

at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:149)

at TempTestCase1533277835725.run(TempTestCase1533277835725.groovy:20)

at groovy.lang.GroovyShell.runScriptOrMainOrTestOrRunnable(GroovyShell.java:263)

at groovy.lang.GroovyShell.run(GroovyShell.java:518)

at groovy.lang.GroovyShell.run(GroovyShell.java:507)

at groovy.ui.GroovyMain.processOnce(GroovyMain.java:653)

at groovy.ui.GroovyMain.run(GroovyMain.java:384)

at groovy.ui.GroovyMain.process(GroovyMain.java:370)

at groovy.ui.GroovyMain.processArgs(GroovyMain.java:129)

at groovy.ui.GroovyMain.main(GroovyMain.java:109)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at org.codehaus.groovy.tools.GroovyStarter.rootLoader(GroovyStarter.java:109)

at org.codehaus.groovy.tools.GroovyStarter.main(GroovyStarter.java:131)


#16

Can you provide your code, please? thanks


#17

Will this do…??

Mobile.verifyElementVisible(findTestObject(‘Object Repository/AddCard_CardNumberLabel’), 5, FailureHandling.STOP_ON_FAILURE)

Object excelData1 = ExcelFactory.getExcelDataWithDefaultSheet(“C:\\Users\\Shweta Sao\\Downloads\\TD1.xlsx”, “TD1”, true)

Mobile.tap(findTestObject(‘Object Repository/AddCard_CardNumberInputField’), 5, FailureHandling.STOP_ON_FAILURE)

Mobile.setText(findTestObject(‘Object Repository/AddCard_CardNumberInputField’), excelData1.getValue(“Card Number”, 1) , 5, FailureHandling.STOP_ON_FAILURE)

Mobile.hideKeyboard()


#18

I don’t see anything wrong with your code. Maybe your other codes cause that error.


#19

Hello Arnel,

I am able to find “com.kms.katalon.core.testdata” in katalon studio but after that I am not able to find .reader.ExcelFactory.

Please help me to find it. I am new to katalon studio

Thanks


#20

Hi Umakant,

What do you mean by you are not able to find the .reader.ExcelFactory?
You don’t need to add external libraries to call it.

//just type it
import com.kms.katalon.core.testdata.reader.ExcelFactory

Object excel = ExcelFactory.getExcelDataWithDefaultSheet("", "", false)

Kindly try the whole code…

Cheers,