Katalon writing data to excel

I wanna do like this…
get URL then put into the excel file…

After acquiring the current URL by the following method, I want to contain it in the “url” row in the excel file.

https://docs.katalon.com/katalon-studio/docs/webui-get-url.html#description-

Of course, I downloaded “Excel Keyword”from plug-in like this,


But I have no idea and what can i do.

This document is too poor… someone need to explain at least how to write(export) data to excel
and import data from excel(and so and)

Please somebody help me for the love of GOD.

I explained how you can do this in your other question on this topic. Start there. However, you need to move off the Manual tab onto the Script tab or you can “translate” what I put down from that question into the Manual tab. If this doesn’t fulfill your need, then we can give more information.

Edit: To make it even easier, I brought what I wrote there over to this question:

Maybe like this:

I need to read a client name between Test Suites, so I save the name from one Test Case of Suite 1 into another Test Case of Suite 2.

There may be newer ways to save between suites, but since it works, I don’t upgrade the code.

import org.apache.poi.ss.usermodel.Cell
import org.apache.poi.ss.usermodel.Row
import org.apache.poi.xssf.usermodel.XSSFSheet
import org.apache.poi.xssf.usermodel.XSSFWorkbook

gTestIdPathWay = "G:\\Katalon Test Cases\\Data Files\\TestId ST-07-CLM.xlsx";
gUserLastName = "WarmPerson01A1";

/* open connection to MS Excel, save surname to sheet 1, cell A2, close connection */
FileInputStream fis = new FileInputStream (gTestIdPathWay);
XSSFWorkbook workbook = new XSSFWorkbook (fis);

XSSFSheet sheet = workbook.getSheet("Sheet1");

// cell A2
Row row = sheet.createRow(1);
Cell cell = row.createCell(0);

cell.setCellType(cell.CELL_TYPE_STRING);
cell.setCellValue(gUserLastName);

FileOutputStream fos = new FileOutputStream(gTestIdPathWay);
workbook.write(fos);
fos.close();
fis.close();

and then to retrieve it:

import org.apache.poi.ss.usermodel.Cell
import org.apache.poi.ss.usermodel.Row
import org.apache.poi.xssf.usermodel.XSSFSheet
import org.apache.poi.xssf.usermodel.XSSFWorkbook

gTestIdPathWay = "G:\\Katalon Test Cases\\Data Files\\TestId ST-07-CLM.xlsx";

FileInputStream fis = new FileInputStream (gTestIdPathWay);
XSSFWorkbook workbook = new XSSFWorkbook (fis);

XSSFSheet sheet = workbook.getSheet("Sheet1");
// cell A2
Row row = sheet.getRow(1);
Cell cell = row.getCell(0);
gUserLastName= cell.getStringCellValue();

fis.close()

@grylion54
thx for your refly.
Can you share with me the contents of the Excel file used for that work? (Just the format)

As the comment in my code states, I have the Client’s last name in cell A2. I also have a heading in cell A1, “Surname”, but that was just window-dressing. The Client’s name is a simple String (cell.CELL_TYPE_STRING), like WarmPerson01A1. My TCs write and read the A2 cell and pass the data between them. I can also set other cells (down the column) of other information if I need it. It is a simple system that I found on-line that does what I need.

Writing to cell A2

// cell A2
Row row = sheet.createRow(1);
Cell cell = row.createCell(0);

Retrieving from cell A2

// cell A2
Row row = sheet.getRow(1);
Cell cell = row.getCell(0);

Also note that you can make more sheets within your spreadsheet than just Sheet1 and use them as well. Just change the reference to which Sheet you are using.

XSSFSheet sheet = workbook.getSheet("Sheet1");

@grylion54
I just made TEST Cases 1 , 2

As you mentioned to me, I added “Surname” heading on to sheet1(file name is excel)

But, it does not work.

Should I set the file to be added to the A2 cell as test data separately?

