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