Python 自動化辦公：Excel 數據處理的“秘密武器”

引言

????????在日常的 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}")

????????通過以上方法，我們可以輕松實現銷售日報的定時自動生成與發送，讓辦公自動化為我們的工作減負增效。同樣的思路也可應用于周報、月報等其他報表的定時生成與分發。