1

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?

spacedinosaur10
  • 695
  • 3
  • 10
  • 24
  • Why woud you want a nested loop here? You can do 2 things in single for loop. Since you probably have a list of country names `names` you can jsut use `enumerate(wb.worksheets)` and open images with `names[i]`. Or zip them if you want it to be more pythonic. Also in first loop you use `i` as iterator and then reassign `i` inside loop. You should never do this – Tomasz Plaskota Sep 27 '16 at 20:31

1 Answers1

3

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')
brianpck
  • 8,084
  • 1
  • 22
  • 33
  • Really appreciate your help on this! Sorry i am pretty new to python. I have used the following code: 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('snakes.xlsx') and I am getting an error message as follows " IOError: [Errno 2] No such file or directory: u'sheet1.png'. Why is there a u appearing in front of the worksheet title? The png file is sheet 1. – spacedinosaur10 Sep 27 '16 at 21:10
  • @spacedinosaur10 Let me know if you have further questions. Otherwise an upvote/accept is appreciated! – brianpck Sep 27 '16 at 21:11
  • see my question above? I also tried adding my dictionary + the png as follows: img = openpyxl.drawing.image.Image(country + '.png') but i got another error as follows: TypeError: unsupported operand type(s) for +: 'dict' and 'str'. Not a happy python today – spacedinosaur10 Sep 27 '16 at 21:17
  • The `u` stands for unicode (http://stackoverflow.com/questions/599625/python-string-prints-as-ustring) but that's not the issue: the issue is that the png file has a space (`sheet 1` instead of `sheet1`). – brianpck Sep 27 '16 at 21:22
  • Woops! No that is just me adding a space! I figured out my problem.. the reason I had folder in there (but did not include in the code) is folder = the location of the excel worksheet. So when i do the following : img = openpyxl.drawing.image.Image(folder+ ws.title + '.png') it works!!!!! You are the best @brianpck. Thanks for teaching me. – spacedinosaur10 Sep 27 '16 at 21:29