Selecting dropdown values using Excel(OpenPyXl) - Selenium-Python

Question

I am having a web application which is having multiple dropdowns. For inputting the values for the other text fields, I am reading the data from excel to comply with data driven testing. But I want to know if it is possible to read the dropdown values also from excel? As of now I have used Xpath to read the dropdown values.

To read the excel I have used the following code

    wk = openpyxl.load_workbook(FilePath.Test_Data_Path + 'testdata.xlsx')
    sh = wk['user_details']
    rows = sh.max_row
    for i in range(2, rows + 1):
        cell_name = sh.cell(row=i, column=1)
        cell_email = sh.cell(row=i, column=2)
        cell_product = sh.cell(row=i, column=3)
        cell_make = sh.cell(row=i, column=4)
        cell_model = sh.cell(row=i, column=5)

To read the dropdown, I have used the following method

dropdown=driver.find_elements_by_xpath(xpath)

Answer 1

Below code may solve your problem:

wk = openpyxl.load_workbook(FilePath.Test_Data_Path + 'testdata.xlsx')
sh = wk['user_details']
rows = sh.max_row

for i in range(2, rows + 1):
    cell_name = sh.cell(row=i, column=1) 
    dropdown = driver.find_element_by_xpath('dropdown_xpath')

    for option in dropdown.find_elements_by_tag_name('option'):
        name = option.text

        if name == cell_name:
           option.click()
           break

It will check each option value with our excel value and if it match then option will select