excelerate 0.16.0

Read, write and recalculate spreadsheets: xlsx, xls, ods, csv, html, and a 518-function formula engine
Documentation
# File formats

Every format is somebody's compromise. Here is what each one carries, what it
drops on the floor, and where the sharp edges are.

## Detection

`reader::read` and `read_bytes` look at the bytes first:

| Signature | Format |
|---|---|
| `PK...` | xlsx, xlsb or ods - the parts inside decide |
| `D0 CF 11 E0` | xls (compound file) |
| `1f 8b` | Gnumeric (gzip) |
| `ID;P` | SYLK |
| `<` | SpreadsheetML 2003 or HTML |

xlsx and xlsb are the same zip with the same part names, so neither gives
itself away in its first bytes; the archive's listing does - `xl/workbook.bin`
means xlsb.

Only when the bytes stay quiet does the extension get a say, and text with an
unknown extension is read as CSV. An extension that lies loses to the
signature - which is what you want when a browser saved `report.xls` that is
really HTML.

```rust
use excelerate::reader::{Format, format_of};

assert_eq!(format_of("tests/fixtures/sample.xlsx")?, Format::Xlsx);
# Ok::<(), excelerate::Error>(())
```

## Encrypted workbooks

A workbook saved with a password to open is not a zip but a compound file
holding the encrypted package. `read_bytes` and `read` recognise it and
decrypt the agile encryption of Excel 2010 and later, for xlsx, xlsm and xlsb
alike. The password goes in `Options`; without one, the reader tries the
password Excel uses for a workbook encrypted only to be opened read-only.

```rust
use excelerate::Error;
use excelerate::progress::Options;
use excelerate::reader::detect::{read_bytes, read_bytes_limited_with};
use excelerate::reader::xlsx::MAX_UNCOMPRESSED_SIZE;

let bytes = std::fs::read("tests/fixtures/encrypted.xlsx")?;
assert_eq!(read_bytes(&bytes, None).unwrap_err(), Error::WrongPassword);

let options = Options::new().password("пароль");
let book = read_bytes_limited_with(&bytes, None, MAX_UNCOMPRESSED_SIZE, &options)?;
assert_eq!(book.sheets().len(), 2);
# Ok::<(), Box<dyn std::error::Error>>(())
```

The standard encryption of Excel 2007 and the RC4 of xls are refused with
`Error::Encrypted`. Writing never encrypts.

## xlsx - the full-fat one

Read *and* written: values, types, formulas (shared ones are expanded), shared
strings, merges, styles, row/column sizing, sheet views and freeze panes, data
validation, the active tab, print setup, headers and footers, page breaks,
hyperlinks, defined names, conditional formatting with `dxf`, the theme, sheet
and workbook protection, autofilters, and the cached values of linked
workbooks.

