ironcalc_base/
conditional_formatting.rs

1use std::collections::HashMap;
2
3use crate::expressions::utils::parse_reference_a1;
4use crate::formatter::dates::{date_to_serial_number, from_excel_date};
5use crate::{
6    calc_result::CalcResult,
7    cell::CellValue,
8    cf_types::{
9        CfCellResult, CfDataBar, CfIcon, CfRating, CfRule, CfRuleInput, Cfvo, ColorScaleThreshold,
10        ConditionalFormatting, ConditionalFormattingView, ExtendedStyle, Icon, IconThreshold,
11        PeriodType, TextOperator, ValueOperator,
12    },
13    expressions::types::{CellReferenceIndex, CellReferenceRC},
14    types::{Color, Dxf},
15    Model,
16};
17
18use chrono::{Datelike, Duration, Months, NaiveDate};
19
20// ---------------------------------------------------------------------------
21// Free helper functions for CF evaluation
22// ---------------------------------------------------------------------------
23
24/// Parses a space-separated sqref like "A1:C3 E5" into a list of (row1,col1,row2,col2) tuples.
25fn parse_sqref(sqref: &str) -> Vec<(i32, i32, i32, i32)> {
26    sqref
27        .split_whitespace()
28        .filter_map(parse_range_part)
29        .collect()
30}
31
32fn parse_range_part(s: &str) -> Option<(i32, i32, i32, i32)> {
33    let upper = s.to_uppercase();
34    let parts: Vec<&str> = upper.splitn(2, ':').collect();
35    match parts.len() {
36        1 => {
37            let r = parse_reference_a1(parts[0])?;
38            Some((r.row, r.column, r.row, r.column))
39        }
40        2 => {
41            let r1 = parse_reference_a1(parts[0])?;
42            let r2 = parse_reference_a1(parts[1])?;
43            let (row_min, row_max) = (r1.row.min(r2.row), r1.row.max(r2.row));
44            let (col_min, col_max) = (r1.column.min(r2.column), r1.column.max(r2.column));
45            Some((row_min, col_min, row_max, col_max))
46        }
47        _ => None,
48    }
49}
50
51/// Interpolates a color along the color scale for a given value.
52fn interpolate_color(v: f64, thresholds: &[f64], colors: &[String]) -> String {
53    let n = thresholds.len();
54    if n == 0 || colors.len() != n {
55        return "#000000".to_string();
56    }
57    if v <= thresholds[0] {
58        return colors[0].clone();
59    }
60    if v >= thresholds[n - 1] {
61        return colors[n - 1].clone();
62    }
63    for i in 0..n - 1 {
64        if v >= thresholds[i] && v <= thresholds[i + 1] {
65            let span = thresholds[i + 1] - thresholds[i];
66            let t = if span.abs() < f64::EPSILON {
67                0.0
68            } else {
69                (v - thresholds[i]) / span
70            };
71            return lerp_color(&colors[i], &colors[i + 1], t);
72        }
73    }
74    colors[0].clone()
75}
76
77fn lerp_color(c1: &str, c2: &str, t: f64) -> String {
78    let (r1, g1, b1) = parse_hex_color(c1);
79    let (r2, g2, b2) = parse_hex_color(c2);
80    let r = (r1 as f64 + (r2 as f64 - r1 as f64) * t).round() as u8;
81    let g = (g1 as f64 + (g2 as f64 - g1 as f64) * t).round() as u8;
82    let b = (b1 as f64 + (b2 as f64 - b1 as f64) * t).round() as u8;
83    format!("#{r:02X}{g:02X}{b:02X}")
84}
85
86fn parse_hex_color(s: &str) -> (u8, u8, u8) {
87    let s = s.trim_start_matches('#');
88    if s.len() == 6 {
89        let r = u8::from_str_radix(&s[0..2], 16).unwrap_or(0);
90        let g = u8::from_str_radix(&s[2..4], 16).unwrap_or(0);
91        let b = u8::from_str_radix(&s[4..6], 16).unwrap_or(0);
92        (r, g, b)
93    } else {
94        (0, 0, 0)
95    }
96}
97
98/// Returns the 0-indexed icon position for `v` given the ordered thresholds.
99/// The icon with index `i` applies when `v >= thresholds[i]`.
100fn compute_icon_index(v: f64, thresholds: &[(f64, bool)]) -> u32 {
101    let mut idx = 0u32;
102    for (i, &(value, is_strict)) in thresholds.iter().enumerate() {
103        if v > value || (v == value && is_strict) {
104            idx = i as u32;
105        }
106    }
107    idx
108}
109
110/// Parses a date bound of a Between/NotBetween time-period rule: either a
111/// date string in any format DATEVALUE accepts (e.g. "2025-04-25",
112/// "4/25/2025") or a plain Excel serial number.
113fn parse_cf_date_bound(value: &str) -> Option<f64> {
114    let trimmed = value.trim();
115    if let Ok(serial) = crate::functions::date_and_time::parse_datevalue_text(trimmed) {
116        return Some(serial as f64);
117    }
118    trimmed.parse::<f64>().ok().map(f64::floor)
119}
120
121/// Stable string key for a CellValue, used for duplicate detection.
122fn cell_value_key(v: &crate::cell::CellValue) -> Option<String> {
123    use crate::cell::CellValue;
124    match v {
125        CellValue::Number(n) => Some(format!("{n}")),
126        CellValue::String(s) => Some(s.to_lowercase()),
127        CellValue::Boolean(b) => Some(b.to_string()),
128        CellValue::None => None,
129    }
130}
131
132impl<'a> Model<'a> {
133    /// Evaluates all conditional formatting rules for the workbook.
134    ///
135    /// Iterates every worksheet's CF rules in priority order (lowest priority number,
136    /// processed last). The result for each cell is stored in
137    /// `cf_cache` and consumed by `get_extended_style_for_cell`.
138    pub fn evaluate_conditional_formatting(&mut self) {
139        self.cf_cache.clear();
140        let sheet_count = self.workbook.worksheets.len();
141        for sheet_idx in 0..sheet_count {
142            let mut cfs = self.workbook.worksheets[sheet_idx]
143                .conditional_formatting
144                .clone();
145            // Lower priority number = higher priority; process high-priority first so that
146            // the first writer into cf_cache wins.
147            cfs.sort_by_key(|cf| cf.priority);
148            for cf in cfs {
149                let ranges = parse_sqref(&cf.range);
150                if ranges.is_empty() {
151                    continue;
152                }
153                self.apply_cf_rule(sheet_idx as u32, &cf.cf_rule, &ranges);
154            }
155        }
156    }
157
158    // -----------------------------------------------------------------------
159    // CF evaluation helpers
160    // -----------------------------------------------------------------------
161
162    fn apply_cf_rule(&mut self, sheet: u32, rule: &CfRule, ranges: &[(i32, i32, i32, i32)]) {
163        match rule {
164            CfRule::ColorScale { thresholds } => {
165                self.apply_cf_color_scale(sheet, thresholds, ranges);
166            }
167            CfRule::CellIs {
168                operator,
169                formula,
170                formula2,
171                dxf_id,
172                stop_if_true,
173            } => {
174                self.apply_cf_cell_is(
175                    sheet,
176                    operator,
177                    formula,
178                    formula2.as_deref(),
179                    *dxf_id,
180                    *stop_if_true,
181                    ranges,
182                );
183            }
184            CfRule::Formula {
185                formula,
186                dxf_id,
187                stop_if_true,
188            } => {
189                self.apply_cf_formula(sheet, formula, *dxf_id, *stop_if_true, ranges);
190            }
191            CfRule::DuplicateValues {
192                dxf_id,
193                stop_if_true,
194            } => {
195                self.apply_cf_duplicate_values(sheet, *dxf_id, *stop_if_true, ranges);
196            }
197            CfRule::AboveAverage {
198                dxf_id,
199                stop_if_true,
200            } => {
201                self.apply_cf_average(sheet, *dxf_id, true, *stop_if_true, ranges);
202            }
203            CfRule::BelowAverage {
204                dxf_id,
205                stop_if_true,
206            } => {
207                self.apply_cf_average(sheet, *dxf_id, false, *stop_if_true, ranges);
208            }
209            CfRule::Top10 {
210                rank,
211                percent,
212                dxf_id,
213                stop_if_true,
214            } => {
215                self.apply_cf_top_n(
216                    sheet,
217                    *rank,
218                    *percent,
219                    false,
220                    *dxf_id,
221                    *stop_if_true,
222                    ranges,
223                );
224            }
225            CfRule::Bottom10 {
226                rank,
227                percent,
228                dxf_id,
229                stop_if_true,
230            } => {
231                self.apply_cf_top_n(sheet, *rank, *percent, true, *dxf_id, *stop_if_true, ranges);
232            }
233            CfRule::DataBar {
234                min,
235                max,
236                positive_color,
237                negative_color,
238                is_gradient,
239                show_value,
240            } => {
241                self.apply_cf_data_bar(
242                    sheet,
243                    min.as_ref(),
244                    max.as_ref(),
245                    positive_color,
246                    negative_color,
247                    *is_gradient,
248                    *show_value,
249                    ranges,
250                );
251            }
252            CfRule::IconSet {
253                thresholds,
254                show_value,
255            } => {
256                self.apply_cf_icon_set(sheet, thresholds, *show_value, ranges);
257            }
258            CfRule::Text {
259                operator,
260                value,
261                dxf_id,
262                stop_if_true,
263            } => {
264                self.apply_cf_text(sheet, operator, value, *dxf_id, *stop_if_true, ranges);
265            }
266            CfRule::UniqueValues {
267                dxf_id,
268                stop_if_true,
269            } => {
270                self.apply_cf_unique_values(sheet, *dxf_id, *stop_if_true, ranges);
271            }
272            CfRule::TimePeriod {
273                time_period,
274                date1,
275                date2,
276                dxf_id,
277                stop_if_true,
278            } => {
279                self.apply_cf_time_period(
280                    sheet,
281                    time_period,
282                    date1.as_deref(),
283                    date2.as_deref(),
284                    *dxf_id,
285                    *stop_if_true,
286                    ranges,
287                );
288            }
289            CfRule::IconRating {
290                icon,
291                color,
292                show_value,
293                thresholds,
294            } => {
295                self.apply_cf_icon_rating(sheet, icon, color, thresholds, *show_value, ranges);
296            }
297            CfRule::Blanks {
298                dxf_id,
299                stop_if_true,
300            } => {
301                self.apply_cf_blanks(sheet, *dxf_id, false, *stop_if_true, ranges);
302            }
303            CfRule::NotBlanks {
304                dxf_id,
305                stop_if_true,
306            } => {
307                self.apply_cf_blanks(sheet, *dxf_id, true, *stop_if_true, ranges);
308            }
309            CfRule::Errors {
310                dxf_id,
311                stop_if_true,
312            } => {
313                self.apply_cf_errors(sheet, *dxf_id, false, *stop_if_true, ranges);
314            }
315            CfRule::NoErrors {
316                dxf_id,
317                stop_if_true,
318            } => {
319                self.apply_cf_errors(sheet, *dxf_id, true, *stop_if_true, ranges);
320            }
321        }
322    }
323
324    fn update_cf_cache(
325        &mut self,
326        sheet: u32,
327        row: i32,
328        col: i32,
329        result: CfCellResult,
330        stop_if_true: bool,
331    ) {
332        if stop_if_true {
333            // Replace any lower-priority results already in the cache for this cell.
334            self.cf_cache.insert((sheet, row, col), vec![result]);
335        } else {
336            self.cf_cache
337                .entry((sheet, row, col))
338                .or_default()
339                .push(result);
340        }
341    }
342
343    /// Collects all numeric values from the given set of ranges on `sheet`.
344    fn collect_numeric_values(&self, sheet: u32, ranges: &[(i32, i32, i32, i32)]) -> Vec<f64> {
345        let mut values = Vec::new();
346        for &(r1, c1, r2, c2) in ranges {
347            for row in r1..=r2 {
348                for col in c1..=c2 {
349                    if let Ok(CellValue::Number(n)) = self.get_cell_value_by_index(sheet, row, col)
350                    {
351                        values.push(n);
352                    }
353                }
354            }
355        }
356        values
357    }
358
359    /// Resolves a Cfvo threshold to a concrete f64 value.
360    fn resolve_cfvo(&mut self, cfvo: &Cfvo, values: &[f64], sheet: u32) -> f64 {
361        match cfvo {
362            Cfvo::Min => values.iter().cloned().fold(f64::INFINITY, f64::min),
363            Cfvo::Max => values.iter().cloned().fold(f64::NEG_INFINITY, f64::max),
364            Cfvo::Number(n) => *n,
365            Cfvo::Percent(p) => {
366                if values.is_empty() {
367                    return 0.0;
368                }
369                let min = values.iter().cloned().fold(f64::INFINITY, f64::min);
370                let max = values.iter().cloned().fold(f64::NEG_INFINITY, f64::max);
371                let p = *p;
372                min + (max - min) * p / 100.0
373            }
374            Cfvo::Percentile(p) => {
375                if values.is_empty() {
376                    return 0.0;
377                }
378                let mut sorted = values.to_vec();
379                sorted.sort_by(|a, b| a.partial_cmp(b).unwrap_or(std::cmp::Ordering::Equal));
380                let idx = ((p / 100.0) * (sorted.len() - 1) as f64).floor() as usize;
381                sorted[idx.min(sorted.len() - 1)]
382            }
383            Cfvo::Formula(f) => self.evaluate_formula(f, sheet).unwrap_or(0.0),
384        }
385    }
386
387    fn apply_cf_color_scale(
388        &mut self,
389        sheet: u32,
390        thresholds: &[ColorScaleThreshold],
391        ranges: &[(i32, i32, i32, i32)],
392    ) {
393        if thresholds.len() < 2 {
394            return;
395        }
396        let values = self.collect_numeric_values(sheet, ranges);
397        if values.is_empty() {
398            return;
399        }
400        let colors: Vec<String> = thresholds
401            .iter()
402            .map(|t| t.color.to_rgb(&self.workbook.theme))
403            .collect();
404        let mut stops: Vec<f64> = Vec::with_capacity(thresholds.len());
405        for t in thresholds {
406            stops.push(self.resolve_cfvo(&t.cfvo, &values, sheet));
407        }
408        // Excel sorts only the threshold values, keeping colors at their original
409        // positional indices. A formula cfvo that resolves out-of-order (e.g. $G$13=0
410        // appearing after cfvo num=20) must be sorted into place while color[i] still
411        // maps to the i-th stop in sorted order.
412        stops.sort_by(|a, b| a.partial_cmp(b).unwrap_or(std::cmp::Ordering::Equal));
413
414        for &(r1, c1, r2, c2) in ranges {
415            for row in r1..=r2 {
416                for col in c1..=c2 {
417                    if let Ok(CellValue::Number(v)) = self.get_cell_value_by_index(sheet, row, col)
418                    {
419                        let color = interpolate_color(v, &stops, &colors);
420                        self.update_cf_cache(
421                            sheet,
422                            row,
423                            col,
424                            CfCellResult::ColorScale(Color::Rgb(color)),
425                            false,
426                        );
427                    }
428                }
429            }
430        }
431    }
432
433    #[allow(clippy::too_many_arguments)]
434    fn apply_cf_data_bar(
435        &mut self,
436        sheet: u32,
437        min: Option<&Cfvo>,
438        max: Option<&Cfvo>,
439        positive_color: &Color,
440        negative_color: &Color,
441        is_gradient: bool,
442        show_value: bool,
443        ranges: &[(i32, i32, i32, i32)],
444    ) {
445        let values = self.collect_numeric_values(sheet, ranges);
446        if values.is_empty() {
447            return;
448        }
449        let min_val = match min {
450            Some(cfvo) => self.resolve_cfvo(cfvo, &values, sheet),
451            None => values.iter().cloned().fold(0.0_f64, f64::min),
452        };
453        let max_val = match max {
454            Some(cfvo) => self.resolve_cfvo(cfvo, &values, sheet),
455            None => values.iter().cloned().fold(0.0_f64, f64::max),
456        };
457        let span = max_val - min_val;
458        if span.abs() < f64::EPSILON {
459            return;
460        }
461        let axis_position = (-min_val / span).clamp(0.0, 1.0);
462
463        for &(r1, c1, r2, c2) in ranges {
464            for row in r1..=r2 {
465                for col in c1..=c2 {
466                    if let Ok(CellValue::Number(v)) = self.get_cell_value_by_index(sheet, row, col)
467                    {
468                        let proportion = ((v - min_val) / span).clamp(0.0, 1.0);
469                        self.update_cf_cache(
470                            sheet,
471                            row,
472                            col,
473                            CfCellResult::DataBar {
474                                positive_color: positive_color.clone(),
475                                negative_color: negative_color.clone(),
476                                is_gradient,
477                                value: proportion,
478                                axis_position,
479                                show_value,
480                            },
481                            false,
482                        );
483                    }
484                }
485            }
486        }
487    }
488
489    fn apply_cf_icon_set(
490        &mut self,
491        sheet: u32,
492        thresholds: &[IconThreshold],
493        show_value: bool,
494        ranges: &[(i32, i32, i32, i32)],
495    ) {
496        if thresholds.is_empty() {
497            return;
498        }
499        let values = self.collect_numeric_values(sheet, ranges);
500        let stops: Vec<(f64, bool)> = thresholds
501            .iter()
502            .map(|t| (self.resolve_cfvo(&t.cfvo, &values, sheet), t.is_strict))
503            .collect();
504        let n = thresholds.len();
505
506        for &(r1, c1, r2, c2) in ranges {
507            for row in r1..=r2 {
508                for col in c1..=c2 {
509                    if let Ok(CellValue::Number(v)) = self.get_cell_value_by_index(sheet, row, col)
510                    {
511                        let idx = (compute_icon_index(v, &stops) as usize).min(n - 1);
512                        self.update_cf_cache(
513                            sheet,
514                            row,
515                            col,
516                            CfCellResult::Icon {
517                                icon: thresholds[idx].icon.clone(),
518                                color: thresholds[idx].color.clone(),
519                                show_value,
520                            },
521                            false,
522                        );
523                    }
524                }
525            }
526        }
527    }
528
529    fn apply_cf_icon_rating(
530        &mut self,
531        sheet: u32,
532        icon: &Icon,
533        color: &Color,
534        thresholds: &[(Cfvo, bool)],
535        show_value: bool,
536        ranges: &[(i32, i32, i32, i32)],
537    ) {
538        if thresholds.is_empty() {
539            return;
540        }
541        // Thresholds are stored lowest-value-first. count = how many thresholds the
542        // cell value exceeds; count ∈ [0, max] where max = thresholds.len().
543        // is_strict=true → threshold applies when v >= value (gte).
544        // is_strict=false → threshold applies when v > value (gt).
545        let max = thresholds.len() as u32;
546
547        let values = self.collect_numeric_values(sheet, ranges);
548        // Pre-resolve all Cfvo entries before iterating cells.
549        let resolved: Vec<(f64, bool)> = thresholds
550            .iter()
551            .map(|(cfvo, is_strict)| (self.resolve_cfvo(cfvo, &values, sheet), *is_strict))
552            .collect();
553
554        for &(r1, c1, r2, c2) in ranges {
555            for row in r1..=r2 {
556                for col in c1..=c2 {
557                    if let Ok(CellValue::Number(v)) = self.get_cell_value_by_index(sheet, row, col)
558                    {
559                        let mut count = 0u32;
560                        for &(threshold_val, is_strict) in resolved.iter().rev() {
561                            let exceeds = if is_strict {
562                                v >= threshold_val
563                            } else {
564                                v > threshold_val
565                            };
566                            if exceeds {
567                                count += 1;
568                            }
569                        }
570                        self.update_cf_cache(
571                            sheet,
572                            row,
573                            col,
574                            CfCellResult::Rating {
575                                icon: icon.clone(),
576                                count: count.min(max),
577                                max,
578                                color: color.clone(),
579                                show_value,
580                            },
581                            false,
582                        );
583                    }
584                }
585            }
586        }
587    }
588
589    #[allow(clippy::too_many_arguments)]
590    fn apply_cf_cell_is(
591        &mut self,
592        sheet: u32,
593        operator: &ValueOperator,
594        formula: &str,
595        formula2: Option<&str>,
596        dxf_id: u32,
597        stop_if_true: bool,
598        ranges: &[(i32, i32, i32, i32)],
599    ) {
600        let threshold = match self.evaluate_formula(formula, sheet) {
601            Some(v) => v,
602            None => return,
603        };
604        let threshold2 = formula2.and_then(|f| self.evaluate_formula(f, sheet));
605
606        for &(r1, c1, r2, c2) in ranges {
607            for row in r1..=r2 {
608                for col in c1..=c2 {
609                    if let Ok(CellValue::Number(v)) = self.get_cell_value_by_index(sheet, row, col)
610                    {
611                        let matches = match operator {
612                            ValueOperator::Equal => (v - threshold).abs() < f64::EPSILON,
613                            ValueOperator::GreaterThan => v > threshold,
614                            ValueOperator::GreaterThanOrEqual => v >= threshold,
615                            ValueOperator::LessThan => v < threshold,
616                            ValueOperator::LessThanOrEqual => v <= threshold,
617                            ValueOperator::NotEqual => (v - threshold).abs() >= f64::EPSILON,
618                            ValueOperator::Between => {
619                                let t2 = threshold2.unwrap_or(threshold);
620                                v >= threshold.min(t2) && v <= threshold.max(t2)
621                            }
622                            ValueOperator::NotBetween => {
623                                let t2 = threshold2.unwrap_or(threshold);
624                                v < threshold.min(t2) || v > threshold.max(t2)
625                            }
626                        };
627                        if matches {
628                            self.update_cf_cache(
629                                sheet,
630                                row,
631                                col,
632                                CfCellResult::Dxf(dxf_id),
633                                stop_if_true,
634                            );
635                        }
636                    }
637                }
638            }
639        }
640    }
641
642    fn apply_cf_average(
643        &mut self,
644        sheet: u32,
645        dxf_id: u32,
646        above: bool,
647        stop_if_true: bool,
648        ranges: &[(i32, i32, i32, i32)],
649    ) {
650        let values = self.collect_numeric_values(sheet, ranges);
651        if values.is_empty() {
652            return;
653        }
654        let avg = values.iter().sum::<f64>() / values.len() as f64;
655
656        for &(r1, c1, r2, c2) in ranges {
657            for row in r1..=r2 {
658                for col in c1..=c2 {
659                    if let Ok(CellValue::Number(v)) = self.get_cell_value_by_index(sheet, row, col)
660                    {
661                        if (above && v > avg) || (!above && v < avg) {
662                            self.update_cf_cache(
663                                sheet,
664                                row,
665                                col,
666                                CfCellResult::Dxf(dxf_id),
667                                stop_if_true,
668                            );
669                        }
670                    }
671                }
672            }
673        }
674    }
675
676    #[allow(clippy::too_many_arguments)]
677    fn apply_cf_top_n(
678        &mut self,
679        sheet: u32,
680        rank: u32,
681        percent: bool,
682        bottom: bool,
683        dxf_id: u32,
684        stop_if_true: bool,
685        ranges: &[(i32, i32, i32, i32)],
686    ) {
687        let values = self.collect_numeric_values(sheet, ranges);
688        if values.is_empty() {
689            return;
690        }
691        let n = if percent {
692            ((rank as f64 / 100.0) * values.len() as f64).ceil() as usize
693        } else {
694            rank as usize
695        }
696        .max(1);
697
698        let mut sorted = values.clone();
699        if bottom {
700            sorted.sort_by(|a, b| a.partial_cmp(b).unwrap_or(std::cmp::Ordering::Equal));
701        } else {
702            sorted.sort_by(|a, b| b.partial_cmp(a).unwrap_or(std::cmp::Ordering::Equal));
703        }
704        let threshold = sorted[n.min(sorted.len()) - 1];
705
706        for &(r1, c1, r2, c2) in ranges {
707            for row in r1..=r2 {
708                for col in c1..=c2 {
709                    if let Ok(CellValue::Number(v)) = self.get_cell_value_by_index(sheet, row, col)
710                    {
711                        let matches = if bottom {
712                            v <= threshold
713                        } else {
714                            v >= threshold
715                        };
716                        if matches {
717                            self.update_cf_cache(
718                                sheet,
719                                row,
720                                col,
721                                CfCellResult::Dxf(dxf_id),
722                                stop_if_true,
723                            );
724                        }
725                    }
726                }
727            }
728        }
729    }
730
731    fn apply_cf_text(
732        &mut self,
733        sheet: u32,
734        operator: &TextOperator,
735        value: &str,
736        dxf_id: u32,
737        stop_if_true: bool,
738        ranges: &[(i32, i32, i32, i32)],
739    ) {
740        let search = value.to_lowercase();
741        for &(r1, c1, r2, c2) in ranges {
742            for row in r1..=r2 {
743                for col in c1..=c2 {
744                    if let Ok(CellValue::String(s)) = self.get_cell_value_by_index(sheet, row, col)
745                    {
746                        let cell_lower = s.to_lowercase();
747                        let matches = match operator {
748                            TextOperator::Contains => cell_lower.contains(search.as_str()),
749                            TextOperator::DoesNotContain => !cell_lower.contains(search.as_str()),
750                            TextOperator::BeginsWith => cell_lower.starts_with(search.as_str()),
751                            TextOperator::EndsWith => cell_lower.ends_with(search.as_str()),
752                            TextOperator::Equals => cell_lower == search.as_str(),
753                        };
754                        if matches {
755                            self.update_cf_cache(
756                                sheet,
757                                row,
758                                col,
759                                CfCellResult::Dxf(dxf_id),
760                                stop_if_true,
761                            );
762                        }
763                    }
764                }
765            }
766        }
767    }
768
769    /// Evaluates a boolean CF formula rule across the given ranges.
770    ///
771    /// The formula is parsed once using the top-left cell of the first range as the parse
772    /// anchor.
773    fn apply_cf_formula(
774        &mut self,
775        sheet: u32,
776        formula: &str,
777        dxf_id: u32,
778        stop_if_true: bool,
779        ranges: &[(i32, i32, i32, i32)],
780    ) {
781        // The parse anchor is the top-left of the bounding box of *all* areas:
782        // the minimum row and minimum column across every range. For a
783        // single-area rule this is just its top-left, but for a multiple-areas
784        // rule like "D4:D8 B10:D10" the anchor is B4 (min row 4, min col 2),
785        // not the first area's top-left (D4).
786        let Some((anchor_row, anchor_col)) = ranges
787            .iter()
788            .map(|&(r1, c1, _, _)| (r1, c1))
789            .reduce(|(r, c), (r1, c1)| (r.min(r1), c.min(c1)))
790        else {
791            return;
792        };
793        let body = formula.trim().strip_prefix('=').unwrap_or(formula.trim());
794        if body.is_empty() {
795            return;
796        }
797        let sheet_name = match self.workbook.worksheets.get(sheet as usize) {
798            Some(ws) => ws.get_name(),
799            None => return,
800        };
801        let context_rc = CellReferenceRC {
802            sheet: sheet_name,
803            row: anchor_row,
804            column: anchor_col,
805        };
806        // CF formulas are stored internally in English.
807        let node = self.parse_internal_formula(body, &context_rc);
808
809        for &(r1, c1, r2, c2) in ranges {
810            for row in r1..=r2 {
811                for column in c1..=c2 {
812                    let ctx = CellReferenceIndex { sheet, row, column };
813                    let matches = match self.evaluate_node_in_context(&node, ctx) {
814                        CalcResult::Number(n) => n != 0.0,
815                        CalcResult::Boolean(b) => b,
816                        _ => false,
817                    };
818                    if matches {
819                        self.update_cf_cache(
820                            sheet,
821                            row,
822                            column,
823                            CfCellResult::Dxf(dxf_id),
824                            stop_if_true,
825                        );
826                    }
827                }
828            }
829        }
830    }
831
832    fn apply_cf_unique_values(
833        &mut self,
834        sheet: u32,
835        dxf_id: u32,
836        stop_if_true: bool,
837        ranges: &[(i32, i32, i32, i32)],
838    ) {
839        let mut counts: HashMap<String, u32> = HashMap::new();
840        for &(r1, c1, r2, c2) in ranges {
841            for row in r1..=r2 {
842                for col in c1..=c2 {
843                    if let Ok(v) = self.get_cell_value_by_index(sheet, row, col) {
844                        if let Some(k) = cell_value_key(&v) {
845                            *counts.entry(k).or_insert(0) += 1;
846                        }
847                    }
848                }
849            }
850        }
851        for &(r1, c1, r2, c2) in ranges {
852            for row in r1..=r2 {
853                for col in c1..=c2 {
854                    if let Ok(v) = self.get_cell_value_by_index(sheet, row, col) {
855                        if let Some(k) = cell_value_key(&v) {
856                            if counts.get(&k).copied().unwrap_or(0) == 1 {
857                                self.update_cf_cache(
858                                    sheet,
859                                    row,
860                                    col,
861                                    CfCellResult::Dxf(dxf_id),
862                                    stop_if_true,
863                                );
864                            }
865                        }
866                    }
867                }
868            }
869        }
870    }
871
872    #[allow(clippy::too_many_arguments)]
873    fn apply_cf_time_period(
874        &mut self,
875        sheet: u32,
876        period: &PeriodType,
877        date1: Option<&str>,
878        date2: Option<&str>,
879        dxf_id: u32,
880        stop_if_true: bool,
881        ranges: &[(i32, i32, i32, i32)],
882    ) {
883        let today_serial = match crate::tz::excel_serial_for_now(&self.tz) {
884            Some(s) => s.floor() as i64,
885            None => return,
886        };
887        let today = match from_excel_date(today_serial) {
888            Ok(d) => d,
889            Err(_) => return,
890        };
891
892        let serial_of = |d: NaiveDate| -> f64 {
893            date_to_serial_number(d.day(), d.month(), d.year()).unwrap_or(0) as f64
894        };
895
896        let range: (f64, f64) = match period {
897            PeriodType::Yesterday => {
898                let s = today_serial as f64 - 1.0;
899                (s, s)
900            }
901            PeriodType::Today => (today_serial as f64, today_serial as f64),
902            PeriodType::Tomorrow => {
903                let s = today_serial as f64 + 1.0;
904                (s, s)
905            }
906            PeriodType::Last7Days => (today_serial as f64 - 6.0, today_serial as f64),
907            PeriodType::Next7Days => (today_serial as f64, today_serial as f64 + 6.0),
908            PeriodType::LastWeek => {
909                let dow = today.weekday().num_days_from_monday() as i64;
910                let this_mon = today - Duration::days(dow);
911                let last_mon = this_mon - Duration::days(7);
912                let last_sun = this_mon - Duration::days(1);
913                (serial_of(last_mon), serial_of(last_sun))
914            }
915            PeriodType::ThisWeek => {
916                let dow = today.weekday().num_days_from_monday() as i64;
917                let this_mon = today - Duration::days(dow);
918                let this_sun = this_mon + Duration::days(6);
919                (serial_of(this_mon), serial_of(this_sun))
920            }
921            PeriodType::NextWeek => {
922                let dow = today.weekday().num_days_from_monday() as i64;
923                let next_mon = today - Duration::days(dow) + Duration::days(7);
924                let next_sun = next_mon + Duration::days(6);
925                (serial_of(next_mon), serial_of(next_sun))
926            }
927            PeriodType::LastMonth => {
928                let Some(this_month_start) =
929                    NaiveDate::from_ymd_opt(today.year(), today.month(), 1)
930                else {
931                    return;
932                };
933                let last_month_end = this_month_start - Duration::days(1);
934                let Some(last_month_start) =
935                    NaiveDate::from_ymd_opt(last_month_end.year(), last_month_end.month(), 1)
936                else {
937                    return;
938                };
939                (serial_of(last_month_start), serial_of(last_month_end))
940            }
941            PeriodType::ThisMonth => {
942                let Some(start) = NaiveDate::from_ymd_opt(today.year(), today.month(), 1) else {
943                    return;
944                };
945                let end = start + Months::new(1) - Duration::days(1);
946                (serial_of(start), serial_of(end))
947            }
948            PeriodType::NextMonth => {
949                let Some(this_start) = NaiveDate::from_ymd_opt(today.year(), today.month(), 1)
950                else {
951                    return;
952                };
953                let next_start = this_start + Months::new(1);
954                let next_end = next_start + Months::new(1) - Duration::days(1);
955                (serial_of(next_start), serial_of(next_end))
956            }
957            PeriodType::LastYear => {
958                let y = today.year() - 1;
959                let Some(start) = NaiveDate::from_ymd_opt(y, 1, 1) else {
960                    return;
961                };
962                let Some(end) = NaiveDate::from_ymd_opt(y, 12, 31) else {
963                    return;
964                };
965                (serial_of(start), serial_of(end))
966            }
967            PeriodType::ThisYear => {
968                let y = today.year();
969                let Some(start) = NaiveDate::from_ymd_opt(y, 1, 1) else {
970                    return;
971                };
972                let Some(end) = NaiveDate::from_ymd_opt(y, 12, 31) else {
973                    return;
974                };
975                (serial_of(start), serial_of(end))
976            }
977            PeriodType::NextYear => {
978                let y = today.year() + 1;
979                let Some(start) = NaiveDate::from_ymd_opt(y, 1, 1) else {
980                    return;
981                };
982                let Some(end) = NaiveDate::from_ymd_opt(y, 12, 31) else {
983                    return;
984                };
985                (serial_of(start), serial_of(end))
986            }
987            PeriodType::Between | PeriodType::NotBetween => {
988                let (Some(d1), Some(d2)) = (date1, date2) else {
989                    return;
990                };
991                let (Some(s1), Some(s2)) = (parse_cf_date_bound(d1), parse_cf_date_bound(d2))
992                else {
993                    return;
994                };
995                (s1.min(s2), s1.max(s2))
996            }
997        };
998        let negate = matches!(period, PeriodType::NotBetween);
999
1000        for &(r1, c1, r2, c2) in ranges {
1001            for row in r1..=r2 {
1002                for col in c1..=c2 {
1003                    if let Ok(CellValue::Number(v)) = self.get_cell_value_by_index(sheet, row, col)
1004                    {
1005                        let day = v.floor();
1006                        let inside = day >= range.0 && day <= range.1;
1007                        if inside != negate {
1008                            self.update_cf_cache(
1009                                sheet,
1010                                row,
1011                                col,
1012                                CfCellResult::Dxf(dxf_id),
1013                                stop_if_true,
1014                            );
1015                        }
1016                    }
1017                }
1018            }
1019        }
1020    }
1021
1022    fn apply_cf_duplicate_values(
1023        &mut self,
1024        sheet: u32,
1025        dxf_id: u32,
1026        stop_if_true: bool,
1027        ranges: &[(i32, i32, i32, i32)],
1028    ) {
1029        let mut counts: HashMap<String, u32> = HashMap::new();
1030        for &(r1, c1, r2, c2) in ranges {
1031            for row in r1..=r2 {
1032                for col in c1..=c2 {
1033                    if let Ok(v) = self.get_cell_value_by_index(sheet, row, col) {
1034                        let key = cell_value_key(&v);
1035                        if let Some(k) = key {
1036                            *counts.entry(k).or_insert(0) += 1;
1037                        }
1038                    }
1039                }
1040            }
1041        }
1042        for &(r1, c1, r2, c2) in ranges {
1043            for row in r1..=r2 {
1044                for col in c1..=c2 {
1045                    if let Ok(v) = self.get_cell_value_by_index(sheet, row, col) {
1046                        if let Some(k) = cell_value_key(&v) {
1047                            if counts.get(&k).copied().unwrap_or(0) > 1 {
1048                                self.update_cf_cache(
1049                                    sheet,
1050                                    row,
1051                                    col,
1052                                    CfCellResult::Dxf(dxf_id),
1053                                    stop_if_true,
1054                                );
1055                            }
1056                        }
1057                    }
1058                }
1059            }
1060        }
1061    }
1062
1063    fn apply_cf_blanks(
1064        &mut self,
1065        sheet: u32,
1066        dxf_id: u32,
1067        invert: bool,
1068        stop_if_true: bool,
1069        ranges: &[(i32, i32, i32, i32)],
1070    ) {
1071        for &(r1, c1, r2, c2) in ranges {
1072            for row in r1..=r2 {
1073                for col in c1..=c2 {
1074                    let is_blank = matches!(
1075                        self.get_cell_value_by_index(sheet, row, col),
1076                        Ok(CellValue::None)
1077                    );
1078                    if is_blank != invert {
1079                        self.update_cf_cache(
1080                            sheet,
1081                            row,
1082                            col,
1083                            CfCellResult::Dxf(dxf_id),
1084                            stop_if_true,
1085                        );
1086                    }
1087                }
1088            }
1089        }
1090    }
1091
1092    fn apply_cf_errors(
1093        &mut self,
1094        sheet: u32,
1095        dxf_id: u32,
1096        invert: bool,
1097        stop_if_true: bool,
1098        ranges: &[(i32, i32, i32, i32)],
1099    ) {
1100        use crate::types::CellType;
1101        for &(r1, c1, r2, c2) in ranges {
1102            for row in r1..=r2 {
1103                for col in c1..=c2 {
1104                    let is_error = self
1105                        .workbook
1106                        .worksheet(sheet)
1107                        .ok()
1108                        .and_then(|ws| ws.cell(row, col))
1109                        .is_some_and(|c| c.get_type() == CellType::ErrorValue);
1110                    if is_error != invert {
1111                        self.update_cf_cache(
1112                            sheet,
1113                            row,
1114                            col,
1115                            CfCellResult::Dxf(dxf_id),
1116                            stop_if_true,
1117                        );
1118                    }
1119                }
1120            }
1121        }
1122    }
1123
1124    // -----------------------------------------------------------------------
1125
1126    /// Returns the extended cell style for (`sheet`, `row`, `column`), including
1127    /// any conditional-formatting overlay computed by the last evaluate() call.
1128    pub fn get_extended_style_for_cell(
1129        &self,
1130        sheet: u32,
1131        row: i32,
1132        column: i32,
1133    ) -> Result<ExtendedStyle, String> {
1134        let base = self.get_style_for_cell(sheet, row, column)?;
1135        let extended = match self.cf_cache.get(&(sheet, row, column)) {
1136            Some(c) => c,
1137            None => {
1138                return Ok(ExtendedStyle {
1139                    style: base,
1140                    icon: None,
1141                    data_bar: None,
1142                    rating: None,
1143                })
1144            }
1145        };
1146        let mut style = base;
1147        let mut icon = None;
1148        let mut data_bar = None;
1149        let mut rating = None;
1150        // `extended` contains the cached conditional-formatting results for this cell in the
1151        // order produced by the evaluator. Dxf results accumulate as we iterate; for exclusive
1152        // types (ColorScale, DataBar, Icon, Rating), the last assignment in this evaluated
1153        // result order wins.
1154        for cf_result in extended {
1155            match cf_result {
1156                CfCellResult::Dxf(dxf_id) => {
1157                    if let Some(dxf) = self.workbook.styles.dxfs.get(*dxf_id as usize) {
1158                        style = dxf.apply_to(&style);
1159                    }
1160                }
1161                CfCellResult::ColorScale(color) => {
1162                    style.fill.color = color.clone();
1163                }
1164                CfCellResult::DataBar {
1165                    positive_color,
1166                    negative_color,
1167                    is_gradient,
1168                    value,
1169                    axis_position,
1170                    show_value,
1171                } => {
1172                    data_bar = Some(CfDataBar {
1173                        positive_color: positive_color.clone(),
1174                        negative_color: negative_color.clone(),
1175                        is_gradient: *is_gradient,
1176                        value: *value,
1177                        axis_position: *axis_position,
1178                        show_value: *show_value,
1179                    });
1180                }
1181                CfCellResult::Icon {
1182                    icon: cf_icon,
1183                    color,
1184                    show_value,
1185                } => {
1186                    icon = Some(CfIcon {
1187                        icon: cf_icon.clone(),
1188                        color: color.clone(),
1189                        show_value: *show_value,
1190                    });
1191                }
1192                CfCellResult::Rating {
1193                    icon: cf_icon,
1194                    count,
1195                    max,
1196                    color,
1197                    show_value,
1198                } => {
1199                    rating = Some(CfRating {
1200                        icon: cf_icon.clone(),
1201                        count: *count,
1202                        max: *max,
1203                        color: color.clone(),
1204                        show_value: *show_value,
1205                    });
1206                }
1207            }
1208        }
1209        Ok(ExtendedStyle {
1210            style,
1211            icon,
1212            data_bar,
1213            rating,
1214        })
1215    }
1216
1217    // -----------------------------------------------------------------------
1218    // CRUD API for conditional formatting rules
1219    // -----------------------------------------------------------------------
1220
1221    /// Appends `dxf` to the workbook's dxf table and returns its new index.
1222    fn create_dxf(&mut self, dxf: Dxf) -> u32 {
1223        let id = self.workbook.styles.dxfs.len() as u32;
1224        self.workbook.styles.dxfs.push(dxf);
1225        id
1226    }
1227
1228    /// Converts a `CfRuleInput` into a stored `CfRule`, creating a dxf entry when a format is provided.
1229    fn cf_rule_from_input(&mut self, rule: CfRuleInput) -> CfRule {
1230        match rule {
1231            CfRuleInput::ColorScale { thresholds } => CfRule::ColorScale { thresholds },
1232            CfRuleInput::CellIs {
1233                operator,
1234                formula,
1235                formula2,
1236                format,
1237                stop_if_true,
1238            } => CfRule::CellIs {
1239                operator,
1240                formula,
1241                formula2,
1242                dxf_id: self.create_dxf(format),
1243                stop_if_true,
1244            },
1245            CfRuleInput::Text {
1246                operator,
1247                value,
1248                format,
1249                stop_if_true,
1250            } => CfRule::Text {
1251                operator,
1252                value,
1253                dxf_id: self.create_dxf(format),
1254                stop_if_true,
1255            },
1256            CfRuleInput::Formula {
1257                formula,
1258                format,
1259                stop_if_true,
1260            } => CfRule::Formula {
1261                formula,
1262                dxf_id: self.create_dxf(format),
1263                stop_if_true,
1264            },
1265            CfRuleInput::TimePeriod {
1266                time_period,
1267                date1,
1268                date2,
1269                format,
1270                stop_if_true,
1271            } => CfRule::TimePeriod {
1272                time_period,
1273                date1,
1274                date2,
1275                dxf_id: self.create_dxf(format),
1276                stop_if_true,
1277            },
1278            CfRuleInput::DuplicateValues {
1279                format,
1280                stop_if_true,
1281            } => CfRule::DuplicateValues {
1282                dxf_id: self.create_dxf(format),
1283                stop_if_true,
1284            },
1285            CfRuleInput::UniqueValues {
1286                format,
1287                stop_if_true,
1288            } => CfRule::UniqueValues {
1289                dxf_id: self.create_dxf(format),
1290                stop_if_true,
1291            },
1292            CfRuleInput::Blanks {
1293                format,
1294                stop_if_true,
1295            } => CfRule::Blanks {
1296                dxf_id: self.create_dxf(format),
1297                stop_if_true,
1298            },
1299            CfRuleInput::NotBlanks {
1300                format,
1301                stop_if_true,
1302            } => CfRule::NotBlanks {
1303                dxf_id: self.create_dxf(format),
1304                stop_if_true,
1305            },
1306            CfRuleInput::Errors {
1307                format,
1308                stop_if_true,
1309            } => CfRule::Errors {
1310                dxf_id: self.create_dxf(format),
1311                stop_if_true,
1312            },
1313            CfRuleInput::NoErrors {
1314                format,
1315                stop_if_true,
1316            } => CfRule::NoErrors {
1317                dxf_id: self.create_dxf(format),
1318                stop_if_true,
1319            },
1320            CfRuleInput::AboveAverage {
1321                format,
1322                stop_if_true,
1323            } => CfRule::AboveAverage {
1324                dxf_id: self.create_dxf(format),
1325                stop_if_true,
1326            },
1327            CfRuleInput::BelowAverage {
1328                format,
1329                stop_if_true,
1330            } => CfRule::BelowAverage {
1331                dxf_id: self.create_dxf(format),
1332                stop_if_true,
1333            },
1334            CfRuleInput::Top10 {
1335                rank,
1336                percent,
1337                format,
1338                stop_if_true,
1339            } => CfRule::Top10 {
1340                rank,
1341                percent,
1342                dxf_id: self.create_dxf(format),
1343                stop_if_true,
1344            },
1345            CfRuleInput::Bottom10 {
1346                rank,
1347                percent,
1348                format,
1349                stop_if_true,
1350            } => CfRule::Bottom10 {
1351                rank,
1352                percent,
1353                dxf_id: self.create_dxf(format),
1354                stop_if_true,
1355            },
1356            CfRuleInput::DataBar {
1357                min,
1358                max,
1359                positive_color,
1360                negative_color,
1361                is_gradient,
1362                show_value,
1363            } => CfRule::DataBar {
1364                min,
1365                max,
1366                positive_color,
1367                negative_color,
1368                is_gradient,
1369                show_value,
1370            },
1371            CfRuleInput::IconSet {
1372                thresholds,
1373                show_value,
1374            } => CfRule::IconSet {
1375                thresholds,
1376                show_value,
1377            },
1378            CfRuleInput::IconRating {
1379                icon,
1380                color,
1381                thresholds,
1382                show_value,
1383            } => CfRule::IconRating {
1384                icon,
1385                color,
1386                thresholds,
1387                show_value,
1388            },
1389        }
1390    }
1391
1392    /// The anchor used to parse/stringify a conditional-formatting formula on
1393    /// `sheet`. References round trip identically regardless of the anchor, so a
1394    /// fixed A1 anchor is enough.
1395    fn cf_formula_context(&self, sheet: u32) -> CellReferenceRC {
1396        let sheet_name = self
1397            .workbook
1398            .worksheets
1399            .get(sheet as usize)
1400            .map(|w| w.get_name())
1401            .unwrap_or_default();
1402        CellReferenceRC {
1403            sheet: sheet_name,
1404            row: 1,
1405            column: 1,
1406        }
1407    }
1408
1409    /// Translates every formula in a CF rule input from the active
1410    /// language/locale into the internal English representation that is stored.
1411    ///
1412    /// This runs before the rule is materialized (and before any dxf entry is
1413    /// created), so an invalid formula fails the whole operation without leaving
1414    /// behind an orphan dxf or storing a non-canonical formula that would later
1415    /// fail to parse as English.
1416    fn cf_rule_input_to_internal(
1417        &mut self,
1418        rule: &mut CfRuleInput,
1419        sheet: u32,
1420    ) -> Result<(), String> {
1421        let ctx = self.cf_formula_context(sheet);
1422        match rule {
1423            CfRuleInput::CellIs {
1424                formula, formula2, ..
1425            } => {
1426                *formula = self.user_formula_to_internal(formula, &ctx)?;
1427                if let Some(f2) = formula2 {
1428                    *f2 = self.user_formula_to_internal(f2, &ctx)?;
1429                }
1430            }
1431            CfRuleInput::Formula { formula, .. } => {
1432                *formula = self.user_formula_to_internal(formula, &ctx)?;
1433            }
1434            CfRuleInput::ColorScale { thresholds } => {
1435                for t in thresholds {
1436                    self.cfvo_to_internal(&mut t.cfvo, &ctx)?;
1437                }
1438            }
1439            CfRuleInput::DataBar { min, max, .. } => {
1440                if let Some(min) = min {
1441                    self.cfvo_to_internal(min, &ctx)?;
1442                }
1443                if let Some(max) = max {
1444                    self.cfvo_to_internal(max, &ctx)?;
1445                }
1446            }
1447            CfRuleInput::IconSet { thresholds, .. } => {
1448                for t in thresholds {
1449                    self.cfvo_to_internal(&mut t.cfvo, &ctx)?;
1450                }
1451            }
1452            CfRuleInput::IconRating { thresholds, .. } => {
1453                for (cfvo, _) in thresholds {
1454                    self.cfvo_to_internal(cfvo, &ctx)?;
1455                }
1456            }
1457            // Remaining rule kinds carry no formula strings.
1458            _ => {}
1459        }
1460        Ok(())
1461    }
1462
1463    fn cfvo_to_internal(&mut self, cfvo: &mut Cfvo, ctx: &CellReferenceRC) -> Result<(), String> {
1464        if let Cfvo::Formula(f) = cfvo {
1465            *f = self.user_formula_to_internal(f, ctx)?;
1466        }
1467        Ok(())
1468    }
1469
1470    /// Translates every formula in a CF rule from the internal English
1471    /// representation into the active language/locale for display.
1472    fn cf_rule_to_display(&self, rule: &mut CfRule, sheet: u32) {
1473        let ctx = self.cf_formula_context(sheet);
1474        match rule {
1475            CfRule::CellIs {
1476                formula, formula2, ..
1477            } => {
1478                *formula = self.internal_formula_to_display(formula, &ctx);
1479                if let Some(f2) = formula2 {
1480                    *f2 = self.internal_formula_to_display(f2, &ctx);
1481                }
1482            }
1483            CfRule::Formula { formula, .. } => {
1484                *formula = self.internal_formula_to_display(formula, &ctx);
1485            }
1486            CfRule::ColorScale { thresholds } => {
1487                for t in thresholds {
1488                    self.cfvo_to_display(&mut t.cfvo, &ctx);
1489                }
1490            }
1491            CfRule::DataBar { min, max, .. } => {
1492                if let Some(min) = min {
1493                    self.cfvo_to_display(min, &ctx);
1494                }
1495                if let Some(max) = max {
1496                    self.cfvo_to_display(max, &ctx);
1497                }
1498            }
1499            CfRule::IconSet { thresholds, .. } => {
1500                for t in thresholds {
1501                    self.cfvo_to_display(&mut t.cfvo, &ctx);
1502                }
1503            }
1504            CfRule::IconRating { thresholds, .. } => {
1505                for (cfvo, _) in thresholds {
1506                    self.cfvo_to_display(cfvo, &ctx);
1507                }
1508            }
1509            _ => {}
1510        }
1511    }
1512
1513    fn cfvo_to_display(&self, cfvo: &mut Cfvo, ctx: &CellReferenceRC) {
1514        if let Cfvo::Formula(f) = cfvo {
1515            *f = self.internal_formula_to_display(f, ctx);
1516        }
1517    }
1518
1519    /// Returns all CF rules for the given sheet, sorted by priority descending
1520    /// (highest priority first).
1521    ///
1522    /// Each entry carries its `index` into the worksheet's stored
1523    /// `conditional_formatting` vector so callers can pass it back to the
1524    /// index-based mutators ([`Self::get_dxf_for_conditional_formatting`],
1525    /// [`Self::update_conditional_formatting`], [`Self::delete_conditional_formatting`],
1526    /// [`Self::raise_conditional_formatting_priority`],
1527    /// [`Self::lower_conditional_formatting_priority`]) without having to infer the
1528    /// index from the display position.
1529    ///
1530    /// Formulas are stored internally in English; they are translated into the
1531    /// active language/locale for display.
1532    pub fn get_conditional_formatting_list(
1533        &self,
1534        sheet: u32,
1535    ) -> Result<Vec<ConditionalFormattingView>, String> {
1536        let mut list: Vec<(usize, ConditionalFormatting)> = self
1537            .workbook
1538            .worksheet(sheet)?
1539            .conditional_formatting
1540            .iter()
1541            .cloned()
1542            .enumerate()
1543            .collect();
1544        // sort by priority descending (highest first)
1545        list.sort_by_key(|(_, cf)| std::cmp::Reverse(cf.priority));
1546        let mut result = Vec::with_capacity(list.len());
1547        for (index, mut cf) in list {
1548            self.cf_rule_to_display(&mut cf.cf_rule, sheet);
1549            result.push(ConditionalFormattingView {
1550                index,
1551                range: cf.range,
1552                cf_rule: cf.cf_rule,
1553                priority: cf.priority,
1554            });
1555        }
1556        Ok(result)
1557    }
1558
1559    /// Returns the differential format (Dxf) for the CF rule at `index` on `sheet`,
1560    /// or `None` if the rule type has no dxf (e.g. ColorScale, DataBar, IconSet).
1561    pub fn get_dxf_for_conditional_formatting(
1562        &self,
1563        sheet: u32,
1564        index: usize,
1565    ) -> Result<Option<Dxf>, String> {
1566        let ws = self.workbook.worksheet(sheet)?;
1567        let cf = ws
1568            .conditional_formatting
1569            .get(index)
1570            .ok_or_else(|| format!("Conditional formatting index {index} out of bounds"))?;
1571        let dxf_id = match &cf.cf_rule {
1572            CfRule::CellIs { dxf_id, .. }
1573            | CfRule::Text { dxf_id, .. }
1574            | CfRule::TimePeriod { dxf_id, .. }
1575            | CfRule::DuplicateValues { dxf_id, .. }
1576            | CfRule::UniqueValues { dxf_id, .. }
1577            | CfRule::Blanks { dxf_id, .. }
1578            | CfRule::NotBlanks { dxf_id, .. }
1579            | CfRule::Errors { dxf_id, .. }
1580            | CfRule::NoErrors { dxf_id, .. }
1581            | CfRule::AboveAverage { dxf_id, .. }
1582            | CfRule::BelowAverage { dxf_id, .. }
1583            | CfRule::Top10 { dxf_id, .. }
1584            | CfRule::Bottom10 { dxf_id, .. }
1585            | CfRule::Formula { dxf_id, .. } => *dxf_id,
1586            _ => return Ok(None),
1587        };
1588        Ok(self.workbook.styles.dxfs.get(dxf_id as usize).cloned())
1589    }
1590
1591    /// Adds a new CF rule to `sheet`, appended with priority = 1 + current max.
1592    /// Returns the assigned priority.
1593    pub fn add_conditional_formatting(
1594        &mut self,
1595        sheet: u32,
1596        range: &str,
1597        rule: CfRuleInput,
1598    ) -> Result<u32, String> {
1599        if parse_sqref(range).is_empty() {
1600            return Err(format!("Invalid conditional formatting range: '{range}'"));
1601        }
1602        // Formulas are stored internally in English regardless of the user's
1603        // active language. Translate before materializing the rule so an invalid
1604        // formula fails without creating an orphan dxf.
1605        let mut rule = rule;
1606        self.cf_rule_input_to_internal(&mut rule, sheet)?;
1607        let final_rule = self.cf_rule_from_input(rule);
1608        let ws = self.workbook.worksheet_mut(sheet)?;
1609        let priority = ws
1610            .conditional_formatting
1611            .iter()
1612            .map(|cf| cf.priority)
1613            .max()
1614            .map(|m| m + 1)
1615            .unwrap_or(1);
1616        ws.conditional_formatting.push(ConditionalFormatting {
1617            range: range.to_string(),
1618            cf_rule: final_rule,
1619            priority,
1620        });
1621        Ok(priority)
1622    }
1623
1624    /// Removes the CF rule at `index` from `sheet`.  Returns the removed rule.
1625    pub fn delete_conditional_formatting(
1626        &mut self,
1627        sheet: u32,
1628        index: usize,
1629    ) -> Result<crate::cf_types::ConditionalFormatting, String> {
1630        let ws = self.workbook.worksheet_mut(sheet)?;
1631        if index >= ws.conditional_formatting.len() {
1632            return Err(format!(
1633                "Conditional formatting index {index} out of bounds"
1634            ));
1635        }
1636        Ok(ws.conditional_formatting.remove(index))
1637    }
1638
1639    /// Replaces the range and rule of the CF entry at `index` on `sheet`.
1640    /// The priority is preserved.  Returns the previous entry.
1641    pub fn update_conditional_formatting(
1642        &mut self,
1643        sheet: u32,
1644        index: usize,
1645        new_range: &str,
1646        new_rule: CfRuleInput,
1647    ) -> Result<ConditionalFormatting, String> {
1648        if parse_sqref(new_range).is_empty() {
1649            return Err(format!(
1650                "Invalid conditional formatting range: '{new_range}'"
1651            ));
1652        }
1653        // Formulas are stored internally in English regardless of the user's
1654        // active language. Translate before materializing the rule so an invalid
1655        // formula fails without creating an orphan dxf.
1656        let mut new_rule = new_rule;
1657        self.cf_rule_input_to_internal(&mut new_rule, sheet)?;
1658        let final_rule = self.cf_rule_from_input(new_rule);
1659        let ws = self.workbook.worksheet_mut(sheet)?;
1660        if index >= ws.conditional_formatting.len() {
1661            return Err(format!(
1662                "Conditional formatting index {index} out of bounds"
1663            ));
1664        }
1665        let old = ws.conditional_formatting[index].clone();
1666        ws.conditional_formatting[index].range = new_range.to_string();
1667        ws.conditional_formatting[index].cf_rule = final_rule;
1668        Ok(old)
1669    }
1670
1671    /// Raises the priority of the CF rule at `index` on `sheet`.
1672    ///
1673    /// In IronCalc a higher priority *number* means higher priority. Raising a
1674    /// rule swaps its priority with the rule that has the next-higher priority
1675    /// number (the closest rule above it). If the rule is already the
1676    /// highest-priority one, this is a no-op.
1677    pub fn raise_conditional_formatting_priority(
1678        &mut self,
1679        sheet: u32,
1680        index: usize,
1681    ) -> Result<(), String> {
1682        let ws = self.workbook.worksheet_mut(sheet)?;
1683        if index >= ws.conditional_formatting.len() {
1684            return Err(format!(
1685                "Conditional formatting index {index} out of bounds"
1686            ));
1687        }
1688        let current = ws.conditional_formatting[index].priority;
1689        // The neighbour is the rule with the smallest priority strictly greater
1690        // than `current`.
1691        let neighbour = ws
1692            .conditional_formatting
1693            .iter()
1694            .enumerate()
1695            .filter(|(_, cf)| cf.priority > current)
1696            .min_by_key(|(_, cf)| cf.priority)
1697            .map(|(i, _)| i);
1698        if let Some(j) = neighbour {
1699            let other = ws.conditional_formatting[j].priority;
1700            ws.conditional_formatting[j].priority = current;
1701            ws.conditional_formatting[index].priority = other;
1702        }
1703        Ok(())
1704    }
1705
1706    /// Lowers the priority of the CF rule at `index` on `sheet`.
1707    ///
1708    /// In IronCalc a higher priority *number* means higher priority. Lowering a
1709    /// rule swaps its priority with the rule that has the next-lower priority
1710    /// number (the closest rule below it). If the rule is already the
1711    /// lowest-priority one, this is a no-op.
1712    pub fn lower_conditional_formatting_priority(
1713        &mut self,
1714        sheet: u32,
1715        index: usize,
1716    ) -> Result<(), String> {
1717        let ws = self.workbook.worksheet_mut(sheet)?;
1718        if index >= ws.conditional_formatting.len() {
1719            return Err(format!(
1720                "Conditional formatting index {index} out of bounds"
1721            ));
1722        }
1723        let current = ws.conditional_formatting[index].priority;
1724        // The neighbour is the rule with the largest priority strictly less than
1725        // `current`.
1726        let neighbour = ws
1727            .conditional_formatting
1728            .iter()
1729            .enumerate()
1730            .filter(|(_, cf)| cf.priority < current)
1731            .max_by_key(|(_, cf)| cf.priority)
1732            .map(|(i, _)| i);
1733        if let Some(j) = neighbour {
1734            let other = ws.conditional_formatting[j].priority;
1735            ws.conditional_formatting[j].priority = current;
1736            ws.conditional_formatting[index].priority = other;
1737        }
1738        Ok(())
1739    }
1740
1741    /// Inserts a CF entry at `index` without modifying priority (used for undo/redo).
1742    pub(crate) fn insert_conditional_formatting_at(
1743        &mut self,
1744        sheet: u32,
1745        index: usize,
1746        cf: crate::cf_types::ConditionalFormatting,
1747    ) -> Result<(), String> {
1748        let ws = self.workbook.worksheet_mut(sheet)?;
1749        if index > ws.conditional_formatting.len() {
1750            return Err(format!(
1751                "Conditional formatting index {index} out of bounds"
1752            ));
1753        }
1754        ws.conditional_formatting.insert(index, cf);
1755        Ok(())
1756    }
1757}
ironcalc_base/conditional_formatting.rs

ironcalc_base/
conditional_formatting.rs
