vla_lang/

sheet.rs

//! The sheet model (PORT.7): what a workbook holds before it is written.
//! The language's since PORT.12 (2026-10-08): the grid with its A1
//! coordinates, its style table and the references a formula's text holds
//! (`crate::refers`) are what an engine stands on; the file formats that
//! write and read it stay in `frazaro-core`'s `sheet` module, which
//! re-exports this one.
//! Sheets of cells, each cell a text, a number, a truth value, an error
//! value or a formula's text; column widths, a hidden column, a style per
//! cell or per column from a small style table; and nothing here that
//! computes: a formula is text the host evaluates when it opens the file,
//! which `fullCalcOnLoad` (ooxml.rs) makes it do, and since KERNEL.7
//! (2026-10-08) text `crate::calc` evaluates over this model in memory,
//! behind a declared subset that refuses by name, as `web/CALLOSUM.md`
//! section 7 decision 3 amended docs/HORIZON.md section 12.
//!
//! New ground, said plainly: the reference has no model of a sheet; it
//! writes through Excel's object model. What is modelled is the little the
//! room needs (`BuildWorkspace`, VLA_IDE.bas) and what slice 7b's static
//! subset writes, and the model grows only as a slice needs it.

use std::collections::{BTreeMap, HashMap};

use crate::rows::Visibility;

/// A colour, as `Interior.Color = RGB(r, g, b)` names it.
#[derive(Clone, Copy, Debug, PartialEq, Eq)]
pub struct Rgb(pub u8, pub u8, pub u8);

impl Rgb {
    /// The file's spelling: opaque alpha, then the three bytes in hex.
    pub fn argb_hex(self) -> String {
        format!("FF{:02X}{:02X}{:02X}", self.0, self.1, self.2)
    }
}

/// The number formats a style names: Excel's built-in General and Text
/// (`NumberFormat = "@"`, so a sentence is never read as a formula).
#[derive(Clone, Copy, Debug, Default, PartialEq, Eq)]
pub enum NumFmt {
    #[default]
    General,
    Text,
}

impl NumFmt {
    /// The built-in format's id in the styles part.
    pub fn id(self) -> u32 {
        match self {
            NumFmt::General => 0,
            NumFmt::Text => 49,
        }
    }
}

/// One cell format: a solid fill or none, a number format, wrapped text or
/// not. Index 0 of a [`Styles`] table is this type's default.
#[derive(Clone, Debug, Default, PartialEq, Eq)]
pub struct Style {
    pub fill: Option<Rgb>,
    pub num_fmt: NumFmt,
    pub wrap: bool,
}

/// The workbook's cell formats, each once; a cell or a column names one by
/// its index.
#[derive(Clone, Debug)]
pub struct Styles {
    xfs: Vec<Style>,
}

impl Default for Styles {
    fn default() -> Self {
        Self::new()
    }
}

impl Styles {
    /// A table holding only the default format, at index 0.
    pub fn new() -> Styles {
        Styles {
            xfs: vec![Style::default()],
        }
    }

    /// The index of a format, added if it is new.
    pub fn id(&mut self, style: Style) -> u32 {
        if let Some(i) = self.xfs.iter().position(|s| *s == style) {
            return i as u32;
        }
        self.xfs.push(style);
        (self.xfs.len() - 1) as u32
    }

    /// A format put at the index a record names (KERNEL.22, the loader:
    /// a `format` row says its own index), the table grown with default
    /// formats up to it.
    pub fn put(&mut self, index: u32, style: Style) {
        let i = index as usize;
        if self.xfs.len() <= i {
            self.xfs.resize(i + 1, Style::default());
        }
        self.xfs[i] = style;
    }

    /// The format at an index, or `None` past the table.
    pub fn get(&self, index: u32) -> Option<&Style> {
        self.xfs.get(index as usize)
    }

    /// Every format, in index order.
    pub fn xfs(&self) -> &[Style] {
        &self.xfs
    }

    /// The distinct fills, in the order they were first used.
    pub fn fills(&self) -> Vec<Rgb> {
        let mut fills: Vec<Rgb> = Vec::new();
        for s in &self.xfs {
            if let Some(f) = s.fill {
                if !fills.contains(&f) {
                    fills.push(f);
                }
            }
        }
        fills
    }
}

