Skip to main content

sheetkit_core/
validation.rs

1//! Data validation builder and utilities.
2//!
3//! Provides a high-level API for adding, querying, and removing data validation
4//! rules on worksheet cells.
5
6use crate::error::{Error, Result};
7use sheetkit_xml::worksheet::{DataValidation, DataValidations, WorksheetXml};
8
9/// The type of data validation to apply.
10#[derive(Debug, Clone, PartialEq)]
11pub enum ValidationType {
12    /// No value restriction (prompt/message only).
13    None,
14    Whole,
15    Decimal,
16    List,
17    Date,
18    Time,
19    TextLength,
20    Custom,
21}
22
23impl ValidationType {
24    /// Convert to the XML attribute string.
25    pub fn as_str(&self) -> &str {
26        match self {
27            ValidationType::None => "none",
28            ValidationType::Whole => "whole",
29            ValidationType::Decimal => "decimal",
30            ValidationType::List => "list",
31            ValidationType::Date => "date",
32            ValidationType::Time => "time",
33            ValidationType::TextLength => "textLength",
34            ValidationType::Custom => "custom",
35        }
36    }
37
38    /// Parse from the XML attribute string.
39    pub fn parse(s: &str) -> Option<Self> {
40        match s {
41            "none" => Some(ValidationType::None),
42            "whole" => Some(ValidationType::Whole),
43            "decimal" => Some(ValidationType::Decimal),
44            "list" => Some(ValidationType::List),
45            "date" => Some(ValidationType::Date),
46            "time" => Some(ValidationType::Time),
47            "textLength" => Some(ValidationType::TextLength),
48            "custom" => Some(ValidationType::Custom),
49            _ => None,
50        }
51    }
52
53    /// Whether this type requires an operator.
54    pub fn uses_operator(&self) -> bool {
55        matches!(
56            self,
57            ValidationType::Whole
58                | ValidationType::Decimal
59                | ValidationType::Date
60                | ValidationType::Time
61                | ValidationType::TextLength
62        )
63    }
64}
65
66/// The comparison operator for data validation.
67#[derive(Debug, Clone, PartialEq)]
68pub enum ValidationOperator {
69    Between,
70    NotBetween,
71    Equal,
72    NotEqual,
73    LessThan,
74    LessThanOrEqual,
75    GreaterThan,
76    GreaterThanOrEqual,
77}
78
79impl ValidationOperator {
80    /// Convert to the XML attribute string.
81    pub fn as_str(&self) -> &str {
82        match self {
83            ValidationOperator::Between => "between",
84            ValidationOperator::NotBetween => "notBetween",
85            ValidationOperator::Equal => "equal",
86            ValidationOperator::NotEqual => "notEqual",
87            ValidationOperator::LessThan => "lessThan",
88            ValidationOperator::LessThanOrEqual => "lessThanOrEqual",
89            ValidationOperator::GreaterThan => "greaterThan",
90            ValidationOperator::GreaterThanOrEqual => "greaterThanOrEqual",
91        }
92    }
93
94    /// Parse from the XML attribute string.
95    pub fn parse(s: &str) -> Option<Self> {
96        match s {
97            "between" => Some(ValidationOperator::Between),
98            "notBetween" => Some(ValidationOperator::NotBetween),
99            "equal" => Some(ValidationOperator::Equal),
100            "notEqual" => Some(ValidationOperator::NotEqual),
101            "lessThan" => Some(ValidationOperator::LessThan),
102            "lessThanOrEqual" => Some(ValidationOperator::LessThanOrEqual),
103            "greaterThan" => Some(ValidationOperator::GreaterThan),
104            "greaterThanOrEqual" => Some(ValidationOperator::GreaterThanOrEqual),
105            _ => None,
106        }
107    }
108
109    /// Whether this operator requires two formulas.
110    pub fn needs_formula2(&self) -> bool {
111        matches!(
112            self,
113            ValidationOperator::Between | ValidationOperator::NotBetween
114        )
115    }
116}
117
118/// The error display style for validation failures.
119#[derive(Debug, Clone, PartialEq)]
120pub enum ErrorStyle {
121    Stop,
122    Warning,
123    Information,
124}
125
126impl ErrorStyle {
127    /// Convert to the XML attribute string.
128    pub fn as_str(&self) -> &str {
129        match self {
130            ErrorStyle::Stop => "stop",
131            ErrorStyle::Warning => "warning",
132            ErrorStyle::Information => "information",
133        }
134    }
135
136    /// Parse from the XML attribute string.
137    pub fn parse(s: &str) -> Option<Self> {
138        match s {
139            "stop" => Some(ErrorStyle::Stop),
140            "warning" => Some(ErrorStyle::Warning),
141            "information" => Some(ErrorStyle::Information),
142            _ => None,
143        }
144    }
145}
146
147/// Configuration for a data validation rule.
148#[derive(Debug, Clone)]
149pub struct DataValidationConfig {
150    /// The cell range to apply validation to (e.g. "A1:A100").
151    pub sqref: String,
152    /// The type of validation.
153    pub validation_type: ValidationType,
154    /// The comparison operator (not used for list validations).
155    pub operator: Option<ValidationOperator>,
156    /// The first formula/value for the validation constraint.
157    pub formula1: Option<String>,
158    /// The second formula/value (used with Between/NotBetween operators).
159    pub formula2: Option<String>,
160    /// Whether blank cells are allowed.
161    pub allow_blank: bool,
162    /// The error display style.
163    pub error_style: Option<ErrorStyle>,
164    /// The title for the error dialog.
165    pub error_title: Option<String>,
166    /// The message for the error dialog.
167    pub error_message: Option<String>,
168    /// The title for the input prompt.
169    pub prompt_title: Option<String>,
170    /// The message for the input prompt.
171    pub prompt_message: Option<String>,
172    /// Whether to show the input message when the cell is selected.
173    pub show_input_message: bool,
174    /// Whether to show the error message on invalid input.
175    pub show_error_message: bool,
176}
177
178impl DataValidationConfig {
179    /// Create a dropdown list validation.
180    ///
181    /// The items are joined with commas and quoted for the formula.
182    pub fn dropdown(sqref: &str, items: &[&str]) -> Self {
183        let formula = format!("\"{}\"", items.join(",").replace('"', "\"\""));
184        Self {
185            sqref: sqref.to_string(),
186            validation_type: ValidationType::List,
187            operator: None,
188            formula1: Some(formula),
189            formula2: None,
190            allow_blank: true,
191            error_style: Some(ErrorStyle::Stop),
192            error_title: None,
193            error_message: None,
194            prompt_title: None,
195            prompt_message: None,
196            show_input_message: true,
197            show_error_message: true,
198        }
199    }
200
201    /// Create a whole number range validation (between min and max).
202    pub fn whole_number(sqref: &str, min: i64, max: i64) -> Self {
203        Self {
204            sqref: sqref.to_string(),
205            validation_type: ValidationType::Whole,
206            operator: Some(ValidationOperator::Between),
207            formula1: Some(min.to_string()),
208            formula2: Some(max.to_string()),
209            allow_blank: true,
210            error_style: Some(ErrorStyle::Stop),
211            error_title: None,
212            error_message: None,
213            prompt_title: None,
214            prompt_message: None,
215            show_input_message: true,
216            show_error_message: true,
217        }
218    }
219
220    /// Create a decimal range validation (between min and max).
221    pub fn decimal(sqref: &str, min: f64, max: f64) -> Self {
222        Self {
223            sqref: sqref.to_string(),
224            validation_type: ValidationType::Decimal,
225            operator: Some(ValidationOperator::Between),
226            formula1: Some(min.to_string()),
227            formula2: Some(max.to_string()),
228            allow_blank: true,
229            error_style: Some(ErrorStyle::Stop),
230            error_title: None,
231            error_message: None,
232            prompt_title: None,
233            prompt_message: None,
234            show_input_message: true,
235            show_error_message: true,
236        }
237    }
238
239    /// Create a text length validation.
240    pub fn text_length(sqref: &str, operator: ValidationOperator, length: u32) -> Self {
241        Self {
242            sqref: sqref.to_string(),
243            validation_type: ValidationType::TextLength,
244            operator: Some(operator),
245            formula1: Some(length.to_string()),
246            formula2: None,
247            allow_blank: true,
248            error_style: Some(ErrorStyle::Stop),
249            error_title: None,
250            error_message: None,
251            prompt_title: None,
252            prompt_message: None,
253            show_input_message: true,
254            show_error_message: true,
255        }
256    }
257}
258
259/// Validate that `sqref` looks like a valid cell range reference.
260///
261/// Accepts single refs ("A1"), ranges ("A1:B10"), and space-separated
262/// multi-area refs ("A1:B10 D1:E10"). This is not exhaustive but catches
263/// obvious mistakes like empty strings.
264fn validate_sqref(sqref: &str) -> Result<()> {
265    if sqref.is_empty() {
266        return Err(Error::InvalidReference {
267            reference: sqref.to_string(),
268        });
269    }
270    // Each part (split by space) must match a cell or range pattern.
271    for part in sqref.split(' ') {
272        if part.is_empty() {
273            return Err(Error::InvalidReference {
274                reference: sqref.to_string(),
275            });
276        }
277        // Allow "A1" or "A1:B10" shapes.  Each side must start with a letter
278        // and contain at least one digit.
279        for side in part.split(':') {
280            let has_alpha = side.chars().any(|c| c.is_ascii_alphabetic());
281            let has_digit = side.chars().any(|c| c.is_ascii_digit());
282            if !has_alpha || !has_digit {
283                return Err(Error::InvalidReference {
284                    reference: sqref.to_string(),
285                });
286            }
287        }
288    }
289    Ok(())
290}
291
292/// Validate formula constraints for the given validation type and operator.
293fn validate_formulas(config: &DataValidationConfig) -> Result<()> {
294    let has_formula1 = config
295        .formula1
296        .as_ref()
297        .is_some_and(|formula| !formula.is_empty());
298    let has_formula2 = config
299        .formula2
300        .as_ref()
301        .is_some_and(|formula| !formula.is_empty());
302
303    match config.validation_type {
304        ValidationType::None => {
305            if config.operator.is_some() || has_formula1 || has_formula2 {
306                return Err(Error::InvalidArgument(
307                    "none validation does not accept an operator or formulas".to_string(),
308                ));
309            }
310        }
311        ValidationType::List => {
312            if !has_formula1 {
313                return Err(Error::InvalidArgument(
314                    "formula1 is required for List validation".to_string(),
315                ));
316            }
317            if config.operator.is_some() || has_formula2 {
318                return Err(Error::InvalidArgument(
319                    "list validation does not accept an operator or formula2".to_string(),
320                ));
321            }
322            let formula1 = config.formula1.as_ref().unwrap();
323            if is_inline_list_formula(formula1) && formula1.chars().count() > 255 {
324                return Err(Error::InvalidArgument(format!(
325                    "inline list formula1 is {} characters (max 255)",
326                    formula1.chars().count()
327                )));
328            }
329        }
330        ValidationType::Custom => {
331            if !has_formula1 {
332                return Err(Error::InvalidArgument(
333                    "formula1 is required for Custom validation".to_string(),
334                ));
335            }
336            if config.operator.is_some() || has_formula2 {
337                return Err(Error::InvalidArgument(
338                    "custom validation does not accept an operator or formula2".to_string(),
339                ));
340            }
341        }
342        _ => {
343            if !has_formula1 {
344                return Err(Error::InvalidArgument(format!(
345                    "formula1 is required for {:?} validation",
346                    config.validation_type
347                )));
348            }
349            let operator = config.operator.as_ref().ok_or_else(|| {
350                Error::InvalidArgument(format!(
351                    "operator is required for {:?} validation",
352                    config.validation_type
353                ))
354            })?;
355            if operator.needs_formula2() != has_formula2 {
356                let requirement = if operator.needs_formula2() {
357                    "formula2 is required"
358                } else {
359                    "formula2 is only valid"
360                };
361                return Err(Error::InvalidArgument(format!(
362                    "{requirement} for {:?} operator",
363                    operator
364                )));
365            }
366        }
367    }
368    Ok(())
369}
370
371fn is_inline_list_formula(formula: &str) -> bool {
372    formula.starts_with('"') && formula.ends_with('"')
373}
374
375/// Convert a `DataValidationConfig` to the XML `DataValidation` struct.
376pub fn config_to_xml(config: &DataValidationConfig) -> DataValidation {
377    DataValidation {
378        validation_type: Some(config.validation_type.as_str().to_string()),
379        operator: config.operator.as_ref().map(|o| o.as_str().to_string()),
380        allow_blank: if config.allow_blank { Some(true) } else { None },
381        show_drop_down: None,
382        show_input_message: if config.show_input_message {
383            Some(true)
384        } else {
385            None
386        },
387        show_error_message: if config.show_error_message {
388            Some(true)
389        } else {
390            None
391        },
392        error_style: config.error_style.as_ref().map(|e| e.as_str().to_string()),
393        ime_mode: None,
394        error_title: config.error_title.clone(),
395        error: config.error_message.clone(),
396        prompt_title: config.prompt_title.clone(),
397        prompt: config.prompt_message.clone(),
398        sqref: config.sqref.clone(),
399        formula1: config.formula1.clone(),
400        formula2: config.formula2.clone(),
401    }
402}
403
404/// Convert an XML `DataValidation` to a `DataValidationConfig`.
405fn xml_to_config(dv: &DataValidation) -> DataValidationConfig {
406    DataValidationConfig {
407        sqref: dv.sqref.clone(),
408        validation_type: dv
409            .validation_type
410            .as_deref()
411            .and_then(ValidationType::parse)
412            .unwrap_or(ValidationType::None),
413        operator: dv.operator.as_deref().and_then(ValidationOperator::parse),
414        formula1: dv.formula1.clone(),
415        formula2: dv.formula2.clone(),
416        allow_blank: dv.allow_blank.unwrap_or(false),
417        error_style: dv.error_style.as_deref().and_then(ErrorStyle::parse),
418        error_title: dv.error_title.clone(),
419        error_message: dv.error.clone(),
420        prompt_title: dv.prompt_title.clone(),
421        prompt_message: dv.prompt.clone(),
422        show_input_message: dv.show_input_message.unwrap_or(false),
423        show_error_message: dv.show_error_message.unwrap_or(false),
424    }
425}
426
427/// Add a data validation to a worksheet.
428pub fn add_validation(ws: &mut WorksheetXml, config: &DataValidationConfig) -> Result<()> {
429    validate_sqref(&config.sqref)?;
430    validate_formulas(config)?;
431    let dv = config_to_xml(config);
432    let dvs = ws.data_validations.get_or_insert_with(|| DataValidations {
433        count: Some(0),
434        disable_prompts: None,
435        x_window: None,
436        y_window: None,
437        data_validations: Vec::new(),
438    });
439    dvs.data_validations.push(dv);
440    dvs.count = Some(dvs.data_validations.len() as u32);
441    Ok(())
442}
443
444/// Get all data validations from a worksheet.
445pub fn get_validations(ws: &WorksheetXml) -> Vec<DataValidationConfig> {
446    match &ws.data_validations {
447        Some(dvs) => dvs.data_validations.iter().map(xml_to_config).collect(),
448        None => Vec::new(),
449    }
450}
451
452/// Remove validations matching a specific cell range from a worksheet.
453///
454/// Returns `Ok(())` regardless of whether any validations were actually removed.
455pub fn remove_validation(ws: &mut WorksheetXml, sqref: &str) -> Result<()> {
456    if let Some(ref mut dvs) = ws.data_validations {
457        dvs.data_validations.retain(|dv| dv.sqref != sqref);
458        dvs.count = Some(dvs.data_validations.len() as u32);
459        if dvs.data_validations.is_empty() {
460            ws.data_validations = None;
461        }
462    }
463    Ok(())
464}
465
466#[cfg(test)]
467mod tests {
468    use super::*;
469
470    #[test]
471    fn test_dropdown_validation() {
472        let config = DataValidationConfig::dropdown("A1:A100", &["Yes", "No", "Maybe"]);
473        assert_eq!(config.sqref, "A1:A100");
474        assert_eq!(config.validation_type, ValidationType::List);
475        assert_eq!(config.formula1, Some("\"Yes,No,Maybe\"".to_string()));
476        assert!(config.allow_blank);
477        assert!(config.show_input_message);
478        assert!(config.show_error_message);
479    }
480
481    #[test]
482    fn test_dropdown_escapes_embedded_quotes() {
483        let config = DataValidationConfig::dropdown("A1", &["North, East", "He said \"yes\""]);
484        assert_eq!(
485            config.formula1,
486            Some("\"North, East,He said \"\"yes\"\"\"".to_string())
487        );
488    }
489
490    #[test]
491    fn test_dropdown_quote_escaping_survives_xml_roundtrip() {
492        let mut ws = WorksheetXml::default();
493        let config = DataValidationConfig::dropdown("A1", &["North, East", "He said \"yes\""]);
494        add_validation(&mut ws, &config).unwrap();
495
496        let xml = quick_xml::se::to_string(&ws).unwrap();
497        assert!(xml.contains("<formula1>\"North, East,He said \"\"yes\"\"\"</formula1>"));
498
499        let reopened: WorksheetXml = quick_xml::de::from_str(&xml).unwrap();
500        assert_eq!(
501            get_validations(&reopened)[0].formula1,
502            Some("\"North, East,He said \"\"yes\"\"\"".to_string())
503        );
504    }
505
506    #[test]
507    fn test_whole_number_validation() {
508        let config = DataValidationConfig::whole_number("B1:B50", 1, 100);
509        assert_eq!(config.sqref, "B1:B50");
510        assert_eq!(config.validation_type, ValidationType::Whole);
511        assert_eq!(config.operator, Some(ValidationOperator::Between));
512        assert_eq!(config.formula1, Some("1".to_string()));
513        assert_eq!(config.formula2, Some("100".to_string()));
514    }
515
516    #[test]
517    fn test_decimal_validation() {
518        let config = DataValidationConfig::decimal("C1:C10", 0.0, 99.99);
519        assert_eq!(config.sqref, "C1:C10");
520        assert_eq!(config.validation_type, ValidationType::Decimal);
521        assert_eq!(config.operator, Some(ValidationOperator::Between));
522        assert_eq!(config.formula1, Some("0".to_string()));
523        assert_eq!(config.formula2, Some("99.99".to_string()));
524    }
525
526    #[test]
527    fn test_text_length_validation() {
528        let config =
529            DataValidationConfig::text_length("D1:D10", ValidationOperator::LessThanOrEqual, 255);
530        assert_eq!(config.sqref, "D1:D10");
531        assert_eq!(config.validation_type, ValidationType::TextLength);
532        assert_eq!(config.operator, Some(ValidationOperator::LessThanOrEqual));
533        assert_eq!(config.formula1, Some("255".to_string()));
534    }
535
536    #[test]
537    fn test_config_to_xml_roundtrip() {
538        let config = DataValidationConfig::dropdown("A1:A10", &["Red", "Blue"]);
539        let xml = config_to_xml(&config);
540        assert_eq!(xml.validation_type, Some("list".to_string()));
541        assert_eq!(xml.sqref, "A1:A10");
542        assert_eq!(xml.formula1, Some("\"Red,Blue\"".to_string()));
543        assert_eq!(xml.allow_blank, Some(true));
544        assert_eq!(xml.show_input_message, Some(true));
545        assert_eq!(xml.show_error_message, Some(true));
546    }
547
548    #[test]
549    fn test_add_validation_to_worksheet() {
550        let mut ws = WorksheetXml::default();
551        let config = DataValidationConfig::dropdown("A1:A100", &["Yes", "No"]);
552        add_validation(&mut ws, &config).unwrap();
553
554        assert!(ws.data_validations.is_some());
555        let dvs = ws.data_validations.as_ref().unwrap();
556        assert_eq!(dvs.count, Some(1));
557        assert_eq!(dvs.data_validations.len(), 1);
558        assert_eq!(dvs.data_validations[0].sqref, "A1:A100");
559    }
560
561    #[test]
562    fn test_add_multiple_validations() {
563        let mut ws = WorksheetXml::default();
564        let config1 = DataValidationConfig::dropdown("A1:A100", &["Yes", "No"]);
565        let config2 = DataValidationConfig::whole_number("B1:B100", 1, 100);
566        add_validation(&mut ws, &config1).unwrap();
567        add_validation(&mut ws, &config2).unwrap();
568
569        let dvs = ws.data_validations.as_ref().unwrap();
570        assert_eq!(dvs.count, Some(2));
571        assert_eq!(dvs.data_validations.len(), 2);
572    }
573
574    #[test]
575    fn test_get_validations() {
576        let mut ws = WorksheetXml::default();
577        let config = DataValidationConfig::dropdown("A1:A100", &["Yes", "No"]);
578        add_validation(&mut ws, &config).unwrap();
579
580        let configs = get_validations(&ws);
581        assert_eq!(configs.len(), 1);
582        assert_eq!(configs[0].sqref, "A1:A100");
583        assert_eq!(configs[0].validation_type, ValidationType::List);
584    }
585
586    #[test]
587    fn test_get_validations_empty() {
588        let ws = WorksheetXml::default();
589        let configs = get_validations(&ws);
590        assert!(configs.is_empty());
591    }
592
593    #[test]
594    fn test_remove_validation() {
595        let mut ws = WorksheetXml::default();
596        let config1 = DataValidationConfig::dropdown("A1:A100", &["Yes", "No"]);
597        let config2 = DataValidationConfig::whole_number("B1:B100", 1, 100);
598        add_validation(&mut ws, &config1).unwrap();
599        add_validation(&mut ws, &config2).unwrap();
600
601        remove_validation(&mut ws, "A1:A100").unwrap();
602
603        let dvs = ws.data_validations.as_ref().unwrap();
604        assert_eq!(dvs.count, Some(1));
605        assert_eq!(dvs.data_validations.len(), 1);
606        assert_eq!(dvs.data_validations[0].sqref, "B1:B100");
607    }
608
609    #[test]
610    fn test_remove_last_validation_clears_container() {
611        let mut ws = WorksheetXml::default();
612        let config = DataValidationConfig::dropdown("A1:A100", &["Yes", "No"]);
613        add_validation(&mut ws, &config).unwrap();
614        remove_validation(&mut ws, "A1:A100").unwrap();
615
616        assert!(ws.data_validations.is_none());
617    }
618
619    #[test]
620    fn test_remove_nonexistent_validation() {
621        let mut ws = WorksheetXml::default();
622        // Should not error when removing from empty worksheet
623        remove_validation(&mut ws, "Z1:Z99").unwrap();
624        assert!(ws.data_validations.is_none());
625    }
626
627    #[test]
628    fn test_validation_xml_serialization_roundtrip() {
629        let mut ws = WorksheetXml::default();
630        let config = DataValidationConfig::dropdown("A1:A10", &["Apple", "Banana"]);
631        add_validation(&mut ws, &config).unwrap();
632
633        let xml = quick_xml::se::to_string(&ws).unwrap();
634        assert!(xml.contains("dataValidations"));
635        assert!(xml.contains("A1:A10"));
636
637        let parsed: WorksheetXml = quick_xml::de::from_str(&xml).unwrap();
638        assert!(parsed.data_validations.is_some());
639        let dvs = parsed.data_validations.as_ref().unwrap();
640        assert_eq!(dvs.data_validations.len(), 1);
641        assert_eq!(dvs.data_validations[0].sqref, "A1:A10");
642        assert_eq!(
643            dvs.data_validations[0].validation_type,
644            Some("list".to_string())
645        );
646    }
647
648    #[test]
649    fn test_whole_number_validation_xml_roundtrip() {
650        let mut ws = WorksheetXml::default();
651        let config = DataValidationConfig::whole_number("B1:B50", 10, 200);
652        add_validation(&mut ws, &config).unwrap();
653
654        let xml = quick_xml::se::to_string(&ws).unwrap();
655        let parsed: WorksheetXml = quick_xml::de::from_str(&xml).unwrap();
656
657        let configs = get_validations(&parsed);
658        assert_eq!(configs.len(), 1);
659        assert_eq!(configs[0].sqref, "B1:B50");
660        assert_eq!(configs[0].validation_type, ValidationType::Whole);
661        assert_eq!(configs[0].operator, Some(ValidationOperator::Between));
662        assert_eq!(configs[0].formula1, Some("10".to_string()));
663        assert_eq!(configs[0].formula2, Some("200".to_string()));
664    }
665
666    #[test]
667    fn test_decimal_validation_xml_roundtrip() {
668        let mut ws = WorksheetXml::default();
669        let config = DataValidationConfig::decimal("C1:C10", 1.5, 99.9);
670        add_validation(&mut ws, &config).unwrap();
671
672        let xml = quick_xml::se::to_string(&ws).unwrap();
673        let parsed: WorksheetXml = quick_xml::de::from_str(&xml).unwrap();
674
675        let configs = get_validations(&parsed);
676        assert_eq!(configs.len(), 1);
677        assert_eq!(configs[0].validation_type, ValidationType::Decimal);
678    }
679
680    #[test]
681    fn test_validation_type_as_str() {
682        assert_eq!(ValidationType::None.as_str(), "none");
683        assert_eq!(ValidationType::Whole.as_str(), "whole");
684        assert_eq!(ValidationType::Decimal.as_str(), "decimal");
685        assert_eq!(ValidationType::List.as_str(), "list");
686        assert_eq!(ValidationType::Date.as_str(), "date");
687        assert_eq!(ValidationType::Time.as_str(), "time");
688        assert_eq!(ValidationType::TextLength.as_str(), "textLength");
689        assert_eq!(ValidationType::Custom.as_str(), "custom");
690    }
691
692    #[test]
693    fn test_validation_operator_as_str() {
694        assert_eq!(ValidationOperator::Between.as_str(), "between");
695        assert_eq!(ValidationOperator::NotBetween.as_str(), "notBetween");
696        assert_eq!(ValidationOperator::Equal.as_str(), "equal");
697        assert_eq!(ValidationOperator::NotEqual.as_str(), "notEqual");
698        assert_eq!(ValidationOperator::LessThan.as_str(), "lessThan");
699        assert_eq!(
700            ValidationOperator::LessThanOrEqual.as_str(),
701            "lessThanOrEqual"
702        );
703        assert_eq!(ValidationOperator::GreaterThan.as_str(), "greaterThan");
704        assert_eq!(
705            ValidationOperator::GreaterThanOrEqual.as_str(),
706            "greaterThanOrEqual"
707        );
708    }
709
710    #[test]
711    fn test_error_style_as_str() {
712        assert_eq!(ErrorStyle::Stop.as_str(), "stop");
713        assert_eq!(ErrorStyle::Warning.as_str(), "warning");
714        assert_eq!(ErrorStyle::Information.as_str(), "information");
715    }
716
717    #[test]
718    fn test_none_type_roundtrip() {
719        assert_eq!(ValidationType::parse("none"), Some(ValidationType::None));
720        assert_eq!(ValidationType::None.as_str(), "none");
721    }
722
723    #[test]
724    fn test_unknown_type_defaults_to_none() {
725        let dv = DataValidation {
726            validation_type: Some("unknownFuture".to_string()),
727            operator: None,
728            allow_blank: None,
729            show_drop_down: None,
730            show_input_message: None,
731            show_error_message: None,
732            error_style: None,
733            ime_mode: None,
734            error_title: None,
735            error: None,
736            prompt_title: None,
737            prompt: None,
738            sqref: "A1".to_string(),
739            formula1: None,
740            formula2: None,
741        };
742        let config = xml_to_config(&dv);
743        assert_eq!(config.validation_type, ValidationType::None);
744    }
745
746    #[test]
747    fn test_validate_sqref_valid() {
748        assert!(validate_sqref("A1").is_ok());
749        assert!(validate_sqref("A1:B10").is_ok());
750        assert!(validate_sqref("A1:B10 D1:E10").is_ok());
751        assert!(validate_sqref("AA100:ZZ999").is_ok());
752    }
753
754    #[test]
755    fn test_validate_sqref_invalid() {
756        assert!(validate_sqref("").is_err());
757        assert!(validate_sqref("hello").is_err());
758        assert!(validate_sqref("123").is_err());
759        assert!(validate_sqref("A1: B10").is_err()); // space inside range
760    }
761
762    #[test]
763    fn test_add_validation_rejects_empty_sqref() {
764        let mut ws = WorksheetXml::default();
765        let config = DataValidationConfig {
766            sqref: "".to_string(),
767            validation_type: ValidationType::List,
768            operator: None,
769            formula1: Some("\"A,B\"".to_string()),
770            formula2: None,
771            allow_blank: false,
772            error_style: None,
773            error_title: None,
774            error_message: None,
775            prompt_title: None,
776            prompt_message: None,
777            show_input_message: false,
778            show_error_message: false,
779        };
780        assert!(add_validation(&mut ws, &config).is_err());
781    }
782
783    #[test]
784    fn test_add_validation_rejects_missing_formula1_for_list() {
785        let mut ws = WorksheetXml::default();
786        let config = DataValidationConfig {
787            sqref: "A1:A10".to_string(),
788            validation_type: ValidationType::List,
789            operator: None,
790            formula1: None,
791            formula2: None,
792            allow_blank: false,
793            error_style: None,
794            error_title: None,
795            error_message: None,
796            prompt_title: None,
797            prompt_message: None,
798            show_input_message: false,
799            show_error_message: false,
800        };
801        assert!(add_validation(&mut ws, &config).is_err());
802    }
803
804    #[test]
805    fn test_add_validation_rejects_missing_formula2_for_between() {
806        let mut ws = WorksheetXml::default();
807        let config = DataValidationConfig {
808            sqref: "A1:A10".to_string(),
809            validation_type: ValidationType::Whole,
810            operator: Some(ValidationOperator::Between),
811            formula1: Some("1".to_string()),
812            formula2: None,
813            allow_blank: false,
814            error_style: None,
815            error_title: None,
816            error_message: None,
817            prompt_title: None,
818            prompt_message: None,
819            show_input_message: false,
820            show_error_message: false,
821        };
822        assert!(add_validation(&mut ws, &config).is_err());
823    }
824
825    #[test]
826    fn test_validation_type_requires_its_applicable_operator_and_formulas() {
827        let base = |validation_type, operator, formula1, formula2| DataValidationConfig {
828            sqref: "A1".to_string(),
829            validation_type,
830            operator,
831            formula1,
832            formula2,
833            allow_blank: true,
834            error_style: None,
835            error_title: None,
836            error_message: None,
837            prompt_title: None,
838            prompt_message: None,
839            show_input_message: true,
840            show_error_message: true,
841        };
842        let mut ws = WorksheetXml::default();
843
844        assert!(add_validation(
845            &mut ws,
846            &base(ValidationType::Date, None, Some("1".to_string()), None)
847        )
848        .is_err());
849        assert!(add_validation(
850            &mut ws,
851            &base(
852                ValidationType::Time,
853                Some(ValidationOperator::Equal),
854                Some("1".to_string()),
855                Some("2".to_string()),
856            )
857        )
858        .is_err());
859        assert!(add_validation(
860            &mut ws,
861            &base(
862                ValidationType::Custom,
863                Some(ValidationOperator::Equal),
864                Some("A1>0".to_string()),
865                None,
866            )
867        )
868        .is_err());
869        assert!(add_validation(
870            &mut ws,
871            &base(
872                ValidationType::TextLength,
873                Some(ValidationOperator::LessThanOrEqual),
874                Some("10".to_string()),
875                None,
876            )
877        )
878        .is_ok());
879    }
880
881    #[test]
882    fn test_inline_list_formula_limit_counts_characters_and_preserves_worksheet() {
883        let mut ws = WorksheetXml::default();
884        let valid = DataValidationConfig::dropdown("A1", &[&"a".repeat(253)]);
885        add_validation(&mut ws, &valid).unwrap();
886
887        let too_long = DataValidationConfig::dropdown("A2", &[&"a".repeat(254)]);
888        let error = add_validation(&mut ws, &too_long).unwrap_err();
889        assert!(error.to_string().contains("255"));
890        assert_eq!(
891            ws.data_validations.as_ref().unwrap().data_validations.len(),
892            1
893        );
894
895        let non_ascii = DataValidationConfig::dropdown("A3", &[&"é".repeat(253)]);
896        add_validation(&mut ws, &non_ascii).unwrap();
897
898        let non_ascii_too_long = DataValidationConfig::dropdown("A4", &[&"é".repeat(254)]);
899        assert!(add_validation(&mut ws, &non_ascii_too_long).is_err());
900    }
901
902    #[test]
903    fn test_list_range_source_is_not_subject_to_inline_list_limit() {
904        let mut ws = WorksheetXml::default();
905        let config = DataValidationConfig {
906            sqref: "A1".to_string(),
907            validation_type: ValidationType::List,
908            operator: None,
909            formula1: Some(format!("=Sheet1!${}$1", "A".repeat(300))),
910            formula2: None,
911            allow_blank: true,
912            error_style: None,
913            error_title: None,
914            error_message: None,
915            prompt_title: None,
916            prompt_message: None,
917            show_input_message: true,
918            show_error_message: true,
919        };
920        assert!(add_validation(&mut ws, &config).is_ok());
921    }
922
923    #[test]
924    fn test_none_type_no_formula_required() {
925        let mut ws = WorksheetXml::default();
926        let config = DataValidationConfig {
927            sqref: "A1:A10".to_string(),
928            validation_type: ValidationType::None,
929            operator: None,
930            formula1: None,
931            formula2: None,
932            allow_blank: false,
933            error_style: None,
934            error_title: None,
935            error_message: None,
936            prompt_title: Some("Hint".to_string()),
937            prompt_message: Some("Enter a value".to_string()),
938            show_input_message: true,
939            show_error_message: false,
940        };
941        assert!(add_validation(&mut ws, &config).is_ok());
942        let configs = get_validations(&ws);
943        assert_eq!(configs[0].validation_type, ValidationType::None);
944    }
945
946    #[test]
947    fn test_uses_operator() {
948        assert!(!ValidationType::None.uses_operator());
949        assert!(ValidationType::Whole.uses_operator());
950        assert!(ValidationType::Decimal.uses_operator());
951        assert!(!ValidationType::List.uses_operator());
952        assert!(ValidationType::Date.uses_operator());
953        assert!(ValidationType::Time.uses_operator());
954        assert!(ValidationType::TextLength.uses_operator());
955        assert!(!ValidationType::Custom.uses_operator());
956    }
957
958    #[test]
959    fn test_needs_formula2() {
960        assert!(ValidationOperator::Between.needs_formula2());
961        assert!(ValidationOperator::NotBetween.needs_formula2());
962        assert!(!ValidationOperator::Equal.needs_formula2());
963        assert!(!ValidationOperator::GreaterThan.needs_formula2());
964    }
965
966    #[test]
967    fn test_show_drop_down_preserved_in_xml() {
968        let dv = DataValidation {
969            validation_type: Some("list".to_string()),
970            operator: None,
971            allow_blank: None,
972            show_drop_down: Some(true),
973            show_input_message: None,
974            show_error_message: None,
975            error_style: None,
976            ime_mode: None,
977            error_title: None,
978            error: None,
979            prompt_title: None,
980            prompt: None,
981            sqref: "A1".to_string(),
982            formula1: Some("\"A,B\"".to_string()),
983            formula2: None,
984        };
985        let xml = quick_xml::se::to_string(&dv).unwrap();
986        assert!(xml.contains("showDropDown"));
987
988        let parsed: DataValidation = quick_xml::de::from_str(&xml).unwrap();
989        assert_eq!(parsed.show_drop_down, Some(true));
990    }
991
992    #[test]
993    fn test_ime_mode_preserved_in_xml() {
994        let dv = DataValidation {
995            validation_type: Some("whole".to_string()),
996            operator: None,
997            allow_blank: None,
998            show_drop_down: None,
999            show_input_message: None,
1000            show_error_message: None,
1001            error_style: None,
1002            ime_mode: Some("hiragana".to_string()),
1003            error_title: None,
1004            error: None,
1005            prompt_title: None,
1006            prompt: None,
1007            sqref: "A1".to_string(),
1008            formula1: Some("1".to_string()),
1009            formula2: None,
1010        };
1011        let xml = quick_xml::se::to_string(&dv).unwrap();
1012        assert!(xml.contains("imeMode"));
1013
1014        let parsed: DataValidation = quick_xml::de::from_str(&xml).unwrap();
1015        assert_eq!(parsed.ime_mode, Some("hiragana".to_string()));
1016    }
1017
1018    #[test]
1019    fn test_container_attrs_preserved_in_xml() {
1020        let dvs = DataValidations {
1021            count: Some(0),
1022            disable_prompts: Some(true),
1023            x_window: Some(100),
1024            y_window: Some(200),
1025            data_validations: Vec::new(),
1026        };
1027        let xml = quick_xml::se::to_string(&dvs).unwrap();
1028        assert!(xml.contains("disablePrompts"));
1029        assert!(xml.contains("xWindow"));
1030        assert!(xml.contains("yWindow"));
1031
1032        let parsed: DataValidations = quick_xml::de::from_str(&xml).unwrap();
1033        assert_eq!(parsed.disable_prompts, Some(true));
1034        assert_eq!(parsed.x_window, Some(100));
1035        assert_eq!(parsed.y_window, Some(200));
1036    }
1037}