CellReferenceHelper


1   /*********************************************************************
2   *
3   *      Copyright (C) 2002 Andrew Khan
4   *
5   * This library is free software; you can redistribute it and/or
6   * modify it under the terms of the GNU Lesser General Public
7   * License as published by the Free Software Foundation; either
8   * version 2.1 of the License, or (at your option) any later version.
9   *
10  * This library is distributed in the hope that it will be useful,
11  * but WITHOUT ANY WARRANTY; without even the implied warranty of
12  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
13  * Lesser General Public License for more details.
14  *
15  * You should have received a copy of the GNU Lesser General Public
16  * License along with this library; if not, write to the Free Software
17  * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
18  ***************************************************************************/
19  
20  package jxl.biff;
21  
22  import common.Logger;
23  
24  import jxl.Cell;
25  import jxl.biff.formula.ExternalSheet;
26  
27  /**
28   * A helper to transform between excel cell references and
29   * sheet:column:row notation
30   * Because this function will be called when generating a string
31   * representation of a formula, the cell reference will merely
32   * be appened to the string buffer instead of returning a full
33   * blooded string, for performance reasons
34   */
35  public final class CellReferenceHelper
36  {
37    /**
38     * The logger
39     */
40    private static Logger logger = Logger.getLogger(CellReferenceHelper.class);
41  
42    /**
43     * The character which indicates whether a reference is fixed
44     */
45    private static final char fixedInd='$';
46  
47    /**
48     * Constructor to prevent instantiation
49     */
50    private CellReferenceHelper()
51    {
52    }
53  
54    /**
55     * Gets the cell reference 
56     *
57     * @param column
58     * @param row
59     * @param buf
60     */
61    public static void getCellReference(int column, int row, StringBuffer   buf)
62    {
63      // Put the column letter into the buffer
64      getColumnReference(column, buf);
65  
66      // Add the row into the buffer
67      buf.append(Integer.toString(row+1));
68    }
69  
70    /**
71     * Overloaded method which prepends $ for absolute reference
72     *
73     * @param column
74     * @param colabs TRUE if the column reference is absolute
75     * @param row
76     * @param rowabs TRUE if the row reference is absolute
77     * @param buf
78     */
79    public static void getCellReference(int column, boolean colabs, 
80                                        int row, boolean rowabs,
81                                        StringBuffer   buf)
82    {
83      if (colabs)
84      {
85        buf.append(fixedInd);
86      }
87  
88      // Put the column letter into the buffer
89      getColumnReference(column, buf);
90  
91      if (rowabs)
92      {
93        buf.append(fixedInd);
94      }
95  
96      // Add the row into the buffer
97      buf.append(Integer.toString(row+1));
98    }
99  
100   /**
101    * Gets the column letter corresponding to the 0-based column number
102    * 
103    * @param column the column number
104    * @return the letter for that column number
105    */
106   public static String   getColumnReference(int column)
107   {
108     StringBuffer   buf = new StringBuffer  ();
109     getColumnReference(column, buf);
110     return buf.toString();
111   }
112 
113   /**
114    * Gets the column letter corresponding to the 0-based column number
115    * 
116    * @param column the column number
117    * @param buf the string buffer in which to write the column letter
118    */
119   public static void getColumnReference(int column, StringBuffer   buf)
120   {
121     int v = column/26;
122     int r = column%26;
123 
124     StringBuffer   tmp = new StringBuffer  ();
125     while (v != 0)
126     {
127       char col = (char) ((int) 'A' +  r) ;
128 
129       tmp.append(col);
130 
131       r = v%26 - 1; // subtract one because only rows >26 preceded by A
132       v = v/26;
133     }
134 
135     char col = (char) ((int) 'A' +  r) ;
136     tmp.append(col);
137 
138     // Insert into the proper string buffer in reverse order
139     for (int i = tmp.length() - 1; i >= 0; i--)
140     {
141       buf.append(tmp.charAt(i));
142     }
143   }
144 
145   /**
146    * Gets the fully qualified cell reference given the column, row
147    * external sheet reference etc
148    *
149    * @param sheet
150    * @param column
151    * @param row
152    * @param workbook
153    * @param buf
154    */
155   public static void getCellReference
156     (int sheet, int column, int row,
157      ExternalSheet workbook, StringBuffer   buf)
158   {
159     buf.append('\'');
160     buf.append(workbook.getExternalSheetName(sheet));
161     buf.append('\'');
162     buf.append('!');
163     getCellReference(column, row, buf);
164   }
165 
166   /**
167    * Gets the fully qualified cell reference given the column, row
168    * external sheet reference etc
169    *
170    * @param sheet
171    * @param column
172    * @param colabs TRUE if the column is an absolute reference
173    * @param row
174    * @param rowabs TRUE if the row is an absolute reference
175    * @param workbook
176    * @param buf
177    */
178   public static void getCellReference
179     (int sheet, int column, boolean colabs, 
180      int row, boolean rowabs,
181      ExternalSheet workbook, StringBuffer   buf)
182   {
183     buf.append('\'');
184     buf.append(workbook.getExternalSheetName(sheet));
185     buf.append('\'');
186     buf.append('!');
187     getCellReference(column, colabs, row, rowabs, buf);
188   }
189 
190   /**
191    * Gets the fully qualified cell reference given the column, row
192    * external sheet reference etc
193    *
194    * @param sheet
195    * @param column
196    * @param row
197    * @param workbook
198    * @return the cell reference in the form 'Sheet 1'!A1
199    */
200   public static String   getCellReference
201     (int sheet, int column, int row,
202      ExternalSheet workbook)
203   {
204     StringBuffer   sb = new StringBuffer  ();
205     getCellReference(sheet, column, row, workbook, sb);
206     return sb.toString();
207   }
208 
209 
210   /**
211    * Gets the cell reference for the specified column and row
212    *
213    * @param column
214    * @param row
215    * @return
216    */
217   public static String   getCellReference(int column, int row)
218   {
219     StringBuffer   buf = new StringBuffer  ();
220     getCellReference(column, row, buf);
221     return buf.toString();
222   }
223 
224   /**
225    * Gets the columnn number of the string cell reference
226    *
227    * @param s the string to parse
228    * @return the column portion of the cell reference
229    */
230   public static int getColumn(String   s)
231   {
232     int colnum = 0;
233     int numindex = getNumberIndex(s);
234 
235     String   s2 = s.toUpperCase();
236 
237     int startPos = 0;
238     if (s.charAt(0) == fixedInd)
239     {
240       startPos = 1;
241     }
242 
243     int endPos = numindex;
244     if (s.charAt(numindex - 1) == fixedInd)
245     {
246       endPos--;
247     }
248 
249     for (int i = startPos; i < endPos ; i++)
250     {
251 
252       if (i != startPos)
253       {
254         colnum = (colnum+1) * 26;
255       }
256       colnum += (int) s2.charAt(i) - (int) 'A';
257     }
258 
259     return colnum;
260   }
261 
262   /**
263    * Gets the row number of the cell reference
264    */
265   public static int getRow(String   s)
266   {
267     try
268     {
269       return (Integer.parseInt(s.substring(getNumberIndex(s))) - 1);
270     }
271     catch (NumberFormatException   e)
272     {
273       logger.warn(e, e);
274       return 0xffff;
275     }
276   }
277 
278   /**
279    * Finds the position where the first number occurs in the string
280    */
281   private static int getNumberIndex(String   s)
282   {
283     // Find the position of the first number
284     boolean numberFound = false;
285     int pos = 0;
286     char c = '\0';
287 
288     while (!numberFound && pos < s.length() )
289     {
290       c = s.charAt(pos);
291 
292       if (c >= '0' && c <= '9')
293       {
294         numberFound = true;
295       }
296       else
297       {
298         pos++;
299       }
300     }
301 
302     return pos;
303   }
304 
305   /**
306    * Sees if the column component is relative or not
307    *
308    * @param s
309    * @return TRUE if the column is relative, FALSE otherwise
310    */
311   public static boolean isColumnRelative(String   s)
312   {
313     return s.charAt(0) != fixedInd;
314   }
315 
316   /**
317    * Sees if the row component is relative or not
318    *
319    * @param s
320    * @return TRUE if the row is relative, FALSE otherwise
321    */
322   public static boolean isRowRelative(String   s)
323   {
324     return s.charAt(getNumberIndex(s) - 1) != fixedInd;
325   }
326 
327 }
328
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags