Read Write Excel Custom Keywords

Product Forums Katalon Studio
61

hi @nhannguyen can you also help me with my problem in my keyword code writing into excel file?
this is the output

image
image456×624 12.8 KB

this is the expected output
image
image471×635 12.7 KB

This is my keyword code
image
image1259×850 37.1 KB

image
image992×757 16.9 KB

image
image962×703 17.9 KB

62

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.

image
image422×663 15.9 KB

63

Anyone please share in case of Rest API, how to write dynamic id crated in response into excel sheet ??

64

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?

65

Hi, I hope this is simple, but when i run a compare two excels, there is no error thrown if they do not match. Therefore my tests pass when they compare incorrectly.
Is there a way to get this keyword to fail the test?

66

Any one please let me know for , how to get all the data present in excel

67

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:

https://plugin-docs.katalon.com/docs/excel-custom-keywords/com/kms/katalon/keyword/excel/ExcelKeywords.html#getTableContent(org.apache.poi.ss.usermodel.Sheet,%20int,%20int)

69

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)

70

You can read the source the plugin here:

I suppose you can find something there.

71

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.

72

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.

73

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.

74

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.

75

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)

76

Perhaps, yes.

Why not you try it.

1 Like
77

I see, thanks for the replies

78

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:

  • Test script #1 - makes updates to cells in the spreadsheet
  • Test script #2 which runs right after, uses those written values to make decisions.

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:

ExcelKeywords.setValueToCellByAddress(sheet01, EnrolledYNColumn + RowInd, EnrolledYN)
ExcelKeywords.saveWorkbook(excelDataFile, workBook01)

79

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

1 Like
80

Thanks for the suggestions. I have attached the two scripts.

![image|690x122](upload://eWAoGxclqMrPcaOgZ1kOh7VIuph.png)
I have a delay in between the scripts which doesn't seem to have the effect I want (allowing time for changes to be flushed). [09 - New AI Application (data driven).tc|attachment](upload://rzRt4DIc3uC7AByOb4FwjEWn3b2.tc) (14.2 KB) [Approve AI Application (Data Driven).tc|attachment](upload://c9dXaYcq9NayXZhXDD4wuHsrmgB.tc) (5.8 KB)

I checked the ExcelKeywords list of methods and it doesn’t seem possible to explicitly close the file at the moment.

Cheers,
Jonathan