1    package org.apache.poi.hssf.util;
2    
3    
4    
5    /* ====================================================================
6    
7     * The Apache Software License, Version 1.1
8    
9     *
10   
11    * Copyright (c) 2002 The Apache Software Foundation.  All rights
12   
13    * reserved.
14   
15    *
16   
17    * Redistribution and use in source and binary forms, with or without
18   
19    * modification, are permitted provided that the following conditions
20   
21    * are met:
22   
23    *
24   
25    * 1. Redistributions of source code must retain the above copyright
26   
27    *    notice, this list of conditions and the following disclaimer.
28   
29    *
30   
31    * 2. Redistributions in binary form must reproduce the above copyright
32   
33    *    notice, this list of conditions and the following disclaimer in
34   
35    *    the documentation and/or other materials provided with the
36   
37    *    distribution.
38   
39    *
40   
41    * 3. The end-user documentation included with the redistribution,
42   
43    *    if any, must include the following acknowledgment:
44   
45    *       "This product includes software developed by the
46   
47    *        Apache Software Foundation (http://www.apache.org/)."
48   
49    *    Alternately, this acknowledgment may appear in the software itself,
50   
51    *    if and wherever such third-party acknowledgments normally appear.
52   
53    *
54   
55    * 4. The names "Apache" and "Apache Software Foundation" and
56   
57    *    "Apache POI" must not be used to endorse or promote products
58   
59    *    derived from this software without prior written permission. For
60   
61    *    written permission, please contact apache@apache.org.
62   
63    *
64   
65    * 5. Products derived from this software may not be called "Apache",
66   
67    *    "Apache POI", nor may "Apache" appear in their name, without
68   
69    *    prior written permission of the Apache Software Foundation.
70   
71    *
72   
73    * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED
74   
75    * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
76   
77    * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
78   
79    * DISCLAIMED.  IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR
80   
81    * ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
82   
83    * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
84   
85    * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
86   
87    * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
88   
89    * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
90   
91    * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
92   
93    * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
94   
95    * SUCH DAMAGE.
96   
97    * ====================================================================
98   
99    *
100  
101   * This software consists of voluntary contributions made by many
102  
103   * individuals on behalf of the Apache Software Foundation.  For more
104  
105   * information on the Apache Software Foundation, please see
106  
107   * <http://www.apache.org/>.
108  
109   */
110  
111  
112  
113  
114  
115  /**
116  
117   * Title:        Range Address <P>
118  
119   * Description:  provides connectivity utilities for ranges<P>
120  
121   *
122  
123   *
124  
125   * REFERENCE:  <P>
126  
127   * @author IgOr KaTz && EuGeNe BuMaGiN (Tal Moshaiov) (VistaPortal LDT.)
128  
129   * @version 1.0
130  
131   */
132  
133  
134  
135  public class RangeAddress {
136  
137    final static int WRONG_POS  = -1;
138  
139    final static int MAX_HEIGHT = 66666;
140  
141    final static char SO_FORMNAME_ENCLOSURE =  '\'';
142  
143    String m_sheetName;
144  
145    String m_cellFrom;
146  
147    String m_cellTo;
148  
149    
150  
151    public RangeAddress (String _url) {
152  
153      init (_url);
154  
155    }
156  
157    
158  
159    public RangeAddress (int _startCol, int _startRow, int _endCol, int _endRow) {
160  
161      init (numTo26Sys (_startCol) + _startRow + ":"
162  
163      + numTo26Sys (_endCol) + _endRow);
164  
165    }
166  
167    
168  
169    public String getAddress (){
170  
171      String result = "";
172  
173      if(m_sheetName != null)
174  
175        result += m_sheetName;
176  
177      if(m_cellFrom != null){
178  
179        result += m_cellFrom;
180  
181        if(m_cellTo != null)
182  
183          result += ":" + m_cellTo;
184  
185      }
186  
187      return result;
188  
189    }
190  
191    
192  
193    public String getSheetName (){
194  
195      return m_sheetName;
196  
197    }
198  
199    
200  
201    public String getRange (){
202  
203      String result = "";
204  
205      if(m_cellFrom != null){
206  
207        result += m_cellFrom;
208  
209        if(m_cellTo != null)
210  
211          result += ":" + m_cellTo;
212  
213      }
214  
215      return result;
216  
217    }
218  
219    
220  
221    public  boolean isCellOk (String _cell){
222  
223      if (_cell != null){
224  
225        if ( (getYPosition (_cell) != WRONG_POS) &&
226  
227        (getXPosition (_cell) != WRONG_POS) )
228  
229          return true;
230  
231        else
232  
233          return false;
234  
235      } else
236  
237        return false;
238  
239    }
240  
241    
242  
243    public  boolean isSheetNameOk (){
244  
245      return isSheetNameOk (m_sheetName);
246  
247    }
248  
249    
250  
251    private  static boolean intern_isSheetNameOk (String _sheetName, boolean _canBeWaitSpace){
252  
253      for (int i = 0  ; i < _sheetName.length (); i++){
254  
255        char ch = _sheetName.charAt (i);
256  
257        if (! (Character.isLetterOrDigit (ch) || (ch == '_')||
258  
259        _canBeWaitSpace&&(ch == ' '))){
260  
261          return false;
262  
263        }
264  
265      }
266  
267      return true;
268  
269    }
270  
271    
272  
273    public  static boolean isSheetNameOk (String _sheetName){
274  
275      boolean res = false;
276  
277      if ( ( _sheetName != null) && !_sheetName.equals ("")){
278  
279        res = intern_isSheetNameOk (_sheetName,true);
280  
281      }else
282  
283        res = true;
284  
285      return res;
286  
287    }
288  
289    
290  
291    
292  
293    public String getFromCell (){
294  
295      return m_cellFrom;
296  
297    }
298  
299    
300  
301    public String getToCell (){
302  
303      return m_cellTo;
304  
305    }
306  
307    
308  
309    public int getWidth (){
310  
311      if(m_cellFrom != null && m_cellTo != null){
312  
313        int toX    =  getXPosition (m_cellTo);
314  
315        int fromX  =  getXPosition (m_cellFrom);
316  
317        if ((toX == WRONG_POS) || (fromX == WRONG_POS)){
318  
319          return 0;
320  
321        }else
322  
323          return toX - fromX + 1;
324  
325      }
326  
327      return 0;
328  
329    }
330  
331    
332  
333    public int getHeight (){
334  
335      if(m_cellFrom != null && m_cellTo != null){
336  
337        int toY    =  getYPosition (m_cellTo);
338  
339        int fromY  =  getYPosition (m_cellFrom);
340  
341        if ((toY == WRONG_POS) || (fromY == WRONG_POS)){
342  
343          return 0;
344  
345        }else
346  
347          return toY - fromY + 1;
348  
349      }
350  
351      return 0;
352  
353    }
354  
355    
356  
357    public void setSize (int _width, int _height){
358  
359      if(m_cellFrom == null)
360  
361        m_cellFrom = "a1";
362  
363      int tlX, tlY, rbX, rbY;
364  
365      tlX = getXPosition (m_cellFrom);
366  
367      tlY = getYPosition (m_cellFrom);
368  
369      m_cellTo = numTo26Sys (tlX + _width - 1);
370  
371      m_cellTo += String.valueOf (tlY + _height - 1);
372  
373    }
374  
375    
376  
377    public boolean hasSheetName (){
378  
379      if(m_sheetName == null)
380  
381        return false;
382  
383      return true;
384  
385    }
386  
387    
388  
389    public boolean hasRange (){
390  
391      if(m_cellFrom == null || m_cellTo == null)
392  
393        return false;
394  
395      return true;
396  
397    }
398  
399    
400  
401    public boolean hasCell (){
402  
403      if(m_cellFrom == null)
404  
405        return false;
406  
407      return true;
408  
409    }
410  
411    
412  
413    private void init (String _url){
414  
415  
416  
417      _url = removeString(_url, "$");
418  
419      _url = removeString(_url, "'");
420  
421      
422  
423      String[] urls = parseURL (_url);
424  
425      m_sheetName = urls[0];
426  
427      m_cellFrom = urls[1];
428  
429      m_cellTo = urls[2];
430  
431  
432  
433      //What if range is one celled ?
434  
435      if (m_cellTo == null){
436  
437        m_cellTo = m_cellFrom;
438  
439      }
440  
441          
442  
443      //Removing noneeds characters
444  
445      m_cellTo    = removeString(m_cellTo,".");
446  
447      
448  
449      
450  
451    }
452  
453    
454  
455    private String[] parseURL (String _url){
456  
457      String[] result = new String[3];
458  
459      int index = _url.indexOf(':');
460  
461      if (index >= 0) {
462  
463        String fromStr = _url.substring(0, index);
464  
465        String toStr = _url.substring(index+1);
466  
467        index = fromStr.indexOf('.');
468  
469        if (index >= 0) {
470  
471          result[0] = fromStr.substring(0, index);
472  
473          result[1] = fromStr.substring(index+1);
474  
475        } else {
476  
477          result[1] = fromStr;
478  
479        }
480  
481        index = toStr.indexOf('.');
482  
483        if (index >= 0) {
484  
485          result[2] = toStr.substring(index+1); 
486  
487        } else {
488  
489          result[2] = toStr; 
490  
491        }     
492  
493      } else {
494  
495        index = _url.indexOf('.');
496  
497        if (index >= 0) {
498  
499          result[0] = _url.substring(0, index);
500  
501          result[1] = _url.substring(index+1);
502  
503        } else {
504  
505          result[1] = _url;
506  
507        }
508  
509      }
510  
511      return result;
512  
513    }
514  
515    
516  
517    public int getYPosition (String _subrange){
518  
519      int result = WRONG_POS;
520  
521      _subrange = _subrange.trim ();
522  
523      if (_subrange.length () != 0){
524  
525        String digitstr = getDigitPart (_subrange);
526  
527        try {
528  
529          result = Integer.parseInt (digitstr);
530  
531          if (result  > MAX_HEIGHT){
532  
533            result = WRONG_POS;
534  
535          }
536  
537        }
538  
539        catch (Exception ex) {
540  
541          
542  
543          result = WRONG_POS;
544  
545        }
546  
547      }
548  
549      return result;
550  
551    }
552  
553    
554  
555    private static boolean isLetter (String _str){
556  
557      boolean res = true;
558  
559      if ( !_str.equals ("") ){
560  
561        for (int i = 0  ; i < _str.length (); i++){
562  
563          char ch = _str.charAt (i);
564  
565          if (! Character.isLetter (ch)){
566  
567            res = false;
568  
569            break;
570  
571          }
572  
573        }
574  
575      }else
576  
577        res = false;
578  
579      return res;
580  
581    }
582  
583    
584  
585    public int getXPosition (String _subrange){
586  
587      int result = WRONG_POS;
588  
589      String tmp = filter$ (_subrange);
590  
591      tmp = this.getCharPart (_subrange);
592  
593      // we will process only 2 letters ranges
594  
595      if (isLetter (tmp) && ((tmp.length () == 2)|| (tmp.length () == 1) )){
596  
597        result =  get26Sys (tmp);
598  
599      }
600  
601      return result;
602  
603    }
604  
605    
606  
607    public String getDigitPart (String _value){
608  
609      String result = "";
610  
611      int digitpos = getFirstDigitPosition (_value);
612  
613      if(digitpos >= 0){
614  
615        result = _value.substring (digitpos);
616  
617      }
618  
619      return result;
620  
621    }
622  
623    
624  
625    public String getCharPart (String _value){
626  
627      String result = "";
628  
629      int digitpos = getFirstDigitPosition (_value);
630  
631      if(digitpos >= 0){
632  
633        result = _value.substring (0, digitpos);
634  
635      }
636  
637      return result;
638  
639    }
640  
641    
642  
643    private String filter$ (String _range){
644  
645      String res = "";
646  
647      for (int i = 0 ; i < _range.length () ; i++){
648  
649        char ch = _range.charAt (i);
650  
651        if  ( ch != '$' ){
652  
653          res = res + ch;
654  
655        }
656  
657      }
658  
659      return res;
660  
661    }
662  
663    
664  
665    private int getFirstDigitPosition (String _value){
666  
667      int result = WRONG_POS;
668  
669      if(_value != null && _value.trim ().length () == 0){
670  
671        return result;
672  
673      }
674  
675      _value = _value.trim ();
676  
677      int length = _value.length ();
678  
679      for(int i = 0; i < length; i++){
680  
681        if(Character.isDigit (_value.charAt (i))){
682  
683          result = i;
684  
685          break;
686  
687        }
688  
689      }
690  
691      return result;
692  
693    }
694  
695    
696  
697    public int get26Sys (String _s){
698  
699      int sum = 0;
700  
701      int multiplier = 1;
702  
703      if (_s != "") {
704  
705        for (int i = _s.length ()-1 ; i >= 0 ; i--){
706  
707          char ch = _s.charAt (i);
708  
709          int val =  Character.getNumericValue (ch) - Character.getNumericValue ('A')+1;
710  
711          sum = sum + val * multiplier;
712  
713          multiplier = multiplier * 26;
714  
715        }
716  
717        return sum;
718  
719      }
720  
721      return WRONG_POS;
722  
723    }
724  
725    
726  
727    public String numTo26Sys (int _num){
728  
729      int sum = 0;
730  
731      int reminder;
732  
733      String s ="";
734  
735      do{
736  
737        _num --;
738  
739        reminder = _num % 26;
740  
741        int val =  65 + reminder;
742  
743        _num = _num / 26;
744  
745        s = (char)val + s; // reverce
746  
747      }while(_num > 0);
748  
749      return s;
750  
751    }
752  
753    
754  
755      public String replaceString(String _source , String _oldPattern,
756  
757      String _newPattern){
758  
759          StringBuffer res = new StringBuffer(_source);
760  
761          int pos = -1;
762  
763          
764  
765          while ((pos = res.toString().indexOf(_oldPattern, pos)) > -1){
766  
767              res.replace(pos, pos + _oldPattern.length(), _newPattern);
768  
769          }
770  
771          
772  
773          return res.toString();
774  
775      }
776  
777      
778  
779      public String removeString(String _source, String _match){
780  
781          return replaceString(_source, _match, "");
782  
783      }
784  
785    
786  
787  }
788  
789  ??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????RangeAddress????????????????????WRONG_POS????????????????????MAX_HEIGHT?????????????????????SO_FORMNAME_ENCLOSURE??????????m_sheetName??????????m_cellFrom??????????m_cellTo??????????RangeAddress???????????_url??????????RangeAddress?????init???????????numTo26Sys???????????????????????_startCol????????????????????????????????????_startRow???????numTo26Sys???????????????????_endCol??????????????????????????????_endRow?????????????????getAddress????????m_sheetName???????result?????????????????m_sheetName????????m_cellFrom???????result?????????????????m_cellFrom??????????m_cellTo?????????result?????????????????????????m_cellTo????????????result?????????????????getSheetName?????????????????getRange????????m_cellFrom???????result?????????????????m_cellFrom??????????m_cellTo?????????result?????????????????????????m_cellTo????????????result???????????????????isCellOk?????????_cell?????????????getYPosition???????????????????????????_cell?????????????????????????????????????WRONG_POS????????getXPosition??????????????????????_cell????????????????????????????????WRONG_POS???????????????????isSheetNameOk????????????isSheetNameOk???????????????????????????m_sheetName???????????????????????????intern_isSheetNameOk???????????????????????i???????????????????????????_sheetName?????????????????????????????????????????????????i?????????????????_sheetName????????????????????????????????????i?????????????????????????????????????????ch?????????????????????????????????????????????????ch???????_canBeWaitSpace?????????????????????????ch??????????????????????????isSheetNameOk????????????_sheetName????????????????????????????????????_sheetName???????res?????????????intern_isSheetNameOk???????????????????????????????????_sheetName???????res????????????res?????????????????getFromCell?????????????????getToCell??????????????getWidth????????m_cellFrom??????????????????????????????m_cellTo?????????????????????getXPosition???????????????????????????????????m_cellTo?????????????????????getXPosition???????????????????????????????????m_cellFrom????????????toX???????????????????WRONG_POS??????????????????????????????????fromX???????????????????????????????????????????WRONG_POS????????????????toX??????????????????????fromX??????????????getHeight????????m_cellFrom??????????????????????????????m_cellTo?????????????????????getYPosition???????????????????????????????????m_cellTo?????????????????????getYPosition???????????????????????????????????m_cellFrom????????????toY???????????????????WRONG_POS??????????????????????????????????fromY???????????????????????????????????????????WRONG_POS????????????????toY??????????????????????fromY???????????????setSize????????m_cellFrom???????m_cellFrom?????tlX???????????getXPosition?????????????????????????m_cellFrom?????tlY???????????getYPosition?????????????????????????m_cellFrom?????m_cellTo????????????????numTo26Sys????????????????????????????tlX??????????????????????????????????_width?????m_cellTo?????????????????????????????????tlY???????????????????????????????????????_height??????????????????hasSheetName??????????????????hasRange??????????????????hasCell????????m_cellFrom????????????????init?????_url????????????removeString?????????????????????????_url?????_url????????????removeString?????????????????????????_url?????????????????????parseURL???????????????????????????????_url?????m_sheetName???????????????????urls?????m_cellFrom??????????????????urls?????m_cellTo????????????????urls?????????????????????????????????????????????m_cellTo???????m_cellTo??????????????????m_cellFrom???????????????????????????????????????m_cellTo???????????????????removeString????????????????????????????????m_cellTo????????????????????parseURL?????????????????_url?????????index????????????????????????_url??????????????????????????????????????????index??????????????????????_url?????????????????????????????????????index???????index???????????????fromStr???????????index?????????result?????????????????????fromStr??????????????????????????????????????????index?????????result?????????????????????fromStr???????????????????????????????????????index?????????result?????????????????????fromStr???????index???????????????toStr???????????index?????????result?????????????????????toStr?????????????????????????????????????index?????????result?????????????????????toStr???????index???????????????_url???????????index?????????result?????????????????????_url???????????????????????????????????????index?????????result?????????????????????_url????????????????????????????????????index?????????result?????????????????????_url????????????result??????????????getYPosition??????????????????WRONG_POS?????_subrange?????????????????_subrange?????????_subrange?????????????????????????getDigitPart???????????????????????????????????????_subrange?????????result????????????????????????????????????digitstr?????????????result???????????????????????MAX_HEIGHT???????????result????????????????????WRONG_POS?????????result??????????????????WRONG_POS????????????result??????????????????????????isLetter???????????_str?????????????????????????i?????????????????????????????_str?????????????????????????????????????????????i???????????????????_str????????????????????????????????i???????????????????????????????????ch???????????res???????res????????????res??????????????getXPosition??????????????????WRONG_POS??????????????????filter$???????????????????????????_subrange?????tmp?????????????????????????????_subrange??????????????????????????????????????????????????????isLetter???????????????????tmp?????????????????????????????tmp????????????????????????????????????????????????????tmp???????result?????????????????get26Sys???????????????????????????tmp????????????result?????????????????getDigitPart????????????????????getFirstDigitPosition???????????????????????????????????????????_value????????digitpos???????result????????????????_value??????????????????????????????????digitpos????????????result?????????????????getCharPart????????????????????getFirstDigitPosition???????????????????????????????????????????_value????????digitpos???????result????????????????_value?????????????????????????????????????digitpos????????????result??????????????????filter$??????????????????????i??????????????????????????_range?????????????????????????????????????????????i?????????????????_range????????????????????????????????i?????????????ch?????????res???????????????res?????????????????????ch????????????res???????????????getFirstDigitPosition??????????????????WRONG_POS????????_value??????????????????????????_value??????????????result?????_value??????????????_value??????????????????_value????????????????????i????????????????????????length????????????????????????????????i?????????????????????????????_value????????????????????????????????????????????i?????????result??????????????????i????????????result??????????????get26Sys?????????_s????????????????????_s?????????????????????????????????????i??????????????????????????????????????????????i???????????????????_s??????????????????????????????i???????????????????????????????????????????????ch?????????sum???????????????sum?????????????????????val???????????????????????????multiplier?????????multiplier??????????????????????multiplier??????????????sum????????????WRONG_POS?????????????????numTo26Sys???????_num???????reminder??????????????????_num???????????????????????reminder???????_num??????????????_num???????s?????????????????val???????????????????????s????????????????????????????????????????????????_num????????????s???????????????????replaceString?????????????????????????????????????????????_source?????????????????pos???????????????????????res??????????????????????????????????????????????_oldPattern???????????????????????????????????????????????????????????pos?????????????res?????????????????????????pos??????????????????????????????pos????????????????????????????????????_oldPattern??????????????????????????????????????????????????????????_newPattern????????????????res???????????????????removeString????????????????replaceString??????????????????????????????_source???????????????????????????????????????_match