1    package org.apache.poi.hssf.util;
2    
3    /* ====================================================================
4     * The Apache Software License, Version 1.1
5     *
6     * Copyright (c) 2002 The Apache Software Foundation.  All rights
7     * reserved.
8     *
9     * Redistribution and use in source and binary forms, with or without
10    * modification, are permitted provided that the following conditions
11    * are met:
12    *
13    * 1. Redistributions of source code must retain the above copyright
14    *    notice, this list of conditions and the following disclaimer.
15    *
16    * 2. Redistributions in binary form must reproduce the above copyright
17    *    notice, this list of conditions and the following disclaimer in
18    *    the documentation and/or other materials provided with the
19    *    distribution.
20    *
21    * 3. The end-user documentation included with the redistribution,
22    *    if any, must include the following acknowledgment:
23    *       "This product includes software developed by the
24    *        Apache Software Foundation (http://www.apache.org/)."
25    *    Alternately, this acknowledgment may appear in the software itself,
26    *    if and wherever such third-party acknowledgments normally appear.
27    *
28    * 4. The names "Apache" and "Apache Software Foundation" and
29    *    "Apache POI" must not be used to endorse or promote products
30    *    derived from this software without prior written permission. For
31    *    written permission, please contact apache@apache.org.
32    *
33    * 5. Products derived from this software may not be called "Apache",
34    *    "Apache POI", nor may "Apache" appear in their name, without
35    *    prior written permission of the Apache Software Foundation.
36    *
37    * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
38    * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
39    * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
40    * DISCLAIMED.  IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR
41    * ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
42    * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
43    * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
44    * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
45    * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
46    * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
47    * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
48    * SUCH DAMAGE.
49    * ====================================================================
50    *
51    * This software consists of voluntary contributions made by many
52    * individuals on behalf of the Apache Software Foundation.  For more
53    * information on the Apache Software Foundation, please see
54    * <http://www.apache.org/>.
55    */
56   
57   
58   /**
59    * Title:        Range Address <P>
60    * Description:  provides connectivity utilities for ranges<P>
61    *
62    *
63    * REFERENCE:  <P>
64    * @author IgOr KaTz && EuGeNe BuMaGiN (Tal Moshaiov) (VistaPortal LDT.)
65    * @version 1.0
66    */
67   
68   public class RangeAddress {
69     final static int WRONG_POS  = -1;
70     final static int MAX_HEIGHT = 66666;
71     final static char SO_FORMNAME_ENCLOSURE =  '\'';
72     String m_sheetName;
73     String m_cellFrom;
74     String m_cellTo;
75     
76     public RangeAddress (String _url) {
77       init (_url);
78     }
79     
80     public RangeAddress (int _startCol, int _startRow, int _endCol, int _endRow) {
81       init (numTo26Sys (_startCol) + _startRow + ":"
82       + numTo26Sys (_endCol) + _endRow);
83     }
84     
85     public String getAddress (){
86       String result = "";
87       if(m_sheetName != null)
88         result += m_sheetName;
89       if(m_cellFrom != null){
90         result += m_cellFrom;
91         if(m_cellTo != null)
92           result += ":" + m_cellTo;
93       }
94       return result;
95     }
96     
97     public String getSheetName (){
98       return m_sheetName;
99     }
100    
101    public String getRange (){
102      String result = "";
103      if(m_cellFrom != null){
104        result += m_cellFrom;
105        if(m_cellTo != null)
106          result += ":" + m_cellTo;
107      }
108      return result;
109    }
110    
111    public  boolean isCellOk (String _cell){
112      if (_cell != null){
113        if ( (getYPosition (_cell) != WRONG_POS) &&
114        (getXPosition (_cell) != WRONG_POS) )
115          return true;
116        else
117          return false;
118      } else
119        return false;
120    }
121    
122    public  boolean isSheetNameOk (){
123      return isSheetNameOk (m_sheetName);
124    }
125    
126    private  static boolean intern_isSheetNameOk (String _sheetName, boolean _canBeWaitSpace){
127      for (int i = 0  ; i < _sheetName.length (); i++){
128        char ch = _sheetName.charAt (i);
129        if (! (Character.isLetterOrDigit (ch) || (ch == '_')||
130        _canBeWaitSpace&&(ch == ' '))){
131          return false;
132        }
133      }
134      return true;
135    }
136    
137    public  static boolean isSheetNameOk (String _sheetName){
138      boolean res = false;
139      if ( ( _sheetName != null) && !_sheetName.equals ("")){
140        res = intern_isSheetNameOk (_sheetName,true);
141      }else
142        res = true;
143      return res;
144    }
145    
146    
147    public String getFromCell (){
148      return m_cellFrom;
149    }
150    
151    public String getToCell (){
152      return m_cellTo;
153    }
154    
155    public int getWidth (){
156      if(m_cellFrom != null && m_cellTo != null){
157        int toX    =  getXPosition (m_cellTo);
158        int fromX  =  getXPosition (m_cellFrom);
159        if ((toX == WRONG_POS) || (fromX == WRONG_POS)){
160          return 0;
161        }else
162          return toX - fromX + 1;
163      }
164      return 0;
165    }
166    
167    public int getHeight (){
168      if(m_cellFrom != null && m_cellTo != null){
169        int toY    =  getYPosition (m_cellTo);
170        int fromY  =  getYPosition (m_cellFrom);
171        if ((toY == WRONG_POS) || (fromY == WRONG_POS)){
172          return 0;
173        }else
174          return toY - fromY + 1;
175      }
176      return 0;
177    }
178    
179    public void setSize (int _width, int _height){
180      if(m_cellFrom == null)
181        m_cellFrom = "a1";
182      int tlX, tlY, rbX, rbY;
183      tlX = getXPosition (m_cellFrom);
184      tlY = getYPosition (m_cellFrom);
185      m_cellTo = numTo26Sys (tlX + _width - 1);
186      m_cellTo += String.valueOf (tlY + _height - 1);
187    }
188    
189    public boolean hasSheetName (){
190      if(m_sheetName == null)
191        return false;
192      return true;
193    }
194    
195    public boolean hasRange (){
196      if(m_cellFrom == null || m_cellTo == null)
197        return false;
198      return true;
199    }
200    
201    public boolean hasCell (){
202      if(m_cellFrom == null)
203        return false;
204      return true;
205    }
206    
207    private void init (String _url){
208  
209      _url = removeString(_url, "$");
210      _url = removeString(_url, "'");
211      
212      String[] urls = parseURL (_url);
213      m_sheetName = urls[0];
214      m_cellFrom = urls[1];
215      m_cellTo = urls[2];
216  
217      //What if range is one celled ?
218      if (m_cellTo == null){
219        m_cellTo = m_cellFrom;
220      }
221          
222      //Removing noneeds characters
223      m_cellTo    = removeString(m_cellTo,".");
224      
225      
226    }
227    
228    private String[] parseURL (String _url){
229      String[] result = new String[3];
230      int index = _url.indexOf(':');
231      if (index >= 0) {
232        String fromStr = _url.substring(0, index);
233        String toStr = _url.substring(index+1);
234        index = fromStr.indexOf('.');
235        if (index >= 0) {
236          result[0] = fromStr.substring(0, index);
237          result[1] = fromStr.substring(index+1);
238        } else {
239          result[1] = fromStr;
240        }
241        index = toStr.indexOf('.');
242        if (index >= 0) {
243          result[2] = toStr.substring(index+1); 
244        } else {
245          result[2] = toStr; 
246        }     
247      } else {
248        index = _url.indexOf('.');
249        if (index >= 0) {
250          result[0] = _url.substring(0, index);
251          result[1] = _url.substring(index+1);
252        } else {
253          result[1] = _url;
254        }
255      }
256      return result;
257    }
258    
259    public int getYPosition (String _subrange){
260      int result = WRONG_POS;
261      _subrange = _subrange.trim ();
262      if (_subrange.length () != 0){
263        String digitstr = getDigitPart (_subrange);
264        try {
265          result = Integer.parseInt (digitstr);
266          if (result  > MAX_HEIGHT){
267            result = WRONG_POS;
268          }
269        }
270        catch (Exception ex) {
271          
272          result = WRONG_POS;
273        }
274      }
275      return result;
276    }
277    
278    private static boolean isLetter (String _str){
279      boolean res = true;
280      if ( !_str.equals ("") ){
281        for (int i = 0  ; i < _str.length (); i++){
282          char ch = _str.charAt (i);
283          if (! Character.isLetter (ch)){
284            res = false;
285            break;
286          }
287        }
288      }else
289        res = false;
290      return res;
291    }
292    
293    public int getXPosition (String _subrange){
294      int result = WRONG_POS;
295      String tmp = filter$ (_subrange);
296      tmp = this.getCharPart (_subrange);
297      // we will process only 2 letters ranges
298      if (isLetter (tmp) && ((tmp.length () == 2)|| (tmp.length () == 1) )){
299        result =  get26Sys (tmp);
300      }
301      return result;
302    }
303    
304    public String getDigitPart (String _value){
305      String result = "";
306      int digitpos = getFirstDigitPosition (_value);
307      if(digitpos >= 0){
308        result = _value.substring (digitpos);
309      }
310      return result;
311    }
312    
313    public String getCharPart (String _value){
314      String result = "";
315      int digitpos = getFirstDigitPosition (_value);
316      if(digitpos >= 0){
317        result = _value.substring (0, digitpos);
318      }
319      return result;
320    }
321    
322    private String filter$ (String _range){
323      String res = "";
324      for (int i = 0 ; i < _range.length () ; i++){
325        char ch = _range.charAt (i);
326        if  ( ch != '$' ){
327          res = res + ch;
328        }
329      }
330      return res;
331    }
332    
333    private int getFirstDigitPosition (String _value){
334      int result = WRONG_POS;
335      if(_value != null && _value.trim ().length () == 0){
336        return result;
337      }
338      _value = _value.trim ();
339      int length = _value.length ();
340      for(int i = 0; i < length; i++){
341        if(Character.isDigit (_value.charAt (i))){
342          result = i;
343          break;
344        }
345      }
346      return result;
347    }
348    
349    public int get26Sys (String _s){
350      int sum = 0;
351      int multiplier = 1;
352      if (_s != "") {
353        for (int i = _s.length ()-1 ; i >= 0 ; i--){
354          char ch = _s.charAt (i);
355          int val =  Character.getNumericValue (ch) - Character.getNumericValue ('A')+1;
356          sum = sum + val * multiplier;
357          multiplier = multiplier * 26;
358        }
359        return sum;
360      }
361      return WRONG_POS;
362    }
363    
364    public String numTo26Sys (int _num){
365      int sum = 0;
366      int reminder;
367      String s ="";
368      do{
369        _num --;
370        reminder = _num % 26;
371        int val =  65 + reminder;
372        _num = _num / 26;
373        s = (char)val + s; // reverce
374      }while(_num > 0);
375      return s;
376    }
377    
378      public String replaceString(String _source , String _oldPattern,
379      String _newPattern){
380          StringBuffer res = new StringBuffer(_source);
381          int pos = -1;
382          
383          while ((pos = res.toString().indexOf(_oldPattern, pos)) > -1){
384              res.replace(pos, pos + _oldPattern.length(), _newPattern);
385          }
386          
387          return res.toString();
388      }
389      
390      public String removeString(String _source, String _match){
391          return replaceString(_source, _match, "");
392      }
393    
394  }
395