1b164ae3eSLei De Bin /************************************************************** 2b164ae3eSLei De Bin * 3b164ae3eSLei De Bin * Licensed to the Apache Software Foundation (ASF) under one 4b164ae3eSLei De Bin * or more contributor license agreements. See the NOTICE file 5b164ae3eSLei De Bin * distributed with this work for additional information 6b164ae3eSLei De Bin * regarding copyright ownership. The ASF licenses this file 7b164ae3eSLei De Bin * to you under the Apache License, Version 2.0 (the 8b164ae3eSLei De Bin * "License"); you may not use this file except in compliance 9b164ae3eSLei De Bin * with the License. You may obtain a copy of the License at 10b164ae3eSLei De Bin * 11b164ae3eSLei De Bin * http://www.apache.org/licenses/LICENSE-2.0 12b164ae3eSLei De Bin * 13b164ae3eSLei De Bin * Unless required by applicable law or agreed to in writing, 14b164ae3eSLei De Bin * software distributed under the License is distributed on an 15b164ae3eSLei De Bin * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY 16b164ae3eSLei De Bin * KIND, either express or implied. See the License for the 17b164ae3eSLei De Bin * specific language governing permissions and limitations 18b164ae3eSLei De Bin * under the License. 19b164ae3eSLei De Bin * 20b164ae3eSLei De Bin *************************************************************/ 21b164ae3eSLei De Bin 2295269d92SLiu Zhe package testlib.gui; 23faa4b864SLei De Bin 24faa4b864SLei De Bin import static org.openoffice.test.vcl.Tester.*; 2595269d92SLiu Zhe import static testlib.gui.UIMap.*; 26faa4b864SLei De Bin 27140164b7SLiu Zhe import java.lang.reflect.Array; 28faa4b864SLei De Bin import java.util.StringTokenizer; 29140164b7SLiu Zhe import java.util.logging.Logger; 30faa4b864SLei De Bin 31*b4d2d410SLiu Zhe public class SCTool { 3222a14f28SLiu Zhe 33*b4d2d410SLiu Zhe private static Logger LOG = Logger.getLogger(SCTool.class.getName()); 3422a14f28SLiu Zhe 35faa4b864SLei De Bin /** 36faa4b864SLei De Bin * Select a range. 37faa4b864SLei De Bin * 38faa4b864SLei De Bin * @param range 39faa4b864SLei De Bin * e.g. Sheet1.A10, Sheet1.A11:B30, A11, A30:B45 40faa4b864SLei De Bin */ selectRange(String range)41faa4b864SLei De Bin public static void selectRange(String range) { 42*b4d2d410SLiu Zhe scInputBarPosition.click(0.5, 0.5); 43*b4d2d410SLiu Zhe scInputBarPosition.setText(range); 44faa4b864SLei De Bin typeKeys("<enter>"); 45faa4b864SLei De Bin sleep(1); 46faa4b864SLei De Bin } 47faa4b864SLei De Bin 48faa4b864SLei De Bin /** 49faa4b864SLei De Bin * Get the input at the given cell. If the cell is a formula, return the 50faa4b864SLei De Bin * formula rather than the result 51faa4b864SLei De Bin * 52faa4b864SLei De Bin * @param cell 53faa4b864SLei De Bin * @return 54faa4b864SLei De Bin */ getCellInput(String cell)55faa4b864SLei De Bin public static String getCellInput(String cell) { 56faa4b864SLei De Bin if (cell != null) 57faa4b864SLei De Bin selectRange(cell); 58*b4d2d410SLiu Zhe String caption = scInputBarInput.getCaption(); 5922a14f28SLiu Zhe // Fix: When formulaEdit's caption is blank, the hook will return the 6022a14f28SLiu Zhe // document window's caption 61faa4b864SLei De Bin // So if it's document window caption, return "" 6222a14f28SLiu Zhe return caption.contains("[12479]") ? "" : caption; 63faa4b864SLei De Bin } 64faa4b864SLei De Bin 65faa4b864SLei De Bin /** 66faa4b864SLei De Bin * Get the text at the given cell. If the cell is a formula, return the 67faa4b864SLei De Bin * result rather than the formula 68faa4b864SLei De Bin * 69faa4b864SLei De Bin * @param cell 70faa4b864SLei De Bin * @return 71faa4b864SLei De Bin */ getCellText(String cell)72faa4b864SLei De Bin public static String getCellText(String cell) { 73faa4b864SLei De Bin app.setClipboard("$$$$"); 74faa4b864SLei De Bin selectRange(cell); 75faa4b864SLei De Bin typeKeys("<$copy>"); 76faa4b864SLei De Bin String content = app.getClipboard(); 77faa4b864SLei De Bin if (content.endsWith("\r\n")) 78faa4b864SLei De Bin content = content.substring(0, content.length() - 2); 79faa4b864SLei De Bin else if (content.endsWith("\n")) 8022a14f28SLiu Zhe content = content.substring(0, content.length() - 1); 8122a14f28SLiu Zhe 82faa4b864SLei De Bin return content; 83faa4b864SLei De Bin } 84faa4b864SLei De Bin 85faa4b864SLei De Bin /** 8622a14f28SLiu Zhe * convert the format of column number to integer e.g. A -> 1 AA -> 27 AMJ 8722a14f28SLiu Zhe * -> 1024 8822a14f28SLiu Zhe * 89faa4b864SLei De Bin * @param no 90faa4b864SLei De Bin * @return 91faa4b864SLei De Bin */ toIntColumnNo(String no)92faa4b864SLei De Bin public static int toIntColumnNo(String no) { 93faa4b864SLei De Bin int len = no.length(); 94faa4b864SLei De Bin int ret = 0; 9522a14f28SLiu Zhe for (int i = 0; i < len; i++) { 96faa4b864SLei De Bin char c = no.charAt(len - i - 1); 97faa4b864SLei De Bin ret += Math.pow(26, i) * (c - 'A' + 1); 98faa4b864SLei De Bin } 9922a14f28SLiu Zhe 100faa4b864SLei De Bin return ret; 101faa4b864SLei De Bin } 10222a14f28SLiu Zhe 103faa4b864SLei De Bin /** 10422a14f28SLiu Zhe * Convert the format of column number to char 10522a14f28SLiu Zhe * 106faa4b864SLei De Bin * @param no 107faa4b864SLei De Bin * @return 108faa4b864SLei De Bin */ toCharColumnNo(int no)10922a14f28SLiu Zhe public static String toCharColumnNo(int no) { 110faa4b864SLei De Bin String ret = ""; 111faa4b864SLei De Bin int f = 0; 112faa4b864SLei De Bin do { 113faa4b864SLei De Bin f = (no - 1) / 26; 114faa4b864SLei De Bin int s = (no - 1) % 26; 11522a14f28SLiu Zhe ret = (char) ('A' + s) + ret; 116faa4b864SLei De Bin no = f; 117faa4b864SLei De Bin } while (f != 0); 118faa4b864SLei De Bin return ret; 119faa4b864SLei De Bin } 12022a14f28SLiu Zhe 121faa4b864SLei De Bin /** 122faa4b864SLei De Bin * Parse location string into integer values 12322a14f28SLiu Zhe * 12422a14f28SLiu Zhe * @param loc 12522a14f28SLiu Zhe * e.g. A1 126faa4b864SLei De Bin * @return 127faa4b864SLei De Bin */ parseLocation(String loc)128faa4b864SLei De Bin public static int[] parseLocation(String loc) { 129faa4b864SLei De Bin int i = 0; 130faa4b864SLei De Bin for (; i < loc.length(); i++) { 131faa4b864SLei De Bin char c = loc.charAt(i); 132faa4b864SLei De Bin if (c >= '0' && c <= '9') 133faa4b864SLei De Bin break; 134faa4b864SLei De Bin } 13522a14f28SLiu Zhe 136faa4b864SLei De Bin String col = loc.substring(0, i); 137faa4b864SLei De Bin String row = loc.substring(i); 138faa4b864SLei De Bin int sC = toIntColumnNo(col); 139faa4b864SLei De Bin int sR = Integer.parseInt(row); 14022a14f28SLiu Zhe return new int[] { sC, sR }; 141faa4b864SLei De Bin } 14222a14f28SLiu Zhe 143faa4b864SLei De Bin /** 144faa4b864SLei De Bin * Parse range string into integer values 14522a14f28SLiu Zhe * 14622a14f28SLiu Zhe * @param range 14722a14f28SLiu Zhe * e.g. A3:F9 148faa4b864SLei De Bin * @return 149faa4b864SLei De Bin */ parseRange(String range)150faa4b864SLei De Bin public static int[] parseRange(String range) { 151faa4b864SLei De Bin int dotIndex = range.indexOf("."); 152faa4b864SLei De Bin if (dotIndex != -1) { 153faa4b864SLei De Bin range = range.substring(dotIndex + 1); 154faa4b864SLei De Bin } 15522a14f28SLiu Zhe 156faa4b864SLei De Bin String[] locs = range.split(":"); 157faa4b864SLei De Bin int[] ret = new int[4]; 158faa4b864SLei De Bin int[] start = parseLocation(locs[0]); 159faa4b864SLei De Bin ret[0] = start[0]; 160faa4b864SLei De Bin ret[1] = start[1]; 161faa4b864SLei De Bin if (locs.length == 1) { 162faa4b864SLei De Bin ret[2] = start[0]; 163faa4b864SLei De Bin ret[3] = start[1]; 164faa4b864SLei De Bin } else { 165faa4b864SLei De Bin int[] end = parseLocation(locs[1]); 166faa4b864SLei De Bin ret[2] = end[0]; 167faa4b864SLei De Bin ret[3] = end[1]; 168faa4b864SLei De Bin } 16922a14f28SLiu Zhe 170faa4b864SLei De Bin return ret; 171faa4b864SLei De Bin } 172faa4b864SLei De Bin 173faa4b864SLei De Bin /** 174faa4b864SLei De Bin * Get the text at the given cells. If the cell is a formula, return the 17522a14f28SLiu Zhe * result rather than the formula. Note: 176faa4b864SLei De Bin * 17722a14f28SLiu Zhe * @param range 17822a14f28SLiu Zhe * e.g. A3:D9 179faa4b864SLei De Bin * @return 180faa4b864SLei De Bin */ getCellTexts(String range)181faa4b864SLei De Bin public static String[][] getCellTexts(String range) { 182faa4b864SLei De Bin selectRange(range); 183faa4b864SLei De Bin int[] intRange = parseRange(range); 18422a14f28SLiu Zhe int rowCount = intRange[3] - intRange[1] + 1; 18522a14f28SLiu Zhe int colCount = intRange[2] - intRange[0] + 1; 186faa4b864SLei De Bin String[][] texts = new String[rowCount][colCount]; 18722a14f28SLiu Zhe 188faa4b864SLei De Bin app.setClipboard("$$$$"); 189faa4b864SLei De Bin typeKeys("<$copy>"); 190faa4b864SLei De Bin sleep(1); 191faa4b864SLei De Bin String text = app.getClipboard(); 192faa4b864SLei De Bin StringTokenizer tokenizer = new StringTokenizer(text, "\"\t\n", true); 193faa4b864SLei De Bin int state = 0; 194faa4b864SLei De Bin String cellContent = ""; 19522a14f28SLiu Zhe int r = 0, c = 0; 196faa4b864SLei De Bin while (tokenizer.hasMoreTokens()) { 197faa4b864SLei De Bin String token = tokenizer.nextToken(); 198faa4b864SLei De Bin switch (state) { 199faa4b864SLei De Bin case 0: 200faa4b864SLei De Bin if ("\"".equals(token)) { 201faa4b864SLei De Bin state = 1; 202faa4b864SLei De Bin } else if ("\t".equals(token)) { 203faa4b864SLei De Bin texts[r][c] = cellContent; 204faa4b864SLei De Bin cellContent = ""; 205faa4b864SLei De Bin c++; 206faa4b864SLei De Bin } else if ("\n".equals(token)) { 207faa4b864SLei De Bin if (cellContent.endsWith("\r")) 208faa4b864SLei De Bin cellContent = cellContent.substring(0, cellContent.length() - 1); 209faa4b864SLei De Bin texts[r][c] = cellContent; 210faa4b864SLei De Bin cellContent = ""; 211faa4b864SLei De Bin c = 0; 212faa4b864SLei De Bin r++; 21322a14f28SLiu Zhe } else { 214faa4b864SLei De Bin cellContent += token; 215faa4b864SLei De Bin } 216faa4b864SLei De Bin break; 217faa4b864SLei De Bin case 1: 218faa4b864SLei De Bin if ("\"".equals(token)) { 219faa4b864SLei De Bin state = 0; 220faa4b864SLei De Bin } else { 221faa4b864SLei De Bin cellContent += token; 222faa4b864SLei De Bin } 223faa4b864SLei De Bin break; 224faa4b864SLei De Bin } 225faa4b864SLei De Bin } 22622a14f28SLiu Zhe 227140164b7SLiu Zhe LOG.info("Text of range [" + range + "]:\n" + arrayToString(texts)); 228faa4b864SLei De Bin return texts; 229faa4b864SLei De Bin } 230faa4b864SLei De Bin arrayToString(Object array)231140164b7SLiu Zhe private static String arrayToString(Object array) { 232140164b7SLiu Zhe if (array == null) 233140164b7SLiu Zhe return "null"; 23422a14f28SLiu Zhe if (!array.getClass().isArray()) 235140164b7SLiu Zhe return array.toString(); 23622a14f28SLiu Zhe 237140164b7SLiu Zhe int len = Array.getLength(array); 238140164b7SLiu Zhe String ret = "{"; 23922a14f28SLiu Zhe for (int i = 0; i < len; i++) { 240140164b7SLiu Zhe Object el = Array.get(array, i); 241140164b7SLiu Zhe if (el == null) { 242140164b7SLiu Zhe ret += "null"; 243140164b7SLiu Zhe } else if (el.getClass().isArray()) { 244140164b7SLiu Zhe ret += arrayToString(el); 245140164b7SLiu Zhe } else { 246140164b7SLiu Zhe ret += "\"" + el + "\""; 247140164b7SLiu Zhe } 24822a14f28SLiu Zhe 249140164b7SLiu Zhe if (i + 1 != len) 250140164b7SLiu Zhe ret += ","; 25122a14f28SLiu Zhe 252140164b7SLiu Zhe } 253140164b7SLiu Zhe ret += "}"; 254140164b7SLiu Zhe return ret; 255140164b7SLiu Zhe } 256faa4b864SLei De Bin }