Read Write Excel Custom Keywords

Product Forums Katalon Studio
1
This is a companion discussion topic for the original entry at https://store.katalon.com/product/34#qa-content
1 Like
2

where i can find a video on how to implement this? Thanks.

3

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.

4

Hey its working now, thank you :smiley:

image
image1101×732 81.3 KB

1 Like
5

Im trying to implement this in a test suite,

CustomKeywords.‘com.kms.katalon.keyword.excel.ExcelKeywords.setValueToCellByIndex’(sheet01, RowVar, 0, TextOne)

In every iteration i want to write into (RowVar, 0) but How do i implement this to not create the File.xlsx every time.

6

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!

Thanks,
Nhan Nguyen Thi Hanh

import com.kms.katalon.keyword.excel.ExcelKeywords as ExcelKeywords
/**

  • Example for update an existing excel file
  • Scenarios:
    • Open an existing file
    • Write the first cell of row 3 to row 5 with text 'Fruit ’ + rowIndex
    • Save the update into that existing file
      */

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)

UpdateExistingExcelFile-Code
UpdateExistingExcelFile-Code982×528 29.2 KB

UpdateExistingExcelFile-Result
UpdateExistingExcelFile-Result715×642 28.3 KB

1 Like
7

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.

8

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

9

Can we have the same implementation for CSV as well.

It would be great

10

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

11

It is a good idea, Tony. We will consider about this option to create a new keyword for it.

12

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

1 Like
13

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.

14

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.

15

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

16

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

17

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!

18

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?

19

(answering my own question… :sunglasses:) 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.

1 Like
20

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.

image
image1172×541 31.8 KB

could anyone help me to find the location of excel.jar