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

sheetkit_core/workbook/
cell_ops.rs
