1 /* ====================================================================
2 Licensed to the Apache Software Foundation (ASF) under one or more
3 contributor license agreements. See the NOTICE file distributed with
4 this work for additional information regarding copyright ownership.
5 The ASF licenses this file to You under the Apache License, Version 2.0
6 (the "License"); you may not use this file except in compliance with
7 the License. You may obtain a copy of the License at
9 http://www.apache.org/licenses/LICENSE-2.0
11 Unless required by applicable law or agreed to in writing, software
12 distributed under the License is distributed on an "AS IS" BASIS,
13 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14 See the License for the specific language governing permissions and
15 limitations under the License.
16 ==================================================================== */
18 package org.apache.poi.hssf.model;
20 import junit.framework.AssertionFailedError;
21 import junit.framework.TestCase;
23 import org.apache.poi.hssf.model.FormulaParser.FormulaParseException;
24 import org.apache.poi.hssf.record.formula.AbstractFunctionPtg;
25 import org.apache.poi.hssf.record.formula.AddPtg;
26 import org.apache.poi.hssf.record.formula.AreaPtg;
27 import org.apache.poi.hssf.record.formula.AttrPtg;
28 import org.apache.poi.hssf.record.formula.BoolPtg;
29 import org.apache.poi.hssf.record.formula.ConcatPtg;
30 import org.apache.poi.hssf.record.formula.DividePtg;
31 import org.apache.poi.hssf.record.formula.EqualPtg;
32 import org.apache.poi.hssf.record.formula.ErrPtg;
33 import org.apache.poi.hssf.record.formula.FuncPtg;
34 import org.apache.poi.hssf.record.formula.FuncVarPtg;
35 import org.apache.poi.hssf.record.formula.IntPtg;
36 import org.apache.poi.hssf.record.formula.LessEqualPtg;
37 import org.apache.poi.hssf.record.formula.LessThanPtg;
38 import org.apache.poi.hssf.record.formula.MissingArgPtg;
39 import org.apache.poi.hssf.record.formula.MultiplyPtg;
40 import org.apache.poi.hssf.record.formula.NamePtg;
41 import org.apache.poi.hssf.record.formula.NotEqualPtg;
42 import org.apache.poi.hssf.record.formula.NumberPtg;
43 import org.apache.poi.hssf.record.formula.PercentPtg;
44 import org.apache.poi.hssf.record.formula.PowerPtg;
45 import org.apache.poi.hssf.record.formula.Ptg;
46 import org.apache.poi.hssf.record.formula.ReferencePtg;
47 import org.apache.poi.hssf.record.formula.StringPtg;
48 import org.apache.poi.hssf.record.formula.SubtractPtg;
49 import org.apache.poi.hssf.record.formula.UnaryMinusPtg;
50 import org.apache.poi.hssf.record.formula.UnaryPlusPtg;
51 import org.apache.poi.hssf.usermodel.HSSFCell;
52 import org.apache.poi.hssf.usermodel.HSSFRow;
53 import org.apache.poi.hssf.usermodel.HSSFSheet;
54 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
57 * Test the low level formula parser functionality. High level tests are to
58 * be done via usermodel/HSSFCell.setFormulaValue() .
59 * Some tests are also done in scratchpad, if they need
60 * HSSFFormulaEvaluator, which is there
62 public final class TestFormulaParser extends TestCase {
65 * @return parsed token array already confirmed not <code>null</code>
67 private static Ptg[] parseFormula(String s) {
68 // TODO - replace multiple copies of this code with calls to this method
69 FormulaParser fp = new FormulaParser(s, null);
71 Ptg[] result = fp.getRPNPtg();
72 assertNotNull("Ptg array should not be null", result);
76 public void testSimpleFormula() {
77 FormulaParser fp = new FormulaParser("2+2",null);
79 Ptg[] ptgs = fp.getRPNPtg();
80 assertTrue("three tokens expected, got "+ptgs.length,ptgs.length == 3);
82 public void testFormulaWithSpace1() {
83 FormulaParser fp = new FormulaParser(" 2 + 2 ",null);
85 Ptg[] ptgs = fp.getRPNPtg();
86 assertTrue("three tokens expected, got "+ptgs.length,ptgs.length == 3);
87 assertTrue("",(ptgs[0] instanceof IntPtg));
88 assertTrue("",(ptgs[1] instanceof IntPtg));
89 assertTrue("",(ptgs[2] instanceof AddPtg));
92 public void testFormulaWithSpace2() {
95 fp = new FormulaParser("2+ sum( 3 , 4) ",null);
97 ptgs = fp.getRPNPtg();
98 assertTrue("five tokens expected, got "+ptgs.length,ptgs.length == 5);
101 public void testFormulaWithSpaceNRef() {
104 fp = new FormulaParser("sum( A2:A3 )",null);
106 ptgs = fp.getRPNPtg();
107 assertTrue("two tokens expected, got "+ptgs.length,ptgs.length == 2);
110 public void testFormulaWithString() {
113 fp = new FormulaParser("\"hello\" & \"world\" ",null);
115 ptgs = fp.getRPNPtg();
116 assertTrue("three token expected, got " + ptgs.length, ptgs.length == 3);
119 public void testTRUE() throws Exception {
120 FormulaParser fp = new FormulaParser("TRUE", null);
122 Ptg[] asts = fp.getRPNPtg();
123 assertEquals(1, asts.length);
124 BoolPtg flag = (BoolPtg) asts[0];
125 assertEquals(true, flag.getValue());
128 public void testYN() throws Exception {
129 final String yn = "IF(TRUE,\"Y\",\"N\")";
130 FormulaParser fp = new FormulaParser(yn, null);
132 Ptg[] asts = fp.getRPNPtg();
133 assertEquals(7, asts.length);
135 BoolPtg flag = (BoolPtg) asts[0];
136 AttrPtg funif = (AttrPtg) asts[1];
137 StringPtg y = (StringPtg) asts[2];
138 AttrPtg goto1 = (AttrPtg) asts[3];
139 StringPtg n = (StringPtg) asts[4];
142 assertEquals(true, flag.getValue());
143 assertEquals("Y", y.getValue());
144 assertEquals("N", n.getValue());
145 assertEquals("IF", funif.toFormulaString((HSSFWorkbook) null));
146 assertTrue("Goto ptg exists", goto1.isGoto());
149 public void testSimpleIf() throws Exception {
150 final String simpleif = "IF(1=1,0,1)";
151 FormulaParser fp = new FormulaParser(simpleif, null);
153 Ptg[] asts = fp.getRPNPtg();
154 assertEquals(9, asts.length);
156 IntPtg op1 = (IntPtg) asts[0];
157 IntPtg op2 = (IntPtg) asts[1];
158 EqualPtg eq = (EqualPtg) asts[2];
159 AttrPtg ifPtg = (AttrPtg) asts[3];
160 IntPtg res1 = (IntPtg) asts[4];
162 AttrPtg ptgGoto= (AttrPtg) asts[5];
163 assertEquals("Goto 1 Length", (short)10, ptgGoto.getData());
165 IntPtg res2 = (IntPtg) asts[6];
166 AttrPtg ptgGoto2 = (AttrPtg) asts[7];
167 assertEquals("Goto 2 Length", (short)3, ptgGoto2.getData());
169 assertEquals("If FALSE offset", (short)7, ifPtg.getData());
171 FuncVarPtg funcPtg = (FuncVarPtg)asts[8];
175 * Make sure the ptgs are generated properly with two functions embedded
178 public void testNestedFunctionIf() {
179 String function = "IF(A1=B1,AVERAGE(A1:B1),AVERAGE(A2:B2))";
181 FormulaParser fp = new FormulaParser(function, null);
183 Ptg[] asts = fp.getRPNPtg();
184 assertEquals("11 Ptgs expected", 11, asts.length);
186 assertTrue("IF Attr set correctly", (asts[3] instanceof AttrPtg));
187 AttrPtg ifFunc = (AttrPtg)asts[3];
188 assertTrue("It is not an if", ifFunc.isOptimizedIf());
190 assertTrue("Average Function set correctly", (asts[5] instanceof FuncVarPtg));
193 public void testIfSingleCondition(){
194 String function = "IF(1=1,10)";
196 FormulaParser fp = new FormulaParser(function, null);
198 Ptg[] asts = fp.getRPNPtg();
199 assertEquals("7 Ptgs expected", 7, asts.length);
201 assertTrue("IF Attr set correctly", (asts[3] instanceof AttrPtg));
202 AttrPtg ifFunc = (AttrPtg)asts[3];
203 assertTrue("It is not an if", ifFunc.isOptimizedIf());
205 assertTrue("Single Value is not an IntPtg", (asts[4] instanceof IntPtg));
206 IntPtg intPtg = (IntPtg)asts[4];
207 assertEquals("Result", (short)10, intPtg.getValue());
209 assertTrue("Ptg is not a Variable Function", (asts[6] instanceof FuncVarPtg));
210 FuncVarPtg funcPtg = (FuncVarPtg)asts[6];
211 assertEquals("Arguments", 2, funcPtg.getNumberOfOperands());
214 public void testSumIf() {
215 String function ="SUMIF(A1:A5,\">4000\",B1:B5)";
216 FormulaParser fp = new FormulaParser(function, null);
218 Ptg[] asts = fp.getRPNPtg();
219 assertEquals("4 Ptgs expected", 4, asts.length);
223 * Bug Reported by xt-jens.riis@nokia.com (Jens Riis)
224 * Refers to Bug <a href="http://issues.apache.org/bugzilla/show_bug.cgi?id=17582">#17582</a>
227 public void testNonAlphaFormula(){
228 String currencyCell = "F3";
229 String function="\"TOTAL[\"&"+currencyCell+"&\"]\"";
231 Ptg[] asts = parseFormula(function);
232 assertEquals("5 ptgs expected", 5, asts.length);
233 assertTrue ("Ptg[0] is a string", (asts[0] instanceof StringPtg));
234 StringPtg firstString = (StringPtg)asts[0];
236 assertEquals("TOTAL[", firstString.getValue());
237 //the PTG order isn't 100% correct but it still works - dmui
240 public void testSimpleLogical() {
241 Ptg[] ptgs = parseFormula("IF(A1<A2,B1,B2)");
242 assertEquals("Ptg array length", 9, ptgs.length);
243 assertEquals("3rd Ptg is less than", LessThanPtg.class, ptgs[2].getClass());
246 public void testParenIf() {
247 Ptg[] ptgs = parseFormula("IF((A1+A2)<=3,\"yes\",\"no\")");
248 assertEquals("Ptg array length", 12, ptgs.length);
249 assertEquals("6th Ptg is less than equal",LessEqualPtg.class,ptgs[5].getClass());
250 assertEquals("11th Ptg is not a goto (Attr) ptg",AttrPtg.class,ptgs[10].getClass());
253 public void testEmbeddedIf() {
254 Ptg[] ptgs = parseFormula("IF(3>=1,\"*\",IF(4<>1,\"first\",\"second\"))");
255 assertEquals("Ptg array length", 17, ptgs.length);
257 assertEquals("6th Ptg is not a goto (Attr) ptg",AttrPtg.class,ptgs[5].getClass());
258 assertEquals("9th Ptg is not a not equal ptg",NotEqualPtg.class,ptgs[8].getClass());
259 assertEquals("15th Ptg is not the inner IF variable function ptg",FuncVarPtg.class,ptgs[14].getClass());
262 public void testMacroFunction() {
263 HSSFWorkbook w = new HSSFWorkbook();
264 FormulaParser fp = new FormulaParser("FOO()", w);
266 Ptg[] ptg = fp.getRPNPtg();
268 // the name gets encoded as the first arg
269 NamePtg tname = (NamePtg) ptg[0];
270 assertEquals("FOO", tname.toFormulaString(w));
272 AbstractFunctionPtg tfunc = (AbstractFunctionPtg) ptg[1];
273 assertTrue(tfunc.isExternalFunction());
276 public void testEmbeddedSlash() {
277 FormulaParser fp = new FormulaParser("HYPERLINK(\"http://www.jakarta.org\",\"Jakarta\")",null);
279 Ptg[] ptg = fp.getRPNPtg();
280 assertTrue("first ptg is string",ptg[0] instanceof StringPtg);
281 assertTrue("second ptg is string",ptg[1] instanceof StringPtg);
284 public void testConcatenate() {
285 FormulaParser fp = new FormulaParser("CONCATENATE(\"first\",\"second\")", null);
287 Ptg[] ptg = fp.getRPNPtg();
288 assertTrue("first ptg is string", ptg[0] instanceof StringPtg);
289 assertTrue("second ptg is string", ptg[1] instanceof StringPtg);
292 public void testWorksheetReferences()
294 HSSFWorkbook wb = new HSSFWorkbook();
296 wb.createSheet("NoQuotesNeeded");
297 wb.createSheet("Quotes Needed Here &#$@");
299 HSSFSheet sheet = wb.createSheet("Test");
300 HSSFRow row = sheet.createRow(0);
303 cell = row.createCell((short)0);
304 cell.setCellFormula("NoQuotesNeeded!A1");
306 cell = row.createCell((short)1);
307 cell.setCellFormula("'Quotes Needed Here &#$@'!A1");
310 public void testUnaryMinus()
312 FormulaParser fp = new FormulaParser("-A1", null);
314 Ptg[] ptg = fp.getRPNPtg();
315 assertTrue("got 2 ptgs", ptg.length == 2);
316 assertTrue("first ptg is reference",ptg[0] instanceof ReferencePtg);
317 assertTrue("second ptg is Minus",ptg[1] instanceof UnaryMinusPtg);
320 public void testUnaryPlus()
322 FormulaParser fp = new FormulaParser("+A1", null);
324 Ptg[] ptg = fp.getRPNPtg();
325 assertTrue("got 2 ptgs", ptg.length == 2);
326 assertTrue("first ptg is reference",ptg[0] instanceof ReferencePtg);
327 assertTrue("second ptg is Plus",ptg[1] instanceof UnaryPlusPtg);
330 public void testLeadingSpaceInString()
332 String value = " hi ";
333 FormulaParser fp = new FormulaParser("\"" + value + "\"", null);
335 Ptg[] ptg = fp.getRPNPtg();
337 assertTrue("got 1 ptg", ptg.length == 1);
338 assertTrue("ptg0 is a StringPtg", ptg[0] instanceof StringPtg);
339 assertTrue("ptg0 contains exact value", ((StringPtg)ptg[0]).getValue().equals(value));
342 public void testLookupAndMatchFunctionArgs()
344 FormulaParser fp = new FormulaParser("lookup(A1, A3:A52, B3:B52)", null);
346 Ptg[] ptg = fp.getRPNPtg();
348 assertTrue("got 4 ptg", ptg.length == 4);
349 assertTrue("ptg0 has Value class", ptg[0].getPtgClass() == Ptg.CLASS_VALUE);
351 fp = new FormulaParser("match(A1, A3:A52)", null);
353 ptg = fp.getRPNPtg();
355 assertTrue("got 3 ptg", ptg.length == 3);
356 assertTrue("ptg0 has Value class", ptg[0].getPtgClass() == Ptg.CLASS_VALUE);
360 public void testLargeInt() {
361 FormulaParser fp = new FormulaParser("40", null);
363 Ptg[] ptg=fp.getRPNPtg();
364 assertTrue("ptg is Int, is "+ptg[0].getClass(),ptg[0] instanceof IntPtg);
366 fp = new FormulaParser("40000", null);
369 assertTrue("ptg should be IntPtg, is "+ptg[0].getClass(), ptg[0] instanceof IntPtg);
372 /** bug 33160, testcase by Amol Deshmukh*/
373 public void testSimpleLongFormula() {
374 FormulaParser fp = new FormulaParser("40000/2", null);
376 Ptg[] ptgs = fp.getRPNPtg();
377 assertTrue("three tokens expected, got " + ptgs.length, ptgs.length == 3);
378 assertTrue("IntPtg", (ptgs[0] instanceof IntPtg));
379 assertTrue("IntPtg", (ptgs[1] instanceof IntPtg));
380 assertTrue("DividePtg", (ptgs[2] instanceof DividePtg));
383 /** bug 35027, underscore in sheet name */
384 public void testUnderscore() {
385 HSSFWorkbook wb = new HSSFWorkbook();
387 wb.createSheet("Cash_Flow");
389 HSSFSheet sheet = wb.createSheet("Test");
390 HSSFRow row = sheet.createRow(0);
393 cell = row.createCell((short)0);
394 cell.setCellFormula("Cash_Flow!A1");
398 // bug 38396 : Formula with exponential numbers not parsed correctly.
399 public void testExponentialParsing() {
400 FormulaParser fp = new FormulaParser("1.3E21/2", null);
402 Ptg[] ptgs = fp.getRPNPtg();
403 assertTrue("three tokens expected, got " + ptgs.length, ptgs.length == 3);
404 assertTrue("NumberPtg", (ptgs[0] instanceof NumberPtg));
405 assertTrue("IntPtg", (ptgs[1] instanceof IntPtg));
406 assertTrue("DividePtg", (ptgs[2] instanceof DividePtg));
408 fp = new FormulaParser("1322E21/2", null);
410 ptgs = fp.getRPNPtg();
411 assertTrue("three tokens expected, got " + ptgs.length, ptgs.length == 3);
412 assertTrue("NumberPtg", (ptgs[0] instanceof NumberPtg));
413 assertTrue("IntPtg", (ptgs[1] instanceof IntPtg));
414 assertTrue("DividePtg", (ptgs[2] instanceof DividePtg));
416 fp = new FormulaParser("1.3E1/2", null);
418 ptgs = fp.getRPNPtg();
419 assertTrue("three tokens expected, got " + ptgs.length, ptgs.length == 3);
420 assertTrue("NumberPtg", (ptgs[0] instanceof NumberPtg));
421 assertTrue("IntPtg", (ptgs[1] instanceof IntPtg));
422 assertTrue("DividePtg", (ptgs[2] instanceof DividePtg));
425 public void testExponentialInSheet() throws Exception {
426 HSSFWorkbook wb = new HSSFWorkbook();
428 wb.createSheet("Cash_Flow");
430 HSSFSheet sheet = wb.createSheet("Test");
431 HSSFRow row = sheet.createRow(0);
432 HSSFCell cell = row.createCell((short)0);
433 String formula = null;
435 cell.setCellFormula("1.3E21/3");
436 formula = cell.getCellFormula();
437 assertEquals("Exponential formula string", "1.3E21/3", formula);
439 cell.setCellFormula("-1.3E21/3");
440 formula = cell.getCellFormula();
441 assertEquals("Exponential formula string", "-1.3E21/3", formula);
443 cell.setCellFormula("1322E21/3");
444 formula = cell.getCellFormula();
445 assertEquals("Exponential formula string", "1.322E24/3", formula);
447 cell.setCellFormula("-1322E21/3");
448 formula = cell.getCellFormula();
449 assertEquals("Exponential formula string", "-1.322E24/3", formula);
451 cell.setCellFormula("1.3E1/3");
452 formula = cell.getCellFormula();
453 assertEquals("Exponential formula string", "13.0/3", formula);
455 cell.setCellFormula("-1.3E1/3");
456 formula = cell.getCellFormula();
457 assertEquals("Exponential formula string", "-13.0/3", formula);
459 cell.setCellFormula("1.3E-4/3");
460 formula = cell.getCellFormula();
461 assertEquals("Exponential formula string", "1.3E-4/3", formula);
463 cell.setCellFormula("-1.3E-4/3");
464 formula = cell.getCellFormula();
465 assertEquals("Exponential formula string", "-1.3E-4/3", formula);
467 cell.setCellFormula("13E-15/3");
468 formula = cell.getCellFormula();
469 assertEquals("Exponential formula string", "1.3E-14/3", formula);
471 cell.setCellFormula("-13E-15/3");
472 formula = cell.getCellFormula();
473 assertEquals("Exponential formula string", "-1.3E-14/3", formula);
475 cell.setCellFormula("1.3E3/3");
476 formula = cell.getCellFormula();
477 assertEquals("Exponential formula string", "1300.0/3", formula);
479 cell.setCellFormula("-1.3E3/3");
480 formula = cell.getCellFormula();
481 assertEquals("Exponential formula string", "-1300.0/3", formula);
483 cell.setCellFormula("1300000000000000/3");
484 formula = cell.getCellFormula();
485 assertEquals("Exponential formula string", "1.3E15/3", formula);
487 cell.setCellFormula("-1300000000000000/3");
488 formula = cell.getCellFormula();
489 assertEquals("Exponential formula string", "-1.3E15/3", formula);
491 cell.setCellFormula("-10E-1/3.1E2*4E3/3E4");
492 formula = cell.getCellFormula();
493 assertEquals("Exponential formula string", "-1.0/310.0*4000.0/30000.0", formula);
496 public static void main(String [] args) {
497 System.out.println("Testing org.apache.poi.hssf.record.formula.FormulaParser");
498 junit.textui.TestRunner.run(TestFormulaParser.class);
501 public void testNumbers() {
502 HSSFWorkbook wb = new HSSFWorkbook();
504 wb.createSheet("Cash_Flow");
506 HSSFSheet sheet = wb.createSheet("Test");
507 HSSFRow row = sheet.createRow(0);
508 HSSFCell cell = row.createCell((short)0);
509 String formula = null;
511 // starts from decimal point
513 cell.setCellFormula(".1");
514 formula = cell.getCellFormula();
515 assertEquals("0.1", formula);
517 cell.setCellFormula("+.1");
518 formula = cell.getCellFormula();
519 assertEquals("+0.1", formula);
521 cell.setCellFormula("-.1");
522 formula = cell.getCellFormula();
523 assertEquals("-0.1", formula);
527 cell.setCellFormula("10E1");
528 formula = cell.getCellFormula();
529 assertEquals("100.0", formula);
531 cell.setCellFormula("10E+1");
532 formula = cell.getCellFormula();
533 assertEquals("100.0", formula);
535 cell.setCellFormula("10E-1");
536 formula = cell.getCellFormula();
537 assertEquals("1.0", formula);
540 public void testRanges() {
541 HSSFWorkbook wb = new HSSFWorkbook();
543 wb.createSheet("Cash_Flow");
545 HSSFSheet sheet = wb.createSheet("Test");
546 HSSFRow row = sheet.createRow(0);
547 HSSFCell cell = row.createCell((short)0);
548 String formula = null;
550 cell.setCellFormula("A1.A2");
551 formula = cell.getCellFormula();
552 assertEquals("A1:A2", formula);
554 cell.setCellFormula("A1..A2");
555 formula = cell.getCellFormula();
556 assertEquals("A1:A2", formula);
558 cell.setCellFormula("A1...A2");
559 formula = cell.getCellFormula();
560 assertEquals("A1:A2", formula);
564 * Test for bug observable at svn revision 618865 (5-Feb-2008)<br/>
565 * a formula consisting of a single no-arg function got rendered without the function braces
567 public void testToFormulaStringZeroArgFunction() {
568 HSSFWorkbook book = new HSSFWorkbook();
573 assertEquals("NA()", FormulaParser.toFormulaString(book, ptgs));
576 public void testPercent() {
578 ptgs = parseFormula("5%");
579 assertEquals(2, ptgs.length);
580 assertEquals(ptgs[0].getClass(), IntPtg.class);
581 assertEquals(ptgs[1].getClass(), PercentPtg.class);
584 ptgs = parseFormula(" 250 % ");
585 assertEquals(2, ptgs.length);
586 assertEquals(ptgs[0].getClass(), IntPtg.class);
587 assertEquals(ptgs[1].getClass(), PercentPtg.class);
591 ptgs = parseFormula("12345.678%%");
592 assertEquals(3, ptgs.length);
593 assertEquals(ptgs[0].getClass(), NumberPtg.class);
594 assertEquals(ptgs[1].getClass(), PercentPtg.class);
595 assertEquals(ptgs[2].getClass(), PercentPtg.class);
597 // percent of a bracketed expression
598 ptgs = parseFormula("(A1+35)%*B1%");
599 assertEquals(8, ptgs.length);
600 assertEquals(ptgs[4].getClass(), PercentPtg.class);
601 assertEquals(ptgs[6].getClass(), PercentPtg.class);
603 // percent of a text quantity
604 ptgs = parseFormula("\"8.75\"%");
605 assertEquals(2, ptgs.length);
606 assertEquals(ptgs[0].getClass(), StringPtg.class);
607 assertEquals(ptgs[1].getClass(), PercentPtg.class);
609 // percent to the power of
610 ptgs = parseFormula("50%^3");
611 assertEquals(4, ptgs.length);
612 assertEquals(ptgs[0].getClass(), IntPtg.class);
613 assertEquals(ptgs[1].getClass(), PercentPtg.class);
614 assertEquals(ptgs[2].getClass(), IntPtg.class);
615 assertEquals(ptgs[3].getClass(), PowerPtg.class);
618 // things that parse OK but would *evaluate* to an error
620 ptgs = parseFormula("\"abc\"%");
621 assertEquals(2, ptgs.length);
622 assertEquals(ptgs[0].getClass(), StringPtg.class);
623 assertEquals(ptgs[1].getClass(), PercentPtg.class);
625 ptgs = parseFormula("#N/A%");
626 assertEquals(2, ptgs.length);
627 assertEquals(ptgs[0].getClass(), ErrPtg.class);
628 assertEquals(ptgs[1].getClass(), PercentPtg.class);
632 * Tests combinations of various operators in the absence of brackets
634 public void testPrecedenceAndAssociativity() {
638 // TRUE=TRUE=2=2 evaluates to FALSE
639 expClss = new Class[] { BoolPtg.class, BoolPtg.class, EqualPtg.class,
640 IntPtg.class, EqualPtg.class, IntPtg.class, EqualPtg.class, };
641 confirmTokenClasses("TRUE=TRUE=2=2", expClss);
644 // 2^3^2 evaluates to 64 not 512
645 expClss = new Class[] { IntPtg.class, IntPtg.class, PowerPtg.class,
646 IntPtg.class, PowerPtg.class, };
647 confirmTokenClasses("2^3^2", expClss);
649 // "abc" & 2 + 3 & "def" evaluates to "abc5def"
650 expClss = new Class[] { StringPtg.class, IntPtg.class, IntPtg.class,
651 AddPtg.class, ConcatPtg.class, StringPtg.class, ConcatPtg.class, };
652 confirmTokenClasses("\"abc\"&2+3&\"def\"", expClss);
656 expClss = new Class[] { IntPtg.class, IntPtg.class, DividePtg.class,
657 IntPtg.class, IntPtg.class, MultiplyPtg.class, SubtractPtg.class, };
658 confirmTokenClasses("1/2-3*4", expClss);
661 expClss = new Class[] { IntPtg.class, IntPtg.class, IntPtg.class, PowerPtg.class, MultiplyPtg.class, };
662 // NOT: (2 *2) ^ 2 -> int int multiply int power
663 confirmTokenClasses("2*2^2", expClss);
665 // 2^200% -> 2 not 1.6E58
666 expClss = new Class[] { IntPtg.class, IntPtg.class, PercentPtg.class, PowerPtg.class, };
667 confirmTokenClasses("2^200%", expClss);
670 private static void confirmTokenClasses(String formula, Class[] expectedClasses) {
671 Ptg[] ptgs = parseFormula(formula);
672 assertEquals(expectedClasses.length, ptgs.length);
673 for (int i = 0; i < expectedClasses.length; i++) {
674 if(expectedClasses[i] != ptgs[i].getClass()) {
675 fail("difference at token[" + i + "]: expected ("
676 + expectedClasses[i].getName() + ") but got ("
677 + ptgs[i].getClass().getName() + ")");
682 public void testPower() {
683 confirmTokenClasses("2^5", new Class[] { IntPtg.class, IntPtg.class, PowerPtg.class, });
686 private static Ptg parseSingleToken(String formula, Class ptgClass) {
687 Ptg[] ptgs = parseFormula(formula);
688 assertEquals(1, ptgs.length);
689 Ptg result = ptgs[0];
690 assertEquals(ptgClass, result.getClass());
694 public void testParseNumber() {
698 ip = (IntPtg) parseSingleToken("40", IntPtg.class);
699 assertEquals(40, ip.getValue());
700 ip = (IntPtg) parseSingleToken("40000", IntPtg.class);
701 assertEquals(40000, ip.getValue());
703 // check the upper edge of the IntPtg range:
704 ip = (IntPtg) parseSingleToken("65535", IntPtg.class);
705 assertEquals(65535, ip.getValue());
706 NumberPtg np = (NumberPtg) parseSingleToken("65536", NumberPtg.class);
707 assertEquals(65536, np.getValue(), 0);
709 np = (NumberPtg) parseSingleToken("65534.6", NumberPtg.class);
710 assertEquals(65534.6, np.getValue(), 0);
713 public void testMissingArgs() {
717 expClss = new Class[] { ReferencePtg.class, MissingArgPtg.class, ReferencePtg.class,
719 confirmTokenClasses("if(A1, ,C1)", expClss);
721 expClss = new Class[] { MissingArgPtg.class, AreaPtg.class, MissingArgPtg.class,
723 confirmTokenClasses("counta( , A1:B2, )", expClss);
726 public void testParseErrorLiterals() {
728 confirmParseErrorLiteral(ErrPtg.NULL_INTERSECTION, "#NULL!");
729 confirmParseErrorLiteral(ErrPtg.DIV_ZERO, "#DIV/0!");
730 confirmParseErrorLiteral(ErrPtg.VALUE_INVALID, "#VALUE!");
731 confirmParseErrorLiteral(ErrPtg.REF_INVALID, "#REF!");
732 confirmParseErrorLiteral(ErrPtg.NAME_INVALID, "#NAME?");
733 confirmParseErrorLiteral(ErrPtg.NUM_ERROR, "#NUM!");
734 confirmParseErrorLiteral(ErrPtg.N_A, "#N/A");
737 private static void confirmParseErrorLiteral(ErrPtg expectedToken, String formula) {
738 assertEquals(expectedToken, parseSingleToken(formula, ErrPtg.class));
742 * To aid readability the parameters have been encoded with single quotes instead of double
743 * quotes. This method converts single quotes to double quotes before performing the parse
746 private static void confirmStringParse(String singleQuotedValue) {
747 // formula: internal quotes become double double, surround with double quotes
748 String formula = '"' + singleQuotedValue.replaceAll("'", "\"\"") + '"';
749 String expectedValue = singleQuotedValue.replace('\'', '"');
751 StringPtg sp = (StringPtg) parseSingleToken(formula, StringPtg.class);
752 assertEquals(expectedValue, sp.getValue());
754 public void testParseStringLiterals_bug28754() {
758 sp = (StringPtg) parseSingleToken("\"test\"\"ing\"", StringPtg.class);
759 } catch (RuntimeException e) {
760 if(e.getMessage().startsWith("Cannot Parse")) {
761 throw new AssertionFailedError("Identified bug 28754a");
765 assertEquals("test\"ing", sp.getValue());
767 HSSFWorkbook wb = new HSSFWorkbook();
768 HSSFSheet sheet = wb.createSheet();
769 wb.setSheetName(0, "Sheet1");
771 HSSFRow row = sheet.createRow(0);
772 HSSFCell cell = row.createCell((short)0);
773 cell.setCellFormula("right(\"test\"\"ing\", 3)");
774 String actualCellFormula = cell.getCellFormula();
775 if("RIGHT(\"test\"ing\",3)".equals(actualCellFormula)) {
776 throw new AssertionFailedError("Identified bug 28754b");
778 assertEquals("RIGHT(\"test\"\"ing\",3)", actualCellFormula);
781 public void testParseStringLiterals() {
782 confirmStringParse("goto considered harmful");
784 confirmStringParse("goto 'considered' harmful");
786 confirmStringParse("");
787 confirmStringParse("'");
788 confirmStringParse("''");
789 confirmStringParse("' '");
790 confirmStringParse(" ' ");
793 public void testParseSumIfSum() {
794 String formulaString;
796 ptgs = parseFormula("sum(5, 2, if(3>2, sum(A1:A2), 6))");
797 formulaString = FormulaParser.toFormulaString(null, ptgs);
798 assertEquals("SUM(5,2,IF(3>2,SUM(A1:A2),6))", formulaString);
800 ptgs = parseFormula("if(1<2,sum(5, 2, if(3>2, sum(A1:A2), 6)),4)");
801 formulaString = FormulaParser.toFormulaString(null, ptgs);
802 assertEquals("IF(1<2,SUM(5,2,IF(3>2,SUM(A1:A2),6)),4)", formulaString);
804 public void testParserErrors() {
805 parseExpectedException("1 2");
806 parseExpectedException(" 12 . 345 ");
807 parseExpectedException("1 .23 ");
809 parseExpectedException("sum(#NAME)");
810 parseExpectedException("1 + #N / A * 2");
811 parseExpectedException("#value?");
812 parseExpectedException("#DIV/ 0+2");
815 parseExpectedException("IF(TRUE)");
816 parseExpectedException("countif(A1:B5, C1, D1)");
819 private static void parseExpectedException(String formula) {
821 parseFormula(formula);
822 throw new AssertionFailedError("expected parse exception");
823 } catch (FormulaParseException e) {
824 // expected during successful test
825 assertNotNull(e.getMessage());
826 } catch (RuntimeException e) {
828 fail("Wrong exception:" + e.getMessage());
832 public void testSetFormulaWithRowBeyond32768_Bug44539() {
834 HSSFWorkbook wb = new HSSFWorkbook();
835 HSSFSheet sheet = wb.createSheet();
836 wb.setSheetName(0, "Sheet1");
838 HSSFRow row = sheet.createRow(0);
839 HSSFCell cell = row.createCell((short)0);
840 cell.setCellFormula("SUM(A32769:A32770)");
841 if("SUM(A-32767:A-32766)".equals(cell.getCellFormula())) {
842 fail("Identified bug 44539");
844 assertEquals("SUM(A32769:A32770)", cell.getCellFormula());
847 public void testSpaceAtStartOfFormula() {
848 // Simulating cell formula of "= 4" (note space)
849 // The same Ptg array can be observed if an excel file is saved with that exact formula
851 AttrPtg spacePtg = AttrPtg.createSpace(AttrPtg.SpaceType.SPACE_BEFORE, 1);
852 Ptg[] ptgs = { spacePtg, new IntPtg(4), };
853 String formulaString;
855 formulaString = FormulaParser.toFormulaString(null, ptgs);
856 } catch (IllegalStateException e) {
857 if(e.getMessage().equalsIgnoreCase("too much stuff left on the stack")) {
858 throw new AssertionFailedError("Identified bug 44609");
860 // else some unexpected error
863 // FormulaParser strips spaces anyway
864 assertEquals("4", formulaString);
866 ptgs = new Ptg[] { new IntPtg(3), spacePtg, new IntPtg(4), spacePtg, new AddPtg()};
867 formulaString = FormulaParser.toFormulaString(null, ptgs);
868 assertEquals("3+4", formulaString);
872 * Checks some internal error detecting logic ('stack underflow error' in toFormulaString)
874 public void testTooFewOperandArgs() {
875 // Simulating badly encoded cell formula of "=/1"
876 // Not sure if Excel could ever produce this
878 // Excel would probably have put tMissArg here
883 FormulaParser.toFormulaString(null, ptgs);
884 fail("Expected exception was not thrown");
885 } catch (IllegalStateException e) {
886 // expected during successful test
887 assertTrue(e.getMessage().startsWith("Too few arguments suppled to operation token"));
891 * Make sure that POI uses the right Func Ptg when encoding formulas. Functions with variable
892 * number of args should get FuncVarPtg, functions with fixed args should get FuncPtg.<p/>
894 * Prior to the fix for bug 44675 POI would encode FuncVarPtg for all functions. In many cases
895 * Excel tolerates the wrong Ptg and evaluates the formula OK (e.g. SIN), but in some cases
896 * (e.g. COUNTIF) Excel fails to evaluate the formula, giving '#VALUE!' instead.
898 public void testFuncPtgSelection() {
899 HSSFWorkbook book = new HSSFWorkbook();
901 ptgs = FormulaParser.parse("countif(A1:A2, 1)", book);
902 assertEquals(3, ptgs.length);
903 if(FuncVarPtg.class == ptgs[2].getClass()) {
904 throw new AssertionFailedError("Identified bug 44675");
906 assertEquals(FuncPtg.class, ptgs[2].getClass());
907 ptgs = FormulaParser.parse("sin(1)", book);
908 assertEquals(2, ptgs.length);
909 assertEquals(FuncPtg.class, ptgs[1].getClass());
912 public void testWrongNumberOfFunctionArgs() {
913 confirmArgCountMsg("sin()", "Too few arguments to function 'SIN'. Expected 1 but got 0.");
914 confirmArgCountMsg("countif(1, 2, 3, 4)", "Too many arguments to function 'COUNTIF'. Expected 2 but got 4.");
915 confirmArgCountMsg("index(1, 2, 3, 4, 5, 6)", "Too many arguments to function 'INDEX'. At most 4 were expected but got 6.");
916 confirmArgCountMsg("vlookup(1, 2)", "Too few arguments to function 'VLOOKUP'. At least 3 were expected but got 2.");
919 private static void confirmArgCountMsg(String formula, String expectedMessage) {
920 HSSFWorkbook book = new HSSFWorkbook();
922 FormulaParser.parse(formula, book);
923 throw new AssertionFailedError("Didn't get parse exception as expected");
924 } catch (FormulaParseException e) {
925 assertEquals(expectedMessage, e.getMessage());