SPB Git

Repository Public

COMPATIBILITY.md 17.9 KB

# SheetIO — compatibility matrix

What each format reads, writes, preserves and drops. Every statement below is backed by a test in Tests/SheetIOTests; where an independent program was used to check the result it is named (openpyxl 3.1.5, pandas 3.0.2, LibreOffice 26.8.2 headless).

Microsoft Excel itself was not run. XLSX output is validated by openpyxl and LibreOffice only; nothing here is a claim that Excel opens a file "without repair", although the writer follows the element order, limits and conventions Excel documents (and that XlsxWriter/openpyxl rely on).

Legend: R read · W written · P preserved byte-for-byte without being interpreted · ≈ approximated (reported) · ✗ dropped with a CompatibilityIssue · – not applicable.

# Formats

Format Read Write Notes
.orixsheet yes yes native package, lossless (see NATIVE-FORMAT.md)
.xlsx yes yes
.xlsm yes yes VBA project preserved, never executed
.ods yes yes values, formulas, basic styles, layout
.csv, .tsv yes yes active sheet only on write
.json, .jsonl yes yes array of objects / array of arrays
.xls (BIFF8) no no detected (magic bytes); clear error. No reader is shipped rather than a partial one
.xlsb no no detected as FileFormat.xlsb (an OPC package whose main part is xl/workbook.bin, or the extension when the content says nothing); clear error asking to save as .xlsx, raised by WorkbookReader.load before the XLSX reader runs
.fods no no recognised by its extension; clear error
.numbers no no detected (single file and package); error explains File › Export To › Excel/CSV

FileFormat.detect looks at the content first (ZIP entries, mimetype, compound-file magic, package directory), then at the extension: an ODS renamed .xlsx or an .xlsm renamed .xlsx is recognised for what it is. An Excel binary workbook is recognised by content too, including when it is renamed .xlsx or .xlsm or carries a VBA project; a genuine XLSX named .xlsb opens normally.

# XLSX / XLSM

