1    /* ====================================================================
2     * The Apache Software License, Version 1.1
3     *
4     * Copyright (c) 2002 The Apache Software Foundation.  All rights
5     * reserved.
6     *
7     * Redistribution and use in source and binary forms, with or without
8     * modification, are permitted provided that the following conditions
9     * are met:
10    *
11    * 1. Redistributions of source code must retain the above copyright
12    *    notice, this list of conditions and the following disclaimer.
13    *
14    * 2. Redistributions in binary form must reproduce the above copyright
15    *    notice, this list of conditions and the following disclaimer in
16    *    the documentation and/or other materials provided with the
17    *    distribution.
18    *
19    * 3. The end-user documentation included with the redistribution,
20    *    if any, must include the following acknowledgment:
21    *       "This product includes software developed by the
22    *        Apache Software Foundation (http://www.apache.org/)."
23    *    Alternately, this acknowledgment may appear in the software itself,
24    *    if and wherever such third-party acknowledgments normally appear.
25    *
26    * 4. The names "Apache" and "Apache Software Foundation" and
27    *    "Apache POI" must not be used to endorse or promote products
28    *    derived from this software without prior written permission. For
29    *    written permission, please contact apache@apache.org.
30    *
31    * 5. Products derived from this software may not be called "Apache",
32    *    "Apache POI", nor may "Apache" appear in their name, without
33    *    prior written permission of the Apache Software Foundation.
34    *
35    * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
36    * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
37    * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
38    * DISCLAIMED.  IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR
39    * ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
40    * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
41    * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
42    * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
43    * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
44    * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
45    * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
46    * SUCH DAMAGE.
47    * ====================================================================
48    *
49    * This software consists of voluntary contributions made by many
50    * individuals on behalf of the Apache Software Foundation.  For more
51    * information on the Apache Software Foundation, please see
52    * <http://www.apache.org/>.
53    */
54   
55   package org.apache.poi.hssf.util;
56   
57   /**
58    *
59    * @author  Avik Sengupta
60    * @author  Dennis Doubleday (patch to seperateRowColumns())
61    */
62   public class CellReference {
63       
64       /** Creates new CellReference */
65       private int row;
66       private int col;
67       private boolean rowAbs;
68       private boolean colAbs;
69       
70       public CellReference(String cellRef) {
71           String[] parts = seperateRowColumns(cellRef);
72           String ref = parts[0];
73           if (ref.charAt(0) == '$') {
74               colAbs=true; 
75               ref=ref.substring(1);
76           }
77           col = convertColStringToNum(ref);
78           ref=parts[1];
79           if (ref.charAt(0) == '$') {
80               rowAbs=true; 
81               ref=ref.substring(1);
82           }
83           row = Integer.parseInt(ref)-1;
84       }
85       
86       public CellReference(int pRow, int pCol) {
87           this(pRow,pCol,false,false);
88       }
89       
90       public CellReference(int pRow, int pCol, boolean pAbsRow, boolean pAbsCol) {
91           row=pRow;col=pCol;
92           rowAbs = pAbsRow;
93           colAbs=pAbsCol;
94           
95       }
96       
97       public int getRow(){return row;}
98       public int getCol(){return col;}
99       public boolean isRowAbsolute(){return rowAbs;}
100      public boolean isColAbsolute(){return colAbs;}
101      
102      /**
103       * takes in a column reference portion of a CellRef and converts it from
104       * ALPHA-26 number format to 0-based base 10.
105       */
106      private int convertColStringToNum(String ref) {
107          int len = ref.length();
108          int retval=0;
109          int pos = 0;
110          
111          for (int k = ref.length()-1; k > -1; k--) {
112              char thechar = ref.charAt(k);
113              if ( pos == 0) {
114                  retval += (Character.getNumericValue(thechar)-9);
115              } else {
116                  retval += (Character.getNumericValue(thechar)-9) * (pos * 26);
117              }
118              pos++;
119          }
120          return retval-1;
121      }
122      
123      
124      /**
125       * Seperates the row from the columns and returns an array.  Element in
126       * position one is the substring containing the columns still in ALPHA-26
127       * number format.
128       */
129      private String[] seperateRowColumns(String reference) {
130          
131          // Look for end of sheet name. This will either set
132          // start to 0 (if no sheet name present) or the
133          // index after the sheet reference ends.
134          int start = reference.indexOf("!") + 1;
135  
136          String retval[] = new String[2];
137          int length = reference.length();
138  
139  
140          char[] chars = reference.toCharArray();
141          int loc = start;
142          if (chars[loc]=='$') loc++;
143          for (; loc < chars.length; loc++) {
144              if (Character.isDigit(chars[loc]) || chars[loc] == '$') {
145                  break;
146              }
147          }
148          
149          
150          retval[0] = reference.substring(start,loc);
151          retval[1] = reference.substring(loc);
152          return retval;
153      }
154      
155      /**
156       * takes in a 0-based base-10 column and returns a ALPHA-26 representation
157       */
158      private static String convertNumToColString(int col) {
159          String retval = null;
160          int mod = col % 26;
161          int div = col / 26;
162          char small=(char)(mod + 65);
163          char big = (char)(div + 64);
164          
165          if (div == 0) {
166              retval = ""+small;
167          } else {
168              retval = ""+big+""+small;
169          }
170          
171          return retval;
172      }
173      
174      
175      public String toString() {
176          StringBuffer retval = new StringBuffer();
177          retval.append( (colAbs)?"$":"");
178          retval.append( convertNumToColString(col));
179          retval.append((rowAbs)?"$":"");
180          retval.append(row+1);
181      
182      return retval.toString();
183      }
184  }
185