001: /*
002: * Licensed to the Apache Software Foundation (ASF) under one or more
003: * contributor license agreements. See the NOTICE file distributed with
004: * this work for additional information regarding copyright ownership.
005: * The ASF licenses this file to You under the Apache License, Version 2.0
006: * (the "License"); you may not use this file except in compliance with
007: * the License. You may obtain a copy of the License at
008: *
009: * http://www.apache.org/licenses/LICENSE-2.0
010: *
011: * Unless required by applicable law or agreed to in writing, software
012: * distributed under the License is distributed on an "AS IS" BASIS,
013: * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
014: * See the License for the specific language governing permissions and
015: * limitations under the License.
016: */
017: /*
018: * Created on May 5, 2005
019: *
020: */
021: package org.apache.poi.hssf.usermodel;
022:
023: import java.lang.reflect.Constructor;
024: import java.util.HashMap;
025: import java.util.Map;
026: import java.util.Stack;
027:
028: import org.apache.poi.hssf.model.FormulaParser;
029: import org.apache.poi.hssf.model.Workbook;
030: import org.apache.poi.hssf.record.formula.AddPtg;
031: import org.apache.poi.hssf.record.formula.Area3DPtg;
032: import org.apache.poi.hssf.record.formula.AreaPtg;
033: import org.apache.poi.hssf.record.formula.AttrPtg;
034: import org.apache.poi.hssf.record.formula.BoolPtg;
035: import org.apache.poi.hssf.record.formula.ConcatPtg;
036: import org.apache.poi.hssf.record.formula.ControlPtg;
037: import org.apache.poi.hssf.record.formula.DividePtg;
038: import org.apache.poi.hssf.record.formula.EqualPtg;
039: import org.apache.poi.hssf.record.formula.FuncPtg;
040: import org.apache.poi.hssf.record.formula.FuncVarPtg;
041: import org.apache.poi.hssf.record.formula.GreaterEqualPtg;
042: import org.apache.poi.hssf.record.formula.GreaterThanPtg;
043: import org.apache.poi.hssf.record.formula.IntPtg;
044: import org.apache.poi.hssf.record.formula.LessEqualPtg;
045: import org.apache.poi.hssf.record.formula.LessThanPtg;
046: import org.apache.poi.hssf.record.formula.MemErrPtg;
047: import org.apache.poi.hssf.record.formula.MissingArgPtg;
048: import org.apache.poi.hssf.record.formula.MultiplyPtg;
049: import org.apache.poi.hssf.record.formula.NamePtg;
050: import org.apache.poi.hssf.record.formula.NameXPtg;
051: import org.apache.poi.hssf.record.formula.NotEqualPtg;
052: import org.apache.poi.hssf.record.formula.NumberPtg;
053: import org.apache.poi.hssf.record.formula.OperationPtg;
054: import org.apache.poi.hssf.record.formula.ParenthesisPtg;
055: import org.apache.poi.hssf.record.formula.PowerPtg;
056: import org.apache.poi.hssf.record.formula.Ptg;
057: import org.apache.poi.hssf.record.formula.Ref3DPtg;
058: import org.apache.poi.hssf.record.formula.ReferencePtg;
059: import org.apache.poi.hssf.record.formula.StringPtg;
060: import org.apache.poi.hssf.record.formula.SubtractPtg;
061: import org.apache.poi.hssf.record.formula.UnaryMinusPtg;
062: import org.apache.poi.hssf.record.formula.UnaryPlusPtg;
063: import org.apache.poi.hssf.record.formula.UnionPtg;
064: import org.apache.poi.hssf.record.formula.UnknownPtg;
065: import org.apache.poi.hssf.record.formula.eval.AddEval;
066: import org.apache.poi.hssf.record.formula.eval.Area2DEval;
067: import org.apache.poi.hssf.record.formula.eval.Area3DEval;
068: import org.apache.poi.hssf.record.formula.eval.AreaEval;
069: import org.apache.poi.hssf.record.formula.eval.BlankEval;
070: import org.apache.poi.hssf.record.formula.eval.BoolEval;
071: import org.apache.poi.hssf.record.formula.eval.ConcatEval;
072: import org.apache.poi.hssf.record.formula.eval.DivideEval;
073: import org.apache.poi.hssf.record.formula.eval.EqualEval;
074: import org.apache.poi.hssf.record.formula.eval.ErrorEval;
075: import org.apache.poi.hssf.record.formula.eval.Eval;
076: import org.apache.poi.hssf.record.formula.eval.FuncVarEval;
077: import org.apache.poi.hssf.record.formula.eval.GreaterEqualEval;
078: import org.apache.poi.hssf.record.formula.eval.GreaterThanEval;
079: import org.apache.poi.hssf.record.formula.eval.LessEqualEval;
080: import org.apache.poi.hssf.record.formula.eval.LessThanEval;
081: import org.apache.poi.hssf.record.formula.eval.MultiplyEval;
082: import org.apache.poi.hssf.record.formula.eval.NotEqualEval;
083: import org.apache.poi.hssf.record.formula.eval.NumberEval;
084: import org.apache.poi.hssf.record.formula.eval.OperationEval;
085: import org.apache.poi.hssf.record.formula.eval.PowerEval;
086: import org.apache.poi.hssf.record.formula.eval.Ref2DEval;
087: import org.apache.poi.hssf.record.formula.eval.Ref3DEval;
088: import org.apache.poi.hssf.record.formula.eval.RefEval;
089: import org.apache.poi.hssf.record.formula.eval.StringEval;
090: import org.apache.poi.hssf.record.formula.eval.SubtractEval;
091: import org.apache.poi.hssf.record.formula.eval.UnaryMinusEval;
092: import org.apache.poi.hssf.record.formula.eval.UnaryPlusEval;
093: import org.apache.poi.hssf.record.formula.eval.ValueEval;
094: import org.apache.poi.hssf.usermodel.HSSFSheet;
095:
096: /**
097: * @author Amol S. Deshmukh < amolweb at ya hoo dot com >
098: *
099: * Limitations: Unfortunately, cyclic references will cause stackoverflow
100: * exception
101: */
102: public class HSSFFormulaEvaluator {
103:
104: // params to lookup the right constructor using reflection
105: private static final Class[] OPERATION_CONSTRUCTOR_CLASS_ARRAY = new Class[] { Ptg.class };
106:
107: private static final Class[] VALUE_CONTRUCTOR_CLASS_ARRAY = new Class[] { Ptg.class };
108:
109: private static final Class[] AREA3D_CONSTRUCTOR_CLASS_ARRAY = new Class[] {
110: Ptg.class, ValueEval[].class };
111:
112: private static final Class[] REFERENCE_CONSTRUCTOR_CLASS_ARRAY = new Class[] {
113: Ptg.class, ValueEval.class };
114:
115: private static final Class[] REF3D_CONSTRUCTOR_CLASS_ARRAY = new Class[] {
116: Ptg.class, ValueEval.class };
117:
118: // Maps for mapping *Eval to *Ptg
119: private static final Map VALUE_EVALS_MAP = new HashMap();
120:
121: private static final Map OPERATION_EVALS_MAP = new HashMap();
122:
123: /*
124: * Following is the mapping between the Ptg tokens returned
125: * by the FormulaParser and the *Eval classes that are used
126: * by the FormulaEvaluator
127: */
128: static {
129: VALUE_EVALS_MAP.put(BoolPtg.class, BoolEval.class);
130: VALUE_EVALS_MAP.put(IntPtg.class, NumberEval.class);
131: VALUE_EVALS_MAP.put(NumberPtg.class, NumberEval.class);
132: VALUE_EVALS_MAP.put(StringPtg.class, StringEval.class);
133:
134: OPERATION_EVALS_MAP.put(AddPtg.class, AddEval.class);
135: OPERATION_EVALS_MAP.put(ConcatPtg.class, ConcatEval.class);
136: OPERATION_EVALS_MAP.put(DividePtg.class, DivideEval.class);
137: OPERATION_EVALS_MAP.put(EqualPtg.class, EqualEval.class);
138: //OPERATION_EVALS_MAP.put(ExpPtg.class, ExpEval.class); // TODO: check
139: // this
140: OPERATION_EVALS_MAP.put(FuncPtg.class, FuncVarEval.class); // TODO:
141: // check this
142: OPERATION_EVALS_MAP.put(FuncVarPtg.class, FuncVarEval.class);
143: OPERATION_EVALS_MAP.put(GreaterEqualPtg.class,
144: GreaterEqualEval.class);
145: OPERATION_EVALS_MAP.put(GreaterThanPtg.class,
146: GreaterThanEval.class);
147: OPERATION_EVALS_MAP
148: .put(LessEqualPtg.class, LessEqualEval.class);
149: OPERATION_EVALS_MAP.put(LessThanPtg.class, LessThanEval.class);
150: OPERATION_EVALS_MAP.put(MultiplyPtg.class, MultiplyEval.class);
151: OPERATION_EVALS_MAP.put(NotEqualPtg.class, NotEqualEval.class);
152: OPERATION_EVALS_MAP.put(PowerPtg.class, PowerEval.class);
153: OPERATION_EVALS_MAP.put(SubtractPtg.class, SubtractEval.class);
154: OPERATION_EVALS_MAP.put(UnaryMinusPtg.class,
155: UnaryMinusEval.class);
156: OPERATION_EVALS_MAP
157: .put(UnaryPlusPtg.class, UnaryPlusEval.class);
158:
159: }
160:
161: protected HSSFRow row;
162: protected HSSFSheet sheet;
163: protected HSSFWorkbook workbook;
164:
165: public HSSFFormulaEvaluator(HSSFSheet sheet, HSSFWorkbook workbook) {
166: this .sheet = sheet;
167: this .workbook = workbook;
168: }
169:
170: public void setCurrentRow(HSSFRow row) {
171: this .row = row;
172: }
173:
174: /**
175: * Returns an underlying FormulaParser, for the specified
176: * Formula String and HSSFWorkbook.
177: * This will allow you to generate the Ptgs yourself, if
178: * your needs are more complex than just having the
179: * formula evaluated.
180: */
181: public static FormulaParser getUnderlyingParser(
182: HSSFWorkbook workbook, String formula) {
183: return new FormulaParser(formula, workbook.getWorkbook());
184: }
185:
186: /**
187: * If cell contains a formula, the formula is evaluated and returned,
188: * else the CellValue simply copies the appropriate cell value from
189: * the cell and also its cell type. This method should be preferred over
190: * evaluateInCell() when the call should not modify the contents of the
191: * original cell.
192: * @param cell
193: */
194: public CellValue evaluate(HSSFCell cell) {
195: CellValue retval = null;
196: if (cell != null) {
197: switch (cell.getCellType()) {
198: case HSSFCell.CELL_TYPE_BLANK:
199: retval = new CellValue(HSSFCell.CELL_TYPE_BLANK);
200: break;
201: case HSSFCell.CELL_TYPE_BOOLEAN:
202: retval = new CellValue(HSSFCell.CELL_TYPE_BOOLEAN);
203: retval.setBooleanValue(cell.getBooleanCellValue());
204: break;
205: case HSSFCell.CELL_TYPE_ERROR:
206: retval = new CellValue(HSSFCell.CELL_TYPE_ERROR);
207: retval.setErrorValue(cell.getErrorCellValue());
208: break;
209: case HSSFCell.CELL_TYPE_FORMULA:
210: retval = getCellValueForEval(internalEvaluate(cell,
211: row, sheet, workbook));
212: break;
213: case HSSFCell.CELL_TYPE_NUMERIC:
214: retval = new CellValue(HSSFCell.CELL_TYPE_NUMERIC);
215: retval.setNumberValue(cell.getNumericCellValue());
216: break;
217: case HSSFCell.CELL_TYPE_STRING:
218: retval = new CellValue(HSSFCell.CELL_TYPE_STRING);
219: retval.setRichTextStringValue(cell
220: .getRichStringCellValue());
221: break;
222: }
223: }
224: return retval;
225: }
226:
227: /**
228: * If cell contains formula, it evaluates the formula, and puts the
229: * formula result back into the cell.
230: * Else if cell does not contain formula, this method leaves the cell
231: * unchanged. Note that the same instance of HSSFCell is returned to
232: * allow chained calls like:
233: * <pre>
234: * int evaluatedCellType = evaluator.evaluateInCell(cell).getCellType();
235: * </pre>
236: * @param cell
237: */
238: public HSSFCell evaluateInCell(HSSFCell cell) {
239: if (cell != null) {
240: switch (cell.getCellType()) {
241: case HSSFCell.CELL_TYPE_FORMULA:
242: CellValue cv = getCellValueForEval(internalEvaluate(
243: cell, row, sheet, workbook));
244: switch (cv.getCellType()) {
245: case HSSFCell.CELL_TYPE_BOOLEAN:
246: cell.setCellType(HSSFCell.CELL_TYPE_BOOLEAN);
247: cell.setCellValue(cv.getBooleanValue());
248: break;
249: case HSSFCell.CELL_TYPE_ERROR:
250: cell.setCellType(HSSFCell.CELL_TYPE_ERROR);
251: cell.setCellValue(cv.getErrorValue());
252: break;
253: case HSSFCell.CELL_TYPE_NUMERIC:
254: cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
255: cell.setCellValue(cv.getNumberValue());
256: break;
257: case HSSFCell.CELL_TYPE_STRING:
258: cell.setCellType(HSSFCell.CELL_TYPE_STRING);
259: cell.setCellValue(cv.getRichTextStringValue());
260: break;
261: case HSSFCell.CELL_TYPE_BLANK:
262: break;
263: case HSSFCell.CELL_TYPE_FORMULA: // this will never happen, we have already evaluated the formula
264: break;
265: }
266: }
267: }
268: return cell;
269: }
270:
271: /**
272: * Returns a CellValue wrapper around the supplied ValueEval instance.
273: * @param eval
274: */
275: protected static CellValue getCellValueForEval(ValueEval eval) {
276: CellValue retval = null;
277: if (eval != null) {
278: if (eval instanceof NumberEval) {
279: NumberEval ne = (NumberEval) eval;
280: retval = new CellValue(HSSFCell.CELL_TYPE_NUMERIC);
281: retval.setNumberValue(ne.getNumberValue());
282: } else if (eval instanceof BoolEval) {
283: BoolEval be = (BoolEval) eval;
284: retval = new CellValue(HSSFCell.CELL_TYPE_BOOLEAN);
285: retval.setBooleanValue(be.getBooleanValue());
286: } else if (eval instanceof StringEval) {
287: StringEval ne = (StringEval) eval;
288: retval = new CellValue(HSSFCell.CELL_TYPE_STRING);
289: retval.setStringValue(ne.getStringValue());
290: } else if (eval instanceof BlankEval) {
291: retval = new CellValue(HSSFCell.CELL_TYPE_BLANK);
292: } else {
293: retval = new CellValue(HSSFCell.CELL_TYPE_ERROR);
294: }
295: }
296: return retval;
297: }
298:
299: /**
300: * Dev. Note: Internal evaluate must be passed only a formula cell
301: * else a runtime exception will be thrown somewhere inside the method.
302: * (Hence this is a private method.)
303: *
304: * @param srcCell
305: * @param srcRow
306: * @param sheet
307: * @param workbook
308: */
309: protected static ValueEval internalEvaluate(HSSFCell srcCell,
310: HSSFRow srcRow, HSSFSheet sheet, HSSFWorkbook workbook) {
311: int srcRowNum = srcRow.getRowNum();
312: short srcColNum = srcCell.getCellNum();
313: FormulaParser parser = new FormulaParser(srcCell
314: .getCellFormula(), workbook.getWorkbook());
315: parser.parse();
316: Ptg[] ptgs = parser.getRPNPtg();
317: // -- parsing over --
318:
319: Stack stack = new Stack();
320: for (int i = 0, iSize = ptgs.length; i < iSize; i++) {
321:
322: // since we dont know how to handle these yet :(
323: if (ptgs[i] instanceof ControlPtg) {
324: continue;
325: }
326: if (ptgs[i] instanceof MemErrPtg) {
327: continue;
328: }
329: if (ptgs[i] instanceof MissingArgPtg) {
330: continue;
331: }
332: if (ptgs[i] instanceof NamePtg) {
333: continue;
334: }
335: if (ptgs[i] instanceof NameXPtg) {
336: continue;
337: }
338: if (ptgs[i] instanceof UnknownPtg) {
339: continue;
340: }
341:
342: if (ptgs[i] instanceof OperationPtg) {
343: OperationPtg optg = (OperationPtg) ptgs[i];
344:
345: // parens can be ignored since we have RPN tokens
346: if (optg instanceof ParenthesisPtg) {
347: continue;
348: }
349: if (optg instanceof AttrPtg) {
350: continue;
351: }
352: if (optg instanceof UnionPtg) {
353: continue;
354: }
355:
356: OperationEval operation = (OperationEval) getOperationEvalForPtg(optg);
357:
358: int numops = operation.getNumberOfOperands();
359: Eval[] ops = new Eval[numops];
360:
361: // storing the ops in reverse order since they are popping
362: for (int j = numops - 1; j >= 0; j--) {
363: Eval p = (Eval) stack.pop();
364: ops[j] = p;
365: }
366: Eval opresult = operation.evaluate(ops, srcRowNum,
367: srcColNum);
368: stack.push(opresult);
369: } else if (ptgs[i] instanceof ReferencePtg) {
370: ReferencePtg ptg = (ReferencePtg) ptgs[i];
371: short colnum = ptg.getColumn();
372: short rownum = ptg.getRow();
373: HSSFRow row = sheet.getRow(rownum);
374: HSSFCell cell = (row != null) ? row.getCell(colnum)
375: : null;
376: pushRef2DEval(ptg, stack, cell, row, sheet, workbook);
377: } else if (ptgs[i] instanceof Ref3DPtg) {
378: Ref3DPtg ptg = (Ref3DPtg) ptgs[i];
379: short colnum = ptg.getColumn();
380: short rownum = ptg.getRow();
381: Workbook wb = workbook.getWorkbook();
382: HSSFSheet xsheet = workbook.getSheetAt(wb
383: .getSheetIndexFromExternSheetIndex(ptg
384: .getExternSheetIndex()));
385: HSSFRow row = xsheet.getRow(rownum);
386: HSSFCell cell = (row != null) ? row.getCell(colnum)
387: : null;
388: pushRef3DEval(ptg, stack, cell, row, xsheet, workbook);
389: } else if (ptgs[i] instanceof AreaPtg) {
390: AreaPtg ap = (AreaPtg) ptgs[i];
391: short row0 = ap.getFirstRow();
392: short col0 = ap.getFirstColumn();
393: short row1 = ap.getLastRow();
394: short col1 = ap.getLastColumn();
395: ValueEval[] values = new ValueEval[(row1 - row0 + 1)
396: * (col1 - col0 + 1)];
397: for (short x = row0; sheet != null && x < row1 + 1; x++) {
398: HSSFRow row = sheet.getRow(x);
399: for (short y = col0; row != null && y < col1 + 1; y++) {
400: values[(x - row0) * (col1 - col0 + 1)
401: + (y - col0)] = getEvalForCell(row
402: .getCell(y), row, sheet, workbook);
403: }
404: }
405: AreaEval ae = new Area2DEval(ap, values);
406: stack.push(ae);
407: } else if (ptgs[i] instanceof Area3DPtg) {
408: Area3DPtg a3dp = (Area3DPtg) ptgs[i];
409: short row0 = a3dp.getFirstRow();
410: short col0 = a3dp.getFirstColumn();
411: short row1 = a3dp.getLastRow();
412: short col1 = a3dp.getLastColumn();
413: Workbook wb = workbook.getWorkbook();
414: HSSFSheet xsheet = workbook.getSheetAt(wb
415: .getSheetIndexFromExternSheetIndex(a3dp
416: .getExternSheetIndex()));
417: ValueEval[] values = new ValueEval[(row1 - row0 + 1)
418: * (col1 - col0 + 1)];
419: for (short x = row0; xsheet != null && x < row1 + 1; x++) {
420: HSSFRow row = xsheet.getRow(x);
421: for (short y = col0; row != null && y < col1 + 1; y++) {
422: values[(x - row0) * (col1 - col0 + 1)
423: + (y - col0)] = getEvalForCell(row
424: .getCell(y), row, xsheet, workbook);
425: }
426: }
427: AreaEval ae = new Area3DEval(a3dp, values);
428: stack.push(ae);
429: } else {
430: Eval ptgEval = getEvalForPtg(ptgs[i]);
431: stack.push(ptgEval);
432: }
433: }
434: ValueEval value = ((ValueEval) stack.pop());
435: if (value instanceof RefEval) {
436: RefEval rv = (RefEval) value;
437: value = rv.getInnerValueEval();
438: } else if (value instanceof AreaEval) {
439: AreaEval ae = (AreaEval) value;
440: if (ae.isRow())
441: value = ae.getValueAt(ae.getFirstRow(), srcColNum);
442: else if (ae.isColumn())
443: value = ae.getValueAt(srcRowNum, ae.getFirstColumn());
444: else
445: value = ErrorEval.VALUE_INVALID;
446: }
447: return value;
448: }
449:
450: /**
451: * returns the OperationEval concrete impl instance corresponding
452: * to the suplied operationPtg
453: * @param ptg
454: */
455: protected static Eval getOperationEvalForPtg(OperationPtg ptg) {
456: Eval retval = null;
457:
458: Class clazz = (Class) OPERATION_EVALS_MAP.get(ptg.getClass());
459: try {
460: Constructor constructor = clazz
461: .getConstructor(OPERATION_CONSTRUCTOR_CLASS_ARRAY);
462: retval = (OperationEval) constructor
463: .newInstance(new Ptg[] { ptg });
464: } catch (Exception e) {
465: throw new RuntimeException("Fatal Error: ", e);
466: }
467: return retval;
468: }
469:
470: /**
471: * returns an appropriate Eval impl instance for the Ptg. The Ptg must be
472: * one of: Area3DPtg, AreaPtg, ReferencePtg, Ref3DPtg, IntPtg, NumberPtg,
473: * StringPtg, BoolPtg <br/>special Note: OperationPtg subtypes cannot be
474: * passed here!
475: *
476: * @param ptg
477: */
478: protected static Eval getEvalForPtg(Ptg ptg) {
479: Eval retval = null;
480:
481: Class clazz = (Class) VALUE_EVALS_MAP.get(ptg.getClass());
482: try {
483: if (ptg instanceof Area3DPtg) {
484: Constructor constructor = clazz
485: .getConstructor(AREA3D_CONSTRUCTOR_CLASS_ARRAY);
486: retval = (OperationEval) constructor
487: .newInstance(new Ptg[] { ptg });
488: } else if (ptg instanceof AreaPtg) {
489: Constructor constructor = clazz
490: .getConstructor(AREA3D_CONSTRUCTOR_CLASS_ARRAY);
491: retval = (OperationEval) constructor
492: .newInstance(new Ptg[] { ptg });
493: } else if (ptg instanceof ReferencePtg) {
494: Constructor constructor = clazz
495: .getConstructor(REFERENCE_CONSTRUCTOR_CLASS_ARRAY);
496: retval = (OperationEval) constructor
497: .newInstance(new Ptg[] { ptg });
498: } else if (ptg instanceof Ref3DPtg) {
499: Constructor constructor = clazz
500: .getConstructor(REF3D_CONSTRUCTOR_CLASS_ARRAY);
501: retval = (OperationEval) constructor
502: .newInstance(new Ptg[] { ptg });
503: } else {
504: if (ptg instanceof IntPtg || ptg instanceof NumberPtg
505: || ptg instanceof StringPtg
506: || ptg instanceof BoolPtg) {
507: Constructor constructor = clazz
508: .getConstructor(VALUE_CONTRUCTOR_CLASS_ARRAY);
509: retval = (ValueEval) constructor
510: .newInstance(new Ptg[] { ptg });
511: }
512: }
513: } catch (Exception e) {
514: throw new RuntimeException("Fatal Error: ", e);
515: }
516: return retval;
517:
518: }
519:
520: /**
521: * Given a cell, find its type and from that create an appropriate ValueEval
522: * impl instance and return that. Since the cell could be an external
523: * reference, we need the sheet that this belongs to.
524: * Non existent cells are treated as empty.
525: * @param cell
526: * @param sheet
527: * @param workbook
528: */
529: protected static ValueEval getEvalForCell(HSSFCell cell,
530: HSSFRow row, HSSFSheet sheet, HSSFWorkbook workbook) {
531: ValueEval retval = BlankEval.INSTANCE;
532: if (cell != null) {
533: switch (cell.getCellType()) {
534: case HSSFCell.CELL_TYPE_NUMERIC:
535: retval = new NumberEval(cell.getNumericCellValue());
536: break;
537: case HSSFCell.CELL_TYPE_STRING:
538: retval = new StringEval(cell.getRichStringCellValue()
539: .getString());
540: break;
541: case HSSFCell.CELL_TYPE_FORMULA:
542: retval = internalEvaluate(cell, row, sheet, workbook);
543: break;
544: case HSSFCell.CELL_TYPE_BOOLEAN:
545: retval = cell.getBooleanCellValue() ? BoolEval.TRUE
546: : BoolEval.FALSE;
547: break;
548: case HSSFCell.CELL_TYPE_BLANK:
549: retval = BlankEval.INSTANCE;
550: break;
551: case HSSFCell.CELL_TYPE_ERROR:
552: retval = ErrorEval.UNKNOWN_ERROR; // TODO: think about this...
553: break;
554: }
555: }
556: return retval;
557: }
558:
559: /**
560: * create a Ref2DEval for ReferencePtg and push it on the stack.
561: * Non existent cells are treated as RefEvals containing BlankEval.
562: * @param ptg
563: * @param stack
564: * @param cell
565: * @param sheet
566: * @param workbook
567: */
568: protected static void pushRef2DEval(ReferencePtg ptg, Stack stack,
569: HSSFCell cell, HSSFRow row, HSSFSheet sheet,
570: HSSFWorkbook workbook) {
571: if (cell != null)
572: switch (cell.getCellType()) {
573: case HSSFCell.CELL_TYPE_NUMERIC:
574: stack.push(new Ref2DEval(ptg, new NumberEval(cell
575: .getNumericCellValue()), false));
576: break;
577: case HSSFCell.CELL_TYPE_STRING:
578: stack.push(new Ref2DEval(ptg, new StringEval(cell
579: .getRichStringCellValue().getString()), false));
580: break;
581: case HSSFCell.CELL_TYPE_FORMULA:
582: stack.push(new Ref2DEval(ptg, internalEvaluate(cell,
583: row, sheet, workbook), true));
584: break;
585: case HSSFCell.CELL_TYPE_BOOLEAN:
586: stack.push(new Ref2DEval(ptg, cell
587: .getBooleanCellValue() ? BoolEval.TRUE
588: : BoolEval.FALSE, false));
589: break;
590: case HSSFCell.CELL_TYPE_BLANK:
591: stack
592: .push(new Ref2DEval(ptg, BlankEval.INSTANCE,
593: false));
594: break;
595: case HSSFCell.CELL_TYPE_ERROR:
596: stack.push(new Ref2DEval(ptg, ErrorEval.UNKNOWN_ERROR,
597: false)); // TODO: think abt this
598: break;
599: }
600: else {
601: stack.push(new Ref2DEval(ptg, BlankEval.INSTANCE, false));
602: }
603: }
604:
605: /**
606: * create a Ref3DEval for Ref3DPtg and push it on the stack.
607: *
608: * @param ptg
609: * @param stack
610: * @param cell
611: * @param sheet
612: * @param workbook
613: */
614: protected static void pushRef3DEval(Ref3DPtg ptg, Stack stack,
615: HSSFCell cell, HSSFRow row, HSSFSheet sheet,
616: HSSFWorkbook workbook) {
617: if (cell != null)
618: switch (cell.getCellType()) {
619: case HSSFCell.CELL_TYPE_NUMERIC:
620: stack.push(new Ref3DEval(ptg, new NumberEval(cell
621: .getNumericCellValue()), false));
622: break;
623: case HSSFCell.CELL_TYPE_STRING:
624: stack.push(new Ref3DEval(ptg, new StringEval(cell
625: .getRichStringCellValue().getString()), false));
626: break;
627: case HSSFCell.CELL_TYPE_FORMULA:
628: stack.push(new Ref3DEval(ptg, internalEvaluate(cell,
629: row, sheet, workbook), true));
630: break;
631: case HSSFCell.CELL_TYPE_BOOLEAN:
632: stack.push(new Ref3DEval(ptg, cell
633: .getBooleanCellValue() ? BoolEval.TRUE
634: : BoolEval.FALSE, false));
635: break;
636: case HSSFCell.CELL_TYPE_BLANK:
637: stack
638: .push(new Ref3DEval(ptg, BlankEval.INSTANCE,
639: false));
640: break;
641: case HSSFCell.CELL_TYPE_ERROR:
642: stack.push(new Ref3DEval(ptg, ErrorEval.UNKNOWN_ERROR,
643: false)); // TODO: think abt this
644: break;
645: }
646: else {
647: stack.push(new Ref3DEval(ptg, BlankEval.INSTANCE, false));
648: }
649: }
650:
651: /**
652: * Mimics the 'data view' of a cell. This allows formula evaluator
653: * to return a CellValue instead of precasting the value to String
654: * or Number or boolean type.
655: * @author Amol S. Deshmukh < amolweb at ya hoo dot com >
656: */
657: public static final class CellValue {
658: private int cellType;
659: private HSSFRichTextString richTextStringValue;
660: private double numberValue;
661: private boolean booleanValue;
662: private byte errorValue;
663:
664: /**
665: * CellType should be one of the types defined in HSSFCell
666: * @param cellType
667: */
668: public CellValue(int cellType) {
669: super ();
670: this .cellType = cellType;
671: }
672:
673: /**
674: * @return Returns the booleanValue.
675: */
676: public boolean getBooleanValue() {
677: return booleanValue;
678: }
679:
680: /**
681: * @param booleanValue The booleanValue to set.
682: */
683: public void setBooleanValue(boolean booleanValue) {
684: this .booleanValue = booleanValue;
685: }
686:
687: /**
688: * @return Returns the numberValue.
689: */
690: public double getNumberValue() {
691: return numberValue;
692: }
693:
694: /**
695: * @param numberValue The numberValue to set.
696: */
697: public void setNumberValue(double numberValue) {
698: this .numberValue = numberValue;
699: }
700:
701: /**
702: * @return Returns the stringValue. This method is deprecated, use
703: * getRichTextStringValue instead
704: * @deprecated
705: */
706: public String getStringValue() {
707: return richTextStringValue.getString();
708: }
709:
710: /**
711: * @param stringValue The stringValue to set. This method is deprecated, use
712: * getRichTextStringValue instead.
713: * @deprecated
714: */
715: public void setStringValue(String stringValue) {
716: this .richTextStringValue = new HSSFRichTextString(
717: stringValue);
718: }
719:
720: /**
721: * @return Returns the cellType.
722: */
723: public int getCellType() {
724: return cellType;
725: }
726:
727: /**
728: * @return Returns the errorValue.
729: */
730: public byte getErrorValue() {
731: return errorValue;
732: }
733:
734: /**
735: * @param errorValue The errorValue to set.
736: */
737: public void setErrorValue(byte errorValue) {
738: this .errorValue = errorValue;
739: }
740:
741: /**
742: * @return Returns the richTextStringValue.
743: */
744: public HSSFRichTextString getRichTextStringValue() {
745: return richTextStringValue;
746: }
747:
748: /**
749: * @param richTextStringValue The richTextStringValue to set.
750: */
751: public void setRichTextStringValue(
752: HSSFRichTextString richTextStringValue) {
753: this .richTextStringValue = richTextStringValue;
754: }
755: }
756:
757: /**
758: * debug method
759: *
760: * @param formula
761: * @param sheet
762: * @param workbook
763: */
764: void inspectPtgs(String formula) {
765: FormulaParser fp = new FormulaParser(formula, workbook
766: .getWorkbook());
767: fp.parse();
768: Ptg[] ptgs = fp.getRPNPtg();
769: System.out.println("<ptg-group>");
770: for (int i = 0, iSize = ptgs.length; i < iSize; i++) {
771: System.out.println("<ptg>");
772: System.out.println(ptgs[i]);
773: if (ptgs[i] instanceof OperationPtg) {
774: System.out.println("numoperands: "
775: + ((OperationPtg) ptgs[i])
776: .getNumberOfOperands());
777: }
778: System.out.println("</ptg>");
779: }
780: System.out.println("</ptg-group>");
781: }
782:
783: }
|