1916729d0Smseidel /************************************************************** 2916729d0Smseidel * 3916729d0Smseidel * Licensed to the Apache Software Foundation (ASF) under one 421f666c7SLiu Zhe * or more contributor license agreements. See the NOTICE file 521f666c7SLiu Zhe * distributed with this work for additional information 621f666c7SLiu Zhe * regarding copyright ownership. The ASF licenses this file 721f666c7SLiu Zhe * to you under the Apache License, Version 2.0 (the 821f666c7SLiu Zhe * "License"); you may not use this file except in compliance 921f666c7SLiu Zhe * with the License. You may obtain a copy of the License at 10916729d0Smseidel * 1121f666c7SLiu Zhe * http://www.apache.org/licenses/LICENSE-2.0 12916729d0Smseidel * 1321f666c7SLiu Zhe * Unless required by applicable law or agreed to in writing, 1421f666c7SLiu Zhe * software distributed under the License is distributed on an 1521f666c7SLiu Zhe * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY 1621f666c7SLiu Zhe * KIND, either express or implied. See the License for the 1721f666c7SLiu Zhe * specific language governing permissions and limitations 1821f666c7SLiu Zhe * under the License. 19916729d0Smseidel * 2021f666c7SLiu Zhe *************************************************************/ 2121f666c7SLiu Zhe 22eba4d44aSLiu Zhe package fvt.uno.sc.sheet; 2321f666c7SLiu Zhe 2421f666c7SLiu Zhe import static org.junit.Assert.*; 2521f666c7SLiu Zhe import org.junit.After; 2621f666c7SLiu Zhe import org.junit.AfterClass; 2721f666c7SLiu Zhe import org.junit.Before; 2821f666c7SLiu Zhe import org.junit.BeforeClass; 29*47b4b2ceSCarl Marcum import org.junit.Ignore; 3021f666c7SLiu Zhe import org.junit.Test; 3121f666c7SLiu Zhe import org.openoffice.test.common.Testspace; 3221f666c7SLiu Zhe import org.openoffice.test.uno.UnoApp; 33d01630b6SLiu Zhe import testlib.uno.SCUtil; 34d01630b6SLiu Zhe import com.sun.star.beans.XPropertySet; 3521f666c7SLiu Zhe import com.sun.star.container.XIndexAccess; 3621f666c7SLiu Zhe import com.sun.star.lang.XComponent; 37d01630b6SLiu Zhe import com.sun.star.sheet.SheetLinkMode; 38d01630b6SLiu Zhe import com.sun.star.sheet.XSheetLinkable; 3921f666c7SLiu Zhe import com.sun.star.sheet.XSpreadsheet; 4021f666c7SLiu Zhe import com.sun.star.sheet.XSpreadsheetDocument; 4121f666c7SLiu Zhe import com.sun.star.sheet.XSpreadsheets; 4221f666c7SLiu Zhe import com.sun.star.uno.UnoRuntime; 43d01630b6SLiu Zhe import com.sun.star.util.XRefreshable; 4421f666c7SLiu Zhe 45cfe4bce3SLi Feng Wang /** 46cfe4bce3SLi Feng Wang * Basic sheet operator testing 47cfe4bce3SLi Feng Wang * 48cfe4bce3SLi Feng Wang */ 4921f666c7SLiu Zhe public class SheetBasicTest { 5021f666c7SLiu Zhe UnoApp unoApp = new UnoApp(); 5121f666c7SLiu Zhe XSpreadsheetDocument scDocument = null; 5221f666c7SLiu Zhe XComponent scComponent = null; 5321f666c7SLiu Zhe 5421f666c7SLiu Zhe @BeforeClass setUpBeforeClass()5521f666c7SLiu Zhe public static void setUpBeforeClass() throws Exception { 5621f666c7SLiu Zhe 5721f666c7SLiu Zhe } 5821f666c7SLiu Zhe 5921f666c7SLiu Zhe @AfterClass tearDownAfterClass()6021f666c7SLiu Zhe public static void tearDownAfterClass() throws Exception { 6121f666c7SLiu Zhe } 6221f666c7SLiu Zhe 6321f666c7SLiu Zhe @Before setUp()6421f666c7SLiu Zhe public void setUp() throws Exception { 6521f666c7SLiu Zhe unoApp.start(); 6621f666c7SLiu Zhe // New a SC document 6721f666c7SLiu Zhe scComponent = unoApp.newDocument("scalc"); 6821f666c7SLiu Zhe } 6921f666c7SLiu Zhe 7021f666c7SLiu Zhe @After tearDown()7121f666c7SLiu Zhe public void tearDown() throws Exception { 7221f666c7SLiu Zhe unoApp.closeDocument(scComponent); 7321f666c7SLiu Zhe unoApp.close(); 7421f666c7SLiu Zhe } 7521f666c7SLiu Zhe 76cfe4bce3SLi Feng Wang /** 77cfe4bce3SLi Feng Wang * test insert a sheet, rename sheet name and delete sheet 78cfe4bce3SLi Feng Wang */ 7921f666c7SLiu Zhe @Test insertRenameDeleteSheet()8021f666c7SLiu Zhe public void insertRenameDeleteSheet() throws Exception { 8121f666c7SLiu Zhe // Insert a sheet named aa after first sheet 8221f666c7SLiu Zhe String sheetname = "aa"; 83d01630b6SLiu Zhe scDocument = SCUtil.getSCDocument(scComponent); 8421f666c7SLiu Zhe XSpreadsheets spreadsheets = scDocument.getSheets(); 8521f666c7SLiu Zhe spreadsheets.insertNewByName(sheetname, (short) 1); 8621f666c7SLiu Zhe 8721f666c7SLiu Zhe // active the sheet second sheet aa 88d01630b6SLiu Zhe XSpreadsheet newSpreadSheet = SCUtil.getSCSheetByIndex(scDocument, 89d01630b6SLiu Zhe (short) 1); 90d01630b6SLiu Zhe SCUtil.setCurrentSheet(scDocument, newSpreadSheet); 9121f666c7SLiu Zhe 92916729d0Smseidel // get the new spreadsheet name 93d01630b6SLiu Zhe assertEquals("actual should equals aa", sheetname, 94d01630b6SLiu Zhe SCUtil.getSCSheetNameByIndex(scDocument, (short) 1)); 9521f666c7SLiu Zhe 9621f666c7SLiu Zhe // Change the Spreadsheet name 9721f666c7SLiu Zhe String changedname = "SpeadsheetAfterChange"; 98d01630b6SLiu Zhe SCUtil.setSCSheetNameByIndex(scDocument, (short) 1, changedname); 9921f666c7SLiu Zhe 10021f666c7SLiu Zhe // Save and reload document 101d01630b6SLiu Zhe SCUtil.saveFileAs(scComponent, "TestSpreadsheet", "ods"); 102d01630b6SLiu Zhe XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp, 103d01630b6SLiu Zhe scDocument, "TestSpreadsheet.ods"); 104d01630b6SLiu Zhe 105d01630b6SLiu Zhe scDocument = scDocumentTemp; 106d01630b6SLiu Zhe String sheetnameaftermove = SCUtil.getSCSheetNameByIndex(scDocument, 107d01630b6SLiu Zhe (short) 1); 10821f666c7SLiu Zhe 10921f666c7SLiu Zhe // Verify the changed Spreadsheet name 11021f666c7SLiu Zhe assertEquals("actual should equals SpeadsheetAfterChange", changedname, 111d01630b6SLiu Zhe sheetnameaftermove); 11221f666c7SLiu Zhe 113d01630b6SLiu Zhe scDocument.getSheets().removeByName(changedname); 11421f666c7SLiu Zhe 11521f666c7SLiu Zhe assertFalse("actual should equals false", 11621f666c7SLiu Zhe spreadsheets.hasByName(changedname)); 117d01630b6SLiu Zhe SCUtil.save(scDocumentTemp); 11821f666c7SLiu Zhe } 11921f666c7SLiu Zhe 120cfe4bce3SLi Feng Wang /** 121916729d0Smseidel * Test copy and paste sheet 122cfe4bce3SLi Feng Wang */ 12321f666c7SLiu Zhe @Test copypastesheet()12421f666c7SLiu Zhe public void copypastesheet() throws Exception { 12521f666c7SLiu Zhe // Insert some value into cells 126d01630b6SLiu Zhe scDocument = SCUtil.getSCDocument(scComponent); 127d01630b6SLiu Zhe String souceSheetName = "sourcesheet"; 128d01630b6SLiu Zhe SCUtil.setSCSheetNameByIndex(scDocument, (short) 0, souceSheetName); 12921f666c7SLiu Zhe String[][] stringValues = { { "", "Jan", "Feb", "Mar", "Apr", "Mai" }, 13021f666c7SLiu Zhe { "Profit", "12.3", "43.2", "5.1", "76", "56.8" }, 13121f666c7SLiu Zhe { "Rival in business", "12.2", "12.6", "17.7", "20.4", "100" }, }; 132d01630b6SLiu Zhe XSpreadsheet sourceSpreadSheet = SCUtil.getSCSheetByName(scDocument, 133d01630b6SLiu Zhe souceSheetName); 13421f666c7SLiu Zhe // input strings into sheet1 135cfe4bce3SLi Feng Wang SCUtil.setTextToCellRange(sourceSpreadSheet, 0, 0, stringValues); 13621f666c7SLiu Zhe // copy the sheet from sourcesheet to copysheet 13721f666c7SLiu Zhe String newcopysheet = "copysheet"; 138d01630b6SLiu Zhe XSpreadsheets spreadsheets = scDocument.getSheets(); 139d01630b6SLiu Zhe spreadsheets.copyByName(souceSheetName, newcopysheet, (short) 2); 14021f666c7SLiu Zhe 14121f666c7SLiu Zhe // Save and reload document 142d01630b6SLiu Zhe SCUtil.saveFileAs(scComponent, "TestCopysheet", "xls"); 143d01630b6SLiu Zhe XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp, 144d01630b6SLiu Zhe scDocument, "TestCopysheet.xls"); 145d01630b6SLiu Zhe scDocument = scDocumentTemp; 146d01630b6SLiu Zhe 147d01630b6SLiu Zhe XSpreadsheet copysheet = SCUtil 148d01630b6SLiu Zhe .getSCSheetByIndex(scDocument, (short) 2); 149d01630b6SLiu Zhe String[][] CopystringValues = SCUtil.getTextFromCellRange(copysheet, 0, 150d01630b6SLiu Zhe 0, 5, 2); 151d01630b6SLiu Zhe assertArrayEquals("Expect string value should be stringValues", 152d01630b6SLiu Zhe stringValues, CopystringValues); 15321f666c7SLiu Zhe 15421f666c7SLiu Zhe } 15521f666c7SLiu Zhe 156cfe4bce3SLi Feng Wang /** 157cfe4bce3SLi Feng Wang * Test move sheet 158cfe4bce3SLi Feng Wang */ 15921f666c7SLiu Zhe @Test movesheet()16021f666c7SLiu Zhe public void movesheet() throws Exception { 16121f666c7SLiu Zhe 16221f666c7SLiu Zhe // new sc document 163d01630b6SLiu Zhe scDocument = SCUtil.getSCDocument(scComponent); 164d01630b6SLiu Zhe XSpreadsheets spreadsheets = scDocument.getSheets(); 16521f666c7SLiu Zhe 16621f666c7SLiu Zhe // change the first sheet name and input same value into the sheet cell 16721f666c7SLiu Zhe String sheetname = "sourcesheet"; 168d01630b6SLiu Zhe SCUtil.setSCSheetNameByIndex(scDocument, (short) 0, sheetname); 16921f666c7SLiu Zhe String[][] stringValues = { { "", "Jan", "Feb", "Mar", "Apr", "Mai" }, 17021f666c7SLiu Zhe { "Profit", "12.3", "43.2", "5.1", "76", "56.8" }, 17121f666c7SLiu Zhe { "Rival in business", "12.2", "12.6", "17.7", "20.4", "100" }, }; 172d01630b6SLiu Zhe XSpreadsheet movesheet = SCUtil 173d01630b6SLiu Zhe .getSCSheetByIndex(scDocument, (short) 0); 174cfe4bce3SLi Feng Wang SCUtil.setTextToCellRange(movesheet, 0, 0,stringValues); 17521f666c7SLiu Zhe 17621f666c7SLiu Zhe // Before move, get the 2nd sheet name 177d01630b6SLiu Zhe String secondSheetNameBeforeMove = SCUtil.getSCSheetNameByIndex( 178d01630b6SLiu Zhe scDocument, (short) 1); 17921f666c7SLiu Zhe 18021f666c7SLiu Zhe // move the first sheet 18121f666c7SLiu Zhe spreadsheets.moveByName(sheetname, (short) 2); 18221f666c7SLiu Zhe 18321f666c7SLiu Zhe // Save and reload document 184d01630b6SLiu Zhe SCUtil.saveFileAs(scComponent, "Testmovesheet", "xls"); 185d01630b6SLiu Zhe XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp, 186d01630b6SLiu Zhe scDocument, "Testmovesheet.xls"); 187d01630b6SLiu Zhe scDocument = scDocumentTemp; 18821f666c7SLiu Zhe 18921f666c7SLiu Zhe // After move, get the first sheet name, and verify it same as 2nd sheet 19021f666c7SLiu Zhe // name before move 191d01630b6SLiu Zhe String firstsheetnameAfterMove = SCUtil.getSCSheetNameByIndex( 192d01630b6SLiu Zhe scDocument, (short) 0); 19321f666c7SLiu Zhe assertEquals("Expect result should be Sheet2", 194d01630b6SLiu Zhe secondSheetNameBeforeMove, firstsheetnameAfterMove); 19521f666c7SLiu Zhe 19621f666c7SLiu Zhe // Get the target sheet name after move 197d01630b6SLiu Zhe String sheetnameAfterMove = SCUtil.getSCSheetNameByIndex(scDocument, 198d01630b6SLiu Zhe (short) 1); 19921f666c7SLiu Zhe assertEquals("Expect result should be sourcesheet", sheetname, 200d01630b6SLiu Zhe sheetnameAfterMove); 20121f666c7SLiu Zhe 20221f666c7SLiu Zhe // Check the cell value after move 203d01630b6SLiu Zhe XSpreadsheet sheetaftermove = SCUtil.getSCSheetByIndex(scDocument, 204d01630b6SLiu Zhe (short) 1); 205d01630b6SLiu Zhe String[][] stringValuesaftermove = SCUtil.getTextFromCellRange( 206d01630b6SLiu Zhe sheetaftermove, 0, 0, 5, 2); 207d01630b6SLiu Zhe 208d01630b6SLiu Zhe assertArrayEquals("Expect result should be stringValues", stringValues, 209d01630b6SLiu Zhe stringValuesaftermove); 21021f666c7SLiu Zhe } 21121f666c7SLiu Zhe 212cfe4bce3SLi Feng Wang /** 213cfe4bce3SLi Feng Wang * Test hide and show sheet 214cfe4bce3SLi Feng Wang */ 215d01630b6SLiu Zhe @Test hideShowSheet()216d01630b6SLiu Zhe public void hideShowSheet() throws Exception { 217d01630b6SLiu Zhe // Insert a sheet named hide sheet after first sheet 218d01630b6SLiu Zhe String sheetname = "hide sheet"; 219d01630b6SLiu Zhe scDocument = SCUtil.getSCDocument(scComponent); 220d01630b6SLiu Zhe XSpreadsheets spreadsheets = scDocument.getSheets(); 221d01630b6SLiu Zhe spreadsheets.insertNewByName(sheetname, (short) 1); 222d01630b6SLiu Zhe 223d01630b6SLiu Zhe // active the sheet second sheet "hide sheet" 224d01630b6SLiu Zhe XSpreadsheet secondSpreadSheet = SCUtil.getSCSheetByIndex(scDocument, 225d01630b6SLiu Zhe (short) 1); 226d01630b6SLiu Zhe SCUtil.setCurrentSheet(scDocument, secondSpreadSheet); 227d01630b6SLiu Zhe // get second sheet name and verify it should be "hide sheet" 228d01630b6SLiu Zhe assertEquals("expect active sheet name will be hide sheet", sheetname, 229d01630b6SLiu Zhe SCUtil.getSCSheetNameByIndex(scDocument, (short) 1)); 230d01630b6SLiu Zhe 231d01630b6SLiu Zhe // hide the sheet you insert 232d01630b6SLiu Zhe XPropertySet sheetPropertySet = (XPropertySet) UnoRuntime 233d01630b6SLiu Zhe .queryInterface(XPropertySet.class, secondSpreadSheet); 234d01630b6SLiu Zhe boolean isvisiable = false; 235d01630b6SLiu Zhe sheetPropertySet.setPropertyValue("IsVisible", isvisiable); 236d01630b6SLiu Zhe 237d01630b6SLiu Zhe // Save and reload document 238d01630b6SLiu Zhe SCUtil.saveFileAs(scComponent, "Testhideshowsheet", "xls"); 239d01630b6SLiu Zhe XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp, 240d01630b6SLiu Zhe scDocument, "Testhideshowsheet.xls"); 241d01630b6SLiu Zhe scDocument = scDocumentTemp; 242d01630b6SLiu Zhe 243d01630b6SLiu Zhe // get the active sheet name after hide sheet, it should be Sheet2 244d01630b6SLiu Zhe String sheet2Name = SCUtil.getSCSheetNameByIndex(scDocument, (short) 2); 245d01630b6SLiu Zhe String activesheetname = SCUtil.getSCActiveSheetName(scDocument); 246d01630b6SLiu Zhe assertEquals("Expect sheet name should be Sheet2", sheet2Name, 247d01630b6SLiu Zhe activesheetname); 248d01630b6SLiu Zhe 249d01630b6SLiu Zhe // show sheet "hide sheet" 250d01630b6SLiu Zhe sheetPropertySet = (XPropertySet) UnoRuntime.queryInterface( 251d01630b6SLiu Zhe XPropertySet.class, 252d01630b6SLiu Zhe SCUtil.getSCSheetByIndex(scDocument, (short) 1)); 253d01630b6SLiu Zhe isvisiable = true; 254d01630b6SLiu Zhe sheetPropertySet.setPropertyValue("IsVisible", isvisiable); 255d01630b6SLiu Zhe 256d01630b6SLiu Zhe // active sheet "hide sheet" 257d01630b6SLiu Zhe secondSpreadSheet = SCUtil.getSCSheetByIndex(scDocument, (short) 1); 258d01630b6SLiu Zhe SCUtil.setCurrentSheet(scDocument, secondSpreadSheet); 259d01630b6SLiu Zhe 260d01630b6SLiu Zhe // Get current active sheet name, verify it same as "hide sheet" 261d01630b6SLiu Zhe String currentactivesheetname = SCUtil.getSCActiveSheetName(scDocument); 262d01630b6SLiu Zhe assertEquals("Expect active sheet name is hidesheet", sheetname, 263d01630b6SLiu Zhe currentactivesheetname); 264d01630b6SLiu Zhe SCUtil.save(scDocument); 265d01630b6SLiu Zhe } 266d01630b6SLiu Zhe 267cfe4bce3SLi Feng Wang /** 268cfe4bce3SLi Feng Wang * Test sheet tab color 269cfe4bce3SLi Feng Wang */ 270d01630b6SLiu Zhe @Test sheetColor()271d01630b6SLiu Zhe public void sheetColor() throws Exception { 272d01630b6SLiu Zhe // get first sheet propertyset 273d01630b6SLiu Zhe scDocument = SCUtil.getSCDocument(scComponent); 274d01630b6SLiu Zhe XSpreadsheets spreadsheets = scDocument.getSheets(); 275d01630b6SLiu Zhe XSpreadsheet firstSpreadSheet = SCUtil.getSCSheetByIndex(scDocument, 276d01630b6SLiu Zhe (short) 0); 277d01630b6SLiu Zhe XPropertySet sheet1PropertySet = (XPropertySet) UnoRuntime 278d01630b6SLiu Zhe .queryInterface(XPropertySet.class, firstSpreadSheet); 279d01630b6SLiu Zhe 280cfe4bce3SLi Feng Wang // Set sheet tab color to 111 281d01630b6SLiu Zhe sheet1PropertySet.setPropertyValue("TabColor", 111); 282d01630b6SLiu Zhe 283d01630b6SLiu Zhe // copy the color sheet to new sheet 284d01630b6SLiu Zhe spreadsheets.copyByName( 285d01630b6SLiu Zhe SCUtil.getSCSheetNameByIndex(scDocument, (short) 0), 286d01630b6SLiu Zhe "newsheet", (short) 3); 287d01630b6SLiu Zhe 288d01630b6SLiu Zhe // Save and reopen the document 289d01630b6SLiu Zhe SCUtil.saveFileAs(scComponent, "Testcolorsheet", "ods"); 290d01630b6SLiu Zhe XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp, 291d01630b6SLiu Zhe scDocument, "Testcolorsheet.ods"); 292d01630b6SLiu Zhe scDocument = scDocumentTemp; 293d01630b6SLiu Zhe 294d01630b6SLiu Zhe // Get first sheet color 295d01630b6SLiu Zhe sheet1PropertySet = (XPropertySet) UnoRuntime.queryInterface( 296d01630b6SLiu Zhe XPropertySet.class, 297d01630b6SLiu Zhe SCUtil.getSCSheetByIndex(scDocument, (short) 0)); 298d01630b6SLiu Zhe int firstSheetcolorid = (Integer) sheet1PropertySet 299d01630b6SLiu Zhe .getPropertyValue("TabColor"); 300d01630b6SLiu Zhe 301916729d0Smseidel // Get the copied sheet color 302d01630b6SLiu Zhe XPropertySet newsheetPropertySet = (XPropertySet) UnoRuntime 303d01630b6SLiu Zhe .queryInterface(XPropertySet.class, 304d01630b6SLiu Zhe SCUtil.getSCSheetByIndex(scDocument, (short) 3)); 305d01630b6SLiu Zhe int copySheetcolorid = (Integer) newsheetPropertySet 306d01630b6SLiu Zhe .getPropertyValue("TabColor"); 307d01630b6SLiu Zhe 308d01630b6SLiu Zhe // Verify first sheet color changed successfully 309d01630b6SLiu Zhe assertEquals("Expect color should be 111", 111, firstSheetcolorid); 310d01630b6SLiu Zhe 311d01630b6SLiu Zhe // Verify first sheet color same as copy sheet color 312d01630b6SLiu Zhe assertEquals("Expect color should be 111", firstSheetcolorid, 313d01630b6SLiu Zhe copySheetcolorid); 31421f666c7SLiu Zhe } 31521f666c7SLiu Zhe 316cfe4bce3SLi Feng Wang /** 317cfe4bce3SLi Feng Wang * test insert sheet from other file 318cfe4bce3SLi Feng Wang */ 319*47b4b2ceSCarl Marcum @Ignore("Bug #128536 locks up on update link confirmation dialog. replaced by InsertExcelSheetTest class.") 320b554e61dSDamjan Jovanovic @Test(timeout = 15000) insertSheetFromfile()321d01630b6SLiu Zhe public void insertSheetFromfile() throws Exception { 322d01630b6SLiu Zhe // New a document source.xls, add value to 3 sheet 323d01630b6SLiu Zhe scDocument = SCUtil.getSCDocument(scComponent); 324d01630b6SLiu Zhe XSpreadsheets spreadsheets = scDocument.getSheets(); 325d01630b6SLiu Zhe XSpreadsheet firstSheet = SCUtil.getSCSheetByIndex(scDocument, 326d01630b6SLiu Zhe (short) 0); 327d01630b6SLiu Zhe XSpreadsheet secondSheet = SCUtil.getSCSheetByIndex(scDocument, 328d01630b6SLiu Zhe (short) 1); 329d01630b6SLiu Zhe XSpreadsheet thirdSheet = SCUtil.getSCSheetByIndex(scDocument, 330d01630b6SLiu Zhe (short) 2); 331d01630b6SLiu Zhe SCUtil.setFormulaToCell(firstSheet, 1, 2, "=2*2"); 332d01630b6SLiu Zhe SCUtil.setFormulaToCell(secondSheet, 1, 2, "=2*2"); 333d01630b6SLiu Zhe SCUtil.setFormulaToCell(thirdSheet, 1, 2, "=2*2"); 334d01630b6SLiu Zhe 335d01630b6SLiu Zhe // Save and close this document 336d01630b6SLiu Zhe SCUtil.saveFileAs(scComponent, "source", "xls"); 337d01630b6SLiu Zhe SCUtil.closeFile(scDocument); 338d01630b6SLiu Zhe 339d01630b6SLiu Zhe // get source document URL 340dded528dSLiu Zhe String SourcestoreUrl = Testspace.getUrl("output/sc/" + "source" + "." 341d01630b6SLiu Zhe + "xls"); 342d01630b6SLiu Zhe 343d01630b6SLiu Zhe // New a document 344d01630b6SLiu Zhe scComponent = unoApp.newDocument("scalc"); 345d01630b6SLiu Zhe scDocument = SCUtil.getSCDocument(scComponent); 346d01630b6SLiu Zhe spreadsheets = scDocument.getSheets(); 347d01630b6SLiu Zhe // Insert firstexternalsheet sheet, link with Sheet1 in source document 348d01630b6SLiu Zhe // and the link mode is NORMAL 349d01630b6SLiu Zhe spreadsheets.insertNewByName("firstexternalsheet", (short) 3); 350d01630b6SLiu Zhe XSpreadsheet firstexternalsheet = SCUtil.getSCSheetByIndex(scDocument, 351d01630b6SLiu Zhe (short) 3); 352d01630b6SLiu Zhe XSheetLinkable xfirstSheetLinkable = (XSheetLinkable) UnoRuntime 353d01630b6SLiu Zhe .queryInterface(XSheetLinkable.class, firstexternalsheet); 354d01630b6SLiu Zhe xfirstSheetLinkable.link(SourcestoreUrl, "", "MS Excel 97", "", 355d01630b6SLiu Zhe SheetLinkMode.NORMAL); 356d01630b6SLiu Zhe 357d01630b6SLiu Zhe // Insert secondexternalsheet sheet, link with Sheet2 in source document 358d01630b6SLiu Zhe // and the link mode is VALUE 359d01630b6SLiu Zhe spreadsheets.insertNewByName("secondexternalsheet", (short) 4); 360d01630b6SLiu Zhe XSpreadsheet secondexternalsheet = SCUtil.getSCSheetByIndex(scDocument, 361d01630b6SLiu Zhe (short) 4); 362d01630b6SLiu Zhe XSheetLinkable xsecondSheetLinkable = (XSheetLinkable) UnoRuntime 363d01630b6SLiu Zhe .queryInterface(XSheetLinkable.class, secondexternalsheet); 364d01630b6SLiu Zhe xsecondSheetLinkable.link(SourcestoreUrl, "Sheet2", "MS Excel 97", "", 365d01630b6SLiu Zhe SheetLinkMode.VALUE); 366d01630b6SLiu Zhe 367d01630b6SLiu Zhe // Insert secondexternalsheet sheet, link with Sheet2 in source document 368d01630b6SLiu Zhe // and the link mode is NONE 369d01630b6SLiu Zhe spreadsheets.insertNewByName("thirdexternalsheet", (short) 5); 370d01630b6SLiu Zhe XSpreadsheet thirdexternalsheet = SCUtil.getSCSheetByIndex(scDocument, 371d01630b6SLiu Zhe (short) 5); 372d01630b6SLiu Zhe XSheetLinkable xthirdSheetLinkable = (XSheetLinkable) UnoRuntime 373d01630b6SLiu Zhe .queryInterface(XSheetLinkable.class, thirdexternalsheet); 374d01630b6SLiu Zhe xthirdSheetLinkable.link(SourcestoreUrl, "Sheet3", "MS Excel 97", "", 375d01630b6SLiu Zhe SheetLinkMode.NONE); 376d01630b6SLiu Zhe 377d01630b6SLiu Zhe // Verify firstexternalsheet 378d01630b6SLiu Zhe assertEquals("Expect formula should be =2*2", "=2*2", 379d01630b6SLiu Zhe SCUtil.getFormulaFromCell(firstexternalsheet, 1, 2)); 380d01630b6SLiu Zhe assertEquals("Expect formula result should be 4", "4", 381d01630b6SLiu Zhe SCUtil.getTextFromCell(firstexternalsheet, 1, 2)); 382d01630b6SLiu Zhe 383d01630b6SLiu Zhe // Verify secondexternalsheet 384d01630b6SLiu Zhe assertEquals("Expect formula should be 4", "4", 385d01630b6SLiu Zhe SCUtil.getFormulaFromCell(secondexternalsheet, 1, 2)); 386d01630b6SLiu Zhe assertEquals("Expect formula result should be 4", "4", 387d01630b6SLiu Zhe SCUtil.getTextFromCell(secondexternalsheet, 1, 2)); 388d01630b6SLiu Zhe 389d01630b6SLiu Zhe // Verify thirdexternalsheet 390d01630b6SLiu Zhe assertEquals("Expect formula should be blank", "", 391d01630b6SLiu Zhe SCUtil.getFormulaFromCell(thirdexternalsheet, 1, 2)); 392d01630b6SLiu Zhe assertEquals("Expect formula result should be blank", "", 393d01630b6SLiu Zhe SCUtil.getTextFromCell(thirdexternalsheet, 1, 2)); 394d01630b6SLiu Zhe 395d01630b6SLiu Zhe // save document and verify the linked sheet again 396d01630b6SLiu Zhe SCUtil.saveFileAs(scComponent, "linked", "ods"); 397d01630b6SLiu Zhe XSpreadsheetDocument tempscDocument = SCUtil.reloadFile(unoApp, 398d01630b6SLiu Zhe scDocument, "linked.ods"); 399d01630b6SLiu Zhe scDocument = tempscDocument; 400d01630b6SLiu Zhe firstexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 3); 401d01630b6SLiu Zhe secondexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 4); 402d01630b6SLiu Zhe thirdexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 5); 403d01630b6SLiu Zhe 404d01630b6SLiu Zhe // Verify firstexternalsheet 405d01630b6SLiu Zhe assertEquals("Expect formula should be =2*2", "=2*2", 406d01630b6SLiu Zhe SCUtil.getFormulaFromCell(firstexternalsheet, 1, 2)); 407d01630b6SLiu Zhe assertEquals("Expect formula result should be 4", "4", 408d01630b6SLiu Zhe SCUtil.getTextFromCell(firstexternalsheet, 1, 2)); 409d01630b6SLiu Zhe 410d01630b6SLiu Zhe // Verify secondexternalsheet 411d01630b6SLiu Zhe assertEquals("Expect formula should be 4", "4", 412d01630b6SLiu Zhe SCUtil.getFormulaFromCell(secondexternalsheet, 1, 2)); 413d01630b6SLiu Zhe assertEquals("Expect formula result should be 4", "4", 414d01630b6SLiu Zhe SCUtil.getTextFromCell(secondexternalsheet, 1, 2)); 415d01630b6SLiu Zhe 416d01630b6SLiu Zhe // Verify thirdexternalsheet 417d01630b6SLiu Zhe assertEquals("Expect formula should be blank", "", 418d01630b6SLiu Zhe SCUtil.getFormulaFromCell(thirdexternalsheet, 1, 2)); 419d01630b6SLiu Zhe assertEquals("Expect formula result should be blank", "", 420d01630b6SLiu Zhe SCUtil.getTextFromCell(thirdexternalsheet, 1, 2)); 421d01630b6SLiu Zhe 422916729d0Smseidel // save and close document 423d01630b6SLiu Zhe SCUtil.save(scDocument); 424d01630b6SLiu Zhe SCUtil.closeFile(scDocument); 425d01630b6SLiu Zhe 426916729d0Smseidel // Open source document and change the value in source document 427d01630b6SLiu Zhe XSpreadsheetDocument sourcescDocument = SCUtil.reloadFile(unoApp, 428d01630b6SLiu Zhe scDocument, "source.xls"); 429d01630b6SLiu Zhe firstSheet = SCUtil.getSCSheetByIndex(sourcescDocument, (short) 0); 430d01630b6SLiu Zhe secondSheet = SCUtil.getSCSheetByIndex(sourcescDocument, (short) 1); 431d01630b6SLiu Zhe thirdSheet = SCUtil.getSCSheetByIndex(sourcescDocument, (short) 2); 432d01630b6SLiu Zhe SCUtil.setFormulaToCell(firstSheet, 1, 2, "=3*3"); 433d01630b6SLiu Zhe SCUtil.setFormulaToCell(secondSheet, 1, 2, "=3*3"); 434d01630b6SLiu Zhe SCUtil.setFormulaToCell(thirdSheet, 1, 2, "=3*3"); 435d01630b6SLiu Zhe SCUtil.save(sourcescDocument); 436d01630b6SLiu Zhe SCUtil.closeFile(sourcescDocument); 437d01630b6SLiu Zhe 438916729d0Smseidel // Open link document 439d01630b6SLiu Zhe tempscDocument = SCUtil.reloadFile(unoApp, scDocument, "linked.ods"); 440d01630b6SLiu Zhe scDocument = tempscDocument; 441d01630b6SLiu Zhe spreadsheets = scDocument.getSheets(); 442d01630b6SLiu Zhe 443d01630b6SLiu Zhe firstexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 3); 444d01630b6SLiu Zhe secondexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 4); 445d01630b6SLiu Zhe thirdexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 5); 446d01630b6SLiu Zhe 447916729d0Smseidel // get Object SheetLinks for document 448d01630b6SLiu Zhe XPropertySet sheetpropertyset = (XPropertySet) UnoRuntime 449d01630b6SLiu Zhe .queryInterface(XPropertySet.class, scDocument); 450d01630b6SLiu Zhe Object sheetLinks = sheetpropertyset.getPropertyValue("SheetLinks"); 451d01630b6SLiu Zhe 452d01630b6SLiu Zhe XIndexAccess xsheetlinks = (XIndexAccess) UnoRuntime.queryInterface( 453d01630b6SLiu Zhe XIndexAccess.class, sheetLinks); 454d01630b6SLiu Zhe 455916729d0Smseidel // Refresh all links 456d01630b6SLiu Zhe for (int i = 0; i < xsheetlinks.getCount(); i++) { 457d01630b6SLiu Zhe Object sheetlink = xsheetlinks.getByIndex(i); 458d01630b6SLiu Zhe XRefreshable xsheetRefreshable = (XRefreshable) UnoRuntime 459d01630b6SLiu Zhe .queryInterface(XRefreshable.class, sheetlink); 460d01630b6SLiu Zhe xsheetRefreshable.refresh(); 461d01630b6SLiu Zhe } 462d01630b6SLiu Zhe 463d01630b6SLiu Zhe // Verify firstexternalsheet 464d01630b6SLiu Zhe assertEquals("Expect formula should be =3*3", "=3*3", 465d01630b6SLiu Zhe SCUtil.getFormulaFromCell(firstexternalsheet, 1, 2)); 466d01630b6SLiu Zhe assertEquals("Expect formula result should be 9", "9", 467d01630b6SLiu Zhe SCUtil.getTextFromCell(firstexternalsheet, 1, 2)); 468d01630b6SLiu Zhe 469d01630b6SLiu Zhe // Verify secondexternalsheet 470d01630b6SLiu Zhe assertEquals("Expect formula should be 9", "9", 471d01630b6SLiu Zhe SCUtil.getFormulaFromCell(secondexternalsheet, 1, 2)); 472d01630b6SLiu Zhe assertEquals("Expect formula result should be 9", "9", 473d01630b6SLiu Zhe SCUtil.getTextFromCell(secondexternalsheet, 1, 2)); 474d01630b6SLiu Zhe 475d01630b6SLiu Zhe // Verify thirdexternalsheet 476d01630b6SLiu Zhe assertEquals("Expect formula should be blank", "", 477d01630b6SLiu Zhe SCUtil.getFormulaFromCell(thirdexternalsheet, 1, 2)); 478d01630b6SLiu Zhe assertEquals("Expect formula result should be blank", "", 479d01630b6SLiu Zhe SCUtil.getTextFromCell(thirdexternalsheet, 1, 2)); 480d01630b6SLiu Zhe 481916729d0Smseidel // Save the document before close 482d01630b6SLiu Zhe SCUtil.save(scDocument); 483d01630b6SLiu Zhe 48421f666c7SLiu Zhe } 485d01630b6SLiu Zhe 48621f666c7SLiu Zhe } 487