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 |