Excel自动化编程指南：使用Python/Node.js创建编辑XLSX文件与数据分析

document-xlsx by vasilyu1983/ai-agents-public

190 周安装量

46 GitHub Stars

GitHub

安装命令

npx skills add https://github.com/vasilyu1983/ai-agents-public --skill document-xlsx

办公软件自动化数据处理

🇨🇳中文介绍

Document XLSX 技能 — 快速参考

此技能支持以编程方式创建、编辑和分析 Excel 电子表格。当用户需要生成数据报告、财务模型、自动化 Excel 工作流或处理电子表格数据时，Claude 应应用这些模式。

现代最佳实践（2026年1月）：

将电子表格视为软件：清晰的输入/输出、可审计性和版本控制。
保护数据完整性：控制总计、验证和可追溯至源头。
可访问性：标签、对比度、结构；使用 Excel 的可访问性检查器；在外部分发时满足采购/监管要求。
如果在欧盟或受监管的环境中分发，请遵循适用的可访问性要求（通常与 EN 301 549 / WCAG 一致）。
附带审查循环和所有者（避免"神秘模型"）。
安全性：将不受信任的输入/工作簿视为敌对（公式注入、外部链接、隐藏内容、宏）。

快速参考

任务	工具/库	语言	使用时机
创建 XLSX	ExcelJS	Node.js	报告、数据导出
创建 XLSX	openpyxl	Python	读取/写入、修改现有文件
创建 XLSX	XlsxWriter	Python	仅写入、丰富的格式、图表
数据分析	pandas + openpyxl	Python

广告位招租

在这里展示您的产品或服务

触达数万 AI 开发者，精准高效

联系我们

防护措施与注意事项

公式计算：库写入公式；Excel 在打开时计算结果。如果需要在服务器端计算值，请在代码中计算并写入值（或使用专用的公式引擎）。
数据透视表：程序化创建有限。如果确实需要原生数据透视表，首选 pandas 汇总（数据透视表作为数据）或 Excel 自动化（xlwings/Office Scripts/VBA）。
宏：openpyxl 可以保留现有的 VBA（keep_vba=True），但不能编写宏；切勿从未受信任的输入生成或执行宏。
电子表格注入：切勿将不受信任的字符串放入 formula 字段；将它们作为文本值写入，并验证/清理用于导出的用户提供数据。

创建电子表格 (Node.js - exceljs)

import ExcelJS from 'exceljs';

const workbook = new ExcelJS.Workbook();
const sheet = workbook.addWorksheet('Sales Report');

// 带样式的表头
sheet.columns = [
  { header: 'Product', key: 'product', width: 20 },
  { header: 'Quantity', key: 'qty', width: 12 },
  { header: 'Price', key: 'price', width: 12 },
  { header: 'Total', key: 'total', width: 15 },
];

// 样式化表头行
sheet.getRow(1).font = { bold: true };
sheet.getRow(1).fill = {
  type: 'pattern',
  pattern: 'solid',
  fgColor: { argb: 'FF4472C4' }
};

// 添加数据
const data = [
  { product: 'Widget A', qty: 100, price: 10 },
  { product: 'Widget B', qty: 50, price: 25 },
];

data.forEach((item, index) => {
  sheet.addRow({
    product: item.product,
    qty: item.qty,
    price: item.price,
    total: { formula: `B${index + 2}*C${index + 2}` }
  });
});

// 添加总计行
const lastRow = sheet.rowCount + 1;
sheet.addRow({
  product: 'TOTAL',
  total: { formula: `SUM(D2:D${lastRow - 1})` }
});

// 货币格式化
sheet.getColumn('price').numFmt = '$#,##0.00';
sheet.getColumn('total').numFmt = '$#,##0.00';

await workbook.xlsx.writeFile('report.xlsx');

创建电子表格 (Python - openpyxl)

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

wb = Workbook()
ws = wb.active
ws.title = 'Sales Report'

// 表头
headers = ['Product', 'Quantity', 'Price', 'Total']
for col, header in enumerate(headers, 1):
    cell = ws.cell(row=1, column=col, value=header)
    cell.font = Font(bold=True, color='FFFFFF')
    cell.fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')

