OpenPyxl, loop a formula across columns using .format()?

Question

I use openpyxl to create Excel files from Python. I want to take a pandas Dataframe, insert it into a spreadsheet and then summarize column A, B and C with a for loop. (formula in cell A5, B5 and C6 ). However, I'm struggle with how to change the formula string in the loop so I summarize the correct column.

data = pd.DataFrame(np.array([[1, 2, 3], [4, 5, 6], [7, 8, 9]]),
                   columns=['a', 'b', 'c'])

data

wb1 = openpyxl.Workbook()
ws1 = wb1.active

rows = dataframe_to_rows(data, index=False, header=False)
for r_idx, row in enumerate(rows, 2):
    for c_idx, value in enumerate(row, 1):
        ws1.cell(row=r_idx, column=c_idx, value=value)

for col in ws1.iter_cols(min_row=5, max_row=5, min_col=1, max_col=3):
    for cell in col:
        cell.value = '=SUMMA(A2:A4)' # can I use .format() here in a smart way so A2:A4 changes to # B2:B4 and so on?         
        
wb1.save('test.xlsx')

Eric Jensen · Accepted Answer · 2021-07-15T12:18:21.993

2

This should work:

for col in ws1.iter_cols(min_row=5, max_row=5, min_col=1, max_col=3):
    for cell in col:
        cell.value = '=SUMMA({0}2:{0}4)'.format(cell.column)

edited Jul 15 '21 at 12:18

answered Jul 06 '21 at 11:05

Eric Jensen

193
11

Nice! However, It throws back an error. Maybe becauses my col iterator is not returning a single number, instead col prints (,), (,) (,)' – Henri Jul 06 '21 at 11:18
1

That is probably it. I'll update my answer slightly. – Eric Jensen Jul 06 '21 at 11:24
You can do this without your own fragile counter and converter: cells with tell you their column_letter – Charlie Clark Jul 06 '21 at 17:52
Why am I getting a "@" after the = ?? – Michelangelo Amoruso Manzari Oct 07 '21 at 16:38
Michelangelo check out this question [openpyxl: '@' is inserted to formula when saving to file](https://stackoverflow.com/questions/61705150/openpyxl-is-inserted-to-formula-when-saving-to-file) – Eric Jensen Oct 12 '21 at 08:23

score 1 · Answer 2 · answered Feb 15 '23 at 16:35

The most recent edit above did not work for me. Using cell.value = '=SUMMA({0}2:{0}4)'.format(cell.column) results in the formula being output with numbers in the place of column letters (ie, =SUMMA(12:14)). This is fixed with cell.value = '=SUMMA({0}2:{0}4)'.format(cell.column_letter)

OpenPyxl, loop a formula across columns using .format()?

2 Answers2