10 Python Scripts for Automating Excel Tasks with pandas, openpyxl, and matplotlib

1. Install required libraries

First, ensure that pip install pandas openpyxl is executed to install the core Python packages for Excel manipulation.

2. Read an Excel file

Load an Excel workbook and display its first rows:

import pandas as pd
df = pd.read_excel('example.xlsx')
print(df.head())  # 显示前5行数据

3. Write to an Excel file

Save a DataFrame to a new workbook without the index column:

df.to_excel('output.xlsx', index=False)  # index=False不保存索引列

4. Merge multiple worksheets

Combine all sheets from a multi‑sheet workbook into a single DataFrame:

xls = pd.ExcelFile('multi_sheets.xlsx')
dfs = {sheet_name: xls.parse(sheet_name) for sheet_name in xls.sheet_names}
combined_df = pd.concat(dfs.values(), ignore_index=True)

5. Data cleaning – drop rows with missing values

df_cleaned = df.dropna()  # 删除所有含空值的行

6. Data filtering

filtered_df = df[df['Sales'] > 10000]  # 筛选出销售额大于10000的记录

7. Create a pivot table

pivot_table = pd.pivot_table(df, values='Sales', index=['Category'], aggfunc=np.sum)

8. Automatic chart generation

Use matplotlib to create and save a bar chart of monthly sales:

import matplotlib.pyplot as plt
plt.figure(figsize=(10,6))
df.plot(kind='bar', x='Month', y='Sales')
plt.title('Monthly Sales')
plt.savefig('monthly_sales.png')

9. Batch modify cell styles

After saving with pandas, use openpyxl to apply conditional formatting (e.g., highlight profits > 5000 in red):

from openpyxl import load_workbook
wb = load_workbook('output.xlsx')
ws = wb.active
for row in ws.iter_rows(min_row=2, max_col=3, values_only=True):
    if row[2] > 5000:  # 假设第三列是'Profit'，大于5000标红
        cell = ws.cell(row=row[0], column=3)
        cell.fill = PatternFill(start_color="FF0000", end_color="FF0000", fill_type="solid")
wb.save('styled_output.xlsx')

10. Automatic email sending of Excel reports

Send the generated report as an email attachment using smtplib and the email package:

import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.base import MIMEBase
from email.mime.text import MIMEText
from email.utils import COMMASPACE
from email import encoders

def send_email(sender, recipients, subject, body, attachment_path):
    msg = MIMEMultipart()
    msg['From'] = sender
    msg['To'] = COMMASPACE.join(recipients)
    msg['Subject'] = subject
    msg.attach(MIMEText(body))
    part = MIMEBase('application', "octet-stream")
    with open(attachment_path, 'rb') as file:
        part.set_payload(file.read())
    encoders.encode_base64(part)
    part.add_header('Content-Disposition', 'attachment; filename="%s"' % os.path.basename(attachment_path))
    msg.attach(part)
    server = smtplib.SMTP('smtp.example.com', 587)
    server.starttls()
    server.login('your_username', 'your_password')
    server.sendmail(sender, recipients, msg.as_string())
    server.quit()

# 使用函数发送邮件
send_email('[email protected]', ['[email protected]', '[email protected]'],
           'Monthly Sales Report', 'Please find attached the latest sales report.', 'monthly_sales.xlsx')

By mastering these ten scripts, you can automate the entire Excel workflow—from data ingestion and cleaning to analysis, visualization, and report distribution—making you a highly efficient office automation specialist.