Charts are modelled and written back: an untouched chart as its original bytes,
a changed or new one from the model (see [model.md](model.md#charts)). The
2016 chart types are written the same way, a change spliced into their part.
Pivot tables and their caches are written by comparison as well (see
[model.md](model.md#pivot-tables)).

Pictures and shapes are modelled the same way: an untouched one goes back as
written, an edit is spliced into its drawing (see [model.md](model.md#pictures)
and [model.md](model.md#shapes)). A comment's box lives in a VML part that is
carried as bytes and brought in line with the comments on write: a new comment
gets a box, a removed one loses it.

Not modelled, but carried through byte for byte with their relationships:
connectors and other drawing objects, document properties, links to other
workbooks.

Two deliberate calls:

- `calcChain.xml` is dropped. It states the order formulas were evaluated in;
  after a rewrite that would be fiction.
- `<fileVersion>` is not written. It names the application that last saved the
  file, and that is not us.

## xls - BIFF8

Reads sheets, every value type, the shared string table (including
continuation records), the whole cell format - number format, font, fill,
borders, alignment and protection - merges, column widths, row heights, row
and column outline (levels, collapsed groups, whether summaries sit below and
to the right), the sheet view (frozen or split panes, the selection of each
pane, scroll position, the gridline, heading, zero and right-to-left
switches) and the workbook epoch. Writes the same back.

Style 0 is the workbook's Normal style, taken from the default cell format
(XF 15). It matters beyond looks: column widths are counted in digits of its
font, so a book in Arial 8 read as Calibri 11 would open with every column
wider.

Colours in BIFF8 are indexes into a 56-entry palette. Reading resolves them to
RGB through the file's own palette, so a colour means the same thing once it
leaves the file. Writing goes the other way: a colour the default palette has
keeps its entry, one it lacks takes over an entry nothing else uses (and the
file gets a `PALETTE` record), and beyond 56 distinct colours the rest are
drawn with the nearest entry. Theme colours are resolved through the
workbook's theme, tint included.

Formulas come back as text beside their cached result. BIFF8 stores a formula
as tokens, and the reader turns them back into `SUM(A1:A3)`: references
relative and absolute, 3D references across sheets, other workbooks and
add-in functions, array constants, shared formulas (expanded onto every cell,
as xlsx reading does) and defined names. An array formula lives on its first
cell; the rest of its range keeps the values. A formula the reader cannot turn
back - a data table, a token it does not know - keeps only its cached value
rather than a text that says something else.

The writer compiles formulas back into tokens and writes them with their
result, and defined names as `NAME` records. Parentheses Excel needs are put
back from operator precedence, so `(A1+B1)*2` survives, but redundant ones
(`(A1&B1)="x"`) do not: the parsed formula no longer holds them. Functions
newer than the format go out by name with the `_xlfn.` prefix, the way Excel
stores them. What BIFF8 cannot hold is written as its value instead: a
structured reference, a reference past row 65536 or column IV, a string
literal over 255 characters, a function called with more arguments than the
format allows.

BIFF5 - the xls Excel 5 and 95 wrote - is read too, and by the same code: the
records are the same ones in the same order, packed more narrowly. One byte per
character in the workbook's code page rather than UTF-16, one byte per column,
the relative flags of a reference on its row rather than its column, and an
`XF` record of sixteen bytes rather than twenty. A 3D reference names its sheet
inside the token, where BIFF8 points at an `EXTERNSHEET` entry. Its formatted
text lives on the cell (`RSTRING`) rather than in the string table, and is
read into the same `RichText`.

Its text is bytes rather than UTF-16, so it needs a code page. The workbook
names one in a `CODEPAGE` record, and `shared::codepage` holds the pages such
files carry - Windows 1252 and 1251, Mac Roman, DOS 866 - reading anything
else as 1252. Three of the four BIFF5 workbooks tested here say Mac Roman, so
assuming 1252 would have been wrong. Some say nothing at all, and some lie;
for those, `read_xls_in(path, page)` and `read_xls_from_in(bytes, page)` take
the page from you and outrank the record:

```rust,no_run
use excelerate::reader::xls::read_xls_in;
use excelerate::shared::codepage::WINDOWS_1251;

let book = read_xls_in("export.xls", WINDOWS_1251)?;
# Ok::<(), excelerate::Error>(())
```

Writing is BIFF8 only. BIFF4 and older, and encrypted workbooks, are rejected
rather than read halfway.

Document properties sit in two streams beside the workbook,
`\x05SummaryInformation` and `\x05DocumentSummaryInformation`, in the
property set format (MS-OLEPS). Reading honours each set's code page: 1C
exports write 1251, WPS and 1C also write 1200, where text is UTF-16. Writing
uses 1200 for every string, so no character is lost to an ANSI page. A whole
number past 32 bits is written as a double, since the version 0 sets Excel
writes have no 64-bit integer.

## ods - OpenDocument

Both directions: sheets, value types (float, percentage, currency, boolean,
date, time, string), repeated rows and columns, merges, formulas, hyperlinks,
cell styles, named expressions.

Three things behave differently by nature of the format:

- A row's tail is one cell repeated to the end of the sheet, so an empty cell
  that only carries a style is recoverable only if a non-empty cell follows it
  in the same row.
- ODS has no per-cell number format string. Date and time formats are inferred
  from the value on read and re-encoded on write; an arbitrary custom format
  does not survive the loop.
- Theme and indexed palette colours have no equivalent and are lost.
- Document properties live in `meta.xml` under ODF's names (the author is
  `meta:initial-creator`, the last editor `dc:creator`). Category, status,
  identifier, version, company and manager have no ODF element and are not
  written; keywords come back as one string joined with `, `.

Formula syntax is translated both ways (`A1` <-> `[.A1]`, argument separators,
`COM.MICROSOFT.` prefixes), so a formula written here still parses there.

## CSV

Reading guesses the encoding from the BOM (UTF-8/16, else CP1252), honours a
`sep=` line, and infers the delimiter by scoring candidates across rows. RFC
4180 quoting, with a doubled quote inside a quoted field.

```rust,no_run
use excelerate::reader::{CsvOptions, read_csv_with};

let opts = CsvOptions {
    delimiter: Some(';'),
    contiguous: true,             // skip rows that produced no cell
    preserve_empty_fields: true,  // "" becomes an empty string, not a hole
    ..CsvOptions::default()
};
let book = read_csv_with("export.csv", &opts)?;
# Ok::<(), excelerate::Error>(())
```

Writing takes one sheet, resolves formulas (cache first, otherwise it
evaluates), quotes only where needed, and prints 15 significant digits - the
same precision Excel shows.

## HTML

Writing gives you one table per sheet, workbook styles as `td.styleN` classes,
values rendered through the number-format engine, `colspan`/`rowspan`, widths,
heights, hyperlinks and rich text, one `<span style>` per run. Reading a page
turns `<b>`, `<i>`, `<u>`, `<s>`, `<sup>`, `<sub>`, `<font>` and
`<span style>` inside a cell into runs:

```rust
use excelerate::writer::{HtmlOptions, write_html_to};
# use excelerate::reader;

# let book = reader::read("tests/fixtures/sample.xlsx")?;
let mut out = Vec::new();
write_html_to(&book, &mut out, &HtmlOptions {
    sheet: Some(0),      // None writes every sheet
    fragment: true,      // no <html>/<head>, for embedding
})?;
# Ok::<(), excelerate::Error>(())
```

Reading uses a hand-rolled tag scanner rather than a DOM: attributes, entities,
implied end tags (`<tr>` closes `<td>`). A page becomes one sheet - tables give
the grid, `colspan`/`rowspan` become merges, text outside a table (`<p>`,
`<h1>`) gets a cell per block. Inline `style` and the old `bgcolor`/`align`/
`width`/`height` attributes are honoured, and so are the rules of a `<style>`
block that name a class of cells (`.x`, `td.x`, `th.x`), a column width
(`col.x`) or a row height (`tr.x`) - the inline style
wins over them. There is no cascade beyond that: rules for one class apply in
page order.

Our own writer marks each cell so a page reads back as the book it came from:
`data-type` and `data-value` hold the value itself (the text shows `1 234,50`,
the attribute says `1234.5`), `data-formula` the formula, `data-format` the
number format. Text that would read back as text anyway goes without them, so
a sheet of plain labels costs nothing extra.

The catch nobody escapes: **a page has no cell addresses.** The grid always
starts at `A1`, so a sheet whose used range began at row 5 comes back shifted
up. Images, comments and document properties are not carried; relative CSS
units (`em`, `%`) and `direction` are ignored.

## xlsb - BIFF12

Read-only. The xlsx package with its XML parts replaced by record streams: a
record is a number, a length and that many bytes, both numbers written seven
bits at a time.

What comes across: values of every type, shared strings, formulas (decompiled
from their tokens, shared and array formulas expanded onto every cell that
uses them), defined names, the full cell style - number format, font, fill,
borders, alignment, protection - row heights and hidden rows, column widths,
merges, the saved view (frozen panes, scroll position, selection), the auto
filter, and sheet visibility. What does not: hyperlinks and data validation
(no workbook to check them against yet), and everything built on parts this
reader does not open - tables, pivot tables, drawings, conditional
formatting.

Four sharp edges:

- **Functions numbered past the BIFF8 table.** xlsb numbers the analysis
  add-in functions and the ones Excel 2007 added (`IFERROR`, `COUNTIFS`,
  `AVERAGEIF`). Those numbers are known; a number outside both tables leaves
  the formula unread and its cached value standing, which is true as far as
  it goes.
- **Structured references** (`Sales[Amount]`) are a token kind of their own.
  One whose table was deleted reads as `#REF!`, the way Excel writes it into
  xlsx; a live one needs the table's name and columns, which means reading the
  table parts, and that is not done yet - such a formula keeps its cached
  value rather than a guess.
- A function Excel itself does not know is written `_xludf.MAXIFS` in xlsx and
  bare in xlsb. Bare is what you get here - this crate's own engine knows
  `MAXIFS` under that name.
- The binary always says what the XML leaves to the default: every row carries
  a height, every fill an automatic foreground, every pane its selection. Those
  are read as the default, so the same workbook in either format gives the
  same model rather than one full of noise.

## SYLK, Gnumeric, SpreadsheetML 2003

Read-only, all three.

- **SYLK** - `C`/`F`/`P` records, R1C1 formulas converted to A1, shared
  formulas, number formats, fonts, borders, column widths.
- **Gnumeric** - gzipped XML: sheets, value types, shared formulas by
  `ExprID`, per-cell formats, merges, sizing.
- **SpreadsheetML 2003** - named styles, `ss:Index` instead of addresses,
  R1C1 formulas, `MergeAcross`/`MergeDown`, widths and heights.

## Round-trip guarantee

`read -> write -> read` is covered by tests for xlsx and the other writable
formats. What that means in practice: if a feature is listed as modelled above,
it comes back identical; if it is listed as carried, the bytes come back
identical; anything else is a bug worth reporting.