How to Write and Update Excel Data?

[JCMS]

Hi all, i need a help. I have a data in excel which i use to Data Binding on Test Suite to create Jira Task. After input and submit the data to create jira task, i do getText on Jira ID and i want to write this Jira ID in Column iCenter on the same Excel i’ve been used for Data Binding each time the row executed. My problem is i don’t know how to write it on excel. Can u give me any sugestion or some example to do it. Thanks

Here is the example of excel data.

Here is my Test Suite (Data Binding)

Here is my Test Case (Script)

Here is my Test Case (Variables)

[paulo.resende]

I use the following script to write into Excel.

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

String excelFilePath = ‘Directory of excel.extention’

String sheetName = ‘Name of sheet’

String textToWrite = WebUI. getText ( findTestObject (‘OBJECT’))

workbook01 = ExcelKeywords. getWorkbook (excelFilePath)

sheet01 = ExcelKeywords. getExcelSheet (workbook01, sheetName)

for ( int rowIndex = 1; rowIndex < 2; rowIndex++) {

ExcelKeywords. setValueToCellByIndex (sheet01, rowIndex, 0, textToWrite)

}

ExcelKeywords. saveWorkbook (excelFilePath, workbook01)

With the 0 in sheet01, rowIndex, 0, textToWrite you can say which column.
So with this script I get the data and will write in the first column on the 2nd row. And no more.

A note to this. Your excel file needs to be closed and not open. If the file is open, Katalon can’t write into it.

Let me know if you got it to work.

[JCMS]

Hi Paulo, thanks for responding. I’ve got another solution.

Here’s my real Excel Data

Here’s update on Test Suite Data Binding

Here’s update on Test Case (Script)

*Notes: i have information of Column “No” on my Excel data and i used it for rowNo. Why its plus by 3? Well its a tricky way . Heres my explanation: As my first row used as a header it will be not count as first row. Then as you can see my second and third row are used as a title and the fourth row used as header info (its a duplicate from first row which is hidden by me so as a document it looks no duplicate ). So, by plus by 3 i got real row number to update the row. and the cell filled with 7 as my column “iCenter” for input jira ID is in cell 7.

Here’s update on Test Case (Variables)

[JCMS]

To Import in Test Script:

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;