From 759683dcc0084497ecccac8aa917f11e22e8aa85 Mon Sep 17 00:00:00 2001 From: Yegor Kozlov Date: Wed, 16 Sep 2009 19:16:39 +0000 Subject: [PATCH] improved work with user-defined functions, see Bugzilla 47809 git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@815928 13f79535-47bb-0310-9956-ffa450edef68 --- src/documentation/content/xdocs/status.xml | 1 + .../record/formula/atp/AnalysisToolPak.java | 32 +++-- .../formula/eval/UserDefinedFunction.java | 24 +++- .../formula/toolpack/DefaultToolPack.java | 51 ++++++++ .../toolpack/MainToolPacksHandler.java | 102 +++++++++++++++ .../record/formula/toolpack/ToolPack.java | 56 ++++++++ .../usermodel/HSSFEvaluationWorkbook.java | 7 + .../poi/hssf/usermodel/HSSFWorkbook.java | 33 ++++- .../poi/ss/formula/EvaluationWorkbook.java | 14 ++ .../eval/forked/ForkedEvaluationWorkbook.java | 7 + .../org/apache/poi/ss/usermodel/Workbook.java | 28 ++++ .../usermodel/XSSFEvaluationWorkbook.java | 7 + .../poi/xssf/usermodel/XSSFWorkbook.java | 25 ++++ .../formula/eval/TestExternalFunction.java | 123 +++++++++++------- test-data/spreadsheet/testNames.xls | Bin 28160 -> 31232 bytes 15 files changed, 445 insertions(+), 65 deletions(-) create mode 100755 src/java/org/apache/poi/hssf/record/formula/toolpack/DefaultToolPack.java create mode 100755 src/java/org/apache/poi/hssf/record/formula/toolpack/MainToolPacksHandler.java create mode 100755 src/java/org/apache/poi/hssf/record/formula/toolpack/ToolPack.java diff --git a/src/documentation/content/xdocs/status.xml b/src/documentation/content/xdocs/status.xml index 17ada799c7..c4c9288550 100644 --- a/src/documentation/content/xdocs/status.xml +++ b/src/documentation/content/xdocs/status.xml @@ -33,6 +33,7 @@ + 47809 - Improved work with user-defined functions 47581 - fixed XSSFSheet.setColumnWidth to produce XML compatible with Mac Excel 2008 47734 - removed unnecessary svn:executable flag from files in SVN trunk 47543 - added javadoc how to avoid Excel crash when creating too many HSSFRichTextString cells 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 24423d04a0..acfb306242 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 @@ -22,10 +22,14 @@ import java.util.Map; import org.apache.poi.hssf.record.formula.eval.ValueEval; import org.apache.poi.hssf.record.formula.functions.FreeRefFunction; +import org.apache.poi.hssf.record.formula.toolpack.ToolPack; import org.apache.poi.ss.formula.OperationEvaluationContext; import org.apache.poi.ss.formula.eval.NotImplementedException; -public final class AnalysisToolPak { +/** + * Modified 09/07/09 by Petr Udalau - systematized work of ToolPacks. + */ +public final class AnalysisToolPak implements ToolPack { private static final class NotImplemented implements FreeRefFunction { private final String _functionName; @@ -38,18 +42,14 @@ public final class AnalysisToolPak { throw new NotImplementedException(_functionName); } }; + + private Map _functionsByName = createFunctionsMap(); - private static Map _functionsByName = createFunctionsMap(); - - private AnalysisToolPak() { - // no instances of this class - } - - public static FreeRefFunction findFunction(String name) { + public FreeRefFunction findFunction(String name) { return _functionsByName.get(name); } - - private static Map createFunctionsMap() { + + private Map createFunctionsMap() { Map m = new HashMap(100); r(m, "ACCRINT", null); @@ -153,4 +153,16 @@ public final class AnalysisToolPak { FreeRefFunction func = pFunc == null ? new NotImplemented(functionName) : pFunc; m.put(functionName, func); } + + public void addFunction(String name, FreeRefFunction evaluator) { + r(_functionsByName, name, evaluator); + } + + public boolean containsFunction(String name) { + return _functionsByName.containsKey(name); + } + + public void removeFunction(String name) { + _functionsByName.remove(name); + } } diff --git a/src/java/org/apache/poi/hssf/record/formula/eval/UserDefinedFunction.java b/src/java/org/apache/poi/hssf/record/formula/eval/UserDefinedFunction.java index f12c96e59f..bf390141ee 100644 --- a/src/java/org/apache/poi/hssf/record/formula/eval/UserDefinedFunction.java +++ b/src/java/org/apache/poi/hssf/record/formula/eval/UserDefinedFunction.java @@ -17,8 +17,8 @@ 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.hssf.record.formula.toolpack.MainToolPacksHandler; import org.apache.poi.ss.formula.EvaluationWorkbook; import org.apache.poi.ss.formula.OperationEvaluationContext; import org.apache.poi.ss.formula.eval.NotImplementedException; @@ -28,6 +28,8 @@ import org.apache.poi.ss.formula.eval.NotImplementedException; * AbstractFunctionPtg.field_2_fnc_index == 255) * * @author Josh Micich + * + * Modified 09/07/09 by Petr Udalau - Improved resolving of UDFs through the ToolPacks. */ final class UserDefinedFunction implements FreeRefFunction { @@ -38,7 +40,7 @@ final class UserDefinedFunction implements FreeRefFunction { } public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) { - + EvaluationWorkbook workbook = ec.getWorkbook(); int nIncomingArgs = args.length; if(nIncomingArgs < 1) { throw new RuntimeException("function name argument missing"); @@ -47,9 +49,9 @@ final class UserDefinedFunction implements FreeRefFunction { ValueEval nameArg = args[0]; FreeRefFunction targetFunc; if (nameArg instanceof NameEval) { - targetFunc = findInternalUserDefinedFunction((NameEval) nameArg); + targetFunc = findInternalUserDefinedFunction(workbook, (NameEval) nameArg); } else if (nameArg instanceof NameXEval) { - targetFunc = findExternalUserDefinedFunction(ec.getWorkbook(), (NameXEval) nameArg); + targetFunc = findExternalUserDefinedFunction(workbook, (NameXEval) nameArg); } else { throw new RuntimeException("First argument should be a NameEval, but got (" + nameArg.getClass().getName() + ")"); @@ -67,21 +69,29 @@ final class UserDefinedFunction implements FreeRefFunction { if(false) { System.out.println("received call to external user defined function (" + functionName + ")"); } - // currently only looking for functions from the 'Analysis TookPak' e.g. "YEARFRAC" or "ISEVEN" + // currently only looking for functions from the 'Analysis TookPak'(contained in MainToolPacksHandler) 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); + FreeRefFunction result = MainToolPacksHandler.instance().findFunction(functionName); if (result != null) { return result; } throw new NotImplementedException(functionName); } - private static FreeRefFunction findInternalUserDefinedFunction(NameEval functionNameEval) { + private static FreeRefFunction findInternalUserDefinedFunction(EvaluationWorkbook workbook, + NameEval functionNameEval) { String functionName = functionNameEval.getFunctionName(); if(false) { System.out.println("received call to internal user defined function (" + functionName + ")"); } + FreeRefFunction functionEvaluator = workbook.findUserDefinedFunction(functionName); + if (functionEvaluator == null) { + functionEvaluator = MainToolPacksHandler.instance().findFunction(functionName); + } + if (functionEvaluator != null) { + return functionEvaluator; + } // TODO find the implementation for the user defined function throw new NotImplementedException(functionName); diff --git a/src/java/org/apache/poi/hssf/record/formula/toolpack/DefaultToolPack.java b/src/java/org/apache/poi/hssf/record/formula/toolpack/DefaultToolPack.java new file mode 100755 index 0000000000..39d2c47ca9 --- /dev/null +++ b/src/java/org/apache/poi/hssf/record/formula/toolpack/DefaultToolPack.java @@ -0,0 +1,51 @@ +/* ==================================================================== + 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.toolpack; + +import java.util.HashMap; +import java.util.Map; + +import org.apache.poi.hssf.record.formula.functions.FreeRefFunction; + +/** + * Default tool pack. + * If you want to add some UDF, but you don't want to create new tool pack, use this. + * + * @author PUdalau + */ +public class DefaultToolPack implements ToolPack { + private Map functionsByName = new HashMap(); + + public void addFunction(String name, FreeRefFunction evaluator) { + if (evaluator != null){ + functionsByName.put(name, evaluator); + } + } + + public boolean containsFunction(String name) { + return functionsByName.containsKey(name); + } + + public FreeRefFunction findFunction(String name) { + return functionsByName.get(name); + } + + public void removeFunction(String name) { + functionsByName.remove(name); + } +} diff --git a/src/java/org/apache/poi/hssf/record/formula/toolpack/MainToolPacksHandler.java b/src/java/org/apache/poi/hssf/record/formula/toolpack/MainToolPacksHandler.java new file mode 100755 index 0000000000..9bb0c07888 --- /dev/null +++ b/src/java/org/apache/poi/hssf/record/formula/toolpack/MainToolPacksHandler.java @@ -0,0 +1,102 @@ +/* ==================================================================== + 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.toolpack; + +import java.util.ArrayList; +import java.util.List; + +import org.apache.poi.hssf.record.formula.atp.AnalysisToolPak; +import org.apache.poi.hssf.record.formula.functions.FreeRefFunction; + +/** + * Contains all tool packs. Processing of UDF is through this class. + * + * @author PUdalau + */ +public class MainToolPacksHandler{ + + private DefaultToolPack defaultToolPack; + + private List usedToolPacks = new ArrayList(); + + private static MainToolPacksHandler instance; + + /** + * @return Unique instance of handler. + */ + public static MainToolPacksHandler instance() { + if (instance == null) { + instance = new MainToolPacksHandler(); + } + return instance; + } + + /** + * @return Default tool pack(which is obligatory exists in handler). + */ + public DefaultToolPack getDefaultToolPack() { + return defaultToolPack; + } + + private MainToolPacksHandler() { + defaultToolPack = new DefaultToolPack(); + usedToolPacks.add(defaultToolPack); + usedToolPacks.add(new AnalysisToolPak()); + } + + /** + * Checks if such function exists in any registered tool pack. + * @param name Name of function. + * @return true if some tool pack contains such function. + */ + public boolean containsFunction(String name) { + for (ToolPack pack : usedToolPacks) { + if (pack.containsFunction(name)) { + return true; + } + } + return false; + } + + /** + * Returns executor by specified name. Returns null if + * function isn't contained by any registered tool pack. + * + * @param name + * Name of function. + * @return Function executor. + */ + public FreeRefFunction findFunction(String name) { + FreeRefFunction evaluatorForFunction; + for (ToolPack pack : usedToolPacks) { + evaluatorForFunction = pack.findFunction(name); + if (evaluatorForFunction != null) { + return evaluatorForFunction; + } + } + return null; + } + + /** + * Registers new tool pack in handler. + * @param pack Tool pack to add. + */ + public void addToolPack(ToolPack pack) { + usedToolPacks.add(pack); + } +} diff --git a/src/java/org/apache/poi/hssf/record/formula/toolpack/ToolPack.java b/src/java/org/apache/poi/hssf/record/formula/toolpack/ToolPack.java new file mode 100755 index 0000000000..d2e58da9e3 --- /dev/null +++ b/src/java/org/apache/poi/hssf/record/formula/toolpack/ToolPack.java @@ -0,0 +1,56 @@ +/* ==================================================================== + 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.toolpack; + +import org.apache.poi.hssf.record.formula.functions.FreeRefFunction; + +/** + * Common interface for any new tool pack with executors for functions. + * + * @author PUdalau + */ +public interface ToolPack { + /** + * Returns executor by specified name. Returns null if tool + * pack doesn't contains such function. + * + * @param name Name of function. + * @return Function executor. + */ + FreeRefFunction findFunction(String name); + + /** + * Add new function with executor. + * @param name Name of function. + * @param evaluator Function executor. + */ + void addFunction(String name, FreeRefFunction evaluator); + + /** + * Returns executor by specified name if it exists. + * @param name Name of function. + */ + void removeFunction(String name); + + /** + * Checks if such function exists in tool pack. + * @param name Name of function. + * @return true if tool pack contains such function. + */ + boolean containsFunction(String name); +} diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationWorkbook.java b/src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationWorkbook.java index 968daef664..b0f18bcda9 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationWorkbook.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationWorkbook.java @@ -24,6 +24,7 @@ import org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate; 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.hssf.record.formula.functions.FreeRefFunction; import org.apache.poi.ss.formula.*; import org.apache.poi.ss.SpreadsheetVersion; @@ -31,6 +32,8 @@ import org.apache.poi.ss.SpreadsheetVersion; * Internal POI use only * * @author Josh Micich + * + * Modified 09/07/09 by Petr Udalau - added methods for searching for UDFs of this Workbook. */ public final class HSSFEvaluationWorkbook implements FormulaRenderingWorkbook, EvaluationWorkbook, FormulaParsingWorkbook { @@ -159,4 +162,8 @@ public final class HSSFEvaluationWorkbook implements FormulaRenderingWorkbook, E public SpreadsheetVersion getSpreadsheetVersion(){ return SpreadsheetVersion.EXCEL97; } + + public FreeRefFunction findUserDefinedFunction(String functionName) { + return _uBook.getUserDefinedFunction(functionName); + } } diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java b/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java index 0f98298a17..b9a732bc32 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java @@ -24,9 +24,11 @@ import java.io.InputStream; import java.io.OutputStream; import java.io.PrintWriter; import java.util.ArrayList; +import java.util.HashMap; import java.util.Hashtable; import java.util.Iterator; import java.util.List; +import java.util.Map; import java.util.regex.Pattern; import org.apache.poi.POIDocument; @@ -62,15 +64,18 @@ import org.apache.poi.hssf.record.formula.Ptg; import org.apache.poi.hssf.record.formula.Ref3DPtg; import org.apache.poi.hssf.record.formula.SheetNameFormatter; import org.apache.poi.hssf.record.formula.UnionPtg; +import org.apache.poi.hssf.record.formula.functions.FreeRefFunction; import org.apache.poi.hssf.util.CellReference; import org.apache.poi.poifs.filesystem.DirectoryNode; import org.apache.poi.poifs.filesystem.POIFSFileSystem; import org.apache.poi.ss.usermodel.CreationHelper; +import org.apache.poi.ss.usermodel.Name; import org.apache.poi.ss.usermodel.Row.MissingCellPolicy; import org.apache.poi.ss.formula.FormulaType; import org.apache.poi.util.POILogFactory; import org.apache.poi.util.POILogger; + /** * High level representation of a workbook. This is the first object most users * will construct whether they are reading or writing a workbook. It is also the @@ -82,6 +87,8 @@ import org.apache.poi.util.POILogger; * @author Glen Stampoultzis (glens at apache.org) * @author Shawn Laubach (slaubach at apache dot org) * + * + * Modified 09/07/09 by Petr Udalau - added methods for work with UDFs of this Workbook. */ public class HSSFWorkbook extends POIDocument implements org.apache.poi.ss.usermodel.Workbook { private static final Pattern COMMA_PATTERN = Pattern.compile(","); @@ -160,8 +167,9 @@ public class HSSFWorkbook extends POIDocument implements org.apache.poi.ss.userm private static POILogger log = POILogFactory.getLogger(HSSFWorkbook.class); - - + + /** Map of user defined functions, key - function name, value - instance of FreeRefFunctions */ + private Map udfFunctions; /** * Creates new HSSFWorkbook from scratch (start here!) @@ -178,6 +186,7 @@ public class HSSFWorkbook extends POIDocument implements org.apache.poi.ss.userm workbook = book; _sheets = new ArrayList( INITIAL_CAPACITY ); names = new ArrayList( INITIAL_CAPACITY ); + udfFunctions = new HashMap(); } public HSSFWorkbook(POIFSFileSystem fs) throws IOException { @@ -269,6 +278,7 @@ public class HSSFWorkbook extends POIDocument implements org.apache.poi.ss.userm _sheets = new ArrayList(INITIAL_CAPACITY); names = new ArrayList(INITIAL_CAPACITY); + udfFunctions = new HashMap(); // Grab the data from the workbook stream, however // it happens to be spelled. @@ -1617,6 +1627,25 @@ public class HSSFWorkbook extends POIDocument implements org.apache.poi.ss.userm } } } + + public FreeRefFunction getUserDefinedFunction(String functionName) { + return udfFunctions.get(functionName); + } + + public void registerUserDefinedFunction(String functionName, FreeRefFunction freeRefFunction) { + Name udfDeclaration = getName(functionName); + if (udfDeclaration == null) { + udfDeclaration = createName(); + } + udfDeclaration.setNameName(functionName); + udfDeclaration.setFunction(true); + udfFunctions.put(functionName, freeRefFunction); + + } + + public List getUserDefinedFunctionNames() { + return new ArrayList(udfFunctions.keySet()); + } /** * Is the workbook protected with a password (not encrypted)? diff --git a/src/java/org/apache/poi/ss/formula/EvaluationWorkbook.java b/src/java/org/apache/poi/ss/formula/EvaluationWorkbook.java index a3b2325719..0d65af92df 100644 --- a/src/java/org/apache/poi/ss/formula/EvaluationWorkbook.java +++ b/src/java/org/apache/poi/ss/formula/EvaluationWorkbook.java @@ -20,6 +20,7 @@ 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.hssf.record.formula.functions.FreeRefFunction; /** * Abstracts a workbook for the purpose of formula evaluation.
@@ -27,6 +28,8 @@ import org.apache.poi.hssf.record.formula.Ptg; * For POI internal use only * * @author Josh Micich + * + * Modified 09/07/09 by Petr Udalau - added methods for searching for UDFs of this Workbook. */ public interface EvaluationWorkbook { String getSheetName(int sheetIndex); @@ -51,6 +54,17 @@ public interface EvaluationWorkbook { String resolveNameXText(NameXPtg ptg); Ptg[] getFormulaTokens(EvaluationCell cell); + /** + * Find and return user defined function (UDF) contained by workbook with + * specified name. + * + * @param functionName UDF name + * @return instance of FreeRefFunction or null if no UDF with the specified + * name exists. + */ + FreeRefFunction findUserDefinedFunction(String functionName); + + class ExternalSheet { private final String _workbookName; private final String _sheetName; diff --git a/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationWorkbook.java b/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationWorkbook.java index 08c7841197..1525708f31 100644 --- a/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationWorkbook.java +++ b/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationWorkbook.java @@ -23,6 +23,7 @@ import java.util.Map; 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.hssf.record.formula.functions.FreeRefFunction; import org.apache.poi.ss.formula.EvaluationCell; import org.apache.poi.ss.formula.EvaluationName; import org.apache.poi.ss.formula.EvaluationSheet; @@ -35,6 +36,8 @@ import org.apache.poi.ss.usermodel.Workbook; * updated after a call to {@link #getOrCreateUpdatableCell(String, int, int)}. * * @author Josh Micich + * + * Modified 09/07/09 by Petr Udalau - added methods for searching for UDFs of this Workbook. */ final class ForkedEvaluationWorkbook implements EvaluationWorkbook { @@ -141,4 +144,8 @@ final class ForkedEvaluationWorkbook implements EvaluationWorkbook { return _index - o._index; } } + + public FreeRefFunction findUserDefinedFunction(String functionName) { + return _masterBook.findUserDefinedFunction(functionName); + } } diff --git a/src/java/org/apache/poi/ss/usermodel/Workbook.java b/src/java/org/apache/poi/ss/usermodel/Workbook.java index 6ebf1013f8..cbbc2a4268 100644 --- a/src/java/org/apache/poi/ss/usermodel/Workbook.java +++ b/src/java/org/apache/poi/ss/usermodel/Workbook.java @@ -21,12 +21,15 @@ import java.io.IOException; import java.io.OutputStream; import java.util.List; +import org.apache.poi.hssf.record.formula.functions.FreeRefFunction; import org.apache.poi.ss.usermodel.Row.MissingCellPolicy; /** * High level representation of a Excel workbook. This is the first object most users * will construct whether they are reading or writing a workbook. It is also the * top level object for creating new sheets/etc. + * + * Modified 09/07/09 by Petr Udalau - added methods for work with UDFs of this Workbook. */ public interface Workbook { @@ -464,4 +467,29 @@ public interface Workbook { * @param hidden 0 for not hidden, 1 for hidden, 2 for very hidden */ void setSheetHidden(int sheetIx, int hidden); + + /** + * Find and return user defined function (UDF) with specified name. + * + * @param functionName + * UDF name + * @return instance of FreeRefFunction or null if no UDF with the specified + * name exists. + */ + FreeRefFunction getUserDefinedFunction(String functionName); + + /** + * Add user defined function (UDF) to workbook + * + * @param name + * @param function + */ + void registerUserDefinedFunction(String name, FreeRefFunction function); + + /** + * Returns user defined functions (UDF) names + * + * @return list of UDF names + */ + List getUserDefinedFunctionNames(); } diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java index 5264281f00..5e6fd93904 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java @@ -20,6 +20,7 @@ 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.hssf.record.formula.functions.FreeRefFunction; import org.apache.poi.ss.formula.*; import org.apache.poi.ss.SpreadsheetVersion; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDefinedName; @@ -28,6 +29,8 @@ import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDefinedName; * Internal POI use only * * @author Josh Micich + * + * Modified 09/07/09 by Petr Udalau - added methods for searching for UDFs of this Workbook. */ public final class XSSFEvaluationWorkbook implements FormulaRenderingWorkbook, EvaluationWorkbook, FormulaParsingWorkbook { @@ -177,4 +180,8 @@ public final class XSSFEvaluationWorkbook implements FormulaRenderingWorkbook, E public SpreadsheetVersion getSpreadsheetVersion(){ return SpreadsheetVersion.EXCEL2007; } + + public FreeRefFunction findUserDefinedFunction(String functionName) { + return _uBook.getUserDefinedFunction(functionName); + } } 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 e761146d3e..ffd1736119 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java @@ -32,6 +32,7 @@ import org.apache.poi.POIXMLDocumentPart; import org.apache.poi.POIXMLException; import org.apache.poi.POIXMLProperties; import org.apache.poi.hssf.record.formula.SheetNameFormatter; +import org.apache.poi.hssf.record.formula.functions.FreeRefFunction; import org.apache.poi.openxml4j.exceptions.OpenXML4JException; import org.apache.poi.openxml4j.opc.OPCPackage; import org.apache.poi.openxml4j.opc.PackagePart; @@ -40,6 +41,7 @@ import org.apache.poi.openxml4j.opc.PackageRelationship; import org.apache.poi.openxml4j.opc.PackageRelationshipTypes; import org.apache.poi.openxml4j.opc.PackagingURIHelper; import org.apache.poi.openxml4j.opc.TargetMode; +import org.apache.poi.ss.usermodel.Name; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; @@ -63,6 +65,8 @@ import org.openxmlformats.schemas.spreadsheetml.x2006.main.*; * High level representation of a SpreadsheetML workbook. This is the first object most users * will construct whether they are reading or writing a workbook. It is also the * top level object for creating new sheets/etc. + * + * Modified 09/07/09 by Petr Udalau - added methods for work with UDFs of this Workbook. */ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Iterable { private static final Pattern COMMA_PATTERN = Pattern.compile(","); @@ -129,6 +133,9 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Iterable pictures; private static POILogger logger = POILogFactory.getLogger(XSSFWorkbook.class); + + /** Map of user defined functions, key - function name, value - instance of FreeRefFunctions */ + private Map udfFunctions = new HashMap(); /** * Create a new SpreadsheetML workbook. @@ -1346,4 +1353,22 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Iterable getUserDefinedFunctionNames() { + return new ArrayList(udfFunctions.keySet()); + } + } 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 ebd0afb552..b2937b0fcb 100644 --- a/src/testcases/org/apache/poi/hssf/record/formula/eval/TestExternalFunction.java +++ b/src/testcases/org/apache/poi/hssf/record/formula/eval/TestExternalFunction.java @@ -22,61 +22,92 @@ import java.io.IOException; import junit.framework.TestCase; import org.apache.poi.hssf.HSSFTestDataSamples; +import org.apache.poi.hssf.record.formula.functions.FreeRefFunction; +import org.apache.poi.hssf.record.formula.toolpack.DefaultToolPack; +import org.apache.poi.hssf.record.formula.toolpack.MainToolPacksHandler; +import org.apache.poi.hssf.record.formula.toolpack.ToolPack; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator; -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.eval.NotImplementedException; -import org.apache.poi.ss.usermodel.CellValue; +import org.apache.poi.ss.formula.OperationEvaluationContext; +import org.apache.poi.ss.usermodel.Workbook; + /** * * @author Josh Micich + * + * Modified 09/14/09 by Petr Udalau - Test of registering UDFs in workbook and + * using ToolPacks. */ public final class TestExternalFunction extends TestCase { - /** - * Checks that an external function can get invoked from the formula evaluator. - * @throws IOException - - */ - public void testInvoke() { - - HSSFWorkbook wb; - HSSFSheet sheet; - HSSFCell cell; - if (false) { - // TODO - this code won't work until we can create user-defined functions directly with POI - wb = new HSSFWorkbook(); - sheet = wb.createSheet(); - wb.setSheetName(0, "Sheet1"); - HSSFName hssfName = wb.createName(); - hssfName.setNameName("myFunc"); - - } else { - // This sample spreadsheet already has a VB function called 'myFunc' - wb = HSSFTestDataSamples.openSampleWorkbook("testNames.xls"); - sheet = wb.getSheetAt(0); - HSSFRow row = sheet.createRow(0); - cell = row.createCell(1); - } - - cell.setCellFormula("myFunc()"); - String actualFormula=cell.getCellFormula(); - assertEquals("myFunc()", actualFormula); - - HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); - // Check out what ExternalFunction.evaluate() does: - CellValue evalResult; - try { - evalResult = fe.evaluate(cell); - } catch (NotImplementedException e) { - assertEquals("Error evaluating cell Sheet1!B1", e.getMessage()); - assertEquals("myFunc", e.getCause().getMessage()); - return; - } - // TODO - make this test assert something more interesting as soon as ExternalFunction works a bit better - assertNotNull(evalResult); - } + private static class MyFunc implements FreeRefFunction { + public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) { + if (args.length != 1 || !(args[0] instanceof StringEval)) { + return ErrorEval.VALUE_INVALID; + } else { + StringEval input = (StringEval) args[0]; + return new StringEval(input.getStringValue() + "abc"); + } + } + } + + private static class MyFunc2 implements FreeRefFunction { + public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) { + if (args.length != 1 || !(args[0] instanceof StringEval)) { + return ErrorEval.VALUE_INVALID; + } else { + StringEval input = (StringEval) args[0]; + return new StringEval(input.getStringValue() + "abc2"); + } + } + } + + /** + * Creates and registers user-defined function "MyFunc()" directly with POI. + * This is VB function defined in "testNames.xls". In future there must be + * some parser of VBA scripts which will register UDFs. + */ + private void registerMyFunc(Workbook workbook) { + workbook.registerUserDefinedFunction("myFunc", new MyFunc()); + } + + /** + * Creates example ToolPack which contains function "MyFunc2()". + */ + private void createExampleToolPack() { + ToolPack exampleToolPack = new DefaultToolPack(); + exampleToolPack.addFunction("myFunc2", new MyFunc2()); + MainToolPacksHandler.instance().addToolPack(exampleToolPack); + } + + /** + * Checks that an external function can get invoked from the formula + * evaluator. + * + * @throws IOException + * + */ + public void testInvoke() { + HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("testNames.xls"); + HSSFSheet sheet = wb.getSheetAt(0); + + registerMyFunc(wb); + createExampleToolPack(); + + HSSFRow row = sheet.getRow(0); + HSSFCell myFuncCell = row.getCell(1); //=myFunc("_") + + HSSFCell myFunc2Cell = row.getCell(2); //=myFunc2("_") + + HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb); + try { + assertEquals("_abc", fe.evaluate(myFuncCell).getStringValue()); + assertEquals("_abc2", fe.evaluate(myFunc2Cell).getStringValue()); + } catch (Exception e) { + assertFalse(true); + } + } } diff --git a/test-data/spreadsheet/testNames.xls b/test-data/spreadsheet/testNames.xls index c5d43cc038a5d0ee463a66ddb0d1c125c914a750..ad1c2889c8b681f381720b0e035b5c63a20aa685 100644 GIT binary patch delta 7657 zcmcIp3wTsjlCFFEcBj)xNGHTRAS9g!k^~aFJDsFM2QvRKQ>V^3bMM=sNdU2cQa24R8YH18M1<*(`xpBY@z??qZ-wirrC{4SI!k@iS%N+z|L zDPy8484F-RGG>c9BioXUnPRmuDP}bs<7J2+8dFqhhIl4MWt*IIT%Kfg;e^$+M7GKv zE>6^}W{bfXl_9>L$hPi|>x50;G)$1Z6r=&_ryd%{Gf$}@J>`&8MDwT!KP&mkLWSf~ zD)6jBtsLC*$?_C887S|;FC|d^WGfYL;@jPrs*$I(l97tfZzeo3 zoNs(O$EEXGyq3za4yA5HpbRlNe-v1%Md`viVNEG+s1QvG&kFN|+(k_1Dc4fzh5PQt z>~vFws(PWmC+mdkR8@9xqVLHHnmpe3E=;%keB+)PB+YSNBWI8PY|St)rHL_4_|ZTO zeRSs!lvExpbJahzg4fi{=#-S-VT?{3Y>^9Apj*4HHY|=yGbCXAUc-)!f zRTe!=pGT40{s)_0Ehj7|DFYky=mq!>U4Z}K`17f^O3Lz2zkXx9E3H!}TS+{W;brt^ z4LL4SYg;w+a}DV-Rc4EZZq?B18p_I2Wqu7E($F6?G&ft7ZPd`uG;~Ho4LPdp1`WNW zp=mQzW~YYsY3N-I70&c7vLYvBAe$0}!{KOrwHqh#w1wP2iIaHo9buZ_37ci6cxk4s ze!^lyL&Fq{%sba2^I{go)0R2oSLR+UaR7JDQhomy4Vm-QT36o4q%Qsjr-dPZC0h^V ztM=~C_fE2R?AWm>E;H|3mzfuLdA4QcBnyArpr6jPBCBSQ4iljO;uA4`bHVFr@*%sp zveF|Zh(nc&vomQLVbUhb;MS(iSM}P(N0sm9uKj~VUr{pg5gCJPqhx~Zi|pck$AJ0y zeWzA-$HvLb z7%%Z0-Zt3;4pAzg)@8sM!vW$NeY98~jjCafrce=koF!4WM~+g0oWQfu9qj4VTF7dP zm;b1>$cG7T!ULLUVnh~Eo7hEXO@|VAZ>e4!tyyihwzsMM;O-JlrXodTILl2uk2&T+ z@*2cqXMQRV`EAN154<=?o5WV!h*T*0K3pWM6_fGk?^Oheq=x zF*<)#&!c)#Y^__I%z@$<;N}+3dDT-K;VWdWcbun^DyZ~?~ZtqRM9qkcf?2SXgLwdRM4(S?Z6U~up-lp-$4hAPfFsXJ8y45 zG4W8C6Nq!<-7%_UHiwF355`)zpswSXPA=ki?kwp;JoBX|Mbv$XT~Dlc?cBO0I+BWU z`ulN5T@(A2;nvxKw~=0|#O>G)sG(Z%T8pPBg$lPDaoNRt1DEYa1<4NC3)>TX434tJ zFFlupdF&d;KrYY};l-{w>e-$Q3$iwOu#k?3tb;7BA7RBw`aC#E+5_gDoQ6;yCyA~l z3uVhCG(*0`JA?oPz`xVj!eX+7DNlYJF+gIVG@3)XGPQLqYwqZWBEML%NuDOgmPzePVoI}*zOHBK8qxapl>Fg*ZE5De84w5sf1or+sh=22YoBWK_z{xNeRe2j7BWF|Af?x zXbppo0Nw;};r>p|>8|0J7lGalcpNkiG8CQ%4KDyqba?k=8D&byx9^VOWhIrL|0PGX zE7L8k5ii^Oh$=2*^Uo@+|Hy0A=Sda{MN`8K(_P#kOy zBysfF1qt`jq?Vd6cfxfSu)KpaqLzKuu|So?-Bm@Z>$4DiPRQ3Ew5S<`TkS(#hCb61 zEj2o>&lXm)P%UaS#HyCs+>NNaG^YA=@#LU{HsL`bcuf6Epgjcwr|wzUm( z5B*NHua3YWo?Yh>=0zW43a4WVyT9WU9>B@$Msc@Sf1P!|?3cgOtlO|Cw8Z^wv%XB5 z^)_Qx5mr?q;C)3~8XP2v^yB$$<&tcc#M1UP;^wwoaiT3_{MYR}<@Wc~v!a>4hmIQ2 zRTa($AAUBxL;ApTQWGe4!-n_Oa?~}Aa&YYUvc(!+v}*+A!OtO%bkwFcQ7fcYyvjGy z5?Ugpj;y368p4;ELE1zXF}vfEutY|Q&nd0%Dp>KMjYG9tj#@Q(a-c_t>J5A0hA%c) zEQf?Q0DEM^*90HvN>EO^4i`;3dbI*Gc=656j~2^7b-^beZG)bl+Ni4uA8`1CQIIx+ zu|!pI19!$$mg5u8AZ>(f1%~AVZiQ@O5EYPbp}zACt(9Ycs=}9Y`^g#R8!P0%Ev-pE z z87UxZmzbBusTH|$cC5BVJhH;NH{b|=G|=C3@$vdg_rH4h^z;55jcOx|C>Y$6S>5t> z^W9s0zYhK2!~Jpo>Q4%mtX{XedEcMjD*5Bn!OS?HF{eYuEr=3hzHCLn10^x2O9$Fvj9r^h)W-=Hzjr0u-ghYU8aeQU=O6vAq z6CBta90_Ir2FxGvg^Z{**gHIAduyo~Z*cD3k^XB!-q66nKRsclBx)KO9^5<-?67%l zo!Jvjw3opzF2d`W5?pB()vGLGXw|H6g3>zFw|O9B?@cpfLw0Qn4YLJX>A#sN!Pwf# zl}+AMRAPO{-YP$O(Tw*MoLmF&hX)-m1z0Bhy2J~JKw}}q!V=erM(4>&tW6FkS6x%n zpcRdpWgVW=(I5q@DQAX`uLxL;{9$^cSa59+sh3yeNtW@5dU=3~wM>@xB+|wVLyL~C zm#Oc4apmQO;q7R4Jzxjm2EdJgoq$~cz6;z0xEb($03VmP0^bI>9k2(m7cd48fFA(v z0Ne?<3&6+h-GILb+ymGT_#xl`;2!`7iPqC05cdMu_~96SAch|VJ_>jU@G#&MBRM1KGQ&+}&hrM544gY;Iev&+MqQId@k#E<+w{ z^=-5H+q$SPI1t?IU$V^XbuZ~8WqD{Yu+hK5<8$>qS6#6sw56}5_pQ1}L4;0GJeNgc z$;|zBVX7zmx{jtlC!Lxj=lo)Wy4r1J&ChQbiafLV`e$tP(2PW*-E7R2XSR5x^|t5U ziO`#Q9$C`QYxm4}akRi=oa>Qtr00IKd|m&h%Z7$&L(kCAhGxp{vgno{9PE!YUiO@C z!}HX05(meIh`HdJl+$|q8U0W?=cB6!({0@~h4-d~(oaP?A6*v;jZnd}Qb--{s#oUvM|4ooe{khsr8ndgt?1LalJm$|5FZj#EhyG+~ zgZSKE6khnsZ#SN?6fAo5_#-zQc(?ouNj+Nmh;S68r9;`uflJ!@K$ViNgDm zmpT`wb<`e{^vCe_Z#41BpeYi6H(_lQar+hJik`ld;C7~*sMC<5qEh}32uuSbkr%j3 zpN76&Vp@+~+!I(Jofhu|N{jF^C-a|B-uZsy!?nY~K}%ErKxmV{wI7*o)6kj`i+^!K&}bT3<)#=f zO)nxn89Auw&OIKDa5Y!l9?S}3A6P3Imn?yKd=Ela){-UnzFn_&PH`Of;Ao8hNepk) z)G)m~#oV1-{HlKW3;9cbGOOKv=zupn1;%ZVA~M($@h!nSBFtNYcLX*GwuHJP{4pOY zuqo6iXd#;7$4xPQ+>gj4KeWWn3feKl!{eHk_~U~e+&WkNx`>dbB;yqXVTl!tB8h83 z-1~cR$w56VKI>_ebfUKR#j!Q6vAz-MKC!tkEZr|Y?+Y7_KpAg&qoL8{M?tMGiTy&HmSL-pTtduyBQO|{l?pR=Z>+-CFo%4?gdo#j@m+ikU0)i~Uart8GkHA|OS zE3J?^YiyNH&txb}F*UY%>I)rShtFxNvO6nl-A;!FkTrYj3u~)tYpR^~M()E`%iwg1 zjJ3PO{x2R2Mk0O0Mi_lwk|c9 viQCteCenX=p#Ed3;}`U?{u`wC#f|-cmVPQWuMg{an+-#-c)W4Laru7%LZ2b= delta 4595 zcmcIodvKK16+ideZ#TP}2TKz2e(WYoo;-E~c@ZGl4J0IKNJvnE36M?LNWwy5vJr?Q zyF}Fbh8(q`BgLs5skIi!lvb;_;3zPy7AjWJ>I|Ar$8i{}wXLPd_MGqgqAU7`Q>S<5 z_x;X2=iGDeJ@@f_N$@if{Il4zSY#eva#Dy4)Jr#(UJZxCb^w@1$R=0`HbM>|moS^) zB;*nD2?c~gd_qdF%_oUlJBUjN3keGdr3BVrJ-n(|OvoFPTF3LAe(OJ{gu{F#?~SSzNm*s&OXYLFs7R>`k^lOCyZF2IA(Ag7;rThFvX8gOpR3`w?Wmto) zh9rE%a9vb4tcHxW9r&4{Ml|9~Qwk17nZAo;Ho+bW1`;UE%!yxBh0#V4gfRuHj2ufC zld#hmtwjxJxek|Y$!~mZ*4co$C z1|z&R4{a4b%b5>v*>c@P`KB9n@4b9+t!V{5UQuA!L08Mx68v37tFU55<*GQV5Y${y zkL-`PRJ!rC%8Y=OLsH-wV3aIMd>Ov3L}Wee2m@oH7} z3?ATHkOc41ln+20&RM)Pj{D%Wn&<~GxqWe!m{_`9Dlp*XrDKLv(!CBiwscINN#Z`- zQQZ{7b7E(@ro*^bZ#P(}azTfetNW~ccc)P`osdD`I$zoRc3>HUQigu|HIObVs!)wR zV-eY_2#X0z2ulgo1a_EkIpk)NaiPnnws_KIRfSV7M}B}zL4rsUVEBZDg*4A|#D{2_ z)I8in^*;S%w)-K$!vG;ZC(9ldsYtgaO6+AMUk4Vi%)|rBj0q`HNU=gU6Wh1r8?_5o zuv9^skftr5NIV@M>0`)`m(dpnI7NDvxvfl#@;24EM915e-Y{;NHJx`GHwuRZodq4} z;a(P?|F%5*XqheVZQ6Nk4N*hp$il!KFiqcy_P zL9(P=f+T3A0t0o)B1F$@dNcgEZoWfSa$J~fYczwfLlJaA8}w7TY(l>`)qU2vXHjg8=k{>h2Wu~3* zn?vC8`|*jEM0{+;ejUV`>BAufgAI;iNn1aik{n*X9lY_W@Mw8PS6<~BKn1?rP)^B8 zXv~kLtro8w!gv9F7;LJMe?)XCfhH|##QsswKKT0xPW>nN^sEjS{^|V-LWqJp@i$o0Ws>|Hly%8Im z?UJ+@hnl;i`99z#Le${9&E*Lzw0aG(S_FFPW?}0#8IP@u!Tals`20$X!k;!L;?cU4 zXrats*$5N$ZVa|uoM`hKI49?IIMz01s3Ec3jF(C5C($9{So>J#Fo_+M9WH!QzvIOB z(~!UC#2u^uFB7l1{vy__F(#X6Qj95CSrK)DAWv>sGgnAg0PpYU(|ceDhA_4>dD6c2 z4hci+((D%H8sJT+7WHKD;&73aWx`L^mC$?8CMV#s^(nKiIbl#qPKkKadTW%Ac4j!7 zf`RJP^h8>p&WN*EMrX8_tTsup<6qX#D4a{1i!Xw9*aTks`RUAZyLH#L!^{miajG{D zONdrUy>6btkGma)6xyh?zRCqO6}X}6hU!bJPqysF2M#0;8O|v!Xm&+EpE`E&iT0*_ zPo^cTtF^p1tv^>hzW3R?K5Y2uV{6ZS@aUp~X+1OVr=XIVSt1$2@Od6rOdtglAxxw9 zjOb&QZ}0W`g2fil#a!=p{R_`{<^+s#>+s-!x7Y79CtJV}GvePk=<{N?1SdOBIO@azb((N#U@%YvLsx$+O7M%!fQ9Oc}9I za{*-><;!9v2`75z*@(rQBSHT5VJ}CKB<(T5P@-Xl4#p+eIEC-`=2)a*k!aK#;!RS# zvKuEYeX~Vi#?0|^I!GATUC2+BAu4EAQq}Es)wW^)^h-6HkGO|W`X|Hzub;myIG!NJY-kWyn9UtKfO z4W5vf`lynp+_?==AExg&oKCl#$ihpT<|a^4%TY7Kkv>VEhftn6lxZi9;JasS%$NG zW|O4T5>w!3+D)#tA=*V4C)`Hh51iXIzEk77i0>xcL%5f4AK`w&0|fTpOFTh9t^N@4 z!-O9Z9?^7gZI_mFrBpBt>77_P$2!#`-Qtf+LAmX<9jt&BuYta(|hdr3~E zyRx#htgN)Of*uOUQeR7k<(0Li3(C0Za%x(~aFwB9