poi-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From n...@apache.org
Subject svn commit: r675717 - in /poi/trunk/src: java/org/apache/poi/hssf/util/CellReference.java testcases/org/apache/poi/hssf/util/TestCellReference.java
Date Thu, 10 Jul 2008 20:30:46 GMT
Author: nick
Date: Thu Jul 10 13:30:46 2008
New Revision: 675717

URL: http://svn.apache.org/viewvc?rev=675717&view=rev
Log:
Back port fixes from the ooxml branch for CellReference column number<->letter stuff

Modified:
    poi/trunk/src/java/org/apache/poi/hssf/util/CellReference.java
    poi/trunk/src/testcases/org/apache/poi/hssf/util/TestCellReference.java

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=675717&r1=675716&r2=675717&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 Thu Jul 10 13:30:46 2008
@@ -92,6 +92,13 @@
         _rowIndex = Integer.parseInt(rowRef)-1; // -1 to convert 1-based to zero-based
     }
 
+    public CellReference(int pRow, int pCol) {
+    	this(pRow, pCol, false, false);
+    }
+    public CellReference(int pRow, short pCol) {
+    	this(pRow, (int)pCol, false, false);
+    }
+
     public CellReference(int pRow, int pCol, boolean pAbsRow, boolean pAbsCol) {
         this(null, pRow, pCol, pAbsRow, pAbsCol);
     }
@@ -128,20 +135,19 @@
      * ALPHA-26 number format to 0-based base 10.
      */
     private int convertColStringToNum(String ref) {
-        int lastIx = ref.length()-1;
-        int retval=0;
-        int pos = 0;
-
-        for (int k = lastIx; k > -1; k--) {
-            char thechar = ref.charAt(k);
-            if ( pos == 0) {
-                retval += (Character.getNumericValue(thechar)-9);
-            } else {
-                retval += (Character.getNumericValue(thechar)-9) * (pos * 26);
-            }
-            pos++;
-        }
-        return retval-1;
+		int lastIx = ref.length()-1;
+		int retval=0;
+		int pos = 0;
+		
+		for (int k = lastIx; k > -1; k--) {
+			char thechar = ref.charAt(k);
+			// Character.getNumericValue() returns the values
+			//  10-35 for the letter A-Z
+			int shift = (int)Math.pow(26, pos);
+			retval += (Character.getNumericValue(thechar)-9) * shift;
+			pos++;
+		}
+		return retval-1;
     }
 
     /**
@@ -349,19 +355,24 @@
      * eg column #3 -> D
      */
     protected static String convertNumToColString(int col) {
-        String retval = null;
-        int mod = col % 26;
-        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;
+		// Excel counts column A as the 1st column, we
+		//  treat it as the 0th one
+		int excelColNum = col + 1;
+		
+		String colRef = "";
+		int colRemain = excelColNum;
+		
+		while(colRemain > 0) {
+			int thisPart = colRemain % 26;
+			if(thisPart == 0) { thisPart = 26; }
+			colRemain = (colRemain - thisPart) / 26;
+			
+			// The letter A is at 65
+			char colChar = (char)(thisPart+64);
+			colRef = colChar + colRef;
+		}
+		
+		return colRef;
     }
 
     /**
@@ -392,6 +403,22 @@
         return sb.toString();
     }
 
+	/**
+	 * Returns the three parts of the cell reference, the
+	 *  Sheet name (or null if none supplied), the 1 based
+	 *  row number, and the A based column letter.
+	 * This will not include any markers for absolute
+	 *  references, so use {@link #formatAsString()}
+	 *  to properly turn references into strings. 
+	 */
+	public String[] getCellRefParts() {
+		return new String[] {
+				_sheetName,
+				Integer.toString(_rowIndex+1),
+				convertNumToColString(_colIndex)
+		};
+	}
+
     /**
      * Appends cell reference with '$' markers for absolute values as required.
      * Sheet name is not included.

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=675717&r1=675716&r2=675717&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 Thu Jul 10 13:30:46
2008
@@ -106,4 +106,147 @@
         int actualResult = CellReference.classifyCellReference(ref);
         assertEquals(expectedResult, actualResult);
     }
-}
+
+
+	public void testGetCellRefParts() {
+		CellReference cellReference;
+		String[] parts;
+		
+		String cellRef = "A1";
+		cellReference = new CellReference(cellRef);
+		assertEquals(0, cellReference.getCol());
+		parts = cellReference.getCellRefParts();
+		assertNotNull(parts);
+		assertEquals(null, parts[0]);
+		assertEquals("1", parts[1]);
+		assertEquals("A", parts[2]);
+
+		cellRef = "AA1";
+		cellReference = new CellReference(cellRef);
+		assertEquals(26, cellReference.getCol());
+		parts = cellReference.getCellRefParts();
+		assertNotNull(parts);
+		assertEquals(null, parts[0]);
+		assertEquals("1", parts[1]);
+		assertEquals("AA", parts[2]);
+
+		cellRef = "AA100";
+		cellReference = new CellReference(cellRef);
+		assertEquals(26, cellReference.getCol());
+		parts = cellReference.getCellRefParts();
+		assertNotNull(parts);
+		assertEquals(null, parts[0]);
+		assertEquals("100", parts[1]);
+		assertEquals("AA", parts[2]);
+
+		cellRef = "AAA300";
+		cellReference = new CellReference(cellRef);
+		assertEquals(702, cellReference.getCol());
+		parts = cellReference.getCellRefParts();
+		assertNotNull(parts);
+		assertEquals(null, parts[0]);
+		assertEquals("300", parts[1]);
+		assertEquals("AAA", parts[2]);
+
+		cellRef = "ZZ100521";
+		cellReference = new CellReference(cellRef);
+		assertEquals(26*26+25, cellReference.getCol());
+		parts = cellReference.getCellRefParts();
+		assertNotNull(parts);
+		assertEquals(null, parts[0]);
+		assertEquals("100521", parts[1]);
+		assertEquals("ZZ", parts[2]);
+
+		cellRef = "ZYX987";
+		cellReference = new CellReference(cellRef);
+		assertEquals(26*26*26 + 25*26 + 24 - 1, cellReference.getCol());
+		parts = cellReference.getCellRefParts();
+		assertNotNull(parts);
+		assertEquals(null, parts[0]);
+		assertEquals("987", parts[1]);
+		assertEquals("ZYX", parts[2]);
+
+		cellRef = "AABC10065";
+		cellReference = new CellReference(cellRef);
+		parts = cellReference.getCellRefParts();
+		assertNotNull(parts);
+		assertEquals(null, parts[0]);
+		assertEquals("10065", parts[1]);
+		assertEquals("AABC", parts[2]);
+	}
+	
+	public void testGetColNumFromRef() {
+		String cellRef = "A1";
+		CellReference cellReference = new CellReference(cellRef);
+		assertEquals(0, cellReference.getCol());
+
+		cellRef = "AA1";
+		cellReference = new CellReference(cellRef);
+		assertEquals(26, cellReference.getCol());
+
+		cellRef = "AB1";
+		cellReference = new CellReference(cellRef);
+		assertEquals(27, cellReference.getCol());
+
+		cellRef = "BA1";
+		cellReference = new CellReference(cellRef);
+		assertEquals(26+26, cellReference.getCol());
+		
+		cellRef = "CA1";
+		cellReference = new CellReference(cellRef);
+		assertEquals(26+26+26, cellReference.getCol());
+		
+		cellRef = "ZA1";
+		cellReference = new CellReference(cellRef);
+		assertEquals(26*26, cellReference.getCol());
+		
+		cellRef = "ZZ1";
+		cellReference = new CellReference(cellRef);
+		assertEquals(26*26+25, cellReference.getCol());
+		
+		cellRef = "AAA1";
+		cellReference = new CellReference(cellRef);
+		assertEquals(26*26+26, cellReference.getCol());
+		
+		
+		cellRef = "A1100";
+		cellReference = new CellReference(cellRef);
+		assertEquals(0, cellReference.getCol());
+
+		cellRef = "BC15";
+		cellReference = new CellReference(cellRef);
+		assertEquals(54, cellReference.getCol());
+	}
+	
+	public void testGetRowNumFromRef() {
+		String cellRef = "A1";
+		CellReference cellReference = new CellReference(cellRef);
+		assertEquals(0, cellReference.getRow());
+
+		cellRef = "A12";
+		cellReference = new CellReference(cellRef);
+		assertEquals(11, cellReference.getRow());
+
+		cellRef = "AS121";
+		cellReference = new CellReference(cellRef);
+		assertEquals(120, cellReference.getRow());
+	}
+	
+	public void testConvertNumToColString() {
+		short col = 702;
+		String collRef = new CellReference(0, col).formatAsString();
+		assertEquals("AAA1", collRef);
+
+		short col2 = 0;
+		String collRef2 = new CellReference(0, col2).formatAsString();
+		assertEquals("A1", collRef2);
+		
+		short col3 = 27;
+		String collRef3 = new CellReference(0, col3).formatAsString();
+		assertEquals("AB1", collRef3);
+		
+		short col4 = 2080;
+		String collRef4 = new CellReference(0, col4).formatAsString();
+		assertEquals("CBA1", collRef4);
+	}
+}
\ No newline at end of file



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


Mime
View raw message