python - 遍历 excel 列（记住最后一行，然后在一段时间后恢复迭代）

Question

我正在尝试创建一个从 excel 列中检索值的脚本。新值不断输入到列中。我希望脚本每天运行，例如在早上 6 点计算“新”平均值。我希望它记住前一天使用的最后一行单元格，然后根据过去新一天新输入的值计算平均值。

import openpyxl


# Calculate Average CF
def ave(lst):
    return round(sum(lst) / len(lst), 2)


# Save today's Ave. CF and last row used for calculation
def save_ave(average, last_cell):
    with open('Prac.txt', 'a') as fw:
        summary = f'Average is: {average}, and last calculated cell={last_cell}\n'
        fw.write(summary)


# Open excel file
cm_wb = openpyxl.load_workbook('Process Dashboard - Copy.xlsx',read_only=True, data_only=True)
# Retrieve names of all sheets in opened excel file
cm_sheets = cm_wb.sheetnames
# Specify sheet to work with (eg last sheet)
cm2_sheet = cm_wb[cm_sheets[len(cm_sheets) - 1]]
# Declare empty list to store values which we'l use to calculate Ave.
t = []

# Open text file in which our average values are stored and corresponding last row used
with open('Prac.txt', 'r') as fr:
    file_lines = [line.strip() for line in fr.readlines()]
    if not file_lines:
        # If file is initially empty, we'll start iterating from Row 1
        first_row = 1
    else:
        # After 1st time run, retrieve the stored last row value
        cell = file_lines[-1].split('=')
        # We'll start iterating from last used row + 1
        first_row = int(cell[-1]) + 1

# Iterations to be made to last entry in column
last_row = cm2_sheet.max_row

# Iterate column E
for row in cm2_sheet.iter_rows(min_row=first_row, max_row=last_row, max_col=5, min_col=5):
    # Each value into list t
    for i in row:
        t.append(i.value)

# Calculate the average, and store last used row in file
save_ave(ave(t), last_row)

# Close the file to allow other programs to use
cm_wb.close()

标签： pythonpython-3.xexcelpython-2.7openpyxl