Python xlsx loops, repeat actions

Question

Python xlsx loops, repeat actions

Navigation

#1 by (0 votes)

1

I have the following problem:

With the code I will paste below, I charge my .xlsx. In it I have columns and in them different values. I add one more column that will have a specific value for each row. My problem is that I do it individually but not automatically and I have a lot of records to calculate:

from openpyxl import load_workbook
wb = load_workbook(filename='Data.xlsx')
ws = wb['Data']

ws.auto_filter.ref = "A1:E1"

ws["E1"] = 'Fillrate%'

ws["E2"] = round(((ws['C2'].value)/(float(ws['B2'].value))*100),2)
ws["E3"] = round(((ws['C3'].value)/(float(ws['B3'].value))*100),2)
ws["E4"] = round(((ws['C4'].value)/(float(ws['B4'].value))*100),2)
ws["E5"] = round(((ws['C5'].value)/(float(ws['B5'].value))*100),2)
ws["E6"] = round(((ws['C6'].value)/(float(ws['B6'].value))*100),2)


wb.save("Analized_Data.xlsx")

What I need is that:

ws["E2"] = round(((ws['C2'].value)/(float(ws['B2'].value))*100),2)

be automatically for all rows instead of having to type row by line.

This is my original xlsx:

This would be my result so far:

loops python excel

asked by Martin Bouhier 24.08.2017 в 10:36

source

1 answer

Batch or Powershell to align partitions of a hard disk? Form-control ('date')

score 0 · Answer 1

You can try a loop that runs through all the rows:

from openpyxl import load_workbook
wb = load_workbook(filename='Data.xlsx')
ws = wb['Data']

for row in ws.nrows :
    /* aquí tienes cada fila de la hoja */
      ws["E%s" % row] = round(((ws['C%s' % row].value)/(float(ws['B%s' % row].value))*100),2)

wb.save("Analized_Data.xlsx")

PS: I do not know much about Python , if I have a bug, comment it and I'll correct.