sheetkit_core/formula/functions/
information.rs1use crate::cell::CellValue;
5use crate::error::Result;
6use crate::formula::ast::Expr;
7use crate::formula::eval::{coerce_to_number, Evaluator};
8use crate::formula::functions::check_arg_count;
9
10pub fn fn_iserr(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
12 check_arg_count("ISERR", args, 1, 1)?;
13 let v = ctx.eval_expr(&args[0])?;
14 let result = match &v {
15 CellValue::Error(e) => e != "#N/A",
16 _ => false,
17 };
18 Ok(CellValue::Bool(result))
19}
20
21pub fn fn_isna(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
23 check_arg_count("ISNA", args, 1, 1)?;
24 let v = ctx.eval_expr(&args[0])?;
25 let result = matches!(&v, CellValue::Error(e) if e == "#N/A");
26 Ok(CellValue::Bool(result))
27}
28
29pub fn fn_islogical(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
31 check_arg_count("ISLOGICAL", args, 1, 1)?;
32 let v = ctx.eval_expr(&args[0])?;
33 Ok(CellValue::Bool(matches!(v, CellValue::Bool(_))))
34}
35
36pub fn fn_iseven(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
38 check_arg_count("ISEVEN", args, 1, 1)?;
39 let v = ctx.eval_expr(&args[0])?;
40 let n = coerce_to_number(&v)?;
41 if !n.is_finite() {
42 return Ok(CellValue::Error("#NUM!".to_string()));
43 }
44 Ok(CellValue::Bool(n.trunc() % 2.0 == 0.0))
46}
47
48pub fn fn_isodd(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
50 check_arg_count("ISODD", args, 1, 1)?;
51 let v = ctx.eval_expr(&args[0])?;
52 let n = coerce_to_number(&v)?;
53 if !n.is_finite() {
54 return Ok(CellValue::Error("#NUM!".to_string()));
55 }
56 Ok(CellValue::Bool(n.trunc() % 2.0 != 0.0))
57}
58
59pub fn fn_type(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
61 check_arg_count("TYPE", args, 1, 1)?;
62 let v = ctx.eval_expr(&args[0])?;
63 let code = match v {
64 CellValue::Number(_) | CellValue::Date(_) | CellValue::Empty => 1.0,
65 CellValue::String(_) | CellValue::RichString(_) => 2.0,
66 CellValue::Bool(_) => 4.0,
67 CellValue::Error(_) => 16.0,
68 CellValue::Formula { .. } => 1.0,
69 };
70 Ok(CellValue::Number(code))
71}
72
73pub fn fn_n(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
75 check_arg_count("N", args, 1, 1)?;
76 let v = ctx.eval_expr(&args[0])?;
77 match v {
78 CellValue::Number(n) => Ok(CellValue::Number(n)),
79 CellValue::Date(n) => Ok(CellValue::Number(n)),
80 CellValue::Bool(b) => Ok(CellValue::Number(if b { 1.0 } else { 0.0 })),
81 CellValue::Error(_) => Ok(v),
82 _ => Ok(CellValue::Number(0.0)),
83 }
84}
85
86pub fn fn_na(args: &[Expr], _ctx: &mut Evaluator) -> Result<CellValue> {
88 check_arg_count("NA", args, 0, 0)?;
89 Ok(CellValue::Error("#N/A".to_string()))
90}
91
92pub fn fn_error_type(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
96 check_arg_count("ERROR.TYPE", args, 1, 1)?;
97 let v = ctx.eval_expr(&args[0])?;
98 match v {
99 CellValue::Error(ref e) => {
100 let code = match e.as_str() {
101 "#NULL!" => 1.0,
102 "#DIV/0!" => 2.0,
103 "#VALUE!" => 3.0,
104 "#REF!" => 4.0,
105 "#NAME?" => 5.0,
106 "#NUM!" => 6.0,
107 "#N/A" => 7.0,
108 _ => return Ok(CellValue::Error("#N/A".to_string())),
109 };
110 Ok(CellValue::Number(code))
111 }
112 _ => Ok(CellValue::Error("#N/A".to_string())),
113 }
114}
115
116#[cfg(test)]
117mod tests {
118 use crate::cell::CellValue;
119 use crate::formula::eval::{evaluate, CellSnapshot};
120 use crate::formula::parser::parse_formula;
121
122 fn eval_with_data(formula: &str, data: &[(&str, u32, u32, CellValue)]) -> CellValue {
123 let mut snap = CellSnapshot::new("Sheet1".to_string());
124 for (sheet, col, row, val) in data {
125 snap.set_cell(sheet, *col, *row, val.clone());
126 }
127 let expr = parse_formula(formula).unwrap();
128 evaluate(&expr, &snap).unwrap()
129 }
130
131 fn eval(formula: &str) -> CellValue {
132 eval_with_data(formula, &[])
133 }
134
135 #[test]
138 fn iserr_div_zero() {
139 let data = vec![("Sheet1", 1, 1, CellValue::Error("#DIV/0!".to_string()))];
140 assert_eq!(eval_with_data("ISERR(A1)", &data), CellValue::Bool(true));
141 }
142
143 #[test]
144 fn iserr_na_is_false() {
145 let data = vec![("Sheet1", 1, 1, CellValue::Error("#N/A".to_string()))];
146 assert_eq!(eval_with_data("ISERR(A1)", &data), CellValue::Bool(false));
147 }
148
149 #[test]
150 fn iserr_not_error() {
151 assert_eq!(eval("ISERR(42)"), CellValue::Bool(false));
152 }
153
154 #[test]
157 fn isna_true() {
158 assert_eq!(eval("ISNA(NA())"), CellValue::Bool(true));
159 }
160
161 #[test]
162 fn isna_false_for_other_error() {
163 let data = vec![("Sheet1", 1, 1, CellValue::Error("#DIV/0!".to_string()))];
164 assert_eq!(eval_with_data("ISNA(A1)", &data), CellValue::Bool(false));
165 }
166
167 #[test]
168 fn isna_false_for_number() {
169 assert_eq!(eval("ISNA(1)"), CellValue::Bool(false));
170 }
171
172 #[test]
175 fn islogical_true() {
176 assert_eq!(eval("ISLOGICAL(TRUE)"), CellValue::Bool(true));
177 }
178
179 #[test]
180 fn islogical_false_for_number() {
181 assert_eq!(eval("ISLOGICAL(1)"), CellValue::Bool(false));
182 }
183
184 #[test]
187 fn iseven_true() {
188 assert_eq!(eval("ISEVEN(4)"), CellValue::Bool(true));
189 }
190
191 #[test]
192 fn iseven_false() {
193 assert_eq!(eval("ISEVEN(3)"), CellValue::Bool(false));
194 }
195
196 #[test]
197 fn iseven_zero() {
198 assert_eq!(eval("ISEVEN(0)"), CellValue::Bool(true));
199 }
200
201 #[test]
204 fn isodd_true() {
205 assert_eq!(eval("ISODD(3)"), CellValue::Bool(true));
206 }
207
208 #[test]
209 fn isodd_false() {
210 assert_eq!(eval("ISODD(4)"), CellValue::Bool(false));
211 }
212
213 #[test]
216 fn type_number() {
217 assert_eq!(eval("TYPE(42)"), CellValue::Number(1.0));
218 }
219
220 #[test]
221 fn type_text() {
222 assert_eq!(eval("TYPE(\"hello\")"), CellValue::Number(2.0));
223 }
224
225 #[test]
226 fn type_boolean() {
227 assert_eq!(eval("TYPE(TRUE)"), CellValue::Number(4.0));
228 }
229
230 #[test]
231 fn type_error() {
232 assert_eq!(eval("TYPE(NA())"), CellValue::Number(16.0));
233 }
234
235 #[test]
238 fn n_number() {
239 assert_eq!(eval("N(42)"), CellValue::Number(42.0));
240 }
241
242 #[test]
243 fn n_true() {
244 assert_eq!(eval("N(TRUE)"), CellValue::Number(1.0));
245 }
246
247 #[test]
248 fn n_false() {
249 assert_eq!(eval("N(FALSE)"), CellValue::Number(0.0));
250 }
251
252 #[test]
253 fn n_string() {
254 assert_eq!(eval("N(\"text\")"), CellValue::Number(0.0));
255 }
256
257 #[test]
258 fn n_error() {
259 assert_eq!(eval("N(NA())"), CellValue::Error("#N/A".to_string()));
260 }
261
262 #[test]
265 fn na_returns_error() {
266 assert_eq!(eval("NA()"), CellValue::Error("#N/A".to_string()));
267 }
268
269 #[test]
272 fn error_type_div_zero() {
273 let data = vec![("Sheet1", 1, 1, CellValue::Error("#DIV/0!".to_string()))];
274 assert_eq!(
275 eval_with_data("ERROR.TYPE(A1)", &data),
276 CellValue::Number(2.0)
277 );
278 }
279
280 #[test]
281 fn error_type_na() {
282 assert_eq!(eval("ERROR.TYPE(NA())"), CellValue::Number(7.0));
283 }
284
285 #[test]
286 fn error_type_value_error() {
287 let data = vec![("Sheet1", 1, 1, CellValue::Error("#VALUE!".to_string()))];
288 assert_eq!(
289 eval_with_data("ERROR.TYPE(A1)", &data),
290 CellValue::Number(3.0)
291 );
292 }
293
294 #[test]
295 fn error_type_not_error() {
296 assert_eq!(eval("ERROR.TYPE(42)"), CellValue::Error("#N/A".to_string()));
297 }
298}