xlsx-processing

XLSX 处理

概述

使用 openpyxl 以编程方式处理 Excel 文件，实现丰富的格式设置；使用 pandas 进行数据分析。本技能涵盖电子表格的读取和写入、公式、图表、条件格式、数据验证、数据透视表生成、CSV 导入/导出，以及处理大型文件的策略。

如果需要通过代码而非手动编辑来创建、读取、转换或增强 Excel 文件，请使用本技能。

多阶段流程

阶段 1：需求

1. 确定操作类型（读取、写入、转换、生成报告）
2. 确定数据来源和数据量
3. 明确格式和公式要求
4. 规划工作表结构和命名
5. 评估性能需求（行数、文件大小）

停止 — 在确定行数以及是否需要格式设置之前，切勿开始实施（这将决定库的选择）。

阶段 2：实施

1. 选择库（参见决策表）
2. 实施数据加载和转换
3. 应用格式、公式和验证
4. 添加图表和条件格式
5. 针对文件大小和内存进行优化

停止 — 对于超过 10,000 行的文件，切勿跳过内存优化。

阶段 3：验证

1. 在 Excel、LibreOffice 和 Google Sheets 中打开
2. 验证公式计算是否正确
3. 检查格式是否一致显示
4. 使用边界情况进行测试（空数据、最大行数）
5. 验证数据准确性

库选择决策表

场景	库	原因
丰富的格式设置（颜色、边框、字体）	openpyxl	完整的格式 API
数据分析、汇总、数据透视表	pandas	DataFrame 操作
根据数据分析生成格式化报告	pandas + openpyxl	结合两者优势
仅读取数据，无需格式设置	pandas	API 最简单
大型文件（> 10K 行），以写入为主	openpyxl write_only	流式写入，占用内存少
大型文件（> 10K 行），以读取为主	openpyxl read_only	流式读取，占用内存少
CSV 与 Excel 之间的转换	pandas	一行代码即可完成操作
在电子表格中创建图表	openpyxl	完全可控的图表 API

openpyxl 用法模式

创建工作簿

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter

wb = Workbook()
ws = wb.active
ws.title = "Report"

# Header row
headers = ['Name', 'Department', 'Revenue', 'Target', 'Achievement']
header_font = Font(name='Calibri', size=11, bold=True, color='FFFFFF')
header_fill = PatternFill(start_color='2F5496', end_color='2F5496', fill_type='solid')
header_alignment = Alignment(horizontal='center', vertical='center')
thin_border = Border(
    left=Side(style='thin'),
    right=Side(style='thin'),
    top=Side(style='thin'),
    bottom=Side(style='thin'),
)

for col, header in enumerate(headers, 1):
    cell = ws.cell(row=1, column=col, value=header)
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = header_alignment
    cell.border = thin_border

# Data rows
for row_idx, row_data in enumerate(data, 2):
    for col_idx, value in enumerate(row_data, 1):
        cell = ws.cell(row=row_idx, column=col_idx, value=value)
        cell.border = thin_border

# Auto-fit column widths
for col in range(1, len(headers) + 1):
    max_length = max(
        len(str(ws.cell(row=row, column=col).value or ''))
        for row in range(1, ws.max_row + 1)
    )
    ws.column_dimensions[get_column_letter(col)].width = min(max_length + 2, 50)

# Freeze header row
ws.freeze_panes = 'A2'

wb.save('report.xlsx')

公式

# Basic formulas
ws['E2'] = '=C2/D2'                    # Division
ws['F2'] = '=SUM(C2:C100)'             # Sum
ws['G2'] = '=AVERAGE(C2:C100)'         # Average
ws['H2'] = '=COUNTIF(E2:E100,">1")'   # Count if
ws['I2'] = '=IF(E2>=1,"Met","Below")'  # Conditional
ws['J2'] = '=VLOOKUP(A2,Sheet2!A:B,2,FALSE)'  # Lookup

# Array formula (Excel 365 dynamic array)
ws['K2'] = '=UNIQUE(A2:A100)'

# Named range
from openpyxl.workbook.defined_name import DefinedName
ref = f"Report!$C$2:$C${len(data)+1}"
defn = DefinedName('RevenueRange', attr_text=ref)
wb.defined_names.add(defn)

图表

from openpyxl.chart import BarChart, LineChart, PieChart, Reference

# Bar chart
chart = BarChart()
chart.type = 'col'
chart.title = 'Revenue by Department'
chart.y_axis.title = 'Revenue ($)'
chart.x_axis.title = 'Department'
chart.style = 10  # Built-in style

data_ref = Reference(ws, min_col=3, min_row=1, max_row=ws.max_row)
cats_ref = Reference(ws, min_col=2, min_row=2, max_row=ws.max_row)
chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(cats_ref)
chart.width = 20
chart.height = 12

ws.add_chart(chart, 'G2')

# Line chart with multiple series
line = LineChart()
line.title = 'Monthly Trends'
for col in range(3, 6):
    values = Reference(ws, min_col=col, min_row=1, max_row=13)
    line.add_data(values, titles_from_data=True)
cats = Reference(ws, min_col=1, min_row=2, max_row=13)
line.set_categories(cats)
ws.add_chart(line, 'G20')

条件格式

from openpyxl.formatting.rule import CellIsRule, ColorScaleRule, DataBarRule

# Highlight cells above threshold
ws.conditional_formatting.add(
    'C2:C100',
    CellIsRule(operator='greaterThan', formula=['100000'],
              fill=PatternFill(bgColor='C6EFCE'))
)

# Red for below target
ws.conditional_formatting.add(
    'E2:E100',
    CellIsRule(operator='lessThan', formula=['1'],
              fill=PatternFill(bgColor='FFC7CE'),
              font=Font(color='9C0006'))
)

# Color scale (green to red)
ws.conditional_formatting.add(
    'E2:E100',
    ColorScaleRule(
        start_type='min', start_color='F8696B',
        mid_type='percentile', mid_value=50, mid_color='FFEB84',
        end_type='max', end_color='63BE7B'
    )
)

# Data bars
ws.conditional_formatting.add(
    'C2:C100',
    DataBarRule(start_type='min', end_type='max', color='638EC6')
)

数据验证

from openpyxl.worksheet.datavalidation import DataValidation

# Dropdown list
dv = DataValidation(type='list', formula1='"Active,Inactive,Pending"', allow_blank=True)
dv.error = 'Please select a valid status'
dv.errorTitle = 'Invalid Entry'
ws.add_data_validation(dv)
dv.add('D2:D100')

# Number range
nv = DataValidation(type='whole', operator='between', formula1=0, formula2=100)
nv.error = 'Value must be between 0 and 100'
ws.add_data_validation(nv)
nv.add('F2:F100')

# Date validation
dv_date = DataValidation(type='date', operator='greaterThan', formula1='2025-01-01')
ws.add_data_validation(dv_date)
dv_date.add('G2:G100')

pandas 用法模式

读取 Excel

import pandas as pd

# Read single sheet
df = pd.read_excel('data.xlsx', sheet_name='Sheet1')

# Read with options
df = pd.read_excel('data.xlsx',
    sheet_name='Sales',
    header=0,
    usecols='A:E',
    dtype={'ID': str, 'Revenue': float},
    parse_dates=['Date'],
    na_values=['N/A', 'null', ''],
)

# Read all sheets
sheets = pd.read_excel('data.xlsx', sheet_name=None)  # Dict of DataFrames

使用 pandas + openpyxl 写入 Excel

with pd.ExcelWriter('output.xlsx', engine='openpyxl') as writer:
    df_summary.to_excel(writer, sheet_name='Summary', index=False)
    df_detail.to_excel(writer, sheet_name='Detail', index=False)

    # Access openpyxl workbook for formatting
    wb = writer.book
    ws = wb['Summary']
    # Apply formatting...

