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