KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > apache > poi > hssf > model > TestFormulaParser


1
2 /* ====================================================================
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 JavaDoc 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 JavaDoc {
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 JavaDoc {
113         final String JavaDoc 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 JavaDoc {
134         final String JavaDoc 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 JavaDoc 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 JavaDoc 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 JavaDoc 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 JavaDoc currencyCell = "F3";
220         String JavaDoc 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 JavaDoc 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 JavaDoc [] args) {
357         System.out.println("Testing org.apache.poi.hssf.record.formula.FormulaParser");
358         junit.textui.TestRunner.run(TestFormulaParser.class);
359     }
360 }
361
Popular Tags