From 1f218ff691efd6b1c0bbb2a0085287105b96f2fb Mon Sep 17 00:00:00 2001 From: Nick Burch Date: Tue, 18 Sep 2007 15:01:15 +0000 Subject: [PATCH] Fix for cell references on rows > 32768 (bug #43399) git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@576939 13f79535-47bb-0310-9956-ffa450edef68 --- src/documentation/content/xdocs/changes.xml | 1 + src/documentation/content/xdocs/status.xml | 1 + .../poi/hssf/record/formula/ReferencePtg.java | 40 ++++++++- .../org/apache/poi/hssf/data/ReferencePtg.xls | Bin 0 -> 17408 bytes .../hssf/record/formula/TestReferencePtg.java | 77 ++++++++++++++++++ 5 files changed, 118 insertions(+), 1 deletion(-) create mode 100644 src/testcases/org/apache/poi/hssf/data/ReferencePtg.xls create mode 100644 src/testcases/org/apache/poi/hssf/record/formula/TestReferencePtg.java diff --git a/src/documentation/content/xdocs/changes.xml b/src/documentation/content/xdocs/changes.xml index 0f17e7cc31..bac5f0a167 100644 --- a/src/documentation/content/xdocs/changes.xml +++ b/src/documentation/content/xdocs/changes.xml @@ -36,6 +36,7 @@ + 43399 - [PATCH] - Fix for Cell References for rows > 32678 43410 - [PATCH] - Improved Formula Parser support for numbers and ranges When writing HSLF files out, optionally preserve all OLE2 nodes (default is just the HSLF related nodes) 43323 - [PATCH] - Support for adding Pictures to ShapeGroups in HSLF. diff --git a/src/documentation/content/xdocs/status.xml b/src/documentation/content/xdocs/status.xml index 9a3a8d6fa7..9ab70b7fd0 100644 --- a/src/documentation/content/xdocs/status.xml +++ b/src/documentation/content/xdocs/status.xml @@ -33,6 +33,7 @@ + 43399 - [PATCH] - Fix for Cell References for rows > 32678 43410 - [PATCH] - Improved Formula Parser support for numbers and ranges When writing HSLF files out, optionally preserve all OLE2 nodes (default is just the HSLF related nodes) 43323 - [PATCH] - Support for adding Pictures to ShapeGroups in HSLF. diff --git a/src/java/org/apache/poi/hssf/record/formula/ReferencePtg.java b/src/java/org/apache/poi/hssf/record/formula/ReferencePtg.java index 7e8758d6f2..df3e5a70bc 100644 --- a/src/java/org/apache/poi/hssf/record/formula/ReferencePtg.java +++ b/src/java/org/apache/poi/hssf/record/formula/ReferencePtg.java @@ -35,8 +35,18 @@ public class ReferencePtg extends Ptg { private final static int SIZE = 5; public final static byte sid = 0x24; + private final static int MAX_ROW_NUMBER = 65536; //public final static byte sid = 0x44; + + /** + * The row number, between 0 and 65535, but stored as a signed + * short between -32767 and 32768. + * Take care about which version you fetch back! + */ private short field_1_row; + /** + * The column number, between 0 and ?? + */ private short field_2_col; private BitField rowRelative = BitFieldFactory.getInstance(0x8000); private BitField colRelative = BitFieldFactory.getInstance(0x4000); @@ -93,6 +103,7 @@ public class ReferencePtg extends Ptg public void writeBytes(byte [] array, int offset) { array[offset] = (byte) (sid + ptgClass); + LittleEndian.putShort(array,offset+1,field_1_row); LittleEndian.putShort(array,offset+3,field_2_col); } @@ -101,11 +112,38 @@ public class ReferencePtg extends Ptg { field_1_row = row; } + public void setRow(int row) + { + if(row < 0 || row >= MAX_ROW_NUMBER) { + throw new IllegalArgumentException("The row number, when specified as an integer, must be between 0 and " + MAX_ROW_NUMBER); + } + + // Save, wrapping as needed + if(row > Short.MAX_VALUE) { + field_1_row = (short)(row - MAX_ROW_NUMBER); + } else { + field_1_row = (short)row; + } + } + /** + * Returns the row number as a short, which will be + * wrapped (negative) for values between 32769 and 65535 + */ public short getRow() { return field_1_row; } + /** + * Returns the row number as an int, between 0 and 65535 + */ + public int getRowAsInt() + { + if(field_1_row < 0) { + return field_1_row + MAX_ROW_NUMBER; + } + return field_1_row; + } public boolean isRowRelative() { @@ -153,7 +191,7 @@ public class ReferencePtg extends Ptg public String toFormulaString(Workbook book) { //TODO -- should we store a cellreference instance in this ptg?? but .. memory is an issue, i believe! - return (new CellReference(getRow(),getColumn(),!isRowRelative(),!isColRelative())).toString(); + return (new CellReference(getRowAsInt(),getColumn(),!isRowRelative(),!isColRelative())).toString(); } public byte getDefaultOperandClass() { diff --git a/src/testcases/org/apache/poi/hssf/data/ReferencePtg.xls b/src/testcases/org/apache/poi/hssf/data/ReferencePtg.xls new file mode 100644 index 0000000000000000000000000000000000000000..d415034d1ab5bfc3666904de3b2beff0536ffbdd GIT binary patch literal 17408 zcmeI)U92PLSpeYAXHGV|-8R{ccM}@7>1ih2?yk3Ok}34Zw7ZF0fn*9%3?h=LmekEs zuvAdoULXs4sHs%RAR#P8Ix;tvZvadD$WnV@F1GSTK;8<3FDhXHDZca-GDsCpW1g9_ z&Yn%Ol!7YM;PF25^UgQ(&WwHDImh~D|L$*3zx1`geDFKrHN(e46kb`M2*-}ZbLQW* zSw0o4U$4`}&2-Pa(02F{x&kMTdn1qC6~6f1Z{7Vj`(}4S_)gdff3f>b;}HIZUkbkw z-V(yk{%XCk_v9C!uYYduhd(@-9mBJ^^cb%D;C(E2Eab$Z@JGh?gtuo$cV9NnXX8K5 z+Wu8G{#dxrb|L)qY{%ZfpOca49@~G{xbu9?6m(%BzVkU%r*3mM@qgQ_`mf{Z=kSf%-zlE%^^MxUnd7;z zm!96uVRJo;hu7h|uZ>^0L;TVm;ur4_KkvVd0|5!G? zZ+|zN=J)T;reQy1(=+>bt$$fv$b~;t0}pG6vL3P<-px^Z_sb$?_R};sNBgGx>4KT- z*RPxGhl7K4I7qI!Ki&v8w7=%H77y1f-$-{Tar#PUv;Adz$o7$Du~D|&4co8R2jRf- z`oOmKyS{M{l56Xb+_)jGb?Sg?c7N?4yzs&cL4%MS2qgY-kP%A`!iz7y7`~#W{#tgk zKNhN=d+xa}JeEej`VmK?`ec#E)5t%5^hhN6y|C*F<-(ukc2(xw?gLp3eObZT3~Q0& z>D?n7-f2$VcBeVHbH?rGaM;;DoKDoT{EvlDAf`w{i82+c)Tq;-NsBfey7cHXV91Cu z6Q<0VvtY@J=b+H~mBqtAdLBgRaaGGoqyB`cx_h(I!ju_v7A#p2y-R-b6o@I3P@+tQDmCgfXwsrhhb}$(3>Y$E z%!Da3<}6sUBHELmJOyHkB$Oyqp-PQ94VtuQ)1ga`J_Ck~7&BqYj5!OItccz%KY0qo z6iFyirb3k(bs98j(WXO}9(@K388K$Ulo@jtELjmfEI)Y)#1u&=QKmwb8g&{pY0;)b zmmYlv3>h(I!ju_v7A#p2#qyJsZpmvlNN0{bm`G&z>pDRCQO+zXTg#c z(IfJcr$9`Rgc4;cRH;#?L6a73I&|sLXTXpVVb+H~mBqtAdLBgRaaGGoqyB`czH@{^}POp$~VWhzvuQKvzZ7Hv9o>CtDvkP%}h zOqnrf!IBk`-{((~r$9`Rgc4;cRH;#?L6a73I&|sLXTXpVV{E)M?P9MVk&? zdh{7EWW<;WQ)bLruw+GaQGW6ih$)g#qD+MoCQl(M3>|zPl1>s2_?!@s8XX& zgC;H7bm-Ef&wwE##!Q$pW6pvlE28(wPo4rXMG{JssZgaxod!)>wCT{LN1p*hMvR#- zWyYKZOIAehm!CWZVu~b`C{v+IjXDjQv}n_zOOHMShKv|9Vakj-3zn>i{E>Z#lb<{VVu~b`C{v+IjXDi{Jrr8B>CmM|p8-QgjF~WH#+(I9 zRz#1>Po4rXMG{JssZgaxod!)>wCT{LN1p*hMvR#-WyYKZOIAewDm+P^0x?C>zZgrD zsZgaxod!)>wCT{LN1p*hMvR#-WyYKZOIAc5l%G5WVu~b`C{v+IjXDjQv}n_zOOHMS zhKv|9Vakj-3zn>ieq4U?6o@I3P@+tQDmCgfXwsrhhb}$(3>Y$E%!Da3<}6sUBKisW z$x|SvNJ5D+6{^&z)1XOCtDvkP%}hOqnrf!IBkU`EUq7_mL3Z=39eYc&qVe!@b%3>1=$;cL^{5$-n;n zr#|!4<*QlbqOoJl#$V1N`$xWgc+bwRwd?RZ)_imKjD5W|gmXW0u6(Zc^G`ha>2pt? z4QJD@iqp_j&;H7Dd*^CTJ@HeYw4$Bu@prTFd3zlEX*&GJw(W))*0cIFKs#_Twnk5mGnIi zX=enDuBVOD9h}vlVJB<6ezkE3FI~ynrj0eqdM}&b`|B%F)IeIg{fdJ(DwheCNLK^<&?45w2KD zyLhC-J9R>d(mkXh)BRj7?c;HWr(@DBvSG9NAAj`C+dIZHim&M%vpM@)KTO$0&B9lV zH&-RQsE3U=p&#U;ey_B>s9*o@Uev?OydA$euE5jb*S)?F_QEH_=S`mvzxe~5n5UL= zo5$TZKYHJ92b=#dAZ_=HvOgah;WOEBG``=auUqd4@K^Jv$ML!bcL+TGlXqD0|16xo zXFEiszqa1RG4AF#CrC@_NzxvSZzJuwcrR&x#nb3O8SF-_7{Q&&9eV9BK;hEZ}Z{VPXAA?z<&Z}fRTs* literal 0 HcmV?d00001 diff --git a/src/testcases/org/apache/poi/hssf/record/formula/TestReferencePtg.java b/src/testcases/org/apache/poi/hssf/record/formula/TestReferencePtg.java new file mode 100644 index 0000000000..226b14400d --- /dev/null +++ b/src/testcases/org/apache/poi/hssf/record/formula/TestReferencePtg.java @@ -0,0 +1,77 @@ + +/* ==================================================================== + 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; + +import org.apache.poi.hssf.usermodel.HSSFSheet; +import org.apache.poi.hssf.usermodel.HSSFWorkbook; + +/** + * Tests for {@link ReferencePtg}. + */ +public class TestReferencePtg extends AbstractPtgTestCase +{ + /** + * Tests reading a file containing this ptg. + */ + public void testReading() throws Exception + { + HSSFWorkbook workbook = loadWorkbook("ReferencePtg.xls"); + HSSFSheet sheet = workbook.getSheetAt(0); + + // First row + assertEquals("Wrong numeric value for original number", 55.0, + sheet.getRow(0).getCell((short) 0).getNumericCellValue(), 0.0); + assertEquals("Wrong numeric value for referemce", 55.0, + sheet.getRow(0).getCell((short) 1).getNumericCellValue(), 0.0); + assertEquals("Wrong formula string for reference", "A1", + sheet.getRow(0).getCell((short) 1).getCellFormula()); + + // Now moving over the 2**15 boundary + // (Remember that excel row (n) is poi row (n-1) + assertEquals("Wrong numeric value for original number", 32767.0, + sheet.getRow(32766).getCell((short) 0).getNumericCellValue(), 0.0); + assertEquals("Wrong numeric value for referemce", 32767.0, + sheet.getRow(32766).getCell((short) 1).getNumericCellValue(), 0.0); + assertEquals("Wrong formula string for reference", "A32767", + sheet.getRow(32766).getCell((short) 1).getCellFormula()); + + assertEquals("Wrong numeric value for original number", 32768.0, + sheet.getRow(32767).getCell((short) 0).getNumericCellValue(), 0.0); + assertEquals("Wrong numeric value for referemce", 32768.0, + sheet.getRow(32767).getCell((short) 1).getNumericCellValue(), 0.0); + assertEquals("Wrong formula string for reference", "A32768", + sheet.getRow(32767).getCell((short) 1).getCellFormula()); + + assertEquals("Wrong numeric value for original number", 32769.0, + sheet.getRow(32768).getCell((short) 0).getNumericCellValue(), 0.0); + assertEquals("Wrong numeric value for referemce", 32769.0, + sheet.getRow(32768).getCell((short) 1).getNumericCellValue(), 0.0); + assertEquals("Wrong formula string for reference", "A32769", + sheet.getRow(32768).getCell((short) 1).getCellFormula()); + + assertEquals("Wrong numeric value for original number", 32770.0, + sheet.getRow(32769).getCell((short) 0).getNumericCellValue(), 0.0); + assertEquals("Wrong numeric value for referemce", 32770.0, + sheet.getRow(32769).getCell((short) 1).getNumericCellValue(), 0.0); + assertEquals("Wrong formula string for reference", "A32770", + sheet.getRow(32769).getCell((short) 1).getCellFormula()); + } +} + + -- 2.39.5