formualizer_eval/builtins/lookup/
array_shape.rs

1//! Dynamic array shape helpers: TOCOL and TOROW.
2
3use super::super::utils::collapse_if_scalar;
4use crate::args::{ArgSchema, CoercionPolicy, ShapeKind};
5use crate::function::Function;
6use crate::traits::{ArgumentHandle, FunctionContext};
7use formualizer_common::{ArgKind, ExcelError, ExcelErrorKind, LiteralValue};
8use formualizer_macros::func_caps;
9
10/// Converts an array or range into a single column.
11///
12/// Flattens input values into one column, with options to ignore blanks/errors
13/// and to scan by row or by column.
14///
15/// # Remarks
16/// - `ignore` values are `0` keep all, `1` ignore blanks, `2` ignore errors, `3` ignore both.
17/// - `scan_by_column` defaults to FALSE, so values are read row by row.
18///
19/// ```yaml,sandbox
20/// title: "Flatten row by row"
21/// formula: "=TOCOL({1,2;3,4})"
22/// expected: [[1],[2],[3],[4]]
23/// ```
24///
25/// ```yaml,sandbox
26/// title: "Scan by column"
27/// formula: "=TOCOL({1,2;3,4},0,TRUE)"
28/// expected: [[1],[3],[2],[4]]
29/// ```
30///
31/// ```yaml,docs
32/// related:
33///   - TOROW
34///   - HSTACK
35///   - VSTACK
36/// faq:
37///   - q: "Can TOCOL filter blanks and errors?"
38///     a: "Yes. Use the ignore argument to drop blanks, errors, or both."
39/// ```
40#[derive(Debug)]
41pub struct ToColFn;
42
43/// Converts an array or range into a single row.
44///
45/// Flattens input values into one row, with options to ignore blanks/errors and
46/// to scan by row or by column.
47///
48/// # Remarks
49/// - `ignore` values are `0` keep all, `1` ignore blanks, `2` ignore errors, `3` ignore both.
50/// - `scan_by_column` defaults to FALSE, so values are read row by row.
51///
52/// ```yaml,sandbox
53/// title: "Flatten to row"
54/// formula: "=TOROW({1,2;3,4})"
55/// expected: [[1,2,3,4]]
56/// ```
57///
58/// ```yaml,sandbox
59/// title: "Scan by column"
60/// formula: "=TOROW({1,2;3,4},0,TRUE)"
61/// expected: [[1,3,2,4]]
62/// ```
63///
64/// ```yaml,docs
65/// related:
66///   - TOCOL
67///   - HSTACK
68///   - VSTACK
69/// faq:
70///   - q: "Does TOROW preserve row order by default?"
71///     a: "Yes. The default scan order is row-major."
72/// ```
73#[derive(Debug)]
74pub struct ToRowFn;
75
76fn schema() -> &'static [ArgSchema] {
77    use once_cell::sync::Lazy;
78    static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(|| {
79        vec![
80            ArgSchema {
81                kinds: smallvec::smallvec![ArgKind::Range, ArgKind::Any],
82                required: true,
83                by_ref: false,
84                shape: ShapeKind::Range,
85                coercion: CoercionPolicy::None,
86                max: None,
87                repeating: None,
88                default: None,
89            },
90            ArgSchema {
91                kinds: smallvec::smallvec![ArgKind::Number],
92                required: false,
93                by_ref: false,
94                shape: ShapeKind::Scalar,
95                coercion: CoercionPolicy::NumberLenientText,
96                max: None,
97                repeating: None,
98                default: Some(LiteralValue::Int(0)),
99            },
100            ArgSchema {
101                kinds: smallvec::smallvec![ArgKind::Logical, ArgKind::Number],
102                required: false,
103                by_ref: false,
104                shape: ShapeKind::Scalar,
105                coercion: CoercionPolicy::None,
106                max: None,
107                repeating: None,
108                default: Some(LiteralValue::Boolean(false)),
109            },
110        ]
111    });
112    &SCHEMA
113}
114
115fn materialize_arg<'b>(arg: &ArgumentHandle<'_, 'b>) -> Result<Vec<Vec<LiteralValue>>, ExcelError> {
116    if let Ok(view) = arg.range_view() {
117        let mut rows = Vec::new();
118        view.for_each_row(&mut |row| {
119            rows.push(row.to_vec());
120            Ok(())
121        })?;
122        return Ok(rows);
123    }
124
125    Ok(match arg.value()?.into_literal() {
126        LiteralValue::Array(rows) => rows,
127        v => vec![vec![v]],
128    })
129}
130
131fn ignore_mode<'b>(args: &[ArgumentHandle<'_, 'b>]) -> Result<i64, ExcelError> {
132    if args.len() < 2 {
133        return Ok(0);
134    }
135    let raw = args[1].value()?.into_literal();
136    let n = match raw {
137        LiteralValue::Int(i) => i,
138        LiteralValue::Number(n) => n as i64,
139        LiteralValue::Error(e) => return Err(e),
140        other => crate::coercion::to_number_lenient(&other)? as i64,
141    };
142    if !(0..=3).contains(&n) {
143        return Err(
144            ExcelError::new(ExcelErrorKind::Value).with_message("ignore must be 0, 1, 2, or 3")
145        );
146    }
147    Ok(n)
148}
149
150fn scan_by_column<'b>(args: &[ArgumentHandle<'_, 'b>]) -> Result<bool, ExcelError> {
151    if args.len() < 3 {
152        return Ok(false);
153    }
154    crate::coercion::to_logical(&args[2].value()?.into_literal())
155}
156
157fn include_cell(v: &LiteralValue, ignore: i64) -> bool {
158    let is_blank = matches!(v, LiteralValue::Empty);
159    let is_error = matches!(v, LiteralValue::Error(_));
160    match ignore {
161        1 => !is_blank,
162        2 => !is_error,
163        3 => !is_blank && !is_error,
164        _ => true,
165    }
166}
167
168fn flatten_array<'b>(args: &[ArgumentHandle<'_, 'b>]) -> Result<Vec<LiteralValue>, ExcelError> {
169    if args.is_empty() || args.len() > 3 {
170        return Err(ExcelError::new(ExcelErrorKind::Value));
171    }
172    let data = materialize_arg(&args[0])?;
173    let ignore = ignore_mode(args)?;
174    let scan_by_col = scan_by_column(args)?;
175
176    let rows = data.len();
177    let cols = data.iter().map(Vec::len).max().unwrap_or(0);
178    let mut flat = Vec::with_capacity(rows.saturating_mul(cols));
179
180    if scan_by_col {
181        for c in 0..cols {
182            for row in &data {
183                let v = row.get(c).cloned().unwrap_or(LiteralValue::Empty);
184                if include_cell(&v, ignore) {
185                    flat.push(v);
186                }
187            }
188        }
189    } else {
190        for row in &data {
191            for c in 0..cols {
192                let v = row.get(c).cloned().unwrap_or(LiteralValue::Empty);
193                if include_cell(&v, ignore) {
194                    flat.push(v);
195                }
196            }
197        }
198    }
199
200    if flat.is_empty() {
201        return Err(ExcelError::new(ExcelErrorKind::Calc)
202            .with_message("TOCOL/TOROW returned an empty array"));
203    }
204    Ok(flat)
205}
206
207/// [formualizer-docgen:schema:start]
208/// Name: TOCOL
209/// Type: ToColFn
210/// Min args: 1
211/// Max args: variadic
212/// Variadic: true
213/// Signature: TOCOL(arg1: range|any@range, arg2?: number@scalar, arg3?...: logical|number@scalar)
214/// Arg schema: arg1{kinds=range|any,required=true,shape=range,by_ref=false,coercion=None,max=None,repeating=None,default=false}; arg2{kinds=number,required=false,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=true}; arg3{kinds=logical|number,required=false,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=true}
215/// Caps: PURE
216/// [formualizer-docgen:schema:end]
217impl Function for ToColFn {
218    func_caps!(PURE);
219    fn name(&self) -> &'static str {
220        "TOCOL"
221    }
222    fn min_args(&self) -> usize {
223        1
224    }
225    fn variadic(&self) -> bool {
226        true
227    }
228    fn arg_schema(&self) -> &'static [ArgSchema] {
229        schema()
230    }
231    fn eval<'a, 'b, 'c>(
232        &self,
233        args: &'c [ArgumentHandle<'a, 'b>],
234        ctx: &dyn FunctionContext<'b>,
235    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
236        match flatten_array(args) {
237            Ok(flat) => Ok(collapse_if_scalar(
238                flat.into_iter().map(|v| vec![v]).collect(),
239                ctx.date_system(),
240            )),
241            Err(e) => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e))),
242        }
243    }
244}
245
246/// [formualizer-docgen:schema:start]
247/// Name: TOROW
248/// Type: ToRowFn
249/// Min args: 1
250/// Max args: variadic
251/// Variadic: true
252/// Signature: TOROW(arg1: range|any@range, arg2?: number@scalar, arg3?...: logical|number@scalar)
253/// Arg schema: arg1{kinds=range|any,required=true,shape=range,by_ref=false,coercion=None,max=None,repeating=None,default=false}; arg2{kinds=number,required=false,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=true}; arg3{kinds=logical|number,required=false,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=true}
254/// Caps: PURE
255/// [formualizer-docgen:schema:end]
256impl Function for ToRowFn {
257    func_caps!(PURE);
258    fn name(&self) -> &'static str {
259        "TOROW"
260    }
261    fn min_args(&self) -> usize {
262        1
263    }
264    fn variadic(&self) -> bool {
265        true
266    }
267    fn arg_schema(&self) -> &'static [ArgSchema] {
268        schema()
269    }
270    fn eval<'a, 'b, 'c>(
271        &self,
272        args: &'c [ArgumentHandle<'a, 'b>],
273        ctx: &dyn FunctionContext<'b>,
274    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
275        match flatten_array(args) {
276            Ok(flat) => Ok(collapse_if_scalar(vec![flat], ctx.date_system())),
277            Err(e) => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e))),
278        }
279    }
280}
281
282pub fn register_builtins() {
283    use crate::function_registry::register_function;
284    use std::sync::Arc;
285
286    register_function(Arc::new(ToColFn));
287    register_function(Arc::new(ToRowFn));
288}
289
290#[cfg(test)]
291mod tests {
292    use crate::builtins::logical::{FalseFn, TrueFn};
293    use crate::test_workbook::TestWorkbook;
294    use formualizer_common::{ExcelError, ExcelErrorKind, LiteralValue};
295    use formualizer_parse::parser::parse;
296    use std::sync::Arc;
297
298    fn eval(formula: &str) -> LiteralValue {
299        let wb = TestWorkbook::new()
300            .with_function(Arc::new(super::ToColFn))
301            .with_function(Arc::new(super::ToRowFn))
302            .with_function(Arc::new(TrueFn))
303            .with_function(Arc::new(FalseFn));
304        let interp = wb.interpreter();
305        let ast = parse(formula).expect("parse");
306        interp.evaluate_ast(&ast).expect("eval").into_literal()
307    }
308
309    #[test]
310    fn tocol_flattens_rows_by_default() {
311        assert_eq!(
312            eval("=TOCOL({1,2;3,4})"),
313            LiteralValue::Array(vec![
314                vec![LiteralValue::Number(1.0)],
315                vec![LiteralValue::Number(2.0)],
316                vec![LiteralValue::Number(3.0)],
317                vec![LiteralValue::Number(4.0)],
318            ])
319        );
320    }
321
322    #[test]
323    fn torow_can_scan_by_column() {
324        assert_eq!(
325            eval("=TOROW({1,2;3,4},0,TRUE)"),
326            LiteralValue::Array(vec![vec![
327                LiteralValue::Number(1.0),
328                LiteralValue::Number(3.0),
329                LiteralValue::Number(2.0),
330                LiteralValue::Number(4.0),
331            ]])
332        );
333    }
334
335    #[test]
336    fn ignores_blanks_and_errors() {
337        let value = eval("=TOROW({1,#N/A;\"\",2},2,FALSE)");
338        assert_eq!(
339            value,
340            LiteralValue::Array(vec![vec![
341                LiteralValue::Number(1.0),
342                LiteralValue::Text(String::new()),
343                LiteralValue::Number(2.0),
344            ]])
345        );
346
347        let value = eval("=TOROW({#N/A},2)");
348        assert!(matches!(value, LiteralValue::Error(e) if e.kind == ExcelErrorKind::Calc));
349    }
350
351    #[test]
352    fn rejects_invalid_ignore_mode() {
353        let value = eval("=TOCOL({1,2},4)");
354        assert!(matches!(
355            value,
356            LiteralValue::Error(ExcelError {
357                kind: ExcelErrorKind::Value,
358                ..
359            })
360        ));
361    }
362}
formualizer_eval/builtins/lookup/array_shape.rs

formualizer_eval/builtins/lookup/
array_shape.rs
