frazaro_core/sheet/
mod.rs

1//! The sheet model (PORT.7): what a workbook holds before it is written.
2//! Sheets of cells, each cell a text, a number, a truth value or a formula's
3//! text; column widths, a hidden column, a style per cell or per column from
4//! a small style table; and nothing that computes. docs/HORIZON.md section
5//! 12: the core has no calc engine, so a formula is text the host evaluates
6//! when it opens the file, which `fullCalcOnLoad` (ooxml.rs) makes it do.
7//!
8//! New ground, said plainly: the reference has no model of a sheet; it
9//! writes through Excel's object model. What is modelled is the little the
10//! room needs (`BuildWorkspace`, VLA_IDE.bas) and what slice 7b's static
11//! subset writes, and the model grows only as a slice needs it.
12
13pub mod inflate;
14pub mod merge;
15pub mod ooxml;
16pub mod xlfn;
17pub mod xml;
18pub mod zip;
19
20use std::collections::BTreeMap;
21
22use crate::intrinsics::fold;
23
24/// A colour, as `Interior.Color = RGB(r, g, b)` names it.
25#[derive(Clone, Copy, Debug, PartialEq, Eq)]
26pub struct Rgb(pub u8, pub u8, pub u8);
27
28impl Rgb {
29    /// The file's spelling: opaque alpha, then the three bytes in hex.
30    pub fn argb_hex(self) -> String {
31        format!("FF{:02X}{:02X}{:02X}", self.0, self.1, self.2)
32    }
33}
34
35/// The number formats a style names: Excel's built-in General and Text
36/// (`NumberFormat = "@"`, so a sentence is never read as a formula).
37#[derive(Clone, Copy, Debug, Default, PartialEq, Eq)]
38pub enum NumFmt {
39    #[default]
40    General,
41    Text,
42}
43
44impl NumFmt {
45    /// The built-in format's id in the styles part.
46    pub fn id(self) -> u32 {
47        match self {
48            NumFmt::General => 0,
49            NumFmt::Text => 49,
50        }
51    }
52}
53
54/// One cell format: a solid fill or none, a number format, wrapped text or
55/// not. Index 0 of a [`Styles`] table is this type's default.
56#[derive(Clone, Debug, Default, PartialEq, Eq)]
57pub struct Style {
58    pub fill: Option<Rgb>,
59    pub num_fmt: NumFmt,
60    pub wrap: bool,
61}
62
63/// The workbook's cell formats, each once; a cell or a column names one by
64/// its index.
65#[derive(Clone, Debug)]
66pub struct Styles {
67    xfs: Vec<Style>,
68}
69
70impl Default for Styles {
71    fn default() -> Self {
72        Self::new()
73    }
74}
75
76impl Styles {
77    /// A table holding only the default format, at index 0.
78    pub fn new() -> Styles {
79        Styles {
80            xfs: vec![Style::default()],
81        }
82    }
83
84    /// The index of a format, added if it is new.
85    pub fn id(&mut self, style: Style) -> u32 {
86        if let Some(i) = self.xfs.iter().position(|s| *s == style) {
87            return i as u32;
88        }
89        self.xfs.push(style);
90        (self.xfs.len() - 1) as u32
91    }
92
93    /// Every format, in index order.
94    pub fn xfs(&self) -> &[Style] {
95        &self.xfs
96    }
97
98    /// The distinct fills, in the order they were first used.
99    pub fn fills(&self) -> Vec<Rgb> {
100        let mut fills: Vec<Rgb> = Vec::new();
101        for s in &self.xfs {
102            if let Some(f) = s.fill {
103                if !fills.contains(&f) {
104                    fills.push(f);
105                }
106            }
107        }
108        fills
109    }
110}
111
112/// What a cell holds. A formula is its text without the leading `=`, as the
113/// file stores it; the host computes it. A formula written into a range of
114/// cells is a shared formula, which is what Excel itself writes when a
115/// formula is filled: the top-left cell is the master and holds the text
116/// and the range, the others point at it by its index, and the host
117/// adjusts the references for each cell as `Range.Formula2` would have.
118#[derive(Clone, Debug, PartialEq)]
119pub enum Content {
120    Text(String),
121    Number(f64),
122    Bool(bool),
123    Formula(String),
124    SharedMaster {
125        text: String,
126        range: String,
127        si: u32,
128    },
129    SharedChild {
130        si: u32,
131    },
132    /// A dynamic-array formula (`cm="1"`, `t="array"` over its own cell):
133    /// what Excel stores for a `Formula2` entry of a formula whose function
134    /// can return an array, and reads back without the `@` it puts on a
135    /// legacy formula of that kind.
136    DynamicFormula(String),
137}
138
139#[derive(Clone, Debug, PartialEq)]
140pub struct Cell {
141    pub content: Content,
142    pub style: u32,
143}
144
145/// A column's settings: `index` is 1-based; `width` is in characters, as
146/// `ColumnWidth` counts them; `style` is the format of its cells that have
147/// none of their own.
148#[derive(Clone, Debug, PartialEq)]
149pub struct Column {
150    pub index: u32,
151    pub width: Option<f64>,
152    pub hidden: bool,
153    pub style: Option<u32>,
154}
155
156/// A rectangle of cells, rows and columns 1-based and inclusive.
157#[derive(Clone, Copy, Debug, PartialEq, Eq)]
158pub struct A1Range {
159    pub top: u32,
160    pub left: u32,
161    pub bottom: u32,
162    pub right: u32,
163}
164
165impl A1Range {
166    pub fn cells(&self) -> u64 {
167        u64::from(self.bottom - self.top + 1) * u64::from(self.right - self.left + 1)
168    }
169
170    /// `B2` for one cell, `B2:C4` for more.
171    pub fn text(&self) -> String {
172        if self.top == self.bottom && self.left == self.right {
173            cell_ref(self.top, self.left)
174        } else {
175            format!(
176                "{}:{}",
177                cell_ref(self.top, self.left),
178                cell_ref(self.bottom, self.right)
179            )
180        }
181    }
182}
183
184/// Excel's last column (XFD) and last row.
185pub const MAX_COLUMN: u32 = 16_384;
186pub const MAX_ROW: u32 = 1_048_576;
187
188fn parse_a1_cell(s: &str) -> Option<(u32, u32)> {
189    let s = s.replace('$', "");
190    let letters: String = s.chars().take_while(|c| c.is_ascii_alphabetic()).collect();
191    let digits = &s[letters.len()..];
192    if letters.is_empty() || letters.len() > 3 || digits.is_empty() {
193        return None;
194    }
195    if !digits.chars().all(|c| c.is_ascii_digit()) {
196        return None;
197    }
198    let mut col: u32 = 0;
199    for c in letters.chars() {
200        col = col * 26 + (c.to_ascii_uppercase() as u32 - 'A' as u32 + 1);
201    }
202    let row: u32 = digits.parse().ok()?;
203    if col == 0 || col > MAX_COLUMN || row == 0 || row > MAX_ROW {
204        return None;
205    }
206    Some((row, col))
207}
208
209/// An A1 reference as a program writes it (`b2`, `A1:B5`, `$C$2`), in any
210/// case, with the corners in any order; `None` for anything else, a whole
211/// column or row included.
212pub fn parse_a1_range(s: &str) -> Option<A1Range> {
213    let s = s.trim();
214    let (a, b) = match s.split_once(':') {
215        Some((a, b)) => (a, b),
216        None => (s, s),
217    };
218    let (r1, c1) = parse_a1_cell(a)?;
219    let (r2, c2) = parse_a1_cell(b)?;
220    Some(A1Range {
221        top: r1.min(r2),
222        left: c1.min(c2),
223        bottom: r1.max(r2),
224        right: c1.max(c2),
225    })
226}
227
228/// One sheet: its cells by (row, column), both 1-based.
229#[derive(Clone, Debug, PartialEq)]
230pub struct Sheet {
231    pub name: String,
232    pub cells: BTreeMap<(u32, u32), Cell>,
233    pub columns: Vec<Column>,
234    pub gridlines: bool,
235    /// The selected cell when the sheet opens, (row, column).
236    pub active_cell: (u32, u32),
237    /// How many shared formulas the sheet holds; the next one's index.
238    pub shared_formulas: u32,
239}
240
241impl Sheet {
242    pub fn new(name: &str) -> Sheet {
243        Sheet {
244            name: name.to_string(),
245            cells: BTreeMap::new(),
246            columns: Vec::new(),
247            gridlines: true,
248            active_cell: (1, 1),
249            shared_formulas: 0,
250        }
251    }
252
253    pub fn set(&mut self, row: u32, col: u32, cell: Cell) {
254        self.cells.insert((row, col), cell);
255    }
256
257    /// A formula's text (without its `=`) into every cell of a range: one
258    /// plain formula for one cell, a shared formula for more.
259    /// A formula whose function can return an array, into every cell of a
260    /// range as Excel stores a `Formula2` entry of it: a dynamic-array
261    /// formula per cell (a dynamic-array formula cannot be shared), its
262    /// references moved by the cell's distance from the top-left one, as
263    /// Excel moves them when a formula is filled (the mover is
264    /// `crate::refers::shift_a1_references` since PORT.8, slice 8b).
265    pub fn set_formula_dynamic(&mut self, range: A1Range, text: &str, style: u32) {
266        for row in range.top..=range.bottom {
267            for col in range.left..=range.right {
268                let moved = crate::refers::shift_a1_references(
269                    text,
270                    i64::from(row) - i64::from(range.top),
271                    i64::from(col) - i64::from(range.left),
272                );
273                self.set(
274                    row,
275                    col,
276                    Cell {
277                        content: Content::DynamicFormula(moved),
278                        style,
279                    },
280                );
281            }
282        }
283    }
284
285    pub fn set_formula(&mut self, range: A1Range, text: &str, style: u32) {
286        if range.cells() == 1 {
287            self.set(
288                range.top,
289                range.left,
290                Cell {
291                    content: Content::Formula(text.to_string()),
292                    style,
293                },
294            );
295            return;
296        }
297        let si = self.shared_formulas;
298        self.shared_formulas += 1;
299        for row in range.top..=range.bottom {
300            for col in range.left..=range.right {
301                let content = if row == range.top && col == range.left {
302                    Content::SharedMaster {
303                        text: text.to_string(),
304                        range: range.text(),
305                        si,
306                    }
307                } else {
308                    Content::SharedChild { si }
309                };
310                self.set(row, col, Cell { content, style });
311            }
312        }
313    }
314
315    /// The used range as ((first row, first column), (last row, last
316    /// column)), or `None` for a sheet with no cell.
317    pub fn extent(&self) -> Option<((u32, u32), (u32, u32))> {
318        let mut it = self.cells.keys();
319        let &(r0, c0) = it.next()?;
320        let (mut top, mut left, mut bottom, mut right) = (r0, c0, r0, c0);
321        for &(r, c) in it {
322            top = top.min(r);
323            left = left.min(c);
324            bottom = bottom.max(r);
325            right = right.max(c);
326        }
327        Some(((top, left), (bottom, right)))
328    }
329}
330
331/// The workbook: its sheets in tab order, their formats, and which tab is
332/// open when the file opens.
333#[derive(Clone, Debug)]
334pub struct Workbook {
335    pub sheets: Vec<Sheet>,
336    pub styles: Styles,
337    pub active_sheet: usize,
338    /// The workbook's defined names: each a name and its formula's text
339    /// without the `=` (a string constant is `"text"`, inner quotes doubled).
340    pub defined_names: Vec<(String, String)>,
341}
342
343impl Default for Workbook {
344    fn default() -> Self {
345        Self::new()
346    }
347}
348
349impl Workbook {
350    pub fn new() -> Workbook {
351        Workbook {
352            sheets: Vec::new(),
353            styles: Styles::new(),
354            active_sheet: 0,
355            defined_names: Vec::new(),
356        }
357    }
358
359    /// Whether any cell holds a dynamic-array formula, which the file then
360    /// needs its metadata part for.
361    pub fn has_dynamic_formulas(&self) -> bool {
362        self.sheets.iter().any(|s| {
363            s.cells
364                .values()
365                .any(|c| matches!(c.content, Content::DynamicFormula(_)))
366        })
367    }
368
369    /// The index of the sheet of that name, compared as Excel compares sheet
370    /// names, without case.
371    pub fn find_sheet(&self, name: &str) -> Option<usize> {
372        let want = fold(name);
373        self.sheets.iter().position(|s| fold(&s.name) == want)
374    }
375
376    /// The sheet of that name, added last with the name as given when there
377    /// is none (`VlaEnsureSheet`'s shape).
378    pub fn ensure_sheet(&mut self, name: &str) -> usize {
379        if let Some(i) = self.find_sheet(name) {
380            return i;
381        }
382        self.sheets.push(Sheet::new(name));
383        self.sheets.len() - 1
384    }
385}
386
387/// A column's letters: 1 is A, 26 is Z, 27 is AA.
388pub fn column_letters(col: u32) -> String {
389    let mut n = col;
390    let mut letters = Vec::new();
391    while n > 0 {
392        let rem = (n - 1) % 26;
393        letters.push((b'A' + rem as u8) as char);
394        n = (n - 1) / 26;
395    }
396    letters.iter().rev().collect()
397}
398
399/// A cell's reference: row 1, column 2 is `B1`.
400pub fn cell_ref(row: u32, col: u32) -> String {
401    format!("{}{}", column_letters(col), row)
402}
403
404/// `ColumnWidth` in characters to the file's `width` attribute, as Excel
405/// computes it for the default font, Calibri 11, whose widest digit is 7
406/// pixels: the characters plus 5 pixels of padding, in 1/256ths of a
407/// character. 72 gives 72.7109375, which is what Excel itself writes for a
408/// column set to 72.
409pub fn column_width_attr(chars: f64) -> f64 {
410    const MAX_DIGIT_WIDTH: f64 = 7.0;
411    ((chars * MAX_DIGIT_WIDTH + 5.0) / MAX_DIGIT_WIDTH * 256.0).trunc() / 256.0
412}
413
414#[cfg(test)]
415mod tests {
416    use super::*;
417
418    #[test]
419    fn references_and_letters() {
420        assert_eq!(column_letters(1), "A");
421        assert_eq!(column_letters(26), "Z");
422        assert_eq!(column_letters(27), "AA");
423        assert_eq!(column_letters(702), "ZZ");
424        assert_eq!(column_letters(703), "AAA");
425        assert_eq!(cell_ref(1, 2), "B1");
426        assert_eq!(cell_ref(12, 3), "C12");
427    }
428
429    #[test]
430    fn the_room_widths_as_excel_writes_them() {
431        assert_eq!(column_width_attr(72.0), 72.7109375);
432        assert_eq!(column_width_attr(60.0), 60.7109375);
433        assert_eq!(column_width_attr(8.43), 9.140625);
434    }
435
436    #[test]
437    fn styles_dedup_and_fills_keep_first_use_order() {
438        let mut s = Styles::new();
439        let a = s.id(Style {
440            fill: Some(Rgb(1, 2, 3)),
441            ..Style::default()
442        });
443        let b = s.id(Style {
444            fill: Some(Rgb(4, 5, 6)),
445            wrap: true,
446            ..Style::default()
447        });
448        let a2 = s.id(Style {
449            fill: Some(Rgb(1, 2, 3)),
450            ..Style::default()
451        });
452        assert_eq!((a, b, a2), (1, 2, 1));
453        assert_eq!(s.fills(), vec![Rgb(1, 2, 3), Rgb(4, 5, 6)]);
454        assert_eq!(Rgb(247, 244, 252).argb_hex(), "FFF7F4FC");
455    }
456
457    #[test]
458    fn a1_references_as_a_program_writes_them() {
459        let r = parse_a1_range("b2").unwrap();
460        assert_eq!((r.top, r.left, r.bottom, r.right), (2, 2, 2, 2));
461        assert_eq!(r.text(), "B2");
462        let r = parse_a1_range("c4:A1").unwrap();
463        assert_eq!((r.top, r.left, r.bottom, r.right), (1, 1, 4, 3));
464        assert_eq!(r.text(), "A1:C4");
465        assert_eq!(r.cells(), 12);
466        assert_eq!(parse_a1_range("$B$2:$B$4").unwrap().text(), "B2:B4");
467        assert_eq!(parse_a1_range("XFD1048576").unwrap().text(), "XFD1048576");
468        for bad in [
469            "c:c", "1:3", "XFE1", "A0", "A1048577", "AAAA1", "b", "2", "", "b2:",
470        ] {
471            assert!(parse_a1_range(bad).is_none(), "{bad} should not parse");
472        }
473    }
474
475    #[test]
476    fn a_formula_over_a_range_is_shared_from_its_top_left_cell() {
477        let mut sh = Sheet::new("S");
478        sh.set_formula(parse_a1_range("c2:c3").unwrap(), "B2+B3", 0);
479        sh.set_formula(parse_a1_range("d2").unwrap(), "B2*2", 0);
480        sh.set_formula(parse_a1_range("e2:e3").unwrap(), "1", 0);
481        assert_eq!(
482            sh.cells[&(2, 3)].content,
483            Content::SharedMaster {
484                text: "B2+B3".to_string(),
485                range: "C2:C3".to_string(),
486                si: 0
487            }
488        );
489        assert_eq!(sh.cells[&(3, 3)].content, Content::SharedChild { si: 0 });
490        assert_eq!(
491            sh.cells[&(2, 4)].content,
492            Content::Formula("B2*2".to_string())
493        );
494        assert_eq!(
495            sh.cells[&(2, 5)].content,
496            Content::SharedMaster {
497                text: "1".to_string(),
498                range: "E2:E3".to_string(),
499                si: 1
500            }
501        );
502        assert_eq!(sh.shared_formulas, 2);
503    }
504
505    #[test]
506    fn a_dynamic_formula_over_a_range_is_one_per_cell_with_its_references_moved() {
507        let mut sh = Sheet::new("S");
508        sh.set_formula_dynamic(
509            parse_a1_range("c2:d3").unwrap(),
510            "IFS(B2>3,$A$1,TRUE,B$1)",
511            0,
512        );
513        assert_eq!(
514            sh.cells[&(2, 3)].content,
515            Content::DynamicFormula("IFS(B2>3,$A$1,TRUE,B$1)".to_string())
516        );
517        assert_eq!(
518            sh.cells[&(3, 3)].content,
519            Content::DynamicFormula("IFS(B3>3,$A$1,TRUE,B$1)".to_string())
520        );
521        assert_eq!(
522            sh.cells[&(2, 4)].content,
523            Content::DynamicFormula("IFS(C2>3,$A$1,TRUE,C$1)".to_string())
524        );
525        assert_eq!(
526            sh.cells[&(3, 4)].content,
527            Content::DynamicFormula("IFS(C3>3,$A$1,TRUE,C$1)".to_string())
528        );
529        let mut wb = Workbook::new();
530        assert!(!wb.has_dynamic_formulas());
531        wb.sheets.push(sh);
532        assert!(wb.has_dynamic_formulas());
533    }
534
535    #[test]
536    fn sheets_are_found_without_case_and_made_once() {
537        let mut wb = Workbook::new();
538        assert_eq!(wb.find_sheet("Output"), None);
539        let i = wb.ensure_sheet("Output");
540        assert_eq!(wb.ensure_sheet("output"), i);
541        assert_eq!(wb.find_sheet("OUTPUT"), Some(i));
542        assert_eq!(wb.sheets.len(), 1);
543        assert_eq!(wb.sheets[0].name, "Output");
544    }
545
546    #[test]
547    fn a_sheet_knows_its_extent() {
548        let mut sh = Sheet::new("S");
549        assert_eq!(sh.extent(), None);
550        sh.set(
551            3,
552            2,
553            Cell {
554                content: Content::Number(1.0),
555                style: 0,
556            },
557        );
558        sh.set(
559            1,
560            3,
561            Cell {
562                content: Content::Bool(true),
563                style: 0,
564            },
565        );
566        assert_eq!(sh.extent(), Some(((1, 2), (3, 3))));
567    }
568}
frazaro_core/sheet/mod.rs

frazaro_core/sheet/
mod.rs
