python - Can I insert matplotlib graphs into Excel programmatically?

15
votes

I am saving matplotlib files as .tiff images. I'd like to be able to then open an excel file and paste the image there.

openpyxl doesnot seem to support image embedding. xlwt does but only bmp.

ALternatively if i can programmatically convert tiff to bmp, that might help also.

Ideas on either are welcome.

Similar to

Embed multiple jpeg images into EXCEL programmatically?

However converting from tiff to bmp is acceptable as my volume of graphs is small (approximately 10 per file).

3
pythonexcelmatplotlib

3 Answers

9
votes

Here is what I found from two different links on the web, that worked perfectly for me. Matplotlib allows saving png files which is what I make use of here:

from PIL import Image

file_in = "image.png"
img = Image.open(file_in)
file_out = 'test1.bmp'
print len(img.split()) # test
if len(img.split()) == 4:
    # prevent IOError: cannot write mode RGBA as BMP
    r, g, b, a = img.split()
    img = Image.merge("RGB", (r, g, b))
    img.save(file_out)
else:
    img.save(file_out)

from xlwt import Workbook
w = Workbook()
ws = w.add_sheet('Image')
ws.insert_bitmap(file_out, 0, 0)
w.save('images.xls')

The image part of the code is from Ene Urans response here http://www.daniweb.com/software-development/python/threads/253957/converting-an-image-file-png-to-a-bitmap-file.

The xlwt is simply form the documentation of xlwt I found at http://www.simplistix.co.uk/presentations/python-excel.pdf.

7
votes

Openpyxl actually does support image embedding, and might work better for those using .png or existing .xlsx files! The code below appends an image to cell A1 of input.xlsx and saves the file as output.xlsx.

import matplotlib.pyplot as plt
import openpyxl

# Your plot generation code here...
plt.savefig("myplot.png", dpi = 150)

wb = openpyxl.load_workbook('input.xlsx')
ws = wb.active

img = openpyxl.drawing.Image('myplot.png')
img.anchor(ws.cell('A1'))

ws.add_image(img)
wb.save('output.xlsx')

EDIT June 2020: I've been informed openpyxl has changed since time of writing. Line 7 should be:

img = openpyxl.drawing.image.Image('myplot.png')

There is an extra .image in there now.

3
votes

This worked out for me:

import openpyxl

wb = openpyxl.load_workbook('input.xlsx')
ws = wb.active

img = openpyxl.drawing.image.Image('myplot.png')
ws.add_image(ws.cell('A1'))

ws.save('output.xlsx')