Read Write Excel Custom Keywords

I’m running into this issue today (11/21) has the “fix” been released, if so how do I update my plugin code? Thank you for your help.

Please click Reload plugins - the version should be 1.0.2. If you still encounter issue please let me know.

Thank you for the quick reply. I’m still getting the error, I have reloaded the plugins and it does show the correct version. I’m just running the example tests (which used to pass) and I’m getting the following error reported:

11-22-2019 06:06:27 AM Test Cases/VerifyExcelGetData

Elapsed time: 3.828s

com.kms.katalon.keyword.excel.ExcelKeywords.getColumnsByIndex:690

Test Cases/VerifyExcelGetData FAILED.
Reason:
java.lang.NoSuchFieldError: CREATE_NULL_AS_BLANK
at com.kms.katalon.keyword.excel.ExcelKeywords.getColumnsByIndex(ExcelKeywords.groovy:690)
at com.kms.katalon.keyword.excel.ExcelKeywords$getColumnsByIndex$6.call(Unknown Source)
at VerifyExcelGetData.run(VerifyExcelGetData:48)
at com.kms.katalon.core.main.ScriptEngine.run(ScriptEngine.java:194)
at com.kms.katalon.core.main.ScriptEngine.runScriptAsRawText(ScriptEngine.java:119)
at com.kms.katalon.core.main.TestCaseExecutor.runScript(TestCaseExecutor.java:337)
at com.kms.katalon.core.main.TestCaseExecutor.doExecute(TestCaseExecutor.java:328)
at com.kms.katalon.core.main.TestCaseExecutor.processExecutionPhase(TestCaseExecutor.java:307)
at com.kms.katalon.core.main.TestCaseExecutor.accessMainPhase(TestCaseExecutor.java:299)
at com.kms.katalon.core.main.TestCaseExecutor.execute(TestCaseExecutor.java:233)
at com.kms.katalon.core.main.TestCaseMain.runTestCase(TestCaseMain.java:114)
at com.kms.katalon.core.main.TestCaseMain.runTestCase(TestCaseMain.java:105)
at com.kms.katalon.core.main.TestCaseMain$runTestCase$0.call(Unknown Source)
at TempTestCase1574420781332.run(TempTestCase1574420781332.groovy:23)

What I need to do is update a specific cell in an excel file and I get basically the exact same error. Last night I updated Katalon, I copied back down the sample project. I removed and re-added the jar file from the newly downloaded project. None of this seems to make a difference for me.

I just looked and in my ExcelKeywords.groovy file there are still the three references to CREATE_NULL_AS_BLANK, this is from the sample project download. Maybe I’m just confused about how all this works. My plugin says it’s up to date in Katalon, but part of the setup was to download the sample project and import the jar file. Have these steps changed? The plugin is working for many of the commands, but a few of them are crashing now. I’m just now getting around to needing to use this plugin and of course now is when issues have been introduced. Any help resolving this is greatly appreciated.

Hi @Steven_Taylor

We just updated the sample project, can you get it and try it again to see if the problem persists.

1 Like

I will let you know. I did just end up creating my own custom keyword based on the ExcelKeywords.groovy file and that seems to work.

Ok I downloaded the sample project, imported the updated jar, everything is working for me now! Well, at least in the sample files, I’ll go give my own project another try now, hopefully all is well now. Thank you very much for your help.

1 Like

Hello,
Just wanted to check if we can automate the excel macros validations?
I tried a sample but it doesn’t work in Katalon.
Example;For ID I need to enter numbers and when we enter other characters it pops up with an error as please enter numbers only.
But in Katalon when I tried I was able to enter the characters in the excel file.
Please pour in your suggestions.
Thanks.

I got the Excel Keywords Plugin and am able to run the code. But getting an error when I try to setValueToCellByIndex. I am getting the error attached. I am not sure if I am missing any references or files to set values. I am able to get cell values without issues. The cell is blank in spreadsheet and should be set with a value during runtime.