/// What a cell holds. A formula is its text without the leading `=`, as the
/// file stores it; the host computes it. A formula written into a range of
/// cells is a shared formula, which is what Excel itself writes when a
/// formula is filled: the top-left cell is the master and holds the text
/// and the range, the others point at it by its index, and the host
/// adjusts the references for each cell as `Range.Formula2` would have.
#[derive(Clone, Debug, PartialEq)]
pub enum Content {
    Text(String),
    Number(f64),
    Bool(bool),
    /// An error value typed into a cell with no formula behind it (`t="e"`
    /// in the file), by its text, `#N/A`; what a reader meets in a model
    /// (KERNEL.7). The writer writes it as the file holds it; no sentence
    /// puts one there.
    Error(String),
    Formula(String),
    SharedMaster {
        text: String,
        range: String,
        si: u32,
    },
    SharedChild {
        si: u32,
    },
    /// A dynamic-array formula (`cm="1"`, `t="array"` over its own cell):
    /// what Excel stores for a `Formula2` entry of a formula whose function
    /// can return an array, and reads back without the `@` it puts on a
    /// legacy formula of that kind.
    DynamicFormula(String),
}

#[derive(Clone, Debug, PartialEq)]
pub struct Cell {
    pub content: Content,
    pub style: u32,
}

/// A column's settings: `index` is 1-based; `width` is in characters, as
/// `ColumnWidth` counts them; `style` is the format of its cells that have
/// none of their own.
#[derive(Clone, Debug, PartialEq)]
pub struct Column {
    pub index: u32,
    pub width: Option<f64>,
    pub hidden: bool,
    pub style: Option<u32>,
}

/// A rectangle of cells, rows and columns 1-based and inclusive.
#[derive(Clone, Copy, Debug, PartialEq, Eq)]
pub struct A1Range {
    pub top: u32,
    pub left: u32,
    pub bottom: u32,
    pub right: u32,
}

impl A1Range {
    pub fn cells(&self) -> u64 {
        u64::from(self.bottom - self.top + 1) * u64::from(self.right - self.left + 1)
    }

    /// `B2` for one cell, `B2:C4` for more.
    pub fn text(&self) -> String {
        if self.top == self.bottom && self.left == self.right {
            cell_ref(self.top, self.left)
        } else {
            format!(
                "{}:{}",
                cell_ref(self.top, self.left),
                cell_ref(self.bottom, self.right)
            )
        }
    }
}

/// Excel's last column (XFD) and last row.
pub const MAX_COLUMN: u32 = 16_384;
pub const MAX_ROW: u32 = 1_048_576;

fn parse_a1_cell(s: &str) -> Option<(u32, u32)> {
    let s = s.replace('$', "");
    let letters: String = s.chars().take_while(|c| c.is_ascii_alphabetic()).collect();
    let digits = &s[letters.len()..];
    if letters.is_empty() || letters.len() > 3 || digits.is_empty() {
        return None;
    }
    if !digits.chars().all(|c| c.is_ascii_digit()) {
        return None;
    }
    let mut col: u32 = 0;
    for c in letters.chars() {
        col = col * 26 + (c.to_ascii_uppercase() as u32 - 'A' as u32 + 1);
    }
    let row: u32 = digits.parse().ok()?;
    if col == 0 || col > MAX_COLUMN || row == 0 || row > MAX_ROW {
        return None;
    }
    Some((row, col))
}

/// An A1 reference as a program writes it (`b2`, `A1:B5`, `$C$2`), in any
/// case, with the corners in any order; `None` for anything else, a whole
/// column or row included.
pub fn parse_a1_range(s: &str) -> Option<A1Range> {
    let s = s.trim();
    let (a, b) = match s.split_once(':') {
        Some((a, b)) => (a, b),
        None => (s, s),
    };
    let (r1, c1) = parse_a1_cell(a)?;
    let (r2, c2) = parse_a1_cell(b)?;
    Some(A1Range {
        top: r1.min(r2),
        left: c1.min(c2),
        bottom: r1.max(r2),
        right: c1.max(c2),
    })
}

