excel 如何在每次函数运行时打印下一行?(openpyxl)

icomxhvb  于 2023-01-10  发布在  其他
关注(0)|答案(2)|浏览(160)

我正在做的整个项目是,我使用openpxyl从excel文档的特定行和列中取值,然后在网页上填写表单。我填写表单,然后保存它,然后用excel文档中的下一行值填写下一个表单。
Excel的外观如下所示:

First Name         Last Name         DOB

George             Hill              9/9/99
Sam                Genius            8/8/88
Bill               Smith             7/7/77

我想运行下面的函数三次,每次运行时打印出下一行:

def AddFirstName():
     for i in range(3):
       for row in ws.iter_rows(min_row=1, min_col=1, max_row=3,max_col=1):
         for cell in row:
            print(cell.value)
                
            break
       break  # Tried to use breaks to break the loop so it only prints once for each loop

AddFirstName()
AddFirstName()
AddFirstName()

或者在函数外运行,如下所示:

for i in range(3):
       for row in ws.iter_rows(min_row=1, min_col=1, max_row=3,max_col=1):
         for cell in row:
            print(cell.value)
                
            break
       break  # Tried to use breaks to break the loop so it only prints once for each loop

这将打印:

George
George
George

如何修复此问题,以便打印:

George             
Sam                
Bill
qjp7pelc

qjp7pelc1#

我找到的答案是here
基本上,它将打印出我希望在运行函数的次数上挂起的行数。

listofnames = [
              ['George'],
              ['Bill'],
              ['Mike']]

def print_names(name):
cnt = 0
while cnt < 1:
    try:
        print(next(name))
    except StopIteration:
        print("You have reached the end of the list!")
        break
    cnt += 1

first_name = iter(listofnames)

for i in range (3):
    print_names(first_name)
wecizke3

wecizke32#

我编辑了我的答案的基础上,你的意见和你正在寻找完成。
此解决方案利用闭包。闭包是嵌套函数,其中外部函数def封装内部函数def,并且外部函数返回内部函数。在内部函数中声明为非局部的变量在调用方命名空间中内部函数的调用之间保持其值。在此示例中,内部函数有两个非局部变量。2一个跟踪处理的总行数,另一个是迭代器的副本。
对于你的例子来说,一个闭包可能看起来需要做很多工作,但是我认为它会让你的代码在长期运行中更易移植和使用。处理N行的所有智能都在内部函数中。在那里修改它,闭包行为的所有"示例"也会更新。
另外,我使用unittest.mock.sentinel代替了"try",因为它比try输入的内容少很多......而且,它看起来更干净。

import unittest.mock
from unittest.mock import sentinel

sheetData = [
    ['George'],
    ['Bill'],
    ['Mike']
]

def set_iter(sheet_data):
    total_rows = 0
    sheet_data_iter = iter(sheet_data)
    # Create a closure that will track the total number of times we have called
    # the iterator. A closure may seem like a lot of extra work in this example,
    # but it shows how usefule they can be when you variables in a function
    # to persist after they have been called.
    def get_next(rows_to_print):
        # Declaring total_rows as nonlocal means this function uses the total_rows from
        # the enclosing fucntion, and that this variable will keep its value in subsequent
        # calls to the this function after set_iter is called.
        nonlocal total_rows
        nonlocal sheet_data_iter
        rows_printed = 0
        while rows_printed < rows_to_print:
            rows_printed += 1
            print(f"Printing Row: {rows_printed} of {rows_to_print} Rows to Print this time through.")
            row_data = next(sheet_data_iter, sentinel.END_OF_ITERATION)
            if row_data == sentinel.END_OF_ITERATION:
                print("There are no more items to iterate over")
                return None
            else:
                # row_data = next(sheet_data_iter)
                print(f"0th Element of Row With Index: {total_rows} is: '{row_data[0]}'")
                # total_rows maintains it's value through subsequent calls to the returned
                # get_next function.  It's keeps track of the total number of times the
                # iterator has been called.
                total_rows += 1
        return True
    # get_next(row_count)
    return get_next

# print_next_n_rows is a function, as returned by set_iter()
print_next_n_rows = set_iter(sheetData)
# another_print_n_rows is also a function, and it encapsulate a fresh iterator,
# it tracks separate from print_next_n_rows
another_print_n_rows = set_iter(sheetData)

# This line should print George
if print_next_n_rows(1):
    print("There were still rows.\n")
else:
    print("Caught out of rows...\n")

# This should print the next 2 lines
if print_next_n_rows(2):
    print("There were still rows.\n")
else:
    print("Caught out of rows...\n")

# This should print the first 2 lines...
if another_print_n_rows(2):
    print("There were still rows.\n")
else:
    print("Caught out of rows...\n")

# This should catch the end of the list
if print_next_n_rows(1):
    print("There were still rows.\n")
else:
    print("Caught out of rows...\n")

下面是上述脚本的输出:

Printing Row: 1 of 1 Rows to Print this time through.
0th Element of Row With Index: 0 is: 'George'
There were still rows.

Printing Row: 1 of 2 Rows to Print this time through.
0th Element of Row With Index: 1 is: 'Bill'
Printing Row: 2 of 2 Rows to Print this time through.
0th Element of Row With Index: 2 is: 'Mike'
There were still rows.

Printing Row: 1 of 2 Rows to Print this time through.
0th Element of Row With Index: 0 is: 'George'
Printing Row: 2 of 2 Rows to Print this time through.
0th Element of Row With Index: 1 is: 'Bill'
There were still rows.

Printing Row: 1 of 1 Rows to Print this time through.
There are no more items to iterate over
Caught out of rows...

h小时,
David

相关问题