From 5eb3ab088f90ade741014b7b8c4dfe725fe15ab6 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.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 + .../poi/hssf/record/formula/AreaPtg.java | 3 + .../hssf/usermodel/HSSFFormulaEvaluator.java | 18 ++++ .../poi/hssf/usermodel/TestBug44410.java | 93 ++++++++++++++++++ .../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 f951a78176..fc411e2cf7 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 2202a0fc87..e024f8dc46 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 61ce2a0d16..127a989183 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 fb0c980e84..7cad38f102 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 0000000000..4fcda6bcd1 --- /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 zcmeHO4RBP|6+Z9nZn7c4Y{(B7V0}$WiAgqYVkn_$$ZCob5LigUPqT3ok~P^0`7yhJ zq|@S}fJ#e6VWf3vfk6dF8?+SY2+F1w1tg8M#TE*6Q@|O=BFYp7P_q5b-8?smq?7)R z)_2~Vd)__w+2x$nNWy5j;{UrO$wK`@nc)EkVVh<GAJjt4yo1LrtFW^qm zhboEt;K#r#z*z)s0{142LFSGUX}m~x3c39vEkH?~^f2u&8f(pi9Far|Wt)&&E7BB^ z-h@=5y`s)Z!O5m%It2OP1M7IG6Ez#8JE(=+R7FeZzgxNm>W0#rMIVMsC2Ee9O3|kV za*>A=nhm-PbRIQ|PN}G=aVQM{htdu(>&MYEGmsAj3D$+rb4K{(w@ zK?OHm1;4_49+iUsx8d>mF}acaP<(FnM)Lp9dPW6%_f>aGzVx0&IQf`mHJ%(QAd59$weY2oIu`0{zT#ZU|Rg1?}qbzfK>lFK(;*?O~ zw67s*Q<2)))djc>?x~00~WGrU4UKw4BfyJTNL$TsKPITG?WuH9v(Qjuhwih}DKV9%MMEaoM_#mUg1Q-==n^vHtQ_s4% zOAiU@l_H%*PeR0jqlijrI?W{u(wT6J7P7--Tj_R`F~>kHo}=n}L*BcTlb<`OhZmO&+S%4f-saZddN9^0-=NxV%c9 z)oN8rTrF;wN3j>(qfBzCHT9H3Kay7K+BUV8&Fpy1YCQGvnA3%aD|JhEUOf4+bDMk9 z>+9M|TVGq*GdZxmc;?OrW=!)OdGxY#R=2xj`;Xgq)t@+j>P>g|dzYq4FC8v2SH=a7 zDgOF&_w9MESMs`6KCgdd-%st2+RAJFucY`*i7PJVr>$-}_x-hRmoLk{eBt5KyH^#I zxV=?51yuOL8)q(E{wVQy+OCY>7u=Fjab;ZjirL@wTsv30=E|ByX-j|cLBjSa2cmPg zm9@THrhL6)>E`ZZkxFaQfgGta;b|$(_{7>R@BK059ZAW~FPOGv>kG*zT|X(^aNFFR z5sxML?dQ8bD9xYu{@H8$t{gpjRJZ$A2lY|Wmv-NMzpZE7{GZM5PAdJ)$Sm4BVymHQ zS8C^T=eqJUa0PdEosY)@Dp< ztms+um8it4qgQXde_`us-Tb#|-){5Bxnqx$u_f0W{K|9ji>9FlXPe* z7KnI>QZX}ztB$d8C#ZL1xa!TOzPEXP9T<} z9l%H+m)n3mI;R8qrN{vs1#AZL``RNwylv42Afi#)0_2DJEGi*<6f)|f0tSQ zGz(}J&@7-?K(l~mfq@qINAbUWyzBU;tg)u`8xj9!UhUxc{}7NrKydtT2l9f}LLk0# z;==_|G?3$ej@jFQ9QXTxNkDZyU>(kRePA1~|9XHC`9|-OZ-9v9xVw|0MSGm*b{Pg! zGZb-OcugYorHW#};%0e?fyEazwbXm~BbGoAbAP-lD_%P&l6E|2jC<=ba`TNOR22-}8Ah56Asn9tXREs{i#2xtO`jhuB LT)(*VzU%)x@%KNd literal 0 HcmV?d00001 -- 2.39.5