/// A row's settings, the twin of a [`Column`]'s (KERNEL.22; `Alonzo/SPEC.md`
/// decision 19): its height in points, or `None` for the default, and
/// whether it is hidden.
#[derive(Clone, Debug, PartialEq)]
pub struct RowSettings {
    pub height: Option<f64>,
    pub hidden: bool,
}

/// One sheet: its cells by (row, column), both 1-based.
#[derive(Clone, Debug, PartialEq)]
pub struct Sheet {
    pub name: String,
    pub cells: BTreeMap<(u32, u32), Cell>,
    pub columns: Vec<Column>,
    /// The rows with settings of their own, by number (KERNEL.22), printed
    /// as the view record's `row` rows; empty for a sheet the writer built.
    pub rows: BTreeMap<u32, RowSettings>,
    /// The look map (KERNEL.22; `Alonzo/SPEC.md` decision 19): a value as a
    /// `cell` row spells it, and the format a cell holding that value draws
    /// with in place of its own, in the order declared; printed as the view
    /// record's `look` rows.
    pub looks: Vec<(String, u32)>,
    /// Whether the sheet has a tab (KERNEL.22): the reader's three words.
    /// Every sheet the writer builds is visible; an engine's twins are
    /// hidden.
    pub visibility: Visibility,
    pub gridlines: bool,
    /// The selected cell when the sheet opens, (row, column).
    pub active_cell: (u32, u32),
    /// How many shared formulas the sheet holds; the next one's index.
    pub shared_formulas: u32,
    /// The row of the sentence that wrote each cell, by (row, column): the
    /// program line, which is the row of the `Frazaro` sheet that holds the
    /// sentence (KERNEL.4, the view record). Filled by the build's walker;
    /// empty for a sheet nothing wrote by sentence.
    pub sentences: BTreeMap<(u32, u32), u32>,
}

impl Sheet {
    pub fn new(name: &str) -> Sheet {
        Sheet {
            name: name.to_string(),
            cells: BTreeMap::new(),
            columns: Vec::new(),
            rows: BTreeMap::new(),
            looks: Vec::new(),
            visibility: Visibility::Visible,
            gridlines: true,
            active_cell: (1, 1),
            shared_formulas: 0,
            sentences: BTreeMap::new(),
        }
    }

    /// Record that the sentence on program row `row_of_sentence` wrote every
    /// cell of `range`, after the cells are set.
    pub fn wrote(&mut self, range: A1Range, row_of_sentence: u32) {
        if row_of_sentence == 0 {
            return;
        }
        for row in range.top..=range.bottom {
            for col in range.left..=range.right {
                self.sentences.insert((row, col), row_of_sentence);
            }
        }
    }

    pub fn set(&mut self, row: u32, col: u32, cell: Cell) {
        self.cells.insert((row, col), cell);
    }

    /// A formula's text (without its `=`) into every cell of a range: one
    /// plain formula for one cell, a shared formula for more.
    /// A formula whose function can return an array, into every cell of a
    /// range as Excel stores a `Formula2` entry of it: a dynamic-array
    /// formula per cell (a dynamic-array formula cannot be shared), its
    /// references moved by the cell's distance from the top-left one, as
    /// Excel moves them when a formula is filled (the mover is
    /// `crate::refers::shift_a1_references` since PORT.8, slice 8b).
    pub fn set_formula_dynamic(&mut self, range: A1Range, text: &str, style: u32) {
        for row in range.top..=range.bottom {
            for col in range.left..=range.right {
                let moved = crate::refers::shift_a1_references(
                    text,
                    i64::from(row) - i64::from(range.top),
                    i64::from(col) - i64::from(range.left),
                );
                self.set(
                    row,
                    col,
                    Cell {
                        content: Content::DynamicFormula(moved),
                        style,
                    },
                );
            }
        }
    }

    pub fn set_formula(&mut self, range: A1Range, text: &str, style: u32) {
        let si = self.next_shared(range);
        for row in range.top..=range.bottom {
            for col in range.left..=range.right {
                let content = filled(range, text, si, row, col);
                self.set(row, col, Cell { content, style });
            }
        }
    }

    /// The index of the shared formula a formula written over `range` makes,
    /// taken; `None` for a range of one cell, which holds a plain formula.
    pub(crate) fn next_shared(&mut self, range: A1Range) -> Option<u32> {
        (range.cells() > 1).then(|| {
            self.shared_formulas += 1;
            self.shared_formulas - 1
        })
    }