As I mentioned, I am storing the Client’s surname in the spreadsheet. The variable I use to save the information is “gUserLastName”. So you have to have some variable that you “store” the URL and replace my variable with yours.

Or, if you want to see the test in action, then add the following to your code and then run it.

gTestIdPathWay =  "C:\\Users\\hojun.lee\\Katalon Studio\\excel.xlsx"'
gUserLastName = "Lee, Hojun"

After that, you can replace my variable with whatever you want.

If you want to save more than just the one item, then you can create more:

// cell A2
Row row = sheet.createRow(1);
Cell cell = row.createCell(0)

// cell A6
row = sheet.createRow(5);
cell = row.createCell(0)

// cell A10
row = sheet.createRow(9);
cell = row.createCell(0)

When storing data in csv, can multiple data be extracted through a looping operation?
I use the for function below to continuously issue new URLs.
Then, I want to save URsL in csv(by GlobalVariable.url)

Which part should I fix?

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 static com.kms.katalon.core.testobject.ObjectRepository.findWindowsObject
import com.kms.katalon.core.checkpoint.Checkpoint as Checkpoint
import com.kms.katalon.core.cucumber.keyword.CucumberBuiltinKeywords as CucumberKW
import com.kms.katalon.core.mobile.keyword.MobileBuiltInKeywords as Mobile
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.testng.keyword.TestNGBuiltinKeywords as TestNGKW
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.windows.keyword.WindowsBuiltinKeywords as Windows
import internal.GlobalVariable as GlobalVariable
import org.openqa.selenium.Keys as Keys

import org.apache.poi.ss.usermodel.Cell
import org.apache.poi.ss.usermodel.Row
import org.apache.poi.xssf.usermodel.XSSFSheet
import org.apache.poi.xssf.usermodel.XSSFWorkbook
WebUI.openBrowser('')

'Stick to this page(for Download + Upload)'
WebUI.navigateToUrl('https://kusa89.jp/ss/index_comb_ss.html')

'ID'
WebUI.setText(findTestObject('Object Repository/Page_SSCSVNumatcheck/input__f_itcu'), 'hojun.lee')

'PASSWORD'
WebUI.setEncryptedText(findTestObject('Object Repository/Page_SSCSVNumatcheck/input__f_itcp'), '90Dp6xqc0oO/YegCFWVM4w==')

WebUI.sendKeys(findTestObject('Object Repository/Page_SSCSVNumatcheck/input__f_itcp'), Keys.chord(Keys.ENTER))

WebUI.click(findTestObject('Object Repository/Page_SSCSVNumatcheck/a_KICS'))

WebUI.click(findTestObject('Object Repository/Page_SSCSVNumatcheck/a_'))

WebUI.click(findTestObject('Object Repository/Page_SSCSVNumatcheck/a_PonyoPonyo on KICS V2'))

for (def row = 1; row <= findTestData('New').getRowNumbers(); row++) {
	WebUI.click(findTestObject('Page_SSCSVNumatcheck/input_SS_SSnum'))

	WebUI.setText(findTestObject('Object Repository/Page_SSCSVNumatcheck/input_SS_SSnum'), findTestData('NEW').getValue(
			'a', row))

	WebUI.uploadFile(findTestObject('Object Repository/Page_SSCSVNumatcheck/input_SSCSV_csvFileSS'), findTestData('NEW').getValue(
			'c', row))

	WebUI.waitForAlert(5)

	WebUI.uploadFile(findTestObject('uploadObject/Page_SSCSVNumatcheck/input_CSV_csvFileNP'), findTestData('NEW').getValue(
			'd', row))

	WebUI.waitForAlert(5)

	WebUI.click(findTestObject('Object Repository/Page_SSCSVNumatcheck/input_SS_button'))

	WebUI.waitForPageLoad(60)

 
/*①From now new page will be open*/

	WebUI.switchToWindowIndex(1) //switches to 2nd window
	secondUrl = WebUI.getUrl()
	println("secondUrl: " + secondUrl)

/*② I acquire second page’s url*/

	GlobalVariable.url = WebUI.getUrl()
	WebUI.switchToWindowIndex(0) //switches to 1st window
	firstUrl = WebUI.getUrl()
	println("firstUrl: " + firstUrl)
	
	WebUI.waitForAlert(5)
	
/*③After I acquire second page’s url, Close second page */

	WebUI.closeWindowIndex(1)
	WebUI.waitForAlert(5)
		
/*Turn to the First page, then loop ①~③ process */
}

