selenium : how Use values as variables from excel file-CodePudding

I am trying to learn programming, I want your help to solve this , I have some values in an excel file, about 2000 rows, I want to add the values as a variable on my code With the loop until the last row

My idea is as follows:

#my_Excel file 
MyDATA=pd.read_excel(r'C:\Users \Desktop\Project\NEWDATA.xlsx')

#The columns contains values
columns=[['ID','CourseCode','Fromdate','ToDate','Loction']]


#required to use the values in the columns
driver.find_element_by_id("m_m_cBody_bdy_uc_tbl_s_from_date").send_keys(str(Fromdate))
driver.find_element_by_id("m_m_cBody_bdy_uc_tbl_s_from_date1").send_keys(str(ToDate))
driver.find_element_by_css_selector("input[placeholder='Course']").send_keys(str(Coursecode))
time.sleep(4)
driver.find_element_by_css_selector("input[placeholder='Course']").send_keys(Keys.ENTER)
driver.find_element_by_id("m_m_cBody_bdy_uc_tbl_s_btnSearch").click()
time.sleep(4)

courseID="//a[contains(., 'Details') and contains(@href," str(ID) ")]"

WebDriverWait(driver, 10).until(EC.element_to_be_clickable((By.XPATH,courseID))).click()
select = Select(driver.find_element_by_name('m$m$cBody$bdy$uc$tbl$p$e$location_en'))
select.select_by_visible_text(str(Loction))
time.sleep(10)

s = driver.find_element_by_xpath("//*[text()='Save']")
# perform click with execute_script method
driver.execute_script("arguments[0].click();",s)
print("Page title after click: "   driver.title)
time.sleep(10)

CodePudding user response：

From this problem you have to use openpyxl module

Below code will help you to solve your problem

import openpyxl

path1 = r'C:\Users \Desktop\Project\NEWDATA.xlsx'
wb1 = openpyxl.load_workbook(path1) # this will open your excel file
ws1 = wb1.worksheets[0] # this will open your excel sheet1
LastRowCount = ws1.max_row # this will find max row from the excel
start_row_number = 2

for i in range(int(start_row_number), int(LastRowCount)):

    # it will get data of row i which will increase upto maxrow and columnnumber which is fix in excel file of FromDateColumnNumber
    Fromdate = ws1.cell(row=i, column=int(FromdateColumnNumber).value
    driver.find_element_by_id(
        "m_m_cBody_bdy_uc_tbl_s_from_date").send_keys(str(Fromdate))
    ToDate=ws1.cell(row=i, column=int(ToDateColumnNumber).value
    driver.find_element_by_id(
        "m_m_cBody_bdy_uc_tbl_s_from_date1").send_keys(str(ToDate))
    Coursecode=ws1.cell(row=i, column=int(CoursecodeColumnNumber).value
    driver.find_element_by_css_selector(
        "input[placeholder='Course']").send_keys(str(Coursecode))
    time.sleep(4)
    driver.find_element_by_css_selector(
        "input[placeholder='Course']").send_keys(Keys.ENTER)
    driver.find_element_by_id("m_m_cBody_bdy_uc_tbl_s_btnSearch").click()
    time.sleep(4)
    Loction=ws1.cell(row=i, column=int(LoctionColumnNumber).value
    courseID="//a[contains(., 'Details') and contains(@href," str(ID) ")]"

    WebDriverWait(driver, 10).until(
        EC.element_to_be_clickable((By.XPATH, courseID))).click()
    select=Select(driver.find_element_by_name(
        'm$m$cBody$bdy$uc$tbl$p$e$location_en'))
    select.select_by_visible_text(str(Loction))
    time.sleep(10)

    s=driver.find_element_by_xpath("//*[text()='Save']")
    # perform click with execute_script method
    driver.execute_script("arguments[0].click();", s)
    print("Page title after click: "   driver.title)
    time.sleep(10)

Hope this will Help