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 42*cfe4bce3SLi Feng Wang /** 43*cfe4bce3SLi Feng Wang * Basic sheet operator testing 44*cfe4bce3SLi Feng Wang * 45*cfe4bce3SLi 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 73*cfe4bce3SLi Feng Wang /** 74*cfe4bce3SLi Feng Wang * test insert a sheet, rename sheet name and delete sheet 75*cfe4bce3SLi 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 117*cfe4bce3SLi Feng Wang /** 118*cfe4bce3SLi Feng Wang * Test copy and past sheet 119*cfe4bce3SLi 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 132*cfe4bce3SLi 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 153*cfe4bce3SLi Feng Wang /** 154*cfe4bce3SLi Feng Wang * Test move sheet 155*cfe4bce3SLi 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); 171*cfe4bce3SLi 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 209*cfe4bce3SLi Feng Wang /** 210*cfe4bce3SLi Feng Wang * Test hide and show sheet 211*cfe4bce3SLi 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 264*cfe4bce3SLi Feng Wang /** 265*cfe4bce3SLi Feng Wang * Test sheet tab color 266*cfe4bce3SLi 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 277*cfe4bce3SLi 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 313*cfe4bce3SLi Feng Wang /** 314*cfe4bce3SLi Feng Wang * test insert sheet from other file 315*cfe4bce3SLi Feng Wang */ 316d01630b6SLiu Zhe @Test 317d01630b6SLiu Zhe public void insertSheetFromfile() throws Exception { 318d01630b6SLiu Zhe // New a document source.xls, add value to 3 sheet 319d01630b6SLiu Zhe scDocument = SCUtil.getSCDocument(scComponent); 320d01630b6SLiu Zhe XSpreadsheets spreadsheets = scDocument.getSheets(); 321d01630b6SLiu Zhe XSpreadsheet firstSheet = SCUtil.getSCSheetByIndex(scDocument, 322d01630b6SLiu Zhe (short) 0); 323d01630b6SLiu Zhe XSpreadsheet secondSheet = SCUtil.getSCSheetByIndex(scDocument, 324d01630b6SLiu Zhe (short) 1); 325d01630b6SLiu Zhe XSpreadsheet thirdSheet = SCUtil.getSCSheetByIndex(scDocument, 326d01630b6SLiu Zhe (short) 2); 327d01630b6SLiu Zhe SCUtil.setFormulaToCell(firstSheet, 1, 2, "=2*2"); 328d01630b6SLiu Zhe SCUtil.setFormulaToCell(secondSheet, 1, 2, "=2*2"); 329d01630b6SLiu Zhe SCUtil.setFormulaToCell(thirdSheet, 1, 2, "=2*2"); 330d01630b6SLiu Zhe 331d01630b6SLiu Zhe // Save and close this document 332d01630b6SLiu Zhe SCUtil.saveFileAs(scComponent, "source", "xls"); 333d01630b6SLiu Zhe SCUtil.closeFile(scDocument); 334d01630b6SLiu Zhe 335d01630b6SLiu Zhe // get source document URL 336dded528dSLiu Zhe String SourcestoreUrl = Testspace.getUrl("output/sc/" + "source" + "." 337d01630b6SLiu Zhe + "xls"); 338d01630b6SLiu Zhe 339d01630b6SLiu Zhe // New a document 340d01630b6SLiu Zhe scComponent = unoApp.newDocument("scalc"); 341d01630b6SLiu Zhe scDocument = SCUtil.getSCDocument(scComponent); 342d01630b6SLiu Zhe spreadsheets = scDocument.getSheets(); 343d01630b6SLiu Zhe // Insert firstexternalsheet sheet, link with Sheet1 in source document 344d01630b6SLiu Zhe // and the link mode is NORMAL 345d01630b6SLiu Zhe spreadsheets.insertNewByName("firstexternalsheet", (short) 3); 346d01630b6SLiu Zhe XSpreadsheet firstexternalsheet = SCUtil.getSCSheetByIndex(scDocument, 347d01630b6SLiu Zhe (short) 3); 348d01630b6SLiu Zhe XSheetLinkable xfirstSheetLinkable = (XSheetLinkable) UnoRuntime 349d01630b6SLiu Zhe .queryInterface(XSheetLinkable.class, firstexternalsheet); 350d01630b6SLiu Zhe xfirstSheetLinkable.link(SourcestoreUrl, "", "MS Excel 97", "", 351d01630b6SLiu Zhe SheetLinkMode.NORMAL); 352d01630b6SLiu Zhe 353d01630b6SLiu Zhe // Insert secondexternalsheet sheet, link with Sheet2 in source document 354d01630b6SLiu Zhe // and the link mode is VALUE 355d01630b6SLiu Zhe spreadsheets.insertNewByName("secondexternalsheet", (short) 4); 356d01630b6SLiu Zhe XSpreadsheet secondexternalsheet = SCUtil.getSCSheetByIndex(scDocument, 357d01630b6SLiu Zhe (short) 4); 358d01630b6SLiu Zhe XSheetLinkable xsecondSheetLinkable = (XSheetLinkable) UnoRuntime 359d01630b6SLiu Zhe .queryInterface(XSheetLinkable.class, secondexternalsheet); 360d01630b6SLiu Zhe xsecondSheetLinkable.link(SourcestoreUrl, "Sheet2", "MS Excel 97", "", 361d01630b6SLiu Zhe SheetLinkMode.VALUE); 362d01630b6SLiu Zhe 363d01630b6SLiu Zhe // Insert secondexternalsheet sheet, link with Sheet2 in source document 364d01630b6SLiu Zhe // and the link mode is NONE 365d01630b6SLiu Zhe spreadsheets.insertNewByName("thirdexternalsheet", (short) 5); 366d01630b6SLiu Zhe XSpreadsheet thirdexternalsheet = SCUtil.getSCSheetByIndex(scDocument, 367d01630b6SLiu Zhe (short) 5); 368d01630b6SLiu Zhe XSheetLinkable xthirdSheetLinkable = (XSheetLinkable) UnoRuntime 369d01630b6SLiu Zhe .queryInterface(XSheetLinkable.class, thirdexternalsheet); 370d01630b6SLiu Zhe xthirdSheetLinkable.link(SourcestoreUrl, "Sheet3", "MS Excel 97", "", 371d01630b6SLiu Zhe SheetLinkMode.NONE); 372d01630b6SLiu Zhe 373d01630b6SLiu Zhe // Verify firstexternalsheet 374d01630b6SLiu Zhe assertEquals("Expect formula should be =2*2", "=2*2", 375d01630b6SLiu Zhe SCUtil.getFormulaFromCell(firstexternalsheet, 1, 2)); 376d01630b6SLiu Zhe assertEquals("Expect formula result should be 4", "4", 377d01630b6SLiu Zhe SCUtil.getTextFromCell(firstexternalsheet, 1, 2)); 378d01630b6SLiu Zhe 379d01630b6SLiu Zhe // Verify secondexternalsheet 380d01630b6SLiu Zhe assertEquals("Expect formula should be 4", "4", 381d01630b6SLiu Zhe SCUtil.getFormulaFromCell(secondexternalsheet, 1, 2)); 382d01630b6SLiu Zhe assertEquals("Expect formula result should be 4", "4", 383d01630b6SLiu Zhe SCUtil.getTextFromCell(secondexternalsheet, 1, 2)); 384d01630b6SLiu Zhe 385d01630b6SLiu Zhe // Verify thirdexternalsheet 386d01630b6SLiu Zhe assertEquals("Expect formula should be blank", "", 387d01630b6SLiu Zhe SCUtil.getFormulaFromCell(thirdexternalsheet, 1, 2)); 388d01630b6SLiu Zhe assertEquals("Expect formula result should be blank", "", 389d01630b6SLiu Zhe SCUtil.getTextFromCell(thirdexternalsheet, 1, 2)); 390d01630b6SLiu Zhe 391d01630b6SLiu Zhe // save document and verify the linked sheet again 392d01630b6SLiu Zhe SCUtil.saveFileAs(scComponent, "linked", "ods"); 393d01630b6SLiu Zhe XSpreadsheetDocument tempscDocument = SCUtil.reloadFile(unoApp, 394d01630b6SLiu Zhe scDocument, "linked.ods"); 395d01630b6SLiu Zhe scDocument = tempscDocument; 396d01630b6SLiu Zhe firstexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 3); 397d01630b6SLiu Zhe secondexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 4); 398d01630b6SLiu Zhe thirdexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 5); 399d01630b6SLiu Zhe 400d01630b6SLiu Zhe // Verify firstexternalsheet 401d01630b6SLiu Zhe assertEquals("Expect formula should be =2*2", "=2*2", 402d01630b6SLiu Zhe SCUtil.getFormulaFromCell(firstexternalsheet, 1, 2)); 403d01630b6SLiu Zhe assertEquals("Expect formula result should be 4", "4", 404d01630b6SLiu Zhe SCUtil.getTextFromCell(firstexternalsheet, 1, 2)); 405d01630b6SLiu Zhe 406d01630b6SLiu Zhe // Verify secondexternalsheet 407d01630b6SLiu Zhe assertEquals("Expect formula should be 4", "4", 408d01630b6SLiu Zhe SCUtil.getFormulaFromCell(secondexternalsheet, 1, 2)); 409d01630b6SLiu Zhe assertEquals("Expect formula result should be 4", "4", 410d01630b6SLiu Zhe SCUtil.getTextFromCell(secondexternalsheet, 1, 2)); 411d01630b6SLiu Zhe 412d01630b6SLiu Zhe // Verify thirdexternalsheet 413d01630b6SLiu Zhe assertEquals("Expect formula should be blank", "", 414d01630b6SLiu Zhe SCUtil.getFormulaFromCell(thirdexternalsheet, 1, 2)); 415d01630b6SLiu Zhe assertEquals("Expect formula result should be blank", "", 416d01630b6SLiu Zhe SCUtil.getTextFromCell(thirdexternalsheet, 1, 2)); 417d01630b6SLiu Zhe 418d01630b6SLiu Zhe //save and close document 419d01630b6SLiu Zhe SCUtil.save(scDocument); 420d01630b6SLiu Zhe SCUtil.closeFile(scDocument); 421d01630b6SLiu Zhe 422d01630b6SLiu Zhe //Open souce document and change the value in souce document 423d01630b6SLiu Zhe XSpreadsheetDocument sourcescDocument = SCUtil.reloadFile(unoApp, 424d01630b6SLiu Zhe scDocument, "source.xls"); 425d01630b6SLiu Zhe firstSheet = SCUtil.getSCSheetByIndex(sourcescDocument, (short) 0); 426d01630b6SLiu Zhe secondSheet = SCUtil.getSCSheetByIndex(sourcescDocument, (short) 1); 427d01630b6SLiu Zhe thirdSheet = SCUtil.getSCSheetByIndex(sourcescDocument, (short) 2); 428d01630b6SLiu Zhe SCUtil.setFormulaToCell(firstSheet, 1, 2, "=3*3"); 429d01630b6SLiu Zhe SCUtil.setFormulaToCell(secondSheet, 1, 2, "=3*3"); 430d01630b6SLiu Zhe SCUtil.setFormulaToCell(thirdSheet, 1, 2, "=3*3"); 431d01630b6SLiu Zhe SCUtil.save(sourcescDocument); 432d01630b6SLiu Zhe SCUtil.closeFile(sourcescDocument); 433d01630b6SLiu Zhe 434d01630b6SLiu Zhe //Open link document 435d01630b6SLiu Zhe tempscDocument = SCUtil.reloadFile(unoApp, scDocument, "linked.ods"); 436d01630b6SLiu Zhe scDocument = tempscDocument; 437d01630b6SLiu Zhe spreadsheets = scDocument.getSheets(); 438d01630b6SLiu Zhe 439d01630b6SLiu Zhe firstexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 3); 440d01630b6SLiu Zhe secondexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 4); 441d01630b6SLiu Zhe thirdexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 5); 442d01630b6SLiu Zhe 443d01630b6SLiu Zhe //get Object SheetLinks for document 444d01630b6SLiu Zhe XPropertySet sheetpropertyset = (XPropertySet) UnoRuntime 445d01630b6SLiu Zhe .queryInterface(XPropertySet.class, scDocument); 446d01630b6SLiu Zhe Object sheetLinks = sheetpropertyset.getPropertyValue("SheetLinks"); 447d01630b6SLiu Zhe 448d01630b6SLiu Zhe XIndexAccess xsheetlinks = (XIndexAccess) UnoRuntime.queryInterface( 449d01630b6SLiu Zhe XIndexAccess.class, sheetLinks); 450d01630b6SLiu Zhe 451d01630b6SLiu Zhe //Refresh all links 452d01630b6SLiu Zhe for (int i = 0; i < xsheetlinks.getCount(); i++) { 453d01630b6SLiu Zhe Object sheetlink = xsheetlinks.getByIndex(i); 454d01630b6SLiu Zhe XRefreshable xsheetRefreshable = (XRefreshable) UnoRuntime 455d01630b6SLiu Zhe .queryInterface(XRefreshable.class, sheetlink); 456d01630b6SLiu Zhe xsheetRefreshable.refresh(); 457d01630b6SLiu Zhe } 458d01630b6SLiu Zhe 459d01630b6SLiu Zhe // Verify firstexternalsheet 460d01630b6SLiu Zhe assertEquals("Expect formula should be =3*3", "=3*3", 461d01630b6SLiu Zhe SCUtil.getFormulaFromCell(firstexternalsheet, 1, 2)); 462d01630b6SLiu Zhe assertEquals("Expect formula result should be 9", "9", 463d01630b6SLiu Zhe SCUtil.getTextFromCell(firstexternalsheet, 1, 2)); 464d01630b6SLiu Zhe 465d01630b6SLiu Zhe // Verify secondexternalsheet 466d01630b6SLiu Zhe assertEquals("Expect formula should be 9", "9", 467d01630b6SLiu Zhe SCUtil.getFormulaFromCell(secondexternalsheet, 1, 2)); 468d01630b6SLiu Zhe assertEquals("Expect formula result should be 9", "9", 469d01630b6SLiu Zhe SCUtil.getTextFromCell(secondexternalsheet, 1, 2)); 470d01630b6SLiu Zhe 471d01630b6SLiu Zhe // Verify thirdexternalsheet 472d01630b6SLiu Zhe assertEquals("Expect formula should be blank", "", 473d01630b6SLiu Zhe SCUtil.getFormulaFromCell(thirdexternalsheet, 1, 2)); 474d01630b6SLiu Zhe assertEquals("Expect formula result should be blank", "", 475d01630b6SLiu Zhe SCUtil.getTextFromCell(thirdexternalsheet, 1, 2)); 476d01630b6SLiu Zhe 477d01630b6SLiu Zhe //Save the document before close 478d01630b6SLiu Zhe SCUtil.save(scDocument); 479d01630b6SLiu Zhe 48021f666c7SLiu Zhe } 481d01630b6SLiu Zhe 48221f666c7SLiu Zhe } 483