diff options
| author | Josh Micich <josh@apache.org> | 2008-09-19 07:32:34 +0000 |
|---|---|---|
| committer | Josh Micich <josh@apache.org> | 2008-09-19 07:32:34 +0000 |
| commit | 02b99aeeddaa8208324e4703feb7fec1f9f6e75a (patch) | |
| tree | 21899a492458cd080a3ad6f59c241bdadbcd2854 | |
| parent | e78a288ddfc77af316d6be4b7eaa535ba08badd4 (diff) | |
| download | poi-02b99aeeddaa8208324e4703feb7fec1f9f6e75a.tar.gz poi-02b99aeeddaa8208324e4703feb7fec1f9f6e75a.zip | |
Merged revisions 696860,696898 via svnmerge from
https://svn.apache.org/repos/asf/poi/trunk
Quite a lot of manual fixing required. Also moved CellValue to top level class.
........
r696860 | josh | 2008-09-18 17:02:21 -0700 (Thu, 18 Sep 2008) | 1 line
code clean-up (removed compiler warnings/unused methods)
........
r696898 | josh | 2008-09-18 19:19:58 -0700 (Thu, 18 Sep 2008) | 1 line
Partitioning common formula logic. Introduced FormulaParsingWorkbook and EvaluationWorkbook interfaces to make merge with ooxml branch easier
........
git-svn-id: https://svn.apache.org/repos/asf/poi/branches/ooxml@696961 13f79535-47bb-0310-9956-ffa450edef68
69 files changed, 2040 insertions, 1498 deletions
diff --git a/src/java/org/apache/poi/hssf/dev/FormulaViewer.java b/src/java/org/apache/poi/hssf/dev/FormulaViewer.java index 167eaba532..d42cda71a3 100644 --- a/src/java/org/apache/poi/hssf/dev/FormulaViewer.java +++ b/src/java/org/apache/poi/hssf/dev/FormulaViewer.java @@ -20,7 +20,7 @@ package org.apache.poi.hssf.dev; import java.io.FileInputStream; import java.util.List; -import org.apache.poi.hssf.model.FormulaParser; +import org.apache.poi.hssf.model.HSSFFormulaParser; import org.apache.poi.hssf.record.FormulaRecord; import org.apache.poi.hssf.record.Record; import org.apache.poi.hssf.record.RecordFactory; @@ -181,7 +181,7 @@ public class FormulaViewer private static String composeFormula(FormulaRecord record) { - return FormulaParser.toFormulaString((HSSFWorkbook)null, record.getParsedExpression()); + return HSSFFormulaParser.toFormulaString((HSSFWorkbook)null, record.getParsedExpression()); } /** diff --git a/src/java/org/apache/poi/hssf/eventusermodel/EventWorkbookBuilder.java b/src/java/org/apache/poi/hssf/eventusermodel/EventWorkbookBuilder.java index 3c767dc25a..c7a1471a7d 100644 --- a/src/java/org/apache/poi/hssf/eventusermodel/EventWorkbookBuilder.java +++ b/src/java/org/apache/poi/hssf/eventusermodel/EventWorkbookBuilder.java @@ -19,7 +19,7 @@ package org.apache.poi.hssf.eventusermodel; import java.util.ArrayList; import java.util.List; -import org.apache.poi.hssf.model.FormulaParser; +import org.apache.poi.hssf.model.HSSFFormulaParser; import org.apache.poi.hssf.model.Workbook; import org.apache.poi.hssf.record.BoundSheetRecord; import org.apache.poi.hssf.record.EOFRecord; @@ -33,7 +33,7 @@ import org.apache.poi.hssf.usermodel.HSSFWorkbook; * When working with the EventUserModel, if you want to * process formulas, you need an instance of * {@link Workbook} to pass to a {@link HSSFWorkbook}, - * to finally give to {@link FormulaParser}, + * to finally give to {@link HSSFFormulaParser}, * and this will build you stub ones. * Since you're working with the EventUserModel, you * wouldn't want to get a full {@link Workbook} and @@ -41,7 +41,7 @@ import org.apache.poi.hssf.usermodel.HSSFWorkbook; * Instead, you should collect a few key records as they * go past, then call this once you have them to build a * stub {@link Workbook}, and from that a stub - * {@link HSSFWorkbook}, to use with the {@link FormulaParser}. + * {@link HSSFWorkbook}, to use with the {@link HSSFFormulaParser}. * * The records you should collect are: * * {@link ExternSheetRecord} @@ -56,7 +56,7 @@ public class EventWorkbookBuilder { /** * Wraps up your stub {@link Workbook} as a stub * {@link HSSFWorkbook}, ready for passing to - * {@link FormulaParser} + * {@link HSSFFormulaParser} * @param workbook A stub {@link Workbook} */ public static HSSFWorkbook createStubHSSFWorkbook(Workbook workbook) { @@ -65,11 +65,11 @@ public class EventWorkbookBuilder { /** * Creates a stub Workbook from the supplied records, - * suitable for use with the {@link FormulaParser} + * suitable for use with the {@link HSSFFormulaParser} * @param externs The ExternSheetRecords in your file * @param bounds The BoundSheetRecords in your file * @param sst The SSTRecord in your file. - * @return A stub Workbook suitable for use with {@link FormulaParser} + * @return A stub Workbook suitable for use with {@link HSSFFormulaParser} */ public static Workbook createStubWorkbook(ExternSheetRecord[] externs, BoundSheetRecord[] bounds, SSTRecord sst) { @@ -103,10 +103,10 @@ public class EventWorkbookBuilder { /** * Creates a stub workbook from the supplied records, - * suitable for use with the {@link FormulaParser} + * suitable for use with the {@link HSSFFormulaParser} * @param externs The ExternSheetRecords in your file * @param bounds The BoundSheetRecords in your file - * @return A stub Workbook suitable for use with {@link FormulaParser} + * @return A stub Workbook suitable for use with {@link HSSFFormulaParser} */ public static Workbook createStubWorkbook(ExternSheetRecord[] externs, BoundSheetRecord[] bounds) { diff --git a/src/java/org/apache/poi/hssf/extractor/EventBasedExcelExtractor.java b/src/java/org/apache/poi/hssf/extractor/EventBasedExcelExtractor.java index 2ea35c773e..430dcd4758 100644 --- a/src/java/org/apache/poi/hssf/extractor/EventBasedExcelExtractor.java +++ b/src/java/org/apache/poi/hssf/extractor/EventBasedExcelExtractor.java @@ -32,7 +32,7 @@ import org.apache.poi.hssf.eventusermodel.FormatTrackingHSSFListener; import org.apache.poi.hssf.eventusermodel.HSSFEventFactory; import org.apache.poi.hssf.eventusermodel.HSSFListener; import org.apache.poi.hssf.eventusermodel.HSSFRequest; -import org.apache.poi.hssf.model.FormulaParser; +import org.apache.poi.hssf.model.HSSFFormulaParser; import org.apache.poi.hssf.record.BOFRecord; import org.apache.poi.hssf.record.BoundSheetRecord; import org.apache.poi.hssf.record.CellValueRecordInterface; @@ -45,6 +45,7 @@ import org.apache.poi.hssf.record.Record; import org.apache.poi.hssf.record.SSTRecord; import org.apache.poi.hssf.record.StringRecord; import org.apache.poi.hssf.usermodel.HSSFDateUtil; +import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.poifs.filesystem.POIFSFileSystem; /** @@ -177,7 +178,7 @@ public class EventBasedExcelExtractor extends POIOLE2TextExtractor { thisRow = frec.getRow(); if(formulasNotResults) { - thisText = FormulaParser.toFormulaString(null, frec.getParsedExpression()); + thisText = HSSFFormulaParser.toFormulaString((HSSFWorkbook)null, frec.getParsedExpression()); } else { if(frec.hasCachedResultString()) { // Formula result is a string diff --git a/src/java/org/apache/poi/hssf/model/HSSFFormulaParser.java b/src/java/org/apache/poi/hssf/model/HSSFFormulaParser.java new file mode 100644 index 0000000000..c492c5e86e --- /dev/null +++ b/src/java/org/apache/poi/hssf/model/HSSFFormulaParser.java @@ -0,0 +1,72 @@ +/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+
+package org.apache.poi.hssf.model;
+
+import java.util.List;
+
+import org.apache.poi.hssf.record.formula.Ptg;
+import org.apache.poi.hssf.usermodel.HSSFEvaluationWorkbook;
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+import org.apache.poi.ss.formula.FormulaParser;
+import org.apache.poi.ss.formula.FormulaParsingWorkbook;
+import org.apache.poi.ss.formula.FormulaRenderer;
+import org.apache.poi.ss.formula.FormulaRenderingWorkbook;
+
+/**
+ * HSSF wrapper for the {@link FormulaParser}
+ *
+ * @author Josh Micich
+ */
+public final class HSSFFormulaParser {
+
+ private static FormulaParsingWorkbook createParsingWorkbook(HSSFWorkbook book) {
+ return HSSFEvaluationWorkbook.create(book);
+ }
+
+ private HSSFFormulaParser() {
+ // no instances of this class
+ }
+
+ public static Ptg[] parse(String formula, HSSFWorkbook workbook) {
+ return FormulaParser.parse(formula, createParsingWorkbook(workbook));
+ }
+
+ public static Ptg[] parse(String formula, HSSFWorkbook workbook, int formulaType) {
+ return FormulaParser.parse(formula, createParsingWorkbook(workbook), formulaType);
+ }
+
+ public static String toFormulaString(HSSFWorkbook book, List lptgs) {
+ return toFormulaString(HSSFEvaluationWorkbook.create(book), lptgs);
+ }
+ /**
+ * Convenience method which takes in a list then passes it to the
+ * other toFormulaString signature.
+ * @param book workbook for 3D and named references
+ * @param lptgs list of Ptg, can be null or empty
+ * @return a human readable String
+ */
+ public static String toFormulaString(FormulaRenderingWorkbook book, List lptgs) {
+ Ptg[] ptgs = new Ptg[lptgs.size()];
+ lptgs.toArray(ptgs);
+ return FormulaRenderer.toFormulaString(book, ptgs);
+ }
+
+ public static String toFormulaString(HSSFWorkbook book, Ptg[] ptgs) {
+ return FormulaRenderer.toFormulaString(HSSFEvaluationWorkbook.create(book), ptgs);
+ }
+}
diff --git a/src/java/org/apache/poi/hssf/record/CFRuleRecord.java b/src/java/org/apache/poi/hssf/record/CFRuleRecord.java index 06256c5cc5..cca4e6c652 100644 --- a/src/java/org/apache/poi/hssf/record/CFRuleRecord.java +++ b/src/java/org/apache/poi/hssf/record/CFRuleRecord.java @@ -17,7 +17,7 @@ package org.apache.poi.hssf.record; -import org.apache.poi.hssf.model.FormulaParser; +import org.apache.poi.hssf.model.HSSFFormulaParser; import org.apache.poi.hssf.record.cf.BorderFormatting; import org.apache.poi.hssf.record.cf.FontFormatting; import org.apache.poi.hssf.record.cf.PatternFormatting; @@ -595,6 +595,6 @@ public final class CFRuleRecord extends Record { if(formula == null) { return null; } - return FormulaParser.parse(formula, workbook); + return HSSFFormulaParser.parse(formula, workbook); } } diff --git a/src/java/org/apache/poi/hssf/record/NameRecord.java b/src/java/org/apache/poi/hssf/record/NameRecord.java index 1a47e544dc..29834b9d16 100644 --- a/src/java/org/apache/poi/hssf/record/NameRecord.java +++ b/src/java/org/apache/poi/hssf/record/NameRecord.java @@ -20,7 +20,7 @@ package org.apache.poi.hssf.record; import java.util.ArrayList; import java.util.List; -import org.apache.poi.hssf.model.FormulaParser; +import org.apache.poi.hssf.model.HSSFFormulaParser; import org.apache.poi.hssf.record.formula.Area3DPtg; import org.apache.poi.hssf.record.formula.Ptg; import org.apache.poi.hssf.record.formula.Ref3DPtg; @@ -495,7 +495,7 @@ public final class NameRecord extends Record { * @return area reference */ public String getAreaReference(HSSFWorkbook book){ - return FormulaParser.toFormulaString(book, field_13_name_definition); + return HSSFFormulaParser.toFormulaString(book, field_13_name_definition); } /** sets the reference , the area only (range) diff --git a/src/java/org/apache/poi/hssf/record/formula/OperationPtg.java b/src/java/org/apache/poi/hssf/record/formula/OperationPtg.java index 1e7800b55d..f8acc81df3 100644 --- a/src/java/org/apache/poi/hssf/record/formula/OperationPtg.java +++ b/src/java/org/apache/poi/hssf/record/formula/OperationPtg.java @@ -40,10 +40,10 @@ public abstract class OperationPtg extends Ptg { public abstract int getNumberOfOperands(); public byte getDefaultOperandClass() { - return Ptg.CLASS_VALUE; - } + return Ptg.CLASS_VALUE; + } public final int getType() { - // TODO remove "int getType();" from Eval hierarchy - throw new RuntimeException("remove this method"); + // TODO remove "int getType();" from Eval hierarchy + throw new RuntimeException("remove this method"); } } diff --git a/src/java/org/apache/poi/hssf/record/formula/Ref3DPtg.java b/src/java/org/apache/poi/hssf/record/formula/Ref3DPtg.java index 790d3cd1a7..261e98ce8b 100644 --- a/src/java/org/apache/poi/hssf/record/formula/Ref3DPtg.java +++ b/src/java/org/apache/poi/hssf/record/formula/Ref3DPtg.java @@ -66,9 +66,9 @@ public final class Ref3DPtg extends RefPtgBase implements WorkbookDependentFormu } public void writeBytes(byte [] array, int offset) { - LittleEndian.putByte(array, 0 + offset, sid + getPtgClass()); - LittleEndian.putUShort(array, 1 + offset, getExternSheetIndex()); - writeCoordinates(array, offset + 3); + LittleEndian.putByte(array, 0 + offset, sid + getPtgClass()); + LittleEndian.putUShort(array, 1 + offset, getExternSheetIndex()); + writeCoordinates(array, offset + 3); } public int getSize() { @@ -88,9 +88,9 @@ public final class Ref3DPtg extends RefPtgBase implements WorkbookDependentFormu * formulas. The sheet name will get properly delimited if required. */ public String toFormulaString(FormulaRenderingWorkbook book) { - return ExternSheetNameResolver.prependSheetName(book, field_1_index_extern_sheet, formatReferenceAsString()); + return ExternSheetNameResolver.prependSheetName(book, field_1_index_extern_sheet, formatReferenceAsString()); + } + public String toFormulaString() { + throw new RuntimeException("3D references need a workbook to determine formula text"); } - public String toFormulaString() { - throw new RuntimeException("3D references need a workbook to determine formula text"); - } } diff --git a/src/java/org/apache/poi/hssf/record/formula/atp/AnalysisToolPak.java b/src/java/org/apache/poi/hssf/record/formula/atp/AnalysisToolPak.java index efeb5c484b..ca9dd7d3e0 100644 --- a/src/java/org/apache/poi/hssf/record/formula/atp/AnalysisToolPak.java +++ b/src/java/org/apache/poi/hssf/record/formula/atp/AnalysisToolPak.java @@ -24,13 +24,13 @@ import org.apache.poi.hssf.record.formula.eval.ErrorEval; import org.apache.poi.hssf.record.formula.eval.Eval; import org.apache.poi.hssf.record.formula.eval.ValueEval; import org.apache.poi.hssf.record.formula.functions.FreeRefFunction; -import org.apache.poi.ss.usermodel.Workbook; +import org.apache.poi.ss.formula.EvaluationWorkbook; public final class AnalysisToolPak { private static final FreeRefFunction NotImplemented = new FreeRefFunction() { - public ValueEval evaluate(Eval[] args, Workbook workbook, int srcCellSheet, + public ValueEval evaluate(Eval[] args, EvaluationWorkbook workbook, int srcCellSheet, int srcCellRow, int srcCellCol) { return ErrorEval.FUNCTION_NOT_IMPLEMENTED; } diff --git a/src/java/org/apache/poi/hssf/record/formula/atp/ParityFunction.java b/src/java/org/apache/poi/hssf/record/formula/atp/ParityFunction.java index b76ca96019..b907d89eab 100644 --- a/src/java/org/apache/poi/hssf/record/formula/atp/ParityFunction.java +++ b/src/java/org/apache/poi/hssf/record/formula/atp/ParityFunction.java @@ -17,7 +17,6 @@ package org.apache.poi.hssf.record.formula.atp; -import org.apache.poi.hssf.record.formula.eval.BlankEval; import org.apache.poi.hssf.record.formula.eval.BoolEval; import org.apache.poi.hssf.record.formula.eval.ErrorEval; import org.apache.poi.hssf.record.formula.eval.Eval; @@ -25,8 +24,7 @@ import org.apache.poi.hssf.record.formula.eval.EvaluationException; import org.apache.poi.hssf.record.formula.eval.OperandResolver; import org.apache.poi.hssf.record.formula.eval.ValueEval; import org.apache.poi.hssf.record.formula.functions.FreeRefFunction; -import org.apache.poi.ss.usermodel.Sheet; -import org.apache.poi.ss.usermodel.Workbook; +import org.apache.poi.ss.formula.EvaluationWorkbook; /** * Implementation of Excel 'Analysis ToolPak' function ISEVEN() ISODD()<br/> * @@ -42,7 +40,7 @@ final class ParityFunction implements FreeRefFunction { _desiredParity = desiredParity; } - public ValueEval evaluate(Eval[] args, Workbook workbook, int srcCellSheet, int srcCellRow, + public ValueEval evaluate(Eval[] args, EvaluationWorkbook workbook, int srcCellSheet, int srcCellRow, int srcCellCol) { if (args.length != 1) { return ErrorEval.VALUE_INVALID; diff --git a/src/java/org/apache/poi/hssf/record/formula/atp/YearFrac.java b/src/java/org/apache/poi/hssf/record/formula/atp/YearFrac.java index 59c6aea89d..7c87fe2521 100644 --- a/src/java/org/apache/poi/hssf/record/formula/atp/YearFrac.java +++ b/src/java/org/apache/poi/hssf/record/formula/atp/YearFrac.java @@ -21,7 +21,6 @@ import java.util.Calendar; import java.util.GregorianCalendar; import java.util.regex.Pattern; -import org.apache.poi.hssf.record.formula.eval.BlankEval; import org.apache.poi.hssf.record.formula.eval.ErrorEval; import org.apache.poi.hssf.record.formula.eval.Eval; import org.apache.poi.hssf.record.formula.eval.EvaluationException; @@ -31,8 +30,7 @@ import org.apache.poi.hssf.record.formula.eval.StringEval; import org.apache.poi.hssf.record.formula.eval.ValueEval; import org.apache.poi.hssf.record.formula.functions.FreeRefFunction; import org.apache.poi.ss.usermodel.DateUtil; -import org.apache.poi.ss.usermodel.Sheet; -import org.apache.poi.ss.usermodel.Workbook; +import org.apache.poi.ss.formula.EvaluationWorkbook; /** * Implementation of Excel 'Analysis ToolPak' function YEARFRAC()<br/> * @@ -61,7 +59,7 @@ final class YearFrac implements FreeRefFunction { // enforce singleton } - public ValueEval evaluate(Eval[] args, Workbook workbook, int srcCellSheet, int srcCellRow, + public ValueEval evaluate(Eval[] args, EvaluationWorkbook workbook, int srcCellSheet, int srcCellRow, int srcCellCol) { double result; diff --git a/src/java/org/apache/poi/hssf/record/formula/eval/ExternalFunction.java b/src/java/org/apache/poi/hssf/record/formula/eval/ExternalFunction.java index 3782c6c4de..8e8a9799e4 100755 --- a/src/java/org/apache/poi/hssf/record/formula/eval/ExternalFunction.java +++ b/src/java/org/apache/poi/hssf/record/formula/eval/ExternalFunction.java @@ -19,7 +19,7 @@ package org.apache.poi.hssf.record.formula.eval; import org.apache.poi.hssf.record.formula.atp.AnalysisToolPak; import org.apache.poi.hssf.record.formula.functions.FreeRefFunction; -import org.apache.poi.ss.usermodel.Workbook; +import org.apache.poi.ss.formula.EvaluationWorkbook; /** * * Common entry point for all user-defined (non-built-in) functions (where @@ -30,7 +30,7 @@ import org.apache.poi.ss.usermodel.Workbook; */ final class ExternalFunction implements FreeRefFunction { - public ValueEval evaluate(Eval[] args, Workbook workbook, + public ValueEval evaluate(Eval[] args, EvaluationWorkbook workbook, int srcCellSheet, int srcCellRow,int srcCellCol) { int nIncomingArgs = args.length; @@ -58,9 +58,9 @@ final class ExternalFunction implements FreeRefFunction { return targetFunc.evaluate(outGoingArgs, workbook, srcCellSheet, srcCellRow, srcCellCol); } - private FreeRefFunction findExternalUserDefinedFunction(Workbook workbook, + private FreeRefFunction findExternalUserDefinedFunction(EvaluationWorkbook workbook, NameXEval n) throws EvaluationException { - String functionName = workbook.resolveNameXText(n.getSheetRefIndex(), n.getNameNumber()); + String functionName = workbook.resolveNameXText(n.getPtg()); if(false) { System.out.println("received call to external user defined function (" + functionName + ")"); @@ -75,6 +75,7 @@ final class ExternalFunction implements FreeRefFunction { } private FreeRefFunction findInternalUserDefinedFunction(NameEval functionNameEval) throws EvaluationException { + String functionName = functionNameEval.getFunctionName(); if(false) { System.out.println("received call to internal user defined function (" + functionName + ")"); diff --git a/src/java/org/apache/poi/hssf/record/formula/eval/NameXEval.java b/src/java/org/apache/poi/hssf/record/formula/eval/NameXEval.java index 12b6be3805..b67f4eb3d3 100644 --- a/src/java/org/apache/poi/hssf/record/formula/eval/NameXEval.java +++ b/src/java/org/apache/poi/hssf/record/formula/eval/NameXEval.java @@ -17,32 +17,27 @@ package org.apache.poi.hssf.record.formula.eval; +import org.apache.poi.hssf.record.formula.NameXPtg; + /** * @author Josh Micich */ public final class NameXEval implements Eval { - /** index to REF entry in externsheet record */ - private final int _sheetRefIndex; - /** index to defined name or externname table(1 based) */ - private final int _nameNumber; + private final NameXPtg _ptg; - public NameXEval(int sheetRefIndex, int nameNumber) { - _sheetRefIndex = sheetRefIndex; - _nameNumber = nameNumber; + public NameXEval(NameXPtg ptg) { + _ptg = ptg; } - public int getSheetRefIndex() { - return _sheetRefIndex; - } - public int getNameNumber() { - return _nameNumber; + public NameXPtg getPtg() { + return _ptg; } public String toString() { StringBuffer sb = new StringBuffer(64); sb.append(getClass().getName()).append(" ["); - sb.append(_sheetRefIndex).append(", ").append(_nameNumber); + sb.append(_ptg.getSheetRefIndex()).append(", ").append(_ptg.getNameIndex()); sb.append("]"); return sb.toString(); } diff --git a/src/java/org/apache/poi/hssf/record/formula/functions/FreeRefFunction.java b/src/java/org/apache/poi/hssf/record/formula/functions/FreeRefFunction.java index 3a3478ca52..cdec045b40 100755 --- a/src/java/org/apache/poi/hssf/record/formula/functions/FreeRefFunction.java +++ b/src/java/org/apache/poi/hssf/record/formula/functions/FreeRefFunction.java @@ -19,8 +19,7 @@ package org.apache.poi.hssf.record.formula.functions; import org.apache.poi.hssf.record.formula.eval.Eval; import org.apache.poi.hssf.record.formula.eval.ValueEval; -import org.apache.poi.ss.usermodel.Sheet; -import org.apache.poi.ss.usermodel.Workbook; +import org.apache.poi.ss.formula.EvaluationWorkbook; /** @@ -53,5 +52,5 @@ public interface FreeRefFunction { * a specified Excel error (Exceptions are never thrown to represent Excel errors). * */ - ValueEval evaluate(Eval[] args, Workbook workbook, int srcCellSheet, int srcCellRow, int srcCellCol); + ValueEval evaluate(Eval[] args, EvaluationWorkbook workbook, int srcCellSheet, int srcCellRow, int srcCellCol); } diff --git a/src/java/org/apache/poi/hssf/record/formula/functions/Indirect.java b/src/java/org/apache/poi/hssf/record/formula/functions/Indirect.java index be63d70275..567f29b2b1 100644 --- a/src/java/org/apache/poi/hssf/record/formula/functions/Indirect.java +++ b/src/java/org/apache/poi/hssf/record/formula/functions/Indirect.java @@ -1,27 +1,26 @@ -/* -* Licensed to the Apache Software Foundation (ASF) under one or more -* contributor license agreements. See the NOTICE file distributed with -* this work for additional information regarding copyright ownership. -* The ASF licenses this file to You under the Apache License, Version 2.0 -* (the "License"); you may not use this file except in compliance with -* the License. You may obtain a copy of the License at -* -* http://www.apache.org/licenses/LICENSE-2.0 -* -* Unless required by applicable law or agreed to in writing, software -* distributed under the License is distributed on an "AS IS" BASIS, -* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -* See the License for the specific language governing permissions and -* limitations under the License. -*/ +/* ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. +==================================================================== */ package org.apache.poi.hssf.record.formula.functions; import org.apache.poi.hssf.record.formula.eval.ErrorEval; import org.apache.poi.hssf.record.formula.eval.Eval; import org.apache.poi.hssf.record.formula.eval.ValueEval; -import org.apache.poi.ss.usermodel.Sheet; -import org.apache.poi.ss.usermodel.Workbook; +import org.apache.poi.ss.formula.EvaluationWorkbook; /** * Implementation for Excel function INDIRECT<p/> @@ -41,7 +40,7 @@ import org.apache.poi.ss.usermodel.Workbook; */ public final class Indirect implements FreeRefFunction { - public ValueEval evaluate(Eval[] args, Workbook workbook, int srcCellSheet, int srcCellRow, int srcCellCol) { + public ValueEval evaluate(Eval[] args, EvaluationWorkbook workbook, int srcCellSheet, int srcCellRow, int srcCellCol) { // TODO - implement INDIRECT() return ErrorEval.FUNCTION_NOT_IMPLEMENTED; } diff --git a/src/java/org/apache/poi/hssf/usermodel/DVConstraint.java b/src/java/org/apache/poi/hssf/usermodel/DVConstraint.java index a1027ccfaa..5bc478f424 100644 --- a/src/java/org/apache/poi/hssf/usermodel/DVConstraint.java +++ b/src/java/org/apache/poi/hssf/usermodel/DVConstraint.java @@ -20,10 +20,12 @@ import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.Date; -import org.apache.poi.hssf.model.FormulaParser; +import org.apache.poi.hssf.model.HSSFFormulaParser; import org.apache.poi.hssf.record.formula.NumberPtg; import org.apache.poi.hssf.record.formula.Ptg; import org.apache.poi.hssf.record.formula.StringPtg; +import org.apache.poi.ss.formula.FormulaParser; +import org.apache.poi.ss.formula.FormulaType; /** * @@ -339,7 +341,7 @@ public class DVConstraint { if (_explicitListValues == null) { // formula is parsed with slightly different RVA rules: (root node type must be 'reference') - return FormulaParser.parse(_formula1, workbook, FormulaParser.FORMULA_TYPE_DATAVALIDATION_LIST); + return HSSFFormulaParser.parse(_formula1, workbook, FormulaType.DATAVALIDATION_LIST); // To do: Excel places restrictions on the available operations within a list formula. // Some things like union and intersection are not allowed. } @@ -369,7 +371,7 @@ public class DVConstraint { if (value != null) { throw new IllegalStateException("Both formula and value cannot be present"); } - return FormulaParser.parse(formula, workbook); + return HSSFFormulaParser.parse(formula, workbook); } diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java b/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java index 4fbedb8745..a6a1500321 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFCell.java @@ -25,7 +25,7 @@ import java.util.HashMap; import java.util.Iterator; import java.util.List; -import org.apache.poi.hssf.model.FormulaParser; +import org.apache.poi.hssf.model.HSSFFormulaParser; import org.apache.poi.hssf.model.Sheet; import org.apache.poi.hssf.model.Workbook; import org.apache.poi.hssf.record.BlankRecord; @@ -513,20 +513,16 @@ public class HSSFCell implements Cell { } /** - * set a string value for the cell. Please note that if you are using - * full 16 bit unicode you should call <code>setEncoding()</code> first. + * set a string value for the cell. * - * @param value value to set the cell to. For formulas we'll set the formula - * string, for String cells we'll set its value. For other types we will + * @param value value to set the cell to. For formulas we'll set the formula + * cached string result, for String cells we'll set its value. For other types we will * change the cell to a string cell and set its value. * If value is null then we will change the cell to a Blank cell. - * @deprecated Use setCellValue(HSSFRichTextString) instead. */ - - public void setCellValue(String value) - { - HSSFRichTextString str = new HSSFRichTextString(value); - setCellValue(str); + public void setCellValue(String value) { + HSSFRichTextString str = value == null ? null : new HSSFRichTextString(value); + setCellValue(str); } /** @@ -597,12 +593,12 @@ public class HSSFCell implements Cell { if (rec.getXFIndex() == (short)0) { rec.setXFIndex((short) 0x0f); } - Ptg[] ptgs = FormulaParser.parse(formula, book); + Ptg[] ptgs = HSSFFormulaParser.parse(formula, book); frec.setParsedExpression(ptgs); } public String getCellFormula() { - return FormulaParser.toFormulaString(book, ((FormulaRecordAggregate)record).getFormulaRecord().getParsedExpression()); + return HSSFFormulaParser.toFormulaString(book, ((FormulaRecordAggregate)record).getFormulaRecord().getParsedExpression()); } /** diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormattingRule.java b/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormattingRule.java index fc34b9da9c..29430b094f 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormattingRule.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormattingRule.java @@ -17,7 +17,7 @@ package org.apache.poi.hssf.usermodel; -import org.apache.poi.hssf.model.FormulaParser; +import org.apache.poi.hssf.model.HSSFFormulaParser; import org.apache.poi.hssf.record.CFRuleRecord; import org.apache.poi.hssf.record.CFRuleRecord.ComparisonOperator; import org.apache.poi.hssf.record.cf.BorderFormatting; @@ -205,6 +205,6 @@ public final class HSSFConditionalFormattingRule if(parsedExpression ==null) { return null; } - return FormulaParser.toFormulaString(workbook, parsedExpression); + return HSSFFormulaParser.toFormulaString(workbook, parsedExpression); } } diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationWorkbook.java b/src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationWorkbook.java index f9399ee8c5..39dbdcd069 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationWorkbook.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationWorkbook.java @@ -1,17 +1,26 @@ package org.apache.poi.hssf.usermodel;
+import org.apache.poi.hssf.model.HSSFFormulaParser;
import org.apache.poi.hssf.model.Workbook;
+import org.apache.poi.hssf.record.NameRecord;
import org.apache.poi.hssf.record.formula.NamePtg;
import org.apache.poi.hssf.record.formula.NameXPtg;
+import org.apache.poi.hssf.record.formula.Ptg;
+import org.apache.poi.ss.formula.EvaluationName;
+import org.apache.poi.ss.formula.EvaluationWorkbook;
+import org.apache.poi.ss.formula.FormulaParsingWorkbook;
import org.apache.poi.ss.formula.FormulaRenderingWorkbook;
+import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.Sheet;
/**
* Internal POI use only
*
* @author Josh Micich
*/
-public final class HSSFEvaluationWorkbook implements FormulaRenderingWorkbook {
+public final class HSSFEvaluationWorkbook implements FormulaRenderingWorkbook, EvaluationWorkbook, FormulaParsingWorkbook {
+ private final HSSFWorkbook _uBook;
private final Workbook _iBook;
public static HSSFEvaluationWorkbook create(HSSFWorkbook book) {
@@ -22,9 +31,58 @@ public final class HSSFEvaluationWorkbook implements FormulaRenderingWorkbook { }
private HSSFEvaluationWorkbook(HSSFWorkbook book) {
+ _uBook = book;
_iBook = book.getWorkbook();
}
+ public int getExternalSheetIndex(String sheetName) {
+ int sheetIndex = _uBook.getSheetIndex(sheetName);
+ return _iBook.checkExternSheet(sheetIndex);
+ }
+
+ public EvaluationName getName(int index) {
+ return new Name(_iBook.getNameRecord(index), index);
+ }
+
+ public EvaluationName getName(String name) {
+ for(int i=0; i < _iBook.getNumNames(); i++) {
+ NameRecord nr = _iBook.getNameRecord(i);
+ if (name.equalsIgnoreCase(nr.getNameText())) {
+ return new Name(nr, i);
+ }
+ }
+ return null;
+ }
+
+ public int getSheetIndex(Sheet sheet) {
+ return _uBook.getSheetIndex(sheet);
+ }
+
+ public String getSheetName(int sheetIndex) {
+ return _uBook.getSheetName(sheetIndex);
+ }
+
+ public int getNameIndex(String name) {
+ return _uBook.getNameIndex(name);
+ }
+
+ public NameXPtg getNameXPtg(String name) {
+ return _iBook.getNameXPtg(name);
+ }
+
+ public Sheet getSheet(int sheetIndex) {
+ return _uBook.getSheetAt(sheetIndex);
+ }
+
+ public Sheet getSheetByExternSheetIndex(int externSheetIndex) {
+ int sheetIndex = _iBook.getSheetIndexFromExternSheetIndex(externSheetIndex);
+ return _uBook.getSheetAt(sheetIndex);
+ }
+
+ public HSSFWorkbook getWorkbook() {
+ return _uBook;
+ }
+
public String resolveNameXText(NameXPtg n) {
return _iBook.resolveNameXText(n.getSheetRefIndex(), n.getNameIndex());
}
@@ -35,4 +93,45 @@ public final class HSSFEvaluationWorkbook implements FormulaRenderingWorkbook { public String getNameText(NamePtg namePtg) {
return _iBook.getNameRecord(namePtg.getIndex()).getNameText();
}
+ public EvaluationName getName(NamePtg namePtg) {
+ int ix = namePtg.getIndex();
+ return new Name(_iBook.getNameRecord(ix), ix);
+ }
+ public Ptg[] getFormulaTokens(Cell cell) {
+ return HSSFFormulaParser.parse(cell.getCellFormula(), _uBook);
+ }
+
+ private static final class Name implements EvaluationName {
+
+ private final NameRecord _nameRecord;
+ private final int _index;
+
+ public Name(NameRecord nameRecord, int index) {
+ _nameRecord = nameRecord;
+ _index = index;
+ }
+
+ public Ptg[] getNameDefinition() {
+ return _nameRecord.getNameDefinition();
+ }
+
+ public String getNameText() {
+ return _nameRecord.getNameText();
+ }
+
+ public boolean hasFormula() {
+ return _nameRecord.hasFormula();
+ }
+
+ public boolean isFunctionName() {
+ return _nameRecord.isFunctionName();
+ }
+
+ public boolean isRange() {
+ return _nameRecord.hasFormula(); // TODO - is this right?
+ }
+ public NamePtg createPtg() {
+ return new NamePtg(_index);
+ }
+ }
}
diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java b/src/java/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java index 64ec2fd211..9003c1b3bc 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java @@ -1,75 +1,272 @@ -/* -* Licensed to the Apache Software Foundation (ASF) under one or more -* contributor license agreements. See the NOTICE file distributed with -* this work for additional information regarding copyright ownership. -* The ASF licenses this file to You under the Apache License, Version 2.0 -* (the "License"); you may not use this file except in compliance with -* the License. You may obtain a copy of the License at -* -* http://www.apache.org/licenses/LICENSE-2.0 -* -* Unless required by applicable law or agreed to in writing, software -* distributed under the License is distributed on an "AS IS" BASIS, -* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -* See the License for the specific language governing permissions and -* limitations under the License. -*/ - -package org.apache.poi.hssf.usermodel; - -import org.apache.poi.hssf.model.FormulaParser; -import org.apache.poi.hssf.record.formula.OperationPtg; -import org.apache.poi.hssf.record.formula.Ptg; -import org.apache.poi.ss.usermodel.FormulaEvaluator; -import org.apache.poi.ss.usermodel.Workbook; - -/** - * @author Amol S. Deshmukh < amolweb at ya hoo dot com > - * - */ -public class HSSFFormulaEvaluator extends FormulaEvaluator { - public HSSFFormulaEvaluator(HSSFSheet sheet, HSSFWorkbook workbook) { - super(sheet, workbook); - } - public HSSFFormulaEvaluator(HSSFWorkbook workbook) { - super(workbook); - } - - /** - * Returns an underlying FormulaParser, for the specified - * Formula String and HSSFWorkbook. - * This will allow you to generate the Ptgs yourself, if - * your needs are more complex than just having the - * formula evaluated. - */ - public static FormulaParser getUnderlyingParser(Workbook workbook, String formula) { - return new FormulaParser(formula, workbook); - } - - /** - * debug method - */ - void inspectPtgs(String formula) { - Ptg[] ptgs = FormulaParser.parse(formula, _workbook); - System.out.println("<ptg-group>"); - for (int i = 0, iSize = ptgs.length; i < iSize; i++) { - System.out.println("<ptg>"); - System.out.println(ptgs[i]); - if (ptgs[i] instanceof OperationPtg) { - System.out.println("numoperands: " + ((OperationPtg) ptgs[i]).getNumberOfOperands()); - } - System.out.println("</ptg>"); - } - System.out.println("</ptg-group>"); - } - - /** - * Compatibility class. - * Seems to do more harm than good though - */ -// public static class CellValue extends FormulaEvaluator.CellValue { -// public CellValue(int cellType, CreationHelper creationHelper) { -// super(cellType, creationHelper); -// } -// } -} +/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+
+package org.apache.poi.hssf.usermodel;
+
+import java.util.Iterator;
+
+import org.apache.poi.hssf.record.formula.eval.BoolEval;
+import org.apache.poi.hssf.record.formula.eval.ErrorEval;
+import org.apache.poi.hssf.record.formula.eval.NumberEval;
+import org.apache.poi.hssf.record.formula.eval.StringEval;
+import org.apache.poi.hssf.record.formula.eval.ValueEval;
+import org.apache.poi.ss.formula.WorkbookEvaluator;
+import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.CellValue;
+import org.apache.poi.ss.usermodel.Sheet;
+
+/**
+ * Evaluates formula cells.<p/>
+ *
+ * For performance reasons, this class keeps a cache of all previously calculated intermediate
+ * cell values. Be sure to call {@link #clearCache()} if any workbook cells are changed between
+ * calls to evaluate~ methods on this class.
+ *
+ * @author Amol S. Deshmukh < amolweb at ya hoo dot com >
+ * @author Josh Micich
+ */
+public class HSSFFormulaEvaluator /* almost implements FormulaEvaluator */ {
+
+ private WorkbookEvaluator _bookEvaluator;
+
+ /**
+ * @deprecated (Sep 2008) HSSFSheet parameter is ignored
+ */
+ public HSSFFormulaEvaluator(HSSFSheet sheet, HSSFWorkbook workbook) {
+ this(workbook);
+ if (false) {
+ sheet.toString(); // suppress unused parameter compiler warning
+ }
+ }
+ public HSSFFormulaEvaluator(HSSFWorkbook workbook) {
+ _bookEvaluator = new WorkbookEvaluator(HSSFEvaluationWorkbook.create(workbook));
+ }
+
+ /**
+ * TODO for debug/test use
+ */
+ /* package */ int getEvaluationCount() {
+ return _bookEvaluator.getEvaluationCount();
+ }
+
+ /**
+ * Does nothing
+ * @deprecated (Aug 2008) - not needed, since the current row can be derived from the cell
+ */
+ public void setCurrentRow(HSSFRow row) {
+ // do nothing
+ if (false) {
+ row.getClass(); // suppress unused parameter compiler warning
+ }
+ }
+
+ /**
+ * Should be called whenever there are major changes (e.g. moving sheets) to input cells
+ * in the evaluated workbook.
+ * Failure to call this method after changing cell values will cause incorrect behaviour
+ * of the evaluate~ methods of this class
+ */
+ public void clearAllCachedResultValues() {
+ _bookEvaluator.clearAllCachedResultValues();
+ }
+ /**
+ * Should be called whenever there are changes to individual input cells in the evaluated workbook.
+ * Failure to call this method after changing cell values will cause incorrect behaviour
+ * of the evaluate~ methods of this class
+ */
+ public void clearCachedResultValue(Sheet sheet, int rowIndex, int columnIndex) {
+ _bookEvaluator.clearCachedResultValue(sheet, rowIndex, columnIndex);
+ }
+
+ /**
+ * If cell contains a formula, the formula is evaluated and returned,
+ * else the CellValue simply copies the appropriate cell value from
+ * the cell and also its cell type. This method should be preferred over
+ * evaluateInCell() when the call should not modify the contents of the
+ * original cell.
+ * @param cell
+ */
+ public CellValue evaluate(Cell cell) {
+ if (cell == null) {
+ return null;
+ }
+
+ switch (cell.getCellType()) {
+ case HSSFCell.CELL_TYPE_BOOLEAN:
+ return CellValue.valueOf(cell.getBooleanCellValue());
+ case HSSFCell.CELL_TYPE_ERROR:
+ return CellValue.getError(cell.getErrorCellValue());
+ case HSSFCell.CELL_TYPE_FORMULA:
+ return evaluateFormulaCellValue(cell);
+ case HSSFCell.CELL_TYPE_NUMERIC:
+ return new CellValue(cell.getNumericCellValue());
+ case HSSFCell.CELL_TYPE_STRING:
+ return new CellValue(cell.getRichStringCellValue().getString());
+ }
+ throw new IllegalStateException("Bad cell type (" + cell.getCellType() + ")");
+ }
+
+
+ /**
+ * If cell contains formula, it evaluates the formula,
+ * and saves the result of the formula. The cell
+ * remains as a formula cell.
+ * Else if cell does not contain formula, this method leaves
+ * the cell unchanged.
+ * Note that the type of the formula result is returned,
+ * so you know what kind of value is also stored with
+ * the formula.
+ * <pre>
+ * int evaluatedCellType = evaluator.evaluateFormulaCell(cell);
+ * </pre>
+ * Be aware that your cell will hold both the formula,
+ * and the result. If you want the cell replaced with
+ * the result of the formula, use {@link #evaluateInCell(HSSFCell)}
+ * @param cell The cell to evaluate
+ * @return The type of the formula result (the cell's type remains as HSSFCell.CELL_TYPE_FORMULA however)
+ */
+ public int evaluateFormulaCell(Cell cell) {
+ if (cell == null || cell.getCellType() != HSSFCell.CELL_TYPE_FORMULA) {
+ return -1;
+ }
+ CellValue cv = evaluateFormulaCellValue(cell);
+ // cell remains a formula cell, but the cached value is changed
+ setCellValue(cell, cv);
+ return cv.getCellType();
+ }
+
+ /**
+ * If cell contains formula, it evaluates the formula, and
+ * puts the formula result back into the cell, in place
+ * of the old formula.
+ * Else if cell does not contain formula, this method leaves
+ * the cell unchanged.
+ * Note that the same instance of HSSFCell is returned to
+ * allow chained calls like:
+ * <pre>
+ * int evaluatedCellType = evaluator.evaluateInCell(cell).getCellType();
+ * </pre>
+ * Be aware that your cell value will be changed to hold the
+ * result of the formula. If you simply want the formula
+ * value computed for you, use {@link #evaluateFormulaCell(HSSFCell)}
+ * @param cell
+ */
+ public HSSFCell evaluateInCell(Cell cell) {
+ if (cell == null) {
+ return null;
+ }
+ HSSFCell result = (HSSFCell) cell;
+ if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
+ CellValue cv = evaluateFormulaCellValue(cell);
+ setCellType(cell, cv); // cell will no longer be a formula cell
+ setCellValue(cell, cv);
+ }
+ return result;
+ }
+ private static void setCellType(Cell cell, CellValue cv) {
+ int cellType = cv.getCellType();
+ switch (cellType) {
+ case HSSFCell.CELL_TYPE_BOOLEAN:
+ case HSSFCell.CELL_TYPE_ERROR:
+ case HSSFCell.CELL_TYPE_NUMERIC:
+ case HSSFCell.CELL_TYPE_STRING:
+ cell.setCellType(cellType);
+ return;
+ case HSSFCell.CELL_TYPE_BLANK:
+ // never happens - blanks eventually get translated to zero
+ case HSSFCell.CELL_TYPE_FORMULA:
+ // this will never happen, we have already evaluated the formula
+ }
+ throw new IllegalStateException("Unexpected cell value type (" + cellType + ")");
+ }
+
+ private static void setCellValue(Cell cell, CellValue cv) {
+ int cellType = cv.getCellType();
+ switch (cellType) {
+ case HSSFCell.CELL_TYPE_BOOLEAN:
+ cell.setCellValue(cv.getBooleanValue());
+ break;
+ case HSSFCell.CELL_TYPE_ERROR:
+ cell.setCellErrorValue(cv.getErrorValue());
+ break;
+ case HSSFCell.CELL_TYPE_NUMERIC:
+ cell.setCellValue(cv.getNumberValue());
+ break;
+ case HSSFCell.CELL_TYPE_STRING:
+ cell.setCellValue(new HSSFRichTextString(cv.getStringValue()));
+ break;
+ case HSSFCell.CELL_TYPE_BLANK:
+ // never happens - blanks eventually get translated to zero
+ case HSSFCell.CELL_TYPE_FORMULA:
+ // this will never happen, we have already evaluated the formula
+ default:
+ throw new IllegalStateException("Unexpected cell value type (" + cellType + ")");
+ }
+ }
+
+ /**
+ * Loops over all cells in all sheets of the supplied
+ * workbook.
+ * For cells that contain formulas, their formulas are
+ * evaluated, and the results are saved. These cells
+ * remain as formula cells.
+ * For cells that do not contain formulas, no changes
+ * are made.
+ * This is a helpful wrapper around looping over all
+ * cells, and calling evaluateFormulaCell on each one.
+ */
+ public static void evaluateAllFormulaCells(HSSFWorkbook wb) {
+ HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
+ for(int i=0; i<wb.getNumberOfSheets(); i++) {
+ HSSFSheet sheet = wb.getSheetAt(i);
+
+ for (Iterator rit = sheet.rowIterator(); rit.hasNext();) {
+ HSSFRow r = (HSSFRow)rit.next();
+
+ for (Iterator cit = r.cellIterator(); cit.hasNext();) {
+ HSSFCell c = (HSSFCell)cit.next();
+ if (c.getCellType() == HSSFCell.CELL_TYPE_FORMULA)
+ evaluator.evaluateFormulaCell(c);
+ }
+ }
+ }
+ }
+
+ /**
+ * Returns a CellValue wrapper around the supplied ValueEval instance.
+ * @param eval
+ */
+ private CellValue evaluateFormulaCellValue(Cell cell) {
+ ValueEval eval = _bookEvaluator.evaluate(cell);
+ if (eval instanceof NumberEval) {
+ NumberEval ne = (NumberEval) eval;
+ return new CellValue(ne.getNumberValue());
+ }
+ if (eval instanceof BoolEval) {
+ BoolEval be = (BoolEval) eval;
+ return CellValue.valueOf(be.getBooleanValue());
+ }
+ if (eval instanceof StringEval) {
+ StringEval ne = (StringEval) eval;
+ return new CellValue(ne.getStringValue());
+ }
+ if (eval instanceof ErrorEval) {
+ return CellValue.getError(((ErrorEval)eval).getErrorCode());
+ }
+ throw new RuntimeException("Unexpected eval class (" + eval.getClass().getName() + ")");
+ }
+}
diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFName.java b/src/java/org/apache/poi/hssf/usermodel/HSSFName.java index bae2484d7c..62759c7056 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFName.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFName.java @@ -28,7 +28,7 @@ import org.apache.poi.ss.usermodel.Name; * * @author Libin Roman (Vista Portal LDT. Developer) */ -public class HSSFName implements Name { +public final class HSSFName implements Name { private HSSFWorkbook _book; private NameRecord _definedNameRec; @@ -96,8 +96,7 @@ public class HSSFName implements Name { */ private void setSheetName(String sheetName){ int sheetNumber = _book.getSheetIndex(sheetName); - short externSheetNumber = (short) - _book.getExternalSheetIndex(sheetNumber); + short externSheetNumber = _book.getWorkbook().checkExternSheet(sheetNumber); _definedNameRec.setExternSheetNumber(externSheetNumber); } diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java b/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java index 83bae3e43b..b5e969740e 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java @@ -662,17 +662,8 @@ public class HSSFWorkbook extends POIDocument implements org.apache.poi.ss.userm return -1; } - /* package */ int findSheetIndex(Sheet sheet) { - for(int i=0; i<_sheets.size(); i++) { - HSSFSheet hSheet = (HSSFSheet) _sheets.get(i); - if(hSheet.getSheet() == sheet) { - return i; - } - } - throw new IllegalArgumentException("Specified sheet not found in this workbook"); - } - /** +<<<<<<< .working * Returns the external sheet index of the sheet * with the given internal index, creating one * if needed. @@ -709,6 +700,8 @@ public class HSSFWorkbook extends POIDocument implements org.apache.poi.ss.userm /** +======= +>>>>>>> .merge-right.r696898 * create an HSSFSheet for this HSSFWorkbook, adds it to the sheets and returns * the high level representation. Use this to create new sheets. * @@ -751,7 +744,7 @@ public class HSSFWorkbook extends POIDocument implements org.apache.poi.ss.userm if (filterDbNameIndex >=0) { NameRecord origNameRecord = workbook.getNameRecord(filterDbNameIndex); // copy original formula but adjust 3D refs to the new external sheet index - int newExtSheetIx = getExternalSheetIndex(newSheetIndex); + int newExtSheetIx = workbook.checkExternSheet(newSheetIndex); Ptg[] ptgs = origNameRecord.getNameDefinition(); for (int i=0; i< ptgs.length; i++) { Ptg ptg = ptgs[i]; diff --git a/src/java/org/apache/poi/hssf/usermodel/OperationEvaluatorFactory.java b/src/java/org/apache/poi/hssf/usermodel/OperationEvaluatorFactory.java deleted file mode 100755 index 21dc067592..0000000000 --- a/src/java/org/apache/poi/hssf/usermodel/OperationEvaluatorFactory.java +++ /dev/null @@ -1,184 +0,0 @@ -/* ==================================================================== - Licensed to the Apache Software Foundation (ASF) under one or more - contributor license agreements. See the NOTICE file distributed with - this work for additional information regarding copyright ownership. - The ASF licenses this file to You under the Apache License, Version 2.0 - (the "License"); you may not use this file except in compliance with - the License. You may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 - - Unless required by applicable law or agreed to in writing, software - distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the License for the specific language governing permissions and - limitations under the License. -==================================================================== */ - -package org.apache.poi.hssf.usermodel; - -import java.lang.reflect.Constructor; -import java.lang.reflect.InvocationTargetException; -import java.lang.reflect.Modifier; -import java.util.HashMap; -import java.util.Map; - -import org.apache.poi.hssf.record.formula.AddPtg; -import org.apache.poi.hssf.record.formula.ConcatPtg; -import org.apache.poi.hssf.record.formula.DividePtg; -import org.apache.poi.hssf.record.formula.EqualPtg; -import org.apache.poi.hssf.record.formula.ExpPtg; -import org.apache.poi.hssf.record.formula.FuncPtg; -import org.apache.poi.hssf.record.formula.FuncVarPtg; -import org.apache.poi.hssf.record.formula.GreaterEqualPtg; -import org.apache.poi.hssf.record.formula.GreaterThanPtg; -import org.apache.poi.hssf.record.formula.LessEqualPtg; -import org.apache.poi.hssf.record.formula.LessThanPtg; -import org.apache.poi.hssf.record.formula.MultiplyPtg; -import org.apache.poi.hssf.record.formula.NotEqualPtg; -import org.apache.poi.hssf.record.formula.OperationPtg; -import org.apache.poi.hssf.record.formula.PercentPtg; -import org.apache.poi.hssf.record.formula.PowerPtg; -import org.apache.poi.hssf.record.formula.Ptg; -import org.apache.poi.hssf.record.formula.SubtractPtg; -import org.apache.poi.hssf.record.formula.UnaryMinusPtg; -import org.apache.poi.hssf.record.formula.UnaryPlusPtg; -import org.apache.poi.hssf.record.formula.eval.AddEval; -import org.apache.poi.hssf.record.formula.eval.ConcatEval; -import org.apache.poi.hssf.record.formula.eval.DivideEval; -import org.apache.poi.hssf.record.formula.eval.EqualEval; -import org.apache.poi.hssf.record.formula.eval.FuncVarEval; -import org.apache.poi.hssf.record.formula.eval.GreaterEqualEval; -import org.apache.poi.hssf.record.formula.eval.GreaterThanEval; -import org.apache.poi.hssf.record.formula.eval.LessEqualEval; -import org.apache.poi.hssf.record.formula.eval.LessThanEval; -import org.apache.poi.hssf.record.formula.eval.MultiplyEval; -import org.apache.poi.hssf.record.formula.eval.NotEqualEval; -import org.apache.poi.hssf.record.formula.eval.OperationEval; -import org.apache.poi.hssf.record.formula.eval.PercentEval; -import org.apache.poi.hssf.record.formula.eval.PowerEval; -import org.apache.poi.hssf.record.formula.eval.SubtractEval; -import org.apache.poi.hssf.record.formula.eval.UnaryMinusEval; -import org.apache.poi.hssf.record.formula.eval.UnaryPlusEval; - -/** - * This class creates <tt>OperationEval</tt> instances to help evaluate <tt>OperationPtg</tt> - * formula tokens. - * - * @author Josh Micich - */ -final class OperationEvaluatorFactory { - private static final Class[] OPERATION_CONSTRUCTOR_CLASS_ARRAY = new Class[] { Ptg.class }; - // TODO - use singleton instances directly instead of reflection - private static final Map _constructorsByPtgClass = initialiseConstructorsMap(); - private static final Map _instancesByPtgClass = initialiseInstancesMap(); - - private OperationEvaluatorFactory() { - // no instances of this class - } - - private static Map initialiseConstructorsMap() { - Map m = new HashMap(32); - add(m, ConcatPtg.class, ConcatEval.class); - add(m, FuncPtg.class, FuncVarEval.class); - add(m, FuncVarPtg.class, FuncVarEval.class); - return m; - } - private static Map initialiseInstancesMap() { - Map m = new HashMap(32); - add(m, EqualPtg.class, EqualEval.instance); - add(m, GreaterEqualPtg.class, GreaterEqualEval.instance); - add(m, GreaterThanPtg.class, GreaterThanEval.instance); - add(m, LessEqualPtg.class, LessEqualEval.instance); - add(m, LessThanPtg.class, LessThanEval.instance); - add(m, NotEqualPtg.class, NotEqualEval.instance); - - add(m, AddPtg.class, AddEval.instance); - add(m, DividePtg.class, DivideEval.instance); - add(m, MultiplyPtg.class, MultiplyEval.instance); - add(m, PercentPtg.class, PercentEval.instance); - add(m, PowerPtg.class, PowerEval.instance); - add(m, SubtractPtg.class, SubtractEval.instance); - add(m, UnaryMinusPtg.class, UnaryMinusEval.instance); - add(m, UnaryPlusPtg.class, UnaryPlusEval.instance); - return m; - } - - private static void add(Map m, Class ptgClass, OperationEval evalInstance) { - if(!Ptg.class.isAssignableFrom(ptgClass)) { - throw new IllegalArgumentException("Expected Ptg subclass"); - } - m.put(ptgClass, evalInstance); - } - - private static void add(Map m, Class ptgClass, Class evalClass) { - // perform some validation now, to keep later exception handlers simple - if(!Ptg.class.isAssignableFrom(ptgClass)) { - throw new IllegalArgumentException("Expected Ptg subclass"); - } - - if(!OperationEval.class.isAssignableFrom(evalClass)) { - throw new IllegalArgumentException("Expected OperationEval subclass"); - } - if (!Modifier.isPublic(evalClass.getModifiers())) { - throw new RuntimeException("Eval class must be public"); - } - if (Modifier.isAbstract(evalClass.getModifiers())) { - throw new RuntimeException("Eval class must not be abstract"); - } - - Constructor constructor; - try { - constructor = evalClass.getDeclaredConstructor(OPERATION_CONSTRUCTOR_CLASS_ARRAY); - } catch (NoSuchMethodException e) { - throw new RuntimeException("Missing constructor"); - } - if (!Modifier.isPublic(constructor.getModifiers())) { - throw new RuntimeException("Eval constructor must be public"); - } - m.put(ptgClass, constructor); - } - - /** - * returns the OperationEval concrete impl instance corresponding - * to the supplied operationPtg - */ - public static OperationEval create(OperationPtg ptg) { - if(ptg == null) { - throw new IllegalArgumentException("ptg must not be null"); - } - Object result; - - Class ptgClass = ptg.getClass(); - - result = _instancesByPtgClass.get(ptgClass); - if (result != null) { - return (OperationEval) result; - } - - - Constructor constructor = (Constructor) _constructorsByPtgClass.get(ptgClass); - if(constructor == null) { - if(ptgClass == ExpPtg.class) { - // ExpPtg is used for array formulas and shared formulas. - // it is currently unsupported, and may not even get implemented here - throw new RuntimeException("ExpPtg currently not supported"); - } - throw new RuntimeException("Unexpected operation ptg class (" + ptgClass.getName() + ")"); - } - - Object[] initargs = { ptg }; - try { - result = constructor.newInstance(initargs); - } catch (IllegalArgumentException e) { - throw new RuntimeException(e); - } catch (InstantiationException e) { - throw new RuntimeException(e); - } catch (IllegalAccessException e) { - throw new RuntimeException(e); - } catch (InvocationTargetException e) { - throw new RuntimeException(e); - } - return (OperationEval) result; - } -} diff --git a/src/java/org/apache/poi/ss/formula/CellEvaluationFrame.java b/src/java/org/apache/poi/ss/formula/CellEvaluationFrame.java new file mode 100644 index 0000000000..b6f542a082 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/CellEvaluationFrame.java @@ -0,0 +1,71 @@ +/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+
+package org.apache.poi.ss.formula;
+
+/**
+ * Stores the parameters that identify the evaluation of one cell.<br/>
+ */
+final class CellEvaluationFrame {
+
+ private final int _sheetIndex;
+ private final int _srcRowNum;
+ private final int _srcColNum;
+ private final int _hashCode;
+
+ public CellEvaluationFrame(int sheetIndex, int srcRowNum, int srcColNum) {
+ if (sheetIndex < 0) {
+ throw new IllegalArgumentException("sheetIndex must not be negative");
+ }
+ _sheetIndex = sheetIndex;
+ _srcRowNum = srcRowNum;
+ _srcColNum = srcColNum;
+ _hashCode = sheetIndex + 17 * (srcRowNum + 17 * srcColNum);
+ }
+
+ public boolean equals(Object obj) {
+ CellEvaluationFrame other = (CellEvaluationFrame) obj;
+ if (_sheetIndex != other._sheetIndex) {
+ return false;
+ }
+ if (_srcRowNum != other._srcRowNum) {
+ return false;
+ }
+ if (_srcColNum != other._srcColNum) {
+ return false;
+ }
+ return true;
+ }
+ public int hashCode() {
+ return _hashCode;
+ }
+
+ /**
+ * @return human readable string for debug purposes
+ */
+ public String formatAsString() {
+ return "R=" + _srcRowNum + " C=" + _srcColNum + " ShIx=" + _sheetIndex;
+ }
+
+ public String toString() {
+ StringBuffer sb = new StringBuffer(64);
+ sb.append(getClass().getName()).append(" [");
+ sb.append(formatAsString());
+ sb.append("]");
+ return sb.toString();
+ }
+}
\ No newline at end of file diff --git a/src/java/org/apache/poi/ss/formula/CellEvaluator.java b/src/java/org/apache/poi/ss/formula/CellEvaluator.java new file mode 100644 index 0000000000..73f8d4d900 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/CellEvaluator.java @@ -0,0 +1,54 @@ +package org.apache.poi.ss.formula;
+
+import org.apache.poi.hssf.record.formula.eval.BlankEval;
+import org.apache.poi.hssf.record.formula.eval.BoolEval;
+import org.apache.poi.hssf.record.formula.eval.ErrorEval;
+import org.apache.poi.hssf.record.formula.eval.NumberEval;
+import org.apache.poi.hssf.record.formula.eval.StringEval;
+import org.apache.poi.hssf.record.formula.eval.ValueEval;
+import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.Sheet;
+
+final class CellEvaluator {
+
+ private final WorkbookEvaluator _bookEvaluator;
+ private final EvaluationTracker _tracker;
+
+ public CellEvaluator(WorkbookEvaluator bookEvaluator, EvaluationTracker tracker) {
+ _bookEvaluator = bookEvaluator;
+ _tracker = tracker;
+ }
+
+ /**
+ * Given a cell, find its type and from that create an appropriate ValueEval
+ * impl instance and return that. Since the cell could be an external
+ * reference, we need the sheet that this belongs to.
+ * Non existent cells are treated as empty.
+ */
+ public ValueEval getEvalForCell(Cell cell) {
+
+ if (cell == null) {
+ return BlankEval.INSTANCE;
+ }
+ switch (cell.getCellType()) {
+ case Cell.CELL_TYPE_NUMERIC:
+ return new NumberEval(cell.getNumericCellValue());
+ case Cell.CELL_TYPE_STRING:
+ return new StringEval(cell.getRichStringCellValue().getString());
+ case Cell.CELL_TYPE_FORMULA:
+ return _bookEvaluator.internalEvaluate(cell, _tracker);
+ case Cell.CELL_TYPE_BOOLEAN:
+ return BoolEval.valueOf(cell.getBooleanCellValue());
+ case Cell.CELL_TYPE_BLANK:
+ return BlankEval.INSTANCE;
+ case Cell.CELL_TYPE_ERROR:
+ return ErrorEval.valueOf(cell.getErrorCellValue());
+ }
+ throw new RuntimeException("Unexpected cell type (" + cell.getCellType() + ")");
+ }
+
+ public String getSheetName(Sheet sheet) {
+ return _bookEvaluator.getSheetName(sheet);
+ }
+
+}
diff --git a/src/java/org/apache/poi/ss/usermodel/EvaluationCache.java b/src/java/org/apache/poi/ss/formula/EvaluationCache.java index 75eda02fbb..d78f4a7286 100644 --- a/src/java/org/apache/poi/ss/usermodel/EvaluationCache.java +++ b/src/java/org/apache/poi/ss/formula/EvaluationCache.java @@ -15,12 +15,14 @@ limitations under the License. ==================================================================== */ -package org.apache.poi.ss.usermodel; +package org.apache.poi.ss.formula; import java.util.HashMap; +import java.util.List; import java.util.Map; import org.apache.poi.hssf.record.formula.eval.ValueEval; +import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator; /** * Performance optimisation for {@link HSSFFormulaEvaluator}. This class stores previously @@ -31,55 +33,29 @@ import org.apache.poi.hssf.record.formula.eval.ValueEval; * @author Josh Micich */ final class EvaluationCache { - private static final class Key { - - private final int _sheetIndex; - private final int _srcRowNum; - private final int _srcColNum; - private final int _hashCode; - - public Key(int sheetIndex, int srcRowNum, int srcColNum) { - _sheetIndex = sheetIndex; - _srcRowNum = srcRowNum; - _srcColNum = srcColNum; - _hashCode = sheetIndex + srcRowNum + srcColNum; - } - - public int hashCode() { - return _hashCode; - } - - public boolean equals(Object obj) { - Key other = (Key) obj; - if (_hashCode != other._hashCode) { - return false; - } - if (_sheetIndex != other._sheetIndex) { - return false; - } - if (_srcRowNum != other._srcRowNum) { - return false; - } - if (_srcColNum != other._srcColNum) { - return false; - } - return true; - } - } + private static final CellEvaluationFrame[] EMPTY_CEF_ARRAY = { }; private final Map _valuesByKey; + private final Map _consumingCellsByDest; /* package */EvaluationCache() { _valuesByKey = new HashMap(); + _consumingCellsByDest = new HashMap(); } public ValueEval getValue(int sheetIndex, int srcRowNum, int srcColNum) { - Key key = new Key(sheetIndex, srcRowNum, srcColNum); + return getValue(new CellEvaluationFrame(sheetIndex, srcRowNum, srcColNum)); + } + + /* package */ ValueEval getValue(CellEvaluationFrame key) { return (ValueEval) _valuesByKey.get(key); } public void setValue(int sheetIndex, int srcRowNum, int srcColNum, ValueEval value) { - Key key = new Key(sheetIndex, srcRowNum, srcColNum); + setValue(new CellEvaluationFrame(sheetIndex, srcRowNum, srcColNum), value); + } + + /* package */ void setValue(CellEvaluationFrame key, ValueEval value) { if (_valuesByKey.containsKey(key)) { throw new RuntimeException("Already have cached value for this cell"); } @@ -92,4 +68,32 @@ final class EvaluationCache { public void clear() { _valuesByKey.clear(); } + + public void clearValue(int sheetIndex, int rowIndex, int columnIndex) { + clearValuesRecursive(new CellEvaluationFrame(sheetIndex, rowIndex, columnIndex)); + + } + + private void clearValuesRecursive(CellEvaluationFrame cef) { + CellEvaluationFrame[] consumingCells = getConsumingCells(cef); + for (int i = 0; i < consumingCells.length; i++) { + clearValuesRecursive(consumingCells[i]); + } + _valuesByKey.remove(cef); + _consumingCellsByDest.remove(cef); + } + + private CellEvaluationFrame[] getConsumingCells(CellEvaluationFrame cef) { + List temp = (List) _consumingCellsByDest.get(cef); + if (temp == null) { + return EMPTY_CEF_ARRAY; + } + int nItems = temp.size(); + if (temp.size() < 1) { + return EMPTY_CEF_ARRAY; + } + CellEvaluationFrame[] result = new CellEvaluationFrame[nItems]; + temp.toArray(result); + return result; + } } diff --git a/src/java/org/apache/poi/hssf/usermodel/LazyAreaEval.java b/src/java/org/apache/poi/ss/formula/EvaluationName.java index 0a195c8754..ae8624c7b8 100644 --- a/src/java/org/apache/poi/hssf/usermodel/LazyAreaEval.java +++ b/src/java/org/apache/poi/ss/formula/EvaluationName.java @@ -1,29 +1,41 @@ -/* ==================================================================== - Licensed to the Apache Software Foundation (ASF) under one or more - contributor license agreements. See the NOTICE file distributed with - this work for additional information regarding copyright ownership. - The ASF licenses this file to You under the Apache License, Version 2.0 - (the "License"); you may not use this file except in compliance with - the License. You may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 - - Unless required by applicable law or agreed to in writing, software - distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the License for the specific language governing permissions and - limitations under the License. -==================================================================== */ - -package org.apache.poi.hssf.usermodel; -import org.apache.poi.hssf.record.formula.AreaI; - -/** - * - * @author Josh Micich - */ -final class LazyAreaEval extends org.apache.poi.ss.usermodel.LazyAreaEval { - public LazyAreaEval(AreaI ptg, HSSFSheet sheet, HSSFFormulaEvaluator evaluator) { - super(ptg, sheet, evaluator); - } -} +/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+
+package org.apache.poi.ss.formula;
+
+import org.apache.poi.hssf.record.formula.NamePtg;
+import org.apache.poi.hssf.record.formula.Ptg;
+/**
+ * Abstracts a name record for formula evaluation.<br/>
+ *
+ * For POI internal use only
+ *
+ * @author Josh Micich
+ */
+public interface EvaluationName {
+
+ String getNameText();
+
+ boolean isFunctionName();
+
+ boolean hasFormula();
+
+ Ptg[] getNameDefinition();
+
+ boolean isRange();
+ NamePtg createPtg();
+}
diff --git a/src/java/org/apache/poi/ss/usermodel/EvaluationCycleDetector.java b/src/java/org/apache/poi/ss/formula/EvaluationTracker.java index 30c6d31b1e..20ab3299af 100755 --- a/src/java/org/apache/poi/ss/usermodel/EvaluationCycleDetector.java +++ b/src/java/org/apache/poi/ss/formula/EvaluationTracker.java @@ -15,81 +15,31 @@ limitations under the License. ==================================================================== */ -package org.apache.poi.ss.usermodel; +package org.apache.poi.ss.formula; import java.util.ArrayList; import java.util.List; +import org.apache.poi.hssf.record.formula.eval.ErrorEval; +import org.apache.poi.hssf.record.formula.eval.ValueEval; +import org.apache.poi.hssf.usermodel.HSSFCell; + /** * Instances of this class keep track of multiple dependent cell evaluations due - * to recursive calls to <tt>FormulaEvaluator.internalEvaluate()</tt>. + * to recursive calls to {@link WorkbookEvaluator#evaluate(HSSFCell)} * The main purpose of this class is to detect an attempt to evaluate a cell * that is already being evaluated. In other words, it detects circular * references in spreadsheet formulas. * * @author Josh Micich */ -final class EvaluationCycleDetector { - - /** - * Stores the parameters that identify the evaluation of one cell.<br/> - */ - private static final class CellEvaluationFrame { - - private final Workbook _workbook; - private final int _sheetIndex; - private final int _srcRowNum; - private final int _srcColNum; - - public CellEvaluationFrame(Workbook workbook, int sheetIndex, int srcRowNum, int srcColNum) { - if (workbook == null) { - throw new IllegalArgumentException("workbook must not be null"); - } - if (sheetIndex < 0) { - throw new IllegalArgumentException("sheetIndex must not be negative"); - } - _workbook = workbook; - _sheetIndex = sheetIndex; - _srcRowNum = srcRowNum; - _srcColNum = srcColNum; - } - - public boolean equals(Object obj) { - CellEvaluationFrame other = (CellEvaluationFrame) obj; - if (_workbook != other._workbook) { - return false; - } - if (_sheetIndex != other._sheetIndex) { - return false; - } - if (_srcRowNum != other._srcRowNum) { - return false; - } - if (_srcColNum != other._srcColNum) { - return false; - } - return true; - } - - /** - * @return human readable string for debug purposes - */ - public String formatAsString() { - return "R=" + _srcRowNum + " C=" + _srcColNum + " ShIx=" + _sheetIndex; - } - - public String toString() { - StringBuffer sb = new StringBuffer(64); - sb.append(getClass().getName()).append(" ["); - sb.append(formatAsString()); - sb.append("]"); - return sb.toString(); - } - } +final class EvaluationTracker { private final List _evaluationFrames; + private final EvaluationCache _cache; - public EvaluationCycleDetector() { + public EvaluationTracker(EvaluationCache cache) { + _cache = cache; _evaluationFrames = new ArrayList(); } @@ -108,13 +58,16 @@ final class EvaluationCycleDetector { * @return <code>true</code> if the specified cell has not been visited yet in the current * evaluation. <code>false</code> if the specified cell is already being evaluated. */ - public boolean startEvaluate(Workbook workbook, int sheetIndex, int srcRowNum, int srcColNum) { - CellEvaluationFrame cef = new CellEvaluationFrame(workbook, sheetIndex, srcRowNum, srcColNum); + public ValueEval startEvaluate(int sheetIndex, int srcRowNum, int srcColNum) { + CellEvaluationFrame cef = new CellEvaluationFrame(sheetIndex, srcRowNum, srcColNum); if (_evaluationFrames.contains(cef)) { - return false; + return ErrorEval.CIRCULAR_REF_ERROR; + } + ValueEval result = _cache.getValue(cef); + if (result == null) { + _evaluationFrames.add(cef); } - _evaluationFrames.add(cef); - return true; + return result; } /** @@ -128,8 +81,9 @@ final class EvaluationCycleDetector { * Assuming a well behaved client, parameters to this method would not be * required. However, they have been included to assert correct behaviour, * and form more meaningful error messages. + * @param result */ - public void endEvaluate(Workbook workbook, int sheetIndex, int srcRowNum, int srcColNum) { + public void endEvaluate(int sheetIndex, int srcRowNum, int srcColNum, ValueEval result) { int nFrames = _evaluationFrames.size(); if (nFrames < 1) { throw new IllegalStateException("Call to endEvaluate without matching call to startEvaluate"); @@ -137,7 +91,7 @@ final class EvaluationCycleDetector { nFrames--; CellEvaluationFrame cefExpected = (CellEvaluationFrame) _evaluationFrames.get(nFrames); - CellEvaluationFrame cefActual = new CellEvaluationFrame(workbook, sheetIndex, srcRowNum, srcColNum); + CellEvaluationFrame cefActual = new CellEvaluationFrame(sheetIndex, srcRowNum, srcColNum); if (!cefActual.equals(cefExpected)) { throw new RuntimeException("Wrong cell specified. " + "Corresponding startEvaluate() call was for cell {" @@ -146,5 +100,7 @@ final class EvaluationCycleDetector { } // else - no problems so pop current frame _evaluationFrames.remove(nFrames); + + _cache.setValue(cefActual, result); } } diff --git a/src/java/org/apache/poi/ss/formula/EvaluationWorkbook.java b/src/java/org/apache/poi/ss/formula/EvaluationWorkbook.java new file mode 100644 index 0000000000..adf69d0d51 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/EvaluationWorkbook.java @@ -0,0 +1,42 @@ +/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+
+package org.apache.poi.ss.formula;
+
+import org.apache.poi.hssf.record.formula.NamePtg;
+import org.apache.poi.hssf.record.formula.NameXPtg;
+import org.apache.poi.hssf.record.formula.Ptg;
+import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.Sheet;
+/**
+ * Abstracts a workbook for the purpose of formula evaluation.<br/>
+ *
+ * For POI internal use only
+ *
+ * @author Josh Micich
+ */
+public interface EvaluationWorkbook {
+ String getSheetName(int sheetIndex);
+ int getSheetIndex(Sheet sheet);
+
+ Sheet getSheet(int sheetIndex);
+
+ Sheet getSheetByExternSheetIndex(int externSheetIndex);
+ EvaluationName getName(NamePtg namePtg);
+ String resolveNameXText(NameXPtg ptg);
+ Ptg[] getFormulaTokens(Cell cell);
+}
diff --git a/src/java/org/apache/poi/hssf/model/FormulaParser.java b/src/java/org/apache/poi/ss/formula/FormulaParser.java index e849dbf6eb..df2f8c282f 100644 --- a/src/java/org/apache/poi/hssf/model/FormulaParser.java +++ b/src/java/org/apache/poi/ss/formula/FormulaParser.java @@ -15,7 +15,7 @@ limitations under the License. ==================================================================== */ -package org.apache.poi.hssf.model; +package org.apache.poi.ss.formula; import java.util.ArrayList; import java.util.List; @@ -57,16 +57,10 @@ import org.apache.poi.hssf.record.formula.UnaryMinusPtg; import org.apache.poi.hssf.record.formula.UnaryPlusPtg; import org.apache.poi.hssf.record.formula.function.FunctionMetadata; import org.apache.poi.hssf.record.formula.function.FunctionMetadataRegistry; -import org.apache.poi.ss.usermodel.Name; -import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.hssf.usermodel.HSSFErrorConstants; -import org.apache.poi.hssf.usermodel.HSSFEvaluationWorkbook; -import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator; -import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.AreaReference; import org.apache.poi.hssf.util.CellReference; import org.apache.poi.hssf.util.CellReference.NameType; -import org.apache.poi.ss.formula.FormulaRenderer; /** * This class parses a formula string into a List of tokens in RPN order. @@ -84,6 +78,7 @@ import org.apache.poi.ss.formula.FormulaRenderer; * @author Cameron Riley (criley at ekmail.com) * @author Peter M. Murray (pete at quantrix dot com) * @author Pavel Krupets (pkrupets at palmtreebusiness dot com) + * @author Josh Micich */ public final class FormulaParser { @@ -100,14 +95,6 @@ public final class FormulaParser { } } - public static final int FORMULA_TYPE_CELL = 0; - public static final int FORMULA_TYPE_SHARED = 1; - public static final int FORMULA_TYPE_ARRAY =2; - public static final int FORMULA_TYPE_CONDFORMAT = 3; - public static final int FORMULA_TYPE_NAMEDRANGE = 4; - // this constant is currently very specific. The exact differences from general data - // validation formulas or conditional format formulas is not known yet - public static final int FORMULA_TYPE_DATAVALIDATION_LIST = 5; private final String formulaString; private final int formulaLength; @@ -123,7 +110,8 @@ public final class FormulaParser { */ private char look; - private Workbook book; + private FormulaParsingWorkbook book; + /** @@ -134,23 +122,23 @@ public final class FormulaParser { * parse results. * This class is recommended only for single threaded use. * - * If you only have a usermodel.Workbook, and not a + * If you only have a usermodel.HSSFWorkbook, and not a * model.Workbook, then use the convenience method on * usermodel.HSSFFormulaEvaluator */ - public FormulaParser(String formula, Workbook book){ + private FormulaParser(String formula, FormulaParsingWorkbook book){ formulaString = formula; pointer=0; this.book = book; formulaLength = formulaString.length(); } - public static Ptg[] parse(String formula, Workbook book) { - return parse(formula, book, FORMULA_TYPE_CELL); + public static Ptg[] parse(String formula, FormulaParsingWorkbook book) { + return parse(formula, book, FormulaType.CELL); } - public static Ptg[] parse(String formula, Workbook workbook, int formulaType) { - FormulaParser fp = HSSFFormulaEvaluator.getUnderlyingParser(workbook, formula); + public static Ptg[] parse(String formula, FormulaParsingWorkbook workbook, int formulaType) { + FormulaParser fp = new FormulaParser(formula, workbook); fp.parse(); return fp.getRPNPtg(formulaType); } @@ -309,7 +297,7 @@ public final class FormulaParser { Match('!'); String sheetName = name; String first = parseIdentifier(); - short externIdx = (short)book.getExternalSheetIndex(book.getSheetIndex(sheetName)); + int externIdx = book.getExternalSheetIndex(sheetName); areaRef = parseArea(name); if (areaRef != null) { // will happen if dots are used instead of colon @@ -331,7 +319,7 @@ public final class FormulaParser { } return new Area3DPtg(first+":"+second,externIdx); } - return new Ref3DPtg(first,externIdx); + return new Ref3DPtg(first, externIdx); } if (name.equalsIgnoreCase("TRUE") || name.equalsIgnoreCase("FALSE")) { return new BoolPtg(name.toUpperCase()); @@ -347,15 +335,16 @@ public final class FormulaParser { new FormulaParseException("Name '" + name + "' does not look like a cell reference or named range"); } - - for(int i = 0; i < book.getNumberOfNames(); i++) { - // named range name matching is case insensitive - if(book.getNameAt(i).getNameName().equalsIgnoreCase(name)) { - return new NamePtg(i); - } - } - throw new FormulaParseException("Specified named range '" + EvaluationName evalName = book.getName(name); + if (evalName == null) { + throw new FormulaParseException("Specified named range '" + name + "' does not exist in the current workbook."); + } + if (evalName.isRange()) { + return evalName.createPtg(); + } + throw new FormulaParseException("Specified name '" + + name + "' is not a range as expected"); } /** @@ -410,9 +399,16 @@ public final class FormulaParser { if(!AbstractFunctionPtg.isBuiltInFunctionName(name)) { // user defined function // in the token tree, the name is more or less the first argument - int nameIndex = book.getNameIndex(name); - if (nameIndex >= 0) { - Name hName = book.getNameAt(nameIndex); + + EvaluationName hName = book.getName(name); + if (hName == null) { + + nameToken = book.getNameXPtg(name); + if (nameToken == null) { + throw new FormulaParseException("Name '" + name + + "' is completely unknown in the current workbook"); + } + } else { if (!hName.isFunctionName()) { throw new FormulaParseException("Attempt to use name '" + name + "' as a function, but defined name in workbook does not refer to a function"); @@ -420,15 +416,7 @@ public final class FormulaParser { // calls to user-defined functions within the workbook // get a Name token which points to a defined name record - nameToken = new NamePtg(nameIndex); - } else { - if(book instanceof HSSFWorkbook) { - nameToken = ((HSSFWorkbook)book).getNameXPtg(name); - } - if (nameToken == null) { - throw new FormulaParseException("Name '" + name - + "' is completely unknown in the current workbook"); - } + nameToken = hName.createPtg(); } } @@ -965,9 +953,9 @@ end; /** * API call to execute the parsing of the formula - * @deprecated use {@link #parse(String, Workbook)} directly + * */ - public void parse() { + private void parse() { pointer=0; GetChar(); _rootNode = comparisonExpression(); @@ -979,50 +967,10 @@ end; } } - - /********************************* - * PARSER IMPLEMENTATION ENDS HERE - * EXCEL SPECIFIC METHODS BELOW - *******************************/ - - /** API call to retrive the array of Ptgs created as - * a result of the parsing - */ - public Ptg[] getRPNPtg() { - return getRPNPtg(FORMULA_TYPE_CELL); - } - - public Ptg[] getRPNPtg(int formulaType) { + private Ptg[] getRPNPtg(int formulaType) { OperandClassTransformer oct = new OperandClassTransformer(formulaType); // RVA is for 'operand class': 'reference', 'value', 'array' oct.transformFormula(_rootNode); return ParseNode.toTokenArray(_rootNode); } - - /** - * Convenience method which takes in a list then passes it to the - * other toFormulaString signature. - * @param book workbook for 3D and named references - * @param lptgs list of Ptg, can be null or empty - * @return a human readable String - */ - public static String toFormulaString(HSSFWorkbook book, List lptgs) { - String retval = null; - if (lptgs == null || lptgs.size() == 0) return "#NAME"; - Ptg[] ptgs = new Ptg[lptgs.size()]; - ptgs = (Ptg[])lptgs.toArray(ptgs); - retval = toFormulaString(book, ptgs); - return retval; - } - - /** - * Static method to convert an array of Ptgs in RPN order - * to a human readable string format in infix mode. - * @param book workbook for named and 3D references - * @param ptgs array of Ptg, can be null or empty - * @return a human readable String - */ - public static String toFormulaString(HSSFWorkbook book, Ptg[] ptgs) { - return FormulaRenderer.toFormulaString(HSSFEvaluationWorkbook.create(book), ptgs); - } } diff --git a/src/java/org/apache/poi/hssf/usermodel/LazyRefEval.java b/src/java/org/apache/poi/ss/formula/FormulaParsingWorkbook.java index 3b591bbcc0..69431c2c22 100644 --- a/src/java/org/apache/poi/hssf/usermodel/LazyRefEval.java +++ b/src/java/org/apache/poi/ss/formula/FormulaParsingWorkbook.java @@ -15,19 +15,23 @@ limitations under the License.
==================================================================== */
-package org.apache.poi.hssf.usermodel;
-import org.apache.poi.hssf.record.formula.Ref3DPtg;
-import org.apache.poi.hssf.record.formula.RefPtg;
+package org.apache.poi.ss.formula;
+
+import org.apache.poi.hssf.record.formula.NameXPtg;
/**
-*
-* @author Josh Micich
-*/
-final class LazyRefEval extends org.apache.poi.ss.usermodel.LazyRefEval {
- public LazyRefEval(RefPtg ptg, HSSFSheet sheet, HSSFFormulaEvaluator evaluator) {
- super(ptg, sheet, evaluator);
- }
- public LazyRefEval(Ref3DPtg ptg, HSSFSheet sheet, HSSFFormulaEvaluator evaluator) {
- super(ptg, sheet, evaluator);
- }
+ * Abstracts a workbook for the purpose of formula parsing.<br/>
+ *
+ * For POI internal use only
+ *
+ * @author Josh Micich
+ */
+public interface FormulaParsingWorkbook {
+ /**
+ * named range name matching is case insensitive
+ */
+ EvaluationName getName(String name);
+
+ int getExternalSheetIndex(String sheetName);
+ NameXPtg getNameXPtg(String name);
}
diff --git a/src/java/org/apache/poi/ss/usermodel/EvaluationCycleDetectorManager.java b/src/java/org/apache/poi/ss/formula/FormulaType.java index d83d187002..3b47030d47 100755..100644 --- a/src/java/org/apache/poi/ss/usermodel/EvaluationCycleDetectorManager.java +++ b/src/java/org/apache/poi/ss/formula/FormulaType.java @@ -1,46 +1,40 @@ -/* ==================================================================== - Licensed to the Apache Software Foundation (ASF) under one or more - contributor license agreements. See the NOTICE file distributed with - this work for additional information regarding copyright ownership. - The ASF licenses this file to You under the Apache License, Version 2.0 - (the "License"); you may not use this file except in compliance with - the License. You may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 - - Unless required by applicable law or agreed to in writing, software - distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the License for the specific language governing permissions and - limitations under the License. -==================================================================== */ - -package org.apache.poi.ss.usermodel; - -/** - * This class makes an <tt>EvaluationCycleDetector</tt> instance available to - * each thread via a <tt>ThreadLocal</tt> in order to avoid adding a parameter - * to a few protected methods within <tt>HSSFFormulaEvaluator</tt>. - * - * @author Josh Micich - */ -final class EvaluationCycleDetectorManager { - - ThreadLocal tl = null; - private static ThreadLocal _tlEvaluationTracker = new ThreadLocal() { - protected synchronized Object initialValue() { - return new EvaluationCycleDetector(); - } - }; - - /** - * @return - */ - public static EvaluationCycleDetector getTracker() { - return (EvaluationCycleDetector) _tlEvaluationTracker.get(); - } - - private EvaluationCycleDetectorManager() { - // no instances of this class - } -} +/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+
+package org.apache.poi.ss.formula;
+
+/**
+ * Enumeration of various formula types.<br/>
+ *
+ * For POI internal use only
+ *
+ * @author Josh Micich
+ */
+public final class FormulaType {
+ private FormulaType() {
+ // no instances of this class
+ }
+ public static final int CELL = 0;
+ public static final int SHARED = 1;
+ public static final int ARRAY =2;
+ public static final int CONDFORMAT = 3;
+ public static final int NAMEDRANGE = 4;
+ // this constant is currently very specific. The exact differences from general data
+ // validation formulas or conditional format formulas is not known yet
+ public static final int DATAVALIDATION_LIST = 5;
+
+}
diff --git a/src/java/org/apache/poi/ss/usermodel/LazyAreaEval.java b/src/java/org/apache/poi/ss/formula/LazyAreaEval.java index 8d9b192291..5816f0052b 100644 --- a/src/java/org/apache/poi/ss/usermodel/LazyAreaEval.java +++ b/src/java/org/apache/poi/ss/formula/LazyAreaEval.java @@ -15,7 +15,7 @@ limitations under the License. ==================================================================== */ -package org.apache.poi.ss.usermodel; +package org.apache.poi.ss.formula; import org.apache.poi.hssf.record.formula.AreaI; import org.apache.poi.hssf.record.formula.AreaI.OffsetArea; @@ -23,18 +23,21 @@ import org.apache.poi.hssf.record.formula.eval.AreaEval; import org.apache.poi.hssf.record.formula.eval.AreaEvalBase; import org.apache.poi.hssf.record.formula.eval.BlankEval; import org.apache.poi.hssf.record.formula.eval.ValueEval; +import org.apache.poi.ss.usermodel.Cell; +import org.apache.poi.ss.usermodel.Row; +import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.hssf.util.CellReference; /** * * @author Josh Micich */ -public class LazyAreaEval extends AreaEvalBase { +final class LazyAreaEval extends AreaEvalBase { private final Sheet _sheet; - private FormulaEvaluator _evaluator; + private final CellEvaluator _evaluator; - public LazyAreaEval(AreaI ptg, Sheet sheet, FormulaEvaluator evaluator) { + public LazyAreaEval(AreaI ptg, Sheet sheet, CellEvaluator evaluator) { super(ptg); _sheet = sheet; _evaluator = evaluator; diff --git a/src/java/org/apache/poi/ss/usermodel/LazyRefEval.java b/src/java/org/apache/poi/ss/formula/LazyRefEval.java index 7fb7681aeb..2d80585fcb 100644 --- a/src/java/org/apache/poi/ss/usermodel/LazyRefEval.java +++ b/src/java/org/apache/poi/ss/formula/LazyRefEval.java @@ -1,85 +1,88 @@ -/* ==================================================================== - Licensed to the Apache Software Foundation (ASF) under one or more - contributor license agreements. See the NOTICE file distributed with - this work for additional information regarding copyright ownership. - The ASF licenses this file to You under the Apache License, Version 2.0 - (the "License"); you may not use this file except in compliance with - the License. You may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 - - Unless required by applicable law or agreed to in writing, software - distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the License for the specific language governing permissions and - limitations under the License. -==================================================================== */ - -package org.apache.poi.ss.usermodel; - -import org.apache.poi.hssf.record.formula.AreaI; -import org.apache.poi.hssf.record.formula.Ref3DPtg; -import org.apache.poi.hssf.record.formula.RefPtg; -import org.apache.poi.hssf.record.formula.AreaI.OffsetArea; -import org.apache.poi.hssf.record.formula.eval.AreaEval; -import org.apache.poi.hssf.record.formula.eval.BlankEval; -import org.apache.poi.hssf.record.formula.eval.RefEvalBase; -import org.apache.poi.hssf.record.formula.eval.ValueEval; -import org.apache.poi.hssf.util.CellReference; - -/** -* -* @author Josh Micich -*/ -public class LazyRefEval extends RefEvalBase { - - private final Sheet _sheet; - private final FormulaEvaluator _evaluator; - - - public LazyRefEval(RefPtg ptg, Sheet sheet, FormulaEvaluator evaluator) { - super(ptg.getRow(), ptg.getColumn()); - _sheet = sheet; - _evaluator = evaluator; - } - public LazyRefEval(Ref3DPtg ptg, Sheet sheet, FormulaEvaluator evaluator) { - super(ptg.getRow(), ptg.getColumn()); - _sheet = sheet; - _evaluator = evaluator; - } - - public ValueEval getInnerValueEval() { - int rowIx = getRow(); - int colIx = getColumn(); - - Row row = _sheet.getRow(rowIx); - if (row == null) { - return BlankEval.INSTANCE; - } - Cell cell = row.getCell(colIx); - if (cell == null) { - return BlankEval.INSTANCE; - } - return _evaluator.getEvalForCell(cell); - } - - public AreaEval offset(int relFirstRowIx, int relLastRowIx, int relFirstColIx, int relLastColIx) { - - AreaI area = new OffsetArea(getRow(), getColumn(), - relFirstRowIx, relLastRowIx, relFirstColIx, relLastColIx); - - return new LazyAreaEval(area, _sheet, _evaluator); - } - - public String toString() { - CellReference cr = new CellReference(getRow(), getColumn()); - StringBuffer sb = new StringBuffer(); - sb.append(getClass().getName()).append("["); - String sheetName = _evaluator.getSheetName(_sheet); - sb.append(sheetName); - sb.append('!'); - sb.append(cr.formatAsString()); - sb.append("]"); - return sb.toString(); - } -} +/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+
+package org.apache.poi.ss.formula;
+
+import org.apache.poi.hssf.record.formula.AreaI;
+import org.apache.poi.hssf.record.formula.Ref3DPtg;
+import org.apache.poi.hssf.record.formula.RefPtg;
+import org.apache.poi.hssf.record.formula.AreaI.OffsetArea;
+import org.apache.poi.hssf.record.formula.eval.AreaEval;
+import org.apache.poi.hssf.record.formula.eval.BlankEval;
+import org.apache.poi.hssf.record.formula.eval.RefEvalBase;
+import org.apache.poi.hssf.record.formula.eval.ValueEval;
+import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.Row;
+import org.apache.poi.ss.usermodel.Sheet;
+import org.apache.poi.hssf.util.CellReference;
+
+/**
+*
+* @author Josh Micich
+*/
+final class LazyRefEval extends RefEvalBase {
+
+ private final Sheet _sheet;
+ private final CellEvaluator _evaluator;
+
+
+ public LazyRefEval(RefPtg ptg, Sheet sheet, CellEvaluator evaluator) {
+ super(ptg.getRow(), ptg.getColumn());
+ _sheet = sheet;
+ _evaluator = evaluator;
+ }
+ public LazyRefEval(Ref3DPtg ptg, Sheet sheet, CellEvaluator evaluator) {
+ super(ptg.getRow(), ptg.getColumn());
+ _sheet = sheet;
+ _evaluator = evaluator;
+ }
+
+ public ValueEval getInnerValueEval() {
+ int rowIx = getRow();
+ int colIx = getColumn();
+
+ Row row = _sheet.getRow(rowIx);
+ if (row == null) {
+ return BlankEval.INSTANCE;
+ }
+ Cell cell = row.getCell(colIx);
+ if (cell == null) {
+ return BlankEval.INSTANCE;
+ }
+ return _evaluator.getEvalForCell(cell);
+ }
+
+ public AreaEval offset(int relFirstRowIx, int relLastRowIx, int relFirstColIx, int relLastColIx) {
+
+ AreaI area = new OffsetArea(getRow(), getColumn(),
+ relFirstRowIx, relLastRowIx, relFirstColIx, relLastColIx);
+
+ return new LazyAreaEval(area, _sheet, _evaluator);
+ }
+
+ public String toString() {
+ CellReference cr = new CellReference(getRow(), getColumn());
+ StringBuffer sb = new StringBuffer();
+ sb.append(getClass().getName()).append("[");
+ String sheetName = _evaluator.getSheetName(_sheet);
+ sb.append(sheetName);
+ sb.append('!');
+ sb.append(cr.formatAsString());
+ sb.append("]");
+ return sb.toString();
+ }
+}
diff --git a/src/java/org/apache/poi/hssf/model/OperandClassTransformer.java b/src/java/org/apache/poi/ss/formula/OperandClassTransformer.java index 8b7b56638b..79087e4d6d 100644 --- a/src/java/org/apache/poi/hssf/model/OperandClassTransformer.java +++ b/src/java/org/apache/poi/ss/formula/OperandClassTransformer.java @@ -15,7 +15,7 @@ limitations under the License. ==================================================================== */ -package org.apache.poi.hssf.model; +package org.apache.poi.ss.formula; import org.apache.poi.hssf.record.formula.AbstractFunctionPtg; import org.apache.poi.hssf.record.formula.ControlPtg; @@ -63,10 +63,10 @@ final class OperandClassTransformer { public void transformFormula(ParseNode rootNode) { byte rootNodeOperandClass; switch (_formulaType) { - case FormulaParser.FORMULA_TYPE_CELL: + case FormulaType.CELL: rootNodeOperandClass = Ptg.CLASS_VALUE; break; - case FormulaParser.FORMULA_TYPE_DATAVALIDATION_LIST: + case FormulaType.DATAVALIDATION_LIST: rootNodeOperandClass = Ptg.CLASS_REF; break; default: diff --git a/src/java/org/apache/poi/ss/usermodel/OperationEvaluatorFactory.java b/src/java/org/apache/poi/ss/formula/OperationEvaluatorFactory.java index ca8a92312a..eaa57c114f 100755 --- a/src/java/org/apache/poi/ss/usermodel/OperationEvaluatorFactory.java +++ b/src/java/org/apache/poi/ss/formula/OperationEvaluatorFactory.java @@ -15,7 +15,7 @@ limitations under the License. ==================================================================== */ -package org.apache.poi.ss.usermodel; +package org.apache.poi.ss.formula; import java.lang.reflect.Constructor; import java.lang.reflect.InvocationTargetException; diff --git a/src/java/org/apache/poi/hssf/model/ParseNode.java b/src/java/org/apache/poi/ss/formula/ParseNode.java index acd8cb12be..75685dee64 100644 --- a/src/java/org/apache/poi/hssf/model/ParseNode.java +++ b/src/java/org/apache/poi/ss/formula/ParseNode.java @@ -15,7 +15,7 @@ limitations under the License. ==================================================================== */ -package org.apache.poi.hssf.model; +package org.apache.poi.ss.formula; import org.apache.poi.hssf.record.formula.AttrPtg; import org.apache.poi.hssf.record.formula.FuncVarPtg; diff --git a/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java b/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java new file mode 100644 index 0000000000..e2eddbd5b1 --- /dev/null +++ b/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java @@ -0,0 +1,348 @@ +/* ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. +==================================================================== */ + +package org.apache.poi.ss.formula; + +import java.util.Stack; + +import org.apache.poi.hssf.record.formula.Area3DPtg; +import org.apache.poi.hssf.record.formula.AreaPtg; +import org.apache.poi.hssf.record.formula.BoolPtg; +import org.apache.poi.hssf.record.formula.ControlPtg; +import org.apache.poi.hssf.record.formula.ErrPtg; +import org.apache.poi.hssf.record.formula.IntPtg; +import org.apache.poi.hssf.record.formula.MemErrPtg; +import org.apache.poi.hssf.record.formula.MissingArgPtg; +import org.apache.poi.hssf.record.formula.NamePtg; +import org.apache.poi.hssf.record.formula.NameXPtg; +import org.apache.poi.hssf.record.formula.NumberPtg; +import org.apache.poi.hssf.record.formula.OperationPtg; +import org.apache.poi.hssf.record.formula.Ptg; +import org.apache.poi.hssf.record.formula.Ref3DPtg; +import org.apache.poi.hssf.record.formula.RefPtg; +import org.apache.poi.hssf.record.formula.StringPtg; +import org.apache.poi.hssf.record.formula.UnionPtg; +import org.apache.poi.hssf.record.formula.UnknownPtg; +import org.apache.poi.hssf.record.formula.eval.AreaEval; +import org.apache.poi.hssf.record.formula.eval.BlankEval; +import org.apache.poi.hssf.record.formula.eval.BoolEval; +import org.apache.poi.hssf.record.formula.eval.ErrorEval; +import org.apache.poi.hssf.record.formula.eval.Eval; +import org.apache.poi.hssf.record.formula.eval.FunctionEval; +import org.apache.poi.hssf.record.formula.eval.NameEval; +import org.apache.poi.hssf.record.formula.eval.NameXEval; +import org.apache.poi.hssf.record.formula.eval.NumberEval; +import org.apache.poi.hssf.record.formula.eval.OperationEval; +import org.apache.poi.hssf.record.formula.eval.RefEval; +import org.apache.poi.hssf.record.formula.eval.StringEval; +import org.apache.poi.hssf.record.formula.eval.ValueEval; +import org.apache.poi.hssf.util.CellReference; +import org.apache.poi.ss.usermodel.Cell; +import org.apache.poi.ss.usermodel.Sheet; + +/** + * Evaluates formula cells.<p/> + * + * For performance reasons, this class keeps a cache of all previously calculated intermediate + * cell values. Be sure to call {@link #clearCache()} if any workbook cells are changed between + * calls to evaluate~ methods on this class.<br/> + * + * For POI internal use only + * + * @author Josh Micich + */ +public class WorkbookEvaluator { + + /** + * used to track the number of evaluations + */ + private static final class Counter { + public int value; + public int depth; + public Counter() { + value = 0; + } + } + + private final EvaluationWorkbook _workbook; + private final EvaluationCache _cache; + + private Counter _evaluationCounter; + + public WorkbookEvaluator(EvaluationWorkbook workbook) { + _workbook = workbook; + _cache = new EvaluationCache(); + _evaluationCounter = new Counter(); + } + + /** + * for debug use. Used in toString methods + */ + /* package */ String getSheetName(Sheet sheet) { + return getSheetName(getSheetIndex(sheet)); + } + private String getSheetName(int sheetIndex) { + return _workbook.getSheetName(sheetIndex); + } + /** + * for debug/test use + */ + public int getEvaluationCount() { + return _evaluationCounter.value; + } + + private static boolean isDebugLogEnabled() { + return false; + } + private static void logDebug(String s) { + if (isDebugLogEnabled()) { + System.out.println(s); + } + } + + /** + * Should be called whenever there are changes to input cells in the evaluated workbook. + * Failure to call this method after changing cell values will cause incorrect behaviour + * of the evaluate~ methods of this class + */ + public void clearAllCachedResultValues() { + _cache.clear(); + } + + public void clearCachedResultValue(Sheet sheet, int rowIndex, int columnIndex) { + int sheetIndex = getSheetIndex(sheet); + _cache.clearValue(sheetIndex, rowIndex, columnIndex); + + } + private int getSheetIndex(Sheet sheet) { + // TODO cache sheet indexes too + return _workbook.getSheetIndex(sheet); + } + + public ValueEval evaluate(Cell srcCell) { + return internalEvaluate(srcCell, new EvaluationTracker(_cache)); + } + + /** + * Dev. Note: Internal evaluate must be passed only a formula cell + * else a runtime exception will be thrown somewhere inside the method. + * (Hence this is a private method.) + * @return never <code>null</code>, never {@link BlankEval} + */ + /* package */ ValueEval internalEvaluate(Cell srcCell, EvaluationTracker tracker) { + int srcRowNum = srcCell.getRowIndex(); + int srcColNum = srcCell.getCellNum(); + + ValueEval result; + + int sheetIndex = getSheetIndex(srcCell.getSheet()); + result = tracker.startEvaluate(sheetIndex, srcRowNum, srcColNum); + if (result != null) { + return result; + } + _evaluationCounter.value++; + _evaluationCounter.depth++; + + try { + Ptg[] ptgs = _workbook.getFormulaTokens(srcCell); + result = evaluateCell(sheetIndex, srcRowNum, (short)srcColNum, ptgs, tracker); + } finally { + tracker.endEvaluate(sheetIndex, srcRowNum, srcColNum, result); + _evaluationCounter.depth--; + } + if (isDebugLogEnabled()) { + String sheetName = getSheetName(sheetIndex); + CellReference cr = new CellReference(srcRowNum, srcColNum); + logDebug("Evaluated " + sheetName + "!" + cr.formatAsString() + " to " + result.toString()); + } + return result; + } + private ValueEval evaluateCell(int sheetIndex, int srcRowNum, short srcColNum, Ptg[] ptgs, EvaluationTracker tracker) { + + Stack stack = new Stack(); + for (int i = 0, iSize = ptgs.length; i < iSize; i++) { + + // since we don't know how to handle these yet :( + Ptg ptg = ptgs[i]; + if (ptg instanceof ControlPtg) { + // skip Parentheses, Attr, etc + continue; + } + if (ptg instanceof MemErrPtg) { continue; } + if (ptg instanceof MissingArgPtg) { + // TODO - might need to push BlankEval or MissingArgEval + continue; + } + Eval opResult; + if (ptg instanceof OperationPtg) { + OperationPtg optg = (OperationPtg) ptg; + + if (optg instanceof UnionPtg) { continue; } + + OperationEval operation = OperationEvaluatorFactory.create(optg); + + int numops = operation.getNumberOfOperands(); + Eval[] ops = new Eval[numops]; + + // storing the ops in reverse order since they are popping + for (int j = numops - 1; j >= 0; j--) { + Eval p = (Eval) stack.pop(); + ops[j] = p; + } +// logDebug("invoke " + operation + " (nAgs=" + numops + ")"); + opResult = invokeOperation(operation, ops, _workbook, sheetIndex, srcRowNum, srcColNum); + } else { + opResult = getEvalForPtg(ptg, sheetIndex, tracker); + } + if (opResult == null) { + throw new RuntimeException("Evaluation result must not be null"); + } +// logDebug("push " + opResult); + stack.push(opResult); + } + + ValueEval value = ((ValueEval) stack.pop()); + if (!stack.isEmpty()) { + throw new IllegalStateException("evaluation stack not empty"); + } + value = dereferenceValue(value, srcRowNum, srcColNum); + if (value == BlankEval.INSTANCE) { + // Note Excel behaviour here. A blank final final value is converted to zero. + return NumberEval.ZERO; + // Formulas _never_ evaluate to blank. If a formula appears to have evaluated to + // blank, the actual value is empty string. This can be verified with ISBLANK(). + } + return value; + } + + /** + * Dereferences a single value from any AreaEval or RefEval evaluation result. + * If the supplied evaluationResult is just a plain value, it is returned as-is. + * @return a <tt>NumberEval</tt>, <tt>StringEval</tt>, <tt>BoolEval</tt>, + * <tt>BlankEval</tt> or <tt>ErrorEval</tt>. Never <code>null</code>. + */ + private static ValueEval dereferenceValue(ValueEval evaluationResult, int srcRowNum, short srcColNum) { + if (evaluationResult instanceof RefEval) { + RefEval rv = (RefEval) evaluationResult; + return rv.getInnerValueEval(); + } + if (evaluationResult instanceof AreaEval) { + AreaEval ae = (AreaEval) evaluationResult; + if (ae.isRow()) { + if(ae.isColumn()) { + return ae.getRelativeValue(0, 0); + } + return ae.getValueAt(ae.getFirstRow(), srcColNum); + } + if (ae.isColumn()) { + return ae.getValueAt(srcRowNum, ae.getFirstColumn()); + } + return ErrorEval.VALUE_INVALID; + } + return evaluationResult; + } + + private static Eval invokeOperation(OperationEval operation, Eval[] ops, + EvaluationWorkbook workbook, int sheetIndex, int srcRowNum, int srcColNum) { + + if(operation instanceof FunctionEval) { + FunctionEval fe = (FunctionEval) operation; + if(fe.isFreeRefFunction()) { + return fe.getFreeRefFunction().evaluate(ops, workbook, sheetIndex, srcRowNum, srcColNum); + } + } + return operation.evaluate(ops, srcRowNum, (short)srcColNum); + } + + private Sheet getOtherSheet(int externSheetIndex) { + return _workbook.getSheetByExternSheetIndex(externSheetIndex); + } + + /** + * returns an appropriate Eval impl instance for the Ptg. The Ptg must be + * one of: Area3DPtg, AreaPtg, ReferencePtg, Ref3DPtg, IntPtg, NumberPtg, + * StringPtg, BoolPtg <br/>special Note: OperationPtg subtypes cannot be + * passed here! + */ + private Eval getEvalForPtg(Ptg ptg, int sheetIndex, EvaluationTracker tracker) { + if (ptg instanceof NamePtg) { + // named ranges, macro functions + NamePtg namePtg = (NamePtg) ptg; + EvaluationName nameRecord = _workbook.getName(namePtg); + if (nameRecord.isFunctionName()) { + return new NameEval(nameRecord.getNameText()); + } + if (nameRecord.hasFormula()) { + return evaluateNameFormula(nameRecord.getNameDefinition(), sheetIndex, tracker); + } + + throw new RuntimeException("Don't now how to evalate name '" + nameRecord.getNameText() + "'"); + } + if (ptg instanceof NameXPtg) { + return new NameXEval(((NameXPtg) ptg)); + } + + if (ptg instanceof IntPtg) { + return new NumberEval(((IntPtg)ptg).getValue()); + } + if (ptg instanceof NumberPtg) { + return new NumberEval(((NumberPtg)ptg).getValue()); + } + if (ptg instanceof StringPtg) { + return new StringEval(((StringPtg) ptg).getValue()); + } + if (ptg instanceof BoolPtg) { + return BoolEval.valueOf(((BoolPtg) ptg).getValue()); + } + if (ptg instanceof ErrPtg) { + return ErrorEval.valueOf(((ErrPtg) ptg).getErrorCode()); + } + CellEvaluator ce = new CellEvaluator(this, tracker); + Sheet sheet = _workbook.getSheet(sheetIndex); + if (ptg instanceof RefPtg) { + return new LazyRefEval(((RefPtg) ptg), sheet, ce); + } + if (ptg instanceof AreaPtg) { + return new LazyAreaEval(((AreaPtg) ptg), sheet, ce); + } + if (ptg instanceof Ref3DPtg) { + Ref3DPtg refPtg = (Ref3DPtg) ptg; + Sheet xsheet = getOtherSheet(refPtg.getExternSheetIndex()); + return new LazyRefEval(refPtg, xsheet, ce); + } + if (ptg instanceof Area3DPtg) { + Area3DPtg a3dp = (Area3DPtg) ptg; + Sheet xsheet = getOtherSheet(a3dp.getExternSheetIndex()); + return new LazyAreaEval(a3dp, xsheet, ce); + } + + if (ptg instanceof UnknownPtg) { + // POI uses UnknownPtg when the encoded Ptg array seems to be corrupted. + // This seems to occur in very rare cases (e.g. unused name formulas in bug 44774, attachment 21790) + // In any case, formulas are re-parsed before execution, so UnknownPtg should not get here + throw new RuntimeException("UnknownPtg not allowed"); + } + + throw new RuntimeException("Unexpected ptg class (" + ptg.getClass().getName() + ")"); + } + private Eval evaluateNameFormula(Ptg[] ptgs, int sheetIndex, EvaluationTracker tracker) { + if (ptgs.length > 1) { + throw new RuntimeException("Complex name formulas not supported yet"); + } + return getEvalForPtg(ptgs[0], sheetIndex, tracker); + } +} diff --git a/src/java/org/apache/poi/ss/usermodel/CellValue.java b/src/java/org/apache/poi/ss/usermodel/CellValue.java new file mode 100644 index 0000000000..705f557101 --- /dev/null +++ b/src/java/org/apache/poi/ss/usermodel/CellValue.java @@ -0,0 +1,114 @@ +/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+
+package org.apache.poi.ss.usermodel;
+
+import org.apache.poi.hssf.record.formula.eval.ErrorEval;
+import org.apache.poi.ss.usermodel.Cell;
+
+/**
+ * Mimics the 'data view' of a cell. This allows formula evaluator
+ * to return a CellValue instead of precasting the value to String
+ * or Number or boolean type.
+ * @author Amol S. Deshmukh < amolweb at ya hoo dot com >
+ */
+public final class CellValue {
+ public static final CellValue TRUE = new CellValue(Cell.CELL_TYPE_BOOLEAN, 0.0, true, null, 0);
+ public static final CellValue FALSE= new CellValue(Cell.CELL_TYPE_BOOLEAN, 0.0, false, null, 0);
+
+ private final int _cellType;
+ private final double _numberValue;
+ private final boolean _booleanValue;
+ private final String _textValue;
+ private final int _errorCode;
+
+ private CellValue(int cellType, double numberValue, boolean booleanValue,
+ String textValue, int errorCode) {
+ _cellType = cellType;
+ _numberValue = numberValue;
+ _booleanValue = booleanValue;
+ _textValue = textValue;
+ _errorCode = errorCode;
+ }
+
+
+ public CellValue(double numberValue) {
+ this(Cell.CELL_TYPE_NUMERIC, numberValue, false, null, 0);
+ }
+ public static CellValue valueOf(boolean booleanValue) {
+ return booleanValue ? TRUE : FALSE;
+ }
+ public CellValue(String stringValue) {
+ this(Cell.CELL_TYPE_STRING, 0.0, false, stringValue, 0);
+ }
+ public static CellValue getError(int errorCode) {
+ return new CellValue(Cell.CELL_TYPE_ERROR, 0.0, false, null, errorCode);
+ }
+
+
+ /**
+ * @return Returns the booleanValue.
+ */
+ public boolean getBooleanValue() {
+ return _booleanValue;
+ }
+ /**
+ * @return Returns the numberValue.
+ */
+ public double getNumberValue() {
+ return _numberValue;
+ }
+ /**
+ * @return Returns the stringValue.
+ */
+ public String getStringValue() {
+ return _textValue;
+ }
+ /**
+ * @return Returns the cellType.
+ */
+ public int getCellType() {
+ return _cellType;
+ }
+ /**
+ * @return Returns the errorValue.
+ */
+ public byte getErrorValue() {
+ return (byte) _errorCode;
+ }
+ public String toString() {
+ StringBuffer sb = new StringBuffer(64);
+ sb.append(getClass().getName()).append(" [");
+ sb.append(formatAsString());
+ sb.append("]");
+ return sb.toString();
+ }
+
+ public String formatAsString() {
+ switch (_cellType) {
+ case Cell.CELL_TYPE_NUMERIC:
+ return String.valueOf(_numberValue);
+ case Cell.CELL_TYPE_STRING:
+ return '"' + _textValue + '"';
+ case Cell.CELL_TYPE_BOOLEAN:
+ return _booleanValue ? "TRUE" : "FALSE";
+ case Cell.CELL_TYPE_ERROR:
+ return ErrorEval.getText(_errorCode);
+ }
+ return "<error unexpected cell type " + _cellType + ">";
+ }
+}
diff --git a/src/java/org/apache/poi/ss/usermodel/FormulaEvaluator.java b/src/java/org/apache/poi/ss/usermodel/FormulaEvaluator.java index 0f1b7bc47c..1234c9564f 100644 --- a/src/java/org/apache/poi/ss/usermodel/FormulaEvaluator.java +++ b/src/java/org/apache/poi/ss/usermodel/FormulaEvaluator.java @@ -17,43 +17,6 @@ package org.apache.poi.ss.usermodel; -import java.util.Iterator; -import java.util.Stack; - -import org.apache.poi.hssf.model.FormulaParser; -import org.apache.poi.hssf.record.NameRecord; -import org.apache.poi.hssf.record.formula.Area3DPtg; -import org.apache.poi.hssf.record.formula.AreaPtg; -import org.apache.poi.hssf.record.formula.BoolPtg; -import org.apache.poi.hssf.record.formula.ControlPtg; -import org.apache.poi.hssf.record.formula.ErrPtg; -import org.apache.poi.hssf.record.formula.IntPtg; -import org.apache.poi.hssf.record.formula.MemErrPtg; -import org.apache.poi.hssf.record.formula.MissingArgPtg; -import org.apache.poi.hssf.record.formula.NamePtg; -import org.apache.poi.hssf.record.formula.NameXPtg; -import org.apache.poi.hssf.record.formula.NumberPtg; -import org.apache.poi.hssf.record.formula.OperationPtg; -import org.apache.poi.hssf.record.formula.Ptg; -import org.apache.poi.hssf.record.formula.Ref3DPtg; -import org.apache.poi.hssf.record.formula.RefPtg; -import org.apache.poi.hssf.record.formula.StringPtg; -import org.apache.poi.hssf.record.formula.UnionPtg; -import org.apache.poi.hssf.record.formula.UnknownPtg; -import org.apache.poi.hssf.record.formula.eval.AreaEval; -import org.apache.poi.hssf.record.formula.eval.BlankEval; -import org.apache.poi.hssf.record.formula.eval.BoolEval; -import org.apache.poi.hssf.record.formula.eval.ErrorEval; -import org.apache.poi.hssf.record.formula.eval.Eval; -import org.apache.poi.hssf.record.formula.eval.FunctionEval; -import org.apache.poi.hssf.record.formula.eval.NameEval; -import org.apache.poi.hssf.record.formula.eval.NameXEval; -import org.apache.poi.hssf.record.formula.eval.NumberEval; -import org.apache.poi.hssf.record.formula.eval.OperationEval; -import org.apache.poi.hssf.record.formula.eval.RefEval; -import org.apache.poi.hssf.record.formula.eval.StringEval; -import org.apache.poi.hssf.record.formula.eval.ValueEval; -import org.apache.poi.hssf.util.CellReference; /** * Evaluates formula cells.<p/> @@ -65,85 +28,15 @@ import org.apache.poi.hssf.util.CellReference; * @author Amol S. Deshmukh < amolweb at ya hoo dot com > * @author Josh Micich */ -public class FormulaEvaluator { - - /** - * used to track the number of evaluations - */ - private static final class Counter { - public int value; - public int depth; - public Counter() { - value = 0; - } - } - - protected final Workbook _workbook; - private final EvaluationCache _cache; - - private Counter _evaluationCounter; - - /** - * @deprecated (Sep 2008) Sheet parameter is ignored - */ - public FormulaEvaluator(Sheet sheet, Workbook workbook) { - this(workbook); - if (false) { - sheet.toString(); // suppress unused parameter compiler warning - } - } - public FormulaEvaluator(Workbook workbook) { - this(workbook, new EvaluationCache(), new Counter()); - } - - private FormulaEvaluator(Workbook workbook, EvaluationCache cache, Counter evaluationCounter) { - _workbook = workbook; - _cache = cache; - _evaluationCounter = evaluationCounter; - } - - /** - * for debug use. Used in toString methods - */ - public String getSheetName(Sheet sheet) { - return _workbook.getSheetName(_workbook.getSheetIndex(sheet)); - } - /** - * for debug/test use - */ - public int getEvaluationCount() { - return _evaluationCounter.value; - } - - private static boolean isDebugLogEnabled() { - return false; - } - private static void logDebug(String s) { - if (isDebugLogEnabled()) { - System.out.println(s); - } - } - - /** - * Does nothing - * @deprecated (Aug 2008) - not needed, since the current row can be derived from the cell - */ - public void setCurrentRow(Row row) { - // do nothing - if (false) { - row.getClass(); // suppress unused parameter compiler warning - } - } +public interface FormulaEvaluator { /** * Should be called whenever there are changes to input cells in the evaluated workbook. * Failure to call this method after changing cell values will cause incorrect behaviour * of the evaluate~ methods of this class */ - public void clearCache() { - _cache.clear(); - } - + void clearAllCachedResultValues(); + void clearCachedResultValue(Sheet sheet, int rowIndex, int columnIndex); /** * If cell contains a formula, the formula is evaluated and returned, * else the CellValue simply copies the appropriate cell value from @@ -152,25 +45,7 @@ public class FormulaEvaluator { * original cell. * @param cell */ - public CellValue evaluate(Cell cell) { - if (cell == null) { - return null; - } - - switch (cell.getCellType()) { - case Cell.CELL_TYPE_BOOLEAN: - return CellValue.valueOf(cell.getBooleanCellValue()); - case Cell.CELL_TYPE_ERROR: - return CellValue.getError(cell.getErrorCellValue()); - case Cell.CELL_TYPE_FORMULA: - return evaluateFormulaCellValue(cell); - case Cell.CELL_TYPE_NUMERIC: - return new CellValue(cell.getNumericCellValue(), _workbook.getCreationHelper()); - case Cell.CELL_TYPE_STRING: - return new CellValue(cell.getRichStringCellValue().getString(), _workbook.getCreationHelper()); - } - throw new IllegalStateException("Bad cell type (" + cell.getCellType() + ")"); - } + CellValue evaluate(Cell cell); /** @@ -191,15 +66,7 @@ public class FormulaEvaluator { * @param cell The cell to evaluate * @return The type of the formula result (the cell's type remains as Cell.CELL_TYPE_FORMULA however) */ - public int evaluateFormulaCell(Cell cell) { - if (cell == null || cell.getCellType() != Cell.CELL_TYPE_FORMULA) { - return -1; - } - CellValue cv = evaluateFormulaCellValue(cell); - // cell remains a formula cell, but the cached value is changed - setCellValue(cell, cv); - return cv.getCellType(); - } + int evaluateFormulaCell(Cell cell); /** * If cell contains formula, it evaluates the formula, and @@ -217,462 +84,5 @@ public class FormulaEvaluator { * value computed for you, use {@link #evaluateFormulaCell(Cell)} * @param cell */ - public Cell evaluateInCell(Cell cell) { - if (cell == null) { - return null; - } - if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { - CellValue cv = evaluateFormulaCellValue(cell); - setCellType(cell, cv); // cell will no longer be a formula cell - setCellValue(cell, cv); - } - return cell; - } - private static void setCellType(Cell cell, CellValue cv) { - int cellType = cv.getCellType(); - switch (cellType) { - case Cell.CELL_TYPE_BOOLEAN: - case Cell.CELL_TYPE_ERROR: - case Cell.CELL_TYPE_NUMERIC: - case Cell.CELL_TYPE_STRING: - cell.setCellType(cellType); - return; - case Cell.CELL_TYPE_BLANK: - // never happens - blanks eventually get translated to zero - case Cell.CELL_TYPE_FORMULA: - // this will never happen, we have already evaluated the formula - } - throw new IllegalStateException("Unexpected cell value type (" + cellType + ")"); - } - - private static void setCellValue(Cell cell, CellValue cv) { - int cellType = cv.getCellType(); - switch (cellType) { - case Cell.CELL_TYPE_BOOLEAN: - cell.setCellValue(cv.getBooleanValue()); - break; - case Cell.CELL_TYPE_ERROR: - cell.setCellErrorValue(cv.getErrorValue()); - break; - case Cell.CELL_TYPE_NUMERIC: - cell.setCellValue(cv.getNumberValue()); - break; - case Cell.CELL_TYPE_STRING: - cell.setCellValue(cv.getRichTextStringValue()); - break; - case Cell.CELL_TYPE_BLANK: - // never happens - blanks eventually get translated to zero - case Cell.CELL_TYPE_FORMULA: - // this will never happen, we have already evaluated the formula - default: - throw new IllegalStateException("Unexpected cell value type (" + cellType + ")"); - } - } - - /** - * Loops over all cells in all sheets of the supplied - * workbook. - * For cells that contain formulas, their formulas are - * evaluated, and the results are saved. These cells - * remain as formula cells. - * For cells that do not contain formulas, no changes - * are made. - * This is a helpful wrapper around looping over all - * cells, and calling evaluateFormulaCell on each one. - */ - public static void evaluateAllFormulaCells(Workbook wb) { - FormulaEvaluator evaluator = new FormulaEvaluator(wb); - for(int i=0; i<wb.getNumberOfSheets(); i++) { - Sheet sheet = wb.getSheetAt(i); - - for (Iterator rit = sheet.rowIterator(); rit.hasNext();) { - Row r = (Row)rit.next(); - - for (Iterator cit = r.cellIterator(); cit.hasNext();) { - Cell c = (Cell)cit.next(); - if (c.getCellType() == Cell.CELL_TYPE_FORMULA) - evaluator.evaluateFormulaCell(c); - } - } - } - } - - /** - * Returns a CellValue wrapper around the supplied ValueEval instance. - * @param eval - */ - private CellValue evaluateFormulaCellValue(Cell cell) { - ValueEval eval = internalEvaluate(cell); - if (eval instanceof NumberEval) { - NumberEval ne = (NumberEval) eval; - return new CellValue(ne.getNumberValue(), _workbook.getCreationHelper()); - } - if (eval instanceof BoolEval) { - BoolEval be = (BoolEval) eval; - return CellValue.valueOf(be.getBooleanValue()); - } - if (eval instanceof StringEval) { - StringEval ne = (StringEval) eval; - return new CellValue(ne.getStringValue(), _workbook.getCreationHelper()); - } - if (eval instanceof ErrorEval) { - return CellValue.getError(((ErrorEval)eval).getErrorCode()); - } - throw new RuntimeException("Unexpected eval class (" + eval.getClass().getName() + ")"); - } - - /** - * Dev. Note: Internal evaluate must be passed only a formula cell - * else a runtime exception will be thrown somewhere inside the method. - * (Hence this is a private method.) - * @return never <code>null</code>, never {@link BlankEval} - */ - private ValueEval internalEvaluate(Cell srcCell) { - int srcRowNum = srcCell.getRowIndex(); - int srcColNum = srcCell.getCellNum(); - - ValueEval result; - - int sheetIndex = _workbook.getSheetIndex(srcCell.getSheet()); - result = _cache.getValue(sheetIndex, srcRowNum, srcColNum); - if (result != null) { - return result; - } - _evaluationCounter.value++; - _evaluationCounter.depth++; - - EvaluationCycleDetector tracker = EvaluationCycleDetectorManager.getTracker(); - - if(!tracker.startEvaluate(_workbook, sheetIndex, srcRowNum, srcColNum)) { - return ErrorEval.CIRCULAR_REF_ERROR; - } - try { - result = evaluateCell(sheetIndex, srcRowNum, (short)srcColNum, srcCell.getCellFormula()); - } finally { - tracker.endEvaluate(_workbook, sheetIndex, srcRowNum, srcColNum); - _cache.setValue(sheetIndex, srcRowNum, srcColNum, result); - _evaluationCounter.depth--; - } - if (isDebugLogEnabled()) { - String sheetName = _workbook.getSheetName(sheetIndex); - CellReference cr = new CellReference(srcRowNum, srcColNum); - logDebug("Evaluated " + sheetName + "!" + cr.formatAsString() + " to " + result.toString()); - } - return result; - } - private ValueEval evaluateCell(int sheetIndex, int srcRowNum, short srcColNum, String cellFormulaText) { - - Ptg[] ptgs = FormulaParser.parse(cellFormulaText, _workbook); - - Stack stack = new Stack(); - for (int i = 0, iSize = ptgs.length; i < iSize; i++) { - - // since we don't know how to handle these yet :( - Ptg ptg = ptgs[i]; - if (ptg instanceof ControlPtg) { - // skip Parentheses, Attr, etc - continue; - } - if (ptg instanceof MemErrPtg) { continue; } - if (ptg instanceof MissingArgPtg) { - // TODO - might need to push BlankEval or MissingArgEval - continue; - } - Eval opResult; - if (ptg instanceof OperationPtg) { - OperationPtg optg = (OperationPtg) ptg; - - if (optg instanceof UnionPtg) { continue; } - - OperationEval operation = OperationEvaluatorFactory.create(optg); - - int numops = operation.getNumberOfOperands(); - Eval[] ops = new Eval[numops]; - - // storing the ops in reverse order since they are popping - for (int j = numops - 1; j >= 0; j--) { - Eval p = (Eval) stack.pop(); - ops[j] = p; - } -// logDebug("invoke " + operation + " (nAgs=" + numops + ")"); - opResult = invokeOperation(operation, ops, _workbook, sheetIndex, srcRowNum, srcColNum); - } else { - opResult = getEvalForPtg(ptg, sheetIndex); - } - if (opResult == null) { - throw new RuntimeException("Evaluation result must not be null"); - } -// logDebug("push " + opResult); - stack.push(opResult); - } - - ValueEval value = ((ValueEval) stack.pop()); - if (!stack.isEmpty()) { - throw new IllegalStateException("evaluation stack not empty"); - } - value = dereferenceValue(value, srcRowNum, srcColNum); - if (value == BlankEval.INSTANCE) { - // Note Excel behaviour here. A blank final final value is converted to zero. - return NumberEval.ZERO; - // Formulas _never_ evaluate to blank. If a formula appears to have evaluated to - // blank, the actual value is empty string. This can be verified with ISBLANK(). - } - return value; - } - - /** - * Dereferences a single value from any AreaEval or RefEval evaluation result. - * If the supplied evaluationResult is just a plain value, it is returned as-is. - * @return a <tt>NumberEval</tt>, <tt>StringEval</tt>, <tt>BoolEval</tt>, - * <tt>BlankEval</tt> or <tt>ErrorEval</tt>. Never <code>null</code>. - */ - private static ValueEval dereferenceValue(ValueEval evaluationResult, int srcRowNum, short srcColNum) { - if (evaluationResult instanceof RefEval) { - RefEval rv = (RefEval) evaluationResult; - return rv.getInnerValueEval(); - } - if (evaluationResult instanceof AreaEval) { - AreaEval ae = (AreaEval) evaluationResult; - if (ae.isRow()) { - if(ae.isColumn()) { - return ae.getRelativeValue(0, 0); - } - return ae.getValueAt(ae.getFirstRow(), srcColNum); - } - if (ae.isColumn()) { - return ae.getValueAt(srcRowNum, ae.getFirstColumn()); - } - return ErrorEval.VALUE_INVALID; - } - return evaluationResult; - } - - private static Eval invokeOperation(OperationEval operation, Eval[] ops, - Workbook workbook, int sheetIndex, int srcRowNum, int srcColNum) { - - if(operation instanceof FunctionEval) { - FunctionEval fe = (FunctionEval) operation; - if(fe.isFreeRefFunction()) { - return fe.getFreeRefFunction().evaluate(ops, workbook, sheetIndex, srcRowNum, srcColNum); - } - } - return operation.evaluate(ops, srcRowNum, (short)srcColNum); - } - - private Sheet getOtherSheet(int externSheetIndex) { - return _workbook.getSheetAt(_workbook.getSheetIndexFromExternSheetIndex(externSheetIndex)); - } - - /** - * returns an appropriate Eval impl instance for the Ptg. The Ptg must be - * one of: Area3DPtg, AreaPtg, ReferencePtg, Ref3DPtg, IntPtg, NumberPtg, - * StringPtg, BoolPtg <br/>special Note: OperationPtg subtypes cannot be - * passed here! - */ - private Eval getEvalForPtg(Ptg ptg, int sheetIndex) { - if (ptg instanceof NamePtg) { - // named ranges, macro functions - NamePtg namePtg = (NamePtg) ptg; - int numberOfNames = _workbook.getNumberOfNames(); - int nameIndex = namePtg.getIndex(); - if(nameIndex < 0 || nameIndex >= numberOfNames) { - throw new RuntimeException("Bad name index (" + nameIndex - + "). Allowed range is (0.." + (numberOfNames-1) + ")"); - } - if(_workbook instanceof org.apache.poi.hssf.usermodel.HSSFWorkbook) { - NameRecord nameRecord = ((org.apache.poi.hssf.usermodel.HSSFWorkbook)_workbook).getNameRecord(nameIndex); - if (nameRecord.isFunctionName()) { - return new NameEval(nameRecord.getNameText()); - } - if (nameRecord.hasFormula()) { - return evaluateNameFormula(nameRecord.getNameDefinition(), sheetIndex); - } - - throw new RuntimeException("Don't now how to evalate name '" + nameRecord.getNameText() + "'"); - } - throw new RuntimeException("Don't now how to evalate name for XSSFWorkbook"); - } - if (ptg instanceof NameXPtg) { - NameXPtg nameXPtg = (NameXPtg) ptg; - return new NameXEval(nameXPtg.getSheetRefIndex(), nameXPtg.getNameIndex()); - } - - if (ptg instanceof IntPtg) { - return new NumberEval(((IntPtg)ptg).getValue()); - } - if (ptg instanceof NumberPtg) { - return new NumberEval(((NumberPtg)ptg).getValue()); - } - if (ptg instanceof StringPtg) { - return new StringEval(((StringPtg) ptg).getValue()); - } - if (ptg instanceof BoolPtg) { - return BoolEval.valueOf(((BoolPtg) ptg).getValue()); - } - if (ptg instanceof ErrPtg) { - return ErrorEval.valueOf(((ErrPtg) ptg).getErrorCode()); - } - Sheet sheet = _workbook.getSheetAt(sheetIndex); - if (ptg instanceof RefPtg) { - return new LazyRefEval(((RefPtg) ptg), sheet, this); - } - if (ptg instanceof AreaPtg) { - return new LazyAreaEval(((AreaPtg) ptg), sheet, this); - } - if (ptg instanceof Ref3DPtg) { - Ref3DPtg refPtg = (Ref3DPtg) ptg; - Sheet xsheet = getOtherSheet(refPtg.getExternSheetIndex()); - return new LazyRefEval(refPtg, xsheet, this); - } - if (ptg instanceof Area3DPtg) { - Area3DPtg a3dp = (Area3DPtg) ptg; - Sheet xsheet = getOtherSheet(a3dp.getExternSheetIndex()); - return new LazyAreaEval(a3dp, xsheet, this); - } - - if (ptg instanceof UnknownPtg) { - // POI uses UnknownPtg when the encoded Ptg array seems to be corrupted. - // This seems to occur in very rare cases (e.g. unused name formulas in bug 44774, attachment 21790) - // In any case, formulas are re-parsed before execution, so UnknownPtg should not get here - throw new RuntimeException("UnknownPtg not allowed"); - } - - throw new RuntimeException("Unexpected ptg class (" + ptg.getClass().getName() + ")"); - } - private Eval evaluateNameFormula(Ptg[] ptgs, int sheetIndex) { - if (ptgs.length > 1) { - throw new RuntimeException("Complex name formulas not supported yet"); - } - return getEvalForPtg(ptgs[0], sheetIndex); - } - - /** - * Given a cell, find its type and from that create an appropriate ValueEval - * impl instance and return that. Since the cell could be an external - * reference, we need the sheet that this belongs to. - * Non existent cells are treated as empty. - */ - public ValueEval getEvalForCell(Cell cell) { - - if (cell == null) { - return BlankEval.INSTANCE; - } - switch (cell.getCellType()) { - case Cell.CELL_TYPE_NUMERIC: - return new NumberEval(cell.getNumericCellValue()); - case Cell.CELL_TYPE_STRING: - return new StringEval(cell.getRichStringCellValue().getString()); - case Cell.CELL_TYPE_FORMULA: - return internalEvaluate(cell); - case Cell.CELL_TYPE_BOOLEAN: - return BoolEval.valueOf(cell.getBooleanCellValue()); - case Cell.CELL_TYPE_BLANK: - return BlankEval.INSTANCE; - case Cell.CELL_TYPE_ERROR: - return ErrorEval.valueOf(cell.getErrorCellValue()); - } - throw new RuntimeException("Unexpected cell type (" + cell.getCellType() + ")"); - } - - /** - * Mimics the 'data view' of a cell. This allows formula evaluator - * to return a CellValue instead of precasting the value to String - * or Number or boolean type. - * @author Amol S. Deshmukh < amolweb at ya hoo dot com > - */ - public static final class CellValue { - public static final CellValue TRUE = new CellValue(Cell.CELL_TYPE_BOOLEAN, 0.0, true, null, 0, null); - public static final CellValue FALSE= new CellValue(Cell.CELL_TYPE_BOOLEAN, 0.0, false, null, 0, null); - - private final int _cellType; - private final double _numberValue; - private final boolean _booleanValue; - private final String _textValue; - private final int _errorCode; - private CreationHelper _creationHelper; - - private CellValue(int cellType, double numberValue, boolean booleanValue, - String textValue, int errorCode, CreationHelper creationHelper) { - _cellType = cellType; - _numberValue = numberValue; - _booleanValue = booleanValue; - _textValue = textValue; - _errorCode = errorCode; - _creationHelper = creationHelper; - } - - - /* package*/ CellValue(double numberValue, CreationHelper creationHelper) { - this(Cell.CELL_TYPE_NUMERIC, numberValue, false, null, 0, creationHelper); - } - /* package*/ static CellValue valueOf(boolean booleanValue) { - return booleanValue ? TRUE : FALSE; - } - /* package*/ CellValue(String stringValue, CreationHelper creationHelper) { - this(Cell.CELL_TYPE_STRING, 0.0, false, stringValue, 0, creationHelper); - } - /* package*/ static CellValue getError(int errorCode) { - return new CellValue(Cell.CELL_TYPE_ERROR, 0.0, false, null, errorCode, null); - } - - - /** - * @return Returns the booleanValue. - */ - public boolean getBooleanValue() { - return _booleanValue; - } - /** - * @return Returns the numberValue. - */ - public double getNumberValue() { - return _numberValue; - } - /** - * @return Returns the stringValue. - */ - public String getStringValue() { - return _textValue; - } - /** - * @return Returns the cellType. - */ - public int getCellType() { - return _cellType; - } - /** - * @return Returns the errorValue. - */ - public byte getErrorValue() { - return (byte) _errorCode; - } - /** - * @return Returns the richTextStringValue. - * @deprecated (Sep 2008) Text formatting is lost during formula evaluation. Use {@link #getStringValue()} - */ - public RichTextString getRichTextStringValue() { - return _creationHelper.createRichTextString(_textValue); - } - public String toString() { - StringBuffer sb = new StringBuffer(64); - sb.append(getClass().getName()).append(" ["); - sb.append(formatAsString()); - sb.append("]"); - return sb.toString(); - } - - public String formatAsString() { - switch (_cellType) { - case Cell.CELL_TYPE_NUMERIC: - return String.valueOf(_numberValue); - case Cell.CELL_TYPE_STRING: - return '"' + _textValue + '"'; - case Cell.CELL_TYPE_BOOLEAN: - return _booleanValue ? "TRUE" : "FALSE"; - case Cell.CELL_TYPE_ERROR: - return ErrorEval.getText(_errorCode); - } - return "<error unexpected cell type " + _cellType + ">"; - } - } + Cell evaluateInCell(Cell cell); } diff --git a/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Workbook.java b/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Workbook.java index bcf1b4ee96..40c5006874 100644 --- a/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Workbook.java +++ b/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Workbook.java @@ -122,15 +122,6 @@ public interface Workbook { int getSheetIndex(Sheet sheet); /** - * Returns the external sheet index of the sheet - * with the given internal index, creating one - * if needed. - * Used by some of the more obscure formula and - * named range things. - */ - int getExternalSheetIndex(int internalSheetIndex); - - /** * create an HSSFSheet for this HSSFWorkbook, adds it to the sheets and returns * the high level representation. Use this to create new sheets. * @@ -165,14 +156,6 @@ public interface Workbook { int getNumberOfSheets(); /** - * Finds the sheet index for a particular external sheet number. - * @param externSheetNumber The external sheet number to convert - * @return The index to the sheet found. - */ - int getSheetIndexFromExternSheetIndex(int externSheetNumber); - - - /** * Get the HSSFSheet object at the given index. * @param index of the sheet number (0-based physical & logical) * @return HSSFSheet at the provided index @@ -194,8 +177,6 @@ public interface Workbook { */ void removeSheetAt(int index); - - String findSheetNameFromExternSheet(int externSheetIndex); /** * determine whether the Excel GUI will backup the workbook when saving. @@ -334,14 +315,6 @@ public interface Workbook { String getNameName(int index); /** - * TODO - make this less cryptic / move elsewhere - * @param refIndex Index to REF entry in EXTERNSHEET record in the Link Table - * @param definedNameIndex zero-based to DEFINEDNAME or EXTERNALNAME record - * @return the string representation of the defined or external name - */ - String resolveNameXText(int refIndex, int definedNameIndex); - - /** * Sets the printarea for the sheet provided * <p> * i.e. Reference = $A$1:$B$2 diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java new file mode 100644 index 0000000000..2d442e8f4a --- /dev/null +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java @@ -0,0 +1,167 @@ +package org.apache.poi.xssf.usermodel;
+
+import org.apache.poi.hssf.record.formula.NamePtg;
+import org.apache.poi.hssf.record.formula.NameXPtg;
+import org.apache.poi.hssf.record.formula.Ptg;
+import org.apache.poi.ss.formula.EvaluationName;
+import org.apache.poi.ss.formula.EvaluationWorkbook;
+import org.apache.poi.ss.formula.FormulaParser;
+import org.apache.poi.ss.formula.FormulaParsingWorkbook;
+import org.apache.poi.ss.formula.FormulaRenderingWorkbook;
+import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.Sheet;
+import org.apache.poi.ss.usermodel.Workbook;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDefinedName;
+
+/**
+ * Internal POI use only
+ *
+ * @author Josh Micich
+ */
+public final class XSSFEvaluationWorkbook implements FormulaRenderingWorkbook, EvaluationWorkbook, FormulaParsingWorkbook {
+
+ private final XSSFWorkbook _uBook;
+
+ public static XSSFEvaluationWorkbook create(XSSFWorkbook book) {
+ if (book == null) {
+ return null;
+ }
+ return new XSSFEvaluationWorkbook(book);
+ }
+
+ private XSSFEvaluationWorkbook(XSSFWorkbook book) {
+ _uBook = book;
+ }
+
+ private int convertFromExternalSheetIndex(int externSheetIndex) {
+ return externSheetIndex;
+ }
+ /**
+ * @returns the external sheet index of the sheet with the given internal
+ * index, creating one if needed. Used by some of the more obscure
+ * formula and named range things. Fairly easy on XSSF (we
+ * think...) since the internal and external indicies are the same
+ */
+ private int convertToExternalSheetIndex(int sheetIndex) {
+ return sheetIndex;
+ }
+
+ public int getExternalSheetIndex(String sheetName) {
+ int sheetIndex = _uBook.getSheetIndex(sheetName);
+ return convertToExternalSheetIndex(sheetIndex);
+ }
+
+ public EvaluationName getName(int index) {
+ return new Name(_uBook.getNameAt(index), index, this);
+ }
+
+ public EvaluationName getName(String name) {
+ for(int i=0; i < _uBook.getNumberOfNames(); i++) {
+ String nameText = _uBook.getNameName(i);
+ if (name.equalsIgnoreCase(nameText)) {
+ return new Name(_uBook.getNameAt(i), i, this);
+ }
+ }
+ return null;
+ }
+
+ public int getSheetIndex(Sheet sheet) {
+ return _uBook.getSheetIndex(sheet);
+ }
+
+ public String getSheetName(int sheetIndex) {
+ return _uBook.getSheetName(sheetIndex);
+ }
+
+ public int getNameIndex(String name) {
+ return _uBook.getNameIndex(name);
+ }
+
+ public NameXPtg getNameXPtg(String name) {
+ // may require to return null to make tests pass
+ throw new RuntimeException("Not implemented yet");
+ }
+
+ public Sheet getSheet(int sheetIndex) {
+ return _uBook.getSheetAt(sheetIndex);
+ }
+
+ /**
+ * Doesn't do anything - returns the same index
+ * TODO - figure out if this is a ole2 specific thing, or
+ * if we need to do something proper here too!
+ */
+ public Sheet getSheetByExternSheetIndex(int externSheetIndex) {
+ int sheetIndex = convertFromExternalSheetIndex(externSheetIndex);
+ return _uBook.getSheetAt(sheetIndex);
+ }
+
+ public Workbook getWorkbook() {
+ return _uBook;
+ }
+
+ /**
+ * TODO - figure out what the hell this methods does in
+ * HSSF...
+ */
+ public String resolveNameXText(NameXPtg n) {
+ throw new RuntimeException("method not implemented yet");
+ }
+
+ public String getSheetNameByExternSheet(int externSheetIndex) {
+ int sheetIndex = convertFromExternalSheetIndex(externSheetIndex);
+ return _uBook.getSheetName(sheetIndex);
+ }
+
+ public String getNameText(NamePtg namePtg) {
+ return _uBook.getNameAt(namePtg.getIndex()).getNameName();
+ }
+ public EvaluationName getName(NamePtg namePtg) {
+ int ix = namePtg.getIndex();
+ return new Name(_uBook.getNameAt(ix), ix, this);
+ }
+ public Ptg[] getFormulaTokens(Cell cell) {
+ XSSFEvaluationWorkbook frBook = XSSFEvaluationWorkbook.create(_uBook);
+ return FormulaParser.parse(cell.getCellFormula(), frBook);
+ }
+
+ private static final class Name implements EvaluationName {
+
+ private final XSSFName _nameRecord;
+ private final int _index;
+ private final FormulaParsingWorkbook _fpBook;
+
+ public Name(XSSFName name, int index, FormulaParsingWorkbook fpBook) {
+ _nameRecord = name;
+ _index = index;
+ _fpBook = fpBook;
+ }
+
+ public Ptg[] getNameDefinition() {
+
+ return FormulaParser.parse(_nameRecord.getReference(), _fpBook);
+ }
+
+ public String getNameText() {
+ return _nameRecord.getNameName();
+ }
+
+ public boolean hasFormula() {
+ // TODO - no idea if this is right
+ CTDefinedName ctn = _nameRecord.getCTName();
+ String strVal = ctn.getStringValue();
+ return !ctn.getFunction() && strVal != null && strVal.length() > 0;
+ }
+
+ public boolean isFunctionName() {
+ return _nameRecord.isFunctionName();
+ }
+
+ public boolean isRange() {
+ return hasFormula(); // TODO - is this right?
+ }
+ public NamePtg createPtg() {
+ return new NamePtg(_index);
+ }
+ }
+}
diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFFormulaEvaluator.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFFormulaEvaluator.java new file mode 100644 index 0000000000..3dcedf8f5e --- /dev/null +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFFormulaEvaluator.java @@ -0,0 +1,256 @@ +/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+
+package org.apache.poi.xssf.usermodel;
+
+import java.util.Iterator;
+
+import org.apache.poi.hssf.record.formula.eval.BoolEval;
+import org.apache.poi.hssf.record.formula.eval.ErrorEval;
+import org.apache.poi.hssf.record.formula.eval.NumberEval;
+import org.apache.poi.hssf.record.formula.eval.StringEval;
+import org.apache.poi.hssf.record.formula.eval.ValueEval;
+import org.apache.poi.hssf.usermodel.HSSFCell;
+import org.apache.poi.ss.formula.WorkbookEvaluator;
+import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.CellValue;
+import org.apache.poi.ss.usermodel.FormulaEvaluator;
+import org.apache.poi.ss.usermodel.Row;
+import org.apache.poi.ss.usermodel.Sheet;
+
+/**
+ * Evaluates formula cells.<p/>
+ *
+ * For performance reasons, this class keeps a cache of all previously calculated intermediate
+ * cell values. Be sure to call {@link #clearCache()} if any workbook cells are changed between
+ * calls to evaluate~ methods on this class.
+ *
+ * @author Amol S. Deshmukh < amolweb at ya hoo dot com >
+ * @author Josh Micich
+ */
+public class XSSFFormulaEvaluator implements FormulaEvaluator {
+
+ private WorkbookEvaluator _bookEvaluator;
+
+ public XSSFFormulaEvaluator(XSSFWorkbook workbook) {
+ _bookEvaluator = new WorkbookEvaluator(XSSFEvaluationWorkbook.create(workbook));
+ }
+
+ /**
+ * TODO for debug/test use
+ */
+ /* package */ int getEvaluationCount() {
+ return _bookEvaluator.getEvaluationCount();
+ }
+
+
+ /**
+ * Should be called whenever there are major changes (e.g. moving sheets) to input cells
+ * in the evaluated workbook.
+ * Failure to call this method after changing cell values will cause incorrect behaviour
+ * of the evaluate~ methods of this class
+ */
+ public void clearAllCachedResultValues() {
+ _bookEvaluator.clearAllCachedResultValues();
+ }
+ /**
+ * Should be called whenever there are changes to individual input cells in the evaluated workbook.
+ * Failure to call this method after changing cell values will cause incorrect behaviour
+ * of the evaluate~ methods of this class
+ */
+ public void clearCachedResultValue(Sheet sheet, int rowIndex, int columnIndex) {
+ _bookEvaluator.clearCachedResultValue(sheet, rowIndex, columnIndex);
+ }
+
+ /**
+ * If cell contains a formula, the formula is evaluated and returned,
+ * else the CellValue simply copies the appropriate cell value from
+ * the cell and also its cell type. This method should be preferred over
+ * evaluateInCell() when the call should not modify the contents of the
+ * original cell.
+ * @param cell
+ */
+ public CellValue evaluate(Cell cell) {
+ if (cell == null) {
+ return null;
+ }
+
+ switch (cell.getCellType()) {
+ case XSSFCell.CELL_TYPE_BOOLEAN:
+ return CellValue.valueOf(cell.getBooleanCellValue());
+ case XSSFCell.CELL_TYPE_ERROR:
+ return CellValue.getError(cell.getErrorCellValue());
+ case XSSFCell.CELL_TYPE_FORMULA:
+ return evaluateFormulaCellValue(cell);
+ case XSSFCell.CELL_TYPE_NUMERIC:
+ return new CellValue(cell.getNumericCellValue());
+ case XSSFCell.CELL_TYPE_STRING:
+ return new CellValue(cell.getRichStringCellValue().getString());
+ }
+ throw new IllegalStateException("Bad cell type (" + cell.getCellType() + ")");
+ }
+
+
+ /**
+ * If cell contains formula, it evaluates the formula,
+ * and saves the result of the formula. The cell
+ * remains as a formula cell.
+ * Else if cell does not contain formula, this method leaves
+ * the cell unchanged.
+ * Note that the type of the formula result is returned,
+ * so you know what kind of value is also stored with
+ * the formula.
+ * <pre>
+ * int evaluatedCellType = evaluator.evaluateFormulaCell(cell);
+ * </pre>
+ * Be aware that your cell will hold both the formula,
+ * and the result. If you want the cell replaced with
+ * the result of the formula, use {@link #evaluateInCell(HSSFCell)}
+ * @param cell The cell to evaluate
+ * @return The type of the formula result (the cell's type remains as HSSFCell.CELL_TYPE_FORMULA however)
+ */
+ public int evaluateFormulaCell(Cell cell) {
+ if (cell == null || cell.getCellType() != XSSFCell.CELL_TYPE_FORMULA) {
+ return -1;
+ }
+ CellValue cv = evaluateFormulaCellValue(cell);
+ // cell remains a formula cell, but the cached value is changed
+ setCellValue(cell, cv);
+ return cv.getCellType();
+ }
+
+ /**
+ * If cell contains formula, it evaluates the formula, and
+ * puts the formula result back into the cell, in place
+ * of the old formula.
+ * Else if cell does not contain formula, this method leaves
+ * the cell unchanged.
+ * Note that the same instance of HSSFCell is returned to
+ * allow chained calls like:
+ * <pre>
+ * int evaluatedCellType = evaluator.evaluateInCell(cell).getCellType();
+ * </pre>
+ * Be aware that your cell value will be changed to hold the
+ * result of the formula. If you simply want the formula
+ * value computed for you, use {@link #evaluateFormulaCell(HSSFCell)}
+ * @param cell
+ */
+ public XSSFCell evaluateInCell(Cell cell) {
+ if (cell == null) {
+ return null;
+ }
+ XSSFCell result = (XSSFCell) cell;
+ if (cell.getCellType() == XSSFCell.CELL_TYPE_FORMULA) {
+ CellValue cv = evaluateFormulaCellValue(cell);
+ setCellType(cell, cv); // cell will no longer be a formula cell
+ setCellValue(cell, cv);
+ }
+ return result;
+ }
+ private static void setCellType(Cell cell, CellValue cv) {
+ int cellType = cv.getCellType();
+ switch (cellType) {
+ case XSSFCell.CELL_TYPE_BOOLEAN:
+ case XSSFCell.CELL_TYPE_ERROR:
+ case XSSFCell.CELL_TYPE_NUMERIC:
+ case XSSFCell.CELL_TYPE_STRING:
+ cell.setCellType(cellType);
+ return;
+ case XSSFCell.CELL_TYPE_BLANK:
+ // never happens - blanks eventually get translated to zero
+ case XSSFCell.CELL_TYPE_FORMULA:
+ // this will never happen, we have already evaluated the formula
+ }
+ throw new IllegalStateException("Unexpected cell value type (" + cellType + ")");
+ }
+
+ private static void setCellValue(Cell cell, CellValue cv) {
+ int cellType = cv.getCellType();
+ switch (cellType) {
+ case XSSFCell.CELL_TYPE_BOOLEAN:
+ cell.setCellValue(cv.getBooleanValue());
+ break;
+ case XSSFCell.CELL_TYPE_ERROR:
+ cell.setCellErrorValue(cv.getErrorValue());
+ break;
+ case XSSFCell.CELL_TYPE_NUMERIC:
+ cell.setCellValue(cv.getNumberValue());
+ break;
+ case XSSFCell.CELL_TYPE_STRING:
+ cell.setCellValue(new XSSFRichTextString(cv.getStringValue()));
+ break;
+ case XSSFCell.CELL_TYPE_BLANK:
+ // never happens - blanks eventually get translated to zero
+ case XSSFCell.CELL_TYPE_FORMULA:
+ // this will never happen, we have already evaluated the formula
+ default:
+ throw new IllegalStateException("Unexpected cell value type (" + cellType + ")");
+ }
+ }
+
+ /**
+ * Loops over all cells in all sheets of the supplied
+ * workbook.
+ * For cells that contain formulas, their formulas are
+ * evaluated, and the results are saved. These cells
+ * remain as formula cells.
+ * For cells that do not contain formulas, no changes
+ * are made.
+ * This is a helpful wrapper around looping over all
+ * cells, and calling evaluateFormulaCell on each one.
+ */
+ public static void evaluateAllFormulaCells(XSSFWorkbook wb) {
+ XSSFFormulaEvaluator evaluator = new XSSFFormulaEvaluator(wb);
+ for(int i=0; i<wb.getNumberOfSheets(); i++) {
+ Sheet sheet = wb.getSheetAt(i);
+
+ for (Iterator<Row> rit = sheet.rowIterator(); rit.hasNext();) {
+ Row r = rit.next();
+
+ for (Iterator cit = r.cellIterator(); cit.hasNext();) {
+ XSSFCell c = (XSSFCell) cit.next();
+ if (c.getCellType() == XSSFCell.CELL_TYPE_FORMULA)
+ evaluator.evaluateFormulaCell(c);
+ }
+ }
+ }
+ }
+
+ /**
+ * Returns a CellValue wrapper around the supplied ValueEval instance.
+ * @param eval
+ */
+ private CellValue evaluateFormulaCellValue(Cell cell) {
+ ValueEval eval = _bookEvaluator.evaluate(cell);
+ if (eval instanceof NumberEval) {
+ NumberEval ne = (NumberEval) eval;
+ return new CellValue(ne.getNumberValue());
+ }
+ if (eval instanceof BoolEval) {
+ BoolEval be = (BoolEval) eval;
+ return CellValue.valueOf(be.getBooleanValue());
+ }
+ if (eval instanceof StringEval) {
+ StringEval ne = (StringEval) eval;
+ return new CellValue(ne.getStringValue());
+ }
+ if (eval instanceof ErrorEval) {
+ return CellValue.getError(((ErrorEval)eval).getErrorCode());
+ }
+ throw new RuntimeException("Unexpected eval class (" + eval.getClass().getName() + ")");
+ }
+}
diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFName.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFName.java index 5d6b7aec0c..0e9e573b45 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFName.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFName.java @@ -37,7 +37,7 @@ public class XSSFName implements Name { public boolean isFunctionName() { // TODO Figure out how HSSF does this, and do the same! - return false; + return ctName.getFunction(); // maybe this works - verify } /** * Returns the underlying named range object diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java index 5bc8c14e5d..a7b30ff191 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java @@ -408,15 +408,6 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook { return -1; } - /** - * TODO - figure out what the hell this methods does in - * HSSF... - */ - public String resolveNameXText(int refIndex, int definedNameIndex) { - // TODO Replace with something proper - return null; - } - public short getNumCellStyles() { return (short) ((StylesTable)stylesSource).getNumCellStyles(); } @@ -450,23 +441,6 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook { return -1; } - /** - * Doesn't do anything - returns the same index - * TODO - figure out if this is a ole2 specific thing, or - * if we need to do something proper here too! - */ - public int getSheetIndexFromExternSheetIndex(int externSheetNumber) { - return externSheetNumber; - } - /** - * Doesn't do anything special - returns the same as getSheetName() - * TODO - figure out if this is a ole2 specific thing, or - * if we need to do something proper here too! - */ - public String findSheetNameFromExternSheet(int externSheetIndex) { - return getSheetName(externSheetIndex); - } - public Sheet getSheet(String name) { CTSheet[] sheets = this.workbook.getSheets().getSheetArray(); for (int i = 0 ; i < sheets.length ; ++i) { @@ -495,19 +469,6 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook { return this.sheets.indexOf(sheet); } - /** - * Returns the external sheet index of the sheet - * with the given internal index, creating one - * if needed. - * Used by some of the more obscure formula and - * named range things. - * Fairly easy on XSSF (we think...) since the - * internal and external indicies are the same - */ - public int getExternalSheetIndex(int internalSheetIndex) { - return internalSheetIndex; - } - public String getSheetName(int sheet) { return this.workbook.getSheets().getSheetArray(sheet).getName(); } diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestFormulaEvaluatorOnXSSF.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestFormulaEvaluatorOnXSSF.java index 90c75c5a07..9464c05b8f 100644 --- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestFormulaEvaluatorOnXSSF.java +++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestFormulaEvaluatorOnXSSF.java @@ -28,10 +28,10 @@ import org.apache.poi.hssf.HSSFTestDataSamples; import org.apache.poi.hssf.record.formula.eval.TestFormulasFromSpreadsheet; import org.apache.poi.hssf.record.formula.functions.TestMathX; import org.apache.poi.ss.usermodel.Cell; +import org.apache.poi.ss.usermodel.CellValue; import org.apache.poi.ss.usermodel.FormulaEvaluator; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; -import org.apache.poi.ss.usermodel.Workbook; import org.openxml4j.opc.Package; /** @@ -90,7 +90,7 @@ public final class TestFormulaEvaluatorOnXSSF extends TestCase { public static final int NUMBER_OF_ROWS_PER_FUNCTION = 4; } - private Workbook workbook; + private XSSFWorkbook workbook; private Sheet sheet; // Note - multiple failures are aggregated before ending. // If one or more functions fail, a single AssertionFailedError is thrown at the end @@ -107,7 +107,7 @@ public final class TestFormulaEvaluatorOnXSSF extends TestCase { } - private static void confirmExpectedResult(String msg, Cell expected, FormulaEvaluator.CellValue actual) { + private static void confirmExpectedResult(String msg, Cell expected, CellValue actual) { if (expected == null) { throw new AssertionFailedError(msg + " - Bad setup data expected value is null"); } @@ -199,7 +199,7 @@ public final class TestFormulaEvaluatorOnXSSF extends TestCase { */ private void processFunctionGroup(int startRowIndex, String testFocusFunctionName) { - FormulaEvaluator evaluator = new FormulaEvaluator(workbook); + FormulaEvaluator evaluator = new XSSFFormulaEvaluator(workbook); int rowIndex = startRowIndex; while (true) { @@ -256,7 +256,7 @@ public final class TestFormulaEvaluatorOnXSSF extends TestCase { continue; } - FormulaEvaluator.CellValue actualValue; + CellValue actualValue; try { actualValue = evaluator.evaluate(c); } catch (RuntimeException e) { diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java index 70a54b5ef9..160f5609bc 100644 --- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java +++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFFormulaEvaluation.java @@ -17,15 +17,14 @@ package org.apache.poi.xssf.usermodel; +import junit.framework.TestCase; + import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.FormulaEvaluator; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; -import org.apache.poi.ss.usermodel.Workbook; - -import junit.framework.TestCase; -public class TestXSSFFormulaEvaluation extends TestCase { +public final class TestXSSFFormulaEvaluation extends TestCase { public TestXSSFFormulaEvaluation(String name) { super(name); @@ -37,7 +36,7 @@ public class TestXSSFFormulaEvaluation extends TestCase { } public void testSimpleArithmatic() { - Workbook wb = new XSSFWorkbook(); + XSSFWorkbook wb = new XSSFWorkbook(); Sheet s = wb.createSheet(); Row r = s.createRow(0); @@ -49,7 +48,7 @@ public class TestXSSFFormulaEvaluation extends TestCase { c2.setCellFormula("10/2"); assertTrue( Double.isNaN(c2.getNumericCellValue()) ); - FormulaEvaluator fe = new FormulaEvaluator(s, wb); + FormulaEvaluator fe = new XSSFFormulaEvaluator(wb); fe.evaluateFormulaCell(c1); fe.evaluateFormulaCell(c2); @@ -59,7 +58,7 @@ public class TestXSSFFormulaEvaluation extends TestCase { } public void testSumCount() { - Workbook wb = new XSSFWorkbook(); + XSSFWorkbook wb = new XSSFWorkbook(); Sheet s = wb.createSheet(); Row r = s.createRow(0); r.createCell(0).setCellValue(2.5); @@ -87,7 +86,7 @@ public class TestXSSFFormulaEvaluation extends TestCase { // Evaluate and test - FormulaEvaluator fe = new FormulaEvaluator(s, wb); + FormulaEvaluator fe = new XSSFFormulaEvaluator(wb); fe.evaluateFormulaCell(c1); fe.evaluateFormulaCell(c2); diff --git a/src/testcases/org/apache/poi/hssf/eventusermodel/TestEventWorkbookBuilder.java b/src/testcases/org/apache/poi/hssf/eventusermodel/TestEventWorkbookBuilder.java index a17414af74..2c56ea57a1 100644 --- a/src/testcases/org/apache/poi/hssf/eventusermodel/TestEventWorkbookBuilder.java +++ b/src/testcases/org/apache/poi/hssf/eventusermodel/TestEventWorkbookBuilder.java @@ -26,7 +26,7 @@ import junit.framework.TestCase; import org.apache.poi.hssf.HSSFTestDataSamples; import org.apache.poi.hssf.eventusermodel.EventWorkbookBuilder.SheetRecordCollectingListener; -import org.apache.poi.hssf.model.FormulaParser; +import org.apache.poi.hssf.model.HSSFFormulaParser; import org.apache.poi.hssf.model.Workbook; import org.apache.poi.hssf.record.FormulaRecord; import org.apache.poi.hssf.record.Record; @@ -101,7 +101,7 @@ public final class TestEventWorkbookBuilder extends TestCase { // Check we can get the formula without breaking for(int i=0; i<fRecs.length; i++) { - FormulaParser.toFormulaString(stubHSSF, fRecs[i].getParsedExpression()); + HSSFFormulaParser.toFormulaString(stubHSSF, fRecs[i].getParsedExpression()); } // Peer into just one formula, and check that @@ -123,19 +123,19 @@ public final class TestEventWorkbookBuilder extends TestCase { fr = fRecs[0]; assertEquals(1, fr.getRow()); assertEquals(0, fr.getColumn()); - assertEquals("Sheet1!A1", FormulaParser.toFormulaString(stubHSSF, fr.getParsedExpression())); + assertEquals("Sheet1!A1", HSSFFormulaParser.toFormulaString(stubHSSF, fr.getParsedExpression())); // Sheet 1 A5 is to another sheet fr = fRecs[3]; assertEquals(4, fr.getRow()); assertEquals(0, fr.getColumn()); - assertEquals("'S2'!A1", FormulaParser.toFormulaString(stubHSSF, fr.getParsedExpression())); + assertEquals("'S2'!A1", HSSFFormulaParser.toFormulaString(stubHSSF, fr.getParsedExpression())); // Sheet 1 A7 is to another sheet, range fr = fRecs[5]; assertEquals(6, fr.getRow()); assertEquals(0, fr.getColumn()); - assertEquals("SUM(Sh3!A1:A4)", FormulaParser.toFormulaString(stubHSSF, fr.getParsedExpression())); + assertEquals("SUM(Sh3!A1:A4)", HSSFFormulaParser.toFormulaString(stubHSSF, fr.getParsedExpression())); // Now, load via Usermodel and re-check diff --git a/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java b/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java index bb9f3de605..016fa8398c 100644 --- a/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java +++ b/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java @@ -21,7 +21,6 @@ import junit.framework.AssertionFailedError; import junit.framework.TestCase; import org.apache.poi.hssf.HSSFTestDataSamples; -import org.apache.poi.hssf.model.FormulaParser.FormulaParseException; import org.apache.poi.hssf.record.constant.ErrorConstant; import org.apache.poi.hssf.record.formula.AbstractFunctionPtg; import org.apache.poi.hssf.record.formula.AddPtg; @@ -56,6 +55,7 @@ import org.apache.poi.hssf.usermodel.HSSFName; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; +import org.apache.poi.ss.formula.FormulaParserTestHelper; /** * Test the low level formula parser functionality. High level tests are to @@ -67,10 +67,13 @@ public final class TestFormulaParser extends TestCase { * @return parsed token array already confirmed not <code>null</code> */ /* package */ static Ptg[] parseFormula(String formula) { - Ptg[] result = FormulaParser.parse(formula, null); + Ptg[] result = HSSFFormulaParser.parse(formula, (HSSFWorkbook)null); assertNotNull("Ptg array should not be null", result); return result; } + private static String toFormulaString(Ptg[] ptgs) { + return HSSFFormulaParser.toFormulaString((HSSFWorkbook)null, ptgs); + } public void testSimpleFormula() { Ptg[] ptgs = parseFormula("2+2"); @@ -133,7 +136,7 @@ public final class TestFormulaParser extends TestCase { HSSFWorkbook w = HSSFTestDataSamples.openSampleWorkbook("testNames.xls"); HSSFEvaluationWorkbook book = HSSFEvaluationWorkbook.create(w); - Ptg[] ptg = FormulaParser.parse("myFunc()", w); + Ptg[] ptg = HSSFFormulaParser.parse("myFunc()", w); // myFunc() actually takes 1 parameter. Don't know if POI will ever be able to detect this problem // the name gets encoded as the first arg @@ -405,7 +408,7 @@ public final class TestFormulaParser extends TestCase { Ptg[] ptgs = { new FuncPtg(10), }; - assertEquals("NA()", FormulaParser.toFormulaString(book, ptgs)); + assertEquals("NA()", HSSFFormulaParser.toFormulaString(book, ptgs)); } public void testPercent() { @@ -639,11 +642,11 @@ public final class TestFormulaParser extends TestCase { String formulaString; Ptg[] ptgs; ptgs = parseFormula("sum(5, 2, if(3>2, sum(A1:A2), 6))"); - formulaString = FormulaParser.toFormulaString(null, ptgs); + formulaString = toFormulaString(ptgs); assertEquals("SUM(5,2,IF(3>2,SUM(A1:A2),6))", formulaString); ptgs = parseFormula("if(1<2,sum(5, 2, if(3>2, sum(A1:A2), 6)),4)"); - formulaString = FormulaParser.toFormulaString(null, ptgs); + formulaString = toFormulaString(ptgs); assertEquals("IF(1<2,SUM(5,2,IF(3>2,SUM(A1:A2),6)),4)", formulaString); } public void testParserErrors() { @@ -665,12 +668,9 @@ public final class TestFormulaParser extends TestCase { try { parseFormula(formula); throw new AssertionFailedError("expected parse exception"); - } catch (FormulaParseException e) { - // expected during successful test - assertNotNull(e.getMessage()); } catch (RuntimeException e) { - e.printStackTrace(); - fail("Wrong exception:" + e.getMessage()); + // expected during successful test + FormulaParserTestHelper.confirmParseException(e); } } @@ -697,7 +697,7 @@ public final class TestFormulaParser extends TestCase { Ptg[] ptgs = { spacePtg, new IntPtg(4), }; String formulaString; try { - formulaString = FormulaParser.toFormulaString(null, ptgs); + formulaString = toFormulaString(ptgs); } catch (IllegalStateException e) { if(e.getMessage().equalsIgnoreCase("too much stuff left on the stack")) { throw new AssertionFailedError("Identified bug 44609"); @@ -709,7 +709,7 @@ public final class TestFormulaParser extends TestCase { assertEquals("4", formulaString); ptgs = new Ptg[] { new IntPtg(3), spacePtg, new IntPtg(4), spacePtg, AddPtg.instance, }; - formulaString = FormulaParser.toFormulaString(null, ptgs); + formulaString = toFormulaString(ptgs); assertEquals("3+4", formulaString); } @@ -725,7 +725,7 @@ public final class TestFormulaParser extends TestCase { DividePtg.instance, }; try { - FormulaParser.toFormulaString(null, ptgs); + toFormulaString(ptgs); fail("Expected exception was not thrown"); } catch (IllegalStateException e) { // expected during successful test @@ -764,10 +764,10 @@ public final class TestFormulaParser extends TestCase { private static void confirmArgCountMsg(String formula, String expectedMessage) { HSSFWorkbook book = new HSSFWorkbook(); try { - FormulaParser.parse(formula, book); + HSSFFormulaParser.parse(formula, book); throw new AssertionFailedError("Didn't get parse exception as expected"); - } catch (FormulaParseException e) { - assertEquals(expectedMessage, e.getMessage()); + } catch (RuntimeException e) { + FormulaParserTestHelper.confirmParseException(e, expectedMessage); } } @@ -776,15 +776,17 @@ public final class TestFormulaParser extends TestCase { try { parseFormula("round(3.14;2)"); throw new AssertionFailedError("Didn't get parse exception as expected"); - } catch (FormulaParseException e) { - assertEquals("Parse error near char 10 ';' in specified formula 'round(3.14;2)'. Expected ',' or ')'", e.getMessage()); + } catch (RuntimeException e) { + FormulaParserTestHelper.confirmParseException(e, + "Parse error near char 10 ';' in specified formula 'round(3.14;2)'. Expected ',' or ')'"); } try { parseFormula(" =2+2"); throw new AssertionFailedError("Didn't get parse exception as expected"); - } catch (FormulaParseException e) { - assertEquals("The specified formula ' =2+2' starts with an equals sign which is not allowed.", e.getMessage()); + } catch (RuntimeException e) { + FormulaParserTestHelper.confirmParseException(e, + "The specified formula ' =2+2' starts with an equals sign which is not allowed."); } } @@ -819,7 +821,7 @@ public final class TestFormulaParser extends TestCase { Ptg[] ptgs; try { - ptgs = FormulaParser.parse("count(pfy1)", wb); + ptgs = HSSFFormulaParser.parse("count(pfy1)", wb); } catch (IllegalArgumentException e) { if (e.getMessage().equals("Specified colIx (1012) is out of range")) { throw new AssertionFailedError("Identified bug 45354"); @@ -835,10 +837,9 @@ public final class TestFormulaParser extends TestCase { try { cell.setCellFormula("count(pf1)"); throw new AssertionFailedError("Expected formula parse execption"); - } catch (FormulaParseException e) { - if (!e.getMessage().equals("Specified named range 'pf1' does not exist in the current workbook.")) { - throw e; - } + } catch (RuntimeException e) { + FormulaParserTestHelper.confirmParseException(e, + "Specified named range 'pf1' does not exist in the current workbook."); } cell.setCellFormula("count(fp1)"); // plain cell ref, col is in range } @@ -850,14 +851,14 @@ public final class TestFormulaParser extends TestCase { HSSFWorkbook book = new HSSFWorkbook(); book.createSheet("Sheet1"); - ptgs = FormulaParser.parse("Sheet1!A10:A40000", book); + ptgs = HSSFFormulaParser.parse("Sheet1!A10:A40000", book); aptg = (AreaI) ptgs[0]; if (aptg.getLastRow() == -25537) { throw new AssertionFailedError("Identified bug 45358"); } assertEquals(39999, aptg.getLastRow()); - ptgs = FormulaParser.parse("Sheet1!A10:A65536", book); + ptgs = HSSFFormulaParser.parse("Sheet1!A10:A65536", book); aptg = (AreaI) ptgs[0]; assertEquals(65535, aptg.getLastRow()); diff --git a/src/testcases/org/apache/poi/hssf/model/TestFormulaParserEval.java b/src/testcases/org/apache/poi/hssf/model/TestFormulaParserEval.java index bd40e3d05a..f0839cfd2e 100644 --- a/src/testcases/org/apache/poi/hssf/model/TestFormulaParserEval.java +++ b/src/testcases/org/apache/poi/hssf/model/TestFormulaParserEval.java @@ -17,9 +17,9 @@ package org.apache.poi.hssf.model; +import junit.framework.AssertionFailedError; import junit.framework.TestCase; -import org.apache.poi.hssf.model.FormulaParser.FormulaParseException; import org.apache.poi.hssf.record.formula.FuncVarPtg; import org.apache.poi.hssf.record.formula.NamePtg; import org.apache.poi.hssf.record.formula.Ptg; @@ -29,7 +29,8 @@ import org.apache.poi.hssf.usermodel.HSSFName; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; -import org.apache.poi.ss.usermodel.FormulaEvaluator.CellValue; +import org.apache.poi.ss.formula.FormulaParserTestHelper; +import org.apache.poi.ss.usermodel.CellValue; /** * Test the low level formula parser functionality, @@ -51,21 +52,21 @@ public final class TestFormulaParserEval extends TestCase { name.setNameName("testName"); name.setReference("A1:A2"); - ptgs = FormulaParser.parse("SUM(testName)", workbook); + ptgs = HSSFFormulaParser.parse("SUM(testName)", workbook); assertTrue("two tokens expected, got "+ptgs.length,ptgs.length == 2); assertEquals(NamePtg.class, ptgs[0].getClass()); assertEquals(FuncVarPtg.class, ptgs[1].getClass()); // Now make it a single cell name.setReference("C3"); - ptgs = FormulaParser.parse("SUM(testName)", workbook); + ptgs = HSSFFormulaParser.parse("SUM(testName)", workbook); assertTrue("two tokens expected, got "+ptgs.length,ptgs.length == 2); assertEquals(NamePtg.class, ptgs[0].getClass()); assertEquals(FuncVarPtg.class, ptgs[1].getClass()); // And make it non-contiguous name.setReference("A1:A2,C3"); - ptgs = FormulaParser.parse("SUM(testName)", workbook); + ptgs = HSSFFormulaParser.parse("SUM(testName)", workbook); assertTrue("two tokens expected, got "+ptgs.length,ptgs.length == 2); assertEquals(NamePtg.class, ptgs[0].getClass()); assertEquals(FuncVarPtg.class, ptgs[1].getClass()); @@ -89,11 +90,12 @@ public final class TestFormulaParserEval extends TestCase { CellValue result; try { result = fe.evaluate(cell); - } catch (FormulaParseException e) { - if(e.getMessage().equals("Found reference to named range \"A\", but that named range wasn't defined!")) { - fail("Identifed bug 44539"); + } catch (RuntimeException e) { + FormulaParserTestHelper.confirmParseException(e); + if (!e.getMessage().equals("Found reference to named range \"A\", but that named range wasn't defined!")) { + throw new AssertionFailedError("Identifed bug 44539"); } - throw new RuntimeException(e); + throw e; } assertEquals(HSSFCell.CELL_TYPE_NUMERIC, result.getCellType()); assertEquals(42.0, result.getNumberValue(), 0.0); diff --git a/src/testcases/org/apache/poi/hssf/model/TestOperandClassTransformer.java b/src/testcases/org/apache/poi/hssf/model/TestOperandClassTransformer.java index 47cfb574c1..530c2b09c2 100644 --- a/src/testcases/org/apache/poi/hssf/model/TestOperandClassTransformer.java +++ b/src/testcases/org/apache/poi/hssf/model/TestOperandClassTransformer.java @@ -23,6 +23,7 @@ import junit.framework.TestCase; import org.apache.poi.hssf.record.formula.AbstractFunctionPtg; import org.apache.poi.hssf.record.formula.FuncVarPtg; import org.apache.poi.hssf.record.formula.Ptg; +import org.apache.poi.hssf.usermodel.HSSFWorkbook; /** * Tests specific formula examples in <tt>OperandClassTransformer</tt>. @@ -31,9 +32,15 @@ import org.apache.poi.hssf.record.formula.Ptg; */ public final class TestOperandClassTransformer extends TestCase { + private static Ptg[] parseFormula(String formula) { + Ptg[] result = HSSFFormulaParser.parse(formula, (HSSFWorkbook)null); + assertNotNull("Ptg array should not be null", result); + return result; + } + public void testMdeterm() { String formula = "MDETERM(ABS(A1))"; - Ptg[] ptgs = FormulaParser.parse(formula, null); + Ptg[] ptgs = parseFormula(formula); confirmTokenClass(ptgs, 0, Ptg.CLASS_ARRAY); confirmFuncClass(ptgs, 1, "ABS", Ptg.CLASS_ARRAY); @@ -51,7 +58,7 @@ public final class TestOperandClassTransformer extends TestCase { */ public void DISABLED_testIndexPi1() { String formula = "INDEX(PI(),1)"; - Ptg[] ptgs = FormulaParser.parse(formula, null); + Ptg[] ptgs = parseFormula(formula); confirmFuncClass(ptgs, 1, "PI", Ptg.CLASS_ARRAY); // fails as of POI 3.1 confirmFuncClass(ptgs, 2, "INDEX", Ptg.CLASS_VALUE); @@ -63,7 +70,7 @@ public final class TestOperandClassTransformer extends TestCase { */ public void testDirectOperandOfValueOperator() { String formula = "COUNT(A1*1)"; - Ptg[] ptgs = FormulaParser.parse(formula, null); + Ptg[] ptgs = parseFormula(formula); if (ptgs[0].getPtgClass() == Ptg.CLASS_REF) { throw new AssertionFailedError("Identified bug 45348"); } @@ -78,13 +85,13 @@ public final class TestOperandClassTransformer extends TestCase { public void testRtoV() { String formula = "lookup(A1, A3:A52, B3:B52)"; - Ptg[] ptgs = FormulaParser.parse(formula, null); + Ptg[] ptgs = parseFormula(formula); confirmTokenClass(ptgs, 0, Ptg.CLASS_VALUE); } public void testComplexIRR_bug45041() { String formula = "(1+IRR(SUMIF(A:A,ROW(INDIRECT(MIN(A:A)&\":\"&MAX(A:A))),B:B),0))^365-1"; - Ptg[] ptgs = FormulaParser.parse(formula, null); + Ptg[] ptgs = parseFormula(formula); FuncVarPtg rowFunc = (FuncVarPtg) ptgs[10]; FuncVarPtg sumifFunc = (FuncVarPtg) ptgs[12]; diff --git a/src/testcases/org/apache/poi/hssf/model/TestRVA.java b/src/testcases/org/apache/poi/hssf/model/TestRVA.java index 1ec7c92ae9..eb69a5cd0b 100644 --- a/src/testcases/org/apache/poi/hssf/model/TestRVA.java +++ b/src/testcases/org/apache/poi/hssf/model/TestRVA.java @@ -82,7 +82,7 @@ public final class TestRVA extends TestCase { private void confirmCell(HSSFCell formulaCell, String formula, HSSFWorkbook wb) { Ptg[] excelPtgs = FormulaExtractor.getPtgs(formulaCell); - Ptg[] poiPtgs = FormulaParser.parse(formula, wb); + Ptg[] poiPtgs = HSSFFormulaParser.parse(formula, wb); int nExcelTokens = excelPtgs.length; int nPoiTokens = poiPtgs.length; if (nExcelTokens != nPoiTokens) { diff --git a/src/testcases/org/apache/poi/hssf/record/formula/TestAreaPtg.java b/src/testcases/org/apache/poi/hssf/record/formula/TestAreaPtg.java index f5b80f6dac..84f6897748 100644 --- a/src/testcases/org/apache/poi/hssf/record/formula/TestAreaPtg.java +++ b/src/testcases/org/apache/poi/hssf/record/formula/TestAreaPtg.java @@ -20,7 +20,7 @@ package org.apache.poi.hssf.record.formula; import junit.framework.TestCase; -import org.apache.poi.hssf.model.FormulaParser; +import org.apache.poi.hssf.model.HSSFFormulaParser; import org.apache.poi.hssf.usermodel.HSSFWorkbook; /** @@ -87,7 +87,7 @@ public final class TestAreaPtg extends TestCase { private static String shiftAllColumnsBy1(String formula) { int letUsShiftColumn1By1Column=1; HSSFWorkbook wb = null; - Ptg[] ptgs = FormulaParser.parse(formula, wb); + Ptg[] ptgs = HSSFFormulaParser.parse(formula, wb); for(int i=0; i<ptgs.length; i++) { Ptg ptg = ptgs[i]; @@ -98,7 +98,7 @@ public final class TestAreaPtg extends TestCase { aptg.setLastColumn((short)(aptg.getLastColumn()+letUsShiftColumn1By1Column)); } } - String newFormula = FormulaParser.toFormulaString(wb, ptgs); + String newFormula = HSSFFormulaParser.toFormulaString(wb, ptgs); return newFormula; } } diff --git a/src/testcases/org/apache/poi/hssf/record/formula/TestExternalFunctionFormulas.java b/src/testcases/org/apache/poi/hssf/record/formula/TestExternalFunctionFormulas.java index 7e71889d8b..b25e4d902f 100755 --- a/src/testcases/org/apache/poi/hssf/record/formula/TestExternalFunctionFormulas.java +++ b/src/testcases/org/apache/poi/hssf/record/formula/TestExternalFunctionFormulas.java @@ -24,12 +24,12 @@ import java.io.IOException; import junit.framework.TestCase; import org.apache.poi.hssf.HSSFTestDataSamples; -import org.apache.poi.hssf.model.FormulaParser; +import org.apache.poi.hssf.model.HSSFFormulaParser; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; -import org.apache.poi.ss.usermodel.FormulaEvaluator.CellValue; +import org.apache.poi.ss.usermodel.CellValue; /** * Tests for functions from external workbooks (e.g. YEARFRAC). * @@ -52,7 +52,7 @@ public final class TestExternalFunctionFormulas extends TestCase { public void testParse() { HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("externalFunctionExample.xls"); - Ptg[] ptgs = FormulaParser.parse("YEARFRAC(B1,C1)", wb); + Ptg[] ptgs = HSSFFormulaParser.parse("YEARFRAC(B1,C1)", wb); assertEquals(4, ptgs.length); assertEquals(NameXPtg.class, ptgs[0].getClass()); diff --git a/src/testcases/org/apache/poi/hssf/record/formula/TestFuncVarPtg.java b/src/testcases/org/apache/poi/hssf/record/formula/TestFuncVarPtg.java index 58ae55f26b..ec9c3d1e85 100644 --- a/src/testcases/org/apache/poi/hssf/record/formula/TestFuncVarPtg.java +++ b/src/testcases/org/apache/poi/hssf/record/formula/TestFuncVarPtg.java @@ -17,7 +17,7 @@ package org.apache.poi.hssf.record.formula; -import org.apache.poi.hssf.model.FormulaParser; +import org.apache.poi.hssf.model.HSSFFormulaParser; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import junit.framework.AssertionFailedError; @@ -36,7 +36,7 @@ public final class TestFuncVarPtg extends TestCase { */ public void testOperandClass() { HSSFWorkbook book = new HSSFWorkbook(); - Ptg[] ptgs = FormulaParser.parse("sum(A1:A2)", book); + Ptg[] ptgs = HSSFFormulaParser.parse("sum(A1:A2)", book); assertEquals(2, ptgs.length); assertEquals(AreaPtg.class, ptgs[0].getClass()); diff --git a/src/testcases/org/apache/poi/hssf/record/formula/eval/TestCircularReferences.java b/src/testcases/org/apache/poi/hssf/record/formula/eval/TestCircularReferences.java index 74691b27dd..5b299834ae 100755 --- a/src/testcases/org/apache/poi/hssf/record/formula/eval/TestCircularReferences.java +++ b/src/testcases/org/apache/poi/hssf/record/formula/eval/TestCircularReferences.java @@ -25,7 +25,7 @@ import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; -import org.apache.poi.ss.usermodel.FormulaEvaluator.CellValue; +import org.apache.poi.ss.usermodel.CellValue; /** * Tests HSSFFormulaEvaluator for its handling of cell formula circular references. * diff --git a/src/testcases/org/apache/poi/hssf/record/formula/eval/TestExternalFunction.java b/src/testcases/org/apache/poi/hssf/record/formula/eval/TestExternalFunction.java index 95408a4aaf..45e49076c2 100755 --- a/src/testcases/org/apache/poi/hssf/record/formula/eval/TestExternalFunction.java +++ b/src/testcases/org/apache/poi/hssf/record/formula/eval/TestExternalFunction.java @@ -28,7 +28,7 @@ import org.apache.poi.hssf.usermodel.HSSFName; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; -import org.apache.poi.ss.usermodel.FormulaEvaluator.CellValue; +import org.apache.poi.ss.usermodel.CellValue; /** * * @author Josh Micich diff --git a/src/testcases/org/apache/poi/hssf/record/formula/eval/TestFormulaBugs.java b/src/testcases/org/apache/poi/hssf/record/formula/eval/TestFormulaBugs.java index 257bb8f711..45b0f3a659 100755 --- a/src/testcases/org/apache/poi/hssf/record/formula/eval/TestFormulaBugs.java +++ b/src/testcases/org/apache/poi/hssf/record/formula/eval/TestFormulaBugs.java @@ -30,7 +30,7 @@ import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; -import org.apache.poi.ss.usermodel.FormulaEvaluator.CellValue; +import org.apache.poi.ss.usermodel.CellValue; /** * Miscellaneous tests for bugzilla entries.<p/> The test name contains the diff --git a/src/testcases/org/apache/poi/hssf/record/formula/eval/TestFormulasFromSpreadsheet.java b/src/testcases/org/apache/poi/hssf/record/formula/eval/TestFormulasFromSpreadsheet.java index 74b436cdb7..aee26093e2 100644 --- a/src/testcases/org/apache/poi/hssf/record/formula/eval/TestFormulasFromSpreadsheet.java +++ b/src/testcases/org/apache/poi/hssf/record/formula/eval/TestFormulasFromSpreadsheet.java @@ -25,12 +25,12 @@ import junit.framework.TestCase; import org.apache.poi.hssf.HSSFTestDataSamples; import org.apache.poi.hssf.record.formula.functions.TestMathX; +import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; -import org.apache.poi.ss.usermodel.FormulaEvaluator; +import org.apache.poi.ss.usermodel.CellValue; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; -import org.apache.poi.ss.usermodel.Workbook;; /** * Tests formulas and operators as loaded from a test data spreadsheet.<p/> @@ -88,7 +88,7 @@ public final class TestFormulasFromSpreadsheet extends TestCase { public static final int NUMBER_OF_ROWS_PER_FUNCTION = 4; } - private Workbook workbook; + private HSSFWorkbook workbook; private Sheet sheet; // Note - multiple failures are aggregated before ending. // If one or more functions fail, a single AssertionFailedError is thrown at the end @@ -105,7 +105,7 @@ public final class TestFormulasFromSpreadsheet extends TestCase { } - private static void confirmExpectedResult(String msg, Cell expected, FormulaEvaluator.CellValue actual) { + private static void confirmExpectedResult(String msg, Cell expected, CellValue actual) { if (expected == null) { throw new AssertionFailedError(msg + " - Bad setup data expected value is null"); } @@ -178,7 +178,7 @@ public final class TestFormulasFromSpreadsheet extends TestCase { * Typically pass <code>null</code> to test all functions */ private void processFunctionGroup(int startRowIndex, String testFocusFunctionName) { - FormulaEvaluator evaluator = new FormulaEvaluator(workbook); + HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(workbook); int rowIndex = startRowIndex; while (true) { @@ -219,7 +219,7 @@ public final class TestFormulasFromSpreadsheet extends TestCase { * @return a constant from the local Result class denoting whether there were any evaluation * cases, and whether they all succeeded. */ - private int processFunctionRow(FormulaEvaluator evaluator, String targetFunctionName, + private int processFunctionRow(HSSFFormulaEvaluator evaluator, String targetFunctionName, Row formulasRow, Row expectedValuesRow) { int result = Result.NO_EVALUATIONS_FOUND; // so far @@ -232,7 +232,7 @@ public final class TestFormulasFromSpreadsheet extends TestCase { continue; } - FormulaEvaluator.CellValue actualValue = evaluator.evaluate(c); + CellValue actualValue = evaluator.evaluate(c); Cell expectedValueCell = getExpectedValueCell(expectedValuesRow, colnum); try { diff --git a/src/testcases/org/apache/poi/hssf/record/formula/eval/TestPercentEval.java b/src/testcases/org/apache/poi/hssf/record/formula/eval/TestPercentEval.java index bac2e68862..750f71ba01 100755 --- a/src/testcases/org/apache/poi/hssf/record/formula/eval/TestPercentEval.java +++ b/src/testcases/org/apache/poi/hssf/record/formula/eval/TestPercentEval.java @@ -27,7 +27,7 @@ import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; -import org.apache.poi.ss.usermodel.FormulaEvaluator.CellValue; +import org.apache.poi.ss.usermodel.CellValue; /** * Test for percent operator evaluator. diff --git a/src/testcases/org/apache/poi/hssf/record/formula/function/TestParseMissingBuiltInFuncs.java b/src/testcases/org/apache/poi/hssf/record/formula/function/TestParseMissingBuiltInFuncs.java index 7030c5a508..95ad2ef535 100644 --- a/src/testcases/org/apache/poi/hssf/record/formula/function/TestParseMissingBuiltInFuncs.java +++ b/src/testcases/org/apache/poi/hssf/record/formula/function/TestParseMissingBuiltInFuncs.java @@ -20,7 +20,7 @@ package org.apache.poi.hssf.record.formula.function; import junit.framework.AssertionFailedError; import junit.framework.TestCase; -import org.apache.poi.hssf.model.FormulaParser; +import org.apache.poi.hssf.model.HSSFFormulaParser; import org.apache.poi.hssf.record.formula.AbstractFunctionPtg; import org.apache.poi.hssf.record.formula.FuncPtg; import org.apache.poi.hssf.record.formula.FuncVarPtg; @@ -36,7 +36,7 @@ public final class TestParseMissingBuiltInFuncs extends TestCase { private static Ptg[] parse(String formula) { HSSFWorkbook book = new HSSFWorkbook(); - return FormulaParser.parse(formula, book); + return HSSFFormulaParser.parse(formula, book); } private static void confirmFunc(String formula, int expPtgArraySize, boolean isVarArgFunc, int funcIx) { Ptg[] ptgs = parse(formula); @@ -58,7 +58,7 @@ public final class TestParseMissingBuiltInFuncs extends TestCase { // check that parsed Ptg array converts back to formula text OK HSSFWorkbook book = new HSSFWorkbook(); - String reRenderedFormula = FormulaParser.toFormulaString(book, ptgs); + String reRenderedFormula = HSSFFormulaParser.toFormulaString(book, ptgs); assertEquals(formula, reRenderedFormula); } diff --git a/src/testcases/org/apache/poi/hssf/record/formula/functions/TestCountFuncs.java b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestCountFuncs.java index 8e5a8f32a4..98be3db43e 100755 --- a/src/testcases/org/apache/poi/hssf/record/formula/functions/TestCountFuncs.java +++ b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestCountFuncs.java @@ -34,7 +34,7 @@ import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; -import org.apache.poi.ss.usermodel.FormulaEvaluator.CellValue; +import org.apache.poi.ss.usermodel.CellValue; /** * Test cases for COUNT(), COUNTA() COUNTIF(), COUNTBLANK() diff --git a/src/testcases/org/apache/poi/hssf/record/formula/functions/TestDate.java b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestDate.java index 0c08ef5a54..b93eb7a6c7 100644 --- a/src/testcases/org/apache/poi/hssf/record/formula/functions/TestDate.java +++ b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestDate.java @@ -76,7 +76,7 @@ public final class TestDate extends TestCase { private void confirm(String formulaText, double expectedResult) { cell11.setCellFormula(formulaText); - evaluator.clearCache(); + evaluator.clearAllCachedResultValues(); double actualValue = evaluator.evaluate(cell11).getNumberValue(); assertEquals(expectedResult, actualValue, 0); } diff --git a/src/testcases/org/apache/poi/hssf/record/formula/functions/TestIndexFunctionFromSpreadsheet.java b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestIndexFunctionFromSpreadsheet.java index f28a6462f8..2b0f776f73 100644 --- a/src/testcases/org/apache/poi/hssf/record/formula/functions/TestIndexFunctionFromSpreadsheet.java +++ b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestIndexFunctionFromSpreadsheet.java @@ -31,7 +31,7 @@ import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.CellReference; -import org.apache.poi.ss.usermodel.FormulaEvaluator.CellValue; +import org.apache.poi.ss.usermodel.CellValue; /** * Tests INDEX() as loaded from a test data spreadsheet.<p/> @@ -66,7 +66,7 @@ public final class TestIndexFunctionFromSpreadsheet extends TestCase { - private static void confirmExpectedResult(String msg, HSSFCell expected, HSSFFormulaEvaluator.CellValue actual) { + private static void confirmExpectedResult(String msg, HSSFCell expected, CellValue actual) { if (expected == null) { throw new AssertionFailedError(msg + " - Bad setup data expected value is null"); } diff --git a/src/testcases/org/apache/poi/hssf/record/formula/functions/TestIsBlank.java b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestIsBlank.java index 50f92dfecf..5168cd9224 100755 --- a/src/testcases/org/apache/poi/hssf/record/formula/functions/TestIsBlank.java +++ b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestIsBlank.java @@ -24,7 +24,7 @@ import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; -import org.apache.poi.ss.usermodel.FormulaEvaluator.CellValue; +import org.apache.poi.ss.usermodel.CellValue; /** * Tests for Excel function ISBLANK() * diff --git a/src/testcases/org/apache/poi/hssf/record/formula/functions/TestLookupFunctionsFromSpreadsheet.java b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestLookupFunctionsFromSpreadsheet.java index 49d67a8d4e..5bacdbfca9 100644 --- a/src/testcases/org/apache/poi/hssf/record/formula/functions/TestLookupFunctionsFromSpreadsheet.java +++ b/src/testcases/org/apache/poi/hssf/record/formula/functions/TestLookupFunctionsFromSpreadsheet.java @@ -30,8 +30,8 @@ import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; -import org.apache.poi.ss.usermodel.FormulaEvaluator.CellValue; import org.apache.poi.hssf.util.CellReference; +import org.apache.poi.ss.usermodel.CellValue; /** * Tests lookup functions (VLOOKUP, HLOOKUP, LOOKUP, MATCH) as loaded from a test data spreadsheet.<p/> diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestBug42464.java b/src/testcases/org/apache/poi/hssf/usermodel/TestBug42464.java index a43764e852..73ee3f8486 100644 --- a/src/testcases/org/apache/poi/hssf/usermodel/TestBug42464.java +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestBug42464.java @@ -25,8 +25,8 @@ import org.apache.poi.hssf.HSSFTestDataSamples; import org.apache.poi.hssf.record.FormulaRecord; import org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate; import org.apache.poi.hssf.record.formula.Ptg; -import org.apache.poi.ss.usermodel.FormulaEvaluator.CellValue; import org.apache.poi.hssf.util.CellReference; +import org.apache.poi.ss.usermodel.CellValue; /** * diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFFormulaEvaluator.java b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFFormulaEvaluator.java index d1c511feb2..484d9af94e 100644 --- a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFFormulaEvaluator.java +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFFormulaEvaluator.java @@ -17,10 +17,10 @@ package org.apache.poi.hssf.usermodel; -import org.apache.poi.hssf.HSSFTestDataSamples; -import org.apache.poi.ss.usermodel.FormulaEvaluator.CellValue; - import junit.framework.TestCase; + +import org.apache.poi.hssf.HSSFTestDataSamples; +import org.apache.poi.ss.usermodel.CellValue; /** * * @author Josh Micich diff --git a/src/testcases/org/apache/poi/ss/formula/FormulaParserTestHelper.java b/src/testcases/org/apache/poi/ss/formula/FormulaParserTestHelper.java new file mode 100644 index 0000000000..144002279d --- /dev/null +++ b/src/testcases/org/apache/poi/ss/formula/FormulaParserTestHelper.java @@ -0,0 +1,48 @@ +/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+
+package org.apache.poi.ss.formula;
+
+import junit.framework.Assert;
+import junit.framework.AssertionFailedError;
+
+import org.apache.poi.ss.formula.FormulaParser;
+import org.apache.poi.ss.formula.FormulaParser.FormulaParseException;
+/**
+ * Avoids making {@link FormulaParser#FormulaParseException} public
+ *
+ * @author Josh Micich
+ */
+public class FormulaParserTestHelper {
+ public static void confirmParseException(RuntimeException e, String expectedMessage) {
+ checkType(e);
+ Assert.assertEquals(expectedMessage, e.getMessage());
+ }
+ public static void confirmParseException(RuntimeException e) {
+ checkType(e);
+ Assert.assertNotNull(e.getMessage());
+ }
+ private static void checkType(RuntimeException e) throws AssertionFailedError {
+ if (!(e instanceof FormulaParseException)) {
+ String failMsg = "Expected FormulaParseException, but got ("
+ + e.getClass().getName() + "):";
+ System.err.println(failMsg);
+ e.printStackTrace();
+ throw new AssertionFailedError(failMsg);
+ }
+ }
+}
|