sheetkit_core/workbook/
cell_ops.rs

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

sheetkit_core/workbook/
cell_ops.rs
