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

Arnel · July 30, 2018, 5:25am

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: Index (Katalon Studio API Specification)

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. . . .

Anthony · July 30, 2018, 7:57pm

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: Index (Katalon Studio API Specification)

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. . . .

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!

Arnel · July 31, 2018, 3:09am

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

Shweta_sao · August 2, 2018, 1:40pm

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: Index (Katalon Studio API Specification)

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. . . .

Hi Arnel,

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

Arnel · August 3, 2018, 3:05am

Hi Shweta,

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

Shweta_sao · August 3, 2018, 6:09am

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

Shweta_sao · August 3, 2018, 6:12am

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!

Arnel · August 3, 2018, 6:12am

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)

Arnel · August 3, 2018, 6:21am

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)
}

Shweta_sao · August 3, 2018, 6:21am

Used Code -

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

Still getting Error 1

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)

Arnel · August 3, 2018, 6:25am

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

Shweta_sao · August 3, 2018, 6:30am

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

Shweta_sao · August 3, 2018, 6:34am

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)

Arnel · August 3, 2018, 6:40am

Yes. . .

Shweta_sao · August 3, 2018, 6:43am

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)