Skip to main content

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        for (ca, cb) in a.chars().zip(b.chars()) {
1688            let wa = char_weight(ca);
1689            let wb = char_weight(cb);
1690            if wa != wb {
1691                return wa.cmp(&wb);
1692            }
1693        }
1694        a.len().cmp(&b.len())
1695    }
1696
1697    pub(crate) fn clean_float(val: f64) -> f64 {
1698        if val == 0.0 || !val.is_finite() {
1699            return val;
1700        }
1701        let abs_val = val.abs();
1702        let exp = abs_val.log10().floor() as i32;
1703        let factor = 10.0f64.powi(15 - 1 - exp);
1704        if factor.is_finite() && factor != 0.0 {
1705            let rounded = (val * factor).round() / factor;
1706            if (val - rounded).abs() <= 1e-14 * abs_val {
1707                return rounded;
1708            }
1709        }
1710        val
1711    }
1712
1713    pub(crate) fn to_f64(&self, val: &ResultData) -> Option<f64> {
1714        match val {
1715            ResultData::None => Some(0.0),
1716            ResultData::Float(f) => Some(*f),
1717            ResultData::Integer(i) => Some(*i as f64),
1718            ResultData::Boolean(b) => Some(if *b { 1.0 } else { 0.0 }),
1719            ResultData::String(s) => {
1720                let s_trim = s.trim();
1721                if Self::is_excel_number_str(s_trim) {
1722                    if let Ok(f) = s_trim.parse::<f64>() {
1723                        return Some(f);
1724                    }
1725                    if let Some((date, _)) =
1726                        crate::core::date::parse_date_with_locale(s_trim, &self.locale)
1727                    {
1728                        return Some(crate::core::date::date_to_excel_serial(date));
1729                    }
1730                    None
1731                } else if let Some((date, _)) =
1732                    crate::core::date::parse_date_with_locale(s_trim, &self.locale)
1733                {
1734                    Some(crate::core::date::date_to_excel_serial(date))
1735                } else {
1736                    None
1737                }
1738            }
1739            _ => None,
1740        }
1741    }
1742
1743    fn to_f64_arg(&self, arg_opt: Option<&ResultData>, fn_name: &str) -> Result<f64, EngineError> {
1744        let val = arg_opt.ok_or_else(|| {
1745            EngineError::EvalError(EvalError::UnknownFunction(format!(
1746                "{} requires argument",
1747                fn_name
1748            )))
1749        })?;
1750        if let ResultData::Error(e) = val {
1751            return Err(EngineError::EvalError(EvalError::UnknownFunction(
1752                e.clone(),
1753            )));
1754        }
1755        self.to_f64(val).ok_or_else(|| {
1756            EngineError::EvalError(EvalError::UnknownFunction("#VALUE!".to_string()))
1757        })
1758    }
1759
1760    fn find_error_in_args(args: &[ResultData]) -> Option<ResultData> {
1761        for arg in args {
1762            match arg {
1763                ResultData::Error(_) => return Some(arg.clone()),
1764                ResultData::List(list) => {
1765                    if let Some(err) = Self::find_error_in_args(list) {
1766                        return Some(err);
1767                    }
1768                }
1769                _ => {}
1770            }
1771        }
1772        None
1773    }
1774
1775    fn check_arg_errors(&self, args: &[ResultData], is_direct: &[bool]) -> Option<ResultData> {
1776        for (i, arg) in args.iter().enumerate() {
1777            match arg {
1778                ResultData::Error(_) => return Some(arg.clone()),
1779                ResultData::List(list) => {
1780                    if let Some(err) = self.check_arg_errors(list, &[]) {
1781                        return Some(err);
1782                    }
1783                }
1784                ResultData::String(_)
1785                    if is_direct.get(i).copied().unwrap_or(false) && self.to_f64(arg).is_none() =>
1786                {
1787                    return Some(ResultData::Error("#VALUE!".to_string()));
1788                }
1789                _ => {}
1790            }
1791        }
1792        None
1793    }
1794
1795    fn sum_helper(&self, arg: &ResultData, is_direct: bool) -> f64 {
1796        match arg {
1797            ResultData::Float(f) => *f,
1798            ResultData::Integer(i) => *i as f64,
1799            ResultData::Boolean(b) => {
1800                if is_direct {
1801                    if *b { 1.0 } else { 0.0 }
1802                } else {
1803                    0.0
1804                }
1805            }
1806            ResultData::String(_) => {
1807                if is_direct {
1808                    self.to_f64(arg).unwrap_or(0.0)
1809                } else {
1810                    0.0
1811                }
1812            }
1813            ResultData::List(list) => {
1814                let mut sum = 0.0;
1815                for item in list {
1816                    sum += self.sum_helper(item, false);
1817                }
1818                sum
1819            }
1820            _ => 0.0,
1821        }
1822    }
1823
1824    fn flatten_finance_numbers(&self, arg: &ResultData, is_direct: bool) -> Vec<f64> {
1825        match arg {
1826            ResultData::Float(f) => vec![*f],
1827            ResultData::Integer(i) => vec![*i as f64],
1828            ResultData::Boolean(b) => {
1829                if is_direct {
1830                    vec![if *b { 1.0 } else { 0.0 }]
1831                } else {
1832                    vec![]
1833                }
1834            }
1835            ResultData::String(_) => {
1836                if is_direct {
1837                    self.to_f64(arg).into_iter().collect()
1838                } else {
1839                    vec![]
1840                }
1841            }
1842            ResultData::List(list) => list
1843                .iter()
1844                .flat_map(|v| self.flatten_finance_numbers(v, false))
1845                .collect(),
1846            _ => vec![],
1847        }
1848    }
1849
1850    fn flatten_stat_numbers(&self, arg: &ResultData, is_direct: bool) -> Vec<f64> {
1851        match arg {
1852            ResultData::Float(f) => vec![*f],
1853            ResultData::Integer(i) => vec![*i as f64],
1854            ResultData::Boolean(b) => {
1855                if is_direct {
1856                    vec![if *b { 1.0 } else { 0.0 }]
1857                } else {
1858                    vec![]
1859                }
1860            }
1861            ResultData::String(_) => {
1862                if is_direct {
1863                    self.to_f64(arg).into_iter().collect()
1864                } else {
1865                    vec![]
1866                }
1867            }
1868            ResultData::List(list) => list
1869                .iter()
1870                .flat_map(|v| self.flatten_stat_numbers(v, false))
1871                .collect(),
1872            _ => vec![],
1873        }
1874    }
1875
1876    fn flatten_positional(
1877        &self,
1878        arg: &ResultData,
1879        out: &mut Vec<Option<f64>>,
1880        first_err: &mut Option<String>,
1881    ) {
1882        match arg {
1883            ResultData::List(items) => {
1884                for item in items {
1885                    self.flatten_positional(item, out, first_err);
1886                }
1887            }
1888            ResultData::Float(f) => out.push(Some(*f)),
1889            ResultData::Integer(i) => out.push(Some(*i as f64)),
1890            ResultData::Error(e) => {
1891                if first_err.is_none() {
1892                    *first_err = Some(e.clone());
1893                }
1894                out.push(None);
1895            }
1896            _ => out.push(None),
1897        }
1898    }
1899
1900    fn positional_numbers(
1901        &self,
1902        arg: Option<&ResultData>,
1903        first_err: &mut Option<String>,
1904    ) -> Vec<Option<f64>> {
1905        let mut out = Vec::new();
1906        if let Some(a) = arg {
1907            self.flatten_positional(a, &mut out, first_err);
1908        }
1909        out
1910    }
1911
1912    fn pair_and_filter(
1913        xs_raw: Vec<Option<f64>>,
1914        ys_raw: Vec<Option<f64>>,
1915    ) -> Result<(Vec<f64>, Vec<f64>), String> {
1916        if xs_raw.len() != ys_raw.len() {
1917            return Err("#N/A".to_string());
1918        }
1919        let mut xs = Vec::with_capacity(xs_raw.len());
1920        let mut ys = Vec::with_capacity(ys_raw.len());
1921        for (x, y) in xs_raw.into_iter().zip(ys_raw) {
1922            if let (Some(x), Some(y)) = (x, y) {
1923                xs.push(x);
1924                ys.push(y);
1925            }
1926        }
1927        Ok((xs, ys))
1928    }
1929
1930    fn paired_args(
1931        &self,
1932        x_arg: Option<&ResultData>,
1933        y_arg: Option<&ResultData>,
1934    ) -> Result<(Vec<f64>, Vec<f64>), String> {
1935        for arg in [x_arg, y_arg].into_iter().flatten() {
1936            let scalar = match arg {
1937                ResultData::List(items) if items.len() == 1 => &items[0],
1938                other => other,
1939            };
1940            if let ResultData::Error(e) = scalar {
1941                return Err(e.clone());
1942            }
1943            if Self::is_empty_scalar_operand(arg) {
1944                return Err("#VALUE!".to_string());
1945            }
1946        }
1947        let mut first_err = None;
1948        let xs_raw = self.positional_numbers(x_arg, &mut first_err);
1949        let ys_raw = self.positional_numbers(y_arg, &mut first_err);
1950        if xs_raw.len() != ys_raw.len() {
1951            return Err("#N/A".to_string());
1952        }
1953        if let Some(e) = first_err {
1954            return Err(e);
1955        }
1956        Self::pair_and_filter(xs_raw, ys_raw)
1957    }
1958
1959    fn flatten_strict_inner(
1960        &self,
1961        arg: &ResultData,
1962        blanks: BlankPolicy,
1963        coerce_text: bool,
1964        out: &mut Vec<f64>,
1965    ) -> Result<(), String> {
1966        match arg {
1967            ResultData::List(items) => {
1968                for item in items {
1969                    self.flatten_strict_inner(item, blanks, coerce_text, out)?;
1970                }
1971                Ok(())
1972            }
1973            ResultData::Error(e) => Err(e.clone()),
1974            ResultData::Float(f) => {
1975                out.push(*f);
1976                Ok(())
1977            }
1978            ResultData::Integer(i) => {
1979                out.push(*i as f64);
1980                Ok(())
1981            }
1982            ResultData::None => match blanks {
1983                BlankPolicy::Zero => {
1984                    out.push(0.0);
1985                    Ok(())
1986                }
1987                BlankPolicy::Skip => Ok(()),
1988                BlankPolicy::Reject => Err("#VALUE!".to_string()),
1989            },
1990            ResultData::String(_) if coerce_text => match self.to_f64(arg) {
1991                Some(f) => {
1992                    out.push(f);
1993                    Ok(())
1994                }
1995                None => Err("#VALUE!".to_string()),
1996            },
1997            _ => Err("#VALUE!".to_string()),
1998        }
1999    }
2000
2001    fn flatten_strict_numbers(&self, arg: &ResultData) -> Result<Vec<f64>, String> {
2002        let mut out = Vec::new();
2003        self.flatten_strict_inner(arg, BlankPolicy::Zero, true, &mut out)?;
2004        Ok(out)
2005    }
2006
2007    fn flatten_skipping_blanks(&self, arg: Option<&ResultData>) -> Result<Vec<f64>, String> {
2008        let mut out = Vec::new();
2009        if let Some(a) = arg {
2010            self.flatten_strict_inner(a, BlankPolicy::Skip, true, &mut out)?;
2011        }
2012        Ok(out)
2013    }
2014
2015    fn flatten_skipping_blanks_no_text_coercion(
2016        &self,
2017        arg: Option<&ResultData>,
2018    ) -> Result<Vec<f64>, String> {
2019        let mut out = Vec::new();
2020        if let Some(a) = arg {
2021            self.flatten_strict_inner(a, BlankPolicy::Skip, false, &mut out)?;
2022        }
2023        Ok(out)
2024    }
2025
2026    fn flatten_numbers_only(&self, arg: &ResultData) -> Result<Vec<f64>, String> {
2027        let mut out = Vec::new();
2028        self.flatten_strict_inner(arg, BlankPolicy::Reject, false, &mut out)?;
2029        Ok(out)
2030    }
2031
2032    fn aggregate_range_number(val: &ResultData) -> Option<f64> {
2033        match val {
2034            ResultData::Float(f) => Some(*f),
2035            ResultData::Integer(i) => Some(*i as f64),
2036            _ => None,
2037        }
2038    }
2039
2040    fn flatten_numbers_only_arg(&self, arg: Option<&ResultData>) -> Result<Vec<f64>, String> {
2041        match arg {
2042            Some(a) => self.flatten_numbers_only(a),
2043            None => Ok(vec![]),
2044        }
2045    }
2046
2047    fn flatten_args_stat_numbers(
2048        &self,
2049        args: &[ResultData],
2050        is_direct: &[bool],
2051    ) -> Result<Vec<f64>, String> {
2052        let mut out = Vec::new();
2053        for (i, arg) in args.iter().enumerate() {
2054            let direct = is_direct.get(i).copied().unwrap_or(false);
2055            if direct && matches!(arg, ResultData::String(_)) && self.to_f64(arg).is_none() {
2056                return Err("#VALUE!".to_string());
2057            }
2058            out.extend(self.flatten_stat_numbers(arg, direct));
2059        }
2060        Ok(out)
2061    }
2062
2063    fn flatten_stat_numbers_a(
2064        &self,
2065        arg: &ResultData,
2066        is_direct: bool,
2067    ) -> Result<Vec<f64>, String> {
2068        Ok(match arg {
2069            ResultData::Float(f) => vec![*f],
2070            ResultData::Integer(i) => vec![*i as f64],
2071            ResultData::Boolean(b) => vec![if *b { 1.0 } else { 0.0 }],
2072            ResultData::String(_) => {
2073                if is_direct {
2074                    match self.to_f64(arg) {
2075                        Some(f) => vec![f],
2076                        None => return Err("#VALUE!".to_string()),
2077                    }
2078                } else {
2079                    vec![0.0]
2080                }
2081            }
2082            ResultData::Error(e) => return Err(e.clone()),
2083            ResultData::List(list) => {
2084                let mut out = Vec::new();
2085                for v in list {
2086                    out.extend(self.flatten_stat_numbers_a(v, false)?);
2087                }
2088                out
2089            }
2090            ResultData::None => vec![],
2091            _ => vec![0.0],
2092        })
2093    }
2094
2095    fn flatten_args_stat_numbers_a(
2096        &self,
2097        args: &[ResultData],
2098        is_direct: &[bool],
2099    ) -> Result<Vec<f64>, String> {
2100        let mut out = Vec::new();
2101        for (i, arg) in args.iter().enumerate() {
2102            out.extend(
2103                self.flatten_stat_numbers_a(arg, is_direct.get(i).copied().unwrap_or(false))?,
2104            );
2105        }
2106        Ok(out)
2107    }
2108
2109    fn extract_matrix(&self, arg: &ResultData) -> Vec<Vec<f64>> {
2110        match arg {
2111            ResultData::List(list) => {
2112                let mut rows = Vec::new();
2113                for item in list {
2114                    match item {
2115                        ResultData::List(sub_list) => {
2116                            let row: Vec<f64> =
2117                                sub_list.iter().flat_map(|v| self.to_f64(v)).collect();
2118                            if !row.is_empty() {
2119                                rows.push(row);
2120                            }
2121                        }
2122                        _ => {
2123                            if let Some(f) = self.to_f64(item) {
2124                                rows.push(vec![f]);
2125                            }
2126                        }
2127                    }
2128                }
2129                rows
2130            }
2131            _ => vec![],
2132        }
2133    }
2134
2135    fn matrix_from_arg(
2136        &self,
2137        expr: &crate::core::parser::Expr,
2138        value: &ResultData,
2139    ) -> Vec<Vec<f64>> {
2140        if let ResultData::List(items) = value
2141            && items.iter().any(|i| matches!(i, ResultData::List(_)))
2142        {
2143            return self.extract_matrix(value);
2144        }
2145        fn plain(v: &ResultData) -> Option<f64> {
2146            match v {
2147                ResultData::Float(f) => Some(*f),
2148                ResultData::Integer(i) => Some(*i as f64),
2149                _ => None,
2150            }
2151        }
2152        let items: Vec<&ResultData> = match value {
2153            ResultData::List(items) => items.iter().collect(),
2154            other => vec![other],
2155        };
2156        if items.iter().any(|v| plain(v).is_none()) {
2157            return Vec::new();
2158        }
2159        let flat: Vec<f64> = items.iter().filter_map(|v| plain(v)).collect();
2160        let cols = match Self::range_bounds(expr) {
2161            Some((_, _, start_col, _, end_col)) => end_col.saturating_sub(start_col) + 1,
2162            None => flat.len().max(1),
2163        };
2164        if cols == 0 || !flat.len().is_multiple_of(cols) {
2165            return self.extract_matrix(value);
2166        }
2167        flat.chunks(cols).map(|c| c.to_vec()).collect()
2168    }
2169
2170    fn paired_sum_has_no_numbers(&self, arg: Option<&ResultData>) -> bool {
2171        let mut ignored = None;
2172        let slots = self.positional_numbers(arg, &mut ignored);
2173        slots.iter().all(|v| v.is_none())
2174    }
2175
2176    fn is_empty_scalar_operand(arg: &ResultData) -> bool {
2177        let scalar = match arg {
2178            ResultData::List(items) if items.len() == 1 => &items[0],
2179            other => other,
2180        };
2181        matches!(scalar, ResultData::None)
2182    }
2183
2184    fn first_arg_is_boolean(args: &[ResultData]) -> bool {
2185        matches!(args.first(), Some(ResultData::Boolean(_)))
2186    }
2187
2188    fn opt_f64_arg(&self, args: &[ResultData], i: usize, default: f64) -> Result<f64, EngineError> {
2189        match args.get(i) {
2190            None => Ok(default),
2191            Some(ResultData::None) => Ok(0.0),
2192            Some(ResultData::Error(e)) => Err(EngineError::EvalError(EvalError::UnknownFunction(
2193                e.clone(),
2194            ))),
2195            Some(v) => self.to_f64(v).ok_or_else(|| {
2196                EngineError::EvalError(EvalError::UnknownFunction("#VALUE!".to_string()))
2197            }),
2198        }
2199    }
2200
2201    fn opt_f64(&self, args: &[ResultData], i: usize, default: f64) -> f64 {
2202        args.get(i).and_then(|v| self.to_f64(v)).unwrap_or(default)
2203    }
2204
2205    fn average_helper(&self, arg: &ResultData, is_direct: bool) -> (f64, usize) {
2206        match arg {
2207            ResultData::Float(f) => (*f, 1),
2208            ResultData::Integer(i) => (*i as f64, 1),
2209            ResultData::Boolean(b) => {
2210                if is_direct {
2211                    (if *b { 1.0 } else { 0.0 }, 1)
2212                } else {
2213                    (0.0, 0)
2214                }
2215            }
2216            ResultData::String(_) => {
2217                if is_direct {
2218                    if let Some(f) = self.to_f64(arg) {
2219                        (f, 1)
2220                    } else {
2221                        (0.0, 0)
2222                    }
2223                } else {
2224                    (0.0, 0)
2225                }
2226            }
2227            ResultData::List(list) => {
2228                let mut sum = 0.0;
2229                let mut count = 0;
2230                for item in list {
2231                    let (s, c) = self.average_helper(item, false);
2232                    sum += s;
2233                    count += c;
2234                }
2235                (sum, count)
2236            }
2237            _ => (0.0, 0),
2238        }
2239    }
2240
2241    fn count_helper(&self, arg: &ResultData) -> usize {
2242        match arg {
2243            ResultData::Float(_) | ResultData::Integer(_) => 1,
2244            ResultData::List(list) => {
2245                let mut count = 0;
2246                for item in list {
2247                    count += self.count_helper(item);
2248                }
2249                count
2250            }
2251            _ => 0,
2252        }
2253    }
2254
2255    fn min_helper(&self, arg: &ResultData, is_direct: bool) -> f64 {
2256        match arg {
2257            ResultData::Float(f) => *f,
2258            ResultData::Integer(i) => *i as f64,
2259            ResultData::Boolean(b) => {
2260                if is_direct {
2261                    if *b { 1.0 } else { 0.0 }
2262                } else {
2263                    f64::INFINITY
2264                }
2265            }
2266            ResultData::String(_) => {
2267                if is_direct {
2268                    self.to_f64(arg).unwrap_or(f64::INFINITY)
2269                } else {
2270                    f64::INFINITY
2271                }
2272            }
2273            ResultData::List(list) => {
2274                let mut min_val = f64::INFINITY;
2275                for item in list {
2276                    min_val = min_val.min(self.min_helper(item, false));
2277                }
2278                min_val
2279            }
2280            _ => f64::INFINITY,
2281        }
2282    }
2283
2284    fn max_helper(&self, arg: &ResultData, is_direct: bool) -> f64 {
2285        match arg {
2286            ResultData::Float(f) => *f,
2287            ResultData::Integer(i) => *i as f64,
2288            ResultData::Boolean(b) => {
2289                if is_direct {
2290                    if *b { 1.0 } else { 0.0 }
2291                } else {
2292                    f64::NEG_INFINITY
2293                }
2294            }
2295            ResultData::String(_) => {
2296                if is_direct {
2297                    self.to_f64(arg).unwrap_or(f64::NEG_INFINITY)
2298                } else {
2299                    f64::NEG_INFINITY
2300                }
2301            }
2302            ResultData::List(list) => {
2303                let mut max_val = f64::NEG_INFINITY;
2304                for item in list {
2305                    max_val = max_val.max(self.max_helper(item, false));
2306                }
2307                max_val
2308            }
2309            _ => f64::NEG_INFINITY,
2310        }
2311    }
2312
2313    fn concat_helper(&self, arg: &ResultData, out: &mut String) {
2314        match arg {
2315            ResultData::List(list) => {
2316                for item in list {
2317                    self.concat_helper(item, out);
2318                }
2319            }
2320            other => {
2321                out.push_str(&other.to_string());
2322            }
2323        }
2324    }
2325
2326    fn concat_text(arg: &ResultData) -> Result<String, String> {
2327        match arg {
2328            ResultData::Error(e) => Err(e.clone()),
2329            ResultData::List(list) => {
2330                let mut out = String::new();
2331                for item in list {
2332                    out.push_str(&Self::concat_text(item)?);
2333                }
2334                Ok(out)
2335            }
2336            other => Ok(other.to_string()),
2337        }
2338    }
2339
2340    fn counta_helper(&self, arg: &ResultData) -> usize {
2341        match arg {
2342            ResultData::None => 0,
2343            ResultData::List(list) => {
2344                let mut count = 0;
2345                for item in list {
2346                    count += self.counta_helper(item);
2347                }
2348                count
2349            }
2350            _ => 1,
2351        }
2352    }
2353
2354    fn product_helper(&self, arg: &ResultData, is_direct: bool) -> (f64, bool) {
2355        match arg {
2356            ResultData::Float(f) => (*f, true),
2357            ResultData::Integer(i) => (*i as f64, true),
2358            ResultData::Boolean(b) => {
2359                if is_direct {
2360                    (if *b { 1.0 } else { 0.0 }, true)
2361                } else {
2362                    (1.0, false)
2363                }
2364            }
2365            ResultData::String(_) => {
2366                if is_direct {
2367                    if let Some(f) = self.to_f64(arg) {
2368                        (f, true)
2369                    } else {
2370                        (1.0, false)
2371                    }
2372                } else {
2373                    (1.0, false)
2374                }
2375            }
2376            ResultData::List(list) => {
2377                let mut prod = 1.0;
2378                let mut has_nums = false;
2379                for item in list {
2380                    let (p, h) = self.product_helper(item, false);
2381                    if h {
2382                        prod *= p;
2383                        has_nums = true;
2384                    }
2385                }
2386                (prod, has_nums)
2387            }
2388            _ => (1.0, false),
2389        }
2390    }
2391
2392    fn to_bool_opt(&self, val: &ResultData) -> Option<bool> {
2393        match val {
2394            ResultData::Boolean(b) => Some(*b),
2395            ResultData::Integer(i) => Some(*i != 0),
2396            ResultData::Float(f) => Some(*f != 0.0),
2397            ResultData::String(s) => {
2398                let s_trim = s.trim();
2399                if s_trim.eq_ignore_ascii_case("true") {
2400                    Some(true)
2401                } else if s_trim.eq_ignore_ascii_case("false") {
2402                    Some(false)
2403                } else if let Ok(f) = s_trim.parse::<f64>() {
2404                    Some(f != 0.0)
2405                } else {
2406                    None
2407                }
2408            }
2409            ResultData::None => Some(false),
2410            _ => None,
2411        }
2412    }
2413
2414    fn to_bool(&self, val: &ResultData) -> bool {
2415        self.to_bool_opt(val).unwrap_or(false)
2416    }
2417
2418    fn range_numeric(val: &ResultData) -> Option<f64> {
2419        match val {
2420            ResultData::Integer(i) => Some(*i as f64),
2421            ResultData::Float(f) => Some(*f),
2422            _ => None,
2423        }
2424    }
2425
2426    fn exact_lookup_matches(lookup: &ResultData, candidate: &ResultData) -> bool {
2427        if matches!(candidate, ResultData::None) {
2428            return false;
2429        }
2430        let lookup_key = match lookup {
2431            ResultData::None => "0".to_string(),
2432            other => other.to_string(),
2433        };
2434        candidate.to_string() == lookup_key
2435    }
2436
2437    fn wildcard_criteria_matches(pattern: &str, text: &str) -> bool {
2438        fn rec(pat: &[char], txt: &[char]) -> bool {
2439            if pat.is_empty() {
2440                return txt.is_empty();
2441            }
2442            match pat[0] {
2443                '*' => rec(&pat[1..], txt) || (!txt.is_empty() && rec(pat, &txt[1..])),
2444                '?' => !txt.is_empty() && rec(&pat[1..], &txt[1..]),
2445                '~' if pat.len() > 1 && matches!(pat[1], '*' | '?' | '~') => {
2446                    !txt.is_empty() && pat[1] == txt[0] && rec(&pat[2..], &txt[1..])
2447                }
2448                ch => !txt.is_empty() && ch == txt[0] && rec(&pat[1..], &txt[1..]),
2449            }
2450        }
2451
2452        let pat = pattern.to_lowercase().chars().collect::<Vec<_>>();
2453        let txt = text.to_lowercase().chars().collect::<Vec<_>>();
2454        rec(&pat, &txt)
2455    }
2456
2457    fn criteria_text_eq(val: &ResultData, pattern: &str) -> bool {
2458        let text = val.to_string();
2459        if pattern.contains('*') || pattern.contains('?') {
2460            matches!(val, ResultData::String(_)) && Self::wildcard_criteria_matches(pattern, &text)
2461        } else {
2462            text.to_lowercase() == pattern.to_lowercase()
2463        }
2464    }
2465
2466    fn match_criteria(&self, val: &ResultData, criteria: &ResultData) -> bool {
2467        let crit_str = criteria.to_string();
2468        if let Some(rest) = crit_str.strip_prefix(">=") {
2469            let val_f = match Self::range_numeric(val) {
2470                Some(f) => f,
2471                None => return false,
2472            };
2473            let crit_f = rest.trim().parse::<f64>().unwrap_or(0.0);
2474            val_f >= crit_f
2475        } else if let Some(rest) = crit_str.strip_prefix('>') {
2476            let val_f = match Self::range_numeric(val) {
2477                Some(f) => f,
2478                None => return false,
2479            };
2480            let crit_f = rest.trim().parse::<f64>().unwrap_or(0.0);
2481            val_f > crit_f
2482        } else if let Some(rest) = crit_str.strip_prefix("<>") {
2483            let remainder = rest.trim();
2484            !Self::criteria_text_eq(val, remainder)
2485        } else if let Some(rest) = crit_str.strip_prefix("<=") {
2486            let val_f = match Self::range_numeric(val) {
2487                Some(f) => f,
2488                None => return false,
2489            };
2490            let crit_f = rest.trim().parse::<f64>().unwrap_or(0.0);
2491            val_f <= crit_f
2492        } else if let Some(rest) = crit_str.strip_prefix('<') {
2493            let val_f = match Self::range_numeric(val) {
2494                Some(f) => f,
2495                None => return false,
2496            };
2497            let crit_f = rest.trim().parse::<f64>().unwrap_or(0.0);
2498            val_f < crit_f
2499        } else if let Some(rest) = crit_str.strip_prefix('=') {
2500            let remainder = rest.trim();
2501            Self::criteria_text_eq(val, remainder)
2502        } else {
2503            Self::criteria_text_eq(val, &crit_str)
2504        }
2505    }
2506
2507    fn range_bounds(
2508        expr: &crate::core::parser::Expr,
2509    ) -> Option<(Option<String>, usize, usize, usize, usize)> {
2510        use crate::core::parser::Expr;
2511        match expr {
2512            Expr::RangeRef {
2513                sheet,
2514                start_row,
2515                start_col,
2516                end_row,
2517                end_col,
2518                ..
2519            } => Some((sheet.clone(), *start_row, *start_col, *end_row, *end_col)),
2520            Expr::CellRef {
2521                sheet, row, col, ..
2522            } => Some((sheet.clone(), *row, *col, *row, *col)),
2523            _ => None,
2524        }
2525    }
2526
2527    fn materialize_range(
2528        &self,
2529        sheet_opt: &Option<String>,
2530        start_row: usize,
2531        start_col: usize,
2532        end_row: usize,
2533        end_col: usize,
2534        context: Option<&Context>,
2535    ) -> Option<Vec<Vec<ResultData>>> {
2536        let is_self = match sheet_opt {
2537            Some(name) => name == &self.name,
2538            None => true,
2539        };
2540        let source: &Sheet = if is_self {
2541            self
2542        } else {
2543            context?.sheets.get(sheet_opt.as_ref()?)?
2544        };
2545        let actual_end_row = if end_row == usize::MAX {
2546            source.row_count().saturating_sub(1)
2547        } else {
2548            end_row
2549        };
2550        let actual_end_col = if end_col == usize::MAX {
2551            source.col_count().saturating_sub(1)
2552        } else {
2553            end_col
2554        };
2555        if actual_end_row < start_row || actual_end_col < start_col {
2556            return Some(Vec::new());
2557        }
2558        let mut grid = Vec::with_capacity(actual_end_row - start_row + 1);
2559        for r in start_row..=actual_end_row {
2560            let mut row = Vec::with_capacity(actual_end_col - start_col + 1);
2561            for c in start_col..=actual_end_col {
2562                row.push(source.get_result_data(&CellRef::new(r, c)));
2563            }
2564            grid.push(row);
2565        }
2566        Some(grid)
2567    }
2568
2569    fn evaluate_database_function(
2570        &self,
2571        func_name: &str,
2572        args: &[crate::core::parser::Expr],
2573        evaluated_args: &[ResultData],
2574        context: Option<&Context>,
2575    ) -> Result<ResultData, EngineError> {
2576        if args.len() < 3 || evaluated_args.len() < 3 {
2577            return Ok(ResultData::Error("#VALUE!".to_string()));
2578        }
2579        let (db_sheet, db_sr, db_sc, db_er, db_ec) = match Self::range_bounds(&args[0]) {
2580            Some(v) => v,
2581            None => return Ok(ResultData::Error("#VALUE!".to_string())),
2582        };
2583        let (crit_sheet, crit_sr, crit_sc, crit_er, crit_ec) = match Self::range_bounds(&args[2]) {
2584            Some(v) => v,
2585            None => return Ok(ResultData::Error("#VALUE!".to_string())),
2586        };
2587        let db = match self.materialize_range(&db_sheet, db_sr, db_sc, db_er, db_ec, context) {
2588            Some(g) => g,
2589            None => return Ok(ResultData::Error("#REF!".to_string())),
2590        };
2591        let crit = match self.materialize_range(
2592            &crit_sheet,
2593            crit_sr,
2594            crit_sc,
2595            crit_er,
2596            crit_ec,
2597            context,
2598        ) {
2599            Some(g) => g,
2600            None => return Ok(ResultData::Error("#REF!".to_string())),
2601        };
2602        if db.len() < 2 || crit.len() < 2 {
2603            return Ok(ResultData::Error("#VALUE!".to_string()));
2604        }
2605
2606        let db_headers: Vec<String> = db[0].iter().map(|v| v.to_string()).collect();
2607        let field_idx: usize = match &evaluated_args[1] {
2608            ResultData::String(s) => {
2609                match db_headers.iter().position(|h| h.eq_ignore_ascii_case(s)) {
2610                    Some(idx) => idx,
2611                    None => return Ok(ResultData::Error("#VALUE!".to_string())),
2612                }
2613            }
2614            other => match self.to_f64(other) {
2615                Some(n) if n >= 1.0 && (n as usize) <= db_headers.len() => n as usize - 1,
2616                _ => return Ok(ResultData::Error("#VALUE!".to_string())),
2617            },
2618        };
2619
2620        let crit_headers: Vec<String> = crit[0].iter().map(|v| v.to_string()).collect();
2621        let crit_to_db: Vec<Option<usize>> = crit_headers
2622            .iter()
2623            .map(|h| db_headers.iter().position(|dh| dh.eq_ignore_ascii_case(h)))
2624            .collect();
2625
2626        let mut matched: Vec<ResultData> = Vec::new();
2627        for row in db.iter().skip(1) {
2628            let row_matches_any_criteria_row = crit.iter().skip(1).any(|crit_row| {
2629                crit_row.iter().enumerate().all(|(ci, cell)| {
2630                    if matches!(cell, ResultData::None) {
2631                        return true;
2632                    }
2633                    match crit_to_db.get(ci).copied().flatten() {
2634                        Some(db_col) => self.match_criteria(&row[db_col], cell),
2635                        None => false,
2636                    }
2637                })
2638            });
2639            if row_matches_any_criteria_row {
2640                matched.push(row[field_idx].clone());
2641            }
2642        }
2643
2644        match func_name {
2645            "DGET" => match matched.len() {
2646                0 => Ok(ResultData::Error("#VALUE!".to_string())),
2647                1 => Ok(matched.into_iter().next().unwrap()),
2648                _ => Ok(ResultData::Error("#NUM!".to_string())),
2649            },
2650            "DCOUNT" => Ok(ResultData::Float(
2651                matched
2652                    .iter()
2653                    .filter(|v| Self::range_numeric(v).is_some())
2654                    .count() as f64,
2655            )),
2656            "DCOUNTA" => Ok(ResultData::Float(
2657                matched.iter().map(|v| self.counta_helper(v)).sum::<usize>() as f64,
2658            )),
2659            _ => {
2660                let nums: Vec<f64> = matched.iter().filter_map(Self::range_numeric).collect();
2661                match func_name {
2662                    "DSUM" => Ok(ResultData::Float(nums.iter().sum())),
2663                    "DPRODUCT" => Ok(ResultData::Float(if nums.is_empty() {
2664                        0.0
2665                    } else {
2666                        nums.iter().product()
2667                    })),
2668                    "DMAX" => {
2669                        let m = nums.iter().cloned().fold(f64::NEG_INFINITY, f64::max);
2670                        Ok(ResultData::Float(if m.is_finite() { m } else { 0.0 }))
2671                    }
2672                    "DMIN" => {
2673                        let m = nums.iter().cloned().fold(f64::INFINITY, f64::min);
2674                        Ok(ResultData::Float(if m.is_finite() { m } else { 0.0 }))
2675                    }
2676                    "DAVERAGE" => {
2677                        if nums.is_empty() {
2678                            Ok(ResultData::Error("#DIV/0!".to_string()))
2679                        } else {
2680                            Ok(ResultData::Float(
2681                                nums.iter().sum::<f64>() / nums.len() as f64,
2682                            ))
2683                        }
2684                    }
2685                    "DSTDEV" => match crate::core::stats::stdev_s(&nums) {
2686                        Ok(v) => Ok(ResultData::Float(v)),
2687                        Err(e) => Ok(ResultData::Error(e)),
2688                    },
2689                    "DSTDEVP" => match crate::core::stats::stdev_p(&nums) {
2690                        Ok(v) => Ok(ResultData::Float(v)),
2691                        Err(e) => Ok(ResultData::Error(e)),
2692                    },
2693                    "DVAR" => match crate::core::stats::var_s(&nums) {
2694                        Ok(v) => Ok(ResultData::Float(v)),
2695                        Err(e) => Ok(ResultData::Error(e)),
2696                    },
2697                    "DVARP" => match crate::core::stats::var_p(&nums) {
2698                        Ok(v) => Ok(ResultData::Float(v)),
2699                        Err(e) => Ok(ResultData::Error(e)),
2700                    },
2701                    _ => unreachable!(),
2702                }
2703            }
2704        }
2705    }
2706
2707    fn proper(&self, s: &str) -> String {
2708        let mut c_chars = Vec::new();
2709        let mut capitalize_next = true;
2710        for c in s.chars() {
2711            if c.is_alphabetic() {
2712                if capitalize_next {
2713                    c_chars.extend(c.to_uppercase());
2714                } else {
2715                    c_chars.extend(c.to_lowercase());
2716                }
2717                capitalize_next = false;
2718            } else {
2719                c_chars.push(c);
2720                capitalize_next = true;
2721            }
2722        }
2723        c_chars.into_iter().collect()
2724    }
2725
2726    fn get_ymd_hms(&self) -> ((i32, u32, u32), (u32, u32, u32)) {
2727        let now = web_time::SystemTime::now()
2728            .duration_since(web_time::SystemTime::UNIX_EPOCH)
2729            .unwrap_or_default()
2730            .as_secs();
2731        let secs_in_day = 86400;
2732        let days_since_epoch = (now / secs_in_day) as i32;
2733        let seconds_of_day = (now % secs_in_day) as u32;
2734
2735        let hour = seconds_of_day / 3600;
2736        let minute = (seconds_of_day % 3600) / 60;
2737        let second = seconds_of_day % 60;
2738
2739        let era = (if days_since_epoch >= -719468 {
2740            days_since_epoch + 719468
2741        } else {
2742            days_since_epoch + 719468 - 146096
2743        }) / 146097;
2744        let doe = (days_since_epoch + 719468 - era * 146097) as u32;
2745        let yoe = (doe - doe / 1460 + doe / 36524 - doe / 146096) / 365;
2746        let y = (yoe as i32) + era * 400;
2747        let doy = doe - (365 * yoe + yoe / 4 - yoe / 100);
2748        let mp = (5 * doy + 2) / 153;
2749        let d = doy - (153 * mp + 2) / 5 + 1;
2750        let m = if mp < 10 { mp + 3 } else { mp - 9 };
2751        let year = if m <= 2 { y + 1 } else { y };
2752
2753        ((year, m, d), (hour, minute, second))
2754    }
2755
2756    fn evaluate_let(
2757        &self,
2758        args: &[crate::core::parser::Expr],
2759        context: Option<&Context>,
2760        row: Option<usize>,
2761        col: Option<usize>,
2762        deps: &mut Vec<Dependency>,
2763        scope: &LetScope<'_>,
2764    ) -> Result<ResultData, EngineError> {
2765        use crate::core::parser::Expr;
2766
2767        if args.is_empty() || args.len().is_multiple_of(2) {
2768            return Ok(ResultData::Error("#VALUE!".to_string()));
2769        }
2770        if args.len() == 1 {
2771            return self.evaluate_ast(&args[0], context, row, col, deps, scope);
2772        }
2773
2774        let name = match &args[0] {
2775            Expr::Identifier(n) => n.as_str(),
2776            _ => return Ok(ResultData::Error("#VALUE!".to_string())),
2777        };
2778        let remaining_pairs = args.len() / 2 - 1;
2779        let is_duplicate = args[2..]
2780            .iter()
2781            .step_by(2)
2782            .take(remaining_pairs)
2783            .any(|a| matches!(a, Expr::Identifier(n2) if n2.eq_ignore_ascii_case(name)));
2784        if is_duplicate {
2785            return Ok(ResultData::Error("#VALUE!".to_string()));
2786        }
2787
2788        let value = self.evaluate_ast(&args[1], context, row, col, deps, scope)?;
2789        let inner_scope = LetScope::Bound {
2790            name,
2791            value: &value,
2792            parent: scope,
2793        };
2794        self.evaluate_let(&args[2..], context, row, col, deps, &inner_scope)
2795    }
2796
2797    fn extract_lambda(
2798        expr: &crate::core::parser::Expr,
2799    ) -> Option<(Vec<&str>, &crate::core::parser::Expr)> {
2800        use crate::core::parser::Expr;
2801        let Expr::FunctionCall { name, args } = expr else {
2802            return None;
2803        };
2804        if !name.eq_ignore_ascii_case("LAMBDA") || args.is_empty() {
2805            return None;
2806        }
2807        let (body, params) = args.split_last().unwrap();
2808        let param_names: Vec<&str> = params
2809            .iter()
2810            .filter_map(|p| match p {
2811                Expr::Identifier(n) => Some(n.as_str()),
2812                _ => None,
2813            })
2814            .collect();
2815        if param_names.len() != params.len() {
2816            return None;
2817        }
2818        Some((param_names, body))
2819    }
2820
2821    #[allow(clippy::too_many_arguments)]
2822    fn invoke_lambda<'v>(
2823        &self,
2824        params: &[&str],
2825        values: &'v [ResultData],
2826        body: &crate::core::parser::Expr,
2827        context: Option<&Context>,
2828        row: Option<usize>,
2829        col: Option<usize>,
2830        deps: &mut Vec<Dependency>,
2831        scope: &LetScope<'v>,
2832    ) -> Result<ResultData, EngineError> {
2833        match (params.split_first(), values.split_first()) {
2834            (Some((&pname, prest)), Some((vfirst, vrest))) => {
2835                let inner_scope = LetScope::Bound {
2836                    name: pname,
2837                    value: vfirst,
2838                    parent: scope,
2839                };
2840                self.invoke_lambda(prest, vrest, body, context, row, col, deps, &inner_scope)
2841            }
2842            _ => self.evaluate_ast(body, context, row, col, deps, scope),
2843        }
2844    }
2845
2846    fn eval_as_array(
2847        &self,
2848        expr: &crate::core::parser::Expr,
2849        context: Option<&Context>,
2850        row: Option<usize>,
2851        col: Option<usize>,
2852        deps: &mut Vec<Dependency>,
2853        scope: &LetScope<'_>,
2854    ) -> Result<Vec<ResultData>, EngineError> {
2855        Ok(
2856            match self.evaluate_ast(expr, context, row, col, deps, scope)? {
2857                ResultData::List(items) => Self::flatten_row_major(items).0,
2858                other => vec![other],
2859            },
2860        )
2861    }
2862
2863    fn flatten_row_major(items: Vec<ResultData>) -> (Vec<ResultData>, Option<usize>) {
2864        if !items.is_empty() && items.iter().all(|v| matches!(v, ResultData::List(_))) {
2865            let cols = match &items[0] {
2866                ResultData::List(inner) => inner.len().max(1),
2867                _ => 1,
2868            };
2869            let flat = items
2870                .into_iter()
2871                .flat_map(|v| match v {
2872                    ResultData::List(inner) => inner,
2873                    other => vec![other],
2874                })
2875                .collect();
2876            (flat, Some(cols))
2877        } else {
2878            (items, None)
2879        }
2880    }
2881
2882    fn array_shape(
2883        &self,
2884        expr: &crate::core::parser::Expr,
2885        context: Option<&Context>,
2886        row: Option<usize>,
2887        col: Option<usize>,
2888        deps: &mut Vec<Dependency>,
2889        scope: &LetScope<'_>,
2890    ) -> Result<(Vec<ResultData>, usize), EngineError> {
2891        use crate::core::parser::Expr;
2892        let items = match self.evaluate_ast(expr, context, row, col, deps, scope)? {
2893            ResultData::List(items) => items,
2894            other => vec![other],
2895        };
2896        let (flat, nested_cols) = Self::flatten_row_major(items);
2897        if let Some(cols) = nested_cols {
2898            return Ok((flat, cols));
2899        }
2900        let num_cols = match expr {
2901            Expr::RangeRef {
2902                start_col, end_col, ..
2903            } => (end_col - start_col + 1).max(1),
2904            Expr::CellRef { .. } => 1,
2905            Expr::FunctionCall { name, args } => self
2906                .function_call_cols(name, args, context, row, col, deps, scope)
2907                .unwrap_or_else(|| flat.len().max(1)),
2908            _ => flat.len().max(1),
2909        };
2910        Ok((flat, num_cols))
2911    }
2912
2913    #[allow(clippy::too_many_arguments)]
2914    fn function_call_cols(
2915        &self,
2916        name: &str,
2917        args: &[crate::core::parser::Expr],
2918        context: Option<&Context>,
2919        row: Option<usize>,
2920        col: Option<usize>,
2921        deps: &mut Vec<Dependency>,
2922        scope: &LetScope<'_>,
2923    ) -> Option<usize> {
2924        let mut upper = name.to_ascii_uppercase();
2925        if let Some(rest) = upper.strip_prefix("_XLFN.") {
2926            upper = rest.to_string();
2927        }
2928        if let Some(rest) = upper.strip_prefix("_XLWS.") {
2929            upper = rest.to_string();
2930        }
2931        match upper.as_str() {
2932            "TRANSPOSE" => {
2933                let (flat, cols) = self
2934                    .array_shape(args.first()?, context, row, col, deps, scope)
2935                    .ok()?;
2936                Some((flat.len().checked_div(cols).unwrap_or(0)).max(1))
2937            }
2938            "HSTACK" => {
2939                let mut total = 0usize;
2940                for a in args {
2941                    total += self.array_shape(a, context, row, col, deps, scope).ok()?.1;
2942                }
2943                Some(total)
2944            }
2945            "VSTACK" => {
2946                let mut max_cols = 0usize;
2947                for a in args {
2948                    max_cols =
2949                        max_cols.max(self.array_shape(a, context, row, col, deps, scope).ok()?.1);
2950                }
2951                Some(max_cols)
2952            }
2953            "CHOOSEROWS" => Some(
2954                self.array_shape(args.first()?, context, row, col, deps, scope)
2955                    .ok()?
2956                    .1,
2957            ),
2958            "CHOOSECOLS" => Some(args.len().saturating_sub(1).max(1)),
2959            "DROP" | "TAKE" => {
2960                let (_, cols) = self
2961                    .array_shape(args.first()?, context, row, col, deps, scope)
2962                    .ok()?;
2963                let is_take = upper == "TAKE";
2964                match args.get(2) {
2965                    Some(e) => {
2966                        let n = self
2967                            .to_f64(&self.evaluate_ast(e, context, row, col, deps, scope).ok()?)
2968                            .unwrap_or(0.0) as isize;
2969                        let (s, e2) = Self::drop_take_bounds(cols as isize, n, is_take);
2970                        Some((e2 - s).max(0) as usize)
2971                    }
2972                    None => Some(if is_take { cols } else { 0 }),
2973                }
2974            }
2975            "EXPAND" => {
2976                let (_, cols) = self
2977                    .array_shape(args.first()?, context, row, col, deps, scope)
2978                    .ok()?;
2979                match args.get(2) {
2980                    Some(e) => Some(
2981                        self.to_f64(&self.evaluate_ast(e, context, row, col, deps, scope).ok()?)
2982                            .unwrap_or(cols as f64) as usize,
2983                    ),
2984                    None => Some(cols),
2985                }
2986            }
2987            "TOCOL" => Some(1),
2988            "WRAPROWS" => {
2989                let n = self
2990                    .to_f64(
2991                        &self
2992                            .evaluate_ast(args.get(1)?, context, row, col, deps, scope)
2993                            .ok()?,
2994                    )
2995                    .unwrap_or(1.0)
2996                    .max(1.0) as usize;
2997                Some(n)
2998            }
2999            "WRAPCOLS" => {
3000                let (flat, _) = self
3001                    .array_shape(args.first()?, context, row, col, deps, scope)
3002                    .ok()?;
3003                let wrap = self
3004                    .to_f64(
3005                        &self
3006                            .evaluate_ast(args.get(1)?, context, row, col, deps, scope)
3007                            .ok()?,
3008                    )
3009                    .unwrap_or(1.0)
3010                    .max(1.0) as usize;
3011                Some(flat.len().div_ceil(wrap).max(1))
3012            }
3013            "UNIQUE" | "SORT" | "SORTBY" | "FILTER" | "TRIMRANGE" => Some(
3014                self.array_shape(args.first()?, context, row, col, deps, scope)
3015                    .ok()?
3016                    .1,
3017            ),
3018            "SEQUENCE" => match args.get(1) {
3019                Some(e) => Some(
3020                    self.to_f64(&self.evaluate_ast(e, context, row, col, deps, scope).ok()?)
3021                        .unwrap_or(1.0)
3022                        .max(1.0) as usize,
3023                ),
3024                None => Some(1),
3025            },
3026            "MUNIT" => {
3027                let n = self
3028                    .to_f64(
3029                        &self
3030                            .evaluate_ast(args.first()?, context, row, col, deps, scope)
3031                            .ok()?,
3032                    )
3033                    .unwrap_or(1.0)
3034                    .max(1.0) as usize;
3035                Some(n)
3036            }
3037            "MAKEARRAY" => match args.get(1) {
3038                Some(e) => Some(
3039                    self.to_f64(&self.evaluate_ast(e, context, row, col, deps, scope).ok()?)
3040                        .unwrap_or(1.0)
3041                        .max(1.0) as usize,
3042                ),
3043                None => Some(1),
3044            },
3045            _ => None,
3046        }
3047    }
3048
3049    fn drop_take_bounds(total: isize, n: isize, is_take: bool) -> (isize, isize) {
3050        let n = n.clamp(-total, total);
3051        if is_take {
3052            if n >= 0 { (0, n) } else { (total + n, total) }
3053        } else if n >= 0 {
3054            (n, total)
3055        } else {
3056            (0, total + n)
3057        }
3058    }
3059
3060    #[allow(clippy::too_many_arguments)]
3061    fn evaluate_lambda_function(
3062        &self,
3063        func_name: &str,
3064        args: &[crate::core::parser::Expr],
3065        context: Option<&Context>,
3066        row: Option<usize>,
3067        col: Option<usize>,
3068        deps: &mut Vec<Dependency>,
3069        scope: &LetScope<'_>,
3070    ) -> Result<ResultData, EngineError> {
3071        use crate::core::parser::Expr;
3072
3073        match func_name {
3074            "MAP" => {
3075                if args.len() < 2 {
3076                    return Ok(ResultData::Error("#VALUE!".to_string()));
3077                }
3078                let (lambda_expr, array_exprs) = args.split_last().unwrap();
3079                let Some((params, body)) = Self::extract_lambda(lambda_expr) else {
3080                    return Ok(ResultData::Error("#VALUE!".to_string()));
3081                };
3082                if params.len() != array_exprs.len() {
3083                    return Ok(ResultData::Error("#VALUE!".to_string()));
3084                }
3085                let arrays: Vec<Vec<ResultData>> = array_exprs
3086                    .iter()
3087                    .map(|e| self.eval_as_array(e, context, row, col, deps, scope))
3088                    .collect::<Result<_, _>>()?;
3089                let len = arrays.iter().map(|a| a.len()).max().unwrap_or(0);
3090                let mut results = Vec::with_capacity(len);
3091                for i in 0..len {
3092                    let values: Vec<ResultData> = arrays
3093                        .iter()
3094                        .map(|a| a.get(i).cloned().unwrap_or(ResultData::None))
3095                        .collect();
3096                    results.push(
3097                        self.invoke_lambda(&params, &values, body, context, row, col, deps, scope)?,
3098                    );
3099                }
3100                Ok(ResultData::List(results))
3101            }
3102            "BYROW" | "BYCOL" => {
3103                if args.len() != 2 {
3104                    return Ok(ResultData::Error("#VALUE!".to_string()));
3105                }
3106                let Some((params, body)) = Self::extract_lambda(&args[1]) else {
3107                    return Ok(ResultData::Error("#VALUE!".to_string()));
3108                };
3109                if params.len() != 1 {
3110                    return Ok(ResultData::Error("#VALUE!".to_string()));
3111                }
3112                let num_cols = match &args[0] {
3113                    Expr::RangeRef {
3114                        start_col, end_col, ..
3115                    } => (end_col - start_col + 1).max(1),
3116                    _ => 1,
3117                };
3118                let flat = self.eval_as_array(&args[0], context, row, col, deps, scope)?;
3119                let num_rows = if num_cols == 0 {
3120                    0
3121                } else {
3122                    flat.len().div_ceil(num_cols)
3123                };
3124                let mut results = Vec::new();
3125                if func_name == "BYROW" {
3126                    for r in 0..num_rows {
3127                        let row_vals: Vec<ResultData> = (0..num_cols)
3128                            .filter_map(|c| flat.get(r * num_cols + c).cloned())
3129                            .collect();
3130                        let arg = vec![ResultData::List(row_vals)];
3131                        results.push(
3132                            self.invoke_lambda(
3133                                &params, &arg, body, context, row, col, deps, scope,
3134                            )?,
3135                        );
3136                    }
3137                } else {
3138                    for c in 0..num_cols {
3139                        let col_vals: Vec<ResultData> = (0..num_rows)
3140                            .filter_map(|r| flat.get(r * num_cols + c).cloned())
3141                            .collect();
3142                        let arg = vec![ResultData::List(col_vals)];
3143                        results.push(
3144                            self.invoke_lambda(
3145                                &params, &arg, body, context, row, col, deps, scope,
3146                            )?,
3147                        );
3148                    }
3149                }
3150                Ok(ResultData::List(results))
3151            }
3152            "REDUCE" | "SCAN" => {
3153                if args.len() != 2 && args.len() != 3 {
3154                    return Ok(ResultData::Error("#VALUE!".to_string()));
3155                }
3156                let lambda_idx = args.len() - 1;
3157                let array_idx = args.len() - 2;
3158                let Some((params, body)) = Self::extract_lambda(&args[lambda_idx]) else {
3159                    return Ok(ResultData::Error("#VALUE!".to_string()));
3160                };
3161                if params.len() != 2 {
3162                    return Ok(ResultData::Error("#VALUE!".to_string()));
3163                }
3164                let array = self.eval_as_array(&args[array_idx], context, row, col, deps, scope)?;
3165                let (mut acc, rest, mut history): (ResultData, &[ResultData], Vec<ResultData>) =
3166                    if args.len() == 3 {
3167                        let init = self.evaluate_ast(&args[0], context, row, col, deps, scope)?;
3168                        (init, &array[..], Vec::new())
3169                    } else {
3170                        match array.split_first() {
3171                            Some((first, rest)) => (first.clone(), rest, vec![first.clone()]),
3172                            None => return Ok(ResultData::Error("#VALUE!".to_string())),
3173                        }
3174                    };
3175                for item in rest {
3176                    let call_args = [acc.clone(), item.clone()];
3177                    acc = self
3178                        .invoke_lambda(&params, &call_args, body, context, row, col, deps, scope)?;
3179                    history.push(acc.clone());
3180                }
3181                if func_name == "REDUCE" {
3182                    Ok(acc)
3183                } else {
3184                    Ok(ResultData::List(history))
3185                }
3186            }
3187            "MAKEARRAY" => {
3188                if args.len() != 3 {
3189                    return Ok(ResultData::Error("#VALUE!".to_string()));
3190                }
3191                let Some((params, body)) = Self::extract_lambda(&args[2]) else {
3192                    return Ok(ResultData::Error("#VALUE!".to_string()));
3193                };
3194                if params.len() != 2 {
3195                    return Ok(ResultData::Error("#VALUE!".to_string()));
3196                }
3197                let rows_val = self.evaluate_ast(&args[0], context, row, col, deps, scope)?;
3198                let cols_val = self.evaluate_ast(&args[1], context, row, col, deps, scope)?;
3199                let num_rows = self.to_f64(&rows_val).unwrap_or(0.0).max(0.0) as usize;
3200                let num_cols = self.to_f64(&cols_val).unwrap_or(0.0).max(0.0) as usize;
3201                let mut results = Vec::with_capacity(num_rows * num_cols);
3202                for r in 1..=num_rows {
3203                    for c in 1..=num_cols {
3204                        let call_args = [ResultData::Float(r as f64), ResultData::Float(c as f64)];
3205                        results.push(self.invoke_lambda(
3206                            &params, &call_args, body, context, row, col, deps, scope,
3207                        )?);
3208                    }
3209                }
3210                Ok(ResultData::List(results))
3211            }
3212            _ => unreachable!(),
3213        }
3214    }
3215
3216    fn parse_a1_reference(text: &str) -> Option<(Option<String>, usize, usize, usize, usize)> {
3217        let text = text.trim();
3218        let (sheet_part, ref_part) = match text.rfind('!') {
3219            Some(idx) => (Some(&text[..idx]), &text[idx + 1..]),
3220            None => (None, text),
3221        };
3222        let sheet = sheet_part.map(|s| s.trim().trim_matches('\'').to_string());
3223
3224        fn parse_cell(s: &str) -> Option<(usize, usize)> {
3225            let s = s.replace('$', "");
3226            let col_end = s.find(|c: char| c.is_ascii_digit())?;
3227            let (col_str, row_str) = s.split_at(col_end);
3228            if col_str.is_empty() || row_str.is_empty() {
3229                return None;
3230            }
3231            let mut col = 0usize;
3232            for ch in col_str.chars() {
3233                if !ch.is_ascii_alphabetic() {
3234                    return None;
3235                }
3236                col = col * 26 + (ch.to_ascii_uppercase() as usize - 'A' as usize + 1);
3237            }
3238            let row: usize = row_str.parse().ok()?;
3239            if row == 0 || col == 0 {
3240                return None;
3241            }
3242            Some((row - 1, col - 1))
3243        }
3244
3245        if let Some((start, end)) = ref_part.split_once(':') {
3246            let (r1, c1) = parse_cell(start)?;
3247            let (r2, c2) = parse_cell(end)?;
3248            Some((sheet, r1.min(r2), c1.min(c2), r1.max(r2), c1.max(c2)))
3249        } else {
3250            let (r, c) = parse_cell(ref_part)?;
3251            Some((sheet, r, c, r, c))
3252        }
3253    }
3254
3255    fn read_cell_with_deps(
3256        &self,
3257        sheet_opt: &Option<String>,
3258        r: usize,
3259        c: usize,
3260        context: Option<&Context>,
3261        deps: &mut Vec<Dependency>,
3262    ) -> ResultData {
3263        let is_self = sheet_opt.as_deref().is_none_or(|n| n == self.name);
3264        if is_self {
3265            deps.push(Dependency::Local(CellRef::new(r, c)));
3266            self.get_result_data(&CellRef::new(r, c))
3267        } else if let Some(ctx) = context {
3268            let name = sheet_opt.clone().unwrap();
3269            deps.push(Dependency::Remote {
3270                sheet: name.clone(),
3271                cell: CellRef::new(r, c),
3272            });
3273            ctx.sheets
3274                .get(&name)
3275                .map(|s| s.get_result_data(&CellRef::new(r, c)))
3276                .unwrap_or(ResultData::None)
3277        } else {
3278            ResultData::None
3279        }
3280    }
3281
3282    #[allow(clippy::too_many_arguments)]
3283    fn evaluate_range_info_function(
3284        &self,
3285        func_name: &str,
3286        args: &[crate::core::parser::Expr],
3287        context: Option<&Context>,
3288        row: Option<usize>,
3289        col: Option<usize>,
3290        deps: &mut Vec<Dependency>,
3291        scope: &LetScope<'_>,
3292    ) -> Result<ResultData, EngineError> {
3293        use crate::core::parser::Expr;
3294
3295        match func_name {
3296            "ROW" => match args.first() {
3297                Some(arg) => match Self::range_bounds(arg) {
3298                    Some((_, start_row, _, end_row, _)) if end_row > start_row => {
3299                        Ok(ResultData::List(
3300                            (start_row..=end_row)
3301                                .map(|r| ResultData::Float((r + 1) as f64))
3302                                .collect(),
3303                        ))
3304                    }
3305                    Some((_, start_row, _, _, _)) => Ok(ResultData::Float((start_row + 1) as f64)),
3306                    None => Ok(ResultData::Error("#VALUE!".to_string())),
3307                },
3308                None => match row {
3309                    Some(r) => Ok(ResultData::Float((r + 1) as f64)),
3310                    None => Ok(ResultData::Error("#VALUE!".to_string())),
3311                },
3312            },
3313            "COLUMN" => match args.first() {
3314                Some(arg) => match Self::range_bounds(arg) {
3315                    Some((_, _, start_col, _, end_col)) if end_col > start_col => {
3316                        Ok(ResultData::List(
3317                            (start_col..=end_col)
3318                                .map(|c| ResultData::Float((c + 1) as f64))
3319                                .collect(),
3320                        ))
3321                    }
3322                    Some((_, _, start_col, _, _)) => Ok(ResultData::Float((start_col + 1) as f64)),
3323                    None => Ok(ResultData::Error("#VALUE!".to_string())),
3324                },
3325                None => match col {
3326                    Some(c) => Ok(ResultData::Float((c + 1) as f64)),
3327                    None => Ok(ResultData::Error("#VALUE!".to_string())),
3328                },
3329            },
3330            "ROWS" => {
3331                let Some(arg) = args.first() else {
3332                    return Ok(ResultData::Error("#VALUE!".to_string()));
3333                };
3334                let Some((sheet_opt, start_row, _, end_row, _)) = Self::range_bounds(arg) else {
3335                    return Ok(ResultData::Error("#VALUE!".to_string()));
3336                };
3337                let is_self = sheet_opt.as_deref().is_none_or(|n| n == self.name);
3338                let actual_end_row = if end_row == usize::MAX {
3339                    if is_self {
3340                        self.row_count().saturating_sub(1)
3341                    } else {
3342                        context
3343                            .and_then(|ctx| sheet_opt.as_ref().and_then(|n| ctx.sheets.get(n)))
3344                            .map(|s| s.row_count().saturating_sub(1))
3345                            .unwrap_or(0)
3346                    }
3347                } else {
3348                    end_row
3349                };
3350                Ok(ResultData::Float(
3351                    (actual_end_row.saturating_sub(start_row) + 1) as f64,
3352                ))
3353            }
3354            "COLUMNS" => {
3355                let Some(arg) = args.first() else {
3356                    return Ok(ResultData::Error("#VALUE!".to_string()));
3357                };
3358                match Self::range_bounds(arg) {
3359                    Some((_, _, start_col, _, end_col)) => Ok(ResultData::Float(
3360                        (end_col.saturating_sub(start_col) + 1) as f64,
3361                    )),
3362                    None => Ok(ResultData::Error("#VALUE!".to_string())),
3363                }
3364            }
3365            "AREAS" => {
3366                if args.is_empty() {
3367                    Ok(ResultData::Error("#VALUE!".to_string()))
3368                } else {
3369                    Ok(ResultData::Float(1.0))
3370                }
3371            }
3372            "ISREF" => Ok(ResultData::Boolean(matches!(
3373                args.first(),
3374                Some(Expr::CellRef { .. } | Expr::RangeRef { .. } | Expr::StructuredRef { .. })
3375            ))),
3376            "FORMULATEXT" | "ISFORMULA" => {
3377                let Some(arg) = args.first() else {
3378                    return Ok(ResultData::Error("#VALUE!".to_string()));
3379                };
3380                let Some((sheet_opt, r, c, _, _)) = Self::range_bounds(arg) else {
3381                    return Ok(ResultData::Error("#VALUE!".to_string()));
3382                };
3383                let is_self = sheet_opt.as_deref().is_none_or(|n| n == self.name);
3384                let src = if is_self {
3385                    deps.push(Dependency::Local(CellRef::new(r, c)));
3386                    self.get_src_str(&CellRef::new(r, c))
3387                } else if let Some(ctx) = context {
3388                    let name = sheet_opt.unwrap();
3389                    deps.push(Dependency::Remote {
3390                        sheet: name.clone(),
3391                        cell: CellRef::new(r, c),
3392                    });
3393                    ctx.sheets
3394                        .get(&name)
3395                        .map(|s| s.get_src_str(&CellRef::new(r, c)))
3396                        .unwrap_or_default()
3397                } else {
3398                    String::new()
3399                };
3400                let is_formula = src.starts_with('=');
3401                if func_name == "ISFORMULA" {
3402                    Ok(ResultData::Boolean(is_formula))
3403                } else if is_formula {
3404                    Ok(ResultData::String(src))
3405                } else {
3406                    Ok(ResultData::Error("#N/A".to_string()))
3407                }
3408            }
3409            "SHEETS" => Ok(ResultData::Float(
3410                context.map(|c| c.sheets.len() + 1).unwrap_or(1) as f64,
3411            )),
3412            "SHEET" => {
3413                let sheet_name = match args.first() {
3414                    None => Some(self.name.clone()),
3415                    Some(arg) => match Self::range_bounds(arg) {
3416                        Some((sheet_opt, ..)) => {
3417                            Some(sheet_opt.unwrap_or_else(|| self.name.clone()))
3418                        }
3419                        None => self
3420                            .evaluate_ast(arg, context, row, col, deps, scope)
3421                            .ok()
3422                            .map(|v| v.to_string()),
3423                    },
3424                };
3425
3426                match sheet_name {
3427                    Some(name) => {
3428                        let ordinal = context
3429                            .and_then(|c| {
3430                                c.sheet_order
3431                                    .iter()
3432                                    .position(|n| n.eq_ignore_ascii_case(&name))
3433                            })
3434                            .map(|i| i + 1)
3435                            .unwrap_or(1);
3436                        Ok(ResultData::Float(ordinal as f64))
3437                    }
3438                    None => Ok(ResultData::Error("#N/A".to_string())),
3439                }
3440            }
3441            "CELL" => {
3442                if args.is_empty() {
3443                    return Ok(ResultData::Error("#VALUE!".to_string()));
3444                }
3445                let info_type = self
3446                    .evaluate_ast(&args[0], context, row, col, deps, scope)?
3447                    .to_string()
3448                    .to_lowercase();
3449                let bounds = args.get(1).and_then(Self::range_bounds);
3450                match info_type.as_str() {
3451                    "row" => match bounds.map(|b| b.1).or(row) {
3452                        Some(r) => Ok(ResultData::Float((r + 1) as f64)),
3453                        None => Ok(ResultData::Error("#VALUE!".to_string())),
3454                    },
3455                    "col" => match bounds {
3456                        Some((_, _, c, _, _)) => Ok(ResultData::Float((c + 1) as f64)),
3457                        None => Ok(ResultData::Error("#VALUE!".to_string())),
3458                    },
3459                    "address" => match bounds {
3460                        Some((_, r, c, _, _)) => Ok(ResultData::String(format!(
3461                            "${}${}",
3462                            crate::core::parser::col_idx_to_letters(c),
3463                            r + 1
3464                        ))),
3465                        None => Ok(ResultData::Error("#VALUE!".to_string())),
3466                    },
3467                    "contents" => match bounds {
3468                        Some((sheet_opt, r, c, _, _)) => {
3469                            Ok(self.read_cell_with_deps(&sheet_opt, r, c, context, deps))
3470                        }
3471                        None => Ok(ResultData::Error("#VALUE!".to_string())),
3472                    },
3473                    _ => Ok(ResultData::Error("#VALUE!".to_string())),
3474                }
3475            }
3476            "INFO" => {
3477                if args.is_empty() {
3478                    return Ok(ResultData::Error("#VALUE!".to_string()));
3479                }
3480                let info_type = self
3481                    .evaluate_ast(&args[0], context, row, col, deps, scope)?
3482                    .to_string()
3483                    .to_lowercase();
3484                match info_type.as_str() {
3485                    "numfile" => Ok(ResultData::Float(
3486                        context.map(|c| c.sheets.len() + 1).unwrap_or(1) as f64,
3487                    )),
3488                    "release" => Ok(ResultData::String("16.0".to_string())),
3489                    "system" => Ok(ResultData::String(
3490                        if cfg!(target_os = "macos") {
3491                            "mac"
3492                        } else {
3493                            "pcdos"
3494                        }
3495                        .to_string(),
3496                    )),
3497                    _ => Ok(ResultData::Error("#VALUE!".to_string())),
3498                }
3499            }
3500            "INDIRECT" => {
3501                if args.is_empty() {
3502                    return Ok(ResultData::Error("#VALUE!".to_string()));
3503                }
3504                let text = self
3505                    .evaluate_ast(&args[0], context, row, col, deps, scope)?
3506                    .to_string();
3507                let a1_style = match args.get(1) {
3508                    Some(a) => self.to_bool(&self.evaluate_ast(a, context, row, col, deps, scope)?),
3509                    None => true,
3510                };
3511                if !a1_style {
3512                    return Ok(ResultData::Error("#VALUE!".to_string()));
3513                }
3514                match Self::parse_a1_reference(&text) {
3515                    Some((sheet_opt, start_row, start_col, end_row, end_col)) => {
3516                        if start_row == end_row && start_col == end_col {
3517                            Ok(self.read_cell_with_deps(
3518                                &sheet_opt, start_row, start_col, context, deps,
3519                            ))
3520                        } else {
3521                            match self.materialize_range(
3522                                &sheet_opt, start_row, start_col, end_row, end_col, context,
3523                            ) {
3524                                Some(grid) => {
3525                                    Ok(ResultData::List(grid.into_iter().flatten().collect()))
3526                                }
3527                                None => Ok(ResultData::Error("#REF!".to_string())),
3528                            }
3529                        }
3530                    }
3531                    None => Ok(ResultData::Error("#REF!".to_string())),
3532                }
3533            }
3534            "OFFSET" => {
3535                if args.len() < 3 {
3536                    return Ok(ResultData::Error("#VALUE!".to_string()));
3537                }
3538                let Some((sheet_opt, base_row, base_col, base_end_row, base_end_col)) =
3539                    Self::range_bounds(&args[0])
3540                else {
3541                    return Ok(ResultData::Error("#VALUE!".to_string()));
3542                };
3543                let row_offset = self
3544                    .to_f64(&self.evaluate_ast(&args[1], context, row, col, deps, scope)?)
3545                    .unwrap_or(0.0) as isize;
3546                let col_offset = self
3547                    .to_f64(&self.evaluate_ast(&args[2], context, row, col, deps, scope)?)
3548                    .unwrap_or(0.0) as isize;
3549                let base_height = (base_end_row.saturating_sub(base_row) + 1) as isize;
3550                let base_width = (base_end_col.saturating_sub(base_col) + 1) as isize;
3551                let height = match args.get(3) {
3552                    Some(a) => self
3553                        .to_f64(&self.evaluate_ast(a, context, row, col, deps, scope)?)
3554                        .unwrap_or(base_height as f64) as isize,
3555                    None => base_height,
3556                };
3557                let width = match args.get(4) {
3558                    Some(a) => self
3559                        .to_f64(&self.evaluate_ast(a, context, row, col, deps, scope)?)
3560                        .unwrap_or(base_width as f64) as isize,
3561                    None => base_width,
3562                };
3563                let new_row = base_row as isize + row_offset;
3564                let new_col = base_col as isize + col_offset;
3565                if new_row < 0 || new_col < 0 || height <= 0 || width <= 0 {
3566                    return Ok(ResultData::Error("#REF!".to_string()));
3567                }
3568                let (start_row, start_col) = (new_row as usize, new_col as usize);
3569                let (end_row, end_col) = (
3570                    start_row + (height - 1) as usize,
3571                    start_col + (width - 1) as usize,
3572                );
3573                if start_row == end_row && start_col == end_col {
3574                    Ok(self.read_cell_with_deps(&sheet_opt, start_row, start_col, context, deps))
3575                } else {
3576                    match self.materialize_range(
3577                        &sheet_opt, start_row, start_col, end_row, end_col, context,
3578                    ) {
3579                        Some(grid) => Ok(ResultData::List(grid.into_iter().flatten().collect())),
3580                        None => Ok(ResultData::Error("#REF!".to_string())),
3581                    }
3582                }
3583            }
3584            _ => unreachable!(),
3585        }
3586    }
3587
3588    fn evaluate_getpivotdata(
3589        &self,
3590        args: &[crate::core::parser::Expr],
3591        context: Option<&Context>,
3592        row: Option<usize>,
3593        col: Option<usize>,
3594        deps: &mut Vec<Dependency>,
3595        scope: &LetScope<'_>,
3596    ) -> Result<ResultData, EngineError> {
3597        if args.len() < 2 || !(args.len() - 2).is_multiple_of(2) {
3598            return Ok(ResultData::Error("#VALUE!".to_string()));
3599        }
3600
3601        let data_field = self
3602            .evaluate_ast(&args[0], context, row, col, deps, scope)?
3603            .to_string();
3604
3605        let (sheet_opt, target_row, target_col, _, _) = match Self::range_bounds(&args[1]) {
3606            Some(bounds) => bounds,
3607            None => return Ok(ResultData::Error("#REF!".to_string())),
3608        };
3609        self.read_cell_with_deps(&sheet_opt, target_row, target_col, context, deps);
3610
3611        let sheet_id = match &sheet_opt {
3612            None => self.id,
3613            Some(name) if name == &self.name => self.id,
3614            Some(name) => match context.and_then(|c| c.sheets.get(name)) {
3615                Some(s) => s.id,
3616                None => return Ok(ResultData::Error("#REF!".to_string())),
3617            },
3618        };
3619
3620        let pivot_tables = context.map(|c| c.pivot_tables).unwrap_or(&[]);
3621        let pivot = match pivot_tables.iter().find(|p| {
3622            p.dest_sheet_id == sheet_id
3623                && p.last_output_end_row
3624                    .is_some_and(|end| target_row >= p.dest_row && target_row <= end)
3625                && p.last_output_end_col
3626                    .is_some_and(|end| target_col >= p.dest_col && target_col <= end)
3627        }) {
3628            Some(p) => p,
3629            None => return Ok(ResultData::Error("#REF!".to_string())),
3630        };
3631
3632        let mut criteria: Vec<(String, String)> = Vec::new();
3633        let mut i = 2;
3634        while i < args.len() {
3635            let field = self
3636                .evaluate_ast(&args[i], context, row, col, deps, scope)?
3637                .to_string();
3638            let item = self
3639                .evaluate_ast(&args[i + 1], context, row, col, deps, scope)?
3640                .to_string();
3641            criteria.push((field, item));
3642            i += 2;
3643        }
3644
3645        let mut sheet_refs: Vec<&Sheet> = context
3646            .map(|c| c.sheets.values().copied().collect())
3647            .unwrap_or_default();
3648        sheet_refs.push(self);
3649
3650        match crate::core::pivot::getpivotdata(&sheet_refs, pivot, &data_field, &criteria) {
3651            Ok(v) => Ok(v),
3652            Err(e) => Ok(ResultData::Error(e)),
3653        }
3654    }
3655
3656    #[allow(clippy::too_many_arguments)]
3657    fn evaluate_array_reshape_function(
3658        &self,
3659        func_name: &str,
3660        args: &[crate::core::parser::Expr],
3661        context: Option<&Context>,
3662        row: Option<usize>,
3663        col: Option<usize>,
3664        deps: &mut Vec<Dependency>,
3665        scope: &LetScope<'_>,
3666    ) -> Result<ResultData, EngineError> {
3667        match func_name {
3668            "TRANSPOSE" => {
3669                let Some(arg) = args.first() else {
3670                    return Ok(ResultData::Error("#VALUE!".to_string()));
3671                };
3672                let (flat, cols) = self.array_shape(arg, context, row, col, deps, scope)?;
3673                let rows = flat.len().checked_div(cols).unwrap_or(0);
3674                let mut result = Vec::with_capacity(flat.len());
3675                for c in 0..cols {
3676                    for r in 0..rows {
3677                        result.push(flat[r * cols + c].clone());
3678                    }
3679                }
3680                Ok(ResultData::List(result))
3681            }
3682            "HSTACK" | "VSTACK" => {
3683                if args.is_empty() {
3684                    return Ok(ResultData::Error("#VALUE!".to_string()));
3685                }
3686                let mut shapes = Vec::with_capacity(args.len());
3687                for a in args {
3688                    shapes.push(self.array_shape(a, context, row, col, deps, scope)?);
3689                }
3690                let mut result = Vec::new();
3691                if func_name == "HSTACK" {
3692                    let max_rows = shapes
3693                        .iter()
3694                        .map(|(f, c)| if *c == 0 { 0 } else { f.len() / c })
3695                        .max()
3696                        .unwrap_or(0);
3697                    for r in 0..max_rows {
3698                        for (flat, cols) in &shapes {
3699                            let rows = if *cols == 0 { 0 } else { flat.len() / cols };
3700                            for c in 0..*cols {
3701                                result.push(if r < rows {
3702                                    flat[r * cols + c].clone()
3703                                } else {
3704                                    ResultData::Error("#N/A".to_string())
3705                                });
3706                            }
3707                        }
3708                    }
3709                } else {
3710                    let max_cols = shapes.iter().map(|(_, c)| *c).max().unwrap_or(0);
3711                    for (flat, cols) in &shapes {
3712                        let rows = if *cols == 0 { 0 } else { flat.len() / cols };
3713                        for r in 0..rows {
3714                            for c in 0..max_cols {
3715                                result.push(if c < *cols {
3716                                    flat[r * cols + c].clone()
3717                                } else {
3718                                    ResultData::Error("#N/A".to_string())
3719                                });
3720                            }
3721                        }
3722                    }
3723                }
3724                Ok(ResultData::List(result))
3725            }
3726            "CHOOSEROWS" | "CHOOSECOLS" => {
3727                if args.len() < 2 {
3728                    return Ok(ResultData::Error("#VALUE!".to_string()));
3729                }
3730                let (flat, cols) = self.array_shape(&args[0], context, row, col, deps, scope)?;
3731                let rows = flat.len().checked_div(cols).unwrap_or(0);
3732                let total = if func_name == "CHOOSEROWS" {
3733                    rows
3734                } else {
3735                    cols
3736                } as isize;
3737                let mut indices = Vec::with_capacity(args.len() - 1);
3738                for idx_expr in &args[1..] {
3739                    let n = self
3740                        .to_f64(&self.evaluate_ast(idx_expr, context, row, col, deps, scope)?)
3741                        .unwrap_or(0.0) as isize;
3742                    let real_idx = if n < 0 { total + n } else { n - 1 };
3743                    if real_idx < 0 || real_idx >= total {
3744                        return Ok(ResultData::Error("#VALUE!".to_string()));
3745                    }
3746                    indices.push(real_idx as usize);
3747                }
3748                let mut result = Vec::new();
3749                if func_name == "CHOOSEROWS" {
3750                    for r in indices {
3751                        for c in 0..cols {
3752                            result.push(flat[r * cols + c].clone());
3753                        }
3754                    }
3755                } else {
3756                    for r in 0..rows {
3757                        for &c in &indices {
3758                            result.push(flat[r * cols + c].clone());
3759                        }
3760                    }
3761                }
3762                Ok(ResultData::List(result))
3763            }
3764            "DROP" | "TAKE" => {
3765                if args.len() < 2 {
3766                    return Ok(ResultData::Error("#VALUE!".to_string()));
3767                }
3768                let (flat, cols) = self.array_shape(&args[0], context, row, col, deps, scope)?;
3769                let num_rows = flat.len().checked_div(cols).unwrap_or(0) as isize;
3770                let is_take = func_name == "TAKE";
3771                let rows_n = self
3772                    .to_f64(&self.evaluate_ast(&args[1], context, row, col, deps, scope)?)
3773                    .unwrap_or(0.0) as isize;
3774                let cols_n = match args.get(2) {
3775                    Some(e) => self
3776                        .to_f64(&self.evaluate_ast(e, context, row, col, deps, scope)?)
3777                        .unwrap_or(0.0) as isize,
3778                    None => {
3779                        if is_take {
3780                            cols as isize
3781                        } else {
3782                            0
3783                        }
3784                    }
3785                };
3786                let (row_start, row_end) = Self::drop_take_bounds(num_rows, rows_n, is_take);
3787                let (col_start, col_end) = Self::drop_take_bounds(cols as isize, cols_n, is_take);
3788                if row_start >= row_end || col_start >= col_end {
3789                    return Ok(ResultData::Error("#CALC!".to_string()));
3790                }
3791                let mut result = Vec::new();
3792                for r in row_start..row_end {
3793                    for c in col_start..col_end {
3794                        result.push(flat[(r as usize) * cols + (c as usize)].clone());
3795                    }
3796                }
3797                Ok(ResultData::List(result))
3798            }
3799            "EXPAND" => {
3800                if args.len() < 2 {
3801                    return Ok(ResultData::Error("#VALUE!".to_string()));
3802                }
3803                let (flat, cols) = self.array_shape(&args[0], context, row, col, deps, scope)?;
3804                let orig_rows = flat.len().checked_div(cols).unwrap_or(0);
3805                let new_rows = self
3806                    .to_f64(&self.evaluate_ast(&args[1], context, row, col, deps, scope)?)
3807                    .unwrap_or(orig_rows as f64) as usize;
3808                let new_cols = match args.get(2) {
3809                    Some(e) => self
3810                        .to_f64(&self.evaluate_ast(e, context, row, col, deps, scope)?)
3811                        .unwrap_or(cols as f64) as usize,
3812                    None => cols,
3813                };
3814                let pad = match args.get(3) {
3815                    Some(e) => self.evaluate_ast(e, context, row, col, deps, scope)?,
3816                    None => ResultData::Error("#N/A".to_string()),
3817                };
3818                if new_rows < orig_rows || new_cols < cols {
3819                    return Ok(ResultData::Error("#VALUE!".to_string()));
3820                }
3821                let mut result = Vec::with_capacity(new_rows * new_cols);
3822                for r in 0..new_rows {
3823                    for c in 0..new_cols {
3824                        result.push(if r < orig_rows && c < cols {
3825                            flat[r * cols + c].clone()
3826                        } else {
3827                            pad.clone()
3828                        });
3829                    }
3830                }
3831                Ok(ResultData::List(result))
3832            }
3833            "TOCOL" | "TOROW" => {
3834                let Some(arg) = args.first() else {
3835                    return Ok(ResultData::Error("#VALUE!".to_string()));
3836                };
3837                let (flat, cols) = self.array_shape(arg, context, row, col, deps, scope)?;
3838                let rows = flat.len().checked_div(cols).unwrap_or(0);
3839                let ignore = match args.get(1) {
3840                    Some(e) => self
3841                        .to_f64(&self.evaluate_ast(e, context, row, col, deps, scope)?)
3842                        .unwrap_or(0.0) as i64,
3843                    None => 0,
3844                };
3845                let scan_by_col = match args.get(2) {
3846                    Some(e) => self.to_bool(&self.evaluate_ast(e, context, row, col, deps, scope)?),
3847                    None => false,
3848                };
3849                let ordered: Vec<ResultData> = if scan_by_col {
3850                    let mut v = Vec::with_capacity(flat.len());
3851                    for c in 0..cols {
3852                        for r in 0..rows {
3853                            v.push(flat[r * cols + c].clone());
3854                        }
3855                    }
3856                    v
3857                } else {
3858                    flat
3859                };
3860                let filtered: Vec<ResultData> = ordered
3861                    .into_iter()
3862                    .filter(|v| match ignore {
3863                        1 => !matches!(v, ResultData::None),
3864                        2 => !matches!(v, ResultData::Error(_)),
3865                        3 => !matches!(v, ResultData::None | ResultData::Error(_)),
3866                        _ => true,
3867                    })
3868                    .collect();
3869                Ok(ResultData::List(filtered))
3870            }
3871            "WRAPROWS" | "WRAPCOLS" => {
3872                if args.len() < 2 {
3873                    return Ok(ResultData::Error("#VALUE!".to_string()));
3874                }
3875                let (flat, _cols) = self.array_shape(&args[0], context, row, col, deps, scope)?;
3876                let wrap = self
3877                    .to_f64(&self.evaluate_ast(&args[1], context, row, col, deps, scope)?)
3878                    .unwrap_or(1.0)
3879                    .max(1.0) as usize;
3880                let pad = match args.get(2) {
3881                    Some(e) => self.evaluate_ast(e, context, row, col, deps, scope)?,
3882                    None => ResultData::Error("#N/A".to_string()),
3883                };
3884                if func_name == "WRAPROWS" {
3885                    let mut result = flat;
3886                    let rem = result.len() % wrap;
3887                    if rem != 0 {
3888                        result.extend(std::iter::repeat_n(pad, wrap - rem));
3889                    }
3890                    Ok(ResultData::List(result))
3891                } else {
3892                    let num_result_cols = flat.len().div_ceil(wrap).max(1);
3893                    let total = wrap * num_result_cols;
3894                    let mut result = Vec::with_capacity(total);
3895                    for i in 0..total {
3896                        let col = i / wrap;
3897                        let r = i % wrap;
3898                        let target = r * num_result_cols + col;
3899                        while result.len() <= target {
3900                            result.push(pad.clone());
3901                        }
3902                        if i < flat.len() {
3903                            result[target] = flat[i].clone();
3904                        }
3905                    }
3906                    Ok(ResultData::List(result))
3907                }
3908            }
3909            "UNIQUE" => {
3910                let Some(arg) = args.first() else {
3911                    return Ok(ResultData::Error("#VALUE!".to_string()));
3912                };
3913                let (flat, _cols) = self.array_shape(arg, context, row, col, deps, scope)?;
3914                let exactly_once = match args.get(2) {
3915                    Some(e) => self.to_bool(&self.evaluate_ast(e, context, row, col, deps, scope)?),
3916                    None => false,
3917                };
3918                let mut seen: Vec<(String, ResultData, usize)> = Vec::new();
3919                for v in &flat {
3920                    let key = match v {
3921                        ResultData::None => "blank:".to_string(),
3922                        ResultData::Boolean(b) => format!("bool:{b}"),
3923                        ResultData::Integer(i) => format!("num:{}", *i as f64),
3924                        ResultData::Float(f) => format!("num:{f}"),
3925                        ResultData::String(s) => format!("str:{s}"),
3926                        ResultData::Error(e) => format!("err:{e}"),
3927                        ResultData::List(_) | ResultData::Dict(_) => format!("other:{v}"),
3928                    };
3929                    match seen.iter_mut().find(|(k, ..)| k == &key) {
3930                        Some(entry) => entry.2 += 1,
3931                        None => seen.push((key, v.clone(), 1)),
3932                    }
3933                }
3934                let result: Vec<ResultData> = seen
3935                    .into_iter()
3936                    .filter(|(_, _, count)| !exactly_once || *count == 1)
3937                    .map(|(_, v, _)| v)
3938                    .collect();
3939                Ok(ResultData::List(result))
3940            }
3941            "SORT" => {
3942                let Some(arg) = args.first() else {
3943                    return Ok(ResultData::Error("#VALUE!".to_string()));
3944                };
3945                let (flat, cols) = self.array_shape(arg, context, row, col, deps, scope)?;
3946                let rows = flat.len().checked_div(cols).unwrap_or(0);
3947                let sort_index = match args.get(1) {
3948                    Some(e) => self
3949                        .to_f64(&self.evaluate_ast(e, context, row, col, deps, scope)?)
3950                        .unwrap_or(1.0) as usize,
3951                    None => 1,
3952                };
3953                let sort_order = match args.get(2) {
3954                    Some(e) => self
3955                        .to_f64(&self.evaluate_ast(e, context, row, col, deps, scope)?)
3956                        .unwrap_or(1.0),
3957                    None => 1.0,
3958                };
3959                let col_idx = sort_index.saturating_sub(1).min(cols.saturating_sub(1));
3960                let mut row_indices: Vec<usize> = (0..rows).collect();
3961                row_indices.sort_by(|&a, &b| {
3962                    Self::sort_compare_blanks_last(
3963                        &flat[a * cols + col_idx],
3964                        &flat[b * cols + col_idx],
3965                        sort_order,
3966                    )
3967                });
3968                let mut result = Vec::with_capacity(flat.len());
3969                for r in row_indices {
3970                    for c in 0..cols {
3971                        result.push(flat[r * cols + c].clone());
3972                    }
3973                }
3974                Ok(ResultData::List(result))
3975            }
3976            "SORTBY" => {
3977                if args.len() < 2 {
3978                    return Ok(ResultData::Error("#VALUE!".to_string()));
3979                }
3980                let (flat, cols) = self.array_shape(&args[0], context, row, col, deps, scope)?;
3981                let rows = flat.len().checked_div(cols).unwrap_or(0);
3982                let by = self.eval_as_array(&args[1], context, row, col, deps, scope)?;
3983                let order = match args.get(2) {
3984                    Some(e) => self
3985                        .to_f64(&self.evaluate_ast(e, context, row, col, deps, scope)?)
3986                        .unwrap_or(1.0),
3987                    None => 1.0,
3988                };
3989                let mut row_indices: Vec<usize> = (0..rows).collect();
3990                row_indices.sort_by(|&a, &b| {
3991                    let va = by.get(a).cloned().unwrap_or(ResultData::None);
3992                    let vb = by.get(b).cloned().unwrap_or(ResultData::None);
3993                    Self::sort_compare_blanks_last(&va, &vb, order)
3994                });
3995                let mut result = Vec::with_capacity(flat.len());
3996                for r in row_indices {
3997                    for c in 0..cols {
3998                        result.push(flat[r * cols + c].clone());
3999                    }
4000                }
4001                Ok(ResultData::List(result))
4002            }
4003            "FILTER" => {
4004                if args.len() < 2 {
4005                    return Ok(ResultData::Error("#VALUE!".to_string()));
4006                }
4007                let (flat, cols) = self.array_shape(&args[0], context, row, col, deps, scope)?;
4008                let rows = flat.len().checked_div(cols).unwrap_or(0);
4009                let include = self.eval_as_array(&args[1], context, row, col, deps, scope)?;
4010                let mut result = Vec::new();
4011                for r in 0..rows {
4012                    let keep = include.get(r).map(|v| self.to_bool(v)).unwrap_or(false);
4013                    if keep {
4014                        for c in 0..cols {
4015                            result.push(flat[r * cols + c].clone());
4016                        }
4017                    }
4018                }
4019                if result.is_empty() {
4020                    match args.get(2) {
4021                        Some(e) => Ok(self.evaluate_ast(e, context, row, col, deps, scope)?),
4022                        None => Ok(ResultData::Error("#CALC!".to_string())),
4023                    }
4024                } else {
4025                    Ok(ResultData::List(result))
4026                }
4027            }
4028            "TRIMRANGE" => {
4029                let Some(arg) = args.first() else {
4030                    return Ok(ResultData::Error("#VALUE!".to_string()));
4031                };
4032                let (flat, cols) = self.array_shape(arg, context, row, col, deps, scope)?;
4033                let rows = flat.len().checked_div(cols).unwrap_or(0);
4034                let is_blank = |v: &ResultData| {
4035                    matches!(v, ResultData::None)
4036                        || matches!(v, ResultData::String(s) if s.is_empty())
4037                };
4038                let row_blank = |r: usize| (0..cols).all(|c| is_blank(&flat[r * cols + c]));
4039                let col_blank = |c: usize| (0..rows).all(|r| is_blank(&flat[r * cols + c]));
4040                let mut r_start = 0;
4041                while r_start < rows && row_blank(r_start) {
4042                    r_start += 1;
4043                }
4044                let mut r_end = rows;
4045                while r_end > r_start && row_blank(r_end - 1) {
4046                    r_end -= 1;
4047                }
4048                let mut c_start = 0;
4049                while c_start < cols && col_blank(c_start) {
4050                    c_start += 1;
4051                }
4052                let mut c_end = cols;
4053                while c_end > c_start && col_blank(c_end - 1) {
4054                    c_end -= 1;
4055                }
4056                let mut result = Vec::new();
4057                for r in r_start..r_end {
4058                    for c in c_start..c_end {
4059                        result.push(flat[r * cols + c].clone());
4060                    }
4061                }
4062                Ok(ResultData::List(result))
4063            }
4064            _ => unreachable!(),
4065        }
4066    }
4067
4068    /// The raw text typed into a cell. Returns `None` if ref is OOB
4069    pub fn get_src(&self, cell: &CellRef) -> Option<&String> {
4070        let col = self.columns.get(cell.col);
4071        if let Some(col) = col {
4072            col.src.get(cell.row)
4073        } else {
4074            None
4075        }
4076    }
4077
4078    /// [`Sheet::get_src`] but returns empty string on OOB.
4079    pub fn get_src_str(&self, cell: &CellRef) -> String {
4080        let col = self.columns.get(cell.col);
4081        if let Some(col) = col {
4082            col.src.get(cell.row).cloned().unwrap_or("".to_string())
4083        } else {
4084            "".to_string()
4085        }
4086    }
4087
4088    /// [`Sheet::get_src`] as a borrowed `&str` for read-only.
4089    pub fn get_src_str_ref(&self, cell: &CellRef) -> Option<&str> {
4090        let col = self.columns.get(cell.col)?;
4091        col.src.get(cell.row).map(|s| s.as_str())
4092    }
4093
4094    /// See [`get_word_boundaries_from_str`].
4095    pub fn get_word_boundaries(&self, cell: &CellRef, char_offset: usize) -> (usize, usize) {
4096        let text = self.get_src_str(cell);
4097        get_word_boundaries_from_str(&text, char_offset)
4098    }
4099}