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 cell | Written 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 formula | the 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.
- Sheet
Log - 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
sheetskeeps from one workbook, 256 MiB (268 MB): its shared strings, 4 bytes more each, and the sheets’ text, counted before it is kept; the boundarchive::MAX_UNPACKED_BYTESputs on the parts it is written from.
Functions§
- read
- Each worksheet of an
.xlsxworkbook, read as a log on its own byimport::read, in the workbook’s order of sheets. - sheets
- Each worksheet of an
.xlsxworkbook as text;sheets_withinwithMAX_TEXT_BYTES. - sheets_
within - Each worksheet of an
.xlsxworkbook as text, in the workbook’s order of sheets, keeping at mostmax_text_bytesfrom the whole workbook: its shared strings, 4 bytes more each, and the sheets’ text.