ravenrs_sheet/formula/
adjust.rs

1//! Reference adjustment: copying formulas, inserting/deleting rows and columns, moving cells,
2//! renaming and deleting sheets.
3
4use crate::base::{CellError, MAX_COLS, MAX_ROWS, RangeRef};
5
6use crate::formula::ast::{Anchor, Expr, RefKind, Reference, SheetSel, clamp_anchor};
7
8/// Copy/fill: relative parts move by `(dr, dc)`. References pushed off the sheet become `#REF!`.
9pub fn shift_relative(e: Expr, dr: i64, dc: i64) -> Expr {
10    e.map(&mut |x| match x {
11        Expr::Ref(r) => match shift_ref(&r, dr, dc) {
12            Some(r) => Expr::Ref(r),
13            None => Expr::Error(CellError::Ref),
14        },
15        other => other,
16    })
17}
18
19/// Defined names: relative parts move by `(dr, dc)`, wrapping around the sheet's edges. Files
20/// store a name's relative references as seen from A1 (a name for "the cell to the left" defined
21/// in B1 is `Sheet1!XFD1`), and a use in another cell moves them by that cell's offset from A1.
22pub fn shift_relative_wrapping(e: Expr, dr: i64, dc: i64) -> Expr {
23    let wrap = |v: u32, abs: bool, d: i64, max: u32| if abs { v } else { (v as i64 + d).rem_euclid(max as i64) as u32 };
24    let anchor = |a: Anchor| Anchor { row: wrap(a.row, a.row_abs, dr, MAX_ROWS), col: wrap(a.col, a.col_abs, dc, MAX_COLS), ..a };
25    e.map(&mut |x| match x {
26        Expr::Ref(r) => {
27            let kind = match r.kind {
28                RefKind::Cell(a) => RefKind::Cell(anchor(a)),
29                RefKind::Range(a, b) => RefKind::Range(anchor(a), anchor(b)),
30                RefKind::Rows(r0, a0, r1, a1) => RefKind::Rows(wrap(r0, a0, dr, MAX_ROWS), a0, wrap(r1, a1, dr, MAX_ROWS), a1),
31                RefKind::Cols(c0, a0, c1, a1) => RefKind::Cols(wrap(c0, a0, dc, MAX_COLS), a0, wrap(c1, a1, dc, MAX_COLS), a1),
32            };
33            Expr::Ref(Reference { sheet: r.sheet, kind })
34        }
35        other => other,
36    })
37}
38
39/// Whether the formula has a relative (no `$`) row or column anywhere.
40pub fn has_relative_refs(e: &Expr) -> bool {
41    e.references().iter().any(|r| match r.kind {
42        RefKind::Cell(a) => !a.row_abs || !a.col_abs,
43        RefKind::Range(a, b) => !a.row_abs || !a.col_abs || !b.row_abs || !b.col_abs,
44        RefKind::Rows(_, a0, _, a1) | RefKind::Cols(_, a0, _, a1) => !a0 || !a1,
45    })
46}
47
48fn shift_anchor(a: &Anchor, dr: i64, dc: i64) -> Option<Anchor> {
49    let row = if a.row_abs { a.row as i64 } else { a.row as i64 + dr };
50    let col = if a.col_abs { a.col as i64 } else { a.col as i64 + dc };
51    let (row, col) = clamp_anchor(row, col)?;
52    Some(Anchor { row, col, ..*a })
53}
54
55fn shift_line(v: u32, abs: bool, d: i64, max: u32) -> Option<u32> {
56    if abs {
57        return Some(v);
58    }
59    let n = v as i64 + d;
60    if (0..max as i64).contains(&n) { Some(n as u32) } else { None }
61}
62
63fn shift_ref(r: &Reference, dr: i64, dc: i64) -> Option<Reference> {
64    let kind = match &r.kind {
65        RefKind::Cell(a) => RefKind::Cell(shift_anchor(a, dr, dc)?),
66        RefKind::Range(a, b) => RefKind::Range(shift_anchor(a, dr, dc)?, shift_anchor(b, dr, dc)?),
67        RefKind::Rows(r0, a0, r1, a1) => RefKind::Rows(shift_line(*r0, *a0, dr, MAX_ROWS)?, *a0, shift_line(*r1, *a1, dr, MAX_ROWS)?, *a1),
68        RefKind::Cols(c0, a0, c1, a1) => RefKind::Cols(shift_line(*c0, *a0, dc, MAX_COLS)?, *a0, shift_line(*c1, *a1, dc, MAX_COLS)?, *a1),
69    };
70    Some(Reference { sheet: r.sheet.clone(), kind })
71}
72
73#[derive(Clone, Copy, Debug, PartialEq, Eq)]
74pub enum Axis {
75    Rows,
76    Cols,
77}
78
79/// A structural edit on one sheet.
80#[derive(Clone, Debug, PartialEq)]
81pub enum Edit {
82    /// Insert `count` rows/cols before index `at`.
83    Insert { axis: Axis, at: u32, count: u32 },
84    /// Delete `count` rows/cols starting at `at`.
85    Delete { axis: Axis, at: u32, count: u32 },
86    /// Cut-paste of a block: references into `from` now point into the block at `to` (top-left).
87    Move { from: RangeRef, to_row: u32, to_col: u32 },
88    /// Insert Cells shifting down (`Rows`) or right (`Cols`): the cells of `range` and those
89    /// below (right of) it move by its height (width), within its columns (rows) only.
90    InsertCells { axis: Axis, range: RangeRef },
91    /// Delete Cells shifting up (`Rows`) or left (`Cols`): `range` goes and the cells below
92    /// (right of) it move back by its height (width), within its columns (rows) only.
93    DeleteCells { axis: Axis, range: RangeRef },
94    /// Cut-paste to another sheet; fully contained references follow the destination sheet too.
95    MoveToSheet { from: RangeRef, to_sheet: String, to_row: u32, to_col: u32 },
96}
97
98/// Adjusts a formula living on sheet `host` after `edit` on sheet `target`.
99pub fn adjust(e: Expr, host: &str, target: &str, edit: &Edit) -> Expr {
100    e.map(&mut |x| match x {
101        Expr::Ref(r) => {
102            let on_target = match &r.sheet {
103                SheetSel::Current => host.eq_ignore_ascii_case(target),
104                SheetSel::Named(n) => n.eq_ignore_ascii_case(target),
105                SheetSel::Span(..) => false,
106            };
107            if !on_target {
108                return Expr::Ref(r);
109            }
110            match adjust_ref(&r, edit) {
111                Some(r) => Expr::Ref(r),
112                None => Expr::Error(CellError::Ref),
113            }
114        }
115        other => other,
116    })
117}
118
119/// New position of an index after an insert (`None` never) or delete (`None` = deleted).
120fn map_index(v: u32, at: u32, count: u32, insert: bool, max: u32) -> Option<u32> {
121    if insert {
122        if v >= at {
123            let n = v as u64 + count as u64;
124            if n >= max as u64 { None } else { Some(n as u32) }
125        } else {
126            Some(v)
127        }
128    } else if v < at {
129        Some(v)
130    } else if v >= at.saturating_add(count) {
131        Some(v - count)
132    } else {
133        None
134    }
135}
136
137/// Adjusts a span `[lo, hi]` (inclusive) on one axis.
138fn map_span(lo: u32, hi: u32, at: u32, count: u32, insert: bool, max: u32) -> Option<(u32, u32)> {
139    if insert {
140        let lo2 = if lo >= at { lo as u64 + count as u64 } else { lo as u64 };
141        let hi2 = if hi >= at { hi as u64 + count as u64 } else { hi as u64 };
142        // Whole-sheet spans (e.g. A:A) stay whole.
143        let hi2 = hi2.min(max as u64 - 1);
144        if lo2 > hi2 {
145            return None;
146        }
147        Some((lo2 as u32, hi2 as u32))
148    } else {
149        let end = at.saturating_add(count); // exclusive
150        if lo >= at && hi < end {
151            return None; // fully deleted
152        }
153        let lo2 = if lo < at {
154            lo
155        } else if lo >= end {
156            lo - count
157        } else {
158            at
159        };
160        let hi2 = if hi < at {
161            hi
162        } else if hi >= end {
163            hi - count
164        } else {
165            at.saturating_sub(1)
166        };
167        Some((lo2, hi2.max(lo2)))
168    }
169}
170
171fn adjust_ref(r: &Reference, edit: &Edit) -> Option<Reference> {
172    match edit {
173        Edit::Insert { axis, at, count } | Edit::Delete { axis, at, count } => {
174            let insert = matches!(edit, Edit::Insert { .. });
175            let kind = match (&r.kind, axis) {
176                (RefKind::Cell(a), Axis::Rows) => RefKind::Cell(Anchor { row: map_index(a.row, *at, *count, insert, MAX_ROWS)?, ..*a }),
177                (RefKind::Cell(a), Axis::Cols) => RefKind::Cell(Anchor { col: map_index(a.col, *at, *count, insert, MAX_COLS)?, ..*a }),
178                (RefKind::Range(a, b), Axis::Rows) => {
179                    let (lo, hi) = map_span(a.row.min(b.row), a.row.max(b.row), *at, *count, insert, MAX_ROWS)?;
180                    RefKind::Range(Anchor { row: lo, ..*a }, Anchor { row: hi, ..*b })
181                }
182                (RefKind::Range(a, b), Axis::Cols) => {
183                    let (lo, hi) = map_span(a.col.min(b.col), a.col.max(b.col), *at, *count, insert, MAX_COLS)?;
184                    RefKind::Range(Anchor { col: lo, ..*a }, Anchor { col: hi, ..*b })
185                }
186                (RefKind::Rows(r0, a0, r1, a1), Axis::Rows) => {
187                    let (lo, hi) = map_span(*r0.min(r1), *r0.max(r1), *at, *count, insert, MAX_ROWS)?;
188                    RefKind::Rows(lo, *a0, hi, *a1)
189                }
190                (RefKind::Cols(c0, a0, c1, a1), Axis::Cols) => {
191                    let (lo, hi) = map_span(*c0.min(c1), *c0.max(c1), *at, *count, insert, MAX_COLS)?;
192                    RefKind::Cols(lo, *a0, hi, *a1)
193                }
194                (k, _) => k.clone(),
195            };
196            Some(Reference { sheet: r.sheet.clone(), kind })
197        }
198        Edit::InsertCells { axis, range } | Edit::DeleteCells { axis, range } => {
199            // Like inserting/deleting lines, for references that lie within the shifted
200            // columns (rows); Excel leaves references reaching outside them as they are.
201            let span = r.range();
202            let (inside, at, count) = match axis {
203                Axis::Rows => (range.start.col <= span.start.col && span.end.col <= range.end.col, range.start.row, range.height()),
204                Axis::Cols => (range.start.row <= span.start.row && span.end.row <= range.end.row, range.start.col, range.width()),
205            };
206            if !inside {
207                return Some(r.clone());
208            }
209            let axis = *axis;
210            let lines = if matches!(edit, Edit::InsertCells { .. }) { Edit::Insert { axis, at, count } } else { Edit::Delete { axis, at, count } };
211            adjust_ref(r, &lines)
212        }
213        Edit::Move { from, to_row, to_col } => {
214            let dr = *to_row as i64 - from.start.row as i64;
215            let dc = *to_col as i64 - from.start.col as i64;
216            let range = r.range();
217            if !from.contains_range(&range) {
218                return Some(r.clone());
219            }
220            let mv = |a: &Anchor| -> Option<Anchor> {
221                let (row, col) = clamp_anchor(a.row as i64 + dr, a.col as i64 + dc)?;
222                Some(Anchor { row, col, ..*a })
223            };
224            let kind = match &r.kind {
225                RefKind::Cell(a) => RefKind::Cell(mv(a)?),
226                RefKind::Range(a, b) => RefKind::Range(mv(a)?, mv(b)?),
227                RefKind::Rows(a, aa, b, ba) => {
228                    let dr = *to_row as i64 - from.start.row as i64;
229                    RefKind::Rows(shift_line(*a, false, dr, MAX_ROWS)?, *aa, shift_line(*b, false, dr, MAX_ROWS)?, *ba)
230                }
231                RefKind::Cols(a, aa, b, ba) => {
232                    let dc = *to_col as i64 - from.start.col as i64;
233                    RefKind::Cols(shift_line(*a, false, dc, MAX_COLS)?, *aa, shift_line(*b, false, dc, MAX_COLS)?, *ba)
234                }
235            };
236            Some(Reference { sheet: r.sheet.clone(), kind })
237        }
238        Edit::MoveToSheet { from, to_sheet, to_row, to_col } => {
239            if !from.contains_range(&r.range()) {
240                return Some(r.clone());
241            }
242            let mut moved = adjust_ref(r, &Edit::Move { from: *from, to_row: *to_row, to_col: *to_col })?;
243            moved.sheet = SheetSel::Named(to_sheet.clone());
244            Some(moved)
245        }
246    }
247}
248
249/// Renames sheet references `old` → `new`.
250pub fn rename_sheet(e: Expr, old: &str, new: &str) -> Expr {
251    e.map(&mut |x| match x {
252        Expr::Ref(mut r) => {
253            match &mut r.sheet {
254                SheetSel::Named(n) if n.eq_ignore_ascii_case(old) => *n = new.to_string(),
255                SheetSel::Span(a, b) => {
256                    if a.eq_ignore_ascii_case(old) {
257                        *a = new.to_string();
258                    }
259                    if b.eq_ignore_ascii_case(old) {
260                        *b = new.to_string();
261                    }
262                }
263                _ => {}
264            }
265            Expr::Ref(r)
266        }
267        // Sheet-qualified defined name `Old!MyName` is stored as one string, with the bare sheet name as the parser
268        // stores it (the printer adds the quotes).
269        Expr::Name(n) => match n.split_at_checked(old.len()) {
270            Some((head, rest)) if head.eq_ignore_ascii_case(old) && rest.starts_with('!') => Expr::Name(format!("{new}{rest}")),
271            _ => Expr::Name(n),
272        },
273        other => other,
274    })
275}
276
277/// References to a deleted sheet become `#REF!`. `order` lists the sheet names (before the deletion) in tab order:
278/// a 3-D span that loses an end sheet shrinks to the neighbouring surviving sheet, as in Excel.
279pub fn delete_sheet(e: Expr, name: &str, order: &[String]) -> Expr {
280    let pos = |n: &str| order.iter().position(|o| o.eq_ignore_ascii_case(n));
281    e.map(&mut |x| match x {
282        Expr::Ref(r) if r.sheet_name().is_some_and(|n| n.eq_ignore_ascii_case(name)) => Expr::Error(CellError::Ref),
283        Expr::Ref(mut r) => {
284            if let SheetSel::Span(a, b) = &r.sheet
285                && (a.eq_ignore_ascii_case(name) || b.eq_ignore_ascii_case(name))
286            {
287                let (Some(ia), Some(ib)) = (pos(a), pos(b)) else { return Expr::Ref(r) };
288                if ia == ib {
289                    return Expr::Error(CellError::Ref);
290                }
291                // The deleted end moves one sheet towards the other end; the span keeps its direction.
292                let inward = |from: usize, to: usize| order.get(if from < to { from + 1 } else { from.saturating_sub(1) });
293                let (na, nb) = if a.eq_ignore_ascii_case(name) { (inward(ia, ib), order.get(ib)) } else { (order.get(ia), inward(ib, ia)) };
294                let (Some(na), Some(nb)) = (na, nb) else { return Expr::Error(CellError::Ref) };
295                r.sheet = if na.eq_ignore_ascii_case(nb) { SheetSel::Named(na.clone()) } else { SheetSel::Span(na.clone(), nb.clone()) };
296            }
297            Expr::Ref(r)
298        }
299        other => other,
300    })
301}
ravenrs_sheet/formula/adjust.rs

ravenrs_sheet/formula/
adjust.rs
