Matplotlib/Openpyxl - Pasting multiple images into different worksheets excel

Question

I am having a real problem understanding ow python is reading my code and need some help. I have several worksheets within one excel workbook, all containing one dataframe table and one chart. I have organized my code in a way where i run all the different dataframe, write them to each excel worksheet, plot each chart, save the chart to a png file and then use openpyxl to load the image to each worksheet.

The key here seems to how and where i save the file to the workbook. For example, if i type xfile.save('bikes.xlsx') after the last image is uploaded to to the last worksheet, it displays only the last image and none of the other images in the other worksheets. If is type save after the first image is loaded to its worksheet, excel displays the image for the first worksheet. If i put the save function after each image is loaded to their worksheets, only the last image displays in the last worksheet.

#Plot chart 1
df3.plot(x='Length', y=['types of cats'], figsize=(8,4))
plt.savefig('Typesofcats.png')

#Write PNG file to existing worksheet
from openpyxl import Workbook
from openpyxl.drawing.image import Image
xfile1 = openpyxl.load_workbook('Things.xlsx')
sheet1 = xfile1.get_sheet_by_name('Types of Cats')
img1 = Image('Typesofcats.png')
sheet1.add_image(img1, 'I6')
xfile.save('Things.xlsx')

#Plot chart 2
df5.plot(x='Length', y=['Types of dogs'], figsize=(8,4))
plt.savefig('Typesofdogs.png')

#Write PNG file to existing worksheet
from openpyxl import Workbook
from openpyxl.drawing.image import Image
xfile2 = openpyxl.load_workbook('Things.xlsx')
sheet2 = xfile2.get_sheet_by_name('Types of dogs')
img2 = Image('Typesofdogs.png')
sheet2.add_image(img2, 'I6')
xfile2.save('Things.xlsx')

#Plot chart 3
df6.plot(x='Length', y=['Types of pigs'], figsize=(7,3))
plt.savefig('Typesofpigs.png')

#Write PNG file to existing worksheet
from openpyxl import Workbook
from openpyxl.drawing.image import Image
xfile3 = openpyxl.load_workbook('Things.xlsx')
sheet3 = xfile2.get_sheet_by_name('Types of pigs')
img3 = Image('Typesofpigs.png')
sheet3.add_image(img3, 'F6')
xfile3.save('Things.xlsx')

Looks like you're saving and re-opening files. Images are removed when you open files. — Charlie Clark

spacedinosaur10 spacedinosaur10 · Accepted Answer · 2016-08-29T12:37:20

Ah okay yes! Because I am opening and closing the workbook each time, the images are getting deleted from the previous worksheet. If I simply only open the connection once, paste all images to each of the worksheets, and then close it works.

#Write PNG file to existing worksheet
from openpyxl import Workbook
from openpyxl.drawing.image import Image
xfile = openpyxl.load_workbook('Things.xlsx')
sheet = xfile.get_sheet_by_name('Types of Cats')
img = Image('Typesofcats.png')
sheet.add_image(img, 'L6')
sheet1 = xfile.get_sheet_by_name('Types of Dogs')
img1 = Image('Typesofdogs.png')
sheet1.add_image(img1, 'I6')
sheet2 = xfile.get_sheet_by_name('Types of Pigs')
img2 = Image('Typesofpigs.png')
sheet2.add_image(img2, 'I6')
xfile.save('Things.xlsx')

Thank you for your help.

Matplotlib/Openpyxl - Pasting multiple images into different worksheets excel

1 Answers