Katalon studio write excel custom keyword

Hi all, thanks to the keyword below you can add data into excel. Also every time you run the test will add to the next line. Copy the following code as Custom Keyword and use the string parameter to give it a test step.

 package test
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 com.kms.katalon.core.annotation.Keyword
import com.kms.katalon.core.checkpoint.Checkpoint
import com.kms.katalon.core.checkpoint.CheckpointFactory
import com.kms.katalon.core.mobile.keyword.MobileBuiltInKeywords
import com.kms.katalon.core.model.FailureHandling
import com.kms.katalon.core.testcase.TestCase
import com.kms.katalon.core.testcase.TestCaseFactory
import com.kms.katalon.core.testdata.TestData
import com.kms.katalon.core.testdata.TestDataFactory
import com.kms.katalon.core.testobject.ObjectRepository
import com.kms.katalon.core.testobject.TestObject
import com.kms.katalon.core.webservice.keyword.WSBuiltInKeywords
import com.kms.katalon.core.webui.keyword.WebUiBuiltInKeywords
import internal.GlobalVariable
import MobileBuiltInKeywords as Mobile
import WSBuiltInKeywords as WS
import WebUiBuiltInKeywords as WebUI
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
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;
import java.util.Date as Date
import org.apache.poi.xssf.usermodel.XSSFCell as XSSFCell
import org.apache.poi.xssf.usermodel.XSSFRow as XSSFRow
import org.apache.poi.xssf.usermodel.XSSFSheet as XSSFSheet
import org.apache.poi.xssf.usermodel.XSSFWorkbook as XSSFWorkbook
import java.lang.String as String
import org.apache.poi.ss.usermodel.Cell as Cell
import org.apache.poi.ss.usermodel.Row as Row
import org.apache.poi.hssf.usermodel.HSSFCell as HSSFCell
import org.apache.poi.hssf.usermodel.HSSFCellStyle as HSSFCellStyle
import org.apache.poi.hssf.usermodel.HSSFDataFormat as HSSFDataFormat
import org.apache.poi.hssf.usermodel.HSSFRow as HSSFRow
import org.apache.poi.hssf.usermodel.HSSFSheet as HSSFSheet
import org.apache.poi.hssf.usermodel.HSSFWorkbook as HSSFWorkbook
import org.apache.poi.hssf.util.HSSFColor as HSSFColor
import java.time.LocalDateTime as LocalDateTime
import java.time.format.DateTimeFormatter as DateTimeFormatter
import java.time.LocalDate as LocalDate



public class excel {

	@Keyword


	public void demoKey(String result) throws IOException{



		FileInputStream file = new FileInputStream (new File("C:\\Users\\test\\Desktop\\test.xlsx"))
		XSSFWorkbook workbook = new XSSFWorkbook(file);
		XSSFSheet sheet = workbook.getSheetAt(0);


		try {
			String Data_fromCell=sheet.getRow(1).getCell(0).getStringCellValue();
		}
		catch (Exception ex)
		{
			int rowCount = sheet.getLastRowNum();
			Row row = sheet.createRow(rowCount+1);
			Cell cell = row.createCell(0);
			cell.setCellType(cell.CELL_TYPE_STRING);
			cell.setCellValue("null");

		}


		for (int i = 1; i <= sheet.getLastRowNum(); i++) {
			'Read data from excel'
			String Data_fromCell=sheet.getRow(i).getCell(0).getStringCellValue();
			if (Data_fromCell == "null") 
				'Write data to excel'
				String myDate = DateTimeFormatter.ofPattern("dd/MM/yyyy").format(LocalDate.now());
				sheet.getRow(i).createCell(0).setCellValue(result);
				sheet.getRow(i).createCell(1).setCellValue(myDate);
			}
		}

	
		int rowCount = sheet.getLastRowNum();
		Row row = sheet.createRow(rowCount+1);
		Cell cell = row.createCell(0);
		cell.setCellType(cell.CELL_TYPE_STRING);
		cell.setCellValue("null");
		file.close();
		FileOutputStream outFile =new FileOutputStream(new File("C:\\Users\\test\\Desktop\\test.xlsx"));
		workbook.write(outFile);
		outFile.close();
	}

}

Example screen shot

