摘要：本教程旨在让学员通过实际案例掌握Python处理Excel实现办公自动化的技能，从基础操作到高级应用，成为能解决复杂办公场景问题的高级工程师。

本教程旨在让学员通过实际案例掌握Python处理Excel实现办公自动化的技能，从基础操作到高级应用，成为能解决复杂办公场景问题的高级工程师。

场景介绍

某公司有多个销售部门，每个月各部门都会提交销售数据报表（Excel格式）。月底需要将这些报表合并成一个总表，并进行数据清洗、统计分析，比如计算各部门销售总额、各类产品销售占比等。

用到的库和技术

• pandas：强大的数据处理和分析库，用于读取、合并、清洗和统计数据。

• openpyxl：操作Excel文件，用于写入处理后的数据到新的Excel文件，设置格式等。

代码实现步骤

1. 批量读取Excel文件：使用os库遍历文件夹获取所有销售数据文件，再用pandas的read_excel函数读取每个文件的数据。

import pandas as pd

import os

file_path = '销售数据文件夹路径'

all_data =

for file in os.listdir(file_path):

if file.endswith('.xlsx'):

df = pd.read_excel(os.path.join(file_path, file))

all_data.append(df)

2. 数据合并与清洗：使用pandas的concat函数按行合并所有数据，接着处理缺失值和重复值。

merged_data = pd.concat(all_data, ignore_index=True)

# 处理缺失值，这里简单用0填充数值列缺失值

merged_data.fillna(0, inplace=True)

# 去除重复行

merged_data = merged_data.drop_duplicates

3. 数据统计分析：计算各部门销售总额，统计各类产品销售占比。

# 计算各部门销售总额

department_total = merged_data.groupby('部门')['销售额'].sum

# 统计各类产品销售占比

product_sales = merged_data.groupby('产品类别')['销售额'].sum

product_sales_ratio = product_sales / product_sales.sum

4. 结果写入Excel：利用openpyxl将分析结果写入新的Excel文件，并设置格式。

from openpyxl import Workbook

from openpyxl.styles import Font, Alignment

wb = Workbook

ws = wb.active

# 写入各部门销售总额数据

ws.append(['部门', '销售总额'])

for dept, total in department_total.items:

ws.append([dept, total])

# 设置表头样式

header_font = Font(bold=True, color='FFFFFF')

header_fill = PatternFill(start_color='4F81BD', end_color='4F81BD', fill_type='solid')

for cell in ws[1]:

cell.font = header_font

cell.fill = header_fill

cell.alignment = Alignment(horizontal='center')

wb.save('销售数据分析结果.xlsx')

场景介绍

人力资源部门每月要根据员工考勤、工作成果等数据生成绩效评估报告。数据来源于不同的Excel文件，需要整合处理，按照固定模板生成报告，并且格式规范，图表清晰。

用到的库和技术

• pandas：数据处理。

• xlwings：与Excel进行交互，直接在Excel中调用Python脚本，实现数据更新和图表动态生成。

• matplotlib或seaborn：数据可视化，生成图表。

1. 数据整合：使用pandas读取多个数据源Excel文件，合并相关数据。

attendance_df = pd.read_excel('考勤数据.xlsx')

performance_df = pd.read_excel('工作成果数据.xlsx')

# 假设通过员工ID合并数据

merged_df = pd.merge(attendance_df, performance_df, on='员工ID')

2. 绩效计算：根据业务规则计算员工绩效得分。

# 例如绩效得分 = 考勤得分*0.4 + 工作成果得分*0.6

merged_df['绩效得分'] = merged_df['考勤得分'] * 0.4 + merged_df['工作成果得分'] * 0.6

3. 生成图表：使用matplotlib或seaborn生成员工绩效分布图表，如柱状图展示不同绩效等级人数分布。

import seaborn as sns

import matplotlib.pyplot as plt

# 假设根据绩效得分划分绩效等级

merged_df['绩效等级'] = pd.cut(merged_df['绩效得分'], bins=[0, 60, 80, 100], labels=['差', '中', '优'])

# 绘制柱状图

sns.countplot(x='绩效等级', data=merged_df)

plt.show

4. 报告生成：利用xlwings将处理后的数据和生成的图表嵌入到Excel模板中，生成最终报告。

import xlwings as xw

app = xw.App(visible=False)

wb = app.books.open('绩效评估报告模板.xlsx')

ws = wb.sheets['Sheet1']

# 写入数据

ws.range('A1').options(index=False).value = merged_df

# 插入图表，假设图表已保存为图片

ws.pictures.add('绩效分布.png', left=ws.range('E1').left, top=ws.range('E1').top)

wb.save('最终绩效评估报告.xlsx')

wb.close

app.quit

场景介绍

财务部门每月需要处理大量财务数据，生成资产负债表、利润表等报表，要求数据准确，格式符合财务规范，公式自动更新计算。

用到的库和技术

• openpyxl：操作Excel文件，创建报表、写入数据、设置公式和格式。

• pandas：辅助数据处理，如数据清洗、整理。

代码实现步骤

1. 数据处理：用pandas读取财务数据文件，进行清洗和整理。

finance_data = pd.read_excel('原始财务数据.xlsx')

# 数据清洗，比如去除异常值

finance_data = finance_data[(finance_data['金额'] > 0) & (finance_data['金额']

2. 创建报表：使用openpyxl创建新的Excel文件，添加资产负债表、利润表等工作表。

from openpyxl import Workbook

wb = Workbook

balance_sheet_ws = wb.create_sheet('资产负债表')

income_statement_ws = wb.create_sheet('利润表')

3. 写入数据与公式：将处理后的数据写入工作表，并设置公式实现自动计算。

# 假设资产负债表数据处理

assets_data = finance_data[finance_data['项目类别'] == '资产']

liabilities_data = finance_data[finance_data['项目类别'] == '负债']

# 写入资产数据

balance_sheet_ws.append(['资产项目', '金额'])

for index, row in assets_data.iterrows:

balance_sheet_ws.append([row['项目名称'], row['金额']])

# 写入负债数据

balance_sheet_ws.append() # 空行分隔

balance_sheet_ws.append(['负债项目', '金额'])

for index, row in liabilities_data.iterrows:

# 设置资产总计公式

balance_sheet_ws['B' + str(len(assets_data) + 3)] = '=SUM(B2:B' + str(len(assets_data) + 1) + ')'

# 设置负债总计公式

balance_sheet_ws['B' + str(len(assets_data) + len(liabilities_data) + 5)] = '=SUM(B' + str(len(assets_data) + 4) + ':B' + str(len(assets_data) + len(liabilities_data) + 3) + ')'

4. 格式优化：设置数字格式、字体、颜色等，使报表更规范易读。

from openpyxl.styles import Font, Alignment, numbers

# 设置表头字体加粗

header_font = Font(bold=True)

for cell in balance_sheet_ws[1]:

cell.font = header_font

cell.alignment = Alignment(horizontal='center')

# 设置金额数字格式

for row in balance_sheet_ws.iter_rows(min_row=2, min_col=2):

for cell in row:

cell.number_format = numbers.FORMAT_CURRENCY_USD_SIMPLE

wb.save('财务报表.xlsx')

总结

通过这三个案例，希望大家能够掌握Python处理Excel实现办公自动化的核心技能。从数据读取、清洗、分析到结果呈现，灵活运用pandas、openpyxl、xlwings等库解决实际办公中的复杂问题。

课后可以尝试拓展案例功能，或应用到自己的工作场景中 ，持续提升办公自动化水平。

来源：绿叶菜