String accexcel=“C:\Katalon Studio\SDWP_FlatIron Certification.xlsx”

workbook1 = ExcelKeywords.getWorkbook(accexcel)

sheet1 = ExcelKeywords.getExcelSheet(workbook1, “ISA Bulk Certification - SDWP”)
String ContractNumber = “1000012461”
String fieldvalue = ExcelKeywords.getCellValueByIndex(sheet1, 7, 7)

ExcelKeywords.setValueToCellByIndex(sheet1, 2, 2, ‘abc’) // I tried “abc”, but that didnt work either
ExcelKeywords.saveWorkbook(accexcel, workbook1)

@mridula.palivela

Possibly you need to upgrade your Katalon Studio to ver7.1.2

See

Reply
|50pxx50px
Noor Yeaser Khan
December 24, 2019

Updating to version 7.1.2 resolved this issue. Thanks a bunch! :smiley:

at https://store.katalon.com/product/34/Excel-Keywords#rating-content

1 Like

Thanks so much for finding this article @kazurayam!!! Upgrading to KS 7.2.1 worked like a charm! I was so worried that maybe some of the setup and other stuff will be lost, but it is all there. Hoping that it works now with test suite data binding run too.

@kazurayam hope you can help again…

Test Listener code -

import static com.kms.katalon.core.checkpoint.CheckpointFactory.findCheckpoint
import static com.kms.katalon.core.testcase.TestCaseFactory.findTestCase
import static com.kms.katalon.core.testdata.TestDataFactory.findTestData
import static com.kms.katalon.core.testobject.ObjectRepository.findTestObject

import com.kms.katalon.core.checkpoint.Checkpoint as Checkpoint
import com.kms.katalon.core.model.FailureHandling as FailureHandling
import com.kms.katalon.core.testcase.TestCase as TestCase
import com.kms.katalon.core.testdata.TestData as TestData
import com.kms.katalon.core.testobject.TestObject as TestObject

import com.kms.katalon.core.webservice.keyword.WSBuiltInKeywords as WS
import com.kms.katalon.core.webui.keyword.WebUiBuiltInKeywords as WebUI
import com.kms.katalon.core.mobile.keyword.MobileBuiltInKeywords as Mobile

import internal.GlobalVariable as GlobalVariable

import com.kms.katalon.core.annotation.BeforeTestCase
import com.kms.katalon.core.annotation.BeforeTestSuite
import com.kms.katalon.core.annotation.AfterTestCase
import com.kms.katalon.core.annotation.AfterTestSuite
import com.kms.katalon.core.context.TestCaseContext
import com.kms.katalon.core.context.TestSuiteContext

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.kms.katalon.keyword.excel.ExcelKeywords

class TestListener_CertifyStudents {
/**
* Executes before every test case starts.
* @param testCaseContext related information of the executed test case.
*/
@BeforeTestCase
def sampleBeforeTestCase(TestCaseContext testCaseContext) {
println testCaseContext.getTestCaseId()
println testCaseContext.getTestCaseVariables()
}

/**
 * Executes after every test case ends.
 * @param testCaseContext related information of the executed test case.
 */
@AfterTestCase
def sampleAfterTestCase(TestCaseContext testCaseContext) {
	println testCaseContext.getTestCaseId()
	println testCaseContext.getTestCaseStatus()
	
	if (testCaseContext.getTestCaseId().indexOf('CertifyStudents') > 0 ) {
		String accexcel='C:\\Katalon Studio\\SDWP_FlatIron Certification.xlsx'
		if(testCaseContext.getTestCaseStatus() == 'FAILED'){
			// if test case fails write to the excel
			String PassFail = 'Fail'
			
			workbook1 = ExcelKeywords.getWorkbook(accexcel)			
			sheet1 = ExcelKeywords.getExcelSheet(workbook1, "ISA Bulk Certification - SDWP")
			
			String ContractNumber = "1000012461"
			String fieldvalue = ExcelKeywords.getCellValueByIndex(sheet1, 7, 7)
		
			ExcelKeywords.setValueToCellByIndex(sheet1, 4, 2, ContractNumber)
			ExcelKeywords.saveWorkbook(accexcel, workbook1)			//CustomKeywords.'myPack.WriteExcel.demoKey'(PassFail, 'PassFail')
						
		}
	}
	
}

}

