Write values to cells in Excel dynamically in Python

Question

I am using the openpyxl method to write values in Python to Excel. I have around 30 integer values in python which I want to dynamically write to specific Excel cells.

For example, value1-value5 should be written to B1-B5, when this is complete, we should move to the next column and write value6-value10 in cells C1-C5.

I am using the below code, but need help making it dynamic

#create workbook object
wb=openpyxl.load_workbook("report.xlsx")

type (wb)
#get sheet names
wb.get_sheet_names()

#create reference for sheet on which to write
worksheet= wb.get_sheet_by_name("Sheet1") 

#use sheet reference and write the cell address
**worksheet["B1"]=value1** #this part needs to be automated

#save workbook
wb.save("report.xlsx")

Can you expand on your explanation a bit? I'm not sure what "making it dynamic" means. — glibdud
– glibdud, Commented Nov 30, 2017 at 13:51
By dynamic, I mean I want to put it in a loop. Like I mentioned above, value1-value5 should be written to B1-B5, when this is complete, we should move to the next column and write value6-value10 in cells C1-C5. — N. Rao
– N. Rao, Commented Nov 30, 2017 at 13:53

zipa · Accepted Answer · 2017-11-30 14:52:45Z

2

If you want to create these reference strings dynamically this will help:

column, row = 66, 1

for v in values:
    if row == 6:
        row = 1
        column += 1
    worksheet['{}{}'.format(chr(column),row)] = value
    row += 1

This will start with B1 and once it reaches B5 it will move to C1 and so on.

Doesn't work after column Z.

answered Nov 30, 2017 at 14:52

zipa

28k6 gold badges45 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Write values to cells in Excel dynamically in Python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related