I have 2 different cells in one excel file. One cell has only the confirmation number with text ( “Confirmation number :12345”). Another cell has about 20 rows of data that contains different confirmation numbers. How do I match the confirmation number without the text from both cells? If confirmation number matches from both cells, test case should pass. If it doesn’t find the match, it should fail. Can anyone please help with this scenario?
Could you provide us a sample Excel file that shows what you described above?
You do not have to disclose your confidential file at all. You can make a small example and show it to us.
Difficult to understand this. Could you elaborate this a bit more?
hi,
what I know about this case
read the cells e.g. to the list
then regex numbers from cell 1 to the variable and from cell 2 to the list
then verify if the cell 1 value will find from cell 2 values (list)
@sobhan.annepu You can manipulate any String using subString() or split(":")[1] or cellText[-5…-1] (and other String functions) to pull out just part of the original string that you can then compare against the list of Strings (or whatever Excel cell content) using the method in the link below.
https://www.tutorialspoint.com/groovy/groovy_substring.htm
Hi,
one way how to implement it but you can modify this snip for your needs
in excel have values:
column A1
Confirmation number :12356
column D1
Confirmation number :12356
Confirmation number :12345
Confirmation number :12346
Confirmation number :12347
Confirmation number :12348
Confirmation number :12349
Confirmation number :12350
Confirmation number :12351
Confirmation number :12352
Confirmation number :12353
Confirmation number :12354
Confirmation number :12355
Confirmation number :12356
Confirmation number :12357
Confirmation number :12358
Confirmation number :12359
Confirmation number :12360
Confirmation number :12361
Confirmation number :12362
Confirmation number :12363
column A2
Confirmation number :12362
column D2
Confirmation number :12345
Confirmation number :12346
Confirmation number :12347
Confirmation number :12348
Confirmation number :12349
Confirmation number :12350
Confirmation number :12351
Confirmation number :12352
Confirmation number :12353
Confirmation number :12354
Confirmation number :12355
Confirmation number :12356
Confirmation number :12357
Confirmation number :12358
Confirmation number :12359
Confirmation number :12360
Confirmation number :12361
Confirmation number :12362
Confirmation number :12363
TESTCASE
import com.kms.katalon.core.util.KeywordUtil
import java.util.regex.Matcher;
import java.util.regex.Pattern;
KeywordUtil log = new KeywordUtil()
List<String> excelValues = new ArrayList<String>();
List<String> headerValues = new ArrayList<String>();
String path = System.getProperty("user.dir")+"\\ExcelFiles\\SmoobuBookings-2020-sheet2.xlsx";
println path
//create two list arrays
List <String> idArray = new ArrayList<>()
List <String> amountArray = new ArrayList<>()
int counter = 1
int counter2 = 1
int sheet = 2
String path2 = System.getProperty("user.dir")+"\\ExcelFiles\\SmoobuBookings-2020-sheet3-1.xlsx";
List<String> cell1 = new ArrayList<String>();
List<String> cell2 = new ArrayList<String>();
excelValues = CustomKeywords.'readExcelRows.ReadRows.readExcelRows'(0, 2, path2, sheet)
for(int i = 0; i< excelValues.size(); i++){
if (i % 2 == 0){
cell1.add(excelValues.get(i))
}
else{
cell2.add(excelValues.get(i))
}
}
List<String> cell1Regex = new ArrayList<String>();
List<String> cell2Regex = new ArrayList<String>();
//regex values from cell1
String pattern = "Confirmation number :(\\d+)";
// Create a Pattern object
Pattern r = Pattern.compile(pattern);
for (int i = 0; i < cell1.size(); i++){
Matcher m = r.matcher(cell1.get(i));
if (m.find( )) {
System.out.println("Found value: " + m.group(1) );
cell1Regex.add(m.group(1))
} else {
System.out.println("NO MATCH");
}
}
List<String> cell1Values = new ArrayList<String>();
List<String> cell2Values = new ArrayList<String>();
String REGEX = "\\r?\\n";
pat = Pattern.compile(REGEX);
for (int i = 0; i < cell2.size();i++){
def result = pat.split(cell2.get(i));
for(String data:result){
if (i % 2 == 0){
cell1Values.add(data)
}
else{
cell2Values.add(data)
}
}
}
List <String> list1 = new ArrayList<>()
List <String> list2 = new ArrayList<>()
for (int i = 0; i < cell1Values.size(); i++){
list1.add(cell1Values.get(i).replaceAll("[^0-9]", ""))
}
for (int i = 0; i < cell2Values.size(); i++){
list2.add(cell2Values.get(i).replaceAll("[^0-9]", ""))
}
println list1
println list2
//verify values
List<String> searched = new ArrayList<>()
searched = findUsingLoop(cell1Regex.get(0), list1)
if (searched.isEmpty()){
println("No matches")
}
else{
println ("found expected value "+searched)
}
println searched
searched = findUsingLoop(cell1Regex.get(1), list2)
if (searched.isEmpty()){
println("No matches")
}
else{
println ("found expected value "+searched)
}
public List<String> findUsingLoop(String search, List<String> list) {
List<String> matches = new ArrayList<String>();
for(String str: list) {
if (str.contains(search)) {
matches.add(str);
}
}
return matches;
}
KEYWORDS
package readExcelRows
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.;
import org.apache.poi.xssf.usermodel.;
import com.kms.katalon.core.annotation.Keyword
public class ReadRows {
public String SAMPLE_XLSX_FILE_PATH = "C:\\Users\\fitim\\Desktop\\data\\readExcelSheet.xls";
Workbook workbook
@Keyword
public int getCountOfRows(String path, int sheetNum){
// Creating a Workbook from an Excel file (.xls or .xlsx)
workbook = WorkbookFactory.create(new File(path));
//Sheet sheet = workbook.getSheet("sheet0");
Sheet sheet = workbook.getSheetAt(sheetNum);
int lastRowNum = sheet.getLastRowNum()
int used = sheet.getRow(0).getPhysicalNumberOfCells();
println "rows used "+used
int last = sheet.getRow(0).getLastCellNum();
println "rows last "+last
return lastRowNum;
}
@Keyword
public List<String> readExcelRows(int start, int end, String path, int sheetNum) throws IOException, InvalidFormatException{
SAMPLE_XLSX_FILE_PATH = path;
List<String> excelValues = new ArrayList<String>();
// Creating a Workbook from an Excel file (.xls or .xlsx)
workbook = WorkbookFactory.create(new File(path));
// Getting the Sheet at index zero
Sheet sheet = workbook.getSheetAt(sheetNum);
// Create a DataFormatter to format and get each cell's value as String
DataFormatter dataFormatter = new DataFormatter();
boolean last = false
boolean actualValue;
int lastRowNum = sheet.getLastRowNum()
lastRowNum++
if(start == lastRowNum){
last = true
}
int startRow = 0
int endRow = 0;
for (Row row: sheet) {
for(Cell cell: row) {
if (startRow >= start && endRow < end && last==false){
if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) {
excelValues.add("**no value**");
}else{
String cellValue = dataFormatter.formatCellValue(cell);
excelValues.add(cellValue);
}
}
if (last){
String cellValue = dataFormatter.formatCellValue(cell);
excelValues.add(cellValue);
}
//}
}
startRow++;
endRow++;
}
return excelValues;
}
}
RESULTS:
Found value: 12356
Found value: 12362
[12345, 12346, 12347, 12348, 12349, 12350, 12351, 12352, 12353, 12354, 12355, 12356, 12357, 12358, 12359, 12360, 12361, 12362, 12363]
[12345, 12346, 12347, 12348, 12349, 12350, 12351, 12352, 12353, 12354, 12355, 12356, 12357, 12358, 12359, 12360, 12361, 12362, 12363]
found expected value [12356]
found expected value [12362]