1
6
7 package org.apache.poi.hssf.util;
8
9
13 public class ReferenceUtil {
14
15
16 private ReferenceUtil() {
17 }
18
19
22 public static int[] getXYXYFromAreaRef(String reference) {
23 int retval[] = null;
24 String[] refs = seperateAreaRefs(reference);
25 int[] xy1 = getXYFromReference(refs[0]);
26 int[] xy2 = getXYFromReference(refs[1]);
27 retval = new int[] {xy1[0],xy1[1],xy2[0],xy2[1]};
28 return retval;
29 }
30
31
38 public static int[] getXYFromReference(String reference) {
39 int[] retval = new int[2];
40 String[] parts = seperateRowColumns(reference);
41 retval[1] = convertColStringToNum(parts[0]);
42 retval[0] = Integer.parseInt(parts[1])-1;
43 return retval;
44 }
45
46
52 public static String getReferenceFromXY(int row, int col) {
53 String retval = convertNumToColString(col) + ""+(row+1);
54 return retval;
55 }
56
57
60 private static String convertNumToColString(int col) {
61 String retval = null;
62 int mod = col % 26;
63 int div = col / 26;
64 char small=(char)(mod + 65);
65 char big = (char)(div + 64);
66
67 if (div == 0) {
68 retval = ""+small;
69 } else {
70 retval = ""+big+""+small;
71 }
72
73 return retval;
74 }
75
76
80 private static int convertColStringToNum(String ref) {
81 int len = ref.length();
82 int retval=0;
83 int pos = 0;
84 for (int k = ref.length()-1; k > -1; k--) {
85 char thechar = ref.charAt(k);
86 if ( pos == 0) {
87 retval += (Character.getNumericValue(thechar)-9);
88 } else {
89 retval += (Character.getNumericValue(thechar)-9) * (pos * 26);
90 }
91 pos++;
92 }
93 return retval-1;
94 }
95
96
97
102 private static String[] seperateRowColumns(String reference) {
103 int loc = 0;
104 String retval[] = new String[2];
105 int length = reference.length();
106
107 char[] chars = reference.toCharArray();
108
109 for (loc = 0; loc < chars.length; loc++) {
110 if (Character.isDigit(chars[loc])) {
111 break;
112 }
113 }
114
115 retval[0] = reference.substring(0,loc);
116 retval[1] = reference.substring(loc);
117 return retval;
118 }
119
120
121
125 private static String[] seperateAreaRefs(String reference) {
126 String retval[] = new String[2];
127 int length = reference.length();
128
129 int loc = reference.indexOf(':',0);
130
131 retval[0] = reference.substring(0,loc);
132 retval[1] = reference.substring(loc+1);
133 return retval;
134 }
135
136
137 }
138