名称: xlsx 描述: “全面的Excel电子表格创建、编辑和分析功能，支持公式、格式化、图表、数据分析和可视化。适用于处理.xlsx、.xlsm、.csv文件，用于：(1) 创建带有公式和格式的电子表格，(2) 读取/分析数据，(3) 修改现有电子表格同时保留公式，(4) 创建图表和可视化，(5) 数据转换和分析，(6) 多工作表操作”

Excel (XLSX) 技能

概述

此技能提供了使用Python编程方式处理Excel电子表格的全面能力。涵盖从基本文件操作到高级数据分析、公式管理、图表创建和格式化的所有内容。

主要库是用于完整Excel文件操作的openpyxl，辅以用于数据分析任务的pandas。

核心能力

1. 文件操作

读取: 加载.xlsx、.xlsm和.csv文件
写入: 从头创建新的Excel工作簿
编辑: 修改现有工作簿，同时保留公式、格式和图表
转换: 在CSV、Excel和其他格式之间转换

2. 数据管理

单元格操作: 读取、写入和修改单个单元格或范围
公式: 创建和管理Excel公式（SUM、VLOOKUP、INDEX/MATCH等）
数据验证: 设置下拉列表、数值范围、日期约束
命名范围: 定义和使用命名的单元格范围，以便更轻松地管理公式

3. 格式化

单元格样式: 字体、颜色、边框、对齐方式、数字格式
条件格式化: 应用基于规则的格式化
行/列尺寸: 设置宽度、高度、自动调整
合并单元格: 合并单元格用于标题和标签

4. 图表与可视化

图表类型: 折线图、条形图、柱形图、饼图、散点图、面积图、组合图
图表自定义: 标题、图例、数据标签、颜色
多系列: 具有次要轴的多数据集图表
图表定位: 将图表放置在特定位置

5. 多工作表操作

工作表管理: 创建、重命名、删除、重新排序工作表
跨工作表公式: 跨多个工作表引用数据
工作表复制: 复制工作表并保持格式完整
工作表保护: 锁定/解锁工作表和范围

6. 数据分析

筛选: 自动筛选数据范围
排序: 多级排序
数据透视表: 编程方式创建数据透视表
统计函数: 内置和自定义计算

安装

# 主要库
pip install openpyxl

# 用于数据分析
pip install pandas openpyxl

# 或使用uv
uv pip install openpyxl pandas

基本工作流程

工作流程1: 从头创建新工作簿

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

# 创建新工作簿
wb = Workbook()
ws = wb.active
ws.title = "销售报告"

# 添加带格式的标题
headers = ["产品", "第一季度", "第二季度", "第三季度", "第四季度", "总计"]
header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
header_font = Font(color="FFFFFF", bold=True)

for col, header in enumerate(headers, start=1):
    cell = ws.cell(row=1, column=col, value=header)
    cell.fill = header_fill
    cell.font = header_font
    cell.alignment = Alignment(horizontal="center")

# 添加数据
data = [
    ["产品A", 1000, 1200, 1100, 1300],
    ["产品B", 800, 900, 950, 1000],
    ["产品C", 1500, 1400, 1600, 1700]
]

for row_idx, row_data in enumerate(data, start=2):
    for col_idx, value in enumerate(row_data, start=1):
        ws.cell(row=row_idx, column=col_idx, value=value)

# 为总计添加公式
for row in range(2, len(data) + 2):
    formula = f"=SUM(B{row}:E{row})"
    ws.cell(row=row, column=6, value=formula)

# 调整列宽
for col in range(1, 7):
    ws.column_dimensions[get_column_letter(col)].width = 12

# 保存工作簿
wb.save("sales_report.xlsx")

工作流程2: 读取和分析现有工作簿

from openpyxl import load_workbook

# 加载现有工作簿
wb = load_workbook('data.xlsx', data_only=True)  # data_only=True 会计算公式结果
ws = wb.active

# 方法1: 遍历所有行
for row in ws.iter_rows(min_row=2, values_only=True):
    print(row)

# 方法2: 读取特定单元格
value = ws['A1'].value
value = ws.cell(row=2, column=2).value

# 方法3: 读取范围
for row in ws['B2':'D5']:
    for cell in row:
        print(cell.value, end=' ')
    print()

# 计算统计信息
values = [cell.value for cell in ws['B'][1:] if isinstance(cell.value, (int, float))]
if values:
    print(f"总和: {sum(values)}")
    print(f"平均值: {sum(values) / len(values):.2f}")

wb.close()

工作流程3: 编辑工作簿同时保留公式

from openpyxl import load_workbook
from openpyxl.styles import Font

# 加载工作簿时不要使用data_only以保留公式
wb = load_workbook('existing_report.xlsx')
ws = wb['Sales']

# 更新值（在Excel中打开时公式会重新计算）
ws['B2'] = 1500
ws['C2'] = 1650

# 添加带数据和公式的新行
new_row = ws.max_row + 1
ws[f'A{new_row}'] = "产品D"
ws[f'B{new_row}'] = 900
ws[f'C{new_row}'] = 1000
ws[f'D{new_row}'] = 1100
ws[f'E{new_row}'] = 1200
ws[f'F{new_row}'] = f"=SUM(B{new_row}:E{new_row})"  # 添加公式

# 对新行应用格式
for col in range(1, 7):
    cell = ws.cell(row=new_row, column=col)
    if col == 1:
        cell.font = Font(bold=True)

# 保存更改
wb.save('existing_report.xlsx')

工作流程4: 使用Pandas进行数据分析

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill

# 步骤1: 使用pandas读取和分析数据
df = pd.read_excel('sales_data.xlsx')

