Added: poi/trunk/src/resources/main/org/apache/poi/hssf/record/formula/function/functionMetadata-asGenerated.txt URL: http://svn.apache.org/viewvc/poi/trunk/src/resources/main/org/apache/poi/hssf/record/formula/function/functionMetadata-asGenerated.txt?rev=641185&view=auto ============================================================================== --- poi/trunk/src/resources/main/org/apache/poi/hssf/record/formula/function/functionMetadata-asGenerated.txt (added) +++ poi/trunk/src/resources/main/org/apache/poi/hssf/record/formula/function/functionMetadata-asGenerated.txt Wed Mar 26 00:32:28 2008 @@ -0,0 +1,283 @@ +# 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. + +# Created by (org.apache.poi.hssf.record.formula.function.ExcelFileFormatDocFunctionExtractor) +# from source file 'excelfileformat.odt' (size=355750, crc=0x2FAEA65A) +# +#Columns: (index, name, minParams, maxParams, returnClass, paramClasses, isVolatile, hasFootnote ) + +# Built-In Sheet Functions in BIFF2 +0 COUNT 0 30 V R +1 IF 2 3 R V R R +2 ISNA 1 1 V V +3 ISERROR 1 1 V V +4 SUM 0 30 V R +5 AVERAGE 1 30 V R +6 MIN 1 30 V R +7 MAX 1 30 V R +8 ROW 0 1 V R +9 COLUMN 0 1 V R +10 NA 0 0 V +11 NPV 2 30 V V R +12 STDEV 1 30 V R +13 DOLLAR 1 2 V V V +14 FIXED 2 2 V V V x +15 SIN 1 1 V V +16 COS 1 1 V V +17 TAN 1 1 V V +18 ARCTAN 1 1 V V +19 PI 0 0 V +20 SQRT 1 1 V V +21 EXP 1 1 V V +22 LN 1 1 V V +23 LOG10 1 1 V V +24 ABS 1 1 V V +25 INT 1 1 V V +26 SIGN 1 1 V V +27 ROUND 2 2 V V V +28 LOOKUP 2 3 V V R R +29 INDEX 2 4 R R V V V +30 REPT 2 2 V V V +31 MID 3 3 V V V V +32 LEN 1 1 V V +33 VALUE 1 1 V V +34 TRUE 0 0 V +35 FALSE 0 0 V +36 AND 1 30 V R +37 OR 1 30 V R +38 NOT 1 1 V V +39 MOD 2 2 V V V +40 DCOUNT 3 3 V R R R +41 DSUM 3 3 V R R R +42 DAVERAGE 3 3 V R R R +43 DMIN 3 3 V R R R +44 DMAX 3 3 V R R R +45 DSTDEV 3 3 V R R R +46 VAR 1 30 V R +47 DVAR 3 3 V R R R +48 TEXT 2 2 V V V +49 LINEST 1 2 A R R x +50 TREND 1 3 A R R R x +51 LOGEST 1 2 A R R x +52 GROWTH 1 3 A R R R x +56 PV 3 5 V V V V V V +# Built-In Sheet Functions in BIFF2 +57 FV 3 5 V V V V V V +58 NPER 3 5 V V V V V V +59 PMT 3 5 V V V V V V +60 RATE 3 6 V V V V V V V +61 MIRR 3 3 V R V V +62 IRR 1 2 V R V +63 RAND 0 0 V x +64 MATCH 2 3 V V R R +65 DATE 3 3 V V V V +66 TIME 3 3 V V V V +67 DAY 1 1 V V +68 MONTH 1 1 V V +69 YEAR 1 1 V V +70 WEEKDAY 1 1 V V x +71 HOUR 1 1 V V +72 MINUTE 1 1 V V +73 SECOND 1 1 V V +74 NOW 0 0 V x +75 AREAS 1 1 V R +76 ROWS 1 1 V R +77 COLUMNS 1 1 V R +78 OFFSET 3 5 R R V V V V x +82 SEARCH 2 3 V V V V +83 TRANSPOSE 1 1 A A +86 TYPE 1 1 V V +97 ATAN2 2 2 V V V +98 ASIN 1 1 V V +99 ACOS 1 1 V V +100 CHOOSE 2 30 R V R +101 HLOOKUP 3 3 V V R R x +102 VLOOKUP 3 3 V V R R x +105 ISREF 1 1 V R +109 LOG 1 2 V V V +111 CHAR 1 1 V V +112 LOWER 1 1 V V +113 UPPER 1 1 V V +114 PROPER 1 1 V V +115 LEFT 1 2 V V V +116 RIGHT 1 2 V V V +117 EXACT 2 2 V V V +118 TRIM 1 1 V V +119 REPLACE 4 4 V V V V V +120 SUBSTITUTE 3 4 V V V V V +121 CODE 1 1 V V +124 FIND 2 3 V V V V +125 CELL 1 2 V V R x +126 ISERR 1 1 V V +127 ISTEXT 1 1 V V +128 ISNUMBER 1 1 V V +129 ISBLANK 1 1 V V +130 T 1 1 V R +131 N 1 1 V R +140 DATEVALUE 1 1 V V +141 TIMEVALUE 1 1 V V +142 SLN 3 3 V V V V +143 SYD 4 4 V V V V V +144 DDB 4 5 V V V V V V +148 INDIRECT 1 2 R V V x +162 CLEAN 1 1 V V +163 MDETERM 1 1 V A +164 MINVERSE 1 1 A A +165 MMULT 2 2 A A A +167 IPMT 4 6 V V V V V V V +168 PPMT 4 6 V V V V V V V +169 COUNTA 0 30 V R +183 PRODUCT 0 30 V R +184 FACT 1 1 V V +191 DPRODUCT 3 3 V R R R +192 ISNONTEXT 1 1 V V +193 STDEVP 1 30 V R +194 VARP 1 30 V R +195 DSTDEVP 3 3 V R R R +196 DVARP 3 3 V R R R +197 TRUNC 1 1 V V x +198 ISLOGICAL 1 1 V V +199 DCOUNTA 3 3 V R R R +# New Built-In Sheet Functions in BIFF3 +49 LINEST 1 4 A R R V V x +50 TREND 1 4 A R R R V x +51 LOGEST 1 4 A R R V V x +52 GROWTH 1 4 A R R R V x +197 TRUNC 1 2 V V V x +204 YEN 1 2 V V V x +205 FINDB 2 3 V V V V +206 SEARCHB 2 3 V V V V +207 REPLACEB 4 4 V V V V V +208 LEFTB 1 2 V V V +209 RIGHTB 1 2 V V V +210 MIDB 3 3 V V V V +211 LENB 1 1 V V +212 ROUNDUP 2 2 V V V +213 ROUNDDOWN 2 2 V V V +214 ASC 1 1 V V +215 JIS 1 1 V V x +219 ADDRESS 2 5 V V V V V V +220 DAYS360 2 2 V V V x +221 TODAY 0 0 V x +222 VDB 5 7 V V V V V V V V +227 MEDIAN 1 30 V R +228 SUMPRODUCT 1 30 V A +229 SINH 1 1 V V +230 COSH 1 1 V V +231 TANH 1 1 V V +232 ASINH 1 1 V V +233 ACOSH 1 1 V V +234 ATANH 1 1 V V +235 DGET 3 3 V R R R +244 INFO 1 1 V V +# New Built-In Sheet Functions in BIFF4 +14 FIXED 2 3 V V V V x +216 RANK 2 3 V V R V +247 DB 4 5 V V V V V V +252 FREQUENCY 2 2 A R R +261 ERROR.TYPE 1 1 V V +269 AVEDEV 1 30 V R +270 BETADIST 3 5 V V V V V V +271 GAMMALN 1 1 V V +272 BETAINV 3 5 V V V V V V +273 BINOMDIST 4 4 V V V V V +274 CHIDIST 2 2 V V V +275 CHIINV 2 2 V V V +276 COMBIN 2 2 V V V +277 CONFIDENCE 3 3 V V V V +278 CRITBINOM 3 3 V V V V +279 EVEN 1 1 V V +280 EXPONDIST 3 3 V V V V +281 FDIST 3 3 V V V V +282 FINV 3 3 V V V V +283 FISHER 1 1 V V +284 FISHERINV 1 1 V V +285 FLOOR 2 2 V V V +286 GAMMADIST 4 4 V V V V V +287 GAMMAINV 3 3 V V V V +288 CEILING 2 2 V V V +289 HYPGEOMDIST 4 4 V V V V V +290 LOGNORMDIST 3 3 V V V V +291 LOGINV 3 3 V V V V +292 NEGBINOMDIST 3 3 V V V V +293 NORMDIST 4 4 V V V V V +294 NORMSDIST 1 1 V V +295 NORMINV 3 3 V V V V +296 NORMSINV 1 1 V V +297 STANDARDIZE 3 3 V V V V +298 ODD 1 1 V V +299 PERMUT 2 2 V V V +300 POISSON 3 3 V V V V +301 TDIST 3 3 V V V V +302 WEIBULL 4 4 V V V V V +303 SUMXMY2 2 2 V A A +304 SUMX2MY2 2 2 V A A +305 SUMX2PY2 2 2 V A A +306 CHITEST 2 2 V A A +307 CORREL 2 2 V A A +308 COVAR 2 2 V A A +309 FORECAST 3 3 V V A A +310 FTEST 2 2 V A A +311 INTERCEPT 2 2 V A A +312 PEARSON 2 2 V A A +313 RSQ 2 2 V A A +314 STEYX 2 2 V A A +315 SLOPE 2 2 V A A +316 TTEST 4 4 V A A V V +317 PROB 3 4 V A A V V +318 DEVSQ 1 30 V R +319 GEOMEAN 1 30 V R +320 HARMEAN 1 30 V R +321 SUMSQ 0 30 V R +322 KURT 1 30 V R +323 SKEW 1 30 V R +324 ZTEST 2 3 V R V V +325 LARGE 2 2 V R V +326 SMALL 2 2 V R V +327 QUARTILE 2 2 V R V +328 PERCENTILE 2 2 V R V +329 PERCENTRANK 2 3 V R V V +330 MODE 1 30 V A +331 TRIMMEAN 2 2 V R V +332 TINV 2 2 V V V +# New Built-In Sheet Functions in BIFF5 +70 WEEKDAY 1 2 V V V x +101 HLOOKUP 3 4 V V R R V x +102 VLOOKUP 3 4 V V R R V x +220 DAYS360 2 3 V V V V x +336 CONCATENATE 0 30 V V +337 POWER 2 2 V V V +342 RADIANS 1 1 V V +343 DEGREES 1 1 V V +344 SUBTOTAL 2 30 V V R +345 SUMIF 2 3 V R V R +346 COUNTIF 2 2 V R V +347 COUNTBLANK 1 1 V R +350 ISPMT 4 4 V V V V V +351 DATEDIF 3 3 V V V V +352 DATESTRING 1 1 V V +353 NUMBERSTRING 2 2 V V V +354 ROMAN 1 2 V V V +# New Built-In Sheet Functions in BIFF8 +358 GETPIVOTDATA 2 30 +359 HYPERLINK 1 2 V V V +360 PHONETIC 1 1 V R +361 AVERAGEA 1 30 V R +362 MAXA 1 30 V R +363 MINA 1 30 V R +364 STDEVPA 1 30 V R +365 VARPA 1 30 V R +366 STDEVA 1 30 V R +367 VARA 1 30 V R
Added: poi/trunk/src/resources/main/org/apache/poi/hssf/record/formula/function/functionMetadata.txt URL: http://svn.apache.org/viewvc/poi/trunk/src/resources/main/org/apache/poi/hssf/record/formula/function/functionMetadata.txt?rev=641185&view=auto ============================================================================== --- poi/trunk/src/resources/main/org/apache/poi/hssf/record/formula/function/functionMetadata.txt (added) +++ poi/trunk/src/resources/main/org/apache/poi/hssf/record/formula/function/functionMetadata.txt Wed Mar 26 00:32:28 2008 @@ -0,0 +1,287 @@ +# 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. + +# Created by (org.apache.poi.hssf.record.formula.function.ExcelFileFormatDocFunctionExtractor) +# from source file 'excelfileformat.odt' (size=355750, crc=0x2FAEA65A) +# +#Columns: (index, name, minParams, maxParams, returnClass, paramClasses, isVolatile, hasFootnote ) +# +# + some manual edits ! + +# Built-In Sheet Functions in BIFF2 +0 COUNT 0 30 V R +1 IF 2 3 R V R R +2 ISNA 1 1 V V +3 ISERROR 1 1 V V +4 SUM 0 30 V R +5 AVERAGE 1 30 V R +6 MIN 1 30 V R +7 MAX 1 30 V R +8 ROW 0 1 V R +9 COLUMN 0 1 V R +10 NA 0 0 V +11 NPV 2 30 V V R +12 STDEV 1 30 V R +13 DOLLAR 1 2 V V V +14 FIXED 2 2 V V V x +15 SIN 1 1 V V +16 COS 1 1 V V +17 TAN 1 1 V V +18 ATAN 1 1 V V +19 PI 0 0 V +20 SQRT 1 1 V V +21 EXP 1 1 V V +22 LN 1 1 V V +23 LOG10 1 1 V V +24 ABS 1 1 V V +25 INT 1 1 V V +26 SIGN 1 1 V V +27 ROUND 2 2 V V V +28 LOOKUP 2 3 V V R R +29 INDEX 2 4 R R V V V +30 REPT 2 2 V V V +31 MID 3 3 V V V V +32 LEN 1 1 V V +33 VALUE 1 1 V V +34 TRUE 0 0 V +35 FALSE 0 0 V +36 AND 1 30 V R +37 OR 1 30 V R +38 NOT 1 1 V V +39 MOD 2 2 V V V +40 DCOUNT 3 3 V R R R +41 DSUM 3 3 V R R R +42 DAVERAGE 3 3 V R R R +43 DMIN 3 3 V R R R +44 DMAX 3 3 V R R R +45 DSTDEV 3 3 V R R R +46 VAR 1 30 V R +47 DVAR 3 3 V R R R +48 TEXT 2 2 V V V +49 LINEST 1 2 A R R x +50 TREND 1 3 A R R R x +51 LOGEST 1 2 A R R x +52 GROWTH 1 3 A R R R x +56 PV 3 5 V V V V V V +# Built-In Sheet Functions in BIFF2 +57 FV 3 5 V V V V V V +58 NPER 3 5 V V V V V V +59 PMT 3 5 V V V V V V +60 RATE 3 6 V V V V V V V +61 MIRR 3 3 V R V V +62 IRR 1 2 V R V +63 RAND 0 0 V x +64 MATCH 2 3 V V R R +65 DATE 3 3 V V V V +66 TIME 3 3 V V V V +67 DAY 1 1 V V +68 MONTH 1 1 V V +69 YEAR 1 1 V V +70 WEEKDAY 1 1 V V x +71 HOUR 1 1 V V +72 MINUTE 1 1 V V +73 SECOND 1 1 V V +74 NOW 0 0 V x +75 AREAS 1 1 V R +76 ROWS 1 1 V R +77 COLUMNS 1 1 V R +78 OFFSET 3 5 R R V V V V x +82 SEARCH 2 3 V V V V +83 TRANSPOSE 1 1 A A +86 TYPE 1 1 V V +97 ATAN2 2 2 V V V +98 ASIN 1 1 V V +99 ACOS 1 1 V V +100 CHOOSE 2 30 R V R +101 HLOOKUP 3 3 V V R R x +102 VLOOKUP 3 3 V V R R x +105 ISREF 1 1 V R +109 LOG 1 2 V V V +111 CHAR 1 1 V V +112 LOWER 1 1 V V +113 UPPER 1 1 V V +114 PROPER 1 1 V V +115 LEFT 1 2 V V V +116 RIGHT 1 2 V V V +117 EXACT 2 2 V V V +118 TRIM 1 1 V V +119 REPLACE 4 4 V V V V V +120 SUBSTITUTE 3 4 V V V V V +121 CODE 1 1 V V +124 FIND 2 3 V V V V +125 CELL 1 2 V V R x +126 ISERR 1 1 V V +127 ISTEXT 1 1 V V +128 ISNUMBER 1 1 V V +129 ISBLANK 1 1 V V +130 T 1 1 V R +131 N 1 1 V R +140 DATEVALUE 1 1 V V +141 TIMEVALUE 1 1 V V +142 SLN 3 3 V V V V +143 SYD 4 4 V V V V V +144 DDB 4 5 V V V V V V +148 INDIRECT 1 2 R V V x +162 CLEAN 1 1 V V +163 MDETERM 1 1 V A +164 MINVERSE 1 1 A A +165 MMULT 2 2 A A A +167 IPMT 4 6 V V V V V V V +168 PPMT 4 6 V V V V V V V +169 COUNTA 0 30 V R +183 PRODUCT 0 30 V R +184 FACT 1 1 V V +190 ISNONTEXT 1 1 V V +191 DPRODUCT 3 3 V R R R +193 STDEVP 1 30 V R +194 VARP 1 30 V R +195 DSTDEVP 3 3 V R R R +196 DVARP 3 3 V R R R +197 TRUNC 1 1 V V x +198 ISLOGICAL 1 1 V V +199 DCOUNTA 3 3 V R R R +# New Built-In Sheet Functions in BIFF3 +49 LINEST 1 4 A R R V V x +50 TREND 1 4 A R R R V x +51 LOGEST 1 4 A R R V V x +52 GROWTH 1 4 A R R R V x +197 TRUNC 1 2 V V V x +204 YEN 1 2 V V V x +205 FINDB 2 3 V V V V +206 SEARCHB 2 3 V V V V +207 REPLACEB 4 4 V V V V V +208 LEFTB 1 2 V V V +209 RIGHTB 1 2 V V V +210 MIDB 3 3 V V V V +211 LENB 1 1 V V +212 ROUNDUP 2 2 V V V +213 ROUNDDOWN 2 2 V V V +214 ASC 1 1 V V +215 JIS 1 1 V V x +219 ADDRESS 2 5 V V V V V V +220 DAYS360 2 2 V V V x +221 TODAY 0 0 V x +222 VDB 5 7 V V V V V V V V +227 MEDIAN 1 30 V R +228 SUMPRODUCT 1 30 V A +229 SINH 1 1 V V +230 COSH 1 1 V V +231 TANH 1 1 V V +232 ASINH 1 1 V V +233 ACOSH 1 1 V V +234 ATANH 1 1 V V +235 DGET 3 3 V R R R +244 INFO 1 1 V V +# New Built-In Sheet Functions in BIFF4 +14 FIXED 2 3 V V V V x +204 USDOLLAR 1 1 V V x +215 DBCS 1 1 V V x +216 RANK 2 3 V V R V +247 DB 4 5 V V V V V V +252 FREQUENCY 2 2 A R R +261 ERROR.TYPE 1 1 V V +269 AVEDEV 1 30 V R +270 BETADIST 3 5 V V V V V V +271 GAMMALN 1 1 V V +272 BETAINV 3 5 V V V V V V +273 BINOMDIST 4 4 V V V V V +274 CHIDIST 2 2 V V V +275 CHIINV 2 2 V V V +276 COMBIN 2 2 V V V +277 CONFIDENCE 3 3 V V V V +278 CRITBINOM 3 3 V V V V +279 EVEN 1 1 V V +280 EXPONDIST 3 3 V V V V +281 FDIST 3 3 V V V V +282 FINV 3 3 V V V V +283 FISHER 1 1 V V +284 FISHERINV 1 1 V V +285 FLOOR 2 2 V V V +286 GAMMADIST 4 4 V V V V V +287 GAMMAINV 3 3 V V V V +288 CEILING 2 2 V V V +289 HYPGEOMDIST 4 4 V V V V V +290 LOGNORMDIST 3 3 V V V V +291 LOGINV 3 3 V V V V +292 NEGBINOMDIST 3 3 V V V V +293 NORMDIST 4 4 V V V V V +294 NORMSDIST 1 1 V V +295 NORMINV 3 3 V V V V +296 NORMSINV 1 1 V V +297 STANDARDIZE 3 3 V V V V +298 ODD 1 1 V V +299 PERMUT 2 2 V V V +300 POISSON 3 3 V V V V +301 TDIST 3 3 V V V V +302 WEIBULL 4 4 V V V V V +303 SUMXMY2 2 2 V A A +304 SUMX2MY2 2 2 V A A +305 SUMX2PY2 2 2 V A A +306 CHITEST 2 2 V A A +307 CORREL 2 2 V A A +308 COVAR 2 2 V A A +309 FORECAST 3 3 V V A A +310 FTEST 2 2 V A A +311 INTERCEPT 2 2 V A A +312 PEARSON 2 2 V A A +313 RSQ 2 2 V A A +314 STEYX 2 2 V A A +315 SLOPE 2 2 V A A +316 TTEST 4 4 V A A V V +317 PROB 3 4 V A A V V +318 DEVSQ 1 30 V R +319 GEOMEAN 1 30 V R +320 HARMEAN 1 30 V R +321 SUMSQ 0 30 V R +322 KURT 1 30 V R +323 SKEW 1 30 V R +324 ZTEST 2 3 V R V V +325 LARGE 2 2 V R V +326 SMALL 2 2 V R V +327 QUARTILE 2 2 V R V +328 PERCENTILE 2 2 V R V +329 PERCENTRANK 2 3 V R V V +330 MODE 1 30 V A +331 TRIMMEAN 2 2 V R V +332 TINV 2 2 V V V +# New Built-In Sheet Functions in BIFF5 +70 WEEKDAY 1 2 V V V x +101 HLOOKUP 3 4 V V R R V x +102 VLOOKUP 3 4 V V R R V x +220 DAYS360 2 3 V V V V x +336 CONCATENATE 0 30 V V +337 POWER 2 2 V V V +342 RADIANS 1 1 V V +343 DEGREES 1 1 V V +344 SUBTOTAL 2 30 V V R +345 SUMIF 2 3 V R V R +346 COUNTIF 2 2 V R V +347 COUNTBLANK 1 1 V R +350 ISPMT 4 4 V V V V V +351 DATEDIF 3 3 V V V V +352 DATESTRING 1 1 V V +353 NUMBERSTRING 2 2 V V V +354 ROMAN 1 2 V V V +# New Built-In Sheet Functions in BIFF8 +358 GETPIVOTDATA 2 30 +359 HYPERLINK 1 2 V V V +360 PHONETIC 1 1 V R +361 AVERAGEA 1 30 V R +362 MAXA 1 30 V R +363 MINA 1 30 V R +364 STDEVPA 1 30 V R +365 VARPA 1 30 V R +366 STDEVA 1 30 V R +367 VARA 1 30 V R Added: poi/trunk/src/testcases/org/apache/poi/hssf/data/missingFuncs44675.xls URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/data/missingFuncs44675.xls?rev=641185&view=auto ============================================================================== Binary file - no diff available. Propchange: poi/trunk/src/testcases/org/apache/poi/hssf/data/missingFuncs44675.xls ------------------------------------------------------------------------------ svn:mime-type = application/octet-stream Modified: poi/trunk/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java?rev=641185&r1=641184&r2=641185&view=diff ============================================================================== --- poi/trunk/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java (original) +++ poi/trunk/src/testcases/org/apache/poi/hssf/model/TestFormulaParser.java Wed Mar 26 00:32:28 2008 @@ -293,7 +293,7 @@ assertEquals("FOO", tname.toFormulaString(w)); AbstractFunctionPtg tfunc = (AbstractFunctionPtg) ptg[1]; - assertEquals("externalflag", tfunc.getName()); + assertTrue(tfunc.isExternalFunction()); } public void testEmbeddedSlash() { @@ -887,5 +887,18 @@ // expected during successful test assertTrue(e.getMessage().startsWith("Too few arguments suppled to operation token")); } + } + public void testFuncPtgSelection() { + Workbook book = Workbook.createWorkbook(); + Ptg[] ptgs; + ptgs = FormulaParser.parse("countif(A1:A2, 1)", book); + assertEquals(3, ptgs.length); + if(FuncVarPtg.class == ptgs[2].getClass()) { + throw new AssertionFailedError("Identified bug 44675"); + } + assertEquals(FuncPtg.class, ptgs[2].getClass()); + ptgs = FormulaParser.parse("sin(1)", book); + assertEquals(2, ptgs.length); + assertEquals(FuncPtg.class, ptgs[1].getClass()); } } Modified: poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/AllFormulaTests.java URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/AllFormulaTests.java?rev=641185&r1=641184&r2=641185&view=diff ============================================================================== --- poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/AllFormulaTests.java (original) +++ poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/AllFormulaTests.java Wed Mar 26 00:32:28 2008 @@ -17,6 +17,8 @@ package org.apache.poi.hssf.record.formula; +import org.apache.poi.hssf.record.formula.function.AllFormulaFunctionTests; + import junit.framework.Test; import junit.framework.TestSuite; @@ -28,12 +30,12 @@ public class AllFormulaTests { public static Test suite() { - TestSuite result = new TestSuite("Tests for org.apache.poi.hssf.record.formula"); + TestSuite result = new TestSuite(AllFormulaTests.class.getName()); result.addTestSuite(TestArea3DPtg.class); result.addTestSuite(TestAreaErrPtg.class); - result.addTestSuite(TestAreaPtg.class); - result.addTestSuite(TestErrPtg.class); - result.addTestSuite(TestExternalFunctionFormulas.class); + result.addTestSuite(TestAreaPtg.class); + result.addTestSuite(TestErrPtg.class); + result.addTestSuite(TestExternalFunctionFormulas.class); result.addTestSuite(TestFuncPtg.class); result.addTestSuite(TestIntersectionPtg.class); result.addTestSuite(TestPercentPtg.class); @@ -42,6 +44,7 @@ result.addTestSuite(TestReferencePtg.class); result.addTestSuite(TestSheetNameFormatter.class); result.addTestSuite(TestUnionPtg.class); + result.addTest(AllFormulaFunctionTests.suite()); return result; } } Added: poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/function/AllFormulaFunctionTests.java URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/function/AllFormulaFunctionTests.java?rev=641185&view=auto ============================================================================== --- poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/function/AllFormulaFunctionTests.java (added) +++ poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/function/AllFormulaFunctionTests.java Wed Mar 26 00:32:28 2008 @@ -0,0 +1,37 @@ +/* ==================================================================== + 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.function; + +import junit.framework.Test; +import junit.framework.TestSuite; + +/** + * Collects all tests for this package. + * + * @author Josh Micich + */ +public class AllFormulaFunctionTests { + + public static Test suite() { + TestSuite result = new TestSuite(AllFormulaFunctionTests.class.getName()); + result.addTestSuite(TestFunctionMetadataRegistry.class); + result.addTestSuite(TestParseMissingBuiltInFuncs.class); + result.addTestSuite(TestReadMissingBuiltInFuncs.class); + return result; + } +} Added: poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/function/ExcelFileFormatDocFunctionExtractor.java URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/function/ExcelFileFormatDocFunctionExtractor.java?rev=641185&view=auto ============================================================================== --- poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/function/ExcelFileFormatDocFunctionExtractor.java (added) +++ poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/function/ExcelFileFormatDocFunctionExtractor.java Wed Mar 26 00:32:28 2008 @@ -0,0 +1,503 @@ +/* ==================================================================== + 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.function; + +import java.io.File; +import java.io.FileInputStream; +import java.io.FileNotFoundException; +import java.io.FileOutputStream; +import java.io.IOException; +import java.io.InputStream; +import java.io.OutputStream; +import java.io.PrintStream; +import java.net.MalformedURLException; +import java.net.URL; +import java.net.URLConnection; +import java.util.ArrayList; +import java.util.Arrays; +import java.util.HashMap; +import java.util.HashSet; +import java.util.List; +import java.util.Map; +import java.util.Set; +import java.util.Stack; +import java.util.zip.CRC32; +import java.util.zip.ZipException; +import java.util.zip.ZipFile; + +import org.xml.sax.Attributes; +import org.xml.sax.ContentHandler; +import org.xml.sax.InputSource; +import org.xml.sax.Locator; +import org.xml.sax.SAXException; +import org.xml.sax.XMLReader; +import org.xml.sax.helpers.XMLReaderFactory; + +/** + * This class is not used during normal POI run-time but is used at development time to generate + * the file 'functionMetadata.txt'. There are more than 300 built-in functions in Excel and the + * intention of this class is to make it easier to maintain the metadata, by extracting it from + * a reliable source. + * + * @author Josh Micich + */ +public class ExcelFileFormatDocFunctionExtractor { + + private static final String SOURCE_DOC_FILE_NAME = "excelfileformat.odt"; + + private static final class FunctionData { + + private final int _index; + private final boolean _hasFootnote; + private final String _name; + private final int _minParams; + private final int _maxParams; + private final String _returnClass; + private final String _paramClasses; + private final boolean _isVolatile; + + public FunctionData(int funcIx, boolean hasFootnote, String funcName, int minParams, int maxParams, + String returnClass, String paramClasses, boolean isVolatile) { + _index = funcIx; + _hasFootnote = hasFootnote; + _name = funcName; + _minParams = minParams; + _maxParams = maxParams; + _returnClass = returnClass; + _paramClasses = paramClasses; + _isVolatile = isVolatile; + } + public int getIndex() { + return _index; + } + public String getName() { + return _name; + } + public boolean hasFootnote() { + return _hasFootnote; + } + public String formatAsDataLine() { + return _index + "\t" + _name + "\t" + _minParams + "\t" + + _maxParams + "\t" + _returnClass + "\t" + _paramClasses + + "\t" + checkMark(_isVolatile) + "\t" + checkMark(_hasFootnote); + } + private static String checkMark(boolean b) { + return b ? "x" : ""; + } + } + + private static final class FunctionDataCollector { + + + private final Map _allFunctionsByIndex; + private final Map _allFunctionsByName; + private final Set _groupFunctionIndexes; + private final Set _groupFunctionNames; + private final PrintStream _ps; + + public FunctionDataCollector(PrintStream ps) { + _ps = ps; + _allFunctionsByIndex = new HashMap(); + _allFunctionsByName = new HashMap(); + _groupFunctionIndexes = new HashSet(); + _groupFunctionNames = new HashSet(); + } + + public void addFuntion(int funcIx, boolean hasFootnote, String funcName, int minParams, int maxParams, + String returnClass, String paramClasses, String volatileFlagStr) { + boolean isVolatile = volatileFlagStr.length() > 0; + + Integer funcIxKey = new Integer(funcIx); + if(!_groupFunctionIndexes.add(funcIxKey)) { + throw new RuntimeException("Duplicate function index (" + funcIx + ")"); + } + if(!_groupFunctionNames.add(funcName)) { + throw new RuntimeException("Duplicate function name '" + funcName + "'"); + } + + checkRedefinedFunction(hasFootnote, funcName, funcIxKey); + FunctionData fd = new FunctionData(funcIx, hasFootnote, funcName, + minParams, maxParams, returnClass, paramClasses, isVolatile); + + _allFunctionsByIndex.put(funcIxKey, fd); + _allFunctionsByName.put(funcName, fd); + } + + private void checkRedefinedFunction(boolean hasNote, String funcName, Integer funcIxKey) { + FunctionData fdPrev; + fdPrev = (FunctionData) _allFunctionsByIndex.get(funcIxKey); + if(fdPrev != null) { + if(fdPrev.hasFootnote() && hasNote) { + // func def can change if both have a foot-note + _allFunctionsByName.remove(fdPrev.getName()); + } else { + throw new RuntimeException("changing function definition without foot-note"); + } + } + fdPrev = (FunctionData) _allFunctionsByName.get(funcName); + if(fdPrev != null) { + if(fdPrev.hasFootnote() && hasNote) { + // func def can change if both have a foot-note + _allFunctionsByIndex.remove(new Integer(fdPrev.getIndex())); + } else { + throw new RuntimeException("changing function definition without foot-note"); + } + } + } + + public void endTableGroup(String headingText) { + Integer[] keys = new Integer[_groupFunctionIndexes.size()]; + _groupFunctionIndexes.toArray(keys); + _groupFunctionIndexes.clear(); + _groupFunctionNames.clear(); + Arrays.sort(keys); + + _ps.println("# " + headingText); + for (int i = 0; i < keys.length; i++) { + FunctionData fd = (FunctionData) _allFunctionsByIndex.get(keys[i]); + _ps.println(fd.formatAsDataLine()); + } + } + } + + /** + * To avoid drag-in - parse XML using only JDK. + */ + private static class EFFDocHandler implements ContentHandler { + private static final String[] HEADING_PATH_NAMES = { + "office:document-content", "office:body", "office:text", "text:h", + }; + private static final String[] TABLE_BASE_PATH_NAMES = { + "office:document-content", "office:body", "office:text", "table:table", + }; + private static final String[] TABLE_ROW_RELPATH_NAMES = { + "table:table-row", + }; + private static final String[] TABLE_CELL_RELPATH_NAMES = { + "table:table-row", "table:table-cell", "text:p", + }; + private static final String[] NOTE_REF_RELPATH_NAMES = { + "table:table-row", "table:table-cell", "text:p", "text:span", "text:note-ref", + }; + + + private final Stack _elemNameStack; + /** <code>true</code> only when parsing the target tables */ + private boolean _isInsideTable; + + private final List _rowData; + private final StringBuffer _textNodeBuffer; + private final List _rowNoteFlags; + private boolean _cellHasNote; + + private final FunctionDataCollector _fdc; + private String _lastHeadingText; + + public EFFDocHandler(FunctionDataCollector fdc) { + _fdc = fdc; + _elemNameStack = new Stack(); + _isInsideTable = false; + _rowData = new ArrayList(); + _textNodeBuffer = new StringBuffer(); + _rowNoteFlags = new ArrayList(); + } + + private boolean matchesTargetPath() { + return matchesPath(0, TABLE_BASE_PATH_NAMES); + } + private boolean matchesRelPath(String[] pathNames) { + return matchesPath(TABLE_BASE_PATH_NAMES.length, pathNames); + } + private boolean matchesPath(int baseStackIndex, String[] pathNames) { + if(_elemNameStack.size() != baseStackIndex + pathNames.length) { + return false; + } + for (int i = 0; i < pathNames.length; i++) { + if(!_elemNameStack.get(baseStackIndex + i).equals(pathNames[i])) { + return false; + } + } + return true; + } + public void characters(char[] ch, int start, int length) { + // only 2 text nodes where text is collected: + if(matchesRelPath(TABLE_CELL_RELPATH_NAMES) || matchesPath(0, HEADING_PATH_NAMES)) { + _textNodeBuffer.append(ch, start, length); + } + } + + public void endElement(String namespaceURI, String localName, String name) { + String expectedName = (String) _elemNameStack.peek(); + if(expectedName != name) { + throw new RuntimeException("close tag mismatch"); + } + if(matchesPath(0, HEADING_PATH_NAMES)) { + _lastHeadingText = _textNodeBuffer.toString().trim(); + _textNodeBuffer.setLength(0); + } + if(_isInsideTable) { + if(matchesTargetPath()) { + _fdc.endTableGroup(_lastHeadingText); + _isInsideTable = false; + } else if(matchesRelPath(TABLE_ROW_RELPATH_NAMES)) { + String[] cellData = new String[_rowData.size()]; + _rowData.toArray(cellData); + _rowData.clear(); + Boolean[] noteFlags = new Boolean[_rowNoteFlags.size()]; + _rowNoteFlags.toArray(noteFlags); + _rowNoteFlags.clear(); + processTableRow(cellData, noteFlags); + } else if(matchesRelPath(TABLE_CELL_RELPATH_NAMES)) { + _rowData.add(_textNodeBuffer.toString().trim()); + _rowNoteFlags.add(Boolean.valueOf(_cellHasNote)); + _textNodeBuffer.setLength(0); + } + } + _elemNameStack.pop(); + } + + private void processTableRow(String[] cellData, Boolean[] noteFlags) { + // each table row of the document contains data for two functions + if(cellData.length != 15) { + throw new RuntimeException("Bad table row size"); + } + processFunction(cellData, noteFlags, 0); + processFunction(cellData, noteFlags, 8); + } + public void processFunction(String[] cellData, Boolean[] noteFlags, int i) { + String funcIxStr = cellData[i + 0]; + if (funcIxStr.length() < 1) { + // empty (happens on the right hand side when there is an odd number of functions) + return; + } + int funcIx = parseInt(funcIxStr); + + boolean hasFootnote = noteFlags[i + 1].booleanValue(); + String funcName = cellData[i + 1]; + int minParams = parseInt(cellData[i + 2]); + int maxParams = parseInt(cellData[i + 3]); + + String returnClass = cellData[i + 4]; + String paramClasses = cellData[i + 5]; + String volatileFlagStr = cellData[i + 6]; + + _fdc.addFuntion(funcIx, hasFootnote, funcName, minParams, maxParams, returnClass, paramClasses, volatileFlagStr); + } + private static int parseInt(String valStr) { + try { + return Integer.parseInt(valStr); + } catch (NumberFormatException e) { + throw new RuntimeException("Value '" + valStr + "' could not be parsed as an integer"); + } + } + public void startElement(String namespaceURI, String localName, String name, Attributes atts) { + _elemNameStack.add(name); + if(matchesTargetPath()) { + String tableName = atts.getValue("table:name"); + if(tableName.startsWith("tab_fml_func") && !tableName.equals("tab_fml_func0")) { + _isInsideTable = true; + } + return; + } + if(matchesPath(0, HEADING_PATH_NAMES)) { + _textNodeBuffer.setLength(0); + } else if(matchesRelPath(TABLE_ROW_RELPATH_NAMES)) { + _rowData.clear(); + _rowNoteFlags.clear(); + } else if(matchesRelPath(TABLE_CELL_RELPATH_NAMES)) { + _textNodeBuffer.setLength(0); + _cellHasNote = false; + } else if(matchesRelPath(NOTE_REF_RELPATH_NAMES)) { + _cellHasNote = true; + } + } + + public void endDocument() { + // do nothing + } + public void endPrefixMapping(String prefix) { + // do nothing + } + public void ignorableWhitespace(char[] ch, int start, int length) { + // do nothing + } + public void processingInstruction(String target, String data) { + // do nothing + } + public void setDocumentLocator(Locator locator) { + // do nothing + } + public void skippedEntity(String name) { + // do nothing + } + public void startDocument() { + // do nothing + } + public void startPrefixMapping(String prefix, String uri) { + // do nothing + } + } + + private static void extractFunctionData(FunctionDataCollector fdc, InputStream is) { + System.setProperty("org.xml.sax.driver", "org.apache.crimson.parser.XMLReaderImpl"); + + XMLReader xr; + try { + xr = XMLReaderFactory.createXMLReader(); + } catch (SAXException e) { + throw new RuntimeException(e); + } + xr.setContentHandler(new EFFDocHandler(fdc)); + + InputSource inSrc = new InputSource(is); + + try { + xr.parse(inSrc); + is.close(); + } catch (IOException e) { + throw new RuntimeException(e); + } catch (SAXException e) { + throw new RuntimeException(e); + } + } + + private static void processFile(File effDocFile, File outFile) { + OutputStream os; + try { + os = new FileOutputStream(outFile); + } catch (FileNotFoundException e) { + throw new RuntimeException(e); + } + PrintStream ps = new PrintStream(os); + outputLicenseHeader(ps); + Class genClass = ExcelFileFormatDocFunctionExtractor.class; + ps.println("# Created by (" + genClass.getName() + ")"); + // identify the source file + ps.print("# from source file '" + SOURCE_DOC_FILE_NAME + "'"); + ps.println(" (size=" + effDocFile.length() + ", crc=" + getFileCRC(effDocFile) + ")"); + ps.println("#"); + ps.println("#Columns: (index, name, minParams, maxParams, returnClass, paramClasses, isVolatile, hasFootnote )"); + ps.println(""); + try { + ZipFile zf = new ZipFile(effDocFile); + InputStream is = zf.getInputStream(zf.getEntry("content.xml")); + extractFunctionData(new FunctionDataCollector(ps), is); + zf.close(); + } catch (ZipException e) { + throw new RuntimeException(e); + } catch (IOException e) { + throw new RuntimeException(e); + } + ps.close(); + } + + private static void outputLicenseHeader(PrintStream ps) { + String[] lines= { + "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.", + }; + for (int i = 0; i < lines.length; i++) { + ps.print("# "); + ps.println(lines[i]); + } + ps.println(); + } + + /** + * Helps identify the source file + */ + private static String getFileCRC(File f) { + CRC32 crc = new CRC32(); + byte[]buf = new byte[2048]; + try { + InputStream is = new FileInputStream(f); + while(true) { + int bytesRead = is.read(buf); + if(bytesRead<1) { + break; + } + crc.update(buf, 0, bytesRead); + } + is.close(); + } catch (IOException e) { + throw new RuntimeException(e); + } + return "0x" + Long.toHexString(crc.getValue()).toUpperCase(); + } + + private static File getSourceFile() { + if (true) { + File dir = new File("c:/josh/ref-docs"); + File effDocFile = new File(dir, SOURCE_DOC_FILE_NAME); + return effDocFile; + } + URL url; + try { + url = new URL("http://sc.openoffice.org/"; + SOURCE_DOC_FILE_NAME); + } catch (MalformedURLException e) { + throw new RuntimeException(e); + } + + File result; + byte[]buf = new byte[2048]; + try { + URLConnection conn = url.openConnection(); + InputStream is = conn.getInputStream(); + System.out.println("downloading " + url.toExternalForm()); + result = File.createTempFile("excelfileformat", "odt"); + OutputStream os = new FileOutputStream(result); + while(true) { + int bytesRead = is.read(buf); + if(bytesRead<1) { + break; + } + os.write(buf, 0, bytesRead); + } + is.close(); + os.close(); + } catch (IOException e) { + throw new RuntimeException(e); + } + System.out.println("file downloaded ok"); + return result; + } + + public static void main(String[] args) { + + File effDocFile = getSourceFile(); + if(!effDocFile.exists()) { + throw new RuntimeException("file '" + effDocFile.getAbsolutePath() + "' does not exist"); + } + + File outFile = new File("functionMetadata-asGenerated.txt"); + processFile(effDocFile, outFile); + } + +} Added: poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/function/TestFunctionMetadataRegistry.java URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/function/TestFunctionMetadataRegistry.java?rev=641185&view=auto ============================================================================== --- poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/function/TestFunctionMetadataRegistry.java (added) +++ poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/function/TestFunctionMetadataRegistry.java Wed Mar 26 00:32:28 2008 @@ -0,0 +1,43 @@ +/* ==================================================================== + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. +==================================================================== */ + +package org.apache.poi.hssf.record.formula.function; + +import junit.framework.TestCase; +/** + * + * @author Josh Micich + */ +public final class TestFunctionMetadataRegistry extends TestCase { + + public void testWellKnownFunctions() { + confirmFunction(0, "COUNT"); + confirmFunction(1, "IF"); + + } + + private static void confirmFunction(int index, String funcName) { + FunctionMetadata fm; + fm = FunctionMetadataRegistry.getFunctionByIndex(index); + assertNotNull(fm); + assertEquals(funcName, fm.getName()); + + fm = FunctionMetadataRegistry.getFunctionByName(funcName); + assertNotNull(fm); + assertEquals(index, fm.getIndex()); + } +} Added: poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/function/TestParseMissingBuiltInFuncs.java URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/function/TestParseMissingBuiltInFuncs.java?rev=641185&view=auto ============================================================================== --- poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/function/TestParseMissingBuiltInFuncs.java (added) +++ poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/function/TestParseMissingBuiltInFuncs.java Wed Mar 26 00:32:28 2008 @@ -0,0 +1,79 @@ +/* ==================================================================== + 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.function; + +import junit.framework.AssertionFailedError; +import junit.framework.TestCase; + +import org.apache.poi.hssf.model.FormulaParser; +import org.apache.poi.hssf.model.Workbook; +import org.apache.poi.hssf.record.formula.AbstractFunctionPtg; +import org.apache.poi.hssf.record.formula.FuncPtg; +import org.apache.poi.hssf.record.formula.FuncVarPtg; +import org.apache.poi.hssf.record.formula.Ptg; +/** + * @author Josh Micich + */ +public final class TestParseMissingBuiltInFuncs extends TestCase { + + private static Ptg[] parse(String formula) { + Workbook book = Workbook.createWorkbook(); + return FormulaParser.parse(formula, book); + } + private static void confirmFunc(String formula, int expPtgArraySize, boolean isVarArgFunc, int funcIx) { + Ptg[] ptgs = parse(formula); + Ptg ptgF = ptgs[ptgs.length-1]; // func is last RPN token in all these formulas + + if(!(ptgF instanceof AbstractFunctionPtg)) { + throw new RuntimeException("function token missing"); + } + AbstractFunctionPtg func = (AbstractFunctionPtg) ptgF; + if(func.getFunctionIndex() == 255) { + throw new AssertionFailedError("Failed to recognise built-in function in formula '" + + formula + "'"); + } + + assertEquals(expPtgArraySize, ptgs.length); + assertEquals(funcIx, func.getFunctionIndex()); + Class expCls = isVarArgFunc ? FuncVarPtg.class : FuncPtg.class; + assertEquals(expCls, ptgF.getClass()); + } + + public void testDatedif() { + int expSize = 4; // NB would be 5 if POI added tAttrVolatile properly + confirmFunc("DATEDIF(NOW(),NOW(),\"d\")", expSize, false, 351); + } + + public void testDdb() { + confirmFunc("DDB(1,1,1,1,1)", 6, true, 144); + } + public void testAtan() { + confirmFunc("ATAN(1)", 2, false, 18); + } + + public void testUsdollar() { + confirmFunc("USDOLLAR(1)", 2, false, 204); + } + + public void testDBCS() { + confirmFunc("DBCS(\"abc\")", 2, false, 215); + } + public void testIsnontext() { + confirmFunc("ISNONTEXT(\"abc\")", 2, false, 190); + } +} Added: poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/function/TestReadMissingBuiltInFuncs.java URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/function/TestReadMissingBuiltInFuncs.java?rev=641185&view=auto ============================================================================== --- poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/function/TestReadMissingBuiltInFuncs.java (added) +++ poi/trunk/src/testcases/org/apache/poi/hssf/record/formula/function/TestReadMissingBuiltInFuncs.java Wed Mar 26 00:32:28 2008 @@ -0,0 +1,142 @@ +/* ==================================================================== + 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.function; + +import java.io.File; +import java.io.FileInputStream; +import java.io.IOException; +import java.io.InputStream; + +import org.apache.poi.hssf.usermodel.HSSFSheet; +import org.apache.poi.hssf.usermodel.HSSFWorkbook; + +import junit.framework.AssertionFailedError; +import junit.framework.TestCase; +/** + * Tests reading from a sample spreadsheet some built-in functions that were not properly + * registered in POI as bug #44675 (March 2008). + * + * @author Josh Micich + */ +public final class TestReadMissingBuiltInFuncs extends TestCase { + + private HSSFSheet sht; + + protected void setUp() { + String cwd = System.getProperty("HSSF.testdata.path"); + HSSFWorkbook wb; + try { + InputStream is = new FileInputStream(new File(cwd, "missingFuncs44675.xls")); + wb = new HSSFWorkbook(is); + } catch (IOException e) { + throw new RuntimeException(e); + } + sht = wb.getSheetAt(0); + } + + public void testDatedif() { + + String formula; + try { + formula = getCellFormula(0); + } catch (IllegalStateException e) { + if(e.getMessage().startsWith("Too few arguments")) { + if(e.getMessage().indexOf("AttrPtg") > 0) { + throw afe("tAttrVolatile not supported in FormulaParser.toFormulaString"); + } + throw afe("NOW() registered with 1 arg instead of 0"); + } + if(e.getMessage().startsWith("too much stuff")) { + throw afe("DATEDIF() not registered"); + } + // some other unexpected error + throw e; + } + assertEquals("DATEDIF(NOW(),NOW(),\"d\")", formula); + } + public void testDdb() { + + String formula = getCellFormula(1); + if("externalflag(1,1,1,1,1)".equals(formula)) { + throw afe("DDB() not registered"); + } + assertEquals("DDB(1,1,1,1,1)", formula); + } + public void testAtan() { + + String formula = getCellFormula(2); + if(formula.equals("ARCTAN(1)")) { + throw afe("func ix 18 registered as ARCTAN() instead of ATAN()"); + } + assertEquals("ATAN(1)", formula); + } + + public void testUsdollar() { + + String formula = getCellFormula(3); + if(formula.equals("YEN(1)")) { + throw afe("func ix 204 registered as YEN() instead of USDOLLAR()"); + } + assertEquals("USDOLLAR(1)", formula); + } + + public void testDBCS() { + + String formula; + try { + formula = getCellFormula(4); + } catch (IllegalStateException e) { + if(e.getMessage().startsWith("too much stuff")) { + throw afe("DBCS() not registered"); + } + // some other unexpected error + throw e; + } catch (NegativeArraySizeException e) { + throw afe("found err- DBCS() registered with -1 args"); + } + if(formula.equals("JIS(\"abc\")")) { + throw afe("func ix 215 registered as JIS() instead of DBCS()"); + } + assertEquals("DBCS(\"abc\")", formula); + } + public void testIsnontext() { + + String formula; + try { + formula = getCellFormula(5); + } catch (IllegalStateException e) { + if(e.getMessage().startsWith("too much stuff")) { + throw afe("ISNONTEXT() registered with wrong index"); + } + // some other unexpected error + throw e; + } + assertEquals("ISNONTEXT(\"abc\")", formula); + } + + private String getCellFormula(int rowIx) { + String result = sht.getRow(rowIx).getCell((short)0).getCellFormula(); + if (false) { + System.err.println(result); + } + return result; + } + private static AssertionFailedError afe(String msg) { + return new AssertionFailedError(msg); + } +} --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
