Read Write Excel Custom Keywords

Hello Saranya , Please see this post for your answer

Thank you :slight_smile:
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.

ExcelKeywords.setValueToCellByAddress(sheet01, ‘A1’, ‘Value’) is broken in Katalon version 7.0.0
Please fix

1 Like

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

2 Likes

@Jass Please update the documentation.

Hey,

I issue the same problem but I could not find where the references are. Could you please be more explicit ?

1 Like

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

Sorry for the issue. We will release a new version tomorrow to address it.

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.

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.