SPB Git

Repository Public

formats.md 17.1 KB 274 lines markdown

1# File formats23Every format ORIX Sheet detects, reads or writes; the limits and options of each; what is lost; and4the measured timings. The implementation is `Packages/SheetIO` (no UI, no network); the reference5document written with the code is `Packages/SheetIO/COMPATIBILITY.md`, and the native format is6specified in `Packages/SheetIO/NATIVE-FORMAT.md`.78Related: [excel.md](excel.md) (XLSX feature matrix and behaviour differences),9[../architecture/persistence.md](../architecture/persistence.md) (how the application opens, saves10and exports), [../security.md](../security.md) (untrusted files).1112**Verification scope.** XLSX output is checked with openpyxl 3.1.5 and headless LibreOffice 26.8.2;13CSV with pandas 3.0.2; ODS with LibreOffice. **Microsoft Excel and Apple Numbers were never14launched**: nothing below claims that Excel opens a file "without repair".1516## Summary1718| Format | Extensions recognised | Detect | Read | Write | In the app |19|---|---|---|---|---|---|20| ORIX Sheet document | `.orixsheet` (package directory) | content (`manifest.json` or `workbook.json` in a directory) | yes | yes | opened and saved in place, autosave |21| Excel workbook | `.xlsx`, `.xltx` | content (ZIP with `[Content_Types].xml` and `xl/…`) | yes | yes | imported as untitled; *Export To ▸ Excel Workbook* |22| Excel macro-enabled workbook | `.xlsm`, `.xltm` | content (a `vbaProject.bin` entry) | yes | yes | imported; macros kept, never run; *Export To ▸ Excel Macro-Enabled Workbook* |23| OpenDocument spreadsheet | `.ods`, `.ots` | content (`mimetype` entry) | yes | yes | imported; *Export To ▸ OpenDocument* |24| Comma-separated values | `.csv` | extension | yes | yes (one sheet) | imported; *Export To ▸ CSV — Active Sheet* |25| Tab-separated values | `.tsv`, `.tab` | extension | yes | yes (one sheet) | imported; *Export To ▸ TSV — Active Sheet* |26| JSON | `.json` | extension, or first significant byte `[` / `{` when the extension is unknown | yes | yes (one sheet) | imported; *Export To ▸ JSON — Active Sheet* |27| 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 |28| PDF | — | — | no | yes (print renderer, not `SheetIO`) | *Export To ▸ PDF — Active Sheet / All Sheets*, *Print…* |29| 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` |30| 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` |31| Flat OpenDocument | `.fods` | extension | **no** | no | error message |32| Apple Numbers | `.numbers` | content (package or ZIP with `Index/…iwa` / `Index.zip`) | **no** | no | error message explaining *File ▸ Export To ▸ Excel/CSV* in Numbers |33| Password-protected Office files | any | compound-file magic | **no** | no | error message (same container as `.xls`) |3435Source: `FileFormat` and `WorkbookReader.load` in `Packages/SheetIO/Sources/SheetIO/WorkbookIO.swift`;36`FileFormat.detect` for `.xlsb`; menu entries in `App/Sources/MainMenu.swift`; document types in37`App/Resources/Info.plist`.3839Detection (`FileFormat.detect`) looks at the content first and at the extension second, so an ODS40file renamed `.xlsx`, or an `.xlsm` renamed `.xlsx`, is recognised for what it is. While detecting,41the ZIP directory is only listed; nothing is decompressed except a `mimetype` entry smaller than 25642bytes.4344Every reader returns a `LoadResult` (workbook, list of `CompatibilityIssue`, preserved parts,45`hasMacros`, `hasExternalLinks`); every writer returns the list of issues, and46`WorkbookWriter.preflight` computes that list before writing. Issue severities are `loss`, `warning`47and `info`.4849## Limits common to all readers5051| Limit | Default | Where |52|---|---|---|53| Grid size | 1,048,576 rows × 16,384 columns | `Limits` (SheetCore) |54| Cells materialised from one file | 200,000,000 | `LoadOptions.maxCells` |55| ZIP entries | 100,000 | `ZipLimits.maxEntries` |56| ZIP entry size (uncompressed) | 1 GiB | `ZipLimits.maxEntrySize` |57| ZIP total size (uncompressed) | 4 GiB | `ZipLimits.maxTotalSize` |58| ZIP compression ratio | 500 : 1 (applied above 32 MiB, per the package document) | `ZipLimits.maxCompressionRatio` |59| JSON nesting depth | 64 | `JSONValue.maxDepth` |60| Files in an `.orixsheet` package | 100,000 | `NativeFormat.maxPartCount` |61| Raw size of one native cell chunk | 256 MiB | `NativeCells.maxChunkRawBytes` |6263Source: `Options.swift`, `Zip/ZipArchive.swift`, `JSON/JSONValue.swift`, `Native/NativeFormat.swift`,64`Native/NativeCells.swift`.6566The application uses the defaults; none of these limits is exposed in the user interface.6768## `.orixsheet` (native)6970Lossless for everything the model holds: values, formulas with cached results and spill ranges,71styles, layout, tables, conditional formats, validations, comments, hyperlinks, embedded objects72(charts, figures, pivots), print settings, defined names, attachments (scripts, queries,73workflows), and the raw parts preserved from an imported Excel file.7475- **Read**: verifies SHA-256 and size of every part against the manifest; recovers what it can from76  a damaged package and reports the rest (strict mode available in the API, not used by the app).77- **Write**: atomic (temporary sibling directory, then swap).78- **Options**: `NativeWriteOptions.appVersion`, `preview` (PNG thumbnail; not set by the app),79  `compressionLevel` (zlib 1–9, default 6).80- **Lost**: a LAMBDA value held by a cell is stored as `#CALC!` and recomputed on load.8182Details: [../architecture/persistence.md](../architecture/persistence.md).8384## XLSX and XLSM8586The reader is a streaming SAX reader over its own ZIP implementation (zlib); the worksheet part being87parsed is held decompressed in memory, with no DOM. Shared formulas are expanded; legacy88array formulas become dynamic arrays; theme and indexed colours are resolved to RGB; `_xlfn.`89prefixes are removed on read and added on write.9091**Read, modelled and written**: numbers, text, booleans, errors; formulas with cached values;92dynamic arrays; fonts, fills, borders, alignment, protection flags; number formats; column widths,93row heights, hidden rows/columns, outline levels; merged cells; frozen panes, zoom, gridlines,94headings; tab colour, hidden sheets, active sheet; hyperlinks; data validation; conditional95formatting (the rule kinds listed in [excel.md](excel.md)); autofilter range; tables; notes; page96setup, margins, header/footer, print area and titles, page breaks; defined names; core document97properties; the 1900 and 1904 date systems.9899**Preserved byte-for-byte without being interpreted** (kept in `PreservedParts`, written back to100XLSX/XLSM, stored inside `.orixsheet`): charts, images and shapes (DrawingML), pivot tables and101caches, the VBA project (XLSM only), external link definitions, custom XML, custom document102properties, the theme. They are not displayed, not editable, and not updated when cells change.103104**Dropped or approximated, always with an issue**: see the matrix in [excel.md](excel.md).105106Write-side limits (checked by `WorkbookWriter.preflight` and by the writer):107108| Limit | Value | Behaviour |109|---|---|---|110| Text per cell | 32,767 UTF-16 units | truncated, `loss` issue with the cell address |111| Formula length | 8,192 characters | `warning` |112| Hyperlinks per sheet | 65,530 | the rest dropped, `loss` |113| URL length | 2,079 characters | per the package document |114| Cell formats | about 65,490 | `warning` |115| Validation prompt/error titles and messages | 32 / 255 characters | truncated, reported |116| Inline validation list | 255 characters | dropped, reported |117| Sheet names | Excel's rules, unique case-insensitively | error before anything is written |118| Colour scales | 2 or 3 points | others dropped, `loss` |119120Source: `ExcelLimits` in `XLSX/WorksheetWriter.swift`, `Preflight.swift`, and121`Packages/SheetIO/COMPATIBILITY.md`.122123Units: OOXML column widths are in characters of the default font; ORIX Sheet stores points with124`points = width × 5.25` (Calibri 11, 7 px digit width). A file without explicit default sizes is125given Excel's defaults on import (48 pt columns, 15 pt rows); a workbook created in ORIX Sheet uses12680 pt and 20 pt.127128Built-in number-format ids 0–22 and 37–49 are mapped to their en-US codes through one table shared129with the format engine (`SheetFormat.BuiltinFormats`, exposed as `NumberFormats.builtin`); ids 23–36130(locale-reserved, CJK) and any unknown id without a custom definition fall back to `General`. Id 14131maps to `m/d/yyyy` and id 22 to `m/d/yyyy h:mm` (Excel's en-US short date, confirmed with132LibreOffice's rendering of a file that carries only the ids), not to the `mm-dd-yy` / `m/d/yy h:mm`133printed in ECMA-376 Part 1 §18.8.30; id 47 is `mm:ss.0` (see [excel.md](excel.md)).134135## ODS136137Values (float, percentage, currency, date, time, boolean, string, errors), formulas (OpenFormula ⇄138canonical syntax, including matrix formulas), fonts, colours, fills, borders, alignment, wrap,139rotation, protection, the common number formats (General, fixed, grouped, percent, scientific,140date/time, text), column widths, row heights, hidden rows/columns, merged cells, hidden and141protected sheets, notes, hyperlinks, workbook-scoped names, frozen panes, zoom, gridlines, active142sheet and document properties are read and written.143144Not converted in either direction (warning at load, `loss` at save): conditional formatting,145validation, filters and database ranges. Not written: tables (written as plain ranges), outline,146print settings, tab colour, sheet-scoped names, attachments, embedded objects, currency and other147custom number formats (written as General, with a `loss` issue naming the format). Not read: charts,148images, macros, embedded objects. Encrypted documents and flat `.fods` files are refused.149150Formulas that cannot be converted keep their value and are reported: union `~` and intersection `!`151operators, 3-D ranges and references to other documents when reading; structured references, `A1#`,152LAMBDA calls and sheet-scoped names when writing. `#SPILL!`, `#CALC!` and `#CIRC!` have no153OpenFormula spelling and are not written as formula errors.154155The 1904 date system is converted on export (dates keep their calendar value; `warning`).156157LibreOffice ignores ODS view settings in a headless conversion, so frozen panes, zoom and the active158sheet are verified by ORIX Sheet's own round trip only.159160## CSV and TSV161162| | Read | Write |163|---|---|---|164| Structure | RFC 4180 quoting, embedded quotes, delimiters and newlines, CR / LF / CRLF | one sheet (the active sheet in the app) |165| Delimiter | sniffed among `,` `;` tab `\|` (quote-aware) or forced; TSV defaults to tab | `,` (TSV: tab), configurable |166| 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 |167| 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 |168| Decimal comma | detected when the delimiter is not `,`, or forced | optional for raw values |169| Leading zeros | kept as text (`007`), as are digit strings that are not exact doubles | — |170| Fields starting with `=` | **text**, unless `parseFormulas` is set | — |171| Quoting / line ending | — | minimal (default), all, non-numeric; CRLF (default), LF, CR |172| Size | more than 1,048,576 rows or 16,384 columns raises `tooLarge` with a pointer to the data engine | — |173174Source: `CSVReadOptions` / `CSVWriteOptions` in `Options.swift`, `CSV/CSVReader.swift`,175`CSV/CSVWriter.swift`.176177The reader memory-maps the file, scans UTF-8 bytes and writes cells straight into the column store.178Date inference only recognises ISO forms (`2024-03-01`, `2024-03-01 12:30:00`, `12:30:00`); a field179such as `3/1/2024` stays text.180181In the application: import uses the defaults (type inference on, formulas **not** parsed). Export182writes the active sheet with values **as displayed** (number formats applied), CRLF line endings and183UTF-8 without BOM.184185Lost on export (reported by preflight): other sheets, formulas (exported as their current values),186formatting, merged cells, sizes, tables, validation rules, comments, hyperlinks, objects, defined187names, and content preserved from an Excel file.188189CSV export writes text values unchanged: a cell whose text starts with `=`, `+`, `-` or `@` is not190escaped or prefixed. A program that re-imports the file and interprets such fields as formulas does191so on its own; ORIX Sheet's reader does not (see [../security.md](../security.md)).192193Datasets larger than the grid are the data engine's job194([../architecture/data-engine.md](../architecture/data-engine.md)), which reads CSV through DuckDB195without materialising cells.196197## JSON and JSON Lines198199- **Shapes**: an array of objects (keys become a header row in order of first appearance) or an200  array of arrays, in both directions; a top-level object is one record. JSON Lines: one value per201  line.202- **Nested values**: arrays and objects inside a record are flattened to compact JSON text in their203  cell, with a `warning` giving the count.204- **Write options**: `shape` (`objects` default, or `arrays`), `sheetIndex`, `prettyPrinted`,205  `datesAsISO8601` (default on: a cell with a date/time number format is written as an ISO 8601206  string instead of a serial number). Duplicate headers are disambiguated.207- **Read options**: `headerRow` (default on), `maxRows`.208- **Errors**: nesting deeper than 64 levels; more than 1,048,576 records.209- **Lost on export**: the same list as CSV.210211Source: `JSONReadOptions` / `JSONWriteOptions` in `Options.swift`, `JSON/JSONIO.swift`,212`JSON/JSONValue.swift`.213214## PDF215216Written by `PrintRenderer.pdfData` in `SheetUI`, not by `SheetIO`: the pages are the ones the print217dialog shows, drawn by the same painter as the screen, so text stays text and embedded charts stay218vector. Page size, orientation, margins, scaling, print area, repeated titles, manual breaks,219gridlines, headings and header/footer come from the sheet's page setup. "All Sheets" skips hidden220sheets. There is no PDF import. See [../architecture/grid-engine.md](../architecture/grid-engine.md).221222## Formats that are not supported223224`.xls`, `.xlsb`, `.fods`, `.numbers` and password-protected Office files are recognised and refused225with a message that says what to do instead. No partial reader is shipped for any of them. There is226no HTML, Markdown, Parquet or SQLite import in `SheetIO`; Parquet and other tabular sources are227handled by the data engine as datasets, not as worksheets228([../architecture/data-engine.md](../architecture/data-engine.md)).229230## Measured timings231232One sheet of N rows × 10 columns: 6 numeric columns, 1 unique text, 1 repeated text, 1 date,2331 formula with cached value.234235| 1,000,000 cells | Write | Read | File size |236|---|---|---|---|237| `.orixsheet` | 0.58 s (1.7 M cells/s) | 0.10 s (10.4 M cells/s) | 3.5 MB |238| `.xlsx` | 1.19 s (0.84 M cells/s) | 1.90 s (0.53 M cells/s) | 8.5 MB |239| `.csv` | 0.46 s (2.2 M cells/s) | 0.19 s (5.4 M cells/s) | 10.2 MB |240| `.jsonl` | 0.46 s (2.2 M cells/s) | 0.18 s (5.7 M cells/s) | 10.8 MB |241| `.ods` | 1.45 s (0.69 M cells/s) | 4.58 s (0.22 M cells/s) | 8.9 MB |242243| 5,000,000 cells | Write | Read |244|---|---|---|245| `.orixsheet` | 3.1 s | 0.5 s |246| `.xlsx` | 6.5 s | 10.2 s |247248Source: `Packages/SheetIO/COMPATIBILITY.md`, section "Measured performance" — Apple M5 Max (48 GB),249macOS 27, Swift 6.4, release build, 2026-11-21. Reproduce with250`ORIX_BENCH=1 swift test -c release -Xswiftc -enable-testing --filter PerformanceTests` in251`Packages/SheetIO`. These figures were copied from the package document, not re-measured for this252page.253254Notes from the same source:255256- the native read includes parsing 100,000 (respectively 500,000) formula texts and is compared cell257  by cell with the source workbook; the XLSX and CSV reads are checked the same way;258- memory: the benchmark process peaks at 0.47 GB after the 1 M-cell native round trip and 0.79 GB259  after the XLSX one, while holding both the source workbook and the loaded copy (the cell store260  needs roughly 200 MB per million cells); 3.7 GB at the end of the 5 M-cell run. The XLSX reader261  keeps one decompressed worksheet part in memory (about 50 MB per million cells);262- a debug build is 2–4 times slower;263- these are library timings. In the application, opening a foreign file additionally recalculates264  every formula, and open/save run on the main thread.265266## Regenerating the interoperability fixtures267268`Packages/SheetIO/Tests/SheetIOTests/Fixtures/` holds seven files produced by other programs269(`features_openpyxl.xlsx`, `charts_openpyxl.xlsx`, `dates1904_openpyxl.xlsx`, `features_lo.xlsx`,270`features_lo.ods`, `macro.xlsm`, `raw_features.xlsx`); the scripts that produce them are in271`Tests/SheetIOTests/Generators/` (`gen_openpyxl_corpus.py`, `gen_libreoffice_corpus.sh`,272`gen_raw_xlsx.py`, `dump_xlsx.py`). See [../development/testing.md](../development/testing.md) and273[../mathematics/reference-tests.md](../mathematics/reference-tests.md).274
Files main
Menu

Theme