Oregami
Repositories/oxedyne/fe2o3

oxedyne/fe2o3/fe2o3_file/src/office/sheet.rs

17.5 KiB, 77 runs

created by r1870400018:22742, 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//! A neutral spreadsheet: what a grid of cells *is*, free of the format it was stored in.
2//!
3//! The counterpart to [`oxedyne_fe2o3_text::doc`] for the other kind of document. That tree carries
4//! prose and cannot carry a number that is also a formula that is also a date; this carries exactly
5//! that and no prose beyond a cell's text.
6//!
7//! # The stored value is the value
8//!
9//! A cell holding a formula holds **two** things: the formula, and the value the last calculation
10//! left beside it. [`Cell::value`] is that stored value and **nothing here ever recalculates**.
11//!
12//! That is not laziness about writing an expression evaluator. It is the correct answer twice over.
13//! The stored value is the number the person who wrote the file *saw*, which is what a reader is
14//! asking about. And recalculating would break byte-identity the moment a volatile function is
15//! present -- `NOW`, `TODAY`, `RAND`, `RANDBETWEEN` all change on every open -- so a file opened and
16//! saved with no edit would differ from itself, and the check that exists to catch a damaging edit
17//! would fire on a healthy file instead. A gate that cries wolf is a gate somebody turns off.
18//!
19//! # Addressing
20//!
21//! [`Ref`] and [`Range`] are the `A1` and `A1:D20` a person types, parsed and printed. They are
22//! zero-based inside and one-based on the page, because a spreadsheet counts rows from one and
23//! nothing is served by pretending otherwise at the boundary.
24//!
25//! [Written with AI entirely](https://need2know.ai/entirely-ai/code)\
26//! Anthropic Claude
27
28use oxedyne_fe2o3_core::prelude::*;
29use oxedyne_fe2o3_text::doc::{
30 Block,
31 Doc,
32};
33
34//// The largest a sheet may be, which is Excel's own limit.
35pub const MAX_COL: u32 = 16_384; // column XFD
36pub const MAX_ROW: u32 = 1_048_576;
37
38/// What one cell holds.
39///
40/// A closed set, on purpose. Everything a spreadsheet stores is one of these; what varies between
41/// the formats is the spelling.
42#[derive(Clone, Debug, Default, PartialEq)]
43pub enum Value {
44 #[default]
45 Empty, // never filled in, or emptied
46 Text(String),
47 Number(f64),
48 // A date or a time, as the text it was rendered to. Held as text rather than as a number with a
49 // format beside it, because the number alone is meaningless -- `45678` is a date only if
50 // something says so -- and the format alone cannot be applied without a calendar. The conversion
51 // happens once, where the format is known.
52 Date(String),
53 Bool(bool),
54 Error(String), // what a calculation left where it could not produce a value: `#DIV/0!`, `#N/A`
55}
56
57impl Value {
58
59 /// The value as a person would see it, with nothing added.
60 ///
61 /// A number prints without a trailing `.0`, because a spreadsheet showing `12.0` where the file
62 /// says `12` has changed what the file says.
63 pub fn show(&self) -> String {
64 match self {
65 Self::Empty => String::new(),
66 Self::Text(t) => t.clone(),
67 Self::Date(d) => d.clone(),
68 Self::Error(e) => e.clone(),
69 Self::Bool(b) => match b {
70 true => "TRUE".to_string(),
71 false => "FALSE".to_string(),
72 },
73 Self::Number(n) => show_number(*n),
74 }
75 }
76
77 pub fn is_empty(&self) -> bool {
78 matches!(self, Self::Empty)
79 }
80}
81
82/// A number as a spreadsheet shows it.
83///
84/// **Fifteen significant figures, which is what a spreadsheet keeps.** Not the shortest text that
85/// round-trips the `f64`, which is what Rust prints by default and which is a different thing: `0.1 +
86/// 0.2` round-trips as `0.30000000000000004`, and the file says `0.3`. Showing the binary
87/// representation's noise would be showing a number nobody entered and no reader displays.
88///
89/// A whole number prints without a trailing `.0`, because a cell showing `12.0` where the file says
90/// `12` has changed what the file says.
91fn show_number(n: f64) -> String {
92 if !n.is_finite() {
93 // A spreadsheet has no infinity and no NaN; one that arrived here came from a damaged file.
94 return "#NUM!".to_string();
95 }
96 if n == n.trunc() && n.abs() < 1e15 {
97 // `+ 0.0` so negative zero prints as `0`. A cell holding -0.0 is a cell holding nothing a
98 // person would call negative.
99 return fmt!("{}", (n + 0.0) as i64);
100 }
101 // Fourteen decimals in the mantissa is fifteen significant figures. Rounding through the text and
102 // back lands on the nearest `f64` to the rounded value, which then prints as itself.
103 match fmt!("{:.14e}", n).parse::<f64>() {
104 Ok(r) => fmt!("{}", r),
105 Err(_) => fmt!("{}", n),
106 }
107}
108
109#[derive(Clone, Debug, Default, PartialEq)]
110pub struct Cell {
111 // For a formula cell this is what the last calculation left, and is never recomputed. See the
112 // module's own note.
113 pub value: Value,
114 pub formula: Option<String>, // without its leading `=`
115}
116
117impl Cell {
118
119 pub fn empty() -> Self {
120 Self::default()
121 }
122
123 pub fn text(s: impl Into<String>) -> Self {
124 Self { value: Value::Text(s.into()), formula: None }
125 }
126
127 pub fn number(n: f64) -> Self {
128 Self { value: Value::Number(n), formula: None }
129 }
130
131 pub fn bool(b: bool) -> Self {
132 Self { value: Value::Bool(b), formula: None }
133 }
134
135 /// A cell holding a formula and the value that was last computed for it.
136 pub fn formula(text: impl Into<String>, value: Value) -> Self {
137 Self { value, formula: Some(text.into()) }
138 }
139
140 /// Whether the cell holds nothing at all: no value and no formula.
141 pub fn is_empty(&self) -> bool {
142 self.value.is_empty() && self.formula.is_none()
143 }
144}
145
146#[derive(Clone, Debug, Default, PartialEq)]
147pub struct Sheet {
148 pub name: String, // the name on the tab
149 // From row 1, each a run of cells from column A. Short rows are short; a reader fills nothing in,
150 // because a sheet of a million empty cells is a sheet nobody can hold.
151 pub rows: Vec<Vec<Cell>>,
152}
153
154impl Sheet {
155
156 pub fn new(name: impl Into<String>) -> Self {
157 Self { name: name.into(), rows: Vec::new() }
158 }
159
160 /// The cell at a reference, or an empty one where the sheet does not reach that far.
161 pub fn at(&self, at: &Ref) -> Cell {
162 self.rows.get(at.row as usize)
163 .and_then(|r| r.get(at.col as usize))
164 .cloned()
165 .unwrap_or_default()
166 }
167
168 /// How many rows the sheet holds, and how many columns its widest row does.
169 pub fn size(&self) -> (usize, usize) {
170 (self.rows.len(), self.rows.iter().map(|r| r.len()).max().unwrap_or(0))
171 }
172
173 pub fn extent(&self) -> Option<Range> {
174 let (rows, cols) = self.size();
175 if rows == 0 || cols == 0 {
176 return None;
177 }
178 Some(Range {
179 from: Ref { col: 0, row: 0 },
180 to: Ref { col: cols as u32 - 1, row: rows as u32 - 1 },
181 })
182 }
183
184 /// The cells of a rectangle, row by row. The rectangle asked for is the
185 /// rectangle returned: a position the sheet does not hold comes back as an
186 /// empty cell rather than being clipped away, so a caller drawing a grid gets
187 /// the shape it asked for.
188 pub fn window(&self, range: &Range) -> Vec<Vec<Cell>> {
189 let mut out = Vec::new();
190 for row in range.from.row..=range.to.row {
191 let mut line = Vec::new();
192 for col in range.from.col..=range.to.col {
193 line.push(self.at(&Ref { col, row }));
194 }
195 out.push(line);
196 }
197 out
198 }
199}
200
201/// A workbook: the sheets it holds, in tab order.
202#[derive(Clone, Debug, Default, PartialEq)]
203pub struct Book {
204 pub sheets: Vec<Sheet>,
205}
206
207impl Book {
208
209 pub fn new() -> Self {
210 Self::default()
211 }
212
213 /// The sheet of that name, matched exactly and then without regard to case.
214 ///
215 /// A person typing a sheet name types what is on the tab, and a tab reading `Sales` is asked for
216 /// as `sales` about half the time. Exact first, so two sheets differing only in case still each
217 /// have a name that reaches them.
218 pub fn sheet(&self, name: &str) -> Option<&Sheet> {
219 self.sheets.iter().find(|s| s.name == name)
220 .or_else(|| self.sheets.iter().find(|s| s.name.eq_ignore_ascii_case(name)))
221 }
222
223 pub fn names(&self) -> Vec<&str> {
224 self.sheets.iter().map(|s| s.name.as_str()).collect()
225 }
226}
227
228/// One cell's address: a column and a row, both counted from zero.
229#[derive(Clone, Copy, Debug, Default, PartialEq, Eq, PartialOrd, Ord)]
230pub struct Ref {
231 pub col: u32, // `A` being zero
232 pub row: u32, // row 1 being zero
233}
234
235impl Ref {
236
237 /// The address as a person writes it: `A1`, `BC42`.
238 pub fn name(&self) -> String {
239 fmt!("{}{}", col_name(self.col), self.row + 1)
240 }
241
242 /// The address a string names.
243 ///
244 /// A `$` is accepted and ignored: `$B$4` is the same cell as `B4`, and refusing the form a person
245 /// copied out of a formula bar would be refusing the commonest way of typing one.
246 pub fn parse(s: &str) -> Outcome<Self> {
247 let s = s.trim();
248 let mut col: u32 = 0;
249 let mut seen = false;
250 let mut rest = s;
251 while let Some(c) = rest.chars().next() {
252 match c {
253 '$' => {},
254 c if c.is_ascii_alphabetic() => {
255 col = col * 26 + (c.to_ascii_uppercase() as u32 - 'A' as u32 + 1);
256 seen = true;
257 if col > MAX_COL {
258 return Err(err!(
259 "'{}' names a column past {}, the last a sheet has.", s, col_name(MAX_COL - 1);
260 Invalid, Input, Range));
261 }
262 }
263 _ => break,
264 }
265 rest = &rest[c.len_utf8()..];
266 }
267 let digits: String = rest.chars().filter(|c| *c != '$').collect();
268 if !seen || digits.is_empty() {
269 return Err(err!(
270 "'{}' is not a cell reference. One looks like `A1` or `BC42`.", s; Invalid, Input));
271 }
272 let row: u32 = res!(digits.parse().map_err(|_| err!(
273 "'{}' is not a cell reference: '{}' is not a row number.", s, digits; Invalid, Input)));
274 if row == 0 || row > MAX_ROW {
275 return Err(err!(
276 "'{}' names row {}, and a sheet has rows 1 to {}.", s, row, MAX_ROW;
277 Invalid, Input, Range));
278 }
279 Ok(Self { col: col - 1, row: row - 1 })
280 }
281}
282
283#[derive(Clone, Copy, Debug, Default, PartialEq)]
284pub struct Range {
285 pub from: Ref, // the top left
286 pub to: Ref, // the bottom right, inclusive
287}
288
289impl Range {
290
291 /// The range as a person writes it: `A1:D20`.
292 pub fn name(&self) -> String {
293 fmt!("{}:{}", self.from.name(), self.to.name())
294 }
295
296 pub fn cells(&self) -> u64 {
297 let w = (self.to.col as u64 + 1).saturating_sub(self.from.col as u64);
298 let h = (self.to.row as u64 + 1).saturating_sub(self.from.row as u64);
299 w * h
300 }
301
302 /// The range a string names: `A1:D20`, or `A1` for a single cell.
303 ///
304 /// Corners given the wrong way round are put right rather than refused. `D20:A1` is the rectangle
305 /// somebody dragged from the bottom right, and it is not an error anywhere a person would type it.
306 pub fn parse(s: &str) -> Outcome<Self> {
307 let s = s.trim();
308 let (a, b) = match s.split_once(':') {
309 Some((a, b)) => (a, b),
310 None => (s, s),
311 };
312 let a = res!(Ref::parse(a));
313 let b = res!(Ref::parse(b));
314 Ok(Self {
315 from: Ref { col: a.col.min(b.col), row: a.row.min(b.row) },
316 to: Ref { col: a.col.max(b.col), row: a.row.max(b.row) },
317 })
318 }
319}
320
321/// The letters a column index wears: 0 is `A`, 26 is `AA`.
322pub fn col_name(col: u32) -> String {
323 let mut out = Vec::new();
324 let mut n = col as i64;
325 loop {
326 out.push(b'A' + (n % 26) as u8);
327 n = n / 26 - 1;
328 if n < 0 {
329 break;
330 }
331 }
332 out.reverse();
333 String::from_utf8_lossy(&out).into_owned()
334}
335
336/// The value a typed string stands for, the way a spreadsheet decides it.
337///
338/// This is the rule a person meets when they type into a cell, and it has one subtlety worth stating:
339/// a string is a number only where it is EXACTLY how that number prints. `007` and `1,000` and `+3`
340/// and ` 4 ` therefore stay text, which is what a person typing a part number or an account code
341/// wants, and `3.5` becomes 3.5, which is what a person typing a price wants. A rule that parsed
342/// anything parseable would silently turn `007` into `7`.
343pub fn typed(s: &str) -> Value {
344 if s.is_empty() {
345 return Value::Empty;
346 }
347 if s.eq_ignore_ascii_case("true") {
348 return Value::Bool(true);
349 }
350 if s.eq_ignore_ascii_case("false") {
351 return Value::Bool(false);
352 }
353 match s.parse::<f64>() {
354 Ok(n) if n.is_finite() && stored(n) == s => Value::Number(n),
355 _ => Value::Text(s.to_string()),
356 }
357}
358
359/// A number as a file stores it, which is not how a person reads it.
360///
361/// The shortest text that reads back as the same `f64`, so nothing the caller handed over is lost.
362/// [`Value::show`] is what a person sees, and it rounds; this does not.
363pub fn stored(n: f64) -> String {
364 if !n.is_finite() {
365 return "0".to_string();
366 }
367 if n == n.trunc() && n.abs() < 1e15 {
368 return fmt!("{}", n as i64);
369 }
370 fmt!("{}", n)
371}
372
373impl Book {
374
375 /// The workbook a document's tables make: one sheet per table, named by the heading above it.
376 ///
377 /// The counterpart of [`crate::office::deck::Deck::from_doc`] and the same bargain. A model writes
378 /// Markdown well and a file format badly, so the way to have it produce a spreadsheet is to have it
379 /// produce a table; the conversion from there is code that cannot get the format wrong.
380 ///
381 /// A cell's text becomes a number where it is exactly how that number prints -- see [`typed`], which
382 /// is what stops a column of part numbers being renumbered. A cell beginning `=` becomes a formula
383 /// with no value beside it, because nothing here calculates and the reader will.
384 ///
385 /// A document with no table in it gives a workbook with one empty sheet, not an error: an empty
386 /// spreadsheet is a spreadsheet.
387 pub fn from_doc(doc: &Doc) -> Self {
388 let mut out = Self::new();
389 let mut title: Option<String> = None;
390 for block in &doc.blocks {
391 match block {
392 Block::Heading { content, .. } => title = Some(oxedyne_fe2o3_text::doc::text_of(content)),
393 Block::Table { head, rows, .. } => {
394 // The heading WHOLE. Cutting it to Excel's 31 here would put one format's rule
395 // into the neutral model, and would throw away the very characters that tell two
396 // long headings apart before the writer that has to tell them apart sees them.
397 let name = match title.take() {
398 Some(t) if !t.trim().is_empty() => t.trim().to_string(),
399 _ => fmt!("Sheet{}", out.sheets.len() + 1),
400 };
401 let mut sheet = Sheet::new(name);
402 // The header row is a row of the sheet and not a property of it. A spreadsheet has
403 // no header row; it has a first row that people read as one.
404 for row in head.iter().chain(rows.iter()) {
405 sheet.rows.push(row.0.iter().map(|c| from_text(&c.text_of())).collect());
406 }
407 out.sheets.push(sheet);
408 }
409 _ => {}
410 }
411 }
412 if out.sheets.is_empty() {
413 out.sheets.push(Sheet::new("Sheet1"));
414 }
415 out
416 }
417}
418
419/// The cell a run of text stands for, a leading `=` making it a formula.
420fn from_text(s: &str) -> Cell {
421 let s = s.trim();
422 match s.strip_prefix('=') {
423 Some(f) if !f.is_empty() => Cell { value: Value::Empty, formula: Some(f.to_string()) },
424 _ => Cell { value: typed(s), formula: None },
425 }
426}
427
428/// The longest name a tab may wear, which is Excel's limit and the one both writers keep.
429pub const MAX_TAB: usize = 31;
430
431/// Every sheet's name as a tab can actually wear it: legal, inside the limit, and DISTINCT.
432///
433/// Three separate things make Excel refuse a workbook, and in every one of the three it refuses the
434/// FILE rather than the name: a `: \ / ? * [ ]` in a tab name, a name over [`MAX_TAB`] characters, and
435/// two tabs that share one name. The third is the one that hid, because the first two are properties
436/// of a name on its own and the third is a property of the SET -- and nothing looked at the set. Two
437/// headings as ordinary as `Q1/Q2` and `Q1:Q2` both reduce to `Q1 Q2`, and any two headings that agree
438/// for 31 characters collide after truncation.
439///
440/// **LibreOffice and openpyxl each silently rename the second sheet**, so the file opens and the user
441/// simply does not get the tab they asked for -- and an oracle that reads the file back is repairing
442/// the defect rather than reporting it. The names are corrected here instead, where the intent is
443/// still known.
444///
445/// A collision is disambiguated ` (2)`, which is what Excel itself does when you copy a sheet: a
446/// person who sees `Q1 Q2 (2)` can tell what happened to it, and one who sees `Q1 Q2_1` cannot.
447///
448/// `.ods` takes these same names even though OpenDocument's own limit is looser, so that a sheet is
449/// addressable by one name whichever format it landed in -- an edit names its sheet, and a caller
450/// should not have to know which of the two it is holding.
451pub fn tab_names(book: &Book) -> Vec<String> {
452 let mut out: Vec<String> = Vec::with_capacity(book.sheets.len());
453 for (i, s) in book.sheets.iter().enumerate() {
454 let base = tab_name(&s.name, i);
455 out.push(distinct(&base, &out));
456 }
457 out
458}
459
460/// One name, legal and short enough, but taking no account of the others.
461fn tab_name(s: &str, i: usize) -> String {
462 let cleaned: String = s.trim()
463 .chars()
464 .map(|c| match c {
465 ':' | '\\' | '/' | '?' | '*' | '[' | ']' => ' ',
466 c => c,
467 })
468 .take(MAX_TAB)
469 .collect();
470 let cleaned = cleaned.trim();
471 match cleaned.is_empty() {
472 true => fmt!("Sheet{}", i + 1),
473 false => cleaned.to_string(),
474 }
475}
476
477/// The same name with a ` (n)` on it, where a name is already spoken for.
478///
479/// The head is cut back to leave room for the tag, so the answer is still inside [`MAX_TAB`] -- a
480/// disambiguator that pushed the name over the limit would trade one refusal for another. The
481/// comparison ignores case because Excel's does: `Sales` and `sales` are one tab name to it.
482fn distinct(base: &str, taken: &[String]) -> String {
483 let clashes = |c: &str| taken.iter().any(|t| t.eq_ignore_ascii_case(c));
484 if !clashes(base) {
485 return base.to_string();
486 }
487 // At most one candidate per name already issued can be taken, so a free one is always found
488 // inside this many tries.
489 let mut last = base.to_string();
490 for n in 2..=(taken.len() + 2) {
491 let tag = fmt!(" ({})", n);
492 let keep = MAX_TAB.saturating_sub(tag.chars().count());
493 let head: String = base.chars().take(keep).collect();
494 let head = head.trim_end();
495 let head = match head.is_empty() {
496 true => "Sheet",
497 false => head,
498 };
499 last = fmt!("{}{}", head, tag);
500 if !clashes(&last) {
501 return last;
502 }
503 }
504 last
505}