Read Write Excel Custom Keywords

Steven_Taylor · November 22, 2019, 2:09pm

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.

pranitha.2006 · December 3, 2019, 8:57pm

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.

mridula.palivela · January 28, 2020, 3:59am

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)

kazurayam · January 28, 2020, 5:53am

@mridula.palivela

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

See

Reply

Noor Yeaser Khan
December 24, 2019

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

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

mridula.palivela · January 28, 2020, 2:03pm

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.

mridula.palivela · January 28, 2020, 8:32pm

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

kazurayam · January 29, 2020, 12:30am

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

mridula.palivela · January 29, 2020, 2:41pm

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!

arnaud.cognard · February 6, 2020, 5:04pm

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

mridula.palivela · February 24, 2020, 4:04pm

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

kazurayam · February 24, 2020, 10:12pm

@ThanhTo

Could you answer to this?

ThanhTo · February 25, 2020, 8:18am

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

mridula.palivela · February 25, 2020, 12:53pm

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

ThanhTo · February 25, 2020, 1:41pm

Hi @mridula.palivela

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

mridula.palivela · February 25, 2020, 8:01pm

Thanks @ThanhTo
I deactivated my office email and used personal email to activate KS. I see the difference in some of the menu items like no Debug Mode during runtime which I will really miss. But luckily the excel read write feature is working, phew

Hopefully one last question on this. If I activate the account using my office email again, after trial expiry, what will happen? I cannot use my office email at all? or can I continue to use my office email and Katalon Studio will just revert to the free version?

ThanhTo · February 26, 2020, 3:04am

@mridula.palivela

Yes, after the trial you will be using the free version if you choose not to purchase a KSE license.

mridula.palivela · February 26, 2020, 3:18am

@ThanhTo Thanks! But I wanted to know if I can use my office email instead of personal email once the trial license is expired.

ThanhTo · February 26, 2020, 6:12am

@mridula.palivela

Yes, thats’ what I meant, you can continue using your office email after the trial and it will activate the free version.

mridula.palivela · February 26, 2020, 3:14pm

@ThanhTo Thanks for all the info!!

Aravind_Raja · May 15, 2020, 7:00am

Unable to download the plugin. The Install button disappears after signin. Anyone else facing the same issue? What’s the workaround?

Topic		Replies	Views
Katalon studio write excel custom keyword Katalon Studio katalon-studio , tips-tricks	10	3467	August 8, 2022
Write to Excel Keyword example Tips & Tricks katalon-studio , tips-tricks	1	1058	August 8, 2022
Write into excel at runtime during test execution Katalon Studio katalon-studio , feature-request	26	12934	February 28, 2020
Write value on website to excel/csv/txt file Archive katalon-studio , web-testing	6	4681	August 8, 2022
How to develop Custom Keywords Plugins Katalon Studio plugins , katalon-studio	35	9736	November 17, 2021

Read Write Excel Custom Keywords

Related Topics