Hi,
I know this is an “old” topic. But just my 2cents: I have written a custom keyword that allows writing a property to an excel and later on when I need it to read from that excel the propertyValue. In theory, it can be used across Test Suites. I use it for 2 test cases that run in the same test suite.
Setup:
In my global profiles I have a property called "transferPropertiesPath " that with value the folder that contains the excel defined in my test data = “transferPropertiesToExcel.xlsx”.
In the setup script of the testSuite I run below code to first clear all worksheets from the excel.
@SetUp(skipped = false) // Please change skipped to be false to activate this method.
def setUp() {
try{
File file = new File (TestDataFactory.findTestData(GlobalVariable.transferPropertiesPath).sourceUrl)
boolean exist = file.exists()
if (exist){
FileInputStream inFile = new FileInputStream (new File (TestDataFactory.findTestData(GlobalVariable.transferPropertiesPath).sourceUrl))
XSSFWorkbook workbook = new XSSFWorkbook(inFile);
XSSFSheet sheet
int numberOfSheets = workbook.getNumberOfSheets()
KeywordUtil.logInfo('Setup script - TransferPropertiesToExcel numberOfSheets = ' + numberOfSheets.toString())
for (int i = 1; i<= numberOfSheets; i++){
workbook.removeSheetAt(numberOfSheets-i)
}
workbook.createSheet('sheet')
inFile.close();
FileOutputStream outFile = new FileOutputStream(new File(TestDataFactory.findTestData('Mortgage/i18n/transferPropertiesToExcel').sourceUrl));
workbook.write(outFile);
outFile.close()
}
}
catch(Exception ex) {
println("Catching the exception in Setup script CreateFullRequest");
}
}
The actual Keywords script in TransferProperties.groovy =
package be.fednot.common.test.support.katalon
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRow
import com.kms.katalon.core.annotation.Keyword
import com.kms.katalon.core.testdata.TestDataFactory
import com.kms.katalon.core.util.KeywordUtil
import com.kms.katalon.core.testdata.reader.ExcelFactory
import internal.GlobalVariable
class TransferPropertiesToExcel {
/**
* Transfer the given parameter name and it's value to an excel in the transferPropertiesPath defined in the profile.
*/
@Keyword
def transferPropertiesToExcel(String parameter, String value) {
try{
FileInputStream file = new FileInputStream (new File (TestDataFactory.findTestData(GlobalVariable.transferPropertiesPath).sourceUrl))
XSSFWorkbook workbook = new XSSFWorkbook(file);
XSSFSheet sheet
String testName = GlobalVariable.currentTestCaseId
//KeywordUtil.logInfo("GlobalVariable.currentTestCaseId = " + testName)
if (testName != '' && testName != null){
def index = workbook.getSheetIndex(testName.take(31))
if (index < 0){
KeywordUtil.logInfo "TransferPropertiesToExcel : testName.take(31) = " + testName.take(31) + ". Create sheet because index = - 1 --> "+ index
sheet = workbook.createSheet(testName)
}
else{
sheet = workbook.getSheetAt(index)
}
}
else{
sheet = workbook.getSheetAt(0);
}
int numberOfRows = sheet.getPhysicalNumberOfRows()
if(numberOfRows == 0){
XSSFRow headerRow = sheet.createRow(0)
headerRow.createCell(0).setCellValue("parameter")
headerRow.createCell(1).setCellValue("value")
}
XSSFRow currentRow = sheet.createRow(sheet.getLastRowNum()+1)
currentRow.createCell(0).setCellValue(parameter);
currentRow.createCell(1).setCellValue(value);
file.close();
FileOutputStream outFile =new FileOutputStream(new File(TestDataFactory.findTestData('ABC/i18n/transferPropertiesToExcel').sourceUrl));
workbook.write(outFile);
outFile.close()
}catch (FileNotFoundException ex) {
KeywordUtil.markWarning("FileNotFoundException on the TransferPropertiesToExcel! The file should NOT be open during run. We are not storing properties in the excel!")
}catch (Exception ex) {
KeywordUtil.markWarning("General exception (not FileNotFoundException) on the TransferPropertiesToExcel! We are not storing properties in the excel!")
}
}
@Keyword
def getValueFromExcel (String parameter, String sheetName){
//create an instance. . . .
//the last parameter (boolean) allows your first row to become a column name so that it //will be easy for you to define which column you are accessing. If false, it will be //counted as an excel record. And you don’t have any identification which column you //want to get. So I suggest making it true (it depends on your test).
try{
Object excelData = ExcelFactory.getExcelDataWithDefaultSheet(TestDataFactory.findTestData(GlobalVariable.transferPropertiesPath).sourceUrl, sheetName, true)
List<List<Object>> allData = excelData.getAllData()
int dataLength = allData.size()
//KeywordUtil.logInfo("dataLength = " + dataLength.toString())
boolean isParameter = false
for(int i=0; i<dataLength; i++){
List<String> row = allData.get(i);
isParameter = row.get(0).equals(parameter)
if (isParameter){
def value = row.get(1)
KeywordUtil.logInfo("Parameter \"$parameter\" found! Returned value on row $i = " + value)
return value
}
}
KeywordUtil.markWarning("No value found for parameter $parameter in the excel with $sheetName! The total number of rows is $dataLength.")
} catch (FileNotFoundException ex) {
KeywordUtil.markWarning("FileNotFoundException on the TransferPropertiesToExcel! The file should NOT be open during run. We are not storing properties in the excel!")
} catch (Exception ex) {
KeywordUtil.markWarning("General exception (not FileNotFoundException) on the TransferPropertiesToExcel! We are not storing properties in the excel!")
}
}
}
To write a property to the excel:
CustomKeywords.‘be.fednot.common.test.support.katalon.TransferPropertiesToExcel.transferPropertiesToExcel’(‘parameterNameYouWantToSet’, parameterValue)
To get a property from the excel:
CustomKeywords.‘be.fednot.common.test.support.katalon.TransferPropertiesToExcel.getValueFromExcel’(‘parameterNameYouWantToGet’, ‘worksheetOfExcelYouWantToTarget’)