Skip to main content

hpr_flightdata/
workbook.rs

1//! Excel workbooks, `.xlsx`: a flight's log as a flyer saved it from a spreadsheet, one sheet or
2//! several, such as a Blue Raven's high-rate and low-rate files side by side.
3//!
4//! A workbook isn't a layout of its own. [`sheets`] writes each worksheet's cells out as
5//! comma-separated text, one line a row, and [`read`] gives each sheet's text to
6//! [`import::read`], which recognizes it as it would the file the sheet
7//! came from: a Blue Raven's CSV, an Eggtimer's, a Featherweight tracker's, or a table read by
8//! the units its header states ([`generic`](crate::generic)). One workbook can give several logs.
9//!
10//! An `.xlsx` file is a zip archive of XML parts (ECMA-376, *Office Open XML File Formats*, 5th
11//! edition, Part 1, §18 *SpreadsheetML*), taken out in memory as [`archive::unpack`] takes any
12//! archive's files, within its bound. The package's relationships (Part 2, §9) lead to the
13//! workbook part, and the workbook's to its worksheets, shared strings and styles.
14//!
15//! **What a cell becomes.** A cell is written as the workbook stores it, never as a spreadsheet
16//! would display it, so no digit is lost or invented:
17//!
18//! | The cell | Written as |
19//! |---|---|
20//! | a number (§18.3.1.4, no `t` or `t="n"`) | its stored digits, such as `0.10000000000000001` |
21//! | a shared string (`t="s"`, §18.4.8) | the string's text, every run's, a phonetic guide's left out |
22//! | an inline string (`t="inlineStr"`) or a formula's text (`t="str"`) | its text |
23//! | a formula | the value saved with it; none saved, a gap |
24//! | an error (`t="e"`), such as `#DIV/0!` | a gap |
25//! | a truth value (`t="b"`) | `TRUE` or `FALSE` |
26//! | an ISO 8601 date (`t="d"`) | its text |
27//! | a number under a date or time format (§18.8.30, §18.8.31) | the date and time it stands for, below |
28//!
29//! A cell whose text holds a comma or a double quote is quoted, its quotes doubled; a line break
30//! inside a cell, as in a header wrapped to two lines, becomes a space.
31//!
32//! **Dates and times.** A spreadsheet stores a date as a count of days, a serial number, and
33//! shows it as a date only through the cell's number format. A number under a format that shows
34//! a date is written `2023-03-15`, under one that shows a time of day `18:00:00`, under one that
35//! shows both `2023-03-15 18:00:00`, and under an elapsed-time format such as `[h]:mm:ss`, which
36//! counts hours past 24, `36:00:00`. The seconds are rounded to the millisecond and their fraction
37//! written only when it isn't zero, as `09.375`; a serial's double holds a time of day in 2023 to
38//! about a microsecond. Days count from 1899-12-30, so serial 61 is 1900-03-01, as in Excel from
39//! there on (Excel counts a 1900-02-29 that never was), or from 1904-01-01 in a workbook that
40//! says so (`date1904`, §18.2.28). A date before 1900-03-01, where the two calendars disagree,
41//! or past 9999-12-31 once rounded, is kept as the number stored, with a note. Under a format
42//! that shows only a time, a number under 1 is its time of day; one that also holds a day is
43//! 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
44//! section, outside quotes, brackets and escapes, holds a `y`, a `d` or an `m` for the month, and
45//! a time when it holds an `h`, an `s` or an `m` for the minute: an `m` right after an `h` or
46//! 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
47//! a logger wrote as seconds stays a number.
48//!
49//! **Rows and columns.** Cells are placed by their references (`B12`). Every row is written as
50//! wide as the sheet's last column that holds a value, so a short row gets empty cells and a
51//! cell formatted but empty widens nothing; a row with no value is left out. A sheet with no
52//! value, such as one that only holds a chart, gives no text, and [`read`] says it has no data.
53//! Chart sheets hold no cells and aren't listed.
54//!
55//! **Bounds.** A workbook comes from someone else, so its text is bounded: at most
56//! [`MAX_TEXT_BYTES`] from one workbook, its shared strings (each counted 4 bytes more, for
57//! where it ends) and the text written, each byte counted before it is kept, and at
58//! most [`MAX_SMALL_PART_BYTES`] in each part read whole (relationships, the workbook's list of
59//! sheets, the styles). The sheets and shared strings, which grow with the data, are read as a
60//! stream (the largest sample sheet is 60 MB of XML). A document type declaration is refused, so
61//! no entity can expand, and every reference but XML's own five and characters' is refused.
62//! Cells and rows must come in order, as §18.3.1.4 and §18.3.1.73 have them, two sheets may not
63//! share a part, and a reference past `XFD1048576`, Excel's last cell, is refused.
64//!
65//! ```
66//! use std::io::{Cursor, Write};
67//!
68//! use hpr_flightdata::{log::LogFormat, workbook};
69//!
70//! // An invented workbook of one sheet: a header naming its units, then two rows.
71//! let parts = [
72//!     ("_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>"#),
73//!     ("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>"#),
74//!     ("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>"#),
75//!     ("xl/worksheets/sheet1.xml", r#"<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><sheetData>
76//!         <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>
77//!         <row r="2"><c r="A2"><v>0</v></c><c r="B2"><v>0</v></c></row>
78//!         <row r="3"><c r="A3"><v>0.05</v></c><c r="B3"><v>1.5</v></c></row>
79//!     </sheetData></worksheet>"#),
80//! ];
81//! let mut zip = zip::ZipWriter::new(Cursor::new(Vec::new()));
82//! for (name, xml) in parts {
83//!     zip.start_file(name, zip::write::SimpleFileOptions::default())?;
84//!     zip.write_all(xml.as_bytes())?;
85//! }
86//! let bytes = zip.finish()?.into_inner();
87//!
88//! let sheets = workbook::sheets(&bytes)?;
89//! assert_eq!(sheets[0].text, "Time (s),Altitude (m)\n0,0\n0.05,1.5\n");
90//! let logs = workbook::read(&bytes)?;
91//! assert_eq!(logs[0].log.as_ref().map(|log| log.format), Ok(LogFormat::GenericTable));
92//! # Ok::<(), Box<dyn std::error::Error>>(())
93//! ```
94
95use std::borrow::Cow;
96use std::collections::{HashMap, HashSet};
97
98use quick_xml::XmlVersion;
99use quick_xml::events::{BytesStart, Event};
100use serde::{Deserialize, Serialize};
101
102use crate::archive::{self, Member};
103use crate::error::LogError;
104use crate::import;
105use crate::log::FlightLog;
106
107/// The most text [`sheets`] keeps from one workbook, 256 MiB (268 MB): its shared strings, 4 bytes
108/// more each, and the sheets' text, counted before it is kept; the bound
109/// [`archive::MAX_UNPACKED_BYTES`] puts on the parts it is written from.
110pub const MAX_TEXT_BYTES: usize = 256 * 1024 * 1024;
111
112/// The most bytes in a part read whole as a tree, 16 MiB (17 MB): the relationships, the
113/// workbook's list of sheets and the styles, the largest of them in the project's sample
114/// workbooks well under a megabyte.
115pub const MAX_SMALL_PART_BYTES: usize = 16 * 1024 * 1024;
116
117/// Excel's last column, `XFD` (§18.3.1.4 leaves the limit to the application; Excel's is 16,384).
118const MAX_COLUMNS: u32 = 16_384;
119
120/// Excel's last row, 1,048,576.
121const MAX_ROWS: u32 = 1_048_576;
122
123/// The format's name in errors.
124const FORMAT: &str = "Excel workbook";
125
126/// SpreadsheetML's main namespace, Transitional and Strict (ECMA-376 Part 1, §18; Part 1, Annex
127/// A of the Strict schemas).
128const MAIN_NAMESPACES: [&str; 2] = [
129    "http://schemas.openxmlformats.org/spreadsheetml/2006/main",
130    "http://purl.oclc.org/ooxml/spreadsheetml/main",
131];
132
133/// One worksheet of a workbook, its cells written out as text.
134#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize)]
135#[non_exhaustive]
136pub struct Sheet {
137    /// The sheet's name, as its tab shows it.
138    pub name: String,
139    /// The sheet's cells, comma-separated, one line a row, each line ending in `\n`; empty for a
140    /// sheet with no value.
141    pub text: String,
142    /// What the text doesn't show as the workbook stores it, each a count: errors and formulas
143    /// with no saved value written as gaps, dates kept as numbers.
144    pub notes: Vec<String>,
145}
146
147/// One worksheet of a workbook, read.
148#[derive(Debug, Clone, PartialEq)]
149#[non_exhaustive]
150pub struct SheetLog {
151    /// The sheet's name, as its tab shows it.
152    pub name: String,
153    /// The log its text reads as, its notes opening with the sheet it was read from; or why it
154    /// doesn't read, [`LogError::NoData`] for a sheet with no value.
155    pub log: Result<FlightLog, LogError>,
156}
157
158/// Each worksheet of an `.xlsx` workbook, read as a log on its own by
159/// [`import::read`], in the workbook's order of sheets.
160///
161/// # Errors
162///
163/// As [`sheets`]; a sheet that doesn't read is not an error, but its [`SheetLog::log`].
164pub fn read(bytes: &[u8]) -> Result<Vec<SheetLog>, LogError> {
165    Ok(sheets(bytes)?
166        .into_iter()
167        .map(|sheet| {
168            let log = if sheet.text.is_empty() {
169                Err(LogError::NoData { format: FORMAT })
170            } else {
171                import::read(sheet.text.as_bytes()).map(|mut log| {
172                    let mut notes = vec![format!(
173                        "read from the sheet {:?} of an Excel workbook, its cells as stored",
174                        shortened(&sheet.name)
175                    )];
176                    notes.extend(sheet.notes);
177                    notes.append(&mut log.notes);
178                    log.notes = notes;
179                    log
180                })
181            };
182            SheetLog {
183                name: sheet.name,
184                log,
185            }
186        })
187        .collect())
188}
189
190/// Each worksheet of an `.xlsx` workbook as text; [`sheets_within`] with [`MAX_TEXT_BYTES`].
191///
192/// # Errors
193///
194/// As [`sheets_within`].
195pub fn sheets(bytes: &[u8]) -> Result<Vec<Sheet>, LogError> {
196    sheets_within(bytes, MAX_TEXT_BYTES)
197}
198
199/// Each worksheet of an `.xlsx` workbook as text, in the workbook's order of sheets, keeping at
200/// most `max_text_bytes` from the whole workbook: its shared strings, 4 bytes more each, and the
201/// sheets' text.
202///
203/// # Errors
204///
205/// [`LogError::NotThisFormat`] when the bytes aren't a zip archive, or the archive holds no
206/// SpreadsheetML workbook (a word processor's `.docx`, say); [`LogError::Archive`] as
207/// [`archive::unpack`]; [`LogError::Workbook`] when a part the workbook names is missing or isn't
208/// well-formed XML, holds a document type declaration or a reference to an entity, cells or rows
209/// come out of order or past `XFD1048576`, a cell's value isn't what its type says (a number that
210/// isn't one, a shared string that isn't there), two sheets share a part, a part read whole is
211/// larger than [`MAX_SMALL_PART_BYTES`], a cell holds two values, or the shared strings and text
212/// would pass `max_text_bytes`.
213pub fn sheets_within(bytes: &[u8], max_text_bytes: usize) -> Result<Vec<Sheet>, LogError> {
214    if !archive::is_archive(bytes) {
215        return Err(LogError::NotThisFormat {
216            format: FORMAT,
217            message: "its bytes don't open as a zip archive's, as an `.xlsx` file's do".to_owned(),
218        });
219    }
220    let package = Package::new(archive::unpack(bytes)?)?;
221    let main = package
222        .relationships("")?
223        .into_iter()
224        .find(|rel| rel.kind == "officeDocument" && !rel.external)
225        .ok_or_else(|| LogError::NotThisFormat {
226            format: FORMAT,
227            message: "the archive's relationships (`_rels/.rels`) name no office document"
228                .to_owned(),
229        })?;
230    let mut budget = Budget {
231        left: max_text_bytes,
232        most: max_text_bytes,
233    };
234    let book = Book::read(&package, &main.target, &mut budget)?;
235    let mut used_parts = HashSet::new();
236    let mut out = Vec::new();
237    for (name, target) in &book.sheets {
238        if !used_parts.insert(target.to_ascii_lowercase()) {
239            return Err(refused(format!(
240                "the sheets {:?} and another share the part {:?}",
241                shortened(name),
242                shortened(target)
243            )));
244        }
245        let xml = package.text(target)?;
246        let (text, notes) = read_sheet(&xml, target, &book, &mut budget)?;
247        out.push(Sheet {
248            name: name.clone(),
249            text,
250            notes,
251        });
252    }
253    Ok(out)
254}
255
256/// A refusal of the workbook's content.
257fn refused(message: String) -> LogError {
258    LogError::Workbook { message }
259}
260
261/// The text still allowed out of the workbook.
262struct Budget {
263    left: usize,
264    most: usize,
265}
266
267impl Budget {
268    /// Takes `bytes` from what is left, or refuses the workbook.
269    fn take(&mut self, bytes: usize) -> Result<(), LogError> {
270        self.left = self.left.checked_sub(bytes).ok_or_else(|| {
271            refused(format!(
272                "its sheets come to more than {} bytes of text, the most written from one workbook",
273                self.most
274            ))
275        })?;
276        Ok(())
277    }
278}
279
280/// The parts of the package, by name; a part's name compares without case (ECMA-376 Part 2,
281/// §6.2.2.3).
282struct Package {
283    members: Vec<Member>,
284    by_name: HashMap<String, usize>,
285}
286
287impl Package {
288    fn new(members: Vec<Member>) -> Result<Self, LogError> {
289        let mut by_name = HashMap::new();
290        for (index, member) in members.iter().enumerate() {
291            let name = member.name.trim_start_matches('/').to_ascii_lowercase();
292            if by_name.insert(name, index).is_some() {
293                return Err(refused(format!(
294                    "two parts are named {:?}, apart from case",
295                    shortened(&member.name)
296                )));
297            }
298        }
299        Ok(Self { members, by_name })
300    }
301
302    fn get(&self, name: &str) -> Option<&[u8]> {
303        self.by_name
304            .get(&name.to_ascii_lowercase())
305            .map(|&index| self.members[index].bytes.as_slice())
306    }
307
308    /// A part the workbook names, as text.
309    fn text(&self, name: &str) -> Result<Cow<'_, str>, LogError> {
310        self.get(name)
311            .map(import::decode)
312            .ok_or_else(|| refused(format!("the part {:?} is missing", shortened(name))))
313    }
314
315    /// A part read whole as a tree, within [`MAX_SMALL_PART_BYTES`]; `None` if there is none.
316    fn small(&self, name: &str) -> Result<Option<String>, LogError> {
317        let Some(bytes) = self.get(name) else {
318            return Ok(None);
319        };
320        if bytes.len() > MAX_SMALL_PART_BYTES {
321            return Err(refused(format!(
322                "the part {:?} is {} bytes, more than the {MAX_SMALL_PART_BYTES} read whole",
323                shortened(name),
324                bytes.len()
325            )));
326        }
327        Ok(Some(import::decode(bytes).into_owned()))
328    }
329
330    /// The relationships of the part `source` (the package's own for `""`), each target resolved
331    /// to a part's name (ECMA-376 Part 2, §9.3); none if it has no relationships part.
332    fn relationships(&self, source: &str) -> Result<Vec<Relationship>, LogError> {
333        let (folder, file) = match source.rsplit_once('/') {
334            Some((folder, file)) => (folder, file),
335            None => ("", source),
336        };
337        let rels = if folder.is_empty() {
338            format!("_rels/{file}.rels")
339        } else {
340            format!("{folder}/_rels/{file}.rels")
341        };
342        let Some(xml) = self.small(&rels)? else {
343            return Ok(Vec::new());
344        };
345        let doc = tree(&xml, &rels)?;
346        let mut out = Vec::new();
347        for node in doc
348            .root_element()
349            .children()
350            .filter(|node| node.tag_name().name() == "Relationship")
351        {
352            let (Some(id), Some(kind), Some(target)) = (
353                node.attribute("Id"),
354                node.attribute("Type"),
355                node.attribute("Target"),
356            ) else {
357                return Err(refused(format!(
358                    "a relationship in {rels:?} lacks its `Id`, `Type` or `Target`"
359                )));
360            };
361            let external = node.attribute("TargetMode") == Some("External");
362            out.push(Relationship {
363                id: id.to_owned(),
364                // The type's last segment: the Transitional and Strict URIs differ before it.
365                kind: kind.rsplit('/').next().unwrap_or(kind).to_owned(),
366                target: if external {
367                    target.to_owned()
368                } else {
369                    resolve(folder, target)
370                },
371                external,
372            });
373        }
374        Ok(out)
375    }
376}
377
378/// A relationship from one part to another.
379struct Relationship {
380    id: String,
381    kind: String,
382    target: String,
383    external: bool,
384}
385
386/// A relationship's target as a part's name: from the package's root if it starts with `/`,
387/// else from the source's folder, `.` and `..` resolved.
388fn resolve(folder: &str, target: &str) -> String {
389    let mut parts: Vec<&str> = if target.starts_with('/') {
390        Vec::new()
391    } else {
392        folder.split('/').filter(|part| !part.is_empty()).collect()
393    };
394    for part in target.split('/') {
395        match part {
396            "" | "." => {}
397            ".." => {
398                parts.pop();
399            }
400            part => parts.push(part),
401        }
402    }
403    parts.join("/")
404}
405
406/// A small part as a tree; a document type declaration is refused (roxmltree's default).
407fn tree<'a>(xml: &'a str, name: &str) -> Result<roxmltree::Document<'a>, LogError> {
408    roxmltree::Document::parse(xml).map_err(|error| {
409        refused(format!(
410            "the part {:?} isn't well-formed XML: {error}",
411            shortened(name)
412        ))
413    })
414}
415
416/// The workbook part's sheets, the shared strings and each style's date kind.
417struct Book {
418    /// Each worksheet's name and part, in the workbook's order.
419    sheets: Vec<(String, String)>,
420    strings: Strings,
421    /// Each cell format's date kind (`cellXfs`, §18.8.10), by the index a cell's `s` names.
422    styles: Vec<Option<DateKind>>,
423    date1904: bool,
424}
425
426impl Book {
427    fn read(package: &Package, part: &str, budget: &mut Budget) -> Result<Self, LogError> {
428        let xml = package.small(part)?.ok_or_else(|| {
429            refused(format!(
430                "the workbook part {:?} is missing",
431                shortened(part)
432            ))
433        })?;
434        let doc = tree(&xml, part)?;
435        let root = doc.root_element();
436        if root.tag_name().name() != "workbook"
437            || !root
438                .tag_name()
439                .namespace()
440                .is_some_and(|uri| MAIN_NAMESPACES.contains(&uri))
441        {
442            return Err(LogError::NotThisFormat {
443                format: FORMAT,
444                message: "the office document isn't a SpreadsheetML workbook".to_owned(),
445            });
446        }
447        let rels = package.relationships(part)?;
448        let date1904 = root
449            .children()
450            .find(|node| node.tag_name().name() == "workbookPr")
451            .and_then(|node| node.attribute("date1904"))
452            .is_some_and(|value| matches!(value, "1" | "true"));
453        let mut sheets = Vec::new();
454        for sheet in root
455            .children()
456            .filter(|node| node.tag_name().name() == "sheets")
457            .flat_map(|node| node.children())
458            .filter(|node| node.tag_name().name() == "sheet")
459        {
460            let name = sheet.attribute("name").unwrap_or_default();
461            // `r:id`, in the relationships namespace, Transitional or Strict.
462            let id = sheet
463                .attributes()
464                .find(|attribute| attribute.name() == "id" && attribute.namespace().is_some())
465                .map(|attribute| attribute.value())
466                .ok_or_else(|| {
467                    refused(format!(
468                        "the sheet {:?} names no relationship",
469                        shortened(name)
470                    ))
471                })?;
472            let rel = rels.iter().find(|rel| rel.id == id).ok_or_else(|| {
473                refused(format!(
474                    "the sheet {:?}'s relationship {:?} isn't in the workbook's",
475                    shortened(name),
476                    shortened(id)
477                ))
478            })?;
479            if rel.external {
480                return Err(refused(format!(
481                    "the sheet {:?} lies outside the workbook",
482                    shortened(name)
483                )));
484            }
485            // A chart sheet, dialog sheet or macro sheet holds no cells.
486            if rel.kind == "worksheet" {
487                sheets.push((name.to_owned(), rel.target.clone()));
488            }
489        }
490        let internal = |kind: &str| {
491            rels.iter()
492                .find(|rel| rel.kind == kind && !rel.external)
493                .map(|rel| rel.target.as_str())
494        };
495        let strings = match internal("sharedStrings") {
496            Some(target) => shared_strings(&package.text(target)?, target, budget)?,
497            None => Strings::default(),
498        };
499        let styles = match internal("styles") {
500            Some(target) => match package.small(target)? {
501                Some(xml) => date_styles(&tree(&xml, target)?),
502                None => Vec::new(),
503            },
504            None => Vec::new(),
505        };
506        Ok(Self {
507            sheets,
508            strings,
509            styles,
510            date1904,
511        })
512    }
513}
514
515/// Each cell format's date kind: its number format's (`numFmtId`), a custom code from
516/// `numFmts` (§18.8.31) or a built-in one (§18.8.30).
517fn date_styles(doc: &roxmltree::Document<'_>) -> Vec<Option<DateKind>> {
518    let root = doc.root_element();
519    let custom: HashMap<&str, &str> = root
520        .children()
521        .filter(|node| node.tag_name().name() == "numFmts")
522        .flat_map(|node| node.children())
523        .filter(|node| node.tag_name().name() == "numFmt")
524        .filter_map(|node| Some((node.attribute("numFmtId")?, node.attribute("formatCode")?)))
525        .collect();
526    // Each format's kind once, however many cell formats name it.
527    let mut kinds: HashMap<&str, Option<DateKind>> = HashMap::new();
528    root.children()
529        .filter(|node| node.tag_name().name() == "cellXfs")
530        .flat_map(|node| node.children())
531        .filter(|node| node.tag_name().name() == "xf")
532        .map(|xf| {
533            let id = xf.attribute("numFmtId").unwrap_or("0");
534            *kinds.entry(id).or_insert_with(|| match custom.get(id) {
535                Some(code) => format_kind(code),
536                None => id
537                    .parse::<u32>()
538                    .ok()
539                    .and_then(|id| BUILT_IN_DATES.iter().find(|(built_in, _)| *built_in == id))
540                    .and_then(|(_, code)| format_kind(code)),
541            })
542        })
543        .collect()
544}
545
546/// The built-in number formats that show a date or a time, by id, with their codes (§18.8.30).
547/// Ids 27 to 36 and 50 to 58 are East Asian dates whose codes the standard leaves to the locale;
548/// a number under one is kept as stored.
549const BUILT_IN_DATES: [(u32, &str); 12] = [
550    (14, "mm-dd-yy"),
551    (15, "d-mmm-yy"),
552    (16, "d-mmm"),
553    (17, "mmm-yy"),
554    (18, "h:mm AM/PM"),
555    (19, "h:mm:ss AM/PM"),
556    (20, "h:mm"),
557    (21, "h:mm:ss"),
558    (22, "m/d/yy h:mm"),
559    (45, "mm:ss"),
560    (46, "[h]:mm:ss"),
561    (47, "mmss.0"),
562];
563
564/// What a number format shows of a serial number.
565#[derive(Debug, Clone, Copy, PartialEq, Eq)]
566enum DateKind {
567    /// A date, `2023-03-15`.
568    Date,
569    /// A time of day, `18:00:00`.
570    Time,
571    /// Both, `2023-03-15 18:00:00`.
572    DateTime,
573    /// A duration counted in hours past 24, `36:00:00` (`[h]`, `[m]` or `[s]`).
574    Elapsed,
575}
576
577/// What a number format code shows, from its first section (§18.8.31): `None` for a number.
578fn format_kind(code: &str) -> Option<DateKind> {
579    let chars: Vec<char> = code.chars().collect();
580    // The date and time letters, lowercased, each run of one letter once.
581    let mut letters: Vec<char> = Vec::new();
582    let mut elapsed = false;
583    let push = |letter: char, letters: &mut Vec<char>| {
584        if letters.last() != Some(&letter) {
585            letters.push(letter);
586        }
587    };
588    let mut at = 0;
589    while let Some(&c) = chars.get(at) {
590        at += 1;
591        match c {
592            ';' => break,
593            '"' => {
594                while chars.get(at).is_some_and(|&c| c != '"') {
595                    at += 1;
596                }
597                at += 1;
598            }
599            // An escaped character, a space as wide as one, or a fill: the next is literal.
600            '\\' | '_' | '*' => at += 1,
601            '[' => {
602                let start = at;
603                while chars.get(at).is_some_and(|&c| c != ']') {
604                    at += 1;
605                }
606                // An unclosed bracket isn't a code a spreadsheet writes: a number, kept as stored.
607                if at >= chars.len() {
608                    return None;
609                }
610                let inner = &chars[start..at];
611                at += 1;
612                // `[h]`, `[mm]`, `[ss]`: an elapsed time, its letter a time's whatever it is (an
613                // `m` in brackets is minutes). Colors, conditions and locales aren't.
614                if let Some(first) = inner.first().map(char::to_ascii_lowercase)
615                    && matches!(first, 'h' | 'm' | 's')
616                    && inner.iter().all(|c| c.to_ascii_lowercase() == first)
617                {
618                    elapsed = true;
619                    push(if first == 'h' { 'h' } else { 's' }, &mut letters);
620                }
621            }
622            _ => {
623                let lower = c.to_ascii_lowercase();
624                let rest = &chars[at - 1..];
625                let opens = |word: &str| {
626                    word.chars().count() <= rest.len()
627                        && word
628                            .chars()
629                            .zip(rest)
630                            .all(|(w, c)| c.to_ascii_lowercase() == w)
631                };
632                if opens("am/pm") {
633                    at += 4;
634                } else if opens("a/p") {
635                    at += 2;
636                } else if matches!(lower, 'y' | 'd' | 'h' | 'm' | 's') {
637                    push(lower, &mut letters);
638                }
639            }
640        }
641    }
642    let mut date = false;
643    let mut time = false;
644    for (index, &letter) in letters.iter().enumerate() {
645        match letter {
646            'y' | 'd' => date = true,
647            'h' | 's' => time = true,
648            _ => {
649                let minute = (index > 0 && letters[index - 1] == 'h')
650                    || letters.get(index + 1) == Some(&'s');
651                if minute {
652                    time = true;
653                } else {
654                    date = true;
655                }
656            }
657        }
658    }
659    match (date, time) {
660        (true, true) => Some(DateKind::DateTime),
661        (true, false) => Some(DateKind::Date),
662        (false, true) if elapsed => Some(DateKind::Elapsed),
663        (false, true) => Some(DateKind::Time),
664        (false, false) => None,
665    }
666}
667
668/// Days from 1970-01-01 to 1899-12-30, the day before serial 1 in Excel's 1900 date system once
669/// past its 1900-02-29.
670const EPOCH_1900: i64 = -25_569;
671/// Days from 1970-01-01 to 1904-01-01, serial 0 in the 1904 date system.
672const EPOCH_1904: i64 = -24_107;
673/// Serial 61, 1900-03-01: below it the 1900 system's days are off by its 1900-02-29.
674const FIRST_SERIAL_1900: i64 = 61;
675/// Serial 2,958,465, 9999-12-31, the last day Excel shows.
676const LAST_SERIAL: i64 = 2_958_465;
677const MS_A_DAY: i64 = 86_400_000;
678
679/// A serial number written as its format shows it; `None` where the date would be before
680/// 1900-03-01 in the 1900 system, before the epoch, or past 9999-12-31.
681fn date_text(serial: f64, kind: DateKind, date1904: bool) -> Option<String> {
682    if !serial.is_finite() || serial < 0.0 || serial >= (LAST_SERIAL + 1) as f64 {
683        return None;
684    }
685    #[allow(
686        clippy::cast_possible_truncation,
687        reason = "in range: under 2.6e14 ms, exact in an f64 and an i64"
688    )]
689    let ms = (serial * MS_A_DAY as f64).round() as i64;
690    let days = ms.div_euclid(MS_A_DAY);
691    let of_day = ms.rem_euclid(MS_A_DAY);
692    let date = || {
693        if !date1904 && days < FIRST_SERIAL_1900 {
694            return None;
695        }
696        let epoch = if date1904 { EPOCH_1904 } else { EPOCH_1900 };
697        // Past 9999-12-31, in either system, once rounded: the last day Excel shows.
698        if epoch + days > EPOCH_1900 + LAST_SERIAL {
699            return None;
700        }
701        let (year, month, day) = civil_from_days(epoch + days);
702        Some(format!("{year:04}-{month:02}-{day:02}"))
703    };
704    match kind {
705        DateKind::Elapsed => Some(clock(ms)),
706        // A time of day under a day long, rounded up to midnight at its end as a clock shows it.
707        DateKind::Time if serial < 1.0 => Some(clock(of_day)),
708        DateKind::Date if of_day == 0 => date(),
709        // A time of day that also holds a date, or a date that holds a time, keeps both.
710        DateKind::Time | DateKind::Date | DateKind::DateTime => {
711            Some(format!("{} {}", date()?, clock(of_day)))
712        }
713    }
714}
715
716/// Milliseconds as `H:MM:SS`, hours past 24 counted on, the seconds' fraction written to the
717/// millisecond only when it isn't zero, trailing zeros dropped.
718fn clock(ms: i64) -> String {
719    let hours = ms / 3_600_000;
720    let minutes = ms / 60_000 % 60;
721    let seconds = ms / 1000 % 60;
722    let fraction = ms % 1000;
723    let mut text = format!("{hours:02}:{minutes:02}:{seconds:02}");
724    if fraction != 0 {
725        text.push_str(format!(".{fraction:03}").trim_end_matches('0'));
726    }
727    text
728}
729
730/// The proleptic Gregorian date `days` after 1970-01-01: Howard Hinnant's `civil_from_days`,
731/// from *chrono-Compatible Low-Level Date Algorithms* (public domain).
732fn civil_from_days(days: i64) -> (i64, u32, u32) {
733    let z = days + 719_468;
734    let era = z.div_euclid(146_097);
735    let doe = z.rem_euclid(146_097);
736    let yoe = (doe - doe / 1460 + doe / 36_524 - doe / 146_096) / 365;
737    let doy = doe - (365 * yoe + yoe / 4 - yoe / 100);
738    let mp = (5 * doy + 2) / 153;
739    let day = doy - (153 * mp + 2) / 5 + 1;
740    let month = if mp < 10 { mp + 3 } else { mp - 9 };
741    #[allow(
742        clippy::cast_possible_truncation,
743        clippy::cast_sign_loss,
744        reason = "a day of a month and a month of a year"
745    )]
746    (
747        yoe + era * 400 + i64::from(month <= 2),
748        month as u32,
749        day as u32,
750    )
751}
752
753/// A streaming reader's events, every refusal named for its part.
754struct Events<'a> {
755    reader: quick_xml::Reader<&'a [u8]>,
756    part: &'a str,
757}
758
759impl<'a> Events<'a> {
760    fn new(xml: &'a str, part: &'a str) -> Self {
761        Self {
762            reader: quick_xml::Reader::from_str(xml),
763            part,
764        }
765    }
766
767    fn next(&mut self) -> Result<Event<'a>, LogError> {
768        let event = self.reader.read_event().map_err(|error| {
769            refused(format!(
770                "the part {:?} isn't well-formed XML at byte {}: {error}",
771                shortened(self.part),
772                self.reader.error_position()
773            ))
774        })?;
775        if matches!(event, Event::DocType(_)) {
776            return Err(refused(format!(
777                "the part {:?} holds a document type declaration",
778                shortened(self.part)
779            )));
780        }
781        Ok(event)
782    }
783
784    /// An event's text, XML's own references resolved; `None` for an event that holds none.
785    fn text(&self, event: &Event<'_>) -> Result<Option<String>, LogError> {
786        Ok(match event {
787            Event::Text(text) => Some(text.xml10_content().into_owned()),
788            Event::CData(data) => Some(data.xml10_content().into_owned()),
789            Event::GeneralRef(reference) => {
790                let resolved = if reference.is_char_ref() {
791                    reference
792                        .resolve_char_ref()
793                        .ok()
794                        .flatten()
795                        .map(String::from)
796                } else {
797                    quick_xml::escape::resolve_predefined_entity(&reference.xml10_content())
798                        .map(str::to_owned)
799                };
800                Some(resolved.ok_or_else(|| {
801                    refused(format!(
802                        "the part {:?} refers to an entity that isn't XML's own",
803                        shortened(self.part)
804                    ))
805                })?)
806            }
807            _ => None,
808        })
809    }
810
811    /// An element's attribute by its local name, normalized.
812    fn attribute(&self, element: &BytesStart<'_>, name: &str) -> Result<Option<String>, LogError> {
813        for attribute in element.attributes() {
814            let attribute = attribute.map_err(|error| {
815                refused(format!(
816                    "an attribute in the part {:?} isn't well-formed: {error}",
817                    shortened(self.part)
818                ))
819            })?;
820            if attribute.key.local_name().as_ref() == name {
821                let value = attribute
822                    .normalized_value(XmlVersion::Implicit1_0)
823                    .map_err(|error| {
824                        refused(format!(
825                            "an attribute in the part {:?} isn't well-formed: {error}",
826                            shortened(self.part)
827                        ))
828                    })?;
829                return Ok(Some(value.into_owned()));
830            }
831        }
832        Ok(None)
833    }
834}
835
836/// The shared strings (`sst`, §18.4.9), each `si`'s text: its `t`, or every run's `t`, a
837/// phonetic guide's (`rPh`) left out.
838fn shared_strings(xml: &str, part: &str, budget: &mut Budget) -> Result<Strings, LogError> {
839    let mut events = Events::new(xml, part);
840    let mut strings = Strings::default();
841    let mut current: Option<String> = None;
842    let mut in_text = false;
843    let mut phonetic = 0usize;
844    loop {
845        let event = events.next()?;
846        match &event {
847            Event::Start(element) => match element.local_name().as_ref() {
848                "si" => current = Some(String::new()),
849                "rPh" => phonetic += 1,
850                "t" => in_text = phonetic == 0 && current.is_some(),
851                _ => {}
852            },
853            Event::Empty(element) if element.local_name().as_ref() == "si" => {
854                strings.push("", budget)?;
855            }
856            Event::End(element) => match element.local_name().as_ref() {
857                "si" => {
858                    if let Some(text) = current.take() {
859                        strings.push(&text, budget)?;
860                    }
861                }
862                "rPh" => phonetic = phonetic.saturating_sub(1),
863                "t" => in_text = false,
864                _ => {}
865            },
866            Event::Eof => break,
867            _ => {
868                if in_text
869                    && let Some(text) = events.text(&event)?
870                    && let Some(current) = current.as_mut()
871                {
872                    current.push_str(&text);
873                }
874            }
875        }
876    }
877    Ok(strings)
878}
879
880/// The shared strings, one after another in one buffer, each's end kept: 4 bytes a string, not
881/// a `String`'s 24, so a part of empty strings can't grow memory past the budget's bytes.
882#[derive(Default)]
883struct Strings {
884    text: String,
885    ends: Vec<u32>,
886}
887
888impl Strings {
889    /// Adds a string, charging its bytes and its end's 4 to the budget.
890    fn push(&mut self, text: &str, budget: &mut Budget) -> Result<(), LogError> {
891        budget.take(text.len() + 4)?;
892        self.text.push_str(text);
893        // Under the budget's 256 MiB, far below `u32::MAX`.
894        self.ends.push(
895            u32::try_from(self.text.len())
896                .map_err(|_| refused("its shared strings pass 4 GiB".to_owned()))?,
897        );
898        Ok(())
899    }
900
901    fn get(&self, index: usize) -> Option<&str> {
902        let end = *self.ends.get(index)? as usize;
903        let start = match index {
904            0 => 0,
905            _ => self.ends[index - 1] as usize,
906        };
907        self.text.get(start..end)
908    }
909}
910
911/// A cell being read: where it is, its type and style, and its value's text so far.
912struct Cell {
913    row: u32,
914    column: u32,
915    kind: Option<String>,
916    style: Option<usize>,
917    /// Whether it holds a formula (`f`), whose value is the one saved with it.
918    formula: bool,
919    value: Option<String>,
920    inline: Option<String>,
921}
922
923/// What a sheet's text leaves out, counted.
924#[derive(Default)]
925struct Counts {
926    errors: usize,
927    unsaved: usize,
928    dates_kept: usize,
929}
930
931impl Counts {
932    fn notes(&self) -> Vec<String> {
933        let mut notes = Vec::new();
934        if self.errors > 0 {
935            notes.push(format!(
936                "{} cells holding an error, such as `#DIV/0!`, written as gaps",
937                self.errors
938            ));
939        }
940        if self.unsaved > 0 {
941            notes.push(format!(
942                "{} formula cells with no value saved with them written as gaps",
943                self.unsaved
944            ));
945        }
946        if self.dates_kept > 0 {
947            notes.push(format!(
948                "{} numbers under a date format kept as stored: their dates would fall before \
949                 1900-03-01 or past 9999-12-31",
950                self.dates_kept
951            ));
952        }
953        notes
954    }
955}
956
957/// A worksheet's text as it is written: each row's cells, its separators and gaps, as they come;
958/// each row's trailing padding waits for the sheet's width, known only at its end.
959struct Lines<'b> {
960    body: String,
961    /// Each written row's end in `body` and its last column.
962    ends: Vec<(usize, u32)>,
963    /// The row being written: where it starts in `body`, and the column after its last cell.
964    start: usize,
965    next: u32,
966    budget: &'b mut Budget,
967}
968
969impl Lines<'_> {
970    /// Starts a row.
971    fn open(&mut self) {
972        self.start = self.body.len();
973        self.next = 1;
974    }
975
976    /// Writes a cell's text in its column, after the commas before it, gaps included.
977    fn cell(&mut self, column: u32, value: &str) -> Result<(), LogError> {
978        // Columns ascend, checked as each cell is placed.
979        let separators = (column - self.next) as usize + usize::from(self.next > 1);
980        let value = escaped(value);
981        // Charged before it is written, so nothing past the bound is ever held.
982        self.budget.take(separators + value.len())?;
983        for _ in 0..separators {
984            self.body.push(',');
985        }
986        self.body.push_str(&value);
987        self.next = column + 1;
988        Ok(())
989    }
990
991    /// Ends a row: kept if it holds a value, else taken back.
992    fn close(&mut self) {
993        if self.next > 1 {
994            self.ends.push((self.body.len(), self.next - 1));
995        } else {
996            self.body.truncate(self.start);
997        }
998        self.next = 1;
999    }
1000
1001    /// The sheet's text: every row as wide as the widest, each ending in `\n`.
1002    fn finish(self) -> Result<String, LogError> {
1003        let width = self.ends.iter().map(|(_, last)| *last).max().unwrap_or(0);
1004        let mut text = String::new();
1005        let mut from = 0;
1006        for (end, last) in &self.ends {
1007            let padding = (width - last) as usize;
1008            self.budget.take(padding + 1)?;
1009            text.push_str(&self.body[from..*end]);
1010            for _ in 0..padding {
1011                text.push(',');
1012            }
1013            text.push('\n');
1014            from = *end;
1015        }
1016        Ok(text)
1017    }
1018}
1019
1020/// A worksheet's cells (`sheetData`, §18.3.1.80) as text, one line a row with a value, and the
1021/// notes of what the text leaves out.
1022fn read_sheet(
1023    xml: &str,
1024    part: &str,
1025    book: &Book,
1026    budget: &mut Budget,
1027) -> Result<(String, Vec<String>), LogError> {
1028    let mut events = Events::new(xml, part);
1029    let mut lines = Lines {
1030        body: String::new(),
1031        ends: Vec::new(),
1032        start: 0,
1033        next: 1,
1034        budget,
1035    };
1036    // The row being read, by its number.
1037    let mut row: Option<u32> = None;
1038    let mut last_row = 0u32;
1039    let mut last_column = 0u32;
1040    let mut cell: Option<Cell> = None;
1041    let mut in_data = false;
1042    let mut in_value = false;
1043    let mut in_is = false;
1044    let mut in_inline = false;
1045    let mut phonetic = 0usize;
1046    let mut counts = Counts::default();
1047    loop {
1048        let event = events.next()?;
1049        match &event {
1050            Event::Start(element) | Event::Empty(element) => {
1051                let empty = matches!(event, Event::Empty(_));
1052                match element.local_name().as_ref() {
1053                    "sheetData" => in_data = !empty,
1054                    "row" if in_data && row.is_none() => {
1055                        let number = match events.attribute(element, "r")? {
1056                            Some(r) => {
1057                                r.parse::<u32>().ok().filter(|r| *r >= 1).ok_or_else(|| {
1058                                    refused(format!(
1059                                        "a row in {part:?} has the number {:?}",
1060                                        shortened(&r)
1061                                    ))
1062                                })?
1063                            }
1064                            None => last_row + 1,
1065                        };
1066                        if number <= last_row || number > MAX_ROWS {
1067                            return Err(refused(format!(
1068                                "row {number} in {part:?} comes after row {last_row}, or past \
1069                                 row {MAX_ROWS}"
1070                            )));
1071                        }
1072                        last_row = number;
1073                        last_column = 0;
1074                        if !empty {
1075                            row = Some(number);
1076                            lines.open();
1077                        }
1078                    }
1079                    "c" if in_data => {
1080                        let (Some(number), None) = (row, cell.as_ref()) else {
1081                            return Err(refused(format!(
1082                                "a cell in {part:?} lies outside a row, or inside another cell"
1083                            )));
1084                        };
1085                        let column = match events.attribute(element, "r")? {
1086                            Some(reference) => {
1087                                let (column, at_row) =
1088                                    cell_reference(&reference).ok_or_else(|| {
1089                                        refused(format!(
1090                                            "a cell in {part:?} has the reference {:?}",
1091                                            shortened(&reference)
1092                                        ))
1093                                    })?;
1094                                if at_row != number {
1095                                    return Err(refused(format!(
1096                                        "the cell {:?} in {part:?} is in row {number}",
1097                                        shortened(&reference)
1098                                    )));
1099                                }
1100                                column
1101                            }
1102                            None => last_column + 1,
1103                        };
1104                        if column <= last_column || column > MAX_COLUMNS {
1105                            return Err(refused(format!(
1106                                "a cell in row {number} of {part:?} is in column {column}, \
1107                                 after column {last_column}, or past column {MAX_COLUMNS}"
1108                            )));
1109                        }
1110                        last_column = column;
1111                        let style = events
1112                            .attribute(element, "s")?
1113                            .map(|s| {
1114                                s.parse::<usize>().map_err(|_| {
1115                                    refused(format!(
1116                                        "a cell in row {number} of {part:?} has the style {:?}",
1117                                        shortened(&s)
1118                                    ))
1119                                })
1120                            })
1121                            .transpose()?;
1122                        let new = Cell {
1123                            row: number,
1124                            column,
1125                            kind: events.attribute(element, "t")?,
1126                            style,
1127                            formula: false,
1128                            value: None,
1129                            inline: None,
1130                        };
1131                        if empty {
1132                            finish_cell(new, book, &mut lines, &mut counts, part)?;
1133                        } else {
1134                            cell = Some(new);
1135                        }
1136                    }
1137                    "f" => {
1138                        if let Some(cell) = cell.as_mut() {
1139                            cell.formula = true;
1140                        }
1141                    }
1142                    "v" | "is" => {
1143                        if let Some(cell) = cell.as_mut() {
1144                            // One value a cell (§18.3.1.4): a second would be joined to the first.
1145                            if cell.value.is_some() || cell.inline.is_some() {
1146                                return Err(refused(format!(
1147                                    "the cell in row {}, column {}, of {part:?} holds two values",
1148                                    cell.row, cell.column
1149                                )));
1150                            }
1151                            let slot = if element.local_name().as_ref() == "v" {
1152                                in_value = !empty;
1153                                &mut cell.value
1154                            } else {
1155                                in_is = !empty;
1156                                &mut cell.inline
1157                            };
1158                            // An empty `<v/>` holds no value, as no `<v>` doesn't.
1159                            if !empty {
1160                                *slot = Some(String::new());
1161                            }
1162                        }
1163                    }
1164                    "rPh" if !empty => phonetic += 1,
1165                    "t" if !empty => in_inline = in_is && phonetic == 0,
1166                    _ => {}
1167                }
1168            }
1169            Event::End(element) => match element.local_name().as_ref() {
1170                "sheetData" => in_data = false,
1171                "row" if row.is_some() && cell.is_none() => {
1172                    row = None;
1173                    lines.close();
1174                }
1175                "c" => {
1176                    if let Some(done) = cell.take() {
1177                        finish_cell(done, book, &mut lines, &mut counts, part)?;
1178                    }
1179                }
1180                "v" => in_value = false,
1181                "is" => in_is = false,
1182                "t" => in_inline = false,
1183                "rPh" => phonetic = phonetic.saturating_sub(1),
1184                _ => {}
1185            },
1186            Event::Eof => break,
1187            _ => {
1188                if (in_value || in_inline)
1189                    && let Some(text) = events.text(&event)?
1190                    && let Some(cell) = cell.as_mut()
1191                {
1192                    let target = if in_value {
1193                        cell.value.get_or_insert_with(String::new)
1194                    } else {
1195                        cell.inline.get_or_insert_with(String::new)
1196                    };
1197                    target.push_str(&text);
1198                }
1199            }
1200        }
1201    }
1202    if row.is_some() || cell.is_some() {
1203        return Err(refused(format!("the part {part:?} ends inside a row")));
1204    }
1205    Ok((lines.finish()?, counts.notes()))
1206}
1207
1208/// A cell's text as the workbook stores it, by its type (§18.18.11), written in its row; a cell
1209/// with no value is a gap.
1210fn finish_cell(
1211    cell: Cell,
1212    book: &Book,
1213    lines: &mut Lines<'_>,
1214    counts: &mut Counts,
1215    part: &str,
1216) -> Result<(), LogError> {
1217    let at = || {
1218        format!(
1219            "the cell in row {}, column {}, of {part:?}",
1220            cell.row, cell.column
1221        )
1222    };
1223    let value = cell.value.as_deref().map(str::trim);
1224    let text: Option<Cow<'_, str>> = match cell.kind.as_deref().unwrap_or("n") {
1225        "n" => match value.filter(|value| !value.is_empty()) {
1226            None => None,
1227            Some(value) => {
1228                let number = value
1229                    .parse::<f64>()
1230                    .ok()
1231                    .filter(|number| number.is_finite())
1232                    .ok_or_else(|| {
1233                        refused(format!(
1234                            "{} holds {:?}, not a number",
1235                            at(),
1236                            shortened(value)
1237                        ))
1238                    })?;
1239                let kind = cell
1240                    .style
1241                    .and_then(|style| book.styles.get(style).copied().flatten());
1242                match kind.map(|kind| date_text(number, kind, book.date1904)) {
1243                    Some(Some(date)) => Some(Cow::Owned(date)),
1244                    Some(None) => {
1245                        counts.dates_kept += 1;
1246                        Some(Cow::Borrowed(value))
1247                    }
1248                    None => Some(Cow::Borrowed(value)),
1249                }
1250            }
1251        },
1252        "s" => match value {
1253            None => None,
1254            Some(value) => {
1255                let string = value
1256                    .parse::<usize>()
1257                    .ok()
1258                    .and_then(|index| book.strings.get(index))
1259                    .ok_or_else(|| {
1260                        refused(format!(
1261                            "{} names the shared string {:?}, which isn't there",
1262                            at(),
1263                            shortened(value)
1264                        ))
1265                    })?;
1266                Some(Cow::Borrowed(string))
1267            }
1268        },
1269        "inlineStr" => cell
1270            .inline
1271            .as_deref()
1272            .or(cell.value.as_deref())
1273            .map(Cow::Borrowed),
1274        "str" => cell.value.as_deref().map(Cow::Borrowed),
1275        "d" => value.map(Cow::Borrowed),
1276        "b" => match value {
1277            None => None,
1278            Some("1") => Some(Cow::Borrowed("TRUE")),
1279            Some("0") => Some(Cow::Borrowed("FALSE")),
1280            Some(other) => {
1281                return Err(refused(format!(
1282                    "{} holds {:?}, not a truth value",
1283                    at(),
1284                    shortened(other)
1285                )));
1286            }
1287        },
1288        "e" => {
1289            counts.errors += 1;
1290            return Ok(());
1291        }
1292        other => {
1293            return Err(refused(format!(
1294                "{} has the type {:?}, which isn't one",
1295                at(),
1296                shortened(other)
1297            )));
1298        }
1299    };
1300    match text {
1301        Some(text) if !text.is_empty() => lines.cell(cell.column, &text)?,
1302        _ if cell.formula => counts.unsaved += 1,
1303        _ => {}
1304    }
1305    Ok(())
1306}
1307
1308/// A cell reference's column and row, both from 1: `B12` is (2, 12); `None` for anything else.
1309fn cell_reference(reference: &str) -> Option<(u32, u32)> {
1310    let split = reference.find(|c: char| c.is_ascii_digit())?;
1311    let (letters, digits) = reference.split_at(split);
1312    if letters.is_empty() || letters.len() > 3 || !digits.bytes().all(|b| b.is_ascii_digit()) {
1313        return None;
1314    }
1315    let mut column = 0u32;
1316    for letter in letters.bytes() {
1317        if !letter.is_ascii_uppercase() {
1318            return None;
1319        }
1320        column = column * 26 + u32::from(letter - b'A' + 1);
1321    }
1322    let row = digits.parse::<u32>().ok().filter(|row| *row >= 1)?;
1323    Some((column, row))
1324}
1325
1326/// A cell's text as a comma-separated line holds it: quoted if it holds a comma or a double
1327/// quote, its quotes doubled; a line break becomes a space.
1328pub(crate) fn escaped(value: &str) -> Cow<'_, str> {
1329    let value: Cow<'_, str> = if value.contains(['\r', '\n']) {
1330        Cow::Owned(value.replace("\r\n", " ").replace(['\r', '\n'], " "))
1331    } else {
1332        Cow::Borrowed(value)
1333    };
1334    if value.contains([',', '"']) {
1335        Cow::Owned(format!("\"{}\"", value.replace('"', "\"\"")))
1336    } else {
1337        value
1338    }
1339}
1340
1341/// A name cut to 60 characters for an error, so a hostile part's long name isn't copied whole.
1342pub(crate) fn shortened(name: &str) -> String {
1343    const MOST: usize = 60;
1344    if name.chars().count() <= MOST {
1345        name.to_owned()
1346    } else {
1347        name.chars().take(MOST).collect::<String>() + "…"
1348    }
1349}
1350
1351/// The most sheets [`listed`] names.
1352pub(crate) const MOST_LISTED: usize = 20;
1353
1354/// Sheets for an error that asks for one to be chosen: each as `describe` writes it from its index
1355/// and its name, cut to 60 characters, at most [`MOST_LISTED`] of them and then how many more,
1356/// so a hostile workbook's many sheets aren't copied whole.
1357pub(crate) fn listed<'a>(
1358    sheets: impl ExactSizeIterator<Item = (usize, &'a str)>,
1359    describe: impl Fn(usize, &str) -> String,
1360) -> String {
1361    let count = sheets.len();
1362    let mut named: Vec<String> = sheets
1363        .take(MOST_LISTED)
1364        .map(|(index, name)| describe(index, &shortened(name)))
1365        .collect();
1366    if count > MOST_LISTED {
1367        named.push(format!("and {} more", count - MOST_LISTED));
1368    }
1369    named.join(", ")
1370}
1371
1372#[cfg(test)]
1373mod tests {
1374    use std::io::{Cursor, Write};
1375
1376    use proptest::prelude::*;
1377    use zip::write::SimpleFileOptions;
1378
1379    use super::*;
1380    use crate::log::LogFormat;
1381
1382    const RELS: &str = 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>"#;
1383    const MAIN: &str = "http://schemas.openxmlformats.org/spreadsheetml/2006/main";
1384    const R: &str = "http://schemas.openxmlformats.org/officeDocument/2006/relationships";
1385    const TYPE: &str = "http://schemas.openxmlformats.org/officeDocument/2006/relationships";
1386
1387    /// An archive of the given parts, each deflated.
1388    fn zipped(parts: &[(&str, &str)]) -> Vec<u8> {
1389        let mut zip = zip::ZipWriter::new(Cursor::new(Vec::new()));
1390        for (name, text) in parts {
1391            zip.start_file(*name, SimpleFileOptions::default()).unwrap();
1392            zip.write_all(text.as_bytes()).unwrap();
1393        }
1394        zip.finish().unwrap().into_inner()
1395    }
1396
1397    /// A workbook of the given sheets' `sheetData` contents, with shared strings and the styles'
1398    /// `numFmts` and `cellXfs` contents when given, and `workbookPr` attributes.
1399    struct Book<'a> {
1400        sheets: Vec<(&'a str, String)>,
1401        strings: Option<&'a str>,
1402        styles: Option<&'a str>,
1403        properties: &'a str,
1404    }
1405
1406    impl<'a> Book<'a> {
1407        fn one(data: &str) -> Self {
1408            Book {
1409                sheets: vec![("Flight", data.to_owned())],
1410                strings: None,
1411                styles: None,
1412                properties: "",
1413            }
1414        }
1415
1416        fn bytes(&self) -> Vec<u8> {
1417            let mut list = String::new();
1418            let mut rels = String::new();
1419            let mut parts: Vec<(String, String)> = Vec::new();
1420            for (n, (name, data)) in self.sheets.iter().enumerate() {
1421                list += &format!(r#"<sheet name="{name}" sheetId="{}" r:id="s{n}"/>"#, n + 1);
1422                rels += &format!(
1423                    r#"<Relationship Id="s{n}" Type="{TYPE}/worksheet" Target="worksheets/sheet{n}.xml"/>"#
1424                );
1425                parts.push((
1426                    format!("xl/worksheets/sheet{n}.xml"),
1427                    format!(
1428                        r#"<worksheet xmlns="{MAIN}"><sheetData>{data}</sheetData></worksheet>"#
1429                    ),
1430                ));
1431            }
1432            if let Some(strings) = self.strings {
1433                rels += &format!(
1434                    r#"<Relationship Id="t" Type="{TYPE}/sharedStrings" Target="sharedStrings.xml"/>"#
1435                );
1436                parts.push((
1437                    "xl/sharedStrings.xml".to_owned(),
1438                    format!(r#"<sst xmlns="{MAIN}">{strings}</sst>"#),
1439                ));
1440            }
1441            if let Some(styles) = self.styles {
1442                rels +=
1443                    &format!(r#"<Relationship Id="y" Type="{TYPE}/styles" Target="styles.xml"/>"#);
1444                parts.push((
1445                    "xl/styles.xml".to_owned(),
1446                    format!(r#"<styleSheet xmlns="{MAIN}">{styles}</styleSheet>"#),
1447                ));
1448            }
1449            parts.push((
1450                "xl/workbook.xml".to_owned(),
1451                format!(
1452                    r#"<workbook xmlns="{MAIN}" xmlns:r="{R}"><workbookPr {}/><sheets>{list}</sheets></workbook>"#,
1453                    self.properties
1454                ),
1455            ));
1456            parts.push((
1457                "xl/_rels/workbook.xml.rels".to_owned(),
1458                format!(
1459                    r#"<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">{rels}</Relationships>"#
1460                ),
1461            ));
1462            parts.push(("_rels/.rels".to_owned(), RELS.to_owned()));
1463            let refs: Vec<(&str, &str)> = parts
1464                .iter()
1465                .map(|(name, text)| (name.as_str(), text.as_str()))
1466                .collect();
1467            zipped(&refs)
1468        }
1469    }
1470
1471    fn text_of(book: &Book<'_>) -> String {
1472        sheets(&book.bytes()).unwrap().remove(0).text
1473    }
1474
1475    fn refusal(book: &Book<'_>) -> String {
1476        match sheets(&book.bytes()) {
1477            Err(LogError::Workbook { message }) => message,
1478            other => panic!("expected a workbook refusal, got {other:?}"),
1479        }
1480    }
1481
1482    #[test]
1483    fn each_kind_of_cell_is_written_as_stored() {
1484        let book = Book {
1485            strings: Some(
1486                // 0: plain; 1: two runs and a phonetic guide; 2: a comma and a quote; 3: two lines.
1487                r#"<si><t>Time (s)</t></si><si><r><t>Alt</t></r><r><t>itude (m)</t></r><rPh sb="0" eb="1"><t>X</t></rPh></si><si><t>a, "b"</t></si><si><t>line
1488two</t></si>"#,
1489            ),
1490            ..Book::one(
1491                r#"<row r="1"><c r="A1" t="s"><v>0</v></c><c r="B1" t="s"><v>1</v></c></row>
1492                <row r="2"><c r="A2"><v>0.10000000000000001</v></c><c r="B2" t="inlineStr"><is><t>x &amp; &#x41;</t></is></c><c r="C2" t="str"><f>A2</f><v>made</v></c></row>
1493                <row r="3"><c r="A3" t="e"><v>#DIV/0!</v></c><c r="B3" t="b"><v>1</v></c><c r="C3" t="b"><v>0</v></c><c r="D3" t="s"><v>2</v></c></row>
1494                <row r="4"><c r="A4"><f>B4</f></c><c r="B4" t="d"><v>2023-03-15T18:00:00</v></c><c r="C4" t="s"><v>3</v></c></row>"#,
1495            )
1496        };
1497        let sheet = sheets(&book.bytes()).unwrap().remove(0);
1498        assert_eq!(
1499            sheet.text,
1500            "Time (s),Altitude (m),,\n\
1501             0.10000000000000001,x & A,made,\n\
1502             ,TRUE,FALSE,\"a, \"\"b\"\"\"\n\
1503             ,2023-03-15T18:00:00,line two,\n"
1504        );
1505        assert_eq!(sheet.notes.len(), 2, "{:?}", sheet.notes);
1506        assert!(
1507            sheet.notes[0].starts_with("1 cells holding an error"),
1508            "{:?}",
1509            sheet.notes
1510        );
1511        assert!(
1512            sheet.notes[1].starts_with("1 formula cells"),
1513            "{:?}",
1514            sheet.notes
1515        );
1516    }
1517
1518    #[test]
1519    fn gaps_are_kept_and_rows_padded_to_the_widest() {
1520        // A gap in column B, a short row, a formatted cell with no value past the last value (it
1521        // widens nothing), an empty row and a row with no `r` (the next number).
1522        let text = text_of(&Book::one(
1523            r#"<row r="1"><c r="A1"><v>1</v></c><c r="C1"><v>3</v></c><c r="F1" s="0"/></row>
1524            <row r="2"><c r="A2"><v>4</v></c></row>
1525            <row r="3"></row><row r="5"/>
1526            <row><c><v>7</v></c><c><v>8</v></c></row>"#,
1527        ));
1528        assert_eq!(text, "1,,3\n4,,\n7,8,\n");
1529    }
1530
1531    #[test]
1532    fn a_sheet_reads_as_the_file_it_came_from() {
1533        let book = Book {
1534            sheets: vec![
1535                (
1536                    "Data",
1537                    r#"<row r="1"><c r="A1" t="inlineStr"><is><t>Time</t></is></c><c r="B1" t="inlineStr"><is><t>Altitude</t></is></c><c r="C1" t="inlineStr"><is><t>Velocity</t></is></c></row>
1538                    <row r="2"><c r="A2"><v>0</v></c><c r="B2"><v>0</v></c><c r="C2"><v>0</v></c></row>
1539                    <row r="3"><c r="A3"><v>0.05</v></c><c r="B3"><v>1</v></c><c r="C3"><v>8</v></c></row>"#
1540                        .to_owned(),
1541                ),
1542                ("Chart", String::new()),
1543            ],
1544            ..Book::one("")
1545        };
1546        let logs = read(&book.bytes()).unwrap();
1547        assert_eq!(logs.len(), 2);
1548        let log = logs[0].log.as_ref().unwrap();
1549        assert_eq!(log.format, LogFormat::PerfectFliteCsv);
1550        assert!(log.notes[0].contains("sheet \"Data\""), "{:?}", log.notes);
1551        assert_eq!(logs[1].name, "Chart");
1552        assert_eq!(logs[1].log, Err(LogError::NoData { format: FORMAT }));
1553    }
1554
1555    #[test]
1556    fn a_number_under_a_date_format_is_its_date() {
1557        // Serial 45,000 is 2023-03-15: 45,000 − 25,569 = 19,431 days after 1970-01-01, and
1558        // 2023-01-01 is 19,358 (53 years, 13 of them leap); 73 days on is 14 March's day after.
1559        let styles = r#"<numFmts count="4"><numFmt numFmtId="164" formatCode="yyyy\-mm\-dd\ hh:mm:ss.000"/><numFmt numFmtId="165" formatCode="0.00&quot;m&quot;"/><numFmt numFmtId="166" formatCode="[Red]0.0"/><numFmt numFmtId="167" formatCode="mm:ss.0;@"/></numFmts>
1560            <cellXfs count="10"><xf numFmtId="0"/><xf numFmtId="14"/><xf numFmtId="21"/><xf numFmtId="22"/><xf numFmtId="46"/><xf numFmtId="164"/><xf numFmtId="165"/><xf numFmtId="166"/><xf numFmtId="167"/><xf numFmtId="11"/></cellXfs>"#;
1561        let book = Book {
1562            styles: Some(styles),
1563            ..Book::one(
1564                r#"<row r="1"><c r="A1" s="1"><v>45000</v></c><c r="B1" s="2"><v>0.5</v></c><c r="C1" s="3"><v>45000.75</v></c><c r="D1" s="4"><v>1.5</v></c><c r="E1" s="5"><v>45000.0000434028</v></c></row>
1565                <row r="2"><c r="A2" s="6"><v>45000</v></c><c r="B2" s="7"><v>45000</v></c><c r="C2" s="8"><v>0.0006</v></c><c r="D2" s="9"><v>45000</v></c><c r="E2" s="0"><v>45000</v></c></row>
1566                <row r="3"><c r="A3" s="1"><v>59</v></c><c r="B3" s="1"><v>2958466</v></c><c r="C3" s="1"><v>45000.25</v></c><c r="D3" s="2"><v>1.25</v></c><c r="E3" s="12"><v>3</v></c></row>"#,
1567            )
1568        };
1569        let sheet = sheets(&book.bytes()).unwrap().remove(0);
1570        // E1: 0.0000434028 days is 3.750002 s, 3.75 to the millisecond. C2: 0.0006 days is 51.84 s.
1571        // E3's style isn't in the list: a number. A3 (1900-02-28), B3 (past 9999-12-31) and D3, a
1572        // time of day that also holds serial 1's day, 1900-01-01, are kept as stored.
1573        assert_eq!(
1574            sheet.text,
1575            "2023-03-15,12:00:00,2023-03-15 18:00:00,36:00:00,2023-03-15 00:00:03.75\n\
1576             45000,45000,00:00:51.84,45000,45000\n\
1577             59,2958466,2023-03-15 06:00:00,1.25,3\n"
1578        );
1579        assert_eq!(sheet.notes.len(), 1, "{:?}", sheet.notes);
1580        assert!(
1581            sheet.notes[0].starts_with("3 numbers under a date format kept"),
1582            "{:?}",
1583            sheet.notes
1584        );
1585    }
1586
1587    #[test]
1588    fn the_1904_date_system_counts_from_1904() {
1589        let book = Book {
1590            styles: Some(r#"<cellXfs><xf numFmtId="0"/><xf numFmtId="14"/></cellXfs>"#),
1591            properties: r#"date1904="1""#,
1592            ..Book::one(
1593                r#"<row r="1"><c r="A1" s="1"><v>0</v></c><c r="B1" s="1"><v>43538</v></c></row>"#,
1594            )
1595        };
1596        // 43,538 = 45,000 − 1,462, the days between the two systems' epochs.
1597        assert_eq!(text_of(&book), "1904-01-01,2023-03-15\n");
1598    }
1599
1600    #[test]
1601    fn format_codes_are_told_apart() {
1602        for (code, kind) in [
1603            ("General", None),
1604            ("0.00E+00", None),
1605            ("#,##0.00", None),
1606            ("@", None),
1607            ("0.0 \"ms\"", None),
1608            ("0.0\\s", None),
1609            ("[Blue]0.00", None),
1610            ("[$-409]0.00", None),
1611            ("mm-dd-yy", Some(DateKind::Date)),
1612            ("d-mmm", Some(DateKind::Date)),
1613            ("mmm-yy", Some(DateKind::Date)),
1614            ("h:mm AM/PM", Some(DateKind::Time)),
1615            ("hh:mm:ss", Some(DateKind::Time)),
1616            ("mm:ss", Some(DateKind::Time)),
1617            ("mmss.0", Some(DateKind::Time)),
1618            ("m/d/yy h:mm", Some(DateKind::DateTime)),
1619            ("yyyy-mm-dd\\ hh:mm:ss", Some(DateKind::DateTime)),
1620            ("[h]:mm:ss", Some(DateKind::Elapsed)),
1621            ("[mm]:ss", Some(DateKind::Elapsed)),
1622            ("[mm]", Some(DateKind::Elapsed)),
1623            ("[m]", Some(DateKind::Elapsed)),
1624            ("[ss].00", Some(DateKind::Elapsed)),
1625            ("[$-F800]dddd, mmmm dd, yyyy", Some(DateKind::Date)),
1626            ("0.00;hh:mm", None),
1627            ("\"d\"0", None),
1628            ("a/p h", Some(DateKind::Time)),
1629        ] {
1630            assert_eq!(format_kind(code), kind, "{code}");
1631        }
1632    }
1633
1634    #[test]
1635    fn hostile_codes_and_numbers_are_kept_as_stored() {
1636        // Unclosed quotes and brackets, an empty code, a long name with emoji: never a date, and
1637        // never a panic.
1638        let long = "🚀".repeat(130);
1639        for code in [
1640            "",
1641            "\"",
1642            "[",
1643            "[h",
1644            "\"yyyy",
1645            "\\",
1646            "_",
1647            "*",
1648            "\u{1b}[0m",
1649            long.as_str(),
1650        ] {
1651            assert_eq!(format_kind(code), None, "{code:?}");
1652        }
1653        // A number under a date format that no date can be is kept as stored, by the caller.
1654        for serial in [
1655            f64::NAN,
1656            f64::INFINITY,
1657            -1.0,
1658            1e300,
1659            2_958_466.0,
1660            f64::MIN_POSITIVE,
1661        ] {
1662            assert_eq!(date_text(serial, DateKind::Date, false), None, "{serial}");
1663        }
1664        // −0 is serial 0: a time of midnight, and in the 1904 system its first day.
1665        assert_eq!(
1666            date_text(-0.0, DateKind::Time, false).as_deref(),
1667            Some("00:00:00")
1668        );
1669        assert_eq!(
1670            date_text(-0.0, DateKind::Date, true).as_deref(),
1671            Some("1904-01-01")
1672        );
1673        // Text that isn't a number, NaN and infinity among it, is refused in a number's cell.
1674        for text in ["NaN", "inf", "-inf", "0x10", ""] {
1675            let data = format!(
1676                r#"<row r="1"><c r="A1" t="n"><v>{text}</v></c><c r="B1"><v>1</v></c></row>"#
1677            );
1678            match sheets(&Book::one(&data).bytes()) {
1679                Ok(sheets) if text.is_empty() => assert_eq!(sheets[0].text, ",1\n"),
1680                Err(LogError::Workbook { message }) => assert!(message.contains("not a number")),
1681                other => panic!("{text:?}: {other:?}"),
1682            }
1683        }
1684    }
1685
1686    #[test]
1687    fn every_byte_written_is_counted() {
1688        // 40 truth values, each 1 byte stored and 4 or 5 written: 40 × 4 + 39 commas + 1.
1689        let cells: String = (1..=40)
1690            .map(|n| format!(r#"<c r="{}1" t="b"><v>1</v></c>"#, column_name(n)))
1691            .collect();
1692        let bytes = Book::one(&format!(r#"<row r="1">{cells}</row>"#)).bytes();
1693        assert_eq!(sheets_within(&bytes, 200).unwrap()[0].text.len(), 200);
1694        assert!(sheets_within(&bytes, 199).is_err());
1695        // Shared strings are kept whole while the sheets are read: 1,000 empty ones are 4,000
1696        // bytes of their ends, whether or not a cell names them.
1697        let strings = "<si/>".repeat(1000);
1698        let book = Book {
1699            strings: Some(&strings),
1700            ..Book::one("")
1701        };
1702        assert!(sheets_within(&book.bytes(), 4_000).is_ok());
1703        assert!(sheets_within(&book.bytes(), 3_999).is_err());
1704    }
1705
1706    /// A column's letters, from 1: `A`, …, `Z`, `AA`.
1707    fn column_name(mut n: u32) -> String {
1708        let mut letters = Vec::new();
1709        while n > 0 {
1710            letters.push(char::from(b'A' + u8::try_from((n - 1) % 26).unwrap()));
1711            n = (n - 1) / 26;
1712        }
1713        letters.iter().rev().collect()
1714    }
1715
1716    #[test]
1717    fn a_cell_holds_one_value() {
1718        let message = refusal(&Book::one(
1719            r#"<row r="1"><c r="A1"><v>1</v><v>2</v></c></row>"#,
1720        ));
1721        assert!(message.contains("two values"), "{message}");
1722        // An empty `<v/>` is an empty cell, whatever its type.
1723        let text = text_of(&Book::one(
1724            r#"<row r="1"><c r="A1" t="s"><v/></c><c r="B1" t="b"><v/></c><c r="C1"><v>3</v></c></row>"#,
1725        ));
1726        assert_eq!(text, ",,3\n");
1727        // A `t` outside the inline string, such as in an extension, isn't its text.
1728        let text = text_of(&Book::one(
1729            r#"<row r="1"><c r="A1" t="inlineStr"><is><t>a</t></is><extLst><t>b</t></extLst></c></row>"#,
1730        ));
1731        assert_eq!(text, "a\n");
1732    }
1733
1734    #[test]
1735    fn many_cell_formats_classify_one_code_once() {
1736        // 20,000 cell formats naming one 20,000-character code: classified once, not 20,000
1737        // times (which took seconds before, and hours at the part's bound).
1738        let code = "0".repeat(20_000);
1739        let xfs = r#"<xf numFmtId="164"/>"#.repeat(20_000);
1740        let styles = format!(
1741            r#"<numFmts><numFmt numFmtId="164" formatCode="{code}"/></numFmts><cellXfs>{xfs}</cellXfs>"#
1742        );
1743        let book = Book {
1744            styles: Some(&styles),
1745            ..Book::one(r#"<row r="1"><c r="A1" s="19999"><v>45000</v></c></row>"#)
1746        };
1747        assert_eq!(text_of(&book), "45000\n");
1748    }
1749
1750    #[test]
1751    fn the_last_day_is_the_last_in_either_system() {
1752        // 9999-12-31 is serial 2,958,465 from 1900 and 2,957,003 (1,462 fewer) from 1904.
1753        assert_eq!(
1754            date_text(2_957_003.0, DateKind::Date, true).as_deref(),
1755            Some("9999-12-31")
1756        );
1757        assert_eq!(date_text(2_957_004.0, DateKind::Date, true), None);
1758        assert_eq!(date_text(2_958_000.0, DateKind::Date, true), None);
1759        // A time that rounds past the last day's end is past it too.
1760        assert_eq!(
1761            date_text(2_958_465.999_999_995, DateKind::DateTime, false),
1762            None
1763        );
1764        assert_eq!(
1765            date_text(2_958_465.5, DateKind::DateTime, false).as_deref(),
1766            Some("9999-12-31 12:00:00")
1767        );
1768        // A time of day a breath short of midnight rounds to it, and stays a time of day.
1769        assert_eq!(
1770            date_text(0.999_999_999_9, DateKind::Time, false).as_deref(),
1771            Some("00:00:00")
1772        );
1773    }
1774
1775    #[test]
1776    fn clocks_and_calendars() {
1777        assert_eq!(clock(0), "00:00:00");
1778        assert_eq!(clock(3_723_500), "01:02:03.5");
1779        assert_eq!(clock(90_000_001), "25:00:00.001");
1780        assert_eq!(civil_from_days(0), (1970, 1, 1));
1781        assert_eq!(civil_from_days(-25_569 + 61), (1900, 3, 1));
1782        assert_eq!(civil_from_days(11_016), (2000, 2, 29));
1783        // The last day: serial 2,958,465.
1784        assert_eq!(civil_from_days(EPOCH_1900 + LAST_SERIAL), (9999, 12, 31));
1785        // A time of day of 23:59:59.9996 rounds into the next day.
1786        assert_eq!(
1787            date_text(
1788                45_000.0 + 86_399.999_6 / 86_400.0,
1789                DateKind::DateTime,
1790                false
1791            )
1792            .as_deref(),
1793            Some("2023-03-16 00:00:00")
1794        );
1795    }
1796
1797    #[test]
1798    fn cells_and_rows_out_of_order_are_refused() {
1799        for (data, words) in [
1800            (
1801                r#"<row r="1"><c r="B1"><v>1</v></c><c r="A1"><v>2</v></c></row>"#,
1802                "after column 2",
1803            ),
1804            (
1805                r#"<row r="1"><c r="A1"><v>1</v></c><c r="A1"><v>2</v></c></row>"#,
1806                "after column 1",
1807            ),
1808            (r#"<row r="2"/><row r="1"/>"#, "comes after row 2"),
1809            (r#"<row r="1"><c r="A2"><v>1</v></c></row>"#, "is in row 1"),
1810            (
1811                r#"<row r="1"><c r="XFE1"><v>1</v></c></row>"#,
1812                "past column 16384",
1813            ),
1814            (r#"<row r="1048577"/>"#, "past row 1048576"),
1815            (
1816                r#"<row r="1"><c r="a1"><v>1</v></c></row>"#,
1817                "has the reference",
1818            ),
1819            (
1820                r#"<row r="1"><c r="A1"><v>1e999</v></c></row>"#,
1821                "not a number",
1822            ),
1823            (r#"<row r="1"><c r="A1"><v>x</v></c></row>"#, "not a number"),
1824            (
1825                r#"<row r="1"><c r="A1" t="s"><v>0</v></c></row>"#,
1826                "isn't there",
1827            ),
1828            (
1829                r#"<row r="1"><c r="A1" t="b"><v>2</v></c></row>"#,
1830                "not a truth value",
1831            ),
1832            (
1833                r#"<row r="1"><c r="A1" t="q"><v>2</v></c></row>"#,
1834                "isn't one",
1835            ),
1836            (
1837                r#"<row r="1"><c r="A1" s="x"><v>2</v></c></row>"#,
1838                "has the style",
1839            ),
1840            (r#"<c r="A1"><v>2</v></c>"#, "outside a row"),
1841            (
1842                r#"<row r="1"><c r="A1"><v>&lol;</v></c></row>"#,
1843                "isn't XML's own",
1844            ),
1845        ] {
1846            let message = refusal(&Book::one(data));
1847            assert!(message.contains(words), "{data}: {message}");
1848        }
1849    }
1850
1851    #[test]
1852    fn a_document_type_declaration_is_refused() {
1853        let doctype = r#"<!DOCTYPE x [<!ENTITY a "aaaa">]>"#;
1854        let book = Book::one("");
1855        let mut parts: Vec<(String, String)> = Vec::new();
1856        let bytes = book.bytes();
1857        for member in archive::unpack(&bytes).unwrap() {
1858            let mut text = String::from_utf8(member.bytes).unwrap();
1859            if member.name.ends_with("sheet0.xml") {
1860                text = format!("{doctype}{text}");
1861            }
1862            parts.push((member.name, text));
1863        }
1864        let refs: Vec<(&str, &str)> = parts
1865            .iter()
1866            .map(|(n, t)| (n.as_str(), t.as_str()))
1867            .collect();
1868        match sheets(&zipped(&refs)) {
1869            Err(LogError::Workbook { message }) => {
1870                assert!(message.contains("document type declaration"), "{message}");
1871            }
1872            other => panic!("expected a refusal, got {other:?}"),
1873        }
1874    }
1875
1876    #[test]
1877    fn the_text_is_bounded() {
1878        // 40 cells of one 1,000-byte shared string: 40,000 bytes of text from 2 KB of XML, and
1879        // 40 line ends; the string itself and its end's 4 bytes are counted once, as it's kept.
1880        let long = "x".repeat(1000);
1881        let strings = format!("<si><t>{long}</t></si>");
1882        let cells: String = (1..=40)
1883            .map(|row| format!(r#"<row r="{row}"><c r="A{row}" t="s"><v>0</v></c></row>"#))
1884            .collect();
1885        let book = Book {
1886            strings: Some(&strings),
1887            ..Book::one(&cells)
1888        };
1889        let bytes = book.bytes();
1890        assert_eq!(sheets_within(&bytes, 41_044).unwrap()[0].text.len(), 40_040);
1891        assert!(sheets_within(&bytes, 41_043).is_err());
1892        match sheets_within(&bytes, 39_000) {
1893            Err(LogError::Workbook { message }) => assert!(message.contains("39000"), "{message}"),
1894            other => panic!("expected the bound, got {other:?}"),
1895        }
1896        // Gaps count: one cell in column XFD writes 16,383 commas.
1897        let wide = Book::one(r#"<row r="1"><c r="XFD1"><v>1</v></c></row>"#).bytes();
1898        assert!(sheets_within(&wide, 16_000).is_err());
1899        assert_eq!(sheets_within(&wide, 16_400).unwrap()[0].text.len(), 16_385);
1900    }
1901
1902    #[test]
1903    fn what_isnt_a_workbook_is_told() {
1904        assert!(matches!(
1905            sheets(b"Time,Altitude\n"),
1906            Err(LogError::NotThisFormat { .. })
1907        ));
1908        // A zip archive with no office document, and one whose document isn't a workbook.
1909        assert!(matches!(
1910            sheets(&zipped(&[("a.csv", "Time,Altitude\n")])),
1911            Err(LogError::NotThisFormat { .. })
1912        ));
1913        let docx = zipped(&[
1914            ("_rels/.rels", RELS),
1915            (
1916                "xl/workbook.xml",
1917                r#"<document xmlns="http://schemas.openxmlformats.org/wordprocessingml/2006/main"/>"#,
1918            ),
1919        ]);
1920        assert!(matches!(sheets(&docx), Err(LogError::NotThisFormat { .. })));
1921        // A sheet named in the workbook whose part is missing.
1922        let mut book = Book::one("").bytes();
1923        let members = archive::unpack(&book).unwrap();
1924        let parts: Vec<(&str, &str)> = members
1925            .iter()
1926            .filter(|m| !m.name.ends_with("sheet0.xml"))
1927            .map(|m| (m.name.as_str(), std::str::from_utf8(&m.bytes).unwrap()))
1928            .collect();
1929        book = zipped(&parts);
1930        assert!(refusal_of(&book).contains("is missing"));
1931    }
1932
1933    fn refusal_of(bytes: &[u8]) -> String {
1934        match sheets(bytes) {
1935            Err(LogError::Workbook { message }) => message,
1936            other => panic!("expected a workbook refusal, got {other:?}"),
1937        }
1938    }
1939
1940    #[test]
1941    fn two_sheets_may_not_share_a_part() {
1942        let bytes = Book::one("").bytes();
1943        let mut parts: Vec<(String, String)> = Vec::new();
1944        for member in archive::unpack(&bytes).unwrap() {
1945            let mut text = String::from_utf8(member.bytes).unwrap();
1946            if member.name == "xl/workbook.xml" {
1947                text = text.replace(
1948                    "</sheets>",
1949                    r#"<sheet name="Again" sheetId="2" r:id="s0"/></sheets>"#,
1950                );
1951            }
1952            parts.push((member.name, text));
1953        }
1954        let refs: Vec<(&str, &str)> = parts
1955            .iter()
1956            .map(|(n, t)| (n.as_str(), t.as_str()))
1957            .collect();
1958        assert!(refusal_of(&zipped(&refs)).contains("share the part"));
1959    }
1960
1961    #[test]
1962    fn prefixed_elements_and_strict_namespaces_read() {
1963        let strict = "http://purl.oclc.org/ooxml/spreadsheetml/main";
1964        let parts = [
1965            ("_rels/.rels", RELS.to_owned()),
1966            (
1967                "xl/workbook.xml",
1968                format!(
1969                    r#"<x:workbook xmlns:x="{strict}" xmlns:r="http://purl.oclc.org/ooxml/officeDocument/relationships"><x:sheets><x:sheet name="S" sheetId="1" r:id="a"/></x:sheets></x:workbook>"#
1970                ),
1971            ),
1972            (
1973                "xl/_rels/workbook.xml.rels",
1974                r#"<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="a" Type="http://purl.oclc.org/ooxml/officeDocument/relationships/worksheet" Target="/xl/worksheets/../sheets/one.xml"/></Relationships>"#.to_owned(),
1975            ),
1976            (
1977                "xl/sheets/one.xml",
1978                format!(
1979                    r#"<x:worksheet xmlns:x="{strict}"><x:sheetData><x:row x:r="1"><x:c r="B1"><x:v>2</x:v></x:c></x:row></x:sheetData></x:worksheet>"#
1980                ),
1981            ),
1982        ];
1983        let refs: Vec<(&str, &str)> = parts.iter().map(|(n, t)| (*n, t.as_str())).collect();
1984        assert_eq!(sheets(&zipped(&refs)).unwrap()[0].text, ",2\n");
1985    }
1986
1987    #[test]
1988    fn a_workbook_given_to_import_is_sent_to_this_module() {
1989        match import::read(&Book::one("").bytes()) {
1990            Err(LogError::Archive { message }) => {
1991                assert!(message.contains("workbook::read"), "{message}");
1992            }
1993            other => panic!("expected the archive refusal, got {other:?}"),
1994        }
1995    }
1996
1997    proptest! {
1998        /// Any cells, in order, come back in their columns, the commas and quotes undone.
1999        #[test]
2000        fn cells_come_back_in_their_columns(
2001            rows in proptest::collection::vec(
2002                proptest::collection::btree_map(1u32..12, "[a-z ,\"]{1,6}", 1..5),
2003                1..6,
2004            )
2005        ) {
2006            let mut data = String::new();
2007            for (n, cells) in rows.iter().enumerate() {
2008                let r = n + 1;
2009                data += &format!(r#"<row r="{r}">"#);
2010                for (column, text) in cells {
2011                    let letter = char::from(b'A' + u8::try_from(column - 1).unwrap());
2012                    let escaped = text.replace('&', "&amp;").replace('"', "&quot;");
2013                    data += &format!(
2014                        r#"<c r="{letter}{r}" t="inlineStr"><is><t xml:space="preserve">{escaped}</t></is></c>"#
2015                    );
2016                }
2017                data += "</row>";
2018            }
2019            let text = text_of(&Book::one(&data));
2020            let width = rows.iter().filter_map(|cells| cells.keys().last()).max().copied().unwrap();
2021            let lines: Vec<&str> = text.lines().collect();
2022            prop_assert_eq!(lines.len(), rows.len());
2023            for (line, cells) in lines.iter().zip(&rows) {
2024                let read = split(line);
2025                prop_assert_eq!(read.len(), width as usize);
2026                for (index, cell) in read.iter().enumerate() {
2027                    let column = u32::try_from(index).unwrap() + 1;
2028                    let want = cells.get(&column).map_or("", String::as_str);
2029                    prop_assert_eq!(cell.as_str(), want);
2030                }
2031            }
2032        }
2033    }
2034
2035    /// A comma-separated line's cells, double quotes undone: RFC 4180's rule, written out here
2036    /// on its own.
2037    fn split(line: &str) -> Vec<String> {
2038        let mut cells = vec![String::new()];
2039        let mut quoted = false;
2040        let mut chars = line.chars().peekable();
2041        while let Some(c) = chars.next() {
2042            match (c, quoted) {
2043                ('"', true) if chars.peek() == Some(&'"') => {
2044                    chars.next();
2045                    cells.last_mut().unwrap().push('"');
2046                }
2047                ('"', _) => quoted = !quoted,
2048                (',', false) => cells.push(String::new()),
2049                (c, _) => cells.last_mut().unwrap().push(c),
2050            }
2051        }
2052        cells
2053    }
2054}