Back to Details

excel-mcp

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Excel MCP Server Skill

Excel MCP Server Skill

Provides 200+ Excel operations via Model Context Protocol. Tools are auto-discovered - this documents quirks, workflows, and gotchas.
通过Model Context Protocol提供200+种Excel操作。工具会被自动发现——本文档介绍相关特性、工作流和注意事项。

Preconditions

前置条件

  • Windows host with Microsoft Excel installed (2016+)
  • Use full Windows paths: 
    C:\Users\Name\Documents\Report.xlsx
  • Excel files must not be open in another Excel instance
  • 安装了Microsoft Excel 2016及以上版本的Windows主机
  • 使用完整的Windows路径：
    C:\Users\Name\Documents\Report.xlsx
  • Excel文件不得在其他Excel实例中打开

Calculation Mode Workflow (Batch Performance)

计算模式工作流（批量性能优化）

Use 
excel_calculation_mode
for bulk write performance optimization. When writing many values or formulas, disable auto-recalc to avoid recalculating after every cell:
1. excel_calculation_mode(action: 'set-mode', mode: 'manual')  → Disable auto-recalc
2. Perform all writes (excel_range set-values, set-formulas)
3. excel_calculation_mode(action: 'calculate', scope: 'workbook')  → Recalculate once
4. excel_calculation_mode(action: 'set-mode', mode: 'automatic')  → Restore default
Note: You do NOT need manual mode to read formulas - 
excel_range get-formulas
returns formula text regardless of calculation mode.
使用
excel_calculation_mode
来优化批量写入性能。当写入大量值或公式时，禁用自动重计算以避免每次单元格写入后都进行重计算：
1. excel_calculation_mode(action: 'set-mode', mode: 'manual')  → 禁用自动重计算
2. 执行所有写入操作（excel_range set-values、set-formulas）
3. excel_calculation_mode(action: 'calculate', scope: 'workbook')  → 一次性重计算
4. excel_calculation_mode(action: 'set-mode', mode: 'automatic')  → 恢复默认设置
注意： 读取公式时无需使用手动模式——
excel_range get-formulas
会直接返回公式文本，不受计算模式影响。

CRITICAL: Execution Rules (MUST FOLLOW)

重要：执行规则（必须遵守）

Rule 1: NEVER Ask Clarifying Questions

规则1：绝不询问澄清问题

STOP. If you're about to ask "Which file?", "What table?", "Where should I put this?" - DON'T.
Bad (Asking)Good (Discovering)
"Which Excel file should I use?"
excel_file(list)
→ use the open session
"What's the table name?"
excel_table(list)
→ discover tables
"Which sheet has the data?"
excel_worksheet(list)
→ check all sheets
"Should I create a PivotTable?"YES - create it on a new sheet
You have tools to answer your own questions. USE THEM.
停止。 如果你打算问“使用哪个文件？”、“什么表格？”、“应该放在哪里？”——不要问。
错误做法（询问）正确做法（自行发现）
“应该使用哪个Excel文件？”
excel_file(list)
→ 使用已打开的会话
“表格名称是什么？”
excel_table(list)
→ 发现表格
“数据在哪个工作表上？”
excel_worksheet(list)
→ 检查所有工作表
“我应该创建数据透视表吗？”是——在新工作表上创建
你有工具可以自行解答问题。请使用它们。

Rule 2: Format Data Professionally

规则2：专业格式化数据

Always apply number formats after setting values:
Data TypeFormat CodeResult
USD
$#,##0.00
$1,234.56
EUR
€#,##0.00
€1,234.56
Percent
0.00%
15.00%
Date (ISO)
yyyy-mm-dd
2025-01-22
Workflow:
1. excel_range set-values (data is now in cells)
2. excel_range_format set-number-format (apply format)
设置值后始终应用数字格式：
数据类型格式代码结果
美元
$#,##0.00
$1,234.56
欧元
€#,##0.00
€1,234.56
百分比
0.00%
15.00%
日期（ISO格式）
yyyy-mm-dd
2025-01-22
工作流：
1. excel_range set-values（数据已写入单元格）
2. excel_range_format set-number-format（应用格式）

