Katalon中Excel读取列表无法调用toLowerCase()问题求助

问题背景 #

使用Katalon（主语言Groovy，部分Java）实现以下操作：

• 读取Excel文件某列的值，存储为String类型列表
• 将列表内容转为小写

遇到异常情况：

• 从Excel获取列表的操作看似正常，打印内容正确
• 普通String列表转小写操作正常
• 仅从Excel获取的列表调用toLowerCase()时失败，报错提示org.apache.poi.xssf.usermodel.XSSFCell没有toLowerCase()方法

测试用例主脚本 #

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 static com.kms.katalon.core.testobject.ObjectRepository.findWindowsObject
import com.kms.katalon.core.checkpoint.Checkpoint as 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 as FailureHandling
import com.kms.katalon.core.testcase.TestCase as TestCase
import com.kms.katalon.core.testdata.TestData as TestData
import com.kms.katalon.core.testng.keyword.TestNGBuiltinKeywords as TestNGKW
import com.kms.katalon.core.testobject.TestObject as 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.windows.keyword.WindowsBuiltinKeywords as Windows
import internal.GlobalVariable as GlobalVariable
import org.openqa.selenium.Keys as Keys
import static java.util.stream.Collectors.*
import java.lang.String as String

List<String> lst_ListFromExcel = new ArrayList()

//this works fine
lst_ListFromExcel = CustomKeywords.'com.utils.ExcelFiles.getCellValuesList'(xlFilePath, 0, 3)

//list printed correctly
WebUI.comment("lst_ListFromExcel = $lst_ListFromExcel")

//Copying list I got from excel to a new one. The first time it worked
List<String> resultsList = new ArrayList<String>(lst_ListFromExcel)

WebUI.comment("resultsList = $resultsList")

//Here is where I got the error. However, with any other list it works fine.
resultsList = resultsList.collect{it.toLowerCase()}

WebUI.comment("resultsList = $resultsList")

自定义关键字（读取Excel列值） #

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DateFormat
import java.text.SimpleDateFormat
import org.apache.poi.ss.usermodel.Cell
import org.apache.poi.ss.usermodel.Row
import org.apache.poi.ss.usermodel.Sheet
import org.apache.poi.ss.usermodel.Workbook
import org.apache.poi.ss.usermodel.WorkbookFactory
import org.apache.poi.xssf.usermodel.XSSFWorkbook

// xlFilePath in this format C:\folder\folder\file.xlsx
@Keyword
def getCellValuesList(String xlFilePath, int sheetIndex, int columnIndex) {
    List<String> rsltValuesList = new ArrayList<>()
    def count = 0
    int column_index_1 = 0;
    File file = new File(xlFilePath);
    Workbook workbook = WorkbookFactory.create(new FileInputStream(file));
    Sheet sheet = workbook.getSheetAt(sheetIndex);
    column_index_1 = columnIndex
    Row row = sheet.getRow(0);
    for (Row r: sheet) {
        if (r.getRowNum() == 0) continue; //hearders
        Cell c_1 = r.getCell(column_index_1);
        if (c_1 != null && c_1.getCellType() != Cell.CELL_TYPE_BLANK) {
            System.out.print("  " + c_1 + "\n");
            rsltValuesList.add(c_1)
            count++
        }
    }
    WebUI.comment("Values count = $count")
    WebUI.comment("rsltValuesList = $rsltValuesList")
    return rsltValuesList
}

错误信息 #

10-09-2022 08:33:27 PM resultsList = resultsList.collect({ -> ... })

Elapsed time: 0.058s

Test Cases/Temp/Temp-ToLowerCase FAILED.
Reason:
groovy.lang.MissingMethodException: No signature of method: org.apache.poi.xssf.usermodel.XSSFCell.toLowerCase() is applicable for argument types: () values: []
    at Script1665186880146$_run_closure1.doCall(Script1665186880146.groovy:32)

解决方案 #

问题根源 #

自定义关键字中直接将Cell对象加入了列表，而非将单元格值转换为String类型。虽然打印时Cell对象会自动调用toString()显示内容，但列表实际存储的是XSSFCell实例，不是String，因此调用toLowerCase()会触发方法不存在的异常。

修复代码 #

修改自定义关键字中的rsltValuesList.add(c_1)，将单元格值转为String后再加入列表。可根据单元格类型做针对性处理，避免格式问题：

// 替换原有的rsltValuesList.add(c_1)
String cellValue;
switch(c_1.getCellType()) {
    case Cell.CELL_TYPE_STRING:
        cellValue = c_1.getStringCellValue();
        break;
    case Cell.CELL_TYPE_NUMERIC:
        // 若需处理日期，可添加日期判断逻辑
        cellValue = String.valueOf(c_1.getNumericCellValue());
        break;
    case Cell.CELL_TYPE_BOOLEAN:
        cellValue = String.valueOf(c_1.getBooleanCellValue());
        break;
    default:
        cellValue = c_1.toString();
}
rsltValuesList.add(cellValue);

如果不需要区分单元格类型，接受默认toString()结果，也可简化为：

rsltValuesList.add(c_1.toString())

修复后，列表中存储的是真正的String对象，后续调用collect{it.toLowerCase()}即可正常执行。

内容的提问来源于stack exchange，提问作者Cristian Inda