如何使用openpyxl循环遍历Excel工作表的行？

Question

我使用Python、Selenium、openpyxl来在线填写表单。为了填写表格，我从excel上的特定单元格(.xlsx)中取值。(要测试代码，只需创建带有2列的excel文件，请在A列下插入一些名称，在B列下插入一些年龄。

• 来自单元格A2，我取该人的姓名并将其插入单元格B2的在线表单
• 中，我取下此人的LASTNAME并将其插入到在线表单
• 中，然后单击“Reset”(这是一个示例，但在实际代码中，我将单击“保存为草稿”)。

我想要创建一个循环，在这个循环中，代码将再次从driver.get("https://www.roboform.com/filling-test-all-fields")开始，然后再转到需要填写表单的页面，但这一次我想采取如下步骤：

来自单元格的

• ，人名，并将其插入单元格B3的在线表单
• 中，将其插入到联机表单
• 中，然后单击'Send‘再一次单击

F 220

然后，再次使用另一个循环来插入来自第4行的数据，因此我想编程再次从driver.get("https://www.roboform.com/filling-test-all-fields")读取我的代码，但这次从A4和B4等获取值，直到excel上的行为空。

使用以下代码，我可以将数据插入到联机表单中：

from selenium import webdriver
from selenium.webdriver.chrome.webdriver import WebDriver
from selenium.common.exceptions import NoSuchElementException
import openpyxl

driver: WebDriver = 
webdriver.Chrome("/Users/HHHHH/PycharmProjects/excel/driver/chromedriver")

driver.maximize_window()

excel_document = openpyxl.load_workbook(r"/Users/XPATH OF THE EXCEL FILE YOU CREATE TO TEST THIS CODE", 
data_only=True)

sheet = excel_document["Sheet1"]



driver.get("https://www.roboform.com/filling-test-all-fields")

#Insert in the form the Name of the person

prevsymbol = sheet["A2"].value
if prevsymbol == None:
    pass
else:
    try:
        driver.find_element_by_name("02frstname").send_keys(sheet["A2"].value)
    except NoSuchElementException:
        print("A2:(name) Not Found")

#Insert in the form the Last Name of the person

prevsymbol = sheet["B2"].value
if prevsymbol == None:
    pass
else:
    try:
        driver.find_element_by_name("04lastname").send_keys(sheet["B2"].value)
    except NoSuchElementException:
        print("B2:(Lastname) Not Found")

#click Save as a draft

driver.find_element_by_xpath("//*[@value='Reset']").click()

Answer 1

可以使用max_row属性获取工作表中的行数。因此，代码变成：

from selenium import webdriver
from selenium.webdriver.chrome.webdriver import WebDriver
from selenium.common.exceptions import NoSuchElementException
import openpyxl

driver: WebDriver = 
webdriver.Chrome("/Users/HHHHH/PycharmProjects/excel/driver/chromedriver")

driver.maximize_window()

excel_document = openpyxl.load_workbook(r"/Users/HHHHH/Desktop/testtesttest1.xlsx", 
data_only=True)

sheet = excel_document["Sheet1"]

for i in range(1, sheet.max_row+1):
    driver.get("https://XXXXXXXXXX")    
    # Insert in the form the Name of the person
    cell = "A" + str(i)
    prevsymbol = sheet[cell].value
    # Note that instead of doing the work at the else clause, you can negate the term
    if prevsymbol is not None:
        try:
            # Note that we can use prevsymbol here, instead of referring to cell once again
            driver.find_element_by_id("name").send_keys(prevsymbol)
        except NoSuchElementException:
            #
            print(cell + ":(name) Not Found")

    # Insert in the form the Age of the person  
    cell = "B" + str(i)
    prevsymbol = sheet[cell].value
    if prevsymbol is not None:
        try:
            driver.find_element_by_id("age").send_keys(prevsymbol)
        except NoSuchElementException:
            print(cell + ":(Age) Not Found")

    # Click Save as a draft    
    driver.find_element_by_xpath("xpath_save_as_draft").click()

Answer 2

我已经创建了一个帮助类，请找到它是否实现了您的目标。此代码是在openpyxl的旧版本中完成的。如果需要，请更新代码。

class OpenpyxlImport(object):
    def __init__(self, file):
        self.file = file
        if self.file.name.endswith('.xls'):
            self.wb = self.xls_to_xlsx(self.file)
        else:
            self.wb = load_workbook(self.file)
        self.sheets = self.wb.worksheets

    def to_camelcase(self, string):
        text = re.sub(r'(?!^)_([a-zA-Z])', lambda m: ' ' + m.group(1).upper(), str(string))
        return text.upper()

    def to_snake_case(self, string):
        text = re.sub(r'\s', '_', str(string))
        return text.lower()

    def xls_to_xlsx(self, content):
        xls_book = xlrd.open_workbook(file_contents=content.read())
        workbook = openpyxlWorkbook()

        for i in range(0, xls_book.nsheets):
            xls_sheet = xls_book.sheet_by_index(i)
            sheet = workbook.active if i == 0 else workbook.create_sheet()
            sheet.title = xls_sheet.name

            for row in range(0, xls_sheet.nrows):
                for col in range(0, xls_sheet.ncols):
                    sheet.cell(row=row + 1, column=col + 1).value = xls_sheet.cell_value(row, col)
        return workbook

    def tally_header(self, row, fields):
        # Strip whitespace in cell value
        for cell in row:
            cell.value = cell.value.rstrip()
        return [cell.value for cell in row] == fields

    def row_to_dict(self, row):
        dct = {}
        for cell in row:
            dct[self.to_snake_case(self.get_first_sheet()[cell.column + '1'].value)] = cell.value
        return dct

    def get_sheets(self):
        return self.sheets

    def get_first_sheet(self):
        return self.sheets[0]

    def get_sheet_rows(self):
        return tuple(self.get_first_sheet().iter_rows())

# Usage
excel = OpenpyxlImport(file)
rows = excel.get_sheet_rows()
if excel.tally_header(rows[0], self.fields):
    for row in rows[1:]:
        params = excel.row_to_dict(row)