Rule 3: Use Excel Tables (Not Plain Ranges)

规则3：使用Excel表格（而非普通单元格区域）

Always convert tabular data to Excel Tables:
1. excel_range set-values (write data including headers)
2. excel_table create tableName="SalesData" rangeAddress="A1:D100"
Why: Structured references, auto-expand, required for Data Model/DAX.
始终将表格数据转换为Excel表格：
1. excel_range set-values（写入包含表头的数据）
2. excel_table create tableName="SalesData" rangeAddress="A1:D100"
原因： 结构化引用、自动扩展，是Data Model/DAX的必要条件。

Rule 4: Session Lifecycle

规则4：会话生命周期

1. excel_file(action: 'open', excelPath: '...')  → sessionId
2. All operations use sessionId
3. excel_file(action: 'close', save: true)  → saves and closes
Unclosed sessions leave Excel processes running, locking files.
1. excel_file(action: 'open', excelPath: '...')  → 获取sessionId
2. 所有操作均使用sessionId
3. excel_file(action: 'close', save: true)  → 保存并关闭
未关闭的会话会导致Excel进程持续运行，锁定文件。

Rule 5: Data Model Prerequisites

规则5：Data Model前置条件

DAX operations require tables in the Data Model:
Step 1: Create table → Table exists
Step 2: excel_table(action: 'add-to-datamodel') → Table in Data Model
Step 3: excel_datamodel(action: 'create-measure') → NOW this works
DAX操作要求表格已加入Data Model：
步骤1：创建表格 → 表格已存在
步骤2：excel_table(action: 'add-to-datamodel') → 将表格加入Data Model
步骤3：excel_datamodel(action: 'create-measure') → 此时该操作才可正常执行

Rule 6: Power Query Development Lifecycle

规则6：Power Query开发生命周期

BEST PRACTICE: Test-First Workflow
1. excel_powerquery(action: 'evaluate', mCode: '...') → Test WITHOUT persisting
2. excel_powerquery(action: 'create', ...) → Store validated query
3. excel_powerquery(action: 'refresh', ...) → Load data
Why evaluate first:
  • Catches syntax errors and missing sources BEFORE creating permanent queries
  • Better error messages than COM exceptions from create/update
  • See actual data preview (columns + sample rows)
  • No cleanup needed - like a REPL for M code
  • Skip only for trivial literal tables
Common mistake: Creating/updating without evaluate → pollutes workbook with broken queries
最佳实践：先测试再落地的工作流
1. excel_powerquery(action: 'evaluate', mCode: '...') → 测试但不持久化
2. excel_powerquery(action: 'create', ...) → 存储验证后的查询
3. excel_powerquery(action: 'refresh', ...) → 加载数据
先测试的原因：
  • 在创建永久查询之前捕获语法错误和缺失的数据源
  • 比create/update操作触发的COM异常提供更清晰的错误信息
  • 查看实际数据预览（列+示例行）
  • 无需清理——类似于M代码的REPL环境
  • 仅在处理简单字面量表时可跳过此步骤
常见错误： 未先测试就创建/更新查询 → 导致工作簿中充斥无效查询

Rule 7: Targeted Updates Over Delete-Rebuild

规则7：优先针对性更新而非删除重建

  • Prefer: 
    set-values
    on specific range (e.g., 
    A5:C5
    for row 5)
  • Avoid: Deleting and recreating entire structures
Why: Preserves formatting, formulas, and references.
  • 推荐：对特定区域执行
    set-values
    操作（例如，对第5行使用
    A5:C5
    ）
  • 避免：删除并重新创建整个结构
原因： 保留格式、公式和引用关系。

Rule 8: Follow suggestedNextActions

规则8：遵循suggestedNextActions（建议后续操作）

Error responses include actionable hints:
json
{
  "success": false,
  "errorMessage": "Table 'Sales' not found in Data Model",
  "suggestedNextActions": ["excel_table(action: 'add-to-datamodel', tableName: 'Sales')"]
}
错误响应包含可执行的提示：
json
{
  "success": false,
  "errorMessage": "Table 'Sales' not found in Data Model",
  "suggestedNextActions": ["excel_table(action: 'add-to-datamodel', tableName: 'Sales')"]
}