// 数据
data = [
    ('Widget A', 100, 10),
    ('Widget B', 50, 25),
    ('Widget C', 75, 15),
]

for row_idx, (product, qty, price) in enumerate(data, 2):
    ws.cell(row=row_idx, column=1, value=product)
    ws.cell(row=row_idx, column=2, value=qty)
    ws.cell(row=row_idx, column=3, value=price)
    ws.cell(row=row_idx, column=4, value=f'=B{row_idx}*C{row_idx}')

// 总计行
total_row = len(data) + 2
ws.cell(row=total_row, column=1, value='TOTAL')
ws.cell(row=total_row, column=4, value=f'=SUM(D2:D{total_row-1})')

// 数字格式化
for row in range(2, total_row + 1):
    ws.cell(row=row, column=3).number_format = '$#,##0.00'
    ws.cell(row=row, column=4).number_format = '$#,##0.00'

wb.save('report.xlsx')

读取与分析 (Python - pandas)

import pandas as pd

// 读取 Excel 文件
df = pd.read_excel('data.xlsx', sheet_name='Sheet1')

// 分析
summary = df.groupby('Category').agg({
    'Sales': 'sum',
    'Quantity': 'mean'
}).round(2)

// 写入 Excel 并格式化
with pd.ExcelWriter('analysis.xlsx', engine='openpyxl') as writer:
    df.to_excel(writer, sheet_name='Raw Data', index=False)
    summary.to_excel(writer, sheet_name='Summary')

    // 自动调整列宽
    for sheet in writer.sheets.values():
        for column in sheet.columns:
            max_length = max(len(str(cell.value)) for cell in column)
            sheet.column_dimensions[column[0].column_letter].width = max_length + 2

添加图表 (Python)

from openpyxl.chart import BarChart, Reference

chart = BarChart()
chart.title = 'Sales by Product'
chart.x_axis.title = 'Product'
chart.y_axis.title = 'Sales'

// 数据范围（假设 D 列包含系列，第 1 行是表头）
max_row = ws.max_row
data_ref = Reference(ws, min_col=4, min_row=1, max_row=max_row, max_col=4)
categories = Reference(ws, min_col=1, min_row=2, max_row=max_row)

chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(categories)
chart.shape = 4

ws.add_chart(chart, 'F2')

from openpyxl.formatting.rule import ColorScaleRule, FormulaRule
from openpyxl.styles import PatternFill

// 色阶（热力图）
ws.conditional_formatting.add(
    'D2:D100',
    ColorScaleRule(
        start_type='min', start_color='FF0000',
        end_type='max', end_color='00FF00'
    )
)

// 高亮超过阈值的单元格
red_fill = PatternFill(start_color='FFCCCC', fill_type='solid')
ws.conditional_formatting.add(
    'D2:D100',
    FormulaRule(formula=['D2>1000'], fill=red_fill)
)

用途	公式	示例
求和	`=SUM(range)`	`=SUM(A1:A10)`
平均值	`=AVERAGE(range)`	`=AVERAGE(B2:B100)`
计数	`=COUNT(range)`	`=COUNT(C:C)`
条件求和	`=SUMIF(range,criteria,sum_range)`	`=SUMIF(A:A,"Widget",B:B)`
查找	`=VLOOKUP(value,range,col,FALSE)`	`=VLOOKUP(A2,Data!A:C,3,FALSE)`
条件判断	`=IF(condition,true,false)`	`=IF(B2>100,"High","Low")`
百分比	`=value/total`	`=B2/SUM(B:B)`

Excel Task: [What do you need?]
    ├─ Create new spreadsheet?
    │   ├─ Simple data export → pandas to_excel()
    │   ├─ Formatted report → exceljs or openpyxl
    │   └─ With charts → openpyxl charts module
    │
    ├─ Read/analyze existing?
    │   ├─ Data analysis → pandas read_excel()
    │   ├─ Preserve formatting → openpyxl load_workbook()
    │   └─ Fast parsing → xlsx (SheetJS)
    │
    ├─ Modify existing?
    │   ├─ Add data → openpyxl (preserves formatting)
    │   └─ Update formulas → openpyxl
    │
    └─ Complex features?
        ├─ Pivot tables → pandas summary tables or xlwings (native pivots)
        ├─ Data validation → openpyxl DataValidation
        └─ Macros → preserve only; use xlwings for Excel automation

