0

This is a continuation of the this question How can I iterate through excel files sheets and insert formula in Python?

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.

Herdil
  • 73
  • 1
  • 7

1 Answers1

0

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
Jasurbek NURBOYEV
  • 11
  • 3
  • Thank you @Jasurbek. The above counts row in each document and starts inserting the formula based on the count. For example if 1001.xlsx has 6 rows, then it will insert the Q6-T6 in all rows. Seems to be something to do with the `for` loop. – Herdil Apr 27 '21 at 08:08
  • I guess your question has 'changed'? so, all you need to do is to detect row count and then use f'=Q{rc}-T{rc}' for all rows? then [this](https://stackoverflow.com/questions/13377793/is-it-possible-to-get-an-excel-documents-row-count-without-loading-the-entire-d) question answers to 'how to get row count?', then you can use it for your code. – Jasurbek NURBOYEV Apr 28 '21 at 09:08