1use sheetkit_xml::workbook::{DefinedName, DefinedNames, WorkbookXml};
8
9use crate::error::{Error, Result};
10
11const DEFINED_NAME_INVALID_CHARS: &[char] = &['/', '?', '*', '[', ']'];
13
14#[derive(Debug, Clone, PartialEq)]
16pub enum DefinedNameScope {
17 Workbook,
19 Sheet(u32),
21}
22
23#[derive(Debug, Clone)]
25pub struct DefinedNameInfo {
26 pub name: String,
27 pub value: String,
29 pub scope: DefinedNameScope,
30 pub comment: Option<String>,
31}
32
33fn 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
126fn 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
134fn 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
142pub 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 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
183pub 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
206pub 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 if defined_names.defined_names.is_empty() {
235 wb.defined_names = None;
236 }
237
238 Ok(())
239}
240
241pub 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 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 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 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_defined_name(
440 &mut wb,
441 "Total",
442 "Sheet1!$A$1",
443 DefinedNameScope::Workbook,
444 None,
445 )
446 .unwrap();
447
448 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 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}