src/scratchpad/src/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java

   1 /*
   2 * Licensed to the Apache Software Foundation (ASF) under one or more
   3 * contributor license agreements.  See the NOTICE file distributed with
   4 * this work for additional information regarding copyright ownership.
   5 * The ASF licenses this file to You under the Apache License, Version 2.0
   6 * (the "License"); you may not use this file except in compliance with
   7 * the License.  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.usermodel;
  19
  20 import java.lang.reflect.Constructor;
  21 import java.util.HashMap;
  22 import java.util.Map;
  23 import java.util.Stack;
  24
  25 import org.apache.poi.hssf.model.FormulaParser;
  26 import org.apache.poi.hssf.model.Workbook;
  27 import org.apache.poi.hssf.record.formula.Area3DPtg;
  28 import org.apache.poi.hssf.record.formula.AreaPtg;
  29 import org.apache.poi.hssf.record.formula.AttrPtg;
  30 import org.apache.poi.hssf.record.formula.BoolPtg;
  31 import org.apache.poi.hssf.record.formula.ControlPtg;
  32 import org.apache.poi.hssf.record.formula.IntPtg;
  33 import org.apache.poi.hssf.record.formula.MemErrPtg;
  34 import org.apache.poi.hssf.record.formula.MissingArgPtg;
  35 import org.apache.poi.hssf.record.formula.NamePtg;
  36 import org.apache.poi.hssf.record.formula.NameXPtg;
  37 import org.apache.poi.hssf.record.formula.NumberPtg;
  38 import org.apache.poi.hssf.record.formula.OperationPtg;
  39 import org.apache.poi.hssf.record.formula.ParenthesisPtg;
  40 import org.apache.poi.hssf.record.formula.Ptg;
  41 import org.apache.poi.hssf.record.formula.Ref3DPtg;
  42 import org.apache.poi.hssf.record.formula.ReferencePtg;
  43 import org.apache.poi.hssf.record.formula.StringPtg;
  44 import org.apache.poi.hssf.record.formula.UnionPtg;
  45 import org.apache.poi.hssf.record.formula.UnknownPtg;
  46 import org.apache.poi.hssf.record.formula.eval.Area2DEval;
  47 import org.apache.poi.hssf.record.formula.eval.Area3DEval;
  48 import org.apache.poi.hssf.record.formula.eval.AreaEval;
  49 import org.apache.poi.hssf.record.formula.eval.BlankEval;
  50 import org.apache.poi.hssf.record.formula.eval.BoolEval;
  51 import org.apache.poi.hssf.record.formula.eval.ErrorEval;
  52 import org.apache.poi.hssf.record.formula.eval.Eval;
  53 import org.apache.poi.hssf.record.formula.eval.FunctionEval;
  54 import org.apache.poi.hssf.record.formula.eval.NameEval;
  55 import org.apache.poi.hssf.record.formula.eval.NumberEval;
  56 import org.apache.poi.hssf.record.formula.eval.OperationEval;
  57 import org.apache.poi.hssf.record.formula.eval.Ref2DEval;
  58 import org.apache.poi.hssf.record.formula.eval.Ref3DEval;
  59 import org.apache.poi.hssf.record.formula.eval.RefEval;
  60 import org.apache.poi.hssf.record.formula.eval.StringEval;
  61 import org.apache.poi.hssf.record.formula.eval.ValueEval;
  62
  63 /**
  64  * @author Amol S. Deshmukh &lt; amolweb at ya hoo dot com &gt;
  65  *
  66  */
  67 public class HSSFFormulaEvaluator {
  68
  69     // params to lookup the right constructor using reflection
  70     private static final Class[] VALUE_CONTRUCTOR_CLASS_ARRAY = new Class[] { Ptg.class };
  71
  72     private static final Class[] AREA3D_CONSTRUCTOR_CLASS_ARRAY = new Class[] { Ptg.class, ValueEval[].class };
  73
  74     private static final Class[] REFERENCE_CONSTRUCTOR_CLASS_ARRAY = new Class[] { Ptg.class, ValueEval.class };
  75
  76     private static final Class[] REF3D_CONSTRUCTOR_CLASS_ARRAY = new Class[] { Ptg.class, ValueEval.class };
  77
  78     // Maps for mapping *Eval to *Ptg
  79     private static final Map VALUE_EVALS_MAP = new HashMap();
  80
  81     /*
  82      * Following is the mapping between the Ptg tokens returned
  83      * by the FormulaParser and the *Eval classes that are used
  84      * by the FormulaEvaluator
  85      */
  86     static {
  87         VALUE_EVALS_MAP.put(BoolPtg.class, BoolEval.class);
  88         VALUE_EVALS_MAP.put(IntPtg.class, NumberEval.class);
  89         VALUE_EVALS_MAP.put(NumberPtg.class, NumberEval.class);
  90         VALUE_EVALS_MAP.put(StringPtg.class, StringEval.class);
  91
  92     }
  93
  94
  95     protected HSSFRow row;
  96     protected HSSFSheet sheet;
  97     protected HSSFWorkbook workbook;
  98
  99     public HSSFFormulaEvaluator(HSSFSheet sheet, HSSFWorkbook workbook) {
 100         this.sheet = sheet;
 101         this.workbook = workbook;
 102     }
 103
 104     public void setCurrentRow(HSSFRow row) {
 105         this.row = row;
 106     }
 107
 108
 109     /**
 110      * Returns an underlying FormulaParser, for the specified
 111      *  Formula String and HSSFWorkbook.
 112      * This will allow you to generate the Ptgs yourself, if
 113      *  your needs are more complex than just having the
 114      *  formula evaluated.
 115      */
 116     public static FormulaParser getUnderlyingParser(HSSFWorkbook workbook, String formula) {
 117         return new FormulaParser(formula, workbook.getWorkbook());
 118     }
 119
 120     /**
 121      * If cell contains a formula, the formula is evaluated and returned,
 122      * else the CellValue simply copies the appropriate cell value from
 123      * the cell and also its cell type. This method should be preferred over
 124      * evaluateInCell() when the call should not modify the contents of the
 125      * original cell.
 126      * @param cell
 127      */
 128     public CellValue evaluate(HSSFCell cell) {
 129         CellValue retval = null;
 130         if (cell != null) {
 131             switch (cell.getCellType()) {
 132             case HSSFCell.CELL_TYPE_BLANK:
 133                 retval = new CellValue(HSSFCell.CELL_TYPE_BLANK);
 134                 break;
 135             case HSSFCell.CELL_TYPE_BOOLEAN:
 136                 retval = new CellValue(HSSFCell.CELL_TYPE_BOOLEAN);
 137                 retval.setBooleanValue(cell.getBooleanCellValue());
 138                 break;
 139             case HSSFCell.CELL_TYPE_ERROR:
 140                 retval = new CellValue(HSSFCell.CELL_TYPE_ERROR);
 141                 retval.setErrorValue(cell.getErrorCellValue());
 142                 break;
 143             case HSSFCell.CELL_TYPE_FORMULA:
 144                 retval = getCellValueForEval(internalEvaluate(cell, row, sheet, workbook));
 145                 break;
 146             case HSSFCell.CELL_TYPE_NUMERIC:
 147                 retval = new CellValue(HSSFCell.CELL_TYPE_NUMERIC);
 148                 retval.setNumberValue(cell.getNumericCellValue());
 149                 break;
 150             case HSSFCell.CELL_TYPE_STRING:
 151                 retval = new CellValue(HSSFCell.CELL_TYPE_STRING);
 152                 retval.setRichTextStringValue(cell.getRichStringCellValue());
 153                 break;
 154             }
 155         }
 156         return retval;
 157     }
 158
 159
 160     /**
 161      * If cell contains formula, it evaluates the formula,
 162      *  and saves the result of the formula. The cell
 163      *  remains as a formula cell.
 164      * Else if cell does not contain formula, this method leaves
 165      *  the cell unchanged.
 166      * Note that the type of the formula result is returned,
 167      *  so you know what kind of value is also stored with
 168      *  the formula.
 169      * <pre>
 170      * int evaluatedCellType = evaluator.evaluateFormulaCell(cell);
 171      * </pre>
 172      * Be aware that your cell will hold both the formula,
 173      *  and the result. If you want the cell replaced with
 174      *  the result of the formula, use {@link #evaluateInCell(HSSFCell)}
 175      * @param cell The cell to evaluate
 176      * @return The type of the formula result (the cell's type remains as HSSFCell.CELL_TYPE_FORMULA however)
 177      */
 178     public int evaluateFormulaCell(HSSFCell cell) {
 179         if (cell != null) {
 180             switch (cell.getCellType()) {
 181             case HSSFCell.CELL_TYPE_FORMULA:
 182                 CellValue cv = getCellValueForEval(internalEvaluate(cell, row, sheet, workbook));
 183                 switch (cv.getCellType()) {
 184                 case HSSFCell.CELL_TYPE_BOOLEAN:
 185                     cell.setCellValue(cv.getBooleanValue());
 186                     break;
 187                 case HSSFCell.CELL_TYPE_ERROR:
 188                     cell.setCellValue(cv.getErrorValue());
 189                     break;
 190                 case HSSFCell.CELL_TYPE_NUMERIC:
 191                     cell.setCellValue(cv.getNumberValue());
 192                     break;
 193                 case HSSFCell.CELL_TYPE_STRING:
 194                     cell.setCellValue(cv.getRichTextStringValue());
 195                     break;
 196                 case HSSFCell.CELL_TYPE_BLANK:
 197                     break;
 198                 case HSSFCell.CELL_TYPE_FORMULA: // this will never happen, we have already evaluated the formula
 199                     break;
 200                 }
 201                 return cv.getCellType();
 202             }
 203         }
 204         return -1;
 205     }
 206
 207     /**
 208      * If cell contains formula, it evaluates the formula, and
 209      *  puts the formula result back into the cell, in place
 210      *  of the old formula.
 211      * Else if cell does not contain formula, this method leaves
 212      *  the cell unchanged.
 213      * Note that the same instance of HSSFCell is returned to
 214      * allow chained calls like:
 215      * <pre>
 216      * int evaluatedCellType = evaluator.evaluateInCell(cell).getCellType();
 217      * </pre>
 218      * Be aware that your cell value will be changed to hold the
 219      *  result of the formula. If you simply want the formula
 220      *  value computed for you, use {@link #evaluateFormulaCell(HSSFCell)}
 221      * @param cell
 222      */
 223     public HSSFCell evaluateInCell(HSSFCell cell) {
 224         if (cell != null) {
 225             switch (cell.getCellType()) {
 226             case HSSFCell.CELL_TYPE_FORMULA:
 227                 CellValue cv = getCellValueForEval(internalEvaluate(cell, row, sheet, workbook));
 228                 switch (cv.getCellType()) {
 229                 case HSSFCell.CELL_TYPE_BOOLEAN:
 230                     cell.setCellType(HSSFCell.CELL_TYPE_BOOLEAN);
 231                     cell.setCellValue(cv.getBooleanValue());
 232                     break;
 233                 case HSSFCell.CELL_TYPE_ERROR:
 234                     cell.setCellType(HSSFCell.CELL_TYPE_ERROR);
 235                     cell.setCellValue(cv.getErrorValue());
 236                     break;
 237                 case HSSFCell.CELL_TYPE_NUMERIC:
 238                     cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
 239                     cell.setCellValue(cv.getNumberValue());
 240                     break;
 241                 case HSSFCell.CELL_TYPE_STRING:
 242                     cell.setCellType(HSSFCell.CELL_TYPE_STRING);
 243                     cell.setCellValue(cv.getRichTextStringValue());
 244                     break;
 245                 case HSSFCell.CELL_TYPE_BLANK:
 246                     break;
 247                 case HSSFCell.CELL_TYPE_FORMULA: // this will never happen, we have already evaluated the formula
 248                     break;
 249                 }
 250             }
 251         }
 252         return cell;
 253     }
 254
 255
 256     /**
 257      * Returns a CellValue wrapper around the supplied ValueEval instance.
 258      * @param eval
 259      */
 260     protected static CellValue getCellValueForEval(ValueEval eval) {
 261         CellValue retval = null;
 262         if (eval != null) {
 263             if (eval instanceof NumberEval) {
 264                 NumberEval ne = (NumberEval) eval;
 265                 retval = new CellValue(HSSFCell.CELL_TYPE_NUMERIC);
 266                 retval.setNumberValue(ne.getNumberValue());
 267             }
 268             else if (eval instanceof BoolEval) {
 269                 BoolEval be = (BoolEval) eval;
 270                 retval = new CellValue(HSSFCell.CELL_TYPE_BOOLEAN);
 271                 retval.setBooleanValue(be.getBooleanValue());
 272             }
 273             else if (eval instanceof StringEval) {
 274                 StringEval ne = (StringEval) eval;
 275                 retval = new CellValue(HSSFCell.CELL_TYPE_STRING);
 276                 retval.setStringValue(ne.getStringValue());
 277             }
 278             else if (eval instanceof BlankEval) {
 279                 retval = new CellValue(HSSFCell.CELL_TYPE_BLANK);
 280             }
 281             else if (eval instanceof ErrorEval) {
 282                 retval = new CellValue(HSSFCell.CELL_TYPE_ERROR);
 283                 retval.setErrorValue((byte)((ErrorEval)eval).getErrorCode());
 284 //                retval.setRichTextStringValue(new HSSFRichTextString("#An error occurred. check cell.getErrorCode()"));
 285             }
 286             else {
 287                 retval = new CellValue(HSSFCell.CELL_TYPE_ERROR);
 288             }
 289         }
 290         return retval;
 291     }
 292
 293     /**
 294      * Dev. Note: Internal evaluate must be passed only a formula cell
 295      * else a runtime exception will be thrown somewhere inside the method.
 296      * (Hence this is a private method.)
 297      */
 298     private static ValueEval internalEvaluate(HSSFCell srcCell, HSSFRow srcRow, HSSFSheet sheet, HSSFWorkbook workbook) {
 299         int srcRowNum = srcRow.getRowNum();
 300         short srcColNum = srcCell.getCellNum();
 301
 302
 303         EvaluationCycleDetector tracker = EvaluationCycleDetectorManager.getTracker();
 304
 305         if(!tracker.startEvaluate(workbook, sheet, srcRowNum, srcColNum)) {
 306             return ErrorEval.CIRCULAR_REF_ERROR;
 307         }
 308         try {
 309             return evaluateCell(workbook, sheet, srcRowNum, srcColNum, srcCell.getCellFormula());
 310         } finally {
 311             tracker.endEvaluate(workbook, sheet, srcRowNum, srcColNum);
 312         }
 313     }
 314     private static ValueEval evaluateCell(HSSFWorkbook workbook, HSSFSheet sheet,
 315             int srcRowNum, short srcColNum, String cellFormulaText) {
 316         FormulaParser parser = new FormulaParser(cellFormulaText, workbook.getWorkbook());
 317         parser.parse();
 318         Ptg[] ptgs = parser.getRPNPtg();
 319         // -- parsing over --
 320
 321
 322         Stack stack = new Stack();
 323         for (int i = 0, iSize = ptgs.length; i < iSize; i++) {
 324
 325             // since we don't know how to handle these yet :(
 326             Ptg ptg = ptgs[i];
 327             if (ptg instanceof ControlPtg) { continue; }
 328             if (ptg instanceof MemErrPtg) { continue; }
 329             if (ptg instanceof MissingArgPtg) { continue; }
 330             if (ptg instanceof NamePtg) {
 331                 // named ranges, macro functions
 332                 NamePtg namePtg = (NamePtg) ptg;
 333                 stack.push(new NameEval(namePtg.getIndex()));
 334                 continue;
 335             }
 336             if (ptg instanceof NameXPtg) {
 337                 // TODO - external functions
 338                 continue;
 339             }
 340             if (ptg instanceof UnknownPtg) { continue; }
 341
 342             if (ptg instanceof OperationPtg) {
 343                 OperationPtg optg = (OperationPtg) ptg;
 344
 345                 // parens can be ignored since we have RPN tokens
 346                 if (optg instanceof ParenthesisPtg) { continue; }
 347                 if (optg instanceof AttrPtg) { continue; }
 348                 if (optg instanceof UnionPtg) { continue; }
 349
 350                 OperationEval operation = OperationEvaluatorFactory.create(optg);
 351
 352                 int numops = operation.getNumberOfOperands();
 353                 Eval[] ops = new Eval[numops];
 354
 355                 // storing the ops in reverse order since they are popping
 356                 for (int j = numops - 1; j >= 0; j--) {
 357                     Eval p = (Eval) stack.pop();
 358                     ops[j] = p;
 359                 }
 360                 Eval opresult = invokeOperation(operation, ops, srcRowNum, srcColNum, workbook, sheet);
 361                 stack.push(opresult);
 362             }
 363             else if (ptg instanceof ReferencePtg) {
 364                 ReferencePtg refPtg = (ReferencePtg) ptg;
 365                 int colIx = refPtg.getColumn();
 366                 int rowIx = refPtg.getRow();
 367                 HSSFRow row = sheet.getRow(rowIx);
 368                 HSSFCell cell = (row != null) ? row.getCell(colIx) : null;
 369                 stack.push(createRef2DEval(refPtg, cell, row, sheet, workbook));
 370             }
 371             else if (ptg instanceof Ref3DPtg) {
 372                 Ref3DPtg refPtg = (Ref3DPtg) ptg;
 373                 int colIx = refPtg.getColumn();
 374                 int rowIx = refPtg.getRow();
 375                 Workbook wb = workbook.getWorkbook();
 376                 HSSFSheet xsheet = workbook.getSheetAt(wb.getSheetIndexFromExternSheetIndex(refPtg.getExternSheetIndex()));
 377                 HSSFRow row = xsheet.getRow(rowIx);
 378                 HSSFCell cell = (row != null) ? row.getCell(colIx) : null;
 379                 stack.push(createRef3DEval(refPtg, cell, row, xsheet, workbook));
 380             }
 381             else if (ptg instanceof AreaPtg) {
 382                 AreaPtg ap = (AreaPtg) ptg;
 383                 AreaEval ae = evaluateAreaPtg(sheet, workbook, ap);
 384                 stack.push(ae);
 385             }
 386             else if (ptg instanceof Area3DPtg) {
 387                 Area3DPtg a3dp = (Area3DPtg) ptg;
 388                 AreaEval ae = evaluateArea3dPtg(workbook, a3dp);
 389                 stack.push(ae);
 390             }
 391             else {
 392                 Eval ptgEval = getEvalForPtg(ptg);
 393                 stack.push(ptgEval);
 394             }
 395         }
 396
 397         ValueEval value = ((ValueEval) stack.pop());
 398         if (!stack.isEmpty()) {
 399             throw new IllegalStateException("evaluation stack not empty");
 400         }
 401         value = dereferenceValue(value, srcRowNum, srcColNum);
 402         if (value instanceof BlankEval) {
 403                 // Note Excel behaviour here. A blank final final value is converted to zero.
 404             return NumberEval.ZERO;
 405             // Formulas _never_ evaluate to blank.  If a formula appears to have evaluated to
 406             // blank, the actual value is empty string. This can be verified with ISBLANK().
 407         }
 408         return value;
 409     }
 410
 411     /**
 412      * Dereferences a single value from any AreaEval or RefEval evaluation result.
 413      * If the supplied evaluationResult is just a plain value, it is returned as-is.
 414          * @return a <tt>NumberEval</tt>, <tt>StringEval</tt>, <tt>BoolEval</tt>,
 415          *  <tt>BlankEval</tt> or <tt>ErrorEval</tt>. Never <code>null</code>.
 416      */
 417     private static ValueEval dereferenceValue(ValueEval evaluationResult, int srcRowNum, short srcColNum) {
 418         if (evaluationResult instanceof RefEval) {
 419             RefEval rv = (RefEval) evaluationResult;
 420             return rv.getInnerValueEval();
 421         }
 422         if (evaluationResult instanceof AreaEval) {
 423             AreaEval ae = (AreaEval) evaluationResult;
 424             if (ae.isRow()) {
 425                 if(ae.isColumn()) {
 426                     return ae.getValues()[0];
 427                 }
 428                 return ae.getValueAt(ae.getFirstRow(), srcColNum);
 429             }
 430             if (ae.isColumn()) {
 431                 return ae.getValueAt(srcRowNum, ae.getFirstColumn());
 432             }
 433             return ErrorEval.VALUE_INVALID;
 434         }
 435         return evaluationResult;
 436     }
 437
 438     private static Eval invokeOperation(OperationEval operation, Eval[] ops, int srcRowNum, short srcColNum,
 439             HSSFWorkbook workbook, HSSFSheet sheet) {
 440
 441         if(operation instanceof FunctionEval) {
 442             FunctionEval fe = (FunctionEval) operation;
 443             if(fe.isFreeRefFunction()) {
 444                 return fe.getFreeRefFunction().evaluate(ops, srcRowNum, srcColNum, workbook, sheet);
 445             }
 446         }
 447         return operation.evaluate(ops, srcRowNum, srcColNum);
 448     }
 449
 450     public static AreaEval evaluateAreaPtg(HSSFSheet sheet, HSSFWorkbook workbook, AreaPtg ap) {
 451         int row0 = ap.getFirstRow();
 452         int col0 = ap.getFirstColumn();
 453         int row1 = ap.getLastRow();
 454         int col1 = ap.getLastColumn();
 455
 456         // If the last row is -1, then the
 457         //  reference is for the rest of the column
 458         // (eg C:C)
 459         // TODO: Handle whole column ranges properly
 460         if(row1 == -1 && row0 >= 0) {
 461             row1 = (short)sheet.getLastRowNum();
 462         }
 463         ValueEval[] values = evalArea(workbook, sheet, row0, col0, row1, col1);
 464         return new Area2DEval(ap, values);
 465     }
 466
 467     public static AreaEval evaluateArea3dPtg(HSSFWorkbook workbook, Area3DPtg a3dp) {
 468         int row0 = a3dp.getFirstRow();
 469         int col0 = a3dp.getFirstColumn();
 470         int row1 = a3dp.getLastRow();
 471         int col1 = a3dp.getLastColumn();
 472         Workbook wb = workbook.getWorkbook();
 473         HSSFSheet xsheet = workbook.getSheetAt(wb.getSheetIndexFromExternSheetIndex(a3dp.getExternSheetIndex()));
 474
 475         // If the last row is -1, then the
 476         //  reference is for the rest of the column
 477         // (eg C:C)
 478         // TODO: Handle whole column ranges properly
 479         if(row1 == -1 && row0 >= 0) {
 480             row1 = (short)xsheet.getLastRowNum();
 481         }
 482
 483         ValueEval[] values = evalArea(workbook, xsheet, row0, col0, row1, col1);
 484         return new Area3DEval(a3dp, values);
 485     }
 486
 487     private static ValueEval[] evalArea(HSSFWorkbook workbook, HSSFSheet sheet,
 488                 int row0, int col0, int row1, int col1) {
 489         ValueEval[] values = new ValueEval[(row1 - row0 + 1) * (col1 - col0 + 1)];
 490         for (int x = row0; sheet != null && x < row1 + 1; x++) {
 491             HSSFRow row = sheet.getRow(x);
 492             for (int y = col0; y < col1 + 1; y++) {
 493                 ValueEval cellEval;
 494                 if(row == null) {
 495                         cellEval = BlankEval.INSTANCE;
 496                 } else {
 497                         cellEval = getEvalForCell(row.getCell(y), row, sheet, workbook);
 498                 }
 499                                 values[(x - row0) * (col1 - col0 + 1) + (y - col0)] = cellEval;
 500             }
 501         }
 502         return values;
 503     }
 504
 505     /**
 506      * returns an appropriate Eval impl instance for the Ptg. The Ptg must be
 507      * one of: Area3DPtg, AreaPtg, ReferencePtg, Ref3DPtg, IntPtg, NumberPtg,
 508      * StringPtg, BoolPtg <br/>special Note: OperationPtg subtypes cannot be
 509      * passed here!
 510      *
 511      * @param ptg
 512      */
 513     protected static Eval getEvalForPtg(Ptg ptg) {
 514         Eval retval = null;
 515
 516         Class clazz = (Class) VALUE_EVALS_MAP.get(ptg.getClass());
 517         try {
 518             if (ptg instanceof Area3DPtg) {
 519                 Constructor constructor = clazz.getConstructor(AREA3D_CONSTRUCTOR_CLASS_ARRAY);
 520                 retval = (OperationEval) constructor.newInstance(new Ptg[] { ptg });
 521             }
 522             else if (ptg instanceof AreaPtg) {
 523                 Constructor constructor = clazz.getConstructor(AREA3D_CONSTRUCTOR_CLASS_ARRAY);
 524                 retval = (OperationEval) constructor.newInstance(new Ptg[] { ptg });
 525             }
 526             else if (ptg instanceof ReferencePtg) {
 527                 Constructor constructor = clazz.getConstructor(REFERENCE_CONSTRUCTOR_CLASS_ARRAY);
 528                 retval = (OperationEval) constructor.newInstance(new Ptg[] { ptg });
 529             }
 530             else if (ptg instanceof Ref3DPtg) {
 531                 Constructor constructor = clazz.getConstructor(REF3D_CONSTRUCTOR_CLASS_ARRAY);
 532                 retval = (OperationEval) constructor.newInstance(new Ptg[] { ptg });
 533             }
 534             else {
 535                 if (ptg instanceof IntPtg || ptg instanceof NumberPtg || ptg instanceof StringPtg
 536                         || ptg instanceof BoolPtg) {
 537                     Constructor constructor = clazz.getConstructor(VALUE_CONTRUCTOR_CLASS_ARRAY);
 538                     retval = (ValueEval) constructor.newInstance(new Ptg[] { ptg });
 539                 }
 540             }
 541         }
 542         catch (Exception e) {
 543             throw new RuntimeException("Fatal Error: ", e);
 544         }
 545         return retval;
 546
 547     }
 548
 549     /**
 550      * Given a cell, find its type and from that create an appropriate ValueEval
 551      * impl instance and return that. Since the cell could be an external
 552      * reference, we need the sheet that this belongs to.
 553      * Non existent cells are treated as empty.
 554      * @param cell
 555      * @param sheet
 556      * @param workbook
 557      */
 558     protected static ValueEval getEvalForCell(HSSFCell cell, HSSFRow row, HSSFSheet sheet, HSSFWorkbook workbook) {
 559
 560         if (cell == null) {
 561             return BlankEval.INSTANCE;
 562         }
 563         switch (cell.getCellType()) {
 564             case HSSFCell.CELL_TYPE_NUMERIC:
 565                 return new NumberEval(cell.getNumericCellValue());
 566             case HSSFCell.CELL_TYPE_STRING:
 567                 return new StringEval(cell.getRichStringCellValue().getString());
 568             case HSSFCell.CELL_TYPE_FORMULA:
 569                 return internalEvaluate(cell, row, sheet, workbook);
 570             case HSSFCell.CELL_TYPE_BOOLEAN:
 571                 return BoolEval.valueOf(cell.getBooleanCellValue());
 572             case HSSFCell.CELL_TYPE_BLANK:
 573                 return BlankEval.INSTANCE;
 574             case HSSFCell.CELL_TYPE_ERROR:
 575                 return ErrorEval.valueOf(cell.getErrorCellValue());
 576         }
 577         throw new RuntimeException("Unexpected cell type (" + cell.getCellType() + ")");
 578     }
 579
 580     /**
 581      * Creates a Ref2DEval for ReferencePtg.
 582      * Non existent cells are treated as RefEvals containing BlankEval.
 583      */
 584     private static Ref2DEval createRef2DEval(ReferencePtg ptg, HSSFCell cell,
 585             HSSFRow row, HSSFSheet sheet, HSSFWorkbook workbook) {
 586         if (cell == null) {
 587             return new Ref2DEval(ptg, BlankEval.INSTANCE);
 588         }
 589
 590         switch (cell.getCellType()) {
 591             case HSSFCell.CELL_TYPE_NUMERIC:
 592                 return new Ref2DEval(ptg, new NumberEval(cell.getNumericCellValue()));
 593             case HSSFCell.CELL_TYPE_STRING:
 594                 return new Ref2DEval(ptg, new StringEval(cell.getRichStringCellValue().getString()));
 595             case HSSFCell.CELL_TYPE_FORMULA:
 596                 return new Ref2DEval(ptg, internalEvaluate(cell, row, sheet, workbook));
 597             case HSSFCell.CELL_TYPE_BOOLEAN:
 598                 return new Ref2DEval(ptg, BoolEval.valueOf(cell.getBooleanCellValue()));
 599             case HSSFCell.CELL_TYPE_BLANK:
 600                 return new Ref2DEval(ptg, BlankEval.INSTANCE);
 601             case HSSFCell.CELL_TYPE_ERROR:
 602                 return new  Ref2DEval(ptg, ErrorEval.valueOf(cell.getErrorCellValue()));
 603         }
 604         throw new RuntimeException("Unexpected cell type (" + cell.getCellType() + ")");
 605     }
 606
 607     /**
 608      * create a Ref3DEval for Ref3DPtg.
 609      */
 610     private static Ref3DEval createRef3DEval(Ref3DPtg ptg, HSSFCell cell,
 611             HSSFRow row, HSSFSheet sheet, HSSFWorkbook workbook) {
 612         if (cell == null) {
 613             return new Ref3DEval(ptg, BlankEval.INSTANCE);
 614         }
 615         switch (cell.getCellType()) {
 616             case HSSFCell.CELL_TYPE_NUMERIC:
 617                 return new Ref3DEval(ptg, new NumberEval(cell.getNumericCellValue()));
 618             case HSSFCell.CELL_TYPE_STRING:
 619                 return new Ref3DEval(ptg, new StringEval(cell.getRichStringCellValue().getString()));
 620             case HSSFCell.CELL_TYPE_FORMULA:
 621                 return new Ref3DEval(ptg, internalEvaluate(cell, row, sheet, workbook));
 622             case HSSFCell.CELL_TYPE_BOOLEAN:
 623                 return new Ref3DEval(ptg, BoolEval.valueOf(cell.getBooleanCellValue()));
 624             case HSSFCell.CELL_TYPE_BLANK:
 625                 return new Ref3DEval(ptg, BlankEval.INSTANCE);
 626             case HSSFCell.CELL_TYPE_ERROR:
 627                 return new Ref3DEval(ptg, ErrorEval.valueOf(cell.getErrorCellValue()));
 628         }
 629         throw new RuntimeException("Unexpected cell type (" + cell.getCellType() + ")");
 630     }
 631
 632     /**
 633      * Mimics the 'data view' of a cell. This allows formula evaluator
 634      * to return a CellValue instead of precasting the value to String
 635      * or Number or boolean type.
 636      * @author Amol S. Deshmukh &lt; amolweb at ya hoo dot com &gt;
 637      */
 638     public static final class CellValue {
 639         private int cellType;
 640         private HSSFRichTextString richTextStringValue;
 641         private double numberValue;
 642         private boolean booleanValue;
 643         private byte errorValue;
 644
 645         /**
 646          * CellType should be one of the types defined in HSSFCell
 647          * @param cellType
 648          */
 649         public CellValue(int cellType) {
 650             super();
 651             this.cellType = cellType;
 652         }
 653         /**
 654          * @return Returns the booleanValue.
 655          */
 656         public boolean getBooleanValue() {
 657             return booleanValue;
 658         }
 659         /**
 660          * @param booleanValue The booleanValue to set.
 661          */
 662         public void setBooleanValue(boolean booleanValue) {
 663             this.booleanValue = booleanValue;
 664         }
 665         /**
 666          * @return Returns the numberValue.
 667          */
 668         public double getNumberValue() {
 669             return numberValue;
 670         }
 671         /**
 672          * @param numberValue The numberValue to set.
 673          */
 674         public void setNumberValue(double numberValue) {
 675             this.numberValue = numberValue;
 676         }
 677         /**
 678          * @return Returns the stringValue. This method is deprecated, use
 679          * getRichTextStringValue instead
 680          * @deprecated
 681          */
 682         public String getStringValue() {
 683             return richTextStringValue.getString();
 684         }
 685         /**
 686          * @param stringValue The stringValue to set. This method is deprecated, use
 687          * getRichTextStringValue instead.
 688          * @deprecated
 689          */
 690         public void setStringValue(String stringValue) {
 691             this.richTextStringValue = new HSSFRichTextString(stringValue);
 692         }
 693         /**
 694          * @return Returns the cellType.
 695          */
 696         public int getCellType() {
 697             return cellType;
 698         }
 699         /**
 700          * @return Returns the errorValue.
 701          */
 702         public byte getErrorValue() {
 703             return errorValue;
 704         }
 705         /**
 706          * @param errorValue The errorValue to set.
 707          */
 708         public void setErrorValue(byte errorValue) {
 709             this.errorValue = errorValue;
 710         }
 711         /**
 712          * @return Returns the richTextStringValue.
 713          */
 714         public HSSFRichTextString getRichTextStringValue() {
 715             return richTextStringValue;
 716         }
 717         /**
 718          * @param richTextStringValue The richTextStringValue to set.
 719          */
 720         public void setRichTextStringValue(HSSFRichTextString richTextStringValue) {
 721             this.richTextStringValue = richTextStringValue;
 722         }
 723     }
 724
 725     /**
 726      * debug method
 727      *
 728      * @param formula
 729      * @param sheet
 730      * @param workbook
 731      */
 732     void inspectPtgs(String formula) {
 733         FormulaParser fp = new FormulaParser(formula, workbook.getWorkbook());
 734         fp.parse();
 735         Ptg[] ptgs = fp.getRPNPtg();
 736         System.out.println("<ptg-group>");
 737         for (int i = 0, iSize = ptgs.length; i < iSize; i++) {
 738             System.out.println("<ptg>");
 739             System.out.println(ptgs[i]);
 740             if (ptgs[i] instanceof OperationPtg) {
 741                 System.out.println("numoperands: " + ((OperationPtg) ptgs[i]).getNumberOfOperands());
 742             }
 743             System.out.println("</ptg>");
 744         }
 745         System.out.println("</ptg-group>");
 746     }
 747
 748 }