Excel自动化办公常用的免费Python代码模板有哪些

沈知远
沈知远
发布时间:2026-11-17| 6人浏览过
原创

本文梳理Excel日常自动化办公中最常用的免费Python代码模板,涵盖多表批量合并、总表按列分类拆分、单元格样式格式化与批量数据清洗,提供即拷即用的完整可运行脚本与参数说明。

日常办公中,大部分关于表格的重复性劳动都可以归纳为固定流程:要么是汇总分散在各部门的数十个报表,要么是按地区或销售员把总表拆分为独立文件,抑或是批量清洗杂乱格式并统一美化输出。借助完全免费开源的 pandas 与 openpyxl 库,无需安装微软商业套件或支付授权费用,即可用几十行 Python 代码搭建起高复用性的自动化管线。本文整理了办公中最核心的四套标准代码模板,并解析各模板适用的业务边界与关键配置。

Excel自动化办公常用的免费Python代码模板有哪些
图 1:开源 Python Excel 自动化技术栈分工机制

准备工作:基础环境依赖

运行本文提供的代码前,需要安装 pandas 和 openpyxl。这两个包均遵循 BSD/MIT 宽松开源协议,支持在个人电脑或企业服务器上免费使用:

pip install pandas openpyxl

代码中使用的文件路径均基于 Python 内置的标准库 pathlib.Path,可跨 Windows 与 macOS/Linux 稳定执行,防止 Windows 下反斜杠转义引起的路径读取错误。

模板一:多工作簿及多工作表批量合并汇总

这是职场中最耗时的环节之一。例如各城市分公司每周上报同构的销售数据,人工逐个打开、复制粘贴容易遗漏且极易错行。该模板遍历指定文件夹下所有的 .xlsx 文件,读取每一个工作表并统一追加合并为单一大表,同时记录“来源文件”和“工作表名称”,便于追溯数据来源。

Excel自动化办公常用的免费Python代码模板有哪些
图 2:多工作簿及工作表合并流水线
from pathlib import Path
import pandas as pd

def batch_merge_excel(folder_path: str, output_file: str):
    """
    遍历指定目录中的所有 Excel 文件(.xlsx),提取所有工作表并合并为一个总表
    """
    source_dir = Path(folder_path)
    # 获取所有的 .xlsx 文件,排除 Excel 打开时产生的以 ~$ 开头的临时缓存锁文件
    excel_files = [f for f in source_dir.glob("*.xlsx") if not f.name.startswith("~$")]
    
    if not excel_files:
        print(f"在目录 {folder_path} 中未找到符合条件的 Excel 文件。")
        return

    frames = []
    for file_path in excel_files:
        # sheet_name=None 会一次性读取全部 Sheet,返回字典结构 {sheet_name: DataFrame}
        sheets_dict = pd.read_excel(file_path, sheet_name=None)
        for sheet_name, df in sheets_dict.items():
            if df.empty:
                continue
            # 记录数据来源,排查原始数据错误时非常有效
            df["_来源文件"] = file_path.name
            df["_工作表名"] = sheet_name
            frames.append(df)

    if frames:
        # ignore_index=True 重新从 0 编排整行索引
        merged_df = pd.concat(frames, ignore_index=True)
        # 输出保存,不保留 pandas 自带的默认行序号索引
        merged_df.to_excel(output_file, index=False)
        print(f"成功合并 {len(excel_files)} 个文件,累计生成 {len(merged_df)} 行有效记录,已存入: {output_file}")
    else:
        print("未提取到任何有效数据行。")

# 示例调用
if __name__ == "__main__":
    batch_merge_excel("./待汇总报表", "./汇总结果/全量数据汇总表.xlsx")
注意事项:使用 pd.concat 时,各子表的列名需要保持完全一致(包括中英文字符与空格)。如果某个表格的列名多了空格(如“姓名 ”与“姓名”),pandas 会将其拆分为两个独立列,因此在执行合并前若发现列异常分裂,需先用 df.columns = df.columns.str.strip() 剔除前后空白。

模板二:按特定列分类拆分总表为独立文件

另一个高频需求是分发报表。财务或运营人员往往拥有一张全公司、全季度的大总表,但在分发时必须按“部门”、“销售区域”或“负责人”拆分成独立表格,避免敏感信息跨区域泄露。借助 DataFrame.groupby 可以实现瞬时切分,配合 re.sub 清理非法文件名字符,确保在所有操作系统下都能平稳生成文件。

Excel自动化办公常用的免费Python代码模板有哪些
图 3:基于核心维度的表格分组自动切分机制
import re
from pathlib import Path
import pandas as pd

