Univer Sheet Units
Load univer first. univer_execute provides univerAPI, api (the same object), and workbook (the FWorkbook selected by unitId). Do not redeclare them. Execution is ESM and has no require.
Obtain a worksheet with workbook.getActiveSheet() or workbook.getSheetByName("…"). Use getSheetName(); FWorksheet has no getName(). Resolve unfamiliar APIs with univer_api before writing code.
Cell model: v, t, f, and number formats
A cell is structured data: { v, t, f?, s? }.
| Field |
Meaning |
v |
Stored value used for calculation, comparison, and writeback. |
t |
Type: 1 text, 2 number, 3 boolean, 4 forced text. |
f |
Formula source; its cached result is stored separately in v. |
s.n.pattern |
Number format; changes display only, never v. |
p |
Rich text; when present it overrides the displayed form of v. |
si |
Shared-formula id; preserve only when deep-copying an existing formula cell. |
displayValue |
Visible text rendered from v, t, and number format. |
Always write explicit ICellData. Bare values are inferred and can corrupt identifiers, scores, leading zeros, and date-like strings:
{ v: "text", t: 1 }
{ v: 42, t: 2 }
{ v: 1, t: 3 }
{ v: "00123", t: 4 }
{ f: "=A1+B1" }
{ v: "=A1+B1", t: 4 }
Dates, percentages, and currencies are numbers plus number formats:
{ v: 44900, t: 2, s: { n: { pattern: "yyyy-MM-DD" } } }
{ v: 0.25, t: 2, s: { n: { pattern: "0%" } } }
Never write display text back as the stored value.
Common APIs
- Ranges:
sheet.getRange("A1:C9") or getRange(row, col, numRows, numCols). Numeric rows and columns are zero-based.
- Sheets:
getActiveSheet(), getSheetByName("Sheet1"), getSheets().
- Authoritative reads:
getCellData() / getCellDatas() for v/t/f/s; getRawValues() for unnormalized stored values.
- Presentation reads:
getDisplayValues() for visible text and getFormula() for formula source.
- Writes:
setValue(cell), setValues(grid), setFormula("=…"), clearContent(), and clear().
- Dimensions:
getLastRow(), getLastColumn(), and setRowCount(n) before out-of-range writes.
- Style: use explicit xlsx-safe colors in
#RRGGBB or rgb(r,g,b) form.
setValues() merges cell data. {} or { s } does not clear existing v/f/p. To replace a region, call clearContent() first, then setValues(). To clear one cell explicitly, set { v: null, f: null, p: null, si: null, custom: null }.
Over-grid images
Create an image with sheet.newOverGridImage(), set source, explicit size and position, await buildAsync(), then pass the built data to sheet.insertImages():
const sheet = workbook.getActiveSheet();
const image = await sheet
.newOverGridImage()
.setSource(imageDataUri, api.Enum.ImageSourceType.BASE64)
.setColumn(1)
.setRow(1)
.setWidth(640)
.setHeight(360)
.buildAsync();
sheet.insertImages([image]);
Verify with sheet.getImages()[0].toBuilder().getSource() and getSourceType().
Formulas and recalculation
Formula source and cached result are separate. Writing a formula schedules calculation, but an immediate value read can still be stale. Loading a snapshot does not implicitly recalculate existing formulas.
Register onCalculationResultApplied() before triggering calculation, then await it:
const calculated = api.getFormula().onCalculationResultApplied();
api.getFormula().executeCalculation();
await calculated;
return workbook.getActiveSheet().getRange("A3").getValue();
For a newly written formula, register the promise before setFormula and await it afterward. Recalculate before exporting because xlsx stores cached values with formulas. If only a final value is required and cache freshness cannot be guaranteed, write the stored value directly.
OOXML table formulas
Use explicit Excel structured-reference scopes:
Orders[[#This Row],[Amount]] or Orders[@[Amount]] reads one row value.
Orders[[#Data],[Amount]] reads the complete data column.
Orders[Amount] is valid, but prefer explicit #Data for whole-column aggregates.
[@[Amount]] is valid only inside the Host table's calculated column.
table[Amount] is invalid unless the real table name is exactly table.
Copy the exact table name from workbook metadata; do not use table id, Sheet tab name, display label, guessed case, or a placeholder. Await calculation, then verify both the stored formula and computed values.
Rich text
Rich text lives in cell.p, not cell.v. Build it with api.newRichText(); never construct the internal body by hand.
const rich = api.newRichText();
rich.insertText("Hello World");
rich.setStyle(0, 5, { bl: 1, cl: { rgb: "#FF0000" } });
workbook.getActiveSheet().getRange("A1").setRichTextValueForCell(rich);
setStyle(start, end, style) uses a half-open range. Derive offsets from inserted JavaScript strings. Common style fields are bl, it, cl, and bg. Verify getCellData().p, text-run ranges, and styles.
Failure prevention
- Specify
t for every non-formula cell written.
- Do not use
getValue() or getValues() for authoritative reads; formatted values and booleans can be converted.
- Deep-copy complete cell data when copying formatted cells.
- After importing csv or tsv, inspect every column's value type and formulas before computing. Mixed values can cause an entire column to import as text.
- Sheet Unit name, worksheet name, and file name are different identifiers.
- Use
univer_api before unfamiliar formatting, chart, table, filter, validation, pivot, image, comment, sparkline, or conditional-formatting operations.
Verification
After every mutation:
- Call
univer_inspect with the exact unitId, worktree, and a range such as Sheet1!A1:D20.
- Verify stored values, types, formulas, display values, row order, and task-specific calculations.
- Read styles, number formats, rich text, images, charts, tables, or other omitted fields through a fresh read-only
univer_execute call.
- For visually relevant formatting, charts, images, or layout, call
univer_screenshot with the exact unitId, worktree or trunk, a workspace output directory, and a narrow range plus optional sheetName. Inspect the returned PNG; use an unscoped full-workbook capture only when the task requires the complete workbook view.
- Recalculate and re-read formula results before
univer_export.
- Follow the
univer ready/status handoff workflow.
1---2name: univer-sheet3description: Read, write, format, calculate, and verify Univer Sheet Units through DSH tools and the Lite Interface. Use proactively for spreadsheet values, formulas, ranges, tables, charts, images, formatting, validation, filters, pivots, rich text, xlsx/csv/tsv import or export, and any Sheet Unit task.4metadata:5 source: https://skillmd.com/skills/dream-num/univer-sheet6---78# Univer Sheet Units910Load `univer` first. `univer_execute` provides `univerAPI`, `api` (the same object), and `workbook` (the `FWorkbook` selected by `unitId`). Do not redeclare them. Execution is ESM and has no `require`.1112Obtain a worksheet with `workbook.getActiveSheet()` or `workbook.getSheetByName("…")`. Use `getSheetName()`; `FWorksheet` has no `getName()`. Resolve unfamiliar APIs with `univer_api` before writing code.1314## Cell model: v, t, f, and number formats1516A cell is structured data: `{ v, t, f?, s? }`.1718| Field | Meaning |19| --- | --- |20| `v` | Stored value used for calculation, comparison, and writeback. |21| `t` | Type: `1` text, `2` number, `3` boolean, `4` forced text. |22| `f` | Formula source; its cached result is stored separately in `v`. |23| `s.n.pattern` | Number format; changes display only, never `v`. |24| `p` | Rich text; when present it overrides the displayed form of `v`. |25| `si` | Shared-formula id; preserve only when deep-copying an existing formula cell. |26| `displayValue` | Visible text rendered from `v`, `t`, and number format. |2728Always write explicit `ICellData`. Bare values are inferred and can corrupt identifiers, scores, leading zeros, and date-like strings:2930```js31{ v: "text", t: 1 }32{ v: 42, t: 2 }33{ v: 1, t: 3 }34{ v: "00123", t: 4 }35{ f: "=A1+B1" }36{ v: "=A1+B1", t: 4 }37```3839Dates, percentages, and currencies are numbers plus number formats:4041```js42{ v: 44900, t: 2, s: { n: { pattern: "yyyy-MM-DD" } } }43{ v: 0.25, t: 2, s: { n: { pattern: "0%" } } }44```4546Never write display text back as the stored value.4748## Common APIs4950- Ranges: `sheet.getRange("A1:C9")` or `getRange(row, col, numRows, numCols)`. Numeric rows and columns are zero-based.51- Sheets: `getActiveSheet()`, `getSheetByName("Sheet1")`, `getSheets()`.52- Authoritative reads: `getCellData()` / `getCellDatas()` for `v/t/f/s`; `getRawValues()` for unnormalized stored values.53- Presentation reads: `getDisplayValues()` for visible text and `getFormula()` for formula source.54- Writes: `setValue(cell)`, `setValues(grid)`, `setFormula("=…")`, `clearContent()`, and `clear()`.55- Dimensions: `getLastRow()`, `getLastColumn()`, and `setRowCount(n)` before out-of-range writes.56- Style: use explicit xlsx-safe colors in `#RRGGBB` or `rgb(r,g,b)` form.5758`setValues()` merges cell data. `{}` or `{ s }` does not clear existing `v/f/p`. To replace a region, call `clearContent()` first, then `setValues()`. To clear one cell explicitly, set `{ v: null, f: null, p: null, si: null, custom: null }`.5960## Over-grid images6162Create an image with `sheet.newOverGridImage()`, set source, explicit size and position, await `buildAsync()`, then pass the built data to `sheet.insertImages()`:6364```js65const sheet = workbook.getActiveSheet();66const image = await sheet67 .newOverGridImage()68 .setSource(imageDataUri, api.Enum.ImageSourceType.BASE64)69 .setColumn(1)70 .setRow(1)71 .setWidth(640)72 .setHeight(360)73 .buildAsync();74sheet.insertImages([image]);75```7677Verify with `sheet.getImages()[0].toBuilder().getSource()` and `getSourceType()`.7879## Formulas and recalculation8081Formula source and cached result are separate. Writing a formula schedules calculation, but an immediate value read can still be stale. Loading a snapshot does not implicitly recalculate existing formulas.8283Register `onCalculationResultApplied()` before triggering calculation, then await it:8485```js86const calculated = api.getFormula().onCalculationResultApplied();87api.getFormula().executeCalculation();88await calculated;89return workbook.getActiveSheet().getRange("A3").getValue();90```9192For a newly written formula, register the promise before `setFormula` and await it afterward. Recalculate before exporting because xlsx stores cached values with formulas. If only a final value is required and cache freshness cannot be guaranteed, write the stored value directly.9394## OOXML table formulas9596Use explicit Excel structured-reference scopes:9798- `Orders[[#This Row],[Amount]]` or `Orders[@[Amount]]` reads one row value.99- `Orders[[#Data],[Amount]]` reads the complete data column.100- `Orders[Amount]` is valid, but prefer explicit `#Data` for whole-column aggregates.101- `[@[Amount]]` is valid only inside the Host table's calculated column.102- `table[Amount]` is invalid unless the real table name is exactly `table`.103104Copy the exact table name from workbook metadata; do not use table id, Sheet tab name, display label, guessed case, or a placeholder. Await calculation, then verify both the stored formula and computed values.105106## Rich text107108Rich text lives in `cell.p`, not `cell.v`. Build it with `api.newRichText()`; never construct the internal body by hand.109110```js111const rich = api.newRichText();112rich.insertText("Hello World");113rich.setStyle(0, 5, { bl: 1, cl: { rgb: "#FF0000" } });114workbook.getActiveSheet().getRange("A1").setRichTextValueForCell(rich);115```116117`setStyle(start, end, style)` uses a half-open range. Derive offsets from inserted JavaScript strings. Common style fields are `bl`, `it`, `cl`, and `bg`. Verify `getCellData().p`, text-run ranges, and styles.118119## Failure prevention120121- Specify `t` for every non-formula cell written.122- Do not use `getValue()` or `getValues()` for authoritative reads; formatted values and booleans can be converted.123- Deep-copy complete cell data when copying formatted cells.124- After importing csv or tsv, inspect every column's value type and formulas before computing. Mixed values can cause an entire column to import as text.125- Sheet Unit name, worksheet name, and file name are different identifiers.126- Use `univer_api` before unfamiliar formatting, chart, table, filter, validation, pivot, image, comment, sparkline, or conditional-formatting operations.127128## Verification129130After every mutation:1311321. Call `univer_inspect` with the exact `unitId`, worktree, and a range such as `Sheet1!A1:D20`.1332. Verify stored values, types, formulas, display values, row order, and task-specific calculations.1343. Read styles, number formats, rich text, images, charts, tables, or other omitted fields through a fresh read-only `univer_execute` call.1354. For visually relevant formatting, charts, images, or layout, call `univer_screenshot` with the exact `unitId`, worktree or trunk, a workspace `output` directory, and a narrow `range` plus optional `sheetName`. Inspect the returned PNG; use an unscoped full-workbook capture only when the task requires the complete workbook view.1365. Recalculate and re-read formula results before `univer_export`.1376. Follow the `univer` ready/status handoff workflow.