[matplotlib]How to Insert Graphs into Excel

This article shows how to insert graphs into Excel.

How to save a graph as an image and then insert it into Excel

I show how to save a graph as an image and then insert it into Excel.

By using "openpyxl", a graph is inserted into Excel.

This is the example code.

import matplotlib.pyplot as plt
import openpyxl


path_fig = r'test\test.png'
path_excel = r'test\test.xlsx'



# Create graph
data_x = [1,2,3,4,5]
data_y = [x*x for x in data_x]

fig, ax = plt.subplots(figsize = (5,5), facecolor='lightblue')

ax.plot(data_x, data_y, linestyle='solid', marker='o')

ax.set_xlabel('x')
ax.set_ylabel('y')

plt.show()
fig.savefig(path_fig)



# Insert graph into excel
wb = openpyxl.load_workbook(path_excel)
sh = wb.create_sheet('graph')

img = openpyxl.drawing.image.Image(path_fig)
sh.add_image(img, 'B2')

wb.save(path_excel)
wb.close()

When the above code is executed, a graph is inserted into Excel.

> fig.savefig(path_fig)

Save the graph as an image using "savefig" method.

> wb = openpyxl.load_workbook(path_excel)
> sh = wb.create_sheet('graph')

An existing Excel file is opened and a new sheet is created.

> img = openpyxl.drawing.image.Image(fig_path)
> sh.add_image(img, 'B2')

By using "openpyxl", the graph is loaded and pasted at "B2 cell".

sponsored link

[Supplement]How to keep a graph in PC memory and then insert it into Excel

I show how to keep a graph in PC memory and then insert it into Excel.

This is the example code.

import matplotlib.pyplot as plt
import openpyxl
import io


path_excel = r'test\test.xlsx'


# Create graph
data_x = [1,2,3,4,5]
data_y = [x*x for x in data_x]

fig, ax = plt.subplots(figsize = (5,5), facecolor='lightblue')

ax.plot(data_x, data_y, linestyle='solid', marker='o')

ax.set_xlabel('x')
ax.set_ylabel('y')

plt.show()



# Insert graph into excel
wb = openpyxl.load_workbook(path_excel)
sh = wb.create_sheet('graph')

img_data = io.BytesIO()
fig.savefig(img_data, format='png')

img = openpyxl.drawing.image.Image(img_data)
sh.add_image(img, 'B2')

wb.save(path_excel)
wb.close()

When the above code is executed, a graph is inserted into Excel.

> img_data = io.BytesIO()
> fig.savefig(img_data, format='png')

"Io" module is a standard python module.

By using "io" module, binary data can be handled in memory.

By using "savefig" method, the graph data is kept in memory.

sponsored link