Merged revisions 699178,699487,699489,699761 via svnmerge from

https://svn.apache.org/repos/asf/poi/trunk

........
  r699178 | josh | 2008-09-25 21:49:20 -0700 (Thu, 25 Sep 2008) | 1 line
  
  Changed HSSFEvaluationWorkbook to avoid re-parsing cell formulas during execution. (working towards fix for bug 45865)
........
  r699487 | josh | 2008-09-26 13:25:45 -0700 (Fri, 26 Sep 2008) | 1 line
  
  Fix formula parser to properly support the range operator. Small fixes to parsing of sheet names and full column references.
........
  r699489 | josh | 2008-09-26 13:32:06 -0700 (Fri, 26 Sep 2008) | 1 line
  
  Code cleanup in junit
........
  r699761 | josh | 2008-09-27 19:04:31 -0700 (Sat, 27 Sep 2008) | 1 line
  
  Bug 45865 - modified Formula Parser/Evaluator to handle cross-worksheet formulas
........


git-svn-id: https://svn.apache.org/repos/asf/poi/branches/ooxml@700234 13f79535-47bb-0310-9956-ffa450edef68

13 files changed, 834 insertions, 381 deletions

diff --git a/src/java/org/apache/poi/ss/formula/CellLocation.java b/src/java/org/apache/poi/ss/formula/CellLocation.java
index d6bb9b7bfb..6857c4bc1b 100644
--- a/src/java/org/apache/poi/ss/formula/CellLocation.java
+++ b/src/java/org/apache/poi/ss/formula/CellLocation.java
@@ -17,25 +17,32 @@
 
 package org.apache.poi.ss.formula;
 
+import org.apache.poi.hssf.util.CellReference;
+
 /**
  * Stores the parameters that identify the evaluation of one cell.<br/>
  */
 final class CellLocation {
 	public static final CellLocation[] EMPTY_ARRAY = { };
 	
+	private final EvaluationWorkbook _book;
 	private final int _sheetIndex;
 	private final int _rowIndex;
 	private final int _columnIndex;
 	private final int _hashCode;
 
-	public CellLocation(int sheetIndex, int rowIndex, int columnIndex) {
+	public CellLocation(EvaluationWorkbook book, int sheetIndex, int rowIndex, int columnIndex) {
 		if (sheetIndex < 0) {
 			throw new IllegalArgumentException("sheetIndex must not be negative");
 		}
+		_book = book;
 		_sheetIndex = sheetIndex;
 		_rowIndex = rowIndex;
 		_columnIndex = columnIndex;
-		_hashCode = sheetIndex + 17 * (rowIndex + 17 * columnIndex);
+		_hashCode = System.identityHashCode(book) + sheetIndex + 17 * (rowIndex + 17 * columnIndex);
+	}
+	public Object getBook() {
+		return _book;
 	}
 	public int getSheetIndex() {
 		return _sheetIndex;
@@ -49,15 +56,18 @@ final class CellLocation {
 
 	public boolean equals(Object obj) {
 		CellLocation other = (CellLocation) obj;
-		if (getSheetIndex() != other.getSheetIndex()) {
-			return false;
-		}
 		if (getRowIndex() != other.getRowIndex()) {
 			return false;
 		}
 		if (getColumnIndex() != other.getColumnIndex()) {
 			return false;
 		}
+		if (getSheetIndex() != other.getSheetIndex()) {
+			return false;
+		}
+		if (getBook() != other.getBook()) {
+			return false;
+		}
 		return true;
 	}
 	public int hashCode() {
@@ -68,7 +78,8 @@ final class CellLocation {
 	 * @return human readable string for debug purposes
 	 */
 	public String formatAsString() {
-		return  "ShIx=" + getSheetIndex() + " R=" + getRowIndex() + " C=" + getColumnIndex();
+		CellReference cr = new CellReference(_rowIndex, _columnIndex, false, false);
+		return  "ShIx=" + getSheetIndex() + " " + cr.formatAsString();
 	}
 
 	public String toString() {
diff --git a/src/java/org/apache/poi/ss/formula/CollaboratingWorkbooksEnvironment.java b/src/java/org/apache/poi/ss/formula/CollaboratingWorkbooksEnvironment.java
new file mode 100644
index 0000000000..c62d2f182d
--- /dev/null
+++ b/src/java/org/apache/poi/ss/formula/CollaboratingWorkbooksEnvironment.java
@@ -0,0 +1,155 @@
+/* ====================================================================
+   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.ss.formula;
+
+import java.util.Collections;
+import java.util.HashMap;
+import java.util.HashSet;
+import java.util.IdentityHashMap;
+import java.util.Iterator;
+import java.util.Map;
+import java.util.Set;
+
+
+/**
+ * Manages a collection of {@link WorkbookEvaluator}s, in order to support evaluation of formulas
+ * across spreadsheets.<p/>
+ *
+ * For POI internal use only
+ *
+ * @author Josh Micich
+ */
+public final class CollaboratingWorkbooksEnvironment {
+	
+	public static final CollaboratingWorkbooksEnvironment EMPTY = new CollaboratingWorkbooksEnvironment();
+	
+	private final Map _evaluatorsByName;
+	private final WorkbookEvaluator[] _evaluators;
+
+	private boolean _unhooked;
+	private CollaboratingWorkbooksEnvironment() {
+		_evaluatorsByName = Collections.EMPTY_MAP;
+		_evaluators = new WorkbookEvaluator[0];
+	}
+	public static void setup(String[] workbookNames, WorkbookEvaluator[] evaluators) {
+		int nItems = workbookNames.length;
+		if (evaluators.length != nItems) {
+			throw new IllegalArgumentException("Number of workbook names is " + nItems 
+					+ " but number of evaluators is " + evaluators.length);
+		}
+		if (nItems < 1) {
+			throw new IllegalArgumentException("Must provide at least one collaborating worbook");
+		}
+		new CollaboratingWorkbooksEnvironment(workbookNames, evaluators, nItems);
+	}
+
+	private CollaboratingWorkbooksEnvironment(String[] workbookNames, WorkbookEvaluator[] evaluators, int nItems) {
+		Map m = new HashMap(nItems * 3 / 2);
+		IdentityHashMap uniqueEvals = new IdentityHashMap(nItems * 3 / 2);
+		for(int i=0; i<nItems; i++) {
+			String wbName = workbookNames[i];
+			WorkbookEvaluator wbEval = evaluators[i];
+			if (m.containsKey(wbName)) {
+				throw new IllegalArgumentException("Duplicate workbook name '" + wbName + "'");
+			}
+			if (uniqueEvals.containsKey(wbEval)) {
+				String msg = "Attempted to register same workbook under names '"
+					+ uniqueEvals.get(wbEval) + "' and '" + wbName + "'";
+				throw new IllegalArgumentException(msg);
+			}
+			uniqueEvals.put(wbEval, wbName);
+			m.put(wbName, wbEval);
+		}
+		unhookOldEnvironments(evaluators);
+		hookNewEnvironment(evaluators, this);
+		_unhooked = false;
+		_evaluators = evaluators;
+		_evaluatorsByName = m;
+	}
+
+	private static void hookNewEnvironment(WorkbookEvaluator[] evaluators, CollaboratingWorkbooksEnvironment env) {
+		
+		// All evaluators will need to share the same cache.
+		// but the cache takes an optional evaluation listener.
+		int nItems = evaluators.length;
+		IEvaluationListener evalListener = evaluators[0].getEvaluationListener();
+		// make sure that all evaluators have the same listener
+		for(int i=0; i<nItems; i++) {
+			if(evalListener != evaluators[i].getEvaluationListener()) {
+				// This would be very complex to support
+				throw new RuntimeException("Workbook evaluators must all have the same evaluation listener");
+			}
+		}
+		EvaluationCache cache = new EvaluationCache(evalListener);
+		
+		for(int i=0; i<nItems; i++) {
+			evaluators[i].attachToEnvironment(env, cache);
+		}
+		
+	}
+	private void unhookOldEnvironments(WorkbookEvaluator[] evaluators) {
+		Set oldEnvs = new HashSet();
+		for(int i=0; i<evaluators.length; i++) {
+			oldEnvs.add(evaluators[i].getEnvironment());
+		}
+		CollaboratingWorkbooksEnvironment[] oldCWEs = new CollaboratingWorkbooksEnvironment[oldEnvs.size()];
+		oldEnvs.toArray(oldCWEs);
+		for (int i = 0; i < oldCWEs.length; i++) {
+			oldCWEs[i].unhook();
+		}
+	}
+
+	/**
+	 * 
+	 */
+	private void unhook() {
+		if (_evaluators.length < 1) {
+			return;
+		}
+		for (int i = 0; i < _evaluators.length; i++) {
+			_evaluators[i].detachFromEnvironment();
+		}
+		_unhooked = true;
+	}
+
+	public WorkbookEvaluator getWorkbookEvaluator(String workbookName) {
+		if (_unhooked) {
+			throw new IllegalStateException("This environment has been unhooked");
+		}
+		WorkbookEvaluator result = (WorkbookEvaluator) _evaluatorsByName.get(workbookName);
+		if (result == null) {
+			StringBuffer sb = new StringBuffer(256);
+			sb.append("Could not resolve external workbook name '").append(workbookName).append("'.");
+			if (_evaluators.length < 1) {
+				sb.append(" Workbook environment has not been set up.");
+			} else {
+				sb.append(" The following workbook names are valid: (");
+				Iterator i = _evaluatorsByName.keySet().iterator();
+				int count=0;
+				while(i.hasNext()) {
+					if (count++>0) {
+						sb.append(", ");
+					}
+					sb.append("'").append(i.next()).append("'");
+				}
+				sb.append(")");
+			}
+			throw new RuntimeException(sb.toString());
+		}
+		return result;
+	}
+}
diff --git a/src/java/org/apache/poi/ss/formula/EvaluationCache.java b/src/java/org/apache/poi/ss/formula/EvaluationCache.java
index fdc933f6fa..b0c34fd78a 100644
--- a/src/java/org/apache/poi/ss/formula/EvaluationCache.java
+++ b/src/java/org/apache/poi/ss/formula/EvaluationCache.java
@@ -81,13 +81,7 @@ final class EvaluationCache {
 						+ cellLoc.formatAsString());
 			}
 		}
-		if (_evaluationListener == null) {
-			// optimisation - don't bother sorting if there is no listener.
-		} else {
-			// for testing
-			// make order of callbacks to listener more deterministic
-			Arrays.sort(usedCells, CellLocationComparator);
-		}
+		sortCellLocationsForLogging(usedCells);
 		CellCacheEntry entry = getEntry(cellLoc);
 		CellLocation[] consumingFormulaCells = entry.getConsumingCells();
 		CellLocation[] prevUsedCells = entry.getUsedCells();
@@ -110,6 +104,18 @@ final class EvaluationCache {
 		recurseClearCachedFormulaResults(consumingFormulaCells, 0);
 	}
 
+	/**
+	 * This method sorts the supplied cellLocs so that the order of call-backs to the evaluation 
+	 * listener is more deterministic
+	 */
+	private void sortCellLocationsForLogging(CellLocation[] cellLocs) {
+		if (_evaluationListener == null) {
+			// optimisation - don't bother sorting if there is no listener.
+		} else {
+			Arrays.sort(cellLocs, CellLocationComparator);
+		}
+	}
+
 	private void unlinkConsumingCells(CellLocation[] prevUsedCells, CellLocation[] usedCells,
 			CellLocation cellLoc) {
 		if (prevUsedCells == null) {
@@ -149,6 +155,7 @@ final class EvaluationCache {
 	 * @param formulaCells
 	 */
 	private void recurseClearCachedFormulaResults(CellLocation[] formulaCells, int depth) {
+		sortCellLocationsForLogging(formulaCells);
 		int nextDepth = depth+1;
 		for (int i = 0; i < formulaCells.length; i++) {
 			CellLocation fc = formulaCells[i];
@@ -196,6 +203,10 @@ final class EvaluationCache {
 			CellLocation clB = (CellLocation) b;
 			
 			int cmp;
+			cmp = System.identityHashCode(clA.getBook()) - System.identityHashCode(clB.getBook());
+			if (cmp != 0) {
+				return cmp;
+			}
 			cmp = clA.getSheetIndex() - clB.getSheetIndex();
 			if (cmp != 0) {
 				return cmp;
diff --git a/src/java/org/apache/poi/ss/formula/EvaluationWorkbook.java b/src/java/org/apache/poi/ss/formula/EvaluationWorkbook.java
index 02917d82a4..6cf938fe9f 100644
--- a/src/java/org/apache/poi/ss/formula/EvaluationWorkbook.java
+++ b/src/java/org/apache/poi/ss/formula/EvaluationWorkbook.java
@@ -31,12 +31,36 @@ import org.apache.poi.ss.usermodel.Sheet;
  */

 public interface EvaluationWorkbook {

 	String getSheetName(int sheetIndex);

+	/**

+	 * @return -1 if the specified sheet is from a different book

+	 */

 	int getSheetIndex(Sheet sheet);

+	int getSheetIndex(String sheetName);

 

 	Sheet getSheet(int sheetIndex);

 

+	/**

+	 * @return <code>null</code> if externSheetIndex refers to a sheet inside the current workbook

+	 */

+	ExternalSheet getExternalSheet(int externSheetIndex);

 	int convertFromExternSheetIndex(int externSheetIndex);

 	EvaluationName getName(NamePtg namePtg);

 	String resolveNameXText(NameXPtg ptg);

 	Ptg[] getFormulaTokens(Cell cell);

+	

+	class ExternalSheet {

+		private final String _workbookName;

+		private final String _sheetName;

+

+		public ExternalSheet(String workbookName, String sheetName) {

+			_workbookName = workbookName;

+			_sheetName = sheetName;

+		}

+		public String getWorkbookName() {

+			return _workbookName;

+		}

+		public String getSheetName() {

+			return _sheetName;

+		}

+	}

 }

diff --git a/src/java/org/apache/poi/ss/formula/ExternSheetReferenceToken.java b/src/java/org/apache/poi/ss/formula/ExternSheetReferenceToken.java
new file mode 100644
index 0000000000..09262a1320
--- /dev/null
+++ b/src/java/org/apache/poi/ss/formula/ExternSheetReferenceToken.java
@@ -0,0 +1,29 @@
+/* ====================================================================
+   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.ss.formula;
+
+/**
+ * Should be implemented by any {@link Ptg} subclass that needs has an extern sheet index <br/>
+ * 
+ * For POI internal use only
+ * 
+ * @author Josh Micich
+ */
+public interface ExternSheetReferenceToken {
+	int getExternSheetIndex();
+}
diff --git a/src/java/org/apache/poi/ss/formula/FormulaParser.java b/src/java/org/apache/poi/ss/formula/FormulaParser.java
index df2f8c282f..1398399ff4 100644
--- a/src/java/org/apache/poi/ss/formula/FormulaParser.java
+++ b/src/java/org/apache/poi/ss/formula/FormulaParser.java
@@ -49,6 +49,7 @@ import org.apache.poi.hssf.record.formula.ParenthesisPtg;
 import org.apache.poi.hssf.record.formula.PercentPtg;
 import org.apache.poi.hssf.record.formula.PowerPtg;
 import org.apache.poi.hssf.record.formula.Ptg;
+import org.apache.poi.hssf.record.formula.RangePtg;
 import org.apache.poi.hssf.record.formula.Ref3DPtg;
 import org.apache.poi.hssf.record.formula.RefPtg;
 import org.apache.poi.hssf.record.formula.StringPtg;
@@ -60,7 +61,7 @@ import org.apache.poi.hssf.record.formula.function.FunctionMetadataRegistry;
 import org.apache.poi.hssf.usermodel.HSSFErrorConstants;
 import org.apache.poi.hssf.util.AreaReference;
 import org.apache.poi.hssf.util.CellReference;
-import org.apache.poi.hssf.util.CellReference.NameType;
+import org.apache.poi.ss.util.CellReference.NameType;
 
 /**
  * This class parses a formula string into a List of tokens in RPN order.
@@ -81,6 +82,33 @@ import org.apache.poi.hssf.util.CellReference.NameType;
  *  @author Josh Micich
  */
 public final class FormulaParser {
+    private static final class Identifier {
+        private final String _name;
+        private final boolean _isQuoted;
+
+        public Identifier(String name, boolean isQuoted) {
+            _name = name;
+            _isQuoted = isQuoted;
+        }
+        public String getName() {
+            return _name;
+        }
+        public boolean isQuoted() {
+            return _isQuoted;
+        }
+        public String toString() {
+            StringBuffer sb = new StringBuffer(64);
+            sb.append(getClass().getName());
+            sb.append(" [");
+            if (_isQuoted) {
+                sb.append("'").append(_name).append("'");
+            } else {
+                sb.append(_name);
+            }
+            sb.append("]");
+            return sb.toString();
+        }
+    }
 
     /**
      * Specific exception thrown when a supplied formula does not parse properly.<br/>
@@ -176,23 +204,23 @@ public final class FormulaParser {
     }
 
     /** Recognize an Alpha Character */
-    private boolean IsAlpha(char c) {
+    private static boolean IsAlpha(char c) {
         return Character.isLetter(c) || c == '$' || c=='_';
     }
 
     /** Recognize a Decimal Digit */
-    private boolean IsDigit(char c) {
+    private static boolean IsDigit(char c) {
         return Character.isDigit(c);
     }
 
     /** Recognize an Alphanumeric */
-    private boolean  IsAlNum(char c) {
-        return  (IsAlpha(c) || IsDigit(c));
+    private static boolean IsAlNum(char c) {
+        return IsAlpha(c) || IsDigit(c);
     }
 
     /** Recognize White Space */
-    private boolean IsWhite( char c) {
-        return  (c ==' ' || c== TAB);
+    private static boolean IsWhite( char c) {
+        return  c ==' ' || c== TAB;
     }
 
     /** Skip Over Leading White Space */
@@ -213,7 +241,13 @@ public final class FormulaParser {
         }
         GetChar();
     }
-
+    private String parseUnquotedIdentifier() {
+        Identifier iden = parseIdentifier();
+        if (iden.isQuoted()) {
+            throw expected("unquoted identifier");
+        }
+        return iden.getName();
+    }
     /**
      * Parses a sheet name, named range name, or simple cell reference.<br/>
      * Note - identifiers in Excel can contain dots, so this method may return a String
@@ -221,18 +255,17 @@ public final class FormulaParser {
      * may return a value like "A1..B2", in which case the caller must convert it to
      * an area reference like "A1:B2"
      */
-    private String parseIdentifier() {
-        StringBuffer Token = new StringBuffer();
-        if (!IsAlpha(look) && look != '\'') {
+    private Identifier parseIdentifier() {
+        StringBuffer sb = new StringBuffer();
+        if (!IsAlpha(look) && look != '\'' && look != '[') {
             throw expected("Name");
         }
-        if(look == '\'')
-        {
+        boolean isQuoted = look == '\''; 
+        if(isQuoted) {
             Match('\'');
             boolean done = look == '\'';
-            while(!done)
-            {
-                Token.append(look);
+            while(!done) {
+                sb.append(look);
                 GetChar();
                 if(look == '\'')
                 {
@@ -240,17 +273,15 @@ public final class FormulaParser {
                     done = look != '\'';
                 }
             }
-        }
-        else
-        {
+        } else {
             // allow for any sequence of dots and identifier chars
             // special case of two consecutive dots is best treated in the calling code
-            while (IsAlNum(look) || look == '.') {
-                Token.append(look);
+            while (IsAlNum(look) || look == '.' || look == '[' || look == ']') {
+                sb.append(look);
                 GetChar();
             }
         }
-        return Token.toString();
+        return new Identifier(sb.toString(), isQuoted);
     }
 
     /** Get a Number */
@@ -265,72 +296,112 @@ public final class FormulaParser {
     }
 
     private ParseNode parseFunctionReferenceOrName() {
-        String name = parseIdentifier();
+        Identifier iden = parseIdentifier();
         if (look == '('){
             //This is a function
-            return function(name);
+            return function(iden.getName());
         }
-        return new ParseNode(parseNameOrReference(name));
-    }
-
-    private Ptg parseNameOrReference(String name) {
-
-        AreaReference areaRef = parseArea(name);
-        if (areaRef != null) {
-            // will happen if dots are used instead of colon
-            return new AreaPtg(areaRef.formatAsString());
+        if (!iden.isQuoted()) {
+            String name = iden.getName();
+            if (name.equalsIgnoreCase("TRUE") || name.equalsIgnoreCase("FALSE")) {
+                return  new ParseNode(new BoolPtg(name.toUpperCase()));
+            }
         }
+        return parseRangeExpression(iden);
+    }
 
-        if (look == ':' || look == '.') { // this is a AreaReference
+    private ParseNode parseRangeExpression(Identifier iden) {
+        Ptg ptgA = parseNameOrCellRef(iden);
+        if (look == ':') {
             GetChar();
-
-            while (look == '.') { // formulas can have . or .. or ... instead of :
-                GetChar();
+            Identifier iden2 = parseIdentifier();
+            Ptg ptgB = parseNameOrCellRef(iden2);
+            Ptg simplified = reduceRangeExpression(ptgA, ptgB);
+            
+            if (simplified == null) {
+                ParseNode[] children = {
+                    new ParseNode(ptgA),    
+                    new ParseNode(ptgB),
+                };
+                return new ParseNode(RangePtg.instance, children);
             }
+            return new ParseNode(simplified);
+        }
+        return new ParseNode(ptgA);
+    } 
+    
+    /**
+     * 
+     * "A1", "B3" -> "A1:B3"   
+     * "sheet1!A1", "B3" -> "sheet1!A1:B3"
+     * 
+     * @return <code>null</code> if the range expression cannot / shouldn't be reduced.
+     */
+    private static Ptg reduceRangeExpression(Ptg ptgA, Ptg ptgB) {
+        if (!(ptgB instanceof RefPtg)) {
+            // only when second ref is simple 2-D ref can the range 
+            // expression be converted to an area ref
+            return null;
+        }
+        RefPtg refB = (RefPtg) ptgB;
 
-            String first = name;
-            String second = parseIdentifier();
-            return new AreaPtg(first+":"+second);
+        if (ptgA instanceof RefPtg) {
+            RefPtg refA = (RefPtg) ptgA;
+            return new AreaPtg(refA.getRow(), refB.getRow(), refA.getColumn(), refB.getColumn(),
+                    refA.isRowRelative(), refB.isRowRelative(), refA.isColRelative(), refB.isColRelative());
+        }
+        if (ptgA instanceof Ref3DPtg) {
+            Ref3DPtg refA = (Ref3DPtg) ptgA;
+            return new Area3DPtg(refA.getRow(), refB.getRow(), refA.getColumn(), refB.getColumn(),
+                    refA.isRowRelative(), refB.isRowRelative(), refA.isColRelative(), refB.isColRelative(),
+                    refA.getExternSheetIndex());
         }
+        // Note - other operand types (like AreaPtg) which probably can't evaluate 
+        // do not cause validation errors at parse time
+        return null;
+    }
 
+    private Ptg parseNameOrCellRef(Identifier iden) {
+        
         if (look == '!') {
-            Match('!');
-            String sheetName = name;
-            String first = parseIdentifier();
-            int externIdx = book.getExternalSheetIndex(sheetName);
-            areaRef = parseArea(name);
-            if (areaRef != null) {
-                // will happen if dots are used instead of colon
-                return new Area3DPtg(areaRef.formatAsString(), externIdx);
-            }
-            if (look == ':') {
-                Match(':');
-                String second=parseIdentifier();
-                if (look == '!') {
-                    //The sheet name was included in both of the areas. Only really
-                    //need it once
-                    Match('!');
-                    String third=parseIdentifier();
-
-                    if (!sheetName.equals(second))
-                        throw new RuntimeException("Unhandled double sheet reference.");
-
-                    return new Area3DPtg(first+":"+third,externIdx);
-                }
-                return new Area3DPtg(first+":"+second,externIdx);
+            GetChar();
+            // 3-D ref
+            // this code assumes iden is a sheetName
+            // TODO - handle <book name> ! <named range name>
+            int externIdx = getExternalSheetIndex(iden.getName());
+            String secondIden = parseUnquotedIdentifier();
+            AreaReference areaRef = parseArea(secondIden);
+            if (areaRef == null) {
+                return new Ref3DPtg(secondIden, externIdx);
             }
-            return new Ref3DPtg(first, externIdx);
-        }
-        if (name.equalsIgnoreCase("TRUE") || name.equalsIgnoreCase("FALSE")) {
-            return new BoolPtg(name.toUpperCase());
+            // will happen if dots are used instead of colon
+            return new Area3DPtg(areaRef.formatAsString(), externIdx);
         }
 
+        String name = iden.getName();
+        AreaReference areaRef = parseArea(name);
+        if (areaRef != null) {
+            // will happen if dots are used instead of colon
+            return new AreaPtg(areaRef.formatAsString());
+        }
         // This can be either a cell ref or a named range
-        // Try to spot which it is
+
+
         int nameType = CellReference.classifyCellReference(name);
         if (nameType == NameType.CELL) {
             return new RefPtg(name);
         }
+        if (look == ':') {
+            if (nameType == NameType.COLUMN) {
+                GetChar();
+                String secondIden = parseUnquotedIdentifier();
+                if (CellReference.classifyCellReference(secondIden) != NameType.COLUMN) {
+                    throw new FormulaParseException("Expected full column after '" + name 
+                            + ":' but got '" + secondIden + "'");
+                }
+                return new AreaPtg(name + ":" + secondIden);
+            }
+        }
         if (nameType != NameType.NAMED_RANGE) {
             new FormulaParseException("Name '" + name
                 + "' does not look like a cell reference or named range");
@@ -347,6 +418,17 @@ public final class FormulaParser {
                     + name + "' is not a range as expected");
     }
 
+    private int getExternalSheetIndex(String name) {
+        if (name.charAt(0) == '[') {
+            // we have a sheet name qualified with workbook name e.g. '[MyData.xls]Sheet1'
+            int pos = name.lastIndexOf(']'); // safe because sheet names never have ']'
+            String wbName = name.substring(1, pos);
+            String sheetName = name.substring(pos+1);
+            return book.getExternalSheetIndex(wbName, sheetName);
+        }
+        return book.getExternalSheetIndex(name);
+    }
+
     /**
      * @param name an 'identifier' like string (i.e. contains alphanums, and dots)
      * @return <code>null</code> if name cannot be split at a dot
@@ -585,7 +667,7 @@ public final class FormulaParser {
                 Match('}');
                 return arrayNode;
         }
-        if (IsAlpha(look) || look == '\''){
+        if (IsAlpha(look) || look == '\'' || look == '['){
             return parseFunctionReferenceOrName();
         }
         // else - assume number
@@ -662,7 +744,7 @@ public final class FormulaParser {
     }
 
     private Boolean parseBooleanLiteral() {
-        String iden = parseIdentifier();
+        String iden = parseUnquotedIdentifier();
         if ("TRUE".equalsIgnoreCase(iden)) {
             return Boolean.TRUE;
         }
@@ -720,7 +802,7 @@ public final class FormulaParser {
 
     private int parseErrorLiteral() {
         Match('#');
-        String part1 = parseIdentifier().toUpperCase();
+        String part1 = parseUnquotedIdentifier().toUpperCase();
 
         switch(part1.charAt(0)) {
             case 'V':
diff --git a/src/java/org/apache/poi/ss/formula/FormulaParsingWorkbook.java b/src/java/org/apache/poi/ss/formula/FormulaParsingWorkbook.java
index 69431c2c22..e9be7d1d39 100644
--- a/src/java/org/apache/poi/ss/formula/FormulaParsingWorkbook.java
+++ b/src/java/org/apache/poi/ss/formula/FormulaParsingWorkbook.java
@@ -32,6 +32,16 @@ public interface FormulaParsingWorkbook {
 	 */

 	EvaluationName getName(String name);

 

-	int getExternalSheetIndex(String sheetName);

 	NameXPtg getNameXPtg(String name);

+

+	/**

+	 * gets the externSheet index for a sheet from this workbook

+	 */

+	int getExternalSheetIndex(String sheetName);

+	/**

+	 * gets the externSheet index for a sheet from an external workbook

+	 * @param workbookName e.g. "Budget.xls"

+	 * @param sheetName a name of a sheet in that workbook

+	 */

+	int getExternalSheetIndex(String workbookName, String sheetName);

 }

diff --git a/src/java/org/apache/poi/ss/formula/FormulaRenderingWorkbook.java b/src/java/org/apache/poi/ss/formula/FormulaRenderingWorkbook.java
index ac95f4da0f..c9b95f6b1c 100644
--- a/src/java/org/apache/poi/ss/formula/FormulaRenderingWorkbook.java
+++ b/src/java/org/apache/poi/ss/formula/FormulaRenderingWorkbook.java
@@ -19,6 +19,7 @@ package org.apache.poi.ss.formula;
 

 import org.apache.poi.hssf.record.formula.NamePtg;

 import org.apache.poi.hssf.record.formula.NameXPtg;

+import org.apache.poi.ss.formula.EvaluationWorkbook.ExternalSheet;

 

 /**

  * Abstracts a workbook for the purpose of converting formula to text.<br/>

@@ -29,6 +30,10 @@ import org.apache.poi.hssf.record.formula.NameXPtg;
  */

 public interface FormulaRenderingWorkbook {

 

+	/**

+	 * @return <code>null</code> if externSheetIndex refers to a sheet inside the current workbook

+	 */

+	ExternalSheet getExternalSheet(int externSheetIndex);

 	String getSheetNameByExternSheet(int externSheetIndex);

 	String resolveNameXText(NameXPtg nameXPtg);

 	String getNameText(NamePtg namePtg);

diff --git a/src/java/org/apache/poi/ss/formula/OperandClassTransformer.java b/src/java/org/apache/poi/ss/formula/OperandClassTransformer.java
index 79087e4d6d..91226bd53b 100644
--- a/src/java/org/apache/poi/ss/formula/OperandClassTransformer.java
+++ b/src/java/org/apache/poi/ss/formula/OperandClassTransformer.java
@@ -19,6 +19,7 @@ package org.apache.poi.ss.formula;
 
 import org.apache.poi.hssf.record.formula.AbstractFunctionPtg;
 import org.apache.poi.hssf.record.formula.ControlPtg;
+import org.apache.poi.hssf.record.formula.RangePtg;
 import org.apache.poi.hssf.record.formula.ValueOperatorPtg;
 import org.apache.poi.hssf.record.formula.Ptg;
 
@@ -115,6 +116,10 @@ final class OperandClassTransformer {
 			return;
 		}
 		if (children.length > 0) {
+			if (token == RangePtg.instance) {
+				// TODO is any token transformation required under the various ref operators?
+				return;
+			}
 			throw new IllegalStateException("Node should not have any children");
 		}
 
diff --git a/src/java/org/apache/poi/ss/formula/OperationEvaluatorFactory.java b/src/java/org/apache/poi/ss/formula/OperationEvaluatorFactory.java
index eaa57c114f..09c93723f9 100755
--- a/src/java/org/apache/poi/ss/formula/OperationEvaluatorFactory.java
+++ b/src/java/org/apache/poi/ss/formula/OperationEvaluatorFactory.java
@@ -40,6 +40,7 @@ import org.apache.poi.hssf.record.formula.OperationPtg;
 import org.apache.poi.hssf.record.formula.PercentPtg;
 import org.apache.poi.hssf.record.formula.PowerPtg;
 import org.apache.poi.hssf.record.formula.Ptg;
+import org.apache.poi.hssf.record.formula.RangePtg;
 import org.apache.poi.hssf.record.formula.SubtractPtg;
 import org.apache.poi.hssf.record.formula.UnaryMinusPtg;
 import org.apache.poi.hssf.record.formula.UnaryPlusPtg;
@@ -57,6 +58,7 @@ import org.apache.poi.hssf.record.formula.eval.NotEqualEval;
 import org.apache.poi.hssf.record.formula.eval.OperationEval;
 import org.apache.poi.hssf.record.formula.eval.PercentEval;
 import org.apache.poi.hssf.record.formula.eval.PowerEval;
+import org.apache.poi.hssf.record.formula.eval.RangeEval;
 import org.apache.poi.hssf.record.formula.eval.SubtractEval;
 import org.apache.poi.hssf.record.formula.eval.UnaryMinusEval;
 import org.apache.poi.hssf.record.formula.eval.UnaryPlusEval;
@@ -101,6 +103,7 @@ final class OperationEvaluatorFactory {
 		add(m, SubtractPtg.class, SubtractEval.instance);
 		add(m, UnaryMinusPtg.class, UnaryMinusEval.instance);
 		add(m, UnaryPlusPtg.class, UnaryPlusEval.instance);
+		add(m, RangePtg.class, RangeEval.instance);
 		return m;
 	}
 
diff --git a/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java b/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java
index b41e2c2c3f..9c02ca9f9e 100644
--- a/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java
+++ b/src/java/org/apache/poi/ss/formula/WorkbookEvaluator.java
@@ -22,12 +22,18 @@ import java.util.Map;
 import java.util.Stack;
 
 import org.apache.poi.hssf.record.formula.Area3DPtg;
+import org.apache.poi.hssf.record.formula.AreaErrPtg;
 import org.apache.poi.hssf.record.formula.AreaPtg;
+import org.apache.poi.hssf.record.formula.AttrPtg;
 import org.apache.poi.hssf.record.formula.BoolPtg;
 import org.apache.poi.hssf.record.formula.ControlPtg;
+import org.apache.poi.hssf.record.formula.DeletedArea3DPtg;
+import org.apache.poi.hssf.record.formula.DeletedRef3DPtg;
 import org.apache.poi.hssf.record.formula.ErrPtg;
+import org.apache.poi.hssf.record.formula.FuncVarPtg;
 import org.apache.poi.hssf.record.formula.IntPtg;
 import org.apache.poi.hssf.record.formula.MemErrPtg;
+import org.apache.poi.hssf.record.formula.MemFuncPtg;
 import org.apache.poi.hssf.record.formula.MissingArgPtg;
 import org.apache.poi.hssf.record.formula.NamePtg;
 import org.apache.poi.hssf.record.formula.NameXPtg;
@@ -35,6 +41,7 @@ import org.apache.poi.hssf.record.formula.NumberPtg;
 import org.apache.poi.hssf.record.formula.OperationPtg;
 import org.apache.poi.hssf.record.formula.Ptg;
 import org.apache.poi.hssf.record.formula.Ref3DPtg;
+import org.apache.poi.hssf.record.formula.RefErrorPtg;
 import org.apache.poi.hssf.record.formula.RefPtg;
 import org.apache.poi.hssf.record.formula.StringPtg;
 import org.apache.poi.hssf.record.formula.UnionPtg;
@@ -53,6 +60,7 @@ import org.apache.poi.hssf.record.formula.eval.RefEval;
 import org.apache.poi.hssf.record.formula.eval.StringEval;
 import org.apache.poi.hssf.record.formula.eval.ValueEval;
 import org.apache.poi.hssf.util.CellReference;
+import org.apache.poi.ss.formula.EvaluationWorkbook.ExternalSheet;
 import org.apache.poi.ss.usermodel.Cell;
 import org.apache.poi.ss.usermodel.Row;
 import org.apache.poi.ss.usermodel.Sheet;
@@ -68,13 +76,14 @@ import org.apache.poi.ss.usermodel.Sheet;
  *
  * @author Josh Micich
  */
-public class WorkbookEvaluator {
+public final class WorkbookEvaluator {
 
 	private final EvaluationWorkbook _workbook;
-	private final EvaluationCache _cache;
+	private EvaluationCache _cache;
 
 	private final IEvaluationListener _evaluationListener;
 	private final Map _sheetIndexesBySheet;
+	private CollaboratingWorkbooksEnvironment _collaboratingWorkbookEnvironment;
 
 	public WorkbookEvaluator(EvaluationWorkbook workbook) {
 		this (workbook, null);
@@ -84,6 +93,7 @@ public class WorkbookEvaluator {
 		_evaluationListener = evaluationListener;
 		_cache = new EvaluationCache(evaluationListener);
 		_sheetIndexesBySheet = new IdentityHashMap();
+		_collaboratingWorkbookEnvironment = CollaboratingWorkbooksEnvironment.EMPTY;
 	}
 
 	/**
@@ -101,7 +111,22 @@ public class WorkbookEvaluator {
 			System.out.println(s);
 		}
 	}
+	/* package */ void attachToEnvironment(CollaboratingWorkbooksEnvironment collaboratingWorkbooksEnvironment, EvaluationCache cache) {
+		_collaboratingWorkbookEnvironment = collaboratingWorkbooksEnvironment;
+		_cache = cache;
+	}
+	/* package */ CollaboratingWorkbooksEnvironment getEnvironment() {
+		return _collaboratingWorkbookEnvironment;
+	}
 
+	/* package */ void detachFromEnvironment() {
+		_collaboratingWorkbookEnvironment = CollaboratingWorkbooksEnvironment.EMPTY;
+		_cache = new EvaluationCache(_evaluationListener);
+	}
+	/* package */ IEvaluationListener getEvaluationListener() {
+		return _evaluationListener;
+	}
+	 
 	/**
 	 * Should be called whenever there are changes to input cells in the evaluated workbook.
 	 * Failure to call this method after changing cell values will cause incorrect behaviour
@@ -123,7 +148,7 @@ public class WorkbookEvaluator {
 			throw new IllegalArgumentException("value must not be null");
 		}
 		int sheetIndex = getSheetIndex(sheet);
-		_cache.setValue(new CellLocation(sheetIndex, rowIndex, columnIndex), true, CellLocation.EMPTY_ARRAY, value);
+		_cache.setValue(new CellLocation(_workbook, sheetIndex, rowIndex, columnIndex), true, CellLocation.EMPTY_ARRAY, value);
 
 	}
 	/**
@@ -132,13 +157,17 @@ public class WorkbookEvaluator {
 	 */
 	public void notifySetFormula(Sheet sheet, int rowIndex, int columnIndex) {
 		int sheetIndex = getSheetIndex(sheet);
-		_cache.setValue(new CellLocation(sheetIndex, rowIndex, columnIndex), false, CellLocation.EMPTY_ARRAY, null);
+		_cache.setValue(new CellLocation(_workbook, sheetIndex, rowIndex, columnIndex), false, CellLocation.EMPTY_ARRAY, null);
 
 	}
 	private int getSheetIndex(Sheet sheet) {
 		Integer result = (Integer) _sheetIndexesBySheet.get(sheet);
 		if (result == null) {
-			result = new Integer(_workbook.getSheetIndex(sheet));
+			int sheetIndex = _workbook.getSheetIndex(sheet);
+			if (sheetIndex < 0) {
+				throw new RuntimeException("Specified sheet from a different book");
+			}
+			result = new Integer(sheetIndex);
 			_sheetIndexesBySheet.put(sheet, result);
 		}
 		return result.intValue();
@@ -146,7 +175,7 @@ public class WorkbookEvaluator {
 
 	public ValueEval evaluate(Cell srcCell) {
 		int sheetIndex = getSheetIndex(srcCell.getSheet());
-		CellLocation cellLoc = new CellLocation(sheetIndex, srcCell.getRowIndex(), srcCell.getCellNum());
+		CellLocation cellLoc = new CellLocation(_workbook, sheetIndex, srcCell.getRowIndex(), srcCell.getCellNum());
 		return internalEvaluate(srcCell, cellLoc, new EvaluationTracker(_cache));
 	}
 
@@ -181,10 +210,10 @@ public class WorkbookEvaluator {
 				isPlainFormulaCell = false;
 				Ptg[] ptgs = _workbook.getFormulaTokens(srcCell);
 				if(evalListener == null) {
-					result = evaluateCell(sheetIndex, rowIndex, (short)columnIndex, ptgs, tracker);
+					result = evaluateFormula(sheetIndex, rowIndex, (short)columnIndex, ptgs, tracker);
 				} else {
 					evalListener.onStartEvaluate(sheetIndex, rowIndex, columnIndex, ptgs);
-					result = evaluateCell(sheetIndex, rowIndex, (short)columnIndex, ptgs, tracker);
+					result = evaluateFormula(sheetIndex, rowIndex, (short)columnIndex, ptgs, tracker);
 					evalListener.onEndEvaluate(sheetIndex, rowIndex, columnIndex, result);
 				}
 			}
@@ -225,17 +254,31 @@ public class WorkbookEvaluator {
 		}
 		throw new RuntimeException("Unexpected cell type (" + cellType + ")");
 	}
-	private ValueEval evaluateCell(int sheetIndex, int srcRowNum, short srcColNum, Ptg[] ptgs, EvaluationTracker tracker) {
+	// visibility raised for testing
+	/* package */ ValueEval evaluateFormula(int sheetIndex, int srcRowNum, int srcColNum, Ptg[] ptgs, EvaluationTracker tracker) {
 
 		Stack stack = new Stack();
 		for (int i = 0, iSize = ptgs.length; i < iSize; i++) {
 
 			// since we don't know how to handle these yet :(
 			Ptg ptg = ptgs[i];
+			if (ptg instanceof AttrPtg) {
+				AttrPtg attrPtg = (AttrPtg) ptg;
+				if (attrPtg.isSum()) {
+					// Excel prefers to encode 'SUM()' as a tAttr token, but this evaluator
+					// expects the equivalent function token
+					byte nArgs = 1;  // tAttrSum always has 1 parameter
+					ptg = new FuncVarPtg("SUM", nArgs); 
+				}
+			}
 			if (ptg instanceof ControlPtg) {
 				// skip Parentheses, Attr, etc
 				continue;
 			}
+			if (ptg instanceof MemFuncPtg) {
+				// can ignore, rest of tokens for this expression are in OK RPN order
+				continue;
+			}
 			if (ptg instanceof MemErrPtg) { continue; }
 			if (ptg instanceof MissingArgPtg) {
 				// TODO - might need to push BlankEval or MissingArgEval
@@ -289,7 +332,7 @@ public class WorkbookEvaluator {
 	 * @return a <tt>NumberEval</tt>, <tt>StringEval</tt>, <tt>BoolEval</tt>,
 	 *  <tt>BlankEval</tt> or <tt>ErrorEval</tt>. Never <code>null</code>.
 	 */
-	private static ValueEval dereferenceValue(ValueEval evaluationResult, int srcRowNum, short srcColNum) {
+	private static ValueEval dereferenceValue(ValueEval evaluationResult, int srcRowNum, int srcColNum) {
 		if (evaluationResult instanceof RefEval) {
 			RefEval rv = (RefEval) evaluationResult;
 			return rv.getInnerValueEval();
@@ -321,6 +364,20 @@ public class WorkbookEvaluator {
 		}
 		return operation.evaluate(ops, srcRowNum, (short)srcColNum);
 	}
+	private SheetRefEvaluator createExternSheetRefEvaluator(EvaluationTracker tracker,
+			ExternSheetReferenceToken ptg) {
+		int externSheetIndex = ptg.getExternSheetIndex();
+		ExternalSheet externalSheet = _workbook.getExternalSheet(externSheetIndex);
+		if (externalSheet != null) {
+			WorkbookEvaluator otherEvaluator = _collaboratingWorkbookEnvironment.getWorkbookEvaluator(externalSheet.getWorkbookName());
+			EvaluationWorkbook otherBook = otherEvaluator._workbook;
+			int otherSheetIndex = otherBook.getSheetIndex(externalSheet.getSheetName());
+			return new SheetRefEvaluator(otherEvaluator, tracker, otherBook, otherSheetIndex);
+		}
+		int otherSheetIndex = _workbook.convertFromExternSheetIndex(externSheetIndex);
+		return new SheetRefEvaluator(this, tracker, _workbook, otherSheetIndex);
+		
+	}
 
 	/**
 	 * returns an appropriate Eval impl instance for the Ptg. The Ptg must be
@@ -329,6 +386,8 @@ public class WorkbookEvaluator {
 	 * passed here!
 	 */
 	private Eval getEvalForPtg(Ptg ptg, int sheetIndex, EvaluationTracker tracker) {
+		//  consider converting all these (ptg instanceof XxxPtg) expressions to (ptg.getClass() == XxxPtg.class)
+
 		if (ptg instanceof NamePtg) {
 			// named ranges, macro functions
 			NamePtg namePtg = (NamePtg) ptg;
@@ -361,16 +420,18 @@ public class WorkbookEvaluator {
 		if (ptg instanceof ErrPtg) {
 			return ErrorEval.valueOf(((ErrPtg) ptg).getErrorCode());
 		}
+		if (ptg instanceof AreaErrPtg ||ptg instanceof RefErrorPtg 
+				|| ptg instanceof DeletedArea3DPtg || ptg instanceof DeletedRef3DPtg) {
+				return ErrorEval.REF_INVALID;
+		}
 		if (ptg instanceof Ref3DPtg) {
 			Ref3DPtg refPtg = (Ref3DPtg) ptg;
-			int otherSheetIndex = _workbook.convertFromExternSheetIndex(refPtg.getExternSheetIndex());
-			SheetRefEvaluator sre = new SheetRefEvaluator(this, tracker, _workbook, otherSheetIndex);
+			SheetRefEvaluator sre = createExternSheetRefEvaluator(tracker, refPtg);
 			return new LazyRefEval(refPtg, sre);
 		}
 		if (ptg instanceof Area3DPtg) {
 			Area3DPtg aptg = (Area3DPtg) ptg;
-			int otherSheetIndex = _workbook.convertFromExternSheetIndex(aptg.getExternSheetIndex());
-			SheetRefEvaluator sre = new SheetRefEvaluator(this, tracker, _workbook, otherSheetIndex);
+			SheetRefEvaluator sre = createExternSheetRefEvaluator(tracker, aptg);
 			return new LazyAreaEval(aptg, sre);
 		}
 		SheetRefEvaluator sre = new SheetRefEvaluator(this, tracker, _workbook, sheetIndex);
@@ -410,7 +471,7 @@ public class WorkbookEvaluator {
 		} else {
 			cell = row.getCell(columnIndex);
  		}
-		CellLocation cellLoc = new CellLocation(sheetIndex, rowIndex, columnIndex);
+		CellLocation cellLoc = new CellLocation(_workbook, sheetIndex, rowIndex, columnIndex);
 		tracker.acceptDependency(cellLoc);
 		return internalEvaluate(cell, cellLoc, tracker);
 	}
diff --git a/src/java/org/apache/poi/ss/util/AreaReference.java b/src/java/org/apache/poi/ss/util/AreaReference.java
index ae2c9192f0..30d92ebcf4 100644
--- a/src/java/org/apache/poi/ss/util/AreaReference.java
+++ b/src/java/org/apache/poi/ss/util/AreaReference.java
@@ -90,7 +90,7 @@ public class AreaReference {
         for(int i=refPart.length()-1; i>=0; i--) {
             int ch = refPart.charAt(i);
             if (ch == '$' && i==0) {
-            	continue;
+                continue;
             }
             if (ch < 'A' || ch > 'Z') {
                 return false;
@@ -103,8 +103,45 @@ public class AreaReference {
      * Creates an area ref from a pair of Cell References.
      */
     public AreaReference(CellReference topLeft, CellReference botRight) {
-        _firstCell = topLeft;
-        _lastCell = botRight;
+        boolean swapRows = topLeft.getRow() > botRight.getRow();
+        boolean swapCols = topLeft.getCol() > botRight.getCol();
+        if (swapRows || swapCols) {
+            int firstRow; 
+            int lastRow; 
+            int firstColumn; 
+            int lastColumn;
+            boolean firstRowAbs; 
+            boolean lastRowAbs; 
+            boolean firstColAbs;
+            boolean lastColAbs;   
+            if (swapRows) {
+                firstRow = botRight.getRow();
+                firstRowAbs = botRight.isRowAbsolute();
+                lastRow = topLeft.getRow();
+                lastRowAbs = topLeft.isRowAbsolute();
+            } else {
+                firstRow = topLeft.getRow();
+                firstRowAbs = topLeft.isRowAbsolute();
+                lastRow = botRight.getRow();
+                lastRowAbs = botRight.isRowAbsolute();
+            }
+            if (swapCols) {
+                firstColumn = botRight.getCol();
+                firstColAbs = botRight.isColAbsolute();
+                lastColumn = topLeft.getCol();
+                lastColAbs = topLeft.isColAbsolute();
+            } else {
+                firstColumn = topLeft.getCol();
+                firstColAbs = topLeft.isColAbsolute();
+                lastColumn = botRight.getCol();
+                lastColAbs = botRight.isColAbsolute();
+            }
+            _firstCell = new CellReference(firstRow, firstColumn, firstRowAbs, firstColAbs);
+            _lastCell = new CellReference(lastRow, lastColumn, lastRowAbs, lastColAbs);
+        } else {
+            _firstCell = topLeft;
+            _lastCell = botRight;
+        }
         _isSingleCell = false;
     }
 
diff --git a/src/java/org/apache/poi/ss/util/CellReference.java b/src/java/org/apache/poi/ss/util/CellReference.java
index e5005db5d4..23b4c806e8 100644
--- a/src/java/org/apache/poi/ss/util/CellReference.java
+++ b/src/java/org/apache/poi/ss/util/CellReference.java
@@ -34,22 +34,29 @@ public class CellReference {
 	public static final class NameType {
 		public static final int CELL = 1;
 		public static final int NAMED_RANGE = 2;
+		public static final int COLUMN = 3;
 		public static final int BAD_CELL_OR_NAMED_RANGE = -1;
 	}
 
-    /** 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 = '\'';
-    
-    /**
-     * Matches a run of letters followed by a run of digits.  The run of letters is group 1 and the
-     * run of digits is group 2.  Each group may optionally be prefixed with a single '$'.
-     */
+	/** 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 = '\'';
+	
+	/**
+	 * Matches a run of one or more letters followed by a run of one or more digits.
+	 * The run of letters is group 1 and the run of digits is group 2.  
+	 * Each group may optionally be prefixed with a single '$'.
+	 */
 	private static final Pattern CELL_REF_PATTERN = Pattern.compile("\\$?([A-Za-z]+)\\$?([0-9]+)");
 	/**
+	 * Matches a run of one or more letters.  The run of letters is group 1.  
+	 * The text may optionally be prefixed with a single '$'.
+	 */
+	private static final Pattern COLUMN_REF_PATTERN = Pattern.compile("\\$?([A-Za-z]+)");
+	/**
 	 * Named range names must start with a letter or underscore.  Subsequent characters may include
 	 * digits or dot.  (They can even end in dot).
 	 */
@@ -65,96 +72,96 @@ public class CellReference {
     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 colRef = parts[1]; 
-        if (colRef.length() < 1) {
-            throw new IllegalArgumentException("Invalid Formula cell reference: '"+cellRef+"'");
-        }
-        _isColAbs = colRef.charAt(0) == '$';
-        if (_isColAbs) {
-            colRef=colRef.substring(1);
-        }
-        _colIndex = convertColStringToIndex(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) {
-    	this(pRow, pCol & 0xFFFF, false, false);
-    }
-    public CellReference(int pRow, short pCol) {
-    	this(pRow, (int)pCol, false, false);
-    }
+	/**
+	 * 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 colRef = parts[1]; 
+		if (colRef.length() < 1) {
+			throw new IllegalArgumentException("Invalid Formula cell reference: '"+cellRef+"'");
+		}
+		_isColAbs = colRef.charAt(0) == '$';
+		if (_isColAbs) {
+			colRef=colRef.substring(1);
+		}
+		_colIndex = convertColStringToIndex(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) {
-        this(null, pRow, pCol, pAbsRow, pAbsCol);
-    }
-    public CellReference(String pSheetName, int pRow, int pCol, boolean pAbsRow, boolean pAbsCol) {
-        // TODO - "-1" is a special value being temporarily used for whole row and whole column area references.
-        // so these checks are currently N.Q.R.
-        if(pRow < -1) {
-            throw new IllegalArgumentException("row index may not be negative");
-        }
-        if(pCol < -1) {
-            throw new IllegalArgumentException("column index may not be negative");
-        }
-        _sheetName = pSheetName;
-        _rowIndex=pRow;
-        _colIndex=pCol;
-        _isRowAbs = pAbsRow;
-        _isColAbs=pAbsCol;
-    }
+	public CellReference(int pRow, int pCol) {
+		this(pRow, pCol, false, false);
+	}
+	public CellReference(int pRow, short pCol) {
+		this(pRow, pCol & 0xFFFF, false, false);
+	}
 
-    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;
-    }
- 
-    public static boolean isPartAbsolute(String part) {
-        return part.charAt(0) == ABSOLUTE_REFERENCE_MARKER;
-    }
+	public CellReference(int pRow, int pCol, boolean pAbsRow, boolean pAbsCol) {
+		this(null, pRow, pCol, pAbsRow, pAbsCol);
+	}
+	public CellReference(String pSheetName, int pRow, int pCol, boolean pAbsRow, boolean pAbsCol) {
+		// TODO - "-1" is a special value being temporarily used for whole row and whole column area references.
+		// so these checks are currently N.Q.R.
+		if(pRow < -1) {
+			throw new IllegalArgumentException("row index may not be negative");
+		}
+		if(pCol < -1) {
+			throw new IllegalArgumentException("column index may not be negative");
+		}
+		_sheetName = pSheetName;
+		_rowIndex=pRow;
+		_colIndex=pCol;
+		_isRowAbs = pAbsRow;
+		_isColAbs=pAbsCol;
+	}
 
-    /**
-     * takes in a column reference portion of a CellRef and converts it from
-     * ALPHA-26 number format to 0-based base 10.
-     * 'A' -> 0
-     * 'Z' -> 25
-     * 'AA' -> 26
-     * 'IV' -> 255
-     * @return zero based column index
-     */
-    protected static int convertColStringToIndex(String ref) {
-        int pos = 0;
-        int retval=0;
+	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;
+	}
+	
+	public static boolean isPartAbsolute(String part) {
+		return part.charAt(0) == ABSOLUTE_REFERENCE_MARKER;
+	}
+	/**
+	 * takes in a column reference portion of a CellRef and converts it from
+	 * ALPHA-26 number format to 0-based base 10.
+	 * 'A' -> 0
+	 * 'Z' -> 25
+	 * 'AA' -> 26
+	 * 'IV' -> 255
+	 * @return zero based column index
+	 */
+	public static int convertColStringToIndex(String ref) {
+	
+		int pos = 0;
+		int retval=0;
 		for (int k = ref.length()-1; k >= 0; k--) {
 			char thechar = ref.charAt(k);
-            if (thechar == ABSOLUTE_REFERENCE_MARKER) {
-               if (k != 0) {
-                  throw new IllegalArgumentException("Bad col ref format '" + ref + "'");
-               }
-               break;
-            }
+			if (thechar == ABSOLUTE_REFERENCE_MARKER) {
+				if (k != 0) {
+					throw new IllegalArgumentException("Bad col ref format '" + ref + "'");
+				}
+				break;
+			}
 			// Character.getNumericValue() returns the values
 			//  10-35 for the letter A-Z
 			int shift = (int)Math.pow(26, pos);
@@ -162,64 +169,70 @@ public class CellReference {
 			pos++;
 		}
 		return retval-1;
-    }
+	}
 
-    /**
-     * Classifies an identifier as either a simple (2D) cell reference or a named range name
-     * @return one of the values from <tt>NameType</tt> 
-     */
-    public static int classifyCellReference(String str) {
-    	int len = str.length();
-    	if (len < 1) {
-    		throw new IllegalArgumentException("Empty string not allowed");
-    	}
-    	char firstChar = str.charAt(0);
-    	switch (firstChar) {
-        	case ABSOLUTE_REFERENCE_MARKER:
-        	case '.':
-        	case '_':
-        		break;
-        	default:
-        		if (!Character.isLetter(firstChar)) {
-            		throw new IllegalArgumentException("Invalid first char (" + firstChar 
-            				+ ") of cell reference or named range.  Letter expected");
-            	}
-    	}
-    	if (!Character.isDigit(str.charAt(len-1))) {
-    		// no digits at end of str
-    		return validateNamedRangeName(str);
-    	}
-    	Matcher cellRefPatternMatcher = CELL_REF_PATTERN.matcher(str);
-    	if (!cellRefPatternMatcher.matches()) {
-    		return validateNamedRangeName(str);
-    	}
-    	String lettersGroup = cellRefPatternMatcher.group(1);
-    	String digitsGroup = cellRefPatternMatcher.group(2);
-    	if (cellReferenceIsWithinRange(lettersGroup, digitsGroup)) {
-    		// valid cell reference
-    		return NameType.CELL;
-    	}
-    	// If str looks like a cell reference, but is out of (row/col) range, it is a valid
-    	// named range name
-    	// This behaviour is a little weird.  For example, "IW123" is a valid named range name
-    	// because the column "IW" is beyond the maximum "IV".  Note - this behaviour is version
-    	// dependent.  In Excel 2007, "IW123" is not a valid named range name.
-    	if (str.indexOf(ABSOLUTE_REFERENCE_MARKER) >= 0) {
-    		// Of course, named range names cannot have '$'
-    		return NameType.BAD_CELL_OR_NAMED_RANGE;
-    	}
-    	return NameType.NAMED_RANGE;
-    }
+	/**
+	 * Classifies an identifier as either a simple (2D) cell reference or a named range name
+	 * @return one of the values from <tt>NameType</tt> 
+	 */
+	public static int classifyCellReference(String str) {
+		int len = str.length();
+		if (len < 1) {
+			throw new IllegalArgumentException("Empty string not allowed");
+		}
+		char firstChar = str.charAt(0);
+		switch (firstChar) {
+			case ABSOLUTE_REFERENCE_MARKER:
+			case '.':
+			case '_':
+				break;
+			default:
+				if (!Character.isLetter(firstChar)) {
+					throw new IllegalArgumentException("Invalid first char (" + firstChar 
+							+ ") of cell reference or named range.  Letter expected");
+				}
+		}
+		if (!Character.isDigit(str.charAt(len-1))) {
+			// no digits at end of str
+			return validateNamedRangeName(str);
+		}
+		Matcher cellRefPatternMatcher = CELL_REF_PATTERN.matcher(str);
+		if (!cellRefPatternMatcher.matches()) {
+			return validateNamedRangeName(str);
+		}
+		String lettersGroup = cellRefPatternMatcher.group(1);
+		String digitsGroup = cellRefPatternMatcher.group(2);
+		if (cellReferenceIsWithinRange(lettersGroup, digitsGroup)) {
+			// valid cell reference
+			return NameType.CELL;
+		}
+		// If str looks like a cell reference, but is out of (row/col) range, it is a valid
+		// named range name
+		// This behaviour is a little weird.  For example, "IW123" is a valid named range name
+		// because the column "IW" is beyond the maximum "IV".  Note - this behaviour is version
+		// dependent.  In BIFF12, "IW123" is not a valid named range name, but in BIFF8 it is.
+		if (str.indexOf(ABSOLUTE_REFERENCE_MARKER) >= 0) {
+			// Of course, named range names cannot have '$'
+			return NameType.BAD_CELL_OR_NAMED_RANGE;
+		}
+		return NameType.NAMED_RANGE;
+	}
 
-    private static int validateNamedRangeName(String str) {
+	private static int validateNamedRangeName(String str) {
+		Matcher colMatcher = COLUMN_REF_PATTERN.matcher(str);
+		if (colMatcher.matches()) {
+			String colStr = colMatcher.group(1);
+			if (isColumnWithnRange(colStr)) {
+				return NameType.COLUMN;
+			}
+		}
 		if (!NAMED_RANGE_NAME_PATTERN.matcher(str).matches()) {
 			return NameType.BAD_CELL_OR_NAMED_RANGE;
 		}
 		return NameType.NAMED_RANGE;
-		
 	}
-    
-    
+	
+	
 	/**
 	 * Used to decide whether a name of the form "[A-Z]*[0-9]*" that appears in a formula can be 
 	 * interpreted as a cell reference.  Names of that form can be also used for sheets and/or
@@ -240,7 +253,7 @@ public class CellReference {
 	 * <blockquote><table border="0" cellpadding="1" cellspacing="0" 
 	 *                 summary="Notable cases.">
 	 *   <tr><th>Input&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</th>
-	 *   	<th>Result&nbsp;</th></tr>
+	 *       <th>Result&nbsp;</th></tr>
 	 *   <tr><td>"A", "1"</td><td>true</td></tr>
 	 *   <tr><td>"a", "111"</td><td>true</td></tr>
 	 *   <tr><td>"A", "65536"</td><td>true</td></tr>
@@ -257,23 +270,13 @@ public class CellReference {
 	 * @return <code>true</code> if the row and col parameters are within range of a BIFF8 spreadsheet.
 	 */
 	public static boolean cellReferenceIsWithinRange(String colStr, String rowStr) {
-		int numberOfLetters = colStr.length();
-		if(numberOfLetters > BIFF8_LAST_COLUMN_TEXT_LEN) {
-			// "Sheet1" case etc
-			return false; // that was easy
+		if (!isColumnWithnRange(colStr)) {
+			return false;
 		}
 		int nDigits = rowStr.length();
 		if(nDigits > BIFF8_LAST_ROW_TEXT_LEN) {
 			return false; 
 		}
-		if(numberOfLetters == BIFF8_LAST_COLUMN_TEXT_LEN) {
-			if(colStr.toUpperCase().compareTo(BIFF8_LAST_COLUMN) > 0) {
-				return false;
-			}
-		} else {
-			// apparent column name has less chars than max
-			// no need to check range
-		}
 		
 		if(nDigits == BIFF8_LAST_ROW_TEXT_LEN) {
 			// ASCII comparison is valid if digit count is same
@@ -288,87 +291,104 @@ public class CellReference {
 		return true;
 	}
 
+	private static boolean isColumnWithnRange(String colStr) {
+		int numberOfLetters = colStr.length();
+		if(numberOfLetters > BIFF8_LAST_COLUMN_TEXT_LEN) {
+			// "Sheet1" case etc
+			return false; // that was easy
+		}
+		if(numberOfLetters == BIFF8_LAST_COLUMN_TEXT_LEN) {
+			if(colStr.toUpperCase().compareTo(BIFF8_LAST_COLUMN) > 0) {
+				return false;
+			}
+		} else {
+			// apparent column name has less chars than max
+			// no need to check range
+		}
+		return true;
+	}
+
 	/**
-     * 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;
+	 * 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();
+		int length = reference.length();
 
 
-        int loc = start;
-        // 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),
-        };
-    }
+		int loc = start;
+		// 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 + ")");
-        }
+	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 + ")");
+		}
 
-        // 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();
-    }
+		// 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();
+	}
 
-    /**
-     * Takes in a 0-based base-10 column and returns a ALPHA-26
-     *  representation.
-     * eg column #3 -> D
-     */
-    protected static String convertNumToColString(int col) {
+	/**
+	 * Takes in a 0-based base-10 column and returns a ALPHA-26
+	 *  representation.
+	 * eg column #3 -> D
+	 */
+	protected static String convertNumToColString(int col) {
 		// Excel counts column A as the 1st column, we
 		//  treat it as the 0th one
 		int excelColNum = col + 1;
@@ -387,36 +407,36 @@ public class CellReference {
 		}
 		
 		return colRef;
-    }
+	}
+
+	/**
+	 *  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 sb = new StringBuffer(64);
+		sb.append(getClass().getName()).append(" [");
+		sb.append(formatAsString());
+		sb.append("]");
+		return sb.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</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 sb = new StringBuffer(64);
-        sb.append(getClass().getName()).append(" [");
-        sb.append(formatAsString());
-        sb.append("]");
-        return sb.toString();
-    }
-    
 	/**
 	 * Returns the three parts of the cell reference, the
 	 *  Sheet name (or null if none supplied), the 1 based
@@ -433,18 +453,18 @@ public class CellReference {
 		};
 	}
 
-    /**
-     * Appends cell reference with '$' markers for absolute values as required.
-     * Sheet name is not included.
-     */
-    protected 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);
-    }
+	/**
+	 * 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);
+	}
 }