Python數(shù)據(jù)處理之Excel報表自動化生成與分析
更新時間：2025年07月17日 09:58:41 作者：nightunderblackcat
這篇文章主要為大家詳細(xì)介紹了如何使用Python實(shí)現(xiàn)一個完整的Excel報表自動化系統(tǒng),涵蓋從數(shù)據(jù)清洗、分析到可視化報表生成的全流程,希望對大家有所幫助
本文將詳細(xì)介紹如何使用Python實(shí)現(xiàn)一個完整的Excel報表自動化系統(tǒng)，涵蓋從數(shù)據(jù)清洗、分析到可視化報表生成的全流程。本教程面向Python初學(xué)者，通過實(shí)際案例講解pandas和openpyxl庫的核心用法。
系統(tǒng)概述
Excel報表自動化系統(tǒng)能夠：
- 自動讀取原始數(shù)據(jù)文件（如CSV、Excel等）
- 進(jìn)行數(shù)據(jù)清洗和預(yù)處理
- 執(zhí)行數(shù)據(jù)分析計算
- 生成格式化的Excel報表
- 創(chuàng)建可視化圖表
環(huán)境準(zhǔn)備
安裝必要庫
pip install pandas openpyxl matplotlib
導(dǎo)入庫
import pandas as pd from openpyxl import Workbook from openpyxl.chart import BarChart, Reference import matplotlib.pyplot as plt
數(shù)據(jù)清洗階段
1. 讀取原始數(shù)據(jù)
# 從CSV文件讀取
raw_data = pd.read_csv('sales_data.csv')
# 從Excel文件讀取
raw_data = pd.read_excel('sales_data.xlsx')
2. 數(shù)據(jù)清洗
典型的數(shù)據(jù)清洗操作包括：
- 處理缺失值：
df.fillna()或df.dropna() - 數(shù)據(jù)類型轉(zhuǎn)換：
df.astype() - 去除重復(fù)值：
df.drop_duplicates() - 異常值處理：通過條件篩選或統(tǒng)計方法
示例：
# 填充缺失值
cleaned_data = raw_data.fillna({'Sales': 0})
# 轉(zhuǎn)換日期格式
cleaned_data['Date'] = pd.to_datetime(cleaned_data['Date'])
# 去除重復(fù)記錄
cleaned_data = cleaned_data.drop_duplicates()
數(shù)據(jù)分析階段
1. 基礎(chǔ)統(tǒng)計分析
# 按產(chǎn)品類別匯總銷售數(shù)據(jù)
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. 寫入數(shù)據(jù)
# 寫入表頭
ws.append(['產(chǎn)品類別', '銷售總額', '平均銷售額', '銷售數(shù)量'])
# 寫入?yún)R總數(shù)據(jù)
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
# 設(shè)置標(biāo)題樣式
for cell in ws[1]:
cell.font = Font(bold=True)
cell.alignment = Alignment(horizontal='center')
cell.fill = PatternFill("solid", fgColor="DDDDDD")
# 設(shè)置數(shù)字格式
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)建主函數(shù)
def generate_report(input_file, output_file):
# 數(shù)據(jù)讀取
raw_data = pd.read_excel(input_file)
# 數(shù)據(jù)清洗
cleaned_data = clean_data(raw_data)
# 數(shù)據(jù)分析
report_data = analyze_data(cleaned_data)
# 生成報表
create_excel_report(report_data, output_file)
print(f"報表已成功生成: {output_file}")
2. 定時任務(wù)設(shè)置
import schedule
import time
def job():
generate_report('daily_sales.xlsx', 'sales_report.xlsx')
# 每天上午9點(diǎn)運(yùn)行
schedule.every().day.at("09:00").do(job)
while True:
schedule.run_pending()
time.sleep(1)
實(shí)際應(yīng)用示例
場景：月度銷售報表
系統(tǒng)自動從ERP導(dǎo)出原始數(shù)據(jù)
清洗并計算各產(chǎn)品線銷售指標(biāo)
生成包含以下內(nèi)容的報表：
- 銷售匯總表
- 區(qū)域銷售分布餅圖
- 月度銷售趨勢折線圖
- 銷售TOP10產(chǎn)品列表
場景：客戶分析報告
整合CRM和銷售系統(tǒng)數(shù)據(jù)
進(jìn)行客戶細(xì)分和價值分析
輸出包含以下內(nèi)容的報告：
- 客戶價值矩陣(RFM)
- 客戶生命周期分析
- 流失風(fēng)險預(yù)警列表
通過本教程，您已掌握使用Python實(shí)現(xiàn)Excel報表自動化的核心技能。實(shí)際應(yīng)用中可根據(jù)具體需求擴(kuò)展功能，如添加郵件自動發(fā)送、多數(shù)據(jù)源整合等高級特性。
方法補(bǔ)充
環(huán)境準(zhǔn)備
我們需要安裝以下Python庫：
- pandas：強(qiáng)大的數(shù)據(jù)分析工具
- openpyxl：Excel文件操作庫
- matplotlib：數(shù)據(jù)可視化庫
安裝命令：
pip install pandas openpyxl matplotlib
項(xiàng)目功能概述
我們將創(chuàng)建一個腳本，實(shí)現(xiàn)以下功能：
- 從多個數(shù)據(jù)源讀取原始數(shù)據(jù)
- 數(shù)據(jù)清洗與預(yù)處理
- 關(guān)鍵指標(biāo)計算與分析
- 自動生成格式化的Excel報表
- 創(chuàng)建數(shù)據(jù)可視化圖表
- 郵件自動發(fā)送報表
代碼詳細(xì)解析
1. 導(dǎo)入必要的庫
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. 配置參數(shù)設(shè)置
# 數(shù)據(jù)源配置
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. 數(shù)據(jù)加載與清洗
def load_and_clean_data():
"""加載并清洗原始數(shù)據(jù)"""
# 加載銷售數(shù)據(jù)
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'])
# 加載庫存數(shù)據(jù)
inventory_df = pd.read_csv(DATA_SOURCES['inventory'])
inventory_df = inventory_df[inventory_df['stock_quantity'] > 0]
# 加載客戶數(shù)據(jù)
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. 數(shù)據(jù)分析與計算
def perform_analysis(sales_df, inventory_df, customers_df):
"""執(zhí)行核心數(shù)據(jù)分析"""
# 銷售分析
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()
# 庫存周轉(zhuǎn)分析
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 = "銷售摘要"
# 添加標(biāo)題
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')
# 添加數(shù)據(jù)表頭
headers = ['產(chǎn)品類別', '總銷售額', '訂單數(shù)量', '平均訂單價值']
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']
# 填充數(shù)據(jù)
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 = '產(chǎn)品類別'
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:
# 數(shù)據(jù)加載與清洗
sales, inventory, customers = load_and_clean_data()
# 數(shù)據(jù)分析
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()完整代碼實(shí)現(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():
"""加載并清洗原始數(shù)據(jù)"""
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í)行核心數(shù)據(jù)分析"""
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('產(chǎn)品類別')
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('新增客戶數(shù)')
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 = "銷售摘要"
# 添加標(biāo)題
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')
# 添加數(shù)據(jù)表頭
headers = ['產(chǎn)品類別', '總銷售額', '訂單數(shù)量', '平均訂單價值']
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']
# 填充數(shù)據(jù)
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 = '產(chǎn)品類別'
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)化建議
數(shù)據(jù)庫集成：添加直接從SQL數(shù)據(jù)庫讀取數(shù)據(jù)的功能
參數(shù)化配置：使用配置文件或命令行參數(shù)管理設(shè)置
異常處理增強(qiáng)：為不同操作添加更精細(xì)的錯誤處理
性能監(jiān)控：添加內(nèi)存使用和性能分析
模板系統(tǒng)：支持自定義Excel模板
擴(kuò)展功能思路
自動化郵件發(fā)送：集成smtplib自動發(fā)送報表
多格式輸出：支持PDF、HTML格式報表
數(shù)據(jù)驗(yàn)證：添加數(shù)據(jù)質(zhì)量檢查功能
定時任務(wù)：設(shè)置定期自動運(yùn)行
交互式儀表盤：使用Dash或Streamlit創(chuàng)建Web儀表盤
總結(jié)
這個項(xiàng)目展示了Python在辦公自動化方面的強(qiáng)大能力，通過擴(kuò)展和定制，可以滿足各種復(fù)雜的報表需求。例如：
- 財務(wù)部門的月度報表自動生成
- 銷售團(tuán)隊(duì)的業(yè)績?nèi)請笸扑?/li>
- 運(yùn)營部門的活動效果分析報告
系統(tǒng)還預(yù)留了擴(kuò)展接口，支持：
- 郵件自動發(fā)送報表
- 企業(yè)微信/釘釘機(jī)器人通知
- 定時任務(wù)調(diào)度
建議讀者在實(shí)際工作中應(yīng)用這些技術(shù)，可以先從簡單的周報自動化開始，逐步構(gòu)建自己的自動化工具庫。典型的學(xué)習(xí)路徑可以是：
- 掌握pandas數(shù)據(jù)處理基礎(chǔ)
- 學(xué)習(xí)Excel文件操作
- 實(shí)現(xiàn)第一個自動化腳本
- 添加異常處理和日志
- 設(shè)計可復(fù)用的報表模板
- 集成到日常工作流程中
到此這篇關(guān)于Python數(shù)據(jù)處理之Excel報表自動化生成與分析的文章就介紹到這了,更多相關(guān)Python自動化生成Excel報表內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
Python函數(shù)any()和all()的用法及區(qū)別介紹
any函數(shù)：any(x)，只要x中有一個不為空，0，false就返回True，否則返回False。all(x)函數(shù)必須x中的所有元素均不為空，0，false才會返回True，否則返回False。接下來通過本文給大家介紹Python函數(shù)any()和all()的用法及區(qū)別介紹，需要的朋友參考下吧2018-09-09
Python?Prometheus接口揭秘數(shù)據(jù)科學(xué)新技巧
本篇文章將分享Prometheus?API的基本概念到PromQL查詢語言的應(yīng)用,再到如何通過Python與Prometheus?API進(jìn)行無縫交互,通過豐富的示例代碼和詳細(xì)的講解,將解鎖使用Python進(jìn)行實(shí)時監(jiān)控的奇妙世界,為讀者打開更廣闊的數(shù)據(jù)分析視野2024-01-01
python+html文字點(diǎn)選驗(yàn)證碼加固安全防線
這篇文章主要為大家介紹了python文字點(diǎn)選驗(yàn)證碼加固安全防線實(shí)現(xiàn)示例詳解,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進(jìn)步,早日升職加薪2023-09-09
這篇文章主要為大家詳細(xì)介紹了Python中常用的8個繪圖函數(shù)的使用,文中的示例代碼講解詳細(xì),具有一定的學(xué)習(xí)價值,感興趣的小伙伴可以跟隨小編一起了解一下2023-10-10
這篇文章主要為大家介紹了Python內(nèi)省與反射巧妙運(yùn)用示例詳解,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進(jìn)步,早日升職加薪2023-11-11
Python語法學(xué)習(xí)之進(jìn)程的創(chuàng)建與常用方法詳解
本文我們將學(xué)習(xí)一下在?Python?中去創(chuàng)建并使用多進(jìn)程的方法，可以通過創(chuàng)建多個進(jìn)程來幫助我們提高腳本執(zhí)行的效率，感興趣的可以了解一下2022-04-04
這篇文章主要介紹了python import 上級目錄的導(dǎo)入,文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-11-11
這篇文章主要介紹了Pytest mark使用實(shí)例及原理解析,文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友可以參考下2020-02-02
這篇文章主要介紹了簡單的編程0基礎(chǔ)下Python入門指引,包括從各個系統(tǒng)的Python安裝和簡單的語句知識，需要的朋友可以參考下2015-04-04
這篇文章主要為大家詳細(xì)介紹了Python實(shí)現(xiàn)在線音樂播放器的相關(guān)資料，文中示例代碼介紹的非常詳細(xì)，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-03-03