121f666c7SLiu Zhe /* Licensed to the Apache Software Foundation (ASF) under one 221f666c7SLiu Zhe * or more contributor license agreements. See the NOTICE file 321f666c7SLiu Zhe * distributed with this work for additional information 421f666c7SLiu Zhe * regarding copyright ownership. The ASF licenses this file 521f666c7SLiu Zhe * to you under the Apache License, Version 2.0 (the 621f666c7SLiu Zhe * "License"); you may not use this file except in compliance 721f666c7SLiu Zhe * with the License. You may obtain a copy of the License at 821f666c7SLiu Zhe * 921f666c7SLiu Zhe * http://www.apache.org/licenses/LICENSE-2.0 1021f666c7SLiu Zhe * 1121f666c7SLiu Zhe * Unless required by applicable law or agreed to in writing, 1221f666c7SLiu Zhe * software distributed under the License is distributed on an 1321f666c7SLiu Zhe * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY 1421f666c7SLiu Zhe * KIND, either express or implied. See the License for the 1521f666c7SLiu Zhe * specific language governing permissions and limitations 1621f666c7SLiu Zhe * under the License. 1721f666c7SLiu Zhe * 1821f666c7SLiu Zhe *************************************************************/ 1921f666c7SLiu Zhe 20eba4d44aSLiu Zhe package fvt.uno.sc.sheet; 2121f666c7SLiu Zhe 2221f666c7SLiu Zhe import static org.junit.Assert.*; 2321f666c7SLiu Zhe import org.junit.After; 2421f666c7SLiu Zhe import org.junit.AfterClass; 2521f666c7SLiu Zhe import org.junit.Before; 2621f666c7SLiu Zhe import org.junit.BeforeClass; 2721f666c7SLiu Zhe import org.junit.Test; 2821f666c7SLiu Zhe import org.openoffice.test.common.Testspace; 2921f666c7SLiu Zhe import org.openoffice.test.uno.UnoApp; 30d01630b6SLiu Zhe import testlib.uno.SCUtil; 31d01630b6SLiu Zhe import com.sun.star.beans.XPropertySet; 3221f666c7SLiu Zhe import com.sun.star.container.XIndexAccess; 3321f666c7SLiu Zhe import com.sun.star.lang.XComponent; 34d01630b6SLiu Zhe import com.sun.star.sheet.SheetLinkMode; 35d01630b6SLiu Zhe import com.sun.star.sheet.XSheetLinkable; 3621f666c7SLiu Zhe import com.sun.star.sheet.XSpreadsheet; 3721f666c7SLiu Zhe import com.sun.star.sheet.XSpreadsheetDocument; 3821f666c7SLiu Zhe import com.sun.star.sheet.XSpreadsheets; 3921f666c7SLiu Zhe import com.sun.star.uno.UnoRuntime; 40d01630b6SLiu Zhe import com.sun.star.util.XRefreshable; 4121f666c7SLiu Zhe 42cfe4bce3SLi Feng Wang /** 43cfe4bce3SLi Feng Wang * Basic sheet operator testing 44cfe4bce3SLi Feng Wang * 45cfe4bce3SLi Feng Wang */ 4621f666c7SLiu Zhe public class SheetBasicTest { 4721f666c7SLiu Zhe UnoApp unoApp = new UnoApp(); 4821f666c7SLiu Zhe XSpreadsheetDocument scDocument = null; 4921f666c7SLiu Zhe XComponent scComponent = null; 5021f666c7SLiu Zhe 5121f666c7SLiu Zhe @BeforeClass 5221f666c7SLiu Zhe public static void setUpBeforeClass() throws Exception { 5321f666c7SLiu Zhe 5421f666c7SLiu Zhe } 5521f666c7SLiu Zhe 5621f666c7SLiu Zhe @AfterClass 5721f666c7SLiu Zhe public static void tearDownAfterClass() throws Exception { 5821f666c7SLiu Zhe } 5921f666c7SLiu Zhe 6021f666c7SLiu Zhe @Before 6121f666c7SLiu Zhe public void setUp() throws Exception { 6221f666c7SLiu Zhe unoApp.start(); 6321f666c7SLiu Zhe // New a SC document 6421f666c7SLiu Zhe scComponent = unoApp.newDocument("scalc"); 6521f666c7SLiu Zhe } 6621f666c7SLiu Zhe 6721f666c7SLiu Zhe @After 6821f666c7SLiu Zhe public void tearDown() throws Exception { 6921f666c7SLiu Zhe unoApp.closeDocument(scComponent); 7021f666c7SLiu Zhe unoApp.close(); 7121f666c7SLiu Zhe } 7221f666c7SLiu Zhe 73cfe4bce3SLi Feng Wang /** 74cfe4bce3SLi Feng Wang * test insert a sheet, rename sheet name and delete sheet 75cfe4bce3SLi Feng Wang */ 7621f666c7SLiu Zhe @Test 7721f666c7SLiu Zhe public void insertRenameDeleteSheet() throws Exception { 7821f666c7SLiu Zhe // Insert a sheet named aa after first sheet 7921f666c7SLiu Zhe String sheetname = "aa"; 80d01630b6SLiu Zhe scDocument = SCUtil.getSCDocument(scComponent); 8121f666c7SLiu Zhe XSpreadsheets spreadsheets = scDocument.getSheets(); 8221f666c7SLiu Zhe spreadsheets.insertNewByName(sheetname, (short) 1); 8321f666c7SLiu Zhe 8421f666c7SLiu Zhe // active the sheet second sheet aa 85d01630b6SLiu Zhe XSpreadsheet newSpreadSheet = SCUtil.getSCSheetByIndex(scDocument, 86d01630b6SLiu Zhe (short) 1); 87d01630b6SLiu Zhe SCUtil.setCurrentSheet(scDocument, newSpreadSheet); 8821f666c7SLiu Zhe 8921f666c7SLiu Zhe // get the new speadsheet name 90d01630b6SLiu Zhe assertEquals("actual should equals aa", sheetname, 91d01630b6SLiu Zhe SCUtil.getSCSheetNameByIndex(scDocument, (short) 1)); 9221f666c7SLiu Zhe 9321f666c7SLiu Zhe // Change the Spreadsheet name 9421f666c7SLiu Zhe String changedname = "SpeadsheetAfterChange"; 95d01630b6SLiu Zhe SCUtil.setSCSheetNameByIndex(scDocument, (short) 1, changedname); 9621f666c7SLiu Zhe 9721f666c7SLiu Zhe // Save and reload document 98d01630b6SLiu Zhe SCUtil.saveFileAs(scComponent, "TestSpreadsheet", "ods"); 99d01630b6SLiu Zhe XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp, 100d01630b6SLiu Zhe scDocument, "TestSpreadsheet.ods"); 101d01630b6SLiu Zhe 102d01630b6SLiu Zhe scDocument = scDocumentTemp; 103d01630b6SLiu Zhe String sheetnameaftermove = SCUtil.getSCSheetNameByIndex(scDocument, 104d01630b6SLiu Zhe (short) 1); 10521f666c7SLiu Zhe 10621f666c7SLiu Zhe // Verify the changed Spreadsheet name 10721f666c7SLiu Zhe assertEquals("actual should equals SpeadsheetAfterChange", changedname, 108d01630b6SLiu Zhe sheetnameaftermove); 10921f666c7SLiu Zhe 110d01630b6SLiu Zhe scDocument.getSheets().removeByName(changedname); 11121f666c7SLiu Zhe 11221f666c7SLiu Zhe assertFalse("actual should equals false", 11321f666c7SLiu Zhe spreadsheets.hasByName(changedname)); 114d01630b6SLiu Zhe SCUtil.save(scDocumentTemp); 11521f666c7SLiu Zhe } 11621f666c7SLiu Zhe 117cfe4bce3SLi Feng Wang /** 118cfe4bce3SLi Feng Wang * Test copy and past sheet 119cfe4bce3SLi Feng Wang */ 12021f666c7SLiu Zhe @Test 12121f666c7SLiu Zhe public void copypastesheet() throws Exception { 12221f666c7SLiu Zhe // Insert some value into cells 123d01630b6SLiu Zhe scDocument = SCUtil.getSCDocument(scComponent); 124d01630b6SLiu Zhe String souceSheetName = "sourcesheet"; 125d01630b6SLiu Zhe SCUtil.setSCSheetNameByIndex(scDocument, (short) 0, souceSheetName); 12621f666c7SLiu Zhe String[][] stringValues = { { "", "Jan", "Feb", "Mar", "Apr", "Mai" }, 12721f666c7SLiu Zhe { "Profit", "12.3", "43.2", "5.1", "76", "56.8" }, 12821f666c7SLiu Zhe { "Rival in business", "12.2", "12.6", "17.7", "20.4", "100" }, }; 129d01630b6SLiu Zhe XSpreadsheet sourceSpreadSheet = SCUtil.getSCSheetByName(scDocument, 130d01630b6SLiu Zhe souceSheetName); 13121f666c7SLiu Zhe // input strings into sheet1 132cfe4bce3SLi Feng Wang SCUtil.setTextToCellRange(sourceSpreadSheet, 0, 0, stringValues); 13321f666c7SLiu Zhe // copy the sheet from sourcesheet to copysheet 13421f666c7SLiu Zhe String newcopysheet = "copysheet"; 135d01630b6SLiu Zhe XSpreadsheets spreadsheets = scDocument.getSheets(); 136d01630b6SLiu Zhe spreadsheets.copyByName(souceSheetName, newcopysheet, (short) 2); 13721f666c7SLiu Zhe 13821f666c7SLiu Zhe // Save and reload document 139d01630b6SLiu Zhe SCUtil.saveFileAs(scComponent, "TestCopysheet", "xls"); 140d01630b6SLiu Zhe XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp, 141d01630b6SLiu Zhe scDocument, "TestCopysheet.xls"); 142d01630b6SLiu Zhe scDocument = scDocumentTemp; 143d01630b6SLiu Zhe 144d01630b6SLiu Zhe XSpreadsheet copysheet = SCUtil 145d01630b6SLiu Zhe .getSCSheetByIndex(scDocument, (short) 2); 146d01630b6SLiu Zhe String[][] CopystringValues = SCUtil.getTextFromCellRange(copysheet, 0, 147d01630b6SLiu Zhe 0, 5, 2); 148d01630b6SLiu Zhe assertArrayEquals("Expect string value should be stringValues", 149d01630b6SLiu Zhe stringValues, CopystringValues); 15021f666c7SLiu Zhe 15121f666c7SLiu Zhe } 15221f666c7SLiu Zhe 153cfe4bce3SLi Feng Wang /** 154cfe4bce3SLi Feng Wang * Test move sheet 155cfe4bce3SLi Feng Wang */ 15621f666c7SLiu Zhe @Test 15721f666c7SLiu Zhe public void movesheet() throws Exception { 15821f666c7SLiu Zhe 15921f666c7SLiu Zhe // new sc document 160d01630b6SLiu Zhe scDocument = SCUtil.getSCDocument(scComponent); 161d01630b6SLiu Zhe XSpreadsheets spreadsheets = scDocument.getSheets(); 16221f666c7SLiu Zhe 16321f666c7SLiu Zhe // change the first sheet name and input same value into the sheet cell 16421f666c7SLiu Zhe String sheetname = "sourcesheet"; 165d01630b6SLiu Zhe SCUtil.setSCSheetNameByIndex(scDocument, (short) 0, sheetname); 16621f666c7SLiu Zhe String[][] stringValues = { { "", "Jan", "Feb", "Mar", "Apr", "Mai" }, 16721f666c7SLiu Zhe { "Profit", "12.3", "43.2", "5.1", "76", "56.8" }, 16821f666c7SLiu Zhe { "Rival in business", "12.2", "12.6", "17.7", "20.4", "100" }, }; 169d01630b6SLiu Zhe XSpreadsheet movesheet = SCUtil 170d01630b6SLiu Zhe .getSCSheetByIndex(scDocument, (short) 0); 171cfe4bce3SLi Feng Wang SCUtil.setTextToCellRange(movesheet, 0, 0,stringValues); 17221f666c7SLiu Zhe 17321f666c7SLiu Zhe // Before move, get the 2nd sheet name 174d01630b6SLiu Zhe String secondSheetNameBeforeMove = SCUtil.getSCSheetNameByIndex( 175d01630b6SLiu Zhe scDocument, (short) 1); 17621f666c7SLiu Zhe 17721f666c7SLiu Zhe // move the first sheet 17821f666c7SLiu Zhe spreadsheets.moveByName(sheetname, (short) 2); 17921f666c7SLiu Zhe 18021f666c7SLiu Zhe // Save and reload document 181d01630b6SLiu Zhe SCUtil.saveFileAs(scComponent, "Testmovesheet", "xls"); 182d01630b6SLiu Zhe XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp, 183d01630b6SLiu Zhe scDocument, "Testmovesheet.xls"); 184d01630b6SLiu Zhe scDocument = scDocumentTemp; 18521f666c7SLiu Zhe 18621f666c7SLiu Zhe // After move, get the first sheet name, and verify it same as 2nd sheet 18721f666c7SLiu Zhe // name before move 188d01630b6SLiu Zhe String firstsheetnameAfterMove = SCUtil.getSCSheetNameByIndex( 189d01630b6SLiu Zhe scDocument, (short) 0); 19021f666c7SLiu Zhe assertEquals("Expect result should be Sheet2", 191d01630b6SLiu Zhe secondSheetNameBeforeMove, firstsheetnameAfterMove); 19221f666c7SLiu Zhe 19321f666c7SLiu Zhe // Get the target sheet name after move 194d01630b6SLiu Zhe String sheetnameAfterMove = SCUtil.getSCSheetNameByIndex(scDocument, 195d01630b6SLiu Zhe (short) 1); 19621f666c7SLiu Zhe assertEquals("Expect result should be sourcesheet", sheetname, 197d01630b6SLiu Zhe sheetnameAfterMove); 19821f666c7SLiu Zhe 19921f666c7SLiu Zhe // Check the cell value after move 200d01630b6SLiu Zhe XSpreadsheet sheetaftermove = SCUtil.getSCSheetByIndex(scDocument, 201d01630b6SLiu Zhe (short) 1); 202d01630b6SLiu Zhe String[][] stringValuesaftermove = SCUtil.getTextFromCellRange( 203d01630b6SLiu Zhe sheetaftermove, 0, 0, 5, 2); 204d01630b6SLiu Zhe 205d01630b6SLiu Zhe assertArrayEquals("Expect result should be stringValues", stringValues, 206d01630b6SLiu Zhe stringValuesaftermove); 20721f666c7SLiu Zhe } 20821f666c7SLiu Zhe 209cfe4bce3SLi Feng Wang /** 210cfe4bce3SLi Feng Wang * Test hide and show sheet 211cfe4bce3SLi Feng Wang */ 212d01630b6SLiu Zhe @Test 213d01630b6SLiu Zhe public void hideShowSheet() throws Exception { 214d01630b6SLiu Zhe // Insert a sheet named hide sheet after first sheet 215d01630b6SLiu Zhe String sheetname = "hide sheet"; 216d01630b6SLiu Zhe scDocument = SCUtil.getSCDocument(scComponent); 217d01630b6SLiu Zhe XSpreadsheets spreadsheets = scDocument.getSheets(); 218d01630b6SLiu Zhe spreadsheets.insertNewByName(sheetname, (short) 1); 219d01630b6SLiu Zhe 220d01630b6SLiu Zhe // active the sheet second sheet "hide sheet" 221d01630b6SLiu Zhe XSpreadsheet secondSpreadSheet = SCUtil.getSCSheetByIndex(scDocument, 222d01630b6SLiu Zhe (short) 1); 223d01630b6SLiu Zhe SCUtil.setCurrentSheet(scDocument, secondSpreadSheet); 224d01630b6SLiu Zhe // get second sheet name and verify it should be "hide sheet" 225d01630b6SLiu Zhe assertEquals("expect active sheet name will be hide sheet", sheetname, 226d01630b6SLiu Zhe SCUtil.getSCSheetNameByIndex(scDocument, (short) 1)); 227d01630b6SLiu Zhe 228d01630b6SLiu Zhe // hide the sheet you insert 229d01630b6SLiu Zhe XPropertySet sheetPropertySet = (XPropertySet) UnoRuntime 230d01630b6SLiu Zhe .queryInterface(XPropertySet.class, secondSpreadSheet); 231d01630b6SLiu Zhe boolean isvisiable = false; 232d01630b6SLiu Zhe sheetPropertySet.setPropertyValue("IsVisible", isvisiable); 233d01630b6SLiu Zhe 234d01630b6SLiu Zhe // Save and reload document 235d01630b6SLiu Zhe SCUtil.saveFileAs(scComponent, "Testhideshowsheet", "xls"); 236d01630b6SLiu Zhe XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp, 237d01630b6SLiu Zhe scDocument, "Testhideshowsheet.xls"); 238d01630b6SLiu Zhe scDocument = scDocumentTemp; 239d01630b6SLiu Zhe 240d01630b6SLiu Zhe // get the active sheet name after hide sheet, it should be Sheet2 241d01630b6SLiu Zhe String sheet2Name = SCUtil.getSCSheetNameByIndex(scDocument, (short) 2); 242d01630b6SLiu Zhe String activesheetname = SCUtil.getSCActiveSheetName(scDocument); 243d01630b6SLiu Zhe assertEquals("Expect sheet name should be Sheet2", sheet2Name, 244d01630b6SLiu Zhe activesheetname); 245d01630b6SLiu Zhe 246d01630b6SLiu Zhe // show sheet "hide sheet" 247d01630b6SLiu Zhe sheetPropertySet = (XPropertySet) UnoRuntime.queryInterface( 248d01630b6SLiu Zhe XPropertySet.class, 249d01630b6SLiu Zhe SCUtil.getSCSheetByIndex(scDocument, (short) 1)); 250d01630b6SLiu Zhe isvisiable = true; 251d01630b6SLiu Zhe sheetPropertySet.setPropertyValue("IsVisible", isvisiable); 252d01630b6SLiu Zhe 253d01630b6SLiu Zhe // active sheet "hide sheet" 254d01630b6SLiu Zhe secondSpreadSheet = SCUtil.getSCSheetByIndex(scDocument, (short) 1); 255d01630b6SLiu Zhe SCUtil.setCurrentSheet(scDocument, secondSpreadSheet); 256d01630b6SLiu Zhe 257d01630b6SLiu Zhe // Get current active sheet name, verify it same as "hide sheet" 258d01630b6SLiu Zhe String currentactivesheetname = SCUtil.getSCActiveSheetName(scDocument); 259d01630b6SLiu Zhe assertEquals("Expect active sheet name is hidesheet", sheetname, 260d01630b6SLiu Zhe currentactivesheetname); 261d01630b6SLiu Zhe SCUtil.save(scDocument); 262d01630b6SLiu Zhe } 263d01630b6SLiu Zhe 264cfe4bce3SLi Feng Wang /** 265cfe4bce3SLi Feng Wang * Test sheet tab color 266cfe4bce3SLi Feng Wang */ 267d01630b6SLiu Zhe @Test 268d01630b6SLiu Zhe public void sheetColor() throws Exception { 269d01630b6SLiu Zhe // get first sheet propertyset 270d01630b6SLiu Zhe scDocument = SCUtil.getSCDocument(scComponent); 271d01630b6SLiu Zhe XSpreadsheets spreadsheets = scDocument.getSheets(); 272d01630b6SLiu Zhe XSpreadsheet firstSpreadSheet = SCUtil.getSCSheetByIndex(scDocument, 273d01630b6SLiu Zhe (short) 0); 274d01630b6SLiu Zhe XPropertySet sheet1PropertySet = (XPropertySet) UnoRuntime 275d01630b6SLiu Zhe .queryInterface(XPropertySet.class, firstSpreadSheet); 276d01630b6SLiu Zhe 277cfe4bce3SLi Feng Wang // Set sheet tab color to 111 278d01630b6SLiu Zhe sheet1PropertySet.setPropertyValue("TabColor", 111); 279d01630b6SLiu Zhe 280d01630b6SLiu Zhe // copy the color sheet to new sheet 281d01630b6SLiu Zhe spreadsheets.copyByName( 282d01630b6SLiu Zhe SCUtil.getSCSheetNameByIndex(scDocument, (short) 0), 283d01630b6SLiu Zhe "newsheet", (short) 3); 284d01630b6SLiu Zhe 285d01630b6SLiu Zhe // Save and reopen the document 286d01630b6SLiu Zhe SCUtil.saveFileAs(scComponent, "Testcolorsheet", "ods"); 287d01630b6SLiu Zhe XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp, 288d01630b6SLiu Zhe scDocument, "Testcolorsheet.ods"); 289d01630b6SLiu Zhe scDocument = scDocumentTemp; 290d01630b6SLiu Zhe 291d01630b6SLiu Zhe // Get first sheet color 292d01630b6SLiu Zhe sheet1PropertySet = (XPropertySet) UnoRuntime.queryInterface( 293d01630b6SLiu Zhe XPropertySet.class, 294d01630b6SLiu Zhe SCUtil.getSCSheetByIndex(scDocument, (short) 0)); 295d01630b6SLiu Zhe int firstSheetcolorid = (Integer) sheet1PropertySet 296d01630b6SLiu Zhe .getPropertyValue("TabColor"); 297d01630b6SLiu Zhe 298d01630b6SLiu Zhe // Get the copyed sheet color 299d01630b6SLiu Zhe XPropertySet newsheetPropertySet = (XPropertySet) UnoRuntime 300d01630b6SLiu Zhe .queryInterface(XPropertySet.class, 301d01630b6SLiu Zhe SCUtil.getSCSheetByIndex(scDocument, (short) 3)); 302d01630b6SLiu Zhe int copySheetcolorid = (Integer) newsheetPropertySet 303d01630b6SLiu Zhe .getPropertyValue("TabColor"); 304d01630b6SLiu Zhe 305d01630b6SLiu Zhe // Verify first sheet color changed successfully 306d01630b6SLiu Zhe assertEquals("Expect color should be 111", 111, firstSheetcolorid); 307d01630b6SLiu Zhe 308d01630b6SLiu Zhe // Verify first sheet color same as copy sheet color 309d01630b6SLiu Zhe assertEquals("Expect color should be 111", firstSheetcolorid, 310d01630b6SLiu Zhe copySheetcolorid); 31121f666c7SLiu Zhe } 31221f666c7SLiu Zhe 313cfe4bce3SLi Feng Wang /** 314cfe4bce3SLi Feng Wang * test insert sheet from other file 315cfe4bce3SLi Feng Wang */ 316*b554e61dSDamjan Jovanovic // FIXME: locks up on update link confirmation dialog. 317*b554e61dSDamjan Jovanovic @Test(timeout = 15000) 318d01630b6SLiu Zhe public void insertSheetFromfile() throws Exception { 319d01630b6SLiu Zhe // New a document source.xls, add value to 3 sheet 320d01630b6SLiu Zhe scDocument = SCUtil.getSCDocument(scComponent); 321d01630b6SLiu Zhe XSpreadsheets spreadsheets = scDocument.getSheets(); 322d01630b6SLiu Zhe XSpreadsheet firstSheet = SCUtil.getSCSheetByIndex(scDocument, 323d01630b6SLiu Zhe (short) 0); 324d01630b6SLiu Zhe XSpreadsheet secondSheet = SCUtil.getSCSheetByIndex(scDocument, 325d01630b6SLiu Zhe (short) 1); 326d01630b6SLiu Zhe XSpreadsheet thirdSheet = SCUtil.getSCSheetByIndex(scDocument, 327d01630b6SLiu Zhe (short) 2); 328d01630b6SLiu Zhe SCUtil.setFormulaToCell(firstSheet, 1, 2, "=2*2"); 329d01630b6SLiu Zhe SCUtil.setFormulaToCell(secondSheet, 1, 2, "=2*2"); 330d01630b6SLiu Zhe SCUtil.setFormulaToCell(thirdSheet, 1, 2, "=2*2"); 331d01630b6SLiu Zhe 332d01630b6SLiu Zhe // Save and close this document 333d01630b6SLiu Zhe SCUtil.saveFileAs(scComponent, "source", "xls"); 334d01630b6SLiu Zhe SCUtil.closeFile(scDocument); 335d01630b6SLiu Zhe 336d01630b6SLiu Zhe // get source document URL 337dded528dSLiu Zhe String SourcestoreUrl = Testspace.getUrl("output/sc/" + "source" + "." 338d01630b6SLiu Zhe + "xls"); 339d01630b6SLiu Zhe 340d01630b6SLiu Zhe // New a document 341d01630b6SLiu Zhe scComponent = unoApp.newDocument("scalc"); 342d01630b6SLiu Zhe scDocument = SCUtil.getSCDocument(scComponent); 343d01630b6SLiu Zhe spreadsheets = scDocument.getSheets(); 344d01630b6SLiu Zhe // Insert firstexternalsheet sheet, link with Sheet1 in source document 345d01630b6SLiu Zhe // and the link mode is NORMAL 346d01630b6SLiu Zhe spreadsheets.insertNewByName("firstexternalsheet", (short) 3); 347d01630b6SLiu Zhe XSpreadsheet firstexternalsheet = SCUtil.getSCSheetByIndex(scDocument, 348d01630b6SLiu Zhe (short) 3); 349d01630b6SLiu Zhe XSheetLinkable xfirstSheetLinkable = (XSheetLinkable) UnoRuntime 350d01630b6SLiu Zhe .queryInterface(XSheetLinkable.class, firstexternalsheet); 351d01630b6SLiu Zhe xfirstSheetLinkable.link(SourcestoreUrl, "", "MS Excel 97", "", 352d01630b6SLiu Zhe SheetLinkMode.NORMAL); 353d01630b6SLiu Zhe 354d01630b6SLiu Zhe // Insert secondexternalsheet sheet, link with Sheet2 in source document 355d01630b6SLiu Zhe // and the link mode is VALUE 356d01630b6SLiu Zhe spreadsheets.insertNewByName("secondexternalsheet", (short) 4); 357d01630b6SLiu Zhe XSpreadsheet secondexternalsheet = SCUtil.getSCSheetByIndex(scDocument, 358d01630b6SLiu Zhe (short) 4); 359d01630b6SLiu Zhe XSheetLinkable xsecondSheetLinkable = (XSheetLinkable) UnoRuntime 360d01630b6SLiu Zhe .queryInterface(XSheetLinkable.class, secondexternalsheet); 361d01630b6SLiu Zhe xsecondSheetLinkable.link(SourcestoreUrl, "Sheet2", "MS Excel 97", "", 362d01630b6SLiu Zhe SheetLinkMode.VALUE); 363d01630b6SLiu Zhe 364d01630b6SLiu Zhe // Insert secondexternalsheet sheet, link with Sheet2 in source document 365d01630b6SLiu Zhe // and the link mode is NONE 366d01630b6SLiu Zhe spreadsheets.insertNewByName("thirdexternalsheet", (short) 5); 367d01630b6SLiu Zhe XSpreadsheet thirdexternalsheet = SCUtil.getSCSheetByIndex(scDocument, 368d01630b6SLiu Zhe (short) 5); 369d01630b6SLiu Zhe XSheetLinkable xthirdSheetLinkable = (XSheetLinkable) UnoRuntime 370d01630b6SLiu Zhe .queryInterface(XSheetLinkable.class, thirdexternalsheet); 371d01630b6SLiu Zhe xthirdSheetLinkable.link(SourcestoreUrl, "Sheet3", "MS Excel 97", "", 372d01630b6SLiu Zhe SheetLinkMode.NONE); 373d01630b6SLiu Zhe 374d01630b6SLiu Zhe // Verify firstexternalsheet 375d01630b6SLiu Zhe assertEquals("Expect formula should be =2*2", "=2*2", 376d01630b6SLiu Zhe SCUtil.getFormulaFromCell(firstexternalsheet, 1, 2)); 377d01630b6SLiu Zhe assertEquals("Expect formula result should be 4", "4", 378d01630b6SLiu Zhe SCUtil.getTextFromCell(firstexternalsheet, 1, 2)); 379d01630b6SLiu Zhe 380d01630b6SLiu Zhe // Verify secondexternalsheet 381d01630b6SLiu Zhe assertEquals("Expect formula should be 4", "4", 382d01630b6SLiu Zhe SCUtil.getFormulaFromCell(secondexternalsheet, 1, 2)); 383d01630b6SLiu Zhe assertEquals("Expect formula result should be 4", "4", 384d01630b6SLiu Zhe SCUtil.getTextFromCell(secondexternalsheet, 1, 2)); 385d01630b6SLiu Zhe 386d01630b6SLiu Zhe // Verify thirdexternalsheet 387d01630b6SLiu Zhe assertEquals("Expect formula should be blank", "", 388d01630b6SLiu Zhe SCUtil.getFormulaFromCell(thirdexternalsheet, 1, 2)); 389d01630b6SLiu Zhe assertEquals("Expect formula result should be blank", "", 390d01630b6SLiu Zhe SCUtil.getTextFromCell(thirdexternalsheet, 1, 2)); 391d01630b6SLiu Zhe 392d01630b6SLiu Zhe // save document and verify the linked sheet again 393d01630b6SLiu Zhe SCUtil.saveFileAs(scComponent, "linked", "ods"); 394d01630b6SLiu Zhe XSpreadsheetDocument tempscDocument = SCUtil.reloadFile(unoApp, 395d01630b6SLiu Zhe scDocument, "linked.ods"); 396d01630b6SLiu Zhe scDocument = tempscDocument; 397d01630b6SLiu Zhe firstexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 3); 398d01630b6SLiu Zhe secondexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 4); 399d01630b6SLiu Zhe thirdexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 5); 400d01630b6SLiu Zhe 401d01630b6SLiu Zhe // Verify firstexternalsheet 402d01630b6SLiu Zhe assertEquals("Expect formula should be =2*2", "=2*2", 403d01630b6SLiu Zhe SCUtil.getFormulaFromCell(firstexternalsheet, 1, 2)); 404d01630b6SLiu Zhe assertEquals("Expect formula result should be 4", "4", 405d01630b6SLiu Zhe SCUtil.getTextFromCell(firstexternalsheet, 1, 2)); 406d01630b6SLiu Zhe 407d01630b6SLiu Zhe // Verify secondexternalsheet 408d01630b6SLiu Zhe assertEquals("Expect formula should be 4", "4", 409d01630b6SLiu Zhe SCUtil.getFormulaFromCell(secondexternalsheet, 1, 2)); 410d01630b6SLiu Zhe assertEquals("Expect formula result should be 4", "4", 411d01630b6SLiu Zhe SCUtil.getTextFromCell(secondexternalsheet, 1, 2)); 412d01630b6SLiu Zhe 413d01630b6SLiu Zhe // Verify thirdexternalsheet 414d01630b6SLiu Zhe assertEquals("Expect formula should be blank", "", 415d01630b6SLiu Zhe SCUtil.getFormulaFromCell(thirdexternalsheet, 1, 2)); 416d01630b6SLiu Zhe assertEquals("Expect formula result should be blank", "", 417d01630b6SLiu Zhe SCUtil.getTextFromCell(thirdexternalsheet, 1, 2)); 418d01630b6SLiu Zhe 419d01630b6SLiu Zhe //save and close document 420d01630b6SLiu Zhe SCUtil.save(scDocument); 421d01630b6SLiu Zhe SCUtil.closeFile(scDocument); 422d01630b6SLiu Zhe 423d01630b6SLiu Zhe //Open souce document and change the value in souce document 424d01630b6SLiu Zhe XSpreadsheetDocument sourcescDocument = SCUtil.reloadFile(unoApp, 425d01630b6SLiu Zhe scDocument, "source.xls"); 426d01630b6SLiu Zhe firstSheet = SCUtil.getSCSheetByIndex(sourcescDocument, (short) 0); 427d01630b6SLiu Zhe secondSheet = SCUtil.getSCSheetByIndex(sourcescDocument, (short) 1); 428d01630b6SLiu Zhe thirdSheet = SCUtil.getSCSheetByIndex(sourcescDocument, (short) 2); 429d01630b6SLiu Zhe SCUtil.setFormulaToCell(firstSheet, 1, 2, "=3*3"); 430d01630b6SLiu Zhe SCUtil.setFormulaToCell(secondSheet, 1, 2, "=3*3"); 431d01630b6SLiu Zhe SCUtil.setFormulaToCell(thirdSheet, 1, 2, "=3*3"); 432d01630b6SLiu Zhe SCUtil.save(sourcescDocument); 433d01630b6SLiu Zhe SCUtil.closeFile(sourcescDocument); 434d01630b6SLiu Zhe 435d01630b6SLiu Zhe //Open link document 436d01630b6SLiu Zhe tempscDocument = SCUtil.reloadFile(unoApp, scDocument, "linked.ods"); 437d01630b6SLiu Zhe scDocument = tempscDocument; 438d01630b6SLiu Zhe spreadsheets = scDocument.getSheets(); 439d01630b6SLiu Zhe 440d01630b6SLiu Zhe firstexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 3); 441d01630b6SLiu Zhe secondexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 4); 442d01630b6SLiu Zhe thirdexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 5); 443d01630b6SLiu Zhe 444d01630b6SLiu Zhe //get Object SheetLinks for document 445d01630b6SLiu Zhe XPropertySet sheetpropertyset = (XPropertySet) UnoRuntime 446d01630b6SLiu Zhe .queryInterface(XPropertySet.class, scDocument); 447d01630b6SLiu Zhe Object sheetLinks = sheetpropertyset.getPropertyValue("SheetLinks"); 448d01630b6SLiu Zhe 449d01630b6SLiu Zhe XIndexAccess xsheetlinks = (XIndexAccess) UnoRuntime.queryInterface( 450d01630b6SLiu Zhe XIndexAccess.class, sheetLinks); 451d01630b6SLiu Zhe 452d01630b6SLiu Zhe //Refresh all links 453d01630b6SLiu Zhe for (int i = 0; i < xsheetlinks.getCount(); i++) { 454d01630b6SLiu Zhe Object sheetlink = xsheetlinks.getByIndex(i); 455d01630b6SLiu Zhe XRefreshable xsheetRefreshable = (XRefreshable) UnoRuntime 456d01630b6SLiu Zhe .queryInterface(XRefreshable.class, sheetlink); 457d01630b6SLiu Zhe xsheetRefreshable.refresh(); 458d01630b6SLiu Zhe } 459d01630b6SLiu Zhe 460d01630b6SLiu Zhe // Verify firstexternalsheet 461d01630b6SLiu Zhe assertEquals("Expect formula should be =3*3", "=3*3", 462d01630b6SLiu Zhe SCUtil.getFormulaFromCell(firstexternalsheet, 1, 2)); 463d01630b6SLiu Zhe assertEquals("Expect formula result should be 9", "9", 464d01630b6SLiu Zhe SCUtil.getTextFromCell(firstexternalsheet, 1, 2)); 465d01630b6SLiu Zhe 466d01630b6SLiu Zhe // Verify secondexternalsheet 467d01630b6SLiu Zhe assertEquals("Expect formula should be 9", "9", 468d01630b6SLiu Zhe SCUtil.getFormulaFromCell(secondexternalsheet, 1, 2)); 469d01630b6SLiu Zhe assertEquals("Expect formula result should be 9", "9", 470d01630b6SLiu Zhe SCUtil.getTextFromCell(secondexternalsheet, 1, 2)); 471d01630b6SLiu Zhe 472d01630b6SLiu Zhe // Verify thirdexternalsheet 473d01630b6SLiu Zhe assertEquals("Expect formula should be blank", "", 474d01630b6SLiu Zhe SCUtil.getFormulaFromCell(thirdexternalsheet, 1, 2)); 475d01630b6SLiu Zhe assertEquals("Expect formula result should be blank", "", 476d01630b6SLiu Zhe SCUtil.getTextFromCell(thirdexternalsheet, 1, 2)); 477d01630b6SLiu Zhe 478d01630b6SLiu Zhe //Save the document before close 479d01630b6SLiu Zhe SCUtil.save(scDocument); 480d01630b6SLiu Zhe 48121f666c7SLiu Zhe } 482d01630b6SLiu Zhe 48321f666c7SLiu Zhe } 484