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 fvt.mix.sc.sheet; 23 24 import static org.junit.Assert.*; 25 26 import org.junit.After; 27 import org.junit.AfterClass; 28 import org.junit.Before; 29 import org.junit.BeforeClass; 30 import org.junit.Rule; 31 import org.junit.Test; 32 33 import org.openoffice.test.common.Logger; 34 import org.openoffice.test.common.Testspace; 35 import org.openoffice.test.uno.UnoApp; 36 37 import com.sun.star.beans.XPropertySet; 38 import com.sun.star.container.XIndexAccess; 39 import com.sun.star.frame.XDesktop; 40 import com.sun.star.lang.XComponent; 41 import com.sun.star.sheet.SheetLinkMode; 42 import com.sun.star.sheet.XSheetLinkable; 43 import com.sun.star.sheet.XSpreadsheet; 44 import com.sun.star.sheet.XSpreadsheetDocument; 45 import com.sun.star.sheet.XSpreadsheets; 46 import com.sun.star.uno.UnoRuntime; 47 import com.sun.star.util.XRefreshable; 48 49 import org.openoffice.test.OpenOffice; 50 import org.openoffice.test.vcl.widgets.VclApp; 51 import org.openoffice.test.vcl.widgets.VclMessageBox; 52 import org.openoffice.test.vcl.widgets.VclButton; 53 import org.openoffice.test.vcl.widgets.VclComboBox; 54 import org.openoffice.test.vcl.widgets.VclListBox; 55 import org.openoffice.test.vcl.widgets.VclDialog; 56 import org.openoffice.test.vcl.client.Constant; 57 import static org.openoffice.test.vcl.Tester.sleep; 58 59 import org.openoffice.test.vcl.client.VclHookException; 60 61 import testlib.uno.SCUtil; 62 63 /** 64 * test inserting linked and non-linked sheets from an XLS spreadsheet. 65 */ 66 public class InsertExcelSheetTest { 67 68 OpenOffice aoo; 69 UnoApp unoApp; 70 VclApp vclApp; 71 VclMessageBox activeMsgBox; 72 VclButton scAfterCurrentSheet; 73 VclButton scNewSheetFromFile; 74 VclButton scFromFileLink; 75 VclButton scFromFileBrowse; 76 VclComboBox filePickerPath; 77 VclButton filePickerOpen; 78 VclListBox scFromFileSheetList; 79 VclDialog scInsertSheetDlg; 80 VclDialog scSelectSheetsDlg; 81 VclListBox scSheetsList; 82 83 XSpreadsheetDocument scDocument = null; 84 XComponent scComponent = null; 85 86 @Rule 87 public Logger log = Logger.getLogger(this); 88 89 90 @BeforeClass setUpBeforeClass()91 public static void setUpBeforeClass() throws Exception { 92 93 } 94 95 @AfterClass tearDownAfterClass()96 public static void tearDownAfterClass() throws Exception { 97 } 98 99 @Before setUp()100 public void setUp() throws Exception { 101 102 OpenOffice aoo = OpenOffice.getDefault(); 103 unoApp = new UnoApp(aoo); 104 vclApp = new VclApp(aoo); 105 scAfterCurrentSheet = new VclButton(vclApp, "sc:RadioButton:RID_SCDLG_INSERT_TABLE:RB_BEHIND"); 106 scNewSheetFromFile = new VclButton(vclApp, "sc:RadioButton:RID_SCDLG_INSERT_TABLE:RB_FROMFILE"); 107 scFromFileLink = new VclButton(vclApp, "sc:CheckBox:RID_SCDLG_INSERT_TABLE:CB_LINK"); 108 scFromFileBrowse = new VclButton(vclApp, "sc:PushButton:RID_SCDLG_INSERT_TABLE:BTN_BROWSE"); 109 filePickerPath = new VclComboBox(vclApp, "SVT_HID_FILEDLG_AUTOCOMPLETEBOX"); 110 filePickerOpen = new VclButton(vclApp, "fpicker:PushButton:DLG_SVT_EXPLORERFILE:BTN_EXPLORERFILE_OPEN"); 111 scFromFileSheetList = new VclListBox(vclApp, "sc:MultiListBox:RID_SCDLG_INSERT_TABLE:LB_TABLES"); 112 scInsertSheetDlg = new VclDialog(vclApp, "sc:ModalDialog:RID_SCDLG_INSERT_TABLE"); 113 scSelectSheetsDlg = new VclDialog(vclApp, ".uno:SelectTables"); 114 scSheetsList = new VclListBox(vclApp, "SC_HID_SELECTTABLES"); 115 116 // existing setup 117 unoApp.start(); 118 // New a SC document 119 scComponent = unoApp.newDocument("scalc"); 120 121 // New a document source.xls, add value to 3 sheet 122 scDocument = SCUtil.getSCDocument(scComponent); 123 XSpreadsheets spreadsheets = scDocument.getSheets(); 124 XSpreadsheet firstSheet = SCUtil.getSCSheetByIndex(scDocument, 125 (short) 0); 126 XSpreadsheet secondSheet = SCUtil.getSCSheetByIndex(scDocument, 127 (short) 1); 128 XSpreadsheet thirdSheet = SCUtil.getSCSheetByIndex(scDocument, 129 (short) 2); 130 SCUtil.setFormulaToCell(firstSheet, 1, 2, "=1*2"); // cell B3 131 SCUtil.setFormulaToCell(secondSheet, 1, 2, "=2*2"); 132 SCUtil.setFormulaToCell(thirdSheet, 1, 2, "=3*2"); 133 134 // Save and close this document 135 SCUtil.saveFileAs(scComponent, "source", "xls"); 136 SCUtil.closeFile(scDocument); 137 138 } 139 140 @After tearDown()141 public void tearDown() throws Exception { 142 unoApp.closeDocument(scComponent); 143 unoApp.close(); 144 } 145 146 /** 147 * test insert sheet from other file with a normal link meaning the cell 148 * will return the formula and text values like the source and the link 149 * will update the cell. 150 */ 151 @Test() insertSheetFromFileLinkedNormal()152 public void insertSheetFromFileLinkedNormal() throws Exception { 153 154 // get source document URL 155 String SourcestoreUrl = Testspace.getUrl("output/sc/" + "source" + "." 156 + "xls"); 157 158 // New a document 159 scComponent = unoApp.newDocument("scalc"); 160 scDocument = SCUtil.getSCDocument(scComponent); 161 XSpreadsheets spreadsheets = scDocument.getSheets(); 162 163 // Insert firstexternalsheet sheet, link with Sheet1 in source document 164 // and the link mode is NORMAL 165 166 // using VCL 167 boolean retry = false; 168 int tryCount = 0; 169 int maxTries = 5; 170 do { 171 tryCount++; // starts at 1 172 retry = false; 173 try { 174 vclApp.dispatch(".uno:SelectTables"); 175 } catch (java.lang.Exception e) { 176 // e.printStackTrace(); 177 log.info("Exception " + tryCount + "Caught !"); 178 retry = true; 179 if (tryCount == maxTries) { 180 throw e; // throw the exception 181 } 182 sleep(1); 183 } 184 } while ((retry) && (tryCount < maxTries)); 185 186 // select sheet3 187 scSheetsList.select(2); 188 189 scSelectSheetsDlg.ok(); 190 191 // Open Insert Sheet dialog via main menu Insert-> Sheet 192 vclApp.dispatch(".uno:Insert"); 193 194 // set after current sheet 195 scAfterCurrentSheet.setChecked(true); 196 197 // enable new sheet from file 198 scNewSheetFromFile.setChecked(true); 199 200 // check Link 201 scFromFileLink.setChecked(true); 202 203 // browse 204 scFromFileBrowse.click(); 205 206 // opens Insert dialog 207 208 // add filename 209 filePickerPath.setText(SourcestoreUrl); 210 211 // Insert button 212 filePickerOpen.click(); 213 214 // back to Insert Sheet dialog 215 216 // might have to select from 217 scFromFileSheetList.select(0); 218 219 // select Insert button 220 scInsertSheetDlg.ok(); 221 222 log.info("sheet inserted..."); 223 224 activeMsgBox = new VclMessageBox(vclApp, Constant.UID_ACTIVE); 225 boolean msgExists = activeMsgBox.exists(1); // wait 1 second for the dialog 226 if (msgExists) { 227 log.info("got the update links dialog and accepting"); 228 activeMsgBox.yes(); // yes dialog 229 } else { 230 log.info("missed the update links dialog"); 231 } 232 XSpreadsheet firstexternalsheet = SCUtil.getSCSheetByIndex(scDocument, 233 (short) 3); 234 XSheetLinkable xfirstSheetLinkable = (XSheetLinkable) UnoRuntime 235 .queryInterface(XSheetLinkable.class, firstexternalsheet); 236 237 assertEquals("Expect first sheet link is NORMAL", 238 SheetLinkMode.NORMAL, xfirstSheetLinkable.getLinkMode()); 239 240 // Verify firstexternalsheet 241 assertEquals("Expect first sheet formula should be =1*2", "=1*2", 242 SCUtil.getFormulaFromCell(firstexternalsheet, 1, 2)); 243 assertEquals("Expect first sheet text should be 2", "2", 244 SCUtil.getTextFromCell(firstexternalsheet, 1, 2)); 245 246 log.info("passed first round of assertions"); 247 log.info("saving document as linked-normal.ods"); 248 249 // save document and verify the linked sheet again 250 SCUtil.saveFileAs(scComponent, "linked-normal", "ods"); 251 log.info("reloading linked-normal.ods..."); 252 253 // this will hang on "update links" dialog with no way out - leaving here for others that follow... 254 // XSpreadsheetDocument tempscDocument = SCUtil.reloadFile(unoApp, scDocument, "linked.ods"); 255 256 // open it with VCL instead 257 vclApp.dispatch(".uno:Open"); 258 // add filename 259 filePickerPath.setText("linked-normal.ods"); 260 261 // Insert button 262 filePickerOpen.click(); 263 sleep(1); 264 265 // get the current document as tempscdocument 266 XDesktop xDesktop = unoApp.getDesktop(); 267 // xDesktop.getCurrentComponent returns an XComponent (XModel) 268 XComponent xComponent = xDesktop.getCurrentComponent(); 269 XSpreadsheetDocument tempscDocument = (XSpreadsheetDocument)UnoRuntime.queryInterface( 270 XSpreadsheetDocument.class, xComponent); 271 272 scDocument = tempscDocument; 273 firstexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 3); 274 275 // Verify firstexternalsheet 276 assertEquals("Expect first sheet formula should be =1*2", "=1*2", 277 SCUtil.getFormulaFromCell(firstexternalsheet, 1, 2)); 278 assertEquals("Expect first sheet text should be 2", "2", 279 SCUtil.getTextFromCell(firstexternalsheet, 1, 2)); 280 281 log.info("passed second round of assertions"); 282 283 log.info("saving the document..."); 284 // save and close document 285 SCUtil.save(scDocument); 286 SCUtil.closeFile(scDocument); 287 288 log.info("opening source.xls to modify first sheet..."); 289 // Open source document and change the value in source document 290 XSpreadsheetDocument sourcescDocument = SCUtil.reloadFile(unoApp, 291 scDocument, "source.xls"); 292 XSpreadsheet firstSheet = SCUtil.getSCSheetByIndex(sourcescDocument, (short) 0); 293 SCUtil.setFormulaToCell(firstSheet, 1, 2, "=1*3"); 294 log.info("saving and closing source.xls..."); 295 SCUtil.save(sourcescDocument); 296 SCUtil.closeFile(sourcescDocument); 297 298 log.info("opening linked-normal.ods..."); 299 // open test document 300 vclApp.dispatch(".uno:Open"); 301 // add filename 302 filePickerPath.setText("linked-normal.ods"); 303 304 // Insert button 305 filePickerOpen.click(); 306 307 msgExists = activeMsgBox.exists(1); // wait 1 second for the dialog 308 if (msgExists) { 309 log.info("got the update links dialog and accepting"); 310 activeMsgBox.yes(); // yes dialog 311 } else { 312 log.info("missed the update links dialog"); 313 } 314 315 // get the current document as tempscdocument 316 xDesktop = unoApp.getDesktop(); 317 xComponent = xDesktop.getCurrentComponent(); 318 tempscDocument = (XSpreadsheetDocument)UnoRuntime.queryInterface( 319 XSpreadsheetDocument.class, xComponent); 320 321 scDocument = tempscDocument; 322 spreadsheets = scDocument.getSheets(); 323 324 firstexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 3); 325 326 // get Object SheetLinks for document 327 XPropertySet sheetpropertyset = (XPropertySet) UnoRuntime 328 .queryInterface(XPropertySet.class, scDocument); 329 Object sheetLinks = sheetpropertyset.getPropertyValue("SheetLinks"); 330 331 XIndexAccess xsheetlinks = (XIndexAccess) UnoRuntime.queryInterface( 332 XIndexAccess.class, sheetLinks); 333 334 // Refresh all links 335 log.info("refreshing all links..."); 336 for (int i = 0; i < xsheetlinks.getCount(); i++) { 337 Object sheetlink = xsheetlinks.getByIndex(i); 338 XRefreshable xsheetRefreshable = (XRefreshable) UnoRuntime 339 .queryInterface(XRefreshable.class, sheetlink); 340 xsheetRefreshable.refresh(); 341 log.info("in refresh loop " + i); 342 msgExists = activeMsgBox.exists(1); // wait 1 second for the dialog 343 if (msgExists) { 344 log.info("got the update links dialog and accepting"); 345 activeMsgBox.yes(); // yes dialog 346 } else { 347 log.info("missed the update links dialog"); 348 } 349 } 350 351 // Verify firstexternalsheet did change 352 assertEquals("Expect first sheet formula should be =1*3", "=1*3", 353 SCUtil.getFormulaFromCell(firstexternalsheet, 1, 2)); 354 assertEquals("Expect first sheet text should be 3", "3", 355 SCUtil.getTextFromCell(firstexternalsheet, 1, 2)); 356 357 // Save the document before close 358 SCUtil.save(scDocument); 359 } 360 361 /** 362 * test insert sheet from other file with a value link, meaning the cell 363 * will return the text value for both the formula and the text values and 364 * the link will update the cell. 365 */ 366 @Test() insertSheetFromFileLinkedValue()367 public void insertSheetFromFileLinkedValue() throws Exception { 368 369 // get source document URL 370 String SourcestoreUrl = Testspace.getUrl("output/sc/" + "source" + "." 371 + "xls"); 372 373 // New a document 374 scComponent = unoApp.newDocument("scalc"); 375 scDocument = SCUtil.getSCDocument(scComponent); 376 XSpreadsheets spreadsheets = scDocument.getSheets(); 377 378 // Insert firstexternalsheet sheet, link with Sheet2 in source document 379 // and the link mode is VALUE 380 381 // using VCL 382 boolean retry = false; 383 int tryCount = 0; 384 int maxTries = 5; 385 do { 386 tryCount++; // starts at 1 387 retry = false; 388 try { 389 vclApp.dispatch(".uno:SelectTables"); 390 } catch (java.lang.Exception e) { 391 // e.printStackTrace(); 392 log.info("Exception " + tryCount + "Caught !"); 393 retry = true; 394 if (tryCount == maxTries) { 395 throw e; // throw the exception 396 } 397 sleep(1); 398 } 399 } while ((retry) && (tryCount < maxTries)); 400 401 // select sheet3 402 scSheetsList.select(2); 403 404 scSelectSheetsDlg.ok(); 405 406 // Open Insert Sheet dialog via main menu Insert-> Sheet 407 vclApp.dispatch(".uno:Insert"); 408 409 // set after current sheet 410 scAfterCurrentSheet.setChecked(true); 411 412 // enable new sheet from file 413 scNewSheetFromFile.setChecked(true); 414 415 // check Link 416 scFromFileLink.setChecked(true); 417 418 // browse 419 scFromFileBrowse.click(); 420 421 // opens Insert dialog 422 423 // add filename 424 filePickerPath.setText(SourcestoreUrl); 425 426 // Insert button 427 filePickerOpen.click(); 428 429 // back to Insert Sheet dialog 430 431 // select sheet 2 432 scFromFileSheetList.select(1); 433 434 // select Insert button 435 scInsertSheetDlg.ok(); 436 437 log.info("sheet inserted..."); 438 439 // check for message box 440 activeMsgBox = new VclMessageBox(vclApp, Constant.UID_ACTIVE); 441 boolean msgExists = activeMsgBox.exists(1); // wait 1 second for the dialog 442 if (msgExists) { 443 log.info("got the update links dialog and accepting"); 444 activeMsgBox.yes(); // yes dialog 445 } else { 446 log.info("missed the update links dialog"); 447 } 448 XSpreadsheet firstexternalsheet = SCUtil.getSCSheetByIndex(scDocument, 449 (short) 3); 450 XSheetLinkable xfirstSheetLinkable = (XSheetLinkable) UnoRuntime 451 .queryInterface(XSheetLinkable.class, firstexternalsheet); 452 453 // this seem the only way to set this as it's not in the UI dialog 454 xfirstSheetLinkable.setLinkMode(SheetLinkMode.VALUE); 455 456 assertEquals("Expect first sheet link is NORMAL", 457 SheetLinkMode.VALUE, xfirstSheetLinkable.getLinkMode()); 458 459 // Verify firstexternalsheet 460 assertEquals("Expect second sheet formula should be 4", "4", 461 SCUtil.getFormulaFromCell(firstexternalsheet, 1, 2)); 462 assertEquals("Expect second sheet text should be 4", "4", 463 SCUtil.getTextFromCell(firstexternalsheet, 1, 2)); 464 465 log.info("passed first round of assertions..."); 466 467 log.info("saving document as linked-value.ods"); 468 // save document and verify the linked sheet again 469 SCUtil.saveFileAs(scComponent, "linked-value", "ods"); 470 471 log.info("reloading linked-value.ods..."); 472 473 // this will hang on "update links" dialog with no way out - leaving here for others that follow... 474 // XSpreadsheetDocument tempscDocument = SCUtil.reloadFile(unoApp, scDocument, "linked.ods"); 475 476 // open it with VCL instead 477 vclApp.dispatch(".uno:Open"); 478 // add filename 479 filePickerPath.setText("linked-value.ods"); 480 481 // Insert button 482 filePickerOpen.click(); 483 sleep(1); 484 485 // get the current document as tempscdocument 486 XDesktop xDesktop = unoApp.getDesktop(); 487 XComponent xComponent = xDesktop.getCurrentComponent(); 488 XSpreadsheetDocument tempscDocument = (XSpreadsheetDocument)UnoRuntime.queryInterface( 489 XSpreadsheetDocument.class, xComponent); 490 491 scDocument = tempscDocument; 492 firstexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 3); 493 494 // Verify firstexternalsheet 495 assertEquals("Expect second sheet formula should be 4", "4", 496 SCUtil.getFormulaFromCell(firstexternalsheet, 1, 2)); 497 assertEquals("Expect second sheet text should be 4", "4", 498 SCUtil.getTextFromCell(firstexternalsheet, 1, 2)); 499 500 log.info("passed second round of assertions"); 501 log.info("saving the document..."); 502 503 // save and close document 504 SCUtil.save(scDocument); 505 SCUtil.closeFile(scDocument); 506 507 // Open source document and change the value in source document 508 XSpreadsheetDocument sourcescDocument = SCUtil.reloadFile(unoApp, 509 scDocument, "source.xls"); 510 XSpreadsheet secondSheet = SCUtil.getSCSheetByIndex(sourcescDocument, (short) 1); 511 SCUtil.setFormulaToCell(secondSheet, 1, 2, "=2*3"); 512 SCUtil.save(sourcescDocument); 513 SCUtil.closeFile(sourcescDocument); 514 515 log.info("opening linked-value.ods..."); 516 // open test document 517 vclApp.dispatch(".uno:Open"); 518 // add filename 519 filePickerPath.setText("linked-value.ods"); 520 521 // Insert button 522 filePickerOpen.click(); 523 524 msgExists = activeMsgBox.exists(1); // wait 1 second for the dialog 525 if (msgExists) { 526 log.info("got the update links dialog and accepting"); 527 activeMsgBox.yes(); // yes dialog 528 } else { 529 log.info("missed the dialog"); 530 } 531 532 // get the current document as tempscdocument 533 xDesktop = unoApp.getDesktop(); 534 xComponent = xDesktop.getCurrentComponent(); 535 tempscDocument = (XSpreadsheetDocument)UnoRuntime.queryInterface( 536 XSpreadsheetDocument.class, xComponent); 537 538 scDocument = tempscDocument; 539 spreadsheets = scDocument.getSheets(); 540 541 firstexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 3); 542 543 // get Object SheetLinks for document 544 XPropertySet sheetpropertyset = (XPropertySet) UnoRuntime 545 .queryInterface(XPropertySet.class, scDocument); 546 Object sheetLinks = sheetpropertyset.getPropertyValue("SheetLinks"); 547 548 XIndexAccess xsheetlinks = (XIndexAccess) UnoRuntime.queryInterface( 549 XIndexAccess.class, sheetLinks); 550 551 log.info("refreshing all links..."); 552 // Refresh all links 553 for (int i = 0; i < xsheetlinks.getCount(); i++) { 554 Object sheetlink = xsheetlinks.getByIndex(i); 555 XRefreshable xsheetRefreshable = (XRefreshable) UnoRuntime 556 .queryInterface(XRefreshable.class, sheetlink); 557 xsheetRefreshable.refresh(); 558 log.info("in refresh loop " + i); 559 msgExists = activeMsgBox.exists(1); // wait 1 second for the dialog 560 if (msgExists) { 561 log.info("got the update links dialog and accepting"); 562 activeMsgBox.yes(); // yes dialog 563 } else { 564 log.info("missed the dialog"); 565 } 566 } 567 568 // Verify firstexternalsheet did change 569 assertEquals("Expect first sheet formula should be =6", "6", 570 SCUtil.getFormulaFromCell(firstexternalsheet, 1, 2)); 571 assertEquals("Expect first sheet text should be 6", "6", 572 SCUtil.getTextFromCell(firstexternalsheet, 1, 2)); 573 574 // Save the document before close 575 SCUtil.save(scDocument); 576 577 } 578 579 /** 580 * test insert sheet from other file with no link meaning the cell will 581 * return the formula and text values like the source but the cell will 582 * not update. 583 */ 584 @Test() insertSheetFromFileLinkedNone()585 public void insertSheetFromFileLinkedNone() throws Exception { 586 587 // get source document URL 588 String SourcestoreUrl = Testspace.getUrl("output/sc/" + "source" + "." 589 + "xls"); 590 591 // New a document 592 scComponent = unoApp.newDocument("scalc"); 593 scDocument = SCUtil.getSCDocument(scComponent); 594 XSpreadsheets spreadsheets = scDocument.getSheets(); 595 596 // Insert firstexternalsheet sheet, link with Sheet1 in source document 597 // and the link mode is NORMAL 598 599 // try with VCL 600 boolean retry = false; 601 int tryCount = 0; 602 int maxTries = 5; 603 do { 604 tryCount++; // starts at 1 605 retry = false; 606 try { 607 vclApp.dispatch(".uno:SelectTables"); 608 } catch (java.lang.Exception e) { 609 // e.printStackTrace(); 610 log.info("Exception " + tryCount + "Caught !"); 611 retry = true; 612 if (tryCount == maxTries) { 613 throw e; // throw the exception 614 } 615 sleep(1); 616 } 617 } while ((retry) && (tryCount < maxTries)); 618 619 // select sheet3 620 scSheetsList.select(2); 621 622 scSelectSheetsDlg.ok(); 623 624 // Open Insert Sheet dialog via main menu Insert-> Sheet 625 vclApp.dispatch(".uno:Insert"); 626 627 // set after current sheet 628 scAfterCurrentSheet.setChecked(true); 629 630 // enable new sheet from file 631 scNewSheetFromFile.setChecked(true); 632 633 // check Link 634 scFromFileLink.setChecked(false); // no link 635 636 // browse 637 scFromFileBrowse.click(); 638 639 // opens Insert dialog 640 641 // add filename 642 filePickerPath.setText(SourcestoreUrl); 643 644 // Insert button 645 filePickerOpen.click(); 646 647 // back to Insert Sheet dialog 648 649 // select sheet 3 650 scFromFileSheetList.select(2); 651 652 // select Insert button 653 scInsertSheetDlg.ok(); 654 655 log.info("sheet inserted......"); 656 657 XSpreadsheet firstexternalsheet = SCUtil.getSCSheetByIndex(scDocument, 658 (short) 3); 659 XSheetLinkable xfirstSheetLinkable = (XSheetLinkable) UnoRuntime 660 .queryInterface(XSheetLinkable.class, firstexternalsheet); 661 662 assertEquals("Expect first sheet link is NONE", 663 SheetLinkMode.NONE, xfirstSheetLinkable.getLinkMode()); 664 665 // Verify thirdexternalsheet 666 assertEquals("Expect third sheet formula should be =3*2", "=3*2", 667 SCUtil.getFormulaFromCell(firstexternalsheet, 1, 2)); 668 assertEquals("Expect third sheet text should be 6", "6", 669 SCUtil.getTextFromCell(firstexternalsheet, 1, 2)); 670 671 log.info("passed first round of assertions..."); 672 log.info(""); 673 674 log.info("saving document as linked-none.ods"); 675 676 // save document and verify the linked sheet again 677 SCUtil.saveFileAs(scComponent, "linked-none", "ods"); 678 log.info("reloading linked-none.ods..."); 679 680 // this will hang on "update links" dialog with no way out - leaving here for others that follow... 681 // XSpreadsheetDocument tempscDocument = SCUtil.reloadFile(unoApp, scDocument, "linked.ods"); 682 683 // open it with VCL instead 684 vclApp.dispatch(".uno:Open"); 685 // add filename 686 filePickerPath.setText("linked-none.ods"); 687 688 // Insert button 689 filePickerOpen.click(); 690 sleep(1); 691 692 // need to get the current document as tempscdocument 693 XDesktop xDesktop = unoApp.getDesktop(); 694 // xDesktop.getCurrentComponent returns an XComponent (XModel) 695 XComponent xComponent = xDesktop.getCurrentComponent(); 696 XSpreadsheetDocument tempscDocument = (XSpreadsheetDocument)UnoRuntime.queryInterface( 697 XSpreadsheetDocument.class, xComponent); 698 699 scDocument = tempscDocument; 700 firstexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 3); 701 702 // Verify firstexternalsheet 703 assertEquals("Expect first sheet formula should be =3*2", "=3*2", 704 SCUtil.getFormulaFromCell(firstexternalsheet, 1, 2)); 705 assertEquals("Expect first sheet text should be 6", "6", 706 SCUtil.getTextFromCell(firstexternalsheet, 1, 2)); 707 708 log.info("passed second round of assertions"); 709 log.info(""); 710 711 // save and close document 712 SCUtil.save(scDocument); 713 SCUtil.closeFile(scDocument); 714 715 // Open source document and change the value in source document 716 XSpreadsheetDocument sourcescDocument = SCUtil.reloadFile(unoApp, 717 scDocument, "source.xls"); 718 XSpreadsheet thirdSheet = SCUtil.getSCSheetByIndex(sourcescDocument, (short) 2); 719 SCUtil.setFormulaToCell(thirdSheet, 1, 2, "=3*3"); 720 SCUtil.save(sourcescDocument); 721 SCUtil.closeFile(sourcescDocument); 722 723 // open test document 724 vclApp.dispatch(".uno:Open"); 725 // add filename 726 filePickerPath.setText("linked-none.ods"); 727 728 // Insert button 729 filePickerOpen.click(); 730 sleep(1); 731 732 // get the current document as tempscdocument 733 xDesktop = unoApp.getDesktop(); 734 xComponent = xDesktop.getCurrentComponent(); 735 tempscDocument = (XSpreadsheetDocument)UnoRuntime.queryInterface( 736 XSpreadsheetDocument.class, xComponent); 737 738 scDocument = tempscDocument; 739 spreadsheets = scDocument.getSheets(); 740 741 firstexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 3); 742 743 // get Object SheetLinks for document 744 XPropertySet sheetpropertyset = (XPropertySet) UnoRuntime 745 .queryInterface(XPropertySet.class, scDocument); 746 Object sheetLinks = sheetpropertyset.getPropertyValue("SheetLinks"); 747 748 XIndexAccess xsheetlinks = (XIndexAccess) UnoRuntime.queryInterface( 749 XIndexAccess.class, sheetLinks); 750 751 log.info("Number of Links should be 0"); 752 assertEquals("Number of Links should be 0", 0, xsheetlinks.getCount()); 753 754 // Verify firstexternalsheet did not change 755 assertEquals("Expect first sheet formula should be =3*2", "=3*2", 756 SCUtil.getFormulaFromCell(firstexternalsheet, 1, 2)); 757 assertEquals("Expect first sheet text should be 6", "6", 758 SCUtil.getTextFromCell(firstexternalsheet, 1, 2)); 759 760 // Save the document before close 761 SCUtil.save(scDocument); 762 763 } 764 765 } // end class