From 9f5eca6190ba9dd873ddbcedd6d0c5d9153d67f3 Mon Sep 17 00:00:00 2001 From: Nick Burch Date: Fri, 15 Feb 2008 13:50:38 +0000 Subject: [PATCH] Further support for whole-column references, including formula strings and the evaluator. Also has some new tests for it git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@628065 13f79535-47bb-0310-9956-ffa450edef68 --- src/documentation/content/xdocs/changes.xml | 2 +- src/documentation/content/xdocs/status.xml | 2 +- .../poi/hssf/record/formula/Area3DPtg.java | 19 +++--- .../apache/poi/hssf/record/formula/AreaI.java | 60 ++++++++++++++++++ .../poi/hssf/record/formula/AreaPtg.java | 20 ++++-- .../apache/poi/hssf/util/AreaReference.java | 46 ++++++++++++++ .../apache/poi/hssf/util/CellReference.java | 6 +- .../poi/hssf/usermodel/TestBug44410.java | 32 ++++++---- .../poi/hssf/data/SingleLetterRanges.xls | Bin 13824 -> 13824 bytes 9 files changed, 154 insertions(+), 33 deletions(-) create mode 100644 src/java/org/apache/poi/hssf/record/formula/AreaI.java diff --git a/src/documentation/content/xdocs/changes.xml b/src/documentation/content/xdocs/changes.xml index fc411e2cf7..7ae55cbb0a 100644 --- a/src/documentation/content/xdocs/changes.xml +++ b/src/documentation/content/xdocs/changes.xml @@ -36,7 +36,7 @@ - 44410 - Partial support for whole-column ranges, such as C:C, in the formula evaluator + 44410 - Support for whole-column ranges, such as C:C, in formula strings and the formula evaluator 44421 - Update Match function to properly support Area references 44417 - Improved handling of references for the need to quote the sheet name for some formulas, but not when fetching a sheet by name 44413 - Fix for circular references in INDEX, OFFSET, VLOOKUP formulas, where a cell is actually allowed to reference itself diff --git a/src/documentation/content/xdocs/status.xml b/src/documentation/content/xdocs/status.xml index e024f8dc46..2d0943a91f 100644 --- a/src/documentation/content/xdocs/status.xml +++ b/src/documentation/content/xdocs/status.xml @@ -33,7 +33,7 @@ - 44410 - Partial support for whole-column ranges, such as C:C, in the formula evaluator + 44410 - Support for whole-column ranges, such as C:C, in formula strings and the formula evaluator 44421 - Update Match function to properly support Area references 44417 - Improved handling of references for the need to quote the sheet name for some formulas, but not when fetching a sheet by name 44413 - Fix for circular references in INDEX, OFFSET, VLOOKUP formulas, where a cell is actually allowed to reference itself diff --git a/src/java/org/apache/poi/hssf/record/formula/Area3DPtg.java b/src/java/org/apache/poi/hssf/record/formula/Area3DPtg.java index 8dd25c7662..91417994e4 100644 --- a/src/java/org/apache/poi/hssf/record/formula/Area3DPtg.java +++ b/src/java/org/apache/poi/hssf/record/formula/Area3DPtg.java @@ -17,15 +17,13 @@ package org.apache.poi.hssf.record.formula; -import org.apache.poi.util.LittleEndian; -import org.apache.poi.hssf.util.AreaReference; -import org.apache.poi.hssf.util.CellReference; -import org.apache.poi.hssf.util.SheetReferences; - import org.apache.poi.hssf.model.Workbook; import org.apache.poi.hssf.record.RecordInputStream; +import org.apache.poi.hssf.util.AreaReference; +import org.apache.poi.hssf.util.CellReference; import org.apache.poi.util.BitField; import org.apache.poi.util.BitFieldFactory; +import org.apache.poi.util.LittleEndian; /** @@ -38,7 +36,7 @@ import org.apache.poi.util.BitFieldFactory; * @version 1.0-pre */ -public class Area3DPtg extends Ptg +public class Area3DPtg extends Ptg implements AreaI { public final static byte sid = 0x3b; private final static int SIZE = 11; // 10 + 1 for Ptg @@ -263,15 +261,18 @@ public class Area3DPtg extends Ptg */ public String toFormulaString(Workbook book) { + // First do the sheet name StringBuffer retval = new StringBuffer(); String sheetName = Ref3DPtg.getSheetName(book, field_1_index_extern_sheet); if(sheetName != null) { SheetNameFormatter.appendFormat(retval, sheetName); retval.append( '!' ); } - retval.append( ( new CellReference( getFirstRow(), getFirstColumn(), !isFirstRowRelative(), !isFirstColRelative() ) ).formatAsString() ); - retval.append( ':' ); - retval.append( ( new CellReference( getLastRow(), getLastColumn(), !isLastRowRelative(), !isLastColRelative() ) ).formatAsString() ); + + // Now the normal area bit + retval.append( AreaPtg.toFormulaString(this, book) ); + + // All done return retval.toString(); } diff --git a/src/java/org/apache/poi/hssf/record/formula/AreaI.java b/src/java/org/apache/poi/hssf/record/formula/AreaI.java new file mode 100644 index 0000000000..974ff95f06 --- /dev/null +++ b/src/java/org/apache/poi/hssf/record/formula/AreaI.java @@ -0,0 +1,60 @@ +/* ==================================================================== + 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; + +/** + * Common interface for AreaPtg and Area3DPtg, and their + * child classes. + */ +public interface AreaI { + /** + * @return the first row in the area + */ + public short getFirstRow(); + + /** + * @return last row in the range (x2 in x1,y1-x2,y2) + */ + public short getLastRow(); + + /** + * @return the first column number in the area. + */ + public short getFirstColumn(); + + /** + * @return lastcolumn in the area + */ + public short getLastColumn(); + + /** + * @return isrelative first column to relative or not + */ + public boolean isFirstColRelative(); + /** + * @return lastcol relative or not + */ + public boolean isLastColRelative(); + /** + * @return whether or not the first row is a relative reference or not. + */ + public boolean isFirstRowRelative(); + /** + * @return last row relative or not + */ + public boolean isLastRowRelative(); +} \ No newline at end of file diff --git a/src/java/org/apache/poi/hssf/record/formula/AreaPtg.java b/src/java/org/apache/poi/hssf/record/formula/AreaPtg.java index 127a989183..ae408c34c7 100644 --- a/src/java/org/apache/poi/hssf/record/formula/AreaPtg.java +++ b/src/java/org/apache/poi/hssf/record/formula/AreaPtg.java @@ -34,7 +34,7 @@ import org.apache.poi.hssf.record.RecordInputStream; */ public class AreaPtg - extends Ptg + extends Ptg implements AreaI { public final static short sid = 0x25; private final static int SIZE = 9; @@ -281,14 +281,20 @@ public class AreaPtg { field_4_last_column = column; } - + public String toFormulaString(Workbook book) { - // TODO: - // For a reference like C:C, which is stored as - // C1:C0 (last row is -1), return as C:C - return (new CellReference(getFirstRow(),getFirstColumn(),!isFirstRowRelative(),!isFirstColRelative())).formatAsString() + ":" + - (new CellReference(getLastRow(),getLastColumn(),!isLastRowRelative(),!isLastColRelative())).formatAsString(); + return toFormulaString(this, book); + } + protected static String toFormulaString(AreaI area, Workbook book) { + CellReference topLeft = new CellReference(area.getFirstRow(),area.getFirstColumn(),!area.isFirstRowRelative(),!area.isFirstColRelative()); + CellReference botRight = new CellReference(area.getLastRow(),area.getLastColumn(),!area.isLastRowRelative(),!area.isLastColRelative()); + + if(AreaReference.isWholeColumnReference(topLeft, botRight)) { + return (new AreaReference(topLeft, botRight)).formatAsString(); + } else { + return topLeft.formatAsString() + ":" + botRight.formatAsString(); + } } public byte getDefaultOperandClass() { diff --git a/src/java/org/apache/poi/hssf/util/AreaReference.java b/src/java/org/apache/poi/hssf/util/AreaReference.java index 8a0c9b0bd4..fbd128842d 100644 --- a/src/java/org/apache/poi/hssf/util/AreaReference.java +++ b/src/java/org/apache/poi/hssf/util/AreaReference.java @@ -47,6 +47,17 @@ public final class AreaReference { } String[] parts = separateAreaRefs(reference); + + // Special handling for whole-column references + if(parts.length == 2 && parts[0].length() == 1 && + parts[1].length() == 1 && + parts[0].charAt(0) >= 'A' && parts[0].charAt(0) <= 'Z' && + parts[1].charAt(0) >= 'A' && parts[1].charAt(0) <= 'Z') { + // Represented internally as x$1 to x$0 + parts[0] = parts[0] + "$1"; + parts[1] = parts[1] + "$0"; + } + _firstCell = new CellReference(parts[0]); if(parts.length == 2) { @@ -57,6 +68,15 @@ public final class AreaReference { _isSingleCell = true; } } + + /** + * Creates an area ref from a pair of Cell References. + */ + public AreaReference(CellReference topLeft, CellReference botRight) { + _firstCell = topLeft; + _lastCell = botRight; + _isSingleCell = false; + } /** * Is the reference for a contiguous (i.e. @@ -71,6 +91,24 @@ public final class AreaReference { } return false; } + + /** + * Is the reference for a whole-column reference, + * such as C:C or D:G ? + */ + public static boolean isWholeColumnReference(CellReference topLeft, CellReference botRight) { + // These are represented as something like + // C$1:C$0 or D$1:F$0 + // i.e. absolute from 1st row to 0th one + if(topLeft.getRow() == 0 && topLeft.isRowAbsolute() && + botRight.getRow() == -1 && botRight.isRowAbsolute()) { + return true; + } + return false; + } + public boolean isWholeColumnReference() { + return isWholeColumnReference(_firstCell, _lastCell); + } /** * Takes a non-contiguous area reference, and @@ -150,6 +188,14 @@ public final class AreaReference { * @return the text representation of this area reference as it would appear in a formula. */ public String formatAsString() { + // Special handling for whole-column references + if(isWholeColumnReference()) { + return + CellReference.convertNumToColString(_firstCell.getCol()) + + ":" + + CellReference.convertNumToColString(_lastCell.getCol()); + } + StringBuffer sb = new StringBuffer(32); sb.append(_firstCell.formatAsString()); if(!_isSingleCell) { diff --git a/src/java/org/apache/poi/hssf/util/CellReference.java b/src/java/org/apache/poi/hssf/util/CellReference.java index 33e33ba958..cb83687728 100644 --- a/src/java/org/apache/poi/hssf/util/CellReference.java +++ b/src/java/org/apache/poi/hssf/util/CellReference.java @@ -188,9 +188,11 @@ public final class CellReference { } /** - * takes in a 0-based base-10 column and returns a ALPHA-26 representation + * Takes in a 0-based base-10 column and returns a ALPHA-26 + * representation. + * eg column #3 -> D */ - private static String convertNumToColString(int col) { + protected static String convertNumToColString(int col) { String retval = null; int mod = col % 26; int div = col / 26; diff --git a/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestBug44410.java b/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestBug44410.java index 4fcda6bcd1..03e35e6af2 100644 --- a/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestBug44410.java +++ b/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestBug44410.java @@ -23,11 +23,9 @@ import java.io.FileInputStream; import java.io.File; import java.util.List; -import org.apache.poi.hssf.record.FormulaRecord; import org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate; import org.apache.poi.hssf.record.formula.AreaPtg; -import org.apache.poi.hssf.record.formula.AttrPtg; -import org.apache.poi.hssf.record.formula.functions.Sumproduct; +import org.apache.poi.hssf.record.formula.FuncVarPtg; /** * Bug 44410: SUM(C:C) is valid in excel, and means a sum @@ -52,6 +50,8 @@ public class TestBug44410 extends TestCase { HSSFRow rowIDX = (HSSFRow)sheet.getRow(3); // =sum(C:C) -> 6 HSSFRow rowSUM = (HSSFRow)sheet.getRow(4); + // =sum(C:D) -> 66 + HSSFRow rowSUM2D = (HSSFRow)sheet.getRow(5); // Test the sum HSSFCell cellSUM = rowSUM.getCell((short)0); @@ -59,8 +59,9 @@ public class TestBug44410 extends TestCase { FormulaRecordAggregate frec = (FormulaRecordAggregate)cellSUM.getCellValueRecord(); List ops = frec.getFormulaRecord().getParsedExpression(); + assertEquals(2, ops.size()); assertEquals(AreaPtg.class, ops.get(0).getClass()); - assertEquals(AttrPtg.class, ops.get(1).getClass()); + assertEquals(FuncVarPtg.class, ops.get(1).getClass()); // Actually stored as C1 to C0 (last row is -1) AreaPtg ptg = (AreaPtg)ops.get(0); @@ -68,12 +69,12 @@ public class TestBug44410 extends TestCase { assertEquals(2, ptg.getLastColumn()); assertEquals(0, ptg.getFirstRow()); assertEquals(-1, ptg.getLastRow()); - assertEquals("C$1:C$0", ptg.toFormulaString(wb.getWorkbook())); + assertEquals("C:C", ptg.toFormulaString(wb.getWorkbook())); - // So will show up wrong here, as we don't - // have the sheet to hand when turning the Ptgs - // into a string - assertEquals("SUM(C$1:C$0)", cellSUM.getCellFormula()); + // Will show as C:C, but won't know how many + // rows it covers as we don't have the sheet + // to hand when turning the Ptgs into a string + assertEquals("SUM(C:C)", cellSUM.getCellFormula()); eva.setCurrentRow(rowSUM); // But the evaluator knows the sheet, so it @@ -82,12 +83,17 @@ public class TestBug44410 extends TestCase { // Test the index - // Again, the formula string will be wrong, as we - // don't have the sheet to hand, but the - // evaluator will be correct + // Again, the formula string will be right but + // lacking row count, evaluated will be right HSSFCell cellIDX = rowIDX.getCell((short)0); - assertEquals("INDEX(C$1:C$0,2,1)", cellIDX.getCellFormula()); + assertEquals("INDEX(C:C,2,1)", cellIDX.getCellFormula()); eva.setCurrentRow(rowIDX); assertEquals(2, eva.evaluate(cellIDX).getNumberValue(), 0); + + // Across two colums + HSSFCell cellSUM2D = rowSUM2D.getCell((short)0); + assertEquals("SUM(C:D)", cellSUM2D.getCellFormula()); + eva.setCurrentRow(rowSUM2D); + assertEquals(66, eva.evaluate(cellSUM2D).getNumberValue(), 0); } } diff --git a/src/testcases/org/apache/poi/hssf/data/SingleLetterRanges.xls b/src/testcases/org/apache/poi/hssf/data/SingleLetterRanges.xls index 386f1c8aecab23dc3ee44b02041490d34a9fd3e1..e35058d410f6c8708ed12a3c3dfe574ae30190d2 100644 GIT binary patch delta 1659 zcmZ8iO>7%Q6#jPnw@I4VUE+`cNme#Baas~vE`M@L?N$|+9;_4zL0mae5<{dUmTV(L zCA3DsEeGgGQTG4>iI)CQ3CR&$0J*17$*qJqAR&PwZaozVxX~`}&HAVI*fTrtoA)iili#C1^n{&XZR z{}74L9R4EHu|C!q#&2@ZW1dao3H;Op`?M^|*uV-lk;J%K)zKz-wR5zxjS8*x2dwXT zKGCfF)6rwmXrd5L=Vw~;e0<6~jmsp3?&{Lw`r7#(7ZX?TWXEML(G!DA4s!%w!R+(t ziDV^Op2u+&^FV=;m^IrexmJEg+4*?dwIk0)DBZ$Lra%gFxQ00M9%ap%k8)xN$ zvd!+Owz*2U%RIm+pCtZus3fgczw9e(+6|cq zJg!|+<8?J&mCJ$nI5YV8H7??RZBE0K2Z4lj5r5)Eg*lD`hi9#UOq%lGpvHJ>sL{2? z22C1`23=~fZ3lL$NUlvZ$-hWHhj0|hZgRkyip4SAcq#&5u@j4s6BC;hz;SHYWIHyI zDX`PDkt#Y!HJgN!H+P$Qk>VF^?C$P@9FVdJNYS6VtvX>T8~gkF$cIMYAthp|9Drp_ zP(|iH!U!&?XU30nZ>_IalRNOz!ZF;^bm~ClM~$kss^}j1x^7O40ShNinAz2GsZuek z@085d(&pP++ZFk)epLJukXQ7nc`-<019}MXhj4HDPT+Qfrj)~wa1W4^@l$_efR1KJ z{;lWEq_y`UaLYT2APxbsv$4~$rB}13&c_zR2*Yrp+{&BlW^5^!edP=-d_WA~Lp6SZ z2p}I0&s(1lg$WUOkK$-_T|U&cJ+zgBkZVJXa2_fV(8d8>P|DQ2Nb^lYxdj!*MnA^N z{TNr-YxR*iaKEFk5Bq`lN2!KYGGWY;nakI@SX_0u!V@-9jf zWLOrCoD5D{ykXvGlp*qQ+js`pL+rsYj_|!9G9q(I^Wp)?@onI`F^SV{k!m?2kR-w&BYK$6vG-%~sd3bg l{6h4XQvI$m7wp6wlamok1fQvY7P)&^{yw1BtH*b={{hPuMLz%l delta 1781 zcmcIkdrXs86hHTVZT(uPltK{|>bEi{ifCKwgb<{T7{%9$C!)dXvdu5~tC0(L#rcTWK(niHGP|ei2y z8Ug14!0XyfPyv)k$uw974#iq$1J~>4*$O zCgMFr79tywgIIvbMc5E|qd~0Y^+`5Dk$Vlkrdh_WDg)~xyS>b?5hQru@la2gCX!*m zqaKt-!b2l&bf*dY4YPnTQ1PK$5XUK^XtqpDS7;|5Q=nDOtB_I!cQvg;nmxGW0Q_k1 zAPvq^#3+9Nr&8Rbu(-TXg=CEq7N9|_@=>q$&2!;12|fi!BBa1fNQP*fXM-NK1?Hok zgg$1;1uHB8CzQb=EW9Ep!POEdMn1rYghD%f&v6Na5|I0B`S`FQkU@z*E;86-U>caj zWTmynC=vU0VXj)q1ej?N=H!*ym(8-52{C%TUa;DiIqao^C2@{0+3q94NdiXLN~*c? z?atKM^=I^a`;*X)gk~GJ{P3e|PjYuVwx8Wxp5i>Sx@$`3{-oK5U9+Z_wyf{Zo!jQ9 z-~V}qd-2tK?H3$vxBI4&Z<-Uew!qHIg2%OZoMTdq{Zvfr>aQj1PVBI(H)j=iPU$=< zP1U2g$m%6MA8)#n^}eCMcWuYfHStzQS)MT-5)Pj0?(2W3`7P2N^=tg7sGNbYtg3k{ zN+0(WY#7*(8M*ws`@#ET8~jYWQ=LDg3UAjh-`RF~h~NxqFcMqv9ummcY^uBchwcV( z35K}%>2vQhDAh{tlT^?pj+{5v-vpBT7!@gjHh$0mu~ukYxLrRJ`% zv_olaAt{%X6X3XFmo(2E(fDOgYs~2z{uL)8ySsaL9{A;0P;ch}&*gm!AAexnTQdHq z`Z2d8l{cF@6G?4!_4QRHZ_y*F7O4ET$x}F2nzW$!oMvin!Iq+{_LhcBZFgSmwv*w{ z7T!93q2bOa1yPfWbGkOXA=mshta{7R3}*+I_H)6N@=|IV+0wJJv7@xhxVgN2r8mHK z^`o=yWaWJ#-32<3i?MzaT{V6ISoIV>1or(26#2j0Q?#=^)6CD|_)|6L2Z|j4h1D>i z9e}4;?Y_0wr-20cZ0x_`;8hzdGoQ`HLZ1gRl79zc(Z6C_N!gD@ApqwG32aB6@4(D< z?5fPin`PK;7$e*H#0;<Y`;lkl5C@`8}G`p@0BCwWU=7wS} zay?VN%pI=kyvLGQe;7RCckCk1Fv)_NjX0P})o{$$S1|vDgkwC%*zw^IDDfTCi$_Xf z3Yrx7fu_?MwUiPn_8K%|m^zpo5Dn^x;U(zCZUO5MN<=Lp2vH{%sUyS{>QM2ZT98+E tRfNSBpC_EVOFb$;v?*0D$U?+$or}&0aqrMw3gpdArfn?HU1r)y{sHv=u;l;% -- 2.39.5