From 62e6778ef014c370b55a7efbc41fa827ad5d7c3a Mon Sep 17 00:00:00 2001 From: Josh Micich Date: Thu, 4 Sep 2008 21:32:17 +0000 Subject: [PATCH] Made HSSFFormulaEvaluator capable of handling simple named ranges git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@692255 13f79535-47bb-0310-9956-ffa450edef68 --- .../apache/poi/hssf/record/NameRecord.java | 8 ++- .../record/formula/eval/ExternalFunction.java | 21 ++---- .../hssf/record/formula/eval/NameEval.java | 18 +++-- .../hssf/usermodel/HSSFFormulaEvaluator.java | 67 +++++++++++++----- .../org/apache/poi/hssf/data/testNames.xls | Bin 27648 -> 28160 bytes .../poi/hssf/usermodel/AllUserModelTests.java | 1 + .../usermodel/TestHSSFFormulaEvaluator.java | 43 +++++++++++ 7 files changed, 115 insertions(+), 43 deletions(-) create mode 100644 src/testcases/org/apache/poi/hssf/usermodel/TestHSSFFormulaEvaluator.java diff --git a/src/java/org/apache/poi/hssf/record/NameRecord.java b/src/java/org/apache/poi/hssf/record/NameRecord.java index bc2c535869..b2cc897b52 100644 --- a/src/java/org/apache/poi/hssf/record/NameRecord.java +++ b/src/java/org/apache/poi/hssf/record/NameRecord.java @@ -243,12 +243,18 @@ public final class NameRecord extends Record { } } /** - * @return true if name is a function + * @return true if name is a function */ public boolean isFunctionName() { return (field_1_option_flag & Option.OPT_FUNCTION_NAME) != 0; } + /** + * @return true 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 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 6959d146f9..8abab17a5b 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 @@ -42,7 +42,7 @@ final class ExternalFunction implements FreeRefFunction { 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 @@ final class ExternalFunction implements FreeRefFunction { 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 @@ final class ExternalFunction implements FreeRefFunction { 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); } diff --git a/src/java/org/apache/poi/hssf/record/formula/eval/NameEval.java b/src/java/org/apache/poi/hssf/record/formula/eval/NameEval.java index 682394b3c2..1f77deec2a 100755 --- a/src/java/org/apache/poi/hssf/record/formula/eval/NameEval.java +++ b/src/java/org/apache/poi/hssf/record/formula/eval/NameEval.java @@ -22,26 +22,24 @@ package org.apache.poi.hssf.record.formula.eval; */ 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(); } diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java b/src/java/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java index f7874d0ca5..55cd04ee11 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java @@ -22,6 +22,7 @@ import java.util.Stack; 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 @@ import org.apache.poi.hssf.record.formula.eval.ValueEval; */ 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 class HSSFFormulaEvaluator { */ public void setCurrentRow(HSSFRow row) { // do nothing + if (false) { + row.getClass(); // suppress unused parameter compiler warning + } } @@ -297,8 +301,9 @@ public class HSSFFormulaEvaluator { */ 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 @@ public class HSSFFormulaEvaluator { 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 @@ public class HSSFFormulaEvaluator { 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 @@ public class HSSFFormulaEvaluator { 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 @@ public class HSSFFormulaEvaluator { * 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 @@ public class HSSFFormulaEvaluator { 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 diff --git a/src/testcases/org/apache/poi/hssf/data/testNames.xls b/src/testcases/org/apache/poi/hssf/data/testNames.xls index 7ebbb633af8bd6a0a951f1d4335c9242fe2c3f20..c5d43cc038a5d0ee463a66ddb0d1c125c914a750 100644 GIT binary patch delta 1911 zcma)7eP~-%6hHUnC3!DNle}8Pn_Xi*bY16I(|j$_e!Mp8#wCu`khp=W*^m z`JH<{??(=N%?{jPN4it!UtfLhGR!XJP12}I&z8^V+97c{HN0A>y?&9w} z91VA(M5|*s$B~oB3FH(q;VX+zJjCYZXSLyj>rO8J`6-LJ?ymdF`xy)J7v$HZpX2-w zIq$oAs3^dSlF!=r`-)#K32fK(A89s!vG+6fDA!b-?{ljEM{=ofgkc^Ml+6lC*(~5; znCAuQA60;FSKY#6fWK9dbiPw8XG)8ABV&+RHDF4(vN)@h&2(3OSfQ`8|QFeAM@1!By4%ih`h`(7I8s^>$L=o+^F~hEc>DQ(oUxi=%lN_pah0 z6n%AgGeBKnAJ6+cnZ}Rd*vvoock=5#ookxT?&TY_b~)d38O*0NXM~OMorx>=>F5<4 zHX6$`Ju(zeB!jnrhm+wbU1LzFnhH(Nj10s_wj>?_JZ}=RjrS#Yv1PuHJWk01bz~0j z_;j2PrGCSWFGJXWloZKN0Lme{Zs@}&F#1uC(WR@$D9?I~{MKXkDkkDrg3wv7Ur4)K zkEwOos}j{Dnh#myBIsB(qpg4q28|oVxXG{q>8K}!#ssQ%i79T__=$9@96Noz}u+R9U+95}+S%v`IM28FDg|+y3Aq^dU_+EEFuhb0Z$QhtXnNT>a z_>rps&63fE-fa|wc3lOPzrOipUd%7eXYQ~s%-=I^ThQ?nDw7!T8qTLNAU^4(QT)9J zvy{w8_d)3%>?0~XLWL&>Kf8Ipw^L#s^9SP_wi%BNY60$E!2zi#Mp2Hyty$5!3h_0$ zcD^8oOe1I0r8jn)gF6xmyK2sjJX0rXMR63E$S~=3>+A=)Q6Uh!_v#E4CmCE2C z7&Y5+s2I%VQi(K~4x;H+;{G%bjNjrb;}`7T(H{-Jv}ZrN!@DMi*g5{nM2^|`#}j%x z^{IMIz=H>#wjqd2*XZeeGtbRVj*mY-J3sjnTW`)y9%3$Rgy;+e`Cof=Ch@S`W*Ye- F`x{9>UzGp= delta 1569 zcmai!e`r%z6vxkbeX%b;+Pu_OB2}BFO0BrnG%rh5ZI`|#nJB0zBB%x1HQILVI_<_L zY_xL&#WK*IIYDsj4-q;Tti0-QiogC)5&TDrZfvr$e>S!;3UgxJ+}pV~Nt%;dy?#03R;E z&lKQe`Ft=h@p!%=k6$jpj~3t;3h?;~+9sZOPDOc%&&fb;cA5KzzLeaXmTB%Mx9(B-YbeHFs zgz2zt{xEz6kRDsK;XZiaH{P)hO5r7_gJz`+&WjKb7acc~lMHJ*E3?k@rei6;Em0>o zStnhOy*JYM82_-Q8{I|A)G0O?oM(c^uh@fc9J_#u=7Ax5n*6^-%Fue&kM;oGR%#;~2Qf4Gy!4g+ui`f)RB~z{8 zun`G|BMCDZNoK^>S6iEcv6fiOh$dc6G$dMt8WL2#@7Iy8ZyYgkA<~tu{rEeDx;Nq| zz2Du63SH~=*Lt`%@nyxeEsn#E{sXTMbfwd84P<|UjA^9jU0h-v>^Y2O;uqZ1-k0tS XY;AvIUz+_Wu(N_bR~K9H-mCZ@Pc%~H diff --git a/src/testcases/org/apache/poi/hssf/usermodel/AllUserModelTests.java b/src/testcases/org/apache/poi/hssf/usermodel/AllUserModelTests.java index 772a4fdfc7..72d420cb4f 100755 --- a/src/testcases/org/apache/poi/hssf/usermodel/AllUserModelTests.java +++ b/src/testcases/org/apache/poi/hssf/usermodel/AllUserModelTests.java @@ -46,6 +46,7 @@ public class AllUserModelTests { 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); diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFFormulaEvaluator.java b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFFormulaEvaluator.java new file mode 100644 index 0000000000..d1f70a0ad4 --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFFormulaEvaluator.java @@ -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); + } +} -- 2.39.5