Return-Path: Delivered-To: apmail-poi-commits-archive@minotaur.apache.org Received: (qmail 88907 invoked from network); 28 Nov 2010 12:05:29 -0000 Received: from unknown (HELO mail.apache.org) (140.211.11.3) by 140.211.11.9 with SMTP; 28 Nov 2010 12:05:29 -0000 Received: (qmail 27583 invoked by uid 500); 28 Nov 2010 12:05:28 -0000 Delivered-To: apmail-poi-commits-archive@poi.apache.org Received: (qmail 27546 invoked by uid 500); 28 Nov 2010 12:05:28 -0000 Mailing-List: contact commits-help@poi.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@poi.apache.org Delivered-To: mailing list commits@poi.apache.org Received: (qmail 27532 invoked by uid 99); 28 Nov 2010 12:05:27 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 28 Nov 2010 12:05:27 +0000 X-ASF-Spam-Status: No, hits=-2000.0 required=10.0 tests=ALL_TRUSTED X-Spam-Check-By: apache.org Received: from [140.211.11.4] (HELO eris.apache.org) (140.211.11.4) by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 28 Nov 2010 12:05:24 +0000 Received: by eris.apache.org (Postfix, from userid 65534) id 42249238897F; Sun, 28 Nov 2010 12:03:53 +0000 (UTC) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r1039870 - in /poi/trunk: src/java/org/apache/poi/hssf/usermodel/ src/java/org/apache/poi/ss/formula/ src/java/org/apache/poi/ss/formula/atp/ src/java/org/apache/poi/ss/formula/eval/forked/ src/java/org/apache/poi/ss/formula/function/ src/j... Date: Sun, 28 Nov 2010 12:03:52 -0000 To: commits@poi.apache.org From: yegor@apache.org X-Mailer: svnmailer-1.0.8 Message-Id: <20101128120353.42249238897F@eris.apache.org> Author: yegor Date: Sun Nov 28 12:03:52 2010 New Revision: 1039870 URL: http://svn.apache.org/viewvc?rev=1039870&view=rev Log: improved work with UDFs and Analysis Toolpack functions, ATP functions are enabled by default and user can create / evaluate them just like built-in functions, both HSSF andf XSSF are supported Added: poi/trunk/src/ooxml/java/org/apache/poi/xssf/model/IndexedUDFFinder.java poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFExternalFunctions.java poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFExternalFunctions.java poi/trunk/src/testcases/org/apache/poi/ss/formula/BaseTestExternalFunctions.java poi/trunk/test-data/spreadsheet/atp.xls (with props) poi/trunk/test-data/spreadsheet/atp.xlsx (with props) Modified: poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationWorkbook.java poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java poi/trunk/src/java/org/apache/poi/ss/formula/EvaluationWorkbook.java poi/trunk/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java poi/trunk/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java poi/trunk/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationWorkbook.java poi/trunk/src/java/org/apache/poi/ss/formula/function/FunctionMetadataRegistry.java poi/trunk/src/java/org/apache/poi/ss/formula/udf/AggregatingUDFFinder.java poi/trunk/src/java/org/apache/poi/ss/formula/udf/DefaultUDFFinder.java poi/trunk/src/java/org/apache/poi/ss/usermodel/Workbook.java poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java Modified: poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationWorkbook.java URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationWorkbook.java?rev=1039870&r1=1039869&r2=1039870&view=diff ============================================================================== --- poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationWorkbook.java (original) +++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFEvaluationWorkbook.java Sun Nov 28 12:03:52 2010 @@ -66,9 +66,7 @@ public final class HSSFEvaluationWorkboo } public NameXPtg getNameXPtg(String name) { - // TODO YK: passing UDFFinder.DEFAULT is temporary, - // a proper design should take it from the parent HSSFWorkbook - return _iBook.getNameXPtg(name, UDFFinder.DEFAULT); + return _iBook.getNameXPtg(name, _uBook.getUDFFinder()); } /** @@ -147,6 +145,9 @@ public final class HSSFEvaluationWorkboo FormulaRecordAggregate fra = (FormulaRecordAggregate) cell.getCellValueRecord(); return fra.getFormulaTokens(); } + public UDFFinder getUDFFinder(){ + return _uBook.getUDFFinder(); + } private static final class Name implements EvaluationName { Modified: poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java?rev=1039870&r1=1039869&r2=1039870&view=diff ============================================================================== --- poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java (original) +++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java Sun Nov 28 12:03:52 2010 @@ -66,6 +66,8 @@ import org.apache.poi.ss.formula.ptg.Uni 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.formula.udf.AggregatingUDFFinder; +import org.apache.poi.ss.formula.udf.UDFFinder; import org.apache.poi.ss.usermodel.CreationHelper; import org.apache.poi.ss.usermodel.Row.MissingCellPolicy; import org.apache.poi.ss.formula.FormulaType; @@ -148,6 +150,12 @@ public final class HSSFWorkbook extends private static POILogger log = POILogFactory.getLogger(HSSFWorkbook.class); + /** + * The locator of user-defined functions. + * By default includes functions from the Excel Analysis Toolpack + */ + private UDFFinder _udfFinder = UDFFinder.DEFAULT; + public static HSSFWorkbook create(InternalWorkbook book) { return new HSSFWorkbook(book); } @@ -1672,11 +1680,33 @@ public final class HSSFWorkbook extends } } - public CreationHelper getCreationHelper() { + public HSSFCreationHelper getCreationHelper() { return new HSSFCreationHelper(this); } private static byte[] newUID() { return new byte[16]; } + + /** + * + * Returns the locator of user-defined functions. + * The default instance extends the built-in functions with the Analysis Tool Pack + * + * @return the locator of user-defined functions + */ + /*package*/ UDFFinder getUDFFinder(){ + return _udfFinder; + } + + /** + * Register a new toolpack in this workbook. + * + * @param toopack the toolpack to register + */ + public void addToolPack(UDFFinder toopack){ + AggregatingUDFFinder udfs = (AggregatingUDFFinder)_udfFinder; + udfs.add(toopack); + } + } Modified: poi/trunk/src/java/org/apache/poi/ss/formula/EvaluationWorkbook.java URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/EvaluationWorkbook.java?rev=1039870&r1=1039869&r2=1039870&view=diff ============================================================================== --- poi/trunk/src/java/org/apache/poi/ss/formula/EvaluationWorkbook.java (original) +++ poi/trunk/src/java/org/apache/poi/ss/formula/EvaluationWorkbook.java Sun Nov 28 12:03:52 2010 @@ -20,6 +20,7 @@ package org.apache.poi.ss.formula; import org.apache.poi.ss.formula.ptg.NamePtg; import org.apache.poi.ss.formula.ptg.NameXPtg; import org.apache.poi.ss.formula.ptg.Ptg; +import org.apache.poi.ss.formula.udf.UDFFinder; /** * Abstracts a workbook for the purpose of formula evaluation.
@@ -49,8 +50,10 @@ public interface EvaluationWorkbook { int convertFromExternSheetIndex(int externSheetIndex); ExternalName getExternalName(int externSheetIndex, int externNameIndex); EvaluationName getName(NamePtg namePtg); + EvaluationName getName(String name, int sheetIndex); String resolveNameXText(NameXPtg ptg); Ptg[] getFormulaTokens(EvaluationCell cell); + UDFFinder getUDFFinder(); class ExternalSheet { private final String _workbookName; Modified: poi/trunk/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java?rev=1039870&r1=1039869&r2=1039870&view=diff ============================================================================== --- poi/trunk/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java (original) +++ poi/trunk/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java Sun Nov 28 12:03:52 2010 @@ -61,8 +61,8 @@ import org.apache.poi.ss.formula.eval.Va import org.apache.poi.ss.formula.functions.Choose; import org.apache.poi.ss.formula.functions.FreeRefFunction; import org.apache.poi.ss.formula.functions.IfFunc; +import org.apache.poi.ss.formula.udf.AggregatingUDFFinder; import org.apache.poi.ss.formula.udf.UDFFinder; -import org.apache.poi.hssf.usermodel.HSSFEvaluationWorkbook; import org.apache.poi.ss.util.CellReference; import org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment.WorkbookNotFoundException; import org.apache.poi.ss.formula.eval.NotImplementedException; @@ -91,7 +91,7 @@ public final class WorkbookEvaluator { private final Map _sheetIndexesByName; private CollaboratingWorkbooksEnvironment _collaboratingWorkbookEnvironment; private final IStabilityClassifier _stabilityClassifier; - private final UDFFinder _udfFinder; + private final AggregatingUDFFinder _udfFinder; /** * @param udfFinder pass null for default (AnalysisToolPak only) @@ -109,7 +109,13 @@ public final class WorkbookEvaluator { _collaboratingWorkbookEnvironment = CollaboratingWorkbooksEnvironment.EMPTY; _workbookIx = 0; _stabilityClassifier = stabilityClassifier; - _udfFinder = udfFinder == null ? UDFFinder.DEFAULT : udfFinder; + + AggregatingUDFFinder defaultToolkit = // workbook can be null in unit tests + workbook == null ? null : (AggregatingUDFFinder)workbook.getUDFFinder(); + if(defaultToolkit != null && udfFinder != null) { + defaultToolkit.add(udfFinder); + } + _udfFinder = defaultToolkit; } /** @@ -124,10 +130,7 @@ public final class WorkbookEvaluator { } /* package */ EvaluationName getName(String name, int sheetIndex) { - NamePtg namePtg = null; - if(_workbook instanceof HSSFEvaluationWorkbook){ - namePtg =((HSSFEvaluationWorkbook)_workbook).getName(name, sheetIndex).createPtg(); - } + NamePtg namePtg = _workbook.getName(name, sheetIndex).createPtg(); if(namePtg == null) { return null; Modified: poi/trunk/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java?rev=1039870&r1=1039869&r2=1039870&view=diff ============================================================================== --- poi/trunk/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java (original) +++ poi/trunk/src/java/org/apache/poi/ss/formula/atp/AnalysisToolPak.java Sun Nov 28 12:03:52 2010 @@ -32,133 +32,150 @@ import org.apache.poi.ss.formula.eval.No */ public final class AnalysisToolPak implements UDFFinder { - public static final UDFFinder instance = new AnalysisToolPak(); + public static final UDFFinder instance = new AnalysisToolPak(); - private static final class NotImplemented implements FreeRefFunction { - private final String _functionName; + private static final class NotImplemented implements FreeRefFunction { + private final String _functionName; - public NotImplemented(String functionName) { - _functionName = functionName; - } - - public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) { - throw new NotImplementedException(_functionName); - } - }; - - private final Map _functionsByName = createFunctionsMap(); - - - private AnalysisToolPak() { - // enforce singleton - } - - public FreeRefFunction findFunction(String name) { - return _functionsByName.get(name); - } - - private Map createFunctionsMap() { - Map m = new HashMap(100); - - r(m, "ACCRINT", null); - r(m, "ACCRINTM", null); - r(m, "AMORDEGRC", null); - r(m, "AMORLINC", null); - r(m, "BESSELI", null); - r(m, "BESSELJ", null); - r(m, "BESSELK", null); - r(m, "BESSELY", null); - r(m, "BIN2DEC", null); - r(m, "BIN2HEX", null); - r(m, "BIN2OCT", null); - r(m, "CO MPLEX", null); - r(m, "CONVERT", null); - r(m, "COUPDAYBS", null); - r(m, "COUPDAYS", null); - r(m, "COUPDAYSNC", null); - r(m, "COUPNCD", null); - r(m, "COUPNUM", null); - r(m, "COUPPCD", null); - r(m, "CUMIPMT", null); - r(m, "CUMPRINC", null); - r(m, "DEC2BIN", null); - r(m, "DEC2HEX", null); - r(m, "DEC2OCT", null); - r(m, "DELTA", null); - r(m, "DISC", null); - r(m, "DOLLARDE", null); - r(m, "DOLLARFR", null); - r(m, "DURATION", null); - r(m, "EDATE", null); - r(m, "EFFECT", null); - r(m, "EOMONTH", null); - r(m, "ERF", null); - r(m, "ERFC", null); - r(m, "FACTDOUBLE", null); - r(m, "FVSCHEDULE", null); - r(m, "GCD", null); - r(m, "GESTEP", null); - r(m, "HEX2BIN", null); - r(m, "HEX2DEC", null); - r(m, "HEX2OCT", null); - r(m, "IMABS", null); - r(m, "IMAGINARY", null); - r(m, "IMARGUMENT", null); - r(m, "IMCONJUGATE", null); - r(m, "IMCOS", null); - r(m, "IMDIV", null); - r(m, "IMEXP", null); - r(m, "IMLN", null); - r(m, "IMLOG10", null); - r(m, "IMLOG2", null); - r(m, "IMPOWER", null); - r(m, "IMPRODUCT", null); - r(m, "IMREAL", null); - r(m, "IMSIN", null); - r(m, "IMSQRT", null); - r(m, "IMSUB", null); - r(m, "IMSUM", null); - r(m, "INTRATE", null); - r(m, "ISEVEN", ParityFunction.IS_EVEN); - r(m, "ISODD", ParityFunction.IS_ODD); - r(m, "LCM", null); - r(m, "MDURATION", null); - r(m, "MROUND", null); - r(m, "MULTINOMIAL", null); - r(m, "NETWORKDAYS", null); - r(m, "NOMINAL", null); - r(m, "OCT2BIN", null); - r(m, "OCT2DEC", null); - r(m, "OCT2HEX", null); - r(m, "ODDFPRICE", null); - r(m, "ODDFYIELD", null); - r(m, "ODDLPRICE", null); - r(m, "ODDLYIELD", null); - r(m, "PRICE", null); - r(m, "PRICEDISC", null); - r(m, "PRICEMAT", null); - r(m, "QUOTIENT", null); - r(m, "RANDBETWEEN", RandBetween.instance); - r(m, "RECEIVED", null); - r(m, "SERIESSUM", null); - r(m, "SQRTPI", null); - r(m, "TBILLEQ", null); - r(m, "TBILLPRICE", null); - r(m, "TBILLYIELD", null); - r(m, "WEEKNUM", null); - r(m, "WORKDAY", null); - r(m, "XIRR", null); - r(m, "XNPV", null); - r(m, "YEARFRAC", YearFrac.instance); - r(m, "YIELD", null); - r(m, "YIELDDISC", null); - r(m, "YIELDMAT", null); - - return m; - } - - private static void r(Map m, String functionName, FreeRefFunction pFunc) { - FreeRefFunction func = pFunc == null ? new NotImplemented(functionName) : pFunc; - m.put(functionName, func); - } + public NotImplemented(String functionName) { + _functionName = functionName; + } + + public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) { + throw new NotImplementedException(_functionName); + } + } + + ; + + private final Map _functionsByName = createFunctionsMap(); + + + private AnalysisToolPak() { + // enforce singleton + } + + public FreeRefFunction findFunction(String name) { + return _functionsByName.get(name); + } + + private Map createFunctionsMap() { + Map m = new HashMap(108); + + r(m, "ACCRINT", null); + r(m, "ACCRINTM", null); + r(m, "AMORDEGRC", null); + r(m, "AMORLINC", null); + r(m, "AVERAGEIF", null); + r(m, "AVERAGEIFS", null); + r(m, "BAHTTEXT", null); + r(m, "BESSELI", null); + r(m, "BESSELJ", null); + r(m, "BESSELK", null); + r(m, "BESSELY", null); + r(m, "BIN2DEC", null); + r(m, "BIN2HEX", null); + r(m, "BIN2OCT", null); + r(m, "COMPLEX", null); + r(m, "CONVERT", null); + r(m, "COUNTIFS", null); + r(m, "COUPDAYBS", null); + r(m, "COUPDAYS", null); + r(m, "COUPDAYSNC", null); + r(m, "COUPNCD", null); + r(m, "COUPNUM", null); + r(m, "COUPPCD", null); + r(m, "CUBEKPIMEMBER", null); + r(m, "CUBEMEMBER", null); + r(m, "CUBEMEMBERPROPERTY", null); + r(m, "CUBERANKEDMEMBER", null); + r(m, "CUBESET", null); + r(m, "CUBESETCOUNT", null); + r(m, "CUBEVALUE", null); + r(m, "CUMIPMT", null); + r(m, "CUMPRINC", null); + r(m, "DEC2BIN", null); + r(m, "DEC2HEX", null); + r(m, "DEC2OCT", null); + r(m, "DELTA", null); + r(m, "DISC", null); + r(m, "DOLLARDE", null); + r(m, "DOLLARFR", null); + r(m, "DURATION", null); + r(m, "EDATE", null); + r(m, "EFFECT", null); + r(m, "EOMONTH", null); + r(m, "ERF", null); + r(m, "ERFC", null); + r(m, "FACTDOUBLE", null); + r(m, "FVSCHEDULE", null); + r(m, "GCD", null); + r(m, "GESTEP", null); + r(m, "HEX2BIN", null); + r(m, "HEX2DEC", null); + r(m, "HEX2OCT", null); + r(m, "IFERROR", null); + r(m, "IMABS", null); + r(m, "IMAGINARY", null); + r(m, "IMARGUMENT", null); + r(m, "IMCONJUGATE", null); + r(m, "IMCOS", null); + r(m, "IMDIV", null); + r(m, "IMEXP", null); + r(m, "IMLN", null); + r(m, "IMLOG10", null); + r(m, "IMLOG2", null); + r(m, "IMPOWER", null); + r(m, "IMPRODUCT", null); + r(m, "IMREAL", null); + r(m, "IMSIN", null); + r(m, "IMSQRT", null); + r(m, "IMSUB", null); + r(m, "IMSUM", null); + r(m, "INTRATE", null); + r(m, "ISEVEN", ParityFunction.IS_EVEN); + r(m, "ISODD", ParityFunction.IS_ODD); + r(m, "JIS", null); + r(m, "LCM", null); + r(m, "MDURATION", null); + r(m, "MROUND", null); + r(m, "MULTINOMIAL", null); + r(m, "NETWORKDAYS", null); + r(m, "NOMINAL", null); + r(m, "OCT2BIN", null); + r(m, "OCT2DEC", null); + r(m, "OCT2HEX", null); + r(m, "ODDFPRICE", null); + r(m, "ODDFYIELD", null); + r(m, "ODDLPRICE", null); + r(m, "ODDLYIELD", null); + r(m, "PRICE", null); + r(m, "PRICEDISC", null); + r(m, "PRICEMAT", null); + r(m, "QUOTIENT", null); + r(m, "RANDBETWEEN", RandBetween.instance); + r(m, "RECEIVED", null); + r(m, "RTD", null); + r(m, "SERIESSUM", null); + r(m, "SQRTPI", null); + r(m, "SUMIFS", null); + r(m, "TBILLEQ", null); + r(m, "TBILLPRICE", null); + r(m, "TBILLYIELD", null); + r(m, "WEEKNUM", null); + r(m, "WORKDAY", null); + r(m, "XIRR", null); + r(m, "XNPV", null); + r(m, "YEARFRAC", YearFrac.instance); + r(m, "YIELD", null); + r(m, "YIELDDISC", null); + r(m, "YIELDMAT", null); + + return m; + } + + private static void r(Map m, String functionName, FreeRefFunction pFunc) { + FreeRefFunction func = pFunc == null ? new NotImplemented(functionName) : pFunc; + m.put(functionName, func); + } } Modified: poi/trunk/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationWorkbook.java URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationWorkbook.java?rev=1039870&r1=1039869&r2=1039870&view=diff ============================================================================== --- poi/trunk/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationWorkbook.java (original) +++ poi/trunk/src/java/org/apache/poi/ss/formula/eval/forked/ForkedEvaluationWorkbook.java Sun Nov 28 12:03:52 2010 @@ -27,6 +27,7 @@ import org.apache.poi.ss.formula.Evaluat import org.apache.poi.ss.formula.EvaluationName; import org.apache.poi.ss.formula.EvaluationSheet; import org.apache.poi.ss.formula.EvaluationWorkbook; +import org.apache.poi.ss.formula.udf.UDFFinder; import org.apache.poi.ss.usermodel.Workbook; /** @@ -102,6 +103,10 @@ final class ForkedEvaluationWorkbook imp return _masterBook.getName(namePtg); } + public EvaluationName getName(String name, int sheetIndex){ + return _masterBook.getName(name, sheetIndex); + } + public EvaluationSheet getSheet(int sheetIndex) { return getSharedSheet(getSheetName(sheetIndex)); } @@ -130,6 +135,10 @@ final class ForkedEvaluationWorkbook imp return _masterBook.resolveNameXText(ptg); } + public UDFFinder getUDFFinder(){ + return _masterBook.getUDFFinder(); + } + private static final class OrderedSheet implements Comparable { private final String _sheetName; private final int _index; Modified: poi/trunk/src/java/org/apache/poi/ss/formula/function/FunctionMetadataRegistry.java URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/function/FunctionMetadataRegistry.java?rev=1039870&r1=1039869&r2=1039870&view=diff ============================================================================== --- poi/trunk/src/java/org/apache/poi/ss/formula/function/FunctionMetadataRegistry.java (original) +++ poi/trunk/src/java/org/apache/poi/ss/formula/function/FunctionMetadataRegistry.java Sun Nov 28 12:03:52 2010 @@ -41,7 +41,7 @@ public final class FunctionMetadataRegis private final FunctionMetadata[] _functionDataByIndex; private final Map _functionDataByName; - private static FunctionMetadataRegistry getInstance() { + public static FunctionMetadataRegistry getInstance() { if (_instance == null) { _instance = FunctionMetadataReader.createRegistry(); } Modified: poi/trunk/src/java/org/apache/poi/ss/formula/udf/AggregatingUDFFinder.java URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/udf/AggregatingUDFFinder.java?rev=1039870&r1=1039869&r2=1039870&view=diff ============================================================================== --- poi/trunk/src/java/org/apache/poi/ss/formula/udf/AggregatingUDFFinder.java (original) +++ poi/trunk/src/java/org/apache/poi/ss/formula/udf/AggregatingUDFFinder.java Sun Nov 28 12:03:52 2010 @@ -19,17 +19,22 @@ package org.apache.poi.ss.formula.udf; import org.apache.poi.ss.formula.functions.FreeRefFunction; +import java.util.ArrayList; +import java.util.Arrays; +import java.util.Collection; + /** * Collects add-in libraries and VB macro functions together into one UDF finder * * @author PUdalau */ -public final class AggregatingUDFFinder implements UDFFinder { +public class AggregatingUDFFinder implements UDFFinder { - private final UDFFinder[] _usedToolPacks; + private final Collection _usedToolPacks; public AggregatingUDFFinder(UDFFinder ... usedToolPacks) { - _usedToolPacks = usedToolPacks.clone(); + _usedToolPacks = new ArrayList(usedToolPacks.length); + _usedToolPacks.addAll(Arrays.asList(usedToolPacks)); } /** @@ -49,4 +54,13 @@ public final class AggregatingUDFFinder } return null; } + + /** + * Add a new toolpack + * + * @param toolPack the UDF toolpack to add + */ + public void add(UDFFinder toolPack){ + _usedToolPacks.add(toolPack); + } } Modified: poi/trunk/src/java/org/apache/poi/ss/formula/udf/DefaultUDFFinder.java URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/udf/DefaultUDFFinder.java?rev=1039870&r1=1039869&r2=1039870&view=diff ============================================================================== --- poi/trunk/src/java/org/apache/poi/ss/formula/udf/DefaultUDFFinder.java (original) +++ poi/trunk/src/java/org/apache/poi/ss/formula/udf/DefaultUDFFinder.java Sun Nov 28 12:03:52 2010 @@ -38,12 +38,12 @@ public final class DefaultUDFFinder impl } HashMap m = new HashMap(nFuncs * 3 / 2); for (int i = 0; i < functionImpls.length; i++) { - m.put(functionNames[i], functionImpls[i]); + m.put(functionNames[i].toUpperCase(), functionImpls[i]); } _functionsByName = m; } public FreeRefFunction findFunction(String name) { - return _functionsByName.get(name); + return _functionsByName.get(name.toUpperCase()); } } Modified: poi/trunk/src/java/org/apache/poi/ss/usermodel/Workbook.java URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/usermodel/Workbook.java?rev=1039870&r1=1039869&r2=1039870&view=diff ============================================================================== --- poi/trunk/src/java/org/apache/poi/ss/usermodel/Workbook.java (original) +++ poi/trunk/src/java/org/apache/poi/ss/usermodel/Workbook.java Sun Nov 28 12:03:52 2010 @@ -21,6 +21,7 @@ import java.io.IOException; import java.io.OutputStream; import java.util.List; +import org.apache.poi.ss.formula.udf.UDFFinder; import org.apache.poi.ss.usermodel.Row.MissingCellPolicy; /** @@ -496,4 +497,12 @@ public interface Workbook { * @throws IllegalArgumentException if the supplied sheet index or state is invalid */ void setSheetHidden(int sheetIx, int hidden); + + /** + * Register a new toolpack in this workbook. + * + * @param toopack the toolpack to register + */ + void addToolPack(UDFFinder toopack); + } Added: poi/trunk/src/ooxml/java/org/apache/poi/xssf/model/IndexedUDFFinder.java URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/model/IndexedUDFFinder.java?rev=1039870&view=auto ============================================================================== --- poi/trunk/src/ooxml/java/org/apache/poi/xssf/model/IndexedUDFFinder.java (added) +++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/model/IndexedUDFFinder.java Sun Nov 28 12:03:52 2010 @@ -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.xssf.model; + +import org.apache.poi.ss.formula.functions.FreeRefFunction; +import org.apache.poi.ss.formula.udf.AggregatingUDFFinder; +import org.apache.poi.ss.formula.udf.UDFFinder; +import org.apache.poi.util.Internal; + +import java.util.HashMap; + +/** + * A UDFFinder that can retrieve functions both by name and by fake index. + * + * @author Yegor Kozlov + */ +@Internal +public final class IndexedUDFFinder extends AggregatingUDFFinder { + private final HashMap _funcMap; + + public IndexedUDFFinder(UDFFinder... usedToolPacks) { + super(usedToolPacks); + _funcMap = new HashMap(); + } + + public FreeRefFunction findFunction(String name) { + FreeRefFunction func = super.findFunction(name); + if (func != null) { + int idx = getFunctionIndex(name); + _funcMap.put(idx, name); + } + return func; + } + + public String getFunctionName(int idx) { + return _funcMap.get(idx); + } + + public int getFunctionIndex(String name) { + return name.hashCode(); + } +} Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java?rev=1039870&r1=1039869&r2=1039870&view=diff ============================================================================== --- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java (original) +++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFEvaluationWorkbook.java Sun Nov 28 12:03:52 2010 @@ -17,6 +17,7 @@ package org.apache.poi.xssf.usermodel; +import org.apache.poi.ss.formula.functions.FreeRefFunction; import org.apache.poi.ss.formula.ptg.NamePtg; import org.apache.poi.ss.formula.ptg.NameXPtg; import org.apache.poi.ss.formula.ptg.Ptg; @@ -29,8 +30,12 @@ import org.apache.poi.ss.formula.Formula import org.apache.poi.ss.formula.FormulaParsingWorkbook; import org.apache.poi.ss.formula.FormulaRenderingWorkbook; import org.apache.poi.ss.formula.FormulaType; +import org.apache.poi.ss.formula.udf.UDFFinder; +import org.apache.poi.xssf.model.IndexedUDFFinder; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDefinedName; +import java.util.HashMap; + /** * Internal POI use only * @@ -100,10 +105,18 @@ public final class XSSFEvaluationWorkboo } public NameXPtg getNameXPtg(String name) { - // may require to return null to make tests pass - throw new RuntimeException("Not implemented yet"); + IndexedUDFFinder udfFinder = (IndexedUDFFinder)getUDFFinder(); + FreeRefFunction func = udfFinder.findFunction(name); + if(func == null) return null; + else return new NameXPtg(0, udfFinder.getFunctionIndex(name)); } + public String resolveNameXText(NameXPtg n) { + int idx = n.getNameIndex(); + IndexedUDFFinder udfFinder = (IndexedUDFFinder)getUDFFinder(); + return udfFinder.getFunctionName(idx); + } + public EvaluationSheet getSheet(int sheetIndex) { return new XSSFEvaluationSheet(_uBook.getSheetAt(sheetIndex)); } @@ -119,14 +132,6 @@ public final class XSSFEvaluationWorkboo return _uBook.getSheetIndex(sheetName); } - /** - * 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); @@ -145,6 +150,10 @@ public final class XSSFEvaluationWorkboo return FormulaParser.parse(cell.getCellFormula(), frBook, FormulaType.CELL, _uBook.getSheetIndex(cell.getSheet())); } + public UDFFinder getUDFFinder(){ + return _uBook.getUDFFinder(); + } + private static final class Name implements EvaluationName { private final XSSFName _nameRecord; Modified: poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java?rev=1039870&r1=1039869&r2=1039870&view=diff ============================================================================== --- poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java (original) +++ poi/trunk/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java Sun Nov 28 12:03:52 2010 @@ -46,6 +46,8 @@ import org.apache.poi.openxml4j.opc.Pack 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.formula.udf.AggregatingUDFFinder; +import org.apache.poi.ss.formula.udf.UDFFinder; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; @@ -53,11 +55,7 @@ import org.apache.poi.ss.usermodel.Row.M import org.apache.poi.ss.util.CellReference; import org.apache.poi.ss.util.WorkbookUtil; import org.apache.poi.util.*; -import org.apache.poi.xssf.model.CalculationChain; -import org.apache.poi.xssf.model.MapInfo; -import org.apache.poi.xssf.model.SharedStringsTable; -import org.apache.poi.xssf.model.StylesTable; -import org.apache.poi.xssf.model.ThemesTable; +import org.apache.poi.xssf.model.*; import org.apache.xmlbeans.XmlException; import org.apache.xmlbeans.XmlObject; import org.apache.xmlbeans.XmlOptions; @@ -124,6 +122,12 @@ public class XSSFWorkbook extends POIXML private ThemesTable theme; /** + * The locator of user-defined functions. + * By default includes functions from the Excel Analysis Toolpack + */ + private IndexedUDFFinder _udfFinder = new IndexedUDFFinder(UDFFinder.DEFAULT); + + /** * TODO */ private CalculationChain calcChain; @@ -1492,4 +1496,31 @@ public class XSSFWorkbook extends POIXML workbook.setWorkbookProtection(CTWorkbookProtection.Factory.newInstance()); } } + + /** + * + * Returns the locator of user-defined functions. + *

+ * The default instance extends the built-in functions with the Excel Analysis Tool Pack. + * To set / evaluate custom functions you need to register them as follows: + * + * + * + *

+ * @return wrapped instance of UDFFinder that allows seeking functions both by index and name + */ + /*package*/ UDFFinder getUDFFinder() { + return _udfFinder; + } + + /** + * Register a new toolpack in this workbook. + * + * @param toopack the toolpack to register + */ + public void addToolPack(UDFFinder toopack){ + _udfFinder.add(toopack); + } + + } Added: poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFExternalFunctions.java URL: http://svn.apache.org/viewvc/poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFExternalFunctions.java?rev=1039870&view=auto ============================================================================== --- poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFExternalFunctions.java (added) +++ poi/trunk/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFExternalFunctions.java Sun Nov 28 12:03:52 2010 @@ -0,0 +1,35 @@ +/* ==================================================================== + 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 org.apache.poi.ss.formula.BaseTestExternalFunctions; +import org.apache.poi.xssf.XSSFITestDataProvider; + +/** + * Tests setting and evaluating user-defined functions in HSSF + */ +public final class TestXSSFExternalFunctions extends BaseTestExternalFunctions { + + public TestXSSFExternalFunctions() { + super(XSSFITestDataProvider.instance); + } + + public void testATP(){ + baseTestInvokeATP("atp.xlsx"); + } +} Added: poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFExternalFunctions.java URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFExternalFunctions.java?rev=1039870&view=auto ============================================================================== --- poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFExternalFunctions.java (added) +++ poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFExternalFunctions.java Sun Nov 28 12:03:52 2010 @@ -0,0 +1,36 @@ +/* ==================================================================== + 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.HSSFITestDataProvider; +import org.apache.poi.ss.formula.BaseTestExternalFunctions; + +/** + * Tests setting and evaluating user-defined functions in HSSF + */ +public final class TestHSSFExternalFunctions extends BaseTestExternalFunctions { + + public TestHSSFExternalFunctions() { + super(HSSFITestDataProvider.instance); + } + + public void testATP(){ + baseTestInvokeATP("atp.xls"); + } + +} Added: poi/trunk/src/testcases/org/apache/poi/ss/formula/BaseTestExternalFunctions.java URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/BaseTestExternalFunctions.java?rev=1039870&view=auto ============================================================================== --- poi/trunk/src/testcases/org/apache/poi/ss/formula/BaseTestExternalFunctions.java (added) +++ poi/trunk/src/testcases/org/apache/poi/ss/formula/BaseTestExternalFunctions.java Sun Nov 28 12:03:52 2010 @@ -0,0 +1,144 @@ +/* ==================================================================== + 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.TestCase; +import org.apache.poi.ss.ITestDataProvider; +import org.apache.poi.ss.formula.eval.ErrorEval; +import org.apache.poi.ss.formula.eval.StringEval; +import org.apache.poi.ss.formula.eval.ValueEval; +import org.apache.poi.ss.formula.functions.FreeRefFunction; +import org.apache.poi.ss.formula.udf.DefaultUDFFinder; +import org.apache.poi.ss.formula.udf.UDFFinder; +import org.apache.poi.ss.usermodel.Cell; +import org.apache.poi.ss.usermodel.FormulaEvaluator; +import org.apache.poi.ss.usermodel.Sheet; +import org.apache.poi.ss.usermodel.Workbook; + +/** + * Test setting / evaluating of Analysis Toolpack and user-defined functions + * + * @author Yegor Kozlov + */ +public class BaseTestExternalFunctions extends TestCase { + // define two custom user-defined functions + private static class MyFunc implements FreeRefFunction { + public MyFunc() { + // + } + + public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) { + if (args.length != 1 || !(args[0] instanceof StringEval)) { + return ErrorEval.VALUE_INVALID; + } + StringEval input = (StringEval) args[0]; + return new StringEval(input.getStringValue() + "abc"); + } + } + + private static class MyFunc2 implements FreeRefFunction { + public MyFunc2() { + // + } + + public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) { + if (args.length != 1 || !(args[0] instanceof StringEval)) { + return ErrorEval.VALUE_INVALID; + } + StringEval input = (StringEval) args[0]; + return new StringEval(input.getStringValue() + "abc2"); + } + } + + /** + * register the two test UDFs in a UDF finder, to be passed to the workbook + */ + private static UDFFinder customToolpack = new DefaultUDFFinder( + new String[] { "myFunc", "myFunc2"}, + new FreeRefFunction[] { new MyFunc(), new MyFunc2()} + ); + + + protected final ITestDataProvider _testDataProvider; + + /** + * @param testDataProvider an object that provides test data in HSSF / XSSF specific way + */ + protected BaseTestExternalFunctions(ITestDataProvider testDataProvider) { + _testDataProvider = testDataProvider; + } + + public void testExternalFunctions() { + Workbook wb = _testDataProvider.createWorkbook(); + + Sheet sh = wb.createSheet(); + + Cell cell1 = sh.createRow(0).createCell(0); + cell1.setCellFormula("ISODD(1)+ISEVEN(2)"); // functions from the Excel Analysis Toolpack + assertEquals("ISODD(1)+ISEVEN(2)", cell1.getCellFormula()); + + Cell cell2 = sh.createRow(1).createCell(0); + try { + cell2.setCellFormula("MYFUNC(\"B1\")"); + fail("Should fail because MYFUNC is an unknown function"); + } catch (FormulaParseException e){ + ; //expected + } + + wb.addToolPack(customToolpack); + + cell2.setCellFormula("MYFUNC(\"B1\")"); + assertEquals("MYFUNC(\"B1\")", cell2.getCellFormula()); + + Cell cell3 = sh.createRow(2).createCell(0); + cell3.setCellFormula("MYFUNC2(\"C1\")&\"-\"&A2"); //where A2 is defined above + assertEquals("MYFUNC2(\"C1\")&\"-\"&A2", cell3.getCellFormula()); + + FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); + assertEquals(2.0, evaluator.evaluate(cell1).getNumberValue()); + assertEquals("B1abc", evaluator.evaluate(cell2).getStringValue()); + assertEquals("C1abc2-B1abc", evaluator.evaluate(cell3).getStringValue()); + + } + + /** + * test invoking saved ATP functions + * + * @param testFile either atp.xls or atp.xlsx + */ + public void baseTestInvokeATP(String testFile){ + Workbook wb = _testDataProvider.openSampleWorkbook(testFile); + FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); + + Sheet sh = wb.getSheetAt(0); + // these two are not imlemented in r + assertEquals("DELTA(1.3,1.5)", sh.getRow(0).getCell(1).getCellFormula()); + assertEquals("COMPLEX(2,4)", sh.getRow(1).getCell(1).getCellFormula()); + + Cell cell2 = sh.getRow(2).getCell(1); + assertEquals("ISODD(2)", cell2.getCellFormula()); + assertEquals(false, evaluator.evaluate(cell2).getBooleanValue()); + assertEquals(Cell.CELL_TYPE_BOOLEAN, evaluator.evaluateFormulaCell(cell2)); + + Cell cell3 = sh.getRow(3).getCell(1); + assertEquals("ISEVEN(2)", cell3.getCellFormula()); + assertEquals(true, evaluator.evaluate(cell3).getBooleanValue()); + assertEquals(Cell.CELL_TYPE_BOOLEAN, evaluator.evaluateFormulaCell(cell3)); + + } + +} Added: poi/trunk/test-data/spreadsheet/atp.xls URL: http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/atp.xls?rev=1039870&view=auto ============================================================================== Binary file - no diff available. Propchange: poi/trunk/test-data/spreadsheet/atp.xls ------------------------------------------------------------------------------ svn:mime-type = application/octet-stream Added: poi/trunk/test-data/spreadsheet/atp.xlsx URL: http://svn.apache.org/viewvc/poi/trunk/test-data/spreadsheet/atp.xlsx?rev=1039870&view=auto ============================================================================== Binary file - no diff available. Propchange: poi/trunk/test-data/spreadsheet/atp.xlsx ------------------------------------------------------------------------------ svn:mime-type = application/octet-stream --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org For additional commands, e-mail: commits-help@poi.apache.org