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