CODEWITHSUNDEEP
pythonexcelexcel-formulaformula
FullMetalJumper
FullMetalJumper

Reputation: 13

Python and Excel Formula

Complete beginner here but have a specific need to try and make my life easier with automating Excel. I have a weekly report that contains a lot of useless columns and using Python I can delete these and rename them, with the code below.

from openpyxl import Workbook, load_workbook

wb = load_workbook('TestExcel.xlsx')
ws = wb.active

ws.delete_cols(1,3)
ws.delete_cols(3,8)
ws.delete_cols(4,3)

ws.insert_cols(3,1)

ws['A1'].value = "Full Name"
ws['C1'].value = "Email Address"

ws['C2'].value = '=B2&"@testdomain.com"'

wb.save('TestExcelUpdated.xlsx')

This does the job but I would like the formula to continue from B2 downwards (since the top row are headings).

ws['C2'].value = '=B2&"@testdomain.com"'

Obviously, in Excel it is just a case of dragging the formula down to the end of the column but I'm at a loss to get this working in Python. I've seen similar questions asked but the answers are over my head. Would really appreciate a dummies guide. Example of Excel report after Python code

Upvotes: 0

Views: 106

Answers (1)

el_oso
el_oso

Reputation: 1061

one way to do this is by iterating over the rows in your worksheet.

for row in ws.iter_rows(min_row=2): #min_row ensures you skip your header row
    row[2].value  = '=B' + str(row[0].row) + '&"@testdomain.com"'

row[2].value selects the third column due to zero based indexing. row[0].row gets the number corresponding to the current row

Upvotes: 1

Related Questions