001: /* ====================================================================
002: Licensed to the Apache Software Foundation (ASF) under one or more
003: contributor license agreements. See the NOTICE file distributed with
004: this work for additional information regarding copyright ownership.
005: The ASF licenses this file to You under the Apache License, Version 2.0
006: (the "License"); you may not use this file except in compliance with
007: the License. You may obtain a copy of the License at
008:
009: http://www.apache.org/licenses/LICENSE-2.0
010:
011: Unless required by applicable law or agreed to in writing, software
012: distributed under the License is distributed on an "AS IS" BASIS,
013: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
014: See the License for the specific language governing permissions and
015: limitations under the License.
016: ==================================================================== */
017:
018: package org.apache.poi.hssf.model;
019:
020: import junit.framework.TestCase;
021:
022: import org.apache.poi.hssf.record.formula.AbstractFunctionPtg;
023: import org.apache.poi.hssf.record.formula.AddPtg;
024: import org.apache.poi.hssf.record.formula.AttrPtg;
025: import org.apache.poi.hssf.record.formula.BoolPtg;
026: import org.apache.poi.hssf.record.formula.DividePtg;
027: import org.apache.poi.hssf.record.formula.EqualPtg;
028: import org.apache.poi.hssf.record.formula.FuncVarPtg;
029: import org.apache.poi.hssf.record.formula.IntPtg;
030: import org.apache.poi.hssf.record.formula.LessEqualPtg;
031: import org.apache.poi.hssf.record.formula.LessThanPtg;
032: import org.apache.poi.hssf.record.formula.NamePtg;
033: import org.apache.poi.hssf.record.formula.NotEqualPtg;
034: import org.apache.poi.hssf.record.formula.NumberPtg;
035: import org.apache.poi.hssf.record.formula.Ptg;
036: import org.apache.poi.hssf.record.formula.ReferencePtg;
037: import org.apache.poi.hssf.record.formula.StringPtg;
038: import org.apache.poi.hssf.record.formula.UnaryMinusPtg;
039: import org.apache.poi.hssf.record.formula.UnaryPlusPtg;
040: import org.apache.poi.hssf.usermodel.HSSFCell;
041: import org.apache.poi.hssf.usermodel.HSSFRow;
042: import org.apache.poi.hssf.usermodel.HSSFSheet;
043: import org.apache.poi.hssf.usermodel.HSSFWorkbook;
044:
045: /**
046: * Test the low level formula parser functionality. High level tests are to
047: * be done via usermodel/HSSFCell.setFormulaValue() .
048: */
049: public class TestFormulaParser extends TestCase {
050:
051: public TestFormulaParser(String name) {
052: super (name);
053: }
054:
055: public void setUp() {
056:
057: }
058:
059: public void tearDown() {
060:
061: }
062:
063: public void testSimpleFormula() {
064: FormulaParser fp = new FormulaParser("2+2;", null);
065: fp.parse();
066: Ptg[] ptgs = fp.getRPNPtg();
067: assertTrue("three tokens expected, got " + ptgs.length,
068: ptgs.length == 3);
069: }
070:
071: public void testFormulaWithSpace1() {
072: FormulaParser fp = new FormulaParser(" 2 + 2 ;", null);
073: fp.parse();
074: Ptg[] ptgs = fp.getRPNPtg();
075: assertTrue("three tokens expected, got " + ptgs.length,
076: ptgs.length == 3);
077: assertTrue("", (ptgs[0] instanceof IntPtg));
078: assertTrue("", (ptgs[1] instanceof IntPtg));
079: assertTrue("", (ptgs[2] instanceof AddPtg));
080:
081: }
082:
083: public void testFormulaWithSpace2() {
084: Ptg[] ptgs;
085: FormulaParser fp;
086: fp = new FormulaParser("2+ sum( 3 , 4) ;", null);
087: fp.parse();
088: ptgs = fp.getRPNPtg();
089: assertTrue("five tokens expected, got " + ptgs.length,
090: ptgs.length == 5);
091: }
092:
093: public void testFormulaWithSpaceNRef() {
094: Ptg[] ptgs;
095: FormulaParser fp;
096: fp = new FormulaParser("sum( A2:A3 );", null);
097: fp.parse();
098: ptgs = fp.getRPNPtg();
099: assertTrue("two tokens expected, got " + ptgs.length,
100: ptgs.length == 2);
101: }
102:
103: public void testFormulaWithString() {
104: Ptg[] ptgs;
105: FormulaParser fp;
106: fp = new FormulaParser("\"hello\" & \"world\" ;", null);
107: fp.parse();
108: ptgs = fp.getRPNPtg();
109: assertTrue("three token expected, got " + ptgs.length,
110: ptgs.length == 3);
111: }
112:
113: public void testTRUE() throws Exception {
114: FormulaParser fp = new FormulaParser("TRUE", null);
115: fp.parse();
116: Ptg[] asts = fp.getRPNPtg();
117: assertEquals(1, asts.length);
118: BoolPtg flag = (BoolPtg) asts[0];
119: assertEquals(true, flag.getValue());
120: }
121:
122: public void testYN() throws Exception {
123: final String yn = "IF(TRUE,\"Y\",\"N\")";
124: FormulaParser fp = new FormulaParser(yn, null);
125: fp.parse();
126: Ptg[] asts = fp.getRPNPtg();
127: assertEquals(7, asts.length);
128:
129: BoolPtg flag = (BoolPtg) asts[0];
130: AttrPtg funif = (AttrPtg) asts[1];
131: StringPtg y = (StringPtg) asts[2];
132: AttrPtg goto1 = (AttrPtg) asts[3];
133: StringPtg n = (StringPtg) asts[4];
134:
135: assertEquals(true, flag.getValue());
136: assertEquals("Y", y.getValue());
137: assertEquals("N", n.getValue());
138: assertEquals("IF", funif.toFormulaString((Workbook) null));
139: assertTrue("Goto ptg exists", goto1.isGoto());
140: }
141:
142: public void testSimpleIf() throws Exception {
143: final String simpleif = "IF(1=1,0,1)";
144: FormulaParser fp = new FormulaParser(simpleif, null);
145: fp.parse();
146: Ptg[] asts = fp.getRPNPtg();
147: assertEquals(9, asts.length);
148:
149: IntPtg op1 = (IntPtg) asts[0];
150: IntPtg op2 = (IntPtg) asts[1];
151: EqualPtg eq = (EqualPtg) asts[2];
152: AttrPtg ifPtg = (AttrPtg) asts[3];
153: IntPtg res1 = (IntPtg) asts[4];
154:
155: AttrPtg ptgGoto = (AttrPtg) asts[5];
156: assertEquals("Goto 1 Length", (short) 10, ptgGoto.getData());
157:
158: IntPtg res2 = (IntPtg) asts[6];
159: AttrPtg ptgGoto2 = (AttrPtg) asts[7];
160: assertEquals("Goto 2 Length", (short) 3, ptgGoto2.getData());
161:
162: assertEquals("If FALSE offset", (short) 7, ifPtg.getData());
163:
164: FuncVarPtg funcPtg = (FuncVarPtg) asts[8];
165:
166: }
167:
168: /**
169: * Make sure the ptgs are generated properly with two functions embedded
170: *
171: */
172: public void testNestedFunctionIf() {
173: String function = "IF(A1=B1,AVERAGE(A1:B1),AVERAGE(A2:B2))";
174:
175: FormulaParser fp = new FormulaParser(function, null);
176: fp.parse();
177: Ptg[] asts = fp.getRPNPtg();
178: assertEquals("11 Ptgs expected", 11, asts.length);
179:
180: assertTrue("IF Attr set correctly",
181: (asts[3] instanceof AttrPtg));
182: AttrPtg ifFunc = (AttrPtg) asts[3];
183: assertTrue("It is not an if", ifFunc.isOptimizedIf());
184:
185: assertTrue("Average Function set correctly",
186: (asts[5] instanceof FuncVarPtg));
187:
188: }
189:
190: public void testIfSingleCondition() {
191: String function = "IF(1=1,10)";
192:
193: FormulaParser fp = new FormulaParser(function, null);
194: fp.parse();
195: Ptg[] asts = fp.getRPNPtg();
196: assertEquals("7 Ptgs expected", 7, asts.length);
197:
198: assertTrue("IF Attr set correctly",
199: (asts[3] instanceof AttrPtg));
200: AttrPtg ifFunc = (AttrPtg) asts[3];
201: assertTrue("It is not an if", ifFunc.isOptimizedIf());
202:
203: assertTrue("Single Value is not an IntPtg",
204: (asts[4] instanceof IntPtg));
205: IntPtg intPtg = (IntPtg) asts[4];
206: assertEquals("Result", (short) 10, intPtg.getValue());
207:
208: assertTrue("Ptg is not a Variable Function",
209: (asts[6] instanceof FuncVarPtg));
210: FuncVarPtg funcPtg = (FuncVarPtg) asts[6];
211: assertEquals("Arguments", 2, funcPtg.getNumberOfOperands());
212:
213: }
214:
215: public void testSumIf() {
216: String function = "SUMIF(A1:A5,\">4000\",B1:B5)";
217: FormulaParser fp = new FormulaParser(function, null);
218: fp.parse();
219: Ptg[] asts = fp.getRPNPtg();
220: assertEquals("4 Ptgs expected", 4, asts.length);
221:
222: }
223:
224: /**
225: * Bug Reported by xt-jens.riis@nokia.com (Jens Riis)
226: * Refers to Bug <a href="http://issues.apache.org/bugzilla/show_bug.cgi?id=17582">#17582</a>
227: *
228: */
229: public void testNonAlphaFormula() {
230: String currencyCell = "F3";
231: String function = "\"TOTAL[\"&" + currencyCell + "&\"]\"";
232:
233: FormulaParser fp = new FormulaParser(function, null);
234: fp.parse();
235: Ptg[] asts = fp.getRPNPtg();
236: assertEquals("5 ptgs expected", 5, asts.length);
237: assertTrue("Ptg[0] is a string", (asts[0] instanceof StringPtg));
238: StringPtg firstString = (StringPtg) asts[0];
239:
240: assertEquals("TOTAL[", firstString.getValue());
241: //the PTG order isn't 100% correct but it still works - dmui
242:
243: }
244:
245: public void testSimpleLogical() {
246: FormulaParser fp = new FormulaParser("IF(A1<A2,B1,B2)", null);
247: fp.parse();
248: Ptg[] ptgs = fp.getRPNPtg();
249: assertTrue("Ptg array should not be null", ptgs != null);
250: assertEquals("Ptg array length", 9, ptgs.length);
251: assertEquals("3rd Ptg is less than", LessThanPtg.class, ptgs[2]
252: .getClass());
253:
254: }
255:
256: public void testParenIf() {
257: FormulaParser fp = new FormulaParser(
258: "IF((A1+A2)<=3,\"yes\",\"no\")", null);
259: fp.parse();
260: Ptg[] ptgs = fp.getRPNPtg();
261: assertTrue("Ptg array should not be null", ptgs != null);
262: assertEquals("Ptg array length", 12, ptgs.length);
263: assertEquals("6th Ptg is less than equal", LessEqualPtg.class,
264: ptgs[5].getClass());
265: assertEquals("11th Ptg is not a goto (Attr) ptg",
266: AttrPtg.class, ptgs[10].getClass());
267: }
268:
269: public void testEmbeddedIf() {
270: FormulaParser fp = new FormulaParser(
271: "IF(3>=1,\"*\",IF(4<>1,\"first\",\"second\"))", null);
272: fp.parse();
273: Ptg[] ptgs = fp.getRPNPtg();
274: assertTrue("Ptg array should not be null", ptgs != null);
275: assertEquals("Ptg array length", 17, ptgs.length);
276:
277: assertEquals("6th Ptg is not a goto (Attr) ptg", AttrPtg.class,
278: ptgs[5].getClass());
279: assertEquals("9th Ptg is not a not equal ptg",
280: NotEqualPtg.class, ptgs[8].getClass());
281: assertEquals(
282: "15th Ptg is not the inner IF variable function ptg",
283: FuncVarPtg.class, ptgs[14].getClass());
284:
285: }
286:
287: public void testMacroFunction() {
288: Workbook w = new Workbook();
289: FormulaParser fp = new FormulaParser("FOO()", w);
290: fp.parse();
291: Ptg[] ptg = fp.getRPNPtg();
292:
293: AbstractFunctionPtg tfunc = (AbstractFunctionPtg) ptg[0];
294: assertEquals("externalflag", tfunc.getName());
295:
296: NamePtg tname = (NamePtg) ptg[1];
297: assertEquals("FOO", tname.toFormulaString(w));
298: }
299:
300: public void testEmbeddedSlash() {
301: FormulaParser fp = new FormulaParser(
302: "HYPERLINK(\"http://www.jakarta.org\",\"Jakarta\");",
303: null);
304: fp.parse();
305: Ptg[] ptg = fp.getRPNPtg();
306: assertTrue("first ptg is string", ptg[0] instanceof StringPtg);
307: assertTrue("second ptg is string", ptg[1] instanceof StringPtg);
308:
309: }
310:
311: public void testConcatenate() {
312: FormulaParser fp = new FormulaParser(
313: "CONCATENATE(\"first\",\"second\")", null);
314: fp.parse();
315: Ptg[] ptg = fp.getRPNPtg();
316: assertTrue("first ptg is string", ptg[0] instanceof StringPtg);
317: assertTrue("second ptg is string", ptg[1] instanceof StringPtg);
318: }
319:
320: public void testWorksheetReferences() {
321: HSSFWorkbook wb = new HSSFWorkbook();
322:
323: wb.createSheet("NoQuotesNeeded");
324: wb.createSheet("Quotes Needed Here &#$@");
325:
326: HSSFSheet sheet = wb.createSheet("Test");
327: HSSFRow row = sheet.createRow(0);
328: HSSFCell cell;
329:
330: cell = row.createCell((short) 0);
331: cell.setCellFormula("NoQuotesNeeded!A1");
332:
333: cell = row.createCell((short) 1);
334: cell.setCellFormula("'Quotes Needed Here &#$@'!A1");
335: }
336:
337: public void testUnaryMinus() {
338: FormulaParser fp = new FormulaParser("-A1", null);
339: fp.parse();
340: Ptg[] ptg = fp.getRPNPtg();
341: assertTrue("got 2 ptgs", ptg.length == 2);
342: assertTrue("first ptg is reference",
343: ptg[0] instanceof ReferencePtg);
344: assertTrue("second ptg is Minus",
345: ptg[1] instanceof UnaryMinusPtg);
346: }
347:
348: public void testUnaryPlus() {
349: FormulaParser fp = new FormulaParser("+A1", null);
350: fp.parse();
351: Ptg[] ptg = fp.getRPNPtg();
352: assertTrue("got 2 ptgs", ptg.length == 2);
353: assertTrue("first ptg is reference",
354: ptg[0] instanceof ReferencePtg);
355: assertTrue("second ptg is Plus", ptg[1] instanceof UnaryPlusPtg);
356: }
357:
358: public void testLeadingSpaceInString() {
359: String value = " hi ";
360: FormulaParser fp = new FormulaParser("\"" + value + "\"", null);
361: fp.parse();
362: Ptg[] ptg = fp.getRPNPtg();
363:
364: assertTrue("got 1 ptg", ptg.length == 1);
365: assertTrue("ptg0 is a StringPtg", ptg[0] instanceof StringPtg);
366: assertTrue("ptg0 contains exact value", ((StringPtg) ptg[0])
367: .getValue().equals(value));
368: }
369:
370: public void testLookupAndMatchFunctionArgs() {
371: FormulaParser fp = new FormulaParser(
372: "lookup(A1, A3:A52, B3:B52)", null);
373: fp.parse();
374: Ptg[] ptg = fp.getRPNPtg();
375:
376: assertTrue("got 4 ptg", ptg.length == 4);
377: assertTrue("ptg0 has Value class",
378: ptg[0].getPtgClass() == Ptg.CLASS_VALUE);
379:
380: fp = new FormulaParser("match(A1, A3:A52)", null);
381: fp.parse();
382: ptg = fp.getRPNPtg();
383:
384: assertTrue("got 3 ptg", ptg.length == 3);
385: assertTrue("ptg0 has Value class",
386: ptg[0].getPtgClass() == Ptg.CLASS_VALUE);
387: }
388:
389: /** bug 33160*/
390: public void testLargeInt() {
391: FormulaParser fp = new FormulaParser("40", null);
392: fp.parse();
393: Ptg[] ptg = fp.getRPNPtg();
394: assertTrue("ptg is Int, is " + ptg[0].getClass(),
395: ptg[0] instanceof IntPtg);
396:
397: fp = new FormulaParser("40000", null);
398: fp.parse();
399: ptg = fp.getRPNPtg();
400: assertTrue("ptg should be Number, is " + ptg[0].getClass(),
401: ptg[0] instanceof NumberPtg);
402: }
403:
404: /** bug 33160, testcase by Amol Deshmukh*/
405: public void testSimpleLongFormula() {
406: FormulaParser fp = new FormulaParser("40000/2", null);
407: fp.parse();
408: Ptg[] ptgs = fp.getRPNPtg();
409: assertTrue("three tokens expected, got " + ptgs.length,
410: ptgs.length == 3);
411: assertTrue("NumberPtg", (ptgs[0] instanceof NumberPtg));
412: assertTrue("IntPtg", (ptgs[1] instanceof IntPtg));
413: assertTrue("DividePtg", (ptgs[2] instanceof DividePtg));
414: }
415:
416: /** bug 35027, underscore in sheet name*/
417: public void testUnderscore() {
418: HSSFWorkbook wb = new HSSFWorkbook();
419:
420: wb.createSheet("Cash_Flow");
421: ;
422:
423: HSSFSheet sheet = wb.createSheet("Test");
424: HSSFRow row = sheet.createRow(0);
425: HSSFCell cell;
426:
427: cell = row.createCell((short) 0);
428: cell.setCellFormula("Cash_Flow!A1");
429:
430: }
431:
432: // bug 38396 : Formula with exponential numbers not parsed correctly.
433: public void testExponentialParsing() {
434: FormulaParser fp = new FormulaParser("1.3E21/2", null);
435: fp.parse();
436: Ptg[] ptgs = fp.getRPNPtg();
437: assertTrue("three tokens expected, got " + ptgs.length,
438: ptgs.length == 3);
439: assertTrue("NumberPtg", (ptgs[0] instanceof NumberPtg));
440: assertTrue("IntPtg", (ptgs[1] instanceof IntPtg));
441: assertTrue("DividePtg", (ptgs[2] instanceof DividePtg));
442:
443: fp = new FormulaParser("1322E21/2", null);
444: fp.parse();
445: ptgs = fp.getRPNPtg();
446: assertTrue("three tokens expected, got " + ptgs.length,
447: ptgs.length == 3);
448: assertTrue("NumberPtg", (ptgs[0] instanceof NumberPtg));
449: assertTrue("IntPtg", (ptgs[1] instanceof IntPtg));
450: assertTrue("DividePtg", (ptgs[2] instanceof DividePtg));
451:
452: fp = new FormulaParser("1.3E1/2", null);
453: fp.parse();
454: ptgs = fp.getRPNPtg();
455: assertTrue("three tokens expected, got " + ptgs.length,
456: ptgs.length == 3);
457: assertTrue("NumberPtg", (ptgs[0] instanceof NumberPtg));
458: assertTrue("IntPtg", (ptgs[1] instanceof IntPtg));
459: assertTrue("DividePtg", (ptgs[2] instanceof DividePtg));
460:
461: }
462:
463: public void testExponentialInSheet() throws Exception {
464: HSSFWorkbook wb = new HSSFWorkbook();
465:
466: wb.createSheet("Cash_Flow");
467: ;
468:
469: HSSFSheet sheet = wb.createSheet("Test");
470: HSSFRow row = sheet.createRow(0);
471: HSSFCell cell = row.createCell((short) 0);
472: String formula = null;
473:
474: cell.setCellFormula("1.3E21/3");
475: formula = cell.getCellFormula();
476: assertEquals("Exponential formula string", "1.3E21/3", formula);
477:
478: cell.setCellFormula("-1.3E21/3");
479: formula = cell.getCellFormula();
480: assertEquals("Exponential formula string", "-1.3E21/3", formula);
481:
482: cell.setCellFormula("1322E21/3");
483: formula = cell.getCellFormula();
484: assertEquals("Exponential formula string", "1.322E24/3",
485: formula);
486:
487: cell.setCellFormula("-1322E21/3");
488: formula = cell.getCellFormula();
489: assertEquals("Exponential formula string", "-1.322E24/3",
490: formula);
491:
492: cell.setCellFormula("1.3E1/3");
493: formula = cell.getCellFormula();
494: assertEquals("Exponential formula string", "13.0/3", formula);
495:
496: cell.setCellFormula("-1.3E1/3");
497: formula = cell.getCellFormula();
498: assertEquals("Exponential formula string", "-13.0/3", formula);
499:
500: cell.setCellFormula("1.3E-4/3");
501: formula = cell.getCellFormula();
502: assertEquals("Exponential formula string", "1.3E-4/3", formula);
503:
504: cell.setCellFormula("-1.3E-4/3");
505: formula = cell.getCellFormula();
506: assertEquals("Exponential formula string", "-1.3E-4/3", formula);
507:
508: cell.setCellFormula("13E-15/3");
509: formula = cell.getCellFormula();
510: assertEquals("Exponential formula string", "1.3E-14/3", formula);
511:
512: cell.setCellFormula("-13E-15/3");
513: formula = cell.getCellFormula();
514: assertEquals("Exponential formula string", "-1.3E-14/3",
515: formula);
516:
517: cell.setCellFormula("1.3E3/3");
518: formula = cell.getCellFormula();
519: assertEquals("Exponential formula string", "1300.0/3", formula);
520:
521: cell.setCellFormula("-1.3E3/3");
522: formula = cell.getCellFormula();
523: assertEquals("Exponential formula string", "-1300.0/3", formula);
524:
525: cell.setCellFormula("1300000000000000/3");
526: formula = cell.getCellFormula();
527: assertEquals("Exponential formula string", "1.3E15/3", formula);
528:
529: cell.setCellFormula("-1300000000000000/3");
530: formula = cell.getCellFormula();
531: assertEquals("Exponential formula string", "-1.3E15/3", formula);
532:
533: cell.setCellFormula("-10E-1/3.1E2*4E3/3E4");
534: formula = cell.getCellFormula();
535: assertEquals("Exponential formula string",
536: "-1.0/310.0*4000.0/30000.0", formula);
537: }
538:
539: public static void main(String[] args) {
540: System.out
541: .println("Testing org.apache.poi.hssf.record.formula.FormulaParser");
542: junit.textui.TestRunner.run(TestFormulaParser.class);
543: }
544:
545: public void testNumbers() {
546: HSSFWorkbook wb = new HSSFWorkbook();
547:
548: wb.createSheet("Cash_Flow");
549: ;
550:
551: HSSFSheet sheet = wb.createSheet("Test");
552: HSSFRow row = sheet.createRow(0);
553: HSSFCell cell = row.createCell((short) 0);
554: String formula = null;
555:
556: // starts from decimal point
557:
558: cell.setCellFormula(".1");
559: formula = cell.getCellFormula();
560: assertEquals("0.1", formula);
561:
562: cell.setCellFormula("+.1");
563: formula = cell.getCellFormula();
564: assertEquals("+0.1", formula);
565:
566: cell.setCellFormula("-.1");
567: formula = cell.getCellFormula();
568: assertEquals("-0.1", formula);
569:
570: // has exponent
571:
572: cell.setCellFormula("10E1");
573: formula = cell.getCellFormula();
574: assertEquals("100.0", formula);
575:
576: cell.setCellFormula("10E+1");
577: formula = cell.getCellFormula();
578: assertEquals("100.0", formula);
579:
580: cell.setCellFormula("10E-1");
581: formula = cell.getCellFormula();
582: assertEquals("1.0", formula);
583: }
584:
585: public void testRanges() {
586: HSSFWorkbook wb = new HSSFWorkbook();
587:
588: wb.createSheet("Cash_Flow");
589: ;
590:
591: HSSFSheet sheet = wb.createSheet("Test");
592: HSSFRow row = sheet.createRow(0);
593: HSSFCell cell = row.createCell((short) 0);
594: String formula = null;
595:
596: cell.setCellFormula("A1.A2");
597: formula = cell.getCellFormula();
598: assertEquals("A1:A2", formula);
599:
600: cell.setCellFormula("A1..A2");
601: formula = cell.getCellFormula();
602: assertEquals("A1:A2", formula);
603:
604: cell.setCellFormula("A1...A2");
605: formula = cell.getCellFormula();
606: assertEquals("A1:A2", formula);
607: }
608: }
|