poi-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From n...@apache.org
Subject svn commit: r628044 - in /poi/trunk/src: documentation/content/xdocs/ java/org/apache/poi/hssf/record/formula/ scratchpad/src/org/apache/poi/hssf/usermodel/ scratchpad/testcases/org/apache/poi/hssf/usermodel/ testcases/org/apache/poi/hssf/data/
Date Fri, 15 Feb 2008 12:59:41 GMT
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


Mime
View raw message