Python數(shù)據(jù)處理之Excel報表自動化生成與分析

更新時間：2025年07月17日 09:58:41 作者：nightunderblackcat

這篇文章主要為大家詳細(xì)介紹了如何使用Python實現(xiàn)一個完整的Excel報表自動化系統(tǒng),涵蓋從數(shù)據(jù)清洗、分析到可視化報表生成的全流程,希望對大家有所幫助

本文將詳細(xì)介紹如何使用Python實現(xiàn)一個完整的Excel報表自動化系統(tǒng)，涵蓋從數(shù)據(jù)清洗、分析到可視化報表生成的全流程。本教程面向Python初學(xué)者，通過實際案例講解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點運(yùn)行
schedule.every().day.at("09:00").do(job)

while True:
    schedule.run_pending()
    time.sleep(1)

實際應(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實現(xiàn)Excel報表自動化的核心技能。實際應(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

項目功能概述

我們將創(chuàng)建一個腳本，實現(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_df

4. 數(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_growth

5. 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()

完整代碼實現(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ù)驗證：添加數(shù)據(jù)質(zhì)量檢查功能

定時任務(wù)：設(shè)置定期自動運(yùn)行

交互式儀表盤：使用Dash或Streamlit創(chuàng)建Web儀表盤

總結(jié)

這個項目展示了Python在辦公自動化方面的強(qiáng)大能力，通過擴(kuò)展和定制，可以滿足各種復(fù)雜的報表需求。例如：

財務(wù)部門的月度報表自動生成
銷售團(tuán)隊的業(yè)績?nèi)請笸扑?/li>
運(yùn)營部門的活動效果分析報告

系統(tǒng)還預(yù)留了擴(kuò)展接口，支持：

郵件自動發(fā)送報表
企業(yè)微信/釘釘機(jī)器人通知
定時任務(wù)調(diào)度

建議讀者在實際工作中應(yīng)用這些技術(shù)，可以先從簡單的周報自動化開始，逐步構(gòu)建自己的自動化工具庫。典型的學(xué)習(xí)路徑可以是：

掌握pandas數(shù)據(jù)處理基礎(chǔ)
學(xué)習(xí)Excel文件操作
實現(xiàn)第一個自動化腳本
添加異常處理和日志
設(shè)計可復(fù)用的報表模板
集成到日常工作流程中

到此這篇關(guān)于Python數(shù)據(jù)處理之Excel報表自動化生成與分析的文章就介紹到這了,更多相關(guān)Python自動化生成Excel報表內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

Python數(shù)據(jù)處理之Excel報表自動化生成與分析

目錄

系統(tǒng)概述

環(huán)境準(zhǔn)備

安裝必要庫

導(dǎo)入庫

數(shù)據(jù)清洗階段

1. 讀取原始數(shù)據(jù)

2. 數(shù)據(jù)清洗

數(shù)據(jù)分析階段

1. 基礎(chǔ)統(tǒng)計分析

2. 高級分析

報表生成階段

1. 創(chuàng)建Excel工作簿

2. 寫入數(shù)據(jù)

3. 添加圖表

4. 格式化單元格

自動化流程整合

1. 創(chuàng)建主函數(shù)

2. 定時任務(wù)設(shè)置

實際應(yīng)用示例

場景：月度銷售報表

場景：客戶分析報告

方法補(bǔ)充

環(huán)境準(zhǔn)備

項目功能概述

代碼詳細(xì)解析

完整代碼實現(xiàn)

代碼優(yōu)化建議

擴(kuò)展功能思路

總結(jié)

相關(guān)文章

最新評論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線小工具