应做 / 应避免（2026年1月）

分离输入 / 计算 / 输出（标签页或清晰的部分）。
明确假设（数值 + 单位 + 来源 + 日期）。
为导入的数据添加控制总计和对账检查。

在公式中硬编码常量而没有文档化的假设。
隐藏会改变结果但未记录的行/列。
共享包含客户 PII 或机密的表格。

结构：清晰的输入/假设、计算和输出分离（标签页或部分）。
完整性：没有 #REF!、损坏的命名范围或隐藏在公式中的硬编码常量。
可追溯性：每个关键输出都能追溯到标记的输入（单位 + 来源 + 日期）。
检查：控制总计、对账和能明显报错的错误标志。
审查：使用 assets/spreadsheet-model-review-checklist.md 进行独立审查。

可选：AI / 自动化

仅在明确请求且符合政策时使用。

生成第一轮公式/图表；人工验证正确性和边界情况。
起草文档标签页（假设、术语表）；不虚构源数据。

references/excel-formulas.md — 公式参考与模式
references/excel-formatting.md — 样式、条件格式
references/excel-charts.md — 图表类型与自定义
references/excel-data-validation.md — 下拉列表、输入约束、级联验证
references/excel-pivot-tables.md — 数据透视表变通方案、汇总模式、pandas
references/excel-security-protection.md — 工作表保护、防止公式注入
data/sources.json — 库文档链接

assets/financial-report.md — 财务报表模板
assets/data-dashboard.md — 带图表的仪表板
assets/spreadsheet-model-review-checklist.md — 模型质量保证检查清单（假设、公式、可追溯性）

../document-pdf/SKILL.md — 从数据生成 PDF
../ai-ml-data-science/SKILL.md — 数据分析模式
../data-sql-optimization/SKILL.md — 数据库到 Excel 的工作流

在最终答案前，使用网络搜索/网络抓取来验证当前的外部事实、版本、定价、截止日期、法规或平台行为。
优先使用原始来源；对于易变信息，报告来源链接和日期。
如果无法访问网络，请说明限制并将指导标记为未经验证。

🇺🇸English

Document XLSX Skill — Quick Reference

This skill enables creation, editing, and analysis of Excel spreadsheets programmatically. Claude should apply these patterns when users need to generate data reports, financial models, automate Excel workflows, or process spreadsheet data.

Modern Best Practices (Jan 2026) :

Treat spreadsheets as software: clear inputs/outputs, auditability, and versioning.
Protect data integrity: control totals, validation, and traceability to sources.
Accessibility: labels, contrast, structure; use Excel's Accessibility Checker; meet procurement/regulatory requirements when distributing externally.
If distributing in the EU or regulated contexts, follow applicable accessibility requirements (often aligned with EN 301 549 / WCAG).
Ship with a review loop and an owner (avoid "mystery models").
Security: treat untrusted input/workbooks as hostile (formula injection, external links, hidden content, macros).

Quick Reference

Task	Tool/Library	Language	When to Use
Create XLSX	ExcelJS	Node.js	Reports, data exports
Create XLSX	openpyxl	Python	Read/write, modify existing files
Create XLSX	XlsxWriter	Python	Write-only, rich formatting, charts
Data analysis	pandas + openpyxl	Python	DataFrame to Excel with formatting
Read XLSX	xlsx (SheetJS)	Node.js	Parse spreadsheets
Charts	openpyxl/XlsxWriter	Python	Embedded visualizations
Styling	ExcelJS/openpyxl	Both	Conditional formatting
Automation	xlwings	Python	Excel installed, interactive workflows

Guardrails and Caveats

Formula calculation: libraries write formulas; Excel computes results when opened. If you need computed values server-side, calculate in code and write values (or use a dedicated formula engine).
Pivot tables: programmatic creation is limited. Prefer pandas summaries (pivot tables as data) or Excel automation (xlwings/Office Scripts/VBA) if you truly need native pivots.
Macros: openpyxl can preserve existing VBA (keep_vba=True) but does not author macros; never generate or execute macros from untrusted input.
Spreadsheet injection: never put untrusted strings into formula fields; write them as text values and validate/sanitize user-provided data used in exports.