    /// The used range as ((first row, first column), (last row, last
    /// column)), or `None` for a sheet with no cell.
    pub fn extent(&self) -> Option<((u32, u32), (u32, u32))> {
        #[cfg(test)]
        work::extents(self.cells.len());
        let mut it = self.cells.keys();
        let &(r0, c0) = it.next()?;
        let (mut top, mut left, mut bottom, mut right) = (r0, c0, r0, c0);
        for &(r, c) in it {
            top = top.min(r);
            left = left.min(c);
            bottom = bottom.max(r);
            right = right.max(c);
        }
        Some(((top, left), (bottom, right)))
    }
}

/// The shared formulas' masters of a sheet, by index: the text and the cell
/// that holds it, so that a child's text is the master's with its
/// references moved. The view record and the evaluator both read a child
/// through this (KERNEL.4; KERNEL.7).
pub fn shared_masters(sheet: &Sheet) -> HashMap<u32, (&str, u32, u32)> {
    #[cfg(test)]
    work::walked(sheet.cells.len());
    let mut masters = HashMap::new();
    for ((row, col), cell) in &sheet.cells {
        if let Content::SharedMaster { text, si, .. } = &cell.content {
            masters.insert(*si, (text.as_str(), *row, *col));
        }
    }
    masters
}

/// A formula's text as the formula bar shows it, without its `=`, the
/// file's prefixes dropped, as the reader spells the same cell of the
/// written file: a shared child is its master's text moved by the cell's
/// distance, moved first and stripped after, as the reader does it. `None`
/// for a value, or for a child whose master the sheet has lost.
pub fn formula_text(
    content: &Content,
    row: u32,
    col: u32,
    masters: &HashMap<u32, (&str, u32, u32)>,
) -> Option<String> {
    let raw = match content {
        Content::Formula(t) | Content::DynamicFormula(t) => t.clone(),
        Content::SharedMaster { text, .. } => text.clone(),
        Content::SharedChild { si } => {
            let (text, m_row, m_col) = masters.get(si)?;
            crate::refers::shift_a1_references(
                text,
                i64::from(row) - i64::from(*m_row),
                i64::from(col) - i64::from(*m_col),
            )
        }
        Content::Text(_) | Content::Number(_) | Content::Bool(_) | Content::Error(_) => {
            return None
        }
    };
    Some(strip_future_prefixes(&raw))
}

/// What a formula written over `range` puts in one of its cells, the rule
/// [`Sheet::set_formula`] and a machine's write share (KERNEL.34): the text
/// for a range of one cell (`si` `None`); else the shared formula `si`, its
/// text and its range at the range's first cell and its index at every
/// other.
pub(crate) fn filled(range: A1Range, text: &str, si: Option<u32>, row: u32, col: u32) -> Content {
    match si {
        None => Content::Formula(text.to_string()),
        Some(si) if row == range.top && col == range.left => Content::SharedMaster {
            text: text.to_string(),
            range: range.text(),
            si,
        },
        Some(si) => Content::SharedChild { si },
    }
}

/// The work a machine's write does, counted in a test (KERNEL.34's oracle,
/// as `crate::calc::vector::taken` notes a run's paths): the cells walked
/// to find a sheet's shared formulas or a released fill's cells, the texts
/// the mover moved, and the fills a write released, each whole or cell by
/// cell. A write's cost is pinned by these counts and not by its time; an
/// allocation count would miss the walks, which allocate once a master and
/// not once a cell. Since KERNEL.37 a view's walks are counted too: the
/// masters' and the extent's (`Sheet::extent`). A frame's end is counted the
/// same way (KERNEL.36's oracle): the cells it copied into their twins, and
/// the cells it walked. Counted on the thread that asked, between
/// [`work::begin`] and [`work::end`], and only in a test build.
#[cfg(test)]
pub(crate) mod work {
    use std::cell::Cell;

    /// What the writes and views since [`begin`] did.
    #[derive(Clone, Copy, Debug, Default, PartialEq, Eq)]
    pub(crate) struct Work {
        /// The cells walked.
        pub(crate) walked: u64,
        /// The texts moved.
        pub(crate) moved: u64,
        /// The fills released whole, their first cell left made their master.
        pub(crate) whole: u64,
        /// The fills released cell by cell, each cell given its own text.
        pub(crate) split: u64,
        /// The cells walked to find a sheet's extent.
        pub(crate) extents: u64,
        /// The cells a frame's end copied into their twins (KERNEL.36).
        pub(crate) copied: u64,
        /// The cells a frame's end walked, every cell of every sheet
        /// (KERNEL.36).
        pub(crate) swept: u64,
    }

    thread_local! {
        static ON: Cell<bool> = const { Cell::new(false) };
        static DONE: Cell<Work> = const {
            Cell::new(Work {
                walked: 0,
                moved: 0,
                whole: 0,
                split: 0,
                extents: 0,
                copied: 0,
                swept: 0,
            })
        };
    }

    /// Counting begins on this thread, from nothing.
    pub(crate) fn begin() {
        ON.with(|on| on.set(true));
        DONE.with(|done| done.set(Work::default()));
    }

    /// What was counted since [`begin`]; counting ends.
    pub(crate) fn end() -> Work {
        ON.with(|on| on.set(false));
        DONE.with(Cell::get)
    }

    fn add(f: impl FnOnce(&mut Work)) {
        if ON.with(Cell::get) {
            DONE.with(|done| {
                let mut work = done.get();
                f(&mut work);
                done.set(work);
            });
        }
    }

    /// A walk over a sheet of `cells` cells.
    pub(crate) fn walked(cells: usize) {
        add(|w| w.walked += cells as u64);
    }

    /// A walk over a sheet of `cells` cells for its extent.
    pub(crate) fn extents(cells: usize) {
        add(|w| w.extents += cells as u64);
    }

    /// One text moved.
    pub(crate) fn moved() {
        add(|w| w.moved += 1);
    }

    /// One fill released, whole or cell by cell.
    pub(crate) fn released(whole: bool) {
        add(|w| {
            if whole {
                w.whole += 1;
            } else {
                w.split += 1;
            }
        });
    }

    /// One cell a frame's end copied into its twin.
    pub(crate) fn copied() {
        add(|w| w.copied += 1);
    }

    /// A frame's end's walk over a sheet of `cells` cells.
    pub(crate) fn swept(cells: usize) {
        add(|w| w.swept += cells as u64);
    }
}

/// The workbook: its sheets in tab order, their formats, and which tab is
/// open when the file opens.
#[derive(Clone, Debug)]
pub struct Workbook {
    pub sheets: Vec<Sheet>,
    pub styles: Styles,
    pub active_sheet: usize,
    /// The workbook's defined names: each a name and its formula's text
    /// without the `=` (a string constant is `"text"`, inner quotes doubled).
    pub defined_names: Vec<(String, String)>,
}

impl Default for Workbook {
    fn default() -> Self {
        Self::new()
    }
}

impl Workbook {
    pub fn new() -> Workbook {
        Workbook {
            sheets: Vec::new(),
            styles: Styles::new(),
            active_sheet: 0,
            defined_names: Vec::new(),
        }
    }

    /// Whether any cell holds a dynamic-array formula, which the file then
    /// needs its metadata part for.
    pub fn has_dynamic_formulas(&self) -> bool {
        self.sheets.iter().any(|s| {
            s.cells
                .values()
                .any(|c| matches!(c.content, Content::DynamicFormula(_)))
        })
    }

    /// The index of the sheet of that name, compared as Excel compares sheet
    /// names, without case: `fold`'s test, which lowers the ASCII capitals
    /// alone, made byte by byte in place (KERNEL.25). Every reference of
    /// every evaluation asks it, and folding each name into a new string
    /// was a third of a frame of Life.
    pub fn find_sheet(&self, name: &str) -> Option<usize> {
        self.sheets
            .iter()
            .position(|s| s.name.eq_ignore_ascii_case(name))
    }

    /// The sheet of that name, added last with the name as given when there
    /// is none (`VlaEnsureSheet`'s shape).
    pub fn ensure_sheet(&mut self, name: &str) -> usize {
        if let Some(i) = self.find_sheet(name) {
            return i;
        }
        self.sheets.push(Sheet::new(name));
        self.sheets.len() - 1
    }
}

