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.FileUtil; 28 import org.openoffice.test.common.Testspace; 29 import org.openoffice.test.uno.UnoApp; 30 31 import com.sun.star.beans.PropertyValue; 32 import com.sun.star.beans.XPropertySet; 33 import com.sun.star.container.XIndexAccess; 34 import com.sun.star.container.XNamed; 35 import com.sun.star.frame.XController; 36 import com.sun.star.frame.XModel; 37 import com.sun.star.frame.XStorable; 38 import com.sun.star.lang.XComponent; 39 import com.sun.star.sheet.XSpreadsheet; 40 import com.sun.star.sheet.XSpreadsheetDocument; 41 import com.sun.star.sheet.XSpreadsheetView; 42 import com.sun.star.sheet.XSpreadsheets; 43 import com.sun.star.table.XCell; 44 import com.sun.star.table.XCellRange; 45 import com.sun.star.table.XColumnRowRange; 46 import com.sun.star.table.XTableColumns; 47 import com.sun.star.table.XTableRows; 48 import com.sun.star.text.XText; 49 import com.sun.star.uno.UnoRuntime; 50 import com.sun.star.util.XCloseable; 51 52 53 /** 54 * Utilities of Spreadsheet 55 * 56 */ 57 58 public class SCUtil { 59 60 private static final String scTempDir = "output/sc/"; //Spreadsheet temp file directory 61 private static HashMap filterName = new HashMap(); 62 63 private SCUtil() { 64 65 } 66 67 /** 68 * Get spreadsheet document object 69 * @param xSpreadsheetComponent 70 * @return 71 * @throws Exception 72 */ 73 public static XSpreadsheetDocument getSCDocument(XComponent xSpreadsheetComponent) throws Exception { 74 XSpreadsheetDocument xSpreadsheetDocument = 75 (XSpreadsheetDocument) UnoRuntime.queryInterface(XSpreadsheetDocument.class, xSpreadsheetComponent); 76 77 return xSpreadsheetDocument; 78 } 79 80 /** 81 * Get sheet object by sheet name 82 * @param xSpreadsheetDocument 83 * @param sheetName 84 * @return 85 * @throws Exception 86 */ 87 public static XSpreadsheet getSCSheetByName(XSpreadsheetDocument xSpreadsheetDocument, String sheetName) throws Exception { 88 XSpreadsheets xSpreadsheets = xSpreadsheetDocument.getSheets(); 89 XSpreadsheet xSpreadsheet = 90 (XSpreadsheet) UnoRuntime.queryInterface(XSpreadsheet.class, xSpreadsheets.getByName(sheetName)); 91 92 return xSpreadsheet; 93 } 94 95 /** 96 * Get sheet object by sheet index 97 * @param xSpreadsheetDocument 98 * @param index (Short) 0,1,2,... 99 * @return 100 * @throws Exception 101 */ 102 public static XSpreadsheet getSCSheetByIndex(XSpreadsheetDocument xSpreadsheetDocument, short index) throws Exception { 103 XSpreadsheets xSpreadsheets = xSpreadsheetDocument.getSheets(); 104 XIndexAccess xIndexAccess = 105 (XIndexAccess) UnoRuntime.queryInterface(XIndexAccess.class, xSpreadsheets); 106 XSpreadsheet xSpreadsheet = 107 (XSpreadsheet) UnoRuntime.queryInterface(XSpreadsheet.class, xIndexAccess.getByIndex(index)); 108 109 return xSpreadsheet; 110 } 111 112 /** 113 * Get sheet name by sheet index 114 * 115 * @param xSpreadsheetDocument 116 * @param index 117 * (Short) 0,1,2,... 118 * @return 119 * @throws Exception 120 */ 121 public static String getSCSheetNameByIndex( 122 XSpreadsheetDocument xSpreadsheetDocument, short index) 123 throws Exception { 124 XSpreadsheets xSpreadsheets = xSpreadsheetDocument.getSheets(); 125 XIndexAccess xIndexAccess = (XIndexAccess) UnoRuntime.queryInterface( 126 XIndexAccess.class, xSpreadsheets); 127 XSpreadsheet xSpreadsheet = (XSpreadsheet) UnoRuntime.queryInterface( 128 XSpreadsheet.class, xIndexAccess.getByIndex(index)); 129 XNamed xsheetname = (XNamed) UnoRuntime.queryInterface(XNamed.class, 130 xSpreadsheet); 131 return xsheetname.getName(); 132 } 133 134 /** 135 * Set sheet name by sheet index 136 * 137 * @param xSpreadsheetDocument 138 * @param index 139 * (Short) 0,1,2,... 140 * @return 141 * @throws Exception 142 */ 143 public static void setSCSheetNameByIndex( 144 XSpreadsheetDocument xSpreadsheetDocument, short index, 145 String sheetname) throws Exception { 146 XSpreadsheets xSpreadsheets = xSpreadsheetDocument.getSheets(); 147 XIndexAccess xIndexAccess = (XIndexAccess) UnoRuntime.queryInterface( 148 XIndexAccess.class, xSpreadsheets); 149 XSpreadsheet xSpreadsheet = (XSpreadsheet) UnoRuntime.queryInterface( 150 XSpreadsheet.class, xIndexAccess.getByIndex(index)); 151 XNamed xsheetname = (XNamed) UnoRuntime.queryInterface(XNamed.class, 152 xSpreadsheet); 153 xsheetname.setName(sheetname); 154 } 155 156 /** 157 * Get rows object 158 * @param xSpreadsheet 159 * @return 160 * @throws Exception 161 */ 162 public static XTableRows getSCRows(XSpreadsheet xSpreadsheet) throws Exception { 163 XColumnRowRange xColumnRowRange = 164 (XColumnRowRange) UnoRuntime.queryInterface(XColumnRowRange.class, xSpreadsheet); 165 XTableRows xTableRows = xColumnRowRange.getRows(); 166 167 return xTableRows; 168 } 169 170 /** 171 * Get columns object 172 * @param xSpreadsheet 173 * @return 174 * @throws Exception 175 */ 176 public static XTableColumns getSCColumns(XSpreadsheet xSpreadsheet) throws Exception { 177 XColumnRowRange xColumnRowRange = 178 (XColumnRowRange) UnoRuntime.queryInterface(XColumnRowRange.class, xSpreadsheet); 179 XTableColumns xTableColumns = xColumnRowRange.getColumns(); 180 181 return xTableColumns; 182 } 183 184 /** 185 * Set floating number into specific cell 186 * @param xSpreadsheet 187 * @param column 188 * @param row 189 * @param value 190 * @throws Exception 191 */ 192 public static void setValueToCell(XSpreadsheet xSpreadsheet, int column, int row, double value) throws Exception { 193 XCell xCell = xSpreadsheet.getCellByPosition(column, row); 194 xCell.setValue(value); 195 } 196 197 /** 198 * Set text into specific cell 199 * @param xSpreadsheet 200 * @param column 201 * @param row 202 * @param text 203 * @throws Exception 204 */ 205 public static void setTextToCell(XSpreadsheet xSpreadsheet, int column, int row, String text) throws Exception { 206 XCell xCell = xSpreadsheet.getCellByPosition(column, row); 207 XText xText = (XText) UnoRuntime.queryInterface(XText.class, xCell); 208 xText.setString(text); 209 } 210 211 /** 212 * Set text into specific cell 213 * @param xCell 214 * @param text 215 * @throws Exception 216 */ 217 public static void setTextToCell(XCell xCell, String text) throws Exception { 218 XText xText = (XText) UnoRuntime.queryInterface(XText.class, xCell); 219 xText.setString(text); 220 } 221 222 /** 223 * Set formula into specific cell 224 * @param xSpreadsheet 225 * @param column 226 * @param row 227 * @param formula 228 * @throws Exception 229 */ 230 public static void setFormulaToCell(XSpreadsheet xSpreadsheet, int column, int row, String formula) throws Exception { 231 XCell xCell = xSpreadsheet.getCellByPosition(column, row); 232 xCell.setFormula(formula); 233 } 234 235 /** 236 * Get value from specific cell 237 * @param xSpreadsheet 238 * @param column 239 * @param row 240 * @return 241 * @throws Exception 242 */ 243 public static double getValueFromCell(XSpreadsheet xSpreadsheet, int column, int row) throws Exception { 244 XCell xCell = xSpreadsheet.getCellByPosition(column, row); 245 double cellValue = xCell.getValue(); 246 247 return cellValue; 248 } 249 250 /** 251 * Get text from specific cell 252 * @param xSpreadsheet 253 * @param column 254 * @param row 255 * 256 * @return 257 * @throws Exception 258 */ 259 public static String getTextFromCell(XSpreadsheet xSpreadsheet, int column, int row) throws Exception { 260 XCell xCell = xSpreadsheet.getCellByPosition(column, row); 261 XText xText = (XText) UnoRuntime.queryInterface(XText.class, xCell); 262 263 return xText.getString(); 264 } 265 266 /** 267 * Get formula string from specific cell 268 * @param xSpreadsheet 269 * @param column 270 * @param row 271 * @return 272 * @throws Exception 273 */ 274 public static String getFormulaFromCell(XSpreadsheet xSpreadsheet, int column, int row) throws Exception { 275 XCell xCell = xSpreadsheet.getCellByPosition(column, row); 276 String cellFormula = xCell.getFormula(); 277 278 return cellFormula; 279 } 280 281 /** 282 * Set numbers into a cell range 283 * @param xSpreadsheet 284 * @param start_col 285 * @param start_row 286 * @param end_col 287 * @param end_row 288 * @param values 289 * @throws Exception 290 */ 291 public static void setValueToCellRange(XSpreadsheet xSpreadsheet, int start_col, int start_row, int end_col, int end_row, double[][] values) throws Exception { 292 XCellRange xCellRange = xSpreadsheet.getCellRangeByPosition(start_col, start_row, end_col, end_row); 293 XCell xCell = null; 294 for (int i = 0; i <= (end_row - start_row); i++ ) { 295 for(int j = 0; j <= (end_col - start_col); j++) { 296 xCell = xCellRange.getCellByPosition(j, i); 297 xCell.setValue(values[i][j]); 298 } 299 } 300 } 301 302 /** 303 * Set text into a cell range 304 * @param xSpreadsheet 305 * @param start_col 306 * @param start_row 307 * @param end_col 308 * @param end_row 309 * @param texts 310 * @throws Exception 311 */ 312 public static void setTextToCellRange(XSpreadsheet xSpreadsheet, int start_col, int start_row, int end_col, int end_row, String[][] texts) throws Exception { 313 XCellRange xCellRange = xSpreadsheet.getCellRangeByPosition(start_col, start_row, end_col, end_row); 314 XCell xCell = null; 315 XText xText = null; 316 for (int i = 0; i <= (end_row - start_row); i++ ) { 317 for(int j = 0; j <= (end_col - start_col); j++) { 318 xCell = xCellRange.getCellByPosition(j, i); 319 xText = (XText) UnoRuntime.queryInterface(XText.class, xCell); 320 xText.setString(texts[i][j]); 321 } 322 } 323 } 324 325 /** 326 * Get number content from a cell range 327 * @param xSpreadsheet 328 * @param start_col 329 * @param start_row 330 * @param end_col 331 * @param end_row 332 * @return 333 * @throws Exception 334 */ 335 public static double[][] getValueFromCellRange(XSpreadsheet xSpreadsheet, int start_col, int start_row, int end_col, int end_row) throws Exception { 336 XCellRange xCellRange = xSpreadsheet.getCellRangeByPosition(start_col, start_row, end_col, end_row); 337 XCell xCell = null; 338 double[][] cellValues = new double[end_row - start_row+1][end_col - start_col +1]; 339 340 for (int i = 0; i <= (end_row - start_row); i++ ) { 341 for(int j = 0; j <= (end_col - start_col); j++) { 342 xCell = xCellRange.getCellByPosition(j, i); 343 cellValues[i][j] = xCell.getValue(); 344 } 345 } 346 347 return cellValues; 348 } 349 350 /** 351 * Get text content from a cell range 352 * @param xSpreadsheet 353 * @param start_col 354 * @param start_row 355 * @param end_col 356 * @param end_row 357 * @return 358 * @throws Exception 359 */ 360 public static String[][] getTextFromCellRange(XSpreadsheet xSpreadsheet, int start_col, int start_row, int end_col, int end_row) throws Exception { 361 XCellRange xCellRange = xSpreadsheet.getCellRangeByPosition(start_col, start_row, end_col, end_row); 362 XCell xCell = null; 363 XText xText = null; 364 String[][] cellTexts = new String[end_row - start_row+1][end_col - start_col +1]; 365 366 for (int i = 0; i <= (end_row - start_row); i++ ) { 367 for (int j = 0; j <= (end_col - start_col); j++) { 368 xCell = xCellRange.getCellByPosition(j, i); 369 xText = (XText) UnoRuntime.queryInterface(XText.class, xCell); 370 cellTexts[i][j] = xText.getString(); 371 } 372 } 373 374 return cellTexts; 375 } 376 377 //TODO ZS - public static String[][] getAllFromCellRange 378 379 /** 380 * Switch to specific sheet 381 * @param xSpreadsheetDocument 382 * @param xSpreadsheet 383 */ 384 public static void setCurrentSheet(XSpreadsheetDocument xSpreadsheetDocument, XSpreadsheet xSpreadsheet) throws Exception { 385 XModel xModel = (XModel) UnoRuntime.queryInterface(XModel.class, xSpreadsheetDocument); 386 XController xController = xModel.getCurrentController(); 387 XSpreadsheetView xSpreadsheetView = (XSpreadsheetView) UnoRuntime.queryInterface(XSpreadsheetView.class, xController); 388 xSpreadsheetView.setActiveSheet(xSpreadsheet); 389 } 390 391 /** 392 * Get sheet object of current active sheet 393 * @param xSpreadsheetDocument 394 * @return 395 */ 396 public static XSpreadsheet getCurrentSheet(XSpreadsheetDocument xSpreadsheetDocument) throws Exception { 397 XModel xModel = (XModel) UnoRuntime.queryInterface(XModel.class, xSpreadsheetDocument); 398 XController xController = xModel.getCurrentController(); 399 XSpreadsheetView xSpreadsheetView = (XSpreadsheetView) UnoRuntime.queryInterface(XSpreadsheetView.class, xController); 400 XSpreadsheet xSpreadsheet = xSpreadsheetView.getActiveSheet(); 401 402 return xSpreadsheet; 403 } 404 405 /** 406 * Get sheet object by sheet index 407 * 408 * @param xSpreadsheetDocument 409 * @return 410 * @throws Exception 411 */ 412 public static String getSCActiveSheetName( 413 XSpreadsheetDocument xSpreadsheetDocument) throws Exception { 414 XModel xSpreadsheetModel = (XModel) UnoRuntime.queryInterface( 415 XModel.class, xSpreadsheetDocument); 416 XSpreadsheetView xSpeadsheetView = (XSpreadsheetView) UnoRuntime 417 .queryInterface(XSpreadsheetView.class, 418 xSpreadsheetModel.getCurrentController()); 419 XSpreadsheet activesheet = xSpeadsheetView.getActiveSheet(); 420 XNamed activesheetName = (XNamed) UnoRuntime.queryInterface( 421 XNamed.class, activesheet); 422 return activesheetName.getName(); 423 } 424 425 /** 426 * Set value of specific property from a cell 427 * @param xCell 428 * @param propName 429 * @param value 430 * @throws Exception 431 */ 432 public static void setCellProperties(XCell xCell, String propName, Object value) throws Exception { 433 434 XPropertySet xPropertySet = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, xCell); 435 xPropertySet.setPropertyValue(propName, value); 436 } 437 438 /** 439 * Get value of specific property from a cell 440 * @param xCell 441 * @param propName 442 * @return 443 * @throws Exception 444 */ 445 public static Object getCellProperties(XCell xCell, String propName) throws Exception { 446 XPropertySet xPropertySet = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, xCell); 447 Object value = xPropertySet.getPropertyValue(propName); 448 449 return value; 450 } 451 452 /** 453 * Clear temp file directory 454 */ 455 public static void clearTempDir() { 456 FileUtil.deleteFile(Testspace.getFile(Testspace.getPath(scTempDir))); 457 } 458 459 /** 460 * Save file as specific file format into spreadsheet temp file folder. 461 * @param scComponent 462 * @param fileName File name string without extension name (e.g. "sampleFile") 463 * @param extName ("ods", "ots", "xls", "xlt", "csv") 464 * @throws Exception 465 */ 466 public static void saveFileAs(XComponent scComponent, String fileName, String extName) throws Exception { 467 468 initFilterName(); 469 470 String storeUrl = Testspace.getUrl(scTempDir + fileName + "." + extName); 471 472 PropertyValue[] storeProps = new PropertyValue[2]; 473 storeProps[0] = new PropertyValue(); 474 storeProps[0].Name = "FilterName"; 475 storeProps[0].Value = filterName.get(extName); 476 storeProps[1] = new PropertyValue(); 477 storeProps[1].Name = "Overwrite"; 478 storeProps[1].Value = new Boolean(true); 479 480 XStorable scStorable = 481 (XStorable) UnoRuntime.queryInterface(XStorable.class, scComponent); 482 scStorable.storeAsURL(storeUrl, storeProps); 483 } 484 485 /** 486 * Save file after open file. 487 * 488 * @param xSpreadsheetDocument 489 * @throws Exception 490 */ 491 public static void save(XSpreadsheetDocument xSpreadsheetDocument) 492 throws Exception { 493 494 XStorable scStorable = (XStorable) UnoRuntime.queryInterface( 495 XStorable.class, xSpreadsheetDocument); 496 scStorable.store(); 497 498 } 499 500 501 /** 502 * Close specific opening spreadsheet file which has been saved 503 * @param xSpreadsheetDocument 504 * @throws Exception 505 */ 506 public static void closeFile(XSpreadsheetDocument xSpreadsheetDocument) throws Exception { 507 XCloseable xCloseable = (XCloseable) UnoRuntime.queryInterface(XCloseable.class, xSpreadsheetDocument); 508 xCloseable.close(false); 509 } 510 511 /** 512 * Close a opening file saved in spreadsheet temp file direction and reopen it in Spreadsheet. For save&reload test scenario only. 513 * @param unoApp 514 * @param xSpreadsheetDocument 515 * @param fullFileName File name with the extension name. (e.g. "sc.ods") 516 * @return 517 * @throws Exception 518 */ 519 public static XSpreadsheetDocument reloadFile(UnoApp unoApp, XSpreadsheetDocument xSpreadsheetDocument, String fullFileName) throws Exception { 520 closeFile(xSpreadsheetDocument); 521 522 String filePath = Testspace.getPath(scTempDir + fullFileName); 523 XSpreadsheetDocument xScDocument = UnoRuntime.queryInterface(XSpreadsheetDocument.class, unoApp.loadDocument(filePath)); 524 525 return xScDocument; 526 } 527 528 /** 529 * open file in Spreadsheet. 530 * @param unoApp 531 * @param filtpath File path with the extension name. (e.g. "testcase/uno/sc/data/sample.xls") 532 * @return 533 * @throws Exception 534 */ 535 public static XSpreadsheetDocument openFile(String filePath, UnoApp app) throws Exception { 536 return (XSpreadsheetDocument) UnoRuntime.queryInterface(XSpreadsheetDocument.class, app.loadDocument(filePath)); 537 } 538 539 /** 540 * Initial the filter name list 541 * @throws Exception 542 */ 543 private static void initFilterName() throws Exception { 544 if (filterName.size() > 0) { 545 return; 546 } 547 548 filterName.put("ods", "calc8"); 549 filterName.put("ots", "calc8_template"); 550 filterName.put("xls", "MS Excel 97"); 551 filterName.put("xlt", "MS Excel 97 Vorlage/Template"); 552 filterName.put("csv", "Text - txt - csv (StarCalc)"); 553 } 554 555 } 556