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        let ws = self.worksheet_ref(sheet)?;
10
11        let (col, row) = cell_name_to_coordinates(cell)?;
12
13        // Find the row via binary search (rows are sorted by row number).
14        let xml_row = match ws.sheet_data.rows.binary_search_by_key(&row, |r| r.r) {
15            Ok(idx) => &ws.sheet_data.rows[idx],
16            Err(_) => return Ok(CellValue::Empty),
17        };
18
19        // Find the cell via binary search on cached column number.
20        let xml_cell = match xml_row.cells.binary_search_by_key(&col, |c| c.col) {
21            Ok(idx) => &xml_row.cells[idx],
22            Err(_) => return Ok(CellValue::Empty),
23        };
24
25        self.xml_cell_to_value(xml_cell)
26    }
27
28    /// Set the value of a cell.
29    ///
30    /// The value can be any type that implements `Into<CellValue>`, including
31    /// `&str`, `String`, `f64`, `i32`, `i64`, and `bool`.
32    ///
33    /// Setting a cell to [`CellValue::Empty`] removes the cell from the row.
34    pub fn set_cell_value(
35        &mut self,
36        sheet: &str,
37        cell: &str,
38        value: impl Into<CellValue>,
39    ) -> Result<()> {
40        let value = value.into();
41
42        // Validate string length.
43        if let CellValue::String(ref s) = value {
44            if s.len() > MAX_CELL_CHARS {
45                return Err(Error::CellValueTooLong {
46                    length: s.len(),
47                    max: MAX_CELL_CHARS,
48                });
49            }
50        }
51
52        let sheet_idx = self.sheet_index(sheet)?;
53        self.invalidate_streamed(sheet_idx);
54        self.ensure_hydrated(sheet_idx)?;
55        self.mark_sheet_dirty(sheet_idx);
56
57        let (col, row_num) = cell_name_to_coordinates(cell)?;
58        let cell_ref = crate::utils::cell_ref::coordinates_to_cell_name(col, row_num)?;
59
60        let ws = self.worksheets[sheet_idx].1.get_mut().unwrap();
61
62        // Find or create the row via binary search (rows are sorted by row number).
63        let row_idx = match ws.sheet_data.rows.binary_search_by_key(&row_num, |r| r.r) {
64            Ok(idx) => idx,
65            Err(idx) => {
66                ws.sheet_data.rows.insert(idx, new_row(row_num));
67                idx
68            }
69        };
70
71        let row = &mut ws.sheet_data.rows[row_idx];
72
73        // Handle Empty: remove the cell if present.
74        if value == CellValue::Empty {
75            if let Ok(idx) = row.cells.binary_search_by_key(&col, |c| c.col) {
76                row.cells.remove(idx);
77            }
78            return Ok(());
79        }
80
81        // Find or create the cell via binary search on cached column number.
82        let cell_idx = match row.cells.binary_search_by_key(&col, |c| c.col) {
83            Ok(idx) => idx,
84            Err(insert_pos) => {
85                row.cells.insert(
86                    insert_pos,
87                    Cell {
88                        r: cell_ref.into(),
89                        col,
90                        s: None,
91                        t: CellTypeTag::None,
92                        v: None,
93                        f: None,
94                        is: None,
95                    },
96                );
97                insert_pos
98            }
99        };
100
101        let xml_cell = &mut row.cells[cell_idx];
102        value_to_xml_cell(&mut self.sst_runtime, xml_cell, value);
103
104        Ok(())
105    }
106
107    /// Convert an XML Cell to a CellValue.
108    pub(crate) fn xml_cell_to_value(&self, xml_cell: &Cell) -> Result<CellValue> {
109        // Check for formula first.
110        if let Some(ref formula) = xml_cell.f {
111            let expr = formula.value.clone().unwrap_or_default();
112            let result = match (xml_cell.t, &xml_cell.v) {
113                (CellTypeTag::Boolean, Some(v)) => Some(Box::new(CellValue::Bool(v == "1"))),
114                (CellTypeTag::Error, Some(v)) => Some(Box::new(CellValue::Error(v.clone()))),
115                (CellTypeTag::FormulaString, Some(v)) => {
116                    Some(Box::new(CellValue::String(v.clone())))
117                }
118                (_, Some(v)) => v
119                    .parse::<f64>()
120                    .ok()
121                    .map(|n| Box::new(CellValue::Number(n))),
122                _ => None,
123            };
124            return Ok(CellValue::Formula { expr, result });
125        }
126
127        let cell_value = xml_cell.v.as_deref();
128
129        match (xml_cell.t, cell_value) {
130            // Shared string
131            (CellTypeTag::SharedString, Some(v)) => {
132                let idx: usize = v
133                    .parse()
134                    .map_err(|_| Error::Internal(format!("invalid SST index: {v}")))?;
135                let s = self
136                    .sst_runtime
137                    .get(idx)
138                    .ok_or_else(|| Error::Internal(format!("SST index {idx} out of bounds")))?;
139                Ok(CellValue::String(s.to_string()))
140            }
141            // Boolean
142            (CellTypeTag::Boolean, Some(v)) => Ok(CellValue::Bool(v == "1")),
143            // Error
144            (CellTypeTag::Error, Some(v)) => Ok(CellValue::Error(v.to_string())),
145            // Inline string
146            (CellTypeTag::InlineString, _) => {
147                let s = xml_cell
148                    .is
149                    .as_ref()
150                    .and_then(|is| is.t.clone())
151                    .unwrap_or_default();
152                Ok(CellValue::String(s))
153            }
154            // Formula string (cached string result)
155            (CellTypeTag::FormulaString, Some(v)) => Ok(CellValue::String(v.to_string())),
156            // Number (explicit or default type) -- may be a date if styled
157            // and `DateInterpretation::NumFmt` is active.
158            (CellTypeTag::None | CellTypeTag::Number, Some(v)) => {
159                let n: f64 = v
160                    .parse()
161                    .map_err(|_| Error::Internal(format!("invalid number: {v}")))?;
162                if matches!(
163                    self.date_interpretation,
164                    super::open_options::DateInterpretation::NumFmt
165                ) && self.is_date_styled_cell(xml_cell)
166                {
167                    return Ok(CellValue::Date(n));
168                }
169                Ok(CellValue::Number(n))
170            }
171            // No value
172            _ => Ok(CellValue::Empty),
173        }
174    }
175
176    /// Check whether a cell's style indicates a date/time number format.
177    pub(crate) fn is_date_styled_cell(&self, xml_cell: &Cell) -> bool {
178        let style_idx = match xml_cell.s {
179            Some(idx) => idx as usize,
180            None => return false,
181        };
182        let xf = match self.stylesheet.cell_xfs.xfs.get(style_idx) {
183            Some(xf) => xf,
184            None => return false,
185        };
186        let num_fmt_id = xf.num_fmt_id.unwrap_or(0);
187        // Check built-in date format IDs.
188        if crate::cell::is_date_num_fmt(num_fmt_id) {
189            return true;
190        }
191        // Check custom number formats for date patterns.
192        if num_fmt_id >= 164 {
193            if let Some(ref num_fmts) = self.stylesheet.num_fmts {
194                if let Some(nf) = num_fmts
195                    .num_fmts
196                    .iter()
197                    .find(|nf| nf.num_fmt_id == num_fmt_id)
198                {
199                    return crate::cell::is_date_format_code(&nf.format_code);
200                }
201            }
202        }
203        false
204    }
205
206    /// Get the formatted display text for a cell, applying its number format.
207    ///
208    /// If the cell has a style with a number format, the raw numeric value is
209    /// formatted according to that format code. String and boolean cells return
210    /// their default display text. Empty cells return an empty string.
211    pub fn get_cell_formatted_value(&self, sheet: &str, cell: &str) -> Result<String> {
212        let ws = self.worksheet_ref(sheet)?;
213        let (col, row) = cell_name_to_coordinates(cell)?;
214
215        let xml_row = match ws.sheet_data.rows.binary_search_by_key(&row, |r| r.r) {
216            Ok(idx) => &ws.sheet_data.rows[idx],
217            Err(_) => return Ok(String::new()),
218        };
219
220        let xml_cell = match xml_row.cells.binary_search_by_key(&col, |c| c.col) {
221            Ok(idx) => &xml_row.cells[idx],
222            Err(_) => return Ok(String::new()),
223        };
224
225        let cell_value = self.xml_cell_to_value(xml_cell)?;
226
227        let numeric_val = match &cell_value {
228            CellValue::Number(n) => Some(*n),
229            CellValue::Date(n) => Some(*n),
230            CellValue::Formula {
231                result: Some(boxed),
232                ..
233            } => match boxed.as_ref() {
234                CellValue::Number(n) => Some(*n),
235                CellValue::Date(n) => Some(*n),
236                _ => None,
237            },
238            _ => None,
239        };
240
241        if let Some(val) = numeric_val {
242            if let Some(format_code) = self.cell_format_code(xml_cell) {
243                return Ok(crate::numfmt::format_number(val, &format_code));
244            }
245        }
246
247        Ok(cell_value.to_string())
248    }
249
250    /// Get the number format code string for a cell from its style.
251    /// Returns `None` if the cell has no style or the default "General" format.
252    pub(crate) fn cell_format_code(&self, xml_cell: &Cell) -> Option<String> {
253        let style_idx = xml_cell.s? as usize;
254        let xf = self.stylesheet.cell_xfs.xfs.get(style_idx)?;
255        let num_fmt_id = xf.num_fmt_id.unwrap_or(0);
256
257        if num_fmt_id == 0 {
258            return None;
259        }
260
261        // Try built-in format
262        if let Some(code) = crate::numfmt::builtin_format_code(num_fmt_id) {
263            return Some(code.to_string());
264        }
265
266        // Try custom format
267        if let Some(ref num_fmts) = self.stylesheet.num_fmts {
268            if let Some(nf) = num_fmts
269                .num_fmts
270                .iter()
271                .find(|nf| nf.num_fmt_id == num_fmt_id)
272            {
273                return Some(nf.format_code.clone());
274            }
275        }
276
277        None
278    }
279
280    /// Register a new style and return its ID.
281    ///
282    /// The style is deduplicated: if an identical style already exists in
283    /// the stylesheet, the existing ID is returned.
284    pub fn add_style(&mut self, style: &crate::style::Style) -> Result<u32> {
285        crate::style::add_style(&mut self.stylesheet, style)
286    }
287
288    /// Get the style ID applied to a cell.
289    ///
290    /// Returns `None` if the cell does not exist or has no explicit style
291    /// (i.e. uses the default style 0).
292    pub fn get_cell_style(&self, sheet: &str, cell: &str) -> Result<Option<u32>> {
293        let ws = self.worksheet_ref(sheet)?;
294
295        let (col, row) = cell_name_to_coordinates(cell)?;
296
297        // Find the row via binary search.
298        let xml_row = match ws.sheet_data.rows.binary_search_by_key(&row, |r| r.r) {
299            Ok(idx) => &ws.sheet_data.rows[idx],
300            Err(_) => return Ok(None),
301        };
302
303        // Find the cell via binary search on cached column number.
304        let xml_cell = match xml_row.cells.binary_search_by_key(&col, |c| c.col) {
305            Ok(idx) => &xml_row.cells[idx],
306            Err(_) => return Ok(None),
307        };
308
309        Ok(xml_cell.s)
310    }
311
312    /// Set the style ID for a cell.
313    ///
314    /// If the cell does not exist, an empty cell with just the style is created.
315    /// The `style_id` must be a valid index in cellXfs.
316    pub fn set_cell_style(&mut self, sheet: &str, cell: &str, style_id: u32) -> Result<()> {
317        // Validate the style_id.
318        if style_id as usize >= self.stylesheet.cell_xfs.xfs.len() {
319            return Err(Error::StyleNotFound { id: style_id });
320        }
321
322        let sheet_idx = self.sheet_index(sheet)?;
323        let ws = self.worksheet_mut_by_index(sheet_idx)?;
324
325        let (col, row_num) = cell_name_to_coordinates(cell)?;
326        let cell_ref = crate::utils::cell_ref::coordinates_to_cell_name(col, row_num)?;
327
328        // Find or create the row via binary search.
329        let row_idx = match ws.sheet_data.rows.binary_search_by_key(&row_num, |r| r.r) {
330            Ok(idx) => idx,
331            Err(idx) => {
332                ws.sheet_data.rows.insert(idx, new_row(row_num));
333                idx
334            }
335        };
336
337        let row = &mut ws.sheet_data.rows[row_idx];
338
339        // Find or create the cell via binary search on cached column number.
340        let cell_idx = match row.cells.binary_search_by_key(&col, |c| c.col) {
341            Ok(idx) => idx,
342            Err(insert_pos) => {
343                row.cells.insert(
344                    insert_pos,
345                    Cell {
346                        r: cell_ref.into(),
347                        col,
348                        s: None,
349                        t: CellTypeTag::None,
350                        v: None,
351                        f: None,
352                        is: None,
353                    },
354                );
355                insert_pos
356            }
357        };
358
359        row.cells[cell_idx].s = Some(style_id);
360        Ok(())
361    }
362
363    /// Merge a range of cells on the given sheet.
364    ///
365    /// `top_left` and `bottom_right` are cell references like "A1" and "C3".
366    /// Returns an error if the range overlaps with an existing merge region.
367    pub fn merge_cells(&mut self, sheet: &str, top_left: &str, bottom_right: &str) -> Result<()> {
368        let ws = self.worksheet_mut(sheet)?;
369        crate::merge::merge_cells(ws, top_left, bottom_right)
370    }
371
372    /// Remove a merged cell range from the given sheet.
373    ///
374    /// `reference` is the exact range string like "A1:C3".
375    pub fn unmerge_cell(&mut self, sheet: &str, reference: &str) -> Result<()> {
376        let ws = self.worksheet_mut(sheet)?;
377        crate::merge::unmerge_cell(ws, reference)
378    }
379
380    /// Get all merged cell ranges on the given sheet.
381    ///
382    /// Returns a list of range strings like `["A1:B2", "D1:F3"]`.
383    pub fn get_merge_cells(&self, sheet: &str) -> Result<Vec<String>> {
384        let ws = self.worksheet_ref(sheet)?;
385        Ok(crate::merge::get_merge_cells(ws))
386    }
387
388    /// Set a formula on a cell.
389    ///
390    /// This is a convenience wrapper around [`set_cell_value`] with
391    /// [`CellValue::Formula`].
392    pub fn set_cell_formula(&mut self, sheet: &str, cell: &str, formula: &str) -> Result<()> {
393        self.set_cell_value(
394            sheet,
395            cell,
396            CellValue::Formula {
397                expr: formula.to_string(),
398                result: None,
399            },
400        )
401    }
402
403    /// Fill a range of cells with a formula, adjusting row references for each
404    /// row relative to the first cell in the range.
405    ///
406    /// `range` is an A1-style range like `"D2:D10"`. The `formula` is the base
407    /// formula for the first cell of the range. For each subsequent row, the
408    /// row references in the formula are shifted by the row offset. Absolute
409    /// row references (`$1`) are left unchanged.
410    pub fn fill_formula(&mut self, sheet: &str, range: &str, formula: &str) -> Result<()> {
411        let parts: Vec<&str> = range.split(':').collect();
412        if parts.len() != 2 {
413            return Err(Error::InvalidCellReference(format!(
414                "invalid range: {range}"
415            )));
416        }
417        let (start_col, start_row) = cell_name_to_coordinates(parts[0])?;
418        let (end_col, end_row) = cell_name_to_coordinates(parts[1])?;
419
420        if start_col != end_col {
421            return Err(Error::InvalidCellReference(
422                "fill_formula only supports single-column ranges".to_string(),
423            ));
424        }
425
426        for row in start_row..=end_row {
427            let row_offset = row as i32 - start_row as i32;
428            let adjusted = if row_offset == 0 {
429                formula.to_string()
430            } else {
431                crate::cell_ref_shift::shift_cell_references_with_abs(
432                    formula,
433                    |col, r, _abs_col, abs_row| {
434                        if abs_row {
435                            (col, r)
436                        } else {
437                            (col, (r as i32 + row_offset) as u32)
438                        }
439                    },
440                )?
441            };
442            let cell_ref = crate::utils::cell_ref::coordinates_to_cell_name(start_col, row)?;
443            self.set_cell_formula(sheet, &cell_ref, &adjusted)?;
444        }
445        Ok(())
446    }
447
448    /// Set a cell to a rich text value (multiple formatted runs).
449    pub fn set_cell_rich_text(
450        &mut self,
451        sheet: &str,
452        cell: &str,
453        runs: Vec<crate::rich_text::RichTextRun>,
454    ) -> Result<()> {
455        self.set_cell_value(sheet, cell, CellValue::RichString(runs))
456    }
457
458    /// Get rich text runs for a cell, if it contains rich text.
459    ///
460    /// Returns `None` if the cell is empty, contains a plain string, or holds
461    /// a non-string value.
462    pub fn get_cell_rich_text(
463        &self,
464        sheet: &str,
465        cell: &str,
466    ) -> Result<Option<Vec<crate::rich_text::RichTextRun>>> {
467        let (col, row) = cell_name_to_coordinates(cell)?;
468        let ws = self.worksheet_ref(sheet)?;
469
470        // Binary search for the row.
471        let xml_row = match ws.sheet_data.rows.binary_search_by_key(&row, |r| r.r) {
472            Ok(idx) => &ws.sheet_data.rows[idx],
473            Err(_) => return Ok(None),
474        };
475
476        // Binary search for the cell by column.
477        let xml_cell = match xml_row.cells.binary_search_by_key(&col, |c| c.col) {
478            Ok(idx) => &xml_row.cells[idx],
479            Err(_) => return Ok(None),
480        };
481
482        if xml_cell.t == CellTypeTag::SharedString {
483            if let Some(ref v) = xml_cell.v {
484                if let Ok(idx) = v.parse::<usize>() {
485                    return Ok(self.sst_runtime.get_rich_text(idx));
486                }
487            }
488        }
489        Ok(None)
490    }
491
492    /// Set multiple cell values at once. Each entry is a (cell_ref, value) pair.
493    ///
494    /// This is more efficient than calling `set_cell_value` repeatedly from
495    /// FFI because it crosses the language boundary only once.
496    pub fn set_cell_values(
497        &mut self,
498        sheet: &str,
499        entries: Vec<(String, CellValue)>,
500    ) -> Result<()> {
501        let sheet_idx = self.sheet_index(sheet)?;
502        self.invalidate_streamed(sheet_idx);
503        self.ensure_hydrated(sheet_idx)?;
504        self.mark_sheet_dirty(sheet_idx);
505
506        for (cell, value) in entries {
507            if let CellValue::String(ref s) = value {
508                if s.len() > MAX_CELL_CHARS {
509                    return Err(Error::CellValueTooLong {
510                        length: s.len(),
511                        max: MAX_CELL_CHARS,
512                    });
513                }
514            }
515
516            let (col, row_num) = cell_name_to_coordinates(&cell)?;
517            let cell_ref = crate::utils::cell_ref::coordinates_to_cell_name(col, row_num)?;
518
519            let row_idx = {
520                let ws = self.worksheets[sheet_idx].1.get_mut().unwrap();
521                match ws.sheet_data.rows.binary_search_by_key(&row_num, |r| r.r) {
522                    Ok(idx) => idx,
523                    Err(idx) => {
524                        ws.sheet_data.rows.insert(idx, new_row(row_num));
525                        idx
526                    }
527                }
528            };
529
530            if value == CellValue::Empty {
531                let row = &mut self.worksheets[sheet_idx]
532                    .1
533                    .get_mut()
534                    .unwrap()
535                    .sheet_data
536                    .rows[row_idx];
537                if let Ok(idx) = row.cells.binary_search_by_key(&col, |c| c.col) {
538                    row.cells.remove(idx);
539                }
540                continue;
541            }
542
543            let cell_idx = {
544                let row = &mut self.worksheets[sheet_idx]
545                    .1
546                    .get_mut()
547                    .unwrap()
548                    .sheet_data
549                    .rows[row_idx];
550                match row.cells.binary_search_by_key(&col, |c| c.col) {
551                    Ok(idx) => idx,
552                    Err(pos) => {
553                        row.cells.insert(
554                            pos,
555                            Cell {
556                                r: cell_ref.into(),
557                                col,
558                                s: None,
559                                t: CellTypeTag::None,
560                                v: None,
561                                f: None,
562                                is: None,
563                            },
564                        );
565                        pos
566                    }
567                }
568            };
569
570            let xml_cell = &mut self.worksheets[sheet_idx]
571                .1
572                .get_mut()
573                .unwrap()
574                .sheet_data
575                .rows[row_idx]
576                .cells[cell_idx];
577            value_to_xml_cell(&mut self.sst_runtime, xml_cell, value);
578        }
579
580        Ok(())
581    }
582
583    /// Set a contiguous block of cell values from a 2D array.
584    ///
585    /// `data` is a row-major 2D array of values. `start_row` and `start_col`
586    /// are 1-based. The first value in `data[0][0]` maps to the cell at
587    /// `(start_col, start_row)`.
588    ///
589    /// This is the fastest way to populate a sheet from JS because it crosses
590    /// the FFI boundary only once for the entire dataset.
591    pub fn set_sheet_data(
592        &mut self,
593        sheet: &str,
594        data: Vec<Vec<CellValue>>,
595        start_row: u32,
596        start_col: u32,
597    ) -> Result<()> {
598        let sheet_idx = self.sheet_index(sheet)?;
599        self.ensure_hydrated(sheet_idx)?;
600        self.mark_sheet_dirty(sheet_idx);
601
602        // Pre-compute column names for the widest row.
603        let max_cols = data.iter().map(|r| r.len()).max().unwrap_or(0) as u32;
604        let col_names: Vec<String> = (0..max_cols)
605            .map(|i| crate::utils::cell_ref::column_number_to_name(start_col + i))
606            .collect::<Result<Vec<_>>>()?;
607
608        for (row_offset, row_values) in data.into_iter().enumerate() {
609            let row_num = start_row + row_offset as u32;
610
611            let row_idx = {
612                let ws = self.worksheets[sheet_idx].1.get_mut().unwrap();
613                match ws.sheet_data.rows.binary_search_by_key(&row_num, |r| r.r) {
614                    Ok(idx) => idx,
615                    Err(idx) => {
616                        ws.sheet_data.rows.insert(idx, new_row(row_num));
617                        idx
618                    }
619                }
620            };
621
622            for (col_offset, value) in row_values.into_iter().enumerate() {
623                let col = start_col + col_offset as u32;
624
625                if let CellValue::String(ref s) = value {
626                    if s.len() > MAX_CELL_CHARS {
627                        return Err(Error::CellValueTooLong {
628                            length: s.len(),
629                            max: MAX_CELL_CHARS,
630                        });
631                    }
632                }
633
634                if value == CellValue::Empty {
635                    let row = &mut self.worksheets[sheet_idx]
636                        .1
637                        .get_mut()
638                        .unwrap()
639                        .sheet_data
640                        .rows[row_idx];
641                    if let Ok(idx) = row.cells.binary_search_by_key(&col, |c| c.col) {
642                        row.cells.remove(idx);
643                    }
644                    continue;
645                }
646
647                let cell_ref = format!("{}{}", col_names[col_offset], row_num);
648
649                let cell_idx = {
650                    let row = &mut self.worksheets[sheet_idx]
651                        .1
652                        .get_mut()
653                        .unwrap()
654                        .sheet_data
655                        .rows[row_idx];
656                    match row.cells.binary_search_by_key(&col, |c| c.col) {
657                        Ok(idx) => idx,
658                        Err(pos) => {
659                            row.cells.insert(
660                                pos,
661                                Cell {
662                                    r: cell_ref.into(),
663                                    col,
664                                    s: None,
665                                    t: CellTypeTag::None,
666                                    v: None,
667                                    f: None,
668                                    is: None,
669                                },
670                            );
671                            pos
672                        }
673                    }
674                };
675
676                let xml_cell = &mut self.worksheets[sheet_idx]
677                    .1
678                    .get_mut()
679                    .unwrap()
680                    .sheet_data
681                    .rows[row_idx]
682                    .cells[cell_idx];
683                value_to_xml_cell(&mut self.sst_runtime, xml_cell, value);
684            }
685        }
686
687        Ok(())
688    }
689
690    /// Set values in a single row starting from the given column.
691    ///
692    /// `row_num` is 1-based. `start_col` is 1-based.
693    /// Values are placed left-to-right starting at `start_col`.
694    pub fn set_row_values(
695        &mut self,
696        sheet: &str,
697        row_num: u32,
698        start_col: u32,
699        values: Vec<CellValue>,
700    ) -> Result<()> {
701        self.set_sheet_data(sheet, vec![values], row_num, start_col)
702    }
703}
704
705/// Write a CellValue into an XML Cell (mutating it in place).
706pub(crate) fn value_to_xml_cell(
707    sst: &mut SharedStringTable,
708    xml_cell: &mut Cell,
709    value: CellValue,
710) {
711    // Clear previous values.
712    xml_cell.t = CellTypeTag::None;
713    xml_cell.v = None;
714    xml_cell.f = None;
715    xml_cell.is = None;
716
717    match value {
718        CellValue::String(s) => {
719            let idx = sst.add_owned(s);
720            xml_cell.t = CellTypeTag::SharedString;
721            xml_cell.v = Some(idx.to_string());
722        }
723        CellValue::Number(n) => {
724            xml_cell.v = Some(n.to_string());
725        }
726        CellValue::Date(serial) => {
727            // Dates are stored as numbers in Excel. The style must apply a
728            // date number format for correct display.
729            xml_cell.v = Some(serial.to_string());
730        }
731        CellValue::Bool(b) => {
732            xml_cell.t = CellTypeTag::Boolean;
733            xml_cell.v = Some(if b { "1" } else { "0" }.to_string());
734        }
735        CellValue::Formula { expr, .. } => {
736            xml_cell.f = Some(Box::new(CellFormula {
737                t: None,
738                reference: None,
739                si: None,
740                value: Some(expr),
741            }));
742        }
743        CellValue::Error(e) => {
744            xml_cell.t = CellTypeTag::Error;
745            xml_cell.v = Some(e);
746        }
747        CellValue::Empty => {
748            // Already cleared above; the caller should have removed the cell.
749        }
750        CellValue::RichString(runs) => {
751            let idx = sst.add_rich_text(&runs);
752            xml_cell.t = CellTypeTag::SharedString;
753            xml_cell.v = Some(idx.to_string());
754        }
755    }
756}
757
758/// Create a new empty row with the given 1-based row number.
759pub(crate) fn new_row(row_num: u32) -> Row {
760    Row {
761        r: row_num,
762        spans: None,
763        s: None,
764        custom_format: None,
765        ht: None,
766        hidden: None,
767        custom_height: None,
768        outline_level: None,
769        cells: vec![],
770    }
771}
772
773#[cfg(test)]
774mod tests {
775    use super::*;
776    use tempfile::TempDir;
777
778    #[test]
779    fn test_set_and_get_string_value() {
780        let mut wb = Workbook::new();
781        wb.set_cell_value("Sheet1", "A1", "Hello").unwrap();
782        let val = wb.get_cell_value("Sheet1", "A1").unwrap();
783        assert_eq!(val, CellValue::String("Hello".to_string()));
784    }
785
786    #[test]
787    fn test_set_and_get_number_value() {
788        let mut wb = Workbook::new();
789        wb.set_cell_value("Sheet1", "B2", 42.5f64).unwrap();
790        let val = wb.get_cell_value("Sheet1", "B2").unwrap();
791        assert_eq!(val, CellValue::Number(42.5));
792    }
793
794    #[test]
795    fn test_set_and_get_bool_value() {
796        let mut wb = Workbook::new();
797        wb.set_cell_value("Sheet1", "C3", true).unwrap();
798        let val = wb.get_cell_value("Sheet1", "C3").unwrap();
799        assert_eq!(val, CellValue::Bool(true));
800
801        wb.set_cell_value("Sheet1", "D4", false).unwrap();
802        let val = wb.get_cell_value("Sheet1", "D4").unwrap();
803        assert_eq!(val, CellValue::Bool(false));
804    }
805
806    #[test]
807    fn test_set_value_sheet_not_found() {
808        let mut wb = Workbook::new();
809        let result = wb.set_cell_value("NoSuchSheet", "A1", "test");
810        assert!(result.is_err());
811        assert!(matches!(result.unwrap_err(), Error::SheetNotFound { .. }));
812    }
813
814    #[test]
815    fn test_get_value_sheet_not_found() {
816        let wb = Workbook::new();
817        let result = wb.get_cell_value("NoSuchSheet", "A1");
818        assert!(result.is_err());
819        assert!(matches!(result.unwrap_err(), Error::SheetNotFound { .. }));
820    }
821
822    #[test]
823    fn test_get_empty_cell_returns_empty() {
824        let wb = Workbook::new();
825        let val = wb.get_cell_value("Sheet1", "Z99").unwrap();
826        assert_eq!(val, CellValue::Empty);
827    }
828
829    #[test]
830    fn test_cell_value_roundtrip_save_open() {
831        let dir = TempDir::new().unwrap();
832        let path = dir.path().join("cell_roundtrip.xlsx");
833
834        let mut wb = Workbook::new();
835        wb.set_cell_value("Sheet1", "A1", "Hello").unwrap();
836        wb.set_cell_value("Sheet1", "B1", 42.0f64).unwrap();
837        wb.set_cell_value("Sheet1", "C1", true).unwrap();
838        wb.save(&path).unwrap();
839
840        let wb2 = Workbook::open(&path).unwrap();
841        assert_eq!(
842            wb2.get_cell_value("Sheet1", "A1").unwrap(),
843            CellValue::String("Hello".to_string())
844        );
845        assert_eq!(
846            wb2.get_cell_value("Sheet1", "B1").unwrap(),
847            CellValue::Number(42.0)
848        );
849        assert_eq!(
850            wb2.get_cell_value("Sheet1", "C1").unwrap(),
851            CellValue::Bool(true)
852        );
853    }
854
855    #[test]
856    fn test_set_empty_value_clears_cell() {
857        let mut wb = Workbook::new();
858        wb.set_cell_value("Sheet1", "A1", "test").unwrap();
859        assert_eq!(
860            wb.get_cell_value("Sheet1", "A1").unwrap(),
861            CellValue::String("test".to_string())
862        );
863
864        wb.set_cell_value("Sheet1", "A1", CellValue::Empty).unwrap();
865        assert_eq!(wb.get_cell_value("Sheet1", "A1").unwrap(), CellValue::Empty);
866    }
867
868    #[test]
869    fn test_string_too_long_returns_error() {
870        let mut wb = Workbook::new();
871        let long_string = "x".repeat(MAX_CELL_CHARS + 1);
872        let result = wb.set_cell_value("Sheet1", "A1", long_string.as_str());
873        assert!(result.is_err());
874        assert!(matches!(
875            result.unwrap_err(),
876            Error::CellValueTooLong { .. }
877        ));
878    }
879
880    #[test]
881    fn test_set_multiple_cells_same_row() {
882        let mut wb = Workbook::new();
883        wb.set_cell_value("Sheet1", "A1", "first").unwrap();
884        wb.set_cell_value("Sheet1", "B1", "second").unwrap();
885        wb.set_cell_value("Sheet1", "C1", "third").unwrap();
886
887        assert_eq!(
888            wb.get_cell_value("Sheet1", "A1").unwrap(),
889            CellValue::String("first".to_string())
890        );
891        assert_eq!(
892            wb.get_cell_value("Sheet1", "B1").unwrap(),
893            CellValue::String("second".to_string())
894        );
895        assert_eq!(
896            wb.get_cell_value("Sheet1", "C1").unwrap(),
897            CellValue::String("third".to_string())
898        );
899    }
900
901    #[test]
902    fn test_overwrite_cell_value() {
903        let mut wb = Workbook::new();
904        wb.set_cell_value("Sheet1", "A1", "original").unwrap();
905        wb.set_cell_value("Sheet1", "A1", "updated").unwrap();
906
907        assert_eq!(
908            wb.get_cell_value("Sheet1", "A1").unwrap(),
909            CellValue::String("updated".to_string())
910        );
911    }
912
913    #[test]
914    fn test_set_and_get_error_value() {
915        let mut wb = Workbook::new();
916        wb.set_cell_value("Sheet1", "A1", CellValue::Error("#DIV/0!".to_string()))
917            .unwrap();
918        let val = wb.get_cell_value("Sheet1", "A1").unwrap();
919        assert_eq!(val, CellValue::Error("#DIV/0!".to_string()));
920    }
921
922    #[test]
923    fn test_set_and_get_date_value() {
924        use crate::style::{builtin_num_fmts, NumFmtStyle, Style};
925
926        let mut wb = Workbook::new();
927        // Create a date style.
928        let style_id = wb
929            .add_style(&Style {
930                num_fmt: Some(NumFmtStyle::Builtin(builtin_num_fmts::DATE_MDY)),
931                ..Style::default()
932            })
933            .unwrap();
934
935        // Set a date value.
936        let date_serial =
937            crate::cell::date_to_serial(chrono::NaiveDate::from_ymd_opt(2024, 6, 15).unwrap());
938        wb.set_cell_value("Sheet1", "A1", CellValue::Date(date_serial))
939            .unwrap();
940        wb.set_cell_style("Sheet1", "A1", style_id).unwrap();
941
942        // Get the value back -- it should be Date because the cell has a date style.
943        let val = wb.get_cell_value("Sheet1", "A1").unwrap();
944        assert_eq!(val, CellValue::Date(date_serial));
945    }
946
947    #[test]
948    fn test_date_value_without_style_returns_number() {
949        let mut wb = Workbook::new();
950        // Set a date value without a date style.
951        let date_serial =
952            crate::cell::date_to_serial(chrono::NaiveDate::from_ymd_opt(2024, 6, 15).unwrap());
953        wb.set_cell_value("Sheet1", "A1", CellValue::Date(date_serial))
954            .unwrap();
955
956        // Without a date style, the value is read back as Number.
957        let val = wb.get_cell_value("Sheet1", "A1").unwrap();
958        assert_eq!(val, CellValue::Number(date_serial));
959    }
960
961    #[test]
962    fn test_date_value_roundtrip_through_save() {
963        use crate::style::{builtin_num_fmts, NumFmtStyle, Style};
964
965        let mut wb = Workbook::new();
966        let style_id = wb
967            .add_style(&Style {
968                num_fmt: Some(NumFmtStyle::Builtin(builtin_num_fmts::DATETIME)),
969                ..Style::default()
970            })
971            .unwrap();
972
973        let dt = chrono::NaiveDate::from_ymd_opt(2024, 3, 15)
974            .unwrap()
975            .and_hms_opt(14, 30, 0)
976            .unwrap();
977        let serial = crate::cell::datetime_to_serial(dt);
978        wb.set_cell_value("Sheet1", "A1", CellValue::Date(serial))
979            .unwrap();
980        wb.set_cell_style("Sheet1", "A1", style_id).unwrap();
981
982        let dir = tempfile::TempDir::new().unwrap();
983        let path = dir.path().join("date_test.xlsx");
984        wb.save(&path).unwrap();
985
986        let wb2 = Workbook::open(&path).unwrap();
987        let val = wb2.get_cell_value("Sheet1", "A1").unwrap();
988        assert_eq!(val, CellValue::Date(serial));
989    }
990
991    #[test]
992    fn test_date_from_naive_date_conversion() {
993        let date = chrono::NaiveDate::from_ymd_opt(2024, 1, 1).unwrap();
994        let cv: CellValue = date.into();
995        match cv {
996            CellValue::Date(s) => {
997                let roundtripped = crate::cell::serial_to_date(s).unwrap();
998                assert_eq!(roundtripped, date);
999            }
1000            _ => panic!("expected Date variant"),
1001        }
1002    }
1003
1004    #[test]
1005    fn test_set_and_get_formula_value() {
1006        let mut wb = Workbook::new();
1007        wb.set_cell_value(
1008            "Sheet1",
1009            "A1",
1010            CellValue::Formula {
1011                expr: "SUM(B1:B10)".to_string(),
1012                result: None,
1013            },
1014        )
1015        .unwrap();
1016        let val = wb.get_cell_value("Sheet1", "A1").unwrap();
1017        match val {
1018            CellValue::Formula { expr, .. } => {
1019                assert_eq!(expr, "SUM(B1:B10)");
1020            }
1021            other => panic!("expected Formula, got {:?}", other),
1022        }
1023    }
1024
1025    #[test]
1026    fn test_set_i32_value() {
1027        let mut wb = Workbook::new();
1028        wb.set_cell_value("Sheet1", "A1", 100i32).unwrap();
1029        let val = wb.get_cell_value("Sheet1", "A1").unwrap();
1030        assert_eq!(val, CellValue::Number(100.0));
1031    }
1032
1033    #[test]
1034    fn test_set_string_at_max_length() {
1035        let mut wb = Workbook::new();
1036        let max_string = "x".repeat(MAX_CELL_CHARS);
1037        wb.set_cell_value("Sheet1", "A1", max_string.as_str())
1038            .unwrap();
1039        let val = wb.get_cell_value("Sheet1", "A1").unwrap();
1040        assert_eq!(val, CellValue::String(max_string));
1041    }
1042
1043    #[test]
1044    fn test_set_cells_different_rows() {
1045        let mut wb = Workbook::new();
1046        wb.set_cell_value("Sheet1", "A1", "row1").unwrap();
1047        wb.set_cell_value("Sheet1", "A3", "row3").unwrap();
1048        wb.set_cell_value("Sheet1", "A2", "row2").unwrap(); // inserted between
1049
1050        assert_eq!(
1051            wb.get_cell_value("Sheet1", "A1").unwrap(),
1052            CellValue::String("row1".to_string())
1053        );
1054        assert_eq!(
1055            wb.get_cell_value("Sheet1", "A2").unwrap(),
1056            CellValue::String("row2".to_string())
1057        );
1058        assert_eq!(
1059            wb.get_cell_value("Sheet1", "A3").unwrap(),
1060            CellValue::String("row3".to_string())
1061        );
1062    }
1063
1064    #[test]
1065    fn test_string_deduplication_in_sst() {
1066        let mut wb = Workbook::new();
1067        wb.set_cell_value("Sheet1", "A1", "same").unwrap();
1068        wb.set_cell_value("Sheet1", "A2", "same").unwrap();
1069        wb.set_cell_value("Sheet1", "A3", "different").unwrap();
1070
1071        // Both A1 and A2 should point to the same SST index
1072        assert_eq!(wb.sst_runtime.len(), 2);
1073        assert_eq!(
1074            wb.get_cell_value("Sheet1", "A1").unwrap(),
1075            CellValue::String("same".to_string())
1076        );
1077        assert_eq!(
1078            wb.get_cell_value("Sheet1", "A2").unwrap(),
1079            CellValue::String("same".to_string())
1080        );
1081    }
1082
1083    #[test]
1084    fn test_add_style_returns_id() {
1085        use crate::style::{FontStyle, Style};
1086
1087        let mut wb = Workbook::new();
1088        let style = Style {
1089            font: Some(FontStyle {
1090                bold: true,
1091                ..FontStyle::default()
1092            }),
1093            ..Style::default()
1094        };
1095        let id = wb.add_style(&style).unwrap();
1096        assert!(id > 0);
1097    }
1098
1099    #[test]
1100    fn test_get_cell_style_unstyled_cell_returns_none() {
1101        let wb = Workbook::new();
1102        let result = wb.get_cell_style("Sheet1", "A1").unwrap();
1103        assert!(result.is_none());
1104    }
1105
1106    #[test]
1107    fn test_set_cell_style_on_existing_value() {
1108        use crate::style::{FontStyle, Style};
1109
1110        let mut wb = Workbook::new();
1111        wb.set_cell_value("Sheet1", "A1", "Hello").unwrap();
1112
1113        let style = Style {
1114            font: Some(FontStyle {
1115                bold: true,
1116                ..FontStyle::default()
1117            }),
1118            ..Style::default()
1119        };
1120        let style_id = wb.add_style(&style).unwrap();
1121        wb.set_cell_style("Sheet1", "A1", style_id).unwrap();
1122
1123        let retrieved_id = wb.get_cell_style("Sheet1", "A1").unwrap();
1124        assert_eq!(retrieved_id, Some(style_id));
1125
1126        // The value should still be there.
1127        let val = wb.get_cell_value("Sheet1", "A1").unwrap();
1128        assert_eq!(val, CellValue::String("Hello".to_string()));
1129    }
1130
1131    #[test]
1132    fn test_set_cell_style_on_empty_cell_creates_cell() {
1133        use crate::style::{FontStyle, Style};
1134
1135        let mut wb = Workbook::new();
1136        let style = Style {
1137            font: Some(FontStyle {
1138                bold: true,
1139                ..FontStyle::default()
1140            }),
1141            ..Style::default()
1142        };
1143        let style_id = wb.add_style(&style).unwrap();
1144
1145        // Set style on a cell that doesn't exist yet.
1146        wb.set_cell_style("Sheet1", "B5", style_id).unwrap();
1147
1148        let retrieved_id = wb.get_cell_style("Sheet1", "B5").unwrap();
1149        assert_eq!(retrieved_id, Some(style_id));
1150
1151        // The cell value should be empty.
1152        let val = wb.get_cell_value("Sheet1", "B5").unwrap();
1153        assert_eq!(val, CellValue::Empty);
1154    }
1155
1156    #[test]
1157    fn test_set_cell_style_invalid_id() {
1158        let mut wb = Workbook::new();
1159        let result = wb.set_cell_style("Sheet1", "A1", 999);
1160        assert!(result.is_err());
1161        assert!(matches!(result.unwrap_err(), Error::StyleNotFound { .. }));
1162    }
1163
1164    #[test]
1165    fn test_set_cell_style_sheet_not_found() {
1166        let mut wb = Workbook::new();
1167        let style = crate::style::Style::default();
1168        let style_id = wb.add_style(&style).unwrap();
1169        let result = wb.set_cell_style("NoSuchSheet", "A1", style_id);
1170        assert!(result.is_err());
1171        assert!(matches!(result.unwrap_err(), Error::SheetNotFound { .. }));
1172    }
1173
1174    #[test]
1175    fn test_get_cell_style_sheet_not_found() {
1176        let wb = Workbook::new();
1177        let result = wb.get_cell_style("NoSuchSheet", "A1");
1178        assert!(result.is_err());
1179        assert!(matches!(result.unwrap_err(), Error::SheetNotFound { .. }));
1180    }
1181
1182    #[test]
1183    fn test_style_roundtrip_save_open() {
1184        use crate::style::{
1185            AlignmentStyle, BorderLineStyle, BorderSideStyle, BorderStyle, FillStyle, FontStyle,
1186            HorizontalAlign, NumFmtStyle, PatternType, Style, StyleColor, VerticalAlign,
1187        };
1188
1189        let dir = TempDir::new().unwrap();
1190        let path = dir.path().join("style_roundtrip.xlsx");
1191
1192        let mut wb = Workbook::new();
1193        wb.set_cell_value("Sheet1", "A1", "Styled").unwrap();
1194
1195        let style = Style {
1196            font: Some(FontStyle {
1197                name: Some("Arial".to_string()),
1198                size: Some(14.0),
1199                bold: true,
1200                italic: true,
1201                color: Some(StyleColor::Rgb("FFFF0000".to_string())),
1202                ..FontStyle::default()
1203            }),
1204            fill: Some(FillStyle {
1205                pattern: PatternType::Solid,
1206                fg_color: Some(StyleColor::Rgb("FFFFFF00".to_string())),
1207                bg_color: None,
1208                gradient: None,
1209            }),
1210            border: Some(BorderStyle {
1211                left: Some(BorderSideStyle {
1212                    style: BorderLineStyle::Thin,
1213                    color: None,
1214                }),
1215                right: Some(BorderSideStyle {
1216                    style: BorderLineStyle::Thin,
1217                    color: None,
1218                }),
1219                top: Some(BorderSideStyle {
1220                    style: BorderLineStyle::Thin,
1221                    color: None,
1222                }),
1223                bottom: Some(BorderSideStyle {
1224                    style: BorderLineStyle::Thin,
1225                    color: None,
1226                }),
1227                diagonal: None,
1228            }),
1229            alignment: Some(AlignmentStyle {
1230                horizontal: Some(HorizontalAlign::Center),
1231                vertical: Some(VerticalAlign::Center),
1232                wrap_text: true,
1233                ..AlignmentStyle::default()
1234            }),
1235            num_fmt: Some(NumFmtStyle::Custom("#,##0.00".to_string())),
1236            protection: None,
1237        };
1238        let style_id = wb.add_style(&style).unwrap();
1239        wb.set_cell_style("Sheet1", "A1", style_id).unwrap();
1240        wb.save(&path).unwrap();
1241
1242        // Re-open and verify.
1243        let wb2 = Workbook::open(&path).unwrap();
1244        let retrieved_id = wb2.get_cell_style("Sheet1", "A1").unwrap();
1245        assert_eq!(retrieved_id, Some(style_id));
1246
1247        // Verify the value is still there.
1248        let val = wb2.get_cell_value("Sheet1", "A1").unwrap();
1249        assert_eq!(val, CellValue::String("Styled".to_string()));
1250
1251        // Reverse-lookup the style to verify components survived the roundtrip.
1252        let retrieved_style = crate::style::get_style(&wb2.stylesheet, style_id).unwrap();
1253        assert!(retrieved_style.font.is_some());
1254        let font = retrieved_style.font.unwrap();
1255        assert!(font.bold);
1256        assert!(font.italic);
1257        assert_eq!(font.name, Some("Arial".to_string()));
1258
1259        assert!(retrieved_style.fill.is_some());
1260        let fill = retrieved_style.fill.unwrap();
1261        assert_eq!(fill.pattern, PatternType::Solid);
1262
1263        assert!(retrieved_style.alignment.is_some());
1264        let align = retrieved_style.alignment.unwrap();
1265        assert_eq!(align.horizontal, Some(HorizontalAlign::Center));
1266        assert_eq!(align.vertical, Some(VerticalAlign::Center));
1267        assert!(align.wrap_text);
1268    }
1269
1270    #[test]
1271    fn test_set_and_get_cell_rich_text() {
1272        use crate::rich_text::RichTextRun;
1273
1274        let mut wb = Workbook::new();
1275        let runs = vec![
1276            RichTextRun {
1277                text: "Bold".to_string(),
1278                font: None,
1279                size: None,
1280                bold: true,
1281                italic: false,
1282                color: None,
1283            },
1284            RichTextRun {
1285                text: " Normal".to_string(),
1286                font: None,
1287                size: None,
1288                bold: false,
1289                italic: false,
1290                color: None,
1291            },
1292        ];
1293        wb.set_cell_rich_text("Sheet1", "A1", runs.clone()).unwrap();
1294
1295        // The cell value should be a shared string whose plain text is "Bold Normal".
1296        let val = wb.get_cell_value("Sheet1", "A1").unwrap();
1297        assert_eq!(val.to_string(), "Bold Normal");
1298
1299        // get_cell_rich_text should return the runs.
1300        let got = wb.get_cell_rich_text("Sheet1", "A1").unwrap();
1301        assert!(got.is_some());
1302        let got_runs = got.unwrap();
1303        assert_eq!(got_runs.len(), 2);
1304        assert_eq!(got_runs[0].text, "Bold");
1305        assert!(got_runs[0].bold);
1306        assert_eq!(got_runs[1].text, " Normal");
1307        assert!(!got_runs[1].bold);
1308    }
1309
1310    #[test]
1311    fn test_get_cell_rich_text_returns_none_for_plain() {
1312        let mut wb = Workbook::new();
1313        wb.set_cell_value("Sheet1", "A1", CellValue::String("plain".to_string()))
1314            .unwrap();
1315        let got = wb.get_cell_rich_text("Sheet1", "A1").unwrap();
1316        assert!(got.is_none());
1317    }
1318
1319    #[test]
1320    fn test_rich_text_roundtrip_save_open() {
1321        use crate::rich_text::RichTextRun;
1322
1323        let dir = TempDir::new().unwrap();
1324        let path = dir.path().join("rich_text.xlsx");
1325
1326        // Note: quick-xml's serde deserializer trims leading and trailing
1327        // whitespace from text content. To avoid false failures, test text
1328        // values must not rely on boundary whitespace being preserved.
1329        let mut wb = Workbook::new();
1330        let runs = vec![
1331            RichTextRun {
1332                text: "Hello".to_string(),
1333                font: Some("Arial".to_string()),
1334                size: Some(14.0),
1335                bold: true,
1336                italic: false,
1337                color: Some("#FF0000".to_string()),
1338            },
1339            RichTextRun {
1340                text: "World".to_string(),
1341                font: None,
1342                size: None,
1343                bold: false,
1344                italic: true,
1345                color: None,
1346            },
1347        ];
1348        wb.set_cell_rich_text("Sheet1", "B2", runs).unwrap();
1349        wb.save(&path).unwrap();
1350
1351        let wb2 = Workbook::open(&path).unwrap();
1352        let val = wb2.get_cell_value("Sheet1", "B2").unwrap();
1353        assert_eq!(val.to_string(), "HelloWorld");
1354
1355        let got = wb2.get_cell_rich_text("Sheet1", "B2").unwrap();
1356        assert!(got.is_some());
1357        let got_runs = got.unwrap();
1358        assert_eq!(got_runs.len(), 2);
1359        assert_eq!(got_runs[0].text, "Hello");
1360        assert!(got_runs[0].bold);
1361        assert_eq!(got_runs[0].font.as_deref(), Some("Arial"));
1362        assert_eq!(got_runs[0].size, Some(14.0));
1363        assert_eq!(got_runs[0].color.as_deref(), Some("#FF0000"));
1364        assert_eq!(got_runs[1].text, "World");
1365        assert!(got_runs[1].italic);
1366        assert!(!got_runs[1].bold);
1367    }
1368
1369    #[test]
1370    fn test_set_cell_formula() {
1371        let mut wb = Workbook::new();
1372        wb.set_cell_formula("Sheet1", "A1", "SUM(B1:B10)").unwrap();
1373        let val = wb.get_cell_value("Sheet1", "A1").unwrap();
1374        match val {
1375            CellValue::Formula { expr, .. } => assert_eq!(expr, "SUM(B1:B10)"),
1376            other => panic!("expected Formula, got {:?}", other),
1377        }
1378    }
1379
1380    #[test]
1381    fn test_fill_formula_basic() {
1382        let mut wb = Workbook::new();
1383        wb.fill_formula("Sheet1", "D2:D5", "SUM(A2:C2)").unwrap();
1384
1385        // D2 should have the base formula unchanged
1386        match wb.get_cell_value("Sheet1", "D2").unwrap() {
1387            CellValue::Formula { expr, .. } => assert_eq!(expr, "SUM(A2:C2)"),
1388            other => panic!("D2: expected Formula, got {:?}", other),
1389        }
1390        // D3 should have row shifted by 1
1391        match wb.get_cell_value("Sheet1", "D3").unwrap() {
1392            CellValue::Formula { expr, .. } => assert_eq!(expr, "SUM(A3:C3)"),
1393            other => panic!("D3: expected Formula, got {:?}", other),
1394        }
1395        // D4 should have row shifted by 2
1396        match wb.get_cell_value("Sheet1", "D4").unwrap() {
1397            CellValue::Formula { expr, .. } => assert_eq!(expr, "SUM(A4:C4)"),
1398            other => panic!("D4: expected Formula, got {:?}", other),
1399        }
1400        // D5 should have row shifted by 3
1401        match wb.get_cell_value("Sheet1", "D5").unwrap() {
1402            CellValue::Formula { expr, .. } => assert_eq!(expr, "SUM(A5:C5)"),
1403            other => panic!("D5: expected Formula, got {:?}", other),
1404        }
1405    }
1406
1407    #[test]
1408    fn test_fill_formula_preserves_absolute_refs() {
1409        let mut wb = Workbook::new();
1410        wb.fill_formula("Sheet1", "B1:B3", "$A$1*A1").unwrap();
1411
1412        match wb.get_cell_value("Sheet1", "B1").unwrap() {
1413            CellValue::Formula { expr, .. } => assert_eq!(expr, "$A$1*A1"),
1414            other => panic!("B1: expected Formula, got {:?}", other),
1415        }
1416        match wb.get_cell_value("Sheet1", "B2").unwrap() {
1417            CellValue::Formula { expr, .. } => assert_eq!(expr, "$A$1*A2"),
1418            other => panic!("B2: expected Formula, got {:?}", other),
1419        }
1420        match wb.get_cell_value("Sheet1", "B3").unwrap() {
1421            CellValue::Formula { expr, .. } => assert_eq!(expr, "$A$1*A3"),
1422            other => panic!("B3: expected Formula, got {:?}", other),
1423        }
1424    }
1425
1426    #[test]
1427    fn test_fill_formula_single_cell() {
1428        let mut wb = Workbook::new();
1429        wb.fill_formula("Sheet1", "A1:A1", "B1+C1").unwrap();
1430        match wb.get_cell_value("Sheet1", "A1").unwrap() {
1431            CellValue::Formula { expr, .. } => assert_eq!(expr, "B1+C1"),
1432            other => panic!("expected Formula, got {:?}", other),
1433        }
1434    }
1435
1436    #[test]
1437    fn test_fill_formula_invalid_range() {
1438        let mut wb = Workbook::new();
1439        assert!(wb.fill_formula("Sheet1", "INVALID", "A1").is_err());
1440    }
1441
1442    #[test]
1443    fn test_fill_formula_multi_column_range_rejected() {
1444        let mut wb = Workbook::new();
1445        assert!(wb.fill_formula("Sheet1", "A1:B5", "C1").is_err());
1446    }
1447
1448    #[test]
1449    fn test_set_cell_values_batch() {
1450        let mut wb = Workbook::new();
1451        wb.set_cell_values(
1452            "Sheet1",
1453            vec![
1454                ("A1".to_string(), CellValue::String("hello".to_string())),
1455                ("B1".to_string(), CellValue::Number(42.0)),
1456                ("C1".to_string(), CellValue::Bool(true)),
1457                ("A2".to_string(), CellValue::String("world".to_string())),
1458            ],
1459        )
1460        .unwrap();
1461
1462        assert_eq!(
1463            wb.get_cell_value("Sheet1", "A1").unwrap(),
1464            CellValue::String("hello".to_string())
1465        );
1466        assert_eq!(
1467            wb.get_cell_value("Sheet1", "B1").unwrap(),
1468            CellValue::Number(42.0)
1469        );
1470        assert_eq!(
1471            wb.get_cell_value("Sheet1", "C1").unwrap(),
1472            CellValue::Bool(true)
1473        );
1474        assert_eq!(
1475            wb.get_cell_value("Sheet1", "A2").unwrap(),
1476            CellValue::String("world".to_string())
1477        );
1478    }
1479
1480    #[test]
1481    fn test_set_cell_values_empty_removes_cell() {
1482        let mut wb = Workbook::new();
1483        wb.set_cell_value("Sheet1", "A1", "existing").unwrap();
1484        wb.set_cell_values("Sheet1", vec![("A1".to_string(), CellValue::Empty)])
1485            .unwrap();
1486        assert_eq!(wb.get_cell_value("Sheet1", "A1").unwrap(), CellValue::Empty);
1487    }
1488
1489    #[test]
1490    fn test_set_sheet_data_basic() {
1491        let mut wb = Workbook::new();
1492        wb.set_sheet_data(
1493            "Sheet1",
1494            vec![
1495                vec![
1496                    CellValue::String("Name".to_string()),
1497                    CellValue::String("Age".to_string()),
1498                ],
1499                vec![
1500                    CellValue::String("Alice".to_string()),
1501                    CellValue::Number(30.0),
1502                ],
1503                vec![
1504                    CellValue::String("Bob".to_string()),
1505                    CellValue::Number(25.0),
1506                ],
1507            ],
1508            1,
1509            1,
1510        )
1511        .unwrap();
1512
1513        assert_eq!(
1514            wb.get_cell_value("Sheet1", "A1").unwrap(),
1515            CellValue::String("Name".to_string())
1516        );
1517        assert_eq!(
1518            wb.get_cell_value("Sheet1", "B1").unwrap(),
1519            CellValue::String("Age".to_string())
1520        );
1521        assert_eq!(
1522            wb.get_cell_value("Sheet1", "A2").unwrap(),
1523            CellValue::String("Alice".to_string())
1524        );
1525        assert_eq!(
1526            wb.get_cell_value("Sheet1", "B2").unwrap(),
1527            CellValue::Number(30.0)
1528        );
1529        assert_eq!(
1530            wb.get_cell_value("Sheet1", "A3").unwrap(),
1531            CellValue::String("Bob".to_string())
1532        );
1533        assert_eq!(
1534            wb.get_cell_value("Sheet1", "B3").unwrap(),
1535            CellValue::Number(25.0)
1536        );
1537    }
1538
1539    #[test]
1540    fn test_set_sheet_data_with_offset() {
1541        let mut wb = Workbook::new();
1542        // Start at C3 (col=3, row=3)
1543        wb.set_sheet_data(
1544            "Sheet1",
1545            vec![
1546                vec![CellValue::Number(1.0), CellValue::Number(2.0)],
1547                vec![CellValue::Number(3.0), CellValue::Number(4.0)],
1548            ],
1549            3,
1550            3,
1551        )
1552        .unwrap();
1553
1554        assert_eq!(
1555            wb.get_cell_value("Sheet1", "C3").unwrap(),
1556            CellValue::Number(1.0)
1557        );
1558        assert_eq!(
1559            wb.get_cell_value("Sheet1", "D3").unwrap(),
1560            CellValue::Number(2.0)
1561        );
1562        assert_eq!(
1563            wb.get_cell_value("Sheet1", "C4").unwrap(),
1564            CellValue::Number(3.0)
1565        );
1566        assert_eq!(
1567            wb.get_cell_value("Sheet1", "D4").unwrap(),
1568            CellValue::Number(4.0)
1569        );
1570        // A1 should still be empty
1571        assert_eq!(wb.get_cell_value("Sheet1", "A1").unwrap(), CellValue::Empty);
1572    }
1573
1574    #[test]
1575    fn test_set_sheet_data_roundtrip() {
1576        let dir = TempDir::new().unwrap();
1577        let path = dir.path().join("batch_roundtrip.xlsx");
1578
1579        let mut wb = Workbook::new();
1580        wb.set_sheet_data(
1581            "Sheet1",
1582            vec![
1583                vec![
1584                    CellValue::String("Header1".to_string()),
1585                    CellValue::String("Header2".to_string()),
1586                ],
1587                vec![CellValue::Number(100.0), CellValue::Bool(true)],
1588            ],
1589            1,
1590            1,
1591        )
1592        .unwrap();
1593        wb.save(&path).unwrap();
1594
1595        let wb2 = Workbook::open(&path).unwrap();
1596        assert_eq!(
1597            wb2.get_cell_value("Sheet1", "A1").unwrap(),
1598            CellValue::String("Header1".to_string())
1599        );
1600        assert_eq!(
1601            wb2.get_cell_value("Sheet1", "B1").unwrap(),
1602            CellValue::String("Header2".to_string())
1603        );
1604        assert_eq!(
1605            wb2.get_cell_value("Sheet1", "A2").unwrap(),
1606            CellValue::Number(100.0)
1607        );
1608        assert_eq!(
1609            wb2.get_cell_value("Sheet1", "B2").unwrap(),
1610            CellValue::Bool(true)
1611        );
1612    }
1613
1614    #[test]
1615    fn test_set_row_values() {
1616        let mut wb = Workbook::new();
1617        wb.set_row_values(
1618            "Sheet1",
1619            1,
1620            1,
1621            vec![
1622                CellValue::String("A".to_string()),
1623                CellValue::String("B".to_string()),
1624                CellValue::String("C".to_string()),
1625            ],
1626        )
1627        .unwrap();
1628
1629        assert_eq!(
1630            wb.get_cell_value("Sheet1", "A1").unwrap(),
1631            CellValue::String("A".to_string())
1632        );
1633        assert_eq!(
1634            wb.get_cell_value("Sheet1", "B1").unwrap(),
1635            CellValue::String("B".to_string())
1636        );
1637        assert_eq!(
1638            wb.get_cell_value("Sheet1", "C1").unwrap(),
1639            CellValue::String("C".to_string())
1640        );
1641    }
1642
1643    #[test]
1644    fn test_set_row_values_with_offset() {
1645        let mut wb = Workbook::new();
1646        // Start at column D (col=4)
1647        wb.set_row_values(
1648            "Sheet1",
1649            2,
1650            4,
1651            vec![CellValue::Number(10.0), CellValue::Number(20.0)],
1652        )
1653        .unwrap();
1654
1655        assert_eq!(
1656            wb.get_cell_value("Sheet1", "D2").unwrap(),
1657            CellValue::Number(10.0)
1658        );
1659        assert_eq!(
1660            wb.get_cell_value("Sheet1", "E2").unwrap(),
1661            CellValue::Number(20.0)
1662        );
1663    }
1664
1665    #[test]
1666    fn test_set_sheet_data_merges_with_existing() {
1667        let mut wb = Workbook::new();
1668        wb.set_cell_value("Sheet1", "A1", "existing").unwrap();
1669        wb.set_sheet_data(
1670            "Sheet1",
1671            vec![vec![CellValue::Empty, CellValue::String("new".to_string())]],
1672            1,
1673            1,
1674        )
1675        .unwrap();
1676
1677        // A1 was cleared by Empty
1678        assert_eq!(wb.get_cell_value("Sheet1", "A1").unwrap(), CellValue::Empty);
1679        // B1 was added
1680        assert_eq!(
1681            wb.get_cell_value("Sheet1", "B1").unwrap(),
1682            CellValue::String("new".to_string())
1683        );
1684    }
1685
1686    /// Saves a workbook with a date-styled number cell and a plain-number
1687    /// cell for reuse across the interpretation round-trip tests below.
1688    fn write_workbook_with_date_style(path: &std::path::Path) {
1689        use crate::style::{builtin_num_fmts, NumFmtStyle, Style};
1690        let mut wb = Workbook::new();
1691        let date_style = wb
1692            .add_style(&Style {
1693                num_fmt: Some(NumFmtStyle::Builtin(builtin_num_fmts::DATE_MDY)),
1694                ..Style::default()
1695            })
1696            .unwrap();
1697        wb.set_cell_value("Sheet1", "A1", 46127.0_f64).unwrap();
1698        wb.set_cell_style("Sheet1", "A1", date_style).unwrap();
1699        wb.set_cell_value("Sheet1", "B1", 42.0_f64).unwrap();
1700        wb.save(path).unwrap();
1701    }
1702
1703    #[test]
1704    fn test_get_cell_value_num_fmt_default_promotes_date_styled_cells() {
1705        let dir = tempfile::TempDir::new().unwrap();
1706        let path = dir.path().join("get_cell_value_default.xlsx");
1707        write_workbook_with_date_style(&path);
1708
1709        // Default interpretation is `NumFmt`, so date-styled numeric cells
1710        // come back as dates while plain numbers stay numbers.
1711        let wb = Workbook::open(&path).unwrap();
1712        assert_eq!(
1713            wb.get_cell_value("Sheet1", "A1").unwrap(),
1714            CellValue::Date(46127.0)
1715        );
1716        assert_eq!(
1717            wb.get_cell_value("Sheet1", "B1").unwrap(),
1718            CellValue::Number(42.0)
1719        );
1720    }
1721
1722    #[test]
1723    fn test_get_cell_value_cell_type_keeps_numbers() {
1724        let dir = tempfile::TempDir::new().unwrap();
1725        let path = dir.path().join("get_cell_value_cell_type.xlsx");
1726        write_workbook_with_date_style(&path);
1727
1728        // Opting into `CellType` makes the reader strictly follow the cell
1729        // type attribute, so even date-styled number cells stay numbers.
1730        let wb = Workbook::open_with_options(
1731            &path,
1732            &crate::workbook::OpenOptions::new()
1733                .date_interpretation(crate::workbook::DateInterpretation::CellType),
1734        )
1735        .unwrap();
1736        assert_eq!(
1737            wb.get_cell_value("Sheet1", "A1").unwrap(),
1738            CellValue::Number(46127.0)
1739        );
1740        assert_eq!(
1741            wb.get_cell_value("Sheet1", "B1").unwrap(),
1742            CellValue::Number(42.0)
1743        );
1744    }
1745
1746    #[test]
1747    fn test_get_rows_num_fmt_default_promotes_date_styled_cells() {
1748        let dir = tempfile::TempDir::new().unwrap();
1749        let path = dir.path().join("get_rows_default.xlsx");
1750        write_workbook_with_date_style(&path);
1751
1752        // `get_rows` now honors the same interpretation option as
1753        // `get_cell_value` and the streaming reader.
1754        let wb = Workbook::open(&path).unwrap();
1755        let rows = wb.get_rows("Sheet1").unwrap();
1756        assert_eq!(rows.len(), 1);
1757        let cells = &rows[0].1;
1758        assert_eq!(cells[0].1, CellValue::Date(46127.0));
1759        assert_eq!(cells[1].1, CellValue::Number(42.0));
1760    }
1761
1762    #[test]
1763    fn test_get_rows_cell_type_keeps_numbers() {
1764        let dir = tempfile::TempDir::new().unwrap();
1765        let path = dir.path().join("get_rows_cell_type.xlsx");
1766        write_workbook_with_date_style(&path);
1767
1768        let wb = Workbook::open_with_options(
1769            &path,
1770            &crate::workbook::OpenOptions::new()
1771                .date_interpretation(crate::workbook::DateInterpretation::CellType),
1772        )
1773        .unwrap();
1774        let rows = wb.get_rows("Sheet1").unwrap();
1775        assert_eq!(rows.len(), 1);
1776        let cells = &rows[0].1;
1777        assert_eq!(cells[0].1, CellValue::Number(46127.0));
1778        assert_eq!(cells[1].1, CellValue::Number(42.0));
1779    }
1780}
sheetkit_core/workbook/cell_ops.rs

sheetkit_core/workbook/
cell_ops.rs
