Python办公自动化：10个Excel处理技巧，效率提升10倍

Python办公自动化：10个Excel处理技巧，效率提升10倍

作者：Python技术实战 标签：#Python #Excel #办公自动化 #Pandas
阅读时间：8分钟
难度：⭐⭐ (适合Python初学者)

前言

还在手动处理Excel数据？每天重复复制粘贴、格式调整、数据汇总？看完这篇文章，让你从繁琐的Excel操作中解放出来！

本文将介绍10个最实用的Python Excel自动化技巧，每个技巧都配有完整代码，复制即用！

---

技巧1：批量读取多个Excel文件

场景：文件夹里有100个Excel文件，需要合并成一个。

import pandas as pd
import os

def merge_excel_files(folder_path, output_file='merged.xlsx'):
    """
    批量读取文件夹内所有Excel文件并合并
    
    Args:
        folder_path: Excel文件夹路径
        output_file: 输出文件名
    """
    all_files = [f for f in os.listdir(folder_path) if f.endswith('.xlsx')]
    
    dfs = []
    for file in all_files:
        df = pd.read_excel(os.path.join(folder_path, file))
        dfs.append(df)
    
    # 合并所有数据
    merged_df = pd.concat(dfs, ignore_index=True)
    
    # 保存
    merged_df.to_excel(output_file, index=False)
    print(f"成功合并{len(all_files)}个文件到{output_file}")

# 使用示例
merge_excel_files('./data_files')

效果：1分钟处理100个文件，手动需要2小时！

---

技巧2：自动调整列宽和格式

场景：导出的Excel列宽太窄，数据显示不全。

from openpyxl import load_workbook
from openpyxl.styles import Font, Alignment

def auto_format_excel(file_path):
    """
    自动调整列宽、设置标题格式
    """
    wb = load_workbook(file_path)
    ws = wb.active
    
    # 自动调整列宽
    for column in ws.columns:
        max_length = 0
        column = [cell for cell in column]
        for cell in column:
            try:
                if len(str(cell.value)) > max_length:
                    max_length = len(str(cell.value))
            except:
                pass
        adjusted_width = (max_length + 2) * 1.2
        ws.column_dimensions[column[0].column_letter].width = adjusted_width
    
    # 设置标题格式（加粗、居中）
    for cell in ws[1]:
        cell.font = Font(bold=True)
        cell.alignment = Alignment(horizontal='center')
    
    wb.save(file_path)
    print(f"格式调整完成：{file_path}")

# 使用
auto_format_excel('output.xlsx')

---

技巧3：数据透视表自动生成

场景：销售数据需要按地区、产品分类统计。

def create_pivot_table(df, values, index, columns, output_file='pivot.xlsx'):
    """
    自动生成数据透视表
    
    Args:
        df: 原始数据DataFrame
        values: 值字段（如['销售额']）
        index: 行字段（如['地区']）
        columns: 列字段（如['产品']）
    """
    pivot_table = df.pivot_table(
        values=values,
        index=index,
        columns=columns,
        aggfunc='sum',
        fill_value=0
    )
    
    # 保存
    pivot_table.to_excel(output_file)
    print(f"透视表已保存到{output_file}")
    
    return pivot_table

# 使用示例
import pandas as pd
df = pd.read_excel('sales_data.xlsx')
pivot = create_pivot_table(
    df,
    values=['销售额'],
    index=['地区'],
    columns=['产品']
)

---

技巧4：条件格式自动标记

场景：标记销售额>10万的单元格为红色。

from openpyxl import load_workbook
from openpyxl.styles import PatternFill
from openpyxl.formatting.rule import CellIsRule

def apply_conditional_formatting(file_path, column_letter, threshold):
    """
    应用条件格式
    
    Args:
        file_path: Excel文件路径
        column_letter: 列字母（如'C'）
        threshold: 阈值
    """
    wb = load_workbook(file_path)
    ws = wb.active
    
    # 红色填充
    red_fill = PatternFill(start_color='FFC7CE', end_color='FFC7CE', fill_type='solid')
    
    # 条件格式规则
    ws.conditional_formatting.add(
        f'{column_letter}2:{column_letter}1048576',
        CellIsRule(operator='greaterThan', formula=[f'{threshold}'], stopIfTrue=True, fill=red_fill)
    )
    
    wb.save(file_path)
    print(f"条件格式已应用到列{column_letter}")

