CODEWITHSUNDEEP
pythonopenpyxl
Bobby
Bobby

Reputation: 109

writing excel formula for each row in a column

I have data from website that writing to excel every time program run. I need to have formula in few columns using the data in certain cells. Below is my code and I have manually added values in cells for this example. I need formulas in column B, Column D and Column F. Column B should be Sum of A2-A1 Column D should be Sum of C2-C1 Column F should be sum of B1/D1

After writing the row , I need to do formulas for all the rows in columns B, D, F. How can I insert formula using python? Can someone help.

from openpyxl import load_workbook
path = r"C:/folder/file.xlsx"
wb = load_workbook(path)

sheet = wb[sheet_name]

row = sheet.max_row + 1
sheet.cell(row=row, column=1, value="10")  # => column A
sheet.cell(row=row, column=2, value="20")   # => column C
sheet.cell(row=row, column=3, value="100")  # => column E

Upvotes: 0

Views: 924

Answers (1)

Алексей Р
Алексей Р

Reputation: 7627

from openpyxl import load_workbook
path = r"c:/Temp/file.xlsx"
wb = load_workbook(path)

sheet_name = "Sheet1"
sheet = wb[sheet_name]

for row in range(1, 101): # loop over 100 first rows
    sheet.cell(row=row, column=1, value=10)  # => column A
    sheet.cell(row=row, column=3, value=20)   # => column C
    sheet.cell(row=row, column=5, value=100)  # => column E

    # make the formulae
    # I did not understand the meaning of the formulas without sample data
    # and examples, so I tried to do it literally as it was written,
    # with correction for line offset
    sheet.cell(row=row+1, column=2, value=f'=SUM(A{row+1}-A{row})')
    sheet.cell(row=row+1, column=4, value=f'=SUM(C{row+1}-C{row})')
    sheet.cell(row=row+1, column=6, value=f'=IFERROR(SUM(B{row}/D{row}),"-")')

wb.save(path)
wb.close

Upvotes: 1

Related Questions