1import {
  2  assertFormulaEquals, assertFormulaEqualsArray, assertFormulaEqualsDependsOnReference,
  3  assertFormulaEqualsError,
  4  test
  5} from "./Utils/Asserts";
  6import {
  7  DIV_ZERO_ERROR,
  8  VALUE_ERROR,
  9  PARSE_ERROR, NA_ERROR, NUM_ERROR, NULL_ERROR, REF_ERROR
 10} from "../src/Errors";
 11import {Cell} from "../src/Cell";
 12
 13test("Parse but throw parse error", function(){
 14  assertFormulaEqualsError('= 10e', PARSE_ERROR);
 15  assertFormulaEqualsError('= SUM(', PARSE_ERROR);
 16});
 17
 18test("Parse & operator", function(){
 19  assertFormulaEquals('="hey"&" "&"there"', "hey there");
 20  assertFormulaEquals('=TEXT(12.3, "###.##")&"mm"', "12.3mm");
 21});
 22
 23test("Parse * operator", function(){
 24  assertFormulaEquals('= 10 * 10', 100);
 25  assertFormulaEquals('= 10 * 0', 0);
 26  assertFormulaEquals('= 1 * 1', 1);
 27});
 28
 29test("Parse / operator", function(){
 30  assertFormulaEquals('= 10 / 2', 5);
 31  assertFormulaEquals('= 10 / 1', 10);
 32  assertFormulaEquals('= 1 / 1', 1);
 33  assertFormulaEquals('= 0 / 1', 0);
 34  assertFormulaEquals('="1" / 1', 1);
 35  assertFormulaEquals('="500" / 1', 500);
 36  assertFormulaEqualsError('= 10 / 0', DIV_ZERO_ERROR);
 37  assertFormulaEqualsError('= 0 / 0', DIV_ZERO_ERROR);
 38  assertFormulaEquals('= P9 / 1', 0);
 39});
 40
 41test("Parse ^ operator", function(){
 42  assertFormulaEquals('= 10 ^ 10', 10000000000);
 43  assertFormulaEquals('= 10 ^ 0', 1);
 44  assertFormulaEquals('= 1 ^ 1', 1);
 45  assertFormulaEquals('= 2 ^ 10', 1024);
 46});
 47
 48test("Parse equality operators", function(){
 49  assertFormulaEquals('= 1 = 1', true);
 50  assertFormulaEquals('= 1 = 0', false);
 51  assertFormulaEquals('= 1 < 2', true);
 52  assertFormulaEquals('= 1 < 0', false);
 53  assertFormulaEquals('= 1 <= 1', true);
 54  assertFormulaEquals('= 1 <= 2', true);
 55  assertFormulaEquals('= 1 >= 1', true);
 56  assertFormulaEquals('= 2 >= 1', true);
 57  assertFormulaEquals('= 1 >= 0', true);
 58  assertFormulaEquals('= 1 >= 2', false);
 59  assertFormulaEquals('= 1 <> 1', false);
 60  assertFormulaEquals('= 1 <> 2', true);
 61});
 62
 63test("Parse operators, order of operations", function(){
 64  assertFormulaEquals('= 10 + -10', 0);
 65  assertFormulaEquals('= 10 + -10 = 0', true);
 66  assertFormulaEquals('= 10 + -10 = 0 & "str"', false);
 67  assertFormulaEquals('= -10%', -0.1);
 68  assertFormulaEquals('= 10 + 10%', 10.1);
 69  assertFormulaEquals('= -10 + 10%', -9.9);
 70  assertFormulaEquals('= -10 - +10%', -10.1);
 71  assertFormulaEquals('= 2^-10 + 10%', 0.1009765625);
 72  assertFormulaEquals('= 4 * 5 / 2', 10);
 73  assertFormulaEquals('= 4 / 5 * 4', 3.2);
 74  assertFormulaEquals('= 2^2*5', 20);
 75  assertFormulaEquals('= 2^(2*5)', 1024);
 76});
 77
 78test("Parse and throw error literal", function () {
 79  assertFormulaEqualsError('=#N/A', NA_ERROR);
 80  assertFormulaEqualsError('=#NUM!', NUM_ERROR);
 81  assertFormulaEqualsError('=#REF!', REF_ERROR);
 82  assertFormulaEqualsError('=#NULL!', NULL_ERROR);
 83  assertFormulaEqualsError('=#ERROR', PARSE_ERROR);
 84  assertFormulaEqualsError('=#DIV/0!', DIV_ZERO_ERROR);
 85  assertFormulaEqualsError('=#VALUE!', VALUE_ERROR);
 86  assertFormulaEquals('=ISERROR(#N/A)', true);
 87  assertFormulaEquals('=ISERROR(#NUM!)', true);
 88  assertFormulaEquals('=ISERROR(#REF!)', true);
 89  assertFormulaEquals('=ISERROR(#NULL!)', true);
 90  assertFormulaEquals('=ISERROR(#ERROR)', true);
 91  assertFormulaEquals('=ISERROR(#DIV/0!)', true);
 92  assertFormulaEquals('=ISERROR(#VALUE!)', true);
 93  assertFormulaEquals('=IFERROR(#N/A, 10)', 10);
 94  assertFormulaEquals('=IFERROR(#NUM!, 10)', 10);
 95  assertFormulaEquals('=IFERROR(#REF!, 10)', 10);
 96  assertFormulaEquals('=IFERROR(#NULL!, 10)', 10);
 97  assertFormulaEquals('=IFERROR(#ERROR, 10)', 10);
 98  assertFormulaEquals('=IFERROR(#DIV/0!, 10)', 10);
 99  assertFormulaEquals('=IFERROR(#VALUE!, 10)', 10);
100});
101
102test("Parse plain numbers", function() {
103  assertFormulaEquals('=10', 10);
104  // assertFormulaEquals('=.1', 0.1); // TODO: [ISSUE-010]
105  assertFormulaEquals('=0.1', 0.1);
106  assertFormulaEquals('=+1', 1);
107  assertFormulaEquals('=-1', -1);
108  assertFormulaEquals('=++1', 1);
109  assertFormulaEquals('=--1', 1);
110  assertFormulaEquals('=10e1', 100);
111  assertFormulaEquals('=0e1', 0);
112  // assertFormulaEquals('=0.e1', 0); // TODO: [ISSUE-010]
113  assertFormulaEquals('=-10e1', -100);
114  assertFormulaEquals('=+10e1', 100);
115  assertFormulaEquals('=++10e1', 100);
116  assertFormulaEquals('=--10e1', 100);
117});
118
119test("Parse complex numbers and math", function(){
120  assertFormulaEquals('= "10" + 10', 20);
121  assertFormulaEquals('= "10.111111" + 0', 10.111111);
122  assertFormulaEquals('= 10%', 0.1);
123  assertFormulaEquals('= 10% + 1', 1.1);
124  assertFormulaEquals('= "10e1" + 0', 100);
125  assertFormulaEquals('= 10e1', 100);
126  assertFormulaEquals('= 10e-1', 1);
127  assertFormulaEquals('= 10e+1', 100);
128  assertFormulaEquals('= 10E1', 100);
129  assertFormulaEquals('= 10E-1', 1);
130  assertFormulaEquals('= 10E+1', 100);
131  assertFormulaEquals('= "1,000,000"  + 0', 1000000);
132  assertFormulaEqualsError('= "10e" + 10', VALUE_ERROR);
133  assertFormulaEquals('= "+$10.00" + 0', 10);
134  assertFormulaEquals('= "-$10.00" + 0', -10);
135  assertFormulaEquals('= "$+10.00" + 0', 10);
136  assertFormulaEquals('= "$-10.00" + 0', -10);
137  assertFormulaEquals('= "10" + 10', 20);
138  assertFormulaEquals('= "10.111111" + 0', 10.111111);
139  assertFormulaEquals('= 10%', 0.1);
140  assertFormulaEquals('= 10% + 1', 1.1);
141  assertFormulaEquals('= "10e1" + 0', 100);
142  assertFormulaEquals('= 10e1', 100);
143  assertFormulaEquals('= 10e-1', 1);
144  assertFormulaEquals('= 10e+1', 100);
145  assertFormulaEquals('= 10E1', 100);
146  assertFormulaEquals('= 10E-1', 1);
147  assertFormulaEquals('= 10E+1', 100);
148  assertFormulaEquals('= "1,000,000"  + 0', 1000000);
149  assertFormulaEqualsError('= "10e" + 10', VALUE_ERROR);
150  assertFormulaEquals('= "+$10.00" + 0', 10);
151  assertFormulaEquals('= "-$10.00" + 0', -10);
152  assertFormulaEquals('= "$+10.00" + 0', 10);
153  assertFormulaEquals('= "$-10.00" + 0', -10);
154});
155
156test("Parse strings", function(){
157  assertFormulaEquals('="str"', "str");
158  assertFormulaEquals('="str"&"str"', "strstr");
159  assertFormulaEqualsError('="str"+"str"', VALUE_ERROR);
160  // assertFormulaEqualsError("='str'", PARSE_ERROR); // TODO: [ISSUE-011]
161
162});
163
164test("Parse boolean literals", function(){
165  assertFormulaEquals('=TRUE', true);
166  assertFormulaEquals('=true', true);
167  assertFormulaEquals('=FALSE', false);
168  assertFormulaEquals('=false', false);
169});
170
171test("Parse boolean logic", function(){
172  assertFormulaEquals('=(1=1)', true);
173  assertFormulaEquals('=(1=2)', false);
174  assertFormulaEquals('=(1=1)+2', 3);
175
176});
177
178
179test("Parse range literal", function(){
180  // assertFormulaEqualsArray('=[1, 2, 3]', [1, 2, 3]); // TODO: [ISSUE-007]
181  // assertFormulaEqualsArray('=[]', []); // TODO: [ISSUE-007]
182  // assertFormulaEqualsArray('=["str", "str"]', ["str", "str"]); // TODO: [ISSUE-007]
183  // assertFormulaEqualsArray('=["str", [1, 2, 3], [1]]', ["str", [1, 2, 3], [1]]); // TODO: [ISSUE-007]
184});
185
186test("Parse cell references", function(){
187  assertFormulaEqualsDependsOnReference("E1", "str", '=E1', Cell.BuildFrom("E1", "str"));
188  // assertFormulaEqualsArray('=E1:E2', [new Cell("E1"), new Cell("E2")]); // TODO: [ISSUE-014]
189});
190
191test("Parse range following comma", function(){
192  // assertFormulaEquals('=SERIESSUM(1, 0, 1, [4, 5, 6])', 15);
193  // assertFormulaEquals('=SERIESSUM([1], [0], [1], [4, 5, 6])', 15);
194});
195
196test("Combinations of expressions and operators", function(){
197  test("Combinations of expressions and operators, ", function(){
198    assertFormulaEquals('=10 + 10', 20);
199    assertFormulaEquals('=10 + TRUE', 11);
200    assertFormulaEquals('=10 + "10"', 20);
201    assertFormulaEquals('=10 + (2*2)', 14);
202    assertFormulaEquals('=10 + SUM(1, 2)', 13);
203    // assertFormulaEquals('=10 + [10]', 20); // TODO: [ISSUE-007]
204    // assertFormulaEqualsError('=10 + #DIV/0!', DIV_ZERO_ERROR); // TODO: [ISSUE-008]
205  });
206  test("Combinations of expressions and operators, plus operator", function(){
207    assertFormulaEquals('=10 + 10', 20);
208    assertFormulaEquals('=10 + TRUE', 11);
209    assertFormulaEquals('=10 + "10"', 20);
210    assertFormulaEquals('=10 + (2*2)', 14);
211    assertFormulaEquals('=10 + SUM(1, 2)', 13);
212    // assertFormulaEquals('=10 + [10]', 20); // TODO: [ISSUE-007]
213    // assertFormulaEqualsError('=10 + #DIV/0!', DIV_ZERO_ERROR); // TODO: [ISSUE-008]
214  });
215  test("Combinations of expressions and operators, minus operator", function(){
216    assertFormulaEquals('=10 - 10', 0);
217    assertFormulaEquals('=10 - TRUE', 9);
218    assertFormulaEquals('=10 - "10"', 0);
219    assertFormulaEquals('=10 - (2*2)', 6);
220    assertFormulaEquals('=10 - SUM(1, 2)', 7);
221    // assertFormulaEquals('=10 - [10]', 10); // TODO: [ISSUE-007]
222    // assertFormulaEqualsError('=10 - #DIV/0!', DIV_ZERO_ERROR); // TODO: [ISSUE-008]
223  });
224  test("Combinations of expressions and operators, division operator", function(){
225    assertFormulaEquals('=10 / 10', 1);
226    assertFormulaEquals('=10 / TRUE', 10);
227    assertFormulaEquals('=10 / "10"', 1);
228    assertFormulaEquals('=10 / (2*2)', 2.5);
229    assertFormulaEquals('=10 / SUM(1, 2)', 3.3333333333333335);
230    // assertFormulaEquals('=10 / [10]', 10); // TODO: [ISSUE-007]
231    // assertFormulaEqualsError('=10 / #DIV/0!', DIV_ZERO_ERROR); // TODO: [ISSUE-008]
232  });
233  test("Combinations of expressions and operators, multiplication operator", function(){
234    assertFormulaEquals('=10 * 10', 100);
235    assertFormulaEquals('=10 * TRUE', 10);
236    assertFormulaEquals('=10 * "10"', 100);
237    assertFormulaEquals('=10 * (2*2)', 40);
238    assertFormulaEquals('=10 * SUM(1, 2)', 30);
239    // assertFormulaEquals('=10 * [10]', 10); // TODO: [ISSUE-007]
240    // assertFormulaEqualsError('=10 * #DIV/0!', DIV_ZERO_ERROR); // TODO: [ISSUE-008]
241  });
242  test("Combinations of expressions and operators, power operator", function(){
243    assertFormulaEquals('=10 ^ 2', 100);
244    assertFormulaEquals('=10 ^ TRUE', 10);
245    assertFormulaEquals('=10 ^ "2"', 100);
246    assertFormulaEquals('=10 ^ (2*2)', 10000);
247    assertFormulaEquals('=10 ^ SUM(2, 2)', 10000);
248    // assertFormulaEquals('=10 ^ 1', 10); // TODO: [ISSUE-007]
249    // assertFormulaEqualsError('=10 & #DIV/0!', DIV_ZERO_ERROR); // TODO: [ISSUE-008]
250  });
251  test("Combinations of expressions and operators, concat operator", function(){
252    assertFormulaEquals('=10 & 10', "1010");
253    assertFormulaEquals('=10 & TRUE', "10TRUE");
254    assertFormulaEquals('=10 & "10"', "1010");
255    assertFormulaEquals('=10 & (2*2)', "104");
256    assertFormulaEquals('=10 & SUM(1, 2)', "103");
257    // assertFormulaEquals('=10 & [10]', 10); // TODO: [ISSUE-007]
258    // assertFormulaEqualsError('=10 & #DIV/0!', DIV_ZERO_ERROR); // TODO: [ISSUE-008]
259  });
260  test("Combinations of expressions and operators, LT operator", function(){
261    assertFormulaEquals('=10 < 10', false);
262    assertFormulaEquals('=10 < TRUE', false);
263    assertFormulaEquals('=10 < "10"', false);
264    assertFormulaEquals('=10 < (2*2)', false);
265    assertFormulaEquals('=10 < SUM(1, 2)', false);
266    // assertFormulaEquals('=10 < [10]', 10); // TODO: [ISSUE-007]
267    // assertFormulaEqualsError('=10 < #DIV/0!', DIV_ZERO_ERROR); // TODO: [ISSUE-008]
268  });
269  test("Combinations of expressions and operators, LTE operator", function(){
270    assertFormulaEquals('=10 <= 10', true);
271    assertFormulaEquals('=10 <= TRUE', false);
272    assertFormulaEquals('=10 <= "10"', true);
273    assertFormulaEquals('=10 <= (2*2)', false);
274    assertFormulaEquals('=10 <= SUM(1, 2)', false);
275    // assertFormulaEquals('=10 <= [10]', 10); // TODO: [ISSUE-007]
276    // assertFormulaEqualsError('=10 <= #DIV/0!', DIV_ZERO_ERROR); // TODO: [ISSUE-008]
277  });
278  test("Combinations of expressions and operators, GT operator", function(){
279    assertFormulaEquals('=10 > 10', false);
280    assertFormulaEquals('=10 > TRUE', true);
281    assertFormulaEquals('=10 > "10"', false);
282    assertFormulaEquals('=10 > (2*2)', true);
283    assertFormulaEquals('=10 > SUM(1, 2)', true);
284    // assertFormulaEquals('=10 > [10]', 10); // TODO: [ISSUE-007]
285    // assertFormulaEqualsError('=10 > #DIV/0!', DIV_ZERO_ERROR); // TODO: [ISSUE-008]
286  });
287  test("Combinations of expressions and operators, GTE operator", function(){
288    assertFormulaEquals('=10 >= 10', true);
289    assertFormulaEquals('=10 >= TRUE', true);
290    assertFormulaEquals('=10 >= "10"', true);
291    assertFormulaEquals('=10 >= (2*2)', true);
292    assertFormulaEquals('=10 >= SUM(1, 2)', true);
293    // assertFormulaEquals('=10 >= [10]', 10); // TODO: [ISSUE-007]
294    // assertFormulaEqualsError('=10 >= #DIV/0!', DIV_ZERO_ERROR); // TODO: [ISSUE-008]
295  });
296  test("Combinations of expressions and operators, equality operator", function(){
297    assertFormulaEquals('=10 = 10', true);
298    assertFormulaEquals('=10 = TRUE', false);
299    assertFormulaEquals('=10 = "10"', false);
300    assertFormulaEquals('=10 = (2*2)', false);
301    assertFormulaEquals('=10 = SUM(1, 2)', false);
302    // assertFormulaEquals('=10 >= [10]', 10); // TODO: [ISSUE-007]
303    // assertFormulaEqualsError('=10 >= #DIV/0!', DIV_ZERO_ERROR); // TODO: [ISSUE-008]
304  });
305  test("Combinations of expressions and operators, not-equal operator", function(){
306    assertFormulaEquals('=10 <> 10', false);
307    assertFormulaEquals('=10 <> TRUE', true);
308    assertFormulaEquals('=10 <> "10"', true);
309    assertFormulaEquals('=10 <> (2*2)', true);
310    assertFormulaEquals('=10 <> SUM(1, 2)', true);
311    // assertFormulaEquals('=10 <> [10]', 10); // TODO: [ISSUE-007]
312    // assertFormulaEqualsError('=10 <> #DIV/0!', DIV_ZERO_ERROR); // TODO: [ISSUE-008]
313  });
314  test("Combinations of expressions and operators, unary prefix operators", function(){
315    assertFormulaEquals('=10 <> -10', true);
316    assertFormulaEquals('=-10', -10);
317    assertFormulaEquals('=-(-10)', 10);
318    assertFormulaEquals('=-(-(-1*2))', -2);
319    assertFormulaEquals('=-TRUE', -1);
320    assertFormulaEquals('=-"1"', -1);
321    assertFormulaEquals('=-+"1"', -1);
322    assertFormulaEquals('=-+-"1"', 1);
323    assertFormulaEquals('=-(1=1)', -1);
324  });
325});