Data Dashboard Skill
Create professional, formula-driven Excel dashboards from CSV or tabular data. The output is a single .xlsx file with a data sheet and a Dashboard sheet -- charts linked to live data, KPIs powered by formulas, and conditional formatting for visual insight.
BEFORE YOU START (CRITICAL)
Every time before using officecli, run this check:
CODEBLOCK0
Use When
- - User wants to create a dashboard from CSV data or tabular data
- User asks for KPI reports, analytics summaries, or metrics dashboards
- User wants to visualize data in Excel with charts, sparklines, and conditional formatting
- User mentions "CSV to Excel", "executive dashboard", or "data visualization"
What This Skill Produces
A single .xlsx file with:
| Component | Sheet | Description |
|---|
| Raw data | Sheet1 | Imported CSV with frozen headers, AutoFilter, column widths, conditional formatting |
| Dashboard |
Dashboard | KPI cards (formula-driven), sparklines, charts (cell-range-linked), preset styling |
The Dashboard sheet is active on open. All formulas recalculate on open.
Core Concepts
Formula-Driven KPIs
Every KPI value on the Dashboard is a formula referencing the data sheet. Never hardcode calculated values. When the underlying data changes, KPIs update automatically.
Cell Range References for Charts
Every chart series references data sheet cells directly (
series1.values="Sheet1!B2:B13"). Charts stay in sync with data. Never use inline data unless aggregation is impossible in Excel formulas.
Chart Presets
Use
preset=dashboard on charts for datasets with 10+ rows. For datasets with fewer than 10 rows, use
preset=minimal. See the complexity table in A.3 of creating.md for the authoritative mapping --
when any other text in this skill conflicts with that table, the table wins. Presets are DeferredAddKeys -- they work on
add only, NOT on
set. A single preset replaces 5-8 manual styling properties with one consistent look.
Data-Size-Aware Complexity
The number of KPIs, charts, sparklines, and CF rules scales with the input data size. A 5-row dataset gets 1 chart and no sparklines. A 200-row dataset gets 3-5 KPIs, 2-3 charts, sparklines, and multiple CF rules.
Workflow Overview
Phase 1: Analyze the Input Data
Count rows and columns. Identify column types (date, numeric, categorical). Determine the primary dimension (X-axis). Look up the data-size-to-complexity table.
Phase 2: Plan Before Building
Decide how many KPIs, which chart types, which CF rules, and chart layout positions. Write out the plan before executing any commands.
Phase 3: Build the Workbook
Follow the 11-step workflow: create + import, column widths, Dashboard sheet, KPIs, sparklines, charts, conditional formatting, tab colors, polish, raw-set, validate.
Phase 4: QA
Run the QA checklist. Fix issues. Re-validate.
Phase 5: Deliver
Deliver the
.xlsx file. Tell the user the Dashboard sheet opens first and formulas recalculate automatically.
Full Guide
Read creating.md and follow it step by step. It contains the complete workflow, decision tables, command templates, a full runnable example, and the QA checklist.
Quick Reference: Key Warnings
| Warning | Detail |
|---|
| Batch JSON values | ALL values must be strings: "true" not true, "24" not INLINECODE11 |
| Chart preset |
Add-only.
preset=dashboard for 10+ rows,
preset=minimal for < 10 rows |
| Scatter charts | Use
series1.xValues NOT
series1.categories (causes validation error) |
| Reference lines | Format is
value:color:label:dash (color BEFORE label) |
| Cell range refs | Always
series1.values="Sheet1!B2:B13", never inline data |
| raw-set ordering | activeTab and calcPr must be the LAST commands |
| formulacf | Do NOT use
font.bold. Use
fill +
font.color only |
| Column widths |
import --header does NOT auto-size. Set widths manually on
ALL sheets including Dashboard |
| Dashboard ### | KPI cells at 24pt bold WILL show ### if Dashboard columns are not set to width=22. See Step 4b |
References
- - creating.md -- Complete dashboard creation guide (the main skill file)
- xlsx SKILL.md -- General xlsx reading, editing, and QA reference
数据仪表盘技能
从CSV或表格数据创建专业、公式驱动的Excel仪表盘。输出为单个.xlsx文件，包含一个数据表和一个仪表盘表——图表链接到实时数据，KPI由公式驱动，条件格式提供可视化洞察。
开始前（关键）
每次使用officecli前，请运行此检查：
bash
if ! command -v officecli &> /dev/null; then
echo 正在安装officecli...
curl -fsSL https://raw.githubusercontent.com/iOfficeAI/OfficeCli/main/install.sh -o /tmp/officecliinstall.sh && bash /tmp/officecliinstall.sh && rm -f /tmp/officecli_install.sh
# Windows: irm https://raw.githubusercontent.com/iOfficeAI/OfficeCli/main/install.ps1 -OutFile $env:TEMP\officecliinstall.ps1; & $env:TEMP\officecliinstall.ps1
else
CURRENT=$(officecli --version 2>&1 | grep -oE [0-9]+\.[0-9]+\.[0-9]+ | head -1)
LATEST=$(curl -fsSL https://api.github.com/repos/iOfficeAI/OfficeCLI/releases/latest | grep tag_name | sed -E s/.v?([0-9.]+)./\1/)
if [ $CURRENT != $LATEST ]; then
echo 正在升级officecli $CURRENT -> $LATEST...
curl -fsSL https://raw.githubusercontent.com/iOfficeAI/OfficeCli/main/install.sh -o /tmp/officecliinstall.sh && bash /tmp/officecliinstall.sh && rm -f /tmp/officecli_install.sh
else
echo officecli $CURRENT 已是最新版本
fi
fi
officecli --version
使用场景
- - 用户希望从CSV数据或表格数据创建仪表盘
- 用户要求KPI报告、分析摘要或指标仪表盘
- 用户希望在Excel中使用图表、迷你图和条件格式可视化数据
- 用户提到CSV转Excel、执行仪表盘或数据可视化
本技能产出
一个包含以下内容的单个.xlsx文件：
| 组件 | 工作表 | 描述 |
|---|
| 原始数据 | Sheet1 | 导入的CSV，包含冻结标题、自动筛选、列宽、条件格式 |
| 仪表盘 |
Dashboard | KPI卡片（公式驱动）、迷你图、图表（单元格区域链接）、预设样式 |
仪表盘表在打开时处于活动状态。所有公式在打开时重新计算。
核心概念
公式驱动的KPI
仪表盘上的每个KPI值都是引用数据表的公式。切勿硬编码计算值。当底层数据发生变化时，KPI会自动更新。
图表的单元格区域引用
每个图表系列直接引用数据表单元格（series1.values=Sheet1!B2:B13）。图表与数据保持同步。除非Excel公式无法实现聚合，否则切勿使用内联数据。
图表预设
对于10行以上的数据集，在图表上使用preset=dashboard。对于少于10行的数据集，使用preset=minimal。请参阅creating.md中A.3节的复杂度表以获取权威映射——
当本技能中的任何其他文本与该表冲突时，以该表为准。 预设是延迟添加键——它们仅在add上生效，而不在set上生效。单个预设用一致的外观替换5-8个手动样式属性。
数据规模感知的复杂度
KPI、图表、迷你图和条件格式规则的数量随输入数据规模而变化。5行的数据集获得1个图表且无迷你图。200行的数据集获得3-5个KPI、2-3个图表、迷你图和多个条件格式规则。
工作流程概览
阶段1：分析输入数据
统计行数和列数。识别列类型（日期、数值、分类）。确定主要维度（X轴）。查阅数据规模与复杂度对照表。
阶段2：构建前规划
决定KPI数量、图表类型、条件格式规则以及图表布局位置。在执行任何命令前写出规划。
阶段3：创建工作簿
遵循11步工作流程：创建+导入、列宽、仪表盘表、KPI、迷你图、图表、条件格式、工作表标签颜色、美化、原始设置、验证。
阶段4：质量保证
运行QA检查清单。修复问题。重新验证。
阶段5：交付
交付.xlsx文件。告知用户仪表盘表首先打开，公式自动重新计算。
完整指南
阅读creating.md并逐步遵循。它包含完整的工作流程、决策表、命令模板、完整的可运行示例以及QA检查清单。
快速参考：关键警告
| 警告 | 详情 |
|---|
| 批量JSON值 | 所有值必须为字符串：true而非true，24而非24 |
| 图表预设 |
仅添加。10行以上使用preset=dashboard，少于10行使用preset=minimal |
| 散点图 | 使用series1.xValues而非series1.categories（会导致验证错误） |
| 参考线 | 格式为value:color:label:dash（颜色在标签之前） |
| 单元格区域引用 | 始终使用series1.values=Sheet1!B2:B13，切勿使用内联数据 |
| raw-set排序 | activeTab和calcPr必须是最后的命令 |
| formulacf | 不要使用font.bold。仅使用fill + font.color |
| 列宽 | import --header不会自动调整大小。在
所有工作表（包括仪表盘表）上手动设置宽度 |
| 仪表盘### | 24pt加粗的KPI单元格在仪表盘列未设置为width=22时会显示###。参见步骤4b |
参考
