poi-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From n...@apache.org
Subject svn commit: r628033 - in /poi/trunk/src: documentation/content/xdocs/ java/org/apache/poi/hssf/record/ java/org/apache/poi/hssf/record/formula/ java/org/apache/poi/hssf/usermodel/ java/org/apache/poi/hssf/util/ scratchpad/testcases/org/apache/poi/hssf/...
Date Fri, 15 Feb 2008 12:04:44 GMT
Author: nick
Date: Fri Feb 15 04:04:42 2008
New Revision: 628033

URL: http://svn.apache.org/viewvc?rev=628033&view=rev
Log:
Fix from Josh from bug #44417 - Improved handling of references for the need to quote the sheet name for some formulas, but not when fetching a sheet by name

Added:
    poi/trunk/src/testcases/org/apache/poi/hssf/util/AllHSSFUtilTests.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/NameRecord.java
    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/record/formula/Ref3DPtg.java
    poi/trunk/src/java/org/apache/poi/hssf/record/formula/ReferencePtg.java
    poi/trunk/src/java/org/apache/poi/hssf/record/formula/SheetNameFormatter.java
    poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.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/TestBug42464.java
    poi/trunk/src/testcases/org/apache/poi/hssf/HSSFTests.java
    poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestFormulas.java
    poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestNamedRange.java
    poi/trunk/src/testcases/org/apache/poi/hssf/util/TestAreaReference.java
    poi/trunk/src/testcases/org/apache/poi/hssf/util/TestCellReference.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=628033&r1=628032&r2=628033&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/changes.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/changes.xml Fri Feb 15 04:04:42 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">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>
            <action dev="POI-DEVELOPERS" type="fix">44403 - Fix for Mid function handling its arguments wrong</action>
            <action dev="POI-DEVELOPERS" type="add">44364 - Support for Match, NA and SumProduct functions, as well as initial function error support</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=628033&r1=628032&r2=628033&view=diff
==============================================================================
--- poi/trunk/src/documentation/content/xdocs/status.xml (original)
+++ poi/trunk/src/documentation/content/xdocs/status.xml Fri Feb 15 04:04:42 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">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>
            <action dev="POI-DEVELOPERS" type="fix">44403 - Fix for Mid function handling its arguments wrong</action>
            <action dev="POI-DEVELOPERS" type="add">44364 - Support for Match, NA and SumProduct functions, as well as initial function error support</action>

Modified: poi/trunk/src/java/org/apache/poi/hssf/record/NameRecord.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/NameRecord.java?rev=628033&r1=628032&r2=628033&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/NameRecord.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/NameRecord.java Fri Feb 15 04:04:42 2008
@@ -726,7 +726,7 @@
         	for(int i=0; i<refs.length; i++) {
 	            ptg = new Area3DPtg();
 	            ((Area3DPtg) ptg).setExternSheetIndex(externSheetIndex);
-	            ((Area3DPtg) ptg).setArea(refs[i].toString());
+	            ((Area3DPtg) ptg).setArea(refs[i].formatAsString());
 	            field_13_name_definition.push(ptg);
 	            this.setDefinitionTextLength( (short)(getDefinitionLength() + ptg.getSize()) );
         	}

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=628033&r1=628032&r2=628033&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 04:04:42 2008
@@ -243,21 +243,17 @@
 	public void setArea( String ref )
 	{
 		AreaReference ar = new AreaReference( ref );
-		CellReference[] crs = ar.getCells();
 		
-		CellReference firstCell = crs[0];
-		CellReference lastCell = firstCell;
-		if(crs.length > 1) {
-			lastCell = crs[1];
-		}
+		CellReference frstCell = ar.getFirstCell();
+		CellReference lastCell = ar.getLastCell();
 
-		setFirstRow(    (short) firstCell.getRow() );
-		setFirstColumn( (short) firstCell.getCol() );
+		setFirstRow(    (short) frstCell.getRow() );
+		setFirstColumn(         frstCell.getCol() );
 		setLastRow(     (short) lastCell.getRow() );
-		setLastColumn(  (short) lastCell.getCol() );
-		setFirstColRelative( !firstCell.isColAbsolute() );
+		setLastColumn(          lastCell.getCol() );
+		setFirstColRelative( !frstCell.isColAbsolute() );
 		setLastColRelative(  !lastCell.isColAbsolute() );
-		setFirstRowRelative( !firstCell.isRowAbsolute() );
+		setFirstRowRelative( !frstCell.isRowAbsolute() );
 		setLastRowRelative(  !lastCell.isRowAbsolute() );
 	}
 
@@ -273,9 +269,9 @@
 			SheetNameFormatter.appendFormat(retval, sheetName);
 			retval.append( '!' );
 		}
-		retval.append( ( new CellReference( getFirstRow(), getFirstColumn(), !isFirstRowRelative(), !isFirstColRelative() ) ).toString() );
+		retval.append( ( new CellReference( getFirstRow(), getFirstColumn(), !isFirstRowRelative(), !isFirstColRelative() ) ).formatAsString() );
 		retval.append( ':' );
-		retval.append( ( new CellReference( getLastRow(), getLastColumn(), !isLastRowRelative(), !isLastColRelative() ) ).toString() );
+		retval.append( ( new CellReference( getLastRow(), getLastColumn(), !isLastRowRelative(), !isLastColRelative() ) ).formatAsString() );
 		return retval.toString();
 	}
 

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=628033&r1=628032&r2=628033&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:04:42 2008
@@ -53,14 +53,16 @@
    
     public AreaPtg(String arearef) {
         AreaReference ar = new AreaReference(arearef);
-        setFirstRow((short)ar.getCells()[0].getRow());
-        setFirstColumn((short)ar.getCells()[0].getCol());
-        setLastRow((short)ar.getCells()[1].getRow());
-        setLastColumn((short)ar.getCells()[1].getCol());
-        setFirstColRelative(!ar.getCells()[0].isColAbsolute());
-        setLastColRelative(!ar.getCells()[1].isColAbsolute());
-        setFirstRowRelative(!ar.getCells()[0].isRowAbsolute());
-        setLastRowRelative(!ar.getCells()[1].isRowAbsolute());        
+        CellReference firstCell = ar.getFirstCell();
+        CellReference lastCell = ar.getLastCell();
+        setFirstRow((short)firstCell.getRow());
+        setFirstColumn(firstCell.getCol());
+        setLastRow((short)lastCell.getRow());
+        setLastColumn(lastCell.getCol());
+        setFirstColRelative(!firstCell.isColAbsolute());
+        setLastColRelative(!lastCell.isColAbsolute());
+        setFirstRowRelative(!firstCell.isRowAbsolute());
+        setLastRowRelative(!lastCell.isRowAbsolute());        
     }
     
     public AreaPtg(short firstRow, short lastRow, short firstColumn, short lastColumn, boolean firstRowRelative, boolean lastRowRelative, boolean firstColRelative, boolean lastColRelative) {
@@ -282,8 +284,8 @@
 
     public String toFormulaString(Workbook book)
     {
-         return (new CellReference(getFirstRow(),getFirstColumn(),!isFirstRowRelative(),!isFirstColRelative())).toString() + ":" +
-                (new CellReference(getLastRow(),getLastColumn(),!isLastRowRelative(),!isLastColRelative())).toString();
+         return (new CellReference(getFirstRow(),getFirstColumn(),!isFirstRowRelative(),!isFirstColRelative())).formatAsString() + ":" +
+                (new CellReference(getLastRow(),getLastColumn(),!isLastRowRelative(),!isLastColRelative())).formatAsString();
     }
 
     public byte getDefaultOperandClass() {

Modified: poi/trunk/src/java/org/apache/poi/hssf/record/formula/Ref3DPtg.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/Ref3DPtg.java?rev=628033&r1=628032&r2=628033&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/Ref3DPtg.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/Ref3DPtg.java Fri Feb 15 04:04:42 2008
@@ -183,7 +183,7 @@
             SheetNameFormatter.appendFormat(retval, sheetName);
             retval.append( '!' );
         }
-        retval.append((new CellReference(getRow(),getColumn(),!isRowRelative(),!isColRelative())).toString()); 
+        retval.append((new CellReference(getRow(),getColumn(),!isRowRelative(),!isColRelative())).formatAsString()); 
         return retval.toString();
     }
 

