Oregami
Repositories/oxedyne/fe2o3

oxedyne/fe2o3/fe2o3_file/src/office/xlsx/read.rs

15.0 KiB, 25 runs

created by r1870400018:22746, which is this file's identity for as long as the history lasts, whatever it is later renamed to

download · who wrote it · its history

1//! Reading a `.xlsx` into the neutral spreadsheet.
2//!
3//! The three traps [`crate::office::xlsx`] names are all here, and each of them is the kind of wrong
4//! that looks right: a shared string read as its index gives a column of small integers, a date read
5//! without its style gives a column of five-digit numbers, and a cell placed by its position rather
6//! than by its address puts every value after a gap one column to the left.
7//!
8//! # The ceiling is stated rather than streamed past
9//!
10//! A sheet part is XML and inflates roughly ten to one, so [`MAX_PART`] is a ceiling on what comes
11//! *out* rather than on what is on disk. Above it the file is refused, by name and with the number,
12//! and it is not read a piece at a time.
13//!
14//! That is a real limit and it is written down rather than hidden: streaming would need a second XML
15//! reader, of the kind that hands over events instead of a tree, and the tree is what the editing
16//! path needs for its spans. One reader that refuses honestly above a stated ceiling beats two
17//! readers that disagree about what a document says.
18//!
19//! [Written with AI entirely](https://need2know.ai/entirely-ai/code)\
20//! Anthropic Claude
21
22use crate::office::opc::{
23 REL_DOC,
24 REL_SHEET,
25 REL_STRINGS,
26 REL_STYLES,
27};
28use crate::office::sheet::{
29 Book,
30 Cell,
31 MAX_COL,
32 MAX_ROW,
33 Ref,
34 Sheet,
35 Value,
36};
37use crate::zip::Zip;
38
39use oxedyne_fe2o3_core::prelude::*;
40use oxedyne_fe2o3_text::xml::{
41 Elem,
42 Xml,
43};
44
45use std::collections::BTreeMap;
46
47// The most a single part is inflated to. A sheet of a hundred thousand rows is about 30 MB of XML,
48// so this admits every spreadsheet a person keeps and refuses the ones built to exhaust a reader.
49pub const MAX_PART: u64 = 96 * 1024 * 1024;
50
51/// The leading bytes of an OLE compound file: an encrypted workbook, or a `.xls` from before 2007.
52const OLE_MAGIC: [u8; 8] = [0xD0, 0xCF, 0x11, 0xE0, 0xA1, 0xB1, 0x1A, 0xE1];
53
54/// A workbook read for reading, and what came with it.
55#[derive(Clone, Debug, Default)]
56pub struct Reading {
57 pub book: Book,
58 pub macros: bool, // said, never run
59 // Worth saying on screen, because it is the number that tells a reader whether what they are
60 // looking at is data or the result of a calculation they cannot see.
61 pub formulas: usize,
62 // Sheets the workbook names and whose part is missing or unreadable, named rather than dropped:
63 // a workbook that quietly came back with four of its five sheets is worse than one that says
64 // which is absent.
65 pub missing: Vec<String>,
66}
67
68pub fn read(bytes: &[u8]) -> Outcome<Reading> {
69 if bytes.len() >= OLE_MAGIC.len() && bytes[..OLE_MAGIC.len()] == OLE_MAGIC {
70 return Err(err!(
71 "This is an OLE compound file, not a `.xlsx`. Either it is encrypted, or it is a `.xls` \
72 from before 2007 -- a different format entirely, which this does not read. Nothing is \
73 guessed at."; Invalid, Input, Unimplemented));
74 }
75 let zip = res!(Zip::read(bytes.to_vec()));
76 let mut out = Reading::default();
77 out.macros = zip.names().iter().any(|n| n.ends_with("vbaProject.bin"));
78
79 let root_rels = res!(rels_of(&zip, ""));
80 let main = res!(root_rels.values()
81 .find(|(kind, _)| kind == REL_DOC)
82 .map(|(_, t)| t.clone())
83 .ok_or_else(|| err!(
84 "The package names no workbook part, so this is not a spreadsheet. It holds: {}.",
85 zip.names().join(", "); Invalid, Input, Missing)));
86 let dir = dir_of(&main);
87 let wb = res!(Xml::parse(&res!(part_text(&zip, &main))));
88 let rels = res!(rels_of(&zip, &main));
89
90 // A date is a number counted from one of two epochs, and which one is a property of the WORKBOOK.
91 // A reader that assumed 1900 reads every date in a file written on a Mac before 2011 four years
92 // and a day early.
93 let epoch_1904 = res!(wb.root()).child("workbookPr")
94 .and_then(|e| e.attr("date1904"))
95 .map(|v| v == "1" || v == "true")
96 .unwrap_or(false);
97
98 let strings = res!(strings_of(&zip, &dir, &rels));
99 let dates = res!(date_styles(&zip, &dir, &rels));
100
101 let sheets = match res!(wb.root()).child("sheets") {
102 Some(s) => s.children("sheet"),
103 None => Vec::new(),
104 };
105 for s in sheets {
106 let name = s.attr("name").unwrap_or("Sheet").to_string();
107 let target = s.attr("r:id")
108 .and_then(|id| rels.get(id))
109 .filter(|(kind, _)| kind == REL_SHEET)
110 .map(|(_, t)| t.clone());
111 let target = match target {
112 Some(t) if zip.has(&t) => t,
113 _ => {
114 out.missing.push(name);
115 continue;
116 }
117 };
118 let part = match part_text(&zip, &target) {
119 Ok(p) => p,
120 Err(_) => {
121 out.missing.push(name);
122 continue;
123 }
124 };
125 let xml = match Xml::parse(&part) {
126 Ok(x) => x,
127 Err(_) => {
128 out.missing.push(name);
129 continue;
130 }
131 };
132 let mut sheet = Sheet::new(name);
133 res!(cells(&xml, &mut sheet, &strings, &dates, epoch_1904, &mut out.formulas));
134 out.book.sheets.push(sheet);
135 }
136 Ok(out)
137}
138
139/// Reads the cells of one sheet, placing each by the address it carries.
140fn cells(
141 xml: &Xml,
142 sheet: &mut Sheet,
143 strings: &[String],
144 dates: &[bool],
145 epoch_1904: bool,
146 formulas: &mut usize,
147)
148 -> Outcome<()>
149{
150 let data = match res!(xml.root()).child("sheetData") {
151 Some(d) => d,
152 None => return Ok(()),
153 };
154 // The address is read from the cell and the row is read from the row, and where either is absent
155 // the position is the fallback. Both exist in the wild: a generator that writes dense rows often
156 // leaves the attributes off entirely.
157 let mut at_row: u32 = 0;
158 for row in data.children("row") {
159 let r = row.attr("r")
160 .and_then(|v| v.parse::<u32>().ok())
161 .map(|v| v.saturating_sub(1))
162 .unwrap_or(at_row);
163 if r >= MAX_ROW {
164 continue;
165 }
166 let mut at_col: u32 = 0;
167 for c in row.children("c") {
168 let pos = match c.attr("r") {
169 Some(a) => match Ref::parse(a) {
170 Ok(p) => p,
171 // An address that does not parse is placed where it stood, which keeps the rest
172 // of the row aligned rather than losing it.
173 Err(_) => Ref { col: at_col, row: r },
174 },
175 None => Ref { col: at_col, row: r },
176 };
177 at_col = pos.col.saturating_add(1);
178 if pos.col >= MAX_COL {
179 continue;
180 }
181 let cell = cell_of(xml, c, strings, dates, epoch_1904);
182 if cell.formula.is_some() {
183 *formulas += 1;
184 }
185 if cell.is_empty() {
186 continue;
187 }
188 // Grown to fit rather than allocated to the sheet's declared size: a sheet claiming a
189 // million rows and holding four should cost four.
190 while sheet.rows.len() <= pos.row as usize {
191 sheet.rows.push(Vec::new());
192 }
193 let line = &mut sheet.rows[pos.row as usize];
194 while line.len() <= pos.col as usize {
195 line.push(Cell::empty());
196 }
197 line[pos.col as usize] = cell;
198 }
199 at_row = r.saturating_add(1);
200 }
201 Ok(())
202}
203
204fn cell_of(
205 xml: &Xml,
206 c: &Elem,
207 strings: &[String],
208 dates: &[bool],
209 epoch_1904: bool,
210)
211 -> Cell
212{
213 // A formula's text is read and NEVER evaluated. What is displayed is the `<v>` beside it, which
214 // is what the last calculation left and what the person who wrote the file saw.
215 let formula = c.child("f").map(|f| xml.text_of(f)).filter(|f| !f.is_empty());
216 let raw = c.child("v").map(|v| xml.text_of(v));
217 let kind = c.attr("t").unwrap_or("n");
218 let value = match kind {
219 // The trap: this is an INDEX into the shared string table, not a number.
220 "s" => match raw.as_ref().and_then(|v| v.parse::<usize>().ok()).and_then(|i| strings.get(i)) {
221 Some(s) => Value::Text(s.clone()),
222 None => Value::Empty,
223 },
224 // A formula whose result is text carries it directly.
225 "str" => match raw {
226 Some(v) => Value::Text(v),
227 None => Value::Empty,
228 },
229 "inlineStr" => {
230 let text: String = match c.child("is") {
231 Some(is) => xml.text_of(is),
232 None => String::new(),
233 };
234 match text.is_empty() {
235 true => Value::Empty,
236 false => Value::Text(text),
237 }
238 }
239 "b" => Value::Bool(raw.as_deref() == Some("1")),
240 "e" => Value::Error(raw.unwrap_or_default()),
241 // A date written as an ISO string rather than as a serial. Rare, and legal.
242 "d" => match raw {
243 Some(v) => Value::Date(v),
244 None => Value::Empty,
245 },
246 _ => match raw.as_ref().and_then(|v| v.trim().parse::<f64>().ok()) {
247 None => Value::Empty,
248 Some(n) => {
249 // The other trap: a number is a date only because its style says so.
250 let style = c.attr("s").and_then(|v| v.parse::<usize>().ok()).unwrap_or(0);
251 match dates.get(style).copied().unwrap_or(false) {
252 true => Value::Date(date_of(n, epoch_1904)),
253 false => Value::Number(n),
254 }
255 }
256 },
257 };
258 Cell { value, formula }
259}
260
261/// A serial number as the date it stands for, in ISO 8601.
262///
263/// The epoch is 30 December 1899 and not 1 January 1900, which looks like an off-by-two and is not:
264/// Lotus 1-2-3 treated 1900 as a leap year, Excel copied the bug deliberately for compatibility, and
265/// every spreadsheet since has kept it. Serial 60 is 29 February 1900, a day that did not happen; the
266/// two errors cancel from 1 March 1900 onward, which is every date anybody stores.
267pub fn date_of(serial: f64, epoch_1904: bool) -> String {
268 let base = match epoch_1904 {
269 true => super::write::days_from_civil(1904, 1, 1),
270 false => super::write::days_from_civil(1899, 12, 30),
271 };
272 let days = serial.floor();
273 let frac = serial - days;
274 let (y, m, d) = civil_from_days(base + days as i64);
275 // A whole number of days is a date; anything else carries a time, and a serial under one is a
276 // time of day with no date at all.
277 let secs = (frac * 86_400.0).round() as i64;
278 if secs == 0 {
279 return fmt!("{:04}-{:02}-{:02}", y, m, d);
280 }
281 let (h, mi, s) = (secs / 3600, (secs % 3600) / 60, secs % 60);
282 if serial < 1.0 {
283 return fmt!("{:02}:{:02}:{:02}", h, mi, s);
284 }
285 fmt!("{:04}-{:02}-{:02}T{:02}:{:02}:{:02}", y, m, d, h, mi, s)
286}
287
288/// The civil date a day count from 1970-01-01 names, by Howard Hinnant's algorithm.
289fn civil_from_days(z: i64) -> (i64, i64, i64) {
290 let z = z + 719_468;
291 let era = if z >= 0 { z } else { z - 146_096 } / 146_097;
292 let doe = z - era * 146_097;
293 let yoe = (doe - doe / 1460 + doe / 36_524 - doe / 146_096) / 365;
294 let y = yoe + era * 400;
295 let doy = doe - (365 * yoe + yoe / 4 - yoe / 100);
296 let mp = (5 * doy + 2) / 153;
297 let d = doy - (153 * mp + 2) / 5 + 1;
298 let m = mp + if mp < 10 { 3 } else { -9 };
299 (y + i64::from(m <= 2), m, d)
300}
301
302/// Which style indices mean "this number is a date".
303///
304/// Two hops, and both matter. A cell names a `cellXfs` entry by index; that entry names a number
305/// format by id; and the format is a date either because its id is one of the built-in date formats
306/// or because its own code says so. A reader that checked only the built-ins misses every workbook
307/// whose author set their own date format, which is most of them.
308fn date_styles(
309 zip: &Zip,
310 dir: &str,
311 rels: &BTreeMap<String, (String, String)>,
312)
313 -> Outcome<Vec<bool>>
314{
315 let part = match part_of(rels, REL_STYLES, dir, "styles.xml", zip) {
316 Some(p) => p,
317 None => return Ok(Vec::new()),
318 };
319 let xml = res!(Xml::parse(&res!(part_text(zip, &part))));
320 let root = res!(xml.root());
321 // The formats the workbook defined for itself.
322 let mut custom: BTreeMap<u32, bool> = BTreeMap::new();
323 if let Some(fmts) = root.child("numFmts") {
324 for f in fmts.children("numFmt") {
325 if let (Some(id), Some(code)) = (f.attr("numFmtId"), f.attr("formatCode")) {
326 if let Ok(id) = id.parse::<u32>() {
327 custom.insert(id, is_date_code(code));
328 }
329 }
330 }
331 }
332 let mut out = Vec::new();
333 if let Some(xfs) = root.child("cellXfs") {
334 for xf in xfs.children("xf") {
335 let id = xf.attr("numFmtId").and_then(|v| v.parse::<u32>().ok()).unwrap_or(0);
336 out.push(match custom.get(&id) {
337 Some(known) => *known,
338 None => builtin_is_date(id),
339 });
340 }
341 }
342 Ok(out)
343}
344
345/// Whether a built-in number format id is a date or a time.
346///
347/// The built-ins are fixed by the specification: 14 to 22 are the dates and times, and 45 to 47 are
348/// the elapsed-time formats.
349fn builtin_is_date(id: u32) -> bool {
350 matches!(id, 14..=22 | 45..=47)
351}
352
353/// Whether a format code describes a date or a time.
354///
355/// The letters are read outside quoted runs and outside the bracketed sections, because a format may
356/// legitimately say `"day "0` or `[Red]`, and reading the `d` in a quoted word as a day is how a
357/// column of money becomes a column of dates.
358fn is_date_code(code: &str) -> bool {
359 let mut chars = code.chars().peekable();
360 let mut quoted = false;
361 let mut bracket = false;
362 while let Some(c) = chars.next() {
363 match c {
364 '"' => quoted = !quoted,
365 '[' => bracket = true,
366 ']' => bracket = false,
367 // A backslash escapes the character after it, which is then literal.
368 '\\' => { chars.next(); }
369 _ if quoted || bracket => {}
370 'y' | 'd' | 'h' | 's' => return true,
371 // `m` is minutes or months and is a date either way. It is also the `m` in `mm` after an
372 // `h`, which is still a time.
373 'm' => return true,
374 _ => {}
375 }
376 }
377 false
378}
379
380/// The shared string table, in index order.
381fn strings_of(
382 zip: &Zip,
383 dir: &str,
384 rels: &BTreeMap<String, (String, String)>,
385)
386 -> Outcome<Vec<String>>
387{
388 let part = match part_of(rels, REL_STRINGS, dir, "sharedStrings.xml", zip) {
389 Some(p) => p,
390 None => return Ok(Vec::new()),
391 };
392 let xml = res!(Xml::parse(&res!(part_text(zip, &part))));
393 // An entry may be one run or many -- a string with a bold word in the middle of it is three runs
394 // -- and its text is all of them joined. Taking the first would silently truncate every styled
395 // string in the workbook.
396 Ok(res!(xml.root()).children("si").iter().map(|si| xml.text_of(si)).collect())
397}
398
399fn part_text(zip: &Zip, name: &str) -> Outcome<String> {
400 let bytes = res!(zip.content_capped(name, MAX_PART));
401 Ok(res!(String::from_utf8(bytes), Decode, String))
402}
403
404/// The directory a part sits in, with its trailing slash.
405fn dir_of(part: &str) -> String {
406 match part.rfind('/') {
407 Some(k) => part[..k + 1].to_string(),
408 None => String::new(),
409 }
410}
411
412/// Where a relationship target actually is within the package.
413fn resolve(dir: &str, target: &str) -> String {
414 match target.starts_with('/') {
415 true => target[1..].to_string(),
416 false => fmt!("{}{}", dir, target),
417 }
418}
419
420/// The relationships a part owns, by id.
421fn rels_of(zip: &Zip, part: &str) -> Outcome<BTreeMap<String, (String, String)>> {
422 let dir = dir_of(part);
423 let name = &part[dir.len()..];
424 let path = fmt!("{}_rels/{}.rels", dir, name);
425 let mut out = BTreeMap::new();
426 if !zip.has(&path) {
427 return Ok(out);
428 }
429 let xml = res!(Xml::parse(&res!(part_text(zip, &path))));
430 for rel in res!(xml.root()).children("Relationship") {
431 let id = match rel.attr("Id") {
432 Some(id) => id.to_string(),
433 None => continue,
434 };
435 let kind = rel.attr("Type").unwrap_or("").to_string();
436 let target = rel.attr("Target").unwrap_or("").to_string();
437 let target = match rel.attr("TargetMode") {
438 Some("External") => target,
439 _ => resolve(&dir, &target),
440 };
441 out.insert(id, (kind, target));
442 }
443 Ok(out)
444}
445
446/// Where a supporting part is: what the relationships say, or the conventional name where they say
447/// nothing.
448fn part_of(
449 rels: &BTreeMap<String, (String, String)>,
450 kind: &str,
451 dir: &str,
452 usual: &str,
453 zip: &Zip,
454)
455 -> Option<String>
456{
457 if let Some((_, target)) = rels.values().find(|(k, _)| k == kind) {
458 if zip.has(target) {
459 return Some(target.clone());
460 }
461 }
462 let guess = fmt!("{}{}", dir, usual);
463 match zip.has(&guess) {
464 true => Some(guess),
465 false => None,
466 }
467}