如何在Python中的Excel工作簿公式中循环浏览并增加行数? [英] How can I loop through and increment the rows in an excel workbook formula in Python?

问题描述

这是该问题的继续我决定在新线程上使用它，这是另一个问题.我有兴趣将公式复制到许多工作簿中各行的列中.我的代码在下面，问题出在for循环中.

I decided to have it on new thread as its another issue. I'm interested in copying a formula to a column across the rows in a number of workbooks. My code is below and the problem is in the for loop.

import openpyxl

in_folder = r'C:\xxx' #Input  folder 
out_folder = r'C:\yyy' #Output  folder 

if not os.path.exists(out_folder):
    os.makedirs(out_folder)
    
dir_list = os.listdir(in_folder)
print(dir_list)
for xlfile in dir_list:
    if xlfile.endswith('.xlsx') or xlfile.endswith('.xls'):

        str_file = xlfile        
        work_book = openpyxl.load_workbook(os.path.join(in_folder,str_file))
        work_sheet = work_book['Sheet1']
        
        for i, cellObj in enumerate(work_sheet['U'], 1):  #The cell where the formula is to be inserted and iterated down to the last row
            
            cellObj.value = '=Q2-T2' #Cells value. This is where I'm going wrong  but  I'm not sure of the best way to have '=Q3-T3' etc till the last row. For each iteration, Q2 and T2 will be incremented to Q3 and T3 till the last row in the dataset.  
        work_book.save(os.path.join(out_folder, xlfile)) #Write  the excel sheet with formulae to another folder

当我在活动工作表中循环到结尾时，如何增加公式中的行?有关更多详细信息，请参见代码旁边的注释.

How can I increment the rows in the formula as I loop through the active worksheet to the end? More details in the comments next to the code.

推荐答案

也许您可以尝试格式化字符串?

maybe you could just try formatting the string?

...
    row_count = 2
    for i, cellObj in enumerate(work_sheet['U'], 1):
        cellObj.value = f'=Q{row_count}-T{row_count}'
        work_book.save(os.path.join(out_folder, xlfile))
        row_count += 1

这篇关于如何在Python中的Excel工作簿公式中循环浏览并增加行数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！