sheetkit_core/formula/functions/
text.rs1use crate::cell::CellValue;
4use crate::error::{Error, Result};
5use crate::formula::ast::Expr;
6use crate::formula::eval::{coerce_to_number, Evaluator};
7use crate::formula::functions::conversion::checked_trunc_count;
8use crate::formula::functions::{check_arg_count, eval_text_arg, wildcard_search};
9
10macro_rules! text_arg {
12 ($expr:expr, $ctx:expr) => {
13 match eval_text_arg($expr, $ctx)? {
14 Ok(text) => text,
15 Err(error) => return Ok(error),
16 }
17 };
18}
19
20const REPLACE_MAX_ARGUMENT: usize = 32_767;
22
23pub fn fn_concat(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
25 check_arg_count("CONCAT", args, 1, 255)?;
26 let mut result = String::new();
27 for arg in args {
28 result.push_str(&text_arg!(arg, ctx));
29 }
30 Ok(CellValue::String(result))
31}
32
33pub fn fn_find(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
35 check_arg_count("FIND", args, 2, 3)?;
36 let find_text = text_arg!(&args[0], ctx);
37 let within_text = text_arg!(&args[1], ctx);
38 let start_value = if args.len() > 2 {
39 coerce_to_number(&ctx.eval_expr(&args[2])?)?
40 } else {
41 1.0
42 };
43 let Some(start_num) = checked_trunc_count(start_value) else {
44 return Ok(CellValue::Error("#VALUE!".to_string()));
45 };
46 let char_count = within_text.chars().count();
47 if start_num < 1 || start_num > char_count.saturating_add(1) {
48 return Ok(CellValue::Error("#VALUE!".to_string()));
49 }
50 let search_in = &within_text[character_position_to_byte_offset(&within_text, start_num)..];
51 match search_in.find(&find_text) {
52 Some(pos) => Ok(CellValue::Number(
53 (start_num + search_in[..pos].chars().count()) as f64,
54 )),
55 None => Ok(CellValue::Error("#VALUE!".to_string())),
56 }
57}
58
59pub fn fn_search(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
61 check_arg_count("SEARCH", args, 2, 3)?;
62 let find_text = text_arg!(&args[0], ctx);
63 let within_text = text_arg!(&args[1], ctx);
64 let start_value = if args.len() > 2 {
65 coerce_to_number(&ctx.eval_expr(&args[2])?)?
66 } else {
67 1.0
68 };
69 let Some(start_num) = checked_trunc_count(start_value) else {
70 return Ok(CellValue::Error("#VALUE!".to_string()));
71 };
72 let char_count = within_text.chars().count();
73 if start_num < 1 || start_num > char_count.saturating_add(1) {
74 return Ok(CellValue::Error("#VALUE!".to_string()));
75 }
76 let search_in = &within_text[character_position_to_byte_offset(&within_text, start_num)..];
77 match wildcard_search(search_in, &find_text, ctx)? {
78 Some(char_offset) => Ok(CellValue::Number((char_offset + start_num) as f64)),
79 None => Ok(CellValue::Error("#VALUE!".to_string())),
80 }
81}
82
83fn character_position_to_byte_offset(text: &str, position: usize) -> usize {
84 text.char_indices()
85 .nth(position - 1)
86 .map_or(text.len(), |(byte_offset, _)| byte_offset)
87}
88
89pub fn fn_substitute(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
91 check_arg_count("SUBSTITUTE", args, 3, 4)?;
92 let text = text_arg!(&args[0], ctx);
93 let old_text = text_arg!(&args[1], ctx);
94 let new_text = text_arg!(&args[2], ctx);
95 if old_text.is_empty() {
96 return Ok(CellValue::String(text));
97 }
98 if args.len() > 3 {
99 let instance_value = coerce_to_number(&ctx.eval_expr(&args[3])?)?;
100 let Some(instance_num) = checked_trunc_count(instance_value) else {
101 return Ok(CellValue::Error("#VALUE!".to_string()));
102 };
103 if instance_num < 1 {
104 return Ok(CellValue::Error("#VALUE!".to_string()));
105 }
106 let occurrence = text.match_indices(&old_text).nth(instance_num - 1);
107 let replacement_count = usize::from(occurrence.is_some());
108 let output_len = checked_substitute_output_len(
109 text.len(),
110 old_text.len(),
111 new_text.len(),
112 replacement_count,
113 );
114 let Some(output_len) = output_len else {
115 ctx.check_output_bytes(u64::MAX)?;
116 unreachable!("an overflowing substitute result must exceed the formula budget");
117 };
118 ctx.check_output_bytes(u64::try_from(output_len).unwrap_or(u64::MAX))?;
119 let Some((position, _)) = occurrence else {
120 return Ok(CellValue::String(text));
121 };
122 let mut result = String::new();
123 result
124 .try_reserve_exact(output_len)
125 .map_err(|_| Error::Internal("substitute allocation failed".to_string()))?;
126 result.push_str(&text[..position]);
127 result.push_str(&new_text);
128 result.push_str(&text[position + old_text.len()..]);
129 Ok(CellValue::String(result))
130 } else {
131 let replacement_count = text.match_indices(&old_text).count();
132 let Some(output_len) = checked_substitute_output_len(
133 text.len(),
134 old_text.len(),
135 new_text.len(),
136 replacement_count,
137 ) else {
138 ctx.check_output_bytes(u64::MAX)?;
139 unreachable!("an overflowing substitute result must exceed the formula budget");
140 };
141 ctx.check_output_bytes(u64::try_from(output_len).unwrap_or(u64::MAX))?;
142 let mut result = String::new();
143 result
144 .try_reserve_exact(output_len)
145 .map_err(|_| Error::Internal("substitute allocation failed".to_string()))?;
146 let mut copied_until = 0;
147 for (position, matched) in text.match_indices(&old_text) {
148 result.push_str(&text[copied_until..position]);
149 result.push_str(&new_text);
150 copied_until = position + matched.len();
151 }
152 result.push_str(&text[copied_until..]);
153 Ok(CellValue::String(result))
154 }
155}
156
157fn checked_substitute_output_len(
158 text_len: usize,
159 old_len: usize,
160 new_len: usize,
161 replacement_count: usize,
162) -> Option<usize> {
163 let removed = old_len.checked_mul(replacement_count)?;
164 let added = new_len.checked_mul(replacement_count)?;
165 text_len.checked_sub(removed)?.checked_add(added)
166}
167
168pub fn fn_replace(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
173 check_arg_count("REPLACE", args, 4, 4)?;
174 let old_text = text_arg!(&args[0], ctx);
175 let start_value = coerce_to_number(&ctx.eval_expr(&args[1])?)?;
176 let count_value = coerce_to_number(&ctx.eval_expr(&args[2])?)?;
177 let new_text = text_arg!(&args[3], ctx);
178 let (Some(start_num), Some(num_chars)) = (
179 checked_trunc_count(start_value),
180 checked_trunc_count(count_value),
181 ) else {
182 return Ok(CellValue::Error("#VALUE!".to_string()));
183 };
184 if !(1..=REPLACE_MAX_ARGUMENT).contains(&start_num) || num_chars > REPLACE_MAX_ARGUMENT {
185 return Ok(CellValue::Error("#VALUE!".to_string()));
186 }
187 let chars: Vec<char> = old_text.chars().collect();
188 let start = start_num - 1;
189 let end = start.saturating_add(num_chars).min(chars.len());
190 let mut result = String::new();
191 for &ch in &chars[..start.min(chars.len())] {
192 result.push(ch);
193 }
194 result.push_str(&new_text);
195 for &ch in &chars[end..] {
196 result.push(ch);
197 }
198 Ok(CellValue::String(result))
199}
200
201pub fn fn_rept(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
203 check_arg_count("REPT", args, 2, 2)?;
204 let text = text_arg!(&args[0], ctx);
205 let times_value = coerce_to_number(&ctx.eval_expr(&args[1])?)?;
206 let Some(times) = checked_trunc_count(times_value) else {
207 return Ok(CellValue::Error("#VALUE!".to_string()));
208 };
209 if text
210 .chars()
211 .count()
212 .checked_mul(times)
213 .is_none_or(|length| length > 32_767)
214 {
215 return Ok(CellValue::Error("#VALUE!".to_string()));
216 }
217 Ok(CellValue::String(text.repeat(times)))
218}
219
220pub fn fn_exact(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
222 check_arg_count("EXACT", args, 2, 2)?;
223 let text1 = text_arg!(&args[0], ctx);
224 let text2 = text_arg!(&args[1], ctx);
225 Ok(CellValue::Bool(text1 == text2))
226}
227
228pub fn fn_t(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
230 check_arg_count("T", args, 1, 1)?;
231 let v = ctx.eval_expr(&args[0])?;
232 match v {
233 CellValue::String(s) => Ok(CellValue::String(s)),
234 CellValue::Error(_) => Ok(v),
235 _ => Ok(CellValue::String(String::new())),
236 }
237}
238
239pub fn fn_proper(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
241 check_arg_count("PROPER", args, 1, 1)?;
242 let text = text_arg!(&args[0], ctx);
243 let mut result = String::with_capacity(text.len());
244 let mut capitalize_next = true;
245 for ch in text.chars() {
246 if ch.is_alphabetic() {
247 if capitalize_next {
248 result.extend(ch.to_uppercase());
249 capitalize_next = false;
250 } else {
251 result.extend(ch.to_lowercase());
252 }
253 } else {
254 result.push(ch);
255 capitalize_next = true;
256 }
257 }
258 Ok(CellValue::String(result))
259}
260
261#[cfg(test)]
262mod tests {
263 use super::*;
264 use crate::formula::eval::{evaluate, CellSnapshot};
265 use crate::formula::parser::parse_formula;
266
267 fn eval(formula: &str) -> CellValue {
268 let snap = CellSnapshot::new("Sheet1".to_string());
269 let expr = parse_formula(formula).unwrap();
270 evaluate(&expr, &snap).unwrap()
271 }
272
273 #[test]
274 fn test_concat() {
275 assert_eq!(
276 eval(r#"CONCAT("Hello"," ","World")"#),
277 CellValue::String("Hello World".to_string())
278 );
279 }
280
281 #[test]
282 fn test_find_basic() {
283 assert_eq!(eval(r#"FIND("B","ABCABC")"#), CellValue::Number(2.0));
284 }
285
286 #[test]
287 fn test_find_start_num() {
288 assert_eq!(eval(r#"FIND("B","ABCABC",3)"#), CellValue::Number(5.0));
289 }
290
291 #[test]
292 fn test_find_uses_character_positions_for_multibyte_text() {
293 assert_eq!(eval(r#"FIND("나","가나다",2)"#), CellValue::Number(2.0));
294 }
295
296 #[test]
297 fn test_find_not_found() {
298 assert_eq!(
299 eval(r#"FIND("Z","ABCABC")"#),
300 CellValue::Error("#VALUE!".to_string())
301 );
302 }
303
304 #[test]
305 fn test_search_case_insensitive() {
306 assert_eq!(eval(r#"SEARCH("b","ABCABC")"#), CellValue::Number(2.0));
307 }
308
309 #[test]
310 fn test_search_wildcard() {
311 assert_eq!(eval(r#"SEARCH("A*C","ABCABC")"#), CellValue::Number(1.0));
312 assert_eq!(eval(r#"SEARCH("*","ABC",4)"#), CellValue::Number(4.0));
313 }
314
315 #[test]
316 fn test_search_uses_character_positions_for_multibyte_text() {
317 assert_eq!(eval(r#"SEARCH("나","가나다",2)"#), CellValue::Number(2.0));
318 }
319
320 #[test]
321 fn test_substitute_all() {
322 assert_eq!(
323 eval(r#"SUBSTITUTE("aabbcc","b","X")"#),
324 CellValue::String("aaXXcc".to_string())
325 );
326 }
327
328 #[test]
329 fn test_substitute_instance() {
330 assert_eq!(
331 eval(r#"SUBSTITUTE("aabbcc","b","X",2)"#),
332 CellValue::String("aabXcc".to_string())
333 );
334 }
335
336 #[test]
337 fn substitute_output_length_detects_integer_overflow() {
338 assert_eq!(
339 checked_substitute_output_len(usize::MAX, 1, usize::MAX, 2),
340 None
341 );
342 }
343
344 #[test]
345 fn test_replace() {
346 assert_eq!(
347 eval(r#"REPLACE("ABCDEF",3,2,"XY")"#),
348 CellValue::String("ABXYEF".to_string())
349 );
350 }
351
352 #[test]
353 fn test_rept() {
354 assert_eq!(
355 eval(r#"REPT("AB",3)"#),
356 CellValue::String("ABABAB".to_string())
357 );
358 }
359
360 #[test]
361 fn test_rept_rejects_results_over_excel_character_limit() {
362 assert_eq!(
363 eval(r#"REPT("AB",16384)"#),
364 CellValue::Error("#VALUE!".to_string())
365 );
366 }
367
368 #[test]
369 fn test_exact_true() {
370 assert_eq!(eval(r#"EXACT("ABC","ABC")"#), CellValue::Bool(true));
371 }
372
373 #[test]
374 fn test_exact_false_case() {
375 assert_eq!(eval(r#"EXACT("ABC","abc")"#), CellValue::Bool(false));
376 }
377
378 #[test]
379 fn test_t_with_text() {
380 assert_eq!(
381 eval(r#"T("hello")"#),
382 CellValue::String("hello".to_string())
383 );
384 }
385
386 #[test]
387 fn test_t_with_number() {
388 assert_eq!(eval("T(42)"), CellValue::String(String::new()));
389 }
390
391 #[test]
392 fn test_proper() {
393 assert_eq!(
394 eval(r#"PROPER("hello world")"#),
395 CellValue::String("Hello World".to_string())
396 );
397 }
398}