1 /**************************************************************
2  *
3  * Licensed to the Apache Software Foundation (ASF) under one
4  * or more contributor license agreements.  See the NOTICE file
5  * distributed with this work for additional information
6  * regarding copyright ownership.  The ASF licenses this file
7  * to you under the Apache License, Version 2.0 (the
8  * "License"); you may not use this file except in compliance
9  * with the License.  You may obtain a copy of the License at
10  *
11  *   http://www.apache.org/licenses/LICENSE-2.0
12  *
13  * Unless required by applicable law or agreed to in writing,
14  * software distributed under the License is distributed on an
15  * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
16  * KIND, either express or implied.  See the License for the
17  * specific language governing permissions and limitations
18  * under the License.
19  *
20  *************************************************************/
21 
22 package testlib.gui;
23 
24 import static org.openoffice.test.vcl.Tester.*;
25 import static testlib.gui.UIMap.*;
26 
27 import java.lang.reflect.Array;
28 import java.util.StringTokenizer;
29 import java.util.logging.Logger;
30 
31 public class SCTool {
32 
33 	private static Logger LOG = Logger.getLogger(SCTool.class.getName());
34 
35 	/**
36 	 * Select a range.
37 	 *
38 	 * @param range
39 	 *            e.g. Sheet1.A10, Sheet1.A11:B30, A11, A30:B45
40 	 */
selectRange(String range)41 	public static void selectRange(String range) {
42 		scInputBarPosition.click(0.5, 0.5);
43 		scInputBarPosition.setText(range);
44 		typeKeys("<enter>");
45 		sleep(1);
46 	}
47 
48 	/**
49 	 * Get the input at the given cell. If the cell is a formula, return the
50 	 * formula rather than the result
51 	 *
52 	 * @param cell
53 	 * @return
54 	 */
getCellInput(String cell)55 	public static String getCellInput(String cell) {
56 		if (cell != null)
57 			selectRange(cell);
58 		String caption = scInputBarInput.getCaption();
59 		// Fix: When formulaEdit's caption is blank, the hook will return the
60 		// document window's caption
61 		// So if it's document window caption, return ""
62 		return caption.contains("[12479]") ? "" : caption;
63 	}
64 
65 	/**
66 	 * Get the text at the given cell. If the cell is a formula, return the
67 	 * result rather than the formula
68 	 *
69 	 * @param cell
70 	 * @return
71 	 */
getCellText(String cell)72 	public static String getCellText(String cell) {
73 		app.setClipboard("$$$$");
74 		selectRange(cell);
75 		typeKeys("<$copy>");
76 		String content = app.getClipboard();
77 		if (content.endsWith("\r\n"))
78 			content = content.substring(0, content.length() - 2);
79 		else if (content.endsWith("\n"))
80 			content = content.substring(0, content.length() - 1);
81 
82 		return content;
83 	}
84 
85 	/**
86 	 * convert the format of column number to integer e.g. A -> 1 AA -> 27 AMJ
87 	 * -> 1024
88 	 *
89 	 * @param no
90 	 * @return
91 	 */
toIntColumnNo(String no)92 	public static int toIntColumnNo(String no) {
93 		int len = no.length();
94 		int ret = 0;
95 		for (int i = 0; i < len; i++) {
96 			char c = no.charAt(len - i - 1);
97 			ret += Math.pow(26, i) * (c - 'A' + 1);
98 		}
99 
100 		return ret;
101 	}
102 
103 	/**
104 	 * Convert the format of column number to char
105 	 *
106 	 * @param no
107 	 * @return
108 	 */
toCharColumnNo(int no)109 	public static String toCharColumnNo(int no) {
110 		String ret = "";
111 		int f = 0;
112 		do {
113 			f = (no - 1) / 26;
114 			int s = (no - 1) % 26;
115 			ret = (char) ('A' + s) + ret;
116 			no = f;
117 		} while (f != 0);
118 		return ret;
119 	}
120 
121 	/**
122 	 * Parse location string into integer values
123 	 *
124 	 * @param loc
125 	 *            e.g. A1
126 	 * @return
127 	 */
parseLocation(String loc)128 	public static int[] parseLocation(String loc) {
129 		int i = 0;
130 		for (; i < loc.length(); i++) {
131 			char c = loc.charAt(i);
132 			if (c >= '0' && c <= '9')
133 				break;
134 		}
135 
136 		String col = loc.substring(0, i);
137 		String row = loc.substring(i);
138 		int sC = toIntColumnNo(col);
139 		int sR = Integer.parseInt(row);
140 		return new int[] { sC, sR };
141 	}
142 
143 	/**
144 	 * Parse range string into integer values
145 	 *
146 	 * @param range
147 	 *            e.g. A3:F9
148 	 * @return
149 	 */
parseRange(String range)150 	public static int[] parseRange(String range) {
151 		int dotIndex = range.indexOf(".");
152 		if (dotIndex != -1) {
153 			range = range.substring(dotIndex + 1);
154 		}
155 
156 		String[] locs = range.split(":");
157 		int[] ret = new int[4];
158 		int[] start = parseLocation(locs[0]);
159 		ret[0] = start[0];
160 		ret[1] = start[1];
161 		if (locs.length == 1) {
162 			ret[2] = start[0];
163 			ret[3] = start[1];
164 		} else {
165 			int[] end = parseLocation(locs[1]);
166 			ret[2] = end[0];
167 			ret[3] = end[1];
168 		}
169 
170 		return ret;
171 	}
172 
173 	/**
174 	 * Get the text at the given cells. If the cell is a formula, return the
175 	 * result rather than the formula. Note:
176 	 *
177 	 * @param range
178 	 *            e.g. A3:D9
179 	 * @return
180 	 */
getCellTexts(String range)181 	public static String[][] getCellTexts(String range) {
182 		selectRange(range);
183 		int[] intRange = parseRange(range);
184 		int rowCount = intRange[3] - intRange[1] + 1;
185 		int colCount = intRange[2] - intRange[0] + 1;
186 		String[][] texts = new String[rowCount][colCount];
187 
188 		app.setClipboard("$$$$");
189 		typeKeys("<$copy>");
190 		sleep(1);
191 		String text = app.getClipboard();
192 		StringTokenizer tokenizer = new StringTokenizer(text, "\"\t\n", true);
193 		int state = 0;
194 		String cellContent = "";
195 		int r = 0, c = 0;
196 		while (tokenizer.hasMoreTokens()) {
197 			String token = tokenizer.nextToken();
198 			switch (state) {
199 			case 0:
200 				if ("\"".equals(token)) {
201 					state = 1;
202 				} else if ("\t".equals(token)) {
203 					texts[r][c] = cellContent;
204 					cellContent = "";
205 					c++;
206 				} else if ("\n".equals(token)) {
207 					if (cellContent.endsWith("\r"))
208 						cellContent = cellContent.substring(0, cellContent.length() - 1);
209 					texts[r][c] = cellContent;
210 					cellContent = "";
211 					c = 0;
212 					r++;
213 				} else {
214 					cellContent += token;
215 				}
216 				break;
217 			case 1:
218 				if ("\"".equals(token)) {
219 					state = 0;
220 				} else {
221 					cellContent += token;
222 				}
223 				break;
224 			}
225 		}
226 
227 		LOG.info("Text of range [" + range + "]:\n" + arrayToString(texts));
228 		return texts;
229 	}
230 
arrayToString(Object array)231 	private static String arrayToString(Object array) {
232 		if (array == null)
233 			return "null";
234 		if (!array.getClass().isArray())
235 			return array.toString();
236 
237 		int len = Array.getLength(array);
238 		String ret = "{";
239 		for (int i = 0; i < len; i++) {
240 			Object el = Array.get(array, i);
241 			if (el == null) {
242 				ret += "null";
243 			} else if (el.getClass().isArray()) {
244 				ret += arrayToString(el);
245 			} else {
246 				ret += "\"" + el + "\"";
247 			}
248 
249 			if (i + 1 != len)
250 				ret += ",";
251 
252 		}
253 		ret += "}";
254 		return ret;
255 	}
256 }