Core Operations

Create Spreadsheet (Node.js - exceljs)

import ExcelJS from 'exceljs';

const workbook = new ExcelJS.Workbook();
const sheet = workbook.addWorksheet('Sales Report');

// Headers with styling
sheet.columns = [
  { header: 'Product', key: 'product', width: 20 },
  { header: 'Quantity', key: 'qty', width: 12 },
  { header: 'Price', key: 'price', width: 12 },
  { header: 'Total', key: 'total', width: 15 },
];

// Style header row
sheet.getRow(1).font = { bold: true };
sheet.getRow(1).fill = {
  type: 'pattern',
  pattern: 'solid',
  fgColor: { argb: 'FF4472C4' }
};

// Add data
const data = [
  { product: 'Widget A', qty: 100, price: 10 },
  { product: 'Widget B', qty: 50, price: 25 },
];

data.forEach((item, index) => {
  sheet.addRow({
    product: item.product,
    qty: item.qty,
    price: item.price,
    total: { formula: `B${index + 2}*C${index + 2}` }
  });
});

// Add totals row
const lastRow = sheet.rowCount + 1;
sheet.addRow({
  product: 'TOTAL',
  total: { formula: `SUM(D2:D${lastRow - 1})` }
});

// Currency formatting
sheet.getColumn('price').numFmt = '$#,##0.00';
sheet.getColumn('total').numFmt = '$#,##0.00';

await workbook.xlsx.writeFile('report.xlsx');

Create Spreadsheet (Python - openpyxl)

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

wb = Workbook()
ws = wb.active
ws.title = 'Sales Report'

# Headers
headers = ['Product', 'Quantity', 'Price', 'Total']
for col, header in enumerate(headers, 1):
    cell = ws.cell(row=1, column=col, value=header)
    cell.font = Font(bold=True, color='FFFFFF')
    cell.fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')

# Data
data = [
    ('Widget A', 100, 10),
    ('Widget B', 50, 25),
    ('Widget C', 75, 15),
]

for row_idx, (product, qty, price) in enumerate(data, 2):
    ws.cell(row=row_idx, column=1, value=product)
    ws.cell(row=row_idx, column=2, value=qty)
    ws.cell(row=row_idx, column=3, value=price)
    ws.cell(row=row_idx, column=4, value=f'=B{row_idx}*C{row_idx}')

# Totals row
total_row = len(data) + 2
ws.cell(row=total_row, column=1, value='TOTAL')
ws.cell(row=total_row, column=4, value=f'=SUM(D2:D{total_row-1})')

# Number formatting
for row in range(2, total_row + 1):
    ws.cell(row=row, column=3).number_format = '$#,##0.00'
    ws.cell(row=row, column=4).number_format = '$#,##0.00'

wb.save('report.xlsx')

Read and Analyze (Python - pandas)

import pandas as pd

# Read Excel file
df = pd.read_excel('data.xlsx', sheet_name='Sheet1')

# Analysis
summary = df.groupby('Category').agg({
    'Sales': 'sum',
    'Quantity': 'mean'
}).round(2)

# Write to Excel with formatting
with pd.ExcelWriter('analysis.xlsx', engine='openpyxl') as writer:
    df.to_excel(writer, sheet_name='Raw Data', index=False)
    summary.to_excel(writer, sheet_name='Summary')

    # Auto-adjust column widths
    for sheet in writer.sheets.values():
        for column in sheet.columns:
            max_length = max(len(str(cell.value)) for cell in column)
            sheet.column_dimensions[column[0].column_letter].width = max_length + 2

Add Charts (Python)

from openpyxl.chart import BarChart, Reference

chart = BarChart()
chart.title = 'Sales by Product'
chart.x_axis.title = 'Product'
chart.y_axis.title = 'Sales'

# Data range (assumes column D contains the series and row 1 is headers)
max_row = ws.max_row
data_ref = Reference(ws, min_col=4, min_row=1, max_row=max_row, max_col=4)
categories = Reference(ws, min_col=1, min_row=2, max_row=max_row)

chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(categories)
chart.shape = 4

ws.add_chart(chart, 'F2')

Conditional Formatting

from openpyxl.formatting.rule import ColorScaleRule, FormulaRule
from openpyxl.styles import PatternFill