Modified: poi/trunk/src/java/org/apache/poi/hssf/record/formula/ReferencePtg.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/ReferencePtg.java?rev=628033&r1=628032&r2=628033&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/ReferencePtg.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/ReferencePtg.java Fri Feb 15 04:04:42 2008
@@ -191,7 +191,7 @@
     public String toFormulaString(Workbook book)
     {
         //TODO -- should we store a cellreference instance in this ptg?? but .. memory is an issue, i believe!
-        return (new CellReference(getRowAsInt(),getColumn(),!isRowRelative(),!isColRelative())).toString();
+        return (new CellReference(getRowAsInt(),getColumn(),!isRowRelative(),!isColRelative())).formatAsString();
     }
     
     public byte getDefaultOperandClass() {

Modified: poi/trunk/src/java/org/apache/poi/hssf/record/formula/SheetNameFormatter.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/record/formula/SheetNameFormatter.java?rev=628033&r1=628032&r2=628033&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/record/formula/SheetNameFormatter.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/record/formula/SheetNameFormatter.java Fri Feb 15 04:04:42 2008
@@ -26,7 +26,7 @@
  * 
  * @author Josh Micich
  */
-final class SheetNameFormatter {
+public final class SheetNameFormatter {
 	
 	private static final String BIFF8_LAST_COLUMN = "IV";
 	private static final int BIFF8_LAST_COLUMN_TEXT_LEN = BIFF8_LAST_COLUMN.length();

Modified: poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java?rev=628033&r1=628032&r2=628033&view=diff
==============================================================================
--- poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java (original)
+++ poi/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFWorkbook.java Fri Feb 15 04:04:42 2008
@@ -1119,12 +1119,12 @@
 	public void setPrintArea(int sheetIndex, int startColumn, int endColumn,
 							  int startRow, int endRow) {
 
-		//using absolute references because they dont get copied and pasted anyway
+		//using absolute references because they don't get copied and pasted anyway
 		CellReference cell = new CellReference(startRow, startColumn, true, true);
-		String reference = cell.toString();
+		String reference = cell.formatAsString();
 
 		cell = new CellReference(endRow, endColumn, true, true);
-		reference = reference+":"+cell.toString();
+		reference = reference+":"+cell.formatAsString();
 
 		setPrintArea(sheetIndex, reference);
 	}

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=628033&r1=628032&r2=628033&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 04:04:42 2008
@@ -21,26 +21,40 @@
 import java.util.ArrayList;
 import java.util.StringTokenizer;
 
-public class AreaReference {
+public final class AreaReference {
 
-
-private CellReference [] cells;
-private int dim;
+    /** The character (!) that separates sheet names from cell references */ 
+    private static final char SHEET_NAME_DELIMITER = '!';
+    /** The character (:) that separates the two cell references in a multi-cell area reference */
+    private static final char CELL_DELIMITER = ':';
+    /** The character (') used to quote sheet names when they contain special characters */
+    private static final char SPECIAL_NAME_DELIMITER = '\'';
+    
+    private final CellReference _firstCell;
+    private final CellReference _lastCell;
+    private final boolean _isSingleCell;
 
     /**
-     * Create an area ref from a string representation.
-     * The area reference must be contiguous
+     * Create an area ref from a string representation.  Sheet names containing special characters should be
+     * delimited and escaped as per normal syntax rules for formulas.<br/> 
+     * The area reference must be contiguous (i.e. represent a single rectangle, not a union of rectangles)
      */
     public AreaReference(String reference) {
         if(! isContiguous(reference)) {
-            throw new IllegalArgumentException("References passed to the AreaReference must be contiguous, use generateContiguous(ref) if you have non-contiguous references");
+            throw new IllegalArgumentException(
+                    "References passed to the AreaReference must be contiguous, " +
+                    "use generateContiguous(ref) if you have non-contiguous references");
         }
 
-        String[] refs = seperateAreaRefs(reference);
-        dim = refs.length;
-        cells = new CellReference[dim];
-        for (int i=0;i<dim;i++) {
-            cells[i]=new CellReference(refs[i]);
+        String[] parts = separateAreaRefs(reference);
+        _firstCell = new CellReference(parts[0]);
+        
+        if(parts.length == 2) {
+            _lastCell = new CellReference(parts[1]);
+            _isSingleCell = false;
+        } else {
+            _lastCell = _firstCell;
+            _isSingleCell = true;
         }
     }
 
@@ -73,75 +87,163 @@
         return (AreaReference[])refs.toArray(new AreaReference[refs.size()]);
     }
 
-    //not sure if we need to be flexible here!
-    /** return the dimensions of this area
-     **/
-    public int getDim() {
-        return dim;
-    }
-    /** 
-     * Return the cell references that define this area
-     * (i.e. the two corners) 
+    /**
+     * @return <code>false</code> if this area reference involves more than one cell
+     */
+    public boolean isSingleCell() {
+        return _isSingleCell;
+    }
+    
+    /**
+     * @return the first cell reference which defines this area. Usually this cell is in the upper
+     * left corner of the area (but this is not a requirement).
      */
-    public CellReference[] getCells() {
-        return cells;
+   public CellReference getFirstCell() {
+        return _firstCell;
+    }
+    
+    /**
+     * Note - if this area reference refers to a single cell, the return value of this method will
+     * be identical to that of <tt>getFirstCell()</tt>
+     * @return the second cell reference which defines this area.  For multi-cell areas, this is 
+     * cell diagonally opposite the 'first cell'.  Usually this cell is in the lower right corner 
+     * of the area (but this is not a requirement).
+     */
+    public CellReference getLastCell() {
+        return _lastCell;
     }
     /**
      * Returns a reference to every cell covered by this area
      */
     public CellReference[] getAllReferencedCells() {
     	// Special case for single cell reference
-    	if(cells.length == 1) {
-    		return cells;
+    	if(_isSingleCell) {
+    		return  new CellReference[] { _firstCell, };
     	}
+ 
     	// Interpolate between the two
-    	int minRow = Math.min(cells[0].getRow(), cells[1].getRow());
-    	int maxRow = Math.max(cells[0].getRow(), cells[1].getRow());
-    	int minCol = Math.min(cells[0].getCol(), cells[1].getCol());
-    	int maxCol = Math.max(cells[0].getCol(), cells[1].getCol());
+        int minRow = Math.min(_firstCell.getRow(), _lastCell.getRow());
+    	int maxRow = Math.max(_firstCell.getRow(), _lastCell.getRow());
+    	int minCol = Math.min(_firstCell.getCol(), _lastCell.getCol());
+    	int maxCol = Math.max(_firstCell.getCol(), _lastCell.getCol());
+        String sheetName = _firstCell.getSheetName();
     	
     	ArrayList refs = new ArrayList();
     	for(int row=minRow; row<=maxRow; row++) {
     		for(int col=minCol; col<=maxCol; col++) {
-    			CellReference ref = new CellReference(row, col, cells[0].isRowAbsolute(), cells[0].isColAbsolute());
-    			ref.setSheetName(cells[0].getSheetName());
+    			CellReference ref = new CellReference(sheetName, row, col, _firstCell.isRowAbsolute(), _firstCell.isColAbsolute());
     			refs.add(ref);
     		}
     	}
     	return (CellReference[])refs.toArray(new CellReference[refs.size()]);
     }
 
-    public String toString() {
-        StringBuffer retval = new StringBuffer();
-        for (int i=0;i<dim;i++){
-            retval.append(':');
-            retval.append(cells[i].toString());
+    /**
+     *  Example return values:
+     *    <table border="0" cellpadding="1" cellspacing="0" summary="Example return values">
+     *      <tr><th align='left'>Result</th><th align='left'>Comment</th></tr>
+     *      <tr><td>A1:A1</td><td>Single cell area reference without sheet</td></tr>
+     *      <tr><td>A1:$C$1</td><td>Multi-cell area reference without sheet</td></tr>
+     *      <tr><td>Sheet1!A$1:B4</td><td>Standard sheet name</td></tr>
+     *      <tr><td>'O''Brien''s Sales'!B5:C6'&nbsp;</td><td>Sheet name with special characters</td></tr>
+     *    </table>
+     * @return the text representation of this area reference as it would appear in a formula.
+     */
+    public String formatAsString() {
+        StringBuffer sb = new StringBuffer(32);
+        sb.append(_firstCell.formatAsString());
+        if(!_isSingleCell) {
+            sb.append(CELL_DELIMITER);
+            if(_lastCell.getSheetName() == null) {
+                sb.append(_lastCell.formatAsString());
+            } else {
+                // don't want to include the sheet name twice
+                _lastCell.appendCellReference(sb);
+            }
         }
-        retval.deleteCharAt(0);
-        return retval.toString();
+        return sb.toString();
+    }
+    public String toString() {
+        StringBuffer sb = new StringBuffer(64);
+        sb.append(getClass().getName()).append(" [");
+        sb.append(formatAsString());
+        sb.append("]");
+        return sb.toString();
     }
 
     /**
-     * seperates Area refs in two parts and returns them as seperate elements in a
-     * String array
-     */
-    private String[] seperateAreaRefs(String reference) {
-        String[] retval = null;
-
-        int length = reference.length();
-
-        int loc = reference.indexOf(':',0);
-        if(loc == -1){
-           retval = new String[1];
-           retval[0] = reference;
-        }
-        else{
-           retval = new String[2];
-           int sheetStart = reference.indexOf("!");
-
-           retval[0] = reference.substring(0, sheetStart+1) + reference.substring(sheetStart + 1,loc);
-           retval[1] = reference.substring(0, sheetStart+1) + reference.substring(loc+1);
-        }
-        return retval;
+     * Separates Area refs in two parts and returns them as separate elements in a String array,
+     * each qualified with the sheet name (if present)
+     * 
+     * @return array with one or two elements. never <code>null</code>
+     */
+    private static String[] separateAreaRefs(String reference) {
+        // TODO - refactor cell reference parsing logic to one place.
+        // Current known incarnations: 
+        //   FormulaParser.GetName()
+        //   CellReference.separateRefParts() 
+        //   AreaReference.separateAreaRefs() (here)
+        //   SheetNameFormatter.format() (inverse)
+        
+        
+        int len = reference.length();
+        int delimiterPos = -1;
+        boolean insideDelimitedName = false;
+        for(int i=0; i<len; i++) {
+            switch(reference.charAt(i)) {
+                case CELL_DELIMITER:
+                    if(!insideDelimitedName) {
+                        if(delimiterPos >=0) {
+                            throw new IllegalArgumentException("More than one cell delimiter '" 
+                                    + CELL_DELIMITER + "' appears in area reference '" + reference + "'");
+                        }
+                        delimiterPos = i;
+                    }
+                default:
+                    continue;
+                case SPECIAL_NAME_DELIMITER:
+                    // fall through
+            }
+            if(!insideDelimitedName) {
+                insideDelimitedName = true;
+                continue;
+            }
+            
+            if(i >= len-1) {
+                // reference ends with the delimited name. 
+                // Assume names like: "Sheet1!'A1'" are never legal.
+                throw new IllegalArgumentException("Area reference '" + reference 
+                        + "' ends with special name delimiter '"  + SPECIAL_NAME_DELIMITER + "'");
+            }
+            if(reference.charAt(i+1) == SPECIAL_NAME_DELIMITER) {
+                // two consecutive quotes is the escape sequence for a single one
+                i++; // skip this and keep parsing the special name
+            } else {
+                // this is the end of the delimited name
+                insideDelimitedName = false;
+            }
+        }
+        if(delimiterPos < 0) {
+            return new String[] { reference, };
+        }
+
+        String partA = reference.substring(0, delimiterPos);
+        String partB = reference.substring(delimiterPos+1);
+        if(partB.indexOf(SHEET_NAME_DELIMITER) >=0) {
+            // TODO - are references like "Sheet1!A1:Sheet1:B2" ever valid?  
+            // FormulaParser has code to handle that.
+            
+            throw new RuntimeException("Unexpected " + SHEET_NAME_DELIMITER 
+                    + " in second cell reference of '" + reference + "'");
+        }
+        
+        int plingPos = partA.lastIndexOf(SHEET_NAME_DELIMITER);
+        if(plingPos < 0) {
+            return new String [] { partA, partB, };
+        }
+        
+        String sheetName = partA.substring(0, plingPos + 1); // +1 to include delimiter
+        
+        return new String [] { partA, sheetName + partB, };
     }
 }

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=628033&r1=628032&r2=628033&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 04:04:42 2008
@@ -15,75 +15,91 @@
    limitations under the License.
 ==================================================================== */
 
-
 package org.apache.poi.hssf.util;
 
+import org.apache.poi.hssf.record.formula.SheetNameFormatter;
+
 /**
  *
  * @author  Avik Sengupta
  * @author  Dennis Doubleday (patch to seperateRowColumns())
  */
-public class CellReference {
+public final class CellReference {
+    /** The character ($) that signifies a row or column value is absolute instead of relative */ 
+    private static final char ABSOLUTE_REFERENCE_MARKER = '$';
+    /** The character (!) that separates sheet names from cell references */ 
+    private static final char SHEET_NAME_DELIMITER = '!';
+    /** The character (') used to quote sheet names when they contain special characters */
+    private static final char SPECIAL_NAME_DELIMITER = '\'';
+    
 
-    /** Creates new CellReference */
-    private int row;
-    private int col;
-    private String sheetName;
-    private boolean rowAbs;
-    private boolean colAbs;
+    private final int _rowIndex;
+    private final int _colIndex;
+    private final String _sheetName;
+    private final boolean _isRowAbs;
+    private final boolean _isColAbs;
 
+    /**
+     * Create an cell ref from a string representation.  Sheet names containing special characters should be
+     * delimited and escaped as per normal syntax rules for formulas.
+     */
     public CellReference(String cellRef) {
         String[] parts = separateRefParts(cellRef);
-        sheetName = parts[0];
-        String ref = parts[1]; 
-        if ((ref == null)||("".equals(ref)))
-        	throw new IllegalArgumentException("Invalid Formula cell reference: '"+cellRef+"'");
-        if (ref.charAt(0) == '$') {
-            colAbs=true;
-            ref=ref.substring(1);
-        }
-        col = convertColStringToNum(ref);
-        ref=parts[2];
-        if ((ref == null)||("".equals(ref)))
-        	throw new IllegalArgumentException("Invalid Formula cell reference: '"+cellRef+"'");
-        if (ref.charAt(0) == '$') {
-            rowAbs=true;
-            ref=ref.substring(1);
+        _sheetName = parts[0];
+        String colRef = parts[1]; 
+        if (colRef.length() < 1) {
+            throw new IllegalArgumentException("Invalid Formula cell reference: '"+cellRef+"'");
         }
-        row = Integer.parseInt(ref)-1;
-    }
-
-    public CellReference(int pRow, int pCol) {
-        this(pRow,pCol,false,false);
+        _isColAbs = colRef.charAt(0) == '$';
+        if (_isColAbs) {
+            colRef=colRef.substring(1);
+        }
+        _colIndex = convertColStringToNum(colRef);
+        
+        String rowRef=parts[2];
+        if (rowRef.length() < 1) {
+            throw new IllegalArgumentException("Invalid Formula cell reference: '"+cellRef+"'");
+        }
+        _isRowAbs = rowRef.charAt(0) == '$';
+        if (_isRowAbs) {
+            rowRef=rowRef.substring(1);
+        }
+        _rowIndex = Integer.parseInt(rowRef)-1; // -1 to convert 1-based to zero-based
     }
 
     public CellReference(int pRow, int pCol, boolean pAbsRow, boolean pAbsCol) {
-        row=pRow;col=pCol;
-        rowAbs = pAbsRow;
-        colAbs=pAbsCol;
-
+        this(null, pRow, pCol, pAbsRow, pAbsCol);
     }
-
-    public int getRow(){return row;}
-    public short getCol(){return (short) col;}
-    public boolean isRowAbsolute(){return rowAbs;}
-    public boolean isColAbsolute(){return colAbs;}
-    public String getSheetName(){return sheetName;}
-    
-    protected void setSheetName(String sheetName) {
-    	this.sheetName = sheetName;
+    public CellReference(String pSheetName, int pRow, int pCol, boolean pAbsRow, boolean pAbsCol) {
+        _sheetName = pSheetName;
+        _rowIndex=pRow;
+        _colIndex=pCol;
+        _isRowAbs = pAbsRow;
+        _isColAbs=pAbsCol;
     }
 
+    public int getRow(){return _rowIndex;}
+    public short getCol(){return (short) _colIndex;}
+    public boolean isRowAbsolute(){return _isRowAbs;}
+    public boolean isColAbsolute(){return _isColAbs;}
+    /**
+      * @return possibly <code>null</code> if this is a 2D reference.  Special characters are not
+      * escaped or delimited
+      */
+    public String getSheetName(){
+        return _sheetName;
+    }
+    
     /**
      * takes in a column reference portion of a CellRef and converts it from
      * ALPHA-26 number format to 0-based base 10.
      */
     private int convertColStringToNum(String ref) {
-        int len = ref.length();
+        int lastIx = ref.length()-1;
         int retval=0;
         int pos = 0;
 
-        for (int k = ref.length()-1; k > -1; k--) {
+        for (int k = lastIx; k > -1; k--) {
             char thechar = ref.charAt(k);
             if ( pos == 0) {
                 retval += (Character.getNumericValue(thechar)-9);
@@ -97,36 +113,78 @@
 
 
     /**
-     * Seperates the row from the columns and returns an array.  Element in
-     * position one is the substring containing the columns still in ALPHA-26
-     * number format.
-     */
-    private String[] separateRefParts(String reference) {
-
-        // Look for end of sheet name. This will either set
-        // start to 0 (if no sheet name present) or the
-        // index after the sheet reference ends.
-        String retval[] = new String[3];
-
-        int start = reference.indexOf("!");
-        if (start != -1) retval[0] = reference.substring(0, start);
-        start += 1;
+     * Separates the row from the columns and returns an array of three Strings.  The first element
+     * is the sheet name. Only the first element may be null.  The second element in is the column 
+     * name still in ALPHA-26 number format.  The third element is the row.
+     */
+    private static String[] separateRefParts(String reference) {
+        
+        int plingPos = reference.lastIndexOf(SHEET_NAME_DELIMITER);
+        String sheetName = parseSheetName(reference, plingPos);
+        int start = plingPos+1;
 
         int length = reference.length();
 
 
-        char[] chars = reference.toCharArray();
         int loc = start;
-        if (chars[loc]=='$') loc++;
-        for (; loc < chars.length; loc++) {
-            if (Character.isDigit(chars[loc]) || chars[loc] == '$') {
+        // skip initial dollars 
+        if (reference.charAt(loc)==ABSOLUTE_REFERENCE_MARKER) {
+            loc++;
+        }
+        // step over column name chars until first digit (or dollars) for row number.
+        for (; loc < length; loc++) {
+            char ch = reference.charAt(loc);
+            if (Character.isDigit(ch) || ch == ABSOLUTE_REFERENCE_MARKER) {
                 break;
             }
         }
+        return new String[] {
+           sheetName,
+           reference.substring(start,loc),
+           reference.substring(loc),
+        };
+    }
+
+    private static String parseSheetName(String reference, int indexOfSheetNameDelimiter) {
+        if(indexOfSheetNameDelimiter < 0) {
+            return null;
+        }
+        
+        boolean isQuoted = reference.charAt(0) == SPECIAL_NAME_DELIMITER;
+        if(!isQuoted) {
+            return reference.substring(0, indexOfSheetNameDelimiter);
+        }
+        int lastQuotePos = indexOfSheetNameDelimiter-1;
+        if(reference.charAt(lastQuotePos) != SPECIAL_NAME_DELIMITER) {
+            throw new RuntimeException("Mismatched quotes: (" + reference + ")");
+        }
 
-        retval[1] = reference.substring(start,loc);
-        retval[2] = reference.substring(loc);
-        return retval;
+        // TODO - refactor cell reference parsing logic to one place.
+        // Current known incarnations: 
+        //   FormulaParser.GetName()
+        //   CellReference.parseSheetName() (here)
+        //   AreaReference.separateAreaRefs() 
+        //   SheetNameFormatter.format() (inverse)
+        
+        StringBuffer sb = new StringBuffer(indexOfSheetNameDelimiter);
+        
+        for(int i=1; i<lastQuotePos; i++) { // Note boundaries - skip outer quotes
+            char ch = reference.charAt(i);
+            if(ch != SPECIAL_NAME_DELIMITER) {
+                sb.append(ch);
+                continue;
+            }
+            if(i < lastQuotePos) {
+                if(reference.charAt(i+1) == SPECIAL_NAME_DELIMITER) {
+                    // two consecutive quotes is the escape sequence for a single one
+                    i++; // skip this and keep parsing the special name
+                    sb.append(ch);
+                    continue;
+                }
+            }
+            throw new RuntimeException("Bad sheet name quote escaping: (" + reference + ")");
+        }
+        return sb.toString();
     }
 
     /**
@@ -148,14 +206,46 @@
         return retval;
     }
 
-
+    /**
+     *  Example return values:
+     *    <table border="0" cellpadding="1" cellspacing="0" summary="Example return values">
+     *      <tr><th align='left'>Result</th><th align='left'>Comment</th></tr>
+     *      <tr><td>A1</td><td>Cell reference without sheet</td></tr>
+     *      <tr><td>Sheet1!A1</td><td>Standard sheet name</td></tr>
+     *      <tr><td>'O''Brien''s Sales'!A1'&nbsp;</td><td>Sheet name with special characters</td></tr>
+     *    </table>
+     * @return the text representation of this cell reference as it would appear in a formula.
+     */
+    public String formatAsString() {
+        StringBuffer sb = new StringBuffer(32);
+        if(_sheetName != null) {
+            SheetNameFormatter.appendFormat(sb, _sheetName);
+            sb.append(SHEET_NAME_DELIMITER);
+        }
+        appendCellReference(sb);
+        return sb.toString();
+    }
+    
     public String toString() {
-        StringBuffer retval = new StringBuffer();
-        retval.append( (colAbs)?"$":"");
-        retval.append( convertNumToColString(col));
-        retval.append((rowAbs)?"$":"");
-        retval.append(row+1);
+        StringBuffer sb = new StringBuffer(64);
+        sb.append(getClass().getName()).append(" [");
+        sb.append(formatAsString());
+        sb.append("]");
+        return sb.toString();
+    }
 
-    return retval.toString();
+    /**
+     * Appends cell reference with '$' markers for absolute values as required.
+     * Sheet name is not included.
+     */
+    /* package */ void appendCellReference(StringBuffer sb) {
+        if(_isColAbs) {
+            sb.append(ABSOLUTE_REFERENCE_MARKER);
+        }
+        sb.append( convertNumToColString(_colIndex));
+        if(_isRowAbs) {
+            sb.append(ABSOLUTE_REFERENCE_MARKER);
+        }
+        sb.append(_rowIndex+1);
     }
 }

Modified: poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestBug42464.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestBug42464.java?rev=628033&r1=628032&r2=628033&view=diff
==============================================================================
--- poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestBug42464.java (original)
+++ poi/trunk/src/scratchpad/testcases/org/apache/poi/hssf/usermodel/TestBug42464.java Fri Feb 15 04:04:42 2008
@@ -21,14 +21,14 @@
 import java.util.Iterator;
 import java.util.List;
 
+import junit.framework.TestCase;
+
 import org.apache.poi.hssf.record.FormulaRecord;
 import org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate;
-import org.apache.poi.hssf.record.formula.ExpPtg;
+import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.CellValue;
 import org.apache.poi.hssf.util.CellReference;
 
-import junit.framework.TestCase;
-
-public class TestBug42464 extends TestCase {
+public final class TestBug42464 extends TestCase {
 	String dirname;
 
 	protected void setUp() throws Exception {
@@ -68,26 +68,27 @@
 		Iterator it = row.cellIterator();
 		while(it.hasNext()) {
 			HSSFCell cell = (HSSFCell)it.next();
-			if(cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
-				FormulaRecordAggregate record = (FormulaRecordAggregate)
-					cell.getCellValueRecord();
-				FormulaRecord r = record.getFormulaRecord();
-				List ptgs = r.getParsedExpression();
-				
-				String cellRef = (new CellReference(row.getRowNum(), cell.getCellNum())).toString();
-				if(cellRef.equals("BP24")) {
-					System.out.print(cellRef);
-					System.out.println(" - has " + r.getNumberOfExpressionTokens() + " ptgs over " + r.getExpressionLength()  + " tokens:");
-					for(int i=0; i<ptgs.size(); i++) {
-						String c = ptgs.get(i).getClass().toString();
-						System.out.println("\t" + c.substring(c.lastIndexOf('.')+1) );
-					}
-					System.out.println("-> " + cell.getCellFormula());
+			if(cell.getCellType() != HSSFCell.CELL_TYPE_FORMULA) {
+			    continue;
+			}
+			FormulaRecordAggregate record = (FormulaRecordAggregate) cell.getCellValueRecord();
+			FormulaRecord r = record.getFormulaRecord();
+			List ptgs = r.getParsedExpression();
+			
+			String cellRef = new CellReference(row.getRowNum(), cell.getCellNum(), false, false).formatAsString();
+			if(false && cellRef.equals("BP24")) { // TODO - replace System.out.println()s with asserts
+				System.out.print(cellRef);
+				System.out.println(" - has " + r.getNumberOfExpressionTokens() 
+				        + " ptgs over " + r.getExpressionLength()  + " tokens:");
+				for(int i=0; i<ptgs.size(); i++) {
+					String c = ptgs.get(i).getClass().toString();
+					System.out.println("\t" + c.substring(c.lastIndexOf('.')+1) );
 				}
-				
-				eval.evaluate(cell);
-				
+				System.out.println("-> " + cell.getCellFormula());
 			}
+			
+			CellValue evalResult = eval.evaluate(cell);
+			assertNotNull(evalResult);
 		}
 	}
 }

Modified: poi/trunk/src/testcases/org/apache/poi/hssf/HSSFTests.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/HSSFTests.java?rev=628033&r1=628032&r2=628033&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/HSSFTests.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/HSSFTests.java Fri Feb 15 04:04:42 2008
@@ -100,11 +100,7 @@
 import org.apache.poi.hssf.usermodel.TestSanityChecker;
 import org.apache.poi.hssf.usermodel.TestSheetShiftRows;
 import org.apache.poi.hssf.usermodel.TestWorkbook;
-import org.apache.poi.hssf.util.TestAreaReference;
-import org.apache.poi.hssf.util.TestCellReference;
-import org.apache.poi.hssf.util.TestRKUtil;
-import org.apache.poi.hssf.util.TestRangeAddress;
-import org.apache.poi.hssf.util.TestSheetReferences;
+import org.apache.poi.hssf.util.AllHSSFUtilTests;
 
 /**
  * Test Suite for running just HSSF tests.  Mostly
@@ -202,11 +198,7 @@
         suite.addTest(new TestSuite(TestUnitsRecord.class));
         suite.addTest(new TestSuite(TestValueRangeRecord.class));
         suite.addTest(new TestSuite(TestRowRecordsAggregate.class));
-        suite.addTest(new TestSuite(TestAreaReference.class));
-        suite.addTest(new TestSuite(TestCellReference.class));
-		  suite.addTest(new TestSuite(TestRangeAddress.class));		
-        suite.addTest(new TestSuite(TestRKUtil.class));
-        suite.addTest(new TestSuite(TestSheetReferences.class));
+        suite.addTest(AllHSSFUtilTests.suite());
         
         
         suite.addTest(AllFormulaTests.suite());

Modified: poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestFormulas.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestFormulas.java?rev=628033&r1=628032&r2=628033&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestFormulas.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestFormulas.java Fri Feb 15 04:04:42 2008
@@ -302,10 +302,10 @@
                 }
                 
                 c = r.getCell((short) y);
-                CellReference cr= new CellReference(refx1,refy1);
-                ref=cr.toString();
-                cr=new CellReference(refx2,refy2);
-                ref2=cr.toString();
+                CellReference cr= new CellReference(refx1,refy1, false, false);
+                ref=cr.formatAsString();
+                cr=new CellReference(refx2,refy2, false, false);
+                ref2=cr.formatAsString();
 
                 c = r.createCell((short) y);
                 c.setCellFormula("" + ref + operator + ref2);
@@ -379,10 +379,10 @@
                 }
 
                 c = r.getCell((short) y);
-                CellReference cr= new CellReference(refx1,refy1);
-                ref=cr.toString();
-                cr=new CellReference(refx2,refy2);
-                ref2=cr.toString();
+                CellReference cr= new CellReference(refx1, refy1, false, false);
+                ref=cr.formatAsString();
+                cr=new CellReference(refx2,refy2, false, false);
+                ref2=cr.formatAsString();
                 
                 
                 assertTrue("loop Formula is as expected "+ref+operator+ref2+"!="+c.getCellFormula(),(

Modified: poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestNamedRange.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestNamedRange.java?rev=628033&r1=628032&r2=628033&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestNamedRange.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/usermodel/TestNamedRange.java Fri Feb 15 04:04:42 2008
@@ -578,18 +578,16 @@
         
         // retrieve the cell at the named range and test its contents
         AreaReference aref = new AreaReference(aNamedCell.getReference());
-        CellReference[] crefs = aref.getCells();
-        assertNotNull(crefs);
-        assertEquals("Should be exactly 1 cell in the named cell :'" +cellName+"'", 1, crefs.length);
-        for (int i=0, iSize=crefs.length; i<iSize; i++) {
-            CellReference cref = crefs[i];
-            assertNotNull(cref);
-            HSSFSheet s = wb.getSheet(cref.getSheetName());
-            HSSFRow r = sheet.getRow(cref.getRow());
-            HSSFCell c = r.getCell(cref.getCol());
-            String contents = c.getStringCellValue();
-            assertEquals("Contents of cell retrieved by its named reference", contents, cellValue);
-        }
+        assertTrue("Should be exactly 1 cell in the named cell :'" +cellName+"'", aref.isSingleCell());
+        
+        CellReference cref = aref.getFirstCell();
+        assertNotNull(cref);
+        HSSFSheet s = wb.getSheet(cref.getSheetName());
+        assertNotNull(s);
+        HSSFRow r = sheet.getRow(cref.getRow());
+        HSSFCell c = r.getCell(cref.getCol());
+        String contents = c.getRichStringCellValue().getString();
+        assertEquals("Contents of cell retrieved by its named reference", contents, cellValue);
     }
 
     /**

Added: poi/trunk/src/testcases/org/apache/poi/hssf/util/AllHSSFUtilTests.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/util/AllHSSFUtilTests.java?rev=628033&view=auto
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/util/AllHSSFUtilTests.java (added)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/util/AllHSSFUtilTests.java Fri Feb 15 04:04:42 2008
@@ -0,0 +1,39 @@
+/* ====================================================================
+   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.util;
+
+import junit.framework.Test;
+import junit.framework.TestSuite;
+
+/**
+ * Collects all tests for org.apache.poi.hssf.util.
+ * 
+ * @author Josh Micich
+ */
+public class AllHSSFUtilTests {
+	
+	public static Test suite() {
+		TestSuite result = new TestSuite("Tests for org.apache.poi.hssf.util");
+        result.addTestSuite(TestAreaReference.class);
+        result.addTestSuite(TestCellReference.class);
+        result.addTestSuite(TestRangeAddress.class);
+        result.addTestSuite(TestRKUtil.class);
+        result.addTestSuite(TestSheetReferences.class);
+		return result;
+	}
+}

Propchange: poi/trunk/src/testcases/org/apache/poi/hssf/util/AllHSSFUtilTests.java
------------------------------------------------------------------------------
    svn:eol-style = native

Propchange: poi/trunk/src/testcases/org/apache/poi/hssf/util/AllHSSFUtilTests.java
------------------------------------------------------------------------------
    svn:executable = *

Modified: poi/trunk/src/testcases/org/apache/poi/hssf/util/TestAreaReference.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/util/TestAreaReference.java?rev=628033&r1=628032&r2=628033&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/util/TestAreaReference.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/util/TestAreaReference.java Fri Feb 15 04:04:42 2008
@@ -1,4 +1,3 @@
-
 /* ====================================================================
    Licensed to the Apache Software Foundation (ASF) under one or more
    contributor license agreements.  See the NOTICE file distributed with
@@ -29,29 +28,28 @@
 import org.apache.poi.hssf.record.formula.UnionPtg;
 
 import java.io.FileInputStream;
+import java.io.IOException;
 import java.io.InputStream;
 import java.util.List;
 
-public class TestAreaReference extends TestCase {
-     public TestAreaReference(String s) {
-        super(s);
-    }
+public final class TestAreaReference extends TestCase {
+
     public void testAreaRef1() {
         AreaReference ar = new AreaReference("$A$1:$B$2");
-        assertTrue("Two cells expected",ar.getCells().length == 2);
-        CellReference cf = ar.getCells()[0];
+        assertFalse("Two cells expected", ar.isSingleCell());
+        CellReference cf = ar.getFirstCell();
         assertTrue("row is 4",cf.getRow()==0);
         assertTrue("col is 1",cf.getCol()==0);
         assertTrue("row is abs",cf.isRowAbsolute());
         assertTrue("col is abs",cf.isColAbsolute());
-        assertTrue("string is $A$1",cf.toString().equals("$A$1"));
+        assertTrue("string is $A$1",cf.formatAsString().equals("$A$1"));
         
-        cf = ar.getCells()[1];
+        cf = ar.getLastCell();
         assertTrue("row is 4",cf.getRow()==1);
         assertTrue("col is 1",cf.getCol()==1);
         assertTrue("row is abs",cf.isRowAbsolute());
         assertTrue("col is abs",cf.isColAbsolute());
-        assertTrue("string is $B$2",cf.toString().equals("$B$2"));
+        assertTrue("string is $B$2",cf.formatAsString().equals("$B$2"));
         
         CellReference[] refs = ar.getAllReferencedCells();
         assertEquals(4, refs.length);
@@ -78,62 +76,31 @@
      * Reported by Arne.Clauss@gedas.de
      */
     public void testReferenceWithSheet() {
-    	String ref = "Tabelle1!B5";
-		AreaReference myAreaReference = new AreaReference(ref);
-		CellReference[] myCellReference = myAreaReference.getCells();
-
-		assertEquals(1, myCellReference.length);
-		assertNotNull("cell reference not null : "+myCellReference[0]);
-    	assertEquals("Not Column B", (short)1,myCellReference[0].getCol());
-		assertEquals("Not Row 5", 4,myCellReference[0].getRow());
-		assertEquals("Shouldn't be absolute", false, myCellReference[0].isRowAbsolute());
-		assertEquals("Shouldn't be absolute", false, myCellReference[0].isColAbsolute());
-		
-		assertEquals(1, myAreaReference.getAllReferencedCells().length);
-		
-		
-		ref = "Tabelle1!$B$5:$B$7";
-		myAreaReference = new AreaReference(ref);
-		myCellReference = myAreaReference.getCells();
-		assertEquals(2, myCellReference.length);
-		
-		assertEquals("Tabelle1", myCellReference[0].getSheetName());
-		assertEquals(4, myCellReference[0].getRow());
-		assertEquals(1, myCellReference[0].getCol());
-		assertTrue(myCellReference[0].isRowAbsolute());
-		assertTrue(myCellReference[0].isColAbsolute());
-		
-		assertEquals("Tabelle1", myCellReference[1].getSheetName());
-		assertEquals(6, myCellReference[1].getRow());
-		assertEquals(1, myCellReference[1].getCol());
-		assertTrue(myCellReference[1].isRowAbsolute());
-		assertTrue(myCellReference[1].isColAbsolute());
-		
-		// And all that make it up
-		myCellReference = myAreaReference.getAllReferencedCells();
-		assertEquals(3, myCellReference.length);
-		
-		assertEquals("Tabelle1", myCellReference[0].getSheetName());
-		assertEquals(4, myCellReference[0].getRow());
-		assertEquals(1, myCellReference[0].getCol());
-		assertTrue(myCellReference[0].isRowAbsolute());
-		assertTrue(myCellReference[0].isColAbsolute());
-		
-		assertEquals("Tabelle1", myCellReference[1].getSheetName());
-		assertEquals(5, myCellReference[1].getRow());
-		assertEquals(1, myCellReference[1].getCol());
-		assertTrue(myCellReference[1].isRowAbsolute());
-		assertTrue(myCellReference[1].isColAbsolute());
-		
-		assertEquals("Tabelle1", myCellReference[2].getSheetName());
-		assertEquals(6, myCellReference[2].getRow());
-		assertEquals(1, myCellReference[2].getCol());
-		assertTrue(myCellReference[2].isRowAbsolute());
-		assertTrue(myCellReference[2].isColAbsolute());
+        AreaReference ar;
+        
+        ar = new AreaReference("Tabelle1!B5");
+        assertTrue(ar.isSingleCell());
+        TestCellReference.confirmCell(ar.getFirstCell(), "Tabelle1", 4, 1, false, false, "Tabelle1!B5");
+        
+        assertEquals(1, ar.getAllReferencedCells().length);
+        
+        
+        ar = new AreaReference("Tabelle1!$B$5:$B$7");
+        assertFalse(ar.isSingleCell());
+        
+        TestCellReference.confirmCell(ar.getFirstCell(), "Tabelle1", 4, 1, true, true, "Tabelle1!$B$5");
+        TestCellReference.confirmCell(ar.getLastCell(), "Tabelle1", 6, 1, true, true, "Tabelle1!$B$7");
+        
+        // And all that make it up
+        CellReference[] allCells = ar.getAllReferencedCells();
+        assertEquals(3, allCells.length);
+        TestCellReference.confirmCell(allCells[0], "Tabelle1", 4, 1, true, true, "Tabelle1!$B$5");
+        TestCellReference.confirmCell(allCells[1], "Tabelle1", 5, 1, true, true, "Tabelle1!$B$6");
+        TestCellReference.confirmCell(allCells[2], "Tabelle1", 6, 1, true, true, "Tabelle1!$B$7");
     }
 
     private static class HSSFWB extends HSSFWorkbook {
-        private HSSFWB(InputStream in) throws Exception {
+        public HSSFWB(InputStream in) throws IOException {
             super(in);
         }
         public Workbook getWorkbook() {
@@ -176,42 +143,42 @@
 
         refs = AreaReference.generateContiguous(refSimple);
         assertEquals(1, refs.length);
-        assertEquals(1, refs[0].getDim());
-        assertEquals("$C$10", refs[0].toString());
+        assertTrue(refs[0].isSingleCell());
+        assertEquals("$C$10", refs[0].formatAsString());
 
         refs = AreaReference.generateContiguous(ref2D);
         assertEquals(1, refs.length);
-        assertEquals(2, refs[0].getDim());
-        assertEquals("$C$10:$D$11", refs[0].toString());
+        assertFalse(refs[0].isSingleCell());
+        assertEquals("$C$10:$D$11", refs[0].formatAsString());
 
         refs = AreaReference.generateContiguous(refDCSimple);
         assertEquals(3, refs.length);
-        assertEquals(1, refs[0].getDim());
-        assertEquals(1, refs[1].getDim());
-        assertEquals(1, refs[2].getDim());
-        assertEquals("$C$10", refs[0].toString());
-        assertEquals("$D$12", refs[1].toString());
-        assertEquals("$E$14", refs[2].toString());
+        assertTrue(refs[0].isSingleCell());
+        assertTrue(refs[1].isSingleCell());
+        assertTrue(refs[2].isSingleCell());
+        assertEquals("$C$10", refs[0].formatAsString());
+        assertEquals("$D$12", refs[1].formatAsString());
+        assertEquals("$E$14", refs[2].formatAsString());
 
         refs = AreaReference.generateContiguous(refDC2D);
         assertEquals(3, refs.length);
-        assertEquals(2, refs[0].getDim());
-        assertEquals(1, refs[1].getDim());
-        assertEquals(2, refs[2].getDim());
-        assertEquals("$C$10:$C$11", refs[0].toString());
-        assertEquals("$D$12", refs[1].toString());
-        assertEquals("$E$14:$E$20", refs[2].toString());
+        assertFalse(refs[0].isSingleCell());
+        assertTrue(refs[1].isSingleCell());
+        assertFalse(refs[2].isSingleCell());
+        assertEquals("$C$10:$C$11", refs[0].formatAsString());
+        assertEquals("$D$12", refs[1].formatAsString());
+        assertEquals("$E$14:$E$20", refs[2].formatAsString());
 
         refs = AreaReference.generateContiguous(refDC3D);
         assertEquals(2, refs.length);
-        assertEquals(2, refs[0].getDim());
-        assertEquals(2, refs[1].getDim());
-        assertEquals("$C$10:$C$14", refs[0].toString());
-        assertEquals("$D$10:$D$12", refs[1].toString());
-        assertEquals("Tabelle1", refs[0].getCells()[0].getSheetName());
-        assertEquals("Tabelle1", refs[0].getCells()[1].getSheetName());
-        assertEquals("Tabelle1", refs[1].getCells()[0].getSheetName());
-        assertEquals("Tabelle1", refs[1].getCells()[1].getSheetName());
+        assertFalse(refs[0].isSingleCell());
+        assertFalse(refs[0].isSingleCell());
+        assertEquals("Tabelle1!$C$10:$C$14", refs[0].formatAsString());
+        assertEquals("Tabelle1!$D$10:$D$12", refs[1].formatAsString());
+        assertEquals("Tabelle1", refs[0].getFirstCell().getSheetName());
+        assertEquals("Tabelle1", refs[0].getLastCell().getSheetName());
+        assertEquals("Tabelle1", refs[1].getFirstCell().getSheetName());
+        assertEquals("Tabelle1", refs[1].getLastCell().getSheetName());
     }
 
     public void testDiscontinousReference() throws Exception {
@@ -261,22 +228,46 @@
         assertFalse(AreaReference.isContiguous(aNamedCell.getReference()));
         AreaReference[] arefs = AreaReference.generateContiguous(aNamedCell.getReference());
         assertEquals(2, arefs.length);
-        assertEquals(rawRefA, arefs[0].toString());
-        assertEquals(rawRefB, arefs[1].toString());
+        assertEquals(refA, arefs[0].formatAsString());
+        assertEquals(refB, arefs[1].formatAsString());
 
         for(int i=0; i<arefs.length; i++) {
-            CellReference[] crefs = arefs[i].getCells();
-            for (int j=0; j<crefs.length; j++) {
-                // Check it turns into real stuff
-                HSSFSheet s = wb.getSheet(crefs[j].getSheetName());
-                HSSFRow r = s.getRow(crefs[j].getRow());
-                HSSFCell c = r.getCell(crefs[j].getCol());
-            }
+            AreaReference ar = arefs[i];
+            confirmResolveCellRef(wb, ar.getFirstCell());
+            confirmResolveCellRef(wb, ar.getLastCell());
         }
     }
+
+    private static void confirmResolveCellRef(HSSFWorkbook wb, CellReference cref) {
+        HSSFSheet s = wb.getSheet(cref.getSheetName());
+        HSSFRow r = s.getRow(cref.getRow());
+        HSSFCell c = r.getCell(cref.getCol());
+        assertNotNull(c);
+    }
     
-    public static void main(java.lang.String[] args) {
-		junit.textui.TestRunner.run(TestAreaReference.class);
-	}
+    public void testSpecialSheetNames() {
+        AreaReference ar;
+        ar = new AreaReference("'Sheet A'!A1");
+        confirmAreaSheetName(ar, "Sheet A", "'Sheet A'!A1");
+        
+        ar = new AreaReference("'Hey! Look Here!'!A1");
+        confirmAreaSheetName(ar, "Hey! Look Here!", "'Hey! Look Here!'!A1");
+        
+        ar = new AreaReference("'O''Toole'!A1:B2");
+        confirmAreaSheetName(ar, "O'Toole", "'O''Toole'!A1:B2");
+        
+        ar = new AreaReference("'one:many'!A1:B2");
+        confirmAreaSheetName(ar, "one:many", "'one:many'!A1:B2");
+    }
+
+    private static void confirmAreaSheetName(AreaReference ar, String sheetName, String expectedFullText) {
+        CellReference[] cells = ar.getAllReferencedCells();
+        assertEquals(sheetName, cells[0].getSheetName());
+        assertEquals(expectedFullText, ar.formatAsString());
+    }
+    
+    public static void main(String[] args) {
+        junit.textui.TestRunner.run(TestAreaReference.class);
+    }
         
 }

Modified: poi/trunk/src/testcases/org/apache/poi/hssf/util/TestCellReference.java
URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/hssf/util/TestCellReference.java?rev=628033&r1=628032&r2=628033&view=diff
==============================================================================
--- poi/trunk/src/testcases/org/apache/poi/hssf/util/TestCellReference.java (original)
+++ poi/trunk/src/testcases/org/apache/poi/hssf/util/TestCellReference.java Fri Feb 15 04:04:42 2008
@@ -1,4 +1,3 @@
-
 /* ====================================================================
    Licensed to the Apache Software Foundation (ASF) under one or more
    contributor license agreements.  See the NOTICE file distributed with
@@ -22,87 +21,74 @@
 import junit.framework.TestCase;
 
 
-public class TestCellReference extends TestCase {
-    public TestCellReference(String s) {
-        super(s);
-    }
+public final class TestCellReference extends TestCase {
     
     public void testAbsRef1(){
         CellReference cf = new CellReference("$B$5");
-        assertTrue("row is 4",cf.getRow()==4);
-        assertTrue("col is 1",cf.getCol()==1);
-        assertTrue("row is abs",cf.isRowAbsolute());
-        assertTrue("col is abs",cf.isColAbsolute());
-        assertTrue("string is $B$5",cf.toString().equals("$B$5"));
+        confirmCell(cf, null, 4, 1, true, true, "$B$5");
     }
     
     public void  testAbsRef2(){
         CellReference cf = new CellReference(4,1,true,true);
-        assertTrue("row is 4",cf.getRow()==4);
-        assertTrue("col is 1",cf.getCol()==1);
-        assertTrue("row is abs",cf.isRowAbsolute());
-        assertTrue("col is abs",cf.isColAbsolute());
-        assertTrue("string is $B$5",cf.toString().equals("$B$5"));
+        confirmCell(cf, null, 4, 1, true, true, "$B$5");
     }
 
     public void  testAbsRef3(){
         CellReference cf = new CellReference("B$5");
-        assertTrue("row is 4",cf.getRow()==4);
-        assertTrue("col is 1",cf.getCol()==1);
-        assertTrue("row is abs",cf.isRowAbsolute());
-        assertTrue("col is rel",!cf.isColAbsolute());
-        assertTrue("string is B$5",cf.toString().equals("B$5"));
+        confirmCell(cf, null, 4, 1, true, false, "B$5");
     }
     
     public void  testAbsRef4(){
         CellReference cf = new CellReference(4,1,true,false);
-        assertTrue("row is 4",cf.getRow()==4);
-        assertTrue("col is 1",cf.getCol()==1);
-        assertTrue("row is abs",cf.isRowAbsolute());
-        assertTrue("col is rel",!cf.isColAbsolute());
-        assertTrue("string is B$5",cf.toString().equals("B$5"));
+        confirmCell(cf, null, 4, 1, true, false, "B$5");
     }
     
     public void  testAbsRef5(){
         CellReference cf = new CellReference("$B5");
-        assertTrue("row is 4",cf.getRow()==4);
-        assertTrue("col is 1",cf.getCol()==1);
-        assertTrue("row is abs",!cf.isRowAbsolute());
-        assertTrue("col is rel",cf.isColAbsolute());
-        assertTrue("string is B$5",cf.toString().equals("$B5"));
+        confirmCell(cf, null, 4, 1, false, true, "$B5");
     }
     
     public void  testAbsRef6(){
         CellReference cf = new CellReference(4,1,false,true);
-        assertTrue("row is 4",cf.getRow()==4);
-        assertTrue("col is 1",cf.getCol()==1);
-        assertTrue("row is abs",!cf.isRowAbsolute());
-        assertTrue("col is rel",cf.isColAbsolute());
-        assertTrue("string is B$5",cf.toString().equals("$B5"));
+        confirmCell(cf, null, 4, 1, false, true, "$B5");
     }
 
     public void  testAbsRef7(){
         CellReference cf = new CellReference("B5");
-        assertTrue("row is 4",cf.getRow()==4);
-        assertTrue("col is 1",cf.getCol()==1);
-        assertTrue("row is abs",!cf.isRowAbsolute());
-        assertTrue("col is rel",!cf.isColAbsolute());
-        assertTrue("string is B$5",cf.toString().equals("B5"));
+        confirmCell(cf, null, 4, 1, false, false, "B5");
     }
     
     public void  testAbsRef8(){
         CellReference cf = new CellReference(4,1,false,false);
-        assertTrue("row is 4",cf.getRow()==4);
-        assertTrue("col is 1",cf.getCol()==1);
-        assertTrue("row is abs",!cf.isRowAbsolute());
-        assertTrue("col is rel",!cf.isColAbsolute());
-        assertTrue("string is B$5",cf.toString().equals("B5"));
+        confirmCell(cf, null, 4, 1, false, false, "B5");
+    }
+    
+    public void testSpecialSheetNames() {
+        CellReference cf;
+        cf = new CellReference("'profit + loss'!A1");
+        confirmCell(cf, "profit + loss", 0, 0, false, false, "'profit + loss'!A1");
+        
+        cf = new CellReference("'O''Brien''s Sales'!A1");
+        confirmCell(cf, "O'Brien's Sales", 0, 0, false, false, "'O''Brien''s Sales'!A1");
+        
+        cf = new CellReference("'Amazing!'!A1");
+        confirmCell(cf, "Amazing!", 0, 0, false, false, "'Amazing!'!A1");
     }
 
     
+    /* package */ static void confirmCell(CellReference cf, String expSheetName, int expRow, 
+            int expCol, boolean expIsRowAbs, boolean expIsColAbs, String expText) {
+        
+        assertEquals(expSheetName, cf.getSheetName());
+        assertEquals("row index is wrong", expRow, cf.getRow());
+        assertEquals("col index is wrong", expCol, cf.getCol());
+        assertEquals("isRowAbsolute is wrong", expIsRowAbs, cf.isRowAbsolute());
+        assertEquals("isColAbsolute is wrong", expIsColAbs, cf.isColAbsolute());
+        assertEquals("text is wrong", expText, cf.formatAsString());
+    }
+
     public static void main(String [] args) {
         System.out.println("Testing org.apache.poi.hssf.util.TestCellReference");
         junit.textui.TestRunner.run(TestCellReference.class);
     }
-    
 }



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


Mime
View raw message