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
10            .worksheets
11            .iter()
12            .find(|(name, _)| name == sheet)
13            .map(|(_, ws)| ws)
14            .ok_or_else(|| Error::SheetNotFound {
15                name: sheet.to_string(),
16            })?;
17
18        let (col, row) = cell_name_to_coordinates(cell)?;
19        let cell_ref = crate::utils::cell_ref::coordinates_to_cell_name(col, row)?;
20
21        // Find the row.
22        let xml_row = ws.sheet_data.rows.iter().find(|r| r.r == row);
23        let xml_row = match xml_row {
24            Some(r) => r,
25            None => return Ok(CellValue::Empty),
26        };
27
28        // Find the cell.
29        let xml_cell = xml_row.cells.iter().find(|c| c.r == cell_ref);
30        let xml_cell = match xml_cell {
31            Some(c) => c,
32            None => return Ok(CellValue::Empty),
33        };
34
35        self.xml_cell_to_value(xml_cell)
36    }
37
38    /// Set the value of a cell.
39    ///
40    /// The value can be any type that implements `Into<CellValue>`, including
41    /// `&str`, `String`, `f64`, `i32`, `i64`, and `bool`.
42    ///
43    /// Setting a cell to [`CellValue::Empty`] removes the cell from the row.
44    pub fn set_cell_value(
45        &mut self,
46        sheet: &str,
47        cell: &str,
48        value: impl Into<CellValue>,
49    ) -> Result<()> {
50        let value = value.into();
51
52        // Validate string length.
53        if let CellValue::String(ref s) = value {
54            if s.len() > MAX_CELL_CHARS {
55                return Err(Error::CellValueTooLong {
56                    length: s.len(),
57                    max: MAX_CELL_CHARS,
58                });
59            }
60        }
61
62        let ws = self
63            .worksheets
64            .iter_mut()
65            .find(|(name, _)| name == sheet)
66            .map(|(_, ws)| ws)
67            .ok_or_else(|| Error::SheetNotFound {
68                name: sheet.to_string(),
69            })?;
70
71        let (col, row_num) = cell_name_to_coordinates(cell)?;
72        let cell_ref = crate::utils::cell_ref::coordinates_to_cell_name(col, row_num)?;
73
74        // Find or create the row (keep rows sorted by row number).
75        let row_idx = match ws.sheet_data.rows.iter().position(|r| r.r >= row_num) {
76            Some(idx) if ws.sheet_data.rows[idx].r == row_num => idx,
77            Some(idx) => {
78                ws.sheet_data.rows.insert(idx, new_row(row_num));
79                idx
80            }
81            None => {
82                ws.sheet_data.rows.push(new_row(row_num));
83                ws.sheet_data.rows.len() - 1
84            }
85        };
86
87        let row = &mut ws.sheet_data.rows[row_idx];
88
89        // Handle Empty: remove the cell if present.
90        if value == CellValue::Empty {
91            row.cells.retain(|c| c.r != cell_ref);
92            return Ok(());
93        }
94
95        // Find or create the cell.
96        let cell_idx = match row.cells.iter().position(|c| c.r == cell_ref) {
97            Some(idx) => idx,
98            None => {
99                // Insert in column order.
100                let insert_pos = row
101                    .cells
102                    .iter()
103                    .position(|c| {
104                        cell_name_to_coordinates(&c.r)
105                            .map(|(c_col, _)| c_col > col)
106                            .unwrap_or(false)
107                    })
108                    .unwrap_or(row.cells.len());
109                row.cells.insert(
110                    insert_pos,
111                    Cell {
112                        r: cell_ref,
113                        s: None,
114                        t: None,
115                        v: None,
116                        f: None,
117                        is: None,
118                    },
119                );
120                insert_pos
121            }
122        };
123
124        let xml_cell = &mut row.cells[cell_idx];
125        value_to_xml_cell(&mut self.sst_runtime, xml_cell, value);
126
127        Ok(())
128    }
129
130    /// Convert an XML Cell to a CellValue.
131    pub(crate) fn xml_cell_to_value(&self, xml_cell: &Cell) -> Result<CellValue> {
132        // Check for formula first.
133        if let Some(ref formula) = xml_cell.f {
134            let expr = formula.value.clone().unwrap_or_default();
135            let result = match (&xml_cell.t, &xml_cell.v) {
136                (Some(t), Some(v)) if t == "b" => Some(Box::new(CellValue::Bool(v == "1"))),
137                (Some(t), Some(v)) if t == "e" => Some(Box::new(CellValue::Error(v.clone()))),
138                (_, Some(v)) => v
139                    .parse::<f64>()
140                    .ok()
141                    .map(|n| Box::new(CellValue::Number(n))),
142                _ => None,
143            };
144            return Ok(CellValue::Formula { expr, result });
145        }
146
147        let cell_type = xml_cell.t.as_deref();
148        let cell_value = xml_cell.v.as_deref();
149
150        match (cell_type, cell_value) {
151            // Shared string
152            (Some("s"), Some(v)) => {
153                let idx: usize = v
154                    .parse()
155                    .map_err(|_| Error::Internal(format!("invalid SST index: {v}")))?;
156                let s = self.sst_runtime.get(idx).unwrap_or("").to_string();
157                Ok(CellValue::String(s))
158            }
159            // Boolean
160            (Some("b"), Some(v)) => Ok(CellValue::Bool(v == "1")),
161            // Error
162            (Some("e"), Some(v)) => Ok(CellValue::Error(v.to_string())),
163            // Inline string
164            (Some("inlineStr"), _) => {
165                let s = xml_cell
166                    .is
167                    .as_ref()
168                    .and_then(|is| is.t.clone())
169                    .unwrap_or_default();
170                Ok(CellValue::String(s))
171            }
172            // Formula string (cached string result)
173            (Some("str"), Some(v)) => Ok(CellValue::String(v.to_string())),
174            // Number (explicit or default type) -- may be a date if styled.
175            (None | Some("n"), Some(v)) => {
176                let n: f64 = v
177                    .parse()
178                    .map_err(|_| Error::Internal(format!("invalid number: {v}")))?;
179                // Check whether this cell has a date number format.
180                if self.is_date_styled_cell(xml_cell) {
181                    return Ok(CellValue::Date(n));
182                }
183                Ok(CellValue::Number(n))
184            }
185            // No value
186            _ => Ok(CellValue::Empty),
187        }
188    }
189
190    /// Check whether a cell's style indicates a date/time number format.
191    pub(crate) fn is_date_styled_cell(&self, xml_cell: &Cell) -> bool {
192        let style_idx = match xml_cell.s {
193            Some(idx) => idx as usize,
194            None => return false,
195        };
196        let xf = match self.stylesheet.cell_xfs.xfs.get(style_idx) {
197            Some(xf) => xf,
198            None => return false,
199        };
200        let num_fmt_id = xf.num_fmt_id.unwrap_or(0);
201        // Check built-in date format IDs.
202        if crate::cell::is_date_num_fmt(num_fmt_id) {
203            return true;
204        }
205        // Check custom number formats for date patterns.
206        if num_fmt_id >= 164 {
207            if let Some(ref num_fmts) = self.stylesheet.num_fmts {
208                if let Some(nf) = num_fmts
209                    .num_fmts
210                    .iter()
211                    .find(|nf| nf.num_fmt_id == num_fmt_id)
212                {
213                    return crate::cell::is_date_format_code(&nf.format_code);
214                }
215            }
216        }
217        false
218    }
219
220    /// Register a new style and return its ID.
221    ///
222    /// The style is deduplicated: if an identical style already exists in
223    /// the stylesheet, the existing ID is returned.
224    pub fn add_style(&mut self, style: &crate::style::Style) -> Result<u32> {
225        crate::style::add_style(&mut self.stylesheet, style)
226    }
227
228    /// Get the style ID applied to a cell.
229    ///
230    /// Returns `None` if the cell does not exist or has no explicit style
231    /// (i.e. uses the default style 0).
232    pub fn get_cell_style(&self, sheet: &str, cell: &str) -> Result<Option<u32>> {
233        let ws = self.worksheet_ref(sheet)?;
234
235        let (col, row) = cell_name_to_coordinates(cell)?;
236        let cell_ref = crate::utils::cell_ref::coordinates_to_cell_name(col, row)?;
237
238        // Find the row.
239        let xml_row = match ws.sheet_data.rows.iter().find(|r| r.r == row) {
240            Some(r) => r,
241            None => return Ok(None),
242        };
243
244        // Find the cell.
245        let xml_cell = match xml_row.cells.iter().find(|c| c.r == cell_ref) {
246            Some(c) => c,
247            None => return Ok(None),
248        };
249
250        Ok(xml_cell.s)
251    }
252
253    /// Set the style ID for a cell.
254    ///
255    /// If the cell does not exist, an empty cell with just the style is created.
256    /// The `style_id` must be a valid index in cellXfs.
257    pub fn set_cell_style(&mut self, sheet: &str, cell: &str, style_id: u32) -> Result<()> {
258        // Validate the style_id.
259        if style_id as usize >= self.stylesheet.cell_xfs.xfs.len() {
260            return Err(Error::StyleNotFound { id: style_id });
261        }
262
263        let ws = self
264            .worksheets
265            .iter_mut()
266            .find(|(name, _)| name == sheet)
267            .map(|(_, ws)| ws)
268            .ok_or_else(|| Error::SheetNotFound {
269                name: sheet.to_string(),
270            })?;
271
272        let (col, row_num) = cell_name_to_coordinates(cell)?;
273        let cell_ref = crate::utils::cell_ref::coordinates_to_cell_name(col, row_num)?;
274
275        // Find or create the row (keep rows sorted by row number).
276        let row_idx = match ws.sheet_data.rows.iter().position(|r| r.r >= row_num) {
277            Some(idx) if ws.sheet_data.rows[idx].r == row_num => idx,
278            Some(idx) => {
279                ws.sheet_data.rows.insert(idx, new_row(row_num));
280                idx
281            }
282            None => {
283                ws.sheet_data.rows.push(new_row(row_num));
284                ws.sheet_data.rows.len() - 1
285            }
286        };
287
288        let row = &mut ws.sheet_data.rows[row_idx];
289
290        // Find or create the cell.
291        let cell_idx = match row.cells.iter().position(|c| c.r == cell_ref) {
292            Some(idx) => idx,
293            None => {
294                // Insert in column order.
295                let insert_pos = row
296                    .cells
297                    .iter()
298                    .position(|c| {
299                        cell_name_to_coordinates(&c.r)
300                            .map(|(c_col, _)| c_col > col)
301                            .unwrap_or(false)
302                    })
303                    .unwrap_or(row.cells.len());
304                row.cells.insert(
305                    insert_pos,
306                    Cell {
307                        r: cell_ref,
308                        s: None,
309                        t: None,
310                        v: None,
311                        f: None,
312                        is: None,
313                    },
314                );
315                insert_pos
316            }
317        };
318
319        row.cells[cell_idx].s = Some(style_id);
320        Ok(())
321    }
322
323    /// Merge a range of cells on the given sheet.
324    ///
325    /// `top_left` and `bottom_right` are cell references like "A1" and "C3".
326    /// Returns an error if the range overlaps with an existing merge region.
327    pub fn merge_cells(&mut self, sheet: &str, top_left: &str, bottom_right: &str) -> Result<()> {
328        let ws = self.worksheet_mut(sheet)?;
329        crate::merge::merge_cells(ws, top_left, bottom_right)
330    }
331
332    /// Remove a merged cell range from the given sheet.
333    ///
334    /// `reference` is the exact range string like "A1:C3".
335    pub fn unmerge_cell(&mut self, sheet: &str, reference: &str) -> Result<()> {
336        let ws = self.worksheet_mut(sheet)?;
337        crate::merge::unmerge_cell(ws, reference)
338    }
339
340    /// Get all merged cell ranges on the given sheet.
341    ///
342    /// Returns a list of range strings like `["A1:B2", "D1:F3"]`.
343    pub fn get_merge_cells(&self, sheet: &str) -> Result<Vec<String>> {
344        let ws = self.worksheet_ref(sheet)?;
345        Ok(crate::merge::get_merge_cells(ws))
346    }
347
348    /// Set a formula on a cell.
349    ///
350    /// This is a convenience wrapper around [`set_cell_value`] with
351    /// [`CellValue::Formula`].
352    pub fn set_cell_formula(&mut self, sheet: &str, cell: &str, formula: &str) -> Result<()> {
353        self.set_cell_value(
354            sheet,
355            cell,
356            CellValue::Formula {
357                expr: formula.to_string(),
358                result: None,
359            },
360        )
361    }
362
363    /// Fill a range of cells with a formula, adjusting row references for each
364    /// row relative to the first cell in the range.
365    ///
366    /// `range` is an A1-style range like `"D2:D10"`. The `formula` is the base
367    /// formula for the first cell of the range. For each subsequent row, the
368    /// row references in the formula are shifted by the row offset. Absolute
369    /// row references (`$1`) are left unchanged.
370    pub fn fill_formula(&mut self, sheet: &str, range: &str, formula: &str) -> Result<()> {
371        let parts: Vec<&str> = range.split(':').collect();
372        if parts.len() != 2 {
373            return Err(Error::InvalidCellReference(format!(
374                "invalid range: {range}"
375            )));
376        }
377        let (start_col, start_row) = cell_name_to_coordinates(parts[0])?;
378        let (end_col, end_row) = cell_name_to_coordinates(parts[1])?;
379
380        if start_col != end_col {
381            return Err(Error::InvalidCellReference(
382                "fill_formula only supports single-column ranges".to_string(),
383            ));
384        }
385
386        for row in start_row..=end_row {
387            let row_offset = row as i32 - start_row as i32;
388            let adjusted = if row_offset == 0 {
389                formula.to_string()
390            } else {
391                crate::cell_ref_shift::shift_cell_references_with_abs(
392                    formula,
393                    |col, r, _abs_col, abs_row| {
394                        if abs_row {
395                            (col, r)
396                        } else {
397                            (col, (r as i32 + row_offset) as u32)
398                        }
399                    },
400                )?
401            };
402            let cell_ref = crate::utils::cell_ref::coordinates_to_cell_name(start_col, row)?;
403            self.set_cell_formula(sheet, &cell_ref, &adjusted)?;
404        }
405        Ok(())
406    }
407
408    /// Set a cell to a rich text value (multiple formatted runs).
409    pub fn set_cell_rich_text(
410        &mut self,
411        sheet: &str,
412        cell: &str,
413        runs: Vec<crate::rich_text::RichTextRun>,
414    ) -> Result<()> {
415        self.set_cell_value(sheet, cell, CellValue::RichString(runs))
416    }
417
418    /// Get rich text runs for a cell, if it contains rich text.
419    ///
420    /// Returns `None` if the cell is empty, contains a plain string, or holds
421    /// a non-string value.
422    pub fn get_cell_rich_text(
423        &self,
424        sheet: &str,
425        cell: &str,
426    ) -> Result<Option<Vec<crate::rich_text::RichTextRun>>> {
427        let (col, row) = cell_name_to_coordinates(cell)?;
428        let sheet_idx = self
429            .worksheets
430            .iter()
431            .position(|(name, _)| name == sheet)
432            .ok_or_else(|| Error::SheetNotFound {
433                name: sheet.to_string(),
434            })?;
435        let ws = &self.worksheets[sheet_idx].1;
436
437        for xml_row in &ws.sheet_data.rows {
438            if xml_row.r == row {
439                for xml_cell in &xml_row.cells {
440                    let (cc, cr) = cell_name_to_coordinates(&xml_cell.r)?;
441                    if cc == col && cr == row {
442                        if xml_cell.t.as_deref() == Some("s") {
443                            if let Some(ref v) = xml_cell.v {
444                                if let Ok(idx) = v.parse::<usize>() {
445                                    return Ok(self.sst_runtime.get_rich_text(idx));
446                                }
447                            }
448                        }
449                        return Ok(None);
450                    }
451                }
452            }
453        }
454        Ok(None)
455    }
456}
457
458/// Write a CellValue into an XML Cell (mutating it in place).
459pub(crate) fn value_to_xml_cell(
460    sst: &mut SharedStringTable,
461    xml_cell: &mut Cell,
462    value: CellValue,
463) {
464    // Clear previous values.
465    xml_cell.t = None;
466    xml_cell.v = None;
467    xml_cell.f = None;
468    xml_cell.is = None;
469
470    match value {
471        CellValue::String(s) => {
472            let idx = sst.add(&s);
473            xml_cell.t = Some("s".to_string());
474            xml_cell.v = Some(idx.to_string());
475        }
476        CellValue::Number(n) => {
477            xml_cell.v = Some(n.to_string());
478        }
479        CellValue::Date(serial) => {
480            // Dates are stored as numbers in Excel. The style must apply a
481            // date number format for correct display.
482            xml_cell.v = Some(serial.to_string());
483        }
484        CellValue::Bool(b) => {
485            xml_cell.t = Some("b".to_string());
486            xml_cell.v = Some(if b { "1" } else { "0" }.to_string());
487        }
488        CellValue::Formula { expr, .. } => {
489            xml_cell.f = Some(CellFormula {
490                t: None,
491                reference: None,
492                si: None,
493                value: Some(expr),
494            });
495        }
496        CellValue::Error(e) => {
497            xml_cell.t = Some("e".to_string());
498            xml_cell.v = Some(e);
499        }
500        CellValue::Empty => {
501            // Already cleared above; the caller should have removed the cell.
502        }
503        CellValue::RichString(runs) => {
504            let idx = sst.add_rich_text(&runs);
505            xml_cell.t = Some("s".to_string());
506            xml_cell.v = Some(idx.to_string());
507        }
508    }
509}
510
511/// Create a new empty row with the given 1-based row number.
512pub(crate) fn new_row(row_num: u32) -> Row {
513    Row {
514        r: row_num,
515        spans: None,
516        s: None,
517        custom_format: None,
518        ht: None,
519        hidden: None,
520        custom_height: None,
521        outline_level: None,
522        cells: vec![],
523    }
524}
525
526#[cfg(test)]
527mod tests {
528    use super::*;
529    use tempfile::TempDir;
530
531    #[test]
532    fn test_set_and_get_string_value() {
533        let mut wb = Workbook::new();
534        wb.set_cell_value("Sheet1", "A1", "Hello").unwrap();
535        let val = wb.get_cell_value("Sheet1", "A1").unwrap();
536        assert_eq!(val, CellValue::String("Hello".to_string()));
537    }
538
539    #[test]
540    fn test_set_and_get_number_value() {
541        let mut wb = Workbook::new();
542        wb.set_cell_value("Sheet1", "B2", 42.5f64).unwrap();
543        let val = wb.get_cell_value("Sheet1", "B2").unwrap();
544        assert_eq!(val, CellValue::Number(42.5));
545    }
546
547    #[test]
548    fn test_set_and_get_bool_value() {
549        let mut wb = Workbook::new();
550        wb.set_cell_value("Sheet1", "C3", true).unwrap();
551        let val = wb.get_cell_value("Sheet1", "C3").unwrap();
552        assert_eq!(val, CellValue::Bool(true));
553
554        wb.set_cell_value("Sheet1", "D4", false).unwrap();
555        let val = wb.get_cell_value("Sheet1", "D4").unwrap();
556        assert_eq!(val, CellValue::Bool(false));
557    }
558
559    #[test]
560    fn test_set_value_sheet_not_found() {
561        let mut wb = Workbook::new();
562        let result = wb.set_cell_value("NoSuchSheet", "A1", "test");
563        assert!(result.is_err());
564        assert!(matches!(result.unwrap_err(), Error::SheetNotFound { .. }));
565    }
566
567    #[test]
568    fn test_get_value_sheet_not_found() {
569        let wb = Workbook::new();
570        let result = wb.get_cell_value("NoSuchSheet", "A1");
571        assert!(result.is_err());
572        assert!(matches!(result.unwrap_err(), Error::SheetNotFound { .. }));
573    }
574
575    #[test]
576    fn test_get_empty_cell_returns_empty() {
577        let wb = Workbook::new();
578        let val = wb.get_cell_value("Sheet1", "Z99").unwrap();
579        assert_eq!(val, CellValue::Empty);
580    }
581
582    #[test]
583    fn test_cell_value_roundtrip_save_open() {
584        let dir = TempDir::new().unwrap();
585        let path = dir.path().join("cell_roundtrip.xlsx");
586
587        let mut wb = Workbook::new();
588        wb.set_cell_value("Sheet1", "A1", "Hello").unwrap();
589        wb.set_cell_value("Sheet1", "B1", 42.0f64).unwrap();
590        wb.set_cell_value("Sheet1", "C1", true).unwrap();
591        wb.save(&path).unwrap();
592
593        let wb2 = Workbook::open(&path).unwrap();
594        assert_eq!(
595            wb2.get_cell_value("Sheet1", "A1").unwrap(),
596            CellValue::String("Hello".to_string())
597        );
598        assert_eq!(
599            wb2.get_cell_value("Sheet1", "B1").unwrap(),
600            CellValue::Number(42.0)
601        );
602        assert_eq!(
603            wb2.get_cell_value("Sheet1", "C1").unwrap(),
604            CellValue::Bool(true)
605        );
606    }
607
608    #[test]
609    fn test_set_empty_value_clears_cell() {
610        let mut wb = Workbook::new();
611        wb.set_cell_value("Sheet1", "A1", "test").unwrap();
612        assert_eq!(
613            wb.get_cell_value("Sheet1", "A1").unwrap(),
614            CellValue::String("test".to_string())
615        );
616
617        wb.set_cell_value("Sheet1", "A1", CellValue::Empty).unwrap();
618        assert_eq!(wb.get_cell_value("Sheet1", "A1").unwrap(), CellValue::Empty);
619    }
620
621    #[test]
622    fn test_string_too_long_returns_error() {
623        let mut wb = Workbook::new();
624        let long_string = "x".repeat(MAX_CELL_CHARS + 1);
625        let result = wb.set_cell_value("Sheet1", "A1", long_string.as_str());
626        assert!(result.is_err());
627        assert!(matches!(
628            result.unwrap_err(),
629            Error::CellValueTooLong { .. }
630        ));
631    }
632
633    #[test]
634    fn test_set_multiple_cells_same_row() {
635        let mut wb = Workbook::new();
636        wb.set_cell_value("Sheet1", "A1", "first").unwrap();
637        wb.set_cell_value("Sheet1", "B1", "second").unwrap();
638        wb.set_cell_value("Sheet1", "C1", "third").unwrap();
639
640        assert_eq!(
641            wb.get_cell_value("Sheet1", "A1").unwrap(),
642            CellValue::String("first".to_string())
643        );
644        assert_eq!(
645            wb.get_cell_value("Sheet1", "B1").unwrap(),
646            CellValue::String("second".to_string())
647        );
648        assert_eq!(
649            wb.get_cell_value("Sheet1", "C1").unwrap(),
650            CellValue::String("third".to_string())
651        );
652    }
653
654    #[test]
655    fn test_overwrite_cell_value() {
656        let mut wb = Workbook::new();
657        wb.set_cell_value("Sheet1", "A1", "original").unwrap();
658        wb.set_cell_value("Sheet1", "A1", "updated").unwrap();
659
660        assert_eq!(
661            wb.get_cell_value("Sheet1", "A1").unwrap(),
662            CellValue::String("updated".to_string())
663        );
664    }
665
666    #[test]
667    fn test_set_and_get_error_value() {
668        let mut wb = Workbook::new();
669        wb.set_cell_value("Sheet1", "A1", CellValue::Error("#DIV/0!".to_string()))
670            .unwrap();
671        let val = wb.get_cell_value("Sheet1", "A1").unwrap();
672        assert_eq!(val, CellValue::Error("#DIV/0!".to_string()));
673    }
674
675    #[test]
676    fn test_set_and_get_date_value() {
677        use crate::style::{builtin_num_fmts, NumFmtStyle, Style};
678
679        let mut wb = Workbook::new();
680        // Create a date style.
681        let style_id = wb
682            .add_style(&Style {
683                num_fmt: Some(NumFmtStyle::Builtin(builtin_num_fmts::DATE_MDY)),
684                ..Style::default()
685            })
686            .unwrap();
687
688        // Set a date value.
689        let date_serial =
690            crate::cell::date_to_serial(chrono::NaiveDate::from_ymd_opt(2024, 6, 15).unwrap());
691        wb.set_cell_value("Sheet1", "A1", CellValue::Date(date_serial))
692            .unwrap();
693        wb.set_cell_style("Sheet1", "A1", style_id).unwrap();
694
695        // Get the value back -- it should be Date because the cell has a date style.
696        let val = wb.get_cell_value("Sheet1", "A1").unwrap();
697        assert_eq!(val, CellValue::Date(date_serial));
698    }
699
700    #[test]
701    fn test_date_value_without_style_returns_number() {
702        let mut wb = Workbook::new();
703        // Set a date value without a date style.
704        let date_serial =
705            crate::cell::date_to_serial(chrono::NaiveDate::from_ymd_opt(2024, 6, 15).unwrap());
706        wb.set_cell_value("Sheet1", "A1", CellValue::Date(date_serial))
707            .unwrap();
708
709        // Without a date style, the value is read back as Number.
710        let val = wb.get_cell_value("Sheet1", "A1").unwrap();
711        assert_eq!(val, CellValue::Number(date_serial));
712    }
713
714    #[test]
715    fn test_date_value_roundtrip_through_save() {
716        use crate::style::{builtin_num_fmts, NumFmtStyle, Style};
717
718        let mut wb = Workbook::new();
719        let style_id = wb
720            .add_style(&Style {
721                num_fmt: Some(NumFmtStyle::Builtin(builtin_num_fmts::DATETIME)),
722                ..Style::default()
723            })
724            .unwrap();
725
726        let dt = chrono::NaiveDate::from_ymd_opt(2024, 3, 15)
727            .unwrap()
728            .and_hms_opt(14, 30, 0)
729            .unwrap();
730        let serial = crate::cell::datetime_to_serial(dt);
731        wb.set_cell_value("Sheet1", "A1", CellValue::Date(serial))
732            .unwrap();
733        wb.set_cell_style("Sheet1", "A1", style_id).unwrap();
734
735        let tmp = tempfile::NamedTempFile::new().unwrap();
736        let path = tmp.path().to_str().unwrap();
737        wb.save(path).unwrap();
738
739        let wb2 = Workbook::open(path).unwrap();
740        let val = wb2.get_cell_value("Sheet1", "A1").unwrap();
741        assert_eq!(val, CellValue::Date(serial));
742    }
743
744    #[test]
745    fn test_date_from_naive_date_conversion() {
746        let date = chrono::NaiveDate::from_ymd_opt(2024, 1, 1).unwrap();
747        let cv: CellValue = date.into();
748        match cv {
749            CellValue::Date(s) => {
750                let roundtripped = crate::cell::serial_to_date(s).unwrap();
751                assert_eq!(roundtripped, date);
752            }
753            _ => panic!("expected Date variant"),
754        }
755    }
756
757    #[test]
758    fn test_set_and_get_formula_value() {
759        let mut wb = Workbook::new();
760        wb.set_cell_value(
761            "Sheet1",
762            "A1",
763            CellValue::Formula {
764                expr: "SUM(B1:B10)".to_string(),
765                result: None,
766            },
767        )
768        .unwrap();
769        let val = wb.get_cell_value("Sheet1", "A1").unwrap();
770        match val {
771            CellValue::Formula { expr, .. } => {
772                assert_eq!(expr, "SUM(B1:B10)");
773            }
774            other => panic!("expected Formula, got {:?}", other),
775        }
776    }
777
778    #[test]
779    fn test_set_i32_value() {
780        let mut wb = Workbook::new();
781        wb.set_cell_value("Sheet1", "A1", 100i32).unwrap();
782        let val = wb.get_cell_value("Sheet1", "A1").unwrap();
783        assert_eq!(val, CellValue::Number(100.0));
784    }
785
786    #[test]
787    fn test_set_string_at_max_length() {
788        let mut wb = Workbook::new();
789        let max_string = "x".repeat(MAX_CELL_CHARS);
790        wb.set_cell_value("Sheet1", "A1", max_string.as_str())
791            .unwrap();
792        let val = wb.get_cell_value("Sheet1", "A1").unwrap();
793        assert_eq!(val, CellValue::String(max_string));
794    }
795
796    #[test]
797    fn test_set_cells_different_rows() {
798        let mut wb = Workbook::new();
799        wb.set_cell_value("Sheet1", "A1", "row1").unwrap();
800        wb.set_cell_value("Sheet1", "A3", "row3").unwrap();
801        wb.set_cell_value("Sheet1", "A2", "row2").unwrap(); // inserted between
802
803        assert_eq!(
804            wb.get_cell_value("Sheet1", "A1").unwrap(),
805            CellValue::String("row1".to_string())
806        );
807        assert_eq!(
808            wb.get_cell_value("Sheet1", "A2").unwrap(),
809            CellValue::String("row2".to_string())
810        );
811        assert_eq!(
812            wb.get_cell_value("Sheet1", "A3").unwrap(),
813            CellValue::String("row3".to_string())
814        );
815    }
816
817    #[test]
818    fn test_string_deduplication_in_sst() {
819        let mut wb = Workbook::new();
820        wb.set_cell_value("Sheet1", "A1", "same").unwrap();
821        wb.set_cell_value("Sheet1", "A2", "same").unwrap();
822        wb.set_cell_value("Sheet1", "A3", "different").unwrap();
823
824        // Both A1 and A2 should point to the same SST index
825        assert_eq!(wb.sst_runtime.len(), 2);
826        assert_eq!(
827            wb.get_cell_value("Sheet1", "A1").unwrap(),
828            CellValue::String("same".to_string())
829        );
830        assert_eq!(
831            wb.get_cell_value("Sheet1", "A2").unwrap(),
832            CellValue::String("same".to_string())
833        );
834    }
835
836    #[test]
837    fn test_add_style_returns_id() {
838        use crate::style::{FontStyle, Style};
839
840        let mut wb = Workbook::new();
841        let style = Style {
842            font: Some(FontStyle {
843                bold: true,
844                ..FontStyle::default()
845            }),
846            ..Style::default()
847        };
848        let id = wb.add_style(&style).unwrap();
849        assert!(id > 0);
850    }
851
852    #[test]
853    fn test_get_cell_style_unstyled_cell_returns_none() {
854        let wb = Workbook::new();
855        let result = wb.get_cell_style("Sheet1", "A1").unwrap();
856        assert!(result.is_none());
857    }
858
859    #[test]
860    fn test_set_cell_style_on_existing_value() {
861        use crate::style::{FontStyle, Style};
862
863        let mut wb = Workbook::new();
864        wb.set_cell_value("Sheet1", "A1", "Hello").unwrap();
865
866        let style = Style {
867            font: Some(FontStyle {
868                bold: true,
869                ..FontStyle::default()
870            }),
871            ..Style::default()
872        };
873        let style_id = wb.add_style(&style).unwrap();
874        wb.set_cell_style("Sheet1", "A1", style_id).unwrap();
875
876        let retrieved_id = wb.get_cell_style("Sheet1", "A1").unwrap();
877        assert_eq!(retrieved_id, Some(style_id));
878
879        // The value should still be there.
880        let val = wb.get_cell_value("Sheet1", "A1").unwrap();
881        assert_eq!(val, CellValue::String("Hello".to_string()));
882    }
883
884    #[test]
885    fn test_set_cell_style_on_empty_cell_creates_cell() {
886        use crate::style::{FontStyle, Style};
887
888        let mut wb = Workbook::new();
889        let style = Style {
890            font: Some(FontStyle {
891                bold: true,
892                ..FontStyle::default()
893            }),
894            ..Style::default()
895        };
896        let style_id = wb.add_style(&style).unwrap();
897
898        // Set style on a cell that doesn't exist yet.
899        wb.set_cell_style("Sheet1", "B5", style_id).unwrap();
900
901        let retrieved_id = wb.get_cell_style("Sheet1", "B5").unwrap();
902        assert_eq!(retrieved_id, Some(style_id));
903
904        // The cell value should be empty.
905        let val = wb.get_cell_value("Sheet1", "B5").unwrap();
906        assert_eq!(val, CellValue::Empty);
907    }
908
909    #[test]
910    fn test_set_cell_style_invalid_id() {
911        let mut wb = Workbook::new();
912        let result = wb.set_cell_style("Sheet1", "A1", 999);
913        assert!(result.is_err());
914        assert!(matches!(result.unwrap_err(), Error::StyleNotFound { .. }));
915    }
916
917    #[test]
918    fn test_set_cell_style_sheet_not_found() {
919        let mut wb = Workbook::new();
920        let style = crate::style::Style::default();
921        let style_id = wb.add_style(&style).unwrap();
922        let result = wb.set_cell_style("NoSuchSheet", "A1", style_id);
923        assert!(result.is_err());
924        assert!(matches!(result.unwrap_err(), Error::SheetNotFound { .. }));
925    }
926
927    #[test]
928    fn test_get_cell_style_sheet_not_found() {
929        let wb = Workbook::new();
930        let result = wb.get_cell_style("NoSuchSheet", "A1");
931        assert!(result.is_err());
932        assert!(matches!(result.unwrap_err(), Error::SheetNotFound { .. }));
933    }
934
935    #[test]
936    fn test_style_roundtrip_save_open() {
937        use crate::style::{
938            AlignmentStyle, BorderLineStyle, BorderSideStyle, BorderStyle, FillStyle, FontStyle,
939            HorizontalAlign, NumFmtStyle, PatternType, Style, StyleColor, VerticalAlign,
940        };
941
942        let dir = TempDir::new().unwrap();
943        let path = dir.path().join("style_roundtrip.xlsx");
944
945        let mut wb = Workbook::new();
946        wb.set_cell_value("Sheet1", "A1", "Styled").unwrap();
947
948        let style = Style {
949            font: Some(FontStyle {
950                name: Some("Arial".to_string()),
951                size: Some(14.0),
952                bold: true,
953                italic: true,
954                color: Some(StyleColor::Rgb("FFFF0000".to_string())),
955                ..FontStyle::default()
956            }),
957            fill: Some(FillStyle {
958                pattern: PatternType::Solid,
959                fg_color: Some(StyleColor::Rgb("FFFFFF00".to_string())),
960                bg_color: None,
961                gradient: None,
962            }),
963            border: Some(BorderStyle {
964                left: Some(BorderSideStyle {
965                    style: BorderLineStyle::Thin,
966                    color: None,
967                }),
968                right: Some(BorderSideStyle {
969                    style: BorderLineStyle::Thin,
970                    color: None,
971                }),
972                top: Some(BorderSideStyle {
973                    style: BorderLineStyle::Thin,
974                    color: None,
975                }),
976                bottom: Some(BorderSideStyle {
977                    style: BorderLineStyle::Thin,
978                    color: None,
979                }),
980                diagonal: None,
981            }),
982            alignment: Some(AlignmentStyle {
983                horizontal: Some(HorizontalAlign::Center),
984                vertical: Some(VerticalAlign::Center),
985                wrap_text: true,
986                ..AlignmentStyle::default()
987            }),
988            num_fmt: Some(NumFmtStyle::Custom("#,##0.00".to_string())),
989            protection: None,
990        };
991        let style_id = wb.add_style(&style).unwrap();
992        wb.set_cell_style("Sheet1", "A1", style_id).unwrap();
993        wb.save(&path).unwrap();
994
995        // Re-open and verify.
996        let wb2 = Workbook::open(&path).unwrap();
997        let retrieved_id = wb2.get_cell_style("Sheet1", "A1").unwrap();
998        assert_eq!(retrieved_id, Some(style_id));
999
1000        // Verify the value is still there.
1001        let val = wb2.get_cell_value("Sheet1", "A1").unwrap();
1002        assert_eq!(val, CellValue::String("Styled".to_string()));
1003
1004        // Reverse-lookup the style to verify components survived the roundtrip.
1005        let retrieved_style = crate::style::get_style(&wb2.stylesheet, style_id).unwrap();
1006        assert!(retrieved_style.font.is_some());
1007        let font = retrieved_style.font.unwrap();
1008        assert!(font.bold);
1009        assert!(font.italic);
1010        assert_eq!(font.name, Some("Arial".to_string()));
1011
1012        assert!(retrieved_style.fill.is_some());
1013        let fill = retrieved_style.fill.unwrap();
1014        assert_eq!(fill.pattern, PatternType::Solid);
1015
1016        assert!(retrieved_style.alignment.is_some());
1017        let align = retrieved_style.alignment.unwrap();
1018        assert_eq!(align.horizontal, Some(HorizontalAlign::Center));
1019        assert_eq!(align.vertical, Some(VerticalAlign::Center));
1020        assert!(align.wrap_text);
1021    }
1022
1023    #[test]
1024    fn test_set_and_get_cell_rich_text() {
1025        use crate::rich_text::RichTextRun;
1026
1027        let mut wb = Workbook::new();
1028        let runs = vec![
1029            RichTextRun {
1030                text: "Bold".to_string(),
1031                font: None,
1032                size: None,
1033                bold: true,
1034                italic: false,
1035                color: None,
1036            },
1037            RichTextRun {
1038                text: " Normal".to_string(),
1039                font: None,
1040                size: None,
1041                bold: false,
1042                italic: false,
1043                color: None,
1044            },
1045        ];
1046        wb.set_cell_rich_text("Sheet1", "A1", runs.clone()).unwrap();
1047
1048        // The cell value should be a shared string whose plain text is "Bold Normal".
1049        let val = wb.get_cell_value("Sheet1", "A1").unwrap();
1050        assert_eq!(val.to_string(), "Bold Normal");
1051
1052        // get_cell_rich_text should return the runs.
1053        let got = wb.get_cell_rich_text("Sheet1", "A1").unwrap();
1054        assert!(got.is_some());
1055        let got_runs = got.unwrap();
1056        assert_eq!(got_runs.len(), 2);
1057        assert_eq!(got_runs[0].text, "Bold");
1058        assert!(got_runs[0].bold);
1059        assert_eq!(got_runs[1].text, " Normal");
1060        assert!(!got_runs[1].bold);
1061    }
1062
1063    #[test]
1064    fn test_get_cell_rich_text_returns_none_for_plain() {
1065        let mut wb = Workbook::new();
1066        wb.set_cell_value("Sheet1", "A1", CellValue::String("plain".to_string()))
1067            .unwrap();
1068        let got = wb.get_cell_rich_text("Sheet1", "A1").unwrap();
1069        assert!(got.is_none());
1070    }
1071
1072    #[test]
1073    fn test_rich_text_roundtrip_save_open() {
1074        use crate::rich_text::RichTextRun;
1075
1076        let dir = TempDir::new().unwrap();
1077        let path = dir.path().join("rich_text.xlsx");
1078
1079        // Note: quick-xml's serde deserializer trims leading and trailing
1080        // whitespace from text content. To avoid false failures, test text
1081        // values must not rely on boundary whitespace being preserved.
1082        let mut wb = Workbook::new();
1083        let runs = vec![
1084            RichTextRun {
1085                text: "Hello".to_string(),
1086                font: Some("Arial".to_string()),
1087                size: Some(14.0),
1088                bold: true,
1089                italic: false,
1090                color: Some("#FF0000".to_string()),
1091            },
1092            RichTextRun {
1093                text: "World".to_string(),
1094                font: None,
1095                size: None,
1096                bold: false,
1097                italic: true,
1098                color: None,
1099            },
1100        ];
1101        wb.set_cell_rich_text("Sheet1", "B2", runs).unwrap();
1102        wb.save(&path).unwrap();
1103
1104        let wb2 = Workbook::open(&path).unwrap();
1105        let val = wb2.get_cell_value("Sheet1", "B2").unwrap();
1106        assert_eq!(val.to_string(), "HelloWorld");
1107
1108        let got = wb2.get_cell_rich_text("Sheet1", "B2").unwrap();
1109        assert!(got.is_some());
1110        let got_runs = got.unwrap();
1111        assert_eq!(got_runs.len(), 2);
1112        assert_eq!(got_runs[0].text, "Hello");
1113        assert!(got_runs[0].bold);
1114        assert_eq!(got_runs[0].font.as_deref(), Some("Arial"));
1115        assert_eq!(got_runs[0].size, Some(14.0));
1116        assert_eq!(got_runs[0].color.as_deref(), Some("#FF0000"));
1117        assert_eq!(got_runs[1].text, "World");
1118        assert!(got_runs[1].italic);
1119        assert!(!got_runs[1].bold);
1120    }
1121
1122    #[test]
1123    fn test_set_cell_formula() {
1124        let mut wb = Workbook::new();
1125        wb.set_cell_formula("Sheet1", "A1", "SUM(B1:B10)").unwrap();
1126        let val = wb.get_cell_value("Sheet1", "A1").unwrap();
1127        match val {
1128            CellValue::Formula { expr, .. } => assert_eq!(expr, "SUM(B1:B10)"),
1129            other => panic!("expected Formula, got {:?}", other),
1130        }
1131    }
1132
1133    #[test]
1134    fn test_fill_formula_basic() {
1135        let mut wb = Workbook::new();
1136        wb.fill_formula("Sheet1", "D2:D5", "SUM(A2:C2)").unwrap();
1137
1138        // D2 should have the base formula unchanged
1139        match wb.get_cell_value("Sheet1", "D2").unwrap() {
1140            CellValue::Formula { expr, .. } => assert_eq!(expr, "SUM(A2:C2)"),
1141            other => panic!("D2: expected Formula, got {:?}", other),
1142        }
1143        // D3 should have row shifted by 1
1144        match wb.get_cell_value("Sheet1", "D3").unwrap() {
1145            CellValue::Formula { expr, .. } => assert_eq!(expr, "SUM(A3:C3)"),
1146            other => panic!("D3: expected Formula, got {:?}", other),
1147        }
1148        // D4 should have row shifted by 2
1149        match wb.get_cell_value("Sheet1", "D4").unwrap() {
1150            CellValue::Formula { expr, .. } => assert_eq!(expr, "SUM(A4:C4)"),
1151            other => panic!("D4: expected Formula, got {:?}", other),
1152        }
1153        // D5 should have row shifted by 3
1154        match wb.get_cell_value("Sheet1", "D5").unwrap() {
1155            CellValue::Formula { expr, .. } => assert_eq!(expr, "SUM(A5:C5)"),
1156            other => panic!("D5: expected Formula, got {:?}", other),
1157        }
1158    }
1159
1160    #[test]
1161    fn test_fill_formula_preserves_absolute_refs() {
1162        let mut wb = Workbook::new();
1163        wb.fill_formula("Sheet1", "B1:B3", "$A$1*A1").unwrap();
1164
1165        match wb.get_cell_value("Sheet1", "B1").unwrap() {
1166            CellValue::Formula { expr, .. } => assert_eq!(expr, "$A$1*A1"),
1167            other => panic!("B1: expected Formula, got {:?}", other),
1168        }
1169        match wb.get_cell_value("Sheet1", "B2").unwrap() {
1170            CellValue::Formula { expr, .. } => assert_eq!(expr, "$A$1*A2"),
1171            other => panic!("B2: expected Formula, got {:?}", other),
1172        }
1173        match wb.get_cell_value("Sheet1", "B3").unwrap() {
1174            CellValue::Formula { expr, .. } => assert_eq!(expr, "$A$1*A3"),
1175            other => panic!("B3: expected Formula, got {:?}", other),
1176        }
1177    }
1178
1179    #[test]
1180    fn test_fill_formula_single_cell() {
1181        let mut wb = Workbook::new();
1182        wb.fill_formula("Sheet1", "A1:A1", "B1+C1").unwrap();
1183        match wb.get_cell_value("Sheet1", "A1").unwrap() {
1184            CellValue::Formula { expr, .. } => assert_eq!(expr, "B1+C1"),
1185            other => panic!("expected Formula, got {:?}", other),
1186        }
1187    }
1188
1189    #[test]
1190    fn test_fill_formula_invalid_range() {
1191        let mut wb = Workbook::new();
1192        assert!(wb.fill_formula("Sheet1", "INVALID", "A1").is_err());
1193    }
1194
1195    #[test]
1196    fn test_fill_formula_multi_column_range_rejected() {
1197        let mut wb = Workbook::new();
1198        assert!(wb.fill_formula("Sheet1", "A1:B5", "C1").is_err());
1199    }
1200}
sheetkit_core/workbook/cell_ops.rs

sheetkit_core/workbook/
cell_ops.rs
