umya_spreadsheet/helper/
formula.rs

1use crate::helper::address::*;
2use crate::helper::coordinate::*;
3use crate::helper::coordinate::*;
4use crate::helper::range::*;
5use crate::structs::StringValue;
6use fancy_regex::{Captures, Regex};
7
8/** PARTLY BASED ON: */
9/** Copyright (c) 2007 E. W. Bachtal, Inc. */
10/** https://ewbi.blogs.com/develops/2007/03/excel_formula_p.html */
11/** https://ewbi.blogs.com/develops/2004/12/excel_formula_p.html */
12
13#[derive(Clone, Debug, PartialEq)]
14pub enum FormulaTokenTypes {
15    Noop,
16    Operand,
17    Function,
18    Subexpression,
19    Argument,
20    OperatorPrefix,
21    OperatorInfix,
22    OperatorPostfix,
23    Whitespace,
24    Unknown,
25}
26
27#[derive(Clone, Debug, PartialEq)]
28pub enum FormulaTokenSubTypes {
29    Nothing,
30    Start,
31    Stop,
32    Text,
33    Number,
34    Logical,
35    Error,
36    Range,
37    Math,
38    Concatenation,
39    Intersection,
40    Union,
41}
42
43#[derive(Clone, Debug)]
44pub struct FormulaToken {
45    value: StringValue,
46    token_type: FormulaTokenTypes,
47    token_sub_type: FormulaTokenSubTypes,
48}
49impl Default for FormulaToken {
50    #[inline]
51    fn default() -> Self {
52        Self {
53            value: StringValue::default(),
54            token_type: FormulaTokenTypes::Unknown,
55            token_sub_type: FormulaTokenSubTypes::Nothing,
56        }
57    }
58}
59impl FormulaToken {
60    #[inline]
61    pub fn get_value(&self) -> &str {
62        self.value.get_value_str()
63    }
64
65    #[inline]
66    pub fn set_value<S: Into<String>>(&mut self, value: S) -> &mut Self {
67        self.value.set_value(value);
68        self
69    }
70
71    #[inline]
72    pub fn get_token_type(&self) -> &FormulaTokenTypes {
73        &self.token_type
74    }
75
76    #[inline]
77    pub fn set_token_type(&mut self, value: FormulaTokenTypes) -> &mut Self {
78        self.token_type = value;
79        self
80    }
81
82    #[inline]
83    pub fn get_token_sub_type(&self) -> &FormulaTokenSubTypes {
84        &self.token_sub_type
85    }
86
87    #[inline]
88    pub fn set_token_sub_type(&mut self, value: FormulaTokenSubTypes) -> &mut Self {
89        self.token_sub_type = value;
90        self
91    }
92}
93
94const QUOTE_DOUBLE: char = '"';
95const QUOTE_SINGLE: char = '\'';
96const BRACKET_CLOSE: char = ']';
97const BRACKET_OPEN: char = '[';
98const BRACE_OPEN: char = '{';
99const BRACE_CLOSE: char = '}';
100const PAREN_OPEN: char = '(';
101const PAREN_CLOSE: char = ')';
102const SEMICOLON: char = ';';
103const WHITESPACE: char = ' ';
104const COMMA: char = ',';
105const ERROR_START: char = '#';
106
107const OPERATORS_SN: &str = "+-";
108const OPERATORS_INFIX: &str = "+-*/^&=><";
109const OPERATORS_POSTFIX: &str = "%";
110
111pub const ERRORS: &'static [&'static str] = &[
112    "#NULL!", "#DIV/0!", "#VALUE!", "#REF!", "#NAME?", "#NUM!", "#N/A",
113];
114const COMPARATORS_MULTI: &'static [&'static str] = &[">=", "<=", "<>"];
115
116lazy_static! {
117    pub static ref SCIENTIFIC_REGEX: Regex = Regex::new(r#"/^[1-9]{1}(\\.\\d+)?E{1}$/"#).unwrap();
118}
119
120pub(crate) fn parse_to_tokens<S: Into<String>>(formula: S) -> Vec<FormulaToken> {
121    let mut tokens: Vec<FormulaToken> = Vec::new();
122
123    let formula = formula.into();
124    let formula_length = formula.chars().count();
125    if formula_length < 2 || formula.chars().next().unwrap() != '=' {
126        return tokens;
127    }
128
129    // Helper variables
130    let mut tokens1: Vec<FormulaToken> = Vec::new();
131    let mut tokens2: Vec<FormulaToken> = Vec::new();
132    let mut stack: Vec<FormulaToken> = Vec::new();
133
134    let mut in_string = false;
135    let mut in_path = false;
136    let mut in_range = false;
137    let mut in_error = false;
138    let mut next_token: Option<FormulaToken> = None;
139
140    let mut index = 1;
141    let mut value = String::new();
142
143    while index < formula_length {
144        // double-quoted strings
145        // embeds are doubled
146        // end marks token
147        if in_string {
148            if formula.chars().nth(index).unwrap() == self::QUOTE_DOUBLE {
149                if ((index + 2) <= formula_length)
150                    && (formula.chars().nth(index + 1).unwrap() == self::QUOTE_DOUBLE)
151                {
152                    value = format!("{}{}", value, self::QUOTE_DOUBLE);
153                    index += 1;
154                } else {
155                    in_string = false;
156                    let mut obj = FormulaToken::default();
157                    obj.set_value(value);
158                    obj.set_token_type(FormulaTokenTypes::Operand);
159                    obj.set_token_sub_type(FormulaTokenSubTypes::Text);
160                    tokens1.push(obj);
161                    value = String::new();
162                }
163            } else {
164                value = format!("{}{}", value, formula.chars().nth(index).unwrap());
165            }
166            index += 1;
167
168            continue;
169        }
170
171        // single-quoted strings (links)
172        // embeds are double
173        // end does not mark a token
174        if in_path {
175            if formula.chars().nth(index).unwrap() == self::QUOTE_SINGLE {
176                if ((index + 2) <= formula_length)
177                    && (formula.chars().nth(index + 1).unwrap() == self::QUOTE_SINGLE)
178                {
179                    value = format!("{}{}", value, self::QUOTE_SINGLE);
180                    index += 1;
181                } else {
182                    in_path = false;
183                }
184            } else {
185                value = format!("{}{}", value, formula.chars().nth(index).unwrap());
186            }
187            index += 1;
188
189            continue;
190        }
191
192        // bracked strings (R1C1 range index or linked workbook name)
193        // no embeds (changed to "()" by Excel)
194        // end does not mark a token
195        if in_range {
196            if formula.chars().nth(index).unwrap() == self::BRACKET_CLOSE {
197                in_range = false;
198            }
199            value = format!("{}{}", value, formula.chars().nth(index).unwrap());
200            index;
201
202            continue;
203        }
204
205        // error values
206        // end marks a token, determined from absolute list of values
207        if in_error {
208            value = format!("{}{}", value, formula.chars().nth(index).unwrap());
209            index += 1;
210            if self::ERRORS.iter().any(|&x| x == &value) {
211                in_error = false;
212                let mut obj = FormulaToken::default();
213                obj.set_value(value);
214                obj.set_token_type(FormulaTokenTypes::Operand);
215                obj.set_token_sub_type(FormulaTokenSubTypes::Error);
216                tokens1.push(obj);
217                value = String::new();
218            }
219
220            continue;
221        }
222
223        // scientific notation check
224        if self::OPERATORS_SN.contains(formula.chars().nth(index).unwrap()) {
225            if value.len() > 1 {
226                if SCIENTIFIC_REGEX
227                    .is_match(&formula.chars().nth(index).unwrap().to_string())
228                    .unwrap_or(false)
229                {
230                    value = format!("{}{}", value, formula.chars().nth(index).unwrap());
231                    index += 1;
232
233                    continue;
234                }
235            }
236        }
237
238        // independent character evaluation (order not important)
239
240        // establish state-dependent character evaluations
241        if formula.chars().nth(index).unwrap() == self::QUOTE_DOUBLE {
242            if value != "" {
243                // unexpected
244                let mut obj = FormulaToken::default();
245                obj.set_value(value);
246                obj.set_token_type(FormulaTokenTypes::Unknown);
247                tokens1.push(obj);
248                value = String::new();
249            }
250            in_string = true;
251            index += 1;
252
253            continue;
254        }
255
256        if formula.chars().nth(index).unwrap() == self::QUOTE_SINGLE {
257            if value != "" {
258                // unexpected
259                let mut obj = FormulaToken::default();
260                obj.set_value(value);
261                obj.set_token_type(FormulaTokenTypes::Unknown);
262                tokens1.push(obj);
263                value = String::new();
264            }
265            in_string = true;
266            index += 1;
267
268            continue;
269        }
270
271        if formula.chars().nth(index).unwrap() == self::BRACKET_OPEN {
272            in_range = true;
273            value = format!("{}{}", value, self::BRACKET_OPEN);
274            index += 1;
275
276            continue;
277        }
278
279        if formula.chars().nth(index).unwrap() == self::ERROR_START {
280            if value != "" {
281                // unexpected
282                let mut obj = FormulaToken::default();
283                obj.set_value(value);
284                obj.set_token_type(FormulaTokenTypes::Unknown);
285                tokens1.push(obj);
286                value = String::new();
287            }
288            in_error = true;
289            value = format!("{}{}", value, self::ERROR_START);
290            index += 1;
291
292            continue;
293        }
294
295        // mark start and end of arrays and array rows
296        if formula.chars().nth(index).unwrap() == self::BRACE_OPEN {
297            if value != "" {
298                // unexpected
299                let mut obj = FormulaToken::default();
300                obj.set_value(value);
301                obj.set_token_type(FormulaTokenTypes::Unknown);
302                tokens1.push(obj);
303                value = String::new();
304            }
305
306            let mut obj = FormulaToken::default();
307            obj.set_value("ARRAY");
308            obj.set_token_type(FormulaTokenTypes::Function);
309            obj.set_token_sub_type(FormulaTokenSubTypes::Start);
310            tokens1.push(obj.clone());
311            stack.push(obj);
312
313            let mut obj = FormulaToken::default();
314            obj.set_value("ARRAYROW");
315            obj.set_token_type(FormulaTokenTypes::Function);
316            obj.set_token_sub_type(FormulaTokenSubTypes::Start);
317            tokens1.push(obj.clone());
318            stack.push(obj);
319
320            index += 1;
321
322            continue;
323        }
324
325        if formula.chars().nth(index).unwrap() == self::SEMICOLON {
326            if value != "" {
327                let mut obj = FormulaToken::default();
328                obj.set_value(value);
329                obj.set_token_type(FormulaTokenTypes::Operand);
330                tokens1.push(obj);
331                value = String::new();
332            }
333
334            let mut obj = stack.pop().unwrap();
335            obj.set_value("");
336            obj.set_token_sub_type(FormulaTokenSubTypes::Stop);
337            tokens1.push(obj);
338
339            let mut obj = FormulaToken::default();
340            obj.set_value(",");
341            obj.set_token_type(FormulaTokenTypes::Argument);
342            tokens1.push(obj);
343
344            let mut obj = FormulaToken::default();
345            obj.set_value("ARRAYROW");
346            obj.set_token_type(FormulaTokenTypes::Function);
347            obj.set_token_sub_type(FormulaTokenSubTypes::Start);
348            tokens1.push(obj.clone());
349            stack.push(obj);
350
351            index += 1;
352
353            continue;
354        }
355
356        if formula.chars().nth(index).unwrap() == self::BRACE_CLOSE {
357            if value != "" {
358                let mut obj = FormulaToken::default();
359                obj.set_value(value);
360                obj.set_token_type(FormulaTokenTypes::Operand);
361                tokens1.push(obj);
362                value = String::new();
363            }
364
365            let mut obj = stack.pop().unwrap();
366            obj.set_value("");
367            obj.set_token_sub_type(FormulaTokenSubTypes::Stop);
368            tokens1.push(obj);
369
370            let mut obj = stack.pop().unwrap();
371            obj.set_value("");
372            obj.set_token_sub_type(FormulaTokenSubTypes::Stop);
373            tokens1.push(obj);
374
375            index += 1;
376
377            continue;
378        }
379
380        // trim white-space
381        if formula.chars().nth(index).unwrap() == self::WHITESPACE {
382            if value != "" {
383                let mut obj = FormulaToken::default();
384                obj.set_value(value);
385                obj.set_token_type(FormulaTokenTypes::Operand);
386                tokens1.push(obj);
387                value = String::new();
388            }
389            let mut obj = FormulaToken::default();
390            obj.set_value("");
391            obj.set_token_type(FormulaTokenTypes::Whitespace);
392            tokens1.push(obj);
393            index += 1;
394            while ((formula.chars().nth(index).unwrap() == self::WHITESPACE)
395                && (index < formula_length))
396            {
397                index += 1;
398            }
399
400            continue;
401        }
402
403        // multi-character comparators
404        if (index + 2) <= formula_length {
405            if COMPARATORS_MULTI
406                .iter()
407                .any(|&x| x == formula.chars().skip(index).take(2).collect::<String>())
408            {
409                if value != "" {
410                    let mut obj = FormulaToken::default();
411                    obj.set_value(value);
412                    obj.set_token_type(FormulaTokenTypes::Operand);
413                    tokens1.push(obj);
414                    value = String::new();
415                }
416                let mut obj = FormulaToken::default();
417                obj.set_value(formula.chars().skip(index).take(2).collect::<String>());
418                obj.set_token_type(FormulaTokenTypes::OperatorInfix);
419                obj.set_token_sub_type(FormulaTokenSubTypes::Logical);
420                tokens1.push(obj);
421                index += 2;
422
423                continue;
424            }
425        }
426
427        // standard infix operators
428        if self::OPERATORS_INFIX.contains(formula.chars().nth(index).unwrap()) {
429            if value != "" {
430                let mut obj = FormulaToken::default();
431                obj.set_value(value);
432                obj.set_token_type(FormulaTokenTypes::Operand);
433                tokens1.push(obj);
434                value = String::new();
435            }
436            let mut obj = FormulaToken::default();
437            obj.set_value(formula.chars().nth(index).unwrap());
438            obj.set_token_type(FormulaTokenTypes::OperatorInfix);
439            tokens1.push(obj);
440            index += 1;
441
442            continue;
443        }
444
445        // standard postfix operators (only one)
446        if self::OPERATORS_POSTFIX.contains(formula.chars().nth(index).unwrap()) {
447            if value != "" {
448                let mut obj = FormulaToken::default();
449                obj.set_value(value);
450                obj.set_token_type(FormulaTokenTypes::Operand);
451                tokens1.push(obj);
452                value = String::new();
453            }
454            let mut obj = FormulaToken::default();
455            obj.set_value(formula.chars().nth(index).unwrap());
456            obj.set_token_type(FormulaTokenTypes::OperatorPostfix);
457            tokens1.push(obj);
458            index += 1;
459
460            continue;
461        }
462
463        // start subexpression or function
464        if formula.chars().nth(index).unwrap() == self::PAREN_OPEN {
465            if value != "" {
466                let mut obj = FormulaToken::default();
467                obj.set_value(value);
468                obj.set_token_type(FormulaTokenTypes::Function);
469                obj.set_token_sub_type(FormulaTokenSubTypes::Start);
470                tokens1.push(obj.clone());
471                stack.push(obj);
472                value = String::new();
473            } else {
474                let mut obj = FormulaToken::default();
475                obj.set_value("");
476                obj.set_token_type(FormulaTokenTypes::Subexpression);
477                obj.set_token_sub_type(FormulaTokenSubTypes::Start);
478                tokens1.push(obj.clone());
479                stack.push(obj);
480            }
481            index += 1;
482
483            continue;
484        }
485
486        // function, subexpression, or array parameters, or operand unions
487        if formula.chars().nth(index).unwrap() == self::COMMA {
488            if value != "" {
489                let mut obj = FormulaToken::default();
490                obj.set_value(value);
491                obj.set_token_type(FormulaTokenTypes::Operand);
492                tokens1.push(obj);
493                value = String::new();
494            }
495
496            let mut obj = stack.pop().unwrap();
497            obj.set_value("");
498            obj.set_token_sub_type(FormulaTokenSubTypes::Stop);
499            stack.push(obj.clone());
500
501            if obj.get_token_type() == &FormulaTokenTypes::Function {
502                let mut obj = FormulaToken::default();
503                obj.set_value(",");
504                obj.set_token_type(FormulaTokenTypes::OperatorInfix);
505                obj.set_token_sub_type(FormulaTokenSubTypes::Union);
506                tokens1.push(obj);
507            } else {
508                let mut obj = FormulaToken::default();
509                obj.set_value(",");
510                obj.set_token_type(FormulaTokenTypes::Argument);
511                tokens1.push(obj);
512            }
513            index += 1;
514
515            continue;
516        }
517
518        // stop subexpression
519        if formula.chars().nth(index).unwrap() == self::PAREN_CLOSE {
520            if value != "" {
521                let mut obj = FormulaToken::default();
522                obj.set_value(value);
523                obj.set_token_type(FormulaTokenTypes::Operand);
524                tokens1.push(obj);
525                value = String::new();
526            }
527
528            let mut obj = stack.pop().unwrap();
529            obj.set_value("");
530            obj.set_token_sub_type(FormulaTokenSubTypes::Stop);
531            tokens1.push(obj);
532
533            index += 1;
534
535            continue;
536        }
537
538        // token accumulation
539        value = format!("{}{}", value, formula.chars().nth(index).unwrap());
540        index += 1;
541    }
542
543    // dump remaining accumulation
544    if value != "" {
545        let mut obj = FormulaToken::default();
546        obj.set_value(value.clone());
547        obj.set_token_type(FormulaTokenTypes::Operand);
548        tokens1.push(obj);
549    }
550
551    // move tokenList to new set, excluding unnecessary white-space tokens and converting necessary ones to intersections
552    let token_count = tokens1.len();
553    let mut previous_token = None;
554    let mut next_token = None;
555    for i in 0..token_count {
556        let token = tokens1.get(i).unwrap();
557        if i > 0 {
558            match tokens1.get((i - 1)) {
559                Some(v) => {
560                    previous_token = Some(v.clone());
561                }
562                None => {
563                    previous_token = None;
564                }
565            }
566        } else {
567            previous_token = None;
568        }
569
570        match tokens1.get((i + 1)) {
571            Some(v) => {
572                next_token = Some(tokens1.get((i + 1)).unwrap());
573            }
574            None => {
575                next_token = None;
576            }
577        }
578
579        if token.get_token_type() != &FormulaTokenTypes::Whitespace {
580            tokens2.push(token.clone());
581
582            continue;
583        }
584
585        if previous_token.is_none() {
586            continue;
587        }
588
589        if !(((previous_token.as_ref().unwrap().get_token_type() == &FormulaTokenTypes::Function)
590            && (previous_token.as_ref().unwrap().get_token_sub_type()
591                == &FormulaTokenSubTypes::Stop))
592            || ((previous_token.as_ref().unwrap().get_token_type()
593                == &FormulaTokenTypes::Subexpression)
594                && (previous_token.as_ref().unwrap().get_token_sub_type()
595                    == &FormulaTokenSubTypes::Stop))
596            || (previous_token.as_ref().unwrap().get_token_type() == &FormulaTokenTypes::Operand))
597        {
598            continue;
599        }
600
601        if next_token.is_none() {
602            continue;
603        }
604
605        if !(((next_token.as_ref().unwrap().get_token_type() == &FormulaTokenTypes::Function)
606            && (next_token.as_ref().unwrap().get_token_sub_type() == &FormulaTokenSubTypes::Start))
607            || ((next_token.as_ref().unwrap().get_token_type()
608                == &FormulaTokenTypes::Subexpression)
609                && (next_token.as_ref().unwrap().get_token_sub_type()
610                    == &FormulaTokenSubTypes::Start))
611            || (next_token.as_ref().unwrap().get_token_type() == &FormulaTokenTypes::Operand))
612        {
613            continue;
614        }
615
616        let mut obj = FormulaToken::default();
617        obj.set_value(value);
618        obj.set_token_type(FormulaTokenTypes::OperatorInfix);
619        obj.set_token_sub_type(FormulaTokenSubTypes::Intersection);
620        tokens2.push(obj);
621        value = String::new();
622    }
623
624    // move tokens to final list, switching infix "-" operators to prefix when appropriate, switching infix "+" operators
625    // to noop when appropriate, identifying operand and infix-operator subtypes, and pulling "@" from function names
626    let token_count = tokens2.len();
627    let mut previous_token = None;
628    for i in 0..token_count {
629        let mut token = tokens2.get(i).unwrap().clone();
630        if i > 0 {
631            match tokens2.get(i - 1) {
632                Some(v) => {
633                    previous_token = Some(v.clone());
634                }
635                None => {
636                    previous_token = None;
637                }
638            }
639        } else {
640            previous_token = None;
641        }
642
643        if token.get_token_type() == &FormulaTokenTypes::OperatorInfix && token.get_value() == "-" {
644            if i == 0 {
645                token.set_token_type(FormulaTokenTypes::OperatorPrefix);
646            } else if ((previous_token.as_ref().unwrap().get_token_type()
647                == &FormulaTokenTypes::Function)
648                && (previous_token.as_ref().unwrap().get_token_sub_type()
649                    == &FormulaTokenSubTypes::Stop))
650                || ((previous_token.as_ref().unwrap().get_token_type()
651                    == &FormulaTokenTypes::Subexpression)
652                    && (previous_token.as_ref().unwrap().get_token_sub_type()
653                        == &FormulaTokenSubTypes::Stop))
654                || (previous_token.as_ref().unwrap().get_token_type()
655                    == &FormulaTokenTypes::OperatorPostfix)
656                || (previous_token.as_ref().unwrap().get_token_type()
657                    == &FormulaTokenTypes::Operand)
658            {
659                token.set_token_sub_type(FormulaTokenSubTypes::Math);
660            } else {
661                token.set_token_type(FormulaTokenTypes::OperatorPrefix);
662            }
663
664            tokens.push(token.clone());
665
666            continue;
667        }
668
669        if token.get_token_type() == &FormulaTokenTypes::OperatorInfix && token.get_value() == "+" {
670            if i == 0 {
671                continue;
672            } else if ((previous_token.as_ref().unwrap().get_token_type()
673                == &FormulaTokenTypes::Function)
674                && (previous_token.as_ref().unwrap().get_token_sub_type()
675                    == &FormulaTokenSubTypes::Stop))
676                || ((previous_token.as_ref().unwrap().get_token_type()
677                    == &FormulaTokenTypes::Subexpression)
678                    && (previous_token.as_ref().unwrap().get_token_sub_type()
679                        == &FormulaTokenSubTypes::Stop))
680                || (previous_token.as_ref().unwrap().get_token_type()
681                    == &FormulaTokenTypes::OperatorPostfix)
682                || (previous_token.as_ref().unwrap().get_token_type()
683                    == &FormulaTokenTypes::Operand)
684            {
685                token.set_token_sub_type(FormulaTokenSubTypes::Math);
686            } else {
687                continue;
688            }
689
690            tokens.push(token.clone());
691
692            continue;
693        }
694
695        if token.get_token_type() == &FormulaTokenTypes::OperatorInfix
696            && token.get_token_sub_type() == &FormulaTokenSubTypes::Nothing
697        {
698            if "<>=".contains(token.get_value().chars().nth(0).unwrap()) {
699                token.set_token_sub_type(FormulaTokenSubTypes::Logical);
700            } else if token.get_value() == "&" {
701                token.set_token_sub_type(FormulaTokenSubTypes::Concatenation);
702            } else {
703                token.set_token_sub_type(FormulaTokenSubTypes::Math);
704            }
705
706            tokens.push(token.clone());
707
708            continue;
709        }
710
711        if token.get_token_type() == &FormulaTokenTypes::Operand
712            && token.get_token_sub_type() == &FormulaTokenSubTypes::Nothing
713        {
714            if !token.get_value().parse::<f64>().is_ok() {
715                if token.get_value().to_uppercase() == "TRUE"
716                    || token.get_value().to_uppercase() == "FALSE"
717                {
718                    token.set_token_sub_type(FormulaTokenSubTypes::Logical);
719                } else {
720                    token.set_token_sub_type(FormulaTokenSubTypes::Range);
721                }
722            } else {
723                token.set_token_sub_type(FormulaTokenSubTypes::Number);
724            }
725
726            tokens.push(token.clone());
727
728            continue;
729        }
730
731        if token.get_token_type() == &FormulaTokenTypes::Function {
732            if token.get_value() != "" {
733                if token.get_value().chars().nth(0).unwrap() == '@' {
734                    token.set_value(token.get_value().chars().skip(1).collect::<String>());
735                }
736            }
737        }
738
739        tokens.push(token.clone());
740    }
741    tokens
742}
743
744pub(crate) fn render(formula_token_list: &[FormulaToken]) -> String {
745    let mut result = String::new();
746    for token in formula_token_list {
747        if token.get_token_type() == &FormulaTokenTypes::Function
748            && token.get_token_sub_type() == &FormulaTokenSubTypes::Start
749        {
750            result = format!("{}{}", result, token.get_value());
751            result = format!("{}{}", result, self::PAREN_OPEN);
752        } else if token.get_token_type() == &FormulaTokenTypes::Function
753            && token.get_token_sub_type() == &FormulaTokenSubTypes::Stop
754        {
755            result = format!("{}{}", result, self::PAREN_CLOSE);
756        } else if token.get_token_type() == &FormulaTokenTypes::Subexpression
757            && token.get_token_sub_type() == &FormulaTokenSubTypes::Start
758        {
759            result = format!("{}{}", result, self::PAREN_OPEN);
760        } else if token.get_token_type() == &FormulaTokenTypes::Subexpression
761            && token.get_token_sub_type() == &FormulaTokenSubTypes::Stop
762        {
763            result = format!("{}{}", result, self::PAREN_CLOSE);
764        } else if token.get_token_type() == &FormulaTokenTypes::Operand
765            && token.get_token_sub_type() == &FormulaTokenSubTypes::Text
766        {
767            result = format!("{}{}", result, self::QUOTE_DOUBLE);
768            result = format!("{}{}", result, token.get_value());
769            result = format!("{}{}", result, self::QUOTE_DOUBLE);
770        } else if token.get_token_type() == &FormulaTokenTypes::OperatorInfix
771            && token.get_token_sub_type() == &FormulaTokenSubTypes::Intersection
772        {
773            result = format!("{}{}", result, self::WHITESPACE);
774        } else {
775            result = format!("{}{}", result, token.get_value());
776        }
777    }
778    result
779}
780
781pub fn adjustment_formula_coordinate(
782    token_list: &mut [FormulaToken],
783    offset_col_num: &i32,
784    offset_row_num: &i32,
785) {
786    for token in token_list.into_iter() {
787        if token.get_token_type() == &FormulaTokenTypes::Operand
788            && token.get_token_sub_type() == &FormulaTokenSubTypes::Range
789        {
790            let (sheet_name, range) = split_address(token.get_value());
791            let mut coordinate_list_new: Vec<String> = Vec::new();
792            let coordinate_list = get_split_range(range);
793            let mut has_error = false;
794            for coordinate in &coordinate_list {
795                let cell = index_from_coordinate(coordinate);
796                if cell.0.is_some() {
797                    let mut col_num = cell.0.unwrap();
798                    let mut row_num = cell.1.unwrap();
799                    let is_lock_col = cell.2.unwrap();
800                    let is_lock_row = cell.3.unwrap();
801                    if !is_lock_col {
802                        let calc_col_num = col_num as i32 + offset_col_num;
803                        if calc_col_num < 1 {
804                            has_error = true;
805                            break;
806                        } else {
807                            col_num = calc_col_num as u32;
808                        }
809                    }
810                    if !is_lock_row {
811                        let calc_row_num = row_num as i32 + offset_row_num;
812                        if calc_row_num < 1 {
813                            has_error = true;
814                            break;
815                        } else {
816                            row_num = calc_row_num as u32;
817                        }
818                    }
819                    let new_corrdinate = coordinate_from_index_with_lock(
820                        &col_num,
821                        &row_num,
822                        &is_lock_col,
823                        &is_lock_row,
824                    );
825                    coordinate_list_new.push(new_corrdinate);
826                } else {
827                    coordinate_list_new.push(coordinate.to_string());
828                }
829            }
830            if has_error {
831                token.set_value("#REF!");
832                token.set_token_sub_type(FormulaTokenSubTypes::Error);
833            } else {
834                let new_value = join_address(sheet_name, &get_join_range(&coordinate_list_new));
835                token.set_value(new_value);
836            }
837        }
838    }
839}
840
841pub fn adjustment_insert_formula_coordinate(
842    token_list: &mut [FormulaToken],
843    root_col_num: &u32,
844    offset_col_num: &u32,
845    root_row_num: &u32,
846    offset_row_num: &u32,
847    worksheet_name: &str,
848    self_worksheet_name: &str,
849    ignore_worksheet: bool,
850) -> String {
851    for token in token_list.into_iter() {
852        if token.get_token_type() == &FormulaTokenTypes::Operand
853            && token.get_token_sub_type() == &FormulaTokenSubTypes::Range
854        {
855            let (sheet_name, range) = split_address(token.get_value());
856            if ignore_worksheet
857                || (sheet_name == "" && worksheet_name == self_worksheet_name)
858                || (sheet_name == worksheet_name)
859            {
860                let mut coordinate_list_new: Vec<String> = Vec::new();
861                let coordinate_list = get_split_range(range);
862                for coordinate in &coordinate_list {
863                    let cell = index_from_coordinate(coordinate);
864                    if cell.0.is_some() && cell.1.is_some() {
865                        let mut col_num = cell.0.unwrap();
866                        let mut row_num = cell.1.unwrap();
867                        let is_lock_col = cell.2.unwrap_or(false);
868                        let is_lock_row = cell.3.unwrap_or(false);
869                        if !is_lock_col {
870                            col_num = adjustment_insert_coordinate(
871                                &col_num,
872                                root_col_num,
873                                offset_col_num,
874                            );
875                        }
876                        if !is_lock_row {
877                            row_num = adjustment_insert_coordinate(
878                                &row_num,
879                                root_row_num,
880                                offset_row_num,
881                            );
882                        }
883                        let new_corrdinate = coordinate_from_index_with_lock(
884                            &col_num,
885                            &row_num,
886                            &is_lock_col,
887                            &is_lock_row,
888                        );
889                        coordinate_list_new.push(new_corrdinate);
890                    } else {
891                        coordinate_list_new.push(coordinate.to_string());
892                    }
893                }
894                let new_value = join_address(sheet_name, &get_join_range(&coordinate_list_new));
895                token.set_value(new_value);
896            }
897        }
898    }
899    render(token_list.as_ref())
900}
901
902pub fn adjustment_remove_formula_coordinate(
903    token_list: &mut [FormulaToken],
904    root_col_num: &u32,
905    offset_col_num: &u32,
906    root_row_num: &u32,
907    offset_row_num: &u32,
908    worksheet_name: &str,
909    self_worksheet_name: &str,
910    ignore_worksheet: bool,
911) -> String {
912    for token in token_list.into_iter() {
913        if token.get_token_type() == &FormulaTokenTypes::Operand
914            && token.get_token_sub_type() == &FormulaTokenSubTypes::Range
915        {
916            let (sheet_name, range) = split_address(token.get_value());
917            if ignore_worksheet
918                || (sheet_name == "" && worksheet_name == self_worksheet_name)
919                || (sheet_name == worksheet_name)
920            {
921                let mut coordinate_list_new: Vec<String> = Vec::new();
922                let coordinate_list = get_split_range(range);
923                for coordinate in &coordinate_list {
924                    let cell = index_from_coordinate(coordinate);
925                    if cell.0.is_some() {
926                        let mut col_num = cell.0.unwrap();
927                        let mut row_num = cell.1.unwrap();
928                        let is_lock_col = cell.2.unwrap();
929                        let is_lock_row = cell.3.unwrap();
930                        if !is_lock_col {
931                            col_num = adjustment_remove_coordinate(
932                                &col_num,
933                                root_col_num,
934                                offset_col_num,
935                            );
936                        }
937                        if !is_lock_row {
938                            row_num = adjustment_remove_coordinate(
939                                &row_num,
940                                root_row_num,
941                                offset_row_num,
942                            );
943                        }
944                        let new_corrdinate = coordinate_from_index_with_lock(
945                            &col_num,
946                            &row_num,
947                            &is_lock_col,
948                            &is_lock_row,
949                        );
950                        coordinate_list_new.push(new_corrdinate);
951                    } else {
952                        coordinate_list_new.push(coordinate.to_string());
953                    }
954                }
955                let new_value = join_address(sheet_name, &get_join_range(&coordinate_list_new));
956                token.set_value(new_value);
957            }
958        }
959    }
960    render(token_list.as_ref())
961}
962
963#[cfg(test)]
964mod tests {
965    use super::*;
966    #[test]
967    fn test() {
968        let formula = "=10+9";
969        assert_eq!(
970            format!("={}", render(parse_to_tokens(formula).as_ref())),
971            formula
972        );
973
974        let formula = "=SUM(E7:I7)";
975        assert_eq!(
976            format!("={}", render(parse_to_tokens(formula).as_ref())),
977            formula
978        );
979
980        let formula = "=SUM(Sheet2!E7:I7)";
981        assert_eq!(
982            format!("={}", render(parse_to_tokens(formula).as_ref())),
983            formula
984        );
985
986        let formula = "=\"TEST\"";
987        assert_eq!(
988            format!("={}", render(parse_to_tokens(formula).as_ref())),
989            formula
990        );
991    }
992}
umya_spreadsheet/helper/formula.rs

umya_spreadsheet/helper/
formula.rs
