Skip to main content

Module workbook

Module workbook 

Source
Expand description

Excel workbooks, .xlsx: a flight’s log as a flyer saved it from a spreadsheet, one sheet or several, such as a Blue Raven’s high-rate and low-rate files side by side.

A workbook isn’t a layout of its own. sheets writes each worksheet’s cells out as comma-separated text, one line a row, and read gives each sheet’s text to import::read, which recognizes it as it would the file the sheet came from: a Blue Raven’s CSV, an Eggtimer’s, a Featherweight tracker’s, or a table read by the units its header states (generic). One workbook can give several logs.

An .xlsx file is a zip archive of XML parts (ECMA-376, Office Open XML File Formats, 5th edition, Part 1, §18 SpreadsheetML), taken out in memory as archive::unpack takes any archive’s files, within its bound. The package’s relationships (Part 2, §9) lead to the workbook part, and the workbook’s to its worksheets, shared strings and styles.

What a cell becomes. A cell is written as the workbook stores it, never as a spreadsheet would display it, so no digit is lost or invented:

The cellWritten as
a number (§18.3.1.4, no t or t="n")its stored digits, such as 0.10000000000000001
a shared string (t="s", §18.4.8)the string’s text, every run’s, a phonetic guide’s left out
an inline string (t="inlineStr") or a formula’s text (t="str")its text
a formulathe value saved with it; none saved, a gap
an error (t="e"), such as #DIV/0!a gap
a truth value (t="b")TRUE or FALSE
an ISO 8601 date (t="d")its text
a number under a date or time format (§18.8.30, §18.8.31)the date and time it stands for, below

A cell whose text holds a comma or a double quote is quoted, its quotes doubled; a line break inside a cell, as in a header wrapped to two lines, becomes a space.

Dates and times. A spreadsheet stores a date as a count of days, a serial number, and shows it as a date only through the cell’s number format. A number under a format that shows a date is written 2023-03-15, under one that shows a time of day 18:00:00, under one that shows both 2023-03-15 18:00:00, and under an elapsed-time format such as [h]:mm:ss, which counts hours past 24, 36:00:00. The seconds are rounded to the millisecond and their fraction written only when it isn’t zero, as 09.375; a serial’s double holds a time of day in 2023 to about a microsecond. Days count from 1899-12-30, so serial 61 is 1900-03-01, as in Excel from there on (Excel counts a 1900-02-29 that never was), or from 1904-01-01 in a workbook that says so (date1904, §18.2.28). A date before 1900-03-01, where the two calendars disagree, or past 9999-12-31 once rounded, is kept as the number stored, with a note. Under a format that shows only a time, a number under 1 is its time of day; one that also holds a day is written as a date and a time, or, before 1900-03-01, kept as the number stored. A format shows a date when its first section, outside quotes, brackets and escapes, holds a y, a d or an m for the month, and a time when it holds an h, an s or an m for the minute: an m right after an h or right before an s is the minute, and one in brackets, as [mm], elapsed minutes (§18.8.31). Every other number is kept as stored, so a time a logger wrote as seconds stays a number.

Rows and columns. Cells are placed by their references (B12). Every row is written as wide as the sheet’s last column that holds a value, so a short row gets empty cells and a cell formatted but empty widens nothing; a row with no value is left out. A sheet with no value, such as one that only holds a chart, gives no text, and read says it has no data. Chart sheets hold no cells and aren’t listed.

Bounds. A workbook comes from someone else, so its text is bounded: at most MAX_TEXT_BYTES from one workbook, its shared strings (each counted 4 bytes more, for where it ends) and the text written, each byte counted before it is kept, and at most MAX_SMALL_PART_BYTES in each part read whole (relationships, the workbook’s list of sheets, the styles). The sheets and shared strings, which grow with the data, are read as a stream (the largest sample sheet is 60 MB of XML). A document type declaration is refused, so no entity can expand, and every reference but XML’s own five and characters’ is refused. Cells and rows must come in order, as §18.3.1.4 and §18.3.1.73 have them, two sheets may not share a part, and a reference past XFD1048576, Excel’s last cell, is refused.

use std::io::{Cursor, Write};

use hpr_flightdata::{log::LogFormat, workbook};

// An invented workbook of one sheet: a header naming its units, then two rows.
let parts = [
    ("_rels/.rels", r#"<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="r1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/></Relationships>"#),
    ("xl/workbook.xml", r#"<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"><sheets><sheet name="Flight" sheetId="1" r:id="s1"/></sheets></workbook>"#),
    ("xl/_rels/workbook.xml.rels", r#"<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="s1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/></Relationships>"#),
    ("xl/worksheets/sheet1.xml", r#"<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><sheetData>
        <row r="1"><c r="A1" t="inlineStr"><is><t>Time (s)</t></is></c><c r="B1" t="inlineStr"><is><t>Altitude (m)</t></is></c></row>
        <row r="2"><c r="A2"><v>0</v></c><c r="B2"><v>0</v></c></row>
        <row r="3"><c r="A3"><v>0.05</v></c><c r="B3"><v>1.5</v></c></row>
    </sheetData></worksheet>"#),
];
let mut zip = zip::ZipWriter::new(Cursor::new(Vec::new()));
for (name, xml) in parts {
    zip.start_file(name, zip::write::SimpleFileOptions::default())?;
    zip.write_all(xml.as_bytes())?;
}
let bytes = zip.finish()?.into_inner();

let sheets = workbook::sheets(&bytes)?;
assert_eq!(sheets[0].text, "Time (s),Altitude (m)\n0,0\n0.05,1.5\n");
let logs = workbook::read(&bytes)?;
assert_eq!(logs[0].log.as_ref().map(|log| log.format), Ok(LogFormat::GenericTable));

Structs§

Sheet
One worksheet of a workbook, its cells written out as text.
SheetLog
One worksheet of a workbook, read.

Constants§

MAX_SMALL_PART_BYTES
The most bytes in a part read whole as a tree, 16 MiB (17 MB): the relationships, the workbook’s list of sheets and the styles, the largest of them in the project’s sample workbooks well under a megabyte.
MAX_TEXT_BYTES
The most text sheets keeps from one workbook, 256 MiB (268 MB): its shared strings, 4 bytes more each, and the sheets’ text, counted before it is kept; the bound archive::MAX_UNPACKED_BYTES puts on the parts it is written from.

Functions§

read
Each worksheet of an .xlsx workbook, read as a log on its own by import::read, in the workbook’s order of sheets.
sheets
Each worksheet of an .xlsx workbook as text; sheets_within with MAX_TEXT_BYTES.
sheets_within
Each worksheet of an .xlsx workbook as text, in the workbook’s order of sheets, keeping at most max_text_bytes from the whole workbook: its shared strings, 4 bytes more each, and the sheets’ text.