001: /* ====================================================================
002: Licensed to the Apache Software Foundation (ASF) under one or more
003: contributor license agreements. See the NOTICE file distributed with
004: this work for additional information regarding copyright ownership.
005: The ASF licenses this file to You under the Apache License, Version 2.0
006: (the "License"); you may not use this file except in compliance with
007: the License. You may obtain a copy of the License at
008:
009: http://www.apache.org/licenses/LICENSE-2.0
010:
011: Unless required by applicable law or agreed to in writing, software
012: distributed under the License is distributed on an "AS IS" BASIS,
013: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
014: See the License for the specific language governing permissions and
015: limitations under the License.
016: ==================================================================== */
017:
018: package org.apache.poi.hssf.util;
019:
020: /**
021: * Title: Range Address <P>
022: * Description: provides connectivity utilities for ranges<P>
023: *
024: *
025: * REFERENCE: <P>
026: * @author IgOr KaTz && EuGeNe BuMaGiN (Tal Moshaiov) (VistaPortal LDT.)
027: @version 1.0
028: */
029:
030: public class RangeAddress {
031: final static int WRONG_POS = -1;
032: final static int MAX_HEIGHT = 66666;
033: final static char SO_FORMNAME_ENCLOSURE = '\'';
034: String m_sheetName;
035: String m_cellFrom;
036: String m_cellTo;
037:
038: /**
039: * Accepts an external reference from excel.
040: * <p>
041: * i.e. Sheet1!$A$4:$B$9
042: * @param _url
043: */
044: public RangeAddress(String _url) {
045: init(_url);
046: }
047:
048: public RangeAddress(int _startCol, int _startRow, int _endCol,
049: int _endRow) {
050: init(numTo26Sys(_startCol) + _startRow + ":"
051: + numTo26Sys(_endCol) + _endRow);
052: }
053:
054: /**
055: *
056: * @return String <b>note: </b> All absolute references are removed
057: */
058: public String getAddress() {
059: String result = "";
060: if (m_sheetName != null)
061: result += m_sheetName + "!";
062: if (m_cellFrom != null) {
063: result += m_cellFrom;
064: if (m_cellTo != null)
065: result += ":" + m_cellTo;
066: }
067: return result;
068: }
069:
070: public String getSheetName() {
071: return m_sheetName;
072: }
073:
074: public String getRange() {
075: String result = "";
076: if (m_cellFrom != null) {
077: result += m_cellFrom;
078: if (m_cellTo != null)
079: result += ":" + m_cellTo;
080: }
081: return result;
082: }
083:
084: public boolean isCellOk(String _cell) {
085: if (_cell != null) {
086: if ((getYPosition(_cell) != WRONG_POS)
087: && (getXPosition(_cell) != WRONG_POS))
088: return true;
089: else
090: return false;
091: } else
092: return false;
093: }
094:
095: public boolean isSheetNameOk() {
096: return isSheetNameOk(m_sheetName);
097: }
098:
099: private static boolean intern_isSheetNameOk(String _sheetName,
100: boolean _canBeWaitSpace) {
101: for (int i = 0; i < _sheetName.length(); i++) {
102: char ch = _sheetName.charAt(i);
103: if (!(Character.isLetterOrDigit(ch) || (ch == '_') || _canBeWaitSpace
104: && (ch == ' '))) {
105: return false;
106: }
107: }
108: return true;
109: }
110:
111: public static boolean isSheetNameOk(String _sheetName) {
112: boolean res = false;
113: if ((_sheetName != null) && !_sheetName.equals("")) {
114: res = intern_isSheetNameOk(_sheetName, true);
115: } else
116: res = true;
117: return res;
118: }
119:
120: public String getFromCell() {
121: return m_cellFrom;
122: }
123:
124: public String getToCell() {
125: return m_cellTo;
126: }
127:
128: public int getWidth() {
129: if (m_cellFrom != null && m_cellTo != null) {
130: int toX = getXPosition(m_cellTo);
131: int fromX = getXPosition(m_cellFrom);
132: if ((toX == WRONG_POS) || (fromX == WRONG_POS)) {
133: return 0;
134: } else
135: return toX - fromX + 1;
136: }
137: return 0;
138: }
139:
140: public int getHeight() {
141: if (m_cellFrom != null && m_cellTo != null) {
142: int toY = getYPosition(m_cellTo);
143: int fromY = getYPosition(m_cellFrom);
144: if ((toY == WRONG_POS) || (fromY == WRONG_POS)) {
145: return 0;
146: } else
147: return toY - fromY + 1;
148: }
149: return 0;
150: }
151:
152: public void setSize(int _width, int _height) {
153: if (m_cellFrom == null)
154: m_cellFrom = "a1";
155: int tlX, tlY, rbX, rbY;
156: tlX = getXPosition(m_cellFrom);
157: tlY = getYPosition(m_cellFrom);
158: m_cellTo = numTo26Sys(tlX + _width - 1);
159: m_cellTo += String.valueOf(tlY + _height - 1);
160: }
161:
162: public boolean hasSheetName() {
163: if (m_sheetName == null)
164: return false;
165: return true;
166: }
167:
168: public boolean hasRange() {
169: return (m_cellFrom != null && m_cellTo != null && !m_cellFrom
170: .equals(m_cellTo));
171: }
172:
173: public boolean hasCell() {
174: if (m_cellFrom == null)
175: return false;
176: return true;
177: }
178:
179: private void init(String _url) {
180:
181: _url = removeString(_url, "$");
182: _url = removeString(_url, "'");
183:
184: String[] urls = parseURL(_url);
185: m_sheetName = urls[0];
186: m_cellFrom = urls[1];
187: m_cellTo = urls[2];
188:
189: //What if range is one celled ?
190: if (m_cellTo == null) {
191: m_cellTo = m_cellFrom;
192: }
193:
194: //Removing noneeds characters
195: m_cellTo = removeString(m_cellTo, ".");
196:
197: }
198:
199: private String[] parseURL(String _url) {
200: String[] result = new String[3];
201: int index = _url.indexOf(':');
202: if (index >= 0) {
203: String fromStr = _url.substring(0, index);
204: String toStr = _url.substring(index + 1);
205: index = fromStr.indexOf('!');
206: if (index >= 0) {
207: result[0] = fromStr.substring(0, index);
208: result[1] = fromStr.substring(index + 1);
209: } else {
210: result[1] = fromStr;
211: }
212: index = toStr.indexOf('!');
213: if (index >= 0) {
214: result[2] = toStr.substring(index + 1);
215: } else {
216: result[2] = toStr;
217: }
218: } else {
219: index = _url.indexOf('!');
220: if (index >= 0) {
221: result[0] = _url.substring(0, index);
222: result[1] = _url.substring(index + 1);
223: } else {
224: result[1] = _url;
225: }
226: }
227: return result;
228: }
229:
230: public int getYPosition(String _subrange) {
231: int result = WRONG_POS;
232: _subrange = _subrange.trim();
233: if (_subrange.length() != 0) {
234: String digitstr = getDigitPart(_subrange);
235: try {
236: result = Integer.parseInt(digitstr);
237: if (result > MAX_HEIGHT) {
238: result = WRONG_POS;
239: }
240: } catch (Exception ex) {
241:
242: result = WRONG_POS;
243: }
244: }
245: return result;
246: }
247:
248: private static boolean isLetter(String _str) {
249: boolean res = true;
250: if (!_str.equals("")) {
251: for (int i = 0; i < _str.length(); i++) {
252: char ch = _str.charAt(i);
253: if (!Character.isLetter(ch)) {
254: res = false;
255: break;
256: }
257: }
258: } else
259: res = false;
260: return res;
261: }
262:
263: public int getXPosition(String _subrange) {
264: int result = WRONG_POS;
265: String tmp = filter$(_subrange);
266: tmp = this .getCharPart(_subrange);
267: // we will process only 2 letters ranges
268: if (isLetter(tmp)
269: && ((tmp.length() == 2) || (tmp.length() == 1))) {
270: result = get26Sys(tmp);
271: }
272: return result;
273: }
274:
275: public String getDigitPart(String _value) {
276: String result = "";
277: int digitpos = getFirstDigitPosition(_value);
278: if (digitpos >= 0) {
279: result = _value.substring(digitpos);
280: }
281: return result;
282: }
283:
284: public String getCharPart(String _value) {
285: String result = "";
286: int digitpos = getFirstDigitPosition(_value);
287: if (digitpos >= 0) {
288: result = _value.substring(0, digitpos);
289: }
290: return result;
291: }
292:
293: private String filter$(String _range) {
294: String res = "";
295: for (int i = 0; i < _range.length(); i++) {
296: char ch = _range.charAt(i);
297: if (ch != '$') {
298: res = res + ch;
299: }
300: }
301: return res;
302: }
303:
304: private int getFirstDigitPosition(String _value) {
305: int result = WRONG_POS;
306: if (_value != null && _value.trim().length() == 0) {
307: return result;
308: }
309: _value = _value.trim();
310: int length = _value.length();
311: for (int i = 0; i < length; i++) {
312: if (Character.isDigit(_value.charAt(i))) {
313: result = i;
314: break;
315: }
316: }
317: return result;
318: }
319:
320: public int get26Sys(String _s) {
321: int sum = 0;
322: int multiplier = 1;
323: if (_s != "") {
324: for (int i = _s.length() - 1; i >= 0; i--) {
325: char ch = _s.charAt(i);
326: int val = Character.getNumericValue(ch)
327: - Character.getNumericValue('A') + 1;
328: sum = sum + val * multiplier;
329: multiplier = multiplier * 26;
330: }
331: return sum;
332: }
333: return WRONG_POS;
334: }
335:
336: public String numTo26Sys(int _num) {
337: int sum = 0;
338: int reminder;
339: String s = "";
340: do {
341: _num--;
342: reminder = _num % 26;
343: int val = 65 + reminder;
344: _num = _num / 26;
345: s = (char) val + s; // reverce
346: } while (_num > 0);
347: return s;
348: }
349:
350: public String replaceString(String _source, String _oldPattern,
351: String _newPattern) {
352: StringBuffer res = new StringBuffer(_source);
353: int pos = -1;
354:
355: while ((pos = res.toString().indexOf(_oldPattern, pos)) > -1) {
356: res.replace(pos, pos + _oldPattern.length(), _newPattern);
357: }
358:
359: return res.toString();
360: }
361:
362: public String removeString(String _source, String _match) {
363: return replaceString(_source, _match, "");
364: }
365:
366: }
|