SPB Git

Repository Public

COMPATIBILITY.md 6.3 KB

# SheetFormat — compatibility notes

Number-format engine of ORIX Sheet: compiles Excel format codes and renders cell values for the grid, TEXT(), CSV and PDF export.

# Supported grammar

Feature Support
Sections up to four: positive;negative;zero;text; a lone @ section is the text section
Conditions [<n] [<=n] [>n] [>=n] [=n] [<>n] on the first two sections
Colours [Black] [Blue] [Cyan] [Green] [Magenta] [Red] [White] [Yellow], [Color 1]…[Color 56] (Excel default palette), returned in RenderedCell.color
Digit placeholders 0 # ? in the integer part, decimals, exponent, numerator and denominator
Grouping and scaling #,##0 thousands separator; trailing commas divide by 1000 each (#,##0,,"M")
Percent % multiplies by 100 (each occurrence)
Scientific 0.00E+00, 0.0E-0, e+; engineering steps with several integer placeholders (##0.0E+0)
Fractions # ?/?, # ??/??, # ???/???, fixed denominators (# ?/8, # ??/100), improper (?/?)
Literals "text", \x, bare punctuation, _x (one space), *x (fill: position and character in RenderedCell.fillIndex / fillCharacter)
Text @ (repeatable), with literals around it
Dates yyyy yy mmmmm mmmm mmm mm m dddd ddd dd d; m/mm are minutes right after an hour token or right before a seconds token
Times hh h mm m ss s, .0 .00 .000, AM/PM, A/P (case of the format kept), elapsed [h] [m] [s] (any width)
Locale tags [$€-40C] → the symbol as a literal; [$-409], [$-F800], [DBNum1]… are accepted and ignored
General General, also with literals and sections (General;(General))
Built-ins OOXML ids 0–22 and 37–49 (BuiltinFormats), en-US codes

Rendering rules:

  • Numbers are rounded half away from zero on their 15-significant-digit decimal expansion (what Excel stores and shows): 0.285 → 0.29, 1.005 → 1.01, 0.1+0.2 → 0.30; digits past the 15th are zeros.
  • A single section shows a leading - for negative numbers; the implicit negative section of a 2/3/4-section format receives the absolute value. A negative number that rounds to zero keeps its sign (-0.00).
  • Dates use the workbook date system: 1900 (serial 60 = 29 Feb 1900, serial 0 = "January 0, 1900") or 1904. Times are rounded to the smallest displayed fraction of a second (a time within half a second of midnight rolls to the next day). Negative values and serials past 31 Dec 9999 render ########; the 1904 system shows negative dates with a leading minus.
  • Text passes through numeric formats unchanged (or through the text section); booleans render TRUE/FALSE; errors render their code; blank renders an empty string.
  • General keeps 15 significant digits when unconstrained and switches to scientific notation at 1E+15 and below 1E-9. With maxChars it behaves like an Excel cell: decimals are rounded to fit, then scientific notation is used, then # fills the width. render(_:generalMaxChars:) exposes the same limit (TEXT(x,"General") uses 11, like Excel).

# Validation

  • Tests/SheetFormatTests/Fixtures/libreoffice_formats.json: 5 085 =TEXT(value, format) results computed by headless LibreOffice 26.8 (en-US profile), generated by Tests/SheetFormatTests/Generators/generate_format_fixtures.py (70 numeric formats × 44 values, 61 date/time formats × 32 serials, text cases). 4 246 are byte-identical; 839 differ in one of the documented ways below (each checked by a narrow rule in ExcelDeviations.swift, so any other difference fails the test).
  • About 340 hand-written expectations (EdgeTests.swift): rounding, 15-digit behaviour, sections, colours, accounting fill, scientific, fractions, the 1900 leap bug, the 1904 system, minute disambiguation, General width fitting, locales, built-in table, editing helpers, cache thread-safety.
  • A fuzz test renders 20 000 random format codes against 12 values in two locales (no crash, bounded General width).
  • Micro-benchmark (BenchmarkTests, release build, Apple Silicon): about 6 million renders/s for #,##0.00, 2.7 million/s for yyyy-mm-dd hh:mm:ss, 1.5 million/s for width-limited General.

# Where LibreOffice differs and ORIX Sheet follows Excel

Deviation LibreOffice ORIX Sheet (Excel) Cases
Serial < 61 epoch 1899-12-30, no leap bug 1900 system with 29 Feb 1900 268
Seconds truncated rounded to the displayed precision 114
Negative number rounding to zero 0.00 -0.00 76
#.## without decimals 5 5. 44
? padding U+2007 figure space ordinary space 31
Blank fraction (# ?/8 on 5) drops the denominator width keeps the full width in spaces 218
Fraction approximation closest fraction (pi → 3 14/99) last continued-fraction convergent (pi → 3 1/7, as Excel displays) 4
Fixed denominator tie rounds half down rounds half up (0.125 as ?/4 → 1/4) 8
Sign with explicit conditions dropped in the catch-all section kept ([>100]…;[<-100]…;0.0 on −1 → -1.0) 14
A/P always lowercase case of the format code 22
General at 1E+15 1000000000000000 1E+15 1
Large values in fraction / elapsed formats #FMT rendered 38
TEXT("", "0") 0 empty text 1

# Known deviations from Excel

  • Format codes are interpreted in the invariant en-US grammar; the locale only supplies separators and names. Locale ids in [$-xxx] do not switch the language of month/day names.
  • [DBNum], [NatNum], Buddhist/era tokens (b, e, g), and [$-F800]/[$-F400] system formats are not interpreted (the tag is ignored and the rest of the code is used).
  • The sign rule for formats with explicit conditions follows the behaviour documented for Excel by Apache POI; it was not checked against a live Excel.
  • Fractions use the continued-fraction algorithm known to reproduce Excel; for denominators above 99 999 the limit is clamped to five digits.
  • *x fill is reported (index and character) but not expanded: the grid pads to the column width.
  • _x always yields one ordinary space rather than the width of x.
  • Unquoted letters that are not format tokens are treated as literals (Excel refuses such codes).
  • General with a width uses the character count, not pixel widths of a proportional font.
Files main
Menu

Theme