sheetkit_core/formula/functions/
text.rs

1//! Text formula functions: CONCAT, FIND, SEARCH, SUBSTITUTE, REPLACE, REPT, EXACT, T, PROPER.
2
3use crate::cell::CellValue;
4use crate::error::Result;
5use crate::formula::ast::Expr;
6use crate::formula::eval::{coerce_to_number, coerce_to_string, Evaluator};
7use crate::formula::functions::check_arg_count;
8
9/// CONCAT(text1, [text2], ...) - concatenates multiple values.
10pub fn fn_concat(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
11    check_arg_count("CONCAT", args, 1, 255)?;
12    let mut result = String::new();
13    for arg in args {
14        let v = ctx.eval_expr(arg)?;
15        result.push_str(&coerce_to_string(&v));
16    }
17    Ok(CellValue::String(result))
18}
19
20/// FIND(find_text, within_text, [start_num]) - case-sensitive search.
21pub fn fn_find(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
22    check_arg_count("FIND", args, 2, 3)?;
23    let find_text = coerce_to_string(&ctx.eval_expr(&args[0])?);
24    let within_text = coerce_to_string(&ctx.eval_expr(&args[1])?);
25    let start_num = if args.len() > 2 {
26        coerce_to_number(&ctx.eval_expr(&args[2])?)? as usize
27    } else {
28        1
29    };
30    if start_num < 1 || start_num > within_text.len() + 1 {
31        return Ok(CellValue::Error("#VALUE!".to_string()));
32    }
33    let search_in = &within_text[(start_num - 1)..];
34    match search_in.find(&find_text) {
35        Some(pos) => Ok(CellValue::Number((pos + start_num) as f64)),
36        None => Ok(CellValue::Error("#VALUE!".to_string())),
37    }
38}
39
40/// SEARCH(find_text, within_text, [start_num]) - case-insensitive search with wildcards.
41pub fn fn_search(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
42    check_arg_count("SEARCH", args, 2, 3)?;
43    let find_text = coerce_to_string(&ctx.eval_expr(&args[0])?).to_ascii_lowercase();
44    let within_text = coerce_to_string(&ctx.eval_expr(&args[1])?).to_ascii_lowercase();
45    let start_num = if args.len() > 2 {
46        coerce_to_number(&ctx.eval_expr(&args[2])?)? as usize
47    } else {
48        1
49    };
50    if start_num < 1 || start_num > within_text.len() + 1 {
51        return Ok(CellValue::Error("#VALUE!".to_string()));
52    }
53    let search_in = &within_text[(start_num - 1)..];
54    if find_text.contains('*') || find_text.contains('?') {
55        for i in 0..=search_in.len() {
56            let substring = &search_in[i..];
57            if wildcard_match_prefix(&find_text, substring) {
58                return Ok(CellValue::Number((i + start_num) as f64));
59            }
60        }
61        Ok(CellValue::Error("#VALUE!".to_string()))
62    } else {
63        match search_in.find(&find_text) {
64            Some(pos) => Ok(CellValue::Number((pos + start_num) as f64)),
65            None => Ok(CellValue::Error("#VALUE!".to_string())),
66        }
67    }
68}
69
70/// Wildcard match where the pattern must be fully consumed but the text may have trailing chars.
71fn wildcard_match_prefix(pattern: &str, text: &str) -> bool {
72    let p: Vec<char> = pattern.chars().collect();
73    let t: Vec<char> = text.chars().collect();
74    let mut pi = 0;
75    let mut ti = 0;
76    let mut star_pi = usize::MAX;
77    let mut star_ti = 0;
78    while pi < p.len() {
79        if pi < p.len() && p[pi] == '*' {
80            star_pi = pi;
81            star_ti = ti;
82            pi += 1;
83        } else if ti < t.len() && (p[pi] == '?' || p[pi] == t[ti]) {
84            pi += 1;
85            ti += 1;
86        } else if star_pi != usize::MAX {
87            pi = star_pi + 1;
88            star_ti += 1;
89            ti = star_ti;
90            if ti > t.len() {
91                return false;
92            }
93        } else {
94            return false;
95        }
96    }
97    true
98}
99
100/// SUBSTITUTE(text, old_text, new_text, [instance_num]) - replaces occurrences.
101pub fn fn_substitute(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
102    check_arg_count("SUBSTITUTE", args, 3, 4)?;
103    let text = coerce_to_string(&ctx.eval_expr(&args[0])?);
104    let old_text = coerce_to_string(&ctx.eval_expr(&args[1])?);
105    let new_text = coerce_to_string(&ctx.eval_expr(&args[2])?);
106    if old_text.is_empty() {
107        return Ok(CellValue::String(text));
108    }
109    if args.len() > 3 {
110        let instance_num = coerce_to_number(&ctx.eval_expr(&args[3])?)? as usize;
111        if instance_num < 1 {
112            return Ok(CellValue::Error("#VALUE!".to_string()));
113        }
114        let mut count = 0;
115        let mut result = String::new();
116        let mut remaining = text.as_str();
117        while let Some(pos) = remaining.find(&old_text) {
118            count += 1;
119            if count == instance_num {
120                result.push_str(&remaining[..pos]);
121                result.push_str(&new_text);
122                result.push_str(&remaining[pos + old_text.len()..]);
123                return Ok(CellValue::String(result));
124            }
125            result.push_str(&remaining[..pos + old_text.len()]);
126            remaining = &remaining[pos + old_text.len()..];
127        }
128        result.push_str(remaining);
129        Ok(CellValue::String(result))
130    } else {
131        Ok(CellValue::String(text.replace(&old_text, &new_text)))
132    }
133}
134
135/// REPLACE(old_text, start_num, num_chars, new_text) - replaces by position.
136pub fn fn_replace(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
137    check_arg_count("REPLACE", args, 4, 4)?;
138    let old_text = coerce_to_string(&ctx.eval_expr(&args[0])?);
139    let start_num = coerce_to_number(&ctx.eval_expr(&args[1])?)? as usize;
140    let num_chars = coerce_to_number(&ctx.eval_expr(&args[2])?)? as usize;
141    let new_text = coerce_to_string(&ctx.eval_expr(&args[3])?);
142    if start_num < 1 {
143        return Ok(CellValue::Error("#VALUE!".to_string()));
144    }
145    let chars: Vec<char> = old_text.chars().collect();
146    let start = start_num - 1;
147    let end = (start + num_chars).min(chars.len());
148    let mut result = String::new();
149    for &ch in &chars[..start.min(chars.len())] {
150        result.push(ch);
151    }
152    result.push_str(&new_text);
153    for &ch in &chars[end..] {
154        result.push(ch);
155    }
156    Ok(CellValue::String(result))
157}
158
159/// REPT(text, number_times) - repeats text.
160pub fn fn_rept(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
161    check_arg_count("REPT", args, 2, 2)?;
162    let text = coerce_to_string(&ctx.eval_expr(&args[0])?);
163    let times = coerce_to_number(&ctx.eval_expr(&args[1])?)? as usize;
164    Ok(CellValue::String(text.repeat(times)))
165}
166
167/// EXACT(text1, text2) - case-sensitive comparison.
168pub fn fn_exact(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
169    check_arg_count("EXACT", args, 2, 2)?;
170    let text1 = coerce_to_string(&ctx.eval_expr(&args[0])?);
171    let text2 = coerce_to_string(&ctx.eval_expr(&args[1])?);
172    Ok(CellValue::Bool(text1 == text2))
173}
174
175/// T(value) - returns text if value is text, empty string otherwise.
176pub fn fn_t(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
177    check_arg_count("T", args, 1, 1)?;
178    let v = ctx.eval_expr(&args[0])?;
179    match v {
180        CellValue::String(s) => Ok(CellValue::String(s)),
181        _ => Ok(CellValue::String(String::new())),
182    }
183}
184
185/// PROPER(text) - capitalizes first letter of each word.
186pub fn fn_proper(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
187    check_arg_count("PROPER", args, 1, 1)?;
188    let text = coerce_to_string(&ctx.eval_expr(&args[0])?);
189    let mut result = String::with_capacity(text.len());
190    let mut capitalize_next = true;
191    for ch in text.chars() {
192        if ch.is_alphabetic() {
193            if capitalize_next {
194                result.extend(ch.to_uppercase());
195                capitalize_next = false;
196            } else {
197                result.extend(ch.to_lowercase());
198            }
199        } else {
200            result.push(ch);
201            capitalize_next = true;
202        }
203    }
204    Ok(CellValue::String(result))
205}
206
207#[cfg(test)]
208mod tests {
209    use super::*;
210    use crate::formula::eval::{evaluate, CellSnapshot};
211    use crate::formula::parser::parse_formula;
212
213    fn eval(formula: &str) -> CellValue {
214        let snap = CellSnapshot::new("Sheet1".to_string());
215        let expr = parse_formula(formula).unwrap();
216        evaluate(&expr, &snap).unwrap()
217    }
218
219    #[test]
220    fn test_concat() {
221        assert_eq!(
222            eval(r#"CONCAT("Hello"," ","World")"#),
223            CellValue::String("Hello World".to_string())
224        );
225    }
226
227    #[test]
228    fn test_find_basic() {
229        assert_eq!(eval(r#"FIND("B","ABCABC")"#), CellValue::Number(2.0));
230    }
231
232    #[test]
233    fn test_find_start_num() {
234        assert_eq!(eval(r#"FIND("B","ABCABC",3)"#), CellValue::Number(5.0));
235    }
236
237    #[test]
238    fn test_find_not_found() {
239        assert_eq!(
240            eval(r#"FIND("Z","ABCABC")"#),
241            CellValue::Error("#VALUE!".to_string())
242        );
243    }
244
245    #[test]
246    fn test_search_case_insensitive() {
247        assert_eq!(eval(r#"SEARCH("b","ABCABC")"#), CellValue::Number(2.0));
248    }
249
250    #[test]
251    fn test_search_wildcard() {
252        assert_eq!(eval(r#"SEARCH("A*C","ABCABC")"#), CellValue::Number(1.0));
253    }
254
255    #[test]
256    fn test_substitute_all() {
257        assert_eq!(
258            eval(r#"SUBSTITUTE("aabbcc","b","X")"#),
259            CellValue::String("aaXXcc".to_string())
260        );
261    }
262
263    #[test]
264    fn test_substitute_instance() {
265        assert_eq!(
266            eval(r#"SUBSTITUTE("aabbcc","b","X",2)"#),
267            CellValue::String("aabXcc".to_string())
268        );
269    }
270
271    #[test]
272    fn test_replace() {
273        assert_eq!(
274            eval(r#"REPLACE("ABCDEF",3,2,"XY")"#),
275            CellValue::String("ABXYEF".to_string())
276        );
277    }
278
279    #[test]
280    fn test_rept() {
281        assert_eq!(
282            eval(r#"REPT("AB",3)"#),
283            CellValue::String("ABABAB".to_string())
284        );
285    }
286
287    #[test]
288    fn test_exact_true() {
289        assert_eq!(eval(r#"EXACT("ABC","ABC")"#), CellValue::Bool(true));
290    }
291
292    #[test]
293    fn test_exact_false_case() {
294        assert_eq!(eval(r#"EXACT("ABC","abc")"#), CellValue::Bool(false));
295    }
296
297    #[test]
298    fn test_t_with_text() {
299        assert_eq!(
300            eval(r#"T("hello")"#),
301            CellValue::String("hello".to_string())
302        );
303    }
304
305    #[test]
306    fn test_t_with_number() {
307        assert_eq!(eval("T(42)"), CellValue::String(String::new()));
308    }
309
310    #[test]
311    fn test_proper() {
312        assert_eq!(
313            eval(r#"PROPER("hello world")"#),
314            CellValue::String("Hello World".to_string())
315        );
316    }
317}
sheetkit_core/formula/functions/text.rs

sheetkit_core/formula/functions/
text.rs