Error after going into the Test listener and trying to get to the workbook using ExcelKeywords
2020-01-28 15:25:28.599 ERROR c.k.k.core.context.internal.TestHooker - :x: groovy.lang.MissingPropertyException: No such property: workbook1 for class: TestListener_CertifyStudents
at TestListener_CertifyStudents.sampleAfterTestCase(TestListener_CertifyStudents.groovy:57)
at TestListener_CertifyStudents.invokeMethod(TestListener_CertifyStudents.groovy)
at com.kms.katalon.core.context.internal.TestHooker.invokeMethod(TestHooker.java:111)
at com.kms.katalon.core.context.internal.TestHooker.lambda$3(TestHooker.java:95)
at com.kms.katalon.core.context.internal.TestHooker.invokeContextMethods(TestHooker.java:94)
at com.kms.katalon.core.context.internal.TestListenerCollector.lambda$3(TestListenerCollector.java:88)
at com.kms.katalon.core.context.internal.TestListenerCollector.handleListenerEvent(TestListenerCollector.java:85)
at com.kms.katalon.core.context.internal.ExecutionEventManager.publicEvent(ExecutionEventManager.java:36)
at com.kms.katalon.core.main.TestCaseExecutor.execute(TestCaseExecutor.java:249)
at com.kms.katalon.core.main.TestSuiteExecutor.accessTestCaseMainPhase(TestSuiteExecutor.java:169)
at com.kms.katalon.core.main.TestSuiteExecutor.accessTestSuiteMainPhase(TestSuiteExecutor.java:142)
at com.kms.katalon.core.main.TestSuiteExecutor.execute(TestSuiteExecutor.java:91)
at com.kms.katalon.core.main.TestCaseMain.startTestSuite(TestCaseMain.java:157)
at com.kms.katalon.core.main.TestCaseMain$startTestSuite$0.call(Unknown Source)
at TempTestSuite1580242998238.run(TempTestSuite1580242998238.groovy:36)

Your code does not declare the type of the name workbook1, therefore Groovy assumes that workbook1 should be a property of the TestListener_CertifyStudents class; but there is no such property declared. Therefore Groovy throws an Exception.

You should declare workbook1 as a local variable in the sampleAfterTestCase() method with explicit type. ExcelKeywords.getWorkbook(String) returns an instace of org.apache.poi.ss.usermodel.Workbook.

So

...
import org.apache.poi.ss.usermodel.Workbook
...
        Workbook workbook1 = ExcelKeywords.getWorkbook(accexcel)	

Or simply,

        def workbook1 = 

will do as well.

See https://plugin-docs.katalon.com/docs/excel-custom-keywords/com/kms/katalon/keyword/excel/ExcelKeywords.html

1 Like

Sorry did not see your post till now, but figured out yesterday that I had to define it. So used Object workbook1 and it worked. But now I will change it to the code you gave and add the import. Thanks so much @kazurayam for helping me out!

1 Like

Hi all, i am getting the same issue, upraded to 7.2.1.

def Ligne_E_Ticket = (OngletTicket.getObjectValue(‘ActualRow’, 1)).toInteger() + 1
String excelFile = ‘Data Files\’ + GlobalVariable.DataFile // GlobalVariable.File_Path + GlobalVariable.DataFile //‘Data Files\File01.xlsx’
def workbook = ExcelKeywords.getWorkbook(excelFile)
def sheet = ExcelKeywords.getExcelSheet(workbook, ‘e-ticket’)
def iRes = ExcelKeywords.setValueToCellByIndex(sheet, 1, 2, Ligne_E_Ticket)
iRes = ExcelKeywords.saveWorkbook(excelFile, workbook)