/// A column's letters: 1 is A, 26 is Z, 27 is AA.
pub fn column_letters(col: u32) -> String {
    let mut n = col;
    let mut letters = Vec::new();
    while n > 0 {
        let rem = (n - 1) % 26;
        letters.push((b'A' + rem as u8) as char);
        n = (n - 1) / 26;
    }
    letters.iter().rev().collect()
}

/// A cell's reference: row 1, column 2 is `B1`.
pub fn cell_ref(row: u32, col: u32) -> String {
    format!("{}{}", column_letters(col), row)
}

/// `ColumnWidth` in characters to the file's `width` attribute, as Excel
/// computes it for the default font, Calibri 11, whose widest digit is 7
/// pixels: the characters plus 5 pixels of padding, in 1/256ths of a
/// character. 72 gives 72.7109375, which is what Excel itself writes for a
/// column set to 72.
pub fn column_width_attr(chars: f64) -> f64 {
    const MAX_DIGIT_WIDTH: f64 = 7.0;
    ((chars * MAX_DIGIT_WIDTH + 5.0) / MAX_DIGIT_WIDTH * 256.0).trunc() / 256.0
}

/// A number as the file holds it, and as the view record spells it. Rust's
/// shortest round-trip spelling, which never uses an exponent, is one Excel
/// reads; a value no cell can hold (not finite) is `None`, which the writer
/// and the view spell as Excel's `#NUM!` error.
pub fn number_text(v: f64) -> Option<String> {
    v.is_finite().then(|| format!("{v}"))
}

/// The first character of a name in a formula's text: a letter or `_`.
pub fn is_name_start(c: char) -> bool {
    c.is_ascii_alphabetic() || c == '_'
}

/// A character inside a name in a formula's text: a letter, a digit, `_`
/// or `.`.
pub fn is_name_char(c: char) -> bool {
    c.is_ascii_alphanumeric() || c == '_' || c == '.'
}

/// A formula's text as the formula bar shows it: `_xlfn.`, `_xlfn._xlws.`
/// and `_xlpm.`, the prefixes an OOXML file stores a newer function and its
/// parameters under (the core's writer adds them, its reader drops them),
/// dropped from every name outside a string literal. The names themselves
/// are kept as the file spells them. Held here with the grid since the view
/// record spells a formula this way (PORT.12); the inverse of the core's
/// `sheet::xlfn::prefix_future_functions`.
pub fn strip_future_prefixes(formula: &str) -> String {
    let chars: Vec<char> = formula.chars().collect();
    let mut out = String::with_capacity(formula.len());
    let mut i = 0;
    while i < chars.len() {
        let c = chars[i];
        if c == '"' {
            out.push(c);
            i += 1;
            while i < chars.len() {
                out.push(chars[i]);
                if chars[i] == '"' {
                    if i + 1 < chars.len() && chars[i + 1] == '"' {
                        out.push('"');
                        i += 2;
                        continue;
                    }
                    i += 1;
                    break;
                }
                i += 1;
            }
            continue;
        }
        if is_name_start(c) && (i == 0 || !is_name_char(chars[i - 1])) {
            let start = i;
            while i < chars.len() && is_name_char(chars[i]) {
                i += 1;
            }
            let name: String = chars[start..i].iter().collect();
            let mut bare = name.as_str();
            loop {
                let before = bare.len();
                for prefix in ["_xlfn.", "_xlws.", "_xlpm."] {
                    if bare.len() > prefix.len()
                        && bare[..prefix.len()].eq_ignore_ascii_case(prefix)
                    {
                        bare = &bare[prefix.len()..];
                    }
                }
                if bare.len() == before {
                    break;
                }
            }
            out.push_str(bare);
            continue;
        }
        out.push(c);
        i += 1;
    }
    out
}

#[cfg(test)]
mod tests {
    use super::*;

    #[test]
    fn references_and_letters() {
        assert_eq!(column_letters(1), "A");
        assert_eq!(column_letters(26), "Z");
        assert_eq!(column_letters(27), "AA");
        assert_eq!(column_letters(702), "ZZ");
        assert_eq!(column_letters(703), "AAA");
        assert_eq!(cell_ref(1, 2), "B1");
        assert_eq!(cell_ref(12, 3), "C12");
    }

