Posibble to store all data under one excel sheet?

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,

  1. your script will read json data and then data should be saved to excel file
  2. 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.

  1. Api object with api uri pointing to my api app.
  2. Data file, excel file stored api data.
  3. Groovy script, keyword. Read data file then Construct api payload by using json builder.
  4. 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.