You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by jo...@apache.org on 2008/09/04 23:32:18 UTC
svn commit: r692255 - in /poi/trunk/src: java/org/apache/poi/hssf/record/
java/org/apache/poi/hssf/record/formula/eval/
java/org/apache/poi/hssf/usermodel/ testcases/org/apache/poi/hssf/data/
testcases/org/apache/poi/hssf/usermodel/
Author: josh
Date: Thu Sep 4 14:32:17 2008
New Revision: 692255
URL: http://svn.apache.org/viewvc?rev=692255&view=rev
Log:
Made HSSFFormulaEvaluator capable of handling simple named ranges
Added:
poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFFormulaEvaluator.java
Modified:
poi/trunk/src/java/org/apache/poi/hssf/record/NameRecord.java
poi/trunk/src/java/org/apache/poi/hssf/record/formula/eval/ExternalFunction.java
poi/trunk/src/java/org/apache/poi/hssf/record/formula/eval/NameEval.java
poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java
poi/trunk/src/testcases/org/apache/poi/hssf/data/testNames.xls
poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/AllUserModelTests.java
Modified: poi/trunk/src/java/org/apache/poi/hssf/record/NameRecord.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/NameRecord.java?rev=692255&r1=692254&r2=692255&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/NameRecord.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/NameRecord.java Thu Sep 4 14:32:17 2008
@@ -243,12 +243,18 @@
}
}
/**
- * @return true if name is a function
+ * @return <code>true</code> if name is a function
*/
public boolean isFunctionName() {
return (field_1_option_flag & Option.OPT_FUNCTION_NAME) != 0;
}
+ /**
+ * @return <code>true</code> if name has a formula (named range or defined value)
+ */
+ public boolean hasFormula() {
+ return field_1_option_flag == 0 && field_13_name_definition.length > 0;
+ }
/**
* @return true if name is a command
Modified: poi/trunk/src/java/org/apache/poi/hssf/record/formula/eval/ExternalFunction.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/eval/ExternalFunction.java?rev=692255&r1=692254&r2=692255&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/eval/ExternalFunction.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/eval/ExternalFunction.java Thu Sep 4 14:32:17 2008
@@ -42,7 +42,7 @@
FreeRefFunction targetFunc;
try {
if (nameArg instanceof NameEval) {
- targetFunc = findInternalUserDefinedFunction(workbook, (NameEval) nameArg);
+ targetFunc = findInternalUserDefinedFunction((NameEval) nameArg);
} else if (nameArg instanceof NameXEval) {
targetFunc = findExternalUserDefinedFunction(workbook, (NameXEval) nameArg);
} else {
@@ -65,7 +65,7 @@
if(false) {
System.out.println("received call to external user defined function (" + functionName + ")");
}
- // currently only looking for functions from the 'Analysis TookPak'
+ // currently only looking for functions from the 'Analysis TookPak' e.g. "YEARFRAC" or "ISEVEN"
// not sure how much this logic would need to change to support other or multiple add-ins.
FreeRefFunction result = AnalysisToolPak.findFunction(functionName);
if (result != null) {
@@ -74,24 +74,13 @@
throw new EvaluationException(ErrorEval.FUNCTION_NOT_IMPLEMENTED);
}
- private FreeRefFunction findInternalUserDefinedFunction(HSSFWorkbook workbook, NameEval functionNameEval) throws EvaluationException {
+ private FreeRefFunction findInternalUserDefinedFunction(NameEval functionNameEval) throws EvaluationException {
- int numberOfNames = workbook.getNumberOfNames();
-
- int nameIndex = functionNameEval.getIndex();
- if(nameIndex < 0 || nameIndex >= numberOfNames) {
- throw new RuntimeException("Bad name index (" + nameIndex
- + "). Allowed range is (0.." + (numberOfNames-1) + ")");
- }
-
- String functionName = workbook.getNameName(nameIndex);
+ String functionName = functionNameEval.getFunctionName();
if(false) {
System.out.println("received call to internal user defined function (" + functionName + ")");
}
- // TODO - detect if the NameRecord corresponds to a named range, function, or something undefined
- // throw the right errors in these cases
-
- // TODO find the implementation for the external function e.g. "YEARFRAC" or "ISEVEN"
+ // TODO find the implementation for the user defined function
throw new EvaluationException(ErrorEval.FUNCTION_NOT_IMPLEMENTED);
}
Modified: poi/trunk/src/java/org/apache/poi/hssf/record/formula/eval/NameEval.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/eval/NameEval.java?rev=692255&r1=692254&r2=692255&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/eval/NameEval.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/eval/NameEval.java Thu Sep 4 14:32:17 2008
@@ -22,26 +22,24 @@
*/
public final class NameEval implements Eval {
- private final int _index;
+ private final String _functionName;
/**
- * @param index zero based index to a defined name record
+ * Creates a NameEval representing a function name
*/
- public NameEval(int index) {
- _index = index;
+ public NameEval(String functionName) {
+ _functionName = functionName;
}
- /**
- * @return zero based index to a defined name record
- */
- public int getIndex() {
- return _index;
+
+ public String getFunctionName() {
+ return _functionName;
}
public String toString() {
StringBuffer sb = new StringBuffer(64);
sb.append(getClass().getName()).append(" [");
- sb.append(_index);
+ sb.append(_functionName);
sb.append("]");
return sb.toString();
}
Modified: poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java?rev=692255&r1=692254&r2=692255&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java Thu Sep 4 14:32:17 2008
@@ -22,6 +22,7 @@
import org.apache.poi.hssf.model.FormulaParser;
import org.apache.poi.hssf.model.Workbook;
+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;
@@ -62,8 +63,8 @@
*/
public class HSSFFormulaEvaluator {
- protected HSSFSheet _sheet;
- protected HSSFWorkbook _workbook;
+ private final HSSFSheet _sheet;
+ private final HSSFWorkbook _workbook;
public HSSFFormulaEvaluator(HSSFSheet sheet, HSSFWorkbook workbook) {
_sheet = sheet;
@@ -76,6 +77,9 @@
*/
public void setCurrentRow(HSSFRow row) {
// do nothing
+ if (false) {
+ row.getClass(); // suppress unused parameter compiler warning
+ }
}
@@ -297,8 +301,9 @@
*/
private static ValueEval internalEvaluate(HSSFCell srcCell, HSSFSheet sheet, HSSFWorkbook workbook) {
int srcRowNum = srcCell.getRowIndex();
- short srcColNum = srcCell.getCellNum();
+ int srcColNum = srcCell.getCellNum();
+ ValueEval result;
EvaluationCycleDetector tracker = EvaluationCycleDetectorManager.getTracker();
@@ -306,10 +311,11 @@
return ErrorEval.CIRCULAR_REF_ERROR;
}
try {
- return evaluateCell(workbook, sheet, srcRowNum, srcColNum, srcCell.getCellFormula());
+ result = evaluateCell(workbook, sheet, srcRowNum, (short)srcColNum, srcCell.getCellFormula());
} finally {
tracker.endEvaluate(workbook, sheet, srcRowNum, srcColNum);
}
+ return result;
}
private static ValueEval evaluateCell(HSSFWorkbook workbook, HSSFSheet sheet,
int srcRowNum, short srcColNum, String cellFormulaText) {
@@ -326,19 +332,10 @@
continue;
}
if (ptg instanceof MemErrPtg) { continue; }
- if (ptg instanceof MissingArgPtg) { continue; }
- if (ptg instanceof NamePtg) {
- // named ranges, macro functions
- NamePtg namePtg = (NamePtg) ptg;
- stack.push(new NameEval(namePtg.getIndex()));
- continue;
- }
- if (ptg instanceof NameXPtg) {
- NameXPtg nameXPtg = (NameXPtg) ptg;
- stack.push(new NameXEval(nameXPtg.getSheetRefIndex(), nameXPtg.getNameIndex()));
+ if (ptg instanceof MissingArgPtg) {
+ // TODO - might need to push BlankEval or MissingArgEval
continue;
}
- if (ptg instanceof UnknownPtg) { continue; }
Eval opResult;
if (ptg instanceof OperationPtg) {
OperationPtg optg = (OperationPtg) ptg;
@@ -355,10 +352,13 @@
Eval p = (Eval) stack.pop();
ops[j] = p;
}
- opResult = invokeOperation(operation, ops, srcRowNum, srcColNum, workbook, sheet);
+ opResult = invokeOperation(operation, ops, srcRowNum, srcColNum, workbook, sheet);
} else {
opResult = getEvalForPtg(ptg, sheet, workbook);
}
+ if (opResult == null) {
+ throw new RuntimeException("Evaluation result must not be null");
+ }
stack.push(opResult);
}
@@ -422,6 +422,29 @@
* passed here!
*/
private static Eval getEvalForPtg(Ptg ptg, HSSFSheet sheet, HSSFWorkbook workbook) {
+ 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) + ")");
+ }
+ NameRecord nameRecord = workbook.getWorkbook().getNameRecord(nameIndex);
+ if (nameRecord.isFunctionName()) {
+ return new NameEval(nameRecord.getNameText());
+ }
+ if (nameRecord.hasFormula()) {
+ return evaluateNameFormula(nameRecord.getNameDefinition(), sheet, workbook);
+ }
+
+ throw new RuntimeException("Don't now how to evalate name '" + nameRecord.getNameText() + "'");
+ }
+ if (ptg instanceof NameXPtg) {
+ NameXPtg nameXPtg = (NameXPtg) ptg;
+ return new NameXEval(nameXPtg.getSheetRefIndex(), nameXPtg.getNameIndex());
+ }
if (ptg instanceof RefPtg) {
return new LazyRefEval(((RefPtg) ptg), sheet, workbook);
}
@@ -456,8 +479,20 @@
if (ptg instanceof ErrPtg) {
return ErrorEval.valueOf(((ErrPtg) ptg).getErrorCode());
}
+ if (ptg instanceof UnknownPtg) {
+ // TODO - remove UnknownPtg
+ throw new RuntimeException("UnknownPtg not allowed");
+ }
throw new RuntimeException("Unexpected ptg class (" + ptg.getClass().getName() + ")");
}
+ private static Eval evaluateNameFormula(Ptg[] ptgs, HSSFSheet sheet,
+ HSSFWorkbook workbook) {
+ if (ptgs.length > 1) {
+ throw new RuntimeException("Complex name formulas not supported yet");
+ }
+ return getEvalForPtg(ptgs[0], sheet, workbook);
+ }
+
/**
* 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
Modified: poi/trunk/src/testcases/org/apache/poi/hssf/data/testNames.xls
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/data/testNames.xls?rev=692255&r1=692254&r2=692255&view=diff
==============================================================================
Binary files - no diff available.
Modified: poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/AllUserModelTests.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/AllUserModelTests.java?rev=692255&r1=692254&r2=692255&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/AllUserModelTests.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/AllUserModelTests.java Thu Sep 4 14:32:17 2008
@@ -46,6 +46,7 @@
result.addTestSuite(TestHSSFConditionalFormatting.class);
result.addTestSuite(TestHSSFDataFormatter.class);
result.addTestSuite(TestHSSFDateUtil.class);
+ result.addTestSuite(TestHSSFFormulaEvaluator.class);
result.addTestSuite(TestHSSFHeaderFooter.class);
result.addTestSuite(TestHSSFHyperlink.class);
result.addTestSuite(TestHSSFOptimiser.class);
Added: poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFFormulaEvaluator.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFFormulaEvaluator.java?rev=692255&view=auto
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFFormulaEvaluator.java (added)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFFormulaEvaluator.java Thu Sep 4 14:32:17 2008
@@ -0,0 +1,43 @@
+/* ====================================================================
+ 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.HSSFTestDataSamples;
+import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.CellValue;
+
+import junit.framework.TestCase;
+/**
+ *
+ * @author Josh Micich
+ */
+public final class TestHSSFFormulaEvaluator extends TestCase {
+
+ /**
+ * Test that the HSSFFormulaEvaluator can evaluate simple named ranges
+ * (single cells and rectangular areas)
+ */
+ public void testEvaluateSimple() {
+ HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("testNames.xls");
+ HSSFSheet sheet = wb.getSheetAt(0);
+ HSSFCell cell = sheet.getRow(8).getCell(0);
+ HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet, wb);
+ CellValue cv = fe.evaluate(cell);
+ assertEquals(HSSFCell.CELL_TYPE_NUMERIC, cv.getCellType());
+ assertEquals(3.72, cv.getNumberValue(), 0.0);
+ }
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org