# 执行分析
summary = df.groupby('Product').agg({
    'Sales': ['sum', 'mean', 'count'],
    'Profit': 'sum'
}).round(2)

summary.columns = ['总销售额', '平均销售额', '交易次数', '总利润']

# 步骤2: 将结果写入新的Excel文件
with pd.ExcelWriter('sales_analysis.xlsx', engine='openpyxl') as writer:
    df.to_excel(writer, sheet_name='原始数据', index=False)
    summary.to_excel(writer, sheet_name='摘要')

# 步骤3: 使用openpyxl增强格式
wb = load_workbook('sales_analysis.xlsx')
ws = wb['摘要']

header_fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid")
header_font = Font(color="FFFFFF", bold=True)

for cell in ws[1]:
    cell.fill = header_fill
    cell.font = header_font

wb.save('sales_analysis.xlsx')

关键原则

公式管理

始终使用公式进行计算，而不是硬编码值
当源数据更改时，公式会自动更新
对复杂公式使用命名范围以提高可读性
如果需要保留公式，加载文件时不要使用data_only=True

性能优化

对于大型数据集: 使用ws.append()批量写入行
对于读取大文件: 使用read_only=True模式
对于写入大文件: 使用write_only=True模式
避免在嵌套循环中进行逐个单元格操作

内存管理

使用后关闭工作簿: wb.close()
对大文件使用read_only/write_only模式
对于非常大的数据集，分块处理数据

错误处理

始终对文件操作使用try/except块
在处理前检查空单元格
在计算前验证数据类型
处理损坏文件的InvalidFileException

日期和时间

使用datetime对象表示日期，而不是字符串
应用正确的数字格式: cell.number_format = 'mm/dd/yyyy'
Excel在内部将日期存储为数字

快速参考

基本操作

from openpyxl import Workbook, load_workbook

# 创建工作簿
wb = Workbook()
ws = wb.active

# 读取单元格
value = ws['A1'].value
value = ws.cell(row=1, column=1).value

# 写入单元格
ws['A1'] = "Hello"
ws.cell(row=1, column=1, value="Hello")

# 写入公式
ws['C1'] = "=A1+B1"

# 添加行
ws.append([1, 2, 3])

# 保存并关闭
wb.save('output.xlsx')
wb.close()

常用导入

from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
from openpyxl.chart import LineChart, BarChart, PieChart, Reference
from openpyxl.utils import get_column_letter
from openpyxl.data_validation import DataValidation
from openpyxl.formatting.rule import ColorScaleRule, CellIsRule

常见用例

创建图表

向电子表格添加可视化。有关完整的图表创建工作流程，包括折线图、条形图和饼图，请参阅examples/workflow-examples.md。

条件格式化

根据单元格值应用视觉格式。有关色阶、图标集和基于规则的格式化，请参阅examples/workflow-examples.md。

数据验证

创建下拉列表和输入约束。有关下拉列表、数值范围和日期验证，请参阅examples/workflow-examples.md。

多工作表工作簿

处理多个工作表和跨工作表公式。有关完整的多工作表工作流程，请参阅examples/workflow-examples.md。

财务报告

创建专业的财务报表。有关带有动态公式的完整损益表示例，请参阅examples/financial-report.md。

数据转换

将CSV数据转换为格式化的Excel报告。有关pandas集成和数据透视表创建，请参阅examples/data-transformation.md。

仪表板

构建带有多个图表的执行仪表板。有关包含KPI和可视化的综合仪表板，请参阅examples/dashboard-creation.md。

辅助脚本

scripts/目录提供了常见操作的实用函数：

from scripts.excel_helper import (
    create_workbook,
    read_excel_data,
    add_chart,
    apply_formatting,
    add_formula,
    auto_fit_columns
)

# 使用数据创建新工作簿
wb, ws = create_workbook("销售报告", headers=["产品", "第一季度", "第二季度"])

# 从现有文件读取数据
data = read_excel_data("data.xlsx", sheet_name="Sheet1")

# 向工作表添加图表
add_chart(ws, chart_type="line", data_range="B2:D10", title="销售趋势")

# 应用格式
apply_formatting(ws, cell_range="A1:D1", bold=True, bg_color="4472C4")

# 向范围添加公式
add_formula(ws, cell="E2", formula="=SUM(B2:D2)", copy_down=10)

# 自动调整所有列
auto_fit_columns(ws)

wb.save("output.xlsx")

附加资源

详细文档

库参考: 有关完整的openpyxl、pandas和xlsxwriter文档，请参阅references/library-reference.md
最佳实践: 有关性能优化、错误处理和常见陷阱，请参阅references/best-practices.md

完整示例

工作流程示例: examples/workflow-examples.md - 图表、条件格式化、数据验证、多工作表操作
财务报告: examples/financial-report.md - 带有动态公式的损益表
数据转换: examples/data-transformation.md - 使用pandas集成的CSV转Excel
仪表板创建: examples/dashboard-creation.md - 带有KPI的多图表仪表板

外部链接

openpyxl文档: https://openpyxl.readthedocs.io/
pandas Excel支持: https://pandas.pydata.org/docs/reference/io.html#excel
Excel公式参考: https://support.microsoft.com/en-us/excel

总结

此技能实现了全面的Excel自动化，包括：

创建带有公式和格式的复杂电子表格
读取和分析现有工作簿
编辑文件同时保留公式和样式
创建专业的图表和可视化
应用条件格式化和数据验证
处理多个工作表和跨工作表公式
与pandas集成进行高级数据分析
高效处理大型数据集

将此技能用于任何涉及Excel文件的任务，从简单的数据输入到复杂的财务报告和仪表板。