2020-02-06 17:59:44.439 TRACE c.k.katalon.core.main.TestCaseExecutor - END null: iRes = saveWorkbook(excelFile, workbook)
2020-02-06 17:59:44.454 ERROR c.k.katalon.core.main.TestCaseExecutor - :x: Test Cases/FlunchGroup/E-Ticket_Brulage FAILED.
Reason:
java.lang.RuntimeException: Unexpected arg eval type (org.apache.poi.ss.formula.LazyRefEval)
at org.apache.poi.ss.formula.eval.OperandResolver.coerceValueToDouble(OperandResolver.java:231)
at org.apache.poi.ss.formula.atp.ArgumentsEvaluator.evaluateNumberArg(ArgumentsEvaluator.java:117)
at org.apache.poi.ss.formula.atp.WorkdayFunction.evaluate(WorkdayFunction.java:69)
at org.apache.poi.ss.formula.UserDefinedFunction.evaluate(UserDefinedFunction.java:61)
at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:129)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:514)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:278)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:220)
at org.apache.poi.xssf.usermodel.BaseXSSFFormulaEvaluator.evaluateFormulaCellValue(BaseXSSFFormulaEvaluator.java:64)
at org.apache.poi.ss.formula.BaseFormulaEvaluator.evaluateFormulaCellEnum(BaseFormulaEvaluator.java:192)
at org.apache.poi.ss.formula.BaseFormulaEvaluator.evaluateAllFormulaCells(BaseFormulaEvaluator.java:267)
at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateAll(XSSFFormulaEvaluator.java:92)
at com.kms.katalon.keyword.excel.ExcelKeywords.saveWorkbook(ExcelKeywords.groovy:140)
at com.kms.katalon.keyword.excel.ExcelKeywords$saveWorkbook$2.call(Unknown Source)
at E-Ticket_Brulage.run(E-Ticket_Brulage:40)
at com.kms.katalon.core.main.ScriptEngine.run(ScriptEngine.java:194)
at com.kms.katalon.core.main.ScriptEngine.runScriptAsRawText(ScriptEngine.java:119)
at com.kms.katalon.core.main.TestCaseExecutor.runScript(TestCaseExecutor.java:337)
at com.kms.katalon.core.main.TestCaseExecutor.doExecute(TestCaseExecutor.java:328)
at com.kms.katalon.core.main.TestCaseExecutor.processExecutionPhase(TestCaseExecutor.java:307)
at com.kms.katalon.core.main.TestCaseExecutor.accessMainPhase(TestCaseExecutor.java:299)
at com.kms.katalon.core.main.TestCaseExecutor.execute(TestCaseExecutor.java:233)
at com.kms.katalon.core.main.TestCaseMain.runTestCase(TestCaseMain.java:114)
at com.kms.katalon.core.keyword.builtin.CallTestCaseKeyword$_callTestCase_closure1.doCall(CallTestCaseKeyword.groovy:59)
at com.kms.katalon.core.keyword.builtin.CallTestCaseKeyword$_callTestCase_closure1.call(CallTestCaseKeyword.groovy)
at com.kms.katalon.core.keyword.internal.KeywordMain.runKeyword(KeywordMain.groovy:68)
at com.kms.katalon.core.keyword.builtin.CallTestCaseKeyword.callTestCase(CallTestCaseKeyword.groovy:81)
at com.kms.katalon.core.keyword.builtin.CallTestCaseKeyword.execute(CallTestCaseKeyword.groovy:44)
at com.kms.katalon.core.keyword.internal.KeywordExecutor.executeKeywordForPlatform(KeywordExecutor.groovy:72)
at com.kms.katalon.core.keyword.BuiltinKeywords.callTestCase(BuiltinKeywords.groovy:350)
at Order and check out multiple products.run(Order and check out multiple products:127)
at com.kms.katalon.core.main.ScriptEngine.run(ScriptEngine.java:194)
at com.kms.katalon.core.main.ScriptEngine.runScriptAsRawText(ScriptEngine.java:119)
at com.kms.katalon.core.main.TestCaseExecutor.runScript(TestCaseExecutor.java:337)
at com.kms.katalon.core.main.TestCaseExecutor.doExecute(TestCaseExecutor.java:328)
at com.kms.katalon.core.main.TestCaseExecutor.processExecutionPhase(TestCaseExecutor.java:307)
at com.kms.katalon.core.main.TestCaseExecutor.accessMainPhase(TestCaseExecutor.java:299)
at com.kms.katalon.core.main.TestCaseExecutor.execute(TestCaseExecutor.java:233)
at com.kms.katalon.core.main.TestCaseMain.runTestCase(TestCaseMain.java:114)
at com.kms.katalon.core.main.TestCaseMain.runTestCase(TestCaseMain.java:105)
at com.kms.katalon.core.main.TestCaseMain$runTestCase$0.call(Unknown Source)
at TempTestCase1581008321019.run(TempTestCase1581008321019.groovy:23)

