Fixes AreaReference when a Sheet name is provided

PR: 19888
Submitted by:
     brett.knights@tanner.com
Test Case Provided by:
     Arne.Clauss@gedas.de


git-svn-id: https://svn.apache.org/repos/asf/jakarta/poi/trunk@353104 13f79535-47bb-0310-9956-ffa450edef68

3 files changed, 77 insertions, 40 deletions

diff --git a/src/java/org/apache/poi/hssf/util/AreaReference.java b/src/java/org/apache/poi/hssf/util/AreaReference.java
index 3d195e131d..4b9617feda 100644
--- a/src/java/org/apache/poi/hssf/util/AreaReference.java
+++ b/src/java/org/apache/poi/hssf/util/AreaReference.java
@@ -55,8 +55,8 @@
 package org.apache.poi.hssf.util;
 
 public class AreaReference {
-    
-    
+
+
 private CellReference [] cells;
 private int dim;
 
@@ -80,7 +80,7 @@ private int dim;
     public CellReference[] getCells() {
         return cells;
     }
-    
+
     public String toString() {
         StringBuffer retval = new StringBuffer();
         for (int i=0;i<dim;i++){
@@ -90,19 +90,28 @@ private int dim;
         retval.deleteCharAt(0);
         return retval.toString();
     }
-    
+
     /**
-     * seperates Area refs in two parts and returns them as seperate elements in a 
+     * seperates Area refs in two parts and returns them as seperate elements in a
      * String array
      */
     private String[] seperateAreaRefs(String reference) {
-        String retval[] = new String[2];
+        String[] retval = null;
+
         int length = reference.length();
-        
+
         int loc = reference.indexOf(':',0);
-        
-        retval[0] = reference.substring(0,loc);
-        retval[1] = reference.substring(loc+1);        
+        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;
     }
 }
\ No newline at end of file
diff --git a/src/java/org/apache/poi/hssf/util/CellReference.java b/src/java/org/apache/poi/hssf/util/CellReference.java
index c1e3f459c8..eeeccc43f4 100644
--- a/src/java/org/apache/poi/hssf/util/CellReference.java
+++ b/src/java/org/apache/poi/hssf/util/CellReference.java
@@ -60,45 +60,48 @@ package org.apache.poi.hssf.util;
  * @author  Dennis Doubleday (patch to seperateRowColumns())
  */
 public class CellReference {
-    
+
     /** Creates new CellReference */
     private int row;
     private int col;
+    private String sheetName;
     private boolean rowAbs;
     private boolean colAbs;
-    
+
     public CellReference(String cellRef) {
-        String[] parts = seperateRowColumns(cellRef);
-        String ref = parts[0];
+        String[] parts = separateRefParts(cellRef);
+        sheetName = parts[0];
+        String ref = parts[1];
         if (ref.charAt(0) == '$') {
-            colAbs=true; 
+            colAbs=true;
             ref=ref.substring(1);
         }
         col = convertColStringToNum(ref);
-        ref=parts[1];
+        ref=parts[2];
         if (ref.charAt(0) == '$') {
-            rowAbs=true; 
+            rowAbs=true;
             ref=ref.substring(1);
         }
         row = Integer.parseInt(ref)-1;
     }
-    
+
     public CellReference(int pRow, int pCol) {
         this(pRow,pCol,false,false);
     }
-    
+
     public CellReference(int pRow, int pCol, boolean pAbsRow, boolean pAbsCol) {
         row=pRow;col=pCol;
         rowAbs = pAbsRow;
         colAbs=pAbsCol;
-        
+
     }
-    
+
     public int getRow(){return row;}
-    public int getCol(){return col;}
+    public short getCol(){return (short) col;}
     public boolean isRowAbsolute(){return rowAbs;}
     public boolean isColAbsolute(){return colAbs;}
-    
+    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.
@@ -107,7 +110,7 @@ public class CellReference {
         int len = ref.length();
         int retval=0;
         int pos = 0;
-        
+
         for (int k = ref.length()-1; k > -1; k--) {
             char thechar = ref.charAt(k);
             if ( pos == 0) {
@@ -119,21 +122,24 @@ public class CellReference {
         }
         return retval-1;
     }
-    
-    
+
+
     /**
      * 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[] seperateRowColumns(String reference) {
-        
+    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.
-        int start = reference.indexOf("!") + 1;
+        String retval[] = new String[3];
+
+        int start = reference.indexOf("!");
+        if (start != -1) retval[0] = reference.substring(0, start);
+        start += 1;
 
-        String retval[] = new String[2];
         int length = reference.length();
 
 
@@ -145,13 +151,12 @@ public class CellReference {
                 break;
             }
         }
-        
-        
-        retval[0] = reference.substring(start,loc);
-        retval[1] = reference.substring(loc);
+
+        retval[1] = reference.substring(start,loc);
+        retval[2] = reference.substring(loc);
         return retval;
     }
-    
+
     /**
      * takes in a 0-based base-10 column and returns a ALPHA-26 representation
      */
@@ -161,24 +166,24 @@ public class CellReference {
         int div = col / 26;
         char small=(char)(mod + 65);
         char big = (char)(div + 64);
-        
+
         if (div == 0) {
             retval = ""+small;
         } else {
             retval = ""+big+""+small;
         }
-        
+
         return retval;
     }
-    
-    
+
+
     public String toString() {
         StringBuffer retval = new StringBuffer();
         retval.append( (colAbs)?"$":"");
         retval.append( convertNumToColString(col));
         retval.append((rowAbs)?"$":"");
         retval.append(row+1);
-    
+
     return retval.toString();
     }
 }
diff --git a/src/testcases/org/apache/poi/hssf/util/TestAreaReference.java b/src/testcases/org/apache/poi/hssf/util/TestAreaReference.java
index beed363670..78655bb398 100644
--- a/src/testcases/org/apache/poi/hssf/util/TestAreaReference.java
+++ b/src/testcases/org/apache/poi/hssf/util/TestAreaReference.java
@@ -56,6 +56,10 @@ package org.apache.poi.hssf.util;
 
 import junit.framework.TestCase;
 
+import org.apache.poi.hssf.usermodel.HSSFCell;
+import org.apache.poi.hssf.usermodel.HSSFRow;
+import org.apache.poi.hssf.usermodel.HSSFSheet;
+
 public class TestAreaReference extends TestCase {
      public TestAreaReference(String s) {
         super(s);
@@ -77,4 +81,23 @@ public class TestAreaReference extends TestCase {
         assertTrue("col is abs",cf.isColAbsolute());
         assertTrue("string is $B$2",cf.toString().equals("$B$2"));
     }
+    
+    /**
+     * References failed when sheet names were being used
+     * Reported by Arne.Clauss@gedas.de
+     */
+    public void testReferenceWithSheet() {
+    	String ref = "Tabelle1!$B$5";
+		AreaReference myAreaReference = new AreaReference(ref);
+		CellReference[] myCellReference = myAreaReference.getCells();
+		
+		assertNotNull("cell reference not null : "+myCellReference[0]);
+    	assertEquals("Not Column B", (short)1,myCellReference[0].getCol());
+		assertEquals("Not Row 5", 4,myCellReference[0].getRow());
+    }
+    
+	public static void main(java.lang.String[] args) {        
+		junit.textui.TestRunner.run(TestAreaReference.class);
+	}
+        
 }
\ No newline at end of file