Why does getAllData() read Excel file data incorrectly?

Product Forums Katalon Studio
,
1

Why does getAllData() read Excel file data incorrectly?

The origin and compare Excel file sheets cannot be compared by themselves, so the compare row is verified to exist in the origin.
By the way, when reading a compare row, why can’t I read an entire line?
In the following image,
the red data (210…) is reading and processing the values in the line directly below. Is this a Katalon error?
My code is in the following.

Object originsheet = ExcelFactory.getExcelDataWithDefaultSheet(originpath, sheetname, true)
Object comparesheet = ExcelFactory.getExcelDataWithDefaultSheet(xlsfile.toString(), sheetname, true)
List<List<String>> originData 	= originsheet.getAllData()
List<List<String>> compareData 	= comparesheet.getAllData()
matches = true
compareData.eachWithIndex {it, index ->					
if ( !(it in originData) ){
	WebUI.comment(index.toString() + " : " + it)
	matches = false
}

image
image989×293 23.6 KB

2

Hi @dev1

Please provide the Excel file and test script to reproduce. Does this problem happen for all files, or just specific file ?

3

excel.zip (22.9 KB)
Here are origin and compare excel file. The main code is in the following. The compare excel file(N*.xls) corresponds to dynamicsheet. and it happended for all excel files.

public static def dynamicSheets = [
	// last update date : 2020.07.06
	'N검색어'

]

@Keyword
def verifyExcelSheets (String type, String originpath) {
	Integer errFileCount = 0

	new File(GlobalVariable.DOWNLOAD_PATH).eachFileMatch(FileType.ANY,~/.*\.xls/ ) { xlsfile ->
		String[] fileinfo = xlsfile.name.split(/_보고서_\d+_\d+/)
		String 	sheetname = (type == 'basic') ?  fileinfo[0].replace('+', '') : fileinfo[0].replace('+', '') + (fileinfo[1].split('\\.'))[0]
		def compareSheetname = ( sheetname in pathSheet ) ?  sheetname : '통합'
		Boolean matches = true
		
		if (sheetname in dynamicSheets) {				
			Object originsheet = ExcelFactory.getExcelDataWithDefaultSheet(originpath, sheetname, true)
			Object dynamicComparesheet = ExcelFactory.getExcelDataWithDefaultSheet(xlsfile.toString(), compareSheetname, true)

			List<List<Object>> originData 			= originsheet.getAllData()
			List<List<Object>> dynamicCompareData 	= dynamicComparesheet.getAllData()

			dynamicCompareData.eachWithIndex {it, index ->					
				if ( !(it in originData) ){						
					WebUI.comment(index.toString() + " : " + it)
					matches = false
				}
			}
		}			
		else {
			def workbook01 =ExcelKeywords.getWorkbook(originpath)
			def workbook02 =ExcelKeywords.getWorkbook(xlsfile.path)
			def originsheet = ExcelKeywords.getExcelSheet(workbook01, sheetname)
			def staticComparesheet = ExcelKeywords.getExcelSheet(workbook02, compareSheetname)
			
			if ( originsheet && staticComparesheet ) {
				matches = ExcelKeywords.compareTwoSheets(originsheet, staticComparesheet, true)
			}				
		}
		Integer cnt = matchingResult(sheetname, matches)
		errFileCount = errFileCount + cnt
	}
	assert errFileCount == 0
}
4

Does anyone else have the same problem? No response from the Katalon Developer team?

5

@dev1

I am afraid I do not understand your problem.

It seems to me that you expect your test script compares these 2 sheets and find out that they are identical. But the 2 Excel sheets you shared are different, as the following screenshot shows.

2sheetsAreDifferent
2sheetsAreDifferent1920×1080 173 KB

Could you step back and rethink?

6

My goal is not to verify that the two sheets are identical, but to verify whether the contents of the COMPARE sheet are at ORIGIN or not.

I’ve already known that some rows in the COMPARE sheet may not be in the ORIGIN sheet. There are 50 more sheets to compare. All of these sheets may be the same or different.

However, there seems to be a problem with the getAllData() function. I hope you can confirm this.
When using getdAllData(), the last line of the COMPARE sheet was not printed. The reason is that the problem is that one row is not completely read, as verified in the screen shot below.

image
image691×445 76.9 KB

If WebUI.comment(index.toString() + “:” + it) is done, the yellow row should be printed.

But actually, the value printed is the red line.

That’s the problem.

7

Are you aware what the 3rd argument to the getExcelDataWithDefaultSheet() call is?

As the javadoc tells:

public static ExcelData getExcelDataWithDefaultSheet(String fullFilePath, String sheetName, boolean hasHeaders)

If you give true to the 3rd argument, then the ExcelData object will regard the 1st row of the sheet as Header not as a data row.

It is likely that the ExcelData object got confused. When I had a look at the sheet you shared, the 1st row of sheets looks empty. However, at the API level, there are several possible cases:

  1. the 1st row does not exist, request to Sheet to return the 1st row it returns null
  2. the 1st row does exists but has no cell
  3. the 1st row does exists and has one or more cell with empty content
  4. the 1st row does exists and has one or more cell with whitespace content

Which case applies to your sheets? Does the 1st row of your sheets “exists” or not? I do not know. But the above 4 cases may affect the behavior of getAllData().

8

Your Excel sheets have a complex format; they are not as simple as Katalon’s builtin ExcelData class assumes. It assumes that a sheet has zero or one header row followed by a set of data rows in a uniform format. On the other hand, your sheet has a few internal "grid"s inside, each has a title part and a data part.

IMHO, you should not use the Katalon built-in ExcelData class. Rather you should use the Apache POI library directly so that your script can parse the sheet to recognize your sheet format correctly.You can import the Apache POI classes in your test case scripts. But, I am afraid, programming it requires some efforts.

9

You can find the source of “ExcelData” class at

You can study it in detail by reading the source.