Python Operation Excel Insert Chart: Unlock efficient passwords for data visualization
In the field of data analysis and display, Python has become a right-hand assistant to many developers and data workers with its powerful library support. Embedding charts into Excel files can not only enrich the data presentation form, but also make information transmission more intuitive and efficient. Today, I will take you to explore in-depth how to use Python to insert charts in Excel to achieve advanced data visualization operations.
Before implementing Python to insert charts in Excel, you need to make sure that the following key libraries are installed:
pandas: used for data processing and analysis, making it convenient to organize data into appropriate data structures.
matplotlib: is a classic drawing library in Python that is responsible for generating various types of charts. In this example, it is used to create bar charts.
openpyxl: focuses on operating .xlsx files of Excel 2010 and above, and supports adding, modifying, and reading data in Excel worksheets, as well as inserting charts.
Pillow: As a friendly branch of the Python Imaging Library (PIL), it is mainly used to process image-related operations, and is used here to load and adjust chart images.
Here is a complete code example for inserting a bar chart into Excel:
from openpyxl import load_workbook
from import Image
import as plt
import pandas as pd
def insert_sheet_to_chart_bar(filename, df):
# Extract data [{'Number of A members': 1783, 'Number of B members': 648}] categories = list()
values = list(().tolist())
# Generate a bar chart (figsize=(8, 4))
bars = (categories, values, color=['#1f77b4', '#ff7f0e'])
# Set Chinese display ['-serif'] = ['SimHei']
("Comparison of Members")
("quantity")
# Add a numeric tag for bar in bars:
height = bar.get_height()
(bar.get_x() + bar.get_width() / 2., height,
f'{int(height)}',
ha='center', va='bottom')
# Save chart pictures chart_path = 'membership_chart.png'
(chart_path, dpi=300, bbox_inches='tight')
()
# Create Excel and insert charts wb = Workbook()
ws =
# Insert chart picture img = Image(chart_path)
= 1000 # Adjust width = 550 # Adjust height ws.add_image(img, 'D2') # Insert to cell D2 (filename)
Code description:
- The function insert_sheet_to_chart_bar receives two parameters, filename represents the Excel file path to insert the chart, and df is a pandas DataFrame object containing the data.
- Flatten the data values into a list as the values of the histogram by extracting the column names of the DataFrame as categories of the histogram.
- Use matplotlib to create a bar chart, set the chart title, axis labels, and add numeric labels to enhance the readability of the chart. Then save the generated chart as a picture file.
- Use openpyxl to load the target Excel file, get the specified worksheet, load the chart image through the Image class, and set the appropriate width and height. Finally, insert the image into the specified cell position and save the modified Excel file.
In actual applications, if you want to insert other types of charts, such as line charts, pie charts, etc., you can adjust the drawing methods and parameters of matplotlib accordingly. In addition, the following points should be noted:
Make sure that the Excel file path is correct and that the file is not occupied by other programs during reading and saving.
According to the actual data scale and requirements, the chart size and picture resolution are reasonably adjusted to ensure that the chart displays clearly and beautifully in Excel. If the image size is too large or too small, it may affect the overall layout and readability.
When the data contains Chinese characters, setting the font to SimHei (bold) can effectively avoid the problem of garbled Chinese display, but there may be differences in the installation and configuration of fonts in different system environments, so you need to ensure that the corresponding fonts are installed in the system.
Mastering Python's ability to insert charts in Excel can bring great convenience and efficiency improvement to the production of data analysis reports, visual display of business data, and other work, helping everyone to be more at ease in the field of data processing and display.
Advanced: Accurately insert charts into specified Excel worksheets
In the process of processing data, we often need to accurately insert the generated chart into the specified worksheet of the Excel file with existing data. With the powerful library combination of Python, this operation can be implemented efficiently. The following is the optimized and commented code to help you easily accomplish this task.
from openpyxl import load_workbook
from import Image
import as plt
import pandas as pd
def insert_sheet_to_chart_bar(filename, df, sheet_name):
"""
Insert the bar chart into the specified Excel worksheet
parameter:
filename -- Excel file path
df -- pandas DataFrame object containing chart data
sheet_name -- Target worksheet name
"""
# Extract data: Get column names and data values from DataFrame categories = list() # Get column names as histogram classification tags values = list(().tolist()) # Flatten and convert data into list form
# Generate a bar chart (figsize=(8, 4))
bars = (categories, values, color=['#1f77b4', '#ff7f0e'])
# Set Chinese display: Specify the font to ensure that the Chinese title and label are displayed correctly ['-serif'] = ['SimHei']
("Comparison of Members") # Set the chart title ("quantity") # Set the vertical axis label
# Add a numerical label: display specific numerical values above each column for bar in bars:
height = bar.get_height()
(bar.get_x() + bar.get_width() / 2., height,
f'{int(height)}',
ha='center', va='bottom')
# Save chart picture: Save the generated chart as PNG format chart_path = 'membership_chart.png'
(chart_path, dpi=300, bbox_inches='tight')
()
# Read Excel file: Load existing Excel file for modification wb = load_workbook(filename)
# Get the specified worksheet: Make sure the chart is inserted into the correct worksheet if sheet_name in :
ws = wb[sheet_name]
else:
ws = wb.create_sheet(sheet_name) # If the worksheet does not exist, create
# Insert chart picture: Load the chart picture and resize it, and then insert it to the specified position img = Image(chart_path)
= 1000 # Set image width = 550 # Set image height ws.add_image(img, 'D2') # Insert the image into the D2 cell position
# Save the modified Excel file: Make sure all changes are saved (filename)
Code application example
Suppose there is an Excel file named , which contains a worksheet called Sheet1, with two columns of data (for example: column A is the month and column B is the sales). We can insert the bar chart into Sheet1 with the following code:
# Import pandas libraryimport pandas as pd
# Create a sample DataFramedata = {'month': ['January', 'February', 'March'], 'Sales': [2500, 3200, 2800]}
df = (data)
# Read the data under the specified sheet directly
# Specify the worksheet name to readsheet_name = 'Sheet1'
df = pd.read_excel('', sheet_name=sheet_name)
print(df)
# Call functioninsert_sheet_to_chart_bar('', df, 'Sheet1')
Summary of key points
Exactly specifying the worksheet: By passing the sheet_name parameter, you can ensure that the chart is inserted into the correct Excel worksheet. If the worksheet does not exist, the code automatically creates the worksheet.
Chart generation and saving: Use the matplotlib library to generate a bar chart and save it as a picture file. When generating a chart, you can set the title, axis label, color, numerical label and other attributes of the chart according to actual needs.
Image Insertion and Adjustment: Use the openpyxl library to load saved images, adjust the width and height of the image so that it has the appropriate size when displayed in Excel, and insert it to the specified cell position.
Flexibility and scalability: This code is clear in structure and is easy to modify and expand according to specific needs. For example, you can easily adjust the chart type and style, or change the insertion position and size of the picture, etc.
This is the article about Python data visualization and inserting charts in Excel. For more related content on Python Excel, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!