使用写入Excel

Question

我正在尝试将代码中的for循环的输出写回Excel文件列。我已经学习了一些教程，但不确定我是否正确地理解了逻辑。

我定义了这样一个函数：

def writeData(file,sheetName,rownum,columnno,data):
    workbook = openpyxl.load_workbook(file)
    sheet = workbook.get_sheet_by_name(sheetName)

随后，我尝试在IF语句(在for循环中)中调用函数(writeData)：

if  document_id[:3] == "A70":
        print(document_id)
        retire_xpath = driver.find_element(By.XPATH, "//*[@id='retire-"+ str(document_id[-7:])+"']")
        time.sleep(6)
        driver.execute_script("arguments[0].click();", retire_xpath)
        time.sleep(5)
        driver.switch_to.alert.accept()
        print("success1")
        writeData(path, "Sheet1",r,3, "Retired")

但是，在我成功完成后检查Excel文件后什么都不会发生。我想说的是“退休”如果行动成功，或‘失败’，如果没有。

如果需要的话，下面是我所有的代码：

path = 'filepathhere'

def getRowCount (file,sheetName):
    workbook = openpyxl.load_workbook(file)
    sheet = workbook.get_sheet_by_name(sheetName)
    return(sheet.max_row)

def getColumnCount(file,sheetName):
    workbook = openpyxl.load_workbook(file)
    sheet = workbook.get_sheet_by_name(sheetName)
    return(sheet.max_column)

def readData(file,sheetName,rownum,columnno):
    workbook = openpyxl.load_workbook(file)
    sheet = workbook.get_sheet_by_name(sheetName)
    return sheet.cell(row=rownum, column=columnno).value

def writeData(file,sheetName,rownum,columnno,data):
    workbook = openpyxl.load_workbook(file)
    sheet = workbook.get_sheet_by_name(sheetName)
    
rows=getRowCount(path, "Sheet1")

driver = webdriver.Chrome(executable_path="C:\Chrome Driver\chromedriver.exe")

email = 'emailhere'
password = 'password here'

EMAILFIELD = (By.ID, "i0116")
PASSWORDFIELD = (By.ID, "i0118")
NEXTBUTTON = (By.ID, "idSIButton9")


driver.get("https://test.rs-dms.com/wp-admin/edit.php?post_type=rs_document")
driver.set_window_position(1500, 0)
driver.maximize_window()

# WebDriverWait(driver, 10).until(EC.element_to_be_clickable(EMAILFIELD)).send_keys(email)
# WebDriverWait(driver, 10).until(EC.element_to_be_clickable(NEXTBUTTON)).click()
# WebDriverWait(driver, 10).until(EC.element_to_be_clickable(PASSWORDFIELD)).send_keys(password)
# WebDriverWait(driver, 10).until(EC.element_to_be_clickable(NEXTBUTTON)).click()
# WebDriverWait(driver, 10).until(EC.element_to_be_clickable(NEXTBUTTON)).click()

SearchBOX = (By.ID, "post-search-input")
ENTER = (By.ID, "search-submit")
Restart = (By.XPATH, "/html/body/div[1]/div[1]/div[2]/ul/li[4]/ul/li[2]/a")

for r in range (2,rows+1):
    stock_number = readData(path,"Sheet1",r,1)
    document_id = readData(path, "Sheet1",r,2)
    print(document_id)
    
    WebDriverWait(driver, 10).until(EC.element_to_be_clickable(SearchBOX)).send_keys(stock_number)
    WebDriverWait(driver, 10).until(EC.element_to_be_clickable(ENTER)).send_keys(Keys.RETURN)

    Options = (By.XPATH, "//*[@id='show-settings-link']")
    Page_Number = (By.XPATH, "//*[@id='edit_rs_document_per_page']")
    Apply_Button = (By.XPATH, "//*[@id='screen-options-apply']")

    WebDriverWait(driver, 10).until(EC.element_to_be_clickable(Options)).click()
    WebDriverWait(driver, 10).until(EC.element_to_be_clickable(Page_Number)).send_keys("999")
    WebDriverWait(driver, 10).until(EC.element_to_be_clickable(Apply_Button)).click()
    WebDriverWait(driver, 10).until(EC.element_to_be_clickable(Options)).click()
    
    before_XPath = "//*[@class='wp-list-table widefat fixed striped table-view-list pages']/tbody/tr["
    aftertd_XPath_1 = "]/td[1]"
    aftertd_XPath_2 = "]/td[2]"
    aftertd_XPath_3 = "]/td[3]"
    before_XPath_1 = "//*[@class='wp-list-table widefat fixed striped table-view-list pages']/tbody/tr[1]/th["
    before_XPath_2 = "//*[@class='wp-list-table widefat fixed striped table-view-list pages']/tbody/tr[2]/td["
    aftertd_XPath = "]/td["
    after_XPath = "]"
    aftertr_XPath = "]"
    time.sleep(10)

    num_rows = len(driver.find_elements(By.XPATH, "//*[@class='wp-list-table widefat fixed striped table-view-list pages']/tbody/tr"))
    num_columns = len(driver.find_elements(By.XPATH, "//*[@class='wp-list-table widefat fixed striped table-view-list pages']/tbody/tr[2]/td"))

    for t_row in range(2, (num_rows + 1)):
        for t_column in range(1, (num_columns + 1)):
            FinalXPath = before_XPath + str(t_row) + aftertd_XPath + str(t_column) + aftertr_XPath
            cell_text = driver.find_element(By.XPATH, FinalXPath).text

    if  document_id[:3] == "A70":
        print(document_id)
        retire_xpath = driver.find_element(By.XPATH, "//*[@id='retire-"+ str(document_id[-7:])+"']")
        time.sleep(6)
        driver.execute_script("arguments[0].click();", retire_xpath)
        time.sleep(5)
        driver.switch_to.alert.accept()
        print("success1")
        writeData(path, "Sheet1",r,3, "Retired")
        
    elif document_id[:3] == "090":            
        print(document_id)
        post_parent_num = driver.find_element(By.XPATH, f"//div[@class='post_name' and text()='{document_id}']//following-sibling::div[@class='post_parent']").get_attribute('innerText')
        retire_xpath = driver.find_element(By.XPATH, "//*[@id='retire-"+ post_parent_num +"']")
        time.sleep(6)
        driver.execute_script("arguments[0].click();", retire_xpath)
        time.sleep(5)
        driver.switch_to.alert.accept()
        print("success2")
        writeData(path, "Sheet1",r,3, "Retired")

    else:
        writeData(path, "Sheet1",r,3, "Failed")
        
    WebDriverWait(driver, 10).until(EC.element_to_be_clickable(Restart)).click()

我觉得我对我的代码的逻辑和/或结构完全没有兴趣，我希望得到任何支持！

Answer 1

您的writeData函数不完整

def writeData(file,sheetName,rownum,columnno,data):
    workbook = openpyxl.load_workbook(file)
    sheet = workbook.get_sheet_by_name(sheetName)

此函数只需打开excel工作表即可在excel中写入任何内容。

def writeData(file,sheetName,rownum,columnno,data):
    workbook = openpyxl.load_workbook(file)
    sheet = workbook.get_sheet_by_name(sheetName)
    # set data value to cell
    sheet.cell(row=rownum, column=columnno).value = data
    # save workbook 
    workbook.save(file)

用此函数替换您的函数，它将将您的数据写入excel工作表，并保存excel。

希望这段代码能帮到你