excelize_rs/
data_validation.rs

1//! Data validation support.
2//!
3//! Ported from Go `datavalidation.go`.
4
5use std::collections::HashMap;
6
7use quick_xml::de::from_reader as xml_from_reader;
8use quick_xml::se::to_string as xml_to_string;
9use serde::{Deserialize, Serialize};
10
11use crate::File;
12use crate::constants::{
13    EXT_URI_DATA_VALIDATIONS, MAX_FIELD_LENGTH, NAMESPACE_SPREADSHEET_EXCEL_2006_MAIN,
14    NAMESPACE_SPREADSHEET_X14,
15};
16use crate::errors::{
17    ErrDataValidationFormulaLength, ErrDataValidationRange, ErrParameterInvalid, ErrSheetNotExist,
18    Result,
19};
20use crate::lib_util::{
21    coordinates_to_cell_name, coordinates_to_range_ref, count_utf16_string, flat_sqref,
22    in_coordinates,
23};
24use crate::xml::common::XlsxInnerXml;
25use crate::xml::worksheet::{
26    DataValidation, XlsxDataValidation, XlsxDataValidations, XlsxWorksheet, XlsxX14DataValidation,
27    XlsxX14DataValidations,
28};
29
30/// Data validation type.
31#[derive(Debug, Clone, Copy, PartialEq, Eq)]
32pub struct DataValidationType(pub u8);
33
34impl DataValidationType {
35    pub const NONE: DataValidationType = DataValidationType(0);
36    pub const CUSTOM: DataValidationType = DataValidationType(1);
37    pub const DATE: DataValidationType = DataValidationType(2);
38    pub const DECIMAL: DataValidationType = DataValidationType(3);
39    pub const LIST: DataValidationType = DataValidationType(4);
40    pub const TEXT_LENGTH: DataValidationType = DataValidationType(5);
41    pub const TIME: DataValidationType = DataValidationType(6);
42    pub const WHOLE: DataValidationType = DataValidationType(7);
43}
44
45/// Data validation error style.
46#[derive(Debug, Clone, Copy, PartialEq, Eq)]
47pub struct DataValidationErrorStyle(pub u8);
48
49impl DataValidationErrorStyle {
50    pub const STOP: DataValidationErrorStyle = DataValidationErrorStyle(1);
51    pub const WARNING: DataValidationErrorStyle = DataValidationErrorStyle(2);
52    pub const INFORMATION: DataValidationErrorStyle = DataValidationErrorStyle(3);
53}
54
55/// Data validation operator.
56#[derive(Debug, Clone, Copy, PartialEq, Eq)]
57pub struct DataValidationOperator(pub u8);
58
59impl DataValidationOperator {
60    pub const BETWEEN: DataValidationOperator = DataValidationOperator(1);
61    pub const EQUAL: DataValidationOperator = DataValidationOperator(2);
62    pub const GREATER_THAN: DataValidationOperator = DataValidationOperator(3);
63    pub const GREATER_THAN_OR_EQUAL: DataValidationOperator = DataValidationOperator(4);
64    pub const LESS_THAN: DataValidationOperator = DataValidationOperator(5);
65    pub const LESS_THAN_OR_EQUAL: DataValidationOperator = DataValidationOperator(6);
66    pub const NOT_BETWEEN: DataValidationOperator = DataValidationOperator(7);
67    pub const NOT_EQUAL: DataValidationOperator = DataValidationOperator(8);
68}
69
70/// Value accepted by [`DataValidation::set_range`].
71#[derive(Debug, Clone, PartialEq)]
72pub enum DataValidationValue {
73    /// No value (corresponds to Go `nil`).
74    None,
75    /// Integer formula value.
76    Integer(i64),
77    /// Floating point formula value.
78    Float(f64),
79    /// Literal formula text.
80    Text(String),
81}
82
83impl From<i64> for DataValidationValue {
84    fn from(v: i64) -> Self {
85        DataValidationValue::Integer(v)
86    }
87}
88
89impl From<f64> for DataValidationValue {
90    fn from(v: f64) -> Self {
91        DataValidationValue::Float(v)
92    }
93}
94
95impl From<&str> for DataValidationValue {
96    fn from(v: &str) -> Self {
97        DataValidationValue::Text(v.to_string())
98    }
99}
100
101impl From<String> for DataValidationValue {
102    fn from(v: String) -> Self {
103        DataValidationValue::Text(v)
104    }
105}
106
107fn dv_type_str(t: DataValidationType) -> &'static str {
108    match t {
109        DataValidationType::NONE => "none",
110        DataValidationType::CUSTOM => "custom",
111        DataValidationType::DATE => "date",
112        DataValidationType::DECIMAL => "decimal",
113        DataValidationType::LIST => "list",
114        DataValidationType::TEXT_LENGTH => "textLength",
115        DataValidationType::TIME => "time",
116        DataValidationType::WHOLE => "whole",
117        _ => "",
118    }
119}
120
121fn dv_error_style_str(s: DataValidationErrorStyle) -> &'static str {
122    match s {
123        DataValidationErrorStyle::WARNING => "warning",
124        DataValidationErrorStyle::INFORMATION => "information",
125        _ => "stop",
126    }
127}
128
129fn dv_operator_str(o: DataValidationOperator) -> &'static str {
130    match o {
131        DataValidationOperator::BETWEEN => "between",
132        DataValidationOperator::EQUAL => "equal",
133        DataValidationOperator::GREATER_THAN => "greaterThan",
134        DataValidationOperator::GREATER_THAN_OR_EQUAL => "greaterThanOrEqual",
135        DataValidationOperator::LESS_THAN => "lessThan",
136        DataValidationOperator::LESS_THAN_OR_EQUAL => "lessThanOrEqual",
137        DataValidationOperator::NOT_BETWEEN => "notBetween",
138        DataValidationOperator::NOT_EQUAL => "notEqual",
139        _ => "",
140    }
141}
142
143fn escape_formula(s: &str) -> String {
144    s.replace('&', "&amp;")
145        .replace('<', "&lt;")
146        .replace('>', "&gt;")
147}
148
149fn unescape_formula(s: &str) -> String {
150    s.replace("&amp;", "&")
151        .replace("&lt;", "<")
152        .replace("&gt;", ">")
153}
154
155fn unescape_data_validation_formula(val: &str) -> String {
156    let s = unescape_formula(val);
157    if s.starts_with('"') {
158        s.replace("\"\"", "\"")
159    } else {
160        s
161    }
162}
163
164/// Create a new data validation rule.
165pub fn new_data_validation(allow_blank: bool) -> DataValidation {
166    DataValidation {
167        allow_blank,
168        ..Default::default()
169    }
170}
171
172impl DataValidation {
173    /// Set the error alert for this data validation rule.
174    pub fn set_error(&mut self, style: DataValidationErrorStyle, title: &str, msg: &str) {
175        self.error = Some(msg.to_string());
176        self.error_title = Some(title.to_string());
177        self.show_error_message = true;
178        self.error_style = Some(dv_error_style_str(style).to_string());
179    }
180
181    /// Set the input prompt for this data validation rule.
182    pub fn set_input(&mut self, title: &str, msg: &str) {
183        self.show_input_message = true;
184        self.prompt_title = Some(title.to_string());
185        self.prompt = Some(msg.to_string());
186    }
187
188    /// Set a fixed drop-down list.
189    pub fn set_drop_list(&mut self, keys: &[&str]) -> Result<()> {
190        let formula = keys.join(",");
191        if MAX_FIELD_LENGTH < count_utf16_string(&formula) {
192            return Err(Box::new(ErrDataValidationFormulaLength));
193        }
194        self.r#type = dv_type_str(DataValidationType::LIST).to_string();
195        if formula.starts_with('=') {
196            self.formula1 = escape_formula(&formula);
197        } else {
198            let escaped = escape_formula(&formula).replace('"', "\"\"");
199            self.formula1 = format!("\"{escaped}\"");
200        }
201        Ok(())
202    }
203
204    /// Set a numeric range validation.
205    pub fn set_range(
206        &mut self,
207        f1: DataValidationValue,
208        f2: DataValidationValue,
209        t: DataValidationType,
210        o: DataValidationOperator,
211    ) -> Result<()> {
212        fn gen_formula(val: &DataValidationValue) -> Result<String> {
213            match val {
214                DataValidationValue::Integer(v) => Ok(format!("{}", v)),
215                DataValidationValue::Float(v) => {
216                    if v.abs() > f32::MAX as f64 {
217                        return Err(Box::new(ErrDataValidationRange));
218                    }
219                    Ok(format!("{}", v))
220                }
221                DataValidationValue::Text(s) => Ok(s.clone()),
222                DataValidationValue::None => Err(Box::new(ErrParameterInvalid)),
223            }
224        }
225        self.formula1 = gen_formula(&f1)?;
226        self.formula2 = gen_formula(&f2)?;
227        self.r#type = dv_type_str(t).to_string();
228        self.operator = dv_operator_str(o).to_string();
229        Ok(())
230    }
231
232    /// Set a drop-down list backed by a worksheet range reference.
233    pub fn set_sqref_drop_list(&mut self, sqref: &str) {
234        self.formula1 = sqref.to_string();
235        self.r#type = dv_type_str(DataValidationType::LIST).to_string();
236    }
237
238    /// Append a cell reference sequence to this rule's sqref.
239    pub fn set_sqref(&mut self, sqref: &str) {
240        if self.sqref.is_empty() {
241            self.sqref = sqref.to_string();
242        } else {
243            self.sqref = format!("{} {}", self.sqref, sqref);
244        }
245    }
246}
247
248impl File {
249    /// Add a data validation rule to a worksheet.
250    pub fn add_data_validation(&self, sheet: &str, dv: &DataValidation) -> Result<()> {
251        let path = self
252            .get_sheet_xml_path(sheet)
253            .ok_or_else(|| ErrSheetNotExist {
254                sheet_name: sheet.to_string(),
255            })?;
256        let mut ws = self.work_sheet_reader(sheet)?;
257        if ws.data_validations.is_none() {
258            ws.data_validations = Some(XlsxDataValidations::default());
259        }
260        let dvs = ws.data_validations.as_mut().unwrap();
261        let xdv = XlsxDataValidation {
262            allow_blank: Some(dv.allow_blank),
263            error: dv.error.clone(),
264            error_style: dv.error_style.clone(),
265            error_title: dv.error_title.clone(),
266            operator: Some(dv.operator.clone()).filter(|s| !s.is_empty()),
267            prompt: dv.prompt.clone(),
268            prompt_title: dv.prompt_title.clone(),
269            show_drop_down: Some(dv.show_drop_down),
270            show_error_message: Some(dv.show_error_message),
271            show_input_message: Some(dv.show_input_message),
272            sqref: dv.sqref.clone(),
273            r#type: Some(dv.r#type.clone()).filter(|s| !s.is_empty()),
274            formula1: if dv.formula1.is_empty() {
275                None
276            } else {
277                Some(XlsxInnerXml {
278                    content: dv.formula1.clone(),
279                })
280            },
281            formula2: if dv.formula2.is_empty() {
282                None
283            } else {
284                Some(XlsxInnerXml {
285                    content: dv.formula2.clone(),
286                })
287            },
288            xm_sqref: None,
289        };
290        dvs.data_validation.push(xdv);
291        dvs.count = Some(dvs.data_validation.len() as i64);
292        self.sheet.insert(path, ws);
293        Ok(())
294    }
295
296    /// Get the data validation rules for a worksheet.
297    pub fn get_data_validations(&self, sheet: &str) -> Result<Vec<DataValidation>> {
298        let ws = self.work_sheet_reader(sheet)?;
299        let mut out = Vec::new();
300        if let Some(dvs) = &ws.data_validations {
301            out.extend(get_data_validations(dvs));
302        }
303        if let Some(ext_lst) = &ws.ext_lst {
304            for ext in &ext_lst.ext {
305                if ext.uri.as_deref() != Some(EXT_URI_DATA_VALIDATIONS) {
306                    continue;
307                }
308                let dvs = parse_x14_data_validations(&ext.content)?;
309                for dv in &dvs.data_validation {
310                    out.push(x14_dv_to_public(dv));
311                }
312            }
313        }
314        Ok(out)
315    }
316
317    /// Delete data validation rules from a worksheet.
318    pub fn delete_data_validation(&self, sheet: &str, sqref: &[&str]) -> Result<()> {
319        let path = self
320            .get_sheet_xml_path(sheet)
321            .ok_or_else(|| ErrSheetNotExist {
322                sheet_name: sheet.to_string(),
323            })?;
324        let mut ws = self.work_sheet_reader(sheet)?;
325        if ws.data_validations.is_none() && ws.ext_lst.is_none() {
326            return Ok(());
327        }
328        if sqref.is_empty() {
329            ws.data_validations = None;
330            self.sheet.insert(path, ws);
331            return Ok(());
332        }
333        let del_cells = flat_sqref(&sqref.join(" "))?;
334        if let Some(dvs) = ws.data_validations.as_mut() {
335            delete_data_validation(dvs, &del_cells)?;
336            if dvs.data_validation.is_empty() {
337                ws.data_validations = None;
338            }
339        }
340        if ws.ext_lst.is_some() {
341            self.delete_x14_data_validation(&mut ws, &del_cells)?;
342        }
343        self.sheet.insert(path, ws);
344        Ok(())
345    }
346}
347
348fn xlsx_dv_to_public(dv: &XlsxDataValidation) -> DataValidation {
349    let mut formula1 = dv
350        .formula1
351        .as_ref()
352        .map(|f| unescape_data_validation_formula(&f.content))
353        .unwrap_or_default();
354    let mut formula2 = dv
355        .formula2
356        .as_ref()
357        .map(|f| unescape_data_validation_formula(&f.content))
358        .unwrap_or_default();
359    let mut sqref = dv.sqref.clone();
360    if let Some(xm) = &dv.xm_sqref {
361        sqref = xm.clone();
362        formula1 = formula1
363            .trim_start_matches("<xm:f>")
364            .trim_end_matches("</xm:f>")
365            .to_string();
366        formula2 = formula2
367            .trim_start_matches("<xm:f>")
368            .trim_end_matches("</xm:f>")
369            .to_string();
370    }
371    DataValidation {
372        allow_blank: dv.allow_blank.unwrap_or(false),
373        error: dv.error.clone(),
374        error_style: dv.error_style.clone(),
375        error_title: dv.error_title.clone(),
376        operator: dv.operator.clone().unwrap_or_default(),
377        prompt: dv.prompt.clone(),
378        prompt_title: dv.prompt_title.clone(),
379        show_drop_down: dv.show_drop_down.unwrap_or(false),
380        show_error_message: dv.show_error_message.unwrap_or(false),
381        show_input_message: dv.show_input_message.unwrap_or(false),
382        sqref,
383        r#type: dv.r#type.clone().unwrap_or_default(),
384        formula1,
385        formula2,
386    }
387}
388
389fn get_data_validations(dvs: &XlsxDataValidations) -> Vec<DataValidation> {
390    dvs.data_validation.iter().map(xlsx_dv_to_public).collect()
391}
392
393fn delete_data_validation(
394    dvs: &mut XlsxDataValidations,
395    del_cells: &HashMap<i32, Vec<Vec<i32>>>,
396) -> Result<()> {
397    let mut i = 0;
398    while i < dvs.data_validation.len() {
399        let new_sqref = delete_cells_from_sqref(&dvs.data_validation[i].sqref, del_cells)?;
400        dvs.data_validation[i].sqref = new_sqref;
401        if dvs.data_validation[i].sqref.is_empty() {
402            dvs.data_validation.remove(i);
403        } else {
404            i += 1;
405        }
406    }
407    dvs.count = Some(dvs.data_validation.len() as i64);
408    Ok(())
409}
410
411impl File {
412    fn delete_x14_data_validation(
413        &self,
414        ws: &mut XlsxWorksheet,
415        del_cells: &HashMap<i32, Vec<Vec<i32>>>,
416    ) -> Result<()> {
417        let mut x14 = crate::xml::common::XlsxExtLst::default();
418        if let Some(ext_lst) = ws.ext_lst.take() {
419            for ext in ext_lst.ext {
420                if ext.uri.as_deref() != Some(EXT_URI_DATA_VALIDATIONS) {
421                    x14.ext.push(ext);
422                    continue;
423                }
424                let dvs = parse_x14_data_validations(&ext.content)?;
425                let mut new_dvs = XlsxX14DataValidations {
426                    xmlns_xm: Some(NAMESPACE_SPREADSHEET_EXCEL_2006_MAIN.to_string()),
427                    count: None,
428                    disable_prompts: dvs.disable_prompts,
429                    x_window: dvs.x_window,
430                    y_window: dvs.y_window,
431                    data_validation: Vec::new(),
432                };
433                for dv in &dvs.data_validation {
434                    let sqref_to_delete = dv.xm_sqref.as_deref().unwrap_or(&dv.sqref);
435                    let new_sqref = delete_cells_from_sqref(sqref_to_delete, del_cells)?;
436                    if new_sqref.is_empty() {
437                        continue;
438                    }
439                    new_dvs.data_validation.push(build_x14_dv(dv, &new_sqref));
440                }
441                new_dvs.count = Some(new_dvs.data_validation.len() as i64);
442                if new_dvs.data_validation.is_empty() {
443                    continue;
444                }
445                let content = xml_to_string(&new_dvs)?;
446                x14.ext.push(crate::xml::common::XlsxExt {
447                    uri: Some(EXT_URI_DATA_VALIDATIONS.to_string()),
448                    xmlns_x14: Some(NAMESPACE_SPREADSHEET_X14.to_string()),
449                    xmlns_xm: None,
450                    content,
451                });
452            }
453        }
454        if x14.ext.is_empty() {
455            ws.ext_lst = None;
456        } else {
457            ws.ext_lst = Some(x14);
458        }
459        Ok(())
460    }
461}
462
463pub(crate) fn squash_sqref(cells: &[Vec<i32>]) -> Vec<String> {
464    if cells.is_empty() {
465        return Vec::new();
466    }
467    if cells.len() == 1 {
468        if let Ok(cell) = coordinates_to_cell_name(cells[0][0], cells[0][1], false) {
469            return vec![cell];
470        }
471        return Vec::new();
472    }
473    let mut refs = Vec::new();
474    let mut l = 0usize;
475    let mut r = 0usize;
476    for i in 1..cells.len() {
477        if cells[i][0] == cells[r][0] && cells[i][1] - cells[r][1] > 1 {
478            let mut coords = cells[l].clone();
479            coords.extend_from_slice(&cells[r]);
480            let ref_str = if l == r {
481                coordinates_to_cell_name(cells[l][0], cells[l][1], false).unwrap_or_default()
482            } else {
483                coordinates_to_range_ref(&coords, false).unwrap_or_default()
484            };
485            if !ref_str.is_empty() {
486                refs.push(ref_str);
487            }
488            l = i;
489            r = i;
490        } else {
491            r += 1;
492        }
493    }
494    let mut coords = cells[l].clone();
495    coords.extend_from_slice(&cells[r]);
496    let ref_str = if l == r {
497        coordinates_to_cell_name(cells[l][0], cells[l][1], false).unwrap_or_default()
498    } else {
499        coordinates_to_range_ref(&coords, false).unwrap_or_default()
500    };
501    if !ref_str.is_empty() {
502        refs.push(ref_str);
503    }
504    refs
505}
506
507pub(crate) fn delete_cells_from_sqref(
508    sqref: &str,
509    del_cells: &HashMap<i32, Vec<Vec<i32>>>,
510) -> Result<String> {
511    let mut col_cells = flat_sqref(sqref)?;
512    for (col, cells) in del_cells {
513        for cell in cells {
514            if let Some(col_vec) = col_cells.get_mut(col) {
515                let idx = in_coordinates(col_vec, cell);
516                if idx != -1 {
517                    col_vec.remove(idx as usize);
518                }
519            }
520        }
521    }
522    let mut apply_sqref = Vec::new();
523    for col in col_cells.values() {
524        apply_sqref.extend(squash_sqref(col));
525    }
526    Ok(apply_sqref.join(" "))
527}
528
529// ------------------------------------------------------------------
530// x14 extension list data validations
531// ------------------------------------------------------------------
532
533#[derive(Debug, Default, Clone, PartialEq, Serialize, Deserialize)]
534#[serde(rename = "dataValidations")]
535struct DecodeX14DataValidations {
536    #[serde(rename = "@count", default)]
537    count: Option<i64>,
538    #[serde(rename = "@disablePrompts", default)]
539    disable_prompts: Option<bool>,
540    #[serde(rename = "@xWindow", default)]
541    x_window: Option<i64>,
542    #[serde(rename = "@yWindow", default)]
543    y_window: Option<i64>,
544    #[serde(rename = "dataValidation", default)]
545    data_validation: Vec<DecodeX14DataValidation>,
546}
547
548#[derive(Debug, Default, Clone, PartialEq, Serialize, Deserialize)]
549#[serde(rename = "dataValidation")]
550struct DecodeX14DataValidation {
551    #[serde(rename = "@allowBlank", default)]
552    allow_blank: Option<bool>,
553    #[serde(rename = "@error", default)]
554    error: Option<String>,
555    #[serde(rename = "@errorStyle", default)]
556    error_style: Option<String>,
557    #[serde(rename = "@errorTitle", default)]
558    error_title: Option<String>,
559    #[serde(rename = "@operator", default)]
560    operator: Option<String>,
561    #[serde(rename = "@prompt", default)]
562    prompt: Option<String>,
563    #[serde(rename = "@promptTitle", default)]
564    prompt_title: Option<String>,
565    #[serde(rename = "@showDropDown", default)]
566    show_drop_down: Option<bool>,
567    #[serde(rename = "@showErrorMessage", default)]
568    show_error_message: Option<bool>,
569    #[serde(rename = "@showInputMessage", default)]
570    show_input_message: Option<bool>,
571    #[serde(rename = "@sqref", default)]
572    sqref: String,
573    #[serde(rename = "@type", default)]
574    r#type: Option<String>,
575    #[serde(rename = "formula1", default)]
576    formula1: Option<DecodeX14Formula>,
577    #[serde(rename = "formula2", default)]
578    formula2: Option<DecodeX14Formula>,
579    #[serde(rename = "sqref", default)]
580    xm_sqref: Option<String>,
581}
582
583#[derive(Debug, Default, Clone, PartialEq, Serialize, Deserialize)]
584struct DecodeX14Formula {
585    #[serde(rename = "f", default)]
586    f: Option<String>,
587}
588
589fn parse_x14_data_validations(content: &str) -> Result<DecodeX14DataValidations> {
590    let stripped = content.replace("<x14:", "<").replace("</x14:", "</");
591    Ok(xml_from_reader(stripped.as_bytes())?)
592}
593
594fn x14_dv_to_public(dv: &DecodeX14DataValidation) -> DataValidation {
595    DataValidation {
596        allow_blank: dv.allow_blank.unwrap_or(false),
597        error: dv.error.clone(),
598        error_style: dv.error_style.clone(),
599        error_title: dv.error_title.clone(),
600        operator: dv.operator.clone().unwrap_or_default(),
601        prompt: dv.prompt.clone(),
602        prompt_title: dv.prompt_title.clone(),
603        show_drop_down: dv.show_drop_down.unwrap_or(false),
604        show_error_message: dv.show_error_message.unwrap_or(false),
605        show_input_message: dv.show_input_message.unwrap_or(false),
606        sqref: dv.xm_sqref.clone().unwrap_or_else(|| dv.sqref.clone()),
607        r#type: dv.r#type.clone().unwrap_or_default(),
608        formula1: dv
609            .formula1
610            .as_ref()
611            .and_then(|f| f.f.as_ref())
612            .map(|s| unescape_data_validation_formula(s))
613            .unwrap_or_default(),
614        formula2: dv
615            .formula2
616            .as_ref()
617            .and_then(|f| f.f.as_ref())
618            .map(|s| unescape_data_validation_formula(s))
619            .unwrap_or_default(),
620    }
621}
622
623fn build_x14_dv(dv: &DecodeX14DataValidation, new_sqref: &str) -> XlsxX14DataValidation {
624    XlsxX14DataValidation {
625        allow_blank: dv.allow_blank,
626        error: dv.error.clone(),
627        error_style: dv.error_style.clone(),
628        error_title: dv.error_title.clone(),
629        operator: dv.operator.clone(),
630        prompt: dv.prompt.clone(),
631        prompt_title: dv.prompt_title.clone(),
632        show_drop_down: dv.show_drop_down,
633        show_error_message: dv.show_error_message,
634        show_input_message: dv.show_input_message,
635        sqref: dv.sqref.clone(),
636        r#type: dv.r#type.clone(),
637        formula1: dv
638            .formula1
639            .as_ref()
640            .and_then(|f| f.f.as_ref())
641            .map(|formula| XlsxInnerXml {
642                content: format!("<xm:f>{formula}</xm:f>"),
643            }),
644        formula2: dv
645            .formula2
646            .as_ref()
647            .and_then(|f| f.f.as_ref())
648            .map(|formula| XlsxInnerXml {
649                content: format!("<xm:f>{formula}</xm:f>"),
650            }),
651        xm_sqref: Some(new_sqref.to_string()),
652    }
653}
654
655// ------------------------------------------------------------------
656// Tests
657// ------------------------------------------------------------------
658
659#[cfg(test)]
660mod tests {
661    use super::*;
662    use crate::xml::common::XlsxExt;
663
664    #[test]
665    fn test_new_data_validation() {
666        let dv = new_data_validation(true);
667        assert!(dv.allow_blank);
668        assert!(!dv.show_error_message);
669        assert!(!dv.show_input_message);
670    }
671
672    #[test]
673    fn test_set_error_styles() {
674        let mut dv = new_data_validation(true);
675        dv.set_error(DataValidationErrorStyle::STOP, "title", "msg");
676        assert_eq!(dv.error_style, Some("stop".to_string()));
677        dv.set_error(DataValidationErrorStyle::WARNING, "title", "msg");
678        assert_eq!(dv.error_style, Some("warning".to_string()));
679        dv.set_error(DataValidationErrorStyle::INFORMATION, "title", "msg");
680        assert_eq!(dv.error_style, Some("information".to_string()));
681    }
682
683    #[test]
684    fn test_set_drop_list() {
685        let mut dv = new_data_validation(true);
686        dv.set_drop_list(&["1", "2", "3"]).unwrap();
687        assert_eq!(dv.formula1, "\"1,2,3\"");
688
689        dv.set_drop_list(&["=A1"]).unwrap();
690        assert_eq!(dv.formula1, "=A1");
691
692        dv.set_drop_list(&["A<", "B>", "C\"", "D\t", "E'", "F"])
693            .unwrap();
694        assert_eq!(dv.formula1, "\"A&lt;,B&gt;,C\"\",D\t,E',F\"");
695
696        let long = vec!["s"; 256];
697        assert!(
698            dv.set_drop_list(&long.iter().map(|s| *s).collect::<Vec<_>>())
699                .is_err()
700        );
701    }
702
703    #[test]
704    fn test_set_range() {
705        let mut dv = new_data_validation(true);
706        dv.set_range(
707            10.into(),
708            20.into(),
709            DataValidationType::WHOLE,
710            DataValidationOperator::BETWEEN,
711        )
712        .unwrap();
713        assert_eq!(dv.formula1, "10");
714        assert_eq!(dv.formula2, "20");
715        assert_eq!(dv.r#type, "whole");
716        assert_eq!(dv.operator, "between");
717
718        dv.set_range(
719            (-f32::MAX as f64).into(),
720            (f32::MAX as f64).into(),
721            DataValidationType::WHOLE,
722            DataValidationOperator::GREATER_THAN,
723        )
724        .unwrap();
725
726        assert!(
727            dv.set_range(
728                (-f64::MAX).into(),
729                20.into(),
730                DataValidationType::WHOLE,
731                DataValidationOperator::GREATER_THAN,
732            )
733            .is_err()
734        );
735        assert!(
736            dv.set_range(
737                DataValidationValue::None,
738                20.into(),
739                DataValidationType::WHOLE,
740                DataValidationOperator::BETWEEN,
741            )
742            .is_err()
743        );
744    }
745
746    #[test]
747    fn test_set_sqref() {
748        let mut dv = new_data_validation(true);
749        dv.set_sqref("A1:B2");
750        assert_eq!(dv.sqref, "A1:B2");
751        dv.set_sqref("A1:B2");
752        assert_eq!(dv.sqref, "A1:B2 A1:B2");
753    }
754
755    #[test]
756    fn test_add_get_delete_data_validation() {
757        let f = File::new_with_options(crate::options::Options::default());
758
759        let mut dv = new_data_validation(true);
760        dv.sqref = "A1:B2".to_string();
761        dv.set_range(
762            10.into(),
763            20.into(),
764            DataValidationType::WHOLE,
765            DataValidationOperator::BETWEEN,
766        )
767        .unwrap();
768        dv.set_error(DataValidationErrorStyle::STOP, "error title", "error body");
769        f.add_data_validation("Sheet1", &dv).unwrap();
770
771        let dvs = f.get_data_validations("Sheet1").unwrap();
772        assert_eq!(dvs.len(), 1);
773        assert_eq!(dvs[0].sqref, "A1:B2");
774        assert_eq!(dvs[0].r#type, "whole");
775
776        f.delete_data_validation("Sheet1", &["A1:B2"]).unwrap();
777        let dvs = f.get_data_validations("Sheet1").unwrap();
778        assert!(dvs.is_empty());
779    }
780
781    #[test]
782    fn test_delete_partial_sqref() {
783        let f = File::new_with_options(crate::options::Options::default());
784
785        let mut dv = new_data_validation(true);
786        dv.sqref = "C2:C5".to_string();
787        dv.set_range(
788            10.into(),
789            20.into(),
790            DataValidationType::WHOLE,
791            DataValidationOperator::BETWEEN,
792        )
793        .unwrap();
794        f.add_data_validation("Sheet1", &dv).unwrap();
795
796        f.delete_data_validation("Sheet1", &["C4"]).unwrap();
797        let dvs = f.get_data_validations("Sheet1").unwrap();
798        assert_eq!(dvs.len(), 1);
799        assert_eq!(dvs[0].sqref, "C2:C3 C5");
800    }
801
802    #[test]
803    fn test_delete_unordered_sqref() {
804        let f = File::new_with_options(crate::options::Options::default());
805        let path = f.get_sheet_xml_path("Sheet1").unwrap();
806        let mut ws = f.work_sheet_reader("Sheet1").unwrap();
807        ws.data_validations = Some(XlsxDataValidations {
808            count: Some(1),
809            data_validation: vec![XlsxDataValidation {
810                allow_blank: Some(true),
811                show_input_message: Some(true),
812                show_error_message: Some(true),
813                r#type: Some("whole".to_string()),
814                operator: Some("between".to_string()),
815                sqref: "A5:A10 A15:A20 A3:A4".to_string(),
816                formula1: Some(XlsxInnerXml {
817                    content: "1".to_string(),
818                }),
819                formula2: Some(XlsxInnerXml {
820                    content: "100".to_string(),
821                }),
822                ..Default::default()
823            }],
824            ..Default::default()
825        });
826        f.sheet.insert(path, ws);
827
828        f.delete_data_validation("Sheet1", &["A7"]).unwrap();
829        let dvs = f.get_data_validations("Sheet1").unwrap();
830        assert_eq!(dvs.len(), 1);
831        assert_eq!(dvs[0].sqref, "A3:A6 A8:A10 A15:A20");
832    }
833
834    #[test]
835    fn test_get_data_validations_ext_lst() {
836        let f = File::new_with_options(crate::options::Options::default());
837        let path = f.get_sheet_xml_path("Sheet1").unwrap();
838        let mut ws = f.work_sheet_reader("Sheet1").unwrap();
839        ws.ext_lst = Some(crate::xml::common::XlsxExtLst {
840            ext: vec![XlsxExt {
841                uri: Some(EXT_URI_DATA_VALIDATIONS.to_string()),
842                xmlns_x14: Some(NAMESPACE_SPREADSHEET_X14.to_string()),
843                xmlns_xm: Some(NAMESPACE_SPREADSHEET_EXCEL_2006_MAIN.to_string()),
844                content: r#"<x14:dataValidations xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main"><x14:dataValidation type="list" allowBlank="1"><x14:formula1><xm:f>Sheet1!$B$1:$B$5</xm:f></x14:formula1><xm:sqref>A7:B8</xm:sqref></x14:dataValidation></x14:dataValidations>"#.to_string(),
845            }],
846        });
847        f.sheet.insert(path, ws);
848
849        let dvs = f.get_data_validations("Sheet1").unwrap();
850        assert_eq!(dvs.len(), 1);
851        assert_eq!(dvs[0].allow_blank, true);
852        assert_eq!(dvs[0].r#type, "list");
853        assert_eq!(dvs[0].formula1, "Sheet1!$B$1:$B$5");
854        assert_eq!(dvs[0].sqref, "A7:B8");
855    }
856
857    #[test]
858    fn test_delete_data_validation_ext_lst() {
859        let f = File::new_with_options(crate::options::Options::default());
860        let path = f.get_sheet_xml_path("Sheet1").unwrap();
861        let mut ws = f.work_sheet_reader("Sheet1").unwrap();
862        ws.ext_lst = Some(crate::xml::common::XlsxExtLst {
863            ext: vec![XlsxExt {
864                uri: Some(EXT_URI_DATA_VALIDATIONS.to_string()),
865                xmlns_x14: Some(NAMESPACE_SPREADSHEET_X14.to_string()),
866                xmlns_xm: Some(NAMESPACE_SPREADSHEET_EXCEL_2006_MAIN.to_string()),
867                content: format!(
868                    r#"<x14:dataValidations xmlns:xm="{}"><x14:dataValidation allowBlank="true" showErrorMessage="true" showInputMessage="true" sqref="" type="list"><xm:sqref>A1:A2</xm:sqref><x14:formula1><xm:f>Sheet1!$A$2:$A$4</xm:f></x14:formula1></x14:dataValidation><x14:dataValidation allowBlank="true" showErrorMessage="true" showInputMessage="true" sqref="" type="list"><xm:sqref>B1:B2</xm:sqref><x14:formula1><xm:f>Sheet1!$B$2:$B$3</xm:f></x14:formula1></x14:dataValidation></x14:dataValidations>"#,
869                    NAMESPACE_SPREADSHEET_EXCEL_2006_MAIN
870                ),
871            }],
872        });
873        f.sheet.insert(path, ws);
874
875        f.delete_data_validation("Sheet1", &["A1:A2"]).unwrap();
876        let dvs = f.get_data_validations("Sheet1").unwrap();
877        assert_eq!(dvs.len(), 1);
878        assert_eq!(dvs[0].sqref, "B1:B2");
879
880        f.delete_data_validation("Sheet1", &["B1:B2"]).unwrap();
881        let dvs = f.get_data_validations("Sheet1").unwrap();
882        assert!(dvs.is_empty());
883    }
884
885    #[test]
886    fn test_squash_sqref() {
887        let cells = vec![vec![1, 1], vec![1, 2], vec![1, 4], vec![1, 5], vec![1, 6]];
888        let refs = squash_sqref(&cells);
889        assert_eq!(refs, vec!["A1:A2", "A4:A6"]);
890    }
891
892    #[test]
893    fn test_data_validation_round_trip() {
894        use std::fs;
895
896        let tmp = std::env::temp_dir().join("excelize_rust_data_validation.xlsx");
897        let _ = fs::remove_file(&tmp);
898
899        let mut f = File::new_with_options(crate::options::Options::default());
900        let mut dv = new_data_validation(true);
901        dv.sqref = "A1:B2".to_string();
902        dv.set_range(
903            10.into(),
904            20.into(),
905            DataValidationType::WHOLE,
906            DataValidationOperator::BETWEEN,
907        )
908        .unwrap();
909        dv.set_error(DataValidationErrorStyle::STOP, "error title", "error body");
910        f.add_data_validation("Sheet1", &dv).unwrap();
911
912        let mut dv2 = new_data_validation(true);
913        dv2.sqref = "A3:B4".to_string();
914        dv2.set_drop_list(&["1", "2", "3"]).unwrap();
915        dv2.set_input("input title", "input body");
916        f.add_data_validation("Sheet1", &dv2).unwrap();
917
918        f.save_as(tmp.to_str().unwrap()).unwrap();
919
920        let f2 =
921            File::open_file(tmp.to_str().unwrap(), crate::options::Options::default()).unwrap();
922        let dvs = f2.get_data_validations("Sheet1").unwrap();
923        assert_eq!(dvs.len(), 2);
924        assert_eq!(dvs[0].sqref, "A1:B2");
925        assert_eq!(dvs[1].sqref, "A3:B4");
926
927        let _ = fs::remove_file(&tmp);
928    }
929}
excelize_rs/data_validation.rs

excelize_rs/
data_validation.rs
