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

nizainaliya111

429人浏览 · 2026-03-04 16:31:14

nizainaliya111 · 2026-03-04 16:31:14 发布

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-3（最常用）
根据实际需求学习其他技巧
结合自己的工作场景优化代码

效率提升：

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

后续学习

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

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

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

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

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

欢迎使用Markdown编辑器

你好！这是你第一次使用 Markdown编辑器 所展示的欢迎页。如果你想学习如何使用Markdown编辑器, 可以仔细阅读这篇文章，了解一下Markdown的基本语法知识。

新的改变

我们对Markdown编辑器进行了一些功能拓展与语法支持，除了标准的Markdown编辑器功能，我们增加了如下几点新功能，帮助你用它写博客：

全新的界面设计 ，将会带来全新的写作体验；
在创作中心设置你喜爱的代码高亮样式，Markdown 将代码片显示选择的高亮样式 进行展示；
增加了 图片拖拽 功能，你可以将本地的图片直接拖拽到编辑区域直接展示；
全新的 KaTeX数学公式 语法；
增加了支持甘特图的mermaid语法¹ 功能；
增加了 多屏幕编辑 Markdown文章功能；
增加了 焦点写作模式、预览模式、简洁写作模式、左右区域同步滚轮设置 等功能，功能按钮位于编辑区域与预览区域中间；
增加了 检查列表 功能。

功能快捷键

撤销：Ctrl/Command + Z
重做：Ctrl/Command + Y
加粗：Ctrl/Command + B
斜体：Ctrl/Command + I
标题：Ctrl/Command + Shift + H
无序列表：Ctrl/Command + Shift + U
有序列表：Ctrl/Command + Shift + O
检查列表：Ctrl/Command + Shift + C
插入代码：Ctrl/Command + Shift + K
插入链接：Ctrl/Command + Shift + L
插入图片：Ctrl/Command + Shift + G
查找：Ctrl/Command + F
替换：Ctrl/Command + G

合理的创建标题，有助于目录的生成

直接输入1次#，并按下space后，将生成1级标题。
输入2次#，并按下space后，将生成2级标题。
以此类推，我们支持6级标题。有助于使用TOC语法后生成一个完美的目录。

如何改变文本的样式

强调文本 强调文本

加粗文本 加粗文本

标记文本

~~删除文本~~

引用文本

H₂O is是液体。

2¹⁰ 运算结果是 1024.

插入链接与图片

链接: link.

图片: Alt

带尺寸的图片:

居中的图片: Alt

居中并且带尺寸的图片:

当然，我们为了让用户更加便捷，我们增加了图片拖拽功能。

如何插入一段漂亮的代码片

去博客设置页面，选择一款你喜欢的代码片高亮样式，下面展示同样高亮的 代码片.

// An highlighted block
var foo = 'bar';

生成一个适合你的列表

项目
- 项目
  - 项目

项目1
项目2
项目3

计划任务
完成任务

创建一个表格

一个简单的表格是这么创建的：

项目	Value
电脑	$1600
手机	$12
导管	$1

设定内容居中、居左、居右

使用:---------:居中
使用:----------居左
使用----------:居右

第一列	第二列	第三列
第一列文本居中	第二列文本居右	第三列文本居左

SmartyPants

SmartyPants 是一个文本转换工具，主要功能是将普通的 ASCII 标点符号自动转换为更美观的印刷体标点符号。例如：

原始符号	转换后	说明
`"引号"`	“引号”	直引号变弯引号
`'单引号'`	‘单引号’	直单引号变弯单引号
`--`	–	两个连字符变短破折号
`---`	—	三个连字符变长破折号
`...`	…	三个点变省略号