数据透视表

# Create pivot table
pivot = pd.pivot_table(
    df,
    values='Revenue',
    index='Department',
    columns='Quarter',
    aggfunc='sum',
    margins=True,
    margins_name='Total'
)

# Write to Excel with formatting
pivot.to_excel(writer, sheet_name='Pivot')

CSV 导入/导出

# CSV to XLSX
df = pd.read_csv('data.csv', encoding='utf-8-sig')
df.to_excel('output.xlsx', index=False)

# XLSX to CSV
df = pd.read_excel('data.xlsx')
df.to_csv('output.csv', index=False, encoding='utf-8-sig')

# Handle encoding issues
df = pd.read_csv('data.csv', encoding='latin-1')  # or 'cp1252'

处理大型文件

高效利用内存地读取

# openpyxl read-only mode
from openpyxl import load_workbook

wb = load_workbook('large_file.xlsx', read_only=True)
ws = wb.active

for row in ws.iter_rows(min_row=2, values_only=True):
    process_row(row)

wb.close()

分块写入

# Write large datasets in chunks
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows

wb = Workbook(write_only=True)
ws = wb.create_sheet()

# Write header
ws.append(headers)

# Write in chunks
chunk_size = 10000
for chunk in pd.read_csv('large.csv', chunksize=chunk_size):
    for row in dataframe_to_rows(chunk, index=False, header=False):
        ws.append(row)

wb.save('output.xlsx')

性能决策表

行数	策略	说明
< 10,000	标准 openpyxl 或 pandas	支持完整格式设置
10K - 100K	write_only / read_only 模式，分块处理	write_only 模式下格式设置有限
100K - 1M	write_only 模式，考虑改用 CSV	接近 Excel 行数上限
> 1M	使用 CSV 或 Parquet，而非 XLSX	Excel 上限：1,048,576 行

反模式 / 常见错误

反模式	失败原因	替代做法
使用 openpyxl 进行纯数据分析	用于分析时冗长且缓慢	使用 pandas 处理数据
将大型文件全部载入内存	内存耗尽、程序崩溃	使用 read_only / write_only 模式
将行号/列号硬编码	数据形状变化时会出错	根据数据长度计算
日期格式不一致	日期显示为数字或字符串	显式设置 number_format
未关闭 read_only 工作簿	资源泄漏	始终调用 wb.close() 或使用上下文管理器
使用 .xls 格式	过时、功能有限且存在安全风险	始终使用 .xlsx
逐个设置单元格格式	处理大范围数据时极其缓慢	对范围应用样式或使用命名样式
未在实际 Excel 中测试	功能的显示效果可能不同	在 Excel、LibreOffice 和 Google Sheets 中测试
忘记冻结表头行	滚动查看大量数据时体验不佳	始终为数据工作表冻结窗格

防止合理化的防护措施

• 不要在 pandas 一行就能完成的数据分析中使用 openpyxl。
• 不要跳过行数评估——它决定了你的整个处理方案。
• 对于超过 10K 行的文件，不要假设标准模式可用——请使用流式模式。
• 不要只在一种电子表格应用中测试——格式显示会有所不同。
• 不要忘记关闭以 read_only 模式打开的工作簿。

集成点

技能	关联方式
pdf-processing	Excel 数据用于生成 PDF 报告
docx-processing	Excel 数据用于填充 Word 文档表格
email-composer	生成的电子表格作为附件添加到专业邮件中
file-organizer	输出文件命名和目录结构规范
database-schema-design	将数据库导出到 Excel 以生成报告
deployment	在 CI/CD 管道中自动生成报告

技能类型

灵活——需要丰富格式时选择 openpyxl，进行数据分析时选择 pandas。需要根据数据分析结果生成格式化报告时，可结合使用两者。根据数据量调整文件处理策略。