当前位置：首页 > news >正文

Python 自动化办公：Excel 数据处理的“秘密武器”

news 来源：原创 2025/6/10 0:43:33

引言

在日常的 IT 办公场景里，Excel 是数据处理与分析的 “常胜将军”。无论是财务人员整理账目、销售团队统计业绩，还是运营人员分析用户数据，Excel 都发挥着关键作用。但面对海量数据，手动操作 Excel 不仅效率低下，还容易出错。Python 凭借其强大的库支持，能轻松实现 Excel 自动化处理，让繁琐的工作变得简单高效。本文将结合实际案例，带大家走进 Python 自动化操作 Excel 的世界，掌握实用的代码技巧。

1 常用 Python 库介绍

1.1 openpyxl

openpyxl 是一个用于读写 Excel 2010 xlsx/xlsm/xltx/xltm 文件的 Python 库。它功能丰富，支持对单元格的读写、格式设置、图表操作等，适合处理较为复杂的Excel文件。

1.2 pandas

pandas 是数据分析领域的明星库，它提供了高效的数据结构和数据分析工具。虽然它本身不直接操作 Excel 文件格式，但通过 to_excel() 和 read_excel() 方法，可以方便地与 Excel 进行数据交互，常用于数据的快速读写和初步处理。

1.3 xlrd/xlwt（较老但仍有使用场景）

xlrd 用于读取 Excel 文件（支持 .xls 格式），xlwt 用于写入 Excel 文件（同样支持 .xls 格式）。由于它们对 .xlsx 格式支持有限，在新项目中使用频率逐渐降低，但在处理老旧格式的 Excel 文件时仍有一定价值。

2 实战案例：销售数据自动化处理

2.1 案例背景

假设我们有一份某公司某月的销售数据 Excel 文件（sales_data.xlsx），包含销售日期、产品名称、销售数量、销售单价等信息。我们的任务是自动化完成以下工作：

读取 Excel 文件中的数据。
计算每笔订单的销售金额（销售数量×销售单价）。
统计每种产品的总销售金额。
将处理后的数据保存到新的 Excel 文件中，并对总销售金额进行降序排序。

2.2 代码实现

1. 准备工作

首先，确保已安装所需库，可通过以下命令安装：

pip install openpyxl pandas

2. 完整代码

import pandas as pd
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, Alignmentdef process_sales_data():# 1. 读取Excel文件input_file = 'sales_data.xlsx'df = pd.read_excel(input_file)# 2. 计算每笔订单的销售金额df['销售金额'] = df['销售数量'] * df['销售单价']# 3. 统计每种产品的总销售金额product_sales = df.groupby('产品名称')['销售金额'].sum().reset_index()product_sales = product_sales.sort_values(by='销售金额', ascending=False)# 4. 将处理后的数据保存到新的Excel文件output_file = 'processed_sales_data.xlsx'with pd.ExcelWriter(output_file, engine='openpyxl') as writer:# 写入原始数据（带计算的销售金额）df.to_excel(writer, sheet_name='原始数据', index=False)# 写入产品总销售金额统计结果product_sales.to_excel(writer, sheet_name='产品统计', index=False)# 5. 使用openpyxl对输出的Excel文件进行格式美化（可选）wb = load_workbook(output_file)# 美化“原始数据”工作表ws_raw = wb['原始数据']# 设置标题行字体加粗、居中for cell in ws_raw[1]:cell.font = Font(bold=True)cell.alignment = Alignment(horizontal='center')# 美化“产品统计”工作表ws_stats = wb['产品统计']# 设置标题行字体加粗、居中for cell in ws_stats[1]:cell.font = Font(bold=True)cell.alignment = Alignment(horizontal='center')# 为总销售金额列添加货币格式（示例，实际可能需要根据Excel版本调整）for row in range(2, ws_stats.max_row + 1):ws_stats.cell(row=row, column=2).number_format = '"¥"#,##0.00'# 保存美化后的文件wb.save(output_file)print(f"数据处理完成，结果已保存到 {output_file}")if __name__ == '__main__':process_sales_data()

代码解析：

数据读取：使用 pandas.read_excel() 函数读取 sales_data.xlsx 文件中的数据到 DataFrame 对象 df 中。
计算销售金额：通过 DataFrame 的列运算，直接在 df 中新增一列“销售金额”，其值为“销售数量”与“销售单价”的乘积。
统计产品总销售金额：利用 groupby() 方法按“产品名称”分组，然后对“销售金额”列求和，并使用 sort_values() 方法按总销售金额降序排序。
数据写入：使用 pandas.ExcelWriter 将处理后的数据分别写入新 Excel 文件的不同工作表中。
格式美化：使用 openpyxl 加载生成的 Excel 文件，对工作表进行格式设置，如设置标题行字体加粗、居中，为金额列添加货币格式等，使输出结果更加美观易读。

3 扩展应用场景

3.1 批量处理多个 Excel 文件

假设公司有多个销售分公司的 Excel 数据文件，文件命名格式为分公司名称 _sales_data.xlsx，我们可以编写代码批量处理这些文件，并将结果合并到一个 Excel 文件中。

import os
import pandas as pddef batch_process_sales_files():input_folder = 'sales_files'  # 假设所有分公司销售数据文件都在此文件夹中output_file = 'all_companies_sales_summary.xlsx'all_data = []# 遍历文件夹中的所有Excel文件for filename in os.listdir(input_folder):if filename.endswith('_sales_data.xlsx'):company_name = filename.split('_')[0]  # 提取分公司名称file_path = os.path.join(input_folder, filename)df = pd.read_excel(file_path)df['分公司'] = company_name  # 添加分公司列all_data.append(df)# 合并所有数据combined_df = pd.concat(all_data, ignore_index=True)# 计算每笔订单销售金额combined_df['销售金额'] = combined_df['销售数量'] * combined_df['销售单价']# 统计各分公司各产品总销售金额company_product_sales = combined_df.groupby(['分公司', '产品名称'])['销售金额'].sum().reset_index()company_product_sales = company_product_sales.sort_values(by=['分公司', '销售金额'], ascending=[True, False])# 保存结果with pd.ExcelWriter(output_file, engine='openpyxl') as writer:combined_df.to_excel(writer, sheet_name='原始合并数据', index=False)company_product_sales.to_excel(writer, sheet_name='分公司产品统计', index=False)print(f"批量处理完成，结果已保存到 {output_file}")if __name__ == '__main__':batch_process_sales_files()

3.2 定时自动生成报表

在办公场景里，日报、周报、月报的生成与分发如同每日打卡，是不可或缺的工作环节。但手动重复操作不仅耗时，还容易因忙碌而遗漏。借助操作系统的定时任务功能与 Python 脚本，我们能让报表在指定时间自动“出炉”，并精准送达相关人员手中，大幅提升办公效率。

3.2.1 定时任务工具选择

不同操作系统提供了各具特色的定时任务工具，能无缝衔接 Python 脚本，实现自动化报表生成。

Windows：“任务计划程序”是 Windows 系统的内置利器，通过直观的图形界面，可轻松设置脚本的定时运行。
Linux/Mac：cron 服务以简洁高效的命令行方式，灵活管理定时任务，满足多样化的定时需求。

3.2.2 典型应用场景：销售日报自动生成与发送

假设公司要求每天早上 9 点，将前一天的销售数据汇总成日报，并发送给销售团队和管理层。下面介绍如何借助 Python 脚本与定时任务工具实现这一需求。

import pandas as pd
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.application import MIMEApplication
from datetime import datetime, timedeltadef generate_and_send_daily_report():# 1. 获取前一天日期yesterday = (datetime.now() - timedelta(days=1)).strftime('%Y-%m-%d')# 2. 模拟生成销售数据（实际项目中可连接数据库获取）data = {'产品名称': ['产品A', '产品B', '产品C'],'销售数量': [120, 85, 95],'销售单价': [50, 80, 60]}df = pd.DataFrame(data)df['销售金额'] = df['销售数量'] * df['销售单价']# 3. 计算总销售额total_sales = df['销售金额'].sum()# 4. 保存报表到Excelreport_filename = f'daily_sales_report_{yesterday}.xlsx'with pd.ExcelWriter(report_filename, engine='openpyxl') as writer:df.to_excel(writer, sheet_name='销售明细', index=False)# 添加总销售额到新工作表summary_df = pd.DataFrame({'统计项': ['总销售额'], '金额': [total_sales]})summary_df.to_excel(writer, sheet_name='统计汇总', index=False)# 5. 发送邮件send_email(report_filename, yesterday)def send_email(file_path, report_date):# 邮件配置sender = 'your_email@example.com'  # 发送者邮箱password = 'your_password_or_app_token'  # 邮箱密码或应用专用密码/授权码receiver = 'sales_team@example.com'  # 接收者邮箱smtp_server = 'smtp.example.com'  # SMTP服务器地址（如Gmail为smtp.gmail.com）smtp_port = 587  # SMTP服务器端口# 创建邮件对象msg = MIMEMultipart()msg['From'] = sendermsg['To'] = receivermsg['Subject'] = f'{report_date}销售日报'# 邮件正文body = f"""<h2>各位同事：</h2><p>以下是{report_date}的销售日报，请查收。</p><p>附件包含详细的销售明细和统计汇总信息。</p><br><p>销售部</p><p>{datetime.now().strftime('%Y-%m-%d')}</p>"""msg.attach(MIMEText(body, 'html'))# 添加附件with open(file_path, 'rb') as f:part = MIMEApplication(f.read())part.add_header('Content-Disposition', 'attachment', filename=file_path)msg.attach(part)# 发送邮件try:with smtplib.SMTP(smtp_server, smtp_port) as server:server.starttls()  # 启用加密连接server.login(sender, password)server.send_message(msg)print(f"邮件发送成功，报表已发送至 {receiver}")except Exception as e:print(f"邮件发送失败: {e}")if __name__ == '__main__':generate_and_send_daily_report()

脚本功能解析：

数据生成：模拟生成前一天的销售数据，包括产品名称、销售数量、销售单价，并计算销售金额和总销售额。
报表生成：使用 pandas 将数据保存到 Excel 文件中，创建“销售明细”和“统计汇总”两个工作表，分别展示详细销售数据和总销售额。
邮件发送：通过 smtplib 和 email 库构建并发送邮件，将生成的 Excel 文件作为附件发送给指定人员。

3.2.3 定时任务设置

Windows 系统：

打开任务计划程序：按下 Win + R 键，输入 taskschd.msc 并回车。
创建基本任务：在右侧操作面板中点击“创建基本任务”。
填写基本信息：输入任务名称（如“每日销售日报发送”）和描述。
设置触发器：选择“每天”，设置开始时间为 9:00，并选择“每天”重复执行。
设置操作：选择“启动程序”，浏览并选择 Python 解释器路径（如 C:\Python39\python.exe）和脚本路径（如 C:\scripts\generate_and_send_daily_report.py）。
完成设置：点击“完成”按钮，任务计划程序将每天 9 点自动运行脚本。

Linux/Mac 系统

编辑 crontab 文件：在终端输入 crontab -e 命令。

添加定时任务：在打开的编辑器中添加以下行，设置每天 9 点运行脚本：

0 9 * * * /usr/bin/python3 /path/to/generate_and_send_daily_report.py

0 9 * * *：表示每天 9:00 执行任务。
/usr/bin/python3：Python 解释器的路径，可通过 which python3 命令查找。
/path/to/generate_and_send_daily_report.py：Python 脚本的绝对路径。

保存并退出：保存文件后，cron 服务将自动加载新的定时任务。

3.2.4 注意事项

邮箱安全：为保障邮箱安全，建议使用应用专用密码或授权码进行 SMTP 登录，而非直接使用邮箱密码。
路径问题：在脚本中使用绝对路径引用文件，避免因工作目录不同而导致文件找不到。
日志记录：在脚本中添加日志记录功能，将脚本的运行情况和错误信息记录到日志文件中，方便排查问题。例如，使用 logging 模块记录日志：

import logging# 配置日志记录
logging.basicConfig(filename='report_generation.log', level=logging.INFO,format='%(asctime)s - %(levelname)s - %(message)s')def generate_and_send_daily_report():try:logging.info("开始生成销售日报")# 原有代码...logging.info("销售日报生成并发送成功")except Exception as e:logging.error(f"生成销售日报时出错: {e}")

通过以上方法，我们可以轻松实现销售日报的定时自动生成与发送，让办公自动化为我们的工作减负增效。同样的思路也可应用于周报、月报等其他报表的定时生成与分发。

引言