From 25c5daa3128b17f3ebea7927091a906e0f3e82b0 Mon Sep 17 00:00:00 2001 From: Nick Burch Date: Fri, 4 Jun 2010 14:40:01 +0000 Subject: [PATCH] Add unit test for bug #45970 - shows that we can read Formulas with URL based references in them, but we can't change them properly. Part of the unit test is disabled for now git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@951420 13f79535-47bb-0310-9956-ffa450edef68 --- .../apache/poi/hssf/usermodel/TestBugs.java | 83 ++++++++++++++++++ test-data/spreadsheet/FormulaRefs.xls | Bin 0 -> 17408 bytes 2 files changed, 83 insertions(+) create mode 100644 test-data/spreadsheet/FormulaRefs.xls diff --git a/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java b/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java index c6b47848f7..01510b087d 100644 --- a/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java +++ b/src/testcases/org/apache/poi/hssf/usermodel/TestBugs.java @@ -1588,4 +1588,87 @@ public final class TestBugs extends BaseTestBugzillaIssues { wb = writeOutAndReadBack(wb); assertEquals(2, wb.getNumberOfSheets()); } + + /** + * Problems with formula references to + * sheets via URLs + */ + public void test45970() throws Exception { + HSSFWorkbook wb = openSample("FormulaRefs.xls"); + assertEquals(3, wb.getNumberOfSheets()); + + HSSFSheet s = wb.getSheetAt(0); + HSSFRow row; + + row = s.getRow(0); + assertEquals(Cell.CELL_TYPE_NUMERIC, row.getCell(1).getCellType()); + assertEquals(112.0, row.getCell(1).getNumericCellValue()); + + row = s.getRow(1); + assertEquals(Cell.CELL_TYPE_FORMULA, row.getCell(1).getCellType()); + assertEquals("B1", row.getCell(1).getCellFormula()); + assertEquals(112.0, row.getCell(1).getNumericCellValue()); + + row = s.getRow(2); + assertEquals(Cell.CELL_TYPE_FORMULA, row.getCell(1).getCellType()); + assertEquals("Sheet1!B1", row.getCell(1).getCellFormula()); + assertEquals(112.0, row.getCell(1).getNumericCellValue()); + + row = s.getRow(3); + assertEquals(Cell.CELL_TYPE_FORMULA, row.getCell(1).getCellType()); + assertEquals("[Formulas2.xls]Sheet1!B2", row.getCell(1).getCellFormula()); + assertEquals(112.0, row.getCell(1).getNumericCellValue()); + + row = s.getRow(4); + assertEquals(Cell.CELL_TYPE_FORMULA, row.getCell(1).getCellType()); + assertEquals("'[\u0005$http://gagravarr.org/FormulaRefs.xls]Sheet1'!B1", row.getCell(1).getCellFormula()); + assertEquals(112.0, row.getCell(1).getNumericCellValue()); + + // Change 4 + row.getCell(1).setCellFormula("'[\u0005$http://gagravarr.org/FormulaRefs2.xls]Sheet1'!B2"); + row.getCell(1).setCellValue(123.0); + + // Add 5 + row = s.createRow(5); + row.createCell(1, Cell.CELL_TYPE_FORMULA); + row.getCell(1).setCellFormula("'[\u0005$http://example.com/FormulaRefs.xls]Sheet1'!B1"); + row.getCell(1).setCellValue(234.0); + + + // Re-test + wb = writeOutAndReadBack(wb); + s = wb.getSheetAt(0); + + row = s.getRow(0); + assertEquals(Cell.CELL_TYPE_NUMERIC, row.getCell(1).getCellType()); + assertEquals(112.0, row.getCell(1).getNumericCellValue()); + + row = s.getRow(1); + assertEquals(Cell.CELL_TYPE_FORMULA, row.getCell(1).getCellType()); + assertEquals("B1", row.getCell(1).getCellFormula()); + assertEquals(112.0, row.getCell(1).getNumericCellValue()); + + row = s.getRow(2); + assertEquals(Cell.CELL_TYPE_FORMULA, row.getCell(1).getCellType()); + assertEquals("Sheet1!B1", row.getCell(1).getCellFormula()); + assertEquals(112.0, row.getCell(1).getNumericCellValue()); + + row = s.getRow(3); + assertEquals(Cell.CELL_TYPE_FORMULA, row.getCell(1).getCellType()); + assertEquals("[Formulas2.xls]Sheet1!B2", row.getCell(1).getCellFormula()); + assertEquals(112.0, row.getCell(1).getNumericCellValue()); + +// TODO - Fix these so they work... +if(1==2) { + row = s.getRow(4); + assertEquals(Cell.CELL_TYPE_FORMULA, row.getCell(1).getCellType()); + assertEquals("'[\u0005$http://gagravarr.org/FormulaRefs2.xls]Sheet1'!B2", row.getCell(1).getCellFormula()); + assertEquals(123.0, row.getCell(1).getNumericCellValue()); + + row = s.getRow(5); + assertEquals(Cell.CELL_TYPE_FORMULA, row.getCell(1).getCellType()); + assertEquals("'[\u0005$http://example.com/FormulaRefs.xls]Sheet1'!B1", row.getCell(1).getCellFormula()); + assertEquals(234.0, row.getCell(1).getNumericCellValue()); +} + } } diff --git a/test-data/spreadsheet/FormulaRefs.xls b/test-data/spreadsheet/FormulaRefs.xls new file mode 100644 index 0000000000000000000000000000000000000000..72da5473eb411381a610398cc30f5129f45e3abf GIT binary patch literal 17408 zcmeHOdvH|M8UOBPlWYJA^g0x0Mh9wRuua4>`oq{M_Si-j2}vDKMQ zrH+D+DWtX5I%CzM;(N3;`iD-(*mhd2?^ZkN*xIR;+G%x^?eBNa-ral8-g~(_+D>se znREBv@1FDfzVm(G`OYKf{^r-UZ{7aDsJF#POQcMWWvisZLAP+vtWu507~JsrST>v0 zL=oJcOec^AK8LJv7_SUDid>FNn^z*okgJdfAP+shdhQ%uvRu~5PPqbqt*Y(kLJRFmCQ>ref%~oi&?(^07|8xZ83KyBUcHigDwngs zp-st>O>>MNEDaUH6z_o=c^s_SAE zlN#jEifr9Hl;AjZQKVUw`<}XvQ`f<`TJor>^O~Z}k~(<<`DqzgX(w zrY=Shz~H4R1$IqM1%|U_y3BAev}@=1+f^0YLcO=tyhr4pY957-pi*C)bk3#}{o@>2 zPSU@KepuI%xQ5_0V(9as+^gtqZvE%!Kl7pA?L$B8LqFt0zt4w$uMhnPKJ@E+=zj9} z;XkNwI4f6vnAgJ(edt=A;__OaV){>fa31iXKjcIIg%AC^KJ>?Z=%vc@u)^W$#;qUi zCtT$y`c1X9w+_5@phRy`<+*VgaaexPnylyqx#s^p>Od*4B;~I0AY;sOVg2+W1<}TL0x3e-+)(Ggq#P zZpb;zroV`(oGi)ffh$+D{8h1GaupSnPfKLMA0K&QPpm=qxacep!HRg8n*DRSsKyeE z4}%f;BgGZ;jIx9ZdSX{X1ujEM5{YYpk}7aWE2#o)Qc?vvZAleql#(jY%}c7l{#8;1 z29%O2uxFH1fnBep3hYZIRbcQ7tgzJR5A5}2{{11UI!daYF2@;O9BEI?1Yu|m0YW$Q z2VuVs0YcaG2Vt-c0YZ262Vvw10YVq{2jQ?A0)%eu55ir!|zQY&U=YEwfSm#D#@S2Jem1^)}*-!U~H^|YWNAn<+IpDo} z_hvbmF~vM+RSp`kRT|(y+Y>W|nufAkC{FB{JX?0>op%-q)zs8fB$Sh83q_iS=9ZP# zl#^CGi=B?*f%5`$o?KsQg@#fqG?rQc+dR28(AqShOR>e%66|2*0van|72&R6``EJ| zI(^KX+sw*?(45RJuJj!egM)k`3`n*q7$g}6#2ju+yUqqk!4Zm@Q|!)jP@&MrnwqZk zR${jQ`$g{j<@GVsD0B0w@a*_@htBp`d7+Bk<4hMwZ_39#vLEz1K+N8b%}YmbFIrF9 zK9?xAKzWPLoO9S#!{135B z^nVa_n6dEx`pRgk{q=W0%9itwm|a&8a3E4^JB(z{crm{Vnf3{+KtLktQ1tPn#UT#_4JOEib?Gq&J(>Og0{#Ha&oi z)6@D}`ukq2ck$m;mo4wi8nQS~fZAt(er>7;7 z&;0(NH=D608xK#b4`Ac;v_$gA?az6$iJNRZJZ)wG8>gowk_TSMMQGDDXP9g}JZ*LW z8>gowlAre7;MJNzG7i%f$&Z^$jd62Kw$gX36j^ii&%Hs%7eL`rCg>RksMCw^uaC8> zyg?@vK;b_o=tKk5=`$jSp19Z>bYcM%-eQ7IH9(ynBGR+@$KIfm3ZU=}6STnqb^3$I zdG|i+4LZ313QsUWXBnVQ>x=yBmK9#0F`2??*|OHn`(yLnmbXS{%VZfAZkzKXa5-mR z*c9OGi1*w%$VHMGoISuE{_@t&ww;}=nU3zR{jsavfRPc|fufHF@%UCJpbN*vD%DJa zpqv=n47jVH7*ya4C0}w?V?hnhr=dJH1x_0m%EskVj0BglimKDl6Gfd#MxAIjhB`ZO zE-S6(zHPbFi{#SSQ;8alMvcnsprRUXUc+SuPqv=DYkOyFS8Jv_y*s`>wJWnfw$cq1 znUk%-p~+o197o%CDf{y<<8J7DJ;d1sf8a93wmyxWbFV724GZ*@-Q951g>GDHR5mIr zp|ozpRk^odV}Y5CH5li-us?QAs&#Wm*OoZe3WlI%HPGxi5Qx6t0YSGwK)ps#E`Sa* z_S`)hj;>e<$#ft*5E+a`h65b2&jhda0N3%)2Rz$!SKlAd5P~P2;D~`H_z(|p9S42D z_0%Auz-K$b5etogP&U*9obgawtkS8L1CaqTho^bY?b^N*uCUW>vdA#BhbKq3!xw4v zLPz6XhHW%%cznU`UxNt};!;aT*S6H=T>n4M5N0IeQj0S5HW+=gD*}||y)hq)?C8S{ zjtVGlkemtmWp!$2Cf$ngVgzREbOh$r0A@?m@F@lsy`oX^6gwPtdwa1Bl5=>tRF9xL zCf%}H0+y@c>1lNUmryzFTevMOR;zHZ3lOWjGpYTtO>T_H0Q47PWFQm|uyfU;M8Q6Z z9n!eRQ3$m9$qs4y$qs4y=|E(l%opsUui2SV7XOC9bU2{bC><2r+!^v2Azd~np&*HV zvL(?^wj}zAp=?1!sqj$hy5c*lDC;OZ96G9Jdkc66-hVXFbZSU#=jPG5o9$c{wg zuFf6sO)Y|bldx}+7b^gfOHj$OEP$!Bq4}^i01tSKGat5A0vG>*vC1cG&1AMOm@#8Z z>y~uum96P?V|RMXj2yr7Q|&uS;;5Sjo*SAe3Lo_^kGzgj6bhupQrqc}gXw82hKvu3 zKCt979OQ);tBjVN)sarMWiazu*Lp=Nu3C<9N&UtlAXK=bqcg=@#I6g@Z{b}QGo_f^ ziGL2j*`zHt0-0=d8j<^r$Sp9!Esi(K-nHZqXonm$3cC-qeGVD{crrwvp|5eBp&u9- z#@8yb8JFN62v1_rJp~<(0V)Fbt5)~5xbjpC?6r~4c z>@*Q!L8RWg8gHJClGkwLZ>!SRsOuz@Zdd8Orpb=`DS4Flo2EMMZ^z1sh;$GOMxqvy zD*dj>{*`sTn1l5e#6S&qTBKZ`v)TA%vxv25^U=}g5Dz1%u6m^2fw|}WwQvNArsN5 zh$#73TVkim7Dq9PhE#@z`+K>zoz02XKXE*q4YWPm{_V3$^>EvxZ5`lzA+4Yun+rr( zz{d)@jP`SZ1-`Hqp1~~k#I^#~;w8qJ|E(1qW<9}g>2?fgj41prlW~~=1f!@L8%SgC z=kLk%)ziQ+OrOiuKf$*WPyYlQmmw?zu50(Ak_dNWH`nHsQTuKURf8?bXUg~WeU31`8w7>q7!)*U6$e4Jl_OHb+ z2j`2Bc`$M_G7tWCB2(rJGTVI>GH09DAX6XvklFT!k)8UWE}3*21}Q8)6O+mOtpgL5 zr9Jdopm3Wg+$LoZ3X&0(PLK<`)7y4%{jx*VFI8ti$R`Vtp$q|nJ;VI=cM?h!?6#)8 zy`wFq%@g`Fft+$(t`(16FR&bvt%5 iHp$mYM&}o}&Zrm$fc+;OreMf2rScC68@kwu%>O^FYlc+- literal 0 HcmV?d00001 -- 2.39.5