officecli-data-dashboard

# Data Dashboard (scene-layer on officecli-xlsx) A dashboard is not "a spreadsheet with charts". It is a composition: **one Dashboard sheet the user lands on** with formula-driven KPI cards, cell-range-linked charts, sparklines, and semantic conditional formatting. Everything else (raw data, aggregations) is upstream infrastructure the user should never need to open. This skill teaches the composition pattern. Everything about the xlsx engine — cells, formulas, batch JSON, shell quoting, validate, HTML preview — comes from `officecli-xlsx` and is not re-taught here. ## 运行环境与交付链（本系统专用，先读这一段） **`officecli` 已经预装在运行环境的 PATH 中，不要联网安装、不要 curl。** `Bash` 工具直接调用即可，自检用 `officecli --version`。 所有命令在当前会话工作目录执行，输入输出使用相对路径或用户指定的真实路径。一次完整作业 = 3 步 + 1 步交付： ``` # Step 1（仅当用户上传过原始文件）把 artifact 送进沙盒 sandbox_put_artifact(artifact_id="<用户文件的 file_id>", dest_path="./in.docx") # Step 2 用 Bash 跑 officecli —— 本文件下面讲的全部命令都在这一步里 Bash("officecli create ./out.docx") # Step 3 把成稿从沙盒取出、登记成 artifact，拿到新的 file_id sandbox_get_artifact(src_path="./out.docx", name="终稿.docx") # Step 4（必做）交付给用户 pin_to_workspace(file_ids=["<新 file_id>"]) ``` **铁律**： - `sandbox_put_artifact` = 放**进**沙盒，`sandbox_get_artifact` = 从沙盒**取出**，别搞反；两个路径参数使用当前工作目录内的相对路径或真实绝对路径。 - 拿到 `file_id` 却不 `pin_to_workspace` 就等于没交付，用户看不到文件。多个产物一次性放进同一个列表，别分多次调用。 - 下文示例里的 `$FILE` 指实际文件；相对路径按当前会话工作目录解析。 - 下文的可视化质检（`view <file> screenshot` / `view <file> html`）依赖沙盒内的 Chromium，镜像已预装；截图失败就按下文 fallback 走 `view html`，并如实标注「未做视觉验证」，不要谎报 PASS。 - **导出 PDF 不要用 `officecli view <file> pdf`** —— 那个模式依赖本系统没有安装的 exporter 插件，必然失败。改用沙盒里的 LibreOffice： `soffice --headless --convert-to pdf --outdir . ./out.docx`。.xlsx / .pptx 换掉扩展名同理，但要沙盒里装了 LibreOffice Calc / Impress 才转得动；转换报错就如实告诉用户 PDF 没导出成功、主产物仍是原文件，不要谎报已导出。 ## ⚠️ Help-First Rule **When a prop name, enum value, or alias is uncertain, consult help before guessing.** ```bash officecli help xlsx # element list officecli help xlsx chart # full schema for charts officecli help xlsx sparkline # sparklines officecli help xlsx conditionalformatting # all CF rule types ``` Help reflects the installed CLI version. When this skill and help disagree, **help wins**. DeferredAddKeys (`combosplit`, `holesize`) work on `add` only — see Reference. ## Mental Model & Inheritance This skill **inherits every xlsx hard rule** from `officecli-xlsx` — shell quoting, zero formula errors, visual delivery floor, batch JSON shape (`{"command":"set"|"add","path":...,"props":{...}}` — key is `command`, NOT `action`), batch JSON dotted-name rule, chart data-feed forms, batch+resident limits, `validate` discipline. Read officecli-xlsx first; honour those rules, do not re-teach them here. **Reverse handoff — do NOT use this skill when:** - The ask is a **single-sheet CSV-with-formatting tracker** (no Dashboard sheet, no KPI cards, ≤ 1 chart) → go back to `officecli-xlsx`. - The ask is a **3-statement / DCF / LBO financial model** with blue-inputs / black-formulas / cross-sheet drivers → use `officecli-financial-model`. - The ask is a **weekly status report** with one SUMIF summary and one chart over < 10 rows → `officecli-xlsx`. This skill only accepts: "a Dashboard sheet the user opens first, multiple KPI cards, multiple charts, some CF / sparklines". ## Shell & Execution Discipline → see officecli-xlsx §Shell & Execution Discipline for the baseline (quoting, heredoc for `!`, incremental execution). Two increments specific to dashboards: - **Long chart `add` commands exceed 180 chars.** Always split across lines with trailing `\`; never pack a chart command onto a single line. The longer the command, the higher the chance a shell-escape bug hides inside it. - **Multi-instance counts use `query --json | jq length`, never `raw-get | grep -c`.** Example: `officecli query "$FILE" chart --json | jq '.data.results | length'` for "how many charts do I have?". ## Core Principles Five non-negotiable principles. If any one is violated the output is not a dashboard, it is a spreadsheet that happens to have a chart. 1. **Formula-driven KPIs.** Every KPI value on the Dashboard sheet is a formula — `SUM`, `AVERAGE`, `IFERROR((...-...)/...,0)`, whatever — referring to cells on the Data / Summary sheet. Never hardcode a computed number. When the underlying data changes tomorrow, KPIs update on open. 2. **Cell-range references for charts.** Every chart series reads from a cell range: `series1.values="Sheet1!B2:B13"`. Inline `data="Revenue:100,200,300"` is for a 5-minute demo, not a delivered dashboard. The one exception: data requires an aggregation Excel cannot express (rare) — document the exception in a comment cell. 3. **Dashboard-first architecture.** KPI label cells, KPI value cells, charts, sparklines all live on the **Dashboard** sheet — the single sheet a user lands on. Raw imports and `SUMIFS` rollups live on Data / Summary sheets, upstream of the Dashboard. The user should never need to switch tabs to find the answer. 4. **Visible cells only for chart sources.** LibreOffice does not evaluate formulas in hidden columns or hidden sheets at render time. A chart whose `series1.values` points at a hidden-column `SUMIFS` renders blank. Pattern: aggregate into a **visible** Summary sheet, point charts at Summary cells, hide only helper columns that are not chart sources. 5. **Data-size-aware complexity.** A 10-row dataset does not get 5 KPIs and 4 charts. A 200-row dataset does not get 1 KPI and 1 chart. Scale up the composition with the input (table in §Design Ideas). Overbuilding is as wrong as underbuilding. ## Requirements All `officecli-xlsx` requirements apply (→ see officecli-xlsx §Requirements for Outputs). Dashboards add these: - **Dashboard sheet is the active tab on open.** Confirm 0-based sheet index with `officecli query "$FILE" sheet` BEFORE filling `activeTab="N"`. Never guess the index. - **`calc.fullCalcOnLoad=true`.** Set via `officecli set "$FILE" / --prop calc.fullCalcOnLoad=true`. Do NOT `raw-set` `<calcPr>` — it produces duplicate elements that fail validate. - **Refresh downstream cachedValue after every upstream edit.** `fullCalcOnLoad=true` schedules runtime recalc only; it does NOT refresh build-time `cachedValue`. After `set B=100 → set E==B+D → fix B=150`, E is stale until you re-issue E's formula (or close/reopen). Stale cache ships "Net Change = 0" to the board. - **Every chart has a descriptive title and every series has a name.** `"Series1"` in a legend is unfinished work. - **Every KPI value cell has a formula.** Verifiable: `officecli query "$FILE" 'Dashboard!:has(formula)' --json | jq '.data.results | length'` should equal your planned KPI count. - **Header row fill on every data sheet.** Data sheet, Summary sheet, and any secondary data sheet need row 1 filled (e.g., `fill=1F3864 + font.color=FFFFFF + font.bold=true`). - **10+ rows on Data sheet → ≥ 1 CF rule on a numeric column.** A 20-row table with zero visual scanning aid is a quality miss. - **Dashboard value columns sized to the widest expected cachedValue — not a fixed 22.** Rule of thumb at 24pt bold + currency numFmt: `width ≈ ceil((visible_chars + 2) × 1.3)`. A KPI holding `¥1,958,414,250` (14 visible chars with currency + commas) needs `width ≥ 28`; a 4-digit KPI still needs `width ≥ 22` as the floor. Hardcoding `22` for a 10+ digit KPI is how `###` ships to the user. - **Sparkline row height ≥ 20.** A sparkline in a default 15pt row is a flat squiggle — set `/Dashboard/row[N] height=22` (or 24 when paired with a 24pt KPI value cell in the same row). - **Print deliverables set `_xlnm.Print_Area` scoped to Dashboard** + hide non-Dashboard sheets + add `<pageSetup fitToPage/>`. Without all three, the print pipeline emits every sheet and Dashboard lands on page 2+. See §Print-ready delivery for the exact commands. ## Quick Start Minimal viable dashboard: 12-month revenue CSV → 4 KPIs + 1 line chart + activeTab + fullCalcOnLoad. Adapt the numbers, don't copy-paste blind. Broken into phases so a single failed phase is obvious. **Phase 1 — Data sheet: create, import, format.** ```bash FILE=my_dashboard.xlsx officecli create "$FILE" officecli import "$FILE" /Sheet1 --file sales.csv --header officecli set "$FILE" '/Sheet1/col[A]' --prop width=12 officecli set "$FILE" '/Sheet1/col[B]' --prop width=15 officecli set "$FILE" '/Sheet1/B2:B13' --prop numFmt='$#,##0' officecli set "$FILE" '/Sheet1/A1:B1' --prop fill=1F3864 --prop font.color=FFFFFF --prop font.bold=true ``` **Phase 2 — Dashboard sheet + one KPI card.** ```bash officecli add "$FILE" / --type sheet --prop name=Dashboard officecli set "$FILE" '/Dashboard/col[A]' --prop width=22 officecli set "$FILE" '/Dashboard/col[B]' --prop width=12 officecli set "$FILE" /Dashboard/A1 --prop value="Total Revenue" --prop font.size=9 --prop font.color=666666 --prop bold=true officecli set "$FILE" /Dashboard/A2 --prop 'formula==SUM(Sheet1!B2:B13)' --prop numFmt='$#,##0' --prop font.size=24 --prop bold=true --prop font.color=2E7D32 ``` **Phase 3 — Sparkline + chart.** ```bash officecli add "$FILE" /Dashboard --type sparkline --prop cell=B2 --prop range='Sheet1!B2:B13' --prop type=line --prop color=4472C4 --prop highPoint=true --prop highMarkerColor=FF0000 officecli add "$FILE" /Dashboard --type chart \ --prop chartType=line \ --prop title="Revenue Trend" \ --prop series1.name="Revenue" \ --prop series1.values='Sheet1!B2:B13' \ --prop series1.categories='Sheet1!A2:A13' \ --prop preset=dashboard --prop axisNumFmt='$#,##0' \ --prop x=0 --prop y=5 --prop width=10 --prop height=15 ``` **Phase 4 — fullCalcOnLoad → activeTab (LAST) → close → validate.** ```bash officecli set "$FILE" / --prop calc.fullCalcOnLoad=true # Resolve Dashboard's 0-based index from the actual sheet list — never hardcode. DASH_IDX=$(officecli query "$FILE" sheet --json \ | jq '[.data.results[].path] | index("/Dashboard")') officecli raw-set "$FILE" /workbook --xpath "//x:sheets" --action insertbefore \ --xml "<bookViews xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\"><workbookView activeTab=\"$DASH_IDX\" /></bookViews>" officecli close "$FILE" officecli validate "$FILE" ``` Verified end-to-end on a 12-row revenue CSV: `validate` reports no errors, Dashboard opens first, `Dashboard/A2.cachedValue` resolves (2,075,000 for the test data), chart renders with values linked. ## Design Ideas Options, not templates. The user's data and audience drive the choices. ### Layout patterns (pick one, stay consistent) **Pattern 1 — executive summary** (board packs): KPI strip A1:H4, charts stack from row 6. ``` ┌ KPI1 │ KPI2 │ KPI3 │ KPI4 ┐ rows 1-4 ├──────┴──────┴──────┴──────┤ │ Chart 1 (wide) │ rows 6-18 ├───────────────┬───────────┤ │ Chart 2 │ Chart 3 │ rows 20-32 ``` **Pattern 2 — ops console** (live ops): KPIs down A:B, charts fill C:L. ``` │ KPI1 │ │ │ KPI2 │ Chart 1 │ rows 1-12 │ KPI3 │ │ │ KPI4 ├───────────────────┤ │ KPI5 │ Chart 2 │ rows 14-26 ``` **Pattern 3 — scorecard** (≥ 6 KPIs, no dominant chart): grid of 2×3 cards (label / value / sparkline). ``` │ KPI1 │ KPI2 │ KPI3 │ rows 1-4 │ KPI4 │ KPI5 │ KPI6 │ rows 5-8 ``` ### Complexity scaling by data size | Rows | KPIs | Charts | Sparklines | CF rules | Preset | |---|---|---|---|---|---| | < 10 | 1–2 | 1 | skip | 0–1 | `minimal` | | 10–50 | 2–3 | 2 | only if sequential time-series | 1–2 | `dashboard` | | 50–200 | 3–5 | 2–3 | only if sequential time-series | 2–3 | `dashboard` | | 200+ | 3–5 | 3 | only if sequential time-series | 3–4 | `dashboard` | ### Chart type selection | Data pattern | Chart type | Notes | |---|---|---| | Trend over time, one series | `line` | Add `trendline=linear` to show direction on noisy series | | Trend over time, multiple components | `line` (multi-series) or `columnStacked` | Stacked when components sum to a meaningful total | | Comparison across categories in time order | `column` | Not `bar` — horizontal bars break left-to-right time reading | | Part-of-whole breakdown | `doughnut` | Prefer over `pie`: `chartType=pie` has a known LibreOffice blank-render regression | | Budget vs actual | `combo` with `combosplit=1` | First series as bars, rest as lines | | Correlation | `scatter` | X-axis via `categories` / `series1.categories` — `series1.xValues` is UNSUPPORTED | ### Preset options `--prop preset=<name>` on every chart. Options: `minimal`, `dashboard`, `corporate`, `magazine`, `colorful`, `monochrome`, `dark`. Pick one and stay consistent across all charts on a single Dashboard — mixing presets reads as accidental. ### Conditional formatting — semantic colors Four CF rule types; each uses `--type <shorthand>` at `add` time: | Intent | `--type` | Typical props | |---|---|---| | Magnitude bar (sales, spend) | `databar` | `sqref=B2:B13 color=4472C4` — explicit `min=0 max=<plausible>` recommended for predictable scaling, but omitting them is valid (defaults to data min/max) | | Heat map (rates, growth) | `colorscale` | `sqref=D2:D13 mincolor=FFCDD2 midcolor=FFFFFF maxcolor=C8E6C9` | | Status indicator | `iconset` | `sqref=E2:E13 iconset=3Arrows` — see help for the full enum | | Custom business rule | `formulacf` | `sqref=B2:B13 'formula=$B2>=100000' fill=C8E6C9 font.color=2E7D32` — `font.bold` works on CF too | Semantic colors to stay consistent within a dashboard: - good / positive: fill `C8E6C9`, font `2E7D32` - bad / negative: fill `FFCDD2`, font `C62828` - neutral: fill `F5F5F5`, font `666666` ### KPI card anatomy A card is a label cell + a value cell. The label is small gray (font.size=9, font.color=666666, bold); the value is large bold (font.size=24, bold=true, numFmt, font.color signals tone). One row of light fill (e.g. `F0F4FF`) across the card area gives the "card" read without building merged-cell scaffolds. Value column width must be sized to the largest cachedValue — never narrower than 22, often 26–32 for 8+ digit currency (see Requirements). ### Chart width budget by title length At the `dashboard` preset's default title font, the chart plot-box width (in column units) must stay ahead of the title string, or the title clips mid-word. Rule of thumb: `chart.width ≥ ceil(title.length × 0.18)`. A 35-character title ("Department: Year-End Headcount vs Attrition Rate") needs `width ≥ 7`; be safer and use 10–12. If the anchor cannot be widened, shorten the title to ≤ 25 characters — clipped titles in a board-ready deliverable are indefensible. `officecli get chart[N]` exposes numeric `width` (e.g. `width=480pt`) alongside `anchor` (e.g. `"A6:K21"`) at `.data.results[0].format`. Either is usable for Gate 2 — derive column span from anchor letters (A→K = 10 cols) when you need a column-unit budget. ### Print-ready delivery (board-pack / investor-send / one-pager)