Hello pgonzalez,
Instead of a video, in the Overview page, we give many examples of using this custom keyword in the Katalon project on GitHub with the following URL
ExcelPluginSample
Hope you could download it and follow the instruction in the README file to enjoy the sample of using this keyword to work with Excel files in Katalon.
1 reply
Hi pgonzalez,
You could open an existing excel file once. Then update into that file on many cells as your expectation. Then you save the workbook once after you completed the update.
Finally, you could open that existing file to check the result.
Below is an example of scripts. Enjoy test scripting!
import com.kms.katalon.keyword.excel.ExcelKeywords as ExcelKeywords
/**
String excelFilePath = ‘Data Files\FileToUpdate.xlsx’
String sheetName = ‘Sheet1’
String textToWrite = 'Fruit ’
workbook01 = ExcelKeywords.getWorkbook(excelFilePath)
sheet01 = ExcelKeywords.getExcelSheet(workbook01, sheetName)
for (int rowIndex = 2; rowIndex < 5; rowIndex++) {
ExcelKeywords.setValueToCellByIndex(sheet01, rowIndex, 0, textToWrite + rowIndex)
}
ExcelKeywords.saveWorkbook(excelFilePath, workbook01)
How do I download ExcelKeywords.jar to install in an offline environment? I can’t see a download link anywhere on this site or in github.
Thanks in advance for the help.
1 reply
Hello Brandon-Hinds,
In the Overview page of this plugin, you could find the URL to download a sample katalon project with test cases using the plug-in. The ExcelKeywords.jar file is under the Drivers sub-folder.
Thanks,
Nhan Nguyen Thi Hanh
Hi
I can not open newest excel version “.xls”. I try to change the XSSF for HSSFWorkbook but the result it’s the same.
java.io.IOException: block[ 2 ] already removed - does your POIFS have circular or duplicate block references?
Regards
1 reply
It is a good idea, Tony. We will consider about this option to create a new keyword for it.
Hi @tasiaylalo,
Our keyword currently support “.xls” from Excel versions 97/2000/XP/2003. It does not work with Excel versions 5.0/95.
Could you give me your testing xls file so I could check? Because we could not reproduce your reported error.
Thanks,
Nguyen Thi Hanh Nhan
Hi
I have followed your steps untill attaching ExcelKeywords_sources.jar. I have done attaching the jar. But still i see the below statement when I hover over the excel keyword
void com.kms.katalon.keyword.excel.ExcelKeywords.saveWorkbook(String filePath, Workbook workbook)
Note: This element has no attached Javadoc and the Javadoc could not be found in the attached source.
1 reply
In the Overview page of this plugin, you could find the URL to download a sample katalon project with test cases using the plug-in. The ExcelKeywords.jar file is under the Drivers sub-folder.
Awesome, thank you nhannguyen. Another hopefully stupid-and-easy-to-answer question, now that I have that jar (or any of the other plugins .jars) how do I install them in my offline katalon?
Thanks for your help.
1 reply
Hi @Brandon-Hinds,
You could follow the steps to Installing The Plug-in in the Overview page of this plugin.
Thank you.
Nguyen Thi Hanh Nhan
Hi Menaka,
Could you double check and follow all steps in the Overview page of this plug-in (all steps of both Installing The Plug-in and To import Java Documentation of the Plug-in sections)
Regards,
Nguyen Thi Hanh Nhan
You could follow the steps to Installing The Plug-in in the Overview page of this plugin.
Of course, sorry that was a stupid question. Thanks for all your help Nguyen!
Dear all
Would it be possible to get ExcelKeywords to work with .xlsm files as well? It works fine for me with .xlsx files, but we also have some .xlsm files where we want to read and write certain cells and then save, without touching (and without deleting) the macros. Any chance of this?
1 reply
(answering my own question… 
) Found a workaround: rename the .xlsm file as .xlsx, then open it and do what you want with it, then save it as an .xlsm again. The macros seem to survive intact so far, and the file content seems to be correct. But it would be nice if this kludge were unnecessary.
Hi
I have done with installation of the plug-in. but i am not getting any .jar file details while i am trying to add into external lib.
Thank you 
I got the solution.
is it possible to make this for loop with TestDataFactory.findTestData(‘Scenarios’).getRowNumbers() ?? cos i want to coincide my data binding iterations with the for loop index, can you assist me with this ? i tried and is not working
Is it possible to use the created excel file for Data binding with test suites? If so please respond on how this can be done.
Hi, I got error like this when using file .xls version 97-2003
block[ 3 ] already removed - does your POIFS have circular or duplicate block references?
But if I use .xlsx, it was work. Can you fix it?
Thank you!
Hi,
After upgrading to version 7.0.2 (edited) of KS I have an error when I’m trying to write a value in a cell:
java.lang.NoSuchFieldError: CREATE_NULL_AS_BLANK
using keyword
CustomKeywords.'com.kms.katalon.keyword.excel.ExcelKeywords.setValueToCellByIndex'(hoja, celda[0], 11, id_incidencia)
where ‘hoja’ is my Excel file (Microsoft Excel 97-2003 (.xls)) ‘celda[0]’ is the row (int) and ‘id_indicencia’ is a string with 16 chars witch is readed from the web page, during the test execution.
I also tried with keyword
com.kms.katalon.keyword.excel.ExcelKeywords.setValueToCellByAddress
with the same result, it doesn’t matter where (row and column) I try to set the value or if the cell is empty or not.
This script was fully operational with KS version 6.3.3 but with new version there is no way the script finishes OK and values were written in the Excel file.
I reloaded several times all my plug-ins successfully, just to be sure there is not something wrong with them.
I also tried other scripts, with this Excel custom keyword, and the error is displayed the moment I want to write in the Excel file in every one of them.
Obviously, Excel file is closed and no other process or person is accessing it. I also double check for no hidden Excel block files (for example ~$test.xlsx) on the folder.
Full log, after the error:
Test Cases/Utiles y pruebas/Excel FAILED.
Reason:
org.codehaus.groovy.runtime.InvokerInvocationException: java.lang.NoSuchFieldError: CREATE_NULL_AS_BLANK
at com.kms.katalon.keyword.excel.ExcelKeywords.invokeMethod(ExcelKeywords.groovy)
at com.kms.katalon.core.main.CustomKeywordDelegatingMetaClass.invokeStaticMethod(CustomKeywordDelegatingMetaClass.java:50)
at Excel.run(Excel:43)
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 TempTestCase1571827898548.run(TempTestCase1571827898548.groovy:23)
Caused by: java.lang.NoSuchFieldError: CREATE_NULL_AS_BLANK
at com.kms.katalon.keyword.excel.ExcelKeywords.setValueToCellByAddress(ExcelKeywords.groovy:242)
… 14 more
Edited to add information shown after clone and load the git repository project on KS 7.0.4:
katalon-excel-keyword-master%ExcelKeywords%ExcelKeywords.prj/Keywords/com/kms/katalon/keyword/excel/ExcelKeywords.groovy: 220 Groovy:[Static type checking] - No such property: CREATE_NULL_AS_BLANK for class: java.lang.Class <org.apache.poi.ss.usermodel.Row> katalon-excel-keyword-master%ExcelKeywords%ExcelKeywords.prj/Keywords/com/kms/katalon/keyword/excel/ExcelKeywords.groovy: 220 Groovy:[Static type checking] - Cannot find matching method org.apache.poi.ss.usermodel.Row#getCell(int, java.lang.Object). Please check if the declared type is right and if the method exists. katalon-excel-keyword-master%ExcelKeywords%ExcelKeywords.prj/Keywords/com/kms/katalon/keyword/excel/ExcelKeywords.groovy: 242 Groovy:[Static type checking] - No such property: CREATE_NULL_AS_BLANK for class: java.lang.Class <org.apache.poi.ss.usermodel.Row> katalon-excel-keyword-master%ExcelKeywords%ExcelKeywords.prj/Keywords/com/kms/katalon/keyword/excel/ExcelKeywords.groovy: 242 Groovy:[Static type checking] - Cannot find matching method org.apache.poi.ss.usermodel.Row#getCell(short, java.lang.Object). Please check if the declared type is right and if the method exists. katalon-excel-keyword-master%ExcelKeywords%ExcelKeywords.prj/Keywords/com/kms/katalon/keyword/excel/ExcelKeywords.groovy: 690 Groovy:[Static type checking] - No such property: CREATE_NULL_AS_BLANK for class: java.lang.Class <org.apache.poi.ss.usermodel.Row> katalon-excel-keyword-master%ExcelKeywords%ExcelKeywords.prj/Keywords/com/kms/katalon/keyword/excel/ExcelKeywords.groovy: 690 Groovy:[Static type checking] - Cannot find matching method org.apache.poi.ss.usermodel.Row#getCell(int, java.lang.Object). Please check if the declared type is right and if the method exists. katalon-excel-keyword-master%ExcelKeywords%ExcelKeywords.prj/Keywords/com/kms/katalon/keyword/excel/ExcelKeywords.groovy: 690 Groovy:[Static type checking] - Cannot call java.util.ArrayList <org.apache.poi.ss.usermodel.Cell>#add(org.apache.poi.ss.usermodel.Cell) with arguments [java.lang.Object]
Please, could be someone so kind to help me or point me to a possible solution?
Thank you very much.
Regards,
jjgonche.
Hi max2,
ExcelKeywords.setValueToCellByAddress(sheet01, ‘A1’, ‘Value’) is broken in Katalon version 7.0.0
Please fix
I think I suffer the same annoying issue than you.
Do you see a ‘CREATE_NULL_AS_BLANK’ error when you run your scripts?
Hope someone has a fix or workaround for this issue.
Thank you very much.
Regards,
jjgonche.
Well, I think I have found out where the problem is.
Loading Excel keyword project (cloned from Git), in file
/katalon-excel-keyword-master/ExcelKeywords/Keywords/com/kms/katalon/keyword/excel/ExcelKeywords.groovy
all three references to
Row.CREATE_NULL_AS_BLANK
should be changed to
Row.MissingCellPolicy.CREATE_NULL_AS_BLANK
on lines 220, 242 and 690.
Edited: I Made a pull request on git repository for ExcelKeywords.groovy file with this fix.
The fix works on KS 6.3.3 and KS 7.0.2.
Thanks a million to JonyD, on stackoverflow, for the tip to fix the issue, kudos for him or her.
I hope this fix will help to someone.
Thank you very much.
Regards,
jjgonche
Hey,
I issue the same problem but I could not find where the references are. Could you please be more explicit ?
1 reply
Hi @vlad.croitoru,
You must edit the file /katalon-excel-keyword-master/ExcelKeywords/Keywords/com/kms/katalon/keyword/excel/ExcelKeywords.groovy, after download it from the project repository (https://github.com/nhannguyenth/katalon-excel-keyword/tree/master/ExcelKeywords).
You must change lines 220, 242 and 690, replacing Row.CREATE_NULL_AS_BLANKto Row.MissingCellPolicy.CREATE_NULL_AS_BLANK.
After that, you can not use the keyword from the store, till someone with rights update the pull request I did several days ago, but you can create you “own” custom keywords for Excel using the modified ExcelKeywords.groovy file on your projects.
I wish pull request will be done as soon as possible because now the keywords are a pain in the neck in its actual state.
Thank you very much.
Regards,
jjgonche
Hi,
I am using the plugin and for some reason, I seem to be getting back some weird information instead of actual data, but only for some of the requests. For example:
This works for me correctly:
ExcelKeywords.getCellValueByRangeAddress(sheet01, “A9”, “C14”)
Yet these return wrong information:
ExcelKeywords.getTableContent(sheet01, 9, 23)
ExcelKeywords.getDataRows(sheet01,[9,10])
And I get back something like this in XML:
[<xml-fragment r="10" xmlns:main="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<main:c r="A10" t="s" s="2">
<main:v>90</main:v>
</main:c>
<main:c r="B10" t="s" s="2">
<main:v>91</main:v>
</main:c>
<main:c r="C10" t="s" s="2">
Any ideas why?
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.
1 reply
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.
1 reply
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.
1 reply
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.
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)
Possibly you need to upgrade your Katalon Studio to ver7.1.2
See
Reply
Noor Yeaser Khan
December 24, 2019Updating to version 7.1.2 resolved this issue. Thanks a bunch!
at https://store.katalon.com/product/34/Excel-Keywords#rating-content
1 reply
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.
1 reply
@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)
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.
1 reply
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!
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 -
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.
1 reply
@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.
Just activate Katalon Studio with a gmail account, you will be activating the free version.
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?
1 reply
Yes, after the trial you will be using the free version if you choose not to purchase a KSE license.
@ThanhTo Thanks! But I wanted to know if I can use my office email instead of personal email once the trial license is expired.
1 reply
Yes, thats’ what I meant, you can continue using your office email after the trial and it will activate the free version.
@ThanhTo Thanks for all the info!!
Unable to download the plugin. The Install button disappears after signin. Anyone else facing the same issue? What’s the workaround?
hi @nhannguyen can you also help me with my problem in my keyword code writing into excel file?
this is the output
How to reuse the created excel file in ExcelKeywords? coz i need to write the result of my next test case in sheet 2. please help. Below is the created excel with get text data in sheet 1.
Anyone please share in case of Rest API, how to write dynamic id crated in response into excel sheet ??
When you say rename it, does it mean you save the macro as xlsx file? then after modifying it, you save again as xlsm file?
Any one please let me know for , how to get all the data present in excel
If you are using “Data-Driven Testing” approach, the following API will help you.
https://docs.katalon.com/javadoc/com/kms/katalon/core/testdata/TestData.html#getAllData()
If you are using Read Writte Excel Custom Keyowors, the following API will let you do what you want to do:
Hi, I believe I am experiencing a bug in the Excel Keywords plugin. I am using the tool in testing exports in my companies software product for various report pages. I use the ExcelKeywords tool on many pages and it is working well with no issues. For one report export though when I compare the 2 excel files using the following command “ExcelKeywords.compareTwoExcels(exampleWorkbook, newWorkbook)”, I get the error below.
Could you please provide information on what might be causing this if it is not a bug? I cannot find any information online for the same issue.
java.lang.IllegalArgumentException: Cell index must be >= 0
at org.apache.poi.xssf.usermodel.XSSFRow.getCell(XSSFRow.java:270)
at org.apache.poi.xssf.usermodel.XSSFRow.getCell(XSSFRow.java:259)
at org.apache.poi.xssf.usermodel.XSSFRow.getCell(XSSFRow.java:45)
at com.kms.katalon.keyword.excel.ExcelKeywords.compareTwoRows(ExcelKeywords.groovy:797)
at com.kms.katalon.keyword.excel.ExcelKeywords.compareTwoSheets(ExcelKeywords.groovy:762)
at com.kms.katalon.keyword.excel.ExcelKeywords.compareTwoExcels(ExcelKeywords.groovy:726)
at com.kms.katalon.keyword.excel.ExcelKeywords.compareTwoExcels(ExcelKeywords.groovy)
at com.kms.katalon.keyword.excel.ExcelKeywords$compareTwoExcels$3.call(Unknown Source)
Thank you for replying I do appreciate it, but I can’t find any useful information at the link you attached. Can yourself or someone else provide some useful information please.
Please provide a Katalon Studio project that can reproduce your problem on other’s PC. The project should include “exampleWorkbook” and “newWorkbook”, plus a Test Case script that does compare the 2 files and causes errors. Unless such sample project provided, it is impossible for those in this forum to find any useful information.
How do I rename an Excel sheet? Whenever I create a new Excel file, the first sheet in the workbook is named “Sheet0” by default. I couldn’t find the keyword for renaming an existing sheet in the documentations.
1 reply
The com.kms.katalon.keyword.excel.ExcelKeywrods class does not implement a method that renames an exisiting sheet to another name.
If you want to, you need to write code using Apache POI library. By searching Google with “Excel POI rename sheet”, you will find a lot of references. The Apache POI library is bundled in Katalon Studio so that you can start using it quickly.
Howerver, the easiest way would be creating a new Worksheet with the name you want and use it. You can leave the Sheet0 unused.
Hi, sorry for the super late reply. How about deleting Sheet0? Do I also need to write code using Apache POI library? I checked the documentation but couldn’t find anything related to removing a sheet (maybe I was searching the wrong keyword)
1 reply
I see, thanks for the replies
Hi,
Thanks for the plugin - it works great!
I have a situation where the changes don’t seem to get written to the file. Here’s the scenario:
In a test suite, I run 2 scripts:
What I find is that if I run test script #1 and then test script #2 individually, it behaves as expected.
When I run the two as part of a test suite, it seems that even though I am making the updates successfully (at least as far as Katalon is concerned), those updates don’t get saved to the excel file.
Do I have to do something top force the changes to be written?
Here’s the snippet of code I use to update the sheet:
1 replyExcelKeywords.setValueToCellByAddress(sheet01, EnrolledYNColumn + RowInd, EnrolledYN)
ExcelKeywords.saveWorkbook(excelDataFile, workBook01)
Can you check the following, not sure if it is possible with the keywords but like ExcelKeywords.close()
This can help ensure that all changes are flushed to disk and the file is properly closed before the next script tries to access it.
2. Add Delays: As a temporary workaround, you could add a short delay between the execution of Test script #1 and Test script #2 to give the file system time to properly save and close the Excel file. This isn’t the most robust solution but can help determine if timing is the issue.
3. Re-open the Excel File in Test Script #2: Ensure that Test script #2 explicitly opens the Excel file again (even if it seems redundant) rather than relying on any cached or in-memory version of the file that might have been left open by Test script #1. This can help ensure that Test script #2 is working with the most up-to-date version of the file. Can you provide the second script as well
Thanks for the suggestions. I have attached the two scripts.
I checked the ExcelKeywords list of methods and it doesn’t seem possible to explicitly close the file at the moment.
Cheers,
Jonathan