Author: nick
Date: Fri Feb 15 04:59:40 2008
New Revision: 628044
URL: http://svn.apache.org/viewvc?rev=628044&view=rev
Log:
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
Added:
poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestBug44410.java (with
props)
poi/trunk/src/testcases/org/apache/poi/hssf/data/SingleLetterRanges.xls (with props)
Modified:
poi/trunk/src/documentation/content/xdocs/changes.xml
poi/trunk/src/documentation/content/xdocs/status.xml
poi/trunk/src/java/org/apache/poi/hssf/record/formula/AreaPtg.java
poi/trunk/src/scratchpad/src/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java
Modified: poi/trunk/src/documentation/content/xdocs/changes.xml
URL: http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/changes.xml?rev=628044&r1=628043&r2=628044&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/changes.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/changes.xml Fri Feb 15 04:59:40 2008
@@ -36,6 +36,7 @@
<!-- Don't forget to update status.xml too! -->
<release version="3.1-beta1" date="2008-??-??">
+ <action dev="POI-DEVELOPERS" type="fix">44410 - Partial support for whole-column
ranges, such as C:C, in the formula evaluator</action>
<action dev="POI-DEVELOPERS" type="fix">44421 - Update Match function to
properly support Area references</action>
<action dev="POI-DEVELOPERS" type="fix">44417 - Improved handling of references
for the need to quote the sheet name for some formulas, but not when fetching a sheet by name</action>
<action dev="POI-DEVELOPERS" type="fix">44413 - Fix for circular references
in INDEX, OFFSET, VLOOKUP formulas, where a cell is actually allowed to reference itself</action>
Modified: poi/trunk/src/documentation/content/xdocs/status.xml
URL: http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/status.xml?rev=628044&r1=628043&r2=628044&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/status.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/status.xml Fri Feb 15 04:59:40 2008
@@ -33,6 +33,7 @@
<!-- Don't forget to update changes.xml too! -->
<changes>
<release version="3.1-beta1" date="2008-??-??">
+ <action dev="POI-DEVELOPERS" type="fix">44410 - Partial support for whole-column
ranges, such as C:C, in the formula evaluator</action>
<action dev="POI-DEVELOPERS" type="fix">44421 - Update Match function to
properly support Area references</action>
<action dev="POI-DEVELOPERS" type="fix">44417 - Improved handling of references
for the need to quote the sheet name for some formulas, but not when fetching a sheet by name</action>
<action dev="POI-DEVELOPERS" type="fix">44413 - Fix for circular references
in INDEX, OFFSET, VLOOKUP formulas, where a cell is actually allowed to reference itself</action>
Modified: poi/trunk/src/java/org/apache/poi/hssf/record/formula/AreaPtg.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/AreaPtg.java?rev=628044&r1=628043&r2=628044&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/AreaPtg.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/AreaPtg.java Fri Feb 15 04:59:40
2008
@@ -284,6 +284,9 @@
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();
}
Modified: poi/trunk/src/scratchpad/src/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/scratchpad/src/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java?rev=628044&r1=628043&r2=628044&view=diff
==============================================================================
--- poi/trunk/src/scratchpad/src/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java (original)
+++ poi/trunk/src/scratchpad/src/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.java Fri
Feb 15 04:59:40 2008
@@ -432,6 +432,15 @@
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 @@
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);
Added: poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestBug44410.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestBug44410.java?rev=628044&view=auto
==============================================================================
--- poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestBug44410.java (added)
+++ poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestBug44410.java Fri
Feb 15 04:59:40 2008
@@ -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);
+ }
+}
Propchange: poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestBug44410.java
------------------------------------------------------------------------------
svn:eol-style = native
Added: poi/trunk/src/testcases/org/apache/poi/hssf/data/SingleLetterRanges.xls
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/data/SingleLetterRanges.xls?rev=628044&view=auto
==============================================================================
Binary file - no diff available.
Propchange: poi/trunk/src/testcases/org/apache/poi/hssf/data/SingleLetterRanges.xls
------------------------------------------------------------------------------
svn:mime-type = application/octet-stream
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org
|