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.
Generalkeeps 15 significant digits when unconstrained and switches to scientific notation at 1E+15 and below 1E-9. WithmaxCharsit 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 byTests/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 inExcelDeviations.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 foryyyy-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.
*xfill is reported (index and character) but not expanded: the grid pads to the column width._xalways yields one ordinary space rather than the width ofx.- 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.