How to increment certain rows by 1 Python

Question

I can't seem to figure out a way to increment ws2 cell values. Right now when the for loop, loops through my files the data is just written over in the ws2 cells. How I make it so instead of writing only to cell ws2 A2 it increments by 1 each time so it would then write to A3 next, same for ws2 D2 this increments by 1 each time so then it would write to D3 next etc.

import openpyxl as xl; 
import os
   
input_dir = 'C:\\work\\comparison\\NMN'
template = 'C:\\work\\comparison\\template.xlsx'
newFile = 'NNM_Comparison.xlsx'
  
  
  
  
files = [file for file in os.listdir(input_dir)
         if os.path.isfile(file) and file.endswith(".xlsx")]
  
for file in files:
    input_file =  os.path.join(input_dir, file)
    wb1=xl.load_workbook(input_file)
    ws=wb1.worksheets[0]

      
    wb2 = xl.load_workbook(template) 
    ws2 = wb2.worksheets[0] 
    ws2['A2']=ws['A1']
    ws2['D2']=ws['B4']
    ws2['E2']=ws['D4']

      
      
    output_file = (newFile)
    wb2.save(output_file)

Answer 1

Two things you need to do:

1. Open the template just once. Otherwise, each time through the loop, you're starting from the original template, not combining the results of each iteration.

2. Use a counter variable to change the row number you're writing to in ws2. You can use enumerate() when you're iterating over the file list, and then add an offset to those indexes.

wb2 = xl.load_workbook(template) 
ws2 = wb2.worksheets[0] 

for i, file in enumerate(files):
    input_file =  os.path.join(input_dir, file)
    wb1=xl.load_workbook(input_file)
    ws=wb1.worksheets[0]

    row = str(i + 2)
      
    ws2['A' + row]=ws['A1']
    ws2['D' + row]=ws['B4']
    ws2['E' + row]=ws['D4']
      
output_file = (newFile)
wb2.save(output_file)