|
1
2
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
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
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
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
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 }
361 |
Popular Tags |
Java API By Example, From Geeks To Geeks.