SPB Git

Repository Public

formats.md 17.1 KB

# File formats

Every format ORIX Sheet detects, reads or writes; the limits and options of each; what is lost; and the measured timings. The implementation is Packages/SheetIO (no UI, no network); the reference document written with the code is Packages/SheetIO/COMPATIBILITY.md, and the native format is specified in Packages/SheetIO/NATIVE-FORMAT.md.

Related: excel.md (XLSX feature matrix and behaviour differences), ../architecture/persistence.md (how the application opens, saves and exports), ../security.md (untrusted files).

Verification scope. XLSX output is checked with openpyxl 3.1.5 and headless LibreOffice 26.8.2; CSV with pandas 3.0.2; ODS with LibreOffice. Microsoft Excel and Apple Numbers were never launched: nothing below claims that Excel opens a file "without repair".

# Summary

Format Extensions recognised Detect Read Write In the app
ORIX Sheet document .orixsheet (package directory) content (manifest.json or workbook.json in a directory) yes yes opened and saved in place, autosave
Excel workbook .xlsx, .xltx content (ZIP with [Content_Types].xml and xl/…) yes yes imported as untitled; Export To ▸ Excel Workbook
Excel macro-enabled workbook .xlsm, .xltm content (a vbaProject.bin entry) yes yes imported; macros kept, never run; Export To ▸ Excel Macro-Enabled Workbook
OpenDocument spreadsheet .ods, .ots content (mimetype entry) yes yes imported; Export To ▸ OpenDocument
Comma-separated values .csv extension yes yes (one sheet) imported; Export To ▸ CSV — Active Sheet
Tab-separated values .tsv, .tab extension yes yes (one sheet) imported; Export To ▸ TSV — Active Sheet
JSON .json extension, or first significant byte [ / { when the extension is unknown yes yes (one sheet) imported; Export To ▸ JSON — Active Sheet
JSON Lines .jsonl, .ndjson extension yes yes (one sheet) readable by SheetIO; no export menu entry, and Info.plist declares no document type for these extensions
PDF — — no yes (print renderer, not SheetIO) Export To ▸ PDF — Active Sheet / All Sheets, Print…
Excel 97-2003 (BIFF) .xls, .xlt content (compound-file magic D0 CF 11 E0 A1 B1 1A E1) no no error message asking to save as .xlsx
Excel binary workbook .xlsb content (OPC package whose main part is xl/workbook.bin), else extension → FileFormat.xlsb no no refused by WorkbookReader.load before the XLSX reader, with an error message asking to save as .xlsx
Flat OpenDocument .fods extension no no error message
Apple Numbers .numbers content (package or ZIP with Index/…iwa / Index.zip) no no error message explaining File ▸ Export To ▸ Excel/CSV in Numbers
Password-protected Office files any compound-file magic no no error message (same container as .xls)

Source: FileFormat and WorkbookReader.load in Packages/SheetIO/Sources/SheetIO/WorkbookIO.swift; FileFormat.detect for .xlsb; menu entries in App/Sources/MainMenu.swift; document types in App/Resources/Info.plist.

Detection (FileFormat.detect) looks at the content first and at the extension second, so an ODS file renamed .xlsx, or an .xlsm renamed .xlsx, is recognised for what it is. While detecting, the ZIP directory is only listed; nothing is decompressed except a mimetype entry smaller than 256 bytes.

Every reader returns a LoadResult (workbook, list of CompatibilityIssue, preserved parts, hasMacros, hasExternalLinks); every writer returns the list of issues, and WorkbookWriter.preflight computes that list before writing. Issue severities are loss, warning and info.

# Limits common to all readers

Limit Default Where
Grid size 1,048,576 rows × 16,384 columns Limits (SheetCore)
Cells materialised from one file 200,000,000 LoadOptions.maxCells
ZIP entries 100,000 ZipLimits.maxEntries
ZIP entry size (uncompressed) 1 GiB ZipLimits.maxEntrySize
ZIP total size (uncompressed) 4 GiB ZipLimits.maxTotalSize
ZIP compression ratio 500 : 1 (applied above 32 MiB, per the package document) ZipLimits.maxCompressionRatio
JSON nesting depth 64 JSONValue.maxDepth
Files in an .orixsheet package 100,000 NativeFormat.maxPartCount
Raw size of one native cell chunk 256 MiB NativeCells.maxChunkRawBytes

Source: Options.swift, Zip/ZipArchive.swift, JSON/JSONValue.swift, Native/NativeFormat.swift, Native/NativeCells.swift.

The application uses the defaults; none of these limits is exposed in the user interface.

# .orixsheet (native)

Lossless for everything the model holds: values, formulas with cached results and spill ranges, styles, layout, tables, conditional formats, validations, comments, hyperlinks, embedded objects (charts, figures, pivots), print settings, defined names, attachments (scripts, queries, workflows), and the raw parts preserved from an imported Excel file.

  • Read: verifies SHA-256 and size of every part against the manifest; recovers what it can from a damaged package and reports the rest (strict mode available in the API, not used by the app).
  • Write: atomic (temporary sibling directory, then swap).
  • Options: NativeWriteOptions.appVersion, preview (PNG thumbnail; not set by the app), compressionLevel (zlib 1–9, default 6).
  • Lost: a LAMBDA value held by a cell is stored as #CALC! and recomputed on load.

Details: ../architecture/persistence.md.

# XLSX and XLSM

The reader is a streaming SAX reader over its own ZIP implementation (zlib); the worksheet part being parsed is held decompressed in memory, with no DOM. Shared formulas are expanded; legacy array formulas become dynamic arrays; theme and indexed colours are resolved to RGB; _xlfn. prefixes are removed on read and added on write.

Read, modelled and written: numbers, text, booleans, errors; formulas with cached values; dynamic arrays; fonts, fills, borders, alignment, protection flags; number formats; column widths, row heights, hidden rows/columns, outline levels; merged cells; frozen panes, zoom, gridlines, headings; tab colour, hidden sheets, active sheet; hyperlinks; data validation; conditional formatting (the rule kinds listed in excel.md); autofilter range; tables; notes; page setup, margins, header/footer, print area and titles, page breaks; defined names; core document properties; the 1900 and 1904 date systems.

Preserved byte-for-byte without being interpreted (kept in PreservedParts, written back to XLSX/XLSM, stored inside .orixsheet): charts, images and shapes (DrawingML), pivot tables and caches, the VBA project (XLSM only), external link definitions, custom XML, custom document properties, the theme. They are not displayed, not editable, and not updated when cells change.

Dropped or approximated, always with an issue: see the matrix in excel.md.

Write-side limits (checked by WorkbookWriter.preflight and by the writer):

Limit Value Behaviour
Text per cell 32,767 UTF-16 units truncated, loss issue with the cell address
Formula length 8,192 characters warning
Hyperlinks per sheet 65,530 the rest dropped, loss
URL length 2,079 characters per the package document
Cell formats about 65,490 warning
Validation prompt/error titles and messages 32 / 255 characters truncated, reported
Inline validation list 255 characters dropped, reported
Sheet names Excel's rules, unique case-insensitively error before anything is written
Colour scales 2 or 3 points others dropped, loss

Source: ExcelLimits in XLSX/WorksheetWriter.swift, Preflight.swift, and Packages/SheetIO/COMPATIBILITY.md.

Units: OOXML column widths are in characters of the default font; ORIX Sheet stores points with points = width × 5.25 (Calibri 11, 7 px digit width). A file without explicit default sizes is given Excel's defaults on import (48 pt columns, 15 pt rows); a workbook created in ORIX Sheet uses 80 pt and 20 pt.

Built-in number-format ids 0–22 and 37–49 are mapped to their en-US codes through one table shared with the format engine (SheetFormat.BuiltinFormats, exposed as NumberFormats.builtin); ids 23–36 (locale-reserved, CJK) and any unknown id without a custom definition fall back to General. Id 14 maps to m/d/yyyy and id 22 to m/d/yyyy h:mm (Excel's en-US short date, confirmed with LibreOffice's rendering of a file that carries only the ids), not to the mm-dd-yy / m/d/yy h:mm printed in ECMA-376 Part 1 §18.8.30; id 47 is mm:ss.0 (see excel.md).

# ODS

Values (float, percentage, currency, date, time, boolean, string, errors), formulas (OpenFormula ⇄ canonical syntax, including matrix formulas), fonts, colours, fills, borders, alignment, wrap, rotation, protection, the common number formats (General, fixed, grouped, percent, scientific, date/time, text), column widths, row heights, hidden rows/columns, merged cells, hidden and protected sheets, notes, hyperlinks, workbook-scoped names, frozen panes, zoom, gridlines, active sheet and document properties are read and written.

Not converted in either direction (warning at load, loss at save): conditional formatting, validation, filters and database ranges. Not written: tables (written as plain ranges), outline, print settings, tab colour, sheet-scoped names, attachments, embedded objects, currency and other custom number formats (written as General, with a loss issue naming the format). Not read: charts, images, macros, embedded objects. Encrypted documents and flat .fods files are refused.

Formulas that cannot be converted keep their value and are reported: union ~ and intersection ! operators, 3-D ranges and references to other documents when reading; structured references, A1#, LAMBDA calls and sheet-scoped names when writing. #SPILL!, #CALC! and #CIRC! have no OpenFormula spelling and are not written as formula errors.

The 1904 date system is converted on export (dates keep their calendar value; warning).

LibreOffice ignores ODS view settings in a headless conversion, so frozen panes, zoom and the active sheet are verified by ORIX Sheet's own round trip only.

# CSV and TSV

Read Write
Structure RFC 4180 quoting, embedded quotes, delimiters and newlines, CR / LF / CRLF one sheet (the active sheet in the app)
Delimiter sniffed among , ; tab | (quote-aware) or forced; TSV defaults to tab , (TSV: tab), configurable
Encoding BOM, then UTF-16 heuristic, then UTF-8 validity, then Windows-1252 fallback (reported as info); or forced: UTF-8, UTF-16 LE/BE, Windows-1252, Latin-1 UTF-8 by default; UTF-8 with BOM, UTF-16 LE/BE, Windows-1252, Latin-1 available
Types optional inference: numbers, thousands groups, percentages (format 0.00%), booleans, error codes, ISO dates and times (formats yyyy-mm-dd, yyyy-mm-dd hh:mm:ss, hh:mm:ss) raw values, or values as displayed through a formatter
Decimal comma detected when the delimiter is not ,, or forced optional for raw values
Leading zeros kept as text (007), as are digit strings that are not exact doubles —
Fields starting with = text, unless parseFormulas is set —
Quoting / line ending — minimal (default), all, non-numeric; CRLF (default), LF, CR
Size more than 1,048,576 rows or 16,384 columns raises tooLarge with a pointer to the data engine —

Source: CSVReadOptions / CSVWriteOptions in Options.swift, CSV/CSVReader.swift, CSV/CSVWriter.swift.

The reader memory-maps the file, scans UTF-8 bytes and writes cells straight into the column store. Date inference only recognises ISO forms (2024-03-01, 2024-03-01 12:30:00, 12:30:00); a field such as 3/1/2024 stays text.

In the application: import uses the defaults (type inference on, formulas not parsed). Export writes the active sheet with values as displayed (number formats applied), CRLF line endings and UTF-8 without BOM.

Lost on export (reported by preflight): other sheets, formulas (exported as their current values), formatting, merged cells, sizes, tables, validation rules, comments, hyperlinks, objects, defined names, and content preserved from an Excel file.

CSV export writes text values unchanged: a cell whose text starts with =, +, - or @ is not escaped or prefixed. A program that re-imports the file and interprets such fields as formulas does so on its own; ORIX Sheet's reader does not (see ../security.md).

Datasets larger than the grid are the data engine's job (../architecture/data-engine.md), which reads CSV through DuckDB without materialising cells.

# JSON and JSON Lines

  • Shapes: an array of objects (keys become a header row in order of first appearance) or an array of arrays, in both directions; a top-level object is one record. JSON Lines: one value per line.
  • Nested values: arrays and objects inside a record are flattened to compact JSON text in their cell, with a warning giving the count.
  • Write options: shape (objects default, or arrays), sheetIndex, prettyPrinted, datesAsISO8601 (default on: a cell with a date/time number format is written as an ISO 8601 string instead of a serial number). Duplicate headers are disambiguated.
  • Read options: headerRow (default on), maxRows.
  • Errors: nesting deeper than 64 levels; more than 1,048,576 records.
  • Lost on export: the same list as CSV.

Source: JSONReadOptions / JSONWriteOptions in Options.swift, JSON/JSONIO.swift, JSON/JSONValue.swift.

# PDF

Written by PrintRenderer.pdfData in SheetUI, not by SheetIO: the pages are the ones the print dialog shows, drawn by the same painter as the screen, so text stays text and embedded charts stay vector. Page size, orientation, margins, scaling, print area, repeated titles, manual breaks, gridlines, headings and header/footer come from the sheet's page setup. "All Sheets" skips hidden sheets. There is no PDF import. See ../architecture/grid-engine.md.

# Formats that are not supported

.xls, .xlsb, .fods, .numbers and password-protected Office files are recognised and refused with a message that says what to do instead. No partial reader is shipped for any of them. There is no HTML, Markdown, Parquet or SQLite import in SheetIO; Parquet and other tabular sources are handled by the data engine as datasets, not as worksheets (../architecture/data-engine.md).

# Measured timings

One sheet of N rows × 10 columns: 6 numeric columns, 1 unique text, 1 repeated text, 1 date, 1 formula with cached value.

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

Source: Packages/SheetIO/COMPATIBILITY.md, section "Measured performance" — Apple M5 Max (48 GB), macOS 27, Swift 6.4, release build, 2026-11-21. Reproduce with ORIX_BENCH=1 swift test -c release -Xswiftc -enable-testing --filter PerformanceTests in Packages/SheetIO. These figures were copied from the package document, not re-measured for this page.

Notes from the same source:

  • the native read includes parsing 100,000 (respectively 500,000) formula texts and is compared cell by cell with 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 both the source workbook and the loaded copy (the cell store 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);
  • a debug build is 2–4 times slower;
  • these are library timings. In the application, opening a foreign file additionally recalculates every formula, and open/save run on the main thread.

# Regenerating the interoperability fixtures

Packages/SheetIO/Tests/SheetIOTests/Fixtures/ holds seven files produced by other programs (features_openpyxl.xlsx, charts_openpyxl.xlsx, dates1904_openpyxl.xlsx, features_lo.xlsx, features_lo.ods, macro.xlsm, raw_features.xlsx); the scripts that produce them are in Tests/SheetIOTests/Generators/ (gen_openpyxl_corpus.py, gen_libreoffice_corpus.sh, gen_raw_xlsx.py, dump_xlsx.py). See ../development/testing.md and ../mathematics/reference-tests.md.

Files main
Menu

Theme