Python操作Excel的10个高效自动化脚本实战

目标与环境准备

本系列脚本旨在替代日常繁琐的Excel手工操作，通过Python实现Excel自动化流程。执行前请确保本地已安装Python 3.x，并使用终端运行 pip install pandas openpyxl 安装依赖库。后续操作均假设源文件（.xlsx）与脚本位于同一工作目录下。

实操步骤

第1步：读取与写入Excel文件

使用 pandas 加载目标工作表并校验数据结构，处理完毕后导出新文件。关闭索引可防止产生额外空白列。

import pandas as pd

# 读取指定工作表并预览前5行
source_df = pd.read_excel('data.xlsx', sheet_name='Sheet1')
print(source_df.head())

# 构建新数据框并导出，index=False 去除序号列
raw_data = {'Name': ['John', 'Anna', 'Peter'], 'Age': [28, 24, 35]}
target_df = pd.DataFrame(raw_data)
target_df.to_excel('output.xlsx', index=False)

第2步：批量合并多个Excel文件

针对跨文件夹汇总需求，遍历当前目录匹配后缀，逐个读取后使用 concat 纵向堆叠，最后统一保存。

import os
import pandas as pd

# 筛选当前目录下的xlsx文件
files = [f for f in os.listdir('.') if f.endswith('.xlsx')]
combined = pd.DataFrame()

for fname in files:
    # 忽略已生成的汇总文件以免循环嵌套
    if fname == 'combined.xlsx': continue
    df = pd.read_excel(fname)
    combined = pd.concat([combined, df], ignore_index=True)

combined.to_excel('combined.xlsx', index=False)

第3步：数据筛选与排序

利用布尔索引过滤记录，配合 sort_values 实现单列或多列条件排序。

# 筛选年龄大于25的行
filtered = source_df[source_df['Age'] > 25]

# 单列升序排列
sorted_asc = source_df.sort_values(by='Name')

# 多重排序：年龄降序，姓名升序
multi_sort = source_df.sort_values(by=['Age', 'Name'], ascending=[False, True])

第4步：生成数据透视表

将分组统计逻辑自动化，对应表格维度进行行列交叉聚合。

# 假设数据包含员工、区域、销售额列
pivot = pd.pivot_table(
    source_df,
    values='Sales',
    index='Salesperson',
    columns='Region',
    aggfunc='sum',
    fill_value=0
)
pivot.to_excel('pivot_table.xlsx')

第5步：提取特定列到新文件

仅保留业务所需字段，减少文件体积并隐藏敏感信息。

# 选取指定列并独立导出
extracted = source_df[['Name', 'Email']]
extracted.to_excel('extracted_data.xlsx', index=False)

第6步：重命名工作表

当需要规范模板结构时，可通过 openpyxl 遍历并修改Sheet标题。

from openpyxl import load_workbook

wb = load_workbook('data.xlsx')
for i, name in enumerate(wb.sheetnames, start=1):
    ws = wb[name]
    ws.title = f'Sheet_{i}'
wb.save('renamed.xlsx')

第7步：应用条件格式

基于单元格数值动态填充背景色，无需手动设置规则。

from openpyxl import load_workbook
from openpyxl.styles import PatternFill

wb = load_workbook('data.xlsx')
ws = wb.active
red_fill = PatternFill(start_color='FF0000', end_color='FF0000', fill_type='solid')

# 遍历B列，值小于50的单元格标红
for row in ws.iter_rows(min_col=2, max_col=2):
    for cell in row:
        if isinstance(cell.value, (int, float)) and cell.value < 50:
            cell.fill = red_fill
wb.save('formatted.xlsx')

第8步：自动填充单元格公式

直接向单元格注入Excel原生表达式，保持工作簿的可交互性。

from openpyxl import load_workbook

wb = load_workbook('data.xlsx')
ws = wb.active
for r in range(2, ws.max_row + 1):
    ws[f'C{r}'] = f'=SUM(A{r}:B{r})'
wb.save('with_formulas.xlsx')

第9步：打包并发送邮件附件

处理完成后调用标准库构建邮件对象，将报表作为二进制流附加发送。

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

msg = MIMEMultipart()
msg['From'] = 'your_email@example.com'
msg['To'] = 'recipient@example.com'
msg['Subject'] = '自动发送的Excel报表'
msg.attach(MIMEText("请查收附件中的Excel报表。", 'plain'))

filename = "report.xlsx"
with open(filename, "rb") as f:
    part = MIMEBase('application', 'octet-stream')
    part.set_payload(f.read())
encoders.encode_base64(part)
part.add_header('Content-Disposition', f'attachment; filename= {filename}')
msg.attach(part)

with smtplib.SMTP('smtp.example.com', 587) as server:
    server.starttls()
    server.login('your_email@example.com', 'your_password')
    server.send_message(msg)

完成判断与排错建议

成功执行上述脚本后，可在同级目录确认新生成的 .xlsx 文件。打开检查数据行数、透视表聚合值、条件格式覆盖率及公式下拉范围是否与预期一致。若遇到报错：

• ModuleNotFoundError：检查是否已通过 pip 安装 pandas 与 openpyxl。
• SyntaxWarning：旧版 pandas 的 concat 建议显式传入 ignore_index=True 以避免警告。
• smtplib.SMTPAuthenticationError：需核对邮箱服务商提供的SMTP主机名、端口及授权码，并非普通登录密码。

简要总结

通过合理组合 pandas 的数据处理能力与 openpyxl 的底层样式控制，即可搭建完整的 办公效率 提升流水线。这些 Excel自动化 脚本覆盖了从数据导入、清洗整合、格式美化到定时推送的全生命周期，只需替换文件名与路径参数即可直接复用。以上就是Python操作Excel的10个高效自动化脚本的详细内容，更多关于Python办公自动化实战的资料请关注本站其它相关文章！