xlsx

enoch-robinson's avatarfrom enoch-robinson

Excel 电子表格处理工具包。用于创建和编辑电子表格、数据分析、公式计算、格式化。当需要处理 .xlsx/.csv 文件进行数据操作、报表生成或财务建模时使用此技能。

⭐0stars🔀0forks📁View on GitHub🕐Updated Jan 10, 2026
[Spreadsheets]

When & Why to Use This Skill

This Claude skill provides a comprehensive toolkit for Excel spreadsheet processing, enabling automated creation, editing, and advanced data analysis. It leverages powerful Python libraries like pandas and openpyxl to handle .xlsx and .csv files, supporting complex formula integration, professional formatting, and standardized financial modeling. It is designed to bridge the gap between raw data processing and presentation-ready reporting.

Use Cases

  • Automated Financial Reporting: Generate professional monthly financial statements with dynamic formulas and standardized color-coding for inputs and calculations.
  • Large-scale Data Cleaning and Transformation: Use pandas to ingest multiple data sources, perform batch cleaning, and export consolidated, structured reports.
  • Dynamic Template Generation: Create customized Excel files from scratch with specific cell styles, column widths, and branding for business workflows.
  • Formula-Driven Data Modeling: Build interactive spreadsheets where results are calculated via native Excel formulas rather than hard-coded values, ensuring the output remains functional for end-users.
namexlsx
descriptionExcel 电子表格处理工具包。用于创建和编辑电子表格、数据分析、公式计算、格式化。当需要处理 .xlsx/.csv 文件进行数据操作、报表生成或财务建模时使用此技能。

XLSX Processing Guide

库选择

任务 推荐库 用途
数据分析 pandas 读写、分析、批量操作
公式/格式 openpyxl 保留公式、样式、图表

读取数据 (pandas)

import pandas as pd

# 读取 Excel
df = pd.read_excel('file.xlsx')  # 默认第一个 sheet
df = pd.read_excel('file.xlsx', sheet_name='Sheet2')
all_sheets = pd.read_excel('file.xlsx', sheet_name=None)  # 所有 sheet

# 基础分析
df.head()       # 预览
df.info()       # 列信息
df.describe()   # 统计摘要

创建 Excel (openpyxl)

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

wb = Workbook()
sheet = wb.active

# 添加数据
sheet['A1'] = '标题'
sheet['B1'] = 100
sheet.append(['行', '数据', '示例'])

# 添加公式（重要：使用公式而非硬编码值）
sheet['B5'] = '=SUM(B2:B4)'
sheet['C5'] = '=AVERAGE(C2:C4)'

# 格式化
sheet['A1'].font = Font(bold=True, color='FF0000')
sheet['A1'].fill = PatternFill('solid', fgColor='FFFF00')
sheet['A1'].alignment = Alignment(horizontal='center')

# 列宽
sheet.column_dimensions['A'].width = 20

wb.save('output.xlsx')

编辑现有文件

from openpyxl import load_workbook

wb = load_workbook('existing.xlsx')
sheet = wb.active

# 修改单元格
sheet['A1'] = '新值'

# 插入/删除行列
sheet.insert_rows(2)
sheet.delete_cols(3)

# 新建sheet
new_sheet = wb.create_sheet('NewSheet')

wb.save('modified.xlsx')

关键原则

###✅ 使用公式

# 正确：让Excel 计算
sheet['B10'] = '=SUM(B2:B9)'

# 错误：Python 计算后硬编码
total = sum(values)
sheet['B10'] = total  # 不要这样做

金融模型颜色规范

颜色 用途
蓝色文字 硬编码输入值
黑色文字 公式和计算
绿色文字 跨 sheet 引用
黄色背景 需要关注的假设

数据导出

# DataFrame导出
df.to_excel('output.xlsx', index=False)

# 多sheet 导出
with pd.ExcelWriter('output.xlsx') as writer:
    df1.to_excel(writer, sheet_name='Sheet1')
    df2.to_excel(writer, sheet_name='Sheet2')

注意事项

  • data_only=True 读取计算值，但保存后公式会丢失
  • 大文件使用 read_only=True 或 write_only=True
  • 单元格索引从 1 开始（A1 = row=1, column=1）
xlsx – AI Agent Skills | Claude Skills