How can I increment values in openpyxl, based on the previous cell?

Question

This example will not work (+= in for loop), but it shows what I want to achieve:

import openpyxl
wb = openpyxl.Workbook()
sheet = wb.active

for a,b,c,d,e,f,g in sheet.iter_rows(min_col=1, max_col=7, min_row=2, max_row=8):
    a.value += 1
    b.value += 1
    c.value += 2
    d.value += 3
    e.value += 4
    f.value += 5
    g.value += 6

wb.save("multi.xlsx")

My goal is to increase per iteration the value, based on the previous cell. So that the result for a.value is: 1,2,3,4,5,6, for d.value 3,6,9,12,15,18 and so on. What is the easiest way to achieve this?

Answer 1

You can use simple math to accomplish this. Define how much you want each column to increase by per row (keeping in mind that each column has a numerical index),

column_amounts = {
  1 : 1,
  3 : 2,
}

and then set the cell value to (row * amount). In this way, we can apply the same logic to every row and cell, but change the amounts for each column in a simple manner.

for a,b,c,d,e,f,g in sheet.iter_rows(min_col=1, max_col=7, min_row=2, max_row=8):
    a.value = column_amounts[a.col_idx] * a.row
    c.value = column_amounts[c.col_idx] * b.row

You could also simplify this into a small helper function:

def amount_at_cell(cell):
  return column_amounts[cell.col_idx] * cell.row

for a,b,c,d,e,f,g in sheet.iter_rows(min_col=1, max_col=7, min_row=2, max_row=8):
    a.value = amount_at_cell(a)
    c.value = amount_at_cell(c)

Relevant API docs for working with cells: https://openpyxl.readthedocs.io/en/latest/api/openpyxl.cell.cell.html?highlight=Cell