Emac Emac - 1 year ago 160
Python Question

Openpyxl Python - Vlookup Iterate through rows

I'm trying to automate a daily report we have, and I'm using a query to pull in data and writing it in Excel using openpyxl, and then doing a vlookup in openpyxl to match a cell value. Unfortunately I'm hung up on how to iterate through the rows to find the cell value to look up.

for row in ws['E5:E91']:
for cell in row:
cell.value = "=VLOOKUP(D5, 'POD data'!C1:D87, 2, FALSE)"


It works except I don't know how to change the
D5
value to look up
D6
,
D7
,
D8
, etc. depending on the row I'm on. I'm honestly at a loss for how to best approach this. Obviously I don't feel like writing the formula out for every single row, and there's other columns I'd like to do this for once I get it.

Answer Source

Using your example, you can do:

for row in ws['E5:E91']:
    for cell in row:
       cell.value = "=VLOOKUP(D%d, 'POD data'!C1:D87, 2, FALSE)" % cell.row
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download