1    /*
2     * ReferenceUtil.java
3     *
4     * Created on April 28, 2002, 1:09 PM
5     */
6    
7    package org.apache.poi.hssf.util;
8    
9    /**
10    * Handles conversion between A1= 0,0 (cell ref to numeric conversion)
11    * @author  Andrew C. Oliver (acoliver at apache dot org)
12    */
13   public class ReferenceUtil {
14   
15       /** You don't neeed to construct this */
16       private ReferenceUtil() {
17       }
18       
19       /**
20        * takes in a cell range and returns an array of integers x1,y1,x2,y2
21        */
22       public static int[] getXYXYFromAreaRef(String reference) {
23           int retval[] = null;
24           String[] refs = seperateAreaRefs(reference);
25           int[] xy1 = getXYFromReference(refs[0]);
26           int[] xy2 = getXYFromReference(refs[1]);
27           retval = new int[] {xy1[0],xy1[1],xy2[0],xy2[1]};
28           return retval;
29       }
30       
31       /**
32        * takes in a cell reference string A1 for instance and returns an integer
33        * array with the first element being the row number and the second being 
34        * the column number, all in 0-based base 10 format.
35        * @param reference a cell reference such as A1 or AA1 or IV1
36        * @return xyarray int array containing row and column number
37        */
38       public static int[] getXYFromReference(String reference) {
39              int[] retval = new int[2];
40              String[] parts = seperateRowColumns(reference);           
41              retval[1] = convertColStringToNum(parts[0]);
42              retval[0] = Integer.parseInt(parts[1])-1;
43              return retval;
44       }
45       
46       /**
47        * takes in a row and column and returns a string cellref
48        * @param row the 0 based row such as 0 
49        * @param col the 0 based col number such as 1 
50        * @return cellreference string such as B1
51        */
52       public static String getReferenceFromXY(int row, int col) {
53           String retval = convertNumToColString(col) + ""+(row+1);
54           return retval;
55       }
56       
57       /**
58        * takes in a 0-based base-10 column and returns a ALPHA-26 representation
59        */
60       private static String convertNumToColString(int col) {
61           String retval = null;
62           int mod = col % 26;
63           int div = col / 26;
64           char small=(char)(mod + 65);
65           char big = (char)(div + 64);
66                   
67           if (div == 0) {
68               retval = ""+small;
69           } else {
70               retval = ""+big+""+small;
71           }
72           
73           return retval;
74       }
75       
76       /**
77        * takes in a column reference portion of a CellRef and converts it from 
78        * ALPHA-26 number format to 0-based base 10.
79        */
80       private static int convertColStringToNum(String ref) {
81           int len = ref.length();
82           int retval=0;
83           int pos = 0;
84           for (int k = ref.length()-1; k > -1; k--) {
85               char thechar = ref.charAt(k);
86               if ( pos == 0) {
87                   retval += (Character.getNumericValue(thechar)-9);
88               } else {
89                   retval += (Character.getNumericValue(thechar)-9) * (pos * 26);
90               }
91               pos++;            
92           }
93           return retval-1;
94       }
95       
96       
97       /**
98        * Seperates the row from the columns and returns an array.  Element in
99        * position one is the substring containing the columns still in ALPHA-26
100       * number format.
101       */
102      private static String[] seperateRowColumns(String reference) {
103          int loc = 0; // location of first number
104          String retval[] = new String[2];
105          int length = reference.length();
106          
107          char[] chars = reference.toCharArray();
108          
109          for (loc = 0; loc < chars.length; loc++) {
110              if (Character.isDigit(chars[loc])) {
111                  break;
112              }
113          }
114          
115          retval[0] = reference.substring(0,loc);
116          retval[1] = reference.substring(loc);        
117          return retval;
118      }
119      
120      
121      /**
122       * seperates Area refs in two parts and returns them as seperate elements in a 
123       * String array
124       */
125      private static String[] seperateAreaRefs(String reference) {
126          String retval[] = new String[2];
127          int length = reference.length();
128          
129          int loc = reference.indexOf(':',0);
130          
131          retval[0] = reference.substring(0,loc);
132          retval[1] = reference.substring(loc+1);        
133          return retval;
134      }
135      
136          
137  }
138