Why Katalon does not recognize empty cells?

Hello,

Today, I’m facing a big problem.
I can’t read or fill empty cells into an empty sheet.
Indeed, into test data corresponding to this sheet, 0 row exist…

How to fix that?

Thanks a lot!

Can you provide some more information on this

Can we see some screenshots of this “sheet” and the test steps that you currently have

Thanks for your answer @hpulsford but sorry but I cant upload anything …

I will try to explain you with some text.
I have an Excel sheet which is empty.
Into Katalon Data Test when I look the sheet, it is empty, there is no line number, just an empty array.
The only way I found to have line numbers into Katalon Data Test is to put a string at the line 30 (for example) into the Excel sheet. With this solution, 30 lines are present into Katalon Data Test and I can read and fill these 30 lines …

I hope I’m clear …

Thanks.

Im sorry, im really not understanding what you are trying to do. Hopefully someone will come across and understand better.

Last try ahah.

If sheet is empty, Katalon not initialize empty cells, there are null.
So when I try to access them, code crashed due to null exception.

Ok i think im getting it, what are you trying to do with these empty cells?

I try to read or fill their content.
For exemple, iterate until to find an empty cell and fill it with a specific value.

So I use this method to get the content of a specific cell for example :

@Keyword
def getValueFromExcel(String testDataFile, String sheet, String columnName, int rowNumber) {
    ExcelData data = initialiseDataDriven(testDataFile, sheet)
    return data.getValue(columnName, rowNumber)
}

So the objective is to find a way to by pass the problem of empty cells …

so,you can simply iterate until null is found. null == empty cell therefore you can fill it

Nop because I cannot fill an empty cell because it’s a null object … So impossible to fill something null

I don’t know much at all about using excel with Katalon, but I’m sure progress can be made by improving the terminology.

A spreadsheet with a set of cells in a 5x5 grid has 25 cells. The other “cells” you “see” do not exist. In other words, an empty cell is NOT the same as a non-existent cell.

Which of course makes perfect sense - a sheet could conceivably end up with with 1Mx1M cells, do you think they all existed when the sheet was new? Of course not.

So it sounds like the issue is “How to CREATE NEW cells in a spreadsheet” and nothing at all about populating empty cells.

2 Likes

hello,

write your own keyword where noted empty cells
like
eCellNumbersX = eRowX.getCell(colIndexNumbers, Row.RETURN_BLANK_AS_NULL);//check if cell is null
eCellValuesX = eRowX.getCell(colIndexValues, Row.RETURN_BLANK_AS_NULL);//check if cell is null
if (eCellNumbersX == null || eCellValuesX == null) {
eCellNumbersX = eRowX.createCell(colIndexNumbers);
eCellValuesX = eRowX.createCell(colIndexValues);
eCellNumbersX.setCellValue(mentry.getKey());
eCellValuesX.setCellValue(mentry.getValue());
} else {
eCellNumbersX.setCellValue(mentry.getKey());
eCellValuesX.setCellValue(mentry.getValue());
}

1 Like

Thanks a lot !

Created own keyword, that using flags (1,0) to check if cell is empty

My keyword, create new excell file, set
1 to first row
0 to second
Search for first 0 row and defines it like empty, adds info to this row
and set 0 to the next row

@Keyword
public String writeOrderNumberInExcel(def paymentType) {
	def excelFile01 = 'Data Files\\OrderNumber.xls'
	def today = new Date()
	def  todaysDate = today.format('dd.MM.yyyy')
	def  nowTime = today.format('hh:mm:ss')
	def orderNumber = WebUI.getText(findTestObject('TYP/orderNumber'))
	File file1 = new File(excelFile01)

	// Verify files are created. If not - create new
	if( file1.exists() == false){
		ExcelKeywords.createExcelFile(excelFile01)
		def workbook01 = ExcelKeywords.getWorkbook(excelFile01)
		def sheet01 = ExcelKeywords.getExcelSheet(workbook01, 'Sheet0')
		ExcelKeywords.setValueToCellByIndex(sheet01,  0, 0, 0)  // set last row value = 0
		ExcelKeywords.saveWorkbook(excelFile01, workbook01)
	}

	def workbook01 = ExcelKeywords.getWorkbook(excelFile01)
	def sheet01 = ExcelKeywords.getExcelSheet(workbook01, 'Sheet0')
	int row = ExcelKeywords.getRowIndexByCellContent(sheet01, "0", 0)
	int rowlLast = row + 1

	ExcelKeywords.setValueToCellByIndex(sheet01,  row, 0, 1)
	ExcelKeywords.setValueToCellByIndex(sheet01,  rowlLast, 0, 0)
	ExcelKeywords.setValueToCellByIndex(sheet01,  row, 1, paymentType)
	ExcelKeywords.setValueToCellByIndex(sheet01,  row, 2, orderNumber)
	ExcelKeywords.setValueToCellByIndex(sheet01,  row, 3, todaysDate + ' | ' + nowTime)

	ExcelKeywords.saveWorkbook(excelFile01, workbook01)
	println('[Order number] : ' + orderNumber + ' [Payment] : ' + paymentType )