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 23 package testlib.uno; 24 25 import java.util.HashMap; 26 27 import org.openoffice.test.common.Testspace; 28 import org.openoffice.test.uno.UnoApp; 29 30 import com.sun.star.beans.PropertyValue; 31 import com.sun.star.container.XIndexAccess; 32 import com.sun.star.frame.XController; 33 import com.sun.star.frame.XModel; 34 import com.sun.star.frame.XStorable; 35 import com.sun.star.lang.XComponent; 36 import com.sun.star.sheet.XSpreadsheet; 37 import com.sun.star.sheet.XSpreadsheetDocument; 38 import com.sun.star.sheet.XSpreadsheetView; 39 import com.sun.star.sheet.XSpreadsheets; 40 import com.sun.star.table.XCell; 41 import com.sun.star.table.XCellRange; 42 import com.sun.star.table.XColumnRowRange; 43 import com.sun.star.table.XTableColumns; 44 import com.sun.star.table.XTableRows; 45 import com.sun.star.text.XText; 46 import com.sun.star.uno.UnoRuntime; 47 import com.sun.star.util.XCloseable; 48 49 50 /** 51 * Utilities of Spreadsheet 52 * @author test 53 * 54 */ 55 56 public class SCUtil { 57 58 private static HashMap filterName = new HashMap(); 59 60 public SCUtil() { 61 62 } 63 64 /** 65 * Get spreadsheet document object 66 * @param xSpreadsheetComponent 67 * @return 68 * @throws Exception 69 */ 70 public static XSpreadsheetDocument getSCDocument(XComponent xSpreadsheetComponent) throws Exception { 71 XSpreadsheetDocument xSpreadsheetDocument = 72 (XSpreadsheetDocument) UnoRuntime.queryInterface(XSpreadsheetDocument.class, xSpreadsheetComponent); 73 74 return xSpreadsheetDocument; 75 } 76 77 /** 78 * Get sheet object by sheet name 79 * @param xSpreadsheetDocument 80 * @param sheetName 81 * @return 82 * @throws Exception 83 */ 84 public static XSpreadsheet getSCSheetByName(XSpreadsheetDocument xSpreadsheetDocument, String sheetName) throws Exception { 85 XSpreadsheets xSpreadsheets = xSpreadsheetDocument.getSheets(); 86 XSpreadsheet xSpreadsheet = 87 (XSpreadsheet) UnoRuntime.queryInterface(XSpreadsheet.class, xSpreadsheets.getByName(sheetName)); 88 89 return xSpreadsheet; 90 } 91 92 /** 93 * Get sheet object by sheet index 94 * @param xSpreadsheetDocument 95 * @param index (Short) 0,1,2,... 96 * @return 97 * @throws Exception 98 */ 99 public static XSpreadsheet getSCSheetByIndex(XSpreadsheetDocument xSpreadsheetDocument, short index) throws Exception { 100 XSpreadsheets xSpreadsheets = xSpreadsheetDocument.getSheets(); 101 XIndexAccess xIndexAccess = 102 (XIndexAccess) UnoRuntime.queryInterface(XIndexAccess.class, xSpreadsheets); 103 XSpreadsheet xSpreadsheet = 104 (XSpreadsheet) UnoRuntime.queryInterface(XSpreadsheet.class, xIndexAccess.getByIndex(index)); 105 106 return xSpreadsheet; 107 } 108 109 /** 110 * Get rows object 111 * @param xSpreadsheet 112 * @return 113 * @throws Exception 114 */ 115 public static XTableRows getSCRows(XSpreadsheet xSpreadsheet) throws Exception { 116 XColumnRowRange xColumnRowRange = 117 (XColumnRowRange) UnoRuntime.queryInterface(XColumnRowRange.class, xSpreadsheet); 118 XTableRows xTableRows = xColumnRowRange.getRows(); 119 120 return xTableRows; 121 } 122 123 /** 124 * Get columns object 125 * @param xSpreadsheet 126 * @return 127 * @throws Exception 128 */ 129 public static XTableColumns getSCColumns(XSpreadsheet xSpreadsheet) throws Exception { 130 XColumnRowRange xColumnRowRange = 131 (XColumnRowRange) UnoRuntime.queryInterface(XColumnRowRange.class, xSpreadsheet); 132 XTableColumns xTableColumns = xColumnRowRange.getColumns(); 133 134 return xTableColumns; 135 } 136 137 /** 138 * Set floating number into specific cell 139 * @param xSpreadsheet 140 * @param column 141 * @param row 142 * @param value 143 * @throws Exception 144 */ 145 public static void setValueToCell(XSpreadsheet xSpreadsheet, int column, int row, double value) throws Exception { 146 XCell xCell = xSpreadsheet.getCellByPosition(column, row); 147 xCell.setValue(value); 148 } 149 150 /** 151 * Set text into specific cell 152 * @param xSpreadsheet 153 * @param column 154 * @param row 155 * @param text 156 * @throws Exception 157 */ 158 public static void setTextToCell(XSpreadsheet xSpreadsheet, int column, int row, String text) throws Exception { 159 XCell xCell = xSpreadsheet.getCellByPosition(column, row); 160 XText xText = (XText) UnoRuntime.queryInterface(XText.class, xCell); 161 xText.setString(text); 162 } 163 164 /** 165 * Set formula into specific cell 166 * @param xSpreadsheet 167 * @param column 168 * @param row 169 * @param formula 170 * @throws Exception 171 */ 172 public static void setFormulaToCell(XSpreadsheet xSpreadsheet, int column, int row, String formula) throws Exception { 173 XCell xCell = xSpreadsheet.getCellByPosition(column, row); 174 xCell.setFormula(formula); 175 } 176 177 /** 178 * Get value from specific cell 179 * @param xSpreadsheet 180 * @param column 181 * @param row 182 * @return 183 * @throws Exception 184 */ 185 public static double getValueFromCell(XSpreadsheet xSpreadsheet, int column, int row) throws Exception { 186 XCell xCell = xSpreadsheet.getCellByPosition(column, row); 187 double cellValue = xCell.getValue(); 188 189 return cellValue; 190 } 191 192 /** 193 * Get text from specific cell 194 * @param xSpreadsheet 195 * @param column 196 * @param row 197 * 198 * @return 199 * @throws Exception 200 */ 201 public static String getTextFromCell(XSpreadsheet xSpreadsheet, int column, int row) throws Exception { 202 XCell xCell = xSpreadsheet.getCellByPosition(column, row); 203 XText xText = (XText) UnoRuntime.queryInterface(XText.class, xCell); 204 205 return xText.getString(); 206 } 207 208 /** 209 * Get formula string from specific cell 210 * @param xSpreadsheet 211 * @param column 212 * @param row 213 * @return 214 * @throws Exception 215 */ 216 public static String getFormulaFromCell(XSpreadsheet xSpreadsheet, int column, int row) throws Exception { 217 XCell xCell = xSpreadsheet.getCellByPosition(column, row); 218 String cellFormula = xCell.getFormula(); 219 220 return cellFormula; 221 } 222 223 /** 224 * Set numbers into a cell range 225 * @param xSpreadsheet 226 * @param start_col 227 * @param start_row 228 * @param end_col 229 * @param end_row 230 * @param values 231 * @throws Exception 232 */ 233 public static void setValueToCellRange(XSpreadsheet xSpreadsheet, int start_col, int start_row, int end_col, int end_row, double[][] values) throws Exception { 234 XCellRange xCellRange = xSpreadsheet.getCellRangeByPosition(start_col, start_row, end_col, end_row); 235 XCell xCell = null; 236 for (int i = 0; i <= (end_row - start_row); i++ ) { 237 for(int j = 0; j <= (end_col - start_col); j++) { 238 xCell = xCellRange.getCellByPosition(j, i); 239 xCell.setValue(values[i][j]); 240 } 241 } 242 } 243 244 /** 245 * Set text into a cell range 246 * @param xSpreadsheet 247 * @param start_col 248 * @param start_row 249 * @param end_col 250 * @param end_row 251 * @param texts 252 * @throws Exception 253 */ 254 public static void setTextToCellRange(XSpreadsheet xSpreadsheet, int start_col, int start_row, int end_col, int end_row, String[][] texts) throws Exception { 255 XCellRange xCellRange = xSpreadsheet.getCellRangeByPosition(start_col, start_row, end_col, end_row); 256 XCell xCell = null; 257 XText xText = null; 258 for (int i = 0; i <= (end_row - start_row); i++ ) { 259 for(int j = 0; j <= (end_col - start_col); j++) { 260 xCell = xCellRange.getCellByPosition(j, i); 261 xText = (XText) UnoRuntime.queryInterface(XText.class, xCell); 262 xText.setString(texts[i][j]); 263 } 264 } 265 } 266 267 /** 268 * Get number content from a cell range 269 * @param xSpreadsheet 270 * @param start_col 271 * @param start_row 272 * @param end_col 273 * @param end_row 274 * @return 275 * @throws Exception 276 */ 277 public static double[][] getValueFromCellRange(XSpreadsheet xSpreadsheet, int start_col, int start_row, int end_col, int end_row) throws Exception { 278 XCellRange xCellRange = xSpreadsheet.getCellRangeByPosition(start_col, start_row, end_col, end_row); 279 XCell xCell = null; 280 double[][] cellValues = new double[end_row - start_row+1][end_col - start_col +1]; 281 282 for (int i = 0; i <= (end_row - start_row); i++ ) { 283 for(int j = 0; j <= (end_col - start_col); j++) { 284 xCell = xCellRange.getCellByPosition(j, i); 285 cellValues[i][j] = xCell.getValue(); 286 } 287 } 288 289 return cellValues; 290 } 291 292 /** 293 * Get text content from a cell range 294 * @param xSpreadsheet 295 * @param start_col 296 * @param start_row 297 * @param end_col 298 * @param end_row 299 * @return 300 * @throws Exception 301 */ 302 public static String[][] getTextFromCellRange(XSpreadsheet xSpreadsheet, int start_col, int start_row, int end_col, int end_row) throws Exception { 303 XCellRange xCellRange = xSpreadsheet.getCellRangeByPosition(start_col, start_row, end_col, end_row); 304 XCell xCell = null; 305 XText xText = null; 306 String[][] cellTexts = new String[end_row - start_row+1][end_col - start_col +1]; 307 308 for (int i = 0; i <= (end_row - start_row); i++ ) { 309 for(int j = 0; j <= (end_col - start_col); j++) { 310 xCell = xCellRange.getCellByPosition(j, i); 311 xText = (XText) UnoRuntime.queryInterface(XText.class, xCell); 312 cellTexts[i][j] = xText.getString(); 313 } 314 } 315 316 return cellTexts; 317 } 318 319 //TODO ZS - public static String[][] getAllFromCellRange 320 321 /** 322 * Switch to specific sheet 323 * @param xSpreadsheetDocument 324 * @param xSpreadsheet 325 */ 326 public static void setCurrentSheet(XSpreadsheetDocument xSpreadsheetDocument, XSpreadsheet xSpreadsheet) throws Exception { 327 XModel xModel = (XModel) UnoRuntime.queryInterface(XModel.class, xSpreadsheetDocument); 328 XController xController = xModel.getCurrentController(); 329 XSpreadsheetView xSpreadsheetView = (XSpreadsheetView) UnoRuntime.queryInterface(XSpreadsheetView.class, xController); 330 xSpreadsheetView.setActiveSheet(xSpreadsheet); 331 } 332 333 /** 334 * Get sheet object of current active sheet 335 * @param xSpreadsheetDocument 336 * @return 337 */ 338 public static XSpreadsheet getCurrentSheet(XSpreadsheetDocument xSpreadsheetDocument) throws Exception { 339 XModel xModel = (XModel) UnoRuntime.queryInterface(XModel.class, xSpreadsheetDocument); 340 XController xController = xModel.getCurrentController(); 341 XSpreadsheetView xSpreadsheetView = (XSpreadsheetView) UnoRuntime.queryInterface(XSpreadsheetView.class, xController); 342 XSpreadsheet xSpreadsheet = xSpreadsheetView.getActiveSheet(); 343 344 return xSpreadsheet; 345 } 346 347 /** 348 * Save file as specific file format into testspace/output folder. 349 * @param scComponent 350 * @param fileName File name string without extension name (e.g. "sampleFile") 351 * @param extName ("ods", "ots", "xls", "xlt", "csv") 352 * @throws Exception 353 */ 354 public static void saveFileAs(XComponent scComponent, String fileName, String extName) throws Exception { 355 356 initFilterName(); 357 358 String storeUrl = Testspace.getUrl("output/" + fileName + "." + extName); 359 360 PropertyValue[] storeProps = new PropertyValue[2]; 361 storeProps[0] = new PropertyValue(); 362 storeProps[0].Name = "FilterName"; 363 storeProps[0].Value = filterName.get(extName); 364 storeProps[1] = new PropertyValue(); 365 storeProps[1].Name = "Overwrite"; 366 storeProps[1].Value = new Boolean(true); 367 368 XStorable scStorable = 369 (XStorable) UnoRuntime.queryInterface(XStorable.class, scComponent); 370 scStorable.storeAsURL(storeUrl, storeProps); 371 } 372 373 /** 374 * Close specific opening spreadsheet file which has been saved 375 * @param xSpreadsheetDocument 376 * @throws Exception 377 */ 378 public static void closeFile(XSpreadsheetDocument xSpreadsheetDocument) throws Exception { 379 XCloseable xCloseable = (XCloseable) UnoRuntime.queryInterface(XCloseable.class, xSpreadsheetDocument); 380 xCloseable.close(false); 381 } 382 383 /** 384 * Close a opening file saved in testspace/output direction and reopen it in Spreadsheet. For save&reload test scenario only. 385 * @param unoApp 386 * @param xSpreadsheetDocument 387 * @param fullFileName File name with the extension name. (e.g. "sc.ods") 388 * @return 389 * @throws Exception 390 */ 391 public static XSpreadsheetDocument reloadFile(UnoApp unoApp, XSpreadsheetDocument xSpreadsheetDocument, String fullFileName) throws Exception { 392 closeFile(xSpreadsheetDocument); 393 394 String filePath = Testspace.getPath("output/" + fullFileName); 395 XSpreadsheetDocument xScDocument = UnoRuntime.queryInterface(XSpreadsheetDocument.class, unoApp.loadDocument(filePath)); 396 397 return xScDocument; 398 } 399 400 /** 401 * Initial the filter name list 402 * @throws Exception 403 */ 404 private static void initFilterName() throws Exception { 405 if (filterName.size() > 0) { 406 return; 407 } 408 409 filterName.put("ods", "calc8"); 410 filterName.put("ots", "calc8_template"); 411 filterName.put("xls", "MS Excel 97"); 412 filterName.put("xlt", "MS Excel 97 Vorlage/Template"); 413 filterName.put("csv", "Text - txt - csv (StarCalc)"); 414 } 415 416 } 417