Skip to main content

sheetkit_core/workbook/
cell_ops.rs

1use super::*;
2
3impl Workbook {
4    /// Get the value of a cell.
5    ///
6    /// Returns [`CellValue::Empty`] for cells that have no value or do not
7    /// exist in the sheet data.
8    pub fn get_cell_value(&self, sheet: &str, cell: &str) -> Result<CellValue> {
9        self.get_cell_value_with_limits(sheet, cell, crate::traversal::TraversalLimits::default())
10    }
11
12    /// Gets one cell value with an explicit retained-value limit.
13    pub fn get_cell_value_with_limits(
14        &self,
15        sheet: &str,
16        cell: &str,
17        limits: crate::traversal::TraversalLimits,
18    ) -> Result<CellValue> {
19        let ws = self.worksheet_ref(sheet)?;
20
21        let (col, row) = cell_name_to_coordinates(cell)?;
22
23        // Find the row via binary search (rows are sorted by row number).
24        let xml_row = match ws.sheet_data.rows.binary_search_by_key(&row, |r| r.r) {
25            Ok(idx) => &ws.sheet_data.rows[idx],
26            Err(_) => return Ok(CellValue::Empty),
27        };
28
29        // Find the cell via binary search on cached column number.
30        let xml_cell = match xml_row.cells.binary_search_by_key(&col, |c| c.col) {
31            Ok(idx) => &xml_row.cells[idx],
32            Err(_) => return Ok(CellValue::Empty),
33        };
34
35        let retained_bytes = u64::try_from(std::mem::size_of::<CellValue>())
36            .unwrap_or(u64::MAX)
37            .saturating_add(crate::row::cell_cloned_payload_bytes(
38                xml_cell,
39                &self.sst_runtime,
40            ));
41        crate::traversal::check_limit(
42            crate::resource::ResourceDimension::RetainedBytes,
43            limits.max_output_bytes(),
44            retained_bytes,
45        )?;
46        let mut value = self.xml_cell_to_value(xml_cell)?;
47        let shared_index = xml_cell.f.as_deref().and_then(|f| f.si);
48        crate::raw_transfer::SharedFollowers::new(ws, limits, retained_bytes, shared_index)?
49            .resolve(&mut value, xml_cell)?;
50        Ok(value)
51    }
52
53    /// Set the value of a cell.
54    ///
55    /// The value can be any type that implements `Into<CellValue>`, including
56    /// `&str`, `String`, `f64`, `i32`, `i64`, and `bool`.
57    ///
58    /// Setting a cell to [`CellValue::Empty`] removes the cell from the row.
59    pub fn set_cell_value(
60        &mut self,
61        sheet: &str,
62        cell: &str,
63        value: impl Into<CellValue>,
64    ) -> Result<()> {
65        let value = value.into();
66        self.sheet_index(sheet)?;
67        cell_name_to_coordinates(cell)?;
68        validate_formula_values_for_mutation(
69            std::iter::once(&value),
70            crate::formula::FormulaLimits::secure_default(),
71        )?;
72        self.set_cell_value_prevalidated(sheet, cell, value)
73    }
74
75    fn set_cell_value_prevalidated(
76        &mut self,
77        sheet: &str,
78        cell: &str,
79        value: CellValue,
80    ) -> Result<()> {
81        let date_style = if matches!(value, CellValue::Date(_)) {
82            let mut style = self
83                .get_cell_style(sheet, cell)?
84                .and_then(|id| crate::style::get_style(&self.stylesheet, id))
85                .unwrap_or_default();
86            style.num_fmt = Some(crate::style::NumFmtStyle::Builtin(
87                crate::style::builtin_num_fmts::DATE_MDY,
88            ));
89            Some(self.add_style(&style)?)
90        } else {
91            None
92        };
93
94        // Validate string length.
95        if let CellValue::String(ref s) = value {
96            if s.len() > MAX_CELL_CHARS {
97                return Err(Error::CellValueTooLong {
98                    length: s.len(),
99                    max: MAX_CELL_CHARS,
100                });
101            }
102        }
103
104        let sheet_idx = self.sheet_index(sheet)?;
105        self.reject_streamed_mutation(sheet_idx)?;
106        self.ensure_hydrated(sheet_idx)?;
107        self.mark_sheet_dirty(sheet_idx);
108
109        let (col, row_num) = cell_name_to_coordinates(cell)?;
110        let cell_ref = crate::utils::cell_ref::compact_cell_ref(col, row_num)?;
111
112        let ws = self.worksheets[sheet_idx].1.get_mut().unwrap();
113
114        // Find or create the row via binary search (rows are sorted by row number).
115        let row_idx = match ws.sheet_data.rows.binary_search_by_key(&row_num, |r| r.r) {
116            Ok(idx) => idx,
117            Err(idx) => {
118                ws.sheet_data.rows.insert(idx, new_row(row_num));
119                idx
120            }
121        };
122
123        let row = &mut ws.sheet_data.rows[row_idx];
124
125        // Handle Empty: remove the cell if present.
126        if value == CellValue::Empty {
127            if let Ok(idx) = row.cells.binary_search_by_key(&col, |c| c.col) {
128                row.cells.remove(idx);
129            }
130            return Ok(());
131        }
132
133        // Find or create the cell via binary search on cached column number.
134        let cell_idx = match row.cells.binary_search_by_key(&col, |c| c.col) {
135            Ok(idx) => idx,
136            Err(insert_pos) => {
137                row.cells.insert(
138                    insert_pos,
139                    Cell {
140                        r: cell_ref,
141                        col,
142                        s: None,
143                        t: CellTypeTag::None,
144                        v: None,
145                        f: None,
146                        is: None,
147                    },
148                );
149                insert_pos
150            }
151        };
152
153        let xml_cell = &mut row.cells[cell_idx];
154        value_to_xml_cell(&mut self.sst_runtime, xml_cell, value);
155        if let Some(style_id) = date_style {
156            xml_cell.s = Some(style_id);
157        }
158
159        Ok(())
160    }
161
162    /// Convert an XML Cell to a CellValue.
163    pub(crate) fn xml_cell_to_value(&self, xml_cell: &Cell) -> Result<CellValue> {
164        // Check for formula first.
165        if let Some(ref formula) = xml_cell.f {
166            let expr = formula.value.clone().unwrap_or_default();
167            let result = match (xml_cell.t, &xml_cell.v) {
168                (CellTypeTag::Boolean, Some(v)) => Some(Box::new(CellValue::Bool(v == "1"))),
169                (CellTypeTag::Error, Some(v)) => Some(Box::new(CellValue::Error(v.clone()))),
170                (CellTypeTag::FormulaString, Some(v)) => {
171                    Some(Box::new(CellValue::String(v.clone())))
172                }
173                (CellTypeTag::Date, Some(v)) => {
174                    Some(Box::new(CellValue::Date(parse_date_cell_value(v)?)))
175                }
176                (_, Some(v)) => v
177                    .parse::<f64>()
178                    .ok()
179                    .map(|n| Box::new(CellValue::Number(n))),
180                _ => None,
181            };
182            return Ok(CellValue::Formula { expr, result });
183        }
184
185        let cell_value = xml_cell.v.as_deref();
186
187        match (xml_cell.t, cell_value) {
188            // Shared string
189            (CellTypeTag::SharedString, Some(v)) => {
190                let idx: usize = v
191                    .parse()
192                    .map_err(|_| Error::Internal(format!("invalid SST index: {v}")))?;
193                let s = self
194                    .sst_runtime
195                    .get(idx)
196                    .ok_or_else(|| Error::Internal(format!("SST index {idx} out of bounds")))?;
197                Ok(CellValue::String(s.to_string()))
198            }
199            // Boolean
200            (CellTypeTag::Boolean, Some(v)) => Ok(CellValue::Bool(v == "1")),
201            // Error
202            (CellTypeTag::Error, Some(v)) => Ok(CellValue::Error(v.to_string())),
203            // Inline string
204            (CellTypeTag::InlineString, _) => {
205                let s = xml_cell
206                    .is
207                    .as_ref()
208                    .and_then(|is| is.t.clone())
209                    .unwrap_or_default();
210                Ok(CellValue::String(s))
211            }
212            // Formula string (cached string result)
213            (CellTypeTag::FormulaString, Some(v)) => Ok(CellValue::String(v.to_string())),
214            // ISO 8601 date/time values are used by explicit OOXML `t="d"`
215            // cells. Numeric serials are accepted for interoperability.
216            (CellTypeTag::Date, Some(v)) => Ok(CellValue::Date(parse_date_cell_value(v)?)),
217            // Number (explicit or default type) -- may be a date if styled
218            // and `DateInterpretation::NumFmt` is active.
219            (CellTypeTag::None | CellTypeTag::Number, Some(v)) => {
220                let n: f64 = v
221                    .parse()
222                    .map_err(|_| Error::Internal(format!("invalid number: {v}")))?;
223                if matches!(
224                    self.date_interpretation,
225                    super::open_options::DateInterpretation::NumFmt
226                ) && self.is_date_styled_cell(xml_cell)
227                {
228                    return Ok(CellValue::Date(n));
229                }
230                Ok(CellValue::Number(n))
231            }
232            // No value
233            _ => Ok(CellValue::Empty),
234        }
235    }
236
237    /// Check whether a cell's style indicates a date/time number format.
238    pub(crate) fn is_date_styled_cell(&self, xml_cell: &Cell) -> bool {
239        let style_idx = match xml_cell.s {
240            Some(idx) => idx as usize,
241            None => return false,
242        };
243        let xf = match self.stylesheet.cell_xfs.xfs.get(style_idx) {
244            Some(xf) => xf,
245            None => return false,
246        };
247        let num_fmt_id = xf.num_fmt_id.unwrap_or(0);
248        // Check built-in date format IDs.
249        if crate::cell::is_date_num_fmt(num_fmt_id) {
250            return true;
251        }
252        // Check custom number formats for date patterns.
253        if num_fmt_id >= 164 {
254            if let Some(ref num_fmts) = self.stylesheet.num_fmts {
255                if let Some(nf) = num_fmts
256                    .num_fmts
257                    .iter()
258                    .find(|nf| nf.num_fmt_id == num_fmt_id)
259                {
260                    return crate::cell::is_date_format_code(&nf.format_code);
261                }
262            }
263        }
264        false
265    }
266
267    /// Get the formatted display text for a cell, applying its number format.
268    ///
269    /// If the cell has a style with a number format, the raw numeric value is
270    /// formatted according to that format code. String and boolean cells return
271    /// their default display text. Empty cells return an empty string.
272    pub fn get_cell_formatted_value(&self, sheet: &str, cell: &str) -> Result<String> {
273        let ws = self.worksheet_ref(sheet)?;
274        let (col, row) = cell_name_to_coordinates(cell)?;
275
276        let xml_row = match ws.sheet_data.rows.binary_search_by_key(&row, |r| r.r) {
277            Ok(idx) => &ws.sheet_data.rows[idx],
278            Err(_) => return Ok(String::new()),
279        };
280
281        let xml_cell = match xml_row.cells.binary_search_by_key(&col, |c| c.col) {
282            Ok(idx) => &xml_row.cells[idx],
283            Err(_) => return Ok(String::new()),
284        };
285
286        let cell_value = self.xml_cell_to_value(xml_cell)?;
287
288        let numeric_val = match &cell_value {
289            CellValue::Number(n) => Some(*n),
290            CellValue::Date(n) => Some(*n),
291            CellValue::Formula {
292                result: Some(boxed),
293                ..
294            } => match boxed.as_ref() {
295                CellValue::Number(n) => Some(*n),
296                CellValue::Date(n) => Some(*n),
297                _ => None,
298            },
299            _ => None,
300        };
301
302        if let Some(val) = numeric_val {
303            if let Some(format_code) = self.cell_format_code(xml_cell) {
304                return Ok(crate::numfmt::format_number(val, &format_code));
305            }
306        }
307
308        Ok(cell_value.to_string())
309    }
310
311    /// Get the number format code string for a cell from its style.
312    /// Returns `None` if the cell has no style or the default "General" format.
313    pub(crate) fn cell_format_code(&self, xml_cell: &Cell) -> Option<String> {
314        let style_idx = xml_cell.s? as usize;
315        let xf = self.stylesheet.cell_xfs.xfs.get(style_idx)?;
316        let num_fmt_id = xf.num_fmt_id.unwrap_or(0);
317
318        if num_fmt_id == 0 {
319            return None;
320        }
321
322        // Try built-in format
323        if let Some(code) = crate::numfmt::builtin_format_code(num_fmt_id) {
324            return Some(code.to_string());
325        }
326
327        // Try custom format
328        if let Some(ref num_fmts) = self.stylesheet.num_fmts {
329            if let Some(nf) = num_fmts
330                .num_fmts
331                .iter()
332                .find(|nf| nf.num_fmt_id == num_fmt_id)
333            {
334                return Some(nf.format_code.clone());
335            }
336        }
337
338        None
339    }
340
341    /// Register a new style and return its ID.
342    ///
343    /// The style is deduplicated: if an identical style already exists in
344    /// the stylesheet, the existing ID is returned.
345    pub fn add_style(&mut self, style: &crate::style::Style) -> Result<u32> {
346        crate::style::add_style(&mut self.stylesheet, style)
347    }
348
349    /// Get the style ID applied to a cell.
350    ///
351    /// Returns `None` if the cell does not exist or has no explicit style
352    /// (i.e. uses the default style 0).
353    pub fn get_cell_style(&self, sheet: &str, cell: &str) -> Result<Option<u32>> {
354        let ws = self.worksheet_ref(sheet)?;
355
356        let (col, row) = cell_name_to_coordinates(cell)?;
357
358        // Find the row via binary search.
359        let xml_row = match ws.sheet_data.rows.binary_search_by_key(&row, |r| r.r) {
360            Ok(idx) => &ws.sheet_data.rows[idx],
361            Err(_) => return Ok(None),
362        };
363
364        // Find the cell via binary search on cached column number.
365        let xml_cell = match xml_row.cells.binary_search_by_key(&col, |c| c.col) {
366            Ok(idx) => &xml_row.cells[idx],
367            Err(_) => return Ok(None),
368        };
369
370        Ok(xml_cell.s)
371    }
372
373    /// Set the style ID for a cell.
374    ///
375    /// If the cell does not exist, an empty cell with just the style is created.
376    /// The `style_id` must be a valid index in cellXfs.
377    pub fn set_cell_style(&mut self, sheet: &str, cell: &str, style_id: u32) -> Result<()> {
378        // Validate the style_id.
379        if style_id as usize >= self.stylesheet.cell_xfs.xfs.len() {
380            return Err(Error::StyleNotFound { id: style_id });
381        }
382
383        let sheet_idx = self.sheet_index(sheet)?;
384        let ws = self.worksheet_mut_by_index(sheet_idx)?;
385
386        let (col, row_num) = cell_name_to_coordinates(cell)?;
387        let cell_ref = crate::utils::cell_ref::compact_cell_ref(col, row_num)?;
388
389        // Find or create the row via binary search.
390        let row_idx = match ws.sheet_data.rows.binary_search_by_key(&row_num, |r| r.r) {
391            Ok(idx) => idx,
392            Err(idx) => {
393                ws.sheet_data.rows.insert(idx, new_row(row_num));
394                idx
395            }
396        };
397
398        let row = &mut ws.sheet_data.rows[row_idx];
399
400        // Find or create the cell via binary search on cached column number.
401        let cell_idx = match row.cells.binary_search_by_key(&col, |c| c.col) {
402            Ok(idx) => idx,
403            Err(insert_pos) => {
404                row.cells.insert(
405                    insert_pos,
406                    Cell {
407                        r: cell_ref,
408                        col,
409                        s: None,
410                        t: CellTypeTag::None,
411                        v: None,
412                        f: None,
413                        is: None,
414                    },
415                );
416                insert_pos
417            }
418        };
419
420        row.cells[cell_idx].s = Some(style_id);
421        Ok(())
422    }
423
424    /// Merge a range of cells on the given sheet.
425    ///
426    /// `top_left` and `bottom_right` are cell references like "A1" and "C3".
427    /// Returns an error if the range overlaps with an existing merge region.
428    pub fn merge_cells(&mut self, sheet: &str, top_left: &str, bottom_right: &str) -> Result<()> {
429        let ws = self.worksheet_mut(sheet)?;
430        crate::merge::merge_cells(ws, top_left, bottom_right)
431    }
432
433    /// Remove a merged cell range from the given sheet.
434    ///
435    /// `reference` is the exact range string like "A1:C3".
436    pub fn unmerge_cell(&mut self, sheet: &str, reference: &str) -> Result<()> {
437        let ws = self.worksheet_mut(sheet)?;
438        crate::merge::unmerge_cell(ws, reference)
439    }
440
441    /// Get all merged cell ranges on the given sheet.
442    ///
443    /// Returns a list of range strings like `["A1:B2", "D1:F3"]`.
444    pub fn get_merge_cells(&self, sheet: &str) -> Result<Vec<String>> {
445        let ws = self.worksheet_ref(sheet)?;
446        Ok(crate::merge::get_merge_cells(ws))
447    }
448
449    /// Set a formula on a cell.
450    ///
451    /// This is a convenience wrapper around [`Self::set_cell_value`] with
452    /// [`CellValue::Formula`].
453    pub fn set_cell_formula(&mut self, sheet: &str, cell: &str, formula: &str) -> Result<()> {
454        self.set_cell_formula_with_limits(
455            sheet,
456            cell,
457            formula,
458            crate::formula::FormulaLimits::secure_default(),
459        )
460    }
461
462    /// Set a formula using caller-selected finite limits for this mutation.
463    pub fn set_cell_formula_with_limits(
464        &mut self,
465        sheet: &str,
466        cell: &str,
467        formula: &str,
468        limits: crate::formula::FormulaLimits,
469    ) -> Result<()> {
470        let sheet_idx = self.sheet_index(sheet)?;
471        self.reject_streamed_mutation(sheet_idx)?;
472        cell_name_to_coordinates(cell)?;
473        let budget = crate::formula::FormulaBudget::for_mutation(limits)?;
474        budget.charge_mutation_cells(1)?;
475        let formula_bytes = u64::try_from(formula.len()).unwrap_or(u64::MAX);
476        budget.charge_mutation_formula(formula_bytes, formula_bytes)?;
477        let formula = fallible_formula_copy(formula)?;
478        self.set_cell_value_prevalidated(
479            sheet,
480            cell,
481            CellValue::Formula {
482                expr: formula,
483                result: None,
484            },
485        )
486    }
487
488    /// Fill a range of cells with a formula, adjusting row references for each
489    /// row relative to the first cell in the range.
490    ///
491    /// `range` is an A1-style range like `"D2:D10"`. The `formula` is the base
492    /// formula for the first cell of the range. For each subsequent row, the
493    /// row references in the formula are shifted by the row offset. Absolute
494    /// row references (`$1`) are left unchanged.
495    pub fn fill_formula(&mut self, sheet: &str, range: &str, formula: &str) -> Result<()> {
496        self.fill_formula_with_limits(
497            sheet,
498            range,
499            formula,
500            crate::formula::FormulaLimits::secure_default(),
501        )
502    }
503
504    /// Fill a single-column range using caller-selected finite limits.
505    pub fn fill_formula_with_limits(
506        &mut self,
507        sheet: &str,
508        range: &str,
509        formula: &str,
510        limits: crate::formula::FormulaLimits,
511    ) -> Result<()> {
512        let sheet_idx = self.sheet_index(sheet)?;
513        self.reject_streamed_mutation(sheet_idx)?;
514        let Some((start, end)) = range.split_once(':') else {
515            return Err(Error::InvalidCellReference(
516                "invalid fill formula range".to_string(),
517            ));
518        };
519        if end.contains(':') {
520            return Err(Error::InvalidCellReference(
521                "invalid fill formula range".to_string(),
522            ));
523        }
524        let (start_col, start_row) = cell_name_to_coordinates(start)?;
525        let (end_col, end_row) = cell_name_to_coordinates(end)?;
526
527        if start_col != end_col {
528            return Err(Error::InvalidCellReference(
529                "fill_formula only supports single-column ranges".to_string(),
530            ));
531        }
532        if start_row > end_row {
533            return Err(Error::InvalidCellReference(
534                "fill_formula range must be ascending".to_string(),
535            ));
536        }
537
538        let cell_count = u64::from(end_row - start_row) + 1;
539        let budget = crate::formula::FormulaBudget::for_mutation(limits)?;
540        budget.charge_mutation_cells(cell_count)?;
541        let input_bytes = u64::try_from(formula.len()).unwrap_or(u64::MAX);
542        budget.check_mutation_input(input_bytes)?;
543        let scan_count = cell_count.saturating_mul(3).saturating_sub(2);
544        let cumulative_input = input_bytes.saturating_mul(scan_count);
545        budget.charge_mutation_input_total(cumulative_input)?;
546
547        let mut cumulative_output = 0u64;
548        for row in start_row..=end_row {
549            let row_offset = row as i32 - start_row as i32;
550            let shifted_len = if row_offset == 0 {
551                formula.len()
552            } else {
553                crate::cell_ref_shift::shifted_cell_references_len_with_abs(
554                    formula,
555                    |col, r, _abs_col, abs_row| {
556                        if abs_row {
557                            (col, r)
558                        } else {
559                            (col, (r as i32 + row_offset) as u32)
560                        }
561                    },
562                )?
563            };
564            cumulative_output =
565                cumulative_output.saturating_add(u64::try_from(shifted_len).unwrap_or(u64::MAX));
566        }
567        budget.charge_mutation_output(cumulative_output)?;
568
569        let capacity = usize::try_from(cell_count)
570            .map_err(|_| Error::Internal("formula fill range is too large".to_string()))?;
571        let mut staged = Vec::new();
572        staged
573            .try_reserve_exact(capacity)
574            .map_err(|_| Error::Internal("formula fill allocation failed".to_string()))?;
575
576        for row in start_row..=end_row {
577            let row_offset = row as i32 - start_row as i32;
578            let adjusted = if row_offset == 0 {
579                fallible_formula_copy(formula)?
580            } else {
581                let shifted_len = crate::cell_ref_shift::shifted_cell_references_len_with_abs(
582                    formula,
583                    |col, r, _abs_col, abs_row| {
584                        if abs_row {
585                            (col, r)
586                        } else {
587                            (col, (r as i32 + row_offset) as u32)
588                        }
589                    },
590                )?;
591                crate::cell_ref_shift::shift_cell_references_with_abs_preallocated(
592                    formula,
593                    shifted_len,
594                    |col, r, _abs_col, abs_row| {
595                        if abs_row {
596                            (col, r)
597                        } else {
598                            (col, (r as i32 + row_offset) as u32)
599                        }
600                    },
601                )?
602            };
603            let cell_ref = crate::utils::cell_ref::coordinates_to_cell_name(start_col, row)?;
604            staged.push((cell_ref, adjusted));
605        }
606
607        self.ensure_hydrated(sheet_idx)?;
608        for (cell_ref, adjusted) in staged {
609            self.set_cell_value_prevalidated(
610                sheet,
611                &cell_ref,
612                CellValue::Formula {
613                    expr: adjusted,
614                    result: None,
615                },
616            )?;
617        }
618        Ok(())
619    }
620
621    /// Set a cell to a rich text value (multiple formatted runs).
622    pub fn set_cell_rich_text(
623        &mut self,
624        sheet: &str,
625        cell: &str,
626        runs: Vec<crate::rich_text::RichTextRun>,
627    ) -> Result<()> {
628        self.set_cell_value(sheet, cell, CellValue::RichString(runs))
629    }
630
631    /// Get rich text runs for a cell, if it contains rich text.
632    ///
633    /// Returns `None` if the cell is empty, contains a plain string, or holds
634    /// a non-string value.
635    pub fn get_cell_rich_text(
636        &self,
637        sheet: &str,
638        cell: &str,
639    ) -> Result<Option<Vec<crate::rich_text::RichTextRun>>> {
640        let (col, row) = cell_name_to_coordinates(cell)?;
641        let ws = self.worksheet_ref(sheet)?;
642
643        // Binary search for the row.
644        let xml_row = match ws.sheet_data.rows.binary_search_by_key(&row, |r| r.r) {
645            Ok(idx) => &ws.sheet_data.rows[idx],
646            Err(_) => return Ok(None),
647        };
648
649        // Binary search for the cell by column.
650        let xml_cell = match xml_row.cells.binary_search_by_key(&col, |c| c.col) {
651            Ok(idx) => &xml_row.cells[idx],
652            Err(_) => return Ok(None),
653        };
654
655        if xml_cell.t == CellTypeTag::SharedString {
656            if let Some(ref v) = xml_cell.v {
657                if let Ok(idx) = v.parse::<usize>() {
658                    return Ok(self.sst_runtime.get_rich_text(idx));
659                }
660            }
661        }
662        Ok(None)
663    }
664
665    /// Set multiple cell values at once. Each entry is a (cell_ref, value) pair.
666    ///
667    /// This is more efficient than calling `set_cell_value` repeatedly from
668    /// FFI because it crosses the language boundary only once.
669    pub fn set_cell_values(
670        &mut self,
671        sheet: &str,
672        entries: Vec<(String, CellValue)>,
673    ) -> Result<()> {
674        let sheet_idx = self.sheet_index(sheet)?;
675        self.reject_streamed_mutation(sheet_idx)?;
676        validate_formula_values_for_mutation(
677            entries.iter().map(|(_, value)| value),
678            crate::formula::FormulaLimits::secure_default(),
679        )?;
680        for (cell, value) in &entries {
681            validate_stored_string(value)?;
682            cell_name_to_coordinates(cell)?;
683        }
684        self.set_cell_values_prevalidated(sheet_idx, entries)
685    }
686
687    /// Apply an opaque raw transfer without resetting its formula budget.
688    pub fn set_validated_cell_transfer(
689        &mut self,
690        sheet: &str,
691        transfer: crate::raw_transfer_write::ValidatedCellTransfer,
692    ) -> Result<()> {
693        let sheet_idx = self.sheet_index(sheet)?;
694        self.reject_streamed_mutation(sheet_idx)?;
695        self.set_cell_values_prevalidated(sheet_idx, transfer.entries)
696    }
697
698    fn set_cell_values_prevalidated(
699        &mut self,
700        sheet_idx: usize,
701        entries: Vec<(String, CellValue)>,
702    ) -> Result<()> {
703        self.ensure_hydrated(sheet_idx)?;
704        self.mark_sheet_dirty(sheet_idx);
705
706        for (cell, value) in entries {
707            let (col, row_num) = cell_name_to_coordinates(&cell)?;
708            let cell_ref = crate::utils::cell_ref::compact_cell_ref(col, row_num)?;
709
710            let row_idx = {
711                let ws = self.worksheets[sheet_idx].1.get_mut().unwrap();
712                match ws.sheet_data.rows.binary_search_by_key(&row_num, |r| r.r) {
713                    Ok(idx) => idx,
714                    Err(idx) => {
715                        ws.sheet_data.rows.insert(idx, new_row(row_num));
716                        idx
717                    }
718                }
719            };
720
721            if value == CellValue::Empty {
722                let row = &mut self.worksheets[sheet_idx]
723                    .1
724                    .get_mut()
725                    .unwrap()
726                    .sheet_data
727                    .rows[row_idx];
728                if let Ok(idx) = row.cells.binary_search_by_key(&col, |c| c.col) {
729                    row.cells.remove(idx);
730                }
731                continue;
732            }
733
734            let cell_idx = {
735                let row = &mut self.worksheets[sheet_idx]
736                    .1
737                    .get_mut()
738                    .unwrap()
739                    .sheet_data
740                    .rows[row_idx];
741                match row.cells.binary_search_by_key(&col, |c| c.col) {
742                    Ok(idx) => idx,
743                    Err(pos) => {
744                        row.cells.insert(
745                            pos,
746                            Cell {
747                                r: cell_ref,
748                                col,
749                                s: None,
750                                t: CellTypeTag::None,
751                                v: None,
752                                f: None,
753                                is: None,
754                            },
755                        );
756                        pos
757                    }
758                }
759            };
760
761            let xml_cell = &mut self.worksheets[sheet_idx]
762                .1
763                .get_mut()
764                .unwrap()
765                .sheet_data
766                .rows[row_idx]
767                .cells[cell_idx];
768            value_to_xml_cell(&mut self.sst_runtime, xml_cell, value);
769        }
770
771        Ok(())
772    }
773
774    /// Set a contiguous block of cell values from a 2D array.
775    ///
776    /// `data` is a row-major 2D array of values. `start_row` and `start_col`
777    /// are 1-based. The first value in `data[0][0]` maps to the cell at
778    /// `(start_col, start_row)`.
779    ///
780    /// This is the fastest way to populate a sheet from JS because it crosses
781    /// the FFI boundary only once for the entire dataset.
782    pub fn set_sheet_data(
783        &mut self,
784        sheet: &str,
785        data: Vec<Vec<CellValue>>,
786        start_row: u32,
787        start_col: u32,
788    ) -> Result<()> {
789        let sheet_idx = self.sheet_index(sheet)?;
790        let row_count = u32::try_from(data.len()).map_err(|_| Error::InvalidRowNumber(u32::MAX))?;
791        if row_count > 0 {
792            let last_row = start_row
793                .checked_add(row_count - 1)
794                .ok_or(Error::InvalidRowNumber(u32::MAX))?;
795            crate::utils::cell_ref::compact_cell_ref(start_col, last_row)?;
796        }
797
798        let max_cols = data.iter().map(Vec::len).max().unwrap_or(0);
799        if max_cols > 0 {
800            let width =
801                u32::try_from(max_cols).map_err(|_| Error::InvalidColumnNumber(u32::MAX))?;
802            let last_col = start_col
803                .checked_add(width - 1)
804                .ok_or(Error::InvalidColumnNumber(u32::MAX))?;
805            crate::utils::cell_ref::compact_cell_ref(last_col, start_row)?;
806        }
807
808        validate_formula_values_for_mutation(
809            data.iter().flat_map(|row| row.iter()),
810            crate::formula::FormulaLimits::secure_default(),
811        )?;
812        for value in data.iter().flat_map(|row| row.iter()) {
813            validate_stored_string(value)?;
814        }
815
816        // This API deliberately replaces streamed backing with typed data.
817        self.invalidate_streamed(sheet_idx);
818        self.ensure_hydrated(sheet_idx)?;
819        self.mark_sheet_dirty(sheet_idx);
820
821        for (row_offset, row_values) in data.into_iter().enumerate() {
822            let row_num = start_row + row_offset as u32;
823
824            let row_idx = {
825                let ws = self.worksheets[sheet_idx].1.get_mut().unwrap();
826                match ws.sheet_data.rows.binary_search_by_key(&row_num, |r| r.r) {
827                    Ok(idx) => idx,
828                    Err(idx) => {
829                        ws.sheet_data.rows.insert(idx, new_row(row_num));
830                        idx
831                    }
832                }
833            };
834
835            for (col_offset, value) in row_values.into_iter().enumerate() {
836                let col = start_col + col_offset as u32;
837
838                if value == CellValue::Empty {
839                    let row = &mut self.worksheets[sheet_idx]
840                        .1
841                        .get_mut()
842                        .unwrap()
843                        .sheet_data
844                        .rows[row_idx];
845                    if let Ok(idx) = row.cells.binary_search_by_key(&col, |c| c.col) {
846                        row.cells.remove(idx);
847                    }
848                    continue;
849                }
850
851                let cell_ref = crate::utils::cell_ref::compact_cell_ref(col, row_num)?;
852
853                let cell_idx = {
854                    let row = &mut self.worksheets[sheet_idx]
855                        .1
856                        .get_mut()
857                        .unwrap()
858                        .sheet_data
859                        .rows[row_idx];
860                    match row.cells.binary_search_by_key(&col, |c| c.col) {
861                        Ok(idx) => idx,
862                        Err(pos) => {
863                            row.cells.insert(
864                                pos,
865                                Cell {
866                                    r: cell_ref,
867                                    col,
868                                    s: None,
869                                    t: CellTypeTag::None,
870                                    v: None,
871                                    f: None,
872                                    is: None,
873                                },
874                            );
875                            pos
876                        }
877                    }
878                };
879
880                let xml_cell = &mut self.worksheets[sheet_idx]
881                    .1
882                    .get_mut()
883                    .unwrap()
884                    .sheet_data
885                    .rows[row_idx]
886                    .cells[cell_idx];
887                value_to_xml_cell(&mut self.sst_runtime, xml_cell, value);
888            }
889        }
890
891        Ok(())
892    }
893
894    /// Set values in a single row starting from the given column.
895    ///
896    /// `row_num` is 1-based. `start_col` is 1-based.
897    /// Values are placed left-to-right starting at `start_col`.
898    pub fn set_row_values(
899        &mut self,
900        sheet: &str,
901        row_num: u32,
902        start_col: u32,
903        values: Vec<CellValue>,
904    ) -> Result<()> {
905        self.set_sheet_data(sheet, vec![values], row_num, start_col)
906    }
907}
908
909fn validate_formula_values_for_mutation<'a>(
910    values: impl IntoIterator<Item = &'a CellValue>,
911    limits: crate::formula::FormulaLimits,
912) -> Result<()> {
913    let budget = crate::formula::FormulaBudget::for_mutation(limits)?;
914    for value in values {
915        if let CellValue::Formula { expr, result } = value {
916            budget.charge_mutation_cells(1)?;
917            let input_bytes = u64::try_from(expr.len()).unwrap_or(u64::MAX);
918            let cached_output_bytes = result
919                .as_deref()
920                .map(formula_cached_result_output_len)
921                .transpose()?
922                .unwrap_or(0);
923            budget.charge_mutation_input(input_bytes)?;
924            budget.charge_mutation_output(input_bytes.saturating_add(cached_output_bytes))?;
925        }
926    }
927    Ok(())
928}
929
930fn fallible_formula_copy(formula: &str) -> Result<String> {
931    let mut copy = String::new();
932    copy.try_reserve_exact(formula.len())
933        .map_err(|_| Error::Internal("formula allocation failed".to_string()))?;
934    copy.push_str(formula);
935    Ok(copy)
936}
937
938fn validate_stored_string(value: &CellValue) -> Result<()> {
939    if let CellValue::String(value) = value {
940        if value.len() > MAX_CELL_CHARS {
941            return Err(Error::CellValueTooLong {
942                length: value.len(),
943                max: MAX_CELL_CHARS,
944            });
945        }
946    }
947    Ok(())
948}
949
950fn formula_cached_result_output_len(value: &CellValue) -> Result<u64> {
951    let length = match value {
952        CellValue::Empty => 0,
953        CellValue::Bool(_) => 1,
954        CellValue::Number(value) => value.to_string().len(),
955        CellValue::Date(serial) => crate::cell::serial_to_datetime(*serial)
956            .map(|value| value.format("%Y-%m-%dT%H:%M:%S").to_string().len())
957            .unwrap_or_else(|| serial.to_string().len()),
958        CellValue::String(value) | CellValue::Error(value) => {
959            if value.len() > MAX_CELL_CHARS {
960                return Err(Error::CellValueTooLong {
961                    length: value.len(),
962                    max: MAX_CELL_CHARS,
963                });
964            }
965            value.len()
966        }
967        CellValue::Formula { .. } | CellValue::RichString(_) => {
968            return Err(Error::Internal(
969                "unsupported formula cached result type".to_string(),
970            ));
971        }
972    };
973    u64::try_from(length)
974        .map_err(|_| Error::Internal("formula cached result is not addressable".to_string()))
975}
976
977fn parse_date_cell_value(value: &str) -> Result<f64> {
978    value
979        .parse::<f64>()
980        .ok()
981        .or_else(|| {
982            chrono::NaiveDateTime::parse_from_str(value, "%Y-%m-%dT%H:%M:%S%.f")
983                .ok()
984                .map(crate::cell::datetime_to_serial)
985        })
986        .or_else(|| {
987            chrono::NaiveDate::parse_from_str(value, "%Y-%m-%d")
988                .ok()
989                .map(crate::cell::date_to_serial)
990        })
991        .ok_or_else(|| Error::Internal(format!("invalid ISO date cell value: {value}")))
992}
993
994/// Write a CellValue into an XML Cell (mutating it in place).
995pub(crate) fn value_to_xml_cell(
996    sst: &mut SharedStringTable,
997    xml_cell: &mut Cell,
998    value: CellValue,
999) {
1000    // Clear previous values.
1001    xml_cell.t = CellTypeTag::None;
1002    xml_cell.v = None;
1003    xml_cell.f = None;
1004    xml_cell.is = None;
1005
1006    match value {
1007        CellValue::String(s) => {
1008            let idx = sst.add_owned(s);
1009            xml_cell.t = CellTypeTag::SharedString;
1010            xml_cell.v = Some(idx.to_string());
1011        }
1012        CellValue::Number(n) => {
1013            xml_cell.v = Some(n.to_string());
1014        }
1015        CellValue::Date(serial) => {
1016            // Dates are stored as numbers in Excel. The style must apply a
1017            // date number format for correct display.
1018            xml_cell.v = Some(serial.to_string());
1019        }
1020        CellValue::Bool(b) => {
1021            xml_cell.t = CellTypeTag::Boolean;
1022            xml_cell.v = Some(if b { "1" } else { "0" }.to_string());
1023        }
1024        CellValue::Formula { expr, result } => {
1025            xml_cell.f = Some(Box::new(CellFormula {
1026                t: None,
1027                reference: None,
1028                si: None,
1029                value: Some(expr),
1030            }));
1031            if let Some(result) = result {
1032                write_formula_cached_result(xml_cell, *result);
1033            }
1034        }
1035        CellValue::Error(e) => {
1036            xml_cell.t = CellTypeTag::Error;
1037            xml_cell.v = Some(e);
1038        }
1039        CellValue::Empty => {
1040            // Already cleared above; the caller should have removed the cell.
1041        }
1042        CellValue::RichString(runs) => {
1043            let idx = sst.add_rich_text(&runs);
1044            xml_cell.t = CellTypeTag::SharedString;
1045            xml_cell.v = Some(idx.to_string());
1046        }
1047    }
1048}
1049
1050fn write_formula_cached_result(xml_cell: &mut Cell, result: CellValue) {
1051    match result {
1052        CellValue::Empty | CellValue::Formula { .. } | CellValue::RichString(_) => {}
1053        CellValue::Bool(value) => {
1054            xml_cell.t = CellTypeTag::Boolean;
1055            xml_cell.v = Some(if value { "1" } else { "0" }.to_string());
1056        }
1057        CellValue::Number(value) => xml_cell.v = Some(value.to_string()),
1058        CellValue::Date(serial) => {
1059            xml_cell.t = CellTypeTag::Date;
1060            xml_cell.v = Some(
1061                crate::cell::serial_to_datetime(serial)
1062                    .map(|value| value.format("%Y-%m-%dT%H:%M:%S").to_string())
1063                    .unwrap_or_else(|| serial.to_string()),
1064            );
1065        }
1066        CellValue::String(value) => {
1067            xml_cell.t = CellTypeTag::FormulaString;
1068            xml_cell.v = Some(value);
1069        }
1070        CellValue::Error(value) => {
1071            xml_cell.t = CellTypeTag::Error;
1072            xml_cell.v = Some(value);
1073        }
1074    }
1075}
1076
1077/// Create a new empty row with the given 1-based row number.
1078pub(crate) fn new_row(row_num: u32) -> Row {
1079    Row {
1080        r: row_num,
1081        spans: None,
1082        s: None,
1083        custom_format: None,
1084        ht: None,
1085        hidden: None,
1086        custom_height: None,
1087        outline_level: None,
1088        cells: vec![],
1089    }
1090}
1091
1092#[cfg(test)]
1093mod tests {
1094    use super::*;
1095    use tempfile::TempDir;
1096
1097    #[test]
1098    fn test_set_and_get_string_value() {
1099        let mut wb = Workbook::new();
1100        wb.set_cell_value("Sheet1", "A1", "Hello").unwrap();
1101        let val = wb.get_cell_value("Sheet1", "A1").unwrap();
1102        assert_eq!(val, CellValue::String("Hello".to_string()));
1103    }
1104
1105    #[test]
1106    fn test_set_and_get_number_value() {
1107        let mut wb = Workbook::new();
1108        wb.set_cell_value("Sheet1", "B2", 42.5f64).unwrap();
1109        let val = wb.get_cell_value("Sheet1", "B2").unwrap();
1110        assert_eq!(val, CellValue::Number(42.5));
1111    }
1112
1113    #[test]
1114    fn test_set_and_get_bool_value() {
1115        let mut wb = Workbook::new();
1116        wb.set_cell_value("Sheet1", "C3", true).unwrap();
1117        let val = wb.get_cell_value("Sheet1", "C3").unwrap();
1118        assert_eq!(val, CellValue::Bool(true));
1119
1120        wb.set_cell_value("Sheet1", "D4", false).unwrap();
1121        let val = wb.get_cell_value("Sheet1", "D4").unwrap();
1122        assert_eq!(val, CellValue::Bool(false));
1123    }
1124
1125    #[test]
1126    fn test_set_value_sheet_not_found() {
1127        let mut wb = Workbook::new();
1128        let result = wb.set_cell_value("NoSuchSheet", "A1", "test");
1129        assert!(result.is_err());
1130        assert!(matches!(result.unwrap_err(), Error::SheetNotFound { .. }));
1131    }
1132
1133    #[test]
1134    fn test_get_value_sheet_not_found() {
1135        let wb = Workbook::new();
1136        let result = wb.get_cell_value("NoSuchSheet", "A1");
1137        assert!(result.is_err());
1138        assert!(matches!(result.unwrap_err(), Error::SheetNotFound { .. }));
1139    }
1140
1141    #[test]
1142    fn test_get_empty_cell_returns_empty() {
1143        let wb = Workbook::new();
1144        let val = wb.get_cell_value("Sheet1", "Z99").unwrap();
1145        assert_eq!(val, CellValue::Empty);
1146    }
1147
1148    #[test]
1149    fn test_cell_value_roundtrip_save_open() {
1150        let dir = TempDir::new().unwrap();
1151        let path = dir.path().join("cell_roundtrip.xlsx");
1152
1153        let mut wb = Workbook::new();
1154        wb.set_cell_value("Sheet1", "A1", "Hello").unwrap();
1155        wb.set_cell_value("Sheet1", "B1", 42.0f64).unwrap();
1156        wb.set_cell_value("Sheet1", "C1", true).unwrap();
1157        wb.save(&path).unwrap();
1158
1159        let wb2 = Workbook::open(&path).unwrap();
1160        assert_eq!(
1161            wb2.get_cell_value("Sheet1", "A1").unwrap(),
1162            CellValue::String("Hello".to_string())
1163        );
1164        assert_eq!(
1165            wb2.get_cell_value("Sheet1", "B1").unwrap(),
1166            CellValue::Number(42.0)
1167        );
1168        assert_eq!(
1169            wb2.get_cell_value("Sheet1", "C1").unwrap(),
1170            CellValue::Bool(true)
1171        );
1172    }
1173
1174    #[test]
1175    fn test_set_empty_value_clears_cell() {
1176        let mut wb = Workbook::new();
1177        wb.set_cell_value("Sheet1", "A1", "test").unwrap();
1178        assert_eq!(
1179            wb.get_cell_value("Sheet1", "A1").unwrap(),
1180            CellValue::String("test".to_string())
1181        );
1182
1183        wb.set_cell_value("Sheet1", "A1", CellValue::Empty).unwrap();
1184        assert_eq!(wb.get_cell_value("Sheet1", "A1").unwrap(), CellValue::Empty);
1185    }
1186
1187    #[test]
1188    fn test_string_too_long_returns_error() {
1189        let mut wb = Workbook::new();
1190        let long_string = "x".repeat(MAX_CELL_CHARS + 1);
1191        let result = wb.set_cell_value("Sheet1", "A1", long_string.as_str());
1192        assert!(result.is_err());
1193        assert!(matches!(
1194            result.unwrap_err(),
1195            Error::CellValueTooLong { .. }
1196        ));
1197    }
1198
1199    #[test]
1200    fn test_set_multiple_cells_same_row() {
1201        let mut wb = Workbook::new();
1202        wb.set_cell_value("Sheet1", "A1", "first").unwrap();
1203        wb.set_cell_value("Sheet1", "B1", "second").unwrap();
1204        wb.set_cell_value("Sheet1", "C1", "third").unwrap();
1205
1206        assert_eq!(
1207            wb.get_cell_value("Sheet1", "A1").unwrap(),
1208            CellValue::String("first".to_string())
1209        );
1210        assert_eq!(
1211            wb.get_cell_value("Sheet1", "B1").unwrap(),
1212            CellValue::String("second".to_string())
1213        );
1214        assert_eq!(
1215            wb.get_cell_value("Sheet1", "C1").unwrap(),
1216            CellValue::String("third".to_string())
1217        );
1218    }
1219
1220    #[test]
1221    fn test_overwrite_cell_value() {
1222        let mut wb = Workbook::new();
1223        wb.set_cell_value("Sheet1", "A1", "original").unwrap();
1224        wb.set_cell_value("Sheet1", "A1", "updated").unwrap();
1225
1226        assert_eq!(
1227            wb.get_cell_value("Sheet1", "A1").unwrap(),
1228            CellValue::String("updated".to_string())
1229        );
1230    }
1231
1232    #[test]
1233    fn test_set_and_get_error_value() {
1234        let mut wb = Workbook::new();
1235        wb.set_cell_value("Sheet1", "A1", CellValue::Error("#DIV/0!".to_string()))
1236            .unwrap();
1237        let val = wb.get_cell_value("Sheet1", "A1").unwrap();
1238        assert_eq!(val, CellValue::Error("#DIV/0!".to_string()));
1239    }
1240
1241    #[test]
1242    fn test_set_and_get_date_value() {
1243        use crate::style::{builtin_num_fmts, NumFmtStyle, Style};
1244
1245        let mut wb = Workbook::new();
1246        // Create a date style.
1247        let style_id = wb
1248            .add_style(&Style {
1249                num_fmt: Some(NumFmtStyle::Builtin(builtin_num_fmts::DATE_MDY)),
1250                ..Style::default()
1251            })
1252            .unwrap();
1253
1254        // Set a date value.
1255        let date_serial =
1256            crate::cell::date_to_serial(chrono::NaiveDate::from_ymd_opt(2024, 6, 15).unwrap());
1257        wb.set_cell_value("Sheet1", "A1", CellValue::Date(date_serial))
1258            .unwrap();
1259        wb.set_cell_style("Sheet1", "A1", style_id).unwrap();
1260
1261        // Get the value back -- it should be Date because the cell has a date style.
1262        let val = wb.get_cell_value("Sheet1", "A1").unwrap();
1263        assert_eq!(val, CellValue::Date(date_serial));
1264    }
1265
1266    #[test]
1267    fn test_date_value_applies_default_date_style() {
1268        let mut wb = Workbook::new();
1269        // Set a date value without an explicit date style.
1270        let date_serial =
1271            crate::cell::date_to_serial(chrono::NaiveDate::from_ymd_opt(2024, 6, 15).unwrap());
1272        wb.set_cell_value("Sheet1", "A1", CellValue::Date(date_serial))
1273            .unwrap();
1274
1275        // The write applies the default date style, so it reads back as Date.
1276        let val = wb.get_cell_value("Sheet1", "A1").unwrap();
1277        assert_eq!(val, CellValue::Date(date_serial));
1278    }
1279
1280    #[test]
1281    fn test_explicit_iso_date_cell_is_date() {
1282        let wb = Workbook::new();
1283        let cell = Cell {
1284            r: "A1".try_into().unwrap(),
1285            col: 1,
1286            s: None,
1287            t: CellTypeTag::Date,
1288            v: Some("2024-06-15".to_string()),
1289            f: None,
1290            is: None,
1291        };
1292
1293        assert_eq!(
1294            wb.xml_cell_to_value(&cell).unwrap(),
1295            CellValue::Date(crate::cell::date_to_serial(
1296                chrono::NaiveDate::from_ymd_opt(2024, 6, 15).unwrap()
1297            ))
1298        );
1299    }
1300
1301    #[test]
1302    fn test_explicit_numeric_date_cell_is_date() {
1303        let wb = Workbook::new();
1304        let cell = Cell {
1305            r: "A1".try_into().unwrap(),
1306            col: 1,
1307            s: None,
1308            t: CellTypeTag::Date,
1309            v: Some("45292.5".to_string()),
1310            f: None,
1311            is: None,
1312        };
1313
1314        assert_eq!(
1315            wb.xml_cell_to_value(&cell).unwrap(),
1316            CellValue::Date(45292.5)
1317        );
1318    }
1319
1320    #[test]
1321    fn test_formula_with_explicit_date_cache_preserves_date_result() {
1322        let wb = Workbook::new();
1323        let cell = Cell {
1324            r: "A1".try_into().unwrap(),
1325            col: 1,
1326            s: None,
1327            t: CellTypeTag::Date,
1328            v: Some("2024-06-15".to_string()),
1329            f: Some(Box::new(CellFormula {
1330                t: None,
1331                reference: None,
1332                si: None,
1333                value: Some("TODAY()".to_string()),
1334            })),
1335            is: None,
1336        };
1337
1338        assert_eq!(
1339            wb.xml_cell_to_value(&cell).unwrap(),
1340            CellValue::Formula {
1341                expr: "TODAY()".to_string(),
1342                result: Some(Box::new(CellValue::Date(crate::cell::date_to_serial(
1343                    chrono::NaiveDate::from_ymd_opt(2024, 6, 15).unwrap()
1344                )))),
1345            }
1346        );
1347    }
1348
1349    #[test]
1350    fn test_date_value_roundtrip_through_save() {
1351        use crate::style::{builtin_num_fmts, NumFmtStyle, Style};
1352
1353        let mut wb = Workbook::new();
1354        let style_id = wb
1355            .add_style(&Style {
1356                num_fmt: Some(NumFmtStyle::Builtin(builtin_num_fmts::DATETIME)),
1357                ..Style::default()
1358            })
1359            .unwrap();
1360
1361        let dt = chrono::NaiveDate::from_ymd_opt(2024, 3, 15)
1362            .unwrap()
1363            .and_hms_opt(14, 30, 0)
1364            .unwrap();
1365        let serial = crate::cell::datetime_to_serial(dt);
1366        wb.set_cell_value("Sheet1", "A1", CellValue::Date(serial))
1367            .unwrap();
1368        wb.set_cell_style("Sheet1", "A1", style_id).unwrap();
1369
1370        let dir = tempfile::TempDir::new().unwrap();
1371        let path = dir.path().join("date_test.xlsx");
1372        wb.save(&path).unwrap();
1373
1374        let wb2 = Workbook::open(&path).unwrap();
1375        let val = wb2.get_cell_value("Sheet1", "A1").unwrap();
1376        assert_eq!(val, CellValue::Date(serial));
1377    }
1378
1379    #[test]
1380    fn test_date_from_naive_date_conversion() {
1381        let date = chrono::NaiveDate::from_ymd_opt(2024, 1, 1).unwrap();
1382        let cv: CellValue = date.into();
1383        match cv {
1384            CellValue::Date(s) => {
1385                let roundtripped = crate::cell::serial_to_date(s).unwrap();
1386                assert_eq!(roundtripped, date);
1387            }
1388            _ => panic!("expected Date variant"),
1389        }
1390    }
1391
1392    #[test]
1393    fn test_set_and_get_formula_value() {
1394        let mut wb = Workbook::new();
1395        wb.set_cell_value(
1396            "Sheet1",
1397            "A1",
1398            CellValue::Formula {
1399                expr: "SUM(B1:B10)".to_string(),
1400                result: None,
1401            },
1402        )
1403        .unwrap();
1404        let val = wb.get_cell_value("Sheet1", "A1").unwrap();
1405        match val {
1406            CellValue::Formula { expr, .. } => {
1407                assert_eq!(expr, "SUM(B1:B10)");
1408            }
1409            other => panic!("expected Formula, got {:?}", other),
1410        }
1411    }
1412
1413    #[test]
1414    fn test_formula_cached_results_roundtrip_through_save() {
1415        let cases = [
1416            ("A1", CellValue::Number(42.0)),
1417            ("A2", CellValue::Bool(true)),
1418            ("A3", CellValue::String("cached".to_string())),
1419            ("A4", CellValue::Error("#DIV/0!".to_string())),
1420            ("A5", CellValue::Date(45292.5)),
1421        ];
1422        let mut wb = Workbook::new();
1423        for (cell, result) in &cases {
1424            wb.set_cell_value(
1425                "Sheet1",
1426                cell,
1427                CellValue::Formula {
1428                    expr: "1+1".to_string(),
1429                    result: Some(Box::new(result.clone())),
1430                },
1431            )
1432            .unwrap();
1433            assert_eq!(
1434                wb.get_cell_value("Sheet1", cell).unwrap(),
1435                CellValue::Formula {
1436                    expr: "1+1".to_string(),
1437                    result: Some(Box::new(result.clone())),
1438                }
1439            );
1440        }
1441
1442        let dir = tempfile::TempDir::new().unwrap();
1443        let path = dir.path().join("formula_cached_results.xlsx");
1444        wb.save(&path).unwrap();
1445        let reopened = Workbook::open(&path).unwrap();
1446        for (cell, result) in &cases {
1447            assert_eq!(
1448                reopened.get_cell_value("Sheet1", cell).unwrap(),
1449                CellValue::Formula {
1450                    expr: "1+1".to_string(),
1451                    result: Some(Box::new(result.clone())),
1452                }
1453            );
1454        }
1455    }
1456
1457    #[test]
1458    fn test_set_i32_value() {
1459        let mut wb = Workbook::new();
1460        wb.set_cell_value("Sheet1", "A1", 100i32).unwrap();
1461        let val = wb.get_cell_value("Sheet1", "A1").unwrap();
1462        assert_eq!(val, CellValue::Number(100.0));
1463    }
1464
1465    #[test]
1466    fn test_set_string_at_max_length() {
1467        let mut wb = Workbook::new();
1468        let max_string = "x".repeat(MAX_CELL_CHARS);
1469        wb.set_cell_value("Sheet1", "A1", max_string.as_str())
1470            .unwrap();
1471        let val = wb.get_cell_value("Sheet1", "A1").unwrap();
1472        assert_eq!(val, CellValue::String(max_string));
1473    }
1474
1475    #[test]
1476    fn test_set_cells_different_rows() {
1477        let mut wb = Workbook::new();
1478        wb.set_cell_value("Sheet1", "A1", "row1").unwrap();
1479        wb.set_cell_value("Sheet1", "A3", "row3").unwrap();
1480        wb.set_cell_value("Sheet1", "A2", "row2").unwrap(); // inserted between
1481
1482        assert_eq!(
1483            wb.get_cell_value("Sheet1", "A1").unwrap(),
1484            CellValue::String("row1".to_string())
1485        );
1486        assert_eq!(
1487            wb.get_cell_value("Sheet1", "A2").unwrap(),
1488            CellValue::String("row2".to_string())
1489        );
1490        assert_eq!(
1491            wb.get_cell_value("Sheet1", "A3").unwrap(),
1492            CellValue::String("row3".to_string())
1493        );
1494    }
1495
1496    #[test]
1497    fn test_string_deduplication_in_sst() {
1498        let mut wb = Workbook::new();
1499        wb.set_cell_value("Sheet1", "A1", "same").unwrap();
1500        wb.set_cell_value("Sheet1", "A2", "same").unwrap();
1501        wb.set_cell_value("Sheet1", "A3", "different").unwrap();
1502
1503        // Both A1 and A2 should point to the same SST index
1504        assert_eq!(wb.sst_runtime.len(), 2);
1505        assert_eq!(
1506            wb.get_cell_value("Sheet1", "A1").unwrap(),
1507            CellValue::String("same".to_string())
1508        );
1509        assert_eq!(
1510            wb.get_cell_value("Sheet1", "A2").unwrap(),
1511            CellValue::String("same".to_string())
1512        );
1513    }
1514
1515    #[test]
1516    fn test_add_style_returns_id() {
1517        use crate::style::{FontStyle, Style};
1518
1519        let mut wb = Workbook::new();
1520        let style = Style {
1521            font: Some(FontStyle {
1522                bold: true,
1523                ..FontStyle::default()
1524            }),
1525            ..Style::default()
1526        };
1527        let id = wb.add_style(&style).unwrap();
1528        assert!(id > 0);
1529    }
1530
1531    #[test]
1532    fn test_get_cell_style_unstyled_cell_returns_none() {
1533        let wb = Workbook::new();
1534        let result = wb.get_cell_style("Sheet1", "A1").unwrap();
1535        assert!(result.is_none());
1536    }
1537
1538    #[test]
1539    fn test_set_cell_style_on_existing_value() {
1540        use crate::style::{FontStyle, Style};
1541
1542        let mut wb = Workbook::new();
1543        wb.set_cell_value("Sheet1", "A1", "Hello").unwrap();
1544
1545        let style = Style {
1546            font: Some(FontStyle {
1547                bold: true,
1548                ..FontStyle::default()
1549            }),
1550            ..Style::default()
1551        };
1552        let style_id = wb.add_style(&style).unwrap();
1553        wb.set_cell_style("Sheet1", "A1", style_id).unwrap();
1554
1555        let retrieved_id = wb.get_cell_style("Sheet1", "A1").unwrap();
1556        assert_eq!(retrieved_id, Some(style_id));
1557
1558        // The value should still be there.
1559        let val = wb.get_cell_value("Sheet1", "A1").unwrap();
1560        assert_eq!(val, CellValue::String("Hello".to_string()));
1561    }
1562
1563    #[test]
1564    fn test_set_cell_style_on_empty_cell_creates_cell() {
1565        use crate::style::{FontStyle, Style};
1566
1567        let mut wb = Workbook::new();
1568        let style = Style {
1569            font: Some(FontStyle {
1570                bold: true,
1571                ..FontStyle::default()
1572            }),
1573            ..Style::default()
1574        };
1575        let style_id = wb.add_style(&style).unwrap();
1576
1577        // Set style on a cell that doesn't exist yet.
1578        wb.set_cell_style("Sheet1", "B5", style_id).unwrap();
1579
1580        let retrieved_id = wb.get_cell_style("Sheet1", "B5").unwrap();
1581        assert_eq!(retrieved_id, Some(style_id));
1582
1583        // The cell value should be empty.
1584        let val = wb.get_cell_value("Sheet1", "B5").unwrap();
1585        assert_eq!(val, CellValue::Empty);
1586    }
1587
1588    #[test]
1589    fn test_set_cell_style_invalid_id() {
1590        let mut wb = Workbook::new();
1591        let result = wb.set_cell_style("Sheet1", "A1", 999);
1592        assert!(result.is_err());
1593        assert!(matches!(result.unwrap_err(), Error::StyleNotFound { .. }));
1594    }
1595
1596    #[test]
1597    fn test_set_cell_style_sheet_not_found() {
1598        let mut wb = Workbook::new();
1599        let style = crate::style::Style::default();
1600        let style_id = wb.add_style(&style).unwrap();
1601        let result = wb.set_cell_style("NoSuchSheet", "A1", style_id);
1602        assert!(result.is_err());
1603        assert!(matches!(result.unwrap_err(), Error::SheetNotFound { .. }));
1604    }
1605
1606    #[test]
1607    fn test_get_cell_style_sheet_not_found() {
1608        let wb = Workbook::new();
1609        let result = wb.get_cell_style("NoSuchSheet", "A1");
1610        assert!(result.is_err());
1611        assert!(matches!(result.unwrap_err(), Error::SheetNotFound { .. }));
1612    }
1613
1614    #[test]
1615    fn test_style_roundtrip_save_open() {
1616        use crate::style::{
1617            AlignmentStyle, BorderLineStyle, BorderSideStyle, BorderStyle, FillStyle, FontStyle,
1618            HorizontalAlign, NumFmtStyle, PatternType, Style, StyleColor, VerticalAlign,
1619        };
1620
1621        let dir = TempDir::new().unwrap();
1622        let path = dir.path().join("style_roundtrip.xlsx");
1623
1624        let mut wb = Workbook::new();
1625        wb.set_cell_value("Sheet1", "A1", "Styled").unwrap();
1626
1627        let style = Style {
1628            font: Some(FontStyle {
1629                name: Some("Arial".to_string()),
1630                size: Some(14.0),
1631                bold: true,
1632                italic: true,
1633                color: Some(StyleColor::Rgb("FFFF0000".to_string())),
1634                ..FontStyle::default()
1635            }),
1636            fill: Some(FillStyle {
1637                pattern: PatternType::Solid,
1638                fg_color: Some(StyleColor::Rgb("FFFFFF00".to_string())),
1639                bg_color: None,
1640                gradient: None,
1641            }),
1642            border: Some(BorderStyle {
1643                left: Some(BorderSideStyle {
1644                    style: BorderLineStyle::Thin,
1645                    color: None,
1646                }),
1647                right: Some(BorderSideStyle {
1648                    style: BorderLineStyle::Thin,
1649                    color: None,
1650                }),
1651                top: Some(BorderSideStyle {
1652                    style: BorderLineStyle::Thin,
1653                    color: None,
1654                }),
1655                bottom: Some(BorderSideStyle {
1656                    style: BorderLineStyle::Thin,
1657                    color: None,
1658                }),
1659                diagonal: None,
1660            }),
1661            alignment: Some(AlignmentStyle {
1662                horizontal: Some(HorizontalAlign::Center),
1663                vertical: Some(VerticalAlign::Center),
1664                wrap_text: true,
1665                ..AlignmentStyle::default()
1666            }),
1667            num_fmt: Some(NumFmtStyle::Custom("#,##0.00".to_string())),
1668            protection: None,
1669        };
1670        let style_id = wb.add_style(&style).unwrap();
1671        wb.set_cell_style("Sheet1", "A1", style_id).unwrap();
1672        wb.save(&path).unwrap();
1673
1674        // Re-open and verify.
1675        let wb2 = Workbook::open(&path).unwrap();
1676        let retrieved_id = wb2.get_cell_style("Sheet1", "A1").unwrap();
1677        assert_eq!(retrieved_id, Some(style_id));
1678
1679        // Verify the value is still there.
1680        let val = wb2.get_cell_value("Sheet1", "A1").unwrap();
1681        assert_eq!(val, CellValue::String("Styled".to_string()));
1682
1683        // Reverse-lookup the style to verify components survived the roundtrip.
1684        let retrieved_style = crate::style::get_style(&wb2.stylesheet, style_id).unwrap();
1685        assert!(retrieved_style.font.is_some());
1686        let font = retrieved_style.font.unwrap();
1687        assert!(font.bold);
1688        assert!(font.italic);
1689        assert_eq!(font.name, Some("Arial".to_string()));
1690
1691        assert!(retrieved_style.fill.is_some());
1692        let fill = retrieved_style.fill.unwrap();
1693        assert_eq!(fill.pattern, PatternType::Solid);
1694
1695        assert!(retrieved_style.alignment.is_some());
1696        let align = retrieved_style.alignment.unwrap();
1697        assert_eq!(align.horizontal, Some(HorizontalAlign::Center));
1698        assert_eq!(align.vertical, Some(VerticalAlign::Center));
1699        assert!(align.wrap_text);
1700    }
1701
1702    #[test]
1703    fn test_set_and_get_cell_rich_text() {
1704        use crate::rich_text::RichTextRun;
1705
1706        let mut wb = Workbook::new();
1707        let runs = vec![
1708            RichTextRun {
1709                text: "Bold".to_string(),
1710                font: None,
1711                size: None,
1712                bold: true,
1713                italic: false,
1714                color: None,
1715            },
1716            RichTextRun {
1717                text: " Normal".to_string(),
1718                font: None,
1719                size: None,
1720                bold: false,
1721                italic: false,
1722                color: None,
1723            },
1724        ];
1725        wb.set_cell_rich_text("Sheet1", "A1", runs.clone()).unwrap();
1726
1727        // The cell value should be a shared string whose plain text is "Bold Normal".
1728        let val = wb.get_cell_value("Sheet1", "A1").unwrap();
1729        assert_eq!(val.to_string(), "Bold Normal");
1730
1731        // get_cell_rich_text should return the runs.
1732        let got = wb.get_cell_rich_text("Sheet1", "A1").unwrap();
1733        assert!(got.is_some());
1734        let got_runs = got.unwrap();
1735        assert_eq!(got_runs.len(), 2);
1736        assert_eq!(got_runs[0].text, "Bold");
1737        assert!(got_runs[0].bold);
1738        assert_eq!(got_runs[1].text, " Normal");
1739        assert!(!got_runs[1].bold);
1740    }
1741
1742    #[test]
1743    fn test_get_cell_rich_text_returns_none_for_plain() {
1744        let mut wb = Workbook::new();
1745        wb.set_cell_value("Sheet1", "A1", CellValue::String("plain".to_string()))
1746            .unwrap();
1747        let got = wb.get_cell_rich_text("Sheet1", "A1").unwrap();
1748        assert!(got.is_none());
1749    }
1750
1751    #[test]
1752    fn test_rich_text_roundtrip_save_open() {
1753        use crate::rich_text::RichTextRun;
1754
1755        let dir = TempDir::new().unwrap();
1756        let path = dir.path().join("rich_text.xlsx");
1757
1758        // Note: quick-xml's serde deserializer trims leading and trailing
1759        // whitespace from text content. To avoid false failures, test text
1760        // values must not rely on boundary whitespace being preserved.
1761        let mut wb = Workbook::new();
1762        let runs = vec![
1763            RichTextRun {
1764                text: "Hello".to_string(),
1765                font: Some("Arial".to_string()),
1766                size: Some(14.0),
1767                bold: true,
1768                italic: false,
1769                color: Some("#FF0000".to_string()),
1770            },
1771            RichTextRun {
1772                text: "World".to_string(),
1773                font: None,
1774                size: None,
1775                bold: false,
1776                italic: true,
1777                color: None,
1778            },
1779        ];
1780        wb.set_cell_rich_text("Sheet1", "B2", runs).unwrap();
1781        wb.save(&path).unwrap();
1782
1783        let wb2 = Workbook::open(&path).unwrap();
1784        let val = wb2.get_cell_value("Sheet1", "B2").unwrap();
1785        assert_eq!(val.to_string(), "HelloWorld");
1786
1787        let got = wb2.get_cell_rich_text("Sheet1", "B2").unwrap();
1788        assert!(got.is_some());
1789        let got_runs = got.unwrap();
1790        assert_eq!(got_runs.len(), 2);
1791        assert_eq!(got_runs[0].text, "Hello");
1792        assert!(got_runs[0].bold);
1793        assert_eq!(got_runs[0].font.as_deref(), Some("Arial"));
1794        assert_eq!(got_runs[0].size, Some(14.0));
1795        assert_eq!(got_runs[0].color.as_deref(), Some("#FF0000"));
1796        assert_eq!(got_runs[1].text, "World");
1797        assert!(got_runs[1].italic);
1798        assert!(!got_runs[1].bold);
1799    }
1800
1801    #[test]
1802    fn test_set_cell_formula() {
1803        let mut wb = Workbook::new();
1804        wb.set_cell_formula("Sheet1", "A1", "SUM(B1:B10)").unwrap();
1805        let val = wb.get_cell_value("Sheet1", "A1").unwrap();
1806        match val {
1807            CellValue::Formula { expr, .. } => assert_eq!(expr, "SUM(B1:B10)"),
1808            other => panic!("expected Formula, got {:?}", other),
1809        }
1810    }
1811
1812    #[test]
1813    fn test_fill_formula_basic() {
1814        let mut wb = Workbook::new();
1815        wb.fill_formula("Sheet1", "D2:D5", "SUM(A2:C2)").unwrap();
1816
1817        // D2 should have the base formula unchanged
1818        match wb.get_cell_value("Sheet1", "D2").unwrap() {
1819            CellValue::Formula { expr, .. } => assert_eq!(expr, "SUM(A2:C2)"),
1820            other => panic!("D2: expected Formula, got {:?}", other),
1821        }
1822        // D3 should have row shifted by 1
1823        match wb.get_cell_value("Sheet1", "D3").unwrap() {
1824            CellValue::Formula { expr, .. } => assert_eq!(expr, "SUM(A3:C3)"),
1825            other => panic!("D3: expected Formula, got {:?}", other),
1826        }
1827        // D4 should have row shifted by 2
1828        match wb.get_cell_value("Sheet1", "D4").unwrap() {
1829            CellValue::Formula { expr, .. } => assert_eq!(expr, "SUM(A4:C4)"),
1830            other => panic!("D4: expected Formula, got {:?}", other),
1831        }
1832        // D5 should have row shifted by 3
1833        match wb.get_cell_value("Sheet1", "D5").unwrap() {
1834            CellValue::Formula { expr, .. } => assert_eq!(expr, "SUM(A5:C5)"),
1835            other => panic!("D5: expected Formula, got {:?}", other),
1836        }
1837    }
1838
1839    #[test]
1840    fn test_fill_formula_preserves_absolute_refs() {
1841        let mut wb = Workbook::new();
1842        wb.fill_formula("Sheet1", "B1:B3", "$A$1*A1").unwrap();
1843
1844        match wb.get_cell_value("Sheet1", "B1").unwrap() {
1845            CellValue::Formula { expr, .. } => assert_eq!(expr, "$A$1*A1"),
1846            other => panic!("B1: expected Formula, got {:?}", other),
1847        }
1848        match wb.get_cell_value("Sheet1", "B2").unwrap() {
1849            CellValue::Formula { expr, .. } => assert_eq!(expr, "$A$1*A2"),
1850            other => panic!("B2: expected Formula, got {:?}", other),
1851        }
1852        match wb.get_cell_value("Sheet1", "B3").unwrap() {
1853            CellValue::Formula { expr, .. } => assert_eq!(expr, "$A$1*A3"),
1854            other => panic!("B3: expected Formula, got {:?}", other),
1855        }
1856    }
1857
1858    #[test]
1859    fn test_fill_formula_single_cell() {
1860        let mut wb = Workbook::new();
1861        wb.fill_formula("Sheet1", "A1:A1", "B1+C1").unwrap();
1862        match wb.get_cell_value("Sheet1", "A1").unwrap() {
1863            CellValue::Formula { expr, .. } => assert_eq!(expr, "B1+C1"),
1864            other => panic!("expected Formula, got {:?}", other),
1865        }
1866    }
1867
1868    #[test]
1869    fn test_fill_formula_invalid_range() {
1870        let mut wb = Workbook::new();
1871        assert!(wb.fill_formula("Sheet1", "INVALID", "A1").is_err());
1872    }
1873
1874    #[test]
1875    fn test_fill_formula_multi_column_range_rejected() {
1876        let mut wb = Workbook::new();
1877        assert!(wb.fill_formula("Sheet1", "A1:B5", "C1").is_err());
1878    }
1879
1880    #[test]
1881    fn formula_assignment_rejects_input_over_secure_default_without_mutation() {
1882        let mut wb = Workbook::new();
1883        wb.set_cell_value("Sheet1", "A1", "original").unwrap();
1884        let formula = "x".repeat(
1885            crate::formula::FormulaLimits::secure_default().per_formula_input_bytes as usize + 1,
1886        );
1887
1888        let error = wb.set_cell_formula("Sheet1", "A1", &formula).unwrap_err();
1889        let Error::ResourceLimitExceeded(metadata) = error else {
1890            panic!("expected resource limit error");
1891        };
1892        assert_eq!(
1893            metadata.operation(),
1894            crate::ResourceOperation::FormulaMutate
1895        );
1896        assert_eq!(metadata.dimension(), crate::ResourceDimension::InputBytes);
1897        assert_eq!(
1898            wb.get_cell_value("Sheet1", "A1").unwrap(),
1899            CellValue::String("original".to_string())
1900        );
1901    }
1902
1903    #[test]
1904    fn formula_assignment_with_limits_preserves_opaque_excel_formula() {
1905        let mut wb = Workbook::new();
1906        let formula = "_xlfn.UNSUPPORTED(A1)";
1907        let limits = crate::formula::FormulaLimits {
1908            per_formula_input_bytes: formula.len() as u64,
1909            operation_input_bytes: formula.len() as u64,
1910            operation_output_bytes: formula.len() as u64,
1911            ..crate::formula::FormulaLimits::secure_default()
1912        };
1913
1914        wb.set_cell_formula_with_limits("Sheet1", "A1", formula, limits)
1915            .unwrap();
1916
1917        assert_eq!(
1918            wb.get_cell_value("Sheet1", "A1").unwrap(),
1919            CellValue::Formula {
1920                expr: formula.to_string(),
1921                result: None,
1922            }
1923        );
1924    }
1925
1926    #[test]
1927    fn fill_formula_range_limit_is_exact_and_atomic() {
1928        let exact = crate::formula::FormulaLimits {
1929            operation_range_cells: 2,
1930            ..crate::formula::FormulaLimits::secure_default()
1931        };
1932        let mut accepted = Workbook::new();
1933        accepted
1934            .fill_formula_with_limits("Sheet1", "D1:D2", "A1", exact)
1935            .unwrap();
1936        assert!(matches!(
1937            accepted.get_cell_value("Sheet1", "D2").unwrap(),
1938            CellValue::Formula { .. }
1939        ));
1940
1941        let mut rejected = Workbook::new();
1942        rejected.set_cell_value("Sheet1", "D1", "first").unwrap();
1943        rejected.set_cell_value("Sheet1", "D2", "second").unwrap();
1944        let over = crate::formula::FormulaLimits {
1945            operation_range_cells: 1,
1946            ..crate::formula::FormulaLimits::secure_default()
1947        };
1948        let error = rejected
1949            .fill_formula_with_limits("Sheet1", "D1:D2", "A1", over)
1950            .unwrap_err();
1951        let Error::ResourceLimitExceeded(metadata) = error else {
1952            panic!("expected resource limit error");
1953        };
1954        assert_eq!(
1955            metadata.dimension(),
1956            crate::ResourceDimension::RangeCellCount
1957        );
1958        assert_eq!(
1959            rejected.get_cell_value("Sheet1", "D1").unwrap(),
1960            CellValue::String("first".to_string())
1961        );
1962        assert_eq!(
1963            rejected.get_cell_value("Sheet1", "D2").unwrap(),
1964            CellValue::String("second".to_string())
1965        );
1966    }
1967
1968    #[test]
1969    fn fill_formula_output_expansion_limit_is_exact_and_atomic() {
1970        let exact = crate::formula::FormulaLimits {
1971            operation_output_bytes: 5,
1972            ..crate::formula::FormulaLimits::secure_default()
1973        };
1974        let mut accepted = Workbook::new();
1975        accepted
1976            .fill_formula_with_limits("Sheet1", "D1:D2", "A9", exact)
1977            .unwrap();
1978        assert_eq!(
1979            accepted.get_cell_value("Sheet1", "D2").unwrap(),
1980            CellValue::Formula {
1981                expr: "A10".to_string(),
1982                result: None,
1983            }
1984        );
1985
1986        let mut rejected = Workbook::new();
1987        rejected.set_cell_value("Sheet1", "D1", "first").unwrap();
1988        rejected.set_cell_value("Sheet1", "D2", "second").unwrap();
1989        let over = crate::formula::FormulaLimits {
1990            operation_output_bytes: 4,
1991            ..crate::formula::FormulaLimits::secure_default()
1992        };
1993        let error = rejected
1994            .fill_formula_with_limits("Sheet1", "D1:D2", "A9", over)
1995            .unwrap_err();
1996        let Error::ResourceLimitExceeded(metadata) = error else {
1997            panic!("expected resource limit error");
1998        };
1999        assert_eq!(metadata.dimension(), crate::ResourceDimension::OutputBytes);
2000        assert_eq!(
2001            rejected.get_cell_value("Sheet1", "D1").unwrap(),
2002            CellValue::String("first".to_string())
2003        );
2004        assert_eq!(
2005            rejected.get_cell_value("Sheet1", "D2").unwrap(),
2006            CellValue::String("second".to_string())
2007        );
2008    }
2009
2010    #[test]
2011    fn fill_formula_cumulative_scan_work_fails_before_commit() {
2012        let mut wb = Workbook::new();
2013        wb.set_cell_value("Sheet1", "D1", "first").unwrap();
2014        wb.set_cell_value("Sheet1", "D2", "second").unwrap();
2015        wb.set_cell_value("Sheet1", "D3", "third").unwrap();
2016        let limits = crate::formula::FormulaLimits {
2017            operation_input_bytes: 5,
2018            ..crate::formula::FormulaLimits::secure_default()
2019        };
2020
2021        let error = wb
2022            .fill_formula_with_limits("Sheet1", "D1:D3", "A1", limits)
2023            .unwrap_err();
2024        let Error::ResourceLimitExceeded(metadata) = error else {
2025            panic!("expected resource limit error");
2026        };
2027        assert_eq!(metadata.dimension(), crate::ResourceDimension::InputBytes);
2028        assert_eq!(
2029            wb.get_cell_value("Sheet1", "D1").unwrap(),
2030            CellValue::String("first".to_string())
2031        );
2032        assert_eq!(
2033            wb.get_cell_value("Sheet1", "D3").unwrap(),
2034            CellValue::String("third".to_string())
2035        );
2036    }
2037
2038    #[test]
2039    fn fill_formula_shift_failure_is_atomic() {
2040        let mut wb = Workbook::new();
2041        wb.set_cell_value("Sheet1", "D1", "first").unwrap();
2042        wb.set_cell_value("Sheet1", "D2", "second").unwrap();
2043
2044        assert!(wb.fill_formula("Sheet1", "D1:D2", "A1048576").is_err());
2045        assert_eq!(
2046            wb.get_cell_value("Sheet1", "D1").unwrap(),
2047            CellValue::String("first".to_string())
2048        );
2049        assert_eq!(
2050            wb.get_cell_value("Sheet1", "D2").unwrap(),
2051            CellValue::String("second".to_string())
2052        );
2053    }
2054
2055    #[test]
2056    fn generic_formula_batch_rejects_before_any_mutation() {
2057        let mut wb = Workbook::new();
2058        wb.set_cell_value("Sheet1", "A1", "original").unwrap();
2059        let oversized = "x".repeat(
2060            crate::formula::FormulaLimits::secure_default().per_formula_input_bytes as usize + 1,
2061        );
2062
2063        let error = wb
2064            .set_cell_values(
2065                "Sheet1",
2066                vec![
2067                    (
2068                        "A1".to_string(),
2069                        CellValue::Formula {
2070                            expr: "1+1".to_string(),
2071                            result: None,
2072                        },
2073                    ),
2074                    (
2075                        "B1".to_string(),
2076                        CellValue::Formula {
2077                            expr: oversized,
2078                            result: None,
2079                        },
2080                    ),
2081                ],
2082            )
2083            .unwrap_err();
2084        assert!(matches!(error, Error::ResourceLimitExceeded(_)));
2085        assert_eq!(
2086            wb.get_cell_value("Sheet1", "A1").unwrap(),
2087            CellValue::String("original".to_string())
2088        );
2089        assert_eq!(wb.get_cell_value("Sheet1", "B1").unwrap(), CellValue::Empty);
2090    }
2091
2092    #[test]
2093    fn generic_single_formula_uses_secure_mutation_limits() {
2094        let mut wb = Workbook::new();
2095        wb.set_cell_value("Sheet1", "A1", "original").unwrap();
2096        let oversized = "x".repeat(
2097            crate::formula::FormulaLimits::secure_default().per_formula_input_bytes as usize + 1,
2098        );
2099
2100        let error = wb
2101            .set_cell_value(
2102                "Sheet1",
2103                "A1",
2104                CellValue::Formula {
2105                    expr: oversized,
2106                    result: None,
2107                },
2108            )
2109            .unwrap_err();
2110
2111        assert!(matches!(error, Error::ResourceLimitExceeded(_)));
2112        assert_eq!(
2113            wb.get_cell_value("Sheet1", "A1").unwrap(),
2114            CellValue::String("original".to_string())
2115        );
2116    }
2117
2118    #[test]
2119    fn formula_cached_result_output_budget_is_exact() {
2120        let values = [
2121            CellValue::Formula {
2122                expr: "x".to_string(),
2123                result: Some(Box::new(CellValue::String("ab".to_string()))),
2124            },
2125            CellValue::Formula {
2126                expr: "y".to_string(),
2127                result: Some(Box::new(CellValue::Error("#N".to_string()))),
2128            },
2129        ];
2130        let exact = crate::formula::FormulaLimits {
2131            operation_output_bytes: 6,
2132            ..crate::formula::FormulaLimits::secure_default()
2133        };
2134        validate_formula_values_for_mutation(values.iter(), exact).unwrap();
2135
2136        let over = crate::formula::FormulaLimits {
2137            operation_output_bytes: 5,
2138            ..exact
2139        };
2140        let error = validate_formula_values_for_mutation(values.iter(), over).unwrap_err();
2141        let Error::ResourceLimitExceeded(metadata) = error else {
2142            panic!("expected a structured formula mutation limit error");
2143        };
2144        assert_eq!(metadata.operation_code(), "formula_mutate");
2145        assert_eq!(metadata.dimension_code(), "output_bytes");
2146        assert_eq!(metadata.limit(), 5);
2147        assert_eq!(metadata.observed(), 6);
2148    }
2149
2150    #[test]
2151    fn formula_cached_result_length_failure_is_atomic() {
2152        let mut wb = Workbook::new();
2153        wb.set_cell_value("Sheet1", "A1", "first").unwrap();
2154        wb.set_cell_value("Sheet1", "B1", "second").unwrap();
2155        let error = wb
2156            .set_cell_values(
2157                "Sheet1",
2158                vec![
2159                    (
2160                        "A1".to_string(),
2161                        CellValue::Formula {
2162                            expr: "1+1".to_string(),
2163                            result: Some(Box::new(CellValue::String("x".repeat(MAX_CELL_CHARS)))),
2164                        },
2165                    ),
2166                    (
2167                        "B1".to_string(),
2168                        CellValue::Formula {
2169                            expr: "2+2".to_string(),
2170                            result: Some(Box::new(CellValue::Error(
2171                                "x".repeat(MAX_CELL_CHARS + 1),
2172                            ))),
2173                        },
2174                    ),
2175                ],
2176            )
2177            .unwrap_err();
2178        assert!(matches!(error, Error::CellValueTooLong { .. }));
2179        assert_eq!(
2180            wb.get_cell_value("Sheet1", "A1").unwrap(),
2181            CellValue::String("first".to_string())
2182        );
2183        assert_eq!(
2184            wb.get_cell_value("Sheet1", "B1").unwrap(),
2185            CellValue::String("second".to_string())
2186        );
2187    }
2188
2189    #[test]
2190    fn sheet_data_formula_budget_failure_is_atomic() {
2191        let mut wb = Workbook::new();
2192        wb.set_cell_value("Sheet1", "A1", "first").unwrap();
2193        wb.set_cell_value("Sheet1", "B1", "second").unwrap();
2194        let oversized = "x".repeat(
2195            crate::formula::FormulaLimits::secure_default().per_formula_input_bytes as usize + 1,
2196        );
2197
2198        let error = wb
2199            .set_sheet_data(
2200                "Sheet1",
2201                vec![vec![
2202                    CellValue::Formula {
2203                        expr: "1+1".to_string(),
2204                        result: None,
2205                    },
2206                    CellValue::Formula {
2207                        expr: oversized,
2208                        result: None,
2209                    },
2210                ]],
2211                1,
2212                1,
2213            )
2214            .unwrap_err();
2215
2216        assert!(matches!(error, Error::ResourceLimitExceeded(_)));
2217        assert_eq!(
2218            wb.get_cell_value("Sheet1", "A1").unwrap(),
2219            CellValue::String("first".to_string())
2220        );
2221        assert_eq!(
2222            wb.get_cell_value("Sheet1", "B1").unwrap(),
2223            CellValue::String("second".to_string())
2224        );
2225    }
2226
2227    #[test]
2228    fn test_set_cell_values_batch() {
2229        let mut wb = Workbook::new();
2230        wb.set_cell_values(
2231            "Sheet1",
2232            vec![
2233                ("A1".to_string(), CellValue::String("hello".to_string())),
2234                ("B1".to_string(), CellValue::Number(42.0)),
2235                ("C1".to_string(), CellValue::Bool(true)),
2236                ("A2".to_string(), CellValue::String("world".to_string())),
2237            ],
2238        )
2239        .unwrap();
2240
2241        assert_eq!(
2242            wb.get_cell_value("Sheet1", "A1").unwrap(),
2243            CellValue::String("hello".to_string())
2244        );
2245        assert_eq!(
2246            wb.get_cell_value("Sheet1", "B1").unwrap(),
2247            CellValue::Number(42.0)
2248        );
2249        assert_eq!(
2250            wb.get_cell_value("Sheet1", "C1").unwrap(),
2251            CellValue::Bool(true)
2252        );
2253        assert_eq!(
2254            wb.get_cell_value("Sheet1", "A2").unwrap(),
2255            CellValue::String("world".to_string())
2256        );
2257    }
2258
2259    #[test]
2260    fn test_set_cell_values_empty_removes_cell() {
2261        let mut wb = Workbook::new();
2262        wb.set_cell_value("Sheet1", "A1", "existing").unwrap();
2263        wb.set_cell_values("Sheet1", vec![("A1".to_string(), CellValue::Empty)])
2264            .unwrap();
2265        assert_eq!(wb.get_cell_value("Sheet1", "A1").unwrap(), CellValue::Empty);
2266    }
2267
2268    #[test]
2269    fn test_set_sheet_data_basic() {
2270        let mut wb = Workbook::new();
2271        wb.set_sheet_data(
2272            "Sheet1",
2273            vec![
2274                vec![
2275                    CellValue::String("Name".to_string()),
2276                    CellValue::String("Age".to_string()),
2277                ],
2278                vec![
2279                    CellValue::String("Alice".to_string()),
2280                    CellValue::Number(30.0),
2281                ],
2282                vec![
2283                    CellValue::String("Bob".to_string()),
2284                    CellValue::Number(25.0),
2285                ],
2286            ],
2287            1,
2288            1,
2289        )
2290        .unwrap();
2291
2292        assert_eq!(
2293            wb.get_cell_value("Sheet1", "A1").unwrap(),
2294            CellValue::String("Name".to_string())
2295        );
2296        assert_eq!(
2297            wb.get_cell_value("Sheet1", "B1").unwrap(),
2298            CellValue::String("Age".to_string())
2299        );
2300        assert_eq!(
2301            wb.get_cell_value("Sheet1", "A2").unwrap(),
2302            CellValue::String("Alice".to_string())
2303        );
2304        assert_eq!(
2305            wb.get_cell_value("Sheet1", "B2").unwrap(),
2306            CellValue::Number(30.0)
2307        );
2308        assert_eq!(
2309            wb.get_cell_value("Sheet1", "A3").unwrap(),
2310            CellValue::String("Bob".to_string())
2311        );
2312        assert_eq!(
2313            wb.get_cell_value("Sheet1", "B3").unwrap(),
2314            CellValue::Number(25.0)
2315        );
2316    }
2317
2318    #[test]
2319    fn test_set_sheet_data_with_offset() {
2320        let mut wb = Workbook::new();
2321        // Start at C3 (col=3, row=3)
2322        wb.set_sheet_data(
2323            "Sheet1",
2324            vec![
2325                vec![CellValue::Number(1.0), CellValue::Number(2.0)],
2326                vec![CellValue::Number(3.0), CellValue::Number(4.0)],
2327            ],
2328            3,
2329            3,
2330        )
2331        .unwrap();
2332
2333        assert_eq!(
2334            wb.get_cell_value("Sheet1", "C3").unwrap(),
2335            CellValue::Number(1.0)
2336        );
2337        assert_eq!(
2338            wb.get_cell_value("Sheet1", "D3").unwrap(),
2339            CellValue::Number(2.0)
2340        );
2341        assert_eq!(
2342            wb.get_cell_value("Sheet1", "C4").unwrap(),
2343            CellValue::Number(3.0)
2344        );
2345        assert_eq!(
2346            wb.get_cell_value("Sheet1", "D4").unwrap(),
2347            CellValue::Number(4.0)
2348        );
2349        // A1 should still be empty
2350        assert_eq!(wb.get_cell_value("Sheet1", "A1").unwrap(), CellValue::Empty);
2351    }
2352
2353    #[test]
2354    fn test_set_sheet_data_roundtrip() {
2355        let dir = TempDir::new().unwrap();
2356        let path = dir.path().join("batch_roundtrip.xlsx");
2357
2358        let mut wb = Workbook::new();
2359        wb.set_sheet_data(
2360            "Sheet1",
2361            vec![
2362                vec![
2363                    CellValue::String("Header1".to_string()),
2364                    CellValue::String("Header2".to_string()),
2365                ],
2366                vec![CellValue::Number(100.0), CellValue::Bool(true)],
2367            ],
2368            1,
2369            1,
2370        )
2371        .unwrap();
2372        wb.save(&path).unwrap();
2373
2374        let wb2 = Workbook::open(&path).unwrap();
2375        assert_eq!(
2376            wb2.get_cell_value("Sheet1", "A1").unwrap(),
2377            CellValue::String("Header1".to_string())
2378        );
2379        assert_eq!(
2380            wb2.get_cell_value("Sheet1", "B1").unwrap(),
2381            CellValue::String("Header2".to_string())
2382        );
2383        assert_eq!(
2384            wb2.get_cell_value("Sheet1", "A2").unwrap(),
2385            CellValue::Number(100.0)
2386        );
2387        assert_eq!(
2388            wb2.get_cell_value("Sheet1", "B2").unwrap(),
2389            CellValue::Bool(true)
2390        );
2391    }
2392
2393    #[test]
2394    fn test_set_row_values() {
2395        let mut wb = Workbook::new();
2396        wb.set_row_values(
2397            "Sheet1",
2398            1,
2399            1,
2400            vec![
2401                CellValue::String("A".to_string()),
2402                CellValue::String("B".to_string()),
2403                CellValue::String("C".to_string()),
2404            ],
2405        )
2406        .unwrap();
2407
2408        assert_eq!(
2409            wb.get_cell_value("Sheet1", "A1").unwrap(),
2410            CellValue::String("A".to_string())
2411        );
2412        assert_eq!(
2413            wb.get_cell_value("Sheet1", "B1").unwrap(),
2414            CellValue::String("B".to_string())
2415        );
2416        assert_eq!(
2417            wb.get_cell_value("Sheet1", "C1").unwrap(),
2418            CellValue::String("C".to_string())
2419        );
2420    }
2421
2422    #[test]
2423    fn test_set_row_values_with_offset() {
2424        let mut wb = Workbook::new();
2425        // Start at column D (col=4)
2426        wb.set_row_values(
2427            "Sheet1",
2428            2,
2429            4,
2430            vec![CellValue::Number(10.0), CellValue::Number(20.0)],
2431        )
2432        .unwrap();
2433
2434        assert_eq!(
2435            wb.get_cell_value("Sheet1", "D2").unwrap(),
2436            CellValue::Number(10.0)
2437        );
2438        assert_eq!(
2439            wb.get_cell_value("Sheet1", "E2").unwrap(),
2440            CellValue::Number(20.0)
2441        );
2442    }
2443
2444    #[test]
2445    fn test_set_sheet_data_merges_with_existing() {
2446        let mut wb = Workbook::new();
2447        wb.set_cell_value("Sheet1", "A1", "existing").unwrap();
2448        wb.set_sheet_data(
2449            "Sheet1",
2450            vec![vec![CellValue::Empty, CellValue::String("new".to_string())]],
2451            1,
2452            1,
2453        )
2454        .unwrap();
2455
2456        // A1 was cleared by Empty
2457        assert_eq!(wb.get_cell_value("Sheet1", "A1").unwrap(), CellValue::Empty);
2458        // B1 was added
2459        assert_eq!(
2460            wb.get_cell_value("Sheet1", "B1").unwrap(),
2461            CellValue::String("new".to_string())
2462        );
2463    }
2464
2465    /// Saves a workbook with a date-styled number cell and a plain-number
2466    /// cell for reuse across the interpretation round-trip tests below.
2467    fn write_workbook_with_date_style(path: &std::path::Path) {
2468        use crate::style::{builtin_num_fmts, NumFmtStyle, Style};
2469        let mut wb = Workbook::new();
2470        let date_style = wb
2471            .add_style(&Style {
2472                num_fmt: Some(NumFmtStyle::Builtin(builtin_num_fmts::DATE_MDY)),
2473                ..Style::default()
2474            })
2475            .unwrap();
2476        wb.set_cell_value("Sheet1", "A1", 46127.0_f64).unwrap();
2477        wb.set_cell_style("Sheet1", "A1", date_style).unwrap();
2478        wb.set_cell_value("Sheet1", "B1", 42.0_f64).unwrap();
2479        wb.save(path).unwrap();
2480    }
2481
2482    #[test]
2483    fn test_get_cell_value_num_fmt_default_promotes_date_styled_cells() {
2484        let dir = tempfile::TempDir::new().unwrap();
2485        let path = dir.path().join("get_cell_value_default.xlsx");
2486        write_workbook_with_date_style(&path);
2487
2488        // Default interpretation is `NumFmt`, so date-styled numeric cells
2489        // come back as dates while plain numbers stay numbers.
2490        let wb = Workbook::open(&path).unwrap();
2491        assert_eq!(
2492            wb.get_cell_value("Sheet1", "A1").unwrap(),
2493            CellValue::Date(46127.0)
2494        );
2495        assert_eq!(
2496            wb.get_cell_value("Sheet1", "B1").unwrap(),
2497            CellValue::Number(42.0)
2498        );
2499    }
2500
2501    #[test]
2502    fn test_get_cell_value_cell_type_keeps_numbers() {
2503        let dir = tempfile::TempDir::new().unwrap();
2504        let path = dir.path().join("get_cell_value_cell_type.xlsx");
2505        write_workbook_with_date_style(&path);
2506
2507        // Opting into `CellType` makes the reader strictly follow the cell
2508        // type attribute, so even date-styled number cells stay numbers.
2509        let wb = Workbook::open_with_options(
2510            &path,
2511            &crate::workbook::OpenOptions::new()
2512                .date_interpretation(crate::workbook::DateInterpretation::CellType),
2513        )
2514        .unwrap();
2515        assert_eq!(
2516            wb.get_cell_value("Sheet1", "A1").unwrap(),
2517            CellValue::Number(46127.0)
2518        );
2519        assert_eq!(
2520            wb.get_cell_value("Sheet1", "B1").unwrap(),
2521            CellValue::Number(42.0)
2522        );
2523    }
2524
2525    #[test]
2526    fn test_get_rows_num_fmt_default_promotes_date_styled_cells() {
2527        let dir = tempfile::TempDir::new().unwrap();
2528        let path = dir.path().join("get_rows_default.xlsx");
2529        write_workbook_with_date_style(&path);
2530
2531        // `get_rows` now honors the same interpretation option as
2532        // `get_cell_value` and the streaming reader.
2533        let wb = Workbook::open(&path).unwrap();
2534        let rows = wb.get_rows("Sheet1").unwrap();
2535        assert_eq!(rows.len(), 1);
2536        let cells = &rows[0].1;
2537        assert_eq!(cells[0].1, CellValue::Date(46127.0));
2538        assert_eq!(cells[1].1, CellValue::Number(42.0));
2539    }
2540
2541    #[test]
2542    fn test_get_rows_cell_type_keeps_numbers() {
2543        let dir = tempfile::TempDir::new().unwrap();
2544        let path = dir.path().join("get_rows_cell_type.xlsx");
2545        write_workbook_with_date_style(&path);
2546
2547        let wb = Workbook::open_with_options(
2548            &path,
2549            &crate::workbook::OpenOptions::new()
2550                .date_interpretation(crate::workbook::DateInterpretation::CellType),
2551        )
2552        .unwrap();
2553        let rows = wb.get_rows("Sheet1").unwrap();
2554        assert_eq!(rows.len(), 1);
2555        let cells = &rows[0].1;
2556        assert_eq!(cells[0].1, CellValue::Number(46127.0));
2557        assert_eq!(cells[1].1, CellValue::Number(42.0));
2558    }
2559
2560    #[test]
2561    fn test_set_sheet_data_replaces_streamed_backing_on_save() {
2562        let dir = tempfile::TempDir::new().unwrap();
2563        let path = dir.path().join("streamed_sheet_data_replacement.xlsx");
2564        let mut wb = Workbook::new();
2565        let mut writer = wb.new_stream_writer("Streamed").unwrap();
2566        writer
2567            .write_row(1, &[CellValue::String("streamed".to_string())])
2568            .unwrap();
2569        wb.apply_stream_writer(writer).unwrap();
2570
2571        wb.set_sheet_data(
2572            "Streamed",
2573            vec![vec![CellValue::String("replacement".to_string())]],
2574            1,
2575            1,
2576        )
2577        .unwrap();
2578        wb.save(&path).unwrap();
2579
2580        let reopened = Workbook::open(&path).unwrap();
2581        assert_eq!(
2582            reopened.get_cell_value("Streamed", "A1").unwrap(),
2583            CellValue::String("replacement".to_string())
2584        );
2585    }
2586
2587    #[test]
2588    fn get_cell_value_charges_shared_string_before_cloning() {
2589        let mut workbook = Workbook::new();
2590        workbook
2591            .set_cell_value("Sheet1", "A1", "x".repeat(1_024))
2592            .unwrap();
2593        let exact = u64::try_from(std::mem::size_of::<CellValue>()).unwrap() + 1_024;
2594        let limits = crate::traversal::TraversalLimits::new(1, 1, 1, exact).unwrap();
2595
2596        assert_eq!(
2597            workbook
2598                .get_cell_value_with_limits("Sheet1", "A1", limits)
2599                .unwrap(),
2600            workbook.get_cell_value("Sheet1", "A1").unwrap()
2601        );
2602
2603        let error = workbook
2604            .get_cell_value_with_limits(
2605                "Sheet1",
2606                "A1",
2607                crate::traversal::TraversalLimits::new(1, 1, 1, exact - 1).unwrap(),
2608            )
2609            .unwrap_err();
2610        let Error::ResourceLimitExceeded(metadata) = error else {
2611            panic!("expected resource limit error");
2612        };
2613        assert_eq!(
2614            metadata.dimension(),
2615            crate::ResourceDimension::RetainedBytes
2616        );
2617        assert_eq!(metadata.limit(), exact - 1);
2618        assert_eq!(metadata.observed(), exact);
2619    }
2620
2621    #[test]
2622    fn shared_formula_followers_resolve_in_cell_row_and_column_reads() {
2623        use sheetkit_xml::worksheet::{Cell, CellFormula, CellTypeTag, Row, SheetData};
2624
2625        let shared = |cell: &str, col: u32, master: Option<&str>, si: u32, cached: &str| Cell {
2626            r: cell.try_into().unwrap(),
2627            col,
2628            s: None,
2629            t: CellTypeTag::None,
2630            v: Some(cached.to_string()),
2631            f: Some(Box::new(CellFormula {
2632                t: Some("shared".to_string()),
2633                reference: master.map(|_| "B1:B2".to_string()),
2634                si: Some(si),
2635                value: master.map(str::to_string),
2636            })),
2637            is: None,
2638        };
2639        let row = |r: u32, cells: Vec<Cell>| Row {
2640            r,
2641            spans: None,
2642            s: None,
2643            custom_format: None,
2644            ht: None,
2645            hidden: None,
2646            custom_height: None,
2647            outline_level: None,
2648            cells,
2649        };
2650        let mut wb = Workbook::new();
2651        wb.worksheet_mut("Sheet1").unwrap().sheet_data = SheetData {
2652            rows: vec![
2653                row(1, vec![shared("B1", 2, Some("A1*2"), 0, "2")]),
2654                row(
2655                    2,
2656                    vec![shared("B2", 2, None, 0, "4"), shared("C2", 3, None, 9, "5")],
2657                ),
2658            ],
2659        };
2660        let formula = |expr: &str, cached: f64| CellValue::Formula {
2661            expr: expr.to_string(),
2662            result: Some(Box::new(CellValue::Number(cached))),
2663        };
2664
2665        assert_eq!(
2666            wb.get_cell_value("Sheet1", "B1").unwrap(),
2667            formula("A1*2", 2.0)
2668        );
2669        assert_eq!(
2670            wb.get_cell_value("Sheet1", "B2").unwrap(),
2671            formula("A2*2", 4.0)
2672        );
2673        // A follower without a master keeps its empty expression.
2674        assert_eq!(wb.get_cell_value("Sheet1", "C2").unwrap(), formula("", 5.0));
2675
2676        let rows = wb.get_rows("Sheet1").unwrap();
2677        assert_eq!(
2678            rows[1].1,
2679            vec![(2, formula("A2*2", 4.0)), (3, formula("", 5.0))]
2680        );
2681        let cols = wb.get_cols("Sheet1").unwrap();
2682        assert_eq!(
2683            cols[0],
2684            (
2685                "B".to_string(),
2686                vec![(1, formula("A1*2", 2.0)), (2, formula("A2*2", 4.0))]
2687            )
2688        );
2689    }
2690}