gTestIdPathWay =  "C:\\Users\\hojun.lee\\Katalon Studio\\excel.xlsx"
gUrl = GlobalVariable.url

/* open connection to MS Excel, save surname to sheet 1, cell A2, close connection */
FileInputStream fis = new FileInputStream (gTestIdPathWay);
XSSFWorkbook workbook = new XSSFWorkbook (fis);

XSSFSheet sheet = workbook.getSheet("Sheet1");

// cell A2
Row row = sheet.createRow(1);
Cell cell = row.createCell(0);

cell.setCellValue(gUrl);

FileOutputStream fos = new FileOutputStream(gTestIdPathWay);
workbook.write(fos);
fos.close();
fis.close();

@hojun.lee

Please use “Code Formatting” syntax in the posts to make the code readable using fixed width fonts.

I modified

Yes you can adjust your script to include your saving the URL to the spreadsheet within the loop, however, you need to allow the statement, Row row = sheet.createRow(1); to allow to create different rows, so something like,

newRow += 3
Row row = sheet.createRow(newRow);

You might even put the writing to spreadsheet method into a Keyword and include the Keyword in your loop.

And you should put a definition of newRow at the top of you code:

int newRow = 0

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 static com.kms.katalon.core.testobject.ObjectRepository.findWindowsObject
import com.kms.katalon.core.checkpoint.Checkpoint as Checkpoint
import com.kms.katalon.core.cucumber.keyword.CucumberBuiltinKeywords as CucumberKW
import com.kms.katalon.core.mobile.keyword.MobileBuiltInKeywords as Mobile
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.testng.keyword.TestNGBuiltinKeywords as TestNGKW
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.windows.keyword.WindowsBuiltinKeywords as Windows
import internal.GlobalVariable as GlobalVariable
import org.openqa.selenium.Keys as Keys

import org.apache.poi.ss.usermodel.Cell
import org.apache.poi.ss.usermodel.Row
import org.apache.poi.xssf.usermodel.XSSFSheet
import org.apache.poi.xssf.usermodel.XSSFWorkbook
WebUI.openBrowser('')

'Stick to this page(for Download + Upload)'
WebUI.navigateToUrl('https://kusa89.jp/ss/index_comb_ss.html')

'ID'
WebUI.setText(findTestObject('Object Repository/Page_SSCSVNumatcheck/input__f_itcu'), 'hojun.lee')

'PASSWORD'
WebUI.setEncryptedText(findTestObject('Object Repository/Page_SSCSVNumatcheck/input__f_itcp'), '90Dp6xqc0oO/YegCFWVM4w==')

WebUI.sendKeys(findTestObject('Object Repository/Page_SSCSVNumatcheck/input__f_itcp'), Keys.chord(Keys.ENTER))

WebUI.click(findTestObject('Object Repository/Page_SSCSVNumatcheck/a_KICS'))

WebUI.click(findTestObject('Object Repository/Page_SSCSVNumatcheck/a_'))

WebUI.click(findTestObject('Object Repository/Page_SSCSVNumatcheck/a_PonyoPonyo on KICS V2'))

int newRow = 0

