A number in a spreadsheet is read with the 15 significant digits Excel keeps, not the binary tail of a computed value by Maya-Kid · Pull Request #1032 · deeplethe/utopia · GitHub
Skip to content

A number in a spreadsheet is read with the 15 significant digits Excel keeps, not the binary tail of a computed value - #1032

Merged
WaylandYang merged 3 commits into
deeplethe:devfrom
Maya-Kid:fix/spreadsheet-numbers-at-excel-precision
Oct 1, 2026
Merged

WaylandYang merged 3 commits into
deeplethe:devfrom
Maya-Kid:fix/spreadsheet-numbers-at-excel-precision

Conversation

@Maya-Kid

@Maya-Kid Maya-Kid commented Oct 1, 2026

Copy link
Copy Markdown
Contributor

Excel compares and shows numbers to 15 significant digits, but it writes the cached value of a formula to the file at full double precision. =0.1+0.2 is stored as 0.30000000000000004 and =1.1*1.1-1 as 0.21000000000000019, while Excel shows 0.3 and 0.21. parsers::spreadsheet prints the double in Rust's shortest round-trip form. That form keeps the tail whenever the computed double is not the one nearest the decimal.

Problem

A sheet with cached values the way Excel writes them, read on dev 2e7ba78:

| 合计 | 0.30000000000000004 |
| 增长率 | 0.2100000000000002 |
  • The text, the evidence quote and any value extracted from these rows carry digits the person never sees.
  • A search for 0.21 does not meet 0.2100000000000002.
  • Values Excel stored as the double nearest their decimal already read clean: 1395.0527999999999 reads 1395.0528, and 19.899999999999999 reads 19.9. Only computed values keep a tail.

Change

parsers::spreadsheet writes a Data::Float cell with excel_number: the value is rounded to 15 significant digits, then printed in its shortest decimal form.

  • Values that were already clean (19.9, 1200, 45306) read exactly as before.
  • Integers stay integers.
  • Non-finite values are left alone.
  • A value with more than 15 significant digits, which Excel itself cannot hold, is written to the 15 that Excel shows.

The new arm sits next to excel_date (#1017).

Verification

Run on macOS (arm64): Rust 1.98.1; the workspace suite against PostgreSQL 16 with pgvector 0.8.2, with Poppler 26.06.

crates/utopia-ingest/tests/spreadsheet_numbers.rs builds an .xlsx with values written the way Excel caches them, under the General format:

  • a_computed_number_reads_with_the_digits_excel_keeps: 0.3, 0.21 and -0.3. On dev with only this file added it fails with 0.30000000000000004, 0.2100000000000002 and -0.30000000000000004.
  • a_number_that_was_already_clean_reads_as_before: 1395.0528, 19.9, 1200, 123456789012345000 and 0.125. It passes on dev and here.

Other checks:

  • The openpyxl ledger from A date in a spreadsheet is read as the date it shows, not as Excel's day count #1017 reads exactly as before.
  • cargo test --locked -p utopia-ingest: 125 passed.
  • cargo fmt --all --check and cargo clippy --locked --workspace --all-targets -- -D warnings are clean.
  • UTOPIA_TEST_REQUIRE_DB=1 UTOPIA_TEST_REQUIRE_PDFTOTEXT=1 cargo test --locked --workspace: 1,345 passed, 0 failed, 6 ignored. That run had this change merged with the Word non-breaking-hyphen fix opened alongside it.

Not in this change

A cell formatted as a percentage still reads as its fraction (0.125, not 12.5%). calamine gives no number format for cells that are not dates.

…l keeps, not the binary tail of a computed value

Signed-off-by: dada-yan <BinjunYann@gmail.com>

@WaylandYang WaylandYang left a comment

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Approving. Thanks @Maya-Kid. Reviewed at 0bc7515: rounding to 15 significant digits and printing the shortest form removes the binary tail of a cached formula value and leaves clean values, integers and non-finite values as they were. #1039 moves the cell text into a helper of its own; this lands first and that one picks the arm up.

@WaylandYang
WaylandYang merged commit 468ef0f into deeplethe:dev Oct 1, 2026
6 checks passed
@Maya-Kid
Maya-Kid deleted the fix/spreadsheet-numbers-at-excel-precision branch October 15, 2026 03:29
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

None yet

Projects

None yet

Development

Successfully merging this pull request may close these issues.

2 participants