Rule 9: Use Calculation Mode for Bulk Write Performance

规则9：使用计算模式优化批量写入性能

When writing many values/formulas (10+ cells), use 
excel_calculation_mode
to avoid recalculating after every write:
1. excel_calculation_mode(action: 'set-mode', mode: 'manual')  → Disable auto-recalc
2. Perform data writes (excel_range set-values, set-formulas)
3. excel_calculation_mode(action: 'calculate', scope: 'workbook')  → Recalculate once at end
4. excel_calculation_mode(action: 'set-mode', mode: 'automatic')  → Restore default
When NOT needed: Reading formulas, small edits (1-10 cells), or when you need immediate calculation results.
当写入大量值/公式（10个以上单元格）时，使用
excel_calculation_mode
避免每次写入后都进行重计算：
1. excel_calculation_mode(action: 'set-mode', mode: 'manual')  → 禁用自动重计算
2. 执行数据写入操作（excel_range set-values、set-formulas）
3. excel_calculation_mode(action: 'calculate', scope: 'workbook')  → 最后一次性重计算
4. excel_calculation_mode(action: 'set-mode', mode: 'automatic')  → 恢复默认设置
无需使用的场景： 读取公式、少量编辑（1-10个单元格）或需要立即获取计算结果时。

Tool Selection Quick Reference

工具选择快速参考

TaskToolKey Action
Create/open/save workbooks
excel_file
open, create, close
Write/read cell data
excel_range
set-values, get-values
Format cells
excel_range_format
set-number-format
Create tables from data
excel_table
create
Add table to Power Pivot
excel_table
add-to-datamodel
Create DAX formulas
excel_datamodel
create-measure
Create PivotTables
excel_pivottable
create, create-from-datamodel
Filter with slicers
excel_slicer
set-slicer-selection
Create charts
excel_chart
create-from-range
Control calculation mode
excel_calculation_mode
get-mode, set-mode, calculate
任务工具关键操作
创建/打开/保存工作簿
excel_file
open、create、close
写入/读取单元格数据
excel_range
set-values、get-values
格式化单元格
excel_range_format
set-number-format
从数据创建表格
excel_table
create
将表格添加到Power Pivot
excel_table
add-to-datamodel
创建DAX公式
excel_datamodel
create-measure
创建数据透视表
excel_pivottable
create、create-from-datamodel
使用切片器过滤
excel_slicer
set-slicer-selection
创建图表
excel_chart
create-from-range
控制计算模式
excel_calculation_mode
get-mode、set-mode、calculate

Reference Documentation

参考文档

See 
references/
for detailed guidance:
  • @references/behavioral-rules.md - Core execution rules and LLM guidelines
  • @references/anti-patterns.md - Common mistakes to avoid
  • @references/workflows.md - Data Model constraints and patterns
  • @references/excel_chart.md - Charts and formatting
  • @references/excel_conditionalformat.md - Conditional formatting operations
  • @references/excel_datamodel.md - Data Model/DAX specifics
  • @references/excel_powerquery.md - Power Query specifics
  • @references/excel_range.md - Range operations and number formats
  • @references/excel_slicer.md - Slicer operations
  • @references/excel_table.md - Table operations
  • @references/excel_worksheet.md - Worksheet operations
请查看
references/
获取详细指南：
  • @references/behavioral-rules.md - 核心执行规则与LLM指南
  • @references/anti-patterns.md - 需避免的常见错误
  • @references/workflows.md - Data Model约束与模式
  • @references/excel_chart.md - 图表与格式设置
  • @references/excel_conditionalformat.md - 条件格式操作
  • @references/excel_datamodel.md - Data Model/DAX详细说明
  • @references/excel_powerquery.md - Power Query详细说明
  • @references/excel_range.md - 单元格区域操作与数字格式
  • @references/excel_slicer.md - 切片器操作
  • @references/excel_table.md - 表格操作
  • @references/excel_worksheet.md - 工作表操作