Excel技能进阶：如何用Python自动化处理复杂数据报表

1次阅读

共计 1924 个字符，预计需要花费 5 分钟才能阅读完成。

在日常工作中，我们经常需要处理大量的 Excel 数据报表。手动操作不仅效率低下，还容易出错。以下是手动处理 Excel 时常见的三大问题：

数据量超过百万行时的卡顿 ：Excel 在处理大数据量时性能下降明显，滚动、筛选、计算都会变得异常缓慢，甚至崩溃。
跨表格关联分析困难 ：当需要从多个工作簿或工作表中提取数据进行分析时，手动操作极其繁琐且容易出错。
格式标准化耗时 ：统一字体、颜色、边框等格式设置需要重复操作，尤其是当报表模板需要频繁调整时，手动操作费时费力。

Python 提供了多个库来处理 Excel 文件，各有优缺点。以下是三种常用方案的对比：

openpyxl：适合读写.xlsx 文件，支持公式、图表、格式设置等功能，但内存占用较高，不适合处理超大数据量。
xlrd/xlwt：适合读写.xls 文件，速度快但功能有限，不支持.xlsx 格式的某些新特性。
pandas：适合数据处理和分析，内置大量数据清洗和转换功能，但对格式设置的支持较弱，通常需要结合 openpyxl 使用。

import pandas as pd

# 读取 Excel 文件
df = pd.read_excel('sales_data.xlsx')

# 创建数据透视表
pivot_table = pd.pivot_table(df, 
                             index=['Region', 'Product'], 
                             columns='Quarter', 
                             values='Sales', 
                             aggfunc='sum')

# 保存到新文件
pivot_table.to_excel('pivot_table.xlsx')

from openpyxl import load_workbook
from openpyxl.styles import Font, Border, Side, PatternFill

# 加载工作簿
wb = load_workbook('report.xlsx')
ws = wb.active

# 设置字体和边框
font = Font(name='Arial', size=12, bold=True)
border = Border(left=Side(style='thin'), 
                right=Side(style='thin'), 
                top=Side(style='thin'), 
                bottom=Side(style='thin'))

# 遍历单元格并应用格式
for row in ws.iter_rows(min_row=2, max_row=100, min_col=1, max_col=5):
    for cell in row:
        cell.font = font
        cell.border = border
        if cell.value > 1000:
            cell.fill = PatternFill(start_color='00FF00', end_color='00FF00', fill_type='solid')

# 保存修改
wb.save('formatted_report.xlsx')

import pandas as pd

# 分块读取大型 CSV 文件
chunk_size = 100000
for chunk in pd.read_csv('large_data.csv', chunksize=chunk_size):
    # 处理每个数据块
    processed_chunk = chunk.groupby('category').sum()
    processed_chunk.to_csv('processed_data.csv', mode='a', header=False)