Skip to main content

sheetkit_core/
conditional.rs

1//! Conditional formatting builder and utilities.
2//!
3//! Provides a high-level API for adding, querying, and removing conditional
4//! formatting rules on worksheet cells. Supports cell-is comparisons,
5//! formula-based rules, color scales, data bars, duplicate/unique values,
6//! top/bottom N, above/below average, and text-based rules.
7
8use crate::error::{Error, Result};
9use crate::style::{
10    BorderLineStyle, BorderSideStyle, BorderStyle, FillStyle, FontStyle, NumFmtStyle, PatternType,
11    StyleColor,
12};
13use sheetkit_xml::styles::{Dxf, Dxfs, NumFmt, StyleSheet};
14use sheetkit_xml::worksheet::{
15    CfColor, CfColorScale, CfDataBar, CfRule, CfVo, ConditionalFormatting, WorksheetXml,
16};
17
18/// Comparison operator for CellIs conditional formatting rules.
19#[derive(Debug, Clone, PartialEq)]
20pub enum CfOperator {
21    LessThan,
22    LessThanOrEqual,
23    Equal,
24    NotEqual,
25    GreaterThanOrEqual,
26    GreaterThan,
27    Between,
28    NotBetween,
29}
30
31impl CfOperator {
32    /// Convert to the XML attribute string.
33    pub fn as_str(&self) -> &str {
34        match self {
35            CfOperator::LessThan => "lessThan",
36            CfOperator::LessThanOrEqual => "lessThanOrEqual",
37            CfOperator::Equal => "equal",
38            CfOperator::NotEqual => "notEqual",
39            CfOperator::GreaterThanOrEqual => "greaterThanOrEqual",
40            CfOperator::GreaterThan => "greaterThan",
41            CfOperator::Between => "between",
42            CfOperator::NotBetween => "notBetween",
43        }
44    }
45
46    /// Parse from the XML attribute string.
47    pub fn parse(s: &str) -> Option<Self> {
48        match s {
49            "lessThan" => Some(CfOperator::LessThan),
50            "lessThanOrEqual" => Some(CfOperator::LessThanOrEqual),
51            "equal" => Some(CfOperator::Equal),
52            "notEqual" => Some(CfOperator::NotEqual),
53            "greaterThanOrEqual" => Some(CfOperator::GreaterThanOrEqual),
54            "greaterThan" => Some(CfOperator::GreaterThan),
55            "between" => Some(CfOperator::Between),
56            "notBetween" => Some(CfOperator::NotBetween),
57            _ => None,
58        }
59    }
60}
61
62/// Value type for color scale and data bar thresholds.
63#[derive(Debug, Clone, PartialEq)]
64pub enum CfValueType {
65    Num,
66    Percent,
67    Min,
68    Max,
69    Percentile,
70    Formula,
71}
72
73impl CfValueType {
74    /// Convert to the XML attribute string.
75    pub fn as_str(&self) -> &str {
76        match self {
77            CfValueType::Num => "num",
78            CfValueType::Percent => "percent",
79            CfValueType::Min => "min",
80            CfValueType::Max => "max",
81            CfValueType::Percentile => "percentile",
82            CfValueType::Formula => "formula",
83        }
84    }
85
86    /// Parse from the XML attribute string.
87    pub fn parse(s: &str) -> Option<Self> {
88        match s {
89            "num" => Some(CfValueType::Num),
90            "percent" => Some(CfValueType::Percent),
91            "min" => Some(CfValueType::Min),
92            "max" => Some(CfValueType::Max),
93            "percentile" => Some(CfValueType::Percentile),
94            "formula" => Some(CfValueType::Formula),
95            _ => None,
96        }
97    }
98}
99
100/// The type of conditional formatting rule.
101#[derive(Debug, Clone, PartialEq)]
102pub enum ConditionalFormatType {
103    /// Cell value comparison (e.g., greater than, between).
104    CellIs {
105        operator: CfOperator,
106        formula: String,
107        /// Optional second formula for Between/NotBetween.
108        formula2: Option<String>,
109    },
110    /// Formula-based rule.
111    Expression { formula: String },
112    /// Color scale (2 or 3 color gradient).
113    ColorScale {
114        min_type: CfValueType,
115        min_value: Option<String>,
116        min_color: String,
117        mid_type: Option<CfValueType>,
118        mid_value: Option<String>,
119        mid_color: Option<String>,
120        max_type: CfValueType,
121        max_value: Option<String>,
122        max_color: String,
123    },
124    /// Data bar visualization.
125    DataBar {
126        min_type: CfValueType,
127        min_value: Option<String>,
128        max_type: CfValueType,
129        max_value: Option<String>,
130        color: String,
131        show_value: bool,
132    },
133    /// Duplicate values.
134    DuplicateValues,
135    /// Unique values.
136    UniqueValues,
137    /// Top N values.
138    Top10 { rank: u32, percent: bool },
139    /// Bottom N values.
140    Bottom10 { rank: u32, percent: bool },
141    /// Above or below average.
142    AboveAverage { above: bool, equal_average: bool },
143    /// Cells that contain blanks.
144    ContainsBlanks,
145    /// Cells that do not contain blanks.
146    NotContainsBlanks,
147    /// Cells that contain errors.
148    ContainsErrors,
149    /// Cells that do not contain errors.
150    NotContainsErrors,
151    /// Cells containing specific text.
152    ContainsText { text: String },
153    /// Cells not containing specific text.
154    NotContainsText { text: String },
155    /// Cells beginning with specific text.
156    BeginsWith { text: String },
157    /// Cells ending with specific text.
158    EndsWith { text: String },
159}
160
161/// Style applied by a conditional formatting rule.
162///
163/// Uses differential formatting (DXF): only the properties that differ from
164/// the cell's base style are specified.
165#[derive(Debug, Clone, Default)]
166pub struct ConditionalStyle {
167    pub font: Option<FontStyle>,
168    pub fill: Option<FillStyle>,
169    pub border: Option<BorderStyle>,
170    pub num_fmt: Option<NumFmtStyle>,
171}
172
173/// A single conditional formatting rule with its style.
174#[derive(Debug, Clone)]
175pub struct ConditionalFormatRule {
176    /// The type of conditional formatting rule.
177    pub rule_type: ConditionalFormatType,
178    /// The differential style to apply when the rule matches.
179    pub format: Option<ConditionalStyle>,
180    /// Rule priority (lower numbers are evaluated first).
181    pub priority: Option<u32>,
182    /// If true, no rules with lower priority are applied when this rule matches.
183    pub stop_if_true: bool,
184}
185
186/// Convert a `ConditionalStyle` to an XML `Dxf` and add it to the stylesheet.
187/// Returns the DXF index.
188fn add_dxf(stylesheet: &mut StyleSheet, style: &ConditionalStyle) -> u32 {
189    let mut dxf = conditional_style_to_dxf(style);
190
191    if let (Some(NumFmtStyle::Custom(format_code)), Some(num_fmt)) =
192        (&style.num_fmt, &mut dxf.num_fmt)
193    {
194        num_fmt.num_fmt_id = custom_num_fmt_id(stylesheet, format_code);
195    }
196
197    let dxfs = stylesheet.dxfs.get_or_insert_with(|| Dxfs {
198        count: Some(0),
199        dxfs: Vec::new(),
200    });
201
202    let id = dxfs.dxfs.len() as u32;
203    dxfs.dxfs.push(dxf);
204    dxfs.count = Some(dxfs.dxfs.len() as u32);
205    id
206}
207
208/// Return a non-conflicting custom number format ID for a DXF.
209fn custom_num_fmt_id(stylesheet: &StyleSheet, format_code: &str) -> u32 {
210    let cell_num_fmts = stylesheet
211        .num_fmts
212        .iter()
213        .flat_map(|num_fmts| num_fmts.num_fmts.iter());
214    let dxf_num_fmts = stylesheet
215        .dxfs
216        .iter()
217        .flat_map(|dxfs| dxfs.dxfs.iter())
218        .filter_map(|dxf| dxf.num_fmt.as_ref());
219
220    let mut next_id = 164;
221    for num_fmt in cell_num_fmts.chain(dxf_num_fmts) {
222        if num_fmt.format_code == format_code {
223            return num_fmt.num_fmt_id;
224        }
225        next_id = next_id.max(num_fmt.num_fmt_id.saturating_add(1));
226    }
227    next_id
228}
229
230/// Convert a `ConditionalStyle` to the XML `Dxf` struct.
231fn conditional_style_to_dxf(style: &ConditionalStyle) -> Dxf {
232    use sheetkit_xml::styles::{
233        BoolVal, Border, BorderSide, Fill, Font, FontName, FontSize, PatternFill, Underline,
234    };
235
236    let font = style.font.as_ref().map(|f| Font {
237        b: if f.bold {
238            Some(BoolVal { val: None })
239        } else {
240            None
241        },
242        i: if f.italic {
243            Some(BoolVal { val: None })
244        } else {
245            None
246        },
247        strike: if f.strikethrough {
248            Some(BoolVal { val: None })
249        } else {
250            None
251        },
252        u: if f.underline {
253            Some(Underline { val: None })
254        } else {
255            None
256        },
257        sz: f.size.map(|val| FontSize { val }),
258        color: f.color.as_ref().map(style_color_to_xml_color),
259        name: f.name.as_ref().map(|val| FontName { val: val.clone() }),
260        family: None,
261        scheme: None,
262    });
263
264    let fill = style.fill.as_ref().map(|f| Fill {
265        pattern_fill: Some(PatternFill {
266            pattern_type: Some(pattern_type_str(&f.pattern).to_string()),
267            fg_color: f.fg_color.as_ref().map(style_color_to_xml_color),
268            bg_color: f.bg_color.as_ref().map(style_color_to_xml_color),
269        }),
270        gradient_fill: None,
271    });
272
273    let border = style.border.as_ref().map(|b| {
274        let side_to_xml = |s: &BorderSideStyle| BorderSide {
275            style: Some(border_line_str(&s.style).to_string()),
276            color: s.color.as_ref().map(style_color_to_xml_color),
277        };
278        Border {
279            diagonal_up: None,
280            diagonal_down: None,
281            left: b.left.as_ref().map(side_to_xml),
282            right: b.right.as_ref().map(side_to_xml),
283            top: b.top.as_ref().map(side_to_xml),
284            bottom: b.bottom.as_ref().map(side_to_xml),
285            diagonal: b.diagonal.as_ref().map(side_to_xml),
286        }
287    });
288
289    let num_fmt = style.num_fmt.as_ref().map(|nf| match nf {
290        NumFmtStyle::Builtin(id) => NumFmt {
291            num_fmt_id: *id,
292            format_code: String::new(),
293        },
294        NumFmtStyle::Custom(code) => NumFmt {
295            num_fmt_id: 164,
296            format_code: code.clone(),
297        },
298    });
299
300    Dxf {
301        font,
302        num_fmt,
303        fill,
304        border,
305    }
306}
307
308/// Convert a `StyleColor` to an XML `Color`.
309fn style_color_to_xml_color(color: &StyleColor) -> sheetkit_xml::styles::Color {
310    sheetkit_xml::styles::Color {
311        auto: None,
312        indexed: match color {
313            StyleColor::Indexed(i) => Some(*i),
314            _ => None,
315        },
316        rgb: match color {
317            StyleColor::Rgb(rgb) => Some(rgb.clone()),
318            _ => None,
319        },
320        theme: match color {
321            StyleColor::Theme(t) => Some(*t),
322            _ => None,
323        },
324        tint: None,
325    }
326}
327
328/// Get the string for a PatternType.
329fn pattern_type_str(pattern: &PatternType) -> &str {
330    match pattern {
331        PatternType::None => "none",
332        PatternType::Solid => "solid",
333        PatternType::Gray125 => "gray125",
334        PatternType::DarkGray => "darkGray",
335        PatternType::MediumGray => "mediumGray",
336        PatternType::LightGray => "lightGray",
337    }
338}
339
340/// Get the string for a BorderLineStyle.
341fn border_line_str(style: &BorderLineStyle) -> &str {
342    match style {
343        BorderLineStyle::Thin => "thin",
344        BorderLineStyle::Medium => "medium",
345        BorderLineStyle::Thick => "thick",
346        BorderLineStyle::Dashed => "dashed",
347        BorderLineStyle::Dotted => "dotted",
348        BorderLineStyle::Double => "double",
349        BorderLineStyle::Hair => "hair",
350        BorderLineStyle::MediumDashed => "mediumDashed",
351        BorderLineStyle::DashDot => "dashDot",
352        BorderLineStyle::MediumDashDot => "mediumDashDot",
353        BorderLineStyle::DashDotDot => "dashDotDot",
354        BorderLineStyle::MediumDashDotDot => "mediumDashDotDot",
355        BorderLineStyle::SlantDashDot => "slantDashDot",
356    }
357}
358
359/// Convert an XML `Dxf` back to a `ConditionalStyle`.
360fn dxf_to_conditional_style(dxf: &Dxf) -> ConditionalStyle {
361    let font = dxf.font.as_ref().map(|f| FontStyle {
362        name: f.name.as_ref().map(|n| n.val.clone()),
363        size: f.sz.as_ref().map(|s| s.val),
364        bold: f.b.is_some(),
365        italic: f.i.is_some(),
366        underline: f.u.is_some(),
367        strikethrough: f.strike.is_some(),
368        color: f.color.as_ref().and_then(xml_color_to_style_color),
369    });
370
371    let fill = dxf.fill.as_ref().and_then(|f| {
372        let pf = f.pattern_fill.as_ref()?;
373        Some(FillStyle {
374            pattern: pf
375                .pattern_type
376                .as_ref()
377                .map(|s| parse_pattern_type(s))
378                .unwrap_or(PatternType::None),
379            fg_color: pf.fg_color.as_ref().and_then(xml_color_to_style_color),
380            bg_color: pf.bg_color.as_ref().and_then(xml_color_to_style_color),
381            gradient: None,
382        })
383    });
384
385    let border = dxf.border.as_ref().map(|b| {
386        let side = |s: &sheetkit_xml::styles::BorderSide| -> Option<BorderSideStyle> {
387            let style_str = s.style.as_ref()?;
388            let line_style = parse_border_line_style(style_str)?;
389            Some(BorderSideStyle {
390                style: line_style,
391                color: s.color.as_ref().and_then(xml_color_to_style_color),
392            })
393        };
394        BorderStyle {
395            left: b.left.as_ref().and_then(side),
396            right: b.right.as_ref().and_then(side),
397            top: b.top.as_ref().and_then(side),
398            bottom: b.bottom.as_ref().and_then(side),
399            diagonal: b.diagonal.as_ref().and_then(side),
400        }
401    });
402
403    let num_fmt = dxf.num_fmt.as_ref().map(|nf| {
404        if nf.format_code.is_empty() {
405            NumFmtStyle::Builtin(nf.num_fmt_id)
406        } else {
407            NumFmtStyle::Custom(nf.format_code.clone())
408        }
409    });
410
411    ConditionalStyle {
412        font,
413        fill,
414        border,
415        num_fmt,
416    }
417}
418
419/// Convert an XML Color to a StyleColor.
420fn xml_color_to_style_color(color: &sheetkit_xml::styles::Color) -> Option<StyleColor> {
421    if let Some(ref rgb) = color.rgb {
422        Some(StyleColor::Rgb(rgb.clone()))
423    } else if let Some(theme) = color.theme {
424        Some(StyleColor::Theme(theme))
425    } else {
426        color.indexed.map(StyleColor::Indexed)
427    }
428}
429
430/// Parse a pattern type string.
431fn parse_pattern_type(s: &str) -> PatternType {
432    match s {
433        "none" => PatternType::None,
434        "solid" => PatternType::Solid,
435        "gray125" => PatternType::Gray125,
436        "darkGray" => PatternType::DarkGray,
437        "mediumGray" => PatternType::MediumGray,
438        "lightGray" => PatternType::LightGray,
439        _ => PatternType::None,
440    }
441}
442
443/// Parse a border line style string.
444fn parse_border_line_style(s: &str) -> Option<BorderLineStyle> {
445    match s {
446        "thin" => Some(BorderLineStyle::Thin),
447        "medium" => Some(BorderLineStyle::Medium),
448        "thick" => Some(BorderLineStyle::Thick),
449        "dashed" => Some(BorderLineStyle::Dashed),
450        "dotted" => Some(BorderLineStyle::Dotted),
451        "double" => Some(BorderLineStyle::Double),
452        "hair" => Some(BorderLineStyle::Hair),
453        "mediumDashed" => Some(BorderLineStyle::MediumDashed),
454        "dashDot" => Some(BorderLineStyle::DashDot),
455        "mediumDashDot" => Some(BorderLineStyle::MediumDashDot),
456        "dashDotDot" => Some(BorderLineStyle::DashDotDot),
457        "mediumDashDotDot" => Some(BorderLineStyle::MediumDashDotDot),
458        "slantDashDot" => Some(BorderLineStyle::SlantDashDot),
459        _ => None,
460    }
461}
462
463/// Largest priority allowed by the schema (`xsd:int`).
464const MAX_CF_PRIORITY: u32 = i32::MAX as u32;
465
466/// Compute the next priority across all existing conditional formatting rules.
467///
468/// Returns one greater than the current maximum so new rules keep the lowest
469/// precedence; gaps are not reused because that would reorder evaluation.
470/// The result is `None` when it would exceed `MAX_CF_PRIORITY`.
471fn next_priority(ws: &WorksheetXml) -> Option<u32> {
472    let max = ws
473        .conditional_formatting
474        .iter()
475        .flat_map(|cf| cf.cf_rules.iter())
476        .map(|r| r.priority)
477        .max()
478        .unwrap_or(0);
479    max.checked_add(1).filter(|p| *p <= MAX_CF_PRIORITY)
480}
481
482/// Convert a `ConditionalFormatRule` to an XML `CfRule`, adding a DXF to the
483/// stylesheet if needed. Returns the CfRule ready for insertion.
484fn rule_to_xml(
485    rule: &ConditionalFormatRule,
486    stylesheet: &mut StyleSheet,
487    priority: u32,
488    first_cell: &str,
489) -> CfRule {
490    let dxf_id = rule.format.as_ref().map(|style| add_dxf(stylesheet, style));
491
492    let stop_if_true = if rule.stop_if_true { Some(true) } else { None };
493
494    match &rule.rule_type {
495        ConditionalFormatType::CellIs {
496            operator,
497            formula,
498            formula2,
499        } => {
500            let mut formulas = vec![formula.clone()];
501            if let Some(f2) = formula2 {
502                formulas.push(f2.clone());
503            }
504            CfRule {
505                rule_type: "cellIs".to_string(),
506                dxf_id,
507                priority,
508                operator: Some(operator.as_str().to_string()),
509                text: None,
510                stop_if_true,
511                above_average: None,
512                equal_average: None,
513                percent: None,
514                rank: None,
515                bottom: None,
516                formulas,
517                color_scale: None,
518                data_bar: None,
519                icon_set: None,
520            }
521        }
522        ConditionalFormatType::Expression { formula } => CfRule {
523            rule_type: "expression".to_string(),
524            dxf_id,
525            priority,
526            operator: None,
527            text: None,
528            stop_if_true,
529            above_average: None,
530            equal_average: None,
531            percent: None,
532            rank: None,
533            bottom: None,
534            formulas: vec![formula.clone()],
535            color_scale: None,
536            data_bar: None,
537            icon_set: None,
538        },
539        ConditionalFormatType::ColorScale {
540            min_type,
541            min_value,
542            min_color,
543            mid_type,
544            mid_value,
545            mid_color,
546            max_type,
547            max_value,
548            max_color,
549        } => {
550            let mut cfvos = vec![CfVo {
551                value_type: min_type.as_str().to_string(),
552                val: min_value.clone(),
553            }];
554            let mut colors = vec![CfColor {
555                rgb: Some(min_color.clone()),
556                theme: None,
557                tint: None,
558            }];
559
560            if let Some(mt) = mid_type {
561                cfvos.push(CfVo {
562                    value_type: mt.as_str().to_string(),
563                    val: mid_value.clone(),
564                });
565                colors.push(CfColor {
566                    rgb: mid_color.clone(),
567                    theme: None,
568                    tint: None,
569                });
570            }
571
572            cfvos.push(CfVo {
573                value_type: max_type.as_str().to_string(),
574                val: max_value.clone(),
575            });
576            colors.push(CfColor {
577                rgb: Some(max_color.clone()),
578                theme: None,
579                tint: None,
580            });
581
582            CfRule {
583                rule_type: "colorScale".to_string(),
584                dxf_id: None, // color scales do not use DXF
585                priority,
586                operator: None,
587                text: None,
588                stop_if_true,
589                above_average: None,
590                equal_average: None,
591                percent: None,
592                rank: None,
593                bottom: None,
594                formulas: vec![],
595                color_scale: Some(CfColorScale { cfvos, colors }),
596                data_bar: None,
597                icon_set: None,
598            }
599        }
600        ConditionalFormatType::DataBar {
601            min_type,
602            min_value,
603            max_type,
604            max_value,
605            color,
606            show_value,
607        } => {
608            let cfvos = vec![
609                CfVo {
610                    value_type: min_type.as_str().to_string(),
611                    val: min_value.clone(),
612                },
613                CfVo {
614                    value_type: max_type.as_str().to_string(),
615                    val: max_value.clone(),
616                },
617            ];
618            CfRule {
619                rule_type: "dataBar".to_string(),
620                dxf_id: None, // data bars do not use DXF
621                priority,
622                operator: None,
623                text: None,
624                stop_if_true,
625                above_average: None,
626                equal_average: None,
627                percent: None,
628                rank: None,
629                bottom: None,
630                formulas: vec![],
631                color_scale: None,
632                data_bar: Some(CfDataBar {
633                    show_value: if *show_value { None } else { Some(false) },
634                    cfvos,
635                    color: Some(CfColor {
636                        rgb: Some(color.clone()),
637                        theme: None,
638                        tint: None,
639                    }),
640                }),
641                icon_set: None,
642            }
643        }
644        ConditionalFormatType::DuplicateValues => CfRule {
645            rule_type: "duplicateValues".to_string(),
646            dxf_id,
647            priority,
648            operator: None,
649            text: None,
650            stop_if_true,
651            above_average: None,
652            equal_average: None,
653            percent: None,
654            rank: None,
655            bottom: None,
656            formulas: vec![],
657            color_scale: None,
658            data_bar: None,
659            icon_set: None,
660        },
661        ConditionalFormatType::UniqueValues => CfRule {
662            rule_type: "uniqueValues".to_string(),
663            dxf_id,
664            priority,
665            operator: None,
666            text: None,
667            stop_if_true,
668            above_average: None,
669            equal_average: None,
670            percent: None,
671            rank: None,
672            bottom: None,
673            formulas: vec![],
674            color_scale: None,
675            data_bar: None,
676            icon_set: None,
677        },
678        ConditionalFormatType::Top10 { rank, percent } => CfRule {
679            rule_type: "top10".to_string(),
680            dxf_id,
681            priority,
682            operator: None,
683            text: None,
684            stop_if_true,
685            above_average: None,
686            equal_average: None,
687            percent: if *percent { Some(true) } else { None },
688            rank: Some(*rank),
689            bottom: None,
690            formulas: vec![],
691            color_scale: None,
692            data_bar: None,
693            icon_set: None,
694        },
695        ConditionalFormatType::Bottom10 { rank, percent } => CfRule {
696            rule_type: "top10".to_string(),
697            dxf_id,
698            priority,
699            operator: None,
700            text: None,
701            stop_if_true,
702            above_average: None,
703            equal_average: None,
704            percent: if *percent { Some(true) } else { None },
705            rank: Some(*rank),
706            bottom: Some(true),
707            formulas: vec![],
708            color_scale: None,
709            data_bar: None,
710            icon_set: None,
711        },
712        ConditionalFormatType::AboveAverage {
713            above,
714            equal_average,
715        } => CfRule {
716            rule_type: "aboveAverage".to_string(),
717            dxf_id,
718            priority,
719            operator: None,
720            text: None,
721            stop_if_true,
722            above_average: if *above { None } else { Some(false) },
723            equal_average: if *equal_average { Some(true) } else { None },
724            percent: None,
725            rank: None,
726            bottom: None,
727            formulas: vec![],
728            color_scale: None,
729            data_bar: None,
730            icon_set: None,
731        },
732        ConditionalFormatType::ContainsBlanks => CfRule {
733            rule_type: "containsBlanks".to_string(),
734            dxf_id,
735            priority,
736            operator: None,
737            text: None,
738            stop_if_true,
739            above_average: None,
740            equal_average: None,
741            percent: None,
742            rank: None,
743            bottom: None,
744            formulas: vec![],
745            color_scale: None,
746            data_bar: None,
747            icon_set: None,
748        },
749        ConditionalFormatType::NotContainsBlanks => CfRule {
750            rule_type: "notContainsBlanks".to_string(),
751            dxf_id,
752            priority,
753            operator: None,
754            text: None,
755            stop_if_true,
756            above_average: None,
757            equal_average: None,
758            percent: None,
759            rank: None,
760            bottom: None,
761            formulas: vec![],
762            color_scale: None,
763            data_bar: None,
764            icon_set: None,
765        },
766        ConditionalFormatType::ContainsErrors => CfRule {
767            rule_type: "containsErrors".to_string(),
768            dxf_id,
769            priority,
770            operator: None,
771            text: None,
772            stop_if_true,
773            above_average: None,
774            equal_average: None,
775            percent: None,
776            rank: None,
777            bottom: None,
778            formulas: vec![],
779            color_scale: None,
780            data_bar: None,
781            icon_set: None,
782        },
783        ConditionalFormatType::NotContainsErrors => CfRule {
784            rule_type: "notContainsErrors".to_string(),
785            dxf_id,
786            priority,
787            operator: None,
788            text: None,
789            stop_if_true,
790            above_average: None,
791            equal_average: None,
792            percent: None,
793            rank: None,
794            bottom: None,
795            formulas: vec![],
796            color_scale: None,
797            data_bar: None,
798            icon_set: None,
799        },
800        ConditionalFormatType::ContainsText { text } => CfRule {
801            rule_type: "containsText".to_string(),
802            dxf_id,
803            priority,
804            operator: Some("containsText".to_string()),
805            text: Some(text.clone()),
806            stop_if_true,
807            above_average: None,
808            equal_average: None,
809            percent: None,
810            rank: None,
811            bottom: None,
812            formulas: vec![format!(
813                "NOT(ISERROR(SEARCH(\"{}\",{})))",
814                escape_formula_string(text),
815                first_cell
816            )],
817            color_scale: None,
818            data_bar: None,
819            icon_set: None,
820        },
821        ConditionalFormatType::NotContainsText { text } => CfRule {
822            rule_type: "notContainsText".to_string(),
823            dxf_id,
824            priority,
825            operator: Some("notContains".to_string()),
826            text: Some(text.clone()),
827            stop_if_true,
828            above_average: None,
829            equal_average: None,
830            percent: None,
831            rank: None,
832            bottom: None,
833            formulas: vec![format!(
834                "ISERROR(SEARCH(\"{}\",{}))",
835                escape_formula_string(text),
836                first_cell
837            )],
838            color_scale: None,
839            data_bar: None,
840            icon_set: None,
841        },
842        ConditionalFormatType::BeginsWith { text } => CfRule {
843            rule_type: "beginsWith".to_string(),
844            dxf_id,
845            priority,
846            operator: Some("beginsWith".to_string()),
847            text: Some(text.clone()),
848            stop_if_true,
849            above_average: None,
850            equal_average: None,
851            percent: None,
852            rank: None,
853            bottom: None,
854            formulas: vec![format!(
855                "LEFT({},LEN(\"{}\"))=\"{}\"",
856                first_cell,
857                escape_formula_string(text),
858                escape_formula_string(text)
859            )],
860            color_scale: None,
861            data_bar: None,
862            icon_set: None,
863        },
864        ConditionalFormatType::EndsWith { text } => CfRule {
865            rule_type: "endsWith".to_string(),
866            dxf_id,
867            priority,
868            operator: Some("endsWith".to_string()),
869            text: Some(text.clone()),
870            stop_if_true,
871            above_average: None,
872            equal_average: None,
873            percent: None,
874            rank: None,
875            bottom: None,
876            formulas: vec![format!(
877                "RIGHT({},LEN(\"{}\"))=\"{}\"",
878                first_cell,
879                escape_formula_string(text),
880                escape_formula_string(text)
881            )],
882            color_scale: None,
883            data_bar: None,
884            icon_set: None,
885        },
886    }
887}
888
889fn escape_formula_string(value: &str) -> String {
890    value.replace('"', "\"\"")
891}
892
893fn first_cell_reference(sqref: &str) -> &str {
894    sqref
895        .split_whitespace()
896        .next()
897        .unwrap_or(sqref)
898        .split(':')
899        .next()
900        .unwrap_or(sqref)
901}
902
903/// Convert an XML `CfRule` to a `ConditionalFormatRule`, looking up the DXF
904/// style from the stylesheet.
905fn xml_to_rule(cf_rule: &CfRule, stylesheet: &StyleSheet) -> ConditionalFormatRule {
906    let format = cf_rule
907        .dxf_id
908        .and_then(|id| {
909            stylesheet
910                .dxfs
911                .as_ref()
912                .and_then(|dxfs| dxfs.dxfs.get(id as usize))
913        })
914        .map(dxf_to_conditional_style);
915
916    let rule_type = match cf_rule.rule_type.as_str() {
917        "cellIs" => {
918            let operator = cf_rule
919                .operator
920                .as_deref()
921                .and_then(CfOperator::parse)
922                .unwrap_or(CfOperator::Equal);
923            let formula = cf_rule.formulas.first().cloned().unwrap_or_default();
924            let formula2 = cf_rule.formulas.get(1).cloned();
925            ConditionalFormatType::CellIs {
926                operator,
927                formula,
928                formula2,
929            }
930        }
931        "expression" => {
932            let formula = cf_rule.formulas.first().cloned().unwrap_or_default();
933            ConditionalFormatType::Expression { formula }
934        }
935        "colorScale" => {
936            if let Some(cs) = &cf_rule.color_scale {
937                let get_cfvo = |idx: usize| -> (CfValueType, Option<String>) {
938                    cs.cfvos
939                        .get(idx)
940                        .map(|v| {
941                            (
942                                CfValueType::parse(&v.value_type).unwrap_or(CfValueType::Min),
943                                v.val.clone(),
944                            )
945                        })
946                        .unwrap_or((CfValueType::Min, None))
947                };
948                let get_color = |idx: usize| -> Option<String> {
949                    cs.colors.get(idx).and_then(|c| c.rgb.clone())
950                };
951
952                let (min_type, min_value) = get_cfvo(0);
953                let min_color = get_color(0).unwrap_or_default();
954
955                let is_three_color = cs.cfvos.len() >= 3;
956                let (mid_type, mid_value, mid_color) = if is_three_color {
957                    let (mt, mv) = get_cfvo(1);
958                    (Some(mt), mv, get_color(1))
959                } else {
960                    (None, None, None)
961                };
962
963                let max_idx = if is_three_color { 2 } else { 1 };
964                let (max_type, max_value) = get_cfvo(max_idx);
965                let max_color = get_color(max_idx).unwrap_or_default();
966
967                ConditionalFormatType::ColorScale {
968                    min_type,
969                    min_value,
970                    min_color,
971                    mid_type,
972                    mid_value,
973                    mid_color,
974                    max_type,
975                    max_value,
976                    max_color,
977                }
978            } else {
979                // Fallback for malformed data
980                ConditionalFormatType::ColorScale {
981                    min_type: CfValueType::Min,
982                    min_value: None,
983                    min_color: String::new(),
984                    mid_type: None,
985                    mid_value: None,
986                    mid_color: None,
987                    max_type: CfValueType::Max,
988                    max_value: None,
989                    max_color: String::new(),
990                }
991            }
992        }
993        "dataBar" => {
994            if let Some(db) = &cf_rule.data_bar {
995                let get_cfvo = |idx: usize| -> (CfValueType, Option<String>) {
996                    db.cfvos
997                        .get(idx)
998                        .map(|v| {
999                            (
1000                                CfValueType::parse(&v.value_type).unwrap_or(CfValueType::Min),
1001                                v.val.clone(),
1002                            )
1003                        })
1004                        .unwrap_or((CfValueType::Min, None))
1005                };
1006                let (min_type, min_value) = get_cfvo(0);
1007                let (max_type, max_value) = get_cfvo(1);
1008                let color = db
1009                    .color
1010                    .as_ref()
1011                    .and_then(|c| c.rgb.clone())
1012                    .unwrap_or_default();
1013                let show_value = db.show_value.unwrap_or(true);
1014
1015                ConditionalFormatType::DataBar {
1016                    min_type,
1017                    min_value,
1018                    max_type,
1019                    max_value,
1020                    color,
1021                    show_value,
1022                }
1023            } else {
1024                ConditionalFormatType::DataBar {
1025                    min_type: CfValueType::Min,
1026                    min_value: None,
1027                    max_type: CfValueType::Max,
1028                    max_value: None,
1029                    color: String::new(),
1030                    show_value: true,
1031                }
1032            }
1033        }
1034        "duplicateValues" => ConditionalFormatType::DuplicateValues,
1035        "uniqueValues" => ConditionalFormatType::UniqueValues,
1036        "top10" => {
1037            let rank = cf_rule.rank.unwrap_or(10);
1038            let percent = cf_rule.percent.unwrap_or(false);
1039            if cf_rule.bottom == Some(true) {
1040                ConditionalFormatType::Bottom10 { rank, percent }
1041            } else {
1042                ConditionalFormatType::Top10 { rank, percent }
1043            }
1044        }
1045        "aboveAverage" => {
1046            let above = cf_rule.above_average.unwrap_or(true);
1047            let equal_average = cf_rule.equal_average.unwrap_or(false);
1048            ConditionalFormatType::AboveAverage {
1049                above,
1050                equal_average,
1051            }
1052        }
1053        "containsBlanks" => ConditionalFormatType::ContainsBlanks,
1054        "notContainsBlanks" => ConditionalFormatType::NotContainsBlanks,
1055        "containsErrors" => ConditionalFormatType::ContainsErrors,
1056        "notContainsErrors" => ConditionalFormatType::NotContainsErrors,
1057        "containsText" => ConditionalFormatType::ContainsText {
1058            text: cf_rule.text.clone().unwrap_or_default(),
1059        },
1060        "notContainsText" => ConditionalFormatType::NotContainsText {
1061            text: cf_rule.text.clone().unwrap_or_default(),
1062        },
1063        "beginsWith" => ConditionalFormatType::BeginsWith {
1064            text: cf_rule.text.clone().unwrap_or_default(),
1065        },
1066        "endsWith" => ConditionalFormatType::EndsWith {
1067            text: cf_rule.text.clone().unwrap_or_default(),
1068        },
1069        // Unknown rule types default to expression
1070        _ => ConditionalFormatType::Expression {
1071            formula: cf_rule.formulas.first().cloned().unwrap_or_default(),
1072        },
1073    };
1074
1075    ConditionalFormatRule {
1076        rule_type,
1077        format,
1078        priority: Some(cf_rule.priority),
1079        stop_if_true: cf_rule.stop_if_true.unwrap_or(false),
1080    }
1081}
1082
1083/// Set conditional formatting rules on a cell range. Each call adds a new
1084/// `conditionalFormatting` element with one or more `cfRule` children.
1085///
1086/// Rules without an explicit priority get one greater than the current
1087/// maximum. Returns `Error::InvalidArgument` without modifying the worksheet
1088/// or stylesheet when such a priority would exceed `i32::MAX`.
1089pub fn set_conditional_format(
1090    ws: &mut WorksheetXml,
1091    stylesheet: &mut StyleSheet,
1092    sqref: &str,
1093    rules: &[ConditionalFormatRule],
1094) -> Result<()> {
1095    let mut priorities = Vec::with_capacity(rules.len());
1096    let mut auto_priority = next_priority(ws);
1097    for rule in rules {
1098        let priority = match rule.priority {
1099            Some(priority) => priority,
1100            None => auto_priority.ok_or_else(|| {
1101                Error::InvalidArgument("no conditional formatting priority available".to_string())
1102            })?,
1103        };
1104        let following = priority.checked_add(1).filter(|p| *p <= MAX_CF_PRIORITY);
1105        auto_priority = match (auto_priority, following) {
1106            (Some(current), Some(next)) => Some(current.max(next)),
1107            _ => None,
1108        };
1109        priorities.push(priority);
1110    }
1111
1112    let mut xml_rules = Vec::with_capacity(rules.len());
1113    let first_cell = first_cell_reference(sqref);
1114    for (rule, priority) in rules.iter().zip(priorities) {
1115        let cf_rule = rule_to_xml(rule, stylesheet, priority, first_cell);
1116        xml_rules.push(cf_rule);
1117    }
1118
1119    ws.conditional_formatting.push(ConditionalFormatting {
1120        sqref: sqref.to_string(),
1121        cf_rules: xml_rules,
1122    });
1123
1124    Ok(())
1125}
1126
1127/// Get all conditional formatting rules from a worksheet.
1128///
1129/// Returns a list of `(sqref, rules)` pairs.
1130pub fn get_conditional_formats(
1131    ws: &WorksheetXml,
1132    stylesheet: &StyleSheet,
1133) -> Vec<(String, Vec<ConditionalFormatRule>)> {
1134    ws.conditional_formatting
1135        .iter()
1136        .map(|cf| {
1137            let rules = cf
1138                .cf_rules
1139                .iter()
1140                .map(|r| xml_to_rule(r, stylesheet))
1141                .collect();
1142            (cf.sqref.clone(), rules)
1143        })
1144        .collect()
1145}
1146
1147/// Delete all conditional formatting rules for a specific cell range.
1148pub fn delete_conditional_format(ws: &mut WorksheetXml, sqref: &str) -> Result<()> {
1149    ws.conditional_formatting.retain(|cf| cf.sqref != sqref);
1150    Ok(())
1151}
1152
1153#[cfg(test)]
1154mod tests {
1155    use super::*;
1156
1157    fn default_stylesheet() -> StyleSheet {
1158        StyleSheet::default()
1159    }
1160
1161    // CellIs tests
1162
1163    #[test]
1164    fn test_cell_is_greater_than() {
1165        let mut ws = WorksheetXml::default();
1166        let mut ss = default_stylesheet();
1167        let rules = vec![ConditionalFormatRule {
1168            rule_type: ConditionalFormatType::CellIs {
1169                operator: CfOperator::GreaterThan,
1170                formula: "100".to_string(),
1171                formula2: None,
1172            },
1173            format: Some(ConditionalStyle {
1174                font: Some(FontStyle {
1175                    bold: true,
1176                    color: Some(StyleColor::Rgb("FFFF0000".to_string())),
1177                    ..FontStyle::default()
1178                }),
1179                ..ConditionalStyle::default()
1180            }),
1181            priority: None,
1182            stop_if_true: false,
1183        }];
1184
1185        set_conditional_format(&mut ws, &mut ss, "A1:A100", &rules).unwrap();
1186
1187        assert_eq!(ws.conditional_formatting.len(), 1);
1188        assert_eq!(ws.conditional_formatting[0].sqref, "A1:A100");
1189        assert_eq!(ws.conditional_formatting[0].cf_rules.len(), 1);
1190
1191        let rule = &ws.conditional_formatting[0].cf_rules[0];
1192        assert_eq!(rule.rule_type, "cellIs");
1193        assert_eq!(rule.operator, Some("greaterThan".to_string()));
1194        assert_eq!(rule.formulas, vec!["100".to_string()]);
1195        assert!(rule.dxf_id.is_some());
1196        assert_eq!(rule.priority, 1);
1197
1198        // Verify DXF was added
1199        let dxfs = ss.dxfs.as_ref().unwrap();
1200        assert_eq!(dxfs.dxfs.len(), 1);
1201        assert!(dxfs.dxfs[0].font.is_some());
1202    }
1203
1204    #[test]
1205    fn test_cell_is_between() {
1206        let mut ws = WorksheetXml::default();
1207        let mut ss = default_stylesheet();
1208        let rules = vec![ConditionalFormatRule {
1209            rule_type: ConditionalFormatType::CellIs {
1210                operator: CfOperator::Between,
1211                formula: "10".to_string(),
1212                formula2: Some("20".to_string()),
1213            },
1214            format: None,
1215            priority: None,
1216            stop_if_true: false,
1217        }];
1218
1219        set_conditional_format(&mut ws, &mut ss, "B1:B50", &rules).unwrap();
1220
1221        let rule = &ws.conditional_formatting[0].cf_rules[0];
1222        assert_eq!(rule.operator, Some("between".to_string()));
1223        assert_eq!(rule.formulas, vec!["10".to_string(), "20".to_string()]);
1224        assert!(rule.dxf_id.is_none());
1225    }
1226
1227    #[test]
1228    fn test_cell_is_equal() {
1229        let mut ws = WorksheetXml::default();
1230        let mut ss = default_stylesheet();
1231        let rules = vec![ConditionalFormatRule {
1232            rule_type: ConditionalFormatType::CellIs {
1233                operator: CfOperator::Equal,
1234                formula: "\"Yes\"".to_string(),
1235                formula2: None,
1236            },
1237            format: Some(ConditionalStyle {
1238                fill: Some(FillStyle {
1239                    pattern: PatternType::Solid,
1240                    fg_color: Some(StyleColor::Rgb("FF00FF00".to_string())),
1241                    bg_color: None,
1242                    gradient: None,
1243                }),
1244                ..ConditionalStyle::default()
1245            }),
1246            priority: None,
1247            stop_if_true: false,
1248        }];
1249
1250        set_conditional_format(&mut ws, &mut ss, "C1:C10", &rules).unwrap();
1251
1252        let rule = &ws.conditional_formatting[0].cf_rules[0];
1253        assert_eq!(rule.operator, Some("equal".to_string()));
1254        assert_eq!(rule.formulas, vec!["\"Yes\"".to_string()]);
1255    }
1256
1257    #[test]
1258    fn test_cell_is_less_than() {
1259        let mut ws = WorksheetXml::default();
1260        let mut ss = default_stylesheet();
1261        let rules = vec![ConditionalFormatRule {
1262            rule_type: ConditionalFormatType::CellIs {
1263                operator: CfOperator::LessThan,
1264                formula: "0".to_string(),
1265                formula2: None,
1266            },
1267            format: None,
1268            priority: None,
1269            stop_if_true: false,
1270        }];
1271
1272        set_conditional_format(&mut ws, &mut ss, "D1:D10", &rules).unwrap();
1273        let rule = &ws.conditional_formatting[0].cf_rules[0];
1274        assert_eq!(rule.operator, Some("lessThan".to_string()));
1275    }
1276
1277    #[test]
1278    fn test_cell_is_not_between() {
1279        let mut ws = WorksheetXml::default();
1280        let mut ss = default_stylesheet();
1281        let rules = vec![ConditionalFormatRule {
1282            rule_type: ConditionalFormatType::CellIs {
1283                operator: CfOperator::NotBetween,
1284                formula: "1".to_string(),
1285                formula2: Some("10".to_string()),
1286            },
1287            format: None,
1288            priority: None,
1289            stop_if_true: false,
1290        }];
1291
1292        set_conditional_format(&mut ws, &mut ss, "E1:E10", &rules).unwrap();
1293        let rule = &ws.conditional_formatting[0].cf_rules[0];
1294        assert_eq!(rule.operator, Some("notBetween".to_string()));
1295        assert_eq!(rule.formulas.len(), 2);
1296    }
1297
1298    // Expression tests
1299
1300    #[test]
1301    fn test_expression_rule() {
1302        let mut ws = WorksheetXml::default();
1303        let mut ss = default_stylesheet();
1304        let rules = vec![ConditionalFormatRule {
1305            rule_type: ConditionalFormatType::Expression {
1306                formula: "MOD(ROW(),2)=0".to_string(),
1307            },
1308            format: Some(ConditionalStyle {
1309                fill: Some(FillStyle {
1310                    pattern: PatternType::Solid,
1311                    fg_color: Some(StyleColor::Rgb("FFEEEEEE".to_string())),
1312                    bg_color: None,
1313                    gradient: None,
1314                }),
1315                ..ConditionalStyle::default()
1316            }),
1317            priority: None,
1318            stop_if_true: false,
1319        }];
1320
1321        set_conditional_format(&mut ws, &mut ss, "A1:Z100", &rules).unwrap();
1322
1323        let rule = &ws.conditional_formatting[0].cf_rules[0];
1324        assert_eq!(rule.rule_type, "expression");
1325        assert_eq!(rule.formulas, vec!["MOD(ROW(),2)=0".to_string()]);
1326        assert!(rule.dxf_id.is_some());
1327    }
1328
1329    // ColorScale tests
1330
1331    #[test]
1332    fn test_two_color_scale() {
1333        let mut ws = WorksheetXml::default();
1334        let mut ss = default_stylesheet();
1335        let rules = vec![ConditionalFormatRule {
1336            rule_type: ConditionalFormatType::ColorScale {
1337                min_type: CfValueType::Min,
1338                min_value: None,
1339                min_color: "FFF8696B".to_string(),
1340                mid_type: None,
1341                mid_value: None,
1342                mid_color: None,
1343                max_type: CfValueType::Max,
1344                max_value: None,
1345                max_color: "FF63BE7B".to_string(),
1346            },
1347            format: None,
1348            priority: None,
1349            stop_if_true: false,
1350        }];
1351
1352        set_conditional_format(&mut ws, &mut ss, "A1:A100", &rules).unwrap();
1353
1354        let rule = &ws.conditional_formatting[0].cf_rules[0];
1355        assert_eq!(rule.rule_type, "colorScale");
1356        assert!(rule.dxf_id.is_none());
1357        let cs = rule.color_scale.as_ref().unwrap();
1358        assert_eq!(cs.cfvos.len(), 2);
1359        assert_eq!(cs.colors.len(), 2);
1360        assert_eq!(cs.cfvos[0].value_type, "min");
1361        assert_eq!(cs.cfvos[1].value_type, "max");
1362        assert_eq!(cs.colors[0].rgb, Some("FFF8696B".to_string()));
1363        assert_eq!(cs.colors[1].rgb, Some("FF63BE7B".to_string()));
1364    }
1365
1366    #[test]
1367    fn test_three_color_scale() {
1368        let mut ws = WorksheetXml::default();
1369        let mut ss = default_stylesheet();
1370        let rules = vec![ConditionalFormatRule {
1371            rule_type: ConditionalFormatType::ColorScale {
1372                min_type: CfValueType::Min,
1373                min_value: None,
1374                min_color: "FFF8696B".to_string(),
1375                mid_type: Some(CfValueType::Percentile),
1376                mid_value: Some("50".to_string()),
1377                mid_color: Some("FFFFEB84".to_string()),
1378                max_type: CfValueType::Max,
1379                max_value: None,
1380                max_color: "FF63BE7B".to_string(),
1381            },
1382            format: None,
1383            priority: None,
1384            stop_if_true: false,
1385        }];
1386
1387        set_conditional_format(&mut ws, &mut ss, "B1:B100", &rules).unwrap();
1388
1389        let rule = &ws.conditional_formatting[0].cf_rules[0];
1390        let cs = rule.color_scale.as_ref().unwrap();
1391        assert_eq!(cs.cfvos.len(), 3);
1392        assert_eq!(cs.colors.len(), 3);
1393        assert_eq!(cs.cfvos[1].value_type, "percentile");
1394        assert_eq!(cs.cfvos[1].val, Some("50".to_string()));
1395        assert_eq!(cs.colors[1].rgb, Some("FFFFEB84".to_string()));
1396    }
1397
1398    // DataBar tests
1399
1400    #[test]
1401    fn test_data_bar() {
1402        let mut ws = WorksheetXml::default();
1403        let mut ss = default_stylesheet();
1404        let rules = vec![ConditionalFormatRule {
1405            rule_type: ConditionalFormatType::DataBar {
1406                min_type: CfValueType::Min,
1407                min_value: None,
1408                max_type: CfValueType::Max,
1409                max_value: None,
1410                color: "FF638EC6".to_string(),
1411                show_value: true,
1412            },
1413            format: None,
1414            priority: None,
1415            stop_if_true: false,
1416        }];
1417
1418        set_conditional_format(&mut ws, &mut ss, "C1:C50", &rules).unwrap();
1419
1420        let rule = &ws.conditional_formatting[0].cf_rules[0];
1421        assert_eq!(rule.rule_type, "dataBar");
1422        let db = rule.data_bar.as_ref().unwrap();
1423        assert!(db.show_value.is_none()); // true is default, not serialized
1424        assert_eq!(db.cfvos.len(), 2);
1425        assert_eq!(db.color.as_ref().unwrap().rgb, Some("FF638EC6".to_string()));
1426    }
1427
1428    #[test]
1429    fn test_data_bar_hidden_value() {
1430        let mut ws = WorksheetXml::default();
1431        let mut ss = default_stylesheet();
1432        let rules = vec![ConditionalFormatRule {
1433            rule_type: ConditionalFormatType::DataBar {
1434                min_type: CfValueType::Num,
1435                min_value: Some("0".to_string()),
1436                max_type: CfValueType::Num,
1437                max_value: Some("100".to_string()),
1438                color: "FF638EC6".to_string(),
1439                show_value: false,
1440            },
1441            format: None,
1442            priority: None,
1443            stop_if_true: false,
1444        }];
1445
1446        set_conditional_format(&mut ws, &mut ss, "D1:D50", &rules).unwrap();
1447
1448        let db = ws.conditional_formatting[0].cf_rules[0]
1449            .data_bar
1450            .as_ref()
1451            .unwrap();
1452        assert_eq!(db.show_value, Some(false));
1453        assert_eq!(db.cfvos[0].value_type, "num");
1454        assert_eq!(db.cfvos[0].val, Some("0".to_string()));
1455    }
1456
1457    // DuplicateValues / UniqueValues tests
1458
1459    #[test]
1460    fn test_duplicate_values() {
1461        let mut ws = WorksheetXml::default();
1462        let mut ss = default_stylesheet();
1463        let rules = vec![ConditionalFormatRule {
1464            rule_type: ConditionalFormatType::DuplicateValues,
1465            format: Some(ConditionalStyle {
1466                fill: Some(FillStyle {
1467                    pattern: PatternType::Solid,
1468                    fg_color: Some(StyleColor::Rgb("FFFF0000".to_string())),
1469                    bg_color: None,
1470                    gradient: None,
1471                }),
1472                ..ConditionalStyle::default()
1473            }),
1474            priority: None,
1475            stop_if_true: false,
1476        }];
1477
1478        set_conditional_format(&mut ws, &mut ss, "A1:A100", &rules).unwrap();
1479
1480        let rule = &ws.conditional_formatting[0].cf_rules[0];
1481        assert_eq!(rule.rule_type, "duplicateValues");
1482        assert!(rule.dxf_id.is_some());
1483    }
1484
1485    #[test]
1486    fn test_unique_values() {
1487        let mut ws = WorksheetXml::default();
1488        let mut ss = default_stylesheet();
1489        let rules = vec![ConditionalFormatRule {
1490            rule_type: ConditionalFormatType::UniqueValues,
1491            format: None,
1492            priority: None,
1493            stop_if_true: false,
1494        }];
1495
1496        set_conditional_format(&mut ws, &mut ss, "B1:B50", &rules).unwrap();
1497
1498        let rule = &ws.conditional_formatting[0].cf_rules[0];
1499        assert_eq!(rule.rule_type, "uniqueValues");
1500    }
1501
1502    // Top10 / Bottom10 tests
1503
1504    #[test]
1505    fn test_top_10() {
1506        let mut ws = WorksheetXml::default();
1507        let mut ss = default_stylesheet();
1508        let rules = vec![ConditionalFormatRule {
1509            rule_type: ConditionalFormatType::Top10 {
1510                rank: 5,
1511                percent: false,
1512            },
1513            format: None,
1514            priority: None,
1515            stop_if_true: false,
1516        }];
1517
1518        set_conditional_format(&mut ws, &mut ss, "A1:A100", &rules).unwrap();
1519
1520        let rule = &ws.conditional_formatting[0].cf_rules[0];
1521        assert_eq!(rule.rule_type, "top10");
1522        assert_eq!(rule.rank, Some(5));
1523        assert!(rule.percent.is_none());
1524        assert!(rule.bottom.is_none());
1525    }
1526
1527    #[test]
1528    fn test_top_10_percent() {
1529        let mut ws = WorksheetXml::default();
1530        let mut ss = default_stylesheet();
1531        let rules = vec![ConditionalFormatRule {
1532            rule_type: ConditionalFormatType::Top10 {
1533                rank: 10,
1534                percent: true,
1535            },
1536            format: None,
1537            priority: None,
1538            stop_if_true: false,
1539        }];
1540
1541        set_conditional_format(&mut ws, &mut ss, "A1:A100", &rules).unwrap();
1542
1543        let rule = &ws.conditional_formatting[0].cf_rules[0];
1544        assert_eq!(rule.percent, Some(true));
1545        assert!(rule.bottom.is_none());
1546    }
1547
1548    #[test]
1549    fn test_bottom_10() {
1550        let mut ws = WorksheetXml::default();
1551        let mut ss = default_stylesheet();
1552        let rules = vec![ConditionalFormatRule {
1553            rule_type: ConditionalFormatType::Bottom10 {
1554                rank: 3,
1555                percent: false,
1556            },
1557            format: None,
1558            priority: None,
1559            stop_if_true: false,
1560        }];
1561
1562        set_conditional_format(&mut ws, &mut ss, "A1:A100", &rules).unwrap();
1563
1564        let rule = &ws.conditional_formatting[0].cf_rules[0];
1565        assert_eq!(rule.rule_type, "top10"); // Bottom uses top10 type with bottom=true
1566        assert_eq!(rule.rank, Some(3));
1567        assert_eq!(rule.bottom, Some(true));
1568    }
1569
1570    // AboveAverage tests
1571
1572    #[test]
1573    fn test_above_average() {
1574        let mut ws = WorksheetXml::default();
1575        let mut ss = default_stylesheet();
1576        let rules = vec![ConditionalFormatRule {
1577            rule_type: ConditionalFormatType::AboveAverage {
1578                above: true,
1579                equal_average: false,
1580            },
1581            format: None,
1582            priority: None,
1583            stop_if_true: false,
1584        }];
1585
1586        set_conditional_format(&mut ws, &mut ss, "A1:A100", &rules).unwrap();
1587
1588        let rule = &ws.conditional_formatting[0].cf_rules[0];
1589        assert_eq!(rule.rule_type, "aboveAverage");
1590        assert!(rule.above_average.is_none()); // true is default
1591        assert!(rule.equal_average.is_none()); // false is default
1592    }
1593
1594    #[test]
1595    fn test_below_average() {
1596        let mut ws = WorksheetXml::default();
1597        let mut ss = default_stylesheet();
1598        let rules = vec![ConditionalFormatRule {
1599            rule_type: ConditionalFormatType::AboveAverage {
1600                above: false,
1601                equal_average: true,
1602            },
1603            format: None,
1604            priority: None,
1605            stop_if_true: false,
1606        }];
1607
1608        set_conditional_format(&mut ws, &mut ss, "A1:A100", &rules).unwrap();
1609
1610        let rule = &ws.conditional_formatting[0].cf_rules[0];
1611        assert_eq!(rule.above_average, Some(false));
1612        assert_eq!(rule.equal_average, Some(true));
1613    }
1614
1615    // Text-based tests
1616
1617    #[test]
1618    fn test_contains_text() {
1619        let mut ws = WorksheetXml::default();
1620        let mut ss = default_stylesheet();
1621        let rules = vec![ConditionalFormatRule {
1622            rule_type: ConditionalFormatType::ContainsText {
1623                text: "error".to_string(),
1624            },
1625            format: None,
1626            priority: None,
1627            stop_if_true: false,
1628        }];
1629
1630        set_conditional_format(&mut ws, &mut ss, "A1:A100", &rules).unwrap();
1631
1632        let rule = &ws.conditional_formatting[0].cf_rules[0];
1633        assert_eq!(rule.rule_type, "containsText");
1634        assert_eq!(rule.text, Some("error".to_string()));
1635        assert_eq!(rule.operator, Some("containsText".to_string()));
1636        assert_eq!(rule.formulas, vec!["NOT(ISERROR(SEARCH(\"error\",A1)))"]);
1637    }
1638
1639    #[test]
1640    fn test_not_contains_text() {
1641        let mut ws = WorksheetXml::default();
1642        let mut ss = default_stylesheet();
1643        let rules = vec![ConditionalFormatRule {
1644            rule_type: ConditionalFormatType::NotContainsText {
1645                text: "done".to_string(),
1646            },
1647            format: None,
1648            priority: None,
1649            stop_if_true: false,
1650        }];
1651
1652        set_conditional_format(&mut ws, &mut ss, "A1:A100", &rules).unwrap();
1653
1654        let rule = &ws.conditional_formatting[0].cf_rules[0];
1655        assert_eq!(rule.rule_type, "notContainsText");
1656        assert_eq!(rule.operator, Some("notContains".to_string()));
1657        assert_eq!(rule.formulas, vec!["ISERROR(SEARCH(\"done\",A1))"]);
1658    }
1659
1660    #[test]
1661    fn test_begins_with() {
1662        let mut ws = WorksheetXml::default();
1663        let mut ss = default_stylesheet();
1664        let rules = vec![ConditionalFormatRule {
1665            rule_type: ConditionalFormatType::BeginsWith {
1666                text: "Total".to_string(),
1667            },
1668            format: None,
1669            priority: None,
1670            stop_if_true: false,
1671        }];
1672
1673        set_conditional_format(&mut ws, &mut ss, "A1:A100", &rules).unwrap();
1674
1675        let rule = &ws.conditional_formatting[0].cf_rules[0];
1676        assert_eq!(rule.rule_type, "beginsWith");
1677        assert_eq!(rule.text, Some("Total".to_string()));
1678        assert_eq!(rule.formulas, vec!["LEFT(A1,LEN(\"Total\"))=\"Total\""]);
1679    }
1680
1681    #[test]
1682    fn test_ends_with() {
1683        let mut ws = WorksheetXml::default();
1684        let mut ss = default_stylesheet();
1685        let rules = vec![ConditionalFormatRule {
1686            rule_type: ConditionalFormatType::EndsWith {
1687                text: "Inc.".to_string(),
1688            },
1689            format: None,
1690            priority: None,
1691            stop_if_true: false,
1692        }];
1693
1694        set_conditional_format(&mut ws, &mut ss, "A1:A100", &rules).unwrap();
1695
1696        let rule = &ws.conditional_formatting[0].cf_rules[0];
1697        assert_eq!(rule.rule_type, "endsWith");
1698        assert_eq!(rule.text, Some("Inc.".to_string()));
1699        assert_eq!(rule.formulas, vec!["RIGHT(A1,LEN(\"Inc.\"))=\"Inc.\""]);
1700    }
1701
1702    // Blanks / Errors tests
1703
1704    #[test]
1705    fn test_contains_blanks() {
1706        let mut ws = WorksheetXml::default();
1707        let mut ss = default_stylesheet();
1708        let rules = vec![ConditionalFormatRule {
1709            rule_type: ConditionalFormatType::ContainsBlanks,
1710            format: None,
1711            priority: None,
1712            stop_if_true: false,
1713        }];
1714
1715        set_conditional_format(&mut ws, &mut ss, "A1:A100", &rules).unwrap();
1716        assert_eq!(
1717            ws.conditional_formatting[0].cf_rules[0].rule_type,
1718            "containsBlanks"
1719        );
1720    }
1721
1722    #[test]
1723    fn test_not_contains_blanks() {
1724        let mut ws = WorksheetXml::default();
1725        let mut ss = default_stylesheet();
1726        let rules = vec![ConditionalFormatRule {
1727            rule_type: ConditionalFormatType::NotContainsBlanks,
1728            format: None,
1729            priority: None,
1730            stop_if_true: false,
1731        }];
1732
1733        set_conditional_format(&mut ws, &mut ss, "A1:A100", &rules).unwrap();
1734        assert_eq!(
1735            ws.conditional_formatting[0].cf_rules[0].rule_type,
1736            "notContainsBlanks"
1737        );
1738    }
1739
1740    #[test]
1741    fn test_contains_errors() {
1742        let mut ws = WorksheetXml::default();
1743        let mut ss = default_stylesheet();
1744        let rules = vec![ConditionalFormatRule {
1745            rule_type: ConditionalFormatType::ContainsErrors,
1746            format: None,
1747            priority: None,
1748            stop_if_true: false,
1749        }];
1750
1751        set_conditional_format(&mut ws, &mut ss, "A1:A100", &rules).unwrap();
1752        assert_eq!(
1753            ws.conditional_formatting[0].cf_rules[0].rule_type,
1754            "containsErrors"
1755        );
1756    }
1757
1758    #[test]
1759    fn test_not_contains_errors() {
1760        let mut ws = WorksheetXml::default();
1761        let mut ss = default_stylesheet();
1762        let rules = vec![ConditionalFormatRule {
1763            rule_type: ConditionalFormatType::NotContainsErrors,
1764            format: None,
1765            priority: None,
1766            stop_if_true: false,
1767        }];
1768
1769        set_conditional_format(&mut ws, &mut ss, "A1:A100", &rules).unwrap();
1770        assert_eq!(
1771            ws.conditional_formatting[0].cf_rules[0].rule_type,
1772            "notContainsErrors"
1773        );
1774    }
1775
1776    // Delete tests
1777
1778    #[test]
1779    fn test_delete_conditional_format() {
1780        let mut ws = WorksheetXml::default();
1781        let mut ss = default_stylesheet();
1782
1783        let rules1 = vec![ConditionalFormatRule {
1784            rule_type: ConditionalFormatType::DuplicateValues,
1785            format: None,
1786            priority: None,
1787            stop_if_true: false,
1788        }];
1789        let rules2 = vec![ConditionalFormatRule {
1790            rule_type: ConditionalFormatType::UniqueValues,
1791            format: None,
1792            priority: None,
1793            stop_if_true: false,
1794        }];
1795
1796        set_conditional_format(&mut ws, &mut ss, "A1:A100", &rules1).unwrap();
1797        set_conditional_format(&mut ws, &mut ss, "B1:B100", &rules2).unwrap();
1798        assert_eq!(ws.conditional_formatting.len(), 2);
1799
1800        delete_conditional_format(&mut ws, "A1:A100").unwrap();
1801        assert_eq!(ws.conditional_formatting.len(), 1);
1802        assert_eq!(ws.conditional_formatting[0].sqref, "B1:B100");
1803    }
1804
1805    #[test]
1806    fn test_delete_nonexistent_conditional_format() {
1807        let mut ws = WorksheetXml::default();
1808        delete_conditional_format(&mut ws, "Z1:Z99").unwrap();
1809        assert!(ws.conditional_formatting.is_empty());
1810    }
1811
1812    // Multiple rules on same range
1813
1814    #[test]
1815    fn test_multiple_rules_same_range() {
1816        let mut ws = WorksheetXml::default();
1817        let mut ss = default_stylesheet();
1818
1819        let rules = vec![
1820            ConditionalFormatRule {
1821                rule_type: ConditionalFormatType::CellIs {
1822                    operator: CfOperator::GreaterThan,
1823                    formula: "90".to_string(),
1824                    formula2: None,
1825                },
1826                format: Some(ConditionalStyle {
1827                    fill: Some(FillStyle {
1828                        pattern: PatternType::Solid,
1829                        fg_color: Some(StyleColor::Rgb("FF00FF00".to_string())),
1830                        bg_color: None,
1831                        gradient: None,
1832                    }),
1833                    ..ConditionalStyle::default()
1834                }),
1835                priority: None,
1836                stop_if_true: false,
1837            },
1838            ConditionalFormatRule {
1839                rule_type: ConditionalFormatType::CellIs {
1840                    operator: CfOperator::LessThan,
1841                    formula: "50".to_string(),
1842                    formula2: None,
1843                },
1844                format: Some(ConditionalStyle {
1845                    fill: Some(FillStyle {
1846                        pattern: PatternType::Solid,
1847                        fg_color: Some(StyleColor::Rgb("FFFF0000".to_string())),
1848                        bg_color: None,
1849                        gradient: None,
1850                    }),
1851                    ..ConditionalStyle::default()
1852                }),
1853                priority: None,
1854                stop_if_true: false,
1855            },
1856        ];
1857
1858        set_conditional_format(&mut ws, &mut ss, "A1:A100", &rules).unwrap();
1859
1860        assert_eq!(ws.conditional_formatting.len(), 1);
1861        assert_eq!(ws.conditional_formatting[0].cf_rules.len(), 2);
1862
1863        let dxfs = ss.dxfs.as_ref().unwrap();
1864        assert_eq!(dxfs.dxfs.len(), 2);
1865    }
1866
1867    // Get (roundtrip) tests
1868
1869    #[test]
1870    fn test_get_conditional_formats_cell_is() {
1871        let mut ws = WorksheetXml::default();
1872        let mut ss = default_stylesheet();
1873
1874        let rules = vec![ConditionalFormatRule {
1875            rule_type: ConditionalFormatType::CellIs {
1876                operator: CfOperator::GreaterThanOrEqual,
1877                formula: "50".to_string(),
1878                formula2: None,
1879            },
1880            format: Some(ConditionalStyle {
1881                font: Some(FontStyle {
1882                    bold: true,
1883                    ..FontStyle::default()
1884                }),
1885                ..ConditionalStyle::default()
1886            }),
1887            priority: None,
1888            stop_if_true: true,
1889        }];
1890
1891        set_conditional_format(&mut ws, &mut ss, "A1:A100", &rules).unwrap();
1892
1893        let formats = get_conditional_formats(&ws, &ss);
1894        assert_eq!(formats.len(), 1);
1895        assert_eq!(formats[0].0, "A1:A100");
1896        assert_eq!(formats[0].1.len(), 1);
1897
1898        let rule = &formats[0].1[0];
1899        match &rule.rule_type {
1900            ConditionalFormatType::CellIs {
1901                operator,
1902                formula,
1903                formula2,
1904            } => {
1905                assert_eq!(*operator, CfOperator::GreaterThanOrEqual);
1906                assert_eq!(formula, "50");
1907                assert!(formula2.is_none());
1908            }
1909            _ => panic!("expected CellIs rule type"),
1910        }
1911        assert!(rule.stop_if_true);
1912        assert!(rule.format.is_some());
1913        assert!(rule.format.as_ref().unwrap().font.as_ref().unwrap().bold);
1914    }
1915
1916    #[test]
1917    fn test_get_conditional_formats_color_scale() {
1918        let mut ws = WorksheetXml::default();
1919        let mut ss = default_stylesheet();
1920
1921        let rules = vec![ConditionalFormatRule {
1922            rule_type: ConditionalFormatType::ColorScale {
1923                min_type: CfValueType::Min,
1924                min_value: None,
1925                min_color: "FFF8696B".to_string(),
1926                mid_type: Some(CfValueType::Percentile),
1927                mid_value: Some("50".to_string()),
1928                mid_color: Some("FFFFEB84".to_string()),
1929                max_type: CfValueType::Max,
1930                max_value: None,
1931                max_color: "FF63BE7B".to_string(),
1932            },
1933            format: None,
1934            priority: None,
1935            stop_if_true: false,
1936        }];
1937
1938        set_conditional_format(&mut ws, &mut ss, "A1:A100", &rules).unwrap();
1939
1940        let formats = get_conditional_formats(&ws, &ss);
1941        let rule = &formats[0].1[0];
1942        match &rule.rule_type {
1943            ConditionalFormatType::ColorScale {
1944                min_type,
1945                min_color,
1946                mid_type,
1947                mid_color,
1948                max_type,
1949                max_color,
1950                ..
1951            } => {
1952                assert_eq!(*min_type, CfValueType::Min);
1953                assert_eq!(min_color, "FFF8696B");
1954                assert_eq!(*mid_type, Some(CfValueType::Percentile));
1955                assert_eq!(*mid_color, Some("FFFFEB84".to_string()));
1956                assert_eq!(*max_type, CfValueType::Max);
1957                assert_eq!(max_color, "FF63BE7B");
1958            }
1959            _ => panic!("expected ColorScale rule type"),
1960        }
1961    }
1962
1963    #[test]
1964    fn test_get_conditional_formats_data_bar() {
1965        let mut ws = WorksheetXml::default();
1966        let mut ss = default_stylesheet();
1967
1968        let rules = vec![ConditionalFormatRule {
1969            rule_type: ConditionalFormatType::DataBar {
1970                min_type: CfValueType::Min,
1971                min_value: None,
1972                max_type: CfValueType::Max,
1973                max_value: None,
1974                color: "FF638EC6".to_string(),
1975                show_value: true,
1976            },
1977            format: None,
1978            priority: None,
1979            stop_if_true: false,
1980        }];
1981
1982        set_conditional_format(&mut ws, &mut ss, "A1:A100", &rules).unwrap();
1983
1984        let formats = get_conditional_formats(&ws, &ss);
1985        let rule = &formats[0].1[0];
1986        match &rule.rule_type {
1987            ConditionalFormatType::DataBar {
1988                min_type,
1989                max_type,
1990                color,
1991                show_value,
1992                ..
1993            } => {
1994                assert_eq!(*min_type, CfValueType::Min);
1995                assert_eq!(*max_type, CfValueType::Max);
1996                assert_eq!(color, "FF638EC6");
1997                assert!(*show_value);
1998            }
1999            _ => panic!("expected DataBar rule type"),
2000        }
2001    }
2002
2003    #[test]
2004    fn test_get_conditional_formats_duplicate_values() {
2005        let mut ws = WorksheetXml::default();
2006        let mut ss = default_stylesheet();
2007
2008        let rules = vec![ConditionalFormatRule {
2009            rule_type: ConditionalFormatType::DuplicateValues,
2010            format: None,
2011            priority: None,
2012            stop_if_true: false,
2013        }];
2014
2015        set_conditional_format(&mut ws, &mut ss, "A1:A100", &rules).unwrap();
2016
2017        let formats = get_conditional_formats(&ws, &ss);
2018        let rule = &formats[0].1[0];
2019        assert_eq!(rule.rule_type, ConditionalFormatType::DuplicateValues);
2020    }
2021
2022    #[test]
2023    fn test_get_conditional_formats_top10() {
2024        let mut ws = WorksheetXml::default();
2025        let mut ss = default_stylesheet();
2026
2027        let rules = vec![ConditionalFormatRule {
2028            rule_type: ConditionalFormatType::Top10 {
2029                rank: 5,
2030                percent: true,
2031            },
2032            format: None,
2033            priority: None,
2034            stop_if_true: false,
2035        }];
2036
2037        set_conditional_format(&mut ws, &mut ss, "A1:A100", &rules).unwrap();
2038
2039        let formats = get_conditional_formats(&ws, &ss);
2040        let rule = &formats[0].1[0];
2041        match &rule.rule_type {
2042            ConditionalFormatType::Top10 { rank, percent } => {
2043                assert_eq!(*rank, 5);
2044                assert!(*percent);
2045            }
2046            _ => panic!("expected Top10 rule type"),
2047        }
2048    }
2049
2050    #[test]
2051    fn test_get_conditional_formats_bottom10() {
2052        let mut ws = WorksheetXml::default();
2053        let mut ss = default_stylesheet();
2054
2055        let rules = vec![ConditionalFormatRule {
2056            rule_type: ConditionalFormatType::Bottom10 {
2057                rank: 3,
2058                percent: false,
2059            },
2060            format: None,
2061            priority: None,
2062            stop_if_true: false,
2063        }];
2064
2065        set_conditional_format(&mut ws, &mut ss, "A1:A100", &rules).unwrap();
2066
2067        let formats = get_conditional_formats(&ws, &ss);
2068        let rule = &formats[0].1[0];
2069        match &rule.rule_type {
2070            ConditionalFormatType::Bottom10 { rank, percent } => {
2071                assert_eq!(*rank, 3);
2072                assert!(!(*percent));
2073            }
2074            _ => panic!("expected Bottom10 rule type"),
2075        }
2076    }
2077
2078    #[test]
2079    fn test_get_conditional_formats_above_average() {
2080        let mut ws = WorksheetXml::default();
2081        let mut ss = default_stylesheet();
2082
2083        let rules = vec![ConditionalFormatRule {
2084            rule_type: ConditionalFormatType::AboveAverage {
2085                above: false,
2086                equal_average: true,
2087            },
2088            format: None,
2089            priority: None,
2090            stop_if_true: false,
2091        }];
2092
2093        set_conditional_format(&mut ws, &mut ss, "A1:A100", &rules).unwrap();
2094
2095        let formats = get_conditional_formats(&ws, &ss);
2096        let rule = &formats[0].1[0];
2097        match &rule.rule_type {
2098            ConditionalFormatType::AboveAverage {
2099                above,
2100                equal_average,
2101            } => {
2102                assert!(!above);
2103                assert!(*equal_average);
2104            }
2105            _ => panic!("expected AboveAverage rule type"),
2106        }
2107    }
2108
2109    #[test]
2110    fn test_get_conditional_formats_empty() {
2111        let ws = WorksheetXml::default();
2112        let ss = default_stylesheet();
2113        let formats = get_conditional_formats(&ws, &ss);
2114        assert!(formats.is_empty());
2115    }
2116
2117    // Priority auto-increment
2118
2119    #[test]
2120    fn test_priority_auto_increment() {
2121        let mut ws = WorksheetXml::default();
2122        let mut ss = default_stylesheet();
2123
2124        let rules1 = vec![ConditionalFormatRule {
2125            rule_type: ConditionalFormatType::DuplicateValues,
2126            format: None,
2127            priority: None,
2128            stop_if_true: false,
2129        }];
2130        set_conditional_format(&mut ws, &mut ss, "A1:A100", &rules1).unwrap();
2131
2132        let rules2 = vec![ConditionalFormatRule {
2133            rule_type: ConditionalFormatType::UniqueValues,
2134            format: None,
2135            priority: None,
2136            stop_if_true: false,
2137        }];
2138        set_conditional_format(&mut ws, &mut ss, "B1:B100", &rules2).unwrap();
2139
2140        assert_eq!(ws.conditional_formatting[0].cf_rules[0].priority, 1);
2141        assert_eq!(ws.conditional_formatting[1].cf_rules[0].priority, 2);
2142    }
2143
2144    #[test]
2145    fn test_priority_auto_increment_within_single_call() {
2146        let mut ws = WorksheetXml::default();
2147        let mut ss = default_stylesheet();
2148        let rules = vec![
2149            ConditionalFormatRule {
2150                rule_type: ConditionalFormatType::DuplicateValues,
2151                format: None,
2152                priority: None,
2153                stop_if_true: false,
2154            },
2155            ConditionalFormatRule {
2156                rule_type: ConditionalFormatType::UniqueValues,
2157                format: None,
2158                priority: None,
2159                stop_if_true: false,
2160            },
2161        ];
2162
2163        set_conditional_format(&mut ws, &mut ss, "A1:A100", &rules).unwrap();
2164
2165        let priorities: Vec<_> = ws.conditional_formatting[0]
2166            .cf_rules
2167            .iter()
2168            .map(|rule| rule.priority)
2169            .collect();
2170        assert_eq!(priorities, vec![1, 2]);
2171    }
2172
2173    #[test]
2174    fn test_custom_dxf_num_fmt_ids_do_not_conflict() {
2175        let mut ws = WorksheetXml::default();
2176        let mut ss = default_stylesheet();
2177        ss.num_fmts = Some(sheetkit_xml::styles::NumFmts {
2178            count: Some(1),
2179            num_fmts: vec![NumFmt {
2180                num_fmt_id: 164,
2181                format_code: "0.00".to_string(),
2182            }],
2183        });
2184        let rules = ["0.0%", "#,##0"].map(|code| ConditionalFormatRule {
2185            rule_type: ConditionalFormatType::DuplicateValues,
2186            format: Some(ConditionalStyle {
2187                num_fmt: Some(NumFmtStyle::Custom(code.to_string())),
2188                ..ConditionalStyle::default()
2189            }),
2190            priority: None,
2191            stop_if_true: false,
2192        });
2193
2194        set_conditional_format(&mut ws, &mut ss, "A1:A100", &rules).unwrap();
2195
2196        let ids: Vec<_> = ss
2197            .dxfs
2198            .as_ref()
2199            .unwrap()
2200            .dxfs
2201            .iter()
2202            .map(|dxf| dxf.num_fmt.as_ref().unwrap().num_fmt_id)
2203            .collect();
2204        assert_eq!(ids, vec![165, 166]);
2205    }
2206
2207    fn duplicate_rule(priority: Option<u32>, bold: bool) -> ConditionalFormatRule {
2208        ConditionalFormatRule {
2209            rule_type: ConditionalFormatType::DuplicateValues,
2210            format: bold.then(|| ConditionalStyle {
2211                font: Some(FontStyle {
2212                    bold: true,
2213                    ..FontStyle::default()
2214                }),
2215                ..ConditionalStyle::default()
2216            }),
2217            priority,
2218            stop_if_true: false,
2219        }
2220    }
2221
2222    #[test]
2223    fn test_auto_priority_after_parsed_u32_max_errors_without_mutation() {
2224        let mut ws = WorksheetXml::default();
2225        let mut ss = default_stylesheet();
2226        set_conditional_format(&mut ws, &mut ss, "A1", &[duplicate_rule(Some(1), false)]).unwrap();
2227        ws.conditional_formatting[0].cf_rules[0].priority = u32::MAX;
2228        let ws_before = ws.clone();
2229        let ss_before = ss.clone();
2230
2231        let result = set_conditional_format(&mut ws, &mut ss, "B1", &[duplicate_rule(None, true)]);
2232
2233        assert!(matches!(result, Err(Error::InvalidArgument(_))));
2234        assert_eq!(ws, ws_before);
2235        assert_eq!(ss, ss_before);
2236    }
2237
2238    #[test]
2239    fn test_auto_priority_at_i32_max_boundary() {
2240        let mut ws = WorksheetXml::default();
2241        let mut ss = default_stylesheet();
2242        let max = i32::MAX as u32;
2243        set_conditional_format(
2244            &mut ws,
2245            &mut ss,
2246            "A1",
2247            &[duplicate_rule(Some(max - 1), false)],
2248        )
2249        .unwrap();
2250        set_conditional_format(&mut ws, &mut ss, "B1", &[duplicate_rule(None, false)]).unwrap();
2251        assert_eq!(ws.conditional_formatting[1].cf_rules[0].priority, max);
2252
2253        let ws_before = ws.clone();
2254        let ss_before = ss.clone();
2255        let result = set_conditional_format(&mut ws, &mut ss, "C1", &[duplicate_rule(None, true)]);
2256        assert!(matches!(result, Err(Error::InvalidArgument(_))));
2257        assert_eq!(ws, ws_before);
2258        assert_eq!(ss, ss_before);
2259    }
2260
2261    #[test]
2262    fn test_auto_priority_after_explicit_i32_max_in_same_call_errors() {
2263        let mut ws = WorksheetXml::default();
2264        let mut ss = default_stylesheet();
2265        let rules = [
2266            duplicate_rule(Some(i32::MAX as u32), true),
2267            duplicate_rule(None, true),
2268        ];
2269        let result = set_conditional_format(&mut ws, &mut ss, "A1", &rules);
2270        assert!(matches!(result, Err(Error::InvalidArgument(_))));
2271        assert!(ws.conditional_formatting.is_empty());
2272        assert!(ss.dxfs.is_none());
2273    }
2274
2275    #[test]
2276    fn test_explicit_priority() {
2277        let mut ws = WorksheetXml::default();
2278        let mut ss = default_stylesheet();
2279
2280        let rules = vec![ConditionalFormatRule {
2281            rule_type: ConditionalFormatType::DuplicateValues,
2282            format: None,
2283            priority: Some(42),
2284            stop_if_true: false,
2285        }];
2286        set_conditional_format(&mut ws, &mut ss, "A1:A100", &rules).unwrap();
2287
2288        assert_eq!(ws.conditional_formatting[0].cf_rules[0].priority, 42);
2289    }
2290
2291    // Stop if true
2292
2293    #[test]
2294    fn test_stop_if_true() {
2295        let mut ws = WorksheetXml::default();
2296        let mut ss = default_stylesheet();
2297
2298        let rules = vec![ConditionalFormatRule {
2299            rule_type: ConditionalFormatType::DuplicateValues,
2300            format: None,
2301            priority: None,
2302            stop_if_true: true,
2303        }];
2304        set_conditional_format(&mut ws, &mut ss, "A1:A100", &rules).unwrap();
2305
2306        assert_eq!(
2307            ws.conditional_formatting[0].cf_rules[0].stop_if_true,
2308            Some(true)
2309        );
2310    }
2311
2312    // DXF style roundtrip
2313
2314    #[test]
2315    fn test_dxf_style_roundtrip_font() {
2316        let style = ConditionalStyle {
2317            font: Some(FontStyle {
2318                bold: true,
2319                italic: true,
2320                color: Some(StyleColor::Rgb("FFFF0000".to_string())),
2321                size: Some(14.0),
2322                name: Some("Arial".to_string()),
2323                ..FontStyle::default()
2324            }),
2325            ..ConditionalStyle::default()
2326        };
2327
2328        let dxf = conditional_style_to_dxf(&style);
2329        let roundtrip = dxf_to_conditional_style(&dxf);
2330
2331        let font = roundtrip.font.unwrap();
2332        assert!(font.bold);
2333        assert!(font.italic);
2334        assert_eq!(font.color, Some(StyleColor::Rgb("FFFF0000".to_string())));
2335        assert_eq!(font.size, Some(14.0));
2336        assert_eq!(font.name, Some("Arial".to_string()));
2337    }
2338
2339    #[test]
2340    fn test_dxf_style_roundtrip_fill() {
2341        let style = ConditionalStyle {
2342            fill: Some(FillStyle {
2343                pattern: PatternType::Solid,
2344                fg_color: Some(StyleColor::Rgb("FFFFFF00".to_string())),
2345                bg_color: None,
2346                gradient: None,
2347            }),
2348            ..ConditionalStyle::default()
2349        };
2350
2351        let dxf = conditional_style_to_dxf(&style);
2352        let roundtrip = dxf_to_conditional_style(&dxf);
2353
2354        let fill = roundtrip.fill.unwrap();
2355        assert_eq!(fill.pattern, PatternType::Solid);
2356        assert_eq!(fill.fg_color, Some(StyleColor::Rgb("FFFFFF00".to_string())));
2357    }
2358
2359    #[test]
2360    fn test_dxf_style_roundtrip_border() {
2361        let style = ConditionalStyle {
2362            border: Some(BorderStyle {
2363                left: Some(BorderSideStyle {
2364                    style: BorderLineStyle::Thin,
2365                    color: Some(StyleColor::Rgb("FF000000".to_string())),
2366                }),
2367                ..BorderStyle::default()
2368            }),
2369            ..ConditionalStyle::default()
2370        };
2371
2372        let dxf = conditional_style_to_dxf(&style);
2373        let roundtrip = dxf_to_conditional_style(&dxf);
2374
2375        let border = roundtrip.border.unwrap();
2376        let left = border.left.unwrap();
2377        assert_eq!(left.style, BorderLineStyle::Thin);
2378        assert_eq!(left.color, Some(StyleColor::Rgb("FF000000".to_string())));
2379    }
2380
2381    // XML serialization roundtrip
2382
2383    #[test]
2384    fn test_xml_serialization_roundtrip() {
2385        let mut ws = WorksheetXml::default();
2386        let mut ss = default_stylesheet();
2387
2388        let rules = vec![ConditionalFormatRule {
2389            rule_type: ConditionalFormatType::CellIs {
2390                operator: CfOperator::GreaterThan,
2391                formula: "100".to_string(),
2392                formula2: None,
2393            },
2394            format: Some(ConditionalStyle {
2395                font: Some(FontStyle {
2396                    bold: true,
2397                    ..FontStyle::default()
2398                }),
2399                ..ConditionalStyle::default()
2400            }),
2401            priority: None,
2402            stop_if_true: false,
2403        }];
2404
2405        set_conditional_format(&mut ws, &mut ss, "A1:A100", &rules).unwrap();
2406
2407        let xml = quick_xml::se::to_string(&ws).unwrap();
2408        assert!(xml.contains("conditionalFormatting"));
2409        assert!(xml.contains("cfRule"));
2410        assert!(xml.contains("cellIs"));
2411
2412        let parsed: WorksheetXml = quick_xml::de::from_str(&xml).unwrap();
2413        assert_eq!(parsed.conditional_formatting.len(), 1);
2414        assert_eq!(parsed.conditional_formatting[0].sqref, "A1:A100");
2415        assert_eq!(parsed.conditional_formatting[0].cf_rules.len(), 1);
2416        assert_eq!(
2417            parsed.conditional_formatting[0].cf_rules[0].rule_type,
2418            "cellIs"
2419        );
2420    }
2421
2422    #[test]
2423    fn test_color_scale_xml_roundtrip() {
2424        let mut ws = WorksheetXml::default();
2425        let mut ss = default_stylesheet();
2426
2427        let rules = vec![ConditionalFormatRule {
2428            rule_type: ConditionalFormatType::ColorScale {
2429                min_type: CfValueType::Min,
2430                min_value: None,
2431                min_color: "FFF8696B".to_string(),
2432                mid_type: None,
2433                mid_value: None,
2434                mid_color: None,
2435                max_type: CfValueType::Max,
2436                max_value: None,
2437                max_color: "FF63BE7B".to_string(),
2438            },
2439            format: None,
2440            priority: None,
2441            stop_if_true: false,
2442        }];
2443
2444        set_conditional_format(&mut ws, &mut ss, "A1:A100", &rules).unwrap();
2445
2446        let xml = quick_xml::se::to_string(&ws).unwrap();
2447        assert!(xml.contains("colorScale"));
2448
2449        let parsed: WorksheetXml = quick_xml::de::from_str(&xml).unwrap();
2450        let cs = parsed.conditional_formatting[0].cf_rules[0]
2451            .color_scale
2452            .as_ref()
2453            .unwrap();
2454        assert_eq!(cs.cfvos.len(), 2);
2455        assert_eq!(cs.colors.len(), 2);
2456    }
2457
2458    #[test]
2459    fn test_data_bar_xml_roundtrip() {
2460        let mut ws = WorksheetXml::default();
2461        let mut ss = default_stylesheet();
2462
2463        let rules = vec![ConditionalFormatRule {
2464            rule_type: ConditionalFormatType::DataBar {
2465                min_type: CfValueType::Min,
2466                min_value: None,
2467                max_type: CfValueType::Max,
2468                max_value: None,
2469                color: "FF638EC6".to_string(),
2470                show_value: true,
2471            },
2472            format: None,
2473            priority: None,
2474            stop_if_true: false,
2475        }];
2476
2477        set_conditional_format(&mut ws, &mut ss, "A1:A100", &rules).unwrap();
2478
2479        let xml = quick_xml::se::to_string(&ws).unwrap();
2480        assert!(xml.contains("dataBar"));
2481
2482        let parsed: WorksheetXml = quick_xml::de::from_str(&xml).unwrap();
2483        let db = parsed.conditional_formatting[0].cf_rules[0]
2484            .data_bar
2485            .as_ref()
2486            .unwrap();
2487        assert_eq!(db.cfvos.len(), 2);
2488    }
2489
2490    // CfOperator / CfValueType parse roundtrip
2491
2492    #[test]
2493    fn test_cf_operator_roundtrip() {
2494        let operators = [
2495            CfOperator::LessThan,
2496            CfOperator::LessThanOrEqual,
2497            CfOperator::Equal,
2498            CfOperator::NotEqual,
2499            CfOperator::GreaterThanOrEqual,
2500            CfOperator::GreaterThan,
2501            CfOperator::Between,
2502            CfOperator::NotBetween,
2503        ];
2504        for op in &operators {
2505            let s = op.as_str();
2506            let parsed = CfOperator::parse(s).unwrap();
2507            assert_eq!(*op, parsed);
2508        }
2509    }
2510
2511    #[test]
2512    fn test_cf_value_type_roundtrip() {
2513        let types = [
2514            CfValueType::Num,
2515            CfValueType::Percent,
2516            CfValueType::Min,
2517            CfValueType::Max,
2518            CfValueType::Percentile,
2519            CfValueType::Formula,
2520        ];
2521        for vt in &types {
2522            let s = vt.as_str();
2523            let parsed = CfValueType::parse(s).unwrap();
2524            assert_eq!(*vt, parsed);
2525        }
2526    }
2527}