# 使用：标记C列大于100000的单元格
apply_conditional_formatting('sales.xlsx', 'C', 100000)

---

技巧5：多Sheet数据处理

场景：一个Excel有多个Sheet，需要分别处理。

def process_multiple_sheets(file_path, operation):
    """
    处理多个Sheet
    
    Args:
        file_path: Excel文件路径
        operation: 处理函数
    """
    xls = pd.ExcelFile(file_path)
    
    with pd.ExcelWriter('processed.xlsx') as writer:
        for sheet_name in xls.sheet_names:
            df = pd.read_excel(xls, sheet_name=sheet_name)
            
            # 应用处理函数
            processed_df = operation(df)
            
            # 保存到新Sheet
            processed_df.to_excel(writer, sheet_name=sheet_name, index=False)
    
    print(f"已处理{len(xls.sheet_names)}个Sheet")

# 使用示例：删除所有Sheet的空行
def remove_empty_rows(df):
    return df.dropna(how='all')

process_multiple_sheets('data.xlsx', remove_empty_rows)

---

技巧6：数据验证下拉列表

场景：某列只能从预设选项中选择（如部门列表）。

from openpyxl import load_workbook
from openpyxl.worksheet.datavalidation import DataValidation

def add_dropdown_list(file_path, column_letter, options_list):
    """
    添加下拉列表验证
    
    Args:
        file_path: Excel文件路径
        column_letter: 列字母
        options_list: 选项列表
    """
    wb = load_workbook(file_path)
    ws = wb.active
    
    # 创建数据验证
    dv = DataValidation(
        type="list",
        formula1=f'"{",".join(options_list)}"',
        allow_blank=False
    )
    
    # 应用到整列
    dv.add(f'{column_letter}2:{column_letter}1048576')
    ws.add_data_validation(dv)
    
    wb.save(file_path)
    print(f"下拉列表已添加到列{column_letter}")

# 使用：部门列只能选择指定部门
add_dropdown_list(
    'employee.xlsx',
    'C',
    ['技术部', '销售部', '人事部', '财务部']
)

---

技巧7：自动生成图表

场景：数据可视化，生成折线图、柱状图。

import pandas as pd
import matplotlib.pyplot as plt
from openpyxl import load_workbook
from openpyxl.chart import LineChart, Reference

def create_line_chart(file_path, min_col, max_col, title="趋势图"):
    """
    在Excel中创建折线图
    
    Args:
        file_path: Excel文件路径
        min_col: 起始列数字
        max_col: 结束列数字
        title: 图表标题
    """
    wb = load_workbook(file_path)
    ws = wb.active
    
    # 创建折线图
    chart = LineChart()
    chart.title = title
    chart.style = 10
    chart.y_axis.title = '数值'
    chart.x_axis.title = '日期'
    
    # 数据范围
    data = Reference(ws, min_col=min_col, max_col=max_col, min_row=1, max_row=100)
    cats = Reference(ws, min_col=1, min_row=2, max_row=100)
    
    chart.add_data(data, titles_from_data=True)
    chart.set_categories(cats)
    
    # 添加图表
    ws.add_chart(chart, "A10")
    
    wb.save(file_path)
    print(f"图表已添加到{file_path}")

# 使用：为B-D列数据创建折线图
create_line_chart('data.xlsx', 2, 4, '销售趋势')

---

技巧8：公式自动填充

场景：需要为大量行应用同一公式。

from openpyxl import load_workbook

def fill_formula(file_path, column_letter, formula, start_row=2, end_row=1000):
    """
    批量填充公式
    
    Args:
        file_path: Excel文件路径
        column_letter: 列字母
        formula: 公式（如'=A2+B2'）
        start_row: 起始行
        end_row: 结束行
    """
    wb = load_workbook(file_path)
    ws = wb.active
    
    # 填充公式
    for row in range(start_row, end_row + 1):
        # 调整行号
        row_formula = formula.replace('2', str(row))
        ws[f'{column_letter}{row}'] = row_formula
    
    wb.save(file_path)
    print(f"公式已填充到{column_letter}列{start_row}-{end_row}行")