    #[test]
    fn a_sheet_is_found_as_the_fold_finds_it() {
        // KERNEL.25: find_sheet compares in place what it once compared
        // through `fold`, which stays here as the rule it must keep. Names
        // whose folds differ only outside ASCII are different sheets.
        use crate::intrinsics::fold;
        let mut book = Workbook::new();
        for name in ["É", "é", "İSTANBUL", "Straße", "Data", "Screen.last"] {
            book.sheets.push(Sheet::new(name));
        }
        let by_fold = |name: &str| book.sheets.iter().position(|s| fold(&s.name) == fold(name));
        let asked = [
            ("É", Some(0)),
            ("é", Some(1)),
            ("İstanbul", Some(2)),
            ("istanbul", None),
            ("straße", Some(3)),
            ("STRASSE", None),
            ("DATA", Some(4)),
            ("SCREEN.LAST", Some(5)),
            ("Screen", None),
            ("", None),
        ];
        for (name, want) in asked {
            assert_eq!(book.find_sheet(name), want, "{name}");
            assert_eq!(book.find_sheet(name), by_fold(name), "{name}");
        }
    }

    #[test]
    fn the_room_widths_as_excel_writes_them() {
        assert_eq!(column_width_attr(72.0), 72.7109375);
        assert_eq!(column_width_attr(60.0), 60.7109375);
        assert_eq!(column_width_attr(8.43), 9.140625);
    }

    #[test]
    fn styles_dedup_and_fills_keep_first_use_order() {
        let mut s = Styles::new();
        let a = s.id(Style {
            fill: Some(Rgb(1, 2, 3)),
            ..Style::default()
        });
        let b = s.id(Style {
            fill: Some(Rgb(4, 5, 6)),
            wrap: true,
            ..Style::default()
        });
        let a2 = s.id(Style {
            fill: Some(Rgb(1, 2, 3)),
            ..Style::default()
        });
        assert_eq!((a, b, a2), (1, 2, 1));
        assert_eq!(s.fills(), vec![Rgb(1, 2, 3), Rgb(4, 5, 6)]);
        assert_eq!(Rgb(247, 244, 252).argb_hex(), "FFF7F4FC");
        // A format put at the index a record names, the table grown with
        // defaults (KERNEL.22, the loader).
        let wrap = Style {
            wrap: true,
            ..Style::default()
        };
        s.put(5, wrap.clone());
        assert_eq!(s.xfs().len(), 6);
        assert_eq!(s.get(5), Some(&wrap));
        assert_eq!(s.get(4), Some(&Style::default()));
        assert_eq!(s.get(6), None);
    }

    #[test]
    fn a1_references_as_a_program_writes_them() {
        let r = parse_a1_range("b2").unwrap();
        assert_eq!((r.top, r.left, r.bottom, r.right), (2, 2, 2, 2));
        assert_eq!(r.text(), "B2");
        let r = parse_a1_range("c4:A1").unwrap();
        assert_eq!((r.top, r.left, r.bottom, r.right), (1, 1, 4, 3));
        assert_eq!(r.text(), "A1:C4");
        assert_eq!(r.cells(), 12);
        assert_eq!(parse_a1_range("$B$2:$B$4").unwrap().text(), "B2:B4");
        assert_eq!(parse_a1_range("XFD1048576").unwrap().text(), "XFD1048576");
        for bad in [
            "c:c", "1:3", "XFE1", "A0", "A1048577", "AAAA1", "b", "2", "", "b2:",
        ] {
            assert!(parse_a1_range(bad).is_none(), "{bad} should not parse");
        }
    }

