visi_core/core/engine/sheet/
mod.rs

1mod edit;
2mod functions;
3
4pub use edit::get_word_boundaries_from_str;
5
6use serde::{Deserialize, Serialize};
7use std::collections::{HashMap, HashSet, VecDeque};
8use web_time::Instant;
9
10use super::cell::{
11    CellRef, CellType, Dependency, EngineError, EvalError, TextCellRef, generate_unique_id,
12};
13use super::column::DataColumn;
14use super::result_data::ResultData;
15/// Context for evaluating expressions, containing references to other sheets
16#[derive(Default)]
17pub struct Context<'a> {
18    /// Map of sheet names to sheet references for cross-sheet lookups
19    pub sheets: HashMap<String, &'a Sheet>,
20    /// Every pivot table in the workbook, so `GETPIVOTDATA` can resolve a
21    /// rendered pivot's destination cell back to its definition
22    pub pivot_tables: &'a [crate::core::pivot::PivotTable],
23    /// Store sheet order, so `SHEET()` works.
24    /// `sheets` is a HashMap, so this is needed
25    pub sheet_order: Vec<String>,
26}
27
28impl<'a> Context<'a> {
29    /// Create a new empty context
30    pub fn new() -> Self {
31        Self {
32            sheets: HashMap::new(),
33            pivot_tables: &[],
34            sheet_order: Vec::new(),
35        }
36    }
37
38    /// Add a sheet to the context for lookup during evaluation
39    pub fn add_table(&mut self, name: String, sheet: &'a Sheet) {
40        self.sheets.insert(name, sheet);
41    }
42}
43
44enum LetScope<'a> {
45    Empty,
46    Bound {
47        name: &'a str,
48        value: &'a ResultData,
49        parent: &'a LetScope<'a>,
50    },
51}
52
53struct EvalReference {
54    sheet: String,
55    start_row: usize,
56    start_col: usize,
57    end_row: usize,
58    end_col: usize,
59}
60
61impl<'a> LetScope<'a> {
62    fn get(&self, name: &str) -> Option<&ResultData> {
63        match self {
64            LetScope::Empty => None,
65            LetScope::Bound {
66                name: n,
67                value,
68                parent,
69            } => {
70                if n.eq_ignore_ascii_case(name) {
71                    Some(value)
72                } else {
73                    parent.get(name)
74                }
75            }
76        }
77    }
78}
79
80/// Which way a fill or selection extends from its anchor cell.
81#[derive(Debug, Clone, Copy, PartialEq)]
82pub enum Direction {
83    /// No direction; the operation is a no-op.
84    None,
85    /// Toward row 0.
86    Up,
87    /// Toward the last row.
88    Down,
89    /// Toward column 0.
90    Left,
91    /// Toward the last column.
92    Right,
93}
94
95/// A worksheet
96#[derive(Debug, Clone, Serialize, Deserialize)]
97pub struct Sheet {
98    /// Workbook-unique identifier
99    #[serde(default = "generate_unique_id")]
100    pub id: u64,
101    /// Display name, as it appears in a cross-sheet reference.
102    pub name: String,
103    /// The cells, one entry per column. Row `r` of column `c` is
104    /// `columns[c]`'s entry `r`.
105    ///
106    /// Every column has the same number of rows
107    pub(crate) columns: Vec<DataColumn>,
108    /// Excel/OpenXML row heights in point units, aligned with sheet rows.
109    #[serde(default)]
110    pub(crate) row_heights: Vec<Option<f64>>,
111    /// Excel Tables (ListObjects) defined on this sheet.
112    #[serde(default)]
113    pub tables: Vec<crate::core::table::ExcelTable>,
114    /// Forward edges: which cells must be recomputed when a dependency
115    /// changes
116    #[serde(skip, default)]
117    pub dependencies: HashMap<Dependency, HashSet<CellRef>>,
118    /// Reverse edges: what each cell currently reads, so its old edges can be
119    /// dropped when its formula changes
120    #[serde(skip, default)]
121    pub dependencies_rev: HashMap<CellRef, HashSet<Dependency>>,
122    /// Edits made since the last commit, for callers that want to observe or
123    /// replay them.
124    #[serde(skip)]
125    pub uncommitted_actions: Vec<crate::core::SheetAction>,
126    /// Regional locale for date and number parsing.
127    #[serde(default)]
128    pub locale: crate::core::locale::Locale,
129}
130
131/// Arguments for [`Sheet::new`]
132#[derive(Debug, Clone, Serialize, Deserialize)]
133pub struct SheetInit {
134    /// Identifier to use; `None` generates a fresh one.
135    #[serde(default)]
136    pub id: Option<u64>,
137    /// Name to use; `None` means `table_1`.
138    pub name: Option<String>,
139    /// Rows to allocate.
140    pub rows: usize,
141    /// Columns to allocate.
142    pub cols: usize,
143}
144
145impl Default for SheetInit {
146    fn default() -> Self {
147        Self {
148            id: None,
149            name: None,
150            rows: 10,
151            cols: 5,
152        }
153    }
154}
155
156#[derive(Clone, Copy, PartialEq, Eq)]
157enum BlankPolicy {
158    /// Counts as 0 (MULTINOMIAL).
159    Zero,
160    /// Dropped entirely, shifting later elements (SERIESSUM).
161    Skip,
162    /// #VALUE!, like text (LINEST/TREND/GROWTH/LOGEST/MMULT).
163    Reject,
164}
165
166impl Sheet {
167    /// Creates a sheet of `args.rows` x `args.cols` empty cells, every one of
168    /// them queued as a pending edit so the first [`Sheet::commit`] sees them.
169    pub fn new(args: SheetInit) -> Sheet {
170        let SheetInit {
171            id,
172            name,
173            rows,
174            cols,
175        } = args;
176        let sheet_id = id.unwrap_or_else(generate_unique_id);
177        let sheet_name = name.unwrap_or_else(|| "table_1".to_string());
178
179        let mut columns = Vec::with_capacity(cols);
180        for _ in 0..cols {
181            columns.push(DataColumn::new(rows));
182        }
183
184        let mut uncommitted_actions = Vec::new();
185        for c in 0..cols {
186            for r in 0..rows {
187                uncommitted_actions.push(crate::core::SheetAction::SetCellSrc {
188                    sheet_name: sheet_name.clone(),
189                    col: c,
190                    row: r,
191                    src: String::new(),
192                });
193            }
194        }
195
196        Self {
197            id: sheet_id,
198            name: sheet_name,
199            columns,
200            row_heights: vec![None; rows],
201            tables: Vec::new(),
202            dependencies: HashMap::new(),
203            dependencies_rev: HashMap::new(),
204            uncommitted_actions,
205            locale: crate::core::locale::Locale::default(),
206        }
207    }
208
209    /// Rebuilds what serialization drops.
210    pub fn setup_after_deserialization(&mut self) {
211        for col in &mut self.columns {
212            col.rebuild_after_load();
213        }
214        let row_count = self.row_count();
215        self.row_heights.resize(row_count, None);
216        self.mark_all_dirty();
217    }
218
219    pub(crate) fn get_all_sheets_for_compilation(&self, context: Option<&Context>) -> Vec<Sheet> {
220        let mut list = vec![self.clone()];
221        let mut seen = std::collections::HashSet::new();
222        seen.insert(self.id);
223        if let Some(ctx) = context {
224            for sheet in ctx.sheets.values() {
225                if !seen.contains(&sheet.id) {
226                    seen.insert(sheet.id);
227                    list.push((*sheet).clone());
228                }
229            }
230        }
231        list
232    }
233
234    /// Queues every cell for recomputation on the next [`Sheet::commit`].
235    pub fn mark_all_dirty(&mut self) {
236        for col in &mut self.columns {
237            col.dirty_indices.clear();
238            col.dirty_indices.extend(0..col.src.len());
239        }
240    }
241
242    /// Commit all changed src items with a context for sheet lookups
243    pub fn commit(&mut self, context: Option<&Context>) -> Result<HashSet<CellRef>, EngineError> {
244        let mut queue: VecDeque<CellRef> = VecDeque::new();
245        let mut queue_set: HashSet<CellRef> = HashSet::new();
246        let mut updated_cells: HashSet<CellRef> = HashSet::new();
247
248        for (col_idx, col_data) in self.columns.iter_mut().enumerate() {
249            for row_idx in &col_data.dirty_indices {
250                let cell = CellRef::new(*row_idx, col_idx);
251                queue.push_back(cell);
252                queue_set.insert(cell);
253                updated_cells.insert(cell);
254            }
255            col_data.dirty_indices.clear();
256        }
257
258        let initial_queue_len = queue.len();
259        if initial_queue_len == 0 {
260            return Ok(updated_cells);
261        }
262
263        let start_commit = Instant::now();
264        log::info!(
265            "Sheet '{}' commit starting for {} dirty cells",
266            self.name,
267            initial_queue_len
268        );
269        let max_ops = 10000.max(initial_queue_len * 3);
270        let mut ops = 0;
271
272        let mut sheets_for_compilation = self.get_all_sheets_for_compilation(context);
273        let mut last_log_time = Instant::now();
274
275        while let Some(cell_ref) = queue.pop_front() {
276            queue_set.remove(&cell_ref);
277            ops += 1;
278            if ops > max_ops {
279                println!("Circular dependency or too many updates detected");
280                break;
281            }
282
283            if ops % 50000 == 0 {
284                log::info!(
285                    "Sheet '{}' commit progress: {}/{} cells processed ({:.2?})",
286                    self.name,
287                    ops,
288                    initial_queue_len,
289                    last_log_time.elapsed()
290                );
291                last_log_time = Instant::now();
292            }
293
294            let cell_type_hint = self
295                .columns
296                .get(cell_ref.col)
297                .and_then(|c| c.cell_types.get(cell_ref.row).copied())
298                .unwrap_or(CellType::Empty);
299
300            let mut detected_num_format: Option<String> = None;
301            let (result, new_deps, compiled_to_cache, mut final_cell_type) = {
302                let src = self.get_src_str_ref(&cell_ref).unwrap_or("");
303                if !src.starts_with('=') && cell_type_hint == CellType::String {
304                    let val = if src.starts_with('"') && src.ends_with('"') && src.len() >= 2 {
305                        src[1..src.len() - 1].to_string()
306                    } else {
307                        src.to_string()
308                    };
309                    (ResultData::String(val), vec![], None, CellType::String)
310                } else if !src.starts_with('=') {
311                    let (res, c_type) = if let Some(stripped) = src.strip_prefix('\'') {
312                        (ResultData::String(stripped.to_string()), CellType::String)
313                    } else if matches!(
314                        cell_type_hint,
315                        CellType::DateTimeIso | CellType::DurationIso
316                    ) {
317                        (ResultData::String(src.to_string()), cell_type_hint)
318                    } else if cell_type_hint == CellType::DateTime {
319                        if let Ok(f) = src.trim().parse::<f64>() {
320                            (ResultData::Float(f), CellType::DateTime)
321                        } else if let Some((date, format)) =
322                            crate::core::date::parse_date_with_locale(
323                                src.trim_matches(' '),
324                                &self.locale,
325                            )
326                        {
327                            detected_num_format = Some(format.to_format_code());
328                            (
329                                ResultData::Float(crate::core::date::date_to_excel_serial(date)),
330                                CellType::DateTime,
331                            )
332                        } else if let Some(f) = crate::core::date_fn::parse_time_fraction(src) {
333                            (ResultData::Float(f), CellType::DateTime)
334                        } else {
335                            (ResultData::String(src.to_string()), CellType::String)
336                        }
337                    } else if cell_type_hint == CellType::Float {
338                        if let Ok(f) = src.trim().parse::<f64>()
339                            && f.is_finite()
340                        {
341                            (ResultData::Float(f), CellType::Float)
342                        } else {
343                            (ResultData::String(src.to_string()), CellType::String)
344                        }
345                    } else if cell_type_hint == CellType::Int {
346                        if let Ok(i) = src.trim().parse::<i64>() {
347                            (ResultData::Integer(i), CellType::Int)
348                        } else {
349                            (ResultData::String(src.to_string()), CellType::String)
350                        }
351                    } else if cell_type_hint == CellType::Bool {
352                        if src == "1" || src.eq_ignore_ascii_case("true") {
353                            (ResultData::Boolean(true), CellType::Bool)
354                        } else if src == "0" || src.eq_ignore_ascii_case("false") {
355                            (ResultData::Boolean(false), CellType::Bool)
356                        } else {
357                            (ResultData::String(src.to_string()), CellType::String)
358                        }
359                    } else if cell_type_hint == CellType::Error {
360                        (ResultData::Error(src.to_uppercase()), CellType::Error)
361                    } else if src.is_empty() {
362                        (ResultData::None, CellType::Empty)
363                    } else if src.starts_with('"') && src.ends_with('"') && src.len() >= 2 {
364                        (
365                            ResultData::String(src[1..src.len() - 1].to_string()),
366                            CellType::String,
367                        )
368                    } else if let Ok(i) = src.trim().parse::<i64>() {
369                        (ResultData::Integer(i), CellType::Int)
370                    } else if let Ok(f) = src.trim().parse::<f64>()
371                        && f.is_finite()
372                    {
373                        (ResultData::Float(f), CellType::Float)
374                    } else if crate::core::engine::result_data::is_excel_error_code(src) {
375                        (ResultData::Error(src.to_uppercase()), CellType::Error)
376                    } else if src.eq_ignore_ascii_case("true") {
377                        (ResultData::Boolean(true), CellType::Bool)
378                    } else if src.eq_ignore_ascii_case("false") {
379                        (ResultData::Boolean(false), CellType::Bool)
380                    } else if let Some((date, format)) = crate::core::date::parse_date_with_locale(
381                        src.trim_matches(' '),
382                        &self.locale,
383                    ) {
384                        detected_num_format = Some(format.to_format_code());
385                        (
386                            ResultData::Float(crate::core::date::date_to_excel_serial(date)),
387                            CellType::DateTime,
388                        )
389                    } else if let Some(f) = crate::core::date_fn::parse_time_fraction(src) {
390                        (ResultData::Float(f), CellType::DateTime)
391                    } else {
392                        (ResultData::String(src.to_string()), CellType::String)
393                    };
394                    (res, vec![], None, c_type)
395                } else {
396                    let compiled =
397                        crate::core::parser::compile_formula(src, &sheets_for_compilation);
398                    let eval_src =
399                        crate::core::parser::serialize_formula(&compiled, &sheets_for_compilation);
400                    let (res, deps) = match self.eval_with_row(
401                        &eval_src,
402                        context,
403                        Some(cell_ref.row),
404                        Some(cell_ref.col),
405                    ) {
406                        Ok(r) => r,
407                        Err(e) => (ResultData::Error(e.to_string()), vec![]),
408                    };
409                    let final_res = if let ResultData::None = res {
410                        ResultData::Float(0.0)
411                    } else {
412                        res
413                    };
414                    let final_cell_type = match &final_res {
415                        ResultData::None => CellType::Empty,
416                        ResultData::Integer(_) => CellType::Int,
417                        ResultData::Float(_) => CellType::Float,
418                        ResultData::String(_) => CellType::String,
419                        ResultData::Boolean(_) => CellType::Bool,
420                        ResultData::Error(_) => CellType::Error,
421                        ResultData::List(_) | ResultData::Dict(_) => CellType::String,
422                    };
423                    (final_res, deps, Some(compiled), final_cell_type)
424                }
425            };
426
427            if let Some(src_str) = self.get_src_str_ref(&cell_ref)
428                && let Some(stripped) = src_str.strip_prefix('\'')
429            {
430                let stripped_str = stripped.to_string();
431                if let Some(col) = self.columns.get_mut(cell_ref.col)
432                    && cell_ref.row < col.src.len()
433                {
434                    col.src[cell_ref.row] = stripped_str;
435                }
436            }
437
438            if let Some(col) = self.columns.get_mut(cell_ref.col)
439                && cell_ref.row < col.compiled_src.len()
440            {
441                col.compiled_src[cell_ref.row] = compiled_to_cache.unwrap_or_default();
442            }
443
444            if let Some(old_deps) = self.dependencies_rev.remove(&cell_ref) {
445                for provider in old_deps {
446                    if let Some(dependents) = self.dependencies.get_mut(&provider) {
447                        dependents.remove(&cell_ref);
448                    }
449                }
450            }
451
452            if !new_deps.is_empty() {
453                let mut new_deps_set = HashSet::new();
454                for provider in new_deps {
455                    new_deps_set.insert(provider.clone());
456                    self.dependencies
457                        .entry(provider)
458                        .or_default()
459                        .insert(cell_ref);
460                }
461                self.dependencies_rev.insert(cell_ref, new_deps_set);
462            }
463
464            let inherited = if detected_num_format.is_some()
465                || !matches!(&result, ResultData::Float(_) | ResultData::Integer(_))
466            {
467                None
468            } else {
469                self.get_src_str_ref(&cell_ref)
470                    .and_then(|src| src.strip_prefix('='))
471                    .and_then(|body| crate::core::parser::parse_excel_formula(body).ok())
472                    .and_then(|ast| self.inherited_date_format(&ast))
473            };
474            if let Some(code) = detected_num_format.or(inherited) {
475                if matches!(&result, ResultData::Float(_) | ResultData::Integer(_)) {
476                    final_cell_type = CellType::DateTime;
477                }
478                let existing = self
479                    .get_cell_style(cell_ref.row, cell_ref.col)
480                    .and_then(|s| s.num_format.clone());
481                if existing.is_none() {
482                    self.update_cell_style(cell_ref.row, cell_ref.col, |style| {
483                        style.num_format = Some(code);
484                    });
485                }
486            }
487
488            if let Some(col) = self.columns.get_mut(cell_ref.col)
489                && cell_ref.row < col.data.len()
490            {
491                col.cell_types[cell_ref.row] = final_cell_type;
492                col.data.set(cell_ref.row, result.clone());
493                updated_cells.insert(cell_ref);
494            }
495            if let Some(comp_sheet) = sheets_for_compilation
496                .iter_mut()
497                .find(|s| s.name == self.name)
498                && let Some(col) = comp_sheet.columns.get_mut(cell_ref.col)
499                && cell_ref.row < col.data.len()
500            {
501                col.cell_types[cell_ref.row] = final_cell_type;
502                col.data.set(cell_ref.row, result);
503            }
504
505            let local_dep_key = Dependency::Local(cell_ref);
506            if let Some(dependents) = self.dependencies.get(&local_dep_key) {
507                for dependent in dependents {
508                    if !queue_set.contains(dependent) {
509                        queue.push_back(*dependent);
510                        queue_set.insert(*dependent);
511                    }
512                }
513            }
514
515            let local_col_dep_key = Dependency::LocalColumn(cell_ref.col);
516            if let Some(dependents) = self.dependencies.get(&local_col_dep_key) {
517                for dependent in dependents {
518                    if !queue_set.contains(dependent) {
519                        queue.push_back(*dependent);
520                        queue_set.insert(*dependent);
521                    }
522                }
523            }
524        }
525        if initial_queue_len > 0 {
526            log::info!(
527                "Sheet '{}' commit finished. Processed {} cell updates. Total time: {:.2?}",
528                self.name,
529                ops,
530                start_commit.elapsed()
531            );
532        }
533        Ok(updated_cells)
534    }
535
536    /// Evaluates cell source text without storing it, as
537    /// [`Sheet::eval`] does, but from the point of view of `(row, col)`.
538    pub fn eval_with_row(
539        &self,
540        input: &str,
541        context: Option<&Context>,
542        row: Option<usize>,
543        col: Option<usize>,
544    ) -> Result<(ResultData, Vec<Dependency>), EngineError> {
545        if input.is_empty() {
546            return Ok((ResultData::None, vec![]));
547        }
548        if let Some(formula) = input.strip_prefix('=') {
549            self.eval_excel(formula, context, row, col)
550        } else {
551            if let Ok(i) = input.parse::<i64>() {
552                Ok((ResultData::Integer(i), vec![]))
553            } else if let Ok(f) = input.parse::<f64>() {
554                Ok((ResultData::Float(f), vec![]))
555            } else if let Ok(b) = input.parse::<bool>() {
556                Ok((ResultData::Boolean(b), vec![]))
557            } else {
558                Ok((ResultData::String(input.to_string()), vec![]))
559            }
560        }
561    }
562
563    /// Evaluates cell source text against this sheet without storing it,
564    /// returning the value and the references it read.
565    pub fn eval(
566        &self,
567        input: &str,
568        context: Option<&Context>,
569    ) -> Result<(ResultData, Vec<Dependency>), EngineError> {
570        self.eval_with_row(input, context, None, None)
571    }
572
573    fn eval_excel(
574        &self,
575        code: &str,
576        context: Option<&Context>,
577        row: Option<usize>,
578        col: Option<usize>,
579    ) -> Result<(ResultData, Vec<Dependency>), EngineError> {
580        let ast = crate::core::parser::parse_excel_formula(code)
581            .map_err(|e| EngineError::EvalError(EvalError::UnknownFunction(e)))?;
582
583        let mut deps = Vec::new();
584        let result = match self.evaluate_ast(&ast, context, row, col, &mut deps, &LetScope::Empty) {
585            Ok(r) => r,
586            Err(EngineError::EvalError(EvalError::UnknownFunction(err_str)))
587                if err_str.starts_with('#') =>
588            {
589                ResultData::Error(err_str)
590            }
591            Err(e) => return Err(e),
592        };
593        Ok((result, deps))
594    }
595
596    fn reference_sheet_dims(
597        &self,
598        sheet_name: &str,
599        context: Option<&Context>,
600    ) -> Result<(usize, usize), EngineError> {
601        if sheet_name == self.name {
602            Ok((self.row_count(), self.col_count()))
603        } else if let Some(ctx) = context {
604            ctx.sheets
605                .get(sheet_name)
606                .map(|sheet| (sheet.row_count(), sheet.col_count()))
607                .ok_or_else(|| {
608                    EngineError::EvalError(EvalError::UnknownFunction(format!(
609                        "Sheet not found: {}",
610                        sheet_name
611                    )))
612                })
613        } else {
614            Err(EngineError::EvalError(EvalError::UnknownFunction(
615                "No context to resolve sheet reference".to_string(),
616            )))
617        }
618    }
619
620    fn areas_from_expr(
621        &self,
622        expr: &crate::core::parser::Expr,
623        context: Option<&Context>,
624    ) -> Result<Option<Vec<EvalReference>>, EngineError> {
625        use crate::core::parser::Expr;
626        use crate::core::parser::Op;
627
628        match expr {
629            Expr::CellRef {
630                sheet, row, col, ..
631            } => Ok(Some(vec![EvalReference {
632                sheet: sheet.clone().unwrap_or_else(|| self.name.clone()),
633                start_row: *row,
634                start_col: *col,
635                end_row: *row,
636                end_col: *col,
637            }])),
638            Expr::RangeRef {
639                sheet,
640                start_row,
641                start_col,
642                end_row,
643                end_col,
644                ..
645            } => {
646                let sheet_name = sheet.clone().unwrap_or_else(|| self.name.clone());
647                let (row_count, col_count) = self.reference_sheet_dims(&sheet_name, context)?;
648                let actual_end_row = if *end_row == usize::MAX {
649                    row_count.saturating_sub(1)
650                } else {
651                    *end_row
652                };
653                let actual_end_col = if *end_col == usize::MAX {
654                    col_count.saturating_sub(1)
655                } else {
656                    *end_col
657                };
658                Ok(Some(vec![EvalReference {
659                    sheet: sheet_name,
660                    start_row: *start_row,
661                    start_col: *start_col,
662                    end_row: actual_end_row,
663                    end_col: actual_end_col,
664                }]))
665            }
666            Expr::BinaryOp {
667                op: Op::Union,
668                left,
669                right,
670            } => {
671                let Some(mut left_areas) = self.areas_from_expr(left, context)? else {
672                    return Ok(None);
673                };
674                let Some(right_areas) = self.areas_from_expr(right, context)? else {
675                    return Ok(None);
676                };
677                left_areas.extend(right_areas);
678                Ok(Some(left_areas))
679            }
680            Expr::BinaryOp {
681                op: Op::Intersect,
682                left,
683                right,
684            } => {
685                let Some(left_areas) = self.areas_from_expr(left, context)? else {
686                    return Ok(None);
687                };
688                let Some(right_areas) = self.areas_from_expr(right, context)? else {
689                    return Ok(None);
690                };
691                let intersections = Self::intersect_areas(&left_areas, &right_areas);
692                if intersections.is_empty() {
693                    return Err(EngineError::EvalError(EvalError::UnknownFunction(
694                        "#NULL!".to_string(),
695                    )));
696                }
697                Ok(Some(intersections))
698            }
699            _ => Ok(None),
700        }
701    }
702
703    fn intersect_areas(left: &[EvalReference], right: &[EvalReference]) -> Vec<EvalReference> {
704        let mut out = Vec::new();
705        for l in left {
706            for r in right {
707                if l.sheet != r.sheet {
708                    continue;
709                }
710                let start_row = l.start_row.max(r.start_row);
711                let start_col = l.start_col.max(r.start_col);
712                let end_row = l.end_row.min(r.end_row);
713                let end_col = l.end_col.min(r.end_col);
714                if start_row <= end_row && start_col <= end_col {
715                    out.push(EvalReference {
716                        sheet: l.sheet.clone(),
717                        start_row,
718                        start_col,
719                        end_row,
720                        end_col,
721                    });
722                }
723            }
724        }
725        out
726    }
727
728    fn eval_area(
729        &self,
730        area: &EvalReference,
731        context: Option<&Context>,
732        row: Option<usize>,
733        col: Option<usize>,
734        deps: &mut Vec<Dependency>,
735    ) -> Result<ResultData, EngineError> {
736        let sheet = if area.sheet == self.name {
737            None
738        } else {
739            Some(area.sheet.clone())
740        };
741        let expr = if area.start_row == area.end_row && area.start_col == area.end_col {
742            crate::core::parser::Expr::CellRef {
743                sheet,
744                row: area.start_row,
745                col: area.start_col,
746                row_abs: false,
747                col_abs: false,
748            }
749        } else {
750            crate::core::parser::Expr::RangeRef {
751                sheet,
752                start_row: area.start_row,
753                start_col: area.start_col,
754                end_row: area.end_row,
755                end_col: area.end_col,
756                start_row_abs: false,
757                start_col_abs: false,
758                end_row_abs: false,
759                end_col_abs: false,
760            }
761        };
762        self.evaluate_ast(&expr, context, row, col, deps, &LetScope::Empty)
763    }
764
765    fn combine_union_values(left: ResultData, right: ResultData) -> ResultData {
766        match (left, right) {
767            (ResultData::List(mut l), ResultData::List(r)) => {
768                l.extend(r);
769                ResultData::List(l)
770            }
771            (ResultData::List(mut l), r) => {
772                l.push(r);
773                ResultData::List(l)
774            }
775            (l, ResultData::List(mut r)) => {
776                r.insert(0, l);
777                ResultData::List(r)
778            }
779            (l, r) => ResultData::List(vec![l, r]),
780        }
781    }
782
783    fn evaluate_reference_intersection(
784        &self,
785        left: &crate::core::parser::Expr,
786        right: &crate::core::parser::Expr,
787        context: Option<&Context>,
788        row: Option<usize>,
789        col: Option<usize>,
790        deps: &mut Vec<Dependency>,
791    ) -> Result<ResultData, EngineError> {
792        let Some(left_areas) = self.areas_from_expr(left, context)? else {
793            return Ok(ResultData::Error("#VALUE!".to_string()));
794        };
795        let Some(right_areas) = self.areas_from_expr(right, context)? else {
796            return Ok(ResultData::Error("#VALUE!".to_string()));
797        };
798        let intersections = Self::intersect_areas(&left_areas, &right_areas);
799        if intersections.is_empty() {
800            return Ok(ResultData::Error("#NULL!".to_string()));
801        }
802        let mut out = Vec::new();
803        for area in intersections {
804            match self.eval_area(&area, context, row, col, deps)? {
805                ResultData::Error(e) => return Ok(ResultData::Error(e)),
806                ResultData::List(items) => out.extend(items),
807                value => out.push(value),
808            }
809        }
810        if out.len() == 1 {
811            Ok(out.pop().unwrap())
812        } else {
813            Ok(ResultData::List(out))
814        }
815    }
816
817    fn evaluate_implicit_intersection(
818        &self,
819        expr: &crate::core::parser::Expr,
820        context: Option<&Context>,
821        row: Option<usize>,
822        col: Option<usize>,
823        deps: &mut Vec<Dependency>,
824        scope: &LetScope<'_>,
825    ) -> Result<ResultData, EngineError> {
826        if let crate::core::parser::Expr::StructuredRef {
827            sheet,
828            column,
829            is_this_row: false,
830            section,
831        } = expr
832            && matches!(
833                section,
834                crate::core::SheetSection::Data | crate::core::SheetSection::All
835            )
836        {
837            let intersected = crate::core::parser::Expr::StructuredRef {
838                sheet: sheet.clone(),
839                column: column.clone(),
840                is_this_row: true,
841                section: *section,
842            };
843            return self.evaluate_ast(&intersected, context, row, col, deps, scope);
844        }
845
846        if let Some(areas) = self.areas_from_expr(expr, context)? {
847            if areas.len() != 1 {
848                return Ok(ResultData::Error("#VALUE!".to_string()));
849            }
850            let area = &areas[0];
851            let target = if area.start_row == area.end_row && area.start_col == area.end_col {
852                Some((area.start_row, area.start_col))
853            } else if area.start_col == area.end_col {
854                let Some(r) = row else {
855                    return Ok(ResultData::Error("#VALUE!".to_string()));
856                };
857                if r >= area.start_row && r <= area.end_row {
858                    Some((r, area.start_col))
859                } else {
860                    None
861                }
862            } else if area.start_row == area.end_row {
863                let Some(c) = col else {
864                    return Ok(ResultData::Error("#VALUE!".to_string()));
865                };
866                if c >= area.start_col && c <= area.end_col {
867                    Some((area.start_row, c))
868                } else {
869                    None
870                }
871            } else {
872                None
873            };
874            let Some((target_row, target_col)) = target else {
875                return Ok(ResultData::Error("#VALUE!".to_string()));
876            };
877            return self.eval_area(
878                &EvalReference {
879                    sheet: area.sheet.clone(),
880                    start_row: target_row,
881                    start_col: target_col,
882                    end_row: target_row,
883                    end_col: target_col,
884                },
885                context,
886                row,
887                col,
888                deps,
889            );
890        }
891
892        let value = self.evaluate_ast(expr, context, row, col, deps, scope)?;
893        match value {
894            ResultData::List(items) => {
895                let (mut flat, _) = Self::flatten_row_major(items);
896                if flat.is_empty() {
897                    Ok(ResultData::Error("#VALUE!".to_string()))
898                } else {
899                    Ok(flat.remove(0))
900                }
901            }
902            other => Ok(other),
903        }
904    }
905
906    fn evaluate_ast(
907        &self,
908        ast: &crate::core::parser::Expr,
909        context: Option<&Context>,
910        row: Option<usize>,
911        col: Option<usize>,
912        deps: &mut Vec<Dependency>,
913        scope: &LetScope<'_>,
914    ) -> Result<ResultData, EngineError> {
915        use crate::core::SheetSection;
916        use crate::core::parser::Expr;
917        use crate::core::parser::Op;
918
919        match ast {
920            Expr::Number(n) => Ok(ResultData::Float(*n)),
921            Expr::String(s) => Ok(ResultData::String(s.clone())),
922            Expr::Boolean(b) => Ok(ResultData::Boolean(*b)),
923            Expr::Error(code) => Ok(ResultData::Error(code.to_string())),
924            Expr::Identifier(name) => match scope.get(name) {
925                Some(val) => Ok(val.clone()),
926                None => Ok(ResultData::Error("#NAME?".to_string())),
927            },
928            Expr::StructuredRef {
929                sheet,
930                column,
931                is_this_row,
932                section,
933            } => {
934                let ref_name = match sheet {
935                    Some(name) => name.clone(),
936                    None => self.name.clone(),
937                };
938
939                let mut found: Option<(&Sheet, &crate::core::table::ExcelTable)> =
940                    self.find_table(&ref_name).map(|t| (self, t));
941                if found.is_none()
942                    && let Some(ctx) = context
943                {
944                    for s in ctx.sheets.values() {
945                        if let Some(t) = s.find_table(&ref_name) {
946                            found = Some((s, t));
947                            break;
948                        }
949                    }
950                }
951
952                if let Some((table_sheet, excel_table)) = found {
953                    let is_self = table_sheet.name == self.name;
954                    let sheet_name = table_sheet.name.clone();
955
956                    let col_indices: Vec<(usize, usize)> = if let Some(col_name) = column {
957                        let local = excel_table.local_column_index(col_name).ok_or_else(|| {
958                            EngineError::EvalError(EvalError::UnknownFunction(format!(
959                                "Column not found: {}",
960                                col_name
961                            )))
962                        })?;
963                        vec![(local, excel_table.start_col + local)]
964                    } else {
965                        (0..excel_table.columns.len())
966                            .map(|local| (local, excel_table.start_col + local))
967                            .collect()
968                    };
969                    let is_whole_table = column.is_none();
970
971                    match section {
972                        SheetSection::Headers => {
973                            let names: Vec<ResultData> = col_indices
974                                .iter()
975                                .map(|&(local, _)| {
976                                    ResultData::String(
977                                        excel_table.columns.get(local).cloned().unwrap_or_default(),
978                                    )
979                                })
980                                .collect();
981                            if is_whole_table {
982                                Ok(ResultData::List(names))
983                            } else {
984                                Ok(names.into_iter().next().unwrap_or(ResultData::None))
985                            }
986                        }
987                        SheetSection::Totals => {
988                            if let Some(totals_row) = excel_table.totals_row() {
989                                let mut results = Vec::new();
990                                for &(_, col_idx) in &col_indices {
991                                    let cell_ref = CellRef::new(totals_row, col_idx);
992                                    if is_self {
993                                        deps.push(Dependency::Local(cell_ref));
994                                    } else {
995                                        deps.push(Dependency::Remote {
996                                            sheet: sheet_name.clone(),
997                                            cell: cell_ref,
998                                        });
999                                    }
1000                                    results.push(table_sheet.get_result_data(&cell_ref));
1001                                }
1002                                if is_whole_table {
1003                                    Ok(ResultData::List(results))
1004                                } else {
1005                                    Ok(results.into_iter().next().unwrap_or(ResultData::None))
1006                                }
1007                            } else {
1008                                Ok(ResultData::None)
1009                            }
1010                        }
1011                        SheetSection::Data | SheetSection::All => {
1012                            if *is_this_row {
1013                                let r = row.ok_or_else(|| {
1014                                    EngineError::EvalError(EvalError::UnknownFunction(
1015                                        "This row reference cannot be evaluated without row context"
1016                                            .to_string(),
1017                                    ))
1018                                })?;
1019                                let mut results = Vec::new();
1020                                for &(_, col_idx) in &col_indices {
1021                                    let cell_ref = CellRef::new(r, col_idx);
1022                                    if is_self {
1023                                        deps.push(Dependency::Local(cell_ref));
1024                                    } else {
1025                                        deps.push(Dependency::Remote {
1026                                            sheet: sheet_name.clone(),
1027                                            cell: cell_ref,
1028                                        });
1029                                    }
1030                                    results.push(table_sheet.get_result_data(&cell_ref));
1031                                }
1032                                if is_whole_table {
1033                                    Ok(ResultData::List(results))
1034                                } else {
1035                                    Ok(results.into_iter().next().unwrap_or(ResultData::None))
1036                                }
1037                            } else {
1038                                let mut results = Vec::new();
1039                                for &(_, col_idx) in &col_indices {
1040                                    for r in
1041                                        excel_table.data_start_row()..=excel_table.data_end_row()
1042                                    {
1043                                        let cell_ref = CellRef::new(r, col_idx);
1044                                        if is_self {
1045                                            deps.push(Dependency::Local(cell_ref));
1046                                        } else {
1047                                            deps.push(Dependency::Remote {
1048                                                sheet: sheet_name.clone(),
1049                                                cell: cell_ref,
1050                                            });
1051                                        }
1052                                        results.push(table_sheet.get_result_data(&cell_ref));
1053                                    }
1054                                }
1055                                Ok(ResultData::List(results))
1056                            }
1057                        }
1058                    }
1059                } else {
1060                    let sheet_name = ref_name;
1061                    let is_self = sheet_name == self.name;
1062
1063                    let target_sheet = if is_self {
1064                        self
1065                    } else if let Some(ctx) = context {
1066                        if let Some(sheet) = ctx.sheets.get(&sheet_name) {
1067                            sheet
1068                        } else {
1069                            return Err(EngineError::EvalError(EvalError::UnknownFunction(
1070                                format!("Sheet not found: {}", sheet_name),
1071                            )));
1072                        }
1073                    } else {
1074                        return Err(EngineError::EvalError(EvalError::UnknownFunction(format!(
1075                            "No context to resolve sheet reference: {}",
1076                            sheet_name
1077                        ))));
1078                    };
1079
1080                    let col_indices: Vec<usize> = if let Some(col_name) = column {
1081                        let pos = target_sheet
1082                            .columns
1083                            .iter()
1084                            .position(|c| c.name == *col_name)
1085                            .ok_or_else(|| {
1086                                EngineError::EvalError(EvalError::UnknownFunction(format!(
1087                                    "Column not found: {}",
1088                                    col_name
1089                                )))
1090                            })?;
1091                        vec![pos]
1092                    } else {
1093                        (0..target_sheet.columns.len()).collect()
1094                    };
1095                    let is_whole_table = column.is_none();
1096
1097                    match section {
1098                        SheetSection::Headers => {
1099                            let names: Vec<ResultData> = col_indices
1100                                .iter()
1101                                .map(|&idx| {
1102                                    ResultData::String(
1103                                        target_sheet
1104                                            .columns
1105                                            .get(idx)
1106                                            .map(|c| c.name.clone())
1107                                            .unwrap_or_default(),
1108                                    )
1109                                })
1110                                .collect();
1111                            if is_whole_table {
1112                                Ok(ResultData::List(names))
1113                            } else {
1114                                Ok(names.into_iter().next().unwrap_or(ResultData::None))
1115                            }
1116                        }
1117                        SheetSection::Totals => Ok(ResultData::None),
1118                        SheetSection::Data | SheetSection::All => {
1119                            if *is_this_row {
1120                                let r = row.ok_or_else(|| {
1121                                    EngineError::EvalError(EvalError::UnknownFunction(
1122                                        "This row reference cannot be evaluated without row context"
1123                                            .to_string(),
1124                                    ))
1125                                })?;
1126                                let mut results = Vec::new();
1127                                for &col_idx in &col_indices {
1128                                    let cell_ref = CellRef::new(r, col_idx);
1129                                    if is_self {
1130                                        deps.push(Dependency::Local(cell_ref));
1131                                    } else {
1132                                        deps.push(Dependency::Remote {
1133                                            sheet: sheet_name.clone(),
1134                                            cell: cell_ref,
1135                                        });
1136                                    }
1137                                    results.push(target_sheet.get_result_data(&cell_ref));
1138                                }
1139                                if is_whole_table {
1140                                    Ok(ResultData::List(results))
1141                                } else {
1142                                    Ok(results.into_iter().next().unwrap_or(ResultData::None))
1143                                }
1144                            } else {
1145                                let mut results = Vec::new();
1146                                for &col_idx in &col_indices {
1147                                    if is_self {
1148                                        deps.push(Dependency::LocalColumn(col_idx));
1149                                    } else {
1150                                        deps.push(Dependency::RemoteColumn {
1151                                            sheet: sheet_name.clone(),
1152                                            col: col_idx,
1153                                        });
1154                                    }
1155                                    for r in 0..target_sheet.row_count() {
1156                                        let cell_ref = CellRef::new(r, col_idx);
1157                                        results.push(target_sheet.get_result_data(&cell_ref));
1158                                    }
1159                                }
1160                                Ok(ResultData::List(results))
1161                            }
1162                        }
1163                    }
1164                }
1165            }
1166            Expr::CellRef {
1167                sheet,
1168                row: r_val,
1169                col,
1170                ..
1171            } => {
1172                let cell_ref = CellRef::new(*r_val, *col);
1173                let is_self = match sheet {
1174                    Some(name) => name == &self.name,
1175                    None => true,
1176                };
1177
1178                if is_self {
1179                    deps.push(Dependency::Local(cell_ref));
1180                    Ok(self.get_result_data(&cell_ref))
1181                } else {
1182                    let name = sheet.as_ref().unwrap().clone();
1183                    deps.push(Dependency::Remote {
1184                        sheet: name.clone(),
1185                        cell: cell_ref,
1186                    });
1187
1188                    if let Some(ctx) = context {
1189                        if let Some(t) = ctx.sheets.get(&name) {
1190                            Ok(t.get_result_data(&cell_ref))
1191                        } else {
1192                            Err(EngineError::EvalError(EvalError::UnknownFunction(format!(
1193                                "Sheet not found: {}",
1194                                name
1195                            ))))
1196                        }
1197                    } else {
1198                        Err(EngineError::EvalError(EvalError::UnknownFunction(
1199                            "No context to resolve sheet reference".to_string(),
1200                        )))
1201                    }
1202                }
1203            }
1204            Expr::RangeRef {
1205                sheet,
1206                start_row,
1207                start_col,
1208                end_row,
1209                end_col,
1210                ..
1211            } => {
1212                let is_self = match sheet {
1213                    Some(name) => name == &self.name,
1214                    None => true,
1215                };
1216
1217                let target_sheet = if is_self {
1218                    Some(self)
1219                } else {
1220                    context.and_then(|ctx| ctx.sheets.get(sheet.as_ref().unwrap()).copied())
1221                };
1222
1223                let actual_end_row = if *end_row == usize::MAX {
1224                    target_sheet
1225                        .map(|t| t.row_count().saturating_sub(1))
1226                        .unwrap_or(0)
1227                } else {
1228                    *end_row
1229                };
1230                let actual_end_col = if *end_col == usize::MAX {
1231                    target_sheet
1232                        .map(|t| t.col_count().saturating_sub(1))
1233                        .unwrap_or(0)
1234                } else {
1235                    *end_col
1236                };
1237
1238                let is_col_range = *end_row == usize::MAX;
1239
1240                let mut seen_col_deps: HashSet<usize> = HashSet::new();
1241
1242                let mut results = Vec::new();
1243                for r in *start_row..=actual_end_row {
1244                    for c in *start_col..=actual_end_col {
1245                        let cell_ref = CellRef::new(r, c);
1246                        if is_self {
1247                            if is_col_range {
1248                                if seen_col_deps.insert(c) {
1249                                    let col_dep = Dependency::LocalColumn(c);
1250                                    if !deps.contains(&col_dep) {
1251                                        deps.push(col_dep);
1252                                    }
1253                                }
1254                            } else {
1255                                deps.push(Dependency::Local(cell_ref));
1256                            }
1257                            if row == Some(r) && col == Some(c) {
1258                                results.push(ResultData::None);
1259                            } else {
1260                                results.push(self.get_result_data(&cell_ref));
1261                            }
1262                        } else {
1263                            let name = sheet.as_ref().unwrap().clone();
1264                            if is_col_range {
1265                                if seen_col_deps.insert(c) {
1266                                    let col_dep = Dependency::RemoteColumn {
1267                                        sheet: name.clone(),
1268                                        col: c,
1269                                    };
1270                                    if !deps.contains(&col_dep) {
1271                                        deps.push(col_dep);
1272                                    }
1273                                }
1274                            } else {
1275                                deps.push(Dependency::Remote {
1276                                    sheet: name.clone(),
1277                                    cell: cell_ref,
1278                                });
1279                            }
1280                            if let Some(ctx) = context {
1281                                if let Some(t) = ctx.sheets.get(&name) {
1282                                    results.push(t.get_result_data(&cell_ref));
1283                                } else {
1284                                    return Err(EngineError::EvalError(
1285                                        EvalError::UnknownFunction(format!(
1286                                            "Sheet not found: {}",
1287                                            name
1288                                        )),
1289                                    ));
1290                                }
1291                            } else {
1292                                return Err(EngineError::EvalError(EvalError::UnknownFunction(
1293                                    "No context to resolve sheet reference".to_string(),
1294                                )));
1295                            }
1296                        }
1297                    }
1298                }
1299                Ok(ResultData::List(results))
1300            }
1301            Expr::List(list) => {
1302                let mut results = Vec::new();
1303                for item in list {
1304                    results.push(self.evaluate_ast(item, context, row, col, deps, scope)?);
1305                }
1306                Ok(ResultData::List(results))
1307            }
1308            Expr::Slice { expr, start, end } => {
1309                let target_val = self.evaluate_ast(expr, context, row, col, deps, scope)?;
1310                if let ResultData::Error(_) = &target_val {
1311                    return Ok(target_val);
1312                }
1313                if let ResultData::List(list) = target_val {
1314                    let len = list.len() as isize;
1315                    let start_idx = if let Some(start_expr) = start {
1316                        let s_val =
1317                            self.evaluate_ast(start_expr, context, row, col, deps, scope)?;
1318                        if let ResultData::Error(_) = &s_val {
1319                            return Ok(s_val);
1320                        }
1321                        let s = self.to_f64(&s_val).unwrap_or(0.0) as isize;
1322                        if s < 0 {
1323                            (len + s).max(0) as usize
1324                        } else {
1325                            s.min(len) as usize
1326                        }
1327                    } else {
1328                        0
1329                    };
1330
1331                    let end_idx = if let Some(end_expr) = end {
1332                        let e_val = self.evaluate_ast(end_expr, context, row, col, deps, scope)?;
1333                        if let ResultData::Error(_) = &e_val {
1334                            return Ok(e_val);
1335                        }
1336                        let e = self.to_f64(&e_val).unwrap_or(len as f64) as isize;
1337                        if e < 0 {
1338                            (len + e).max(0) as usize
1339                        } else {
1340                            e.min(len) as usize
1341                        }
1342                    } else {
1343                        len as usize
1344                    };
1345
1346                    let sliced = if start_idx < end_idx && start_idx < list.len() {
1347                        list[start_idx..end_idx.min(list.len())].to_vec()
1348                    } else {
1349                        Vec::new()
1350                    };
1351                    Ok(ResultData::List(sliced))
1352                } else {
1353                    Ok(ResultData::None)
1354                }
1355            }
1356            Expr::UnaryOp { op, expr } => {
1357                if matches!(op, Op::ImplicitIntersection) {
1358                    return self
1359                        .evaluate_implicit_intersection(expr, context, row, col, deps, scope);
1360                }
1361                let val = self.evaluate_ast(expr, context, row, col, deps, scope)?;
1362                match op {
1363                    Op::Sub => match val {
1364                        ResultData::Error(_) => Ok(val),
1365                        ResultData::Integer(i) if i != i64::MIN => Ok(ResultData::Integer(-i)),
1366                        _ => match self.to_f64(&val) {
1367                            Some(f) => Ok(ResultData::Float(-f)),
1368                            None => Ok(ResultData::Error("#VALUE!".to_string())),
1369                        },
1370                    },
1371                    Op::Percent => {
1372                        if let ResultData::Error(_) = &val {
1373                            return Ok(val);
1374                        }
1375                        match self.to_f64(&val) {
1376                            Some(f) => Ok(ResultData::Float(f / 100.0)),
1377                            None => Ok(ResultData::Error("#VALUE!".to_string())),
1378                        }
1379                    }
1380                    Op::Spill => match val {
1381                        ResultData::Error(_) => Ok(val),
1382                        ResultData::List(_) => Ok(val),
1383                        _ => {
1384                            if let Expr::CellRef {
1385                                sheet,
1386                                row: r_val,
1387                                col: c_val,
1388                                ..
1389                            } = &**expr
1390                            {
1391                                let is_self = match sheet {
1392                                    Some(name) => name == &self.name,
1393                                    None => true,
1394                                };
1395                                let has_formula = if is_self {
1396                                    self.get_src_str_ref(&CellRef::new(*r_val, *c_val))
1397                                        .is_some_and(|s| s.starts_with('='))
1398                                } else if let Some(ctx) = context {
1399                                    ctx.sheets
1400                                        .get(sheet.as_ref().unwrap())
1401                                        .and_then(|s| {
1402                                            s.get_src_str_ref(&CellRef::new(*r_val, *c_val))
1403                                        })
1404                                        .is_some_and(|s| s.starts_with('='))
1405                                } else {
1406                                    false
1407                                };
1408                                if has_formula {
1409                                    Ok(val)
1410                                } else {
1411                                    Ok(ResultData::Error("#REF!".to_string()))
1412                                }
1413                            } else {
1414                                Ok(val)
1415                            }
1416                        }
1417                    },
1418                    _ => Ok(val),
1419                }
1420            }
1421            Expr::BinaryOp { op, left, right } => {
1422                if matches!(op, Op::Intersect) {
1423                    return self
1424                        .evaluate_reference_intersection(left, right, context, row, col, deps);
1425                }
1426
1427                let l_val = self.evaluate_ast(left, context, row, col, deps, scope)?;
1428
1429                match op {
1430                    Op::Union => {
1431                        if let ResultData::Error(_) = &l_val {
1432                            return Ok(l_val);
1433                        }
1434                        let r_val = self.evaluate_ast(right, context, row, col, deps, scope)?;
1435                        if let ResultData::Error(_) = &r_val {
1436                            return Ok(r_val);
1437                        }
1438                        Ok(Self::combine_union_values(l_val, r_val))
1439                    }
1440                    Op::Eq | Op::Ne | Op::Lt | Op::Gt | Op::Le | Op::Ge => {
1441                        if let ResultData::Error(_) = &l_val {
1442                            return Ok(l_val);
1443                        }
1444                        let r_val = self.evaluate_ast(right, context, row, col, deps, scope)?;
1445                        if let ResultData::Error(_) = &r_val {
1446                            return Ok(r_val);
1447                        }
1448                        let ord = Self::compare_excel_values(&l_val, &r_val);
1449                        let b = match op {
1450                            Op::Eq => ord.is_eq(),
1451                            Op::Ne => !ord.is_eq(),
1452                            Op::Lt => ord.is_lt(),
1453                            Op::Gt => ord.is_gt(),
1454                            Op::Le => ord.is_le(),
1455                            Op::Ge => ord.is_ge(),
1456                            _ => unreachable!(),
1457                        };
1458                        Ok(ResultData::Boolean(b))
1459                    }
1460                    Op::Concat => {
1461                        if let ResultData::Error(_) = &l_val {
1462                            return Ok(l_val);
1463                        }
1464                        let r_val = self.evaluate_ast(right, context, row, col, deps, scope)?;
1465                        if let ResultData::Error(_) = &r_val {
1466                            return Ok(r_val);
1467                        }
1468                        let mut out = match Self::concat_text(&l_val) {
1469                            Ok(s) => s,
1470                            Err(e) => return Ok(ResultData::Error(e)),
1471                        };
1472                        let rhs = match Self::concat_text(&r_val) {
1473                            Ok(s) => s,
1474                            Err(e) => return Ok(ResultData::Error(e)),
1475                        };
1476                        out.push_str(&rhs);
1477                        Ok(ResultData::String(out))
1478                    }
1479                    _ => {
1480                        if let ResultData::Error(_) = &l_val {
1481                            return Ok(l_val);
1482                        }
1483                        let lf = match self.to_f64(&l_val) {
1484                            Some(f) => f,
1485                            None => return Ok(ResultData::Error("#VALUE!".to_string())),
1486                        };
1487                        let r_val = self.evaluate_ast(right, context, row, col, deps, scope)?;
1488                        if let ResultData::Error(_) = &r_val {
1489                            return Ok(r_val);
1490                        }
1491                        let rf = match self.to_f64(&r_val) {
1492                            Some(f) => f,
1493                            None => return Ok(ResultData::Error("#VALUE!".to_string())),
1494                        };
1495                        match op {
1496                            Op::Add => Ok(ResultData::Float(lf + rf)),
1497                            Op::Sub => Ok(ResultData::Float(lf - rf)),
1498                            Op::Mul => Ok(ResultData::Float(lf * rf)),
1499                            Op::Div => {
1500                                if rf == 0.0 {
1501                                    return Ok(ResultData::Error("#DIV/0!".to_string()));
1502                                }
1503                                Ok(ResultData::Float(lf / rf))
1504                            }
1505                            Op::Exp => {
1506                                if lf == 0.0 && rf == 0.0 {
1507                                    return Ok(ResultData::Error("#NUM!".to_string()));
1508                                }
1509                                if lf == 0.0 && rf < 0.0 {
1510                                    return Ok(ResultData::Error("#DIV/0!".to_string()));
1511                                }
1512                                if lf < 0.0 {
1513                                    if rf.fract() != 0.0 || rf.abs() > 1e6 {
1514                                        return Ok(ResultData::Error("#NUM!".to_string()));
1515                                    }
1516                                    let res = lf.powi(rf as i32);
1517                                    if res.is_nan() || res.is_infinite() {
1518                                        return Ok(ResultData::Error("#NUM!".to_string()));
1519                                    }
1520                                    return Ok(ResultData::Float(res));
1521                                }
1522                                let res = lf.powf(rf);
1523                                if res.is_nan() || res.is_infinite() {
1524                                    return Ok(ResultData::Error("#NUM!".to_string()));
1525                                }
1526                                Ok(ResultData::Float(res))
1527                            }
1528                            _ => unreachable!(),
1529                        }
1530                    }
1531                }
1532            }
1533            Expr::FunctionCall { name, args } => {
1534                self.evaluate_function(name, args, context, row, col, deps, scope)
1535            }
1536        }
1537    }
1538
1539    fn excel_type_rank(val: &ResultData) -> u8 {
1540        match val {
1541            ResultData::None => 0,
1542            ResultData::Integer(_) | ResultData::Float(_) => 1,
1543            ResultData::String(_) => 2,
1544            ResultData::Boolean(_) => 3,
1545            _ => 4,
1546        }
1547    }
1548
1549    fn compare_excel_values(l: &ResultData, r: &ResultData) -> std::cmp::Ordering {
1550        match (l, r) {
1551            (ResultData::None, ResultData::None) => return std::cmp::Ordering::Equal,
1552            (ResultData::None, ResultData::Integer(b)) => {
1553                return 0.0
1554                    .partial_cmp(&(*b as f64))
1555                    .unwrap_or(std::cmp::Ordering::Equal);
1556            }
1557            (ResultData::None, ResultData::Float(b)) => {
1558                return 0.0.partial_cmp(b).unwrap_or(std::cmp::Ordering::Equal);
1559            }
1560            (ResultData::Integer(a), ResultData::None) => {
1561                return (*a as f64)
1562                    .partial_cmp(&0.0)
1563                    .unwrap_or(std::cmp::Ordering::Equal);
1564            }
1565            (ResultData::Float(a), ResultData::None) => {
1566                return a.partial_cmp(&0.0).unwrap_or(std::cmp::Ordering::Equal);
1567            }
1568            (ResultData::None, ResultData::String(b)) => {
1569                return "".cmp(b.to_lowercase().as_str());
1570            }
1571            (ResultData::String(a), ResultData::None) => {
1572                return a.to_lowercase().as_str().cmp("");
1573            }
1574            (ResultData::None, ResultData::Boolean(b)) => {
1575                return false.cmp(b);
1576            }
1577            (ResultData::Boolean(a), ResultData::None) => {
1578                return a.cmp(&false);
1579            }
1580            _ => {}
1581        }
1582
1583        let rank_l = Self::excel_type_rank(l);
1584        let rank_r = Self::excel_type_rank(r);
1585        if rank_l != rank_r {
1586            return rank_l.cmp(&rank_r);
1587        }
1588        match (l, r) {
1589            (ResultData::Integer(a), ResultData::Integer(b)) => a.cmp(b),
1590            (ResultData::Float(a), ResultData::Float(b)) => {
1591                a.partial_cmp(b).unwrap_or(std::cmp::Ordering::Equal)
1592            }
1593            (ResultData::Integer(a), ResultData::Float(b)) => (*a as f64)
1594                .partial_cmp(b)
1595                .unwrap_or(std::cmp::Ordering::Equal),
1596            (ResultData::Float(a), ResultData::Integer(b)) => a
1597                .partial_cmp(&(*b as f64))
1598                .unwrap_or(std::cmp::Ordering::Equal),
1599            (ResultData::Boolean(a), ResultData::Boolean(b)) => a.cmp(b),
1600            (ResultData::String(a), ResultData::String(b)) => Self::compare_excel_strings(a, b),
1601            _ => std::cmp::Ordering::Equal,
1602        }
1603    }
1604
1605    fn sort_compare_blanks_last(
1606        l: &ResultData,
1607        r: &ResultData,
1608        sort_order: f64,
1609    ) -> std::cmp::Ordering {
1610        match (matches!(l, ResultData::None), matches!(r, ResultData::None)) {
1611            (true, true) => std::cmp::Ordering::Equal,
1612            (true, false) => std::cmp::Ordering::Greater,
1613            (false, true) => std::cmp::Ordering::Less,
1614            (false, false) => {
1615                let ord = Self::compare_excel_values(l, r);
1616                if sort_order < 0.0 { ord.reverse() } else { ord }
1617            }
1618        }
1619    }
1620
1621    fn is_excel_number_str(s: &str) -> bool {
1622        let s = s.trim();
1623        if s.is_empty() {
1624            return false;
1625        }
1626        let bytes = s.as_bytes();
1627        let first = bytes[0];
1628        if first == b'e' || first == b'E' {
1629            return false;
1630        }
1631        if (first == b'+' || first == b'-') && bytes.len() > 1 {
1632            let second = bytes[1];
1633            if second == b'e' || second == b'E' {
1634                return false;
1635            }
1636        }
1637        true
1638    }
1639
1640    fn compare_excel_strings(a: &str, b: &str) -> std::cmp::Ordering {
1641        let char_weight = |ch: char| -> u32 {
1642            match ch {
1643                ' ' => 0,
1644                '_' => 1,
1645                '-' => 2,
1646                ',' => 3,
1647                ';' => 4,
1648                ':' => 5,
1649                '!' => 6,
1650                '?' => 7,
1651                '.' => 8,
1652                '\'' => 9,
1653                '"' => 10,
1654                '(' => 11,
1655                ')' => 12,
1656                '[' => 13,
1657                ']' => 14,
1658                '{' => 15,
1659                '}' => 16,
1660                '@' => 17,
1661                '*' => 18,
1662                '/' => 19,
1663                '\\' => 20,
1664                '&' => 21,
1665                '#' => 22,
1666                '%' => 23,
1667                '`' => 24,
1668                '^' => 25,
1669                '+' => 26,
1670                '<' => 27,
1671                '=' => 28,
1672                '>' => 29,
1673                '|' => 30,
1674                '~' => 31,
1675                '$' => 32,
1676                '0'..='9' => 33 + (ch as u32 - '0' as u32),
1677                'A'..='Z' => 43 + (ch as u32 - 'A' as u32),
1678                'a'..='z' => 43 + (ch as u32 - 'a' as u32),
1679                _ => ch
1680                    .to_lowercase()
1681                    .next()
1682                    .map(|c| c as u32 + 200)
1683                    .unwrap_or(ch as u32 + 200),
1684            }
1685        };
1686
1687        let mut a_chars = a.chars().filter(|&c| c != '-');
1688        let mut b_chars = b.chars().filter(|&c| c != '-');
1689        loop {
1690            match (a_chars.next(), b_chars.next()) {
1691                (Some(ca), Some(cb)) => {
1692                    let wa = char_weight(ca);
1693                    let wb = char_weight(cb);
1694                    if wa != wb {
1695                        return wa.cmp(&wb);
1696                    }
1697                }
1698                (Some(_), None) => return std::cmp::Ordering::Greater,
1699                (None, Some(_)) => return std::cmp::Ordering::Less,
1700                (None, None) => return a.len().cmp(&b.len()),
1701            }
1702        }
1703    }
1704
1705    pub(crate) fn clean_float(val: f64) -> f64 {
1706        if val == 0.0 || !val.is_finite() {
1707            return val;
1708        }
1709        let abs_val = val.abs();
1710        let exp = abs_val.log10().floor() as i32;
1711        let factor = 10.0f64.powi(15 - 1 - exp);
1712        if factor.is_finite() && factor != 0.0 {
1713            let rounded = (val * factor).round() / factor;
1714            if (val - rounded).abs() <= 1e-14 * abs_val {
1715                return rounded;
1716            }
1717        }
1718        val
1719    }
1720
1721    pub(crate) fn to_f64(&self, val: &ResultData) -> Option<f64> {
1722        match val {
1723            ResultData::None => Some(0.0),
1724            ResultData::Float(f) => Some(*f),
1725            ResultData::Integer(i) => Some(*i as f64),
1726            ResultData::Boolean(b) => Some(if *b { 1.0 } else { 0.0 }),
1727            ResultData::String(s) => {
1728                let s_trim = s.trim();
1729                if Self::is_excel_number_str(s_trim) {
1730                    if let Ok(f) = s_trim.parse::<f64>() {
1731                        return Some(f);
1732                    }
1733                    if let Some((date, _)) =
1734                        crate::core::date::parse_date_with_locale(s_trim, &self.locale)
1735                    {
1736                        return (date.year >= 1900)
1737                            .then(|| crate::core::date::date_to_excel_serial(date));
1738                    }
1739                    None
1740                } else if let Some((date, _)) =
1741                    crate::core::date::parse_date_with_locale(s_trim, &self.locale)
1742                {
1743                    (date.year >= 1900).then(|| crate::core::date::date_to_excel_serial(date))
1744                } else {
1745                    None
1746                }
1747            }
1748            _ => None,
1749        }
1750    }
1751
1752    fn to_f64_arg(&self, arg_opt: Option<&ResultData>, fn_name: &str) -> Result<f64, EngineError> {
1753        let val = arg_opt.ok_or_else(|| {
1754            EngineError::EvalError(EvalError::UnknownFunction(format!(
1755                "{} requires argument",
1756                fn_name
1757            )))
1758        })?;
1759        if let ResultData::Error(e) = val {
1760            return Err(EngineError::EvalError(EvalError::UnknownFunction(
1761                e.clone(),
1762            )));
1763        }
1764        self.to_f64(val).ok_or_else(|| {
1765            EngineError::EvalError(EvalError::UnknownFunction("#VALUE!".to_string()))
1766        })
1767    }
1768
1769    fn find_error_in_args(args: &[ResultData]) -> Option<ResultData> {
1770        for arg in args {
1771            match arg {
1772                ResultData::Error(_) => return Some(arg.clone()),
1773                ResultData::List(list) => {
1774                    if let Some(err) = Self::find_error_in_args(list) {
1775                        return Some(err);
1776                    }
1777                }
1778                _ => {}
1779            }
1780        }
1781        None
1782    }
1783
1784    fn check_arg_errors(&self, args: &[ResultData], is_direct: &[bool]) -> Option<ResultData> {
1785        for (i, arg) in args.iter().enumerate() {
1786            match arg {
1787                ResultData::Error(_) => return Some(arg.clone()),
1788                ResultData::List(list) => {
1789                    if let Some(err) = self.check_arg_errors(list, &[]) {
1790                        return Some(err);
1791                    }
1792                }
1793                ResultData::String(_)
1794                    if is_direct.get(i).copied().unwrap_or(false) && self.to_f64(arg).is_none() =>
1795                {
1796                    return Some(ResultData::Error("#VALUE!".to_string()));
1797                }
1798                _ => {}
1799            }
1800        }
1801        None
1802    }
1803
1804    fn sum_helper(&self, arg: &ResultData, is_direct: bool) -> f64 {
1805        match arg {
1806            ResultData::Float(f) => *f,
1807            ResultData::Integer(i) => *i as f64,
1808            ResultData::Boolean(b) => {
1809                if is_direct {
1810                    if *b { 1.0 } else { 0.0 }
1811                } else {
1812                    0.0
1813                }
1814            }
1815            ResultData::String(_) => {
1816                if is_direct {
1817                    self.to_f64(arg).unwrap_or(0.0)
1818                } else {
1819                    0.0
1820                }
1821            }
1822            ResultData::List(list) => {
1823                let mut sum = 0.0;
1824                for item in list {
1825                    sum += self.sum_helper(item, false);
1826                }
1827                sum
1828            }
1829            _ => 0.0,
1830        }
1831    }
1832
1833    fn flatten_finance_numbers(&self, arg: &ResultData, is_direct: bool) -> Vec<f64> {
1834        match arg {
1835            ResultData::Float(f) => vec![*f],
1836            ResultData::Integer(i) => vec![*i as f64],
1837            ResultData::Boolean(b) => {
1838                if is_direct {
1839                    vec![if *b { 1.0 } else { 0.0 }]
1840                } else {
1841                    vec![]
1842                }
1843            }
1844            ResultData::String(_) => {
1845                if is_direct {
1846                    self.to_f64(arg).into_iter().collect()
1847                } else {
1848                    vec![]
1849                }
1850            }
1851            ResultData::List(list) => list
1852                .iter()
1853                .flat_map(|v| self.flatten_finance_numbers(v, false))
1854                .collect(),
1855            _ => vec![],
1856        }
1857    }
1858
1859    fn flatten_stat_numbers(&self, arg: &ResultData, is_direct: bool) -> Vec<f64> {
1860        match arg {
1861            ResultData::Float(f) => vec![*f],
1862            ResultData::Integer(i) => vec![*i as f64],
1863            ResultData::Boolean(b) => {
1864                if is_direct {
1865                    vec![if *b { 1.0 } else { 0.0 }]
1866                } else {
1867                    vec![]
1868                }
1869            }
1870            ResultData::String(_) => {
1871                if is_direct {
1872                    self.to_f64(arg).into_iter().collect()
1873                } else {
1874                    vec![]
1875                }
1876            }
1877            ResultData::List(list) => list
1878                .iter()
1879                .flat_map(|v| self.flatten_stat_numbers(v, false))
1880                .collect(),
1881            _ => vec![],
1882        }
1883    }
1884
1885    fn flatten_positional(
1886        &self,
1887        arg: &ResultData,
1888        out: &mut Vec<Option<f64>>,
1889        first_err: &mut Option<String>,
1890    ) {
1891        match arg {
1892            ResultData::List(items) => {
1893                for item in items {
1894                    self.flatten_positional(item, out, first_err);
1895                }
1896            }
1897            ResultData::Float(f) => out.push(Some(*f)),
1898            ResultData::Integer(i) => out.push(Some(*i as f64)),
1899            ResultData::Error(e) => {
1900                if first_err.is_none() {
1901                    *first_err = Some(e.clone());
1902                }
1903                out.push(None);
1904            }
1905            _ => out.push(None),
1906        }
1907    }
1908
1909    fn positional_numbers(
1910        &self,
1911        arg: Option<&ResultData>,
1912        first_err: &mut Option<String>,
1913    ) -> Vec<Option<f64>> {
1914        let mut out = Vec::new();
1915        if let Some(a) = arg {
1916            self.flatten_positional(a, &mut out, first_err);
1917        }
1918        out
1919    }
1920
1921    fn flatten_positional_with_errors(
1922        &self,
1923        arg: &ResultData,
1924        out: &mut Vec<(Option<f64>, Option<String>)>,
1925    ) {
1926        match arg {
1927            ResultData::List(items) => {
1928                for item in items {
1929                    self.flatten_positional_with_errors(item, out);
1930                }
1931            }
1932            ResultData::Float(f) => out.push((Some(*f), None)),
1933            ResultData::Integer(i) => out.push((Some(*i as f64), None)),
1934            ResultData::Error(e) => out.push((None, Some(e.clone()))),
1935            _ => out.push((None, None)),
1936        }
1937    }
1938
1939    fn positional_numbers_with_errors(
1940        &self,
1941        arg: Option<&ResultData>,
1942    ) -> Vec<(Option<f64>, Option<String>)> {
1943        let mut out = Vec::new();
1944        if let Some(a) = arg {
1945            self.flatten_positional_with_errors(a, &mut out);
1946        }
1947        out
1948    }
1949
1950    fn pair_and_filter(
1951        xs_raw: Vec<Option<f64>>,
1952        ys_raw: Vec<Option<f64>>,
1953    ) -> Result<(Vec<f64>, Vec<f64>), String> {
1954        if xs_raw.len() != ys_raw.len() {
1955            return Err("#N/A".to_string());
1956        }
1957        let mut xs = Vec::with_capacity(xs_raw.len());
1958        let mut ys = Vec::with_capacity(ys_raw.len());
1959        for (x, y) in xs_raw.into_iter().zip(ys_raw) {
1960            if let (Some(x), Some(y)) = (x, y) {
1961                xs.push(x);
1962                ys.push(y);
1963            }
1964        }
1965        Ok((xs, ys))
1966    }
1967
1968    fn paired_args(
1969        &self,
1970        x_arg: Option<&ResultData>,
1971        y_arg: Option<&ResultData>,
1972    ) -> Result<(Vec<f64>, Vec<f64>), String> {
1973        for arg in [x_arg, y_arg].into_iter().flatten() {
1974            let scalar = match arg {
1975                ResultData::List(items) if items.len() == 1 => &items[0],
1976                other => other,
1977            };
1978            if let ResultData::Error(e) = scalar {
1979                return Err(e.clone());
1980            }
1981            if Self::is_empty_scalar_operand(arg) {
1982                return Err("#VALUE!".to_string());
1983            }
1984        }
1985        let mut first_err = None;
1986        let xs_raw = self.positional_numbers(x_arg, &mut first_err);
1987        let ys_raw = self.positional_numbers(y_arg, &mut first_err);
1988        if xs_raw.len() != ys_raw.len() {
1989            return Err("#N/A".to_string());
1990        }
1991        if let Some(e) = first_err {
1992            return Err(e);
1993        }
1994        Self::pair_and_filter(xs_raw, ys_raw)
1995    }
1996
1997    fn paired_args_pairwise_errors(
1998        &self,
1999        x_arg: Option<&ResultData>,
2000        y_arg: Option<&ResultData>,
2001    ) -> Result<(Vec<f64>, Vec<f64>), String> {
2002        for arg in [x_arg, y_arg].into_iter().flatten() {
2003            let scalar = match arg {
2004                ResultData::List(items) if items.len() == 1 => &items[0],
2005                other => other,
2006            };
2007            if let ResultData::Error(e) = scalar {
2008                return Err(e.clone());
2009            }
2010            if Self::is_empty_scalar_operand(arg) {
2011                return Err("#VALUE!".to_string());
2012            }
2013        }
2014        let xs_raw = self.positional_numbers_with_errors(x_arg);
2015        let ys_raw = self.positional_numbers_with_errors(y_arg);
2016        if xs_raw.len() != ys_raw.len() {
2017            return Err("#N/A".to_string());
2018        }
2019        let mut xs = Vec::with_capacity(xs_raw.len());
2020        let mut ys = Vec::with_capacity(ys_raw.len());
2021        for ((x, x_err), (y, y_err)) in xs_raw.into_iter().zip(ys_raw) {
2022            if let Some(e) = x_err {
2023                return Err(if e == "#NULL!" {
2024                    "#NUM!".to_string()
2025                } else {
2026                    e
2027                });
2028            }
2029            if let Some(e) = y_err {
2030                return Err(if e == "#NULL!" {
2031                    "#NUM!".to_string()
2032                } else {
2033                    e
2034                });
2035            }
2036            if let (Some(x), Some(y)) = (x, y) {
2037                xs.push(x);
2038                ys.push(y);
2039            }
2040        }
2041        Ok((xs, ys))
2042    }
2043
2044    fn flatten_strict_inner(
2045        &self,
2046        arg: &ResultData,
2047        blanks: BlankPolicy,
2048        coerce_text: bool,
2049        out: &mut Vec<f64>,
2050    ) -> Result<(), String> {
2051        match arg {
2052            ResultData::List(items) => {
2053                for item in items {
2054                    self.flatten_strict_inner(item, blanks, coerce_text, out)?;
2055                }
2056                Ok(())
2057            }
2058            ResultData::Error(e) => Err(e.clone()),
2059            ResultData::Float(f) => {
2060                out.push(*f);
2061                Ok(())
2062            }
2063            ResultData::Integer(i) => {
2064                out.push(*i as f64);
2065                Ok(())
2066            }
2067            ResultData::None => match blanks {
2068                BlankPolicy::Zero => {
2069                    out.push(0.0);
2070                    Ok(())
2071                }
2072                BlankPolicy::Skip => Ok(()),
2073                BlankPolicy::Reject => Err("#VALUE!".to_string()),
2074            },
2075            ResultData::String(_) if coerce_text => match self.to_f64(arg) {
2076                Some(f) => {
2077                    out.push(f);
2078                    Ok(())
2079                }
2080                None => Err("#VALUE!".to_string()),
2081            },
2082            _ => Err("#VALUE!".to_string()),
2083        }
2084    }
2085
2086    fn flatten_strict_numbers(&self, arg: &ResultData) -> Result<Vec<f64>, String> {
2087        let mut out = Vec::new();
2088        self.flatten_strict_inner(arg, BlankPolicy::Zero, true, &mut out)?;
2089        Ok(out)
2090    }
2091
2092    fn flatten_skipping_blanks(&self, arg: Option<&ResultData>) -> Result<Vec<f64>, String> {
2093        let mut out = Vec::new();
2094        if let Some(a) = arg {
2095            self.flatten_strict_inner(a, BlankPolicy::Skip, true, &mut out)?;
2096        }
2097        Ok(out)
2098    }
2099
2100    fn flatten_skipping_blanks_no_text_coercion(
2101        &self,
2102        arg: Option<&ResultData>,
2103    ) -> Result<Vec<f64>, String> {
2104        let mut out = Vec::new();
2105        if let Some(a) = arg {
2106            self.flatten_strict_inner(a, BlankPolicy::Skip, false, &mut out)?;
2107        }
2108        Ok(out)
2109    }
2110
2111    fn flatten_numbers_only(&self, arg: &ResultData) -> Result<Vec<f64>, String> {
2112        let mut out = Vec::new();
2113        self.flatten_strict_inner(arg, BlankPolicy::Reject, false, &mut out)?;
2114        Ok(out)
2115    }
2116
2117    fn aggregate_range_number(val: &ResultData) -> Option<f64> {
2118        match val {
2119            ResultData::Float(f) => Some(*f),
2120            ResultData::Integer(i) => Some(*i as f64),
2121            _ => None,
2122        }
2123    }
2124
2125    fn flatten_numbers_only_arg(&self, arg: Option<&ResultData>) -> Result<Vec<f64>, String> {
2126        match arg {
2127            Some(a) => self.flatten_numbers_only(a),
2128            None => Ok(vec![]),
2129        }
2130    }
2131
2132    fn flatten_args_stat_numbers(
2133        &self,
2134        args: &[ResultData],
2135        is_direct: &[bool],
2136    ) -> Result<Vec<f64>, String> {
2137        let mut out = Vec::new();
2138        for (i, arg) in args.iter().enumerate() {
2139            let direct = is_direct.get(i).copied().unwrap_or(false);
2140            if direct && matches!(arg, ResultData::String(_)) && self.to_f64(arg).is_none() {
2141                return Err("#VALUE!".to_string());
2142            }
2143            out.extend(self.flatten_stat_numbers(arg, direct));
2144        }
2145        Ok(out)
2146    }
2147
2148    fn flatten_stat_numbers_a(
2149        &self,
2150        arg: &ResultData,
2151        is_direct: bool,
2152    ) -> Result<Vec<f64>, String> {
2153        Ok(match arg {
2154            ResultData::Float(f) => vec![*f],
2155            ResultData::Integer(i) => vec![*i as f64],
2156            ResultData::Boolean(b) => vec![if *b { 1.0 } else { 0.0 }],
2157            ResultData::String(_) => {
2158                if is_direct {
2159                    match self.to_f64(arg) {
2160                        Some(f) => vec![f],
2161                        None => return Err("#VALUE!".to_string()),
2162                    }
2163                } else {
2164                    vec![0.0]
2165                }
2166            }
2167            ResultData::Error(e) => return Err(e.clone()),
2168            ResultData::List(list) => {
2169                let mut out = Vec::new();
2170                for v in list {
2171                    out.extend(self.flatten_stat_numbers_a(v, false)?);
2172                }
2173                out
2174            }
2175            ResultData::None => vec![],
2176            _ => vec![0.0],
2177        })
2178    }
2179
2180    fn flatten_args_stat_numbers_a(
2181        &self,
2182        args: &[ResultData],
2183        is_direct: &[bool],
2184    ) -> Result<Vec<f64>, String> {
2185        let mut out = Vec::new();
2186        for (i, arg) in args.iter().enumerate() {
2187            out.extend(
2188                self.flatten_stat_numbers_a(arg, is_direct.get(i).copied().unwrap_or(false))?,
2189            );
2190        }
2191        Ok(out)
2192    }
2193
2194    fn extract_matrix(&self, arg: &ResultData) -> Vec<Vec<f64>> {
2195        match arg {
2196            ResultData::List(list) => {
2197                let mut rows = Vec::new();
2198                for item in list {
2199                    match item {
2200                        ResultData::List(sub_list) => {
2201                            let row: Vec<f64> =
2202                                sub_list.iter().flat_map(|v| self.to_f64(v)).collect();
2203                            if !row.is_empty() {
2204                                rows.push(row);
2205                            }
2206                        }
2207                        _ => {
2208                            if let Some(f) = self.to_f64(item) {
2209                                rows.push(vec![f]);
2210                            }
2211                        }
2212                    }
2213                }
2214                rows
2215            }
2216            _ => vec![],
2217        }
2218    }
2219
2220    fn matrix_from_arg(
2221        &self,
2222        expr: &crate::core::parser::Expr,
2223        value: &ResultData,
2224    ) -> Vec<Vec<f64>> {
2225        if let ResultData::List(items) = value
2226            && items.iter().any(|i| matches!(i, ResultData::List(_)))
2227        {
2228            return self.extract_matrix(value);
2229        }
2230        fn plain(v: &ResultData) -> Option<f64> {
2231            match v {
2232                ResultData::Float(f) => Some(*f),
2233                ResultData::Integer(i) => Some(*i as f64),
2234                _ => None,
2235            }
2236        }
2237        let items: Vec<&ResultData> = match value {
2238            ResultData::List(items) => items.iter().collect(),
2239            other => vec![other],
2240        };
2241        if items.iter().any(|v| plain(v).is_none()) {
2242            return Vec::new();
2243        }
2244        let flat: Vec<f64> = items.iter().filter_map(|v| plain(v)).collect();
2245        let cols = match Self::range_bounds(expr) {
2246            Some((_, _, start_col, _, end_col)) => end_col.saturating_sub(start_col) + 1,
2247            None => flat.len().max(1),
2248        };
2249        if cols == 0 || !flat.len().is_multiple_of(cols) {
2250            return self.extract_matrix(value);
2251        }
2252        flat.chunks(cols).map(|c| c.to_vec()).collect()
2253    }
2254
2255    fn paired_sum_has_no_numbers(&self, arg: Option<&ResultData>) -> bool {
2256        let mut ignored = None;
2257        let slots = self.positional_numbers(arg, &mut ignored);
2258        slots.iter().all(|v| v.is_none())
2259    }
2260
2261    fn is_empty_scalar_operand(arg: &ResultData) -> bool {
2262        let scalar = match arg {
2263            ResultData::List(items) if items.len() == 1 => &items[0],
2264            other => other,
2265        };
2266        matches!(scalar, ResultData::None)
2267    }
2268
2269    fn first_arg_is_boolean(args: &[ResultData]) -> bool {
2270        matches!(args.first(), Some(ResultData::Boolean(_)))
2271    }
2272
2273    fn opt_f64_arg(&self, args: &[ResultData], i: usize, default: f64) -> Result<f64, EngineError> {
2274        match args.get(i) {
2275            None => Ok(default),
2276            Some(ResultData::None) => Ok(0.0),
2277            Some(ResultData::Error(e)) => Err(EngineError::EvalError(EvalError::UnknownFunction(
2278                e.clone(),
2279            ))),
2280            Some(v) => self.to_f64(v).ok_or_else(|| {
2281                EngineError::EvalError(EvalError::UnknownFunction("#VALUE!".to_string()))
2282            }),
2283        }
2284    }
2285
2286    fn opt_f64(&self, args: &[ResultData], i: usize, default: f64) -> f64 {
2287        args.get(i).and_then(|v| self.to_f64(v)).unwrap_or(default)
2288    }
2289
2290    fn average_helper(&self, arg: &ResultData, is_direct: bool) -> (f64, usize) {
2291        match arg {
2292            ResultData::Float(f) => (*f, 1),
2293            ResultData::Integer(i) => (*i as f64, 1),
2294            ResultData::Boolean(b) => {
2295                if is_direct {
2296                    (if *b { 1.0 } else { 0.0 }, 1)
2297                } else {
2298                    (0.0, 0)
2299                }
2300            }
2301            ResultData::String(_) => {
2302                if is_direct {
2303                    if let Some(f) = self.to_f64(arg) {
2304                        (f, 1)
2305                    } else {
2306                        (0.0, 0)
2307                    }
2308                } else {
2309                    (0.0, 0)
2310                }
2311            }
2312            ResultData::List(list) => {
2313                let mut sum = 0.0;
2314                let mut count = 0;
2315                for item in list {
2316                    let (s, c) = self.average_helper(item, false);
2317                    sum += s;
2318                    count += c;
2319                }
2320                (sum, count)
2321            }
2322            _ => (0.0, 0),
2323        }
2324    }
2325
2326    fn count_helper(&self, arg: &ResultData) -> usize {
2327        match arg {
2328            ResultData::Float(_) | ResultData::Integer(_) => 1,
2329            ResultData::List(list) => {
2330                let mut count = 0;
2331                for item in list {
2332                    count += self.count_helper(item);
2333                }
2334                count
2335            }
2336            _ => 0,
2337        }
2338    }
2339
2340    fn min_helper(&self, arg: &ResultData, is_direct: bool) -> f64 {
2341        match arg {
2342            ResultData::Float(f) => *f,
2343            ResultData::Integer(i) => *i as f64,
2344            ResultData::Boolean(b) => {
2345                if is_direct {
2346                    if *b { 1.0 } else { 0.0 }
2347                } else {
2348                    f64::INFINITY
2349                }
2350            }
2351            ResultData::String(_) => {
2352                if is_direct {
2353                    self.to_f64(arg).unwrap_or(f64::INFINITY)
2354                } else {
2355                    f64::INFINITY
2356                }
2357            }
2358            ResultData::List(list) => {
2359                let mut min_val = f64::INFINITY;
2360                for item in list {
2361                    min_val = min_val.min(self.min_helper(item, false));
2362                }
2363                min_val
2364            }
2365            _ => f64::INFINITY,
2366        }
2367    }
2368
2369    fn max_helper(&self, arg: &ResultData, is_direct: bool) -> f64 {
2370        match arg {
2371            ResultData::Float(f) => *f,
2372            ResultData::Integer(i) => *i as f64,
2373            ResultData::Boolean(b) => {
2374                if is_direct {
2375                    if *b { 1.0 } else { 0.0 }
2376                } else {
2377                    f64::NEG_INFINITY
2378                }
2379            }
2380            ResultData::String(_) => {
2381                if is_direct {
2382                    self.to_f64(arg).unwrap_or(f64::NEG_INFINITY)
2383                } else {
2384                    f64::NEG_INFINITY
2385                }
2386            }
2387            ResultData::List(list) => {
2388                let mut max_val = f64::NEG_INFINITY;
2389                for item in list {
2390                    max_val = max_val.max(self.max_helper(item, false));
2391                }
2392                max_val
2393            }
2394            _ => f64::NEG_INFINITY,
2395        }
2396    }
2397
2398    fn concat_helper(&self, arg: &ResultData, out: &mut String) {
2399        match arg {
2400            ResultData::List(list) => {
2401                for item in list {
2402                    self.concat_helper(item, out);
2403                }
2404            }
2405            other => {
2406                out.push_str(&other.to_string());
2407            }
2408        }
2409    }
2410
2411    fn concat_text(arg: &ResultData) -> Result<String, String> {
2412        match arg {
2413            ResultData::Error(e) => Err(e.clone()),
2414            ResultData::List(list) => {
2415                let mut out = String::new();
2416                for item in list {
2417                    out.push_str(&Self::concat_text(item)?);
2418                }
2419                Ok(out)
2420            }
2421            other => Ok(other.to_string()),
2422        }
2423    }
2424
2425    fn counta_helper(&self, arg: &ResultData) -> usize {
2426        match arg {
2427            ResultData::None => 0,
2428            ResultData::List(list) => {
2429                let mut count = 0;
2430                for item in list {
2431                    count += self.counta_helper(item);
2432                }
2433                count
2434            }
2435            _ => 1,
2436        }
2437    }
2438
2439    fn product_helper(&self, arg: &ResultData, is_direct: bool) -> (f64, bool) {
2440        match arg {
2441            ResultData::Float(f) => (*f, true),
2442            ResultData::Integer(i) => (*i as f64, true),
2443            ResultData::Boolean(b) => {
2444                if is_direct {
2445                    (if *b { 1.0 } else { 0.0 }, true)
2446                } else {
2447                    (1.0, false)
2448                }
2449            }
2450            ResultData::String(_) => {
2451                if is_direct {
2452                    if let Some(f) = self.to_f64(arg) {
2453                        (f, true)
2454                    } else {
2455                        (1.0, false)
2456                    }
2457                } else {
2458                    (1.0, false)
2459                }
2460            }
2461            ResultData::List(list) => {
2462                let mut prod = 1.0;
2463                let mut has_nums = false;
2464                for item in list {
2465                    let (p, h) = self.product_helper(item, false);
2466                    if h {
2467                        prod *= p;
2468                        has_nums = true;
2469                    }
2470                }
2471                (prod, has_nums)
2472            }
2473            _ => (1.0, false),
2474        }
2475    }
2476
2477    fn to_bool_opt(&self, val: &ResultData) -> Option<bool> {
2478        match val {
2479            ResultData::Boolean(b) => Some(*b),
2480            ResultData::Integer(i) => Some(*i != 0),
2481            ResultData::Float(f) => Some(*f != 0.0),
2482            ResultData::String(s) => {
2483                let s_trim = s.trim();
2484                if s_trim.eq_ignore_ascii_case("true") {
2485                    Some(true)
2486                } else if s_trim.eq_ignore_ascii_case("false") {
2487                    Some(false)
2488                } else if let Ok(f) = s_trim.parse::<f64>() {
2489                    Some(f != 0.0)
2490                } else {
2491                    None
2492                }
2493            }
2494            ResultData::None => Some(false),
2495            _ => None,
2496        }
2497    }
2498
2499    fn to_bool(&self, val: &ResultData) -> bool {
2500        self.to_bool_opt(val).unwrap_or(false)
2501    }
2502
2503    fn range_numeric(val: &ResultData) -> Option<f64> {
2504        match val {
2505            ResultData::Integer(i) => Some(*i as f64),
2506            ResultData::Float(f) => Some(*f),
2507            _ => None,
2508        }
2509    }
2510
2511    fn exact_lookup_matches(lookup: &ResultData, candidate: &ResultData) -> bool {
2512        if matches!(candidate, ResultData::None) {
2513            return false;
2514        }
2515        let lookup_key = match lookup {
2516            ResultData::None => "0".to_string(),
2517            other => other.to_string(),
2518        };
2519        candidate.to_string() == lookup_key
2520    }
2521
2522    fn wildcard_criteria_matches(pattern: &str, text: &str) -> bool {
2523        fn rec(pat: &[char], txt: &[char]) -> bool {
2524            if pat.is_empty() {
2525                return txt.is_empty();
2526            }
2527            match pat[0] {
2528                '*' => rec(&pat[1..], txt) || (!txt.is_empty() && rec(pat, &txt[1..])),
2529                '?' => !txt.is_empty() && rec(&pat[1..], &txt[1..]),
2530                '~' if pat.len() > 1 && matches!(pat[1], '*' | '?' | '~') => {
2531                    !txt.is_empty() && pat[1] == txt[0] && rec(&pat[2..], &txt[1..])
2532                }
2533                ch => !txt.is_empty() && ch == txt[0] && rec(&pat[1..], &txt[1..]),
2534            }
2535        }
2536
2537        let pat = pattern.to_lowercase().chars().collect::<Vec<_>>();
2538        let txt = text.to_lowercase().chars().collect::<Vec<_>>();
2539        rec(&pat, &txt)
2540    }
2541
2542    fn criteria_text_eq(val: &ResultData, pattern: &str) -> bool {
2543        let text = val.to_string();
2544        if pattern.contains('*') || pattern.contains('?') {
2545            matches!(val, ResultData::String(_)) && Self::wildcard_criteria_matches(pattern, &text)
2546        } else {
2547            text.to_lowercase() == pattern.to_lowercase()
2548        }
2549    }
2550
2551    fn match_criteria(&self, val: &ResultData, criteria: &ResultData) -> bool {
2552        let crit_str = criteria.to_string();
2553        if let Some(rest) = crit_str.strip_prefix(">=") {
2554            let val_f = match Self::range_numeric(val) {
2555                Some(f) => f,
2556                None => return false,
2557            };
2558            let crit_f = rest.trim().parse::<f64>().unwrap_or(0.0);
2559            val_f >= crit_f
2560        } else if let Some(rest) = crit_str.strip_prefix('>') {
2561            let val_f = match Self::range_numeric(val) {
2562                Some(f) => f,
2563                None => return false,
2564            };
2565            let crit_f = rest.trim().parse::<f64>().unwrap_or(0.0);
2566            val_f > crit_f
2567        } else if let Some(rest) = crit_str.strip_prefix("<>") {
2568            let remainder = rest.trim();
2569            !Self::criteria_text_eq(val, remainder)
2570        } else if let Some(rest) = crit_str.strip_prefix("<=") {
2571            let val_f = match Self::range_numeric(val) {
2572                Some(f) => f,
2573                None => return false,
2574            };
2575            let crit_f = rest.trim().parse::<f64>().unwrap_or(0.0);
2576            val_f <= crit_f
2577        } else if let Some(rest) = crit_str.strip_prefix('<') {
2578            let val_f = match Self::range_numeric(val) {
2579                Some(f) => f,
2580                None => return false,
2581            };
2582            let crit_f = rest.trim().parse::<f64>().unwrap_or(0.0);
2583            val_f < crit_f
2584        } else if let Some(rest) = crit_str.strip_prefix('=') {
2585            let remainder = rest.trim();
2586            Self::criteria_text_eq(val, remainder)
2587        } else {
2588            Self::criteria_text_eq(val, &crit_str)
2589        }
2590    }
2591
2592    fn range_bounds(
2593        expr: &crate::core::parser::Expr,
2594    ) -> Option<(Option<String>, usize, usize, usize, usize)> {
2595        use crate::core::parser::Expr;
2596        match expr {
2597            Expr::RangeRef {
2598                sheet,
2599                start_row,
2600                start_col,
2601                end_row,
2602                end_col,
2603                ..
2604            } => Some((sheet.clone(), *start_row, *start_col, *end_row, *end_col)),
2605            Expr::CellRef {
2606                sheet, row, col, ..
2607            } => Some((sheet.clone(), *row, *col, *row, *col)),
2608            _ => None,
2609        }
2610    }
2611
2612    fn materialize_range(
2613        &self,
2614        sheet_opt: &Option<String>,
2615        start_row: usize,
2616        start_col: usize,
2617        end_row: usize,
2618        end_col: usize,
2619        context: Option<&Context>,
2620    ) -> Option<Vec<Vec<ResultData>>> {
2621        let is_self = match sheet_opt {
2622            Some(name) => name == &self.name,
2623            None => true,
2624        };
2625        let source: &Sheet = if is_self {
2626            self
2627        } else {
2628            context?.sheets.get(sheet_opt.as_ref()?)?
2629        };
2630        let actual_end_row = if end_row == usize::MAX {
2631            source.row_count().saturating_sub(1)
2632        } else {
2633            end_row
2634        };
2635        let actual_end_col = if end_col == usize::MAX {
2636            source.col_count().saturating_sub(1)
2637        } else {
2638            end_col
2639        };
2640        if actual_end_row < start_row || actual_end_col < start_col {
2641            return Some(Vec::new());
2642        }
2643        let mut grid = Vec::with_capacity(actual_end_row - start_row + 1);
2644        for r in start_row..=actual_end_row {
2645            let mut row = Vec::with_capacity(actual_end_col - start_col + 1);
2646            for c in start_col..=actual_end_col {
2647                row.push(source.get_result_data(&CellRef::new(r, c)));
2648            }
2649            grid.push(row);
2650        }
2651        Some(grid)
2652    }
2653
2654    fn evaluate_database_function(
2655        &self,
2656        func_name: &str,
2657        args: &[crate::core::parser::Expr],
2658        evaluated_args: &[ResultData],
2659        context: Option<&Context>,
2660    ) -> Result<ResultData, EngineError> {
2661        if args.len() < 3 || evaluated_args.len() < 3 {
2662            return Ok(ResultData::Error("#VALUE!".to_string()));
2663        }
2664        let (db_sheet, db_sr, db_sc, db_er, db_ec) = match Self::range_bounds(&args[0]) {
2665            Some(v) => v,
2666            None => return Ok(ResultData::Error("#VALUE!".to_string())),
2667        };
2668        let (crit_sheet, crit_sr, crit_sc, crit_er, crit_ec) = match Self::range_bounds(&args[2]) {
2669            Some(v) => v,
2670            None => return Ok(ResultData::Error("#VALUE!".to_string())),
2671        };
2672        let db = match self.materialize_range(&db_sheet, db_sr, db_sc, db_er, db_ec, context) {
2673            Some(g) => g,
2674            None => return Ok(ResultData::Error("#REF!".to_string())),
2675        };
2676        let crit = match self.materialize_range(
2677            &crit_sheet,
2678            crit_sr,
2679            crit_sc,
2680            crit_er,
2681            crit_ec,
2682            context,
2683        ) {
2684            Some(g) => g,
2685            None => return Ok(ResultData::Error("#REF!".to_string())),
2686        };
2687        if db.len() < 2 || crit.len() < 2 {
2688            return Ok(ResultData::Error("#VALUE!".to_string()));
2689        }
2690
2691        let db_headers: Vec<String> = db[0].iter().map(|v| v.to_string()).collect();
2692        let field_idx: usize = match &evaluated_args[1] {
2693            ResultData::String(s) => {
2694                match db_headers.iter().position(|h| h.eq_ignore_ascii_case(s)) {
2695                    Some(idx) => idx,
2696                    None => return Ok(ResultData::Error("#VALUE!".to_string())),
2697                }
2698            }
2699            other => match self.to_f64(other) {
2700                Some(n) if n >= 1.0 && (n as usize) <= db_headers.len() => n as usize - 1,
2701                _ => return Ok(ResultData::Error("#VALUE!".to_string())),
2702            },
2703        };
2704
2705        let crit_headers: Vec<String> = crit[0].iter().map(|v| v.to_string()).collect();
2706        let crit_to_db: Vec<Option<usize>> = crit_headers
2707            .iter()
2708            .map(|h| db_headers.iter().position(|dh| dh.eq_ignore_ascii_case(h)))
2709            .collect();
2710
2711        let mut matched: Vec<ResultData> = Vec::new();
2712        for row in db.iter().skip(1) {
2713            let row_matches_any_criteria_row = crit.iter().skip(1).any(|crit_row| {
2714                crit_row.iter().enumerate().all(|(ci, cell)| {
2715                    if matches!(cell, ResultData::None) {
2716                        return true;
2717                    }
2718                    match crit_to_db.get(ci).copied().flatten() {
2719                        Some(db_col) => self.match_criteria(&row[db_col], cell),
2720                        None => false,
2721                    }
2722                })
2723            });
2724            if row_matches_any_criteria_row {
2725                matched.push(row[field_idx].clone());
2726            }
2727        }
2728
2729        match func_name {
2730            "DGET" => match matched.len() {
2731                0 => Ok(ResultData::Error("#VALUE!".to_string())),
2732                1 => Ok(matched.into_iter().next().unwrap()),
2733                _ => Ok(ResultData::Error("#NUM!".to_string())),
2734            },
2735            "DCOUNT" => Ok(ResultData::Float(
2736                matched
2737                    .iter()
2738                    .filter(|v| Self::range_numeric(v).is_some())
2739                    .count() as f64,
2740            )),
2741            "DCOUNTA" => Ok(ResultData::Float(
2742                matched.iter().map(|v| self.counta_helper(v)).sum::<usize>() as f64,
2743            )),
2744            _ => {
2745                let nums: Vec<f64> = matched.iter().filter_map(Self::range_numeric).collect();
2746                match func_name {
2747                    "DSUM" => Ok(ResultData::Float(nums.iter().sum())),
2748                    "DPRODUCT" => Ok(ResultData::Float(if nums.is_empty() {
2749                        0.0
2750                    } else {
2751                        nums.iter().product()
2752                    })),
2753                    "DMAX" => {
2754                        let m = nums.iter().cloned().fold(f64::NEG_INFINITY, f64::max);
2755                        Ok(ResultData::Float(if m.is_finite() { m } else { 0.0 }))
2756                    }
2757                    "DMIN" => {
2758                        let m = nums.iter().cloned().fold(f64::INFINITY, f64::min);
2759                        Ok(ResultData::Float(if m.is_finite() { m } else { 0.0 }))
2760                    }
2761                    "DAVERAGE" => {
2762                        if nums.is_empty() {
2763                            Ok(ResultData::Error("#DIV/0!".to_string()))
2764                        } else {
2765                            Ok(ResultData::Float(
2766                                nums.iter().sum::<f64>() / nums.len() as f64,
2767                            ))
2768                        }
2769                    }
2770                    "DSTDEV" => match crate::core::stats::stdev_s(&nums) {
2771                        Ok(v) => Ok(ResultData::Float(v)),
2772                        Err(e) => Ok(ResultData::Error(e)),
2773                    },
2774                    "DSTDEVP" => match crate::core::stats::stdev_p(&nums) {
2775                        Ok(v) => Ok(ResultData::Float(v)),
2776                        Err(e) => Ok(ResultData::Error(e)),
2777                    },
2778                    "DVAR" => match crate::core::stats::var_s(&nums) {
2779                        Ok(v) => Ok(ResultData::Float(v)),
2780                        Err(e) => Ok(ResultData::Error(e)),
2781                    },
2782                    "DVARP" => match crate::core::stats::var_p(&nums) {
2783                        Ok(v) => Ok(ResultData::Float(v)),
2784                        Err(e) => Ok(ResultData::Error(e)),
2785                    },
2786                    _ => unreachable!(),
2787                }
2788            }
2789        }
2790    }
2791
2792    fn proper(&self, s: &str) -> String {
2793        let mut c_chars = Vec::new();
2794        let mut capitalize_next = true;
2795        for c in s.chars() {
2796            if c.is_alphabetic() {
2797                if capitalize_next {
2798                    c_chars.extend(c.to_uppercase());
2799                } else {
2800                    c_chars.extend(c.to_lowercase());
2801                }
2802                capitalize_next = false;
2803            } else {
2804                c_chars.push(c);
2805                capitalize_next = true;
2806            }
2807        }
2808        c_chars.into_iter().collect()
2809    }
2810
2811    fn get_ymd_hms(&self) -> ((i32, u32, u32), (u32, u32, u32)) {
2812        let now = web_time::SystemTime::now()
2813            .duration_since(web_time::SystemTime::UNIX_EPOCH)
2814            .unwrap_or_default()
2815            .as_secs();
2816        let secs_in_day = 86400;
2817        let days_since_epoch = (now / secs_in_day) as i32;
2818        let seconds_of_day = (now % secs_in_day) as u32;
2819
2820        let hour = seconds_of_day / 3600;
2821        let minute = (seconds_of_day % 3600) / 60;
2822        let second = seconds_of_day % 60;
2823
2824        let era = (if days_since_epoch >= -719468 {
2825            days_since_epoch + 719468
2826        } else {
2827            days_since_epoch + 719468 - 146096
2828        }) / 146097;
2829        let doe = (days_since_epoch + 719468 - era * 146097) as u32;
2830        let yoe = (doe - doe / 1460 + doe / 36524 - doe / 146096) / 365;
2831        let y = (yoe as i32) + era * 400;
2832        let doy = doe - (365 * yoe + yoe / 4 - yoe / 100);
2833        let mp = (5 * doy + 2) / 153;
2834        let d = doy - (153 * mp + 2) / 5 + 1;
2835        let m = if mp < 10 { mp + 3 } else { mp - 9 };
2836        let year = if m <= 2 { y + 1 } else { y };
2837
2838        ((year, m, d), (hour, minute, second))
2839    }
2840
2841    fn evaluate_let(
2842        &self,
2843        args: &[crate::core::parser::Expr],
2844        context: Option<&Context>,
2845        row: Option<usize>,
2846        col: Option<usize>,
2847        deps: &mut Vec<Dependency>,
2848        scope: &LetScope<'_>,
2849    ) -> Result<ResultData, EngineError> {
2850        use crate::core::parser::Expr;
2851
2852        if args.is_empty() || args.len().is_multiple_of(2) {
2853            return Ok(ResultData::Error("#VALUE!".to_string()));
2854        }
2855        if args.len() == 1 {
2856            return self.evaluate_ast(&args[0], context, row, col, deps, scope);
2857        }
2858
2859        let name = match &args[0] {
2860            Expr::Identifier(n) => n.as_str(),
2861            _ => return Ok(ResultData::Error("#VALUE!".to_string())),
2862        };
2863        let remaining_pairs = args.len() / 2 - 1;
2864        let is_duplicate = args[2..]
2865            .iter()
2866            .step_by(2)
2867            .take(remaining_pairs)
2868            .any(|a| matches!(a, Expr::Identifier(n2) if n2.eq_ignore_ascii_case(name)));
2869        if is_duplicate {
2870            return Ok(ResultData::Error("#VALUE!".to_string()));
2871        }
2872
2873        let value = self.evaluate_ast(&args[1], context, row, col, deps, scope)?;
2874        let inner_scope = LetScope::Bound {
2875            name,
2876            value: &value,
2877            parent: scope,
2878        };
2879        self.evaluate_let(&args[2..], context, row, col, deps, &inner_scope)
2880    }
2881
2882    fn extract_lambda(
2883        expr: &crate::core::parser::Expr,
2884    ) -> Option<(Vec<&str>, &crate::core::parser::Expr)> {
2885        use crate::core::parser::Expr;
2886        let Expr::FunctionCall { name, args } = expr else {
2887            return None;
2888        };
2889        if !name.eq_ignore_ascii_case("LAMBDA") || args.is_empty() {
2890            return None;
2891        }
2892        let (body, params) = args.split_last().unwrap();
2893        let param_names: Vec<&str> = params
2894            .iter()
2895            .filter_map(|p| match p {
2896                Expr::Identifier(n) => Some(n.as_str()),
2897                _ => None,
2898            })
2899            .collect();
2900        if param_names.len() != params.len() {
2901            return None;
2902        }
2903        Some((param_names, body))
2904    }
2905
2906    #[allow(clippy::too_many_arguments)]
2907    fn invoke_lambda<'v>(
2908        &self,
2909        params: &[&str],
2910        values: &'v [ResultData],
2911        body: &crate::core::parser::Expr,
2912        context: Option<&Context>,
2913        row: Option<usize>,
2914        col: Option<usize>,
2915        deps: &mut Vec<Dependency>,
2916        scope: &LetScope<'v>,
2917    ) -> Result<ResultData, EngineError> {
2918        match (params.split_first(), values.split_first()) {
2919            (Some((&pname, prest)), Some((vfirst, vrest))) => {
2920                let inner_scope = LetScope::Bound {
2921                    name: pname,
2922                    value: vfirst,
2923                    parent: scope,
2924                };
2925                self.invoke_lambda(prest, vrest, body, context, row, col, deps, &inner_scope)
2926            }
2927            _ => self.evaluate_ast(body, context, row, col, deps, scope),
2928        }
2929    }
2930
2931    fn eval_as_array(
2932        &self,
2933        expr: &crate::core::parser::Expr,
2934        context: Option<&Context>,
2935        row: Option<usize>,
2936        col: Option<usize>,
2937        deps: &mut Vec<Dependency>,
2938        scope: &LetScope<'_>,
2939    ) -> Result<Vec<ResultData>, EngineError> {
2940        Ok(
2941            match self.evaluate_ast(expr, context, row, col, deps, scope)? {
2942                ResultData::List(items) => Self::flatten_row_major(items).0,
2943                other => vec![other],
2944            },
2945        )
2946    }
2947
2948    fn flatten_row_major(items: Vec<ResultData>) -> (Vec<ResultData>, Option<usize>) {
2949        if !items.is_empty() && items.iter().all(|v| matches!(v, ResultData::List(_))) {
2950            let cols = match &items[0] {
2951                ResultData::List(inner) => inner.len().max(1),
2952                _ => 1,
2953            };
2954            let flat = items
2955                .into_iter()
2956                .flat_map(|v| match v {
2957                    ResultData::List(inner) => inner,
2958                    other => vec![other],
2959                })
2960                .collect();
2961            (flat, Some(cols))
2962        } else {
2963            (items, None)
2964        }
2965    }
2966
2967    fn array_shape(
2968        &self,
2969        expr: &crate::core::parser::Expr,
2970        context: Option<&Context>,
2971        row: Option<usize>,
2972        col: Option<usize>,
2973        deps: &mut Vec<Dependency>,
2974        scope: &LetScope<'_>,
2975    ) -> Result<(Vec<ResultData>, usize), EngineError> {
2976        use crate::core::parser::Expr;
2977        let items = match self.evaluate_ast(expr, context, row, col, deps, scope)? {
2978            ResultData::List(items) => items,
2979            other => vec![other],
2980        };
2981        let (flat, nested_cols) = Self::flatten_row_major(items);
2982        if let Some(cols) = nested_cols {
2983            return Ok((flat, cols));
2984        }
2985        let num_cols = match expr {
2986            Expr::RangeRef {
2987                start_col, end_col, ..
2988            } => (end_col - start_col + 1).max(1),
2989            Expr::CellRef { .. } => 1,
2990            Expr::FunctionCall { name, args } => self
2991                .function_call_cols(name, args, context, row, col, deps, scope)
2992                .unwrap_or_else(|| flat.len().max(1)),
2993            _ => flat.len().max(1),
2994        };
2995        Ok((flat, num_cols))
2996    }
2997
2998    #[allow(clippy::too_many_arguments)]
2999    fn function_call_cols(
3000        &self,
3001        name: &str,
3002        args: &[crate::core::parser::Expr],
3003        context: Option<&Context>,
3004        row: Option<usize>,
3005        col: Option<usize>,
3006        deps: &mut Vec<Dependency>,
3007        scope: &LetScope<'_>,
3008    ) -> Option<usize> {
3009        let mut upper = name.to_ascii_uppercase();
3010        if let Some(rest) = upper.strip_prefix("_XLFN.") {
3011            upper = rest.to_string();
3012        }
3013        if let Some(rest) = upper.strip_prefix("_XLWS.") {
3014            upper = rest.to_string();
3015        }
3016        match upper.as_str() {
3017            "TRANSPOSE" => {
3018                let (flat, cols) = self
3019                    .array_shape(args.first()?, context, row, col, deps, scope)
3020                    .ok()?;
3021                Some((flat.len().checked_div(cols).unwrap_or(0)).max(1))
3022            }
3023            "HSTACK" => {
3024                let mut total = 0usize;
3025                for a in args {
3026                    total += self.array_shape(a, context, row, col, deps, scope).ok()?.1;
3027                }
3028                Some(total)
3029            }
3030            "VSTACK" => {
3031                let mut max_cols = 0usize;
3032                for a in args {
3033                    max_cols =
3034                        max_cols.max(self.array_shape(a, context, row, col, deps, scope).ok()?.1);
3035                }
3036                Some(max_cols)
3037            }
3038            "CHOOSEROWS" => Some(
3039                self.array_shape(args.first()?, context, row, col, deps, scope)
3040                    .ok()?
3041                    .1,
3042            ),
3043            "CHOOSECOLS" => Some(args.len().saturating_sub(1).max(1)),
3044            "DROP" | "TAKE" => {
3045                let (_, cols) = self
3046                    .array_shape(args.first()?, context, row, col, deps, scope)
3047                    .ok()?;
3048                let is_take = upper == "TAKE";
3049                match args.get(2) {
3050                    Some(e) => {
3051                        let n = self
3052                            .to_f64(&self.evaluate_ast(e, context, row, col, deps, scope).ok()?)
3053                            .unwrap_or(0.0) as isize;
3054                        let (s, e2) = Self::drop_take_bounds(cols as isize, n, is_take);
3055                        Some((e2 - s).max(0) as usize)
3056                    }
3057                    None => Some(if is_take { cols } else { 0 }),
3058                }
3059            }
3060            "EXPAND" => {
3061                let (_, cols) = self
3062                    .array_shape(args.first()?, context, row, col, deps, scope)
3063                    .ok()?;
3064                match args.get(2) {
3065                    Some(e) => Some(
3066                        self.to_f64(&self.evaluate_ast(e, context, row, col, deps, scope).ok()?)
3067                            .unwrap_or(cols as f64) as usize,
3068                    ),
3069                    None => Some(cols),
3070                }
3071            }
3072            "TOCOL" => Some(1),
3073            "WRAPROWS" => {
3074                let n = self
3075                    .to_f64(
3076                        &self
3077                            .evaluate_ast(args.get(1)?, context, row, col, deps, scope)
3078                            .ok()?,
3079                    )
3080                    .unwrap_or(1.0)
3081                    .max(1.0) as usize;
3082                Some(n)
3083            }
3084            "WRAPCOLS" => {
3085                let (flat, _) = self
3086                    .array_shape(args.first()?, context, row, col, deps, scope)
3087                    .ok()?;
3088                let wrap = self
3089                    .to_f64(
3090                        &self
3091                            .evaluate_ast(args.get(1)?, context, row, col, deps, scope)
3092                            .ok()?,
3093                    )
3094                    .unwrap_or(1.0)
3095                    .max(1.0) as usize;
3096                Some(flat.len().div_ceil(wrap).max(1))
3097            }
3098            "UNIQUE" | "SORT" | "SORTBY" | "FILTER" | "TRIMRANGE" => Some(
3099                self.array_shape(args.first()?, context, row, col, deps, scope)
3100                    .ok()?
3101                    .1,
3102            ),
3103            "SEQUENCE" => match args.get(1) {
3104                Some(e) => Some(
3105                    self.to_f64(&self.evaluate_ast(e, context, row, col, deps, scope).ok()?)
3106                        .unwrap_or(1.0)
3107                        .max(1.0) as usize,
3108                ),
3109                None => Some(1),
3110            },
3111            "MUNIT" => {
3112                let n = self
3113                    .to_f64(
3114                        &self
3115                            .evaluate_ast(args.first()?, context, row, col, deps, scope)
3116                            .ok()?,
3117                    )
3118                    .unwrap_or(1.0)
3119                    .max(1.0) as usize;
3120                Some(n)
3121            }
3122            "MAKEARRAY" => match args.get(1) {
3123                Some(e) => Some(
3124                    self.to_f64(&self.evaluate_ast(e, context, row, col, deps, scope).ok()?)
3125                        .unwrap_or(1.0)
3126                        .max(1.0) as usize,
3127                ),
3128                None => Some(1),
3129            },
3130            _ => None,
3131        }
3132    }
3133
3134    fn drop_take_bounds(total: isize, n: isize, is_take: bool) -> (isize, isize) {
3135        let n = n.clamp(-total, total);
3136        if is_take {
3137            if n >= 0 { (0, n) } else { (total + n, total) }
3138        } else if n >= 0 {
3139            (n, total)
3140        } else {
3141            (0, total + n)
3142        }
3143    }
3144
3145    #[allow(clippy::too_many_arguments)]
3146    fn evaluate_lambda_function(
3147        &self,
3148        func_name: &str,
3149        args: &[crate::core::parser::Expr],
3150        context: Option<&Context>,
3151        row: Option<usize>,
3152        col: Option<usize>,
3153        deps: &mut Vec<Dependency>,
3154        scope: &LetScope<'_>,
3155    ) -> Result<ResultData, EngineError> {
3156        use crate::core::parser::Expr;
3157
3158        match func_name {
3159            "MAP" => {
3160                if args.len() < 2 {
3161                    return Ok(ResultData::Error("#VALUE!".to_string()));
3162                }
3163                let (lambda_expr, array_exprs) = args.split_last().unwrap();
3164                let Some((params, body)) = Self::extract_lambda(lambda_expr) else {
3165                    return Ok(ResultData::Error("#VALUE!".to_string()));
3166                };
3167                if params.len() != array_exprs.len() {
3168                    return Ok(ResultData::Error("#VALUE!".to_string()));
3169                }
3170                let arrays: Vec<Vec<ResultData>> = array_exprs
3171                    .iter()
3172                    .map(|e| self.eval_as_array(e, context, row, col, deps, scope))
3173                    .collect::<Result<_, _>>()?;
3174                let len = arrays.iter().map(|a| a.len()).max().unwrap_or(0);
3175                let mut results = Vec::with_capacity(len);
3176                for i in 0..len {
3177                    let values: Vec<ResultData> = arrays
3178                        .iter()
3179                        .map(|a| a.get(i).cloned().unwrap_or(ResultData::None))
3180                        .collect();
3181                    results.push(
3182                        self.invoke_lambda(&params, &values, body, context, row, col, deps, scope)?,
3183                    );
3184                }
3185                Ok(ResultData::List(results))
3186            }
3187            "BYROW" | "BYCOL" => {
3188                if args.len() != 2 {
3189                    return Ok(ResultData::Error("#VALUE!".to_string()));
3190                }
3191                let Some((params, body)) = Self::extract_lambda(&args[1]) else {
3192                    return Ok(ResultData::Error("#VALUE!".to_string()));
3193                };
3194                if params.len() != 1 {
3195                    return Ok(ResultData::Error("#VALUE!".to_string()));
3196                }
3197                let num_cols = match &args[0] {
3198                    Expr::RangeRef {
3199                        start_col, end_col, ..
3200                    } => (end_col - start_col + 1).max(1),
3201                    _ => 1,
3202                };
3203                let flat = self.eval_as_array(&args[0], context, row, col, deps, scope)?;
3204                let num_rows = if num_cols == 0 {
3205                    0
3206                } else {
3207                    flat.len().div_ceil(num_cols)
3208                };
3209                let mut results = Vec::new();
3210                if func_name == "BYROW" {
3211                    for r in 0..num_rows {
3212                        let row_vals: Vec<ResultData> = (0..num_cols)
3213                            .filter_map(|c| flat.get(r * num_cols + c).cloned())
3214                            .collect();
3215                        let arg = vec![ResultData::List(row_vals)];
3216                        results.push(
3217                            self.invoke_lambda(
3218                                &params, &arg, body, context, row, col, deps, scope,
3219                            )?,
3220                        );
3221                    }
3222                } else {
3223                    for c in 0..num_cols {
3224                        let col_vals: Vec<ResultData> = (0..num_rows)
3225                            .filter_map(|r| flat.get(r * num_cols + c).cloned())
3226                            .collect();
3227                        let arg = vec![ResultData::List(col_vals)];
3228                        results.push(
3229                            self.invoke_lambda(
3230                                &params, &arg, body, context, row, col, deps, scope,
3231                            )?,
3232                        );
3233                    }
3234                }
3235                Ok(ResultData::List(results))
3236            }
3237            "REDUCE" | "SCAN" => {
3238                if args.len() != 2 && args.len() != 3 {
3239                    return Ok(ResultData::Error("#VALUE!".to_string()));
3240                }
3241                let lambda_idx = args.len() - 1;
3242                let array_idx = args.len() - 2;
3243                let Some((params, body)) = Self::extract_lambda(&args[lambda_idx]) else {
3244                    return Ok(ResultData::Error("#VALUE!".to_string()));
3245                };
3246                if params.len() != 2 {
3247                    return Ok(ResultData::Error("#VALUE!".to_string()));
3248                }
3249                let array = self.eval_as_array(&args[array_idx], context, row, col, deps, scope)?;
3250                let (mut acc, rest, mut history): (ResultData, &[ResultData], Vec<ResultData>) =
3251                    if args.len() == 3 {
3252                        let init = self.evaluate_ast(&args[0], context, row, col, deps, scope)?;
3253                        (init, &array[..], Vec::new())
3254                    } else {
3255                        match array.split_first() {
3256                            Some((first, rest)) => (first.clone(), rest, vec![first.clone()]),
3257                            None => return Ok(ResultData::Error("#VALUE!".to_string())),
3258                        }
3259                    };
3260                for item in rest {
3261                    let call_args = [acc.clone(), item.clone()];
3262                    acc = self
3263                        .invoke_lambda(&params, &call_args, body, context, row, col, deps, scope)?;
3264                    history.push(acc.clone());
3265                }
3266                if func_name == "REDUCE" {
3267                    Ok(acc)
3268                } else {
3269                    Ok(ResultData::List(history))
3270                }
3271            }
3272            "MAKEARRAY" => {
3273                if args.len() != 3 {
3274                    return Ok(ResultData::Error("#VALUE!".to_string()));
3275                }
3276                let Some((params, body)) = Self::extract_lambda(&args[2]) else {
3277                    return Ok(ResultData::Error("#VALUE!".to_string()));
3278                };
3279                if params.len() != 2 {
3280                    return Ok(ResultData::Error("#VALUE!".to_string()));
3281                }
3282                let rows_val = self.evaluate_ast(&args[0], context, row, col, deps, scope)?;
3283                let cols_val = self.evaluate_ast(&args[1], context, row, col, deps, scope)?;
3284                let num_rows = self.to_f64(&rows_val).unwrap_or(0.0).max(0.0) as usize;
3285                let num_cols = self.to_f64(&cols_val).unwrap_or(0.0).max(0.0) as usize;
3286                let mut results = Vec::with_capacity(num_rows * num_cols);
3287                for r in 1..=num_rows {
3288                    for c in 1..=num_cols {
3289                        let call_args = [ResultData::Float(r as f64), ResultData::Float(c as f64)];
3290                        results.push(self.invoke_lambda(
3291                            &params, &call_args, body, context, row, col, deps, scope,
3292                        )?);
3293                    }
3294                }
3295                Ok(ResultData::List(results))
3296            }
3297            _ => unreachable!(),
3298        }
3299    }
3300
3301    fn parse_a1_reference(text: &str) -> Option<(Option<String>, usize, usize, usize, usize)> {
3302        let text = text.trim();
3303        let (sheet_part, ref_part) = match text.rfind('!') {
3304            Some(idx) => (Some(&text[..idx]), &text[idx + 1..]),
3305            None => (None, text),
3306        };
3307        let sheet = sheet_part.map(|s| s.trim().trim_matches('\'').to_string());
3308
3309        fn parse_cell(s: &str) -> Option<(usize, usize)> {
3310            let s = s.replace('$', "");
3311            let col_end = s.find(|c: char| c.is_ascii_digit())?;
3312            let (col_str, row_str) = s.split_at(col_end);
3313            if col_str.is_empty() || row_str.is_empty() {
3314                return None;
3315            }
3316            let mut col = 0usize;
3317            for ch in col_str.chars() {
3318                if !ch.is_ascii_alphabetic() {
3319                    return None;
3320                }
3321                col = col * 26 + (ch.to_ascii_uppercase() as usize - 'A' as usize + 1);
3322            }
3323            let row: usize = row_str.parse().ok()?;
3324            if row == 0 || col == 0 {
3325                return None;
3326            }
3327            Some((row - 1, col - 1))
3328        }
3329
3330        if let Some((start, end)) = ref_part.split_once(':') {
3331            let (r1, c1) = parse_cell(start)?;
3332            let (r2, c2) = parse_cell(end)?;
3333            Some((sheet, r1.min(r2), c1.min(c2), r1.max(r2), c1.max(c2)))
3334        } else {
3335            let (r, c) = parse_cell(ref_part)?;
3336            Some((sheet, r, c, r, c))
3337        }
3338    }
3339
3340    fn read_cell_with_deps(
3341        &self,
3342        sheet_opt: &Option<String>,
3343        r: usize,
3344        c: usize,
3345        context: Option<&Context>,
3346        deps: &mut Vec<Dependency>,
3347    ) -> ResultData {
3348        let is_self = sheet_opt.as_deref().is_none_or(|n| n == self.name);
3349        if is_self {
3350            deps.push(Dependency::Local(CellRef::new(r, c)));
3351            self.get_result_data(&CellRef::new(r, c))
3352        } else if let Some(ctx) = context {
3353            let name = sheet_opt.clone().unwrap();
3354            deps.push(Dependency::Remote {
3355                sheet: name.clone(),
3356                cell: CellRef::new(r, c),
3357            });
3358            ctx.sheets
3359                .get(&name)
3360                .map(|s| s.get_result_data(&CellRef::new(r, c)))
3361                .unwrap_or(ResultData::None)
3362        } else {
3363            ResultData::None
3364        }
3365    }
3366
3367    #[allow(clippy::too_many_arguments)]
3368    fn evaluate_range_info_function(
3369        &self,
3370        func_name: &str,
3371        args: &[crate::core::parser::Expr],
3372        context: Option<&Context>,
3373        row: Option<usize>,
3374        col: Option<usize>,
3375        deps: &mut Vec<Dependency>,
3376        scope: &LetScope<'_>,
3377    ) -> Result<ResultData, EngineError> {
3378        use crate::core::parser::Expr;
3379
3380        match func_name {
3381            "ROW" => match args.first() {
3382                Some(arg) => match Self::range_bounds(arg) {
3383                    Some((_, start_row, _, end_row, _)) if end_row > start_row => {
3384                        Ok(ResultData::List(
3385                            (start_row..=end_row)
3386                                .map(|r| ResultData::Float((r + 1) as f64))
3387                                .collect(),
3388                        ))
3389                    }
3390                    Some((_, start_row, _, _, _)) => Ok(ResultData::Float((start_row + 1) as f64)),
3391                    None => Ok(ResultData::Error("#VALUE!".to_string())),
3392                },
3393                None => match row {
3394                    Some(r) => Ok(ResultData::Float((r + 1) as f64)),
3395                    None => Ok(ResultData::Error("#VALUE!".to_string())),
3396                },
3397            },
3398            "COLUMN" => match args.first() {
3399                Some(arg) => match Self::range_bounds(arg) {
3400                    Some((_, _, start_col, _, end_col)) if end_col > start_col => {
3401                        Ok(ResultData::List(
3402                            (start_col..=end_col)
3403                                .map(|c| ResultData::Float((c + 1) as f64))
3404                                .collect(),
3405                        ))
3406                    }
3407                    Some((_, _, start_col, _, _)) => Ok(ResultData::Float((start_col + 1) as f64)),
3408                    None => Ok(ResultData::Error("#VALUE!".to_string())),
3409                },
3410                None => match col {
3411                    Some(c) => Ok(ResultData::Float((c + 1) as f64)),
3412                    None => Ok(ResultData::Error("#VALUE!".to_string())),
3413                },
3414            },
3415            "ROWS" => {
3416                let Some(arg) = args.first() else {
3417                    return Ok(ResultData::Error("#VALUE!".to_string()));
3418                };
3419                let Some((sheet_opt, start_row, _, end_row, _)) = Self::range_bounds(arg) else {
3420                    return Ok(ResultData::Error("#VALUE!".to_string()));
3421                };
3422                let is_self = sheet_opt.as_deref().is_none_or(|n| n == self.name);
3423                let actual_end_row = if end_row == usize::MAX {
3424                    if is_self {
3425                        self.row_count().saturating_sub(1)
3426                    } else {
3427                        context
3428                            .and_then(|ctx| sheet_opt.as_ref().and_then(|n| ctx.sheets.get(n)))
3429                            .map(|s| s.row_count().saturating_sub(1))
3430                            .unwrap_or(0)
3431                    }
3432                } else {
3433                    end_row
3434                };
3435                Ok(ResultData::Float(
3436                    (actual_end_row.saturating_sub(start_row) + 1) as f64,
3437                ))
3438            }
3439            "COLUMNS" => {
3440                let Some(arg) = args.first() else {
3441                    return Ok(ResultData::Error("#VALUE!".to_string()));
3442                };
3443                match Self::range_bounds(arg) {
3444                    Some((_, _, start_col, _, end_col)) => Ok(ResultData::Float(
3445                        (end_col.saturating_sub(start_col) + 1) as f64,
3446                    )),
3447                    None => Ok(ResultData::Error("#VALUE!".to_string())),
3448                }
3449            }
3450            "AREAS" => {
3451                if args.is_empty() {
3452                    Ok(ResultData::Error("#VALUE!".to_string()))
3453                } else {
3454                    Ok(ResultData::Float(1.0))
3455                }
3456            }
3457            "ISREF" => Ok(ResultData::Boolean(matches!(
3458                args.first(),
3459                Some(Expr::CellRef { .. } | Expr::RangeRef { .. } | Expr::StructuredRef { .. })
3460            ))),
3461            "FORMULATEXT" | "ISFORMULA" => {
3462                let Some(arg) = args.first() else {
3463                    return Ok(ResultData::Error("#VALUE!".to_string()));
3464                };
3465                let Some((sheet_opt, r, c, _, _)) = Self::range_bounds(arg) else {
3466                    return Ok(ResultData::Error("#VALUE!".to_string()));
3467                };
3468                let is_self = sheet_opt.as_deref().is_none_or(|n| n == self.name);
3469                let src = if is_self {
3470                    deps.push(Dependency::Local(CellRef::new(r, c)));
3471                    self.get_src_str(&CellRef::new(r, c))
3472                } else if let Some(ctx) = context {
3473                    let name = sheet_opt.unwrap();
3474                    deps.push(Dependency::Remote {
3475                        sheet: name.clone(),
3476                        cell: CellRef::new(r, c),
3477                    });
3478                    ctx.sheets
3479                        .get(&name)
3480                        .map(|s| s.get_src_str(&CellRef::new(r, c)))
3481                        .unwrap_or_default()
3482                } else {
3483                    String::new()
3484                };
3485                let is_formula = src.starts_with('=');
3486                if func_name == "ISFORMULA" {
3487                    Ok(ResultData::Boolean(is_formula))
3488                } else if is_formula {
3489                    Ok(ResultData::String(src))
3490                } else {
3491                    Ok(ResultData::Error("#N/A".to_string()))
3492                }
3493            }
3494            "SHEETS" => Ok(ResultData::Float(
3495                context.map(|c| c.sheets.len() + 1).unwrap_or(1) as f64,
3496            )),
3497            "SHEET" => {
3498                let sheet_name = match args.first() {
3499                    None => Some(self.name.clone()),
3500                    Some(arg) => match Self::range_bounds(arg) {
3501                        Some((sheet_opt, ..)) => {
3502                            Some(sheet_opt.unwrap_or_else(|| self.name.clone()))
3503                        }
3504                        None => self
3505                            .evaluate_ast(arg, context, row, col, deps, scope)
3506                            .ok()
3507                            .map(|v| v.to_string()),
3508                    },
3509                };
3510
3511                match sheet_name {
3512                    Some(name) => {
3513                        let ordinal = context
3514                            .and_then(|c| {
3515                                c.sheet_order
3516                                    .iter()
3517                                    .position(|n| n.eq_ignore_ascii_case(&name))
3518                            })
3519                            .map(|i| i + 1)
3520                            .unwrap_or(1);
3521                        Ok(ResultData::Float(ordinal as f64))
3522                    }
3523                    None => Ok(ResultData::Error("#N/A".to_string())),
3524                }
3525            }
3526            "CELL" => {
3527                if args.is_empty() {
3528                    return Ok(ResultData::Error("#VALUE!".to_string()));
3529                }
3530                let info_type = self
3531                    .evaluate_ast(&args[0], context, row, col, deps, scope)?
3532                    .to_string()
3533                    .to_lowercase();
3534                let bounds = args.get(1).and_then(Self::range_bounds);
3535                match info_type.as_str() {
3536                    "row" => match bounds.map(|b| b.1).or(row) {
3537                        Some(r) => Ok(ResultData::Float((r + 1) as f64)),
3538                        None => Ok(ResultData::Error("#VALUE!".to_string())),
3539                    },
3540                    "col" => match bounds {
3541                        Some((_, _, c, _, _)) => Ok(ResultData::Float((c + 1) as f64)),
3542                        None => Ok(ResultData::Error("#VALUE!".to_string())),
3543                    },
3544                    "address" => match bounds {
3545                        Some((_, r, c, _, _)) => Ok(ResultData::String(format!(
3546                            "${}${}",
3547                            crate::core::parser::col_idx_to_letters(c),
3548                            r + 1
3549                        ))),
3550                        None => Ok(ResultData::Error("#VALUE!".to_string())),
3551                    },
3552                    "contents" => match bounds {
3553                        Some((sheet_opt, r, c, _, _)) => {
3554                            Ok(self.read_cell_with_deps(&sheet_opt, r, c, context, deps))
3555                        }
3556                        None => Ok(ResultData::Error("#VALUE!".to_string())),
3557                    },
3558                    _ => Ok(ResultData::Error("#VALUE!".to_string())),
3559                }
3560            }
3561            "INFO" => {
3562                if args.is_empty() {
3563                    return Ok(ResultData::Error("#VALUE!".to_string()));
3564                }
3565                let info_type = self
3566                    .evaluate_ast(&args[0], context, row, col, deps, scope)?
3567                    .to_string()
3568                    .to_lowercase();
3569                match info_type.as_str() {
3570                    "numfile" => Ok(ResultData::Float(
3571                        context.map(|c| c.sheets.len() + 1).unwrap_or(1) as f64,
3572                    )),
3573                    "release" => Ok(ResultData::String("16.0".to_string())),
3574                    "system" => Ok(ResultData::String(
3575                        if cfg!(target_os = "macos") {
3576                            "mac"
3577                        } else {
3578                            "pcdos"
3579                        }
3580                        .to_string(),
3581                    )),
3582                    _ => Ok(ResultData::Error("#VALUE!".to_string())),
3583                }
3584            }
3585            "INDIRECT" => {
3586                if args.is_empty() {
3587                    return Ok(ResultData::Error("#VALUE!".to_string()));
3588                }
3589                let text = self
3590                    .evaluate_ast(&args[0], context, row, col, deps, scope)?
3591                    .to_string();
3592                let a1_style = match args.get(1) {
3593                    Some(a) => self.to_bool(&self.evaluate_ast(a, context, row, col, deps, scope)?),
3594                    None => true,
3595                };
3596                if !a1_style {
3597                    return Ok(ResultData::Error("#VALUE!".to_string()));
3598                }
3599                match Self::parse_a1_reference(&text) {
3600                    Some((sheet_opt, start_row, start_col, end_row, end_col)) => {
3601                        if start_row == end_row && start_col == end_col {
3602                            Ok(self.read_cell_with_deps(
3603                                &sheet_opt, start_row, start_col, context, deps,
3604                            ))
3605                        } else {
3606                            match self.materialize_range(
3607                                &sheet_opt, start_row, start_col, end_row, end_col, context,
3608                            ) {
3609                                Some(grid) => {
3610                                    Ok(ResultData::List(grid.into_iter().flatten().collect()))
3611                                }
3612                                None => Ok(ResultData::Error("#REF!".to_string())),
3613                            }
3614                        }
3615                    }
3616                    None => Ok(ResultData::Error("#REF!".to_string())),
3617                }
3618            }
3619            "OFFSET" => {
3620                if args.len() < 3 {
3621                    return Ok(ResultData::Error("#VALUE!".to_string()));
3622                }
3623                let Some((sheet_opt, base_row, base_col, base_end_row, base_end_col)) =
3624                    Self::range_bounds(&args[0])
3625                else {
3626                    return Ok(ResultData::Error("#VALUE!".to_string()));
3627                };
3628                let row_offset = self
3629                    .to_f64(&self.evaluate_ast(&args[1], context, row, col, deps, scope)?)
3630                    .unwrap_or(0.0) as isize;
3631                let col_offset = self
3632                    .to_f64(&self.evaluate_ast(&args[2], context, row, col, deps, scope)?)
3633                    .unwrap_or(0.0) as isize;
3634                let base_height = (base_end_row.saturating_sub(base_row) + 1) as isize;
3635                let base_width = (base_end_col.saturating_sub(base_col) + 1) as isize;
3636                let height = match args.get(3) {
3637                    Some(a) => self
3638                        .to_f64(&self.evaluate_ast(a, context, row, col, deps, scope)?)
3639                        .unwrap_or(base_height as f64) as isize,
3640                    None => base_height,
3641                };
3642                let width = match args.get(4) {
3643                    Some(a) => self
3644                        .to_f64(&self.evaluate_ast(a, context, row, col, deps, scope)?)
3645                        .unwrap_or(base_width as f64) as isize,
3646                    None => base_width,
3647                };
3648                let new_row = base_row as isize + row_offset;
3649                let new_col = base_col as isize + col_offset;
3650                if new_row < 0 || new_col < 0 || height <= 0 || width <= 0 {
3651                    return Ok(ResultData::Error("#REF!".to_string()));
3652                }
3653                let (start_row, start_col) = (new_row as usize, new_col as usize);
3654                let (end_row, end_col) = (
3655                    start_row + (height - 1) as usize,
3656                    start_col + (width - 1) as usize,
3657                );
3658                if start_row == end_row && start_col == end_col {
3659                    Ok(self.read_cell_with_deps(&sheet_opt, start_row, start_col, context, deps))
3660                } else {
3661                    match self.materialize_range(
3662                        &sheet_opt, start_row, start_col, end_row, end_col, context,
3663                    ) {
3664                        Some(grid) => Ok(ResultData::List(grid.into_iter().flatten().collect())),
3665                        None => Ok(ResultData::Error("#REF!".to_string())),
3666                    }
3667                }
3668            }
3669            _ => unreachable!(),
3670        }
3671    }
3672
3673    fn evaluate_getpivotdata(
3674        &self,
3675        args: &[crate::core::parser::Expr],
3676        context: Option<&Context>,
3677        row: Option<usize>,
3678        col: Option<usize>,
3679        deps: &mut Vec<Dependency>,
3680        scope: &LetScope<'_>,
3681    ) -> Result<ResultData, EngineError> {
3682        if args.len() < 2 || !(args.len() - 2).is_multiple_of(2) {
3683            return Ok(ResultData::Error("#VALUE!".to_string()));
3684        }
3685
3686        let data_field = self
3687            .evaluate_ast(&args[0], context, row, col, deps, scope)?
3688            .to_string();
3689
3690        let (sheet_opt, target_row, target_col, _, _) = match Self::range_bounds(&args[1]) {
3691            Some(bounds) => bounds,
3692            None => return Ok(ResultData::Error("#REF!".to_string())),
3693        };
3694        self.read_cell_with_deps(&sheet_opt, target_row, target_col, context, deps);
3695
3696        let sheet_id = match &sheet_opt {
3697            None => self.id,
3698            Some(name) if name == &self.name => self.id,
3699            Some(name) => match context.and_then(|c| c.sheets.get(name)) {
3700                Some(s) => s.id,
3701                None => return Ok(ResultData::Error("#REF!".to_string())),
3702            },
3703        };
3704
3705        let pivot_tables = context.map(|c| c.pivot_tables).unwrap_or(&[]);
3706        let pivot = match pivot_tables.iter().find(|p| {
3707            p.dest_sheet_id == sheet_id
3708                && p.last_output_end_row
3709                    .is_some_and(|end| target_row >= p.dest_row && target_row <= end)
3710                && p.last_output_end_col
3711                    .is_some_and(|end| target_col >= p.dest_col && target_col <= end)
3712        }) {
3713            Some(p) => p,
3714            None => return Ok(ResultData::Error("#REF!".to_string())),
3715        };
3716
3717        let mut criteria: Vec<(String, String)> = Vec::new();
3718        let mut i = 2;
3719        while i < args.len() {
3720            let field = self
3721                .evaluate_ast(&args[i], context, row, col, deps, scope)?
3722                .to_string();
3723            let item = self
3724                .evaluate_ast(&args[i + 1], context, row, col, deps, scope)?
3725                .to_string();
3726            criteria.push((field, item));
3727            i += 2;
3728        }
3729
3730        let mut sheet_refs: Vec<&Sheet> = context
3731            .map(|c| c.sheets.values().copied().collect())
3732            .unwrap_or_default();
3733        sheet_refs.push(self);
3734
3735        match crate::core::pivot::getpivotdata(&sheet_refs, pivot, &data_field, &criteria) {
3736            Ok(v) => Ok(v),
3737            Err(e) => Ok(ResultData::Error(e)),
3738        }
3739    }
3740
3741    #[allow(clippy::too_many_arguments)]
3742    fn evaluate_array_reshape_function(
3743        &self,
3744        func_name: &str,
3745        args: &[crate::core::parser::Expr],
3746        context: Option<&Context>,
3747        row: Option<usize>,
3748        col: Option<usize>,
3749        deps: &mut Vec<Dependency>,
3750        scope: &LetScope<'_>,
3751    ) -> Result<ResultData, EngineError> {
3752        match func_name {
3753            "TRANSPOSE" => {
3754                let Some(arg) = args.first() else {
3755                    return Ok(ResultData::Error("#VALUE!".to_string()));
3756                };
3757                let (flat, cols) = self.array_shape(arg, context, row, col, deps, scope)?;
3758                let rows = flat.len().checked_div(cols).unwrap_or(0);
3759                let mut result = Vec::with_capacity(flat.len());
3760                for c in 0..cols {
3761                    for r in 0..rows {
3762                        result.push(flat[r * cols + c].clone());
3763                    }
3764                }
3765                Ok(ResultData::List(result))
3766            }
3767            "HSTACK" | "VSTACK" => {
3768                if args.is_empty() {
3769                    return Ok(ResultData::Error("#VALUE!".to_string()));
3770                }
3771                let mut shapes = Vec::with_capacity(args.len());
3772                for a in args {
3773                    shapes.push(self.array_shape(a, context, row, col, deps, scope)?);
3774                }
3775                let mut result = Vec::new();
3776                if func_name == "HSTACK" {
3777                    let max_rows = shapes
3778                        .iter()
3779                        .map(|(f, c)| if *c == 0 { 0 } else { f.len() / c })
3780                        .max()
3781                        .unwrap_or(0);
3782                    for r in 0..max_rows {
3783                        for (flat, cols) in &shapes {
3784                            let rows = if *cols == 0 { 0 } else { flat.len() / cols };
3785                            for c in 0..*cols {
3786                                result.push(if r < rows {
3787                                    flat[r * cols + c].clone()
3788                                } else {
3789                                    ResultData::Error("#N/A".to_string())
3790                                });
3791                            }
3792                        }
3793                    }
3794                } else {
3795                    let max_cols = shapes.iter().map(|(_, c)| *c).max().unwrap_or(0);
3796                    for (flat, cols) in &shapes {
3797                        let rows = if *cols == 0 { 0 } else { flat.len() / cols };
3798                        for r in 0..rows {
3799                            for c in 0..max_cols {
3800                                result.push(if c < *cols {
3801                                    flat[r * cols + c].clone()
3802                                } else {
3803                                    ResultData::Error("#N/A".to_string())
3804                                });
3805                            }
3806                        }
3807                    }
3808                }
3809                Ok(ResultData::List(result))
3810            }
3811            "CHOOSEROWS" | "CHOOSECOLS" => {
3812                if args.len() < 2 {
3813                    return Ok(ResultData::Error("#VALUE!".to_string()));
3814                }
3815                let (flat, cols) = self.array_shape(&args[0], context, row, col, deps, scope)?;
3816                let rows = flat.len().checked_div(cols).unwrap_or(0);
3817                let total = if func_name == "CHOOSEROWS" {
3818                    rows
3819                } else {
3820                    cols
3821                } as isize;
3822                let mut indices = Vec::with_capacity(args.len() - 1);
3823                for idx_expr in &args[1..] {
3824                    let n = self
3825                        .to_f64(&self.evaluate_ast(idx_expr, context, row, col, deps, scope)?)
3826                        .unwrap_or(0.0) as isize;
3827                    let real_idx = if n < 0 { total + n } else { n - 1 };
3828                    if real_idx < 0 || real_idx >= total {
3829                        return Ok(ResultData::Error("#VALUE!".to_string()));
3830                    }
3831                    indices.push(real_idx as usize);
3832                }
3833                let mut result = Vec::new();
3834                if func_name == "CHOOSEROWS" {
3835                    for r in indices {
3836                        for c in 0..cols {
3837                            result.push(flat[r * cols + c].clone());
3838                        }
3839                    }
3840                } else {
3841                    for r in 0..rows {
3842                        for &c in &indices {
3843                            result.push(flat[r * cols + c].clone());
3844                        }
3845                    }
3846                }
3847                Ok(ResultData::List(result))
3848            }
3849            "DROP" | "TAKE" => {
3850                if args.len() < 2 {
3851                    return Ok(ResultData::Error("#VALUE!".to_string()));
3852                }
3853                let (flat, cols) = self.array_shape(&args[0], context, row, col, deps, scope)?;
3854                let num_rows = flat.len().checked_div(cols).unwrap_or(0) as isize;
3855                let is_take = func_name == "TAKE";
3856                let rows_n = self
3857                    .to_f64(&self.evaluate_ast(&args[1], context, row, col, deps, scope)?)
3858                    .unwrap_or(0.0) as isize;
3859                let cols_n = match args.get(2) {
3860                    Some(e) => self
3861                        .to_f64(&self.evaluate_ast(e, context, row, col, deps, scope)?)
3862                        .unwrap_or(0.0) as isize,
3863                    None => {
3864                        if is_take {
3865                            cols as isize
3866                        } else {
3867                            0
3868                        }
3869                    }
3870                };
3871                let (row_start, row_end) = Self::drop_take_bounds(num_rows, rows_n, is_take);
3872                let (col_start, col_end) = Self::drop_take_bounds(cols as isize, cols_n, is_take);
3873                if row_start >= row_end || col_start >= col_end {
3874                    return Ok(ResultData::Error("#CALC!".to_string()));
3875                }
3876                let mut result = Vec::new();
3877                for r in row_start..row_end {
3878                    for c in col_start..col_end {
3879                        result.push(flat[(r as usize) * cols + (c as usize)].clone());
3880                    }
3881                }
3882                Ok(ResultData::List(result))
3883            }
3884            "EXPAND" => {
3885                if args.len() < 2 {
3886                    return Ok(ResultData::Error("#VALUE!".to_string()));
3887                }
3888                let (flat, cols) = self.array_shape(&args[0], context, row, col, deps, scope)?;
3889                let orig_rows = flat.len().checked_div(cols).unwrap_or(0);
3890                let new_rows = self
3891                    .to_f64(&self.evaluate_ast(&args[1], context, row, col, deps, scope)?)
3892                    .unwrap_or(orig_rows as f64) as usize;
3893                let new_cols = match args.get(2) {
3894                    Some(e) => self
3895                        .to_f64(&self.evaluate_ast(e, context, row, col, deps, scope)?)
3896                        .unwrap_or(cols as f64) as usize,
3897                    None => cols,
3898                };
3899                let pad = match args.get(3) {
3900                    Some(e) => self.evaluate_ast(e, context, row, col, deps, scope)?,
3901                    None => ResultData::Error("#N/A".to_string()),
3902                };
3903                if new_rows < orig_rows || new_cols < cols {
3904                    return Ok(ResultData::Error("#VALUE!".to_string()));
3905                }
3906                let mut result = Vec::with_capacity(new_rows * new_cols);
3907                for r in 0..new_rows {
3908                    for c in 0..new_cols {
3909                        result.push(if r < orig_rows && c < cols {
3910                            flat[r * cols + c].clone()
3911                        } else {
3912                            pad.clone()
3913                        });
3914                    }
3915                }
3916                Ok(ResultData::List(result))
3917            }
3918            "TOCOL" | "TOROW" => {
3919                let Some(arg) = args.first() else {
3920                    return Ok(ResultData::Error("#VALUE!".to_string()));
3921                };
3922                let (flat, cols) = self.array_shape(arg, context, row, col, deps, scope)?;
3923                let rows = flat.len().checked_div(cols).unwrap_or(0);
3924                let ignore = match args.get(1) {
3925                    Some(e) => self
3926                        .to_f64(&self.evaluate_ast(e, context, row, col, deps, scope)?)
3927                        .unwrap_or(0.0) as i64,
3928                    None => 0,
3929                };
3930                let scan_by_col = match args.get(2) {
3931                    Some(e) => self.to_bool(&self.evaluate_ast(e, context, row, col, deps, scope)?),
3932                    None => false,
3933                };
3934                let ordered: Vec<ResultData> = if scan_by_col {
3935                    let mut v = Vec::with_capacity(flat.len());
3936                    for c in 0..cols {
3937                        for r in 0..rows {
3938                            v.push(flat[r * cols + c].clone());
3939                        }
3940                    }
3941                    v
3942                } else {
3943                    flat
3944                };
3945                let filtered: Vec<ResultData> = ordered
3946                    .into_iter()
3947                    .filter(|v| match ignore {
3948                        1 => !matches!(v, ResultData::None),
3949                        2 => !matches!(v, ResultData::Error(_)),
3950                        3 => !matches!(v, ResultData::None | ResultData::Error(_)),
3951                        _ => true,
3952                    })
3953                    .collect();
3954                Ok(ResultData::List(filtered))
3955            }
3956            "WRAPROWS" | "WRAPCOLS" => {
3957                if args.len() < 2 {
3958                    return Ok(ResultData::Error("#VALUE!".to_string()));
3959                }
3960                let (flat, _cols) = self.array_shape(&args[0], context, row, col, deps, scope)?;
3961                let wrap = self
3962                    .to_f64(&self.evaluate_ast(&args[1], context, row, col, deps, scope)?)
3963                    .unwrap_or(1.0)
3964                    .max(1.0) as usize;
3965                let pad = match args.get(2) {
3966                    Some(e) => self.evaluate_ast(e, context, row, col, deps, scope)?,
3967                    None => ResultData::Error("#N/A".to_string()),
3968                };
3969                if func_name == "WRAPROWS" {
3970                    let mut result = flat;
3971                    let rem = result.len() % wrap;
3972                    if rem != 0 {
3973                        result.extend(std::iter::repeat_n(pad, wrap - rem));
3974                    }
3975                    Ok(ResultData::List(result))
3976                } else {
3977                    let num_result_cols = flat.len().div_ceil(wrap).max(1);
3978                    let total = wrap * num_result_cols;
3979                    let mut result = Vec::with_capacity(total);
3980                    for i in 0..total {
3981                        let col = i / wrap;
3982                        let r = i % wrap;
3983                        let target = r * num_result_cols + col;
3984                        while result.len() <= target {
3985                            result.push(pad.clone());
3986                        }
3987                        if i < flat.len() {
3988                            result[target] = flat[i].clone();
3989                        }
3990                    }
3991                    Ok(ResultData::List(result))
3992                }
3993            }
3994            "UNIQUE" => {
3995                let Some(arg) = args.first() else {
3996                    return Ok(ResultData::Error("#VALUE!".to_string()));
3997                };
3998                let (flat, _cols) = self.array_shape(arg, context, row, col, deps, scope)?;
3999                let exactly_once = match args.get(2) {
4000                    Some(e) => self.to_bool(&self.evaluate_ast(e, context, row, col, deps, scope)?),
4001                    None => false,
4002                };
4003                let mut seen: Vec<(String, ResultData, usize)> = Vec::new();
4004                for v in &flat {
4005                    let key = match v {
4006                        ResultData::None => "blank:".to_string(),
4007                        ResultData::Boolean(b) => format!("bool:{b}"),
4008                        ResultData::Integer(i) => format!("num:{}", *i as f64),
4009                        ResultData::Float(f) => format!("num:{f}"),
4010                        ResultData::String(s) => format!("str:{s}"),
4011                        ResultData::Error(e) => format!("err:{e}"),
4012                        ResultData::List(_) | ResultData::Dict(_) => format!("other:{v}"),
4013                    };
4014                    match seen.iter_mut().find(|(k, ..)| k == &key) {
4015                        Some(entry) => entry.2 += 1,
4016                        None => seen.push((key, v.clone(), 1)),
4017                    }
4018                }
4019                let result: Vec<ResultData> = seen
4020                    .into_iter()
4021                    .filter(|(_, _, count)| !exactly_once || *count == 1)
4022                    .map(|(_, v, _)| v)
4023                    .collect();
4024                Ok(ResultData::List(result))
4025            }
4026            "SORT" => {
4027                let Some(arg) = args.first() else {
4028                    return Ok(ResultData::Error("#VALUE!".to_string()));
4029                };
4030                let (flat, cols) = self.array_shape(arg, context, row, col, deps, scope)?;
4031                let rows = flat.len().checked_div(cols).unwrap_or(0);
4032                let sort_index = match args.get(1) {
4033                    Some(e) => self
4034                        .to_f64(&self.evaluate_ast(e, context, row, col, deps, scope)?)
4035                        .unwrap_or(1.0) as usize,
4036                    None => 1,
4037                };
4038                let sort_order = match args.get(2) {
4039                    Some(e) => self
4040                        .to_f64(&self.evaluate_ast(e, context, row, col, deps, scope)?)
4041                        .unwrap_or(1.0),
4042                    None => 1.0,
4043                };
4044                let col_idx = sort_index.saturating_sub(1).min(cols.saturating_sub(1));
4045                let mut row_indices: Vec<usize> = (0..rows).collect();
4046                row_indices.sort_by(|&a, &b| {
4047                    Self::sort_compare_blanks_last(
4048                        &flat[a * cols + col_idx],
4049                        &flat[b * cols + col_idx],
4050                        sort_order,
4051                    )
4052                });
4053                let mut result = Vec::with_capacity(flat.len());
4054                for r in row_indices {
4055                    for c in 0..cols {
4056                        result.push(flat[r * cols + c].clone());
4057                    }
4058                }
4059                Ok(ResultData::List(result))
4060            }
4061            "SORTBY" => {
4062                if args.len() < 2 {
4063                    return Ok(ResultData::Error("#VALUE!".to_string()));
4064                }
4065                let (flat, cols) = self.array_shape(&args[0], context, row, col, deps, scope)?;
4066                let rows = flat.len().checked_div(cols).unwrap_or(0);
4067                let by = self.eval_as_array(&args[1], context, row, col, deps, scope)?;
4068                let order = match args.get(2) {
4069                    Some(e) => self
4070                        .to_f64(&self.evaluate_ast(e, context, row, col, deps, scope)?)
4071                        .unwrap_or(1.0),
4072                    None => 1.0,
4073                };
4074                let mut row_indices: Vec<usize> = (0..rows).collect();
4075                row_indices.sort_by(|&a, &b| {
4076                    let va = by.get(a).cloned().unwrap_or(ResultData::None);
4077                    let vb = by.get(b).cloned().unwrap_or(ResultData::None);
4078                    Self::sort_compare_blanks_last(&va, &vb, order)
4079                });
4080                let mut result = Vec::with_capacity(flat.len());
4081                for r in row_indices {
4082                    for c in 0..cols {
4083                        result.push(flat[r * cols + c].clone());
4084                    }
4085                }
4086                Ok(ResultData::List(result))
4087            }
4088            "FILTER" => {
4089                if args.len() < 2 {
4090                    return Ok(ResultData::Error("#VALUE!".to_string()));
4091                }
4092                let (flat, cols) = self.array_shape(&args[0], context, row, col, deps, scope)?;
4093                let rows = flat.len().checked_div(cols).unwrap_or(0);
4094                let include = self.eval_as_array(&args[1], context, row, col, deps, scope)?;
4095                let mut result = Vec::new();
4096                for r in 0..rows {
4097                    let keep = include.get(r).map(|v| self.to_bool(v)).unwrap_or(false);
4098                    if keep {
4099                        for c in 0..cols {
4100                            result.push(flat[r * cols + c].clone());
4101                        }
4102                    }
4103                }
4104                if result.is_empty() {
4105                    match args.get(2) {
4106                        Some(e) => Ok(self.evaluate_ast(e, context, row, col, deps, scope)?),
4107                        None => Ok(ResultData::Error("#CALC!".to_string())),
4108                    }
4109                } else {
4110                    Ok(ResultData::List(result))
4111                }
4112            }
4113            "TRIMRANGE" => {
4114                let Some(arg) = args.first() else {
4115                    return Ok(ResultData::Error("#VALUE!".to_string()));
4116                };
4117                let (flat, cols) = self.array_shape(arg, context, row, col, deps, scope)?;
4118                let rows = flat.len().checked_div(cols).unwrap_or(0);
4119                let is_blank = |v: &ResultData| {
4120                    matches!(v, ResultData::None)
4121                        || matches!(v, ResultData::String(s) if s.is_empty())
4122                };
4123                let row_blank = |r: usize| (0..cols).all(|c| is_blank(&flat[r * cols + c]));
4124                let col_blank = |c: usize| (0..rows).all(|r| is_blank(&flat[r * cols + c]));
4125                let mut r_start = 0;
4126                while r_start < rows && row_blank(r_start) {
4127                    r_start += 1;
4128                }
4129                let mut r_end = rows;
4130                while r_end > r_start && row_blank(r_end - 1) {
4131                    r_end -= 1;
4132                }
4133                let mut c_start = 0;
4134                while c_start < cols && col_blank(c_start) {
4135                    c_start += 1;
4136                }
4137                let mut c_end = cols;
4138                while c_end > c_start && col_blank(c_end - 1) {
4139                    c_end -= 1;
4140                }
4141                let mut result = Vec::new();
4142                for r in r_start..r_end {
4143                    for c in c_start..c_end {
4144                        result.push(flat[r * cols + c].clone());
4145                    }
4146                }
4147                Ok(ResultData::List(result))
4148            }
4149            _ => unreachable!(),
4150        }
4151    }
4152
4153    /// The raw text typed into a cell. Returns `None` if ref is OOB
4154    pub fn get_src(&self, cell: &CellRef) -> Option<&String> {
4155        let col = self.columns.get(cell.col);
4156        if let Some(col) = col {
4157            col.src.get(cell.row)
4158        } else {
4159            None
4160        }
4161    }
4162
4163    /// [`Sheet::get_src`] but returns empty string on OOB.
4164    pub fn get_src_str(&self, cell: &CellRef) -> String {
4165        let col = self.columns.get(cell.col);
4166        if let Some(col) = col {
4167            col.src.get(cell.row).cloned().unwrap_or("".to_string())
4168        } else {
4169            "".to_string()
4170        }
4171    }
4172
4173    /// [`Sheet::get_src`] as a borrowed `&str` for read-only.
4174    pub fn get_src_str_ref(&self, cell: &CellRef) -> Option<&str> {
4175        let col = self.columns.get(cell.col)?;
4176        col.src.get(cell.row).map(|s| s.as_str())
4177    }
4178
4179    /// See [`get_word_boundaries_from_str`].
4180    pub fn get_word_boundaries(&self, cell: &CellRef, char_offset: usize) -> (usize, usize) {
4181        let text = self.get_src_str(cell);
4182        get_word_boundaries_from_str(&text, char_offset)
4183    }
4184}
visi_core/core/engine/sheet/mod.rs

visi_core/core/engine/sheet/
mod.rs
