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