Feature Read Write Verified by Notes
Numbers, text, booleans, errors R W openpyxl, LibreOffice #CIRC! (ORIX-only) is written as #NUM!
Shared strings, inline strings R W (shared) openpyxl rich-text runs flattened (≈, info); phonetic runs ignored; _xHHHH_ escapes decoded/encoded
ISO dates (t="d") R – fixture converted to serials; a date format is added when the cell has none
Date system 1900 / 1904 R W openpyxl
Formulas + cached values R W openpyxl, LibreOffice cached values trusted on load (CalcEngine(trustCachedValues:)); a formula without cached value sets fullCalcOnLoad
Shared formulas R (expanded) – fixture written back as ordinary formulas
Legacy array formulas (CSE) R ≈ – openpyxl fixture become dynamic arrays (info); written back as dynamic arrays
Dynamic arrays R W openpyxl (as array formula) cm + xl/metadata.xml (XLDAPR), spill range in ref, spilled cells written as cached values. Not verified in Excel
_xlfn. / _xlfn._xlws. / _xlpm. prefixes R W openpyxl list in FormulaCompat.futureFunctions (Excel 2010 → 365); A1# ⇄ _xlfn.ANCHORARRAY, @x ⇄ _xlfn.SINGLE
Unknown functions R W test formula and cached value kept, one warning per function
Formulas with external references [1]Sheet!A1 ✗ value kept – fixture loss issue; the engine has no external reference syntax
What-if data tables ✗ value kept – – loss issue
Fonts, fills, borders, alignment, protection R W openpyxl, LibreOffice deduplicated on write; cellXfs index = style id
Theme colours + tint, indexed colours R (resolved to RGB) W as RGB fixture tint applied in HLS, within ±2 per channel of Excel's values
Gradient / patterned fills ≈ – fixture first stop / foreground colour (warning / info)
Diagonal borders, stacked vertical text ✗ – fixture warning / info
Number formats R W openpyxl, LibreOffice, test built-in ids 0–22 and 37–49 are defined, in one table shared with the format engine (SheetFormat.BuiltinFormats, exposed as NumberFormats.builtin); ids 23–36 (reserved for locale-specific CJK formats) and any other id without a numFmt definition fall back to General; custom formats from id 164. See "Built-in number formats" below
Column widths, row heights R W openpyxl widths: points = width × 5.25 (see below)
Hidden rows/columns, outline levels, collapsed R W openpyxl
Row and column default styles R W round trip
Merged cells R W openpyxl, LibreOffice overlapping merges dropped on write (✗)
Frozen panes, zoom, gridlines, headings R W openpyxl, LibreOffice split (non-frozen) panes ignored
Tab colour, hidden sheets, active sheet R W openpyxl veryHidden becomes hidden
Sheet protection R (flag) W (flag) openpyxl password hash not kept (warning)
Hyperlinks (external, internal, tooltip) R W openpyxl, LibreOffice max 65,530 per sheet, URL ≤ 2,079 chars
Data validation (incl. x14 cross-sheet lists) R W openpyxl, LibreOffice titles ≤ 32, messages ≤ 255 chars (truncated, reported); inline lists > 255 chars dropped
Conditional formatting R W openpyxl, LibreOffice cellIs, expression, colorScale (2–3 points), dataBar, iconSet, top10, aboveAverage, duplicate/unique, containsText, blanks, errors, with dxf (bold, italic, strike, colour, fill, number format). Other text/time rules are read as their equivalent formula (info). x14 extensions (negative bar colours, custom icons) ✗
AutoFilter R (range) W openpyxl, LibreOffice filter criteria ✗ (info); rows they hid stay hidden
Tables R W openpyxl, LibreOffice name, range, header/totals, columns, totals functions, calculated column formula, style. Column names are aligned with the header cells (Excel requires it)
Notes (legacy comments) R W openpyxl, LibreOffice comments part + VML drawing
Threaded comments R ≈ W as notes fixture replies merged into one text; written back as notes (warning)
Page setup, margins, header/footer, print options, breaks R W openpyxl paper sizes Letter, Legal, Tabloid, Executive, A3, A4, A5, B4, B5
Print area, print titles R W openpyxl, LibreOffice several print areas merged into their bounding range (info)
Defined names (workbook and sheet scope, hidden, comment) R W openpyxl, LibreOffice
Document properties (core) R W openpyxl
Charts, images, shapes (DrawingML) P P openpyxl sees chart + image; LibreOffice keeps the chart attached to the same sheet; not displayed or editable yet
Pivot tables + caches P P bytes compared dropped if their sheet is deleted
VBA project P P in .xlsm, ✗ in .xlsx bytes compared, openpyxl keep_vba never executed; hasMacros; loss issue when saving as .xlsx
External link definitions P P bytes compared never fetched; hasExternalLinks
Custom XML, custom document properties, theme P P bytes compared
Chart sheets, macro sheets, dialog sheets ✗ – fixture warning at load
Sparklines, slicers, timelines, form/ActiveX controls, OLE objects, sheet background, data connections ✗ – fixture (sparklines) warning at load
SheetObject (ORIX charts…), Workbook.attachments – ✗ test loss issue: chart mapping is someone else's job, attachments are native-only

Limits enforced on write: 1,048,576 × 16,384 cells (error), 32,767 characters per cell (truncated, loss issue with the cell address), sheet names (error before anything is written), 8,192-character formulas (warning), about 65,490 cell formats (warning). WorkbookWriter.preflight reports all of them before saving.

# Units

Column widths in OOXML are expressed in characters of the default font, padding included. With the reference font (Calibri 11, 7 px digit width) one unit is 7 px = 5.25 pt, so points = width × 5.25 and back. The default 8.43-character column is 48 pt. Row heights are points in both models. LibreOffice uses its own font metrics: a file written by LibreOffice from an ODS shows widths about 6 % smaller than the points we wrote (112 twips per character instead of 105).

# ODS

Feature Read Write Verified by
Values: float, percentage, currency, date, time, boolean, string, errors R W LibreOffice
Formulas (OpenFormula ⇄ canonical) R W LibreOffice computes the same results as CalcEngine for formulas written without cached values
Array formulas (matrix spans) R W LibreOffice
Fonts, colours, fills, borders, alignment, wrap, rotation, protection R W LibreOffice
Number formats: General, fixed, grouped, percent, scientific, date/time, text R W LibreOffice
Currency and other custom formats R (positive;negative sections) ✗ General loss issue naming the format
Column widths, row heights, hidden rows/columns R W LibreOffice
Merged cells R W LibreOffice
Hidden / protected sheets R W LibreOffice
Notes (annotations), hyperlinks R W LibreOffice
Workbook-scoped names R W LibreOffice
Sheet-scoped names R ✗ loss issue
Frozen panes, zoom, gridlines, active sheet (settings.xml) R W our own round trip only: a headless LibreOffice conversion ignores ODS view settings
Document properties R W round trip
Conditional formatting, validation, filters / database ranges ✗ ✗ warning at load, loss at save
Tables, outline, print settings, tab colour – ✗ loss / info issue
Charts, images, macros, embedded objects ✗ – warning at load; macros never run
Encrypted documents, flat .fods error –