2020-02-06 17:59:44.459 INFO c.k.katalon.core.main.TestCaseExecutor - END CALL Test Cases/FlunchGroup/E-Ticket_Brulage
2020-02-06 17:59:44.459 INFO c.k.katalon.core.main.TestCaseExecutor - --------------------
2020-02-06 17:59:44.460 ERROR c.k.k.core.keyword.internal.KeywordMain - :x: Unable to call Test Case ‘Test Cases/FlunchGroup/E-Ticket_Brulage’ (Root cause: com.kms.katalon.core.exception.StepErrorException: Call Test Case ‘Test Cases/FlunchGroup/E-Ticket_Brulage’ failed because of error(s)
at com.kms.katalon.core.keyword.builtin.CallTestCaseKeyword$_callTestCase_closure1.doCall(CallTestCaseKeyword.groovy:66)
at com.kms.katalon.core.keyword.builtin.CallTestCaseKeyword$_callTestCase_closure1.call(CallTestCaseKeyword.groovy)
at com.kms.katalon.core.keyword.internal.KeywordMain.runKeyword(KeywordMain.groovy:68)
at com.kms.katalon.core.keyword.builtin.CallTestCaseKeyword.callTestCase(CallTestCaseKeyword.groovy:81)
at com.kms.katalon.core.keyword.builtin.CallTestCaseKeyword.execute(CallTestCaseKeyword.groovy:44)
at com.kms.katalon.core.keyword.internal.KeywordExecutor.executeKeywordForPlatform(KeywordExecutor.groovy:72)
at com.kms.katalon.core.keyword.BuiltinKeywords.callTestCase(BuiltinKeywords.groovy:350)
at Order and check out multiple products.run(Order and check out multiple products:127)
at com.kms.katalon.core.main.ScriptEngine.run(ScriptEngine.java:194)
at com.kms.katalon.core.main.ScriptEngine.runScriptAsRawText(ScriptEngine.java:119)
at com.kms.katalon.core.main.TestCaseExecutor.runScript(TestCaseExecutor.java:337)
at com.kms.katalon.core.main.TestCaseExecutor.doExecute(TestCaseExecutor.java:328)
at com.kms.katalon.core.main.TestCaseExecutor.processExecutionPhase(TestCaseExecutor.java:307)
at com.kms.katalon.core.main.TestCaseExecutor.accessMainPhase(TestCaseExecutor.java:299)
at com.kms.katalon.core.main.TestCaseExecutor.execute(TestCaseExecutor.java:233)
at com.kms.katalon.core.main.TestCaseMain.runTestCase(TestCaseMain.java:114)
at com.kms.katalon.core.main.TestCaseMain.runTestCase(TestCaseMain.java:105)
at com.kms.katalon.core.main.TestCaseMain$runTestCase$0.call(Unknown Source)
at TempTestCase1581008321019.run(TempTestCase1581008321019.groovy:23)
Caused by: java.lang.RuntimeException: Unexpected arg eval type (org.apache.poi.ss.formula.LazyRefEval)
at org.apache.poi.ss.formula.eval.OperandResolver.coerceValueToDouble(OperandResolver.java:231)
at org.apache.poi.ss.formula.atp.ArgumentsEvaluator.evaluateNumberArg(ArgumentsEvaluator.java:117)
at org.apache.poi.ss.formula.atp.WorkdayFunction.evaluate(WorkdayFunction.java:69)
at org.apache.poi.ss.formula.UserDefinedFunction.evaluate(UserDefinedFunction.java:61)
at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:129)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:514)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:278)
at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:220)
at org.apache.poi.xssf.usermodel.BaseXSSFFormulaEvaluator.evaluateFormulaCellValue(BaseXSSFFormulaEvaluator.java:64)
at org.apache.poi.ss.formula.BaseFormulaEvaluator.evaluateFormulaCellEnum(BaseFormulaEvaluator.java:192)
at org.apache.poi.ss.formula.BaseFormulaEvaluator.evaluateAllFormulaCells(BaseFormulaEvaluator.java:267)
at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateAll(XSSFFormulaEvaluator.java:92)
at com.kms.katalon.keyword.excel.ExcelKeywords.saveWorkbook(ExcelKeywords.groovy:140)
at com.kms.katalon.keyword.excel.ExcelKeywords$saveWorkbook$2.call(Unknown Source)
at E-Ticket_Brulage.run(E-Ticket_Brulage:40)
at com.kms.katalon.core.main.ScriptEngine.run(ScriptEngine.java:194)
at com.kms.katalon.core.main.ScriptEngine.runScriptAsRawText(ScriptEngine.java:119)
at com.kms.katalon.core.main.TestCaseExecutor.runScript(TestCaseExecutor.java:337)
at com.kms.katalon.core.main.TestCaseExecutor.doExecute(TestCaseExecutor.java:328)
at com.kms.katalon.core.main.TestCaseExecutor.processExecutionPhase(TestCaseExecutor.java:307)
at com.kms.katalon.core.main.TestCaseExecutor.accessMainPhase(TestCaseExecutor.java:299)
at com.kms.katalon.core.main.TestCaseExecutor.execute(TestCaseExecutor.java:233)
at com.kms.katalon.core.main.TestCaseMain.runTestCase(TestCaseMain.java:114)
at com.kms.katalon.core.keyword.builtin.CallTestCaseKeyword$_callTestCase_closure1.doCall(CallTestCaseKeyword.groovy:59)
… 18 more
)

tks in advance for your help.

@kazurayam @Russ_Thomas I was able to work with Excel after upgrading to 7.2.1 version of KS. I realised now that I downloaded the KSE trial version. My trial expiry notice is coming up and I am worried now if the code will not work after KSE trial expires. Do either of you know have any info about this? I tried to download KS7 version but it downloads the KSE 7.2.x trial version. If anyone else has info on this please reply. TIA.

@ThanhTo

Could you answer to this?

@mridula.palivela

The excel plugin is free, so does the functionalities and keywords it offers. I don’t think there’s any restriction that would prevent it from working.

@kazurayam thx for redirecting the question to the right person
@ThanhTo
Thanks for that reply. Is there anyway I can test it, using just KS7 version? Or do I have just wait till my trial version expires? Where can I download it from? As I mentioned in my initial post, if I try to download from the KS7 link, it is downloading the 7.2.x version which is what I have.

Hi @mridula.palivela

Just activate Katalon Studio with a gmail account, you will be activating the free version.