def sanitize_filename(filename: str) -> str:
    """清理在 Windows/Linux 文件命名中不允许的非法字符,如 / \ : * ? " < > |"""
    return re.sub(r'[\\/*?:"<>|]', "_", str(filename)).strip()

def split_excel_by_column(input_file: str, split_column: str, output_dir: str):
    """
    按指定列的唯一取值,把主表格切分导出为多个独立的 .xlsx 文件
    """
    df = pd.read_excel(input_file)
    target_path = Path(output_dir)
    target_path.mkdir(parents=True, exist_ok=True)
    
    if split_column not in df.columns:
        raise ValueError(f"指定的拆分字段 '{split_column}' 不在表格中,当前列包含: {list(df.columns)}")

    # 滤掉拆分列为空值的无效行,避免生成无名文件
    valid_df = df.dropna(subset=[split_column])
    
    # 按照分类列分组并循环导出
    for group_val, group_data in valid_df.groupby(split_column):
        safe_name = sanitize_filename(group_val)
        out_file_path = target_path / f"{safe_name}.xlsx"
        group_data.to_excel(out_file_path, index=False)
        print(f"已生成分组表格: {out_file_path.name} (共 {len(group_data)} 行)")

# 示例调用
if __name__ == "__main__":
    split_excel_by_column(
        input_file="./数据底表/2026年度销售总表.xlsx",
        split_column="所属分部",
        output_dir="./拆分后分发表格"
    )

模板三:单元格样式、列宽与表头规范美化

由 pandas 默认导出的 Excel 文件通常为无边框、无格式高亮、列宽固定的纯白背景,中文或长字符经常被挤压显示为 ###。借助 openpyxl 的样式控制对象,可以对已生成的文件进行批量快速润色:冻结首行、添加品牌表头底色、细黑单元格边框以及依照内容长度自动拉伸列宽。

from pathlib import Path
import openpyxl
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
from openpyxl.utils import get_column_letter

def beautify_excel_report(file_path: str, sheet_name: str = None):
    """
    对指定的 Excel 工作表套用统一的商务排版规范:
    1. 表头深色填充、白字加粗、居中对齐
    2. 全局细实线边框
    3. 自适应计算并拉宽列距,消除字符截断
    4. 冻结顶端表头行
    """
    wb = openpyxl.load_workbook(file_path)
    ws = wb[sheet_name] if sheet_name else wb.active

    # 定义统一的样式规范
    header_fill = PatternFill(start_color="4F6272", end_color="4F6272", fill_type="solid")
    header_font = Font(name="Microsoft YaHei", size=11, bold=True, color="FFFFFF")
    data_font = Font(name="Microsoft YaHei", size=10, bold=False, color="24292F")
    
    center_align = Alignment(horizontal="center", vertical="center", wrap_text=False)
    left_align = Alignment(horizontal="left", vertical="center", wrap_text=False)
    
    thin_border_side = Side(border_style="thin", color="D0D7DE")
    cell_border = Border(left=thin_border_side, right=thin_border_side, 
                         top=thin_border_side, bottom=thin_border_side)

    # 1. 设置表头行高与样式(第 1 行)
    ws.row_dimensions[1].height = 28
    for cell in ws[1]:
        cell.fill = header_fill
        cell.font = header_font
        cell.alignment = center_align
        cell.border = cell_border

    # 2. 遍历数据单元格应用边框与常规字体
    for row in ws.iter_rows(min_row=2):
        ws.row_dimensions[row[0].row].height = 20
        for cell in row:
            cell.font = data_font
            cell.border = cell_border
            if cell.data_type in ("n", "d"):  # 数字或日期
                cell.alignment = center_align
            else:
                cell.alignment = left_align

    # 3. 自适应动态列宽(中文字符按 2 字符位加权计算)
    for col in ws.columns:
        max_len = 0
        for cell in col:
            val_str = str(cell.value or "")
            # 统计字符显示宽度(ASCII 码大于 127 记为 2)
            char_width = sum(2 if ord(c) > 127 else 1 for c in val_str)
            if char_width > max_len:
                max_len = char_width
        col_letter = get_column_letter(col[0].column)
        # 加上 3 个字符的边距,设定最小宽度 10,最大宽度 50
        ws.column_dimensions[col_letter].width = min(max(max_len + 3, 10), 50)

    # 4. 冻结首行
    ws.freeze_panes = "A2"

    wb.save(file_path)
    print(f"表格格式优化完毕: {file_path}")

# 示例调用
if __name__ == "__main__":
    beautify_excel_report("./汇总结果/全量数据汇总表.xlsx")

模板四:脏数据清洗、去重与类型规整

