poi-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From n...@apache.org
Subject svn commit: r628065 - in /poi/trunk/src: documentation/content/xdocs/ java/org/apache/poi/hssf/record/formula/ java/org/apache/poi/hssf/util/ scratchpad/testcases/org/apache/poi/hssf/usermodel/ testcases/org/apache/poi/hssf/data/
Date Fri, 15 Feb 2008 13:50:39 GMT
Author: nick
Date: Fri Feb 15 05:50:38 2008
New Revision: 628065

URL: http://svn.apache.org/viewvc?rev=628065&view=rev
Log:
Further support for whole-column references, including formula strings and the evaluator.
Also has some new tests for it

Added:
    poi/trunk/src/java/org/apache/poi/hssf/record/formula/AreaI.java   (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/Area3DPtg.java
    poi/trunk/src/java/org/apache/poi/hssf/record/formula/AreaPtg.java
    poi/trunk/src/java/org/apache/poi/hssf/util/AreaReference.java
    poi/trunk/src/java/org/apache/poi/hssf/util/CellReference.java
    poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestBug44410.java
    poi/trunk/src/testcases/org/apache/poi/hssf/data/SingleLetterRanges.xls

Modified: poi/trunk/src/documentation/content/xdocs/changes.xml
URL: http://svn.apache.org/viewvc/poi/trunk/src/documentation/content/xdocs/changes.xml?rev=628065&r1=628064&r2=628065&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/changes.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/changes.xml Fri Feb 15 05:50:38 2008
@@ -36,7 +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">44410 - Support for whole-column
ranges, such as C:C, in formula strings and 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=628065&r1=628064&r2=628065&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/status.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/status.xml Fri Feb 15 05:50:38 2008
@@ -33,7 +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">44410 - Support for whole-column
ranges, such as C:C, in formula strings and 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/Area3DPtg.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/Area3DPtg.java?rev=628065&r1=628064&r2=628065&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/Area3DPtg.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/Area3DPtg.java Fri Feb 15 05:50:38
2008
@@ -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 @@
  * @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 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();
 	}
 

Added: poi/trunk/src/java/org/apache/poi/hssf/record/formula/AreaI.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/AreaI.java?rev=628065&view=auto
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/AreaI.java (added)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/AreaI.java Fri Feb 15 05:50:38 2008
@@ -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

Propchange: poi/trunk/src/java/org/apache/poi/hssf/record/formula/AreaI.java
------------------------------------------------------------------------------
    svn:eol-style = native

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=628065&r1=628064&r2=628065&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 05:50:38
2008
@@ -34,7 +34,7 @@
  */
 
 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 @@
     {
         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() {

Modified: poi/trunk/src/java/org/apache/poi/hssf/util/AreaReference.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/util/AreaReference.java?rev=628065&r1=628064&r2=628065&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/util/AreaReference.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/util/AreaReference.java Fri Feb 15 05:50:38 2008
@@ -47,6 +47,17 @@
         }
 
         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 @@
             _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 @@
         }
         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 @@
      * @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) {

Modified: poi/trunk/src/java/org/apache/poi/hssf/util/CellReference.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/util/CellReference.java?rev=628065&r1=628064&r2=628065&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/util/CellReference.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/util/CellReference.java Fri Feb 15 05:50:38 2008
@@ -188,9 +188,11 @@
     }
 
     /**
-     * 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;

Modified: 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=628065&r1=628064&r2=628065&view=diff
==============================================================================
--- poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestBug44410.java (original)
+++ poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestBug44410.java Fri
Feb 15 05:50:38 2008
@@ -23,11 +23,9 @@
 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 @@
         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 @@
         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 @@
         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 @@
         
         
         // 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);
     }
 }

Modified: 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=628065&r1=628064&r2=628065&view=diff
==============================================================================
Binary files - no diff available.



---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org


Mime
View raw message