    #[test]
    fn a_formula_over_a_range_is_shared_from_its_top_left_cell() {
        let mut sh = Sheet::new("S");
        sh.set_formula(parse_a1_range("c2:c3").unwrap(), "B2+B3", 0);
        sh.set_formula(parse_a1_range("d2").unwrap(), "B2*2", 0);
        sh.set_formula(parse_a1_range("e2:e3").unwrap(), "1", 0);
        assert_eq!(
            sh.cells[&(2, 3)].content,
            Content::SharedMaster {
                text: "B2+B3".to_string(),
                range: "C2:C3".to_string(),
                si: 0
            }
        );
        assert_eq!(sh.cells[&(3, 3)].content, Content::SharedChild { si: 0 });
        assert_eq!(
            sh.cells[&(2, 4)].content,
            Content::Formula("B2*2".to_string())
        );
        assert_eq!(
            sh.cells[&(2, 5)].content,
            Content::SharedMaster {
                text: "1".to_string(),
                range: "E2:E3".to_string(),
                si: 1
            }
        );
        assert_eq!(sh.shared_formulas, 2);
    }

    #[test]
    fn a_dynamic_formula_over_a_range_is_one_per_cell_with_its_references_moved() {
        let mut sh = Sheet::new("S");
        sh.set_formula_dynamic(
            parse_a1_range("c2:d3").unwrap(),
            "IFS(B2>3,$A$1,TRUE,B$1)",
            0,
        );
        assert_eq!(
            sh.cells[&(2, 3)].content,
            Content::DynamicFormula("IFS(B2>3,$A$1,TRUE,B$1)".to_string())
        );
        assert_eq!(
            sh.cells[&(3, 3)].content,
            Content::DynamicFormula("IFS(B3>3,$A$1,TRUE,B$1)".to_string())
        );
        assert_eq!(
            sh.cells[&(2, 4)].content,
            Content::DynamicFormula("IFS(C2>3,$A$1,TRUE,C$1)".to_string())
        );
        assert_eq!(
            sh.cells[&(3, 4)].content,
            Content::DynamicFormula("IFS(C3>3,$A$1,TRUE,C$1)".to_string())
        );
        let mut wb = Workbook::new();
        assert!(!wb.has_dynamic_formulas());
        wb.sheets.push(sh);
        assert!(wb.has_dynamic_formulas());
    }

    #[test]
    fn sheets_are_found_without_case_and_made_once() {
        let mut wb = Workbook::new();
        assert_eq!(wb.find_sheet("Output"), None);
        let i = wb.ensure_sheet("Output");
        assert_eq!(wb.ensure_sheet("output"), i);
        assert_eq!(wb.find_sheet("OUTPUT"), Some(i));
        assert_eq!(wb.sheets.len(), 1);
        assert_eq!(wb.sheets[0].name, "Output");
    }

    #[test]
    fn a_sheet_knows_its_extent() {
        let mut sh = Sheet::new("S");
        assert_eq!(sh.extent(), None);
        sh.set(
            3,
            2,
            Cell {
                content: Content::Number(1.0),
                style: 0,
            },
        );
        sh.set(
            1,
            3,
            Cell {
                content: Content::Bool(true),
                style: 0,
            },
        );
        assert_eq!(sh.extent(), Some(((1, 2), (3, 3))));
    }

    #[test]
    fn numbers_as_the_file_holds_them() {
        assert_eq!(number_text(5.0).as_deref(), Some("5"));
        assert_eq!(number_text(0.1).as_deref(), Some("0.1"));
        assert_eq!(number_text(-2.5).as_deref(), Some("-2.5"));
        assert_eq!(number_text(f64::NAN), None);
        assert_eq!(number_text(f64::INFINITY), None);
    }

    #[test]
    fn the_prefixes_come_off_a_formula_read_back() {
        assert_eq!(
            strip_future_prefixes("_xlfn.IFS(B2>3,\"big\",TRUE,\"small\")"),
            "IFS(B2>3,\"big\",TRUE,\"small\")"
        );
        assert_eq!(
            strip_future_prefixes("_xlfn._xlws.FILTER(A:A,B:B>1)"),
            "FILTER(A:A,B:B>1)"
        );
        assert_eq!(
            strip_future_prefixes("_xlfn.LET(_xlpm.x,1,_xlpm.x*2)"),
            "LET(x,1,x*2)"
        );
        assert_eq!(
            strip_future_prefixes("SUM(A1:A3)&\"_xlfn.\""),
            "SUM(A1:A3)&\"_xlfn.\""
        );
        assert_eq!(strip_future_prefixes("Rate*B1"), "Rate*B1");
        assert_eq!(strip_future_prefixes("_xlfn."), "_xlfn.");
    }
}