来自在线问卷、手工录入或第三方业务系统的导出数据通常存在格式不齐的问题,例如:文本字段两端带有不可见的空格换行、日期写成混合格式(2026/01/01 或 2026-1-1)、数值列夹杂逗号或空值。如果在汇总前未作规整,后续的求和函数与透视分析极易得出错误结果。

import pandas as pd

def clean_excel_data(input_file: str, output_file: str):
    """
    标准化清洗常见表格缺陷:
    1. 去除文本型字段首尾空格及制表符
    2. 统一多变日期格式为标准的 ISO YYYY-MM-DD
    3. 数值列去除货币符号与千分位逗号,空缺填充为 0
    4. 剔除完全重复的数据记录
    """
    df = pd.read_excel(input_file)
    initial_rows = len(df)

    # 1. 批量清理所有 object/文本型列的前后多余空白
    text_cols = df.select_dtypes(include=["object"]).columns
    for col in text_cols:
        df[col] = df[col].astype(str).str.strip().replace("nan", "")

    # 2. 规范化处理日期列(若列名中带有“日期”或“时间”)
    for col in df.columns:
        if "日期" in col or "时间" in col:
            # errors='coerce' 会把无法识别的非法日期自动置为 NaT,避免直接闪退中断
            df[col] = pd.to_datetime(df[col], errors="coerce").dt.strftime("%Y-%m-%d")

    # 3. 清理金额/数值中的千分符与货币单位
    for col in df.columns:
        if any(keyword in col for keyword in ["金额", "价格", "单价", "销售额"]):
            # 移除常见的货币符号与千分位逗号
            df[col] = (
                df[col].astype(str)
                .str.replace("¥", "", regex=False)
                .str.replace("$", "", regex=False)
                .str.replace(",", "", regex=False)
                .str.strip()
            )
            # 转为浮点数,非法值转为 0.0
            df[col] = pd.to_numeric(df[col], errors="coerce").fillna(0.0)

    # 4. 全局行去重,只保留首条出现的记录
    df = df.drop_duplicates()
    
    df.to_excel(output_file, index=False)
    print(f"清洗完成!原始数据 {initial_rows} 行,清洗后保留 {len(df)} 行(剔除重复 {initial_rows - len(df)} 行)")

# 示例调用
if __name__ == "__main__":
    clean_excel_data("./待清洗数据/原始采集名单.xlsx", "./清洗归档/清洗合格名单.xlsx")

四套代码模板的核心特性与场景选型速查

在构建日常自动化工作流时,了解不同模板的技术依赖和开销边界能够大幅缩减排错耗时:

Excel自动化办公常用的免费Python代码模板有哪些
图 4:四大常用自动化模板指标综合评估表

注意事项与避坑指南

  • 原表格中 Excel 公式计算结果的处理:默认情况下,如果原表包含 =SUM(...) 等公式,使用 pandas.read_excel 会读取公式当前的计算缓存值;如果文件是由第三方非微软软件生成的,缓存值可能为空。若使用 openpyxl.load_workbook(file, data_only=True),它同样读取缓存值;若设为 data_only=False 则会读取到公式字符串本身。
  • 大文件内存管控:若合并数十个单个体积达数百兆的表格,容易遭遇内存不足。可将 pd.read_excel(file, usecols=[...]) 设置为只读入需要的列,减少内存分配。
  • 多进程与文件锁:在 Windows 系统中,如果用 Excel 软件打开了目标文件,Python 在写入时会报出 PermissionError: [Errno 13] Permission denied。运行自动化脚本前,务必先关闭正在预览的目标 .xlsx 文件。

免责声明:本文内容仅供参考,不代表本站观点。转载或引用请注明出处。

Python 相关产品和应用

全部下载
Selenium

Selenium

4.51.0 · Windows

用于浏览器自动化和 Web 测试的 Python 工具。

scikit-learn

scikit-learn

1.9.1 · Windows

常用 Python 机器学习算法与模型评估工具库。

Black

Black

26.10.1 · Windows

强调一致性的 Python 自动代码格式化工具。

Ruff

Ruff

0.17.1 · macOS

由 Rust 编写的高速 Python 代码检查和格式化工具。

pytest

pytest

9.1.1 · Windows

简洁而强大的 Python 自动化测试框架。

FastAPI

FastAPI

0.146.0 · Windows

高性能 Python API 框架,支持类型提示和自动接口文档。

Flask

Flask

3.1.3 · Windows

轻量灵活的 Python Web 应用框架。

Django

Django

6.1.2 · Windows

成熟的 Python Web 框架,适合构建内容站点和业务系统。

Spyder

Spyder

6.1.7 · Windows

面向科学计算与数据分析的 Python 集成开发环境。

SciPy

SciPy

1.18.1 · Windows

面向科学、工程和技术计算的 Python 算法库。