From 80967dc82a416da1938cc5c98288893f5f151657 Mon Sep 17 00:00:00 2001 From: Josh Micich Date: Tue, 1 Apr 2008 22:18:25 +0000 Subject: [PATCH] Bug #44733 - DPRODUCT function should be index 189, not 191 git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@643625 13f79535-47bb-0310-9956-ffa450edef68 --- .../formula/function/functionMetadata.txt | 2 +- .../poi/hssf/data/missingFuncs44675.xls | Bin 16384 -> 19456 bytes .../hssf/record/formula/AllFormulaTests.java | 3 +- .../TestParseMissingBuiltInFuncs.java | 13 ++++- .../function/TestReadMissingBuiltInFuncs.java | 54 ++++++++++++++---- 5 files changed, 56 insertions(+), 16 deletions(-) diff --git a/src/resources/main/org/apache/poi/hssf/record/formula/function/functionMetadata.txt b/src/resources/main/org/apache/poi/hssf/record/formula/function/functionMetadata.txt index 237fbf094f..590b455c47 100644 --- a/src/resources/main/org/apache/poi/hssf/record/formula/function/functionMetadata.txt +++ b/src/resources/main/org/apache/poi/hssf/record/formula/function/functionMetadata.txt @@ -143,8 +143,8 @@ 169 COUNTA 0 30 V R 183 PRODUCT 0 30 V R 184 FACT 1 1 V V +189 DPRODUCT 3 3 V R R R 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 diff --git a/src/testcases/org/apache/poi/hssf/data/missingFuncs44675.xls b/src/testcases/org/apache/poi/hssf/data/missingFuncs44675.xls index 71f874370ffe246151e5ed0b07a5a6f3b13d641b..4a68472450d05e10e9947ac06d505f2944726e8a 100644 GIT binary patch delta 4165 zcma)9U2I!L7Cv)*ZC^VH&W}mlB}r{s#9!JvO-l{UCU)CJfp$w!DPvuQ z1IOGQE(9ui&Ry+NR6n+9NBMul!wMXw^zYrCZlYOczaN?ydG;d}K|3GQhuzy-X^@I* zDk3fDTo}>|n{R6~a%8Yje`FPyGolv&q<3xrm|Ka?N2n|oQmIhjB0BVoHHyB68bghv z8h;0z;cKo!rx&#kwI7wr(x@5Kt5IoqbvcvUuk>tqtKK?1m?7kQR2E0>kZCE(IJ_&$ ze#!EI$2Y2!E({MH%ejfe95jluqKMMXDo&=(x~@v1zg;70VY=k#u(LN4^oP_m&^3S9 zxBj@8A_wT<5=a$>l7j}(1fe#cWmvz6(B|;3IQ&pAL?dQg77KoZdP)J+)_MO5PcW|MxH7cFEsf`R?Ex zYC8MF!3$jvo}^=IAWaLw;8OwfxwJm+$TlYH7+IiSRJ<`I!4D}qmEYUD8=`H%>bH4$3RoGz|A?((| zj$OZlnK-Hk2^)zdS}Z3`k}1czde@Wt4;1%_{L~p=*E|x7r>YbTP(4qV$_Zx3PcZk{ z>95eTILEjcb4a7J%5MdOO&=Mj1>>yMVDZR*!jgu;Ru@|14Zd(3G%NIob|!kWx$kd#E$vcv1PE7G}w{~RlT0@!4u1XDcNZF zkdHmI43?5XY$@p+^}$D%0aG#v4o=&g&$~R-b7p;AjvbdU*MAqb%;xiC!Q)a(KLAjy z8{z8z;;gn@n+5fhv}s!3=(w+JqA0ggo>hgGzsflf?e0?BJg)e;FCY!IzI_n!Ho`WGn{WxMwB0^*OJ_ z$ac5#=ra3+BXKUX1UtKw0$RVHPh?7@*gh1MVrzcYxe7c`uT$9*N`|Hn4CCEfyl;8FfI z8Z7R=(q|=!qj)j>g(mbPP3SK*p}!fVBT0S3ZwDCx`l%-Le>9=L(}cd1L%TTQ29E-U z|7{dSFN?(MhK>xfk$bkI<9N&Q7IB|5^qAhBI9TFs&cwBLC6jr><#n<#-me-u_g_Q* z0t?pM_pCfGH32%{mVQN@o<cGy)eMLAmHaYV|asPq4r;5`LAVj-oip8bYAC%VZVGUfBp+JA9cZ{L_L{Q>$MJRimqc#E))ZAN-q>wTBoPqi-&BX7rM zk6zEgR)gY6+U{;DA*8Xhm_`c?v~3XN6{Ud)8uz=!G#bIzm zCF+<@lGVIz(pJbHU&xr^MA8)Urv#0)0k&6d@j6nswbBfbQu8lf>D!7)Pl)%`!f9Be z1$Jl)F#?$Ja2ss#hD#;MynWkYxxD-ZZ83MuM4!0sEBpZq^(wH6-5-?^`j^*+!RIVCUGGs z0gbt!f*`sQKVT&y2%-T&T2R3%ir9sVDk!2ZbmeEfcQUE9RPd6#ci%bp_vYRAvgbrL zEApLj0gm_p!ld&0mt|QMxagNAMM0891YMr}8dFg=DGDhk3i4(l;TA;?Ru5gv`A<%X z{PtwaW}2JGCM)5DGU$;>B`;g>sNAAR1dJzf!aeBWY*h|V+zNGy7A%G8{#sG-;+Z`S z4)xXM0W_5=R$Oo;_Sij*wV}&c3N@=)F#@wa$J_8jC?#evZoFUyP0YAOKa9db z7=v**Kw1)_&?W?|gS%X&1T%QCu^b~v)h>YZMyT2kdENnA(Gouznw>3#>2O8+(OA}k zI1;Y0*&uov%UTGdj$IH}+t_5-$%We-8_uf!DTcFZ^I3~C;We_+KqY*$;sCcuVUaJy z-#|Sy;Hj9t1e*wL&D4Kop4)Z%b+hw$Jj8td>rjS7CI zF@UkeDjZ7$loUK1EPNy^wJv^$$iZ?m-zOe{TT;2AmgMTrP6cAX+p>+1-H_Orwr**lSHbAm zp5B*at}sLkHs<|$x!RrGwlO(d+U|<=J$MN|I85|j=z?~L)4v^hqyXHuk0wptEZtO4 zjyfT?pc4SrOA(6AU!H_wP;0yzYQ=8-#OXFQ!Z^R3Tmc7jJ8O++dShP;EH&IwU%W{0 s>sdvwU5bDvoUNZLxcjlcI_ctUn_!gQTnoRZgxVkwvwnAS_b0i>rgl>h($ diff --git a/src/testcases/org/apache/poi/hssf/record/formula/AllFormulaTests.java b/src/testcases/org/apache/poi/hssf/record/formula/AllFormulaTests.java index 645709bb6e..3a70450223 100644 --- a/src/testcases/org/apache/poi/hssf/record/formula/AllFormulaTests.java +++ b/src/testcases/org/apache/poi/hssf/record/formula/AllFormulaTests.java @@ -29,7 +29,7 @@ import junit.framework.TestSuite; * * @author Josh Micich */ -public class AllFormulaTests { +public final class AllFormulaTests { public static Test suite() { TestSuite result = new TestSuite(AllFormulaTests.class.getName()); @@ -50,7 +50,6 @@ public class AllFormulaTests { result.addTestSuite(TestReferencePtg.class); result.addTestSuite(TestSheetNameFormatter.class); result.addTestSuite(TestUnionPtg.class); - result.addTest(AllFormulaFunctionTests.suite()); return result; } } diff --git a/src/testcases/org/apache/poi/hssf/record/formula/function/TestParseMissingBuiltInFuncs.java b/src/testcases/org/apache/poi/hssf/record/formula/function/TestParseMissingBuiltInFuncs.java index 3671d37c1a..fdad5f44d8 100644 --- a/src/testcases/org/apache/poi/hssf/record/formula/function/TestParseMissingBuiltInFuncs.java +++ b/src/testcases/org/apache/poi/hssf/record/formula/function/TestParseMissingBuiltInFuncs.java @@ -27,6 +27,9 @@ import org.apache.poi.hssf.record.formula.FuncPtg; import org.apache.poi.hssf.record.formula.FuncVarPtg; import org.apache.poi.hssf.record.formula.Ptg; /** + * Tests parsing of some built-in functions that were not properly + * registered in POI as bug #44675, #44733 (March/April 2008). + * * @author Josh Micich */ public final class TestParseMissingBuiltInFuncs extends TestCase { @@ -39,6 +42,7 @@ public final class TestParseMissingBuiltInFuncs extends TestCase { Ptg[] ptgs = parse(formula); Ptg ptgF = ptgs[ptgs.length-1]; // func is last RPN token in all these formulas + // Check critical things in the Ptg array encoding. if(!(ptgF instanceof AbstractFunctionPtg)) { throw new RuntimeException("function token missing"); } @@ -47,11 +51,15 @@ public final class TestParseMissingBuiltInFuncs extends TestCase { 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()); + + // check that parsed Ptg array converts back to formula text OK + Workbook book = Workbook.createWorkbook(); + String reRenderedFormula = FormulaParser.toFormulaString(book, ptgs); + assertEquals(formula, reRenderedFormula); } public void testDatedif() { @@ -76,4 +84,7 @@ public final class TestParseMissingBuiltInFuncs extends TestCase { public void testIsnontext() { confirmFunc("ISNONTEXT(\"abc\")", 2, false, 190); } + public void testDproduct() { + confirmFunc("DPRODUCT(C1:E5,\"HarvestYield\",G1:H2)", 4, false, 189); + } } diff --git a/src/testcases/org/apache/poi/hssf/record/formula/function/TestReadMissingBuiltInFuncs.java b/src/testcases/org/apache/poi/hssf/record/formula/function/TestReadMissingBuiltInFuncs.java index f1e6bcfacf..14799bd0a7 100644 --- a/src/testcases/org/apache/poi/hssf/record/formula/function/TestReadMissingBuiltInFuncs.java +++ b/src/testcases/org/apache/poi/hssf/record/formula/function/TestReadMissingBuiltInFuncs.java @@ -21,7 +21,9 @@ import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; +import java.lang.reflect.InvocationTargetException; +import org.apache.poi.hssf.record.RecordFormatException; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; @@ -29,24 +31,31 @@ 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). + * registered in POI as bug #44675, #44733 (March/April 2008). * * @author Josh Micich */ public final class TestReadMissingBuiltInFuncs extends TestCase { - private HSSFSheet sht; + /** + * This spreadsheet has examples of calls to the interesting built-in functions in cells A1:A7 + */ + private static final String SAMPLE_SPREADSHEET_FILE_NAME = "missingFuncs44675.xls"; + private static HSSFSheet _sheet; - 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); + private static HSSFSheet getSheet() { + if (_sheet == null) { + String cwd = System.getProperty("HSSF.testdata.path"); + HSSFWorkbook wb; + try { + InputStream is = new FileInputStream(new File(cwd, SAMPLE_SPREADSHEET_FILE_NAME)); + wb = new HSSFWorkbook(is); + } catch (IOException e) { + throw new RuntimeException(e); + } + _sheet = wb.getSheetAt(0); } - sht = wb.getSheetAt(0); + return _sheet; } public void testDatedif() { @@ -128,9 +137,30 @@ public final class TestReadMissingBuiltInFuncs extends TestCase { } assertEquals("ISNONTEXT(\"abc\")", formula); } + public void testDproduct() { + + String formula = getCellFormula(6); + assertEquals("DPRODUCT(C1:E5,\"HarvestYield\",G1:H2)", formula); + } private String getCellFormula(int rowIx) { - String result = sht.getRow(rowIx).getCell((short)0).getCellFormula(); + HSSFSheet sheet; + try { + sheet = getSheet(); + } catch (RecordFormatException e) { + if(e.getCause() instanceof InvocationTargetException) { + InvocationTargetException ite = (InvocationTargetException) e.getCause(); + if(ite.getTargetException() instanceof RuntimeException) { + RuntimeException re = (RuntimeException) ite.getTargetException(); + if(re.getMessage().equals("Invalid built-in function index (189)")) { + throw afe("DPRODUCT() registered with wrong index"); + } + } + } + // some other unexpected error + throw e; + } + String result = sheet.getRow(rowIx).getCell((short)0).getCellFormula(); if (false) { System.err.println(result); } -- 2.39.5