# 使用：C列填充公式=A2+B2
fill_formula('data.xlsx', 'C', '=A2+B2', 2, 1000)

---

技巧9：多个Excel文件对比

场景：对比两个版本Excel的差异。

def compare_excel_files(file1, file2, output_file='diff.xlsx'):
    """
    对比两个Excel文件的差异
    
    Args:
        file1: 文件1路径
        file2: 文件2路径
        output_file: 差异输出文件
    """
    df1 = pd.read_excel(file1)
    df2 = pd.read_excel(file2)
    
    # 找出差异行
    diff_rows = []
    for i in range(min(len(df1), len(df2))):
        if not df1.iloc[i].equals(df2.iloc[i]):
            diff_rows.append({
                '行号': i + 2,
                '文件1值': str(df1.iloc[i].to_dict()),
                '文件2值': str(df2.iloc[i].to_dict())
            })
    
    # 保存差异
    diff_df = pd.DataFrame(diff_rows)
    diff_df.to_excel(output_file, index=False)
    
    print(f"对比完成，发现{len(diff_rows)}处差异，已保存到{output_file}")
    return diff_df

# 使用
compare_excel_files('data_v1.xlsx', 'data_v2.xlsx')

---

技巧10：定时自动处理Excel

场景：每天自动处理Excel并发送邮件。

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

def process_and_send_email():
    """
    每天定时处理Excel并发送邮件
    """
    print(f"开始处理：{datetime.now()}")
    
    # 1. 处理Excel
    merge_excel_files('./daily_data', f'report_{datetime.now().strftime("%Y%m%d")}.xlsx')
    
    # 2. 发送邮件
    send_email_with_excel(
        subject=f"每日数据报告-{datetime.now().strftime('%Y-%m-%d')}",
        body="请查收附件中的数据报告",
        file_path=f'report_{datetime.now().strftime("%Y%m%d")}.xlsx'
    )
    
    print("处理完成！")

def send_email_with_excel(subject, body, file_path):
    """
    发送带Excel附件的邮件
    """
    # 邮件配置（替换为你的）
    smtp_server = "smtp.gmail.com"
    smtp_port = 587
    sender_email = "your_email@gmail.com"
    sender_password = "your_password"
    receiver_email = "receiver@example.com"
    
    # 创建邮件
    msg = MIMEMultipart()
    msg['From'] = sender_email
    msg['To'] = receiver_email
    msg['Subject'] = subject
    
    # 添加正文
    msg.attach(MIMEText(body, 'plain'))
    
    # 添加附件
    with open(file_path, 'rb') as attachment:
        part = MIMEBase('application', 'octet-stream')
        part.set_payload(attachment.read())
        encoders.encode_base64(part)
        part.add_header(
            'Content-Disposition',
            f'attachment; filename= {file_path}'
        )
        msg.attach(part)
    
    # 发送
    server = smtplib.SMTP(smtp_server, smtp_port)
    server.starttls()
    server.login(sender_email, sender_password)
    server.send_message(msg)
    server.quit()

# 每天上午9点执行
schedule.every().day.at("09:00").do(process_and_send_email)

while True:
    schedule.run_pending()
    time.sleep(60)

---

总结

以上就是10个最实用的Python Excel自动化技巧！

核心工具：

• pandas：数据处理
• openpyxl：Excel操作
• schedule：定时任务

学习建议：

1. 先掌握技巧1-3（最常用）
2. 根据实际需求学习其他技巧
3. 结合自己的工作场景优化代码

效率提升：

• 手动处理：2小时/天
• Python自动化：5分钟/天
• 节省时间：95%！

---

后续学习

想深入学习Python办公自动化？推荐：

1. Pandas官方文档：pandas.pydata.org/docs/
2. OpenPyXL教程：openpyxl.readthedocs.io/
3. Python自动化实战课程：关注我的CSDN博客

下一篇预告：《Pandas数据分析实战：从0到1掌握数据处理》

---

如果这篇文章对你有帮助，请点赞、收藏、转发！
有问题欢迎评论区讨论！ 💪

---

关注我获取更多Python实战技巧！