CODEWITHSUNDEEP
pythonfor-loopopenpyxl
spacedinosaur10
spacedinosaur10

Reputation: 725

Iterate through worksheets in workbook- Python Nested For loop

I am trying to open an excel workbook and iterate through each of the worksheets in a loop. Here is first loop:

wb = openpyxl.load_workbook('snakes.xlsx')
for i in wb.worksheets: 
    i= 0
    wb.get_sheet_names()
    i = i + 1

Once I can successful go through each one of these worksheets, i would like to do a nested loop which takes each one of my png files and places them in the worksheets. It is important to note that the sheet names and the png files have the same names (country names) stored in a dataframe called country_names.

Second loop:

for ws in wb.worksheets: 
    img = openpyxl.drawing.image.Image(folder + str(var) + '.png')
    ws.add_image(img, 'K1')
    wb.save('snakes.xlsx')

Any ideas on how to do the nested for loop so the code loops through the images and write them to the worksheets?

Upvotes: 1

Views: 10195

Answers (1)

brianpck
brianpck

Reputation: 8254

Your code snippets seem to show a fundamental misunderstanding of how the for loop works in Python.

To loop through each sheet, you were on the right track:

wb = openpyxl.load_workbook('test.xlsx')
for sheet in wb.worksheets:
    # do stuff with "sheet"
    pass

The variable in the for loop (sheet in my example, i in yours) is the member of the list (wb.worksheets): it is not an integer index. In your example, you immediately overwrite the value of i with 0 in every loop and thus do not have a sheet to work with.

It is also worth noting get_sheet_names() is called from the workbook object, so there is no need to call it within the for loop:

>>> wb.worksheets
[<Worksheet "Sheet1">, <Worksheet "Sheet2">, <Worksheet "Sheet3">]

Finally, your second "nested for loop" (which isn't even nested) is correct, except it saves the new Excel file every loop, which is wasteful.

Since you indicate that the worksheet name is the same as PNG name, you can just call the attribute title for the worksheet when finding the image.

Below should be a working example:

wb = openpyxl.load_workbook('snakes.xlsx')
for ws in wb.worksheets: 
    img = openpyxl.drawing.image.Image(ws.title + '.png')
    ws.add_image(img, 'K1')
wb.save('new.xlsx')

Upvotes: 3

Related Questions