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

sheetkit_core/workbook/
cell_ops.rs
