1. The idea of this paper

Matplotlib is probably the most basic and most commonly used Python drawing library in Python! Most people who use Python for data analysis have, and this is not the focus of this chapter, so I won’t go over it again.

As you should know, the matplotlib diagrams are usually displayed directly on the console for us to view. But the need to embed a drawn Matplotlib in Excel is really a bit novel.

We already know that Matplotlib can draw many kinds of graphs. If you can do this, can you use pandas to read data in Excel, use Matplotlib to draw a graph, embed the graph in Exce, and send it to your colleagues as if it were in Excel? (Great idea!)

2. Requirement realization

First, there is an Excel table with the following data:

So how do you implement the requirement mentioned above? Here we use the Xlwings library in Python. Below, we explain the process step by step.

① Use pandas to read Excel data

Pandas allows you to read data in a wide variety of formats, in this case, Excel data in XLSX format.

import pandas as pd

df = pd.read_excel("matplotlib.xlsx")
df
Copy the code

The results are as follows:

② Use Matplotlib to plot

I’m not going to be too responsible here, but let’s just use a simple line diagram to illustrate.

import matplotlib.pyplot as plt

figure = plt.figure()
plt.plot(df["x"],df["y"])
plt.show()
Copy the code

The results are as follows:

③ Use Xlwings to embed the above graphics in Excel

So here I’m writing the graph that I drew into a new worksheet. You can go ahead and think about how you can put data and graphics on the same worksheet.

import xlwings as xw

app = xw.App(visible=False)
wb = app.books.add()
ws = wb.sheets.add("New worksheet")
Write the drawing to the workbook, passing it directly to the canvas figure object
ws.pictures.add(figure)
wb.save("matplotlib1.xlsx")
wb.close()
app.quit()
Copy the code

End result:

Here is another effect map, we can have a look!

Expansion: This article is mainly to explain a solution to the problem for you, the specific details of the problem, we need to dig down to learn. The three libraries involved in the article, it is recommended to learn data analysis friends go to learn.

Read more

Top 10 Best Popular Python Libraries of 2020 \

2020 Python Chinese Community Top 10 Articles \

5 minutes to quickly master the Python timed task framework \

Special recommendation \

\

Click below to read the article and join the community