Hi @_daryl 
So nice of you It worked. The excel practically just consists of one column and idea is to get all the values from the column and compare it with one column (read as a list in robot framework) and then comparison is done using python.
In Robot Framework I do it like this:
This file is the test case runner:
*** Settings ***
Resource ../../LoginAsAdmin.robot
Resource ../../Logout.robot
Resource NavigateToCategoriesPage.robot
Resource ../../CommonTests/CommonKeywords.robot
Library ../lib/ExcelUtils.py
Test Setup Create Setup to Run Tests https://admin-demo.nopcommerce.com/login Firefox
#Test Teardown Close Web Browser
*** Variables ***
${PathToExcelFile}= D://LEARNING//MY_LEARNING//ROBOTFRAMEWORK_PYCHARM//ROBOTFRAMEWORK//nopCommerceApp//TestData//ALL_DEFAULT_CATEGORIES.xlsx
${NameOfExcelSheet}= Sheet1
${HTML_Table_Web_Element}= xpath=//*[@id='categories-grid']
#/tbody/tr[${row_count}]/td[1]
# xpath=//*[@id=class="table table-bordered table-hover table-striped dataTable no-footer"]/thead/tr[${row_count}]/td[${row_count}]
*** Test Cases ***
Find Given Category In Given Excel File
Login as Admin
Navigate to Categories Page
Set Id Categories Page Search Box C
Sleep 3s
Click Search Categories Button
Sleep 2s
Click Search Categories Button
Sleep 10s
Page Should Contain 1-7 of 7 items
Sleep 3s
Loop Through Excel File And Find Given Value ${PathToExcelFile} ${NameOfExcelSheet} ${HTML_Table_Web_Element}
Sleep 10s
********************* CommonKeywords.robot ******************************
*** Settings ***
Library SeleniumLibrary
Library ../lib/ExcelUtils.py
Library Collections
Library ExcelLibrary
*** Keywords ***
Loop Through Excel File And Find Given Value
[Arguments] ${PathToExcelFile} ${NameOfExcelSheet} ${HTML_Table_Web_Element}
${RowCountInExcel}= Run Keyword ExcelUtils.Get Row Count ${PathToExcelFile} ${NameOfExcelSheet}
Log Total number of Rows in Excel: ${RowCountInExcel} console=true
# def read_data(file, sheetName, rowNum, columnNum):
${ValueFromExcel} = Set Variable ""
@{ExcelCategoriesValues} Create List
FOR ${counter} IN RANGE 1 ${RowCountInExcel}
TRY
${ValueFromExcel}= Run Keyword ExcelUtils.Read Data ${PathToExcelFile} ${NameOfExcelSheet} ${counter} 1
EXCEPT "TypeError":
Log ValueFromExcel: ${ValueFromExcel}
END
Log ValueFromExcel: ${ValueFromExcel}
Append To List ${ExcelCategoriesValues} ${ValueFromExcel}
END
@{RESULT}= Create List
@{HTML_DATA}= Create List
Log Here the html data is printed: console=true
# FILL THE HTML DATA LIST
FOR ${row_count} IN RANGE 1 16
IF ${row_count} == 8
BREAK
END
${html_table_data}= Get Text xpath=//*[@id='categories-grid']/tbody/tr[${row_count}]/td[2]
Append To List ${HTML_DATA} ${html_table_data}
#Log To Console "--------------------------START: HTML_DATA_LIST------------------------------------"
Log html_table_data:${html_table_data} console=true
END
#Log To Console "--------------------------------END: HTML_DATA_LIST------------------------------"
${HTML_TABLE_ROW_COUNT}= Get Length ${HTML_DATA}
${HTML_TABLE_ROW_COUNT} Evaluate ${HTML_TABLE_ROW_COUNT}-1
${Excel_Row_Count}= Get Length ${ExcelCategoriesValues}
${iterationCount}= Set variable 0
Log To Console Excel_Row_Count: ${Excel_Row_Count}
Log To Console iterationCount : ${iterationCount}
# def find_string_in_list(self, excel_table_as_list, html_table_as_list):
@{RESULT} Create List
@{RESULT}= Run Keyword ExcelUtils.Find String In List ${ExcelCategoriesValues} ${HTML_DATA}
Get Length item
FOR ${element} IN ${RESULT}
Log Final Result: ${element} console=true
IF ${element} == "true"
Log PASS console=true
ELSE
Log Fail console=true
END
END
In python side:
@staticmethod
def find_string_in_list(excel_table_as_list, html_table_as_list):
#def find_string_in_list():
resultList =
counter = 0
#html_manual_list = ['Electronics', 'Electronics >> Camera & photo', 'Electronics >> Cell phones', 'Computers','Gift Cards', 'Apparel >> Clothing', 'Apparel >> Accessories']
#excel_manual_list = ['Computers', 'Computers >> Desktops', 'Computers >> Notebooks', 'Computers >> Software','Electronics', 'Electronics >> Camera & photo', 'Electronics >> Cell phones','Electronics >> Others', 'Apparel', 'Apparel >> Shoes', 'Apparel >> Clothing', 'Apparel >> Accessories', 'Digital downloads', 'Books', 'Jewelry', 'Gift Cards']
excel_table_as_list_here = list(excel_table_as_list)
html_table_as_list_here = list(html_table_as_list)
#excel_table_as_list_here = list(html_manual_list)
#html_table_as_list_here = list(excel_manual_list)
for html_data in html_table_as_list_here:
print("html_data: ", html_data)
if html_data in excel_table_as_list_here:
resultList.append("true")
elif html_data not in html_table_as_list_here and counter >= len(html_table_as_list_here):
resultList.append("false")
break
counter = counter + 1
return resultList
ResultList = ExcelUtils.find_string_in_list()
print(ResultList)
for i in range(len(ResultList)):
print(ResultList[i])
I would greatly appreciate you suggesting better approach to this code
One more question, do you know in pycharm how I can get the output from the python side getting printed (using print statement for example) when I am launching the robot framework code
using robot .\Categories_Page_Test_Runner.robot which then further utilizes the code from the python ExcelUtils.py file but no print statement output is seen such as
print(f"values: {values}")
Excel file is just one column:
HTML table looks like:
Sincere Regards
EagerToLearn
