Calculate formula downwards with openpyxl

Question

I have a a spreadsheet with column A containing dates. I am using a formula to see the difference in days between A1 and A2, A1 and A3 etc, using this excel formula in column B.

=datedif(A1,$A1$1,"D")

When dragging down manually in excel it changes to

=datedif(A2,$A$1,"D")

However my script populates the original formula down all of Column B, keeping A1 instead of moving down to A2, A3 and so on. Here is my script.

for row in ws8.iter_rows(min_col=2, max_col=2, min_row=1):
    for cell in row:
        cell.value = '=datedif(A1,$A$1,"D")'

APhillips · Answer 1 · 2020-01-31T15:08:14.277

0

Be careful to include a max_row value, if you know it.

ws8 = wb.active
for i,row in enumerate(ws8.iter_rows(min_col=2, max_col=2, max_row=10)):
    for cell in row:
        cell.value = f'=datedif(A{i+1},$A$1,"D")'

For older versions of python, without f-strings:

ws8 = wb.active
for i,row in enumerate(ws8.iter_rows(min_col=2, max_col=2, max_row=10)):
    for cell in row:
        cell.value = '=datedif(A{},$A$1,"D")'.format(i+1)

Output:

edited Jan 31 '20 at 15:08

answered Jan 30 '20 at 21:25

APhillips

1,175
9
17

I am getting invalid syntax at the closing ' of the formula – Lord Beerus Jan 30 '20 at 21:42
It worked for me, can you double check that you've copied over exactly what I provided? – APhillips Jan 30 '20 at 21:43
1

Never use a counter when `enumerate()` will do. Even better, use `cell.row` – Charlie Clark Jan 31 '20 at 13:06
@CharlieClark Good point, thanks. I will look further into cell.row. – APhillips Jan 31 '20 at 14:25

score 0 · Answer 2 · answered Jan 30 '20 at 21:41

0

You are pasting it as a string which is then later translated to a formula. i dont know python contatenation works, but you need to pass the row number in there:

howcell.value = '=datedif(A'+ row ',$A$1,"D")'

answered Jan 30 '20 at 21:41

jonadv

434
6
16

Calculate formula downwards with openpyxl

2 Answers2