home / skills / enoch-robinson / agent-skill-collection / xlsx

xlsx skill

safe

This skill helps you read, create, edit, and format Excel files using pandas and openpyxl for data analysis and financial modeling.

npx playbooks add skill enoch-robinson/agent-skill-collection --skill xlsx

Review the files below or copy the command above to add this skill to your agents.

Files (1)

SKILL.md

2.6 KB

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

# XLSX Processing Guide

## 库选择

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

## 读取数据 (pandas)

```python
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)

```python
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')
```

## 编辑现有文件

```python
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')
```

## 关键原则

###✅ 使用公式
```python
# 正确：让Excel 计算
sheet['B10'] = '=SUM(B2:B9)'

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

### 金融模型颜色规范
| 颜色 | 用途 |
|------|------|
| 蓝色文字 | 硬编码输入值 |
| 黑色文字 | 公式和计算 |
| 绿色文字 | 跨 sheet 引用 |
| 黄色背景 | 需要关注的假设 |

## 数据导出

```python
# 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）

Overview

This skill provides a practical toolkit for creating, reading, and editing .xlsx and .csv spreadsheets using Python. It combines pandas for fast tabular data operations and openpyxl for preserving formulas, styles, and workbook structure. Use it to build reports, financial models, and automated Excel workflows with correct formula handling and styling conventions.

How this skill works

The skill reads sheets into pandas DataFrames for analysis and bulk transformations, and writes DataFrames back to Excel files or multiple sheets. For cell-level operations, formula insertion, styling, and structural edits (insert/delete rows or sheets), it uses openpyxl so formulas remain in the workbook. It also supports read-only or data-only modes for large files and performance-sensitive tasks.

When to use it

Automate report generation from data sources into styled Excel workbooks
Build financial models that must keep native Excel formulas and color/code conventions
Bulk-transform or analyze multi-sheet workbooks using pandas
Edit existing .xlsx files to insert rows/columns, change values, or add sheets
Export DataFrames to multi-sheet workbooks for handoff to non-Python users

Best practices

Prefer inserting Excel formulas (e.g., '=SUM(B2:B9)') instead of computing values in Python so spreadsheets remain auditable
Use pandas for reading/writing bulk data and openpyxl for formatting, formulas, and per-cell edits
Open large files with read_only=True or use write_only=True to reduce memory usage
When reading computed results without formulas, set data_only=True but be aware saving will drop formulas
Adopt color conventions in models (blue=inputs, black=formulas, yellow=key assumptions) to improve clarity

Example use cases

Generate monthly sales reports: aggregate data with pandas and write a styled multi-sheet workbook with charts using openpyxl
Create a budget or forecasting model that stores formulas in cells and highlights assumptions with yellow fills
Clean and normalize CSV imports, then export to .xlsx with one sheet per region or department
Patch an existing workbook: insert rows, update headers, and preserve formulas across the file
Export multiple DataFrames to a single workbook for distribution to stakeholders

FAQ

Will formulas remain if I edit an Excel file with this skill?

Yes—use openpyxl to write formulas into cells so the workbook stores them. Avoid reading with data_only=True if you need to preserve formulas on save.

When should I use pandas vs openpyxl?

Use pandas for fast, bulk read/write and analysis. Use openpyxl for per-cell formatting, formulas, charts, and structural edits that must be preserved in the .xlsx file.