for (def row = 1; row <= findTestData('New').getRowNumbers(); row++) {
	WebUI.click(findTestObject('Page_SSCSVNumatcheck/input_SS_SSnum'))

	WebUI.setText(findTestObject('Object Repository/Page_SSCSVNumatcheck/input_SS_SSnum'), findTestData('NEW').getValue(
			'a', row))

	WebUI.uploadFile(findTestObject('Object Repository/Page_SSCSVNumatcheck/input_SSCSV_csvFileSS'), findTestData('NEW').getValue(
			'c', row))

	WebUI.waitForAlert(5)

	WebUI.uploadFile(findTestObject('uploadObject/Page_SSCSVNumatcheck/input_CSV_csvFileNP'), findTestData('NEW').getValue(
			'd', row))

	WebUI.waitForAlert(5)

	WebUI.click(findTestObject('Object Repository/Page_SSCSVNumatcheck/input_SS_button'))

	WebUI.waitForPageLoad(60)

 
/*①From now new page will be open*/

	WebUI.switchToWindowIndex(1) //switches to 2nd window
	secondUrl = WebUI.getUrl()
	println("secondUrl: " + secondUrl)

/*② I acquire second page’s url*/

	GlobalVariable.url = WebUI.getUrl()

	WebUI.switchToWindowIndex(0) //switches to 1st window
	firstUrl = WebUI.getUrl()
	println("firstUrl: " + firstUrl)
	
	WebUI.waitForAlert(5)
	
/*③After I acquire second page’s url, Close second page */

	WebUI.closeWindowIndex(1)
	WebUI.waitForAlert(5)
	
/*Turn to the First page, then loop ①~③ process */
}


gTestIdPathWay =  "C:\\Users\\hojun.lee\\Katalon Studio\\excel.xlsx"
gUrl = GlobalVariable.url

newRow += 3
Row row = sheet.createRow(newRow);

/* open connection to MS Excel, save surname to sheet 1, cell A2, close connection */
FileInputStream fis = new FileInputStream (gTestIdPathWay);
XSSFWorkbook workbook = new XSSFWorkbook (fis);

XSSFSheet sheet = workbook.getSheet("Sheet1");

// cell A2
Row row = sheet.createRow(1);
Cell cell = row.createCell(0);

cell.setCellValue(gUrl);

FileOutputStream fos = new FileOutputStream(gTestIdPathWay);
workbook.write(fos);
fos.close();
fis.close();

@grylion54
I added the code as you said, but the code doesn’t move. Can you tell me if there’s anything to add in the for phrase? Since Globalvariable.url is designated as a string, more than one value does not seem to be included, so how should we deal with this case?

I think you need to include the write to spreadsheet within the for loop.

Maybe like:
WebUI.openBrowser('')

'Stick to this page(for Download + Upload)'
WebUI.navigateToUrl('https://kusa89.jp/ss/index_comb_ss.html')

'ID'
WebUI.setText(findTestObject('Object Repository/Page_SSCSVNumatcheck/input__f_itcu'), 'hojun.lee')

'PASSWORD'
WebUI.setEncryptedText(findTestObject('Object Repository/Page_SSCSVNumatcheck/input__f_itcp'), '90Dp6xqc0oO/YegCFWVM4w==')

WebUI.sendKeys(findTestObject('Object Repository/Page_SSCSVNumatcheck/input__f_itcp'), Keys.chord(Keys.ENTER))

WebUI.click(findTestObject('Object Repository/Page_SSCSVNumatcheck/a_KICS'))

WebUI.click(findTestObject('Object Repository/Page_SSCSVNumatcheck/a_'))

WebUI.click(findTestObject('Object Repository/Page_SSCSVNumatcheck/a_PonyoPonyo on KICS V2'))

int newRow = 1
gTestIdPathWay =  "C:\\Users\\hojun.lee\\Katalon Studio\\excel.xlsx"

for (def row = 1; row <= findTestData('New').getRowNumbers(); row++) {
	WebUI.click(findTestObject('Page_SSCSVNumatcheck/input_SS_SSnum'))

	WebUI.setText(findTestObject('Object Repository/Page_SSCSVNumatcheck/input_SS_SSnum'), findTestData('NEW').getValue(
			'a', row))

	WebUI.uploadFile(findTestObject('Object Repository/Page_SSCSVNumatcheck/input_SSCSV_csvFileSS'), findTestData('NEW').getValue(
			'c', row))

	WebUI.waitForAlert(5)

	WebUI.uploadFile(findTestObject('uploadObject/Page_SSCSVNumatcheck/input_CSV_csvFileNP'), findTestData('NEW').getValue(
			'd', row))

	WebUI.waitForAlert(5)

	WebUI.click(findTestObject('Object Repository/Page_SSCSVNumatcheck/input_SS_button'))

	WebUI.waitForPageLoad(60)

 
/*①From now new page will be open*/

	WebUI.switchToWindowIndex(1) //switches to 2nd window
	secondUrl = WebUI.getUrl()
	println("secondUrl: " + secondUrl)

/*② I acquire second page’s url*/

	GlobalVariable.url = WebUI.getUrl()

	WebUI.switchToWindowIndex(0) //switches to 1st window
	firstUrl = WebUI.getUrl()
	println("firstUrl: " + firstUrl)
	
	WebUI.waitForAlert(5)
	
/*③After I acquire second page’s url, Close second page */

	WebUI.closeWindowIndex(1)
	WebUI.waitForAlert(5)

    /* open connection to MS Excel, save surname to sheet 1, cell A2, close connection */
    FileInputStream fis = new FileInputStream (gTestIdPathWay);
    XSSFWorkbook workbook = new XSSFWorkbook (fis);

    XSSFSheet sheet = workbook.getSheet("Sheet1");

    // cell A2
    Row row = sheet.createRow(newRow );
    Cell cell = row.createCell(0);

    cell.setCellValue(GlobalVariable.url);

    newRow += 3

    FileOutputStream fos = new FileOutputStream(gTestIdPathWay);
    workbook.write(fos);
    fos.close();
    fis.close();
    /*Turn to the First page, then loop ①~③ process */
}

@grylion54
thanks for reply.
there is one error below

2022-01-28 13:14:36.508 ERROR c.k.katalon.core.main.TestCaseExecutor - :x: Test Cases/tasy FAILED.
Reason:
org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed:
file:/C:/Users/hojun.lee/Katalon%20Studio/NEW/Scripts/tasy/Script1643158270283.groovy: 95: The current scope already contains a variable of the name row
@ line 95, column 6.
Row row = sheet.createRow(newRow );
^

1 error

Your choice is to change the loop counter to something else or the reference for the Row. So maybe we will change the Row reference, like:

    Row excelRow = sheet.createRow(newRow );
    Cell cell = excelRow.createCell(0);

@grylion54
thanks for reply. I added what you suggested.
but, there is same error below

2022-01-28 15:36:14.898 ERROR c.k.katalon.core.main.TestCaseExecutor - :x: Test Cases/tasy FAILED.
Reason:
org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed:
file:/C:/Users/hojun.lee/Katalon%20Studio/NEW/Scripts/tasy/Script1643158270283.groovy: 49: The current scope already contains a variable of the name row
@ line 49, column 6.
Row row = sheet.createRow(newRow );
^

1 error

WebUI.openBrowser('')

'Stick to this page(for Download + Upload)'
WebUI.navigateToUrl('https://kusa89.jp/ss/index_comb_ss.html')

'ID'
WebUI.setText(findTestObject('Object Repository/Page_SSCSVNumatcheck/input__f_itcu'), 'hojun.lee')

'PASSWORD'
WebUI.setEncryptedText(findTestObject('Object Repository/Page_SSCSVNumatcheck/input__f_itcp'), '90Dp6xqc0oO/YegCFWVM4w==')

WebUI.sendKeys(findTestObject('Object Repository/Page_SSCSVNumatcheck/input__f_itcp'), Keys.chord(Keys.ENTER))

WebUI.click(findTestObject('Object Repository/Page_SSCSVNumatcheck/a_KICS'))

WebUI.click(findTestObject('Object Repository/Page_SSCSVNumatcheck/a_'))

WebUI.click(findTestObject('Object Repository/Page_SSCSVNumatcheck/a_PonyoPonyo on KICS V2'))

int newRow = 1
gTestIdPathWay =  "C:\\Users\\hojun.lee\\Katalon Studio\\excel.xlsx"

for (def row = 1; row <= findTestData('New').getRowNumbers(); row++) {
	
	Row row = sheet.createRow(newRow );
	
	WebUI.click(findTestObject('Page_SSCSVNumatcheck/input_SS_SSnum'))

	WebUI.setText(findTestObject('Object Repository/Page_SSCSVNumatcheck/input_SS_SSnum'), findTestData('NEW').getValue(
			'a', row))

	WebUI.uploadFile(findTestObject('Object Repository/Page_SSCSVNumatcheck/input_SSCSV_csvFileSS'), findTestData('NEW').getValue(
			'c', row))

	WebUI.waitForAlert(5)

	WebUI.uploadFile(findTestObject('uploadObject/Page_SSCSVNumatcheck/input_CSV_csvFileNP'), findTestData('NEW').getValue(
			'd', row))

	WebUI.waitForAlert(5)

	WebUI.click(findTestObject('Object Repository/Page_SSCSVNumatcheck/input_SS_button'))

	WebUI.waitForPageLoad(60)

/*①From now new page will be open*/

	WebUI.switchToWindowIndex(1) //switches to 2nd window
	secondUrl = WebUI.getUrl()
	println("secondUrl: " + secondUrl)

/*② I acquire second page’s url*/

	GlobalVariable.url = WebUI.getUrl()

	WebUI.switchToWindowIndex(0) //switches to 1st window
	firstUrl = WebUI.getUrl()
	println("firstUrl: " + firstUrl)
	
	WebUI.waitForAlert(5)
	
/*③After I acquire second page’s url, Close second page */

	WebUI.closeWindowIndex(1)
	WebUI.waitForAlert(5)

	/* open connection to MS Excel, save surname to sheet 1, cell A2, close connection */
	FileInputStream fis = new FileInputStream (gTestIdPathWay);
	XSSFWorkbook workbook = new XSSFWorkbook (fis);

	XSSFSheet sheet = workbook.getSheet("Sheet1");

    Row excelRow = sheet.createRow(newRow );
    Cell cell = excelRow.createCell(0);
	
	cell.setCellValue(GlobalVariable.url);

	newRow += 3

	FileOutputStream fos = new FileOutputStream(gTestIdPathWay);
	workbook.write(fos);
	fos.close();
	fis.close();
	/*Turn to the First page, then loop ①~③ process */
}

@grylion54
I think this kind of error will happen because there’s only one global variable I’ve made.
Obviously, the URL is classified as String, so it is questionable whether the URL will fit well into the row if you use List.
Do you think that if you want to receive multiple urls, you should add more Globalvariable? In that case, please let me know how to set up GlobalVariable and change the code.

This error message states that you did not change the reference as I suggested in my post above. If you did change it, then maybe shut down KS, restart it and try again.

You get the URL twice in your code. Which reference did you want to Save?

Maybe you can read up on how to create a Keyword for the Saving routine and call it any number of times.
Sample Custom Keywords | Katalon Docs

Does your Test Suite go over more than 1 Test Case?

How to set up a Global Variable:
Global Variables and Execution Profile | Katalon Docs

@grylion54
thanks for reply.

You get the URL twice in your code. Which reference did you want to Save?

I want to get URL from Data file(a number of rows)

Maybe you can read up on how to create a Keyword for the Saving routine and call it any number of times.

It’s possible, but I don’t think it’s necessary. What kind of customized keyword should I make?

Sample Custom Keywords | Katalon Docs

Does your Test Suite go over more than 1 Test Case?

1 more Test Case is required (before get URL)

How to set up a Global Variable:
Global Variables and Execution Profile | Katalon Docs

@grylion54
Can you write down the full code including the reference I forgot to write?