Currently I create different data file for POST and PUT. So my test case for POST or PUT just call individual data file.
I’m thinking of merge all test data into one excel file, then each test case call same excel file but different column. I’m not sure if Kalaton supporting this. How to achieve, anyone did it please guide me.
hello,
yeah, you can do that with CustomKeyword
e.g this page
Thanks. I’ll explore this. Is codebun external plugin? Or katalon built in plugin?
i could not find exact solution , what syntax to use to locate specific row/column in excel , or different worksheet within same excel file supported?
hi,
are you using excel file format .xls or xlsx?
I have implemented custom keywords for both of them.
let me know which one you are used
I’m using excal in format xlsx.
What is the different if the file format is different?
hi,
you can play with these classes
TestCase
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import org.apache.commons.io.FilenameUtils
SimpleDateFormat sdf = new SimpleDateFormat("yyyy_MM_dd_HH_mm_ss");
Timestamp timestamp = new Timestamp(System.currentTimeMillis());
System.out.println(sdf.format(timestamp));
String stamp = sdf.format(timestamp);
def valToExcel = "car"
int addtoRow = 11
int addToColumn = 0
String xlsPath = System.getProperty("user.dir")+"\\ExcelFiles\\"+stamp+"_FileXlsData.xls";
String xlsxPath = System.getProperty("user.dir")+"\\ExcelFiles\\"+stamp+"_FileXlsxData.xlsx";
List<String> headerValues = new ArrayList<String>();
List<String> excelValues = new ArrayList<String>();
List<String> headerValuesXlsx = new ArrayList<String>();
List<String> excelValuesXlsx = new ArrayList<String>();
String sheetName = "SheetName"
String xlsxSheetName = "TestSheet"
String path = System.getProperty("user.dir")+"\\ExcelFiles\\xlsData.xls";
String pathXlsx = System.getProperty("user.dir")+"\\ExcelFiles\\xlsxData.xlsx";
int processRows = 2; //how many rows to get processed
int start = 0;
int end = 1;
//values to new excel file
List <String> excelTo = new ArrayList<>()
excelTo.add("cat")
excelTo.add("dog")
excelTo.add("pig")
List <String> columns = new ArrayList<>()
columns.add("One")
columns.add("Two")
columns.add("Three")
columns.add("Four")
columns.add("Five")
//verify excel format
String ext = FilenameUtils.getExtension(pathXlsx);
if (ext.equals("xls")){
println "processing .xls file"
String fileName = "xlsData.xls"
//Get the Latest excel file from folder
String filePath = System.getProperty("user.dir")+"\\ExcelFiles\\"
//def latestExcelFile = CustomKeywords.'excelHelper.GetLatestExcelFile.getTheNewestFile'(filePath)
//println latestExcelFile
//create xls
CustomKeywords.'excelHelper.ExcelUtil.ExcelHelperGreateExcelFileWithColumnsName'(xlsPath, xlsxSheetName, columns)//create new excel
//update excel from list
CustomKeywords.'excelHelper.ExcelUtil.ExcelHelperUpdateFromList'(excelTo, addtoRow, addToColumn, path, sheetName)//add or update value to cell
//update exact value
CustomKeywords.'excelHelper.ExcelUtil.ExcelHelperUpdateExactValue'(valToExcel, addtoRow, addToColumn, path, sheetName)//add or update value to cell
//get count of columns
int countOfColums = CustomKeywords.'excelHelper.ExcelUtil.ExcelHelperGetColumnCount'(path, sheetName)//get count of columns
//get header values
headerValues = CustomKeywords.'excelHelper.ExcelUtil.ExcelHelperRead'(countOfColums, start, end, path, sheetName)//get header values
start = 1;
end = 2;
for (int x = 0; x < processRows; x++) {
excelValues = CustomKeywords.'excelHelper.ExcelUtil.ExcelHelperRead'(headerValues.size(), start, end, path, sheetName)
int i = 0;
for (String s : headerValues) {
if (excelValues.get(i).equals("**No Value**")) {
System.out.println("Row " + start + " Header " + headerValues.get(i) + " has not value ");
} else {
System.out.println("Row " + start + " Header " + headerValues.get(i) + " has value " + excelValues.get(i));
}
i++;
}
start++; //increase start & end to get data rows
end++;
}
}
else{
println "processing .xlsx file"
String fileName = "xlsxData.xlsx"
//latestExcelFile = CustomKeywords.'excelHelper.GetLatestExcelFile.getTheNewestFile'(filePath)
//create xlsx
CustomKeywords.'excelHelper.UpdateXlsxFile.ExcelHelperGreateExcelFileWithColumnsNameXlsx'(xlsxPath, xlsxSheetName, columns)//create new excel
//add data from the list
CustomKeywords.'excelHelper.UpdateXlsxFile.updateXlsxFromList'(excelTo, addtoRow, addToColumn, pathXlsx, xlsxSheetName, fileName)
//update exact value
CustomKeywords.'excelHelper.UpdateXlsxFile.ExcelHelperUpdateExactValueXlsx'(valToExcel, addtoRow, addToColumn, pathXlsx, xlsxSheetName, fileName)//add or update value to cell
//int cols = CustomKeywords.'excelHelper.UpdateXlsxFile.ExcelHelperGetColumnCountXlsx'(xlsxPath, xlsxSheetName)
//println cols
int countOfColumsXlsx = CustomKeywords.'excelHelper.UpdateXlsxFile.ExcelHelperGetColumnCountXlsx'(pathXlsx, sheetName)//get count of columns
headerValuesXlsx = CustomKeywords.'excelHelper.UpdateXlsxFile.ExcelHelperReadXlsx'(countOfColumsXlsx, start, end, pathXlsx, sheetName)//get header values
start = 1;
end = 2;
for (int z = 0; z < processRows; z++) {
excelValuesXlsx = CustomKeywords.'excelHelper.UpdateXlsxFile.ExcelHelperReadXlsx'(headerValuesXlsx.size(), start, end, pathXlsx, sheetName)
int y = 0;
for (String s : headerValuesXlsx) {
if (excelValuesXlsx.get(y).equals("**No Value**")) {
System.out.println("Row " + start + " Header " + headerValuesXlsx.get(y) + " has not value ");
} else {
System.out.println("Row " + start + " Header " + headerValuesXlsx.get(y) + " has value " + excelValuesXlsx.get(y));
}
y++;
}
start++; //increase start & end to get data rows
end++;
}
}
Keywords:
This class is for .xls
package excelHelper
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.cucumber.keyword.CucumberBuiltinKeywords as CucumberKW
import com.kms.katalon.core.mobile.keyword.MobileBuiltInKeywords as Mobile
import com.kms.katalon.core.model.FailureHandling
import com.kms.katalon.core.testcase.TestCase
import com.kms.katalon.core.testdata.TestData
import com.kms.katalon.core.testobject.TestObject
import com.kms.katalon.core.webservice.keyword.WSBuiltInKeywords as WS
import com.kms.katalon.core.webui.keyword.WebUiBuiltInKeywords as WebUI
import internal.GlobalVariable
import com.kms.katalon.core.exception.StepErrorException;
import com.kms.katalon.core.exception.StepFailedException;
import com.kms.katalon.core.logging.ErrorCollector;
import com.kms.katalon.core.logging.KeywordLogger;
import com.kms.katalon.core.util.KeywordUtil
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.Row.MissingCellPolicy
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.xssf.usermodel.*;
import java.util.ArrayList;
import java.util.List;
public class ExcelUtil {
FileInputStream fsIP
HSSFWorkbook wb
HSSFSheet worksheet
FileOutputStream outputFile
KeywordUtil logger
public ExcelUtil(){
logger = new KeywordUtil()
}
@Keyword
public void ExcelHelperGreateExcelFileWithColumnsName(String path, String sheetName, List<String>columns){
//Workbook workbook = new XSSFWorkbook();
Workbook workbook = new HSSFWorkbook();
/* CreationHelper helps us create instances of various things like DataFormat,
Hyperlink, RichTextString etc, in a format (HSSF, XSSF) independent way */
//CreationHelper createHelper = workbook.getCreationHelper
File file = new File(path);
if(file.isFile() && file.exists()) {
logger.logInfo("File exists already return!");
return
} else {
logger.logInfo("file open successfully.");
}
//Create file system using specific name
outputFile = new FileOutputStream(file);
Sheet sheet = workbook.createSheet(sheetName);
// Create a Font for styling header cells
Font headerFont = workbook.createFont();
headerFont.setBold(true);
headerFont.setFontHeightInPoints((short) 12);
headerFont.setColor(IndexedColors.RED.getIndex());
// Create a CellStyle with the font
CellStyle headerCellStyle = workbook.createCellStyle();
headerCellStyle.setFont(headerFont);
// Create a Row
Row headerRow = sheet.createRow(0);
// Create cells
for(int i = 0; i < columns.size(); i++) {
Cell cell = headerRow.createCell(i);
cell.setCellValue(columns[i]);
cell.setCellStyle(headerCellStyle);
}
// Create Cell Style for formatting Date for xlsx
//CellStyle dateCellStyle = workbook.createCellStyle();
//dateCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd-MM-yyyy"));
//FileOutputStream fileOut = new FileOutputStream(path);
workbook.write(outputFile);
workbook.close();
}
@Keyword
public int ExcelHelperGetColumnCount(String path, String sheetName) throws IOException, InvalidFormatException {
int noOfColumns = 0
// Check the file extension
if (!path.endsWith(".xls")) {
throw new IllegalArgumentException("Unknown file type. Please use .xls");
}
try
{
Workbook book = WorkbookFactory.create(new File(path));
//Get first/desired sheet from the workbook
Sheet sheet = book.getSheet(sheetName);
noOfColumns = sheet.getRow(0).getLastCellNum();
}
catch(Exception ex){
logger.logInfo(ex)
}
return noOfColumns;
}
@Keyword
public List<String> ExcelHelperRead(int colCount, int start, int end, String path, String sheetName) throws IOException, InvalidFormatException {
// Check the file extension
if (!path.endsWith(".xls")) {
throw new IllegalArgumentException("Unknown file type. Please use .xls");
}
List<String> excelValues = new ArrayList<String>();
int MY_MINIMUM_COLUMN_COUNT = colCount;
try
{
Workbook book = WorkbookFactory.create(new File(path));
//Get first/desired sheet from the workbook
Sheet sheet = book.getSheet(sheetName);
// Create a DataFormatter to format and get each cell's value as String
DataFormatter dataFormatter = new DataFormatter();
// Decide which rows to process
int rowStart = start;
int rowEnd = end;
for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
Row r = sheet.getRow(rowNum);
if (r == null) {
// This whole row is empty
// Handle it as needed
continue;
}
int lastColumn = Math.max(r.getLastCellNum(), MY_MINIMUM_COLUMN_COUNT);
//System.out.println("last column "+lastColumn);
for (int cn = 0; cn < lastColumn; cn++) {
Cell c = r.getCell(cn, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
if (c == null) {
excelValues.add("**No Value**");
// The spreadsheet is empty in this cell
} else {
// Do something useful with the cell's contents
excelValues.add(dataFormatter.formatCellValue(c));
}
}
}
}
catch (Exception ex){
logger.logInfo(ex)
}
return excelValues;
}
@Keyword
public List<String> ExcelHelperUpdateFromList(List<String> excel, int rw, int col, String path, String sheetName) throws IOException, InvalidFormatException {
// Check the file extension
if (!path.endsWith(".xls")) {
throw new IllegalArgumentException("Unknown file type. Please use .xls");
}
try
{
fsIP= new FileInputStream(new File(path)); //Read the spreadsheet that needs to be updated
wb = new HSSFWorkbook(fsIP); //Access the workbook
worksheet = wb.getSheet(sheetName); //Access the worksheet, so that we can update / modify it.
Row row = worksheet.getRow(rw); //row to update
if (row == null) {
row = worksheet.createRow(rw);
}
int c = col
for (String s : excel){
Cell cell = row.getCell(c, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);//check if cell is null
if (cell == null || cell.getCellTypeEnum() == CellType.BLANK){
cell = row.createCell(c);
cell.setCellValue(s);
//worksheet.createRow(rw).createCell(c).setCellValue(s); //if cell is null then update it to new value
}
else{
//cell = row.createCell(c);
cell.setCellValue(s);
}
c++
}
}
catch(Exception ex){
logger.logInfo(ex)
}
finally{
fsIP.close(); //Close the InputStream
outputFile =new FileOutputStream(new File(path)); //Open FileOutputStream to write updates
wb.write(outputFile); //write changes
outputFile.close(); //close the stream
}
}
@Keyword
public List<String> ExcelHelperUpdateExactValue(String value, int rw, int col, String path, String sheetName) throws IOException, InvalidFormatException {
// Check the file extension
if (!path.endsWith(".xls")) {
throw new IllegalArgumentException("Unknown file type. Please use .xls");
}
try
{
fsIP= new FileInputStream(new File(path)); //Read the spreadsheet that needs to be updated
wb = new HSSFWorkbook(fsIP); //Access the workbook
worksheet = wb.getSheet(sheetName); //Access the worksheet, so that we can update / modify it.
Row row = worksheet.getRow(rw); //row to update
if (row == null) {
row = worksheet.createRow(rw);
}
Cell cell = row.getCell(col, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);//check if cell is null
if (cell == null || cell.getCellTypeEnum() == CellType.BLANK){
cell = row.createCell(col);
cell.setCellValue(value);
}
else{
cell.setCellValue(value);
}
}
catch(Exception ex){
logger.logInfo(ex)
}
finally{
fsIP.close(); //Close the InputStream
outputFile =new FileOutputStream(new File(path)); //Open FileOutputStream to write updates
wb.write(outputFile); //write changes
outputFile.close(); //close the stream
}
}
}
This class is for .xlsx
package excelHelper
import java.io.IOException
import java.nio.file.Files
import java.nio.file.Path
import java.nio.file.Paths
import java.util.List
import org.apache.poi.openxml4j.exceptions.InvalidFormatException
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import java.text.SimpleDateFormat;
import com.kms.katalon.core.annotation.Keyword
import com.kms.katalon.core.util.KeywordUtil
public class UpdateXlsxFile {
KeywordUtil logger
FileOutputStream outputFile
Workbook workbook
public UpdateXlsxFile(){
logger = new KeywordUtil()
}
@Keyword
public void updateXlsxFromList(List<String> excel, int rw, int col, String path, String sheetName, String fileName){
File file = new File(path);
workbook = WorkbookFactory.create(file);
// Check the file extension
if (!path.endsWith(".xlsx")) {
throw new IllegalArgumentException("Unknown file type. Please use .xlsx");
}
try
{
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(rw); //row to update
if (row == null) {
row = sheet.createRow(rw);
}
int c = col
for (String s : excel){
Cell cell = row.getCell(c, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);//check if cell is null
if (cell == null || cell.getCellTypeEnum() == CellType.BLANK){
cell = row.createCell(c);
cell.setCellValue(s);
//worksheet.createRow(rw).createCell(c).setCellValue(s); //if cell is null then update it to new value
}
else{
//cell = row.createCell(c);
cell.setCellValue(s);
}
c++
}
}
catch(Exception ex){
logger.logInfo(ex)
}
String path2 = System.getProperty("user.dir")+"\\ExcelFiles\\xlsData_copy.xlsx";
// Write the output to the file
FileOutputStream fileOut = new FileOutputStream(path2);
workbook.write(fileOut);
fileOut.close();
// Closing the workbook
workbook.close();
//delete original file
File f = new File(path);
if(f.delete())
{
System.out.println("File deleted successfully");
}
else
{
System.out.println("Failed to delete the file");
}
//rename copied file
Path source = Paths.get(path2);
Files.move(source, source.resolveSibling(fileName));
}
@Keyword
public void ExcelHelperUpdateExactValueXlsx(String value, int rw, int col, String path, String sheetName, String fileName) throws IOException, InvalidFormatException {
// Check the file extension
if (!path.endsWith(".xlsx")) {
throw new IllegalArgumentException("Unknown file type. Please use .xlsx");
}
try
{
File file = new File(path);
Workbook workbook = WorkbookFactory.create(file);
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(rw); //row to update
if (row == null) {
row = sheet.createRow(rw);
}
Cell cell = row.getCell(col, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);//check if cell is null
if (cell == null || cell.getCellTypeEnum() == CellType.BLANK){
cell = row.createCell(col);
cell.setCellValue(value);
}
else{
cell.setCellValue(value);
}
String path2 = System.getProperty("user.dir")+"\\ExcelFiles\\xlsData_copy.xlsx";
// Write the output to the file
FileOutputStream fileOut = new FileOutputStream(path2);
workbook.write(fileOut);
fileOut.close();
// Closing the workbook
workbook.close();
//delete original file
File f = new File(path);
if(f.delete())
{
System.out.println("File deleted successfully");
}
else
{
System.out.println("Failed to delete the file");
}
//rename copied file
Path source = Paths.get(path2);
Files.move(source, source.resolveSibling(fileName));
}
catch(Exception ex){
logger.logInfo(ex)
}
}
@Keyword
public int ExcelHelperGetColumnCountXlsx(String path, String sheetName) throws IOException, InvalidFormatException {
int noOfColumns = 0
// Check the file extension
if (!path.endsWith(".xlsx")) {
throw new IllegalArgumentException("Unknown file type. Please use .xlsx");
}
try
{
//logger.logInfo("starting to calculate count of columns")
File file = new File(path);
Workbook workbook = WorkbookFactory.create(file);
//Get first/desired sheet from the workbook
Sheet sheet = workbook.getSheetAt(0); //int sheet 0 1 2 ...
//Sheet sheet = book.getSheet(sheetName);
noOfColumns = sheet.getRow(0).getLastCellNum();
//logger.logInfo("counting ended")
}
catch(Exception ex){
logger.logInfo(ex)
}
return noOfColumns;
}
@Keyword
public List<String> ExcelHelperReadXlsx(int colCount, int start, int end, String path, String sheetName) throws IOException, InvalidFormatException {
// Check the file extension
if (!path.endsWith(".xlsx")) {
throw new IllegalArgumentException("Unknown file type. Please use .xlsx");
}
List<String> excelValues = new ArrayList<String>();
int MY_MINIMUM_COLUMN_COUNT = colCount;
try
{
File file = new File(path);
Workbook workbook = WorkbookFactory.create(file);
//Get first/desired sheet from the workbook
Sheet sheet = workbook.getSheetAt(0); //int sheet 0 1 2 ...
// Create a DataFormatter to format and get each cell's value as String
DataFormatter dataFormatter = new DataFormatter();
// Decide which rows to process
int rowStart = start;
int rowEnd = end;
for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
Row r = sheet.getRow(rowNum);
if (r == null) {
// This whole row is empty
// Handle it as needed
continue;
}
int lastColumn = Math.max(r.getLastCellNum(), MY_MINIMUM_COLUMN_COUNT);
//System.out.println("last column "+lastColumn);
for (int cn = 0; cn < lastColumn; cn++) {
Cell c = r.getCell(cn, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
if (c == null) {
excelValues.add("**No Value**");
// The spreadsheet is empty in this cell
} else {
// Do something useful with the cell's contents
excelValues.add(dataFormatter.formatCellValue(c));
}
}
}
}
catch (Exception ex){
logger.logInfo(ex)
}
return excelValues;
}
@Keyword
public void ExcelHelperGreateExcelFileWithColumnsNameXlsx(String path, String sheetName, List<String>columns){
Cell cell
Workbook workbook = new XSSFWorkbook();
/* CreationHelper helps us create instances of various things like DataFormat,
Hyperlink, RichTextString etc, in a format (HSSF, XSSF) independent way */
CreationHelper createHelper = workbook.getCreationHelper()
File file = new File(path);
if(file.isFile() && file.exists()) {
logger.logInfo("File exists already return!");
return
} else {
logger.logInfo("file open successfully.");
}
//Create file system using specific name
outputFile = new FileOutputStream(file);
Sheet sheet = workbook.createSheet(sheetName);
// Create a Font for styling header cells
Font headerFont = workbook.createFont();
headerFont.setBold(true);
headerFont.setFontHeightInPoints((short) 12);
headerFont.setColor(IndexedColors.RED.getIndex());
// Create a CellStyle with the font
CellStyle headerCellStyle = workbook.createCellStyle();
headerCellStyle.setFont(headerFont);
// Create a Row
Row headerRow = sheet.createRow(0);
// Create cells
for(int i = 0; i < columns.size(); i++) {
cell = headerRow.createCell(i);
cell.setCellValue(columns[i]);
cell.setCellStyle(headerCellStyle);
}
double value = 25.698
CellStyle twoDigitFormat = workbook.createCellStyle();
twoDigitFormat = workbook.createCellStyle();
twoDigitFormat.setDataFormat(createHelper.createDataFormat().getFormat("0.00"));
Row cellRow = sheet.createRow(1);
cell = cellRow.createCell(1);//
cell.setCellValue(value);
cell.setCellStyle(twoDigitFormat);
CellStyle threeDigitFormat = workbook.createCellStyle();
threeDigitFormat = workbook.createCellStyle();
threeDigitFormat.setDataFormat(createHelper.createDataFormat().getFormat("0.000"));
CellStyle commaNumberFormat = workbook.createCellStyle();
commaNumberFormat = workbook.createCellStyle();
commaNumberFormat.setDataFormat(createHelper.createDataFormat().getFormat("#,##0"));
CellStyle twoDigitCommaFormat = workbook.createCellStyle();
twoDigitCommaFormat = workbook.createCellStyle();
twoDigitCommaFormat.setDataFormat(createHelper.createDataFormat().getFormat("#,##0.00"));
// Create Cell Style for formatting Date for xlsx
Date date = new Date();
SimpleDateFormat formatter = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss");
System.out.println(formatter.format(date));
CellStyle dateCellStyle = workbook.createCellStyle();
dateCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd-MM-yyyy"));
cell = cellRow.createCell(0);//
cell.setCellValue(formatter.format(date));
cell.setCellStyle(dateCellStyle);
//FileOutputStream fileOut = new FileOutputStream(path);
workbook.write(outputFile);
workbook.close();
}
}
with the this class will get latest file from the directory
package excelHelper
import java.nio.file.Path
import java.nio.file.Paths
import com.kms.katalon.core.annotation.Keyword
public class GetLatestExcelFile {
@Keyword
/* Get the newest file for a specific extension */
public String getTheNewestFile(String filePath) {
//String currentUsersHomeDir = System.getProperty("user.home");
//String downloadFolder = currentUsersHomeDir + File.separator + "Downloads" + File.separator;
File dir = new File(filePath);
File[] files = dir.listFiles();
if (files == null || files.length == 0) {
println("There is no file in the folder");
}
File lastModifiedFile = files[0];
for (int i = 1; i < files.length; i++) {
if (lastModifiedFile.lastModified() < files[i].lastModified()) {
lastModifiedFile = files[i];
}
}
String k = lastModifiedFile.toString();
System.out.println(lastModifiedFile);
Path p = Paths.get(k);
String file = p.getFileName().toString();
return file;
}
}Thanks for comprehensive details.
May I know what is the purpose of
Excelto.add
Columns.add
hi,
those are for new excel files, only there if needed to create new file, you can add comment for those as well
My current implementation reads katalon data files, then I have json builder to construct data into json format.
With your suggestion by using new libraries, how do I integrate my existing design (give up the reads data file), then use your method? The reads data file is just one line code to call katalon\data files.
Sorry that I may sounds confusing, I’m not sure how to proceed,link both ‘new method read data’ with ‘existing jsonbuilder’.
Or would it be Posibble I give up the jsonbuilder, the poi can read the json data stored in excel file? The data in excel file is actually data as part of Katalon object http body.
hi,
didn’t get your point,
- your script will read json data and then data should be saved to excel file
- in your test case needed to read data from excel and used it in UI application
is this what you will do?
I have katalon api object, http body nothing. Http body data is stored in excel.
I have groovy script by using json builder to read data from excel then construct as per api payload then send it thru katalon object.
My application is just api, no UI.
- Api object with api uri pointing to my api app.
- Data file, excel file stored api data.
- Groovy script, keyword. Read data file then Construct api payload by using json builder.
- Test case, call the api object, and call the keyword.
Hi,
Ok you have already excel file with data and now you will need read the excel file row by row.
After that you will use read data to send json request.
@Timo_Kuisma1
what should be the codes to read the excel file by row?
i have below sample codes, which i think is for write not read?
package test
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 com.kms.katalon.core.annotation.Keyword
public class readexcel {
@Keyword
def postdrug(String name) throws IOException{
FileInputStream fis = new FileInputStream("C:\\JY\\testmatrix.xlsx");
XSSFWorkbook workbook = new XSSFWorkbook(fis);
XSSFSheet sheet = workbook.getSheet("testdata");
int rowCount = sheet.getLastRowNum()-sheet.getFirstRowNum();
Row row = sheet.createRow(rowCount+1);
Cell cell = row.createCell(0);
cell.setCellType(cell.CELL_TYPE_STRING);
cell.setCellValue(name);
FileOutputStream fos = new FileOutputStream("C:\\JY\\testmatrix.xlsx");
workbook.write(fos);
fos.close();
}
}
hi,
it’s added in here, look my previously added comments Dec 19
Okay I saw the ExcelHelperReadXlsx
The codes about
Int rowstart = start
Is this = start referring to cell number? How do I specific read only cell B3?
And can I define the exact cell number in test case not keyword?
E. G. I have 3 test cases, each test case call specific cell number.
hello,
try with this snip
TESTCASE
int rowNum = 3
int colNum = 2
//arguments: row number, column number (start from 0), file path, sheetname
def columnData = CustomKeywords.'excelHelper.ExcelUtilForXlsx.getRowColumn'(rowNum, colNum, pathXlsx, xlsxSheetName)
println ("***DEBUG row "+rowNum+" and column "+colNum+" data: "+columnData)
KEYWORD
public String getRowColumn(int rowNum, int columnNum, String path, String sheetName) throws IOException, InvalidFormatException {
// Check the file extension
if (!path.endsWith(".xlsx")) {
throw new IllegalArgumentException("Unknown file type. Please use .xlsx");
}
try
{
Workbook book = WorkbookFactory.create(new File(path));
//Get first/desired sheet from the workbook
Sheet sheet = book.getSheet(sheetName);
//Sheet sheet = book.getSheetAt(1);
// Create a DataFormatter to format and get each cell's value as String
DataFormatter dataFormatter = new DataFormatter();
if (rowNum == 1){
rowNum++ //add 1 cause row 1 is headers
}
Row r = sheet.getRow(rowNum);//get wanted row
//column nums start from 0
Cell c = r.getCell(columnNum, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
if (c == null) {
return ("**No Value**");
// The spreadsheet is empty in this cell
}
else {
return(dataFormatter.formatCellValue(c));
}
}
catch (Exception ex){
logger.logInfo(ex)
}
}there is error at "InvalidFormatException " i removed it
but where to put the file path? dont see in keyword codes?
cannot run test case as no keyword define too…i try to modify your codes…but the keyword is not select-able in test case
package excel
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.cucumber.keyword.CucumberBuiltinKeywords as CucumberKW
import com.kms.katalon.core.mobile.keyword.MobileBuiltInKeywords as Mobile
import com.kms.katalon.core.model.FailureHandling
import com.kms.katalon.core.testcase.TestCase
import com.kms.katalon.core.testdata.TestData
import com.kms.katalon.core.testobject.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.testdata.ExcelData as ExcelData
import com.kms.katalon.core.testobject.RequestObject as RequestObject
import com.kms.katalon.core.testobject.impl.HttpTextBodyContent as HttpTextBodyContent
import com.kms.katalon.core.testdata.reader.ExcelFactory
import static org.assertj.core.api.Assertions.*
import groovy.json.JsonBuilder as JsonBuilder
import groovy.json.JsonOutput as JsonOutput
import groovy.json.JsonSlurper as JsonSlurper
import groovy.json.JsonDelegate
import java.util.Date
import java.text.SimpleDateFormat
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.ss.usermodel.;
import java.text.;
public class readdata{
@Keyword
public String getRowColumn(int rowNum, int columnNum, String path, String sheetName) throws IOException {
// Check the file extension
if (!path.endsWith(".xlsx")) {
throw new IllegalArgumentException("Unknown file type. Please use .xlsx");
}
try
{
Workbook book = WorkbookFactory.create(new File(path));
//Get first/desired sheet from the workbook
Sheet sheet = book.getSheet(sheetName);
//Sheet sheet = book.getSheetAt(1);
// Create a DataFormatter to format and get each cell's value as String
DataFormatter dataFormatter = new DataFormatter();
if (rowNum == 1){
rowNum++ //add 1 cause row 1 is headers
}
Row r = sheet.getRow(rowNum);//get wanted row
//column nums start from 0
Cell c = r.getCell(columnNum, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
if (c == null) {
return ("**No Value**");
// The spreadsheet is empty in this cell
}
else {
return(dataFormatter.formatCellValue(c));
}
}
catch (Exception ex){
logger.logInfo(ex)
}
}
}
hi,
NOTE! this is only example not correct solution for your problem
please modify this for your needs
in my example has mention about keyword too
**KEYWORD**
public String getRowColumn(int rowNum, int columnNum, String path, String sheetName) throws IOException, InvalidFormatException {
and remember this is not the fully copy paste snip for your project 
i see. i got my own codes works now. thanks for guidelines. i will post the full solution later. from read data to bind api http body.
one question, is the katalon built in excel factory did the same as your sample? just curious what is the differences and why you prefer to choose it.