poi-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From j...@apache.org
Subject 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/
Date Thu, 04 Sep 2008 21:32:18 GMT
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


Mime
View raw message