From dfb66ca67f529f15fefed40e78b67703e4290f54 Mon Sep 17 00:00:00 2001 From: Nick Burch Date: Fri, 15 Feb 2008 12:59:40 +0000 Subject: [PATCH] Partial fix for bug #44410 - support whole column ranges such as C:C in the formula evaluator (so SUM(D:D) will now work). However, the formula string will still be displayed wrong git-svn-id: https://svn.eu.apache.org/repos/asf/poi/trunk@628044 13f79535-47bb-0310-9956-ffa450edef68 --- src/documentation/content/xdocs/changes.xml | 1 + src/documentation/content/xdocs/status.xml | 1 + .../apache/poi/hssf/record/formula/AreaPtg.java | 3 + .../poi/hssf/usermodel/HSSFFormulaEvaluator.java | 18 ++++ .../apache/poi/hssf/usermodel/TestBug44410.java | 93 +++++++++++++++++++++ .../apache/poi/hssf/data/SingleLetterRanges.xls | Bin 0 -> 13824 bytes 6 files changed, 116 insertions(+) create mode 100644 src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestBug44410.java create mode 100644 src/testcases/org/apache/poi/hssf/data/SingleLetterRanges.xls diff --git a/src/documentation/content/xdocs/changes.xml b/src/documentation/content/xdocs/changes.xml index f951a78..fc411e2 100644 --- a/src/documentation/content/xdocs/changes.xml +++ b/src/documentation/content/xdocs/changes.xml @@ -36,6 +36,7 @@ + 44410 - Partial support for whole-column ranges, such as C:C, in 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 2202a0f..e024f8d 100644 --- a/src/documentation/content/xdocs/status.xml +++ b/src/documentation/content/xdocs/status.xml @@ -33,6 +33,7 @@ + 44410 - Partial support for whole-column ranges, such as C:C, in 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/AreaPtg.java b/src/java/org/apache/poi/hssf/record/formula/AreaPtg.java index 61ce2a0..127a989 100644 --- a/src/java/org/apache/poi/hssf/record/formula/AreaPtg.java +++ b/src/java/org/apache/poi/hssf/record/formula/AreaPtg.java @@ -284,6 +284,9 @@ public class AreaPtg 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(); } diff --git a/src/scratchpad/src/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java b/src/scratchpad/src/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java index fb0c980..7cad38f 100644 --- a/src/scratchpad/src/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java +++ b/src/scratchpad/src/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java @@ -432,6 +432,15 @@ public class HSSFFormulaEvaluator { short col0 = ap.getFirstColumn(); short row1 = ap.getLastRow(); short col1 = ap.getLastColumn(); + + // If the last row is -1, then the + // reference is for the rest of the column + // (eg C:C) + // TODO: Handle whole column ranges properly + if(row1 == -1 && row0 >= 0) { + row1 = (short)sheet.getLastRowNum(); + } + ValueEval[] values = new ValueEval[(row1 - row0 + 1) * (col1 - col0 + 1)]; for (short x = row0; sheet != null && x < row1 + 1; x++) { HSSFRow row = sheet.getRow(x); @@ -451,6 +460,15 @@ public class HSSFFormulaEvaluator { short col1 = a3dp.getLastColumn(); Workbook wb = workbook.getWorkbook(); HSSFSheet xsheet = workbook.getSheetAt(wb.getSheetIndexFromExternSheetIndex(a3dp.getExternSheetIndex())); + + // If the last row is -1, then the + // reference is for the rest of the column + // (eg C:C) + // TODO: Handle whole column ranges properly + if(row1 == -1 && row0 >= 0) { + row1 = (short)xsheet.getLastRowNum(); + } + ValueEval[] values = new ValueEval[(row1 - row0 + 1) * (col1 - col0 + 1)]; for (short x = row0; xsheet != null && x < row1 + 1; x++) { HSSFRow row = xsheet.getRow(x); diff --git a/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestBug44410.java b/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestBug44410.java new file mode 100644 index 0000000..4fcda6b --- /dev/null +++ b/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestBug44410.java @@ -0,0 +1,93 @@ +package org.apache.poi.hssf.usermodel; +/* ==================================================================== + 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. +==================================================================== */ + +import junit.framework.TestCase; + +import java.io.IOException; +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; + +/** + * Bug 44410: SUM(C:C) is valid in excel, and means a sum + * of all the rows in Column C + * + * @author Nick Burch + */ + +public class TestBug44410 extends TestCase { + protected String cwd = System.getProperty("HSSF.testdata.path"); + + public void test44410() throws IOException { + FileInputStream in = new FileInputStream(new File(cwd, "SingleLetterRanges.xls")); + HSSFWorkbook wb = new HSSFWorkbook(in); + in.close(); + + HSSFSheet sheet = wb.getSheetAt(0); + + HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(sheet, wb); + + // =index(C:C,2,1) -> 2 + HSSFRow rowIDX = (HSSFRow)sheet.getRow(3); + // =sum(C:C) -> 6 + HSSFRow rowSUM = (HSSFRow)sheet.getRow(4); + + // Test the sum + HSSFCell cellSUM = rowSUM.getCell((short)0); + + FormulaRecordAggregate frec = + (FormulaRecordAggregate)cellSUM.getCellValueRecord(); + List ops = frec.getFormulaRecord().getParsedExpression(); + assertEquals(AreaPtg.class, ops.get(0).getClass()); + assertEquals(AttrPtg.class, ops.get(1).getClass()); + + // Actually stored as C1 to C0 (last row is -1) + AreaPtg ptg = (AreaPtg)ops.get(0); + assertEquals(2, ptg.getFirstColumn()); + assertEquals(2, ptg.getLastColumn()); + assertEquals(0, ptg.getFirstRow()); + assertEquals(-1, ptg.getLastRow()); + assertEquals("C$1:C$0", 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()); + eva.setCurrentRow(rowSUM); + + // But the evaluator knows the sheet, so it + // can do it properly + assertEquals(6, eva.evaluate(cellSUM).getNumberValue(), 0); + + + // 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 + HSSFCell cellIDX = rowIDX.getCell((short)0); + assertEquals("INDEX(C$1:C$0,2,1)", cellIDX.getCellFormula()); + eva.setCurrentRow(rowIDX); + assertEquals(2, eva.evaluate(cellIDX).getNumberValue(), 0); + } +} diff --git a/src/testcases/org/apache/poi/hssf/data/SingleLetterRanges.xls b/src/testcases/org/apache/poi/hssf/data/SingleLetterRanges.xls new file mode 100644 index 0000000000000000000000000000000000000000..386f1c8aecab23dc3ee44b02041490d34a9fd3e1 GIT binary patch literal 13824 zcwX&W4OA4@75-*+7M34nk>8+hCQvH@mMjD-mLi)_8_}q%fIkHS2piagEQDPYdRnt4 zY7$#h8$C72u~m;rG$$!0wQ5f^acOFzMoMgJTCFxvHRt4{#>7)SY9ibF-psNK0*U?0 zNjl%0`+n|y_uaR%Z|<8r^XBdD=$@@FC-q_wrb354Ul>AycmmNa{)!PK_xV`SpC=Oe zG<>NLW}rVZM32A6b%h@GQc(x=Lc~i_C#AsOh|19n1F&H7Buh?CR*w9?z}M;?v6ftQ-Yyiayxm%!Ci8SOr2_VH zos*oCg(MtebNIn^0#|I-3wNOf4pd{we_6U^1ZgHd3P=SsM+qe)YlIyx$e2y}GRo(n ziFnm;!Xza^PiF?n+mcf480n`f`B2yS3PD7(!4j7&?gKGjT8 z3QY&oz!rmg;msx?4Hlr1anoeZuQV%t1*)nv*__NP)=INU%E0ZI$P|OMf>j2!&qoGw z2O6RQ46>zg0_F+|0v+Ta9d`s7^lJ}Ny+JPt&mayc`%+zcQGA`!M{BVfbmo@WXE4uNR9cpAAd^<`CJ4<`T^#Dkdr+DkYjvw18+K(ITSn z5|t5^6IBr1M^s5vMN~Zj>qQAhkIu*PXio^w$9N>3$9O_{4i9)9>(TqFxIbJ)^YSoX z`a@7$Z@0U%egujC%npI&f|NimKu`zYDHw>zAG{{uQ_SGgi%}Brt^9o1A}_1AJLTr; z7MHzNUgmJu%eFa1$%=4VTaCTh?Pzii*~o`*vQthYiKPB&3~@y0N1U4P38zF^$czIP zvIsG@zp6Y%i zc=D6VZH`TEtZOT2eSKx`}^PM+?i{CHMeW!3(}+eer|irT2|+MHQ8&7TX8Wjb#>FZAFh3;Y+2Uj z3y+-My(+)h;jYfkN5PA4p1E}SE{h{)Mi+WJbV{Ed#Ko4b#N%B}GSvW2SHXM||O6Kl7;|L5d)1vx7(f7+I64rly=!g@i@TbzdgB8NTTkoezg_oEn@h|Yd*s~m&eN{m>~(FYmiwZrPCWF+ zNdK~iuNt<}ZEzU(BJ2>!hbUeI=qd2$0!m3@zz?|l1)hkAq6I z)kL*su!hjO&U%c33M(koSadyxEyg~dXces{5hek5>&D|ctw}oSDvA#_1S#CGUp3Ln ze_-FJfa)#Ap7Tse;z|sEqUE)ZavSCB$iq}|4EBVmru#9T+Uq1<6iA(wE|5h%CQ~UK z8Z`X7=%edJFNinr+1&0~wfW-JIrYXDequoS#9b#SmctIBP*rXP_?(PEFkMGXux7Vjt1N;#z5@1?+fbpGokE1qxoKpRy6W2C)jTi2Ln-n=I7uG1&P(aC76y{TF_}u|EO+3Gw$or~m)} literal 0 HcwPel00001 -- 2.11.4.GIT