Excel自动化办公常用的免费Python代码模板有哪些
本文梳理Excel日常自动化办公中最常用的免费Python代码模板,涵盖多表批量合并、总表按列分类拆分、单元格样式格式化与批量数据清洗,提供即拷即用的完整可运行脚本与参数说明。
日常办公中,大部分关于表格的重复性劳动都可以归纳为固定流程:要么是汇总分散在各部门的数十个报表,要么是按地区或销售员把总表拆分为独立文件,抑或是批量清洗杂乱格式并统一美化输出。借助完全免费开源的 pandas 与 openpyxl 库,无需安装微软商业套件或支付授权费用,即可用几十行 Python 代码搭建起高复用性的自动化管线。本文整理了办公中最核心的四套标准代码模板,并解析各模板适用的业务边界与关键配置。
准备工作:基础环境依赖
运行本文提供的代码前,需要安装 pandas 和 openpyxl。这两个包均遵循 BSD/MIT 宽松开源协议,支持在个人电脑或企业服务器上免费使用:
pip install pandas openpyxl
代码中使用的文件路径均基于 Python 内置的标准库 pathlib.Path,可跨 Windows 与 macOS/Linux 稳定执行,防止 Windows 下反斜杠转义引起的路径读取错误。
模板一:多工作簿及多工作表批量合并汇总
这是职场中最耗时的环节之一。例如各城市分公司每周上报同构的销售数据,人工逐个打开、复制粘贴容易遗漏且极易错行。该模板遍历指定文件夹下所有的 .xlsx 文件,读取每一个工作表并统一追加合并为单一大表,同时记录“来源文件”和“工作表名称”,便于追溯数据来源。
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 清理非法文件名字符,确保在所有操作系统下都能平稳生成文件。
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 公式计算结果的处理:默认情况下,如果原表包含
=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文件。
免责声明:本文内容仅供参考,不代表本站观点。转载或引用请注明出处。









