craft-xlsx decodes `.xlsb` cell formulas, including shared, array and dynamic-array formulas, into the text an `.xlsx` file holds for them. Until now they were skipped and only their saved values kept. Part of #778. Ported from storytold/gridcraft#3810, #3818 and #3821 (issue gridcraft#3806), my own series; the commits keep me (kiwi4boy) as their author. The three PRs are one series, each containing the one before. ## Commits 1. **The XLSB function table** (gridcraft#3810): `xlsb/ftab.rs`, [MS-XLSB] 2.5.98.10. It is craft-xls's table up to 379 (0x17B), which matches GridCraft's before the PR, plus the PR's 0x17C–0x1E4 entries (CUBE*, the Analysis ToolPak functions, IFERROR, COUNTIFS, SUMIFS, AVERAGEIF(S)). It is craft-xlsx's own copy, not shared with craft-xls: the formats follow separate specifications and their readers develop separately. They can share one table later once both settle. 2. **Cell formulas** (gridcraft#3818, with its two review fixes): `xlsb/formula.rs` is a BIFF12 decoder, separate from craft-xls's BIFF8 one, because nearly every operand differs. `xlsb/expr.rs` is a copy of craft-xls's expression tree and A1 printer, with `BinOp::precedence` added for grouping. A formula cell's `CellFormula.text` is in the file's spelling (`_xlfn.CONCAT`, `_xlpm.x`, no implicit `@`), and a `BrtCellMeta` before a cell sets `cell_metadata`, as `cm` does. The workbook's sheet links, supporting links and names are read with the workbook part, and `Package` keeps them for its sheets. A new `Limits::binary_formula_text` (128 MiB) bounds decoded text. 3. **Shared, array and dynamic-array formulas** (gridcraft#3821, with its review fixes): `PtgExp` cells, `BrtShrFmla` and `BrtArrFmla`. See the mapping below. 4. **A shared formula stops decoding copies once one is kept as its value**: a fix for CodeRabbit's finding that is still open on gridcraft#3821, so it isn't carried over. 5. **README**: what the decoder gives, what keeps its saved result, and the new limit. 6. **Review fixes**: 468 (0x1D4) is CONVERT. The specification's table has no row for it, but Excel writes CONVERT as `PtgFunc` 0x1D4. A shared formula's tokens are borrowed from the part instead of copied. A formula kept as its value loses its cell metadata. 7. **Review fixes, round 2**: a chain of strings joined with `&` is flattened only up to the decoder's depth bound (256 strings), so a balanced tree of thousands can't become a chain that overflows the stack when printed or dropped. A first cell whose range record never comes loses its cell metadata. 8. **Excel's spelling, from a second Excel round:** - Spaces and line breaks typed in a formula (`PtgAttrSpace`) are kept where `.xlsx` keeps them (`1 + 2`, `SUM( A1, B1 )`), and the text is trimmed as the `.xlsx` reader trims it. - A number is written out in full when that takes at most 21 characters without the sign, and in scientific notation otherwise (`100000000000000000000`, `1E+21`), as Excel writes it. The decoder switched at 1E+15 and 1E-9. - ISO.CEILING, ECMA.CEILING, NETWORKDAYS.INTL and WORKDAY.INTL are called through hidden `_xlfn.` names in `.xlsb`, but `.xlsx` spells them without the prefix. 9. **Macro sheets and spans over chart sheets:** a reference to an XLM macro sheet (`Macro1!$A$1`) is named, since macro sheets have cells. A 3-D span with a chart sheet between its ends is spelled by its ends, as `.xlsx` has it. An end with no cells still keeps the saved result. ## How it maps onto craft-xlsx's model - A formula's text is what the `.xlsx` reader gives for the same formula. GridCraft's reading of that text stays in GridCraft: `fmla::from_file`, the implicit `@` on legacy formulas, its parser check, and Python-cell adoption. - **Shared formulas:** every cell of the group gets `FormulaKind::Shared` with the group's `si` (counted per sheet), and the first cell also gets the range. Unlike `.xlsx`, where the other cells have empty text, each cell here gets its own text, decoded from the shared tokens at that cell. [MS-XLSB] defines it that way, with relative offsets that wrap around the grid. Shifting the first cell's text is not exact; the review of the GridCraft PR found that. `CellFormula.text`'s doc says so. - **Arrays:** the first cell gets `FormulaKind::Array` and its range. The other cells keep their cached values and have no formula, as in `.xlsx`. A dynamic array also has its `cell_metadata`, even across a `BrtValueMeta` written in between. Clearing the other cells, and deciding from `fDynamic` whether to spill, is the consumer's call (gridcraft#3825). - **Kept as values:** a formula that needs something the reader doesn't read keeps only its saved result, with a warning: user defined names (XLSB names aren't read yet), table references, other workbooks, `PY(index, …)` (code stored elsewhere in the package) and data tables. So do malformed tokens or records, and formulas past the allowance. A workbook whose links can't be read opens with values only, as before. ## Changed from the GridCraft PRs - craft-xlsx's own function table, tree and printer, instead of GridCraft's formula crate and the table and helpers shared with its `.xls` decoder. - A single-cell dynamic-array formula is read in commit 2, where #3818 left dynamic arrays to #3821: with `cell_metadata` there is nothing more to decide. - The test of GridCraft's 8,192-character parser limit is replaced by one showing that a shared copy may be longer than its first cell. Tests that saved to `.xlsx` with GridCraft are dropped. The rest of the PRs' tests come along in `tests/xlsb.rs`, with craft-xlsx's model in the expectations. - `Limits` gets a new public field, `binary_formula_text`. Code that builds `Limits` with `..Limits::default()` is unaffected. ## Checked against Excel Excel saved the same workbook (`make_probe.ps1` in the GridCraft work: refs, 3-D refs, names, shared, CSE and dynamic arrays, newer functions, LET, a table, an external link, unions and intersections, array constants) as `.xlsb` and as `.xlsx`. Read through craft-xlsx, all 33 formulas this decodes from the `.xlsb` match the `.xlsx`: same text, kind, range and shared index, with the same cell metadata. The other 5 use tables, an external link or defined names, and keep their values with those warnings. A second, wider round (commits 8 and 9) saved three more workbooks both ways: - 487 functions Excel accepts on a worksheet: every name in the function table except 113 macro-sheet-only commands, and about 120 newer `_xlfn.` functions, LET, LAMBDA and its helpers, GROUPBY, the REGEX functions, `TRIMRANGE` and `A1:.A5`, IMAGE, RTD, CUBEVALUE and an add-in function; - operators and precedence, unary and percent chains, unions, intersections, whole rows and columns, 3-D references and spans, quoted and cell-like sheet names, array constants, edge-magnitude numbers, a 255-character string, unicode, errors and missing arguments, legacy and dynamic-array `@`, references to a deleted sheet, column and row; - 246 shared-formula cells, including relative references that wrap at the grid's edges, legacy array formulas, spills, spill references and a data table; - 60 formulas typed with spaces and line breaks, and 62 number literals around Excel's switch to scientific notation. Before commit 8, 725 formulas matched and 20 differed. Now 744 of the 745 match, with the same text, kind, range and cell metadata, and every non-first shared-formula cell matches Excel's own `Range.Formula`. The other one is a deleted column on a sheet named like a cell (`'S3'!#REF!`). That is #1330's: it prints the sheet before `#REF!`, which this PR doesn't. The spacing and number workbooks match in full. Two inline LAMBDA calls, an add-in call and a data table keep their saved results, as described above. ## Ran locally (own target directory) - `cargo fmt -p craft-xlsx --check`: clean - `cargo clippy -p craft-xlsx --all-targets --all-features -- -D warnings`: clean (and `cargo clippy --workspace --all-targets -- -D warnings` after commit 9) - `cargo test -p craft-xlsx --all-features`: 74 pass, 0 fail (after commit 7); 76 after commit 9. No other workspace crate depends on craft-xlsx. - The new tests fail without their commit's code: 5 of 6 for commit 3 (the sixth checks that unreadable shared formulas keep their values, which commit 2 already does), and 1 for commit 4. The tests for commits 8 and 9 fail without them. ## For the coordinator Once this is merged, gridcraft#3810, #3818 and #3821 can be closed, each with a note like "Ported to craft-xlsx in storytold/craft-libs#1166; GridCraft gets it when it switches to craft-xlsx." GridCraft's remainder is the mapping. That means `from_file` and the implicit `@` (as for `.xlsx`), Python cells, clearing array cells, spilling dynamic arrays, the `xlsb-import.md` / `gaps.md` / `file-format-parity.md` notes and the engine's use of `read_xlsb_with`. I'll redo those against the switch. <!-- This is an auto-generated comment: release notes by coderabbit.ai --> ## Summary by CodeRabbit * **New Features** * Binary Excel workbooks now display decoded formula text in familiar spreadsheet notation, including shared and array formulas. * Shared-formula cells show formula text adjusted to their own cell references. Array-formula follower cells do not display separate formula text. * **Bug Fixes** * When formulas cannot be decoded or exceed supported limits, cells retain their saved results; a warning may be provided. * **Documentation** * Clarified formula display behavior and documented limits on formula text, token length, nesting depth, and array constants. <!-- end of auto-generated comment: release notes by coderabbit.ai -->
9.0 KiB
craft-xlsx
Excel workbooks: .xlsx, .xlsm, .xltx, .xltm (SpreadsheetML in an OPC package,
transitional and strict) and .xlsb (the same package with binary parts). Clean-room, from
ECMA-376 Part 1 (SpreadsheetML), [MS-XLSB] and observed files. No unsafe.
This is the part layer. Package::open opens the container through craft-opc and reads:
| Call | Gives |
|---|---|
workbook() |
Workbook: sheets in tab order (name, id, visibility, kind, part), the 1904 date flag, defined names, the active tab, and the whole part as a tree |
shared_strings() |
StringItems: text, formatted runs, phonetic readings |
styles() |
Styles: number formats, fonts, fills, borders, cell and cell-style formats, named styles, differential formats, the indexed palette, as indexed tables |
sheet(entry, each) |
each Row of Cells to a callback, then a SheetPart: the rest of the worksheet as a tree, its relationships, columns and merged cells |
opc(), read_xml(), optional_xml(), rels() |
any other part |
Every part is read with fixed prefixes (ns::CANONICAL): the SpreadsheetML main namespace
(transitional or strict) unprefixed, the others by ns::NAMESPACES (r:id, mc:AlternateContent,
x14:sparklineGroups, xm:sqref), and the prefixes in mc:Ignorable and mc:Choice/@Requires
rewritten to match. A tree kept from a part goes back out with ns::declare, which declares what
it uses. A name in a namespace that is not listed reads as ?:name and is not written.
A cell keeps the file's terms: a shared-string index, a format index, the formula text as the
file spells it, value and cell metadata indexes. CellValue::resolve gives the value it shows.
Shared formulas are not expanded and formulas are not parsed: that needs a formula grammar.
Writing
Document::read holds a whole SpreadsheetML workbook: the Workbook, shared strings, Styles,
each worksheet's Rows and SheetPart, and every other part as a KeptPart (bytes and content
type: drawings, charts, the theme, document properties, a VBA project, their relationships).
Document::write regenerates the workbook part (sheets, defined names, date system, active tab),
the worksheets (dimension, columns, merged cells, rows), the shared strings and the styles tables
from those fields, keeps the rest of each part's tree in place, and writes the kept parts as they
were. Part names and relationship ids are kept, so a file read and written without changes points
where it did, and writing it twice gives the same bytes. The calculation chain is dropped (Excel
rebuilds it). XlsxKind is the workbook part's content type (.xlsx, .xlsm, .xltx, .xltm).
Typed worksheet features (SheetPart::features), each mirroring its element with optional
attributes and the unnamed ones kept: sheet views (panes, selections), print setup (options,
margins, page setup, header and footer, page breaks), data validation (also read from the x14
extension), conditional formats (colour scales, data bars, icon sets, rule extensions kept),
hyperlinks (with their URLs), the legacy drawing. Comments (Comments, from the comments part)
and the note shapes of the VML drawing (NoteShape). A sheet that gains comments and has no VML
drawing gets one (notes_vml, in a shape block no kept drawing uses); an existing VML drawing is
kept as read. Extensions in extLst are carried through unchanged.
Drawings and charts, on craft-drawingml: Package::drawing reads a sheet's xdr:wsDr into a
Drawing of Anchors, each with a typed Placement (two-cell, one-cell, absolute, with EMU
offsets) and its object kept as written; Anchor names the object's kind, its cNvPr, its chart
or image relationship and its a:xfrm, and craft_drawingml::read reads its fill, line and text.
mc:AlternateContent items (Office 2016 charts, ink) are kept in place, and
Drawing::alternate_anchors reads the anchors inside them. Package::chart reads a chart part
(c: or cx:) into craft_drawingml::chart::Chart; Package::theme reads the theme. A
Document worksheet holds its DrawingPart (regenerated, its relationships kept by id);
Document::add_chart and Document::add_picture add a chart part written by craft-drawingml
(a chartEx part behind mc:AlternateContent for the Office 2016 types) or an image, with its
anchor. Chart parts and media are kept parts.
Tables: Package::tables reads a sheet's table parts (in tableParts order) into Table
(every CT_Table attribute, TableColumns with calculated and totals formulas,
TableStyleInfo; autoFilter, sortState and extensions kept as written). A Document
worksheet holds them as TableParts, regenerated on writing with their relationships and
tableParts entries; a removed table takes both with it. Every part name a document keeps is
reserved before new parts are named, so a new part never takes a name another sheet's part
keeps.
PivotTables: Workbook::pivot_caches lists the caches by id; Package::pivot_cache reads a cache
definition (PivotCacheDefinition: source, fields with shared items and grouping),
Package::pivot_records streams its records, and Package::pivot_tables reads a sheet's
PivotTables (PivotTableDefinition: location, fields and items, axes, page and data fields,
style) with the cache each uses. SharedItems::describe gives the flags Excel writes for a field's
values, and records_xml writes a records part. A Document holds caches as PivotCacheParts
and a worksheet's tables as PivotTableParts, regenerated with their relationships and
pivotCaches entries; an OLAP cache (and a table on one) stays a kept part.
Cell metadata: Package::cell_pictures (and Document::cell_pictures) follows a cell's vm
through the value and future metadata to its _localImage rich value and image, as
CellPicture (PNG or JPEG, alternative text). Every table is bounded (MAX_RECORDS, MAX_PICTURE,
MAX_PICTURES_TOTAL); what does not resolve is None with a warning, and the cell keeps its saved
value. Document::add_cell_metadata writes the metadata part, the rich-value parts and the images
for new pictures (vm = i + 1) and the XLDAPR dynamic-array metadata (cm = 1).
The parts this crate writes use the transitional namespaces; a strict file's kept parts stay as they were. A binary workbook is not written.
A binary workbook gives the same Workbook, strings, rows and merged cells, and the same Styles:
number formats, fonts, fills, borders, cell and cell style formats, cell styles, differential
formats and the indexed colours, as Excel writes them in styles.xml (default fill colours and
empty settings left out, a cell style format's apply*="0", a number format with a newer locale tag
in its Excel 2007 spelling, as formatCode has it). Custom table styles and recent colours are not
read; a damaged styles part is a warning and no styles, while one past a cap (65,536 entries in a
table, 1,024 gradient stops) or past binary_text is too large. Its cell formulas are decoded from
their tokens into the text an .xlsx file holds (_xlfn. and _xlpm. names as written, apart from
the four ISO/IEC 29500 functions .xlsx names bare, no implicit @, the spaces and line breaks
typed in the formula, numbers as Excel spells them), with FormulaKind::Shared or
FormulaKind::Array and their ranges, and BrtCellMeta as the cell metadata index. Every cell of a
shared formula gets its own text, decoded at the cell, since the file keeps the tokens once. A
formula whose decoding needs something the reader does not read keeps only its saved result, with a
warning saying why: user defined names, table references, other workbooks, Python code stored
outside the cell, data tables. The XLSB function table (xlsb/ftab.rs) and the expression printer
are this crate's own, apart from craft-xls's.
Hostile input
Limits.package: ZIP entries, bytes per part and in all (craft-opc).Limits.xml: one XML part. Rows and shared strings are streamed, so this bounds one row, one string and what is left of a part, not a sheet's length.Limits.binary_records,binary_cells,binary_text: a binary workbook, over all its parts (40 million records, 4 million cells and as many shared strings, 256 MiB of text, ascraft-xlsallows).binary_formula_text: decoded formula text over the workbook (128 MiB); past it, formulas keep their saved results. A formula's tokens are bounded at 16 KB, its nesting at 256 levels and its array constants at 65,536 values in all, each charged before it is held; once the formula text allowance is spent, no formula is decoded. Fixed caps: 1024 sheets, 100,000 merged ranges per sheet, 64 nested extension blocks.- Cells and rows outside the 1,048,576 × 16,384 grid are dropped and counted.
SpreadsheetML is read leniently: a damaged optional part is a warning, a damaged worksheet keeps the rows before the damage. A binary part that does not parse fails the read, since skipping a record would lose data without a trace.
Shared with craft-xls and craft-ods through craft-sheet: CellRef, Range, Value,
CellError, Visibility, sheet-name quoting, built-in number formats and the palette.