OpenFormula conversion covers references ([.A1], [$Sheet.A1:.B2], quoted sheet names), ; argument separators, {1;2|3;4} arrays, COM.MICROSOFT. prefixed functions and whole columns/rows. Not convertible, value kept and reported: union ~ and intersection ! operators, 3-D ranges, references to other documents (reading); structured references, A1#, LAMBDA calls, sheet-scoped names (writing).

# CSV / TSV

Feature Read Write
RFC 4180 quoting, embedded quotes / delimiters / newlines, CR · LF · CRLF R W
Delimiter sniffing , ; tab | (quote-aware) or forced R configurable
Encodings: UTF-8 (± BOM), UTF-16 LE/BE (± BOM), Windows-1252, Latin-1 R (detected or forced) W
Type inference: numbers, thousands groups, percent, booleans, errors, ISO dates/times R (optional) –
Decimal comma (detected with ;/tab/| delimiters, or forced) R W (raw values)
Leading zeros and digit strings that are not exact doubles kept as text –
Fields starting with = text unless parseFormulas values only
Quoting: minimal / all / non-numeric; line ending; formatted values via closure – W
More than 1,048,576 rows or 16,384 columns tooLarge error pointing to the data engine –
Formulas, formatting, other sheets, names, tables… – ✗ (loss / info issues, also from preflight)

Checked against pandas in both directions.

# JSON / JSON Lines

Array of objects (keys → header row, order of first appearance, duplicate headers disambiguated on write) and array of arrays, both directions; a top-level object is one record. Nested arrays and objects are flattened to compact JSON text in their cell (warning with the count). Dates are written as ISO 8601 strings when the cell has a date format (optional). Nesting deeper than 64 levels and more than 1,048,576 records are errors.

# Security

  • ZIP: own reader on zlib. Entry names with .., absolute paths, backslashes, drive letters or control characters are rejected; limits on entry count (100,000), entry size (1 GiB), total size (4 GiB) and compression ratio (500:1 above 32 MiB), all configurable in LoadOptions.zipLimits; declared sizes are never trusted (inflation stops at the declared size, memory grows with real output); CRC-32 verified; duplicate names and overlapping entries rejected; encrypted archives refused. extractAll creates directories one component at a time and never follows symbolic links. Truncated and randomly corrupted archives fail with SheetIOError (fuzz tests).
  • XML: Foundation XMLParser in SAX mode, external entities off, any DTD / entity declaration aborts the load (unsafeArchive).
  • Macros are never executed; external links are never fetched.
  • Cell budget: LoadOptions.maxCells (200 million) bounds what a file can materialise, including ODS repeat counts.

# Measured performance

Apple M-series laptop, release build (swift test -c release), one sheet of N rows × 10 columns (6 numeric columns, 1 unique text, 1 repeated text, 1 date, 1 formula with cached value). Reproduce with ORIX_BENCH=1 swift test -c release -Xswiftc -enable-testing --filter PerformanceTests.

Measured on an Apple M5 Max (48 GB), macOS 27, Swift 6.4, 2026-11-21:

1,000,000 cells Write Read File size
.orixsheet 0.58 s — 1.7 M cells/s 0.10 s — 10.4 M cells/s 3.5 MB
.xlsx 1.19 s — 0.84 M cells/s 1.90 s — 0.53 M cells/s 8.5 MB
.csv 0.46 s — 2.2 M cells/s 0.19 s — 5.4 M cells/s 10.2 MB
.jsonl 0.46 s — 2.2 M cells/s 0.18 s — 5.7 M cells/s 10.8 MB
.ods 1.45 s — 0.69 M cells/s 4.58 s — 0.22 M cells/s 8.9 MB
5,000,000 cells Write Read
.orixsheet 3.1 s 0.5 s
.xlsx 6.5 s 10.2 s
  • The native read includes parsing 100,000 (resp. 500,000) formula texts and is checked cell by cell against the source workbook; the XLSX and CSV reads are checked the same way.
  • Memory: the benchmark process peaks at 0.47 GB after the 1 M-cell native round trip and 0.79 GB after the XLSX one, while holding the source workbook and the loaded copy (the SheetCore store itself needs roughly 200 MB per million cells); 3.7 GB at the end of the 5 M-cell run. The XLSX reader keeps one decompressed worksheet part in memory (about 50 MB per million cells) and no DOM.
  • Other measurements printed by the test suite (debug build): 240,000 string cells with 120,000 distinct strings — write 0.9 s, read 1.2 s.
  • A debug build is 2–4 times slower.

# Built-in number formats

SheetIO has no table of its own: NumberFormats.builtin is SheetFormat.BuiltinFormats.codes, so what a cell with a built-in numFmtId displays after import is what the format engine defines for that id. The full id → code list is pinned by a test in each package (BuiltinNumberFormatTests.tableIsPinnedAndShared, BuiltinAndEditingTests.builtinTableIsPinned).

  • Defined: 0–22 and 37–49 (36 ids). ECMA-376 Part 1 §18.8.30 lists 0–4, 9–22, 37–40 and 45–49 for all languages; 5–8 and 41–44 (currency, accounting) are the en-US codes Excel uses.
  • Not defined: 23–36. The specification reserves 27–36 for zh-tw, zh-cn, ja-jp and ko-kr and does not list 23–26. A cell using one of them, or any id from 50 to 163 without a numFmt element, is imported as General (LibreOffice shows ids 27–36 as dates in an en-US profile; ORIX Sheet shows the serial number).
  • Id 14 is m/d/yyyy and id 22 m/d/yyyy h:mm, not the mm-dd-yy and m/d/yy h:mm printed in the specification: Excel displays id 14 as the system short date (m/d/yyyy in en-US). Id 47 is mm:ss.0 (the specification prints mmss.0). Cross-check: the fixture builtin_numfmt.xlsx (written by Generators/gen_builtin_numfmt_xlsx.py, ids only, no <numFmts>) converted by headless LibreOffice 26.8 with an en-US profile shows 11/21/2026 for 46347 with id 14, 11/21/2026 13:30 for id 22 and 01:23.3 for id 47, which is what NumberFormat.cached(code).render returns after import (importedBuiltinIdsDisplayAsInExcel). openpyxl's BUILTIN_FORMATS keeps the specification's spellings for 14, 22 and 47.
  • Writing uses the same table in reverse, exact match only: m/d/yyyy is written as id 14 (which Excel renders as the short date of the reader's system), while mm-dd-yy, m/d/yy h:mm or "$"#,##0_);("$"#,##0) are written as custom formats and read back unchanged.

# Known limitations

  • Excel was not launched: XLSX fidelity is established against openpyxl and LibreOffice only.
  • .xls, .xlsb, .fods and Numbers documents are not readable.
  • Formulas the parser cannot represent (external workbook references, data tables) are replaced by their cached values at load, with a loss issue per cell.
  • Rich text inside a cell is flattened; per-run formatting is lost.
  • Threaded comments are written back as notes.
  • Charts, images and pivot tables imported from XLSX are carried along but not rendered, and are not updated when the cells they refer to change. Chart sheets are dropped.
  • Explicit fonts equal to the workbook default read back as "default" (nil); explicitly black text reads back as "automatic".
  • LibreOffice does not evaluate _xlfn.ANCHORARRAY (A1#) or LAMBDA: it shows #NAME? / Err:509 for those formulas in files we write (the formulas are stored the way Excel stores them).
  • A dynamic-array formula is flagged as such when it has a recorded spill, a #SPILL! result, or a dynamic-array function / spill reference / array constant at the top of its expression. A formula such as =ABS(A1:A3) that the engine evaluated to a single value is written as an ordinary formula.
  • ODS: conditional formats, validations, filters and print settings are not converted in either direction.
  • Workbook-level protection, custom views, scenarios and ignored-error flags are not modelled.
Files main
Menu

Theme