10个必备Python脚本：高效操作Excel

前言

作为数据分析师、财务人员或办公自动化爱好者，Excel表格操作是我们日常工作中不可或缺的一部分。而Python凭借其强大的数据处理能力，可以极大地提升我们操作Excel的效率。本文将介绍10个必学的Python脚本，帮助你自动化处理Excel表格，节省大量重复劳动时间。

1. 读取Excel文件

使用openpyxl或pandas库可以轻松读取Excel文件：

import pandas as pd

# 读取Excel文件
df = pd.read_excel('data.xlsx', sheet_name='Sheet1')

# 显示前5行数据
print(df.head())

2. 写入Excel文件

将处理后的数据保存回Excel：

# 创建一个DataFrame
data = {'Name': ['John', 'Anna', 'Peter'],
        'Age': [28, 24, 35]}
df = pd.DataFrame(data)

# 写入Excel文件
df.to_excel('output.xlsx', index=False)

3. 合并多个Excel文件

批量处理多个Excel文件时，合并是非常常见的需求：

import os

# 获取当前目录下所有xlsx文件
files = [f for f in os.listdir('.') if f.endswith('.xlsx')]

# 合并所有文件
combined_df = pd.DataFrame()
for file in files:
    df = pd.read_excel(file)
    combined_df = pd.concat([combined_df, df], ignore_index=True)

# 保存合并后的文件
combined_df.to_excel('combined.xlsx', index=False)

4. 筛选和排序数据

Python可以轻松实现复杂的数据筛选和排序：

# 筛选年龄大于25的记录
filtered_df = df[df['Age'] > 25]

# 按姓名升序排列
sorted_df = df.sort_values(by='Name')

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

5. 数据透视表

创建类似Excel中的数据透视表：

# 假设df包含'Salesperson', 'Region', 'Sales'等列
pivot_table = pd.pivot_table(df, 
                           values='Sales',
                           index='Salesperson',
                           columns='Region',
                           aggfunc='sum',
                           fill_value=0)
pivot_table.to_excel('pivot_table.xlsx')

6. 条件格式设置

使用openpyxl实现类似Excel的条件格式：

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')

7. 批量重命名工作表

from openpyxl import load_workbook

wb = load_workbook('data.xlsx')

# 重命名所有工作表
for i, sheet in enumerate(wb.sheetnames, start=1):
    ws = wb[sheet]
    ws.title = f'Sheet_{i}'

wb.save('renamed.xlsx')

8. 提取特定列到新文件

# 提取'Name'和'Email'列到新文件
extracted_df = df[['Name', 'Email']]
extracted_df.to_excel('extracted_data.xlsx', index=False)

9. 自动填充公式

from openpyxl import load_workbook

wb = load_workbook('data.xlsx')
ws = wb.active

# 在C列添加SUM公式
for row in range(2, ws.max_row + 1):
    ws[f'C{row}'] = f'=SUM(A{row}:B{row})'

wb.save('with_formulas.xlsx')

10. 发送带Excel附件的邮件

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报表'

# 添加正文
body = "请查收附件中的Excel报表。"
msg.attach(MIMEText(body, 'plain'))

# 添加附件
filename = "report.xlsx"
attachment = open(filename, "rb")

part = MIMEBase('application', 'octet-stream')
part.set_payload(attachment.read())
encoders.encode_base64(part)
part.add_header('Content-Disposition', f"attachment; filename= {filename}")

msg.attach(part)

# 发送邮件
server = smtplib.SMTP('smtp.example.com', 587)
server.starttls()
server.login('your_email@example.com', 'your_password')
text = msg.as_string()
server.sendmail('your_email@example.com', 'recipient@example.com', text)
server.quit()

总结

通过本文介绍的10个必备Python脚本，我们深入了解了Python在Excel操作方面的强大能力。从简单的数据读取与写入，到复杂的数据清洗、转换与分析，Python都提供了高效且灵活的解决方案。这些脚本不仅能够帮助我们节省大量的时间和精力，还能显著提高数据处理的准确性和效率。随着数据科学的不断发展，Python在Excel操作方面的应用也将越来越广泛。希望本文能够激发读者对Python操作Excel的兴趣，并鼓励大家在实践中不断探索和创新，以更好地应对数据处理与分析的挑战。