Example Project

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 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.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 internal.GlobalVariable as GlobalVariable
import java.io.FileInputStream as FileInputStream
import java.io.FileNotFoundException as FileNotFoundException
import java.io.IOException as IOException
import java.util.Date as Date
import org.apache.poi.xssf.usermodel.XSSFCell as XSSFCell
import org.apache.poi.xssf.usermodel.XSSFRow as XSSFRow
import org.apache.poi.xssf.usermodel.XSSFSheet as XSSFSheet
import org.apache.poi.xssf.usermodel.XSSFWorkbook as XSSFWorkbook
import java.lang.String as String
import org.apache.poi.ss.usermodel.Cell as Cell
import org.apache.poi.ss.usermodel.Row as Row
import org.apache.poi.hssf.usermodel.HSSFCell as HSSFCell
import org.apache.poi.hssf.usermodel.HSSFCellStyle as HSSFCellStyle
import org.apache.poi.hssf.usermodel.HSSFDataFormat as HSSFDataFormat
import org.apache.poi.hssf.usermodel.HSSFRow as HSSFRow
import org.apache.poi.hssf.usermodel.HSSFSheet as HSSFSheet
import org.apache.poi.hssf.usermodel.HSSFWorkbook as HSSFWorkbook
import org.apache.poi.hssf.util.HSSFColor as HSSFColor
import java.time.LocalDateTime as LocalDateTime
import java.time.format.DateTimeFormatter as DateTimeFormatter
import java.time.LocalDate as LocalDate

WebUI.openBrowser('')

WebUI.navigateToUrl('https://katalon-demo-cura.herokuapp.com/')

WebUI.click(findTestObject('Object Repository/Write excel test case/Page_CURA Healthcare Service/a_Make Appointment'))

WebUI.setText(findTestObject('Object Repository/Write excel test case/Page_CURA Healthcare Service/input_Username_username'), 
    'John Doe')

WebUI.setEncryptedText(findTestObject('Object Repository/Write excel test case/Page_CURA Healthcare Service/input_Password_password'), 
    'g3/DOGG74jC3Flrr3yH+3D/yKbOqqUNM')

WebUI.click(findTestObject('Object Repository/Write excel test case/Page_CURA Healthcare Service/button_Login'))

WebUI.click(findTestObject('Object Repository/Write excel test case/Page_CURA Healthcare Service/h2_Make Appointment'))

WebUI.delay(1)

result = WebUI.getText(findTestObject('Object Repository/Write excel test case/Page_CURA Healthcare Service/h2_Make Appointment'))

CustomKeywords.'test.excel.demoKey'(result)

For more detailed explanation : https://emineakturkblog.wordpress.com

1 Like

trying to implement your custom keyword and having these warnings:

any idea on how to fix this?

hello @pgonzalez

First of all, you should make sure that you define a new custom keyword. The errors you received are not actually errors, it only warns that these libraries have never been used. So it is not an obstacle for code to work.
You must replace the file path in the Keyword definition with the excel file extension on your local computer.
because in my code my file extension is new File (“C: \ Users \ test \ Desktop \ test.xlsx” writes. 2 places in this definition of a file opening and the second is closing the file. In addition, it should work without error.

To add custom keyword


Please contact me if another error occurs.

我循环跑一个流程两遍,每次流程应该是生成不同的数据,但是保存到excel后,是输出两个第二遍生成的数据,请问这个要怎么改,才能将两个数据都输进去。

Hi,
Is it possible that custom keyword will take nex excel name from url which is desribed in my test case as variable ?

Just to easy identify the generated file.

Yes, it should be possible.

Hi,
I have try this keyword, but it looks like have few problems in it:

Hit CTRL + SHIFT + O (oh) and it will set the import statements that you need and remove those that you do not. Unless you are working on a Mobile test case or Web Server test case, you do not need many of the default imports that KS displays. In the case of @emine’s import list, there are a few duplicates, so removing some should tidy it up.

There is an issue with a missing curly bracket after one of the if statements though.

Where to hit this combinantion in customkeyword or test case ?

You can hit it in any Test Case or Custom Keyword “page”. It will work for both. In this case, you should do it in the Custom Keyword page you are creating.

If you want, create a copy of the page beforehand to see what it removed and what it left.

Saying this, are you familiar with creating a Custom Keyword page/class?

1 Like