# Color scale (heatmap)
ws.conditional_formatting.add(
    'D2:D100',
    ColorScaleRule(
        start_type='min', start_color='FF0000',
        end_type='max', end_color='00FF00'
    )
)

# Highlight cells above threshold
red_fill = PatternFill(start_color='FFCCCC', fill_type='solid')
ws.conditional_formatting.add(
    'D2:D100',
    FormulaRule(formula=['D2>1000'], fill=red_fill)
)

Common Formulas Reference

Purpose	Formula	Example
Sum	`=SUM(range)`	`=SUM(A1:A10)`
Average	`=AVERAGE(range)`	`=AVERAGE(B2:B100)`
Count	`=COUNT(range)`	`=COUNT(C:C)`
Conditional sum	`=SUMIF(range,criteria,sum_range)`

Decision Tree

Excel Task: [What do you need?]
    ├─ Create new spreadsheet?
    │   ├─ Simple data export → pandas to_excel()
    │   ├─ Formatted report → exceljs or openpyxl
    │   └─ With charts → openpyxl charts module
    │
    ├─ Read/analyze existing?
    │   ├─ Data analysis → pandas read_excel()
    │   ├─ Preserve formatting → openpyxl load_workbook()
    │   └─ Fast parsing → xlsx (SheetJS)
    │
    ├─ Modify existing?
    │   ├─ Add data → openpyxl (preserves formatting)
    │   └─ Update formulas → openpyxl
    │
    └─ Complex features?
        ├─ Pivot tables → pandas summary tables or xlwings (native pivots)
        ├─ Data validation → openpyxl DataValidation
        └─ Macros → preserve only; use xlwings for Excel automation

Do / Avoid (Jan 2026)

Do

Separate Inputs / Calculations / Outputs (tabs or clear sections).
Keep assumptions explicit (value + unit + source + date).
Add control totals and reconciliation checks for imported data.

Avoid

Hardcoded constants inside formulas without a documented assumption.
Hidden rows/columns that change results without documentation.
Sharing sheets with customer PII or secrets.

What Good Looks Like

Structure: clear Inputs/Assumptions, Calculations, and Outputs separation (tabs or sections).
Integrity: no #REF!, broken named ranges, or hardcoded constants hidden in formulas.
Traceability: every key output ties back to labeled inputs (units + source + date).
Checks: control totals, reconciliations, and error flags that fail loudly.
Review: independent review pass using assets/spreadsheet-model-review-checklist.md.

Optional: AI / Automation

Use only when explicitly requested and policy-compliant.

Generate first-pass formulas/charts; humans verify correctness and edge cases.
Draft documentation tabs (assumptions, glossary); do not invent source data.

Navigation

Resources

references/excel-formulas.md — Formula reference and patterns
references/excel-formatting.md — Styling, conditional formatting
references/excel-charts.md — Chart types and customization
references/excel-data-validation.md — Dropdowns, input constraints, cascading validation
references/excel-pivot-tables.md — Pivot workarounds, summary patterns, pandas
references/excel-security-protection.md — Sheet protection, formula injection prevention
data/sources.json — Library documentation links

Templates

assets/financial-report.md — Financial statement template
assets/data-dashboard.md — Dashboard with charts
assets/spreadsheet-model-review-checklist.md — Model QA checklist (assumptions, formulas, traceability)

Related Skills

../document-pdf/SKILL.md — PDF generation from data
../ai-ml-data-science/SKILL.md — Data analysis patterns
../data-sql-optimization/SKILL.md — Database to Excel workflows

Fact-Checking

Use web search/web fetch to verify current external facts, versions, pricing, deadlines, regulations, or platform behavior before final answers.
Prefer primary sources; report source links and dates for volatile information.
If web access is unavailable, state the limitation and mark guidance as unverified.

Weekly Installs

190

Repository

vasilyu1983/ai-…s-public

GitHub Stars

First Seen

Jan 23, 2026

Security Audits

Gen Agent Trust HubPass SocketPass SnykPass

Installed on

gemini-cli168

opencode167

cursor164

codex163

github-copilot156

cline148

通过 LiteLLM 代理让 Claude Code 对接 GitHub Copilot 运行 | 高级变通方案指南

31,600 周安装