Python數據處理之Excel報表自動化生成與分析
更新時間：2025年07月17日 09:58:41 作者：nightunderblackcat
這篇文章主要為大家詳細介紹了如何使用Python實現(xiàn)一個完整的Excel報表自動化系統(tǒng),涵蓋從數據清洗、分析到可視化報表生成的全流程,希望對大家有所幫助
本文將詳細介紹如何使用Python實現(xiàn)一個完整的Excel報表自動化系統(tǒng)，涵蓋從數據清洗、分析到可視化報表生成的全流程。本教程面向Python初學者，通過實際案例講解pandas和openpyxl庫的核心用法。
系統(tǒng)概述
Excel報表自動化系統(tǒng)能夠：
- 自動讀取原始數據文件（如CSV、Excel等）
- 進行數據清洗和預處理
- 執(zhí)行數據分析計算
- 生成格式化的Excel報表
- 創(chuàng)建可視化圖表
環(huán)境準備
安裝必要庫
pip install pandas openpyxl matplotlib
導入庫
import pandas as pd from openpyxl import Workbook from openpyxl.chart import BarChart, Reference import matplotlib.pyplot as plt
數據清洗階段
1. 讀取原始數據
# 從CSV文件讀取
raw_data = pd.read_csv('sales_data.csv')
# 從Excel文件讀取
raw_data = pd.read_excel('sales_data.xlsx')
2. 數據清洗
典型的數據清洗操作包括：
- 處理缺失值：
df.fillna()或df.dropna() - 數據類型轉換：
df.astype() - 去除重復值：
df.drop_duplicates() - 異常值處理：通過條件篩選或統(tǒng)計方法
示例：
# 填充缺失值
cleaned_data = raw_data.fillna({'Sales': 0})
# 轉換日期格式
cleaned_data['Date'] = pd.to_datetime(cleaned_data['Date'])
# 去除重復記錄
cleaned_data = cleaned_data.drop_duplicates()
數據分析階段
1. 基礎統(tǒng)計分析
# 按產品類別匯總銷售數據
sales_summary = cleaned_data.groupby('Product Category')['Sales'].agg(['sum', 'mean', 'count'])
# 計算月增長率
monthly_sales = cleaned_data.groupby(pd.Grouper(key='Date', freq='M'))['Sales'].sum()
monthly_growth = monthly_sales.pct_change() * 100
2. 高級分析
# 計算各區(qū)域銷售占比
region_share = cleaned_data.groupby('Region')['Sales'].sum() / cleaned_data['Sales'].sum()
# 客戶價值分析(RFM模型)
current_date = pd.to_datetime('today')
rfm_data = cleaned_data.groupby('CustomerID').agg({
'Date': lambda x: (current_date - x.max()).days,
'OrderID': 'count',
'Sales': 'sum'
})
報表生成階段
1. 創(chuàng)建Excel工作簿
wb = Workbook() ws = wb.active ws.title = "銷售匯總"
2. 寫入數據
# 寫入表頭
ws.append(['產品類別', '銷售總額', '平均銷售額', '銷售數量'])
# 寫入匯總數據
for index, row in sales_summary.iterrows():
ws.append([index, row['sum'], row['mean'], row['count']])
3. 添加圖表
# 創(chuàng)建柱狀圖 chart = BarChart() data = Reference(ws, min_col=2, max_col=4, min_row=1, max_row=len(sales_summary)+1) categories = Reference(ws, min_col=1, max_col=1, min_row=2, max_row=len(sales_summary)+1) chart.add_data(data, titles_from_data=True) chart.set_categories(categories) ws.add_chart(chart, "F2")
4. 格式化單元格
from openpyxl.styles import Font, Alignment, Border, Side, PatternFill
# 設置標題樣式
for cell in ws[1]:
cell.font = Font(bold=True)
cell.alignment = Alignment(horizontal='center')
cell.fill = PatternFill("solid", fgColor="DDDDDD")
# 設置數字格式
for row in ws.iter_rows(min_row=2, min_col=2, max_col=4):
for cell in row:
cell.number_format = '#,##0'
自動化流程整合
1. 創(chuàng)建主函數
def generate_report(input_file, output_file):
# 數據讀取
raw_data = pd.read_excel(input_file)
# 數據清洗
cleaned_data = clean_data(raw_data)
# 數據分析
report_data = analyze_data(cleaned_data)
# 生成報表
create_excel_report(report_data, output_file)
print(f"報表已成功生成: {output_file}")
2. 定時任務設置
import schedule
import time
def job():
generate_report('daily_sales.xlsx', 'sales_report.xlsx')
# 每天上午9點運行
schedule.every().day.at("09:00").do(job)
while True:
schedule.run_pending()
time.sleep(1)
實際應用示例
場景：月度銷售報表
系統(tǒng)自動從ERP導出原始數據
清洗并計算各產品線銷售指標
生成包含以下內容的報表：
- 銷售匯總表
- 區(qū)域銷售分布餅圖
- 月度銷售趨勢折線圖
- 銷售TOP10產品列表
場景：客戶分析報告
整合CRM和銷售系統(tǒng)數據
進行客戶細分和價值分析
輸出包含以下內容的報告：
- 客戶價值矩陣(RFM)
- 客戶生命周期分析
- 流失風險預警列表
通過本教程，您已掌握使用Python實現(xiàn)Excel報表自動化的核心技能。實際應用中可根據具體需求擴展功能，如添加郵件自動發(fā)送、多數據源整合等高級特性。
方法補充
環(huán)境準備
我們需要安裝以下Python庫：
- pandas：強大的數據分析工具
- openpyxl：Excel文件操作庫
- matplotlib：數據可視化庫
安裝命令：
pip install pandas openpyxl matplotlib
項目功能概述
我們將創(chuàng)建一個腳本，實現(xiàn)以下功能：
- 從多個數據源讀取原始數據
- 數據清洗與預處理
- 關鍵指標計算與分析
- 自動生成格式化的Excel報表
- 創(chuàng)建數據可視化圖表
- 郵件自動發(fā)送報表
代碼詳細解析
1. 導入必要的庫
import pandas as pd from openpyxl import Workbook from openpyxl.styles import Font, Alignment, Border, Side, PatternFill from openpyxl.chart import BarChart, Reference import matplotlib.pyplot as plt import os from datetime import datetime
2. 配置參數設置
# 數據源配置
DATA_SOURCES = {
'sales': 'data/raw_sales_data.xlsx',
'inventory': 'data/inventory_records.csv',
'customers': 'data/customer_info.json'
}
# 報表輸出配置
REPORT_PATH = 'reports/monthly_report.xlsx'
REPORT_TITLE = '2023年度銷售分析報告'
ANALYSIS_PERIOD = ('2023-01-01', '2023-12-31')
# 樣式配置
HEADER_STYLE = {
'font': Font(bold=True, color='FFFFFF'),
'fill': PatternFill(start_color='4F81BD', end_color='4F81BD', fill_type='solid'),
'alignment': Alignment(horizontal='center')
}
DATA_STYLE = {
'border': Border(left=Side(style='thin'),
'alignment': Alignment(horizontal='right')
}3. 數據加載與清洗
def load_and_clean_data():
"""加載并清洗原始數據"""
# 加載銷售數據
sales_df = pd.read_excel(DATA_SOURCES['sales'])
sales_df = sales_df.dropna(subset=['order_id', 'sale_amount'])
sales_df['order_date'] = pd.to_datetime(sales_df['order_date'])
# 加載庫存數據
inventory_df = pd.read_csv(DATA_SOURCES['inventory'])
inventory_df = inventory_df[inventory_df['stock_quantity'] > 0]
# 加載客戶數據
customers_df = pd.read_json(DATA_SOURCES['customers'])
customers_df['join_date'] = pd.to_datetime(customers_df['join_date'])
return sales_df, inventory_df, customers_df4. 數據分析與計算
def perform_analysis(sales_df, inventory_df, customers_df):
"""執(zhí)行核心數據分析"""
# 銷售分析
period_mask = (sales_df['order_date'] >= ANALYSIS_PERIOD[0]) & \
(sales_df['order_date'] <= ANALYSIS_PERIOD[1])
sales_analysis = sales_df[period_mask].groupby('product_category')['sale_amount'] \
.agg(['sum', 'count', 'mean']).reset_index()
# 庫存周轉分析
inventory_turnover = inventory_df.groupby('category')['stock_quantity'].sum().reset_index()
# 客戶分析
active_customers = customers_df[customers_df['status'] == 'active']
customer_growth = active_customers.groupby(
pd.Grouper(key='join_date', freq='M')).size().reset_index(name='count')
return sales_analysis, inventory_turnover, customer_growth5. Excel報表生成
def generate_excel_report(data, output_path):
"""生成格式化的Excel報表"""
wb = Workbook()
ws = wb.active
ws.title = "銷售摘要"
# 添加標題
ws.merge_cells('A1:D1')
title_cell = ws['A1']
title_cell.value = REPORT_TITLE
title_cell.font = Font(size=16, bold=True)
title_cell.alignment = Alignment(horizontal='center')
# 添加數據表頭
headers = ['產品類別', '總銷售額', '訂單數量', '平均訂單價值']
for col_num, header in enumerate(headers, 1):
cell = ws.cell(row=3, column=col_num, value=header)
cell.font = HEADER_STYLE['font']
cell.fill = HEADER_STYLE['fill']
cell.alignment = HEADER_STYLE['alignment']
# 填充數據
for row_num, row_data in enumerate(data[0].values, 4):
for col_num, value in enumerate(row_data, 1):
cell = ws.cell(row=row_num, column=col_num, value=value)
cell.border = DATA_STYLE['border']
cell.alignment = DATA_STYLE['alignment']
# 添加圖表
chart = BarChart()
chart.title = "各品類銷售額對比"
chart.y_axis.title = '銷售額'
chart.x_axis.title = '產品類別'
data_ref = Reference(ws, min_col=2, min_row=3, max_col=2, max_row=row_num)
cats_ref = Reference(ws, min_col=1, min_row=4, max_row=row_num)
chart.add_data(data_ref, titles_from_data=False)
chart.set_categories(cats_ref)
ws.add_chart(chart, "F3")
# 保存文件
os.makedirs(os.path.dirname(output_path), exist_ok=True)
wb.save(output_path)6. 主程序流程
def main():
print("開始生成月度分析報告...")
start_time = datetime.now()
try:
# 數據加載與清洗
sales, inventory, customers = load_and_clean_data()
# 數據分析
sales_analysis, inventory_turnover, customer_growth = perform_analysis(
sales, inventory, customers)
# 生成報表
generate_excel_report(
(sales_analysis, inventory_turnover, customer_growth),
REPORT_PATH)
# 生成可視化圖表
generate_visualizations(sales_analysis, customer_growth)
elapsed = datetime.now() - start_time
print(f"報表生成完成！耗時: {elapsed.total_seconds():.2f}秒")
print(f"報表路徑: {os.path.abspath(REPORT_PATH)}")
except Exception as e:
print(f"生成報告時出錯: {str(e)}")
if __name__ == "__main__":
main()完整代碼實現(xiàn)
import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, Border, Side, PatternFill
from openpyxl.chart import BarChart, Reference
import matplotlib.pyplot as plt
import os
from datetime import datetime
# 配置區(qū)域
DATA_SOURCES = {
'sales': 'data/raw_sales_data.xlsx',
'inventory': 'data/inventory_records.csv',
'customers': 'data/customer_info.json'
}
REPORT_PATH = 'reports/monthly_report.xlsx'
REPORT_TITLE = '2023年度銷售分析報告'
ANALYSIS_PERIOD = ('2023-01-01', '2023-12-31')
HEADER_STYLE = {
'font': Font(bold=True, color='FFFFFF'),
'fill': PatternFill(start_color='4F81BD', end_color='4F81BD', fill_type='solid'),
'alignment': Alignment(horizontal='center')
}
DATA_STYLE = {
'border': Border(left=Side(style='thin'),
'alignment': Alignment(horizontal='right')
}
def load_and_clean_data():
"""加載并清洗原始數據"""
sales_df = pd.read_excel(DATA_SOURCES['sales'])
sales_df = sales_df.dropna(subset=['order_id', 'sale_amount'])
sales_df['order_date'] = pd.to_datetime(sales_df['order_date'])
inventory_df = pd.read_csv(DATA_SOURCES['inventory'])
inventory_df = inventory_df[inventory_df['stock_quantity'] > 0]
customers_df = pd.read_json(DATA_SOURCES['customers'])
customers_df['join_date'] = pd.to_datetime(customers_df['join_date'])
return sales_df, inventory_df, customers_df
def perform_analysis(sales_df, inventory_df, customers_df):
"""執(zhí)行核心數據分析"""
period_mask = (sales_df['order_date'] >= ANALYSIS_PERIOD[0]) & \
(sales_df['order_date'] <= ANALYSIS_PERIOD[1])
sales_analysis = sales_df[period_mask].groupby('product_category')['sale_amount'] \
.agg(['sum', 'count', 'mean']).reset_index()
inventory_turnover = inventory_df.groupby('category')['stock_quantity'].sum().reset_index()
active_customers = customers_df[customers_df['status'] == 'active']
customer_growth = active_customers.groupby(
pd.Grouper(key='join_date', freq='M')).size().reset_index(name='count')
return sales_analysis, inventory_turnover, customer_growth
def generate_visualizations(sales_analysis, customer_growth):
"""生成可視化圖表"""
# 銷售額分布圖
plt.figure(figsize=(10, 6))
plt.bar(sales_analysis['product_category'], sales_analysis['sum'])
plt.title('各品類銷售額對比')
plt.xlabel('產品類別')
plt.ylabel('銷售額')
plt.xticks(rotation=45)
plt.tight_layout()
plt.savefig('reports/sales_by_category.png')
# 客戶增長曲線
plt.figure(figsize=(10, 6))
plt.plot(customer_growth['join_date'], customer_growth['count'], marker='o')
plt.title('月度新增客戶趨勢')
plt.xlabel('月份')
plt.ylabel('新增客戶數')
plt.grid(True)
plt.tight_layout()
plt.savefig('reports/customer_growth.png')
def generate_excel_report(data, output_path):
"""生成格式化的Excel報表"""
wb = Workbook()
ws = wb.active
ws.title = "銷售摘要"
# 添加標題
ws.merge_cells('A1:D1')
title_cell = ws['A1']
title_cell.value = REPORT_TITLE
title_cell.font = Font(size=16, bold=True)
title_cell.alignment = Alignment(horizontal='center')
# 添加數據表頭
headers = ['產品類別', '總銷售額', '訂單數量', '平均訂單價值']
for col_num, header in enumerate(headers, 1):
cell = ws.cell(row=3, column=col_num, value=header)
cell.font = HEADER_STYLE['font']
cell.fill = HEADER_STYLE['fill']
cell.alignment = HEADER_STYLE['alignment']
# 填充數據
for row_num, row_data in enumerate(data[0].values, 4):
for col_num, value in enumerate(row_data, 1):
cell = ws.cell(row=row_num, column=col_num, value=value)
cell.border = DATA_STYLE['border']
cell.alignment = DATA_STYLE['alignment']
# 添加圖表
chart = BarChart()
chart.title = "各品類銷售額對比"
chart.y_axis.title = '銷售額'
chart.x_axis.title = '產品類別'
data_ref = Reference(ws, min_col=2, min_row=3, max_col=2, max_row=row_num)
cats_ref = Reference(ws, min_col=1, min_row=4, max_row=row_num)
chart.add_data(data_ref, titles_from_data=False)
chart.set_categories(cats_ref)
ws.add_chart(chart, "F3")
# 保存文件
os.makedirs(os.path.dirname(output_path), exist_ok=True)
wb.save(output_path)
def main():
print("開始生成月度分析報告...")
start_time = datetime.now()
try:
sales, inventory, customers = load_and_clean_data()
sales_analysis, inventory_turnover, customer_growth = perform_analysis(
sales, inventory, customers)
generate_excel_report(
(sales_analysis, inventory_turnover, customer_growth),
REPORT_PATH)
generate_visualizations(sales_analysis, customer_growth)
elapsed = datetime.now() - start_time
print(f"報表生成完成！耗時: {elapsed.total_seconds():.2f}秒")
print(f"報表路徑: {os.path.abspath(REPORT_PATH)}")
except Exception as e:
print(f"生成報告時出錯: {str(e)}")
if __name__ == "__main__":
main()代碼優(yōu)化建議
數據庫集成：添加直接從SQL數據庫讀取數據的功能
參數化配置：使用配置文件或命令行參數管理設置
異常處理增強：為不同操作添加更精細的錯誤處理
性能監(jiān)控：添加內存使用和性能分析
模板系統(tǒng)：支持自定義Excel模板
擴展功能思路
自動化郵件發(fā)送：集成smtplib自動發(fā)送報表
多格式輸出：支持PDF、HTML格式報表
數據驗證：添加數據質量檢查功能
定時任務：設置定期自動運行
交互式儀表盤：使用Dash或Streamlit創(chuàng)建Web儀表盤
總結
這個項目展示了Python在辦公自動化方面的強大能力，通過擴展和定制，可以滿足各種復雜的報表需求。例如：
- 財務部門的月度報表自動生成
- 銷售團隊的業(yè)績日報推送
- 運營部門的活動效果分析報告
系統(tǒng)還預留了擴展接口，支持：
- 郵件自動發(fā)送報表
- 企業(yè)微信/釘釘機器人通知
- 定時任務調度
建議讀者在實際工作中應用這些技術，可以先從簡單的周報自動化開始，逐步構建自己的自動化工具庫。典型的學習路徑可以是：
- 掌握pandas數據處理基礎
- 學習Excel文件操作
- 實現(xiàn)第一個自動化腳本
- 添加異常處理和日志
- 設計可復用的報表模板
- 集成到日常工作流程中
到此這篇關于Python數據處理之Excel報表自動化生成與分析的文章就介紹到這了,更多相關Python自動化生成Excel報表內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
Python函數any()和all()的用法及區(qū)別介紹
any函數：any(x)，只要x中有一個不為空，0，false就返回True，否則返回False。all(x)函數必須x中的所有元素均不為空，0，false才會返回True，否則返回False。接下來通過本文給大家介紹Python函數any()和all()的用法及區(qū)別介紹，需要的朋友參考下吧2018-09-09
本篇文章將分享Prometheus?API的基本概念到PromQL查詢語言的應用,再到如何通過Python與Prometheus?API進行無縫交互,通過豐富的示例代碼和詳細的講解,將解鎖使用Python進行實時監(jiān)控的奇妙世界,為讀者打開更廣闊的數據分析視野2024-01-01
這篇文章主要為大家介紹了python文字點選驗證碼加固安全防線實現(xiàn)示例詳解,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進步,早日升職加薪2023-09-09
這篇文章主要為大家詳細介紹了Python中常用的8個繪圖函數的使用,文中的示例代碼講解詳細,具有一定的學習價值,感興趣的小伙伴可以跟隨小編一起了解一下2023-10-10
這篇文章主要為大家介紹了Python內省與反射巧妙運用示例詳解,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進步,早日升職加薪2023-11-11
Python語法學習之進程的創(chuàng)建與常用方法詳解
本文我們將學習一下在?Python?中去創(chuàng)建并使用多進程的方法，可以通過創(chuàng)建多個進程來幫助我們提高腳本執(zhí)行的效率，感興趣的可以了解一下2022-04-04
這篇文章主要介紹了python import 上級目錄的導入,文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2020-11-11
這篇文章主要介紹了Pytest mark使用實例及原理解析,文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值,需要的朋友可以參考下2020-02-02
這篇文章主要介紹了簡單的編程0基礎下Python入門指引,包括從各個系統(tǒng)的Python安裝和簡單的語句知識，需要的朋友可以參考下2015-04-04
這篇文章主要為大家詳細介紹了Python實現(xiàn)在線音樂播放器的相關資料，文中示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-03-03