001package com.hfg.xml.msofficexml.xlsx;
002
003
004import com.hfg.exception.InvalidValueException;
005import com.hfg.util.CompareUtil;
006import com.hfg.util.StringUtil;
007import com.hfg.util.collection.CollectionUtil;
008
009import java.util.regex.Matcher;
010import java.util.regex.Pattern;
011
012//------------------------------------------------------------------------------
013/**
014 Represents a cell location in a spreadsheet (ex: 'B11').
015
016 @author J. Alex Taylor, hairyfatguy.com
017 */
018//------------------------------------------------------------------------------
019// com.hfg XML/HTML Coding Library
020//
021// This library is free software; you can redistribute it and/or
022// modify it under the terms of the GNU Lesser General Public
023// License as published by the Free Software Foundation; either
024// version 2.1 of the License, or (at your option) any later version.
025//
026// This library is distributed in the hope that it will be useful,
027// but WITHOUT ANY WARRANTY; without even the implied warranty of
028// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
029// Lesser General Public License for more details.
030//
031// You should have received a copy of the GNU Lesser General Public
032// License along with this library; if not, write to the Free Software
033// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
034//
035// J. Alex Taylor, President, Founder, CEO, COO, CFO, OOPS hairyfatguy.com
036// jataylor@hairyfatguy.com
037//------------------------------------------------------------------------------
038
039public class CellRef implements Comparable
040{
041   private Integer mColIndex; // 1-based column index
042   private Integer mRowIndex; // 1-based row index
043   private String  mRefString;
044
045   private static final Pattern CELL_REF_PATTERN = Pattern.compile("([A-Z]{1,3})(\\d+)");
046
047   //###########################################################################
048   // CONSTRUCTORS
049   //###########################################################################
050
051   //---------------------------------------------------------------------------
052   public CellRef()
053   {
054
055   }
056
057   //---------------------------------------------------------------------------
058   public CellRef(String inValue)
059   {
060      if (StringUtil.isSet(inValue))
061      {
062         mRefString = inValue.toUpperCase().trim();
063         Matcher m = CELL_REF_PATTERN.matcher(mRefString);
064         if (! m.matches())
065         {
066            throw new InvalidValueException("The cell reference " + StringUtil.singleQuote(inValue) + " is not in a valid format!");
067         }
068
069         mColIndex = 0;
070         int colPosition = m.group(1).length();
071         for (char colChar : m.group(1).toCharArray())
072         {
073            mColIndex += (int) (Math.pow(26, colPosition - 1)) * ((int) colChar - 64);
074            colPosition--;
075         }
076
077         mRowIndex = Integer.parseInt(m.group(2));
078      }
079   }
080
081   //###########################################################################
082   // PUBLIC METHODS
083   //###########################################################################
084
085   //---------------------------------------------------------------------------
086   @Override
087   public String toString()
088   {
089      return mRefString;
090   }
091
092   //---------------------------------------------------------------------------
093   /**
094    Specifies the column index as a 1-based number.
095    */
096   public CellRef setColIndex(int inValue)
097   {
098      mColIndex = inValue;
099      calcRefString();
100      return this;
101   }
102
103   //---------------------------------------------------------------------------
104   /**
105    Returns the column index as a 1-based number.
106    */
107   public Integer getColIndex()
108   {
109      return mColIndex;
110   }
111
112   //---------------------------------------------------------------------------
113   /**
114    Returns the column as a String.
115    */
116   public String getCol()
117   {
118      return mColIndex != null ? colIndexToString(mColIndex) : null;
119   }
120
121   //---------------------------------------------------------------------------
122   /**
123    Returns the next column as a String.
124    */
125   public String nextCol()
126   {
127      return mColIndex != null ? colIndexToString(mColIndex + 1) : null;
128   }
129
130   //---------------------------------------------------------------------------
131   /**
132    Specifies the row index as a 1-based number.
133    */
134   public CellRef setRowIndex(int inValue)
135   {
136      mRowIndex = inValue;
137      calcRefString();
138      return this;
139   }
140
141   //---------------------------------------------------------------------------
142   /**
143    Returns the row index as a 1-based number.
144    */
145   public Integer getRowIndex()
146   {
147      return mRowIndex;
148   }
149
150   //---------------------------------------------------------------------------
151   @Override
152   public boolean equals(Object inObj2)
153   {
154      return (inObj2 != null
155              && inObj2 instanceof CellRef
156              && 0 == compareTo(inObj2));
157   }
158
159   //--------------------------------------------------------------------------
160   @Override
161   public int hashCode()
162   {
163      int hashCode = 0;
164
165      if (getColIndex() != null)
166      {
167         hashCode = getColIndex();
168      }
169
170      if (getRowIndex() != null)
171      {
172         hashCode += 31 * getRowIndex();
173      }
174
175      return hashCode;
176   }
177
178   //---------------------------------------------------------------------------
179   public int compareTo(Object inObj2)
180   {
181      int result = 1;
182
183      if (inObj2 instanceof CellRef)
184      {
185         CellRef cellRef2 = (CellRef) inObj2;
186
187         result = CompareUtil.compare(getColIndex(), cellRef2.getColIndex());
188
189         if (0 == result)
190         {
191            result = CompareUtil.compare(getRowIndex(), cellRef2.getRowIndex());
192         }
193      }
194
195      return result;
196   }
197
198   //###########################################################################
199   // PRIVATE METHODS
200   //###########################################################################
201
202   //---------------------------------------------------------------------------
203   private void calcRefString()
204   {
205      mRefString = null;
206      if (mColIndex != null
207          && mRowIndex != null)
208      {
209         mRefString = String.format("%s%d", colIndexToString(mColIndex), mRowIndex);
210      }
211   }
212
213   //---------------------------------------------------------------------------
214   // Recursively turns a column index into an A-Z string reference.
215   private static String colIndexToString(int inColIndex)
216   {
217      int baseValue = (int) 'A';
218      int zeroBasedColIndex = inColIndex - 1;
219
220      String refString = "";
221
222      if (inColIndex > 26)
223      {
224         refString = colIndexToString(zeroBasedColIndex / 26) ;
225      }
226
227      return refString + (char) (baseValue + (zeroBasedColIndex % 26) );
228   }
229}