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.awt.Rectangle; 32 import com.sun.star.beans.PropertyValue; 33 import com.sun.star.beans.XPropertySet; 34 import com.sun.star.chart.XChartDocument; 35 import com.sun.star.chart.XDiagram; 36 import com.sun.star.container.XIndexAccess; 37 import com.sun.star.container.XNameAccess; 38 import com.sun.star.container.XNamed; 39 import com.sun.star.document.XEmbeddedObjectSupplier; 40 import com.sun.star.frame.XController; 41 import com.sun.star.frame.XModel; 42 import com.sun.star.frame.XStorable; 43 import com.sun.star.lang.XComponent; 44 import com.sun.star.lang.XMultiServiceFactory; 45 import com.sun.star.sheet.XCellRangeAddressable; 46 import com.sun.star.sheet.XSpreadsheet; 47 import com.sun.star.sheet.XSpreadsheetDocument; 48 import com.sun.star.sheet.XSpreadsheetView; 49 import com.sun.star.sheet.XSpreadsheets; 50 import com.sun.star.table.CellRangeAddress; 51 import com.sun.star.table.XCell; 52 import com.sun.star.table.XCellRange; 53 import com.sun.star.table.XColumnRowRange; 54 import com.sun.star.table.XTableChart; 55 import com.sun.star.table.XTableCharts; 56 import com.sun.star.table.XTableChartsSupplier; 57 import com.sun.star.table.XTableColumns; 58 import com.sun.star.table.XTableRows; 59 import com.sun.star.text.XText; 60 import com.sun.star.uno.UnoRuntime; 61 import com.sun.star.util.XCloseable; 62 63 64 /** 65 * Utilities of Spreadsheet 66 * 67 */ 68 69 public class SCUtil { 70 71 private static final String scTempDir = "output/sc/"; //Spreadsheet temp file directory 72 private static HashMap filterName = new HashMap(); 73 SCUtil()74 private SCUtil() { 75 76 } 77 78 /** 79 * Get spreadsheet document object 80 * @param xSpreadsheetComponent 81 * @return 82 * @throws Exception 83 */ getSCDocument(XComponent xSpreadsheetComponent)84 public static XSpreadsheetDocument getSCDocument(XComponent xSpreadsheetComponent) throws Exception { 85 XSpreadsheetDocument xSpreadsheetDocument = 86 (XSpreadsheetDocument) UnoRuntime.queryInterface(XSpreadsheetDocument.class, xSpreadsheetComponent); 87 88 return xSpreadsheetDocument; 89 } 90 91 /** 92 * Get sheet object by sheet name 93 * @param xSpreadsheetDocument 94 * @param sheetName 95 * @return 96 * @throws Exception 97 */ getSCSheetByName(XSpreadsheetDocument xSpreadsheetDocument, String sheetName)98 public static XSpreadsheet getSCSheetByName(XSpreadsheetDocument xSpreadsheetDocument, String sheetName) throws Exception { 99 XSpreadsheets xSpreadsheets = xSpreadsheetDocument.getSheets(); 100 XSpreadsheet xSpreadsheet = 101 (XSpreadsheet) UnoRuntime.queryInterface(XSpreadsheet.class, xSpreadsheets.getByName(sheetName)); 102 103 return xSpreadsheet; 104 } 105 106 /** 107 * Get sheet object by sheet index 108 * @param xSpreadsheetDocument 109 * @param index (Short) 0,1,2,... 110 * @return 111 * @throws Exception 112 */ getSCSheetByIndex(XSpreadsheetDocument xSpreadsheetDocument, short index)113 public static XSpreadsheet getSCSheetByIndex(XSpreadsheetDocument xSpreadsheetDocument, short index) throws Exception { 114 XSpreadsheets xSpreadsheets = xSpreadsheetDocument.getSheets(); 115 XIndexAccess xIndexAccess = 116 (XIndexAccess) UnoRuntime.queryInterface(XIndexAccess.class, xSpreadsheets); 117 XSpreadsheet xSpreadsheet = 118 (XSpreadsheet) UnoRuntime.queryInterface(XSpreadsheet.class, xIndexAccess.getByIndex(index)); 119 120 return xSpreadsheet; 121 } 122 123 /** 124 * Get sheet name by sheet index 125 * 126 * @param xSpreadsheetDocument 127 * @param index 128 * (Short) 0,1,2,... 129 * @return 130 * @throws Exception 131 */ getSCSheetNameByIndex( XSpreadsheetDocument xSpreadsheetDocument, short index)132 public static String getSCSheetNameByIndex( 133 XSpreadsheetDocument xSpreadsheetDocument, short index) 134 throws Exception { 135 XSpreadsheets xSpreadsheets = xSpreadsheetDocument.getSheets(); 136 XIndexAccess xIndexAccess = (XIndexAccess) UnoRuntime.queryInterface( 137 XIndexAccess.class, xSpreadsheets); 138 XSpreadsheet xSpreadsheet = (XSpreadsheet) UnoRuntime.queryInterface( 139 XSpreadsheet.class, xIndexAccess.getByIndex(index)); 140 XNamed xsheetname = (XNamed) UnoRuntime.queryInterface(XNamed.class, 141 xSpreadsheet); 142 return xsheetname.getName(); 143 } 144 145 /** 146 * Set sheet name by sheet index 147 * 148 * @param xSpreadsheetDocument 149 * @param index 150 * (Short) 0,1,2,... 151 * @return 152 * @throws Exception 153 */ setSCSheetNameByIndex( XSpreadsheetDocument xSpreadsheetDocument, short index, String sheetname)154 public static void setSCSheetNameByIndex( 155 XSpreadsheetDocument xSpreadsheetDocument, short index, 156 String sheetname) throws Exception { 157 XSpreadsheets xSpreadsheets = xSpreadsheetDocument.getSheets(); 158 XIndexAccess xIndexAccess = (XIndexAccess) UnoRuntime.queryInterface( 159 XIndexAccess.class, xSpreadsheets); 160 XSpreadsheet xSpreadsheet = (XSpreadsheet) UnoRuntime.queryInterface( 161 XSpreadsheet.class, xIndexAccess.getByIndex(index)); 162 XNamed xsheetname = (XNamed) UnoRuntime.queryInterface(XNamed.class, 163 xSpreadsheet); 164 xsheetname.setName(sheetname); 165 } 166 167 /** 168 * Get rows object 169 * @param xSpreadsheet 170 * @return 171 * @throws Exception 172 */ getSCRows(XSpreadsheet xSpreadsheet)173 public static XTableRows getSCRows(XSpreadsheet xSpreadsheet) throws Exception { 174 XColumnRowRange xColumnRowRange = 175 (XColumnRowRange) UnoRuntime.queryInterface(XColumnRowRange.class, xSpreadsheet); 176 XTableRows xTableRows = xColumnRowRange.getRows(); 177 178 return xTableRows; 179 } 180 181 /** 182 * Get columns object 183 * @param xSpreadsheet 184 * @return 185 * @throws Exception 186 */ getSCColumns(XSpreadsheet xSpreadsheet)187 public static XTableColumns getSCColumns(XSpreadsheet xSpreadsheet) throws Exception { 188 XColumnRowRange xColumnRowRange = 189 (XColumnRowRange) UnoRuntime.queryInterface(XColumnRowRange.class, xSpreadsheet); 190 XTableColumns xTableColumns = xColumnRowRange.getColumns(); 191 192 return xTableColumns; 193 } 194 195 /** 196 * Set floating number into specific cell 197 * @param xSpreadsheet 198 * @param column 199 * @param row 200 * @param value 201 * @throws Exception 202 */ setValueToCell(XSpreadsheet xSpreadsheet, int column, int row, double value)203 public static void setValueToCell(XSpreadsheet xSpreadsheet, int column, int row, double value) throws Exception { 204 XCell xCell = xSpreadsheet.getCellByPosition(column, row); 205 xCell.setValue(value); 206 } 207 208 /** 209 * Set text into specific cell 210 * @param xSpreadsheet 211 * @param column 212 * @param row 213 * @param text 214 * @throws Exception 215 */ setTextToCell(XSpreadsheet xSpreadsheet, int column, int row, String text)216 public static void setTextToCell(XSpreadsheet xSpreadsheet, int column, int row, String text) throws Exception { 217 XCell xCell = xSpreadsheet.getCellByPosition(column, row); 218 XText xText = (XText) UnoRuntime.queryInterface(XText.class, xCell); 219 xText.setString(text); 220 } 221 222 /** 223 * Set text into specific cell 224 * @param xCell 225 * @param text 226 * @throws Exception 227 */ setTextToCell(XCell xCell, String text)228 public static void setTextToCell(XCell xCell, String text) throws Exception { 229 XText xText = (XText) UnoRuntime.queryInterface(XText.class, xCell); 230 xText.setString(text); 231 } 232 233 /** 234 * Set formula into specific cell 235 * @param xSpreadsheet 236 * @param column 237 * @param row 238 * @param formula 239 * @throws Exception 240 */ setFormulaToCell(XSpreadsheet xSpreadsheet, int column, int row, String formula)241 public static void setFormulaToCell(XSpreadsheet xSpreadsheet, int column, int row, String formula) throws Exception { 242 XCell xCell = xSpreadsheet.getCellByPosition(column, row); 243 xCell.setFormula(formula); 244 } 245 246 /** 247 * Get value from specific cell 248 * @param xSpreadsheet 249 * @param column 250 * @param row 251 * @return 252 * @throws Exception 253 */ getValueFromCell(XSpreadsheet xSpreadsheet, int column, int row)254 public static double getValueFromCell(XSpreadsheet xSpreadsheet, int column, int row) throws Exception { 255 XCell xCell = xSpreadsheet.getCellByPosition(column, row); 256 double cellValue = xCell.getValue(); 257 258 return cellValue; 259 } 260 261 /** 262 * Get text from specific cell 263 * @param xSpreadsheet 264 * @param column 265 * @param row 266 * 267 * @return 268 * @throws Exception 269 */ getTextFromCell(XSpreadsheet xSpreadsheet, int column, int row)270 public static String getTextFromCell(XSpreadsheet xSpreadsheet, int column, int row) throws Exception { 271 XCell xCell = xSpreadsheet.getCellByPosition(column, row); 272 XText xText = (XText) UnoRuntime.queryInterface(XText.class, xCell); 273 274 return xText.getString(); 275 } 276 277 /** 278 * Get formula string from specific cell 279 * @param xSpreadsheet 280 * @param column 281 * @param row 282 * @return 283 * @throws Exception 284 */ getFormulaFromCell(XSpreadsheet xSpreadsheet, int column, int row)285 public static String getFormulaFromCell(XSpreadsheet xSpreadsheet, int column, int row) throws Exception { 286 XCell xCell = xSpreadsheet.getCellByPosition(column, row); 287 String cellFormula = xCell.getFormula(); 288 289 return cellFormula; 290 } 291 292 /** 293 * Set numbers into a cell range 294 * @param xSpreadsheet 295 * @param start_col 296 * @param start_row 297 * @param end_col 298 * @param end_row 299 * @param values 300 * @throws Exception 301 */ 302 @Deprecated setValueToCellRange(XSpreadsheet xSpreadsheet, int start_col, int start_row, int end_col, int end_row, double[][] values)303 public static void setValueToCellRange(XSpreadsheet xSpreadsheet, int start_col, int start_row, int end_col, int end_row, double[][] values) throws Exception { 304 XCellRange xCellRange = xSpreadsheet.getCellRangeByPosition(start_col, start_row, end_col, end_row); 305 XCell xCell = null; 306 for (int i = 0; i <= (end_row - start_row); i++ ) { 307 for(int j = 0; j <= (end_col - start_col); j++) { 308 xCell = xCellRange.getCellByPosition(j, i); 309 xCell.setValue(values[i][j]); 310 } 311 } 312 } 313 setValueToCellRange(XSpreadsheet xSpreadsheet, int start_col, int start_row, double[][] values)314 public static void setValueToCellRange(XSpreadsheet xSpreadsheet, int start_col, int start_row, double[][] values) throws Exception { 315 XCellRange xCellRange = xSpreadsheet.getCellRangeByPosition(start_col, start_row, start_col + values[0].length - 1, start_row + values.length - 1); 316 XCell xCell = null; 317 for (int i = 0; i < values.length; i++ ) { 318 for(int j = 0; j < values[0].length; j++) { 319 xCell = xCellRange.getCellByPosition(j, i); 320 xCell.setValue(values[i][j]); 321 } 322 } 323 } 324 325 /** 326 * Set text into a cell range 327 * @param xSpreadsheet 328 * @param start_col 329 * @param start_row 330 * @param end_col 331 * @param end_row 332 * @param texts 333 * @throws Exception 334 */ 335 @Deprecated setTextToCellRange(XSpreadsheet xSpreadsheet, int start_col, int start_row, int end_col, int end_row, String[][] texts)336 public static void setTextToCellRange(XSpreadsheet xSpreadsheet, int start_col, int start_row, int end_col, int end_row, String[][] texts) throws Exception { 337 XCellRange xCellRange = xSpreadsheet.getCellRangeByPosition(start_col, start_row, end_col, end_row); 338 XCell xCell = null; 339 XText xText = null; 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 xText = (XText) UnoRuntime.queryInterface(XText.class, xCell); 344 xText.setString(texts[i][j]); 345 } 346 } 347 } 348 setTextToCellRange(XSpreadsheet xSpreadsheet, int start_col, int start_row, String[][] texts)349 public static void setTextToCellRange(XSpreadsheet xSpreadsheet, int start_col, int start_row, String[][] texts) throws Exception { 350 XCellRange xCellRange = xSpreadsheet.getCellRangeByPosition(start_col, start_row, start_col + texts[0].length - 1, start_row + texts.length - 1); 351 XCell xCell = null; 352 XText xText = null; 353 for (int i = 0; i < texts.length; i++ ) { 354 for(int j = 0; j < texts[0].length; j++) { 355 xCell = xCellRange.getCellByPosition(j, i); 356 xText = (XText) UnoRuntime.queryInterface(XText.class, xCell); 357 xText.setString(texts[i][j]); 358 } 359 } 360 } 361 362 /** 363 * Get number content from a cell range 364 * @param xSpreadsheet 365 * @param start_col 366 * @param start_row 367 * @param end_col 368 * @param end_row 369 * @return 370 * @throws Exception 371 */ getValueFromCellRange(XSpreadsheet xSpreadsheet, int start_col, int start_row, int end_col, int end_row)372 public static double[][] getValueFromCellRange(XSpreadsheet xSpreadsheet, int start_col, int start_row, int end_col, int end_row) throws Exception { 373 XCellRange xCellRange = xSpreadsheet.getCellRangeByPosition(start_col, start_row, end_col, end_row); 374 XCell xCell = null; 375 double[][] cellValues = new double[end_row - start_row+1][end_col - start_col +1]; 376 377 for (int i = 0; i <= (end_row - start_row); i++ ) { 378 for(int j = 0; j <= (end_col - start_col); j++) { 379 xCell = xCellRange.getCellByPosition(j, i); 380 cellValues[i][j] = xCell.getValue(); 381 } 382 } 383 384 return cellValues; 385 } 386 387 /** 388 * Get text content from a cell range 389 * @param xSpreadsheet 390 * @param start_col 391 * @param start_row 392 * @param end_col 393 * @param end_row 394 * @return 395 * @throws Exception 396 */ getTextFromCellRange(XSpreadsheet xSpreadsheet, int start_col, int start_row, int end_col, int end_row)397 public static String[][] getTextFromCellRange(XSpreadsheet xSpreadsheet, int start_col, int start_row, int end_col, int end_row) throws Exception { 398 XCellRange xCellRange = xSpreadsheet.getCellRangeByPosition(start_col, start_row, end_col, end_row); 399 XCell xCell = null; 400 XText xText = null; 401 String[][] cellTexts = new String[end_row - start_row+1][end_col - start_col +1]; 402 403 for (int i = 0; i <= (end_row - start_row); i++ ) { 404 for (int j = 0; j <= (end_col - start_col); j++) { 405 xCell = xCellRange.getCellByPosition(j, i); 406 xText = (XText) UnoRuntime.queryInterface(XText.class, xCell); 407 cellTexts[i][j] = xText.getString(); 408 } 409 } 410 411 return cellTexts; 412 } 413 414 //TODO ZS - public static String[][] getAllFromCellRange 415 416 /** 417 * Switch to specific sheet 418 * @param xSpreadsheetDocument 419 * @param xSpreadsheet 420 */ setCurrentSheet(XSpreadsheetDocument xSpreadsheetDocument, XSpreadsheet xSpreadsheet)421 public static void setCurrentSheet(XSpreadsheetDocument xSpreadsheetDocument, XSpreadsheet xSpreadsheet) throws Exception { 422 XModel xModel = (XModel) UnoRuntime.queryInterface(XModel.class, xSpreadsheetDocument); 423 XController xController = xModel.getCurrentController(); 424 XSpreadsheetView xSpreadsheetView = (XSpreadsheetView) UnoRuntime.queryInterface(XSpreadsheetView.class, xController); 425 xSpreadsheetView.setActiveSheet(xSpreadsheet); 426 } 427 428 /** 429 * Get sheet object of current active sheet 430 * @param xSpreadsheetDocument 431 * @return 432 */ getCurrentSheet(XSpreadsheetDocument xSpreadsheetDocument)433 public static XSpreadsheet getCurrentSheet(XSpreadsheetDocument xSpreadsheetDocument) throws Exception { 434 XModel xModel = (XModel) UnoRuntime.queryInterface(XModel.class, xSpreadsheetDocument); 435 XController xController = xModel.getCurrentController(); 436 XSpreadsheetView xSpreadsheetView = (XSpreadsheetView) UnoRuntime.queryInterface(XSpreadsheetView.class, xController); 437 XSpreadsheet xSpreadsheet = xSpreadsheetView.getActiveSheet(); 438 439 return xSpreadsheet; 440 } 441 442 /** 443 * Get sheet object by sheet index 444 * 445 * @param xSpreadsheetDocument 446 * @return 447 * @throws Exception 448 */ getSCActiveSheetName( XSpreadsheetDocument xSpreadsheetDocument)449 public static String getSCActiveSheetName( 450 XSpreadsheetDocument xSpreadsheetDocument) throws Exception { 451 XModel xSpreadsheetModel = (XModel) UnoRuntime.queryInterface( 452 XModel.class, xSpreadsheetDocument); 453 XSpreadsheetView xSpeadsheetView = (XSpreadsheetView) UnoRuntime 454 .queryInterface(XSpreadsheetView.class, 455 xSpreadsheetModel.getCurrentController()); 456 XSpreadsheet activesheet = xSpeadsheetView.getActiveSheet(); 457 XNamed activesheetName = (XNamed) UnoRuntime.queryInterface( 458 XNamed.class, activesheet); 459 return activesheetName.getName(); 460 } 461 462 /** 463 * Set specific property's value for an object 464 * @param obj 465 * @param propName 466 * @param value 467 * @throws Exception 468 */ setProperties(Object obj, String propName, Object value)469 public static void setProperties(Object obj, String propName, Object value) throws Exception { 470 XPropertySet xPropertySet = 471 (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, obj); 472 xPropertySet.setPropertyValue(propName, value); 473 } 474 475 /** 476 * Get specific property's value of an object 477 * @param obj 478 * @param propName 479 * @return 480 * @throws Exception 481 */ getProperties(Object obj, String propName)482 public static Object getProperties(Object obj, String propName) throws Exception { 483 XPropertySet xPropertySet = 484 (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, obj); 485 Object value = xPropertySet.getPropertyValue(propName); 486 487 return value; 488 } 489 490 /** 491 * Set value of specific property from a cell 492 * @param xCell 493 * @param propName 494 * @param value 495 * @throws Exception 496 */ setCellProperties(XCell xCell, String propName, Object value)497 public static void setCellProperties(XCell xCell, String propName, Object value) throws Exception { 498 499 setProperties(xCell, propName, value); 500 } 501 502 /** 503 * Get value of specific property from a cell 504 * @param xCell 505 * @param propName 506 * @return 507 * @throws Exception 508 */ getCellProperties(XCell xCell, String propName)509 public static Object getCellProperties(XCell xCell, String propName) throws Exception { 510 return getProperties(xCell, propName); 511 } 512 513 /** 514 * Clear temp file directory 515 */ clearTempDir()516 public static void clearTempDir() { 517 FileUtil.deleteFile(Testspace.getFile(Testspace.getPath(scTempDir))); 518 } 519 520 /** 521 * Save file as specific file format into spreadsheet temp file folder. 522 * @param scComponent 523 * @param fileName File name string without extension name (e.g. "sampleFile") 524 * @param extName ("ods", "ots", "xls", "xlt", "csv") 525 * @throws Exception 526 */ saveFileAs(XComponent scComponent, String fileName, String extName)527 public static void saveFileAs(XComponent scComponent, String fileName, String extName) throws Exception { 528 529 initFilterName(); 530 531 String storeUrl = Testspace.getUrl(scTempDir + fileName + "." + extName); 532 533 PropertyValue[] storeProps = new PropertyValue[2]; 534 storeProps[0] = new PropertyValue(); 535 storeProps[0].Name = "FilterName"; 536 storeProps[0].Value = filterName.get(extName); 537 storeProps[1] = new PropertyValue(); 538 storeProps[1].Name = "Overwrite"; 539 storeProps[1].Value = new Boolean(true); 540 541 XStorable scStorable = 542 (XStorable) UnoRuntime.queryInterface(XStorable.class, scComponent); 543 scStorable.storeAsURL(storeUrl, storeProps); 544 } 545 546 /** 547 * Save file after open file. 548 * @param xSpreadsheetDocument 549 * @throws Exception 550 */ save(XSpreadsheetDocument xSpreadsheetDocument)551 public static void save(XSpreadsheetDocument xSpreadsheetDocument) 552 throws Exception { 553 XStorable scStorable = (XStorable) UnoRuntime.queryInterface( 554 XStorable.class, xSpreadsheetDocument); 555 scStorable.store(); 556 } 557 558 559 /** 560 * Close specific opening spreadsheet file which has been saved 561 * @param xSpreadsheetDocument 562 * @throws Exception 563 */ closeFile(XSpreadsheetDocument xSpreadsheetDocument)564 public static void closeFile(XSpreadsheetDocument xSpreadsheetDocument) throws Exception { 565 XCloseable xCloseable = (XCloseable) UnoRuntime.queryInterface(XCloseable.class, xSpreadsheetDocument); 566 xCloseable.close(false); 567 } 568 569 /** 570 * Close a opening file saved in spreadsheet temp file direction and reopen it in Spreadsheet. For save&reload test scenario only. 571 * @param unoApp 572 * @param xSpreadsheetDocument 573 * @param fullFileName File name with the extension name. (e.g. "sc.ods") 574 * @return 575 * @throws Exception 576 */ reloadFile(UnoApp unoApp, XSpreadsheetDocument xSpreadsheetDocument, String fullFileName)577 public static XSpreadsheetDocument reloadFile(UnoApp unoApp, XSpreadsheetDocument xSpreadsheetDocument, String fullFileName) throws Exception { 578 closeFile(xSpreadsheetDocument); 579 580 String filePath = Testspace.getPath(scTempDir + fullFileName); 581 XSpreadsheetDocument xScDocument = (XSpreadsheetDocument) UnoRuntime.queryInterface(XSpreadsheetDocument.class, unoApp.loadDocument(filePath)); 582 583 return xScDocument; 584 } 585 586 /** 587 * open file in Spreadsheet. 588 * @param app 589 * @param filePath File path with the extension name. (e.g. "testcase/uno/sc/data/sample.xls") 590 * @return 591 * @throws Exception 592 */ openFile(String filePath, UnoApp app)593 public static XSpreadsheetDocument openFile(String filePath, UnoApp app) throws Exception { 594 return (XSpreadsheetDocument) UnoRuntime.queryInterface(XSpreadsheetDocument.class, app.loadDocument(filePath)); 595 } 596 597 /** 598 * Initial the filter name list 599 * @throws Exception 600 */ initFilterName()601 private static void initFilterName() throws Exception { 602 if (filterName.size() > 0) { 603 return; 604 } 605 606 filterName.put("ods", "calc8"); 607 filterName.put("ots", "calc8_template"); 608 filterName.put("xls", "MS Excel 97"); 609 filterName.put("xlt", "MS Excel 97 Vorlage/Template"); 610 filterName.put("csv", "Text - txt - csv (StarCalc)"); 611 } 612 613 614 /*************************************************************** 615 * Chart Utility method - using chart interface * 616 ****************************************************************/ 617 618 /** 619 * Get a CellRangeAddress by cell range reference name 620 * @param xSpreadsheet 621 * @param rangeName a cell range reference name(e.g. "A1:B2") 622 * @return 623 */ getChartDataRangeByName(XSpreadsheet xSpreadsheet, String rangeName)624 public static CellRangeAddress getChartDataRangeByName(XSpreadsheet xSpreadsheet, String rangeName) { 625 XCellRange cellRange = xSpreadsheet.getCellRangeByName(rangeName); 626 XCellRangeAddressable xCellRangeAddressable = 627 (XCellRangeAddressable) UnoRuntime.queryInterface(XCellRangeAddressable.class, cellRange); 628 629 CellRangeAddress cellRangeAddress = xCellRangeAddressable.getRangeAddress(); 630 return cellRangeAddress; 631 } 632 633 /** 634 * Create a spreadsheet chart with data in a specific cell range. 635 * @param xSpreadsheet 636 * @param rec a rectangle shape object 637 * @param dataRangeAddress the CellRangeAddress array of chart data source 638 * @param chartName 639 * @return 640 * @throws Exception 641 */ createChart(XSpreadsheet xSpreadsheet, Rectangle rec, CellRangeAddress[] dataRangeAddress, String chartName)642 public static XChartDocument createChart(XSpreadsheet xSpreadsheet, Rectangle rec, CellRangeAddress[] dataRangeAddress, String chartName) throws Exception { 643 644 return createChart(xSpreadsheet, rec, dataRangeAddress, chartName, true, false); 645 } 646 647 /** 648 * Create a spreadsheet chart with data in a specific cell range with column/row label enable/not. 649 * @param xSpreadsheet 650 * @param rec a rectangle shape object 651 * @param dataRangeAddress the CellRangeAddress array of chart data source 652 * @param chartName 653 * @param hasColumnLabel 654 * @param hasRowLabel 655 * @return 656 * @throws Exception 657 */ createChart(XSpreadsheet xSpreadsheet, Rectangle rec, CellRangeAddress[] dataRangeAddress, String chartName, Boolean hasColumnLabel, Boolean hasRowLabel)658 public static XChartDocument createChart(XSpreadsheet xSpreadsheet, Rectangle rec, CellRangeAddress[] dataRangeAddress, String chartName, Boolean hasColumnLabel, Boolean hasRowLabel) throws Exception { 659 XChartDocument xChartDocument = null; 660 XTableChartsSupplier xTChartSupplier = 661 (XTableChartsSupplier) UnoRuntime.queryInterface(XTableChartsSupplier.class, xSpreadsheet); 662 XTableCharts xTableCharts = xTChartSupplier.getCharts(); 663 XNameAccess xNameAccess = 664 (XNameAccess) UnoRuntime.queryInterface(XNameAccess.class, xTableCharts); 665 if (xNameAccess != null && !xNameAccess.hasByName(chartName)) { 666 667 xTableCharts.addNewByName(chartName, rec, dataRangeAddress, hasColumnLabel, hasRowLabel); 668 XTableChart xTableChart = (XTableChart) UnoRuntime.queryInterface( 669 XTableChart.class, xNameAccess.getByName(chartName)); 670 XEmbeddedObjectSupplier xEmbeddedObjectSupplier = (XEmbeddedObjectSupplier) UnoRuntime.queryInterface( 671 XEmbeddedObjectSupplier.class, xTableChart); 672 xChartDocument = (XChartDocument) UnoRuntime.queryInterface( 673 XChartDocument.class, xEmbeddedObjectSupplier.getEmbeddedObject()); 674 } 675 676 return xChartDocument; 677 } 678 679 /** 680 * Get XChartDocument object via the chart name. 681 * @param xSpreadsheet 682 * @param chartName 683 * @return 684 * @throws Exception 685 */ getChartByName(XSpreadsheet xSpreadsheet, String chartName)686 public static XChartDocument getChartByName(XSpreadsheet xSpreadsheet, String chartName) throws Exception { 687 XChartDocument xChartDocument = null; 688 XTableChartsSupplier xTChartSupplier = 689 (XTableChartsSupplier) UnoRuntime.queryInterface(XTableChartsSupplier.class, xSpreadsheet); 690 XTableCharts xTableCharts = xTChartSupplier.getCharts(); 691 XNameAccess xNameAccess = 692 (XNameAccess) UnoRuntime.queryInterface(XNameAccess.class, xTableCharts); 693 694 if (xNameAccess != null && xNameAccess.hasByName(chartName)) { 695 XTableChart xTableChart = (XTableChart) UnoRuntime.queryInterface( 696 XTableChart.class, xNameAccess.getByName(chartName)); 697 XEmbeddedObjectSupplier xEmbeddedObjectSupplier = (XEmbeddedObjectSupplier) UnoRuntime.queryInterface( 698 XEmbeddedObjectSupplier.class, xTableChart); 699 xChartDocument = (XChartDocument) UnoRuntime.queryInterface( 700 XChartDocument.class, xEmbeddedObjectSupplier.getEmbeddedObject()); 701 } 702 703 return xChartDocument; 704 } 705 706 /** 707 * Set specific basic type to chart 708 * @param xChartDocument 709 * @param chartType 710 * @throws Exception 711 */ setChartType(XChartDocument xChartDocument, String chartType)712 public static void setChartType(XChartDocument xChartDocument, String chartType) throws Exception { 713 XMultiServiceFactory xMultiServiceFactory = (XMultiServiceFactory) UnoRuntime.queryInterface( 714 XMultiServiceFactory.class, xChartDocument); 715 XDiagram xDiagram = (XDiagram) UnoRuntime.queryInterface( 716 XDiagram.class, xMultiServiceFactory.createInstance(chartType)); 717 xChartDocument.setDiagram(xDiagram); 718 } 719 720 /** 721 * Get the type string of a chart 722 * @param xChartDocument 723 * @return 724 * @throws Exception 725 */ getChartType(XChartDocument xChartDocument)726 public static String getChartType(XChartDocument xChartDocument) throws Exception { 727 return xChartDocument.getDiagram().getDiagramType(); 728 } 729 730 /** 731 * Get the names of charts in specific sheet 732 * @param xSpreadsheet 733 * @return 734 * @throws Exception 735 */ getChartNameList(XSpreadsheet xSpreadsheet)736 public static String[] getChartNameList(XSpreadsheet xSpreadsheet) throws Exception { 737 XTableChartsSupplier xTChartSupplier = 738 (XTableChartsSupplier) UnoRuntime.queryInterface(XTableChartsSupplier.class, xSpreadsheet); 739 XTableCharts xTableCharts = xTChartSupplier.getCharts(); 740 String[] chartNames = xTableCharts.getElementNames(); 741 return chartNames; 742 } 743 744 745 746 } 747