1 /* ====================================================================
2 Licensed to the Apache Software Foundation (ASF) under one or more
3 contributor license agreements. See the NOTICE file distributed with
4 this work for additional information regarding copyright ownership.
5 The ASF licenses this file to You under the Apache License, Version 2.0
6 (the "License"); you may not use this file except in compliance with
7 the License. You may obtain a copy of the License at
8
9 http://www.apache.org/licenses/LICENSE-2.0
10
11 Unless required by applicable law or agreed to in writing, software
12 distributed under the License is distributed on an "AS IS" BASIS,
13 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14 See the License for the specific language governing permissions and
15 limitations under the License.
16 ==================================================================== */
17
18 package org.apache.poi.hssf.util;
19
20 import org.apache.poi.hssf.record.formula.SheetNameFormatter;
21
22 /**
23 *
24 * @author Avik Sengupta
25 * @author Dennis Doubleday (patch to seperateRowColumns())
26 */
27 public final class CellReference {
28 /** The character ($) that signifies a row or column value is absolute instead of relative */
29 private static final char ABSOLUTE_REFERENCE_MARKER = '$';
30 /** The character (!) that separates sheet names from cell references */
31 private static final char SHEET_NAME_DELIMITER = '!';
32 /** The character (') used to quote sheet names when they contain special characters */
33 private static final char SPECIAL_NAME_DELIMITER = '\'';
34
35
36 private final int _rowIndex;
37 private final int _colIndex;
38 private final String _sheetName;
39 private final boolean _isRowAbs;
40 private final boolean _isColAbs;
41
42 /**
43 * Create an cell ref from a string representation. Sheet names containing special characters should be
44 * delimited and escaped as per normal syntax rules for formulas.
45 */
46 public CellReference(String cellRef) {
47 String[] parts = separateRefParts(cellRef);
48 _sheetName = parts[0];
49 String colRef = parts[1];
50 if (colRef.length() < 1) {
51 throw new IllegalArgumentException("Invalid Formula cell reference: '"+cellRef+"'");
52 }
53 _isColAbs = colRef.charAt(0) == '$';
54 if (_isColAbs) {
55 colRef=colRef.substring(1);
56 }
57 _colIndex = convertColStringToNum(colRef);
58
59 String rowRef=parts[2];
60 if (rowRef.length() < 1) {
61 throw new IllegalArgumentException("Invalid Formula cell reference: '"+cellRef+"'");
62 }
63 _isRowAbs = rowRef.charAt(0) == '$';
64 if (_isRowAbs) {
65 rowRef=rowRef.substring(1);
66 }
67 _rowIndex = Integer.parseInt(rowRef)-1; // -1 to convert 1-based to zero-based
68 }
69
70 public CellReference(int pRow, int pCol, boolean pAbsRow, boolean pAbsCol) {
71 this(null, pRow, pCol, pAbsRow, pAbsCol);
72 }
73 public CellReference(String pSheetName, int pRow, int pCol, boolean pAbsRow, boolean pAbsCol) {
74 // TODO - "-1" is a special value being temporarily used for whole row and whole column area references.
75 // so these checks are currently N.Q.R.
76 if(pRow < -1) {
77 throw new IllegalArgumentException("row index may not be negative");
78 }
79 if(pCol < -1) {
80 throw new IllegalArgumentException("column index may not be negative");
81 }
82 _sheetName = pSheetName;
83 _rowIndex=pRow;
84 _colIndex=pCol;
85 _isRowAbs = pAbsRow;
86 _isColAbs=pAbsCol;
87 }
88
89 public int getRow(){return _rowIndex;}
90 public short getCol(){return (short) _colIndex;}
91 public boolean isRowAbsolute(){return _isRowAbs;}
92 public boolean isColAbsolute(){return _isColAbs;}
93 /**
94 * @return possibly <code>null</code> if this is a 2D reference. Special characters are not
95 * escaped or delimited
96 */
97 public String getSheetName(){
98 return _sheetName;
99 }
100
101 /**
102 * takes in a column reference portion of a CellRef and converts it from
103 * ALPHA-26 number format to 0-based base 10.
104 */
105 private int convertColStringToNum(String ref) {
106 int lastIx = ref.length()-1;
107 int retval=0;
108 int pos = 0;
109
110 for (int k = lastIx; k > -1; k--) {
111 char thechar = ref.charAt(k);
112 if ( pos == 0) {
113 retval += (Character.getNumericValue(thechar)-9);
114 } else {
115 retval += (Character.getNumericValue(thechar)-9) * (pos * 26);
116 }
117 pos++;
118 }
119 return retval-1;
120 }
121
122
123 /**
124 * Separates the row from the columns and returns an array of three Strings. The first element
125 * is the sheet name. Only the first element may be null. The second element in is the column
126 * name still in ALPHA-26 number format. The third element is the row.
127 */
128 private static String[] separateRefParts(String reference) {
129
130 int plingPos = reference.lastIndexOf(SHEET_NAME_DELIMITER);
131 String sheetName = parseSheetName(reference, plingPos);
132 int start = plingPos+1;
133
134 int length = reference.length();
135
136
137 int loc = start;
138 // skip initial dollars
139 if (reference.charAt(loc)==ABSOLUTE_REFERENCE_MARKER) {
140 loc++;
141 }
142 // step over column name chars until first digit (or dollars) for row number.
143 for (; loc < length; loc++) {
144 char ch = reference.charAt(loc);
145 if (Character.isDigit(ch) || ch == ABSOLUTE_REFERENCE_MARKER) {
146 break;
147 }
148 }
149 return new String[] {
150 sheetName,
151 reference.substring(start,loc),
152 reference.substring(loc),
153 };
154 }
155
156 private static String parseSheetName(String reference, int indexOfSheetNameDelimiter) {
157 if(indexOfSheetNameDelimiter < 0) {
158 return null;
159 }
160
161 boolean isQuoted = reference.charAt(0) == SPECIAL_NAME_DELIMITER;
162 if(!isQuoted) {
163 return reference.substring(0, indexOfSheetNameDelimiter);
164 }
165 int lastQuotePos = indexOfSheetNameDelimiter-1;
166 if(reference.charAt(lastQuotePos) != SPECIAL_NAME_DELIMITER) {
167 throw new RuntimeException("Mismatched quotes: (" + reference + ")");
168 }
169
170 // TODO - refactor cell reference parsing logic to one place.
171 // Current known incarnations:
172 // FormulaParser.GetName()
173 // CellReference.parseSheetName() (here)
174 // AreaReference.separateAreaRefs()
175 // SheetNameFormatter.format() (inverse)
176
177 StringBuffer sb = new StringBuffer(indexOfSheetNameDelimiter);
178
179 for(int i=1; i<lastQuotePos; i++) { // Note boundaries - skip outer quotes
180 char ch = reference.charAt(i);
181 if(ch != SPECIAL_NAME_DELIMITER) {
182 sb.append(ch);
183 continue;
184 }
185 if(i < lastQuotePos) {
186 if(reference.charAt(i+1) == SPECIAL_NAME_DELIMITER) {
187 // two consecutive quotes is the escape sequence for a single one
188 i++; // skip this and keep parsing the special name
189 sb.append(ch);
190 continue;
191 }
192 }
193 throw new RuntimeException("Bad sheet name quote escaping: (" + reference + ")");
194 }
195 return sb.toString();
196 }
197
198 /**
199 * Takes in a 0-based base-10 column and returns a ALPHA-26
200 * representation.
201 * eg column #3 -> D
202 */
203 protected static String convertNumToColString(int col) {
204 String retval = null;
205 int mod = col % 26;
206 int div = col / 26;
207 char small=(char)(mod + 65);
208 char big = (char)(div + 64);
209
210 if (div == 0) {
211 retval = ""+small;
212 } else {
213 retval = ""+big+""+small;
214 }
215
216 return retval;
217 }
218
219 /**
220 * Example return values:
221 * <table border="0" cellpadding="1" cellspacing="0" summary="Example return values">
222 * <tr><th align='left'>Result</th><th align='left'>Comment</th></tr>
223 * <tr><td>A1</td><td>Cell reference without sheet</td></tr>
224 * <tr><td>Sheet1!A1</td><td>Standard sheet name</td></tr>
225 * <tr><td>'O''Brien''s Sales'!A1' </td><td>Sheet name with special characters</td></tr>
226 * </table>
227 * @return the text representation of this cell reference as it would appear in a formula.
228 */
229 public String formatAsString() {
230 StringBuffer sb = new StringBuffer(32);
231 if(_sheetName != null) {
232 SheetNameFormatter.appendFormat(sb, _sheetName);
233 sb.append(SHEET_NAME_DELIMITER);
234 }
235 appendCellReference(sb);
236 return sb.toString();
237 }
238
239 public String toString() {
240 StringBuffer sb = new StringBuffer(64);
241 sb.append(getClass().getName()).append(" [");
242 sb.append(formatAsString());
243 sb.append("]");
244 return sb.toString();
245 }
246
247 /**
248 * Appends cell reference with '$' markers for absolute values as required.
249 * Sheet name is not included.
250 */
251 /* package */ void appendCellReference(StringBuffer sb) {
252 if(_isColAbs) {
253 sb.append(ABSOLUTE_REFERENCE_MARKER);
254 }
255 sb.append( convertNumToColString(_colIndex));
256 if(_isRowAbs) {
257 sb.append(ABSOLUTE_REFERENCE_MARKER);
258 }
259 sb.append(_rowIndex+1);
260 }
261 }
Save This Page