CODEWITHSUNDEEP
pythonexcelseleniumloopsrows
Alessio
Alessio

Reputation: 13

How to loop through rows of the Excel sheet using openpyxl?

I am using Python, Selenium, openpyxl in order to fill a form online. To fill the form I am taking values from specific cells on excel (.xlsx). (to test the code you can just create and excel file with 2 columns, insert under column A some names and column B some age.

I would like to create a loop in which the code will start again from driver.get("https://www.roboform.com/filling-test-all-fields") to go again to the page where I need to fill out the form, but this time I would like to take:

Then again, another loop to insert the data from row 4, so I would like to program to read again my code from driver.get("https://www.roboform.com/filling-test-all-fields") but this time take values from A4 and B4, and so on, until the row on excel is empty.

With the following code I can insert the data to the online form:

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()

Upvotes: 1

Views: 3120

Answers (2)

Alp Aribal
Alp Aribal

Reputation: 370

You can get the number of rows in the sheet using the max_row property. So, the code becomes:

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()

Upvotes: 0

Roshan Shrestha
Roshan Shrestha

Reputation: 441

I have create a helper class please find if it fulfill your purpose. This code is done in old version of openpyxl. Please update code if needed.


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)

Upvotes: 1

Related Questions