Skip to main content

sheetkit_core/
defined_names.rs

1//! Defined names (named ranges) management.
2//!
3//! Provides functions to add, retrieve, update, delete, and list defined names
4//! in an Excel workbook. Defined names can be workbook-scoped (visible from all
5//! sheets) or sheet-scoped (visible only in the specified sheet).
6
7use sheetkit_xml::workbook::{DefinedName, DefinedNames, WorkbookXml};
8
9use crate::error::{Error, Result};
10
11/// Characters that are not allowed in defined names.
12const DEFINED_NAME_INVALID_CHARS: &[char] = &['/', '?', '*', '[', ']'];
13
14/// Scope of a defined name.
15#[derive(Debug, Clone, PartialEq)]
16pub enum DefinedNameScope {
17    /// Workbook-level scope (visible from all sheets).
18    Workbook,
19    /// Sheet-level scope (only visible in the specified sheet).
20    Sheet(u32),
21}
22
23/// Information about a defined name.
24#[derive(Debug, Clone)]
25pub struct DefinedNameInfo {
26    pub name: String,
27    /// The reference or formula, e.g. "Sheet1!$A$1:$D$10".
28    pub value: String,
29    pub scope: DefinedNameScope,
30    pub comment: Option<String>,
31}
32
33/// Validate a defined name.
34///
35/// A valid defined name must:
36/// - Be non-empty
37/// - Be at most 255 Unicode scalar characters
38/// - Start with a Unicode letter, underscore, or backslash
39/// - Contain only Unicode letters or digits, underscores, periods, or backslashes
40/// - Not be an A1 or R1C1 reference, or the reserved names R and C
41fn validate_defined_name(name: &str) -> Result<()> {
42    if name.is_empty() {
43        return Err(Error::InvalidDefinedName(
44            "defined name cannot be empty".into(),
45        ));
46    }
47    if name.chars().count() > 255 {
48        return Err(Error::InvalidDefinedName(
49            "defined name cannot exceed 255 characters".into(),
50        ));
51    }
52    if name != name.trim() {
53        return Err(Error::InvalidDefinedName(
54            "defined name cannot start or end with whitespace".into(),
55        ));
56    }
57    for ch in DEFINED_NAME_INVALID_CHARS {
58        if name.contains(*ch) {
59            return Err(Error::InvalidDefinedName(format!(
60                "defined name '{}' contains invalid character '{}'",
61                name, ch
62            )));
63        }
64    }
65    let mut chars = name.chars();
66    let first = chars.next().expect("name was checked as non-empty");
67    if !(first.is_alphabetic() || matches!(first, '_' | '\\')) {
68        return Err(Error::InvalidDefinedName(
69            "defined name must start with a letter, underscore, or backslash".into(),
70        ));
71    }
72    if !chars.all(|ch| ch.is_alphanumeric() || matches!(ch, '_' | '.' | '\\')) {
73        return Err(Error::InvalidDefinedName(
74            "defined name contains characters outside Excel's name grammar".into(),
75        ));
76    }
77    if name.eq_ignore_ascii_case("R") || name.eq_ignore_ascii_case("C") {
78        return Err(Error::InvalidDefinedName(
79            "defined name cannot be the reserved name R or C".into(),
80        ));
81    }
82    if is_a1_reference(name) || is_r1c1_reference(name) {
83        return Err(Error::InvalidDefinedName(
84            "defined name cannot be an Excel cell reference".into(),
85        ));
86    }
87    Ok(())
88}
89
90fn is_a1_reference(name: &str) -> bool {
91    name.is_ascii() && crate::utils::cell_ref::cell_name_to_coordinates(name).is_ok()
92}
93
94fn is_r1c1_reference(name: &str) -> bool {
95    if !name.is_ascii() {
96        return false;
97    }
98    let upper = name.to_ascii_uppercase();
99    let Some(rest) = upper.strip_prefix('R') else {
100        return false;
101    };
102    let Some((row, col)) = rest.split_once('C') else {
103        return false;
104    };
105    !row.is_empty()
106        && !col.is_empty()
107        && r1c1_component_is_valid(row)
108        && r1c1_component_is_valid(col)
109}
110
111fn defined_names_equal(left: &str, right: &str) -> bool {
112    left.to_lowercase() == right.to_lowercase()
113}
114
115fn r1c1_component_is_valid(component: &str) -> bool {
116    component.chars().all(|ch| ch.is_ascii_digit())
117        || (component.starts_with('[')
118            && component.ends_with(']')
119            && component[1..component.len() - 1]
120                .strip_prefix('-')
121                .unwrap_or(&component[1..component.len() - 1])
122                .chars()
123                .all(|ch| ch.is_ascii_digit()))
124}
125
126/// Convert a `DefinedNameScope` to the corresponding `local_sheet_id` value.
127fn scope_to_local_sheet_id(scope: &DefinedNameScope) -> Option<u32> {
128    match scope {
129        DefinedNameScope::Workbook => None,
130        DefinedNameScope::Sheet(id) => Some(*id),
131    }
132}
133
134/// Convert an optional `local_sheet_id` to a `DefinedNameScope`.
135fn local_sheet_id_to_scope(local_sheet_id: Option<u32>) -> DefinedNameScope {
136    match local_sheet_id {
137        None => DefinedNameScope::Workbook,
138        Some(id) => DefinedNameScope::Sheet(id),
139    }
140}
141
142/// Add or update a defined name in the workbook.
143///
144/// If a defined name with the same name and scope already exists, its value
145/// and comment are updated. Otherwise, a new entry is created.
146pub fn set_defined_name(
147    wb: &mut WorkbookXml,
148    name: &str,
149    value: &str,
150    scope: DefinedNameScope,
151    comment: Option<&str>,
152) -> Result<()> {
153    validate_defined_name(name)?;
154
155    let local_sheet_id = scope_to_local_sheet_id(&scope);
156
157    let defined_names = wb.defined_names.get_or_insert_with(|| DefinedNames {
158        defined_names: Vec::new(),
159    });
160
161    // Check if a name with the same name+scope already exists.
162    if let Some(existing) = defined_names
163        .defined_names
164        .iter_mut()
165        .find(|dn| defined_names_equal(&dn.name, name) && dn.local_sheet_id == local_sheet_id)
166    {
167        existing.value = value.to_string();
168        existing.comment = comment.map(|c| c.to_string());
169        return Ok(());
170    }
171
172    defined_names.defined_names.push(DefinedName {
173        name: name.to_string(),
174        local_sheet_id,
175        comment: comment.map(|c| c.to_string()),
176        hidden: None,
177        value: value.to_string(),
178    });
179
180    Ok(())
181}
182
183/// Get a defined name by name and scope.
184///
185/// Returns `None` if no matching defined name is found.
186pub fn get_defined_name(
187    wb: &WorkbookXml,
188    name: &str,
189    scope: DefinedNameScope,
190) -> Option<DefinedNameInfo> {
191    let defined_names = wb.defined_names.as_ref()?;
192    let local_sheet_id = scope_to_local_sheet_id(&scope);
193
194    defined_names
195        .defined_names
196        .iter()
197        .find(|dn| defined_names_equal(&dn.name, name) && dn.local_sheet_id == local_sheet_id)
198        .map(|dn| DefinedNameInfo {
199            name: dn.name.clone(),
200            value: dn.value.clone(),
201            scope: local_sheet_id_to_scope(dn.local_sheet_id),
202            comment: dn.comment.clone(),
203        })
204}
205
206/// Delete a defined name by name and scope.
207///
208/// Returns an error if the defined name does not exist.
209pub fn delete_defined_name(
210    wb: &mut WorkbookXml,
211    name: &str,
212    scope: DefinedNameScope,
213) -> Result<()> {
214    let local_sheet_id = scope_to_local_sheet_id(&scope);
215
216    let defined_names = wb
217        .defined_names
218        .as_mut()
219        .ok_or_else(|| Error::DefinedNameNotFound {
220            name: name.to_string(),
221        })?;
222
223    let idx = defined_names
224        .defined_names
225        .iter()
226        .position(|dn| defined_names_equal(&dn.name, name) && dn.local_sheet_id == local_sheet_id)
227        .ok_or_else(|| Error::DefinedNameNotFound {
228            name: name.to_string(),
229        })?;
230
231    defined_names.defined_names.remove(idx);
232
233    // Clean up: remove the container if empty.
234    if defined_names.defined_names.is_empty() {
235        wb.defined_names = None;
236    }
237
238    Ok(())
239}
240
241/// List all defined names in the workbook.
242pub fn get_all_defined_names(wb: &WorkbookXml) -> Vec<DefinedNameInfo> {
243    let Some(defined_names) = wb.defined_names.as_ref() else {
244        return Vec::new();
245    };
246
247    defined_names
248        .defined_names
249        .iter()
250        .map(|dn| DefinedNameInfo {
251            name: dn.name.clone(),
252            value: dn.value.clone(),
253            scope: local_sheet_id_to_scope(dn.local_sheet_id),
254            comment: dn.comment.clone(),
255        })
256        .collect()
257}
258
259#[cfg(test)]
260mod tests {
261    use super::*;
262
263    /// Create a minimal WorkbookXml for testing.
264    fn test_workbook() -> WorkbookXml {
265        WorkbookXml::default()
266    }
267
268    #[test]
269    fn test_set_defined_name_workbook_scope() {
270        let mut wb = test_workbook();
271        set_defined_name(
272            &mut wb,
273            "SalesData",
274            "Sheet1!$A$1:$D$10",
275            DefinedNameScope::Workbook,
276            None,
277        )
278        .unwrap();
279
280        let dn = wb.defined_names.as_ref().unwrap();
281        assert_eq!(dn.defined_names.len(), 1);
282        assert_eq!(dn.defined_names[0].name, "SalesData");
283        assert_eq!(dn.defined_names[0].value, "Sheet1!$A$1:$D$10");
284        assert!(dn.defined_names[0].local_sheet_id.is_none());
285    }
286
287    #[test]
288    fn test_set_defined_name_sheet_scope() {
289        let mut wb = test_workbook();
290        set_defined_name(
291            &mut wb,
292            "LocalRange",
293            "Sheet1!$B$2:$C$5",
294            DefinedNameScope::Sheet(0),
295            None,
296        )
297        .unwrap();
298
299        let dn = wb.defined_names.as_ref().unwrap();
300        assert_eq!(dn.defined_names.len(), 1);
301        assert_eq!(dn.defined_names[0].name, "LocalRange");
302        assert_eq!(dn.defined_names[0].local_sheet_id, Some(0));
303    }
304
305    #[test]
306    fn test_get_defined_name() {
307        let mut wb = test_workbook();
308        set_defined_name(
309            &mut wb,
310            "Revenue",
311            "Sheet1!$E$1:$E$100",
312            DefinedNameScope::Workbook,
313            None,
314        )
315        .unwrap();
316
317        let info = get_defined_name(&wb, "Revenue", DefinedNameScope::Workbook).unwrap();
318        assert_eq!(info.name, "Revenue");
319        assert_eq!(info.value, "Sheet1!$E$1:$E$100");
320        assert_eq!(info.scope, DefinedNameScope::Workbook);
321        assert!(info.comment.is_none());
322    }
323
324    #[test]
325    fn test_get_defined_name_not_found() {
326        let wb = test_workbook();
327        let result = get_defined_name(&wb, "NonExistent", DefinedNameScope::Workbook);
328        assert!(result.is_none());
329    }
330
331    #[test]
332    fn test_update_defined_name() {
333        let mut wb = test_workbook();
334        set_defined_name(
335            &mut wb,
336            "DataRange",
337            "Sheet1!$A$1:$A$10",
338            DefinedNameScope::Workbook,
339            None,
340        )
341        .unwrap();
342
343        // Update the same name with a new value.
344        set_defined_name(
345            &mut wb,
346            "DataRange",
347            "Sheet1!$A$1:$A$50",
348            DefinedNameScope::Workbook,
349            Some("Updated range"),
350        )
351        .unwrap();
352
353        let dn = wb.defined_names.as_ref().unwrap();
354        assert_eq!(dn.defined_names.len(), 1, "should not duplicate the entry");
355        assert_eq!(dn.defined_names[0].value, "Sheet1!$A$1:$A$50");
356        assert_eq!(
357            dn.defined_names[0].comment,
358            Some("Updated range".to_string())
359        );
360    }
361
362    #[test]
363    fn test_delete_defined_name() {
364        let mut wb = test_workbook();
365        set_defined_name(
366            &mut wb,
367            "ToDelete",
368            "Sheet1!$A$1",
369            DefinedNameScope::Workbook,
370            None,
371        )
372        .unwrap();
373        assert!(wb.defined_names.is_some());
374
375        delete_defined_name(&mut wb, "ToDelete", DefinedNameScope::Workbook).unwrap();
376        // Container should be cleaned up since it is now empty.
377        assert!(wb.defined_names.is_none());
378    }
379
380    #[test]
381    fn test_delete_defined_name_not_found() {
382        let mut wb = test_workbook();
383        let result = delete_defined_name(&mut wb, "Ghost", DefinedNameScope::Workbook);
384        assert!(result.is_err());
385        let err = result.unwrap_err();
386        assert!(
387            err.to_string().contains("Ghost"),
388            "error message should contain the name"
389        );
390    }
391
392    #[test]
393    fn test_get_all_defined_names() {
394        let mut wb = test_workbook();
395        set_defined_name(
396            &mut wb,
397            "Alpha",
398            "Sheet1!$A$1",
399            DefinedNameScope::Workbook,
400            None,
401        )
402        .unwrap();
403        set_defined_name(
404            &mut wb,
405            "Beta",
406            "Sheet1!$B$1",
407            DefinedNameScope::Sheet(0),
408            None,
409        )
410        .unwrap();
411        set_defined_name(
412            &mut wb,
413            "Gamma",
414            "Sheet1!$C$1",
415            DefinedNameScope::Sheet(1),
416            None,
417        )
418        .unwrap();
419
420        let all = get_all_defined_names(&wb);
421        assert_eq!(all.len(), 3);
422        assert_eq!(all[0].name, "Alpha");
423        assert_eq!(all[1].name, "Beta");
424        assert_eq!(all[2].name, "Gamma");
425    }
426
427    #[test]
428    fn test_get_all_defined_names_empty() {
429        let wb = test_workbook();
430        let all = get_all_defined_names(&wb);
431        assert!(all.is_empty());
432    }
433
434    #[test]
435    fn test_same_name_different_scopes() {
436        let mut wb = test_workbook();
437
438        // Set workbook-scoped name.
439        set_defined_name(
440            &mut wb,
441            "Total",
442            "Sheet1!$A$1",
443            DefinedNameScope::Workbook,
444            None,
445        )
446        .unwrap();
447
448        // Set sheet-scoped name with the same name.
449        set_defined_name(
450            &mut wb,
451            "Total",
452            "Sheet1!$B$1",
453            DefinedNameScope::Sheet(0),
454            None,
455        )
456        .unwrap();
457
458        let dn = wb.defined_names.as_ref().unwrap();
459        assert_eq!(dn.defined_names.len(), 2, "both scopes should coexist");
460
461        let wb_info = get_defined_name(&wb, "Total", DefinedNameScope::Workbook).unwrap();
462        assert_eq!(wb_info.value, "Sheet1!$A$1");
463
464        let sheet_info = get_defined_name(&wb, "Total", DefinedNameScope::Sheet(0)).unwrap();
465        assert_eq!(sheet_info.value, "Sheet1!$B$1");
466    }
467
468    #[test]
469    fn test_defined_name_with_comment() {
470        let mut wb = test_workbook();
471        set_defined_name(
472            &mut wb,
473            "Annotated",
474            "Sheet1!$A$1:$Z$100",
475            DefinedNameScope::Workbook,
476            Some("Main data area"),
477        )
478        .unwrap();
479
480        let info = get_defined_name(&wb, "Annotated", DefinedNameScope::Workbook).unwrap();
481        assert_eq!(info.comment, Some("Main data area".to_string()));
482    }
483
484    #[test]
485    fn test_invalid_defined_name_empty() {
486        let mut wb = test_workbook();
487        let result = set_defined_name(&mut wb, "", "Sheet1!$A$1", DefinedNameScope::Workbook, None);
488        assert!(result.is_err());
489        assert!(result.unwrap_err().to_string().contains("empty"));
490    }
491
492    #[test]
493    fn test_invalid_defined_name_leading_whitespace() {
494        let mut wb = test_workbook();
495        let result = set_defined_name(
496            &mut wb,
497            " Leading",
498            "Sheet1!$A$1",
499            DefinedNameScope::Workbook,
500            None,
501        );
502        assert!(result.is_err());
503        assert!(result.unwrap_err().to_string().contains("whitespace"));
504    }
505
506    #[test]
507    fn test_invalid_defined_name_trailing_whitespace() {
508        let mut wb = test_workbook();
509        let result = set_defined_name(
510            &mut wb,
511            "Trailing ",
512            "Sheet1!$A$1",
513            DefinedNameScope::Workbook,
514            None,
515        );
516        assert!(result.is_err());
517        assert!(result.unwrap_err().to_string().contains("whitespace"));
518    }
519
520    #[test]
521    fn test_invalid_defined_name_special_chars() {
522        let mut wb = test_workbook();
523        for ch in DEFINED_NAME_INVALID_CHARS {
524            let name = format!("Bad{}Name", ch);
525            let result = set_defined_name(
526                &mut wb,
527                &name,
528                "Sheet1!$A$1",
529                DefinedNameScope::Workbook,
530                None,
531            );
532            assert!(result.is_err(), "should reject '{}' in name", ch);
533            assert!(
534                result
535                    .unwrap_err()
536                    .to_string()
537                    .contains("invalid character"),
538                "error for '{}' should mention invalid character",
539                ch
540            );
541        }
542    }
543
544    #[test]
545    fn test_defined_name_grammar_boundaries_and_reference_forms() {
546        let mut wb = test_workbook();
547        for name in [
548            "1Name",
549            "Name With Space",
550            "A1",
551            "XFD1048576",
552            "R1C1",
553            "R[-1]C[2]",
554            "R",
555            "C",
556        ] {
557            assert!(
558                set_defined_name(
559                    &mut wb,
560                    name,
561                    "Sheet1!$A$1",
562                    DefinedNameScope::Workbook,
563                    None
564                )
565                .is_err(),
566                "{name} should be rejected"
567            );
568        }
569        assert!(set_defined_name(
570            &mut wb,
571            &"N".repeat(255),
572            "Sheet1!$A$1",
573            DefinedNameScope::Workbook,
574            None,
575        )
576        .is_ok());
577        assert!(set_defined_name(
578            &mut wb,
579            &"N".repeat(256),
580            "Sheet1!$A$1",
581            DefinedNameScope::Workbook,
582            None,
583        )
584        .is_err());
585        assert!(set_defined_name(
586            &mut wb,
587            &"가".repeat(255),
588            "Sheet1!$A$1",
589            DefinedNameScope::Workbook,
590            None,
591        )
592        .is_ok());
593        assert!(set_defined_name(
594            &mut wb,
595            &"가".repeat(256),
596            "Sheet1!$A$1",
597            DefinedNameScope::Workbook,
598            None,
599        )
600        .is_err());
601
602        let korean_name = "매출합계";
603        assert!(set_defined_name(
604            &mut wb,
605            korean_name,
606            "Sheet1!$C$1",
607            DefinedNameScope::Sheet(0),
608            None,
609        )
610        .is_ok());
611        assert_eq!(
612            get_defined_name(&wb, korean_name, DefinedNameScope::Sheet(0))
613                .unwrap()
614                .value,
615            "Sheet1!$C$1"
616        );
617    }
618
619    #[test]
620    fn test_defined_names_are_case_insensitive_within_scope() {
621        let mut wb = test_workbook();
622        set_defined_name(
623            &mut wb,
624            "Revenue",
625            "Sheet1!$A$1",
626            DefinedNameScope::Workbook,
627            None,
628        )
629        .unwrap();
630        set_defined_name(
631            &mut wb,
632            "revenue",
633            "Sheet1!$B$1",
634            DefinedNameScope::Workbook,
635            None,
636        )
637        .unwrap();
638
639        assert_eq!(wb.defined_names.as_ref().unwrap().defined_names.len(), 1);
640        assert_eq!(
641            get_defined_name(&wb, "REVENUE", DefinedNameScope::Workbook)
642                .unwrap()
643                .value,
644            "Sheet1!$B$1"
645        );
646        delete_defined_name(&mut wb, "ReVeNuE", DefinedNameScope::Workbook).unwrap();
647        assert!(wb.defined_names.is_none());
648    }
649
650    #[test]
651    fn test_defined_name_unicode_case_identity() {
652        let mut wb = test_workbook();
653        set_defined_name(
654            &mut wb,
655            "Résumé",
656            "Sheet1!$A$1",
657            DefinedNameScope::Workbook,
658            None,
659        )
660        .unwrap();
661        set_defined_name(
662            &mut wb,
663            "RÉSUMÉ",
664            "Sheet1!$B$1",
665            DefinedNameScope::Workbook,
666            None,
667        )
668        .unwrap();
669
670        assert_eq!(wb.defined_names.as_ref().unwrap().defined_names.len(), 1);
671        assert_eq!(
672            get_defined_name(&wb, "résumé", DefinedNameScope::Workbook)
673                .unwrap()
674                .value,
675            "Sheet1!$B$1"
676        );
677    }
678
679    #[test]
680    fn test_delete_one_keeps_others() {
681        let mut wb = test_workbook();
682        set_defined_name(
683            &mut wb,
684            "Keep",
685            "Sheet1!$A$1",
686            DefinedNameScope::Workbook,
687            None,
688        )
689        .unwrap();
690        set_defined_name(
691            &mut wb,
692            "Remove",
693            "Sheet1!$B$1",
694            DefinedNameScope::Workbook,
695            None,
696        )
697        .unwrap();
698
699        delete_defined_name(&mut wb, "Remove", DefinedNameScope::Workbook).unwrap();
700
701        let dn = wb.defined_names.as_ref().unwrap();
702        assert_eq!(dn.defined_names.len(), 1);
703        assert_eq!(dn.defined_names[0].name, "Keep");
704    }
705
706    #[test]
707    fn test_delete_wrong_scope_not_found() {
708        let mut wb = test_workbook();
709        set_defined_name(
710            &mut wb,
711            "ScopedName",
712            "Sheet1!$A$1",
713            DefinedNameScope::Sheet(0),
714            None,
715        )
716        .unwrap();
717
718        // Deleting with workbook scope should fail because it only exists at sheet scope.
719        let result = delete_defined_name(&mut wb, "ScopedName", DefinedNameScope::Workbook);
720        assert!(result.is_err());
721    }
722
723    #[test]
724    fn test_xml_roundtrip_with_defined_names() {
725        let mut wb = test_workbook();
726        set_defined_name(
727            &mut wb,
728            "RangeA",
729            "Sheet1!$A$1:$A$10",
730            DefinedNameScope::Workbook,
731            Some("First range"),
732        )
733        .unwrap();
734        set_defined_name(
735            &mut wb,
736            "RangeB",
737            "Sheet1!$B$1:$B$5",
738            DefinedNameScope::Sheet(0),
739            None,
740        )
741        .unwrap();
742        set_defined_name(
743            &mut wb,
744            "매출합계",
745            "Sheet1!$C$1:$C$5",
746            DefinedNameScope::Workbook,
747            None,
748        )
749        .unwrap();
750
751        let xml = quick_xml::se::to_string(&wb).unwrap();
752        let parsed: WorkbookXml = quick_xml::de::from_str(&xml).unwrap();
753
754        let all = get_all_defined_names(&parsed);
755        assert_eq!(all.len(), 3);
756        assert_eq!(all[0].name, "RangeA");
757        assert_eq!(all[0].value, "Sheet1!$A$1:$A$10");
758        assert_eq!(all[0].scope, DefinedNameScope::Workbook);
759        assert_eq!(all[0].comment, Some("First range".to_string()));
760        assert_eq!(all[1].name, "RangeB");
761        assert_eq!(all[1].value, "Sheet1!$B$1:$B$5");
762        assert_eq!(all[1].scope, DefinedNameScope::Sheet(0));
763        assert!(all[1].comment.is_none());
764        assert_eq!(all[2].name, "매출합계");
765        assert_eq!(all[2].value, "Sheet1!$C$1:$C$5");
766    }
767}