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 inLoadOptions.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.extractAllcreates directories one component at a time and never follows symbolic links. Truncated and randomly corrupted archives fail withSheetIOError(fuzz tests). - XML: Foundation
XMLParserin 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
numFmtelement, is imported asGeneral(LibreOffice shows ids 27–36 as dates in an en-US profile; ORIX Sheet shows the serial number). - Id 14 is
m/d/yyyyand id 22m/d/yyyy h:mm, not themm-dd-yyandm/d/yy h:mmprinted in the specification: Excel displays id 14 as the system short date (m/d/yyyyin en-US). Id 47 ismm:ss.0(the specification printsmmss.0). Cross-check: the fixturebuiltin_numfmt.xlsx(written byGenerators/gen_builtin_numfmt_xlsx.py, ids only, no<numFmts>) converted by headless LibreOffice 26.8 with an en-US profile shows11/21/2026for 46347 with id 14,11/21/2026 13:30for id 22 and01:23.3for id 47, which is whatNumberFormat.cached(code).renderreturns after import (importedBuiltinIdsDisplayAsInExcel). openpyxl'sBUILTIN_FORMATSkeeps the specification's spellings for 14, 22 and 47. - Writing uses the same table in reverse, exact match only:
m/d/yyyyis written as id 14 (which Excel renders as the short date of the reader's system), whilemm-dd-yy,m/d/yy h:mmor"$"#,##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,.fodsand 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
lossissue 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:509for 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.