poi formula parser

  /* ====================================================================
3    Copyright 2002-2004 Apache Software Foundation
4
5    Licensed under the Apache License, Version 2.0 (the "License");
6    you may not use this file except in compliance with the License.
7    You may obtain a copy of the License at
8
9        http://www.apache.org/licenses/LICENSE-2.0
10
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 ==================================================================== */
17         
18 package org.apache.poi.hssf.model ;
19
20 import junit.framework.TestCase ;
21
22 import org.apache.poi.hssf.record.formula.AbstractFunctionPtg ;
23 import org.apache.poi.hssf.record.formula.AddPtg ;
24 import org.apache.poi.hssf.record.formula.AttrPtg ;
25 import org.apache.poi.hssf.record.formula.BoolPtg ;
26 import org.apache.poi.hssf.record.formula.EqualPtg ;
27 import org.apache.poi.hssf.record.formula.FuncVarPtg ;
28 import org.apache.poi.hssf.record.formula.IntPtg ;
29 import org.apache.poi.hssf.record.formula.LessEqualPtg ;
30 import org.apache.poi.hssf.record.formula.LessThanPtg ;
31 import org.apache.poi.hssf.record.formula.NamePtg ;
32 import org.apache.poi.hssf.record.formula.NotEqualPtg ;
33 import org.apache.poi.hssf.record.formula.Ptg ;
34 import org.apache.poi.hssf.record.formula.ReferencePtg ;
35 import org.apache.poi.hssf.record.formula.StringPtg ;
36 import org.apache.poi.hssf.record.formula.UnaryMinusPtg ;
37 import org.apache.poi.hssf.usermodel.HSSFCell ;
38 import org.apache.poi.hssf.usermodel.HSSFRow ;
39 import org.apache.poi.hssf.usermodel.HSSFSheet ;
40 import org.apache.poi.hssf.usermodel.HSSFWorkbook ;
41
42 /**
43  * Test the low level formula parser functionality. High level tests are to
44  * be done via usermodel/HSSFCell.setFormulaValue() .
45  */
46 public class TestFormulaParser extends TestCase {
47
48     public TestFormulaParser( String name) {
49         super(name);
50     }
51     public void setUp(){
52         
53     }
54     
55     public void tearDown() {
56         
57     }
58     
59     public void testSimpleFormula() {
60          FormulaParser fp = new FormulaParser ("2+2;",null);
61         fp.parse();
62          Ptg [] ptgs = fp.getRPNPtg();
63         assertTrue("three tokens expected, got "+ptgs.length,ptgs.length == 3);
64     }
65     public void testFormulaWithSpace1() {
66          FormulaParser fp = new FormulaParser (" 2 + 2 ;",null);
67         fp.parse();
68          Ptg [] ptgs = fp.getRPNPtg();
69         assertTrue("three tokens expected, got "+ptgs.length,ptgs.length == 3);
70         assertTrue("",(ptgs[0] instanceof IntPtg ));
71         assertTrue("",(ptgs[1] instanceof IntPtg ));
72         assertTrue("",(ptgs[2] instanceof AddPtg ));
73         
74     }
75     
76     public void testFormulaWithSpace2() {
77          Ptg [] ptgs;
78          FormulaParser fp;
79         fp = new FormulaParser ("2+ sum( 3 , 4) ;",null);
80         fp.parse();
81         ptgs = fp.getRPNPtg();
82         assertTrue("five tokens expected, got "+ptgs.length,ptgs.length == 5);
83     }
84     
85      public void testFormulaWithSpaceNRef() {
86          Ptg [] ptgs;
87          FormulaParser fp;
88         fp = new FormulaParser ("sum( A2:A3 );",null);
89         fp.parse();
90         ptgs = fp.getRPNPtg();
91         assertTrue("two tokens expected, got "+ptgs.length,ptgs.length == 2);
92     }
93     
94     public void testFormulaWithString() {
95          Ptg [] ptgs;
96          FormulaParser fp;
97         fp = new FormulaParser ("/"hello/" & /"world/" ;",null);
98         fp.parse();
99         ptgs = fp.getRPNPtg();
100         assertTrue("three token expected, got " + ptgs.length, ptgs.length == 3);
101     }
102
103     public void testTRUE() throws Exception {
104          FormulaParser fp = new FormulaParser ("TRUE", null);
105         fp.parse();
106          Ptg [] asts = fp.getRPNPtg();
107         assertEquals(1, asts.length);
108          BoolPtg flag = ( BoolPtg ) asts[0];
109         assertEquals(true, flag.getValue());
110     }
111
112     public void testYN() throws Exception {
113         final String yn = "IF(TRUE,/"Y/",/"N/")";
114          FormulaParser fp = new FormulaParser (yn, null);
115         fp.parse();
116          Ptg [] asts = fp.getRPNPtg();
117         assertEquals(7, asts.length);
118
119          BoolPtg flag = ( BoolPtg ) asts[0];
120          AttrPtg funif = ( AttrPtg ) asts[1];
121          StringPtg y = ( StringPtg ) asts[2];
122          AttrPtg goto1 = ( AttrPtg ) asts[3];
123          StringPtg n = ( StringPtg ) asts[4];
124
125
126         assertEquals(true, flag.getValue());
127         assertEquals("Y", y.getValue());
128         assertEquals("N", n.getValue());
129         assertEquals("IF", funif.toFormulaString(( Workbook ) null));
130         assertTrue("Goto ptg exists", goto1.isGoto());
131     }
132
133     public void testSimpleIf() throws Exception {
134         final String simpleif = "IF(1=1,0,1)";
135          FormulaParser fp = new FormulaParser (simpleif, null);
136         fp.parse();
137          Ptg [] asts = fp.getRPNPtg();
138         assertEquals(9, asts.length);
139         
140          IntPtg op1 = ( IntPtg ) asts[0];
141          IntPtg op2 = ( IntPtg ) asts[1];
142          EqualPtg eq = ( EqualPtg ) asts[2];
143          AttrPtg ifPtg = ( AttrPtg ) asts[3];
144          IntPtg res1 = ( IntPtg ) asts[4];
145                 
146          AttrPtg ptgGoto= ( AttrPtg ) asts[5];
147         assertEquals("Goto 1 Length", (short)10, ptgGoto.getData());
148         
149          IntPtg res2 = ( IntPtg ) asts[6];
150          AttrPtg ptgGoto2 = ( AttrPtg ) asts[7];
151         assertEquals("Goto 2 Length", (short)3, ptgGoto2.getData());
152         
153         assertEquals("If FALSE offset", (short)7, ifPtg.getData());
154         
155          FuncVarPtg funcPtg = ( FuncVarPtg )asts[8];
156         
157         
158     }
159     
160      /**
161      * Make sure the ptgs are generated properly with two functions embedded
162      *
163      */
164     public void testNestedFunctionIf() {
165          String function = "IF(A1=B1,AVERAGE(A1:B1),AVERAGE(A2:B2))";
166
167          FormulaParser fp = new FormulaParser (function, null);
168         fp.parse();
169          Ptg [] asts = fp.getRPNPtg();
170         assertEquals("11 Ptgs expected", 11, asts.length);
171
172         assertTrue("IF Attr set correctly", (asts[3] instanceof AttrPtg ));
173          AttrPtg ifFunc = ( AttrPtg )asts[3];
174         assertTrue("It is not an if", ifFunc.isOptimizedIf());
175         
176         assertTrue("Average Function set correctly", (asts[5] instanceof FuncVarPtg ));
177         
178                         
179     }
180     
181     public void testIfSingleCondition(){
182          String function = "IF(1=1,10)";
183
184          FormulaParser fp = new FormulaParser (function, null);
185         fp.parse();
186          Ptg [] asts = fp.getRPNPtg();
187         assertEquals("7 Ptgs expected", 7, asts.length);
188
189         assertTrue("IF Attr set correctly", (asts[3] instanceof AttrPtg ));
190          AttrPtg ifFunc = ( AttrPtg )asts[3];
191         assertTrue("It is not an if", ifFunc.isOptimizedIf());
192         
193         assertTrue("Single Value is not an IntPtg", (asts[4] instanceof IntPtg ));
194          IntPtg intPtg = ( IntPtg )asts[4];
195         assertEquals("Result", (short)10, intPtg.getValue());
196         
197         assertTrue("Ptg is not a Variable Function", (asts[6] instanceof FuncVarPtg ));
198          FuncVarPtg funcPtg = ( FuncVarPtg )asts[6];
199         assertEquals("Arguments", 2, funcPtg.getNumberOfOperands());
200         
201         
202     }
203
204     public void testSumIf() {
205          String function ="SUMIF(A1:A5,/">4000/",B1:B5)";
206          FormulaParser fp = new FormulaParser (function, null);
207         fp.parse();
208          Ptg [] asts = fp.getRPNPtg();
209         assertEquals("4 Ptgs expected", 4, asts.length);
210         
211     }
212      
213      /**
214      * Bug Reported by xt-jens.riis@nokia.com (Jens Riis)
215      * Refers to Bug <a HREF="http://issues.apache.org/bugzilla/show_bug.cgi?id=17582">#17582</a>
216      *
217      */
218     public void testNonAlphaFormula(){
219          String currencyCell = "F3";
220          String function="/"TOTAL[/"&"+currencyCell+"&/"]/"";
221
222          FormulaParser fp = new FormulaParser (function, null);
223         fp.parse();
224          Ptg [] asts = fp.getRPNPtg();
225         assertEquals("5 ptgs expected", 5, asts.length);
226         assertTrue ("Ptg[0] is a string", (asts[0] instanceof StringPtg ));
227          StringPtg firstString = ( StringPtg )asts[0];
228         
229         assertEquals("TOTAL[", firstString.getValue());
230          //the PTG order isn't 100% correct but it still works - dmui
231
232                     
233     }
234         
235     public void testSimpleLogical() {
236          FormulaParser fp=new FormulaParser ("IF(A1<A2,B1,B2)",null);
237         fp.parse();
238        Ptg [] ptgs = fp.getRPNPtg();
239       assertTrue("Ptg array should not be null", ptgs !=null);
240       assertEquals("Ptg array length", 9, ptgs.length);
241       assertEquals("3rd Ptg is less than", LessThanPtg .class,ptgs[2].getClass());
242             
243            
244     }
245      
246     public void testParenIf() {
247          FormulaParser fp=new FormulaParser ("IF((A1+A2)<=3,/"yes/",/"no/")",null);
248         fp.parse();
249          Ptg [] ptgs = fp.getRPNPtg();
250         assertTrue("Ptg array should not be null", ptgs !=null);
251         assertEquals("Ptg array length", 12, ptgs.length);
252         assertEquals("6th Ptg is less than equal", LessEqualPtg .class,ptgs[5].getClass());
253         assertEquals("11th Ptg is not a goto (Attr) ptg", AttrPtg .class,ptgs[10].getClass());
254     }
255     
256     public void testEmbeddedIf() {
257          FormulaParser fp=new FormulaParser ("IF(3>=1,/"*/",IF(4<>1,/"first/",/"second/"))",null);
258         fp.parse();
259          Ptg [] ptgs = fp.getRPNPtg();
260         assertTrue("Ptg array should not be null", ptgs !=null);
261         assertEquals("Ptg array length", 17, ptgs.length);
262         
263         assertEquals("6th Ptg is not a goto (Attr) ptg", AttrPtg .class,ptgs[5].getClass());
264         assertEquals("9th Ptg is not a not equal ptg", NotEqualPtg .class,ptgs[8].getClass());
265         assertEquals("15th Ptg is not the inner IF variable function ptg", FuncVarPtg .class,ptgs[14].getClass());
266         
267     }
268         
269     public void testMacroFunction() {
270          Workbook w = new Workbook ();
271          FormulaParser fp = new FormulaParser ("FOO()", w);
272         fp.parse();
273          Ptg [] ptg = fp.getRPNPtg();
274
275          AbstractFunctionPtg tfunc = ( AbstractFunctionPtg ) ptg[0];
276         assertEquals("externalflag", tfunc.getName());
277
278          NamePtg tname = ( NamePtg ) ptg[1];
279         assertEquals("FOO", tname.toFormulaString(w));
280     }
281
282     public void testEmbeddedSlash() {
283          FormulaParser fp = new FormulaParser ("HYPERLINK(/"http://www.jakarta.org/",/"Jakarta/");",null);
284         fp.parse();
285          Ptg [] ptg = fp.getRPNPtg();
286         assertTrue("first ptg is string",ptg[0] instanceof StringPtg );
287         assertTrue("second ptg is string",ptg[1] instanceof StringPtg );
288         
289     }
290     
291     public void testConcatenate(){
292           FormulaParser fp = new FormulaParser ("CONCATENATE(/"first/",/"second/")",null);
293          fp.parse();
294           Ptg [] ptg = fp.getRPNPtg();
295         assertTrue("first ptg is string",ptg[0] instanceof StringPtg );
296         assertTrue("second ptg is string",ptg[1] instanceof StringPtg );
297     }
298     
299     public void testWorksheetReferences()
300     {
301          HSSFWorkbook wb = new HSSFWorkbook ();
302         
303         wb.createSheet("NoQuotesNeeded");
304         wb.createSheet("Quotes Needed Here &#$@");
305         
306          HSSFSheet sheet = wb.createSheet("Test");
307          HSSFRow row = sheet.createRow(0);
308          HSSFCell cell;
309         
310         cell = row.createCell((short)0);
311         cell.setCellFormula("NoQuotesNeeded!A1");
312         
313         cell = row.createCell((short)1);
314         cell.setCellFormula("'Quotes Needed Here &#$@'!A1");
315     }
316     
317     public void testUnaryMinus()
318     {
319          FormulaParser fp = new FormulaParser ("-A1", null);
320         fp.parse();
321          Ptg [] ptg = fp.getRPNPtg();
322         assertTrue("got 2 ptgs", ptg.length == 2);
323         assertTrue("first ptg is reference",ptg[0] instanceof ReferencePtg );
324         assertTrue("second ptg is string",ptg[1] instanceof UnaryMinusPtg );
325      }
326     
327     public void testLeadingSpaceInString()
328     {
329          String value = " hi ";
330          FormulaParser fp = new FormulaParser ("/"" + value + "/"", null);
331         fp.parse();
332          Ptg [] ptg = fp.getRPNPtg();
333     
334         assertTrue("got 1 ptg", ptg.length == 1);
335         assertTrue("ptg0 is a StringPtg", ptg[0] instanceof StringPtg );
336         assertTrue("ptg0 contains exact value", (( StringPtg )ptg[0]).getValue().equals(value));
337     }
338
339     public void testLookupAndMatchFunctionArgs()
340     {
341          FormulaParser fp = new FormulaParser ("lookup(A1, A3:A52, B3:B52)", null);
342         fp.parse();
343          Ptg [] ptg = fp.getRPNPtg();
344     
345         assertTrue("got 4 ptg", ptg.length == 4);
346         assertTrue("ptg0 has Value class", ptg[0].getPtgClass() == Ptg.CLASS_VALUE);
347         
348         fp = new FormulaParser ("match(A1, A3:A52)", null);
349         fp.parse();
350         ptg = fp.getRPNPtg();
351     
352         assertTrue("got 3 ptg", ptg.length == 3);
353         assertTrue("ptg0 has Value class", ptg[0].getPtgClass() == Ptg.CLASS_VALUE);
354     }
355
356      public static void main( String [] args) {
357         System.out.println("Testing org.apache.poi.hssf.record.formula.FormulaParser");
358         junit.textui.TestRunner.run( TestFormulaParser .class);
359     }
360 }