Repository Public
1# SheetFormat — compatibility notes23Number-format engine of ORIX Sheet: compiles Excel format codes and renders cell values for the grid,4`TEXT()`, CSV and PDF export.56## Supported grammar78| Feature | Support |9|---|---|10| Sections | up to four: `positive;negative;zero;text`; a lone `@` section is the text section |11| Conditions | `[<n]` `[<=n]` `[>n]` `[>=n]` `[=n]` `[<>n]` on the first two sections |12| Colours | `[Black]` `[Blue]` `[Cyan]` `[Green]` `[Magenta]` `[Red]` `[White]` `[Yellow]`, `[Color 1]`…`[Color 56]` (Excel default palette), returned in `RenderedCell.color` |13| Digit placeholders | `0` `#` `?` in the integer part, decimals, exponent, numerator and denominator |14| Grouping and scaling | `#,##0` thousands separator; trailing commas divide by 1000 each (`#,##0,,"M"`) |15| Percent | `%` multiplies by 100 (each occurrence) |16| Scientific | `0.00E+00`, `0.0E-0`, `e+`; engineering steps with several integer placeholders (`##0.0E+0`) |17| Fractions | `# ?/?`, `# ??/??`, `# ???/???`, fixed denominators (`# ?/8`, `# ??/100`), improper (`?/?`) |18| Literals | `"text"`, `\x`, bare punctuation, `_x` (one space), `*x` (fill: position and character in `RenderedCell.fillIndex` / `fillCharacter`) |19| Text | `@` (repeatable), with literals around it |20| 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 |21| 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) |22| Locale tags | `[$€-40C]` → the symbol as a literal; `[$-409]`, `[$-F800]`, `[DBNum1]`… are accepted and ignored |23| General | `General`, also with literals and sections (`General;(General)`) |24| Built-ins | OOXML ids 0–22 and 37–49 (`BuiltinFormats`), en-US codes |2526Rendering rules:2728- Numbers are rounded half away from zero on their 15-significant-digit decimal expansion (what Excel29 stores and shows): 0.285 → `0.29`, 1.005 → `1.01`, 0.1+0.2 → `0.30`; digits past the 15th are zeros.30- A single section shows a leading `-` for negative numbers; the implicit negative section of a 2/3/4-section31 format receives the absolute value. A negative number that rounds to zero keeps its sign (`-0.00`).32- Dates use the workbook date system: 1900 (serial 60 = 29 Feb 1900, serial 0 = "January 0, 1900") or 1904.33 Times are rounded to the smallest displayed fraction of a second (a time within half a second of midnight34 rolls to the next day). Negative values and serials past 31 Dec 9999 render `########`; the 1904 system35 shows negative dates with a leading minus.36- Text passes through numeric formats unchanged (or through the text section); booleans render TRUE/FALSE;37 errors render their code; blank renders an empty string.38- `General` keeps 15 significant digits when unconstrained and switches to scientific notation at 1E+15 and39 below 1E-9. With `maxChars` it behaves like an Excel cell: decimals are rounded to fit, then scientific40 notation is used, then `#` fills the width. `render(_:generalMaxChars:)` exposes the same limit41 (`TEXT(x,"General")` uses 11, like Excel).4243## Validation4445- `Tests/SheetFormatTests/Fixtures/libreoffice_formats.json`: 5 085 `=TEXT(value, format)` results computed46 by headless LibreOffice 26.8 (en-US profile), generated by `Tests/SheetFormatTests/Generators/generate_format_fixtures.py`47 (70 numeric formats × 44 values, 61 date/time formats × 32 serials, text cases).48 4 246 are byte-identical; 839 differ in one of the documented ways below (each checked by a narrow rule49 in `ExcelDeviations.swift`, so any other difference fails the test).50- About 340 hand-written expectations (`EdgeTests.swift`): rounding, 15-digit behaviour, sections, colours,51 accounting fill, scientific, fractions, the 1900 leap bug, the 1904 system, minute disambiguation,52 General width fitting, locales, built-in table, editing helpers, cache thread-safety.53- A fuzz test renders 20 000 random format codes against 12 values in two locales (no crash, bounded General width).54- Micro-benchmark (`BenchmarkTests`, release build, Apple Silicon): about 6 million renders/s for `#,##0.00`,55 2.7 million/s for `yyyy-mm-dd hh:mm:ss`, 1.5 million/s for width-limited General.5657## Where LibreOffice differs and ORIX Sheet follows Excel5859| Deviation | LibreOffice | ORIX Sheet (Excel) | Cases |60|---|---|---|---|61| Serial < 61 | epoch 1899-12-30, no leap bug | 1900 system with 29 Feb 1900 | 268 |62| Seconds | truncated | rounded to the displayed precision | 114 |63| Negative number rounding to zero | `0.00` | `-0.00` | 76 |64| `#.##` without decimals | `5` | `5.` | 44 |65| `?` padding | U+2007 figure space | ordinary space | 31 |66| Blank fraction (`# ?/8` on 5) | drops the denominator width | keeps the full width in spaces | 218 |67| Fraction approximation | closest fraction (pi → `3 14/99`) | last continued-fraction convergent (pi → `3 1/7`, as Excel displays) | 4 |68| Fixed denominator tie | rounds half down | rounds half up (`0.125` as `?/4` → `1/4`) | 8 |69| Sign with explicit conditions | dropped in the catch-all section | kept (`[>100]…;[<-100]…;0.0` on −1 → `-1.0`) | 14 |70| `A/P` | always lowercase | case of the format code | 22 |71| `General` at 1E+15 | `1000000000000000` | `1E+15` | 1 |72| Large values in fraction / elapsed formats | `#FMT` | rendered | 38 |73| `TEXT("", "0")` | `0` | empty text | 1 |7475## Known deviations from Excel7677- Format codes are interpreted in the invariant en-US grammar; the locale only supplies separators and78 names. Locale ids in `[$-xxx]` do not switch the language of month/day names.79- `[DBNum]`, `[NatNum]`, Buddhist/era tokens (`b`, `e`, `g`), and `[$-F800]`/`[$-F400]` system formats are80 not interpreted (the tag is ignored and the rest of the code is used).81- The sign rule for formats with explicit conditions follows the behaviour documented for Excel by Apache POI;82 it was not checked against a live Excel.83- Fractions use the continued-fraction algorithm known to reproduce Excel; for denominators above 99 99984 the limit is clamped to five digits.85- `*x` fill is reported (index and character) but not expanded: the grid pads to the column width.86- `_x` always yields one ordinary space rather than the width of `x`.87- Unquoted letters that are not format tokens are treated as literals (Excel refuses such codes).88- General with a width uses the character count, not pixel widths of a proportional font.89