1*cc57ba34Smseidel /************************************************************** 2*cc57ba34Smseidel * 3*cc57ba34Smseidel * 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 10*cc57ba34Smseidel * 1121f666c7SLiu Zhe * http://www.apache.org/licenses/LICENSE-2.0 12*cc57ba34Smseidel * 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. 19*cc57ba34Smseidel * 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; 2921f666c7SLiu Zhe import org.junit.Test; 3021f666c7SLiu Zhe import org.openoffice.test.common.Testspace; 3121f666c7SLiu Zhe import org.openoffice.test.uno.UnoApp; 32d01630b6SLiu Zhe import testlib.uno.SCUtil; 33d01630b6SLiu Zhe import com.sun.star.beans.XPropertySet; 3421f666c7SLiu Zhe import com.sun.star.container.XIndexAccess; 3521f666c7SLiu Zhe import com.sun.star.lang.XComponent; 36d01630b6SLiu Zhe import com.sun.star.sheet.SheetLinkMode; 37d01630b6SLiu Zhe import com.sun.star.sheet.XSheetLinkable; 3821f666c7SLiu Zhe import com.sun.star.sheet.XSpreadsheet; 3921f666c7SLiu Zhe import com.sun.star.sheet.XSpreadsheetDocument; 4021f666c7SLiu Zhe import com.sun.star.sheet.XSpreadsheets; 4121f666c7SLiu Zhe import com.sun.star.uno.UnoRuntime; 42d01630b6SLiu Zhe import com.sun.star.util.XRefreshable; 4321f666c7SLiu Zhe 44cfe4bce3SLi Feng Wang /** 45cfe4bce3SLi Feng Wang * Basic sheet operator testing 46cfe4bce3SLi Feng Wang * 47cfe4bce3SLi Feng Wang */ 4821f666c7SLiu Zhe public class SheetBasicTest { 4921f666c7SLiu Zhe UnoApp unoApp = new UnoApp(); 5021f666c7SLiu Zhe XSpreadsheetDocument scDocument = null; 5121f666c7SLiu Zhe XComponent scComponent = null; 5221f666c7SLiu Zhe 5321f666c7SLiu Zhe @BeforeClass setUpBeforeClass()5421f666c7SLiu Zhe public static void setUpBeforeClass() throws Exception { 5521f666c7SLiu Zhe 5621f666c7SLiu Zhe } 5721f666c7SLiu Zhe 5821f666c7SLiu Zhe @AfterClass tearDownAfterClass()5921f666c7SLiu Zhe public static void tearDownAfterClass() throws Exception { 6021f666c7SLiu Zhe } 6121f666c7SLiu Zhe 6221f666c7SLiu Zhe @Before setUp()6321f666c7SLiu Zhe public void setUp() throws Exception { 6421f666c7SLiu Zhe unoApp.start(); 6521f666c7SLiu Zhe // New a SC document 6621f666c7SLiu Zhe scComponent = unoApp.newDocument("scalc"); 6721f666c7SLiu Zhe } 6821f666c7SLiu Zhe 6921f666c7SLiu Zhe @After tearDown()7021f666c7SLiu Zhe public void tearDown() throws Exception { 7121f666c7SLiu Zhe unoApp.closeDocument(scComponent); 7221f666c7SLiu Zhe unoApp.close(); 7321f666c7SLiu Zhe } 7421f666c7SLiu Zhe 75cfe4bce3SLi Feng Wang /** 76cfe4bce3SLi Feng Wang * test insert a sheet, rename sheet name and delete sheet 77cfe4bce3SLi Feng Wang */ 7821f666c7SLiu Zhe @Test insertRenameDeleteSheet()7921f666c7SLiu Zhe public void insertRenameDeleteSheet() throws Exception { 8021f666c7SLiu Zhe // Insert a sheet named aa after first sheet 8121f666c7SLiu Zhe String sheetname = "aa"; 82d01630b6SLiu Zhe scDocument = SCUtil.getSCDocument(scComponent); 8321f666c7SLiu Zhe XSpreadsheets spreadsheets = scDocument.getSheets(); 8421f666c7SLiu Zhe spreadsheets.insertNewByName(sheetname, (short) 1); 8521f666c7SLiu Zhe 8621f666c7SLiu Zhe // active the sheet second sheet aa 87d01630b6SLiu Zhe XSpreadsheet newSpreadSheet = SCUtil.getSCSheetByIndex(scDocument, 88d01630b6SLiu Zhe (short) 1); 89d01630b6SLiu Zhe SCUtil.setCurrentSheet(scDocument, newSpreadSheet); 9021f666c7SLiu Zhe 91*cc57ba34Smseidel // get the new spreadsheet name 92d01630b6SLiu Zhe assertEquals("actual should equals aa", sheetname, 93d01630b6SLiu Zhe SCUtil.getSCSheetNameByIndex(scDocument, (short) 1)); 9421f666c7SLiu Zhe 9521f666c7SLiu Zhe // Change the Spreadsheet name 9621f666c7SLiu Zhe String changedname = "SpeadsheetAfterChange"; 97d01630b6SLiu Zhe SCUtil.setSCSheetNameByIndex(scDocument, (short) 1, changedname); 9821f666c7SLiu Zhe 9921f666c7SLiu Zhe // Save and reload document 100d01630b6SLiu Zhe SCUtil.saveFileAs(scComponent, "TestSpreadsheet", "ods"); 101d01630b6SLiu Zhe XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp, 102d01630b6SLiu Zhe scDocument, "TestSpreadsheet.ods"); 103d01630b6SLiu Zhe 104d01630b6SLiu Zhe scDocument = scDocumentTemp; 105d01630b6SLiu Zhe String sheetnameaftermove = SCUtil.getSCSheetNameByIndex(scDocument, 106d01630b6SLiu Zhe (short) 1); 10721f666c7SLiu Zhe 10821f666c7SLiu Zhe // Verify the changed Spreadsheet name 10921f666c7SLiu Zhe assertEquals("actual should equals SpeadsheetAfterChange", changedname, 110d01630b6SLiu Zhe sheetnameaftermove); 11121f666c7SLiu Zhe 112d01630b6SLiu Zhe scDocument.getSheets().removeByName(changedname); 11321f666c7SLiu Zhe 11421f666c7SLiu Zhe assertFalse("actual should equals false", 11521f666c7SLiu Zhe spreadsheets.hasByName(changedname)); 116d01630b6SLiu Zhe SCUtil.save(scDocumentTemp); 11721f666c7SLiu Zhe } 11821f666c7SLiu Zhe 119cfe4bce3SLi Feng Wang /** 120*cc57ba34Smseidel * Test copy and paste sheet 121cfe4bce3SLi Feng Wang */ 12221f666c7SLiu Zhe @Test copypastesheet()12321f666c7SLiu Zhe public void copypastesheet() throws Exception { 12421f666c7SLiu Zhe // Insert some value into cells 125d01630b6SLiu Zhe scDocument = SCUtil.getSCDocument(scComponent); 126d01630b6SLiu Zhe String souceSheetName = "sourcesheet"; 127d01630b6SLiu Zhe SCUtil.setSCSheetNameByIndex(scDocument, (short) 0, souceSheetName); 12821f666c7SLiu Zhe String[][] stringValues = { { "", "Jan", "Feb", "Mar", "Apr", "Mai" }, 12921f666c7SLiu Zhe { "Profit", "12.3", "43.2", "5.1", "76", "56.8" }, 13021f666c7SLiu Zhe { "Rival in business", "12.2", "12.6", "17.7", "20.4", "100" }, }; 131d01630b6SLiu Zhe XSpreadsheet sourceSpreadSheet = SCUtil.getSCSheetByName(scDocument, 132d01630b6SLiu Zhe souceSheetName); 13321f666c7SLiu Zhe // input strings into sheet1 134cfe4bce3SLi Feng Wang SCUtil.setTextToCellRange(sourceSpreadSheet, 0, 0, stringValues); 13521f666c7SLiu Zhe // copy the sheet from sourcesheet to copysheet 13621f666c7SLiu Zhe String newcopysheet = "copysheet"; 137d01630b6SLiu Zhe XSpreadsheets spreadsheets = scDocument.getSheets(); 138d01630b6SLiu Zhe spreadsheets.copyByName(souceSheetName, newcopysheet, (short) 2); 13921f666c7SLiu Zhe 14021f666c7SLiu Zhe // Save and reload document 141d01630b6SLiu Zhe SCUtil.saveFileAs(scComponent, "TestCopysheet", "xls"); 142d01630b6SLiu Zhe XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp, 143d01630b6SLiu Zhe scDocument, "TestCopysheet.xls"); 144d01630b6SLiu Zhe scDocument = scDocumentTemp; 145d01630b6SLiu Zhe 146d01630b6SLiu Zhe XSpreadsheet copysheet = SCUtil 147d01630b6SLiu Zhe .getSCSheetByIndex(scDocument, (short) 2); 148d01630b6SLiu Zhe String[][] CopystringValues = SCUtil.getTextFromCellRange(copysheet, 0, 149d01630b6SLiu Zhe 0, 5, 2); 150d01630b6SLiu Zhe assertArrayEquals("Expect string value should be stringValues", 151d01630b6SLiu Zhe stringValues, CopystringValues); 15221f666c7SLiu Zhe 15321f666c7SLiu Zhe } 15421f666c7SLiu Zhe 155cfe4bce3SLi Feng Wang /** 156cfe4bce3SLi Feng Wang * Test move sheet 157cfe4bce3SLi Feng Wang */ 15821f666c7SLiu Zhe @Test movesheet()15921f666c7SLiu Zhe public void movesheet() throws Exception { 16021f666c7SLiu Zhe 16121f666c7SLiu Zhe // new sc document 162d01630b6SLiu Zhe scDocument = SCUtil.getSCDocument(scComponent); 163d01630b6SLiu Zhe XSpreadsheets spreadsheets = scDocument.getSheets(); 16421f666c7SLiu Zhe 16521f666c7SLiu Zhe // change the first sheet name and input same value into the sheet cell 16621f666c7SLiu Zhe String sheetname = "sourcesheet"; 167d01630b6SLiu Zhe SCUtil.setSCSheetNameByIndex(scDocument, (short) 0, sheetname); 16821f666c7SLiu Zhe String[][] stringValues = { { "", "Jan", "Feb", "Mar", "Apr", "Mai" }, 16921f666c7SLiu Zhe { "Profit", "12.3", "43.2", "5.1", "76", "56.8" }, 17021f666c7SLiu Zhe { "Rival in business", "12.2", "12.6", "17.7", "20.4", "100" }, }; 171d01630b6SLiu Zhe XSpreadsheet movesheet = SCUtil 172d01630b6SLiu Zhe .getSCSheetByIndex(scDocument, (short) 0); 173cfe4bce3SLi Feng Wang SCUtil.setTextToCellRange(movesheet, 0, 0,stringValues); 17421f666c7SLiu Zhe 17521f666c7SLiu Zhe // Before move, get the 2nd sheet name 176d01630b6SLiu Zhe String secondSheetNameBeforeMove = SCUtil.getSCSheetNameByIndex( 177d01630b6SLiu Zhe scDocument, (short) 1); 17821f666c7SLiu Zhe 17921f666c7SLiu Zhe // move the first sheet 18021f666c7SLiu Zhe spreadsheets.moveByName(sheetname, (short) 2); 18121f666c7SLiu Zhe 18221f666c7SLiu Zhe // Save and reload document 183d01630b6SLiu Zhe SCUtil.saveFileAs(scComponent, "Testmovesheet", "xls"); 184d01630b6SLiu Zhe XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp, 185d01630b6SLiu Zhe scDocument, "Testmovesheet.xls"); 186d01630b6SLiu Zhe scDocument = scDocumentTemp; 18721f666c7SLiu Zhe 18821f666c7SLiu Zhe // After move, get the first sheet name, and verify it same as 2nd sheet 18921f666c7SLiu Zhe // name before move 190d01630b6SLiu Zhe String firstsheetnameAfterMove = SCUtil.getSCSheetNameByIndex( 191d01630b6SLiu Zhe scDocument, (short) 0); 19221f666c7SLiu Zhe assertEquals("Expect result should be Sheet2", 193d01630b6SLiu Zhe secondSheetNameBeforeMove, firstsheetnameAfterMove); 19421f666c7SLiu Zhe 19521f666c7SLiu Zhe // Get the target sheet name after move 196d01630b6SLiu Zhe String sheetnameAfterMove = SCUtil.getSCSheetNameByIndex(scDocument, 197d01630b6SLiu Zhe (short) 1); 19821f666c7SLiu Zhe assertEquals("Expect result should be sourcesheet", sheetname, 199d01630b6SLiu Zhe sheetnameAfterMove); 20021f666c7SLiu Zhe 20121f666c7SLiu Zhe // Check the cell value after move 202d01630b6SLiu Zhe XSpreadsheet sheetaftermove = SCUtil.getSCSheetByIndex(scDocument, 203d01630b6SLiu Zhe (short) 1); 204d01630b6SLiu Zhe String[][] stringValuesaftermove = SCUtil.getTextFromCellRange( 205d01630b6SLiu Zhe sheetaftermove, 0, 0, 5, 2); 206d01630b6SLiu Zhe 207d01630b6SLiu Zhe assertArrayEquals("Expect result should be stringValues", stringValues, 208d01630b6SLiu Zhe stringValuesaftermove); 20921f666c7SLiu Zhe } 21021f666c7SLiu Zhe 211cfe4bce3SLi Feng Wang /** 212cfe4bce3SLi Feng Wang * Test hide and show sheet 213cfe4bce3SLi Feng Wang */ 214d01630b6SLiu Zhe @Test hideShowSheet()215d01630b6SLiu Zhe public void hideShowSheet() throws Exception { 216d01630b6SLiu Zhe // Insert a sheet named hide sheet after first sheet 217d01630b6SLiu Zhe String sheetname = "hide sheet"; 218d01630b6SLiu Zhe scDocument = SCUtil.getSCDocument(scComponent); 219d01630b6SLiu Zhe XSpreadsheets spreadsheets = scDocument.getSheets(); 220d01630b6SLiu Zhe spreadsheets.insertNewByName(sheetname, (short) 1); 221d01630b6SLiu Zhe 222d01630b6SLiu Zhe // active the sheet second sheet "hide sheet" 223d01630b6SLiu Zhe XSpreadsheet secondSpreadSheet = SCUtil.getSCSheetByIndex(scDocument, 224d01630b6SLiu Zhe (short) 1); 225d01630b6SLiu Zhe SCUtil.setCurrentSheet(scDocument, secondSpreadSheet); 226d01630b6SLiu Zhe // get second sheet name and verify it should be "hide sheet" 227d01630b6SLiu Zhe assertEquals("expect active sheet name will be hide sheet", sheetname, 228d01630b6SLiu Zhe SCUtil.getSCSheetNameByIndex(scDocument, (short) 1)); 229d01630b6SLiu Zhe 230d01630b6SLiu Zhe // hide the sheet you insert 231d01630b6SLiu Zhe XPropertySet sheetPropertySet = (XPropertySet) UnoRuntime 232d01630b6SLiu Zhe .queryInterface(XPropertySet.class, secondSpreadSheet); 233d01630b6SLiu Zhe boolean isvisiable = false; 234d01630b6SLiu Zhe sheetPropertySet.setPropertyValue("IsVisible", isvisiable); 235d01630b6SLiu Zhe 236d01630b6SLiu Zhe // Save and reload document 237d01630b6SLiu Zhe SCUtil.saveFileAs(scComponent, "Testhideshowsheet", "xls"); 238d01630b6SLiu Zhe XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp, 239d01630b6SLiu Zhe scDocument, "Testhideshowsheet.xls"); 240d01630b6SLiu Zhe scDocument = scDocumentTemp; 241d01630b6SLiu Zhe 242d01630b6SLiu Zhe // get the active sheet name after hide sheet, it should be Sheet2 243d01630b6SLiu Zhe String sheet2Name = SCUtil.getSCSheetNameByIndex(scDocument, (short) 2); 244d01630b6SLiu Zhe String activesheetname = SCUtil.getSCActiveSheetName(scDocument); 245d01630b6SLiu Zhe assertEquals("Expect sheet name should be Sheet2", sheet2Name, 246d01630b6SLiu Zhe activesheetname); 247d01630b6SLiu Zhe 248d01630b6SLiu Zhe // show sheet "hide sheet" 249d01630b6SLiu Zhe sheetPropertySet = (XPropertySet) UnoRuntime.queryInterface( 250d01630b6SLiu Zhe XPropertySet.class, 251d01630b6SLiu Zhe SCUtil.getSCSheetByIndex(scDocument, (short) 1)); 252d01630b6SLiu Zhe isvisiable = true; 253d01630b6SLiu Zhe sheetPropertySet.setPropertyValue("IsVisible", isvisiable); 254d01630b6SLiu Zhe 255d01630b6SLiu Zhe // active sheet "hide sheet" 256d01630b6SLiu Zhe secondSpreadSheet = SCUtil.getSCSheetByIndex(scDocument, (short) 1); 257d01630b6SLiu Zhe SCUtil.setCurrentSheet(scDocument, secondSpreadSheet); 258d01630b6SLiu Zhe 259d01630b6SLiu Zhe // Get current active sheet name, verify it same as "hide sheet" 260d01630b6SLiu Zhe String currentactivesheetname = SCUtil.getSCActiveSheetName(scDocument); 261d01630b6SLiu Zhe assertEquals("Expect active sheet name is hidesheet", sheetname, 262d01630b6SLiu Zhe currentactivesheetname); 263d01630b6SLiu Zhe SCUtil.save(scDocument); 264d01630b6SLiu Zhe } 265d01630b6SLiu Zhe 266cfe4bce3SLi Feng Wang /** 267cfe4bce3SLi Feng Wang * Test sheet tab color 268cfe4bce3SLi Feng Wang */ 269d01630b6SLiu Zhe @Test sheetColor()270d01630b6SLiu Zhe public void sheetColor() throws Exception { 271d01630b6SLiu Zhe // get first sheet propertyset 272d01630b6SLiu Zhe scDocument = SCUtil.getSCDocument(scComponent); 273d01630b6SLiu Zhe XSpreadsheets spreadsheets = scDocument.getSheets(); 274d01630b6SLiu Zhe XSpreadsheet firstSpreadSheet = SCUtil.getSCSheetByIndex(scDocument, 275d01630b6SLiu Zhe (short) 0); 276d01630b6SLiu Zhe XPropertySet sheet1PropertySet = (XPropertySet) UnoRuntime 277d01630b6SLiu Zhe .queryInterface(XPropertySet.class, firstSpreadSheet); 278d01630b6SLiu Zhe 279cfe4bce3SLi Feng Wang // Set sheet tab color to 111 280d01630b6SLiu Zhe sheet1PropertySet.setPropertyValue("TabColor", 111); 281d01630b6SLiu Zhe 282d01630b6SLiu Zhe // copy the color sheet to new sheet 283d01630b6SLiu Zhe spreadsheets.copyByName( 284d01630b6SLiu Zhe SCUtil.getSCSheetNameByIndex(scDocument, (short) 0), 285d01630b6SLiu Zhe "newsheet", (short) 3); 286d01630b6SLiu Zhe 287d01630b6SLiu Zhe // Save and reopen the document 288d01630b6SLiu Zhe SCUtil.saveFileAs(scComponent, "Testcolorsheet", "ods"); 289d01630b6SLiu Zhe XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp, 290d01630b6SLiu Zhe scDocument, "Testcolorsheet.ods"); 291d01630b6SLiu Zhe scDocument = scDocumentTemp; 292d01630b6SLiu Zhe 293d01630b6SLiu Zhe // Get first sheet color 294d01630b6SLiu Zhe sheet1PropertySet = (XPropertySet) UnoRuntime.queryInterface( 295d01630b6SLiu Zhe XPropertySet.class, 296d01630b6SLiu Zhe SCUtil.getSCSheetByIndex(scDocument, (short) 0)); 297d01630b6SLiu Zhe int firstSheetcolorid = (Integer) sheet1PropertySet 298d01630b6SLiu Zhe .getPropertyValue("TabColor"); 299d01630b6SLiu Zhe 300*cc57ba34Smseidel // Get the copied sheet color 301d01630b6SLiu Zhe XPropertySet newsheetPropertySet = (XPropertySet) UnoRuntime 302d01630b6SLiu Zhe .queryInterface(XPropertySet.class, 303d01630b6SLiu Zhe SCUtil.getSCSheetByIndex(scDocument, (short) 3)); 304d01630b6SLiu Zhe int copySheetcolorid = (Integer) newsheetPropertySet 305d01630b6SLiu Zhe .getPropertyValue("TabColor"); 306d01630b6SLiu Zhe 307d01630b6SLiu Zhe // Verify first sheet color changed successfully 308d01630b6SLiu Zhe assertEquals("Expect color should be 111", 111, firstSheetcolorid); 309d01630b6SLiu Zhe 310d01630b6SLiu Zhe // Verify first sheet color same as copy sheet color 311d01630b6SLiu Zhe assertEquals("Expect color should be 111", firstSheetcolorid, 312d01630b6SLiu Zhe copySheetcolorid); 31321f666c7SLiu Zhe } 31421f666c7SLiu Zhe 315cfe4bce3SLi Feng Wang /** 316cfe4bce3SLi Feng Wang * test insert sheet from other file 317cfe4bce3SLi Feng Wang */ 318e20f1815SDamjan Jovanovic // FIXME: locks up on update link confirmation dialog. 319e20f1815SDamjan Jovanovic @Test(timeout = 15000) insertSheetFromfile()320d01630b6SLiu Zhe public void insertSheetFromfile() throws Exception { 321d01630b6SLiu Zhe // New a document source.xls, add value to 3 sheet 322d01630b6SLiu Zhe scDocument = SCUtil.getSCDocument(scComponent); 323d01630b6SLiu Zhe XSpreadsheets spreadsheets = scDocument.getSheets(); 324d01630b6SLiu Zhe XSpreadsheet firstSheet = SCUtil.getSCSheetByIndex(scDocument, 325d01630b6SLiu Zhe (short) 0); 326d01630b6SLiu Zhe XSpreadsheet secondSheet = SCUtil.getSCSheetByIndex(scDocument, 327d01630b6SLiu Zhe (short) 1); 328d01630b6SLiu Zhe XSpreadsheet thirdSheet = SCUtil.getSCSheetByIndex(scDocument, 329d01630b6SLiu Zhe (short) 2); 330d01630b6SLiu Zhe SCUtil.setFormulaToCell(firstSheet, 1, 2, "=2*2"); 331d01630b6SLiu Zhe SCUtil.setFormulaToCell(secondSheet, 1, 2, "=2*2"); 332d01630b6SLiu Zhe SCUtil.setFormulaToCell(thirdSheet, 1, 2, "=2*2"); 333d01630b6SLiu Zhe 334d01630b6SLiu Zhe // Save and close this document 335d01630b6SLiu Zhe SCUtil.saveFileAs(scComponent, "source", "xls"); 336d01630b6SLiu Zhe SCUtil.closeFile(scDocument); 337d01630b6SLiu Zhe 338d01630b6SLiu Zhe // get source document URL 339dded528dSLiu Zhe String SourcestoreUrl = Testspace.getUrl("output/sc/" + "source" + "." 340d01630b6SLiu Zhe + "xls"); 341d01630b6SLiu Zhe 342d01630b6SLiu Zhe // New a document 343d01630b6SLiu Zhe scComponent = unoApp.newDocument("scalc"); 344d01630b6SLiu Zhe scDocument = SCUtil.getSCDocument(scComponent); 345d01630b6SLiu Zhe spreadsheets = scDocument.getSheets(); 346d01630b6SLiu Zhe // Insert firstexternalsheet sheet, link with Sheet1 in source document 347d01630b6SLiu Zhe // and the link mode is NORMAL 348d01630b6SLiu Zhe spreadsheets.insertNewByName("firstexternalsheet", (short) 3); 349d01630b6SLiu Zhe XSpreadsheet firstexternalsheet = SCUtil.getSCSheetByIndex(scDocument, 350d01630b6SLiu Zhe (short) 3); 351d01630b6SLiu Zhe XSheetLinkable xfirstSheetLinkable = (XSheetLinkable) UnoRuntime 352d01630b6SLiu Zhe .queryInterface(XSheetLinkable.class, firstexternalsheet); 353d01630b6SLiu Zhe xfirstSheetLinkable.link(SourcestoreUrl, "", "MS Excel 97", "", 354d01630b6SLiu Zhe SheetLinkMode.NORMAL); 355d01630b6SLiu Zhe 356d01630b6SLiu Zhe // Insert secondexternalsheet sheet, link with Sheet2 in source document 357d01630b6SLiu Zhe // and the link mode is VALUE 358d01630b6SLiu Zhe spreadsheets.insertNewByName("secondexternalsheet", (short) 4); 359d01630b6SLiu Zhe XSpreadsheet secondexternalsheet = SCUtil.getSCSheetByIndex(scDocument, 360d01630b6SLiu Zhe (short) 4); 361d01630b6SLiu Zhe XSheetLinkable xsecondSheetLinkable = (XSheetLinkable) UnoRuntime 362d01630b6SLiu Zhe .queryInterface(XSheetLinkable.class, secondexternalsheet); 363d01630b6SLiu Zhe xsecondSheetLinkable.link(SourcestoreUrl, "Sheet2", "MS Excel 97", "", 364d01630b6SLiu Zhe SheetLinkMode.VALUE); 365d01630b6SLiu Zhe 366d01630b6SLiu Zhe // Insert secondexternalsheet sheet, link with Sheet2 in source document 367d01630b6SLiu Zhe // and the link mode is NONE 368d01630b6SLiu Zhe spreadsheets.insertNewByName("thirdexternalsheet", (short) 5); 369d01630b6SLiu Zhe XSpreadsheet thirdexternalsheet = SCUtil.getSCSheetByIndex(scDocument, 370d01630b6SLiu Zhe (short) 5); 371d01630b6SLiu Zhe XSheetLinkable xthirdSheetLinkable = (XSheetLinkable) UnoRuntime 372d01630b6SLiu Zhe .queryInterface(XSheetLinkable.class, thirdexternalsheet); 373d01630b6SLiu Zhe xthirdSheetLinkable.link(SourcestoreUrl, "Sheet3", "MS Excel 97", "", 374d01630b6SLiu Zhe SheetLinkMode.NONE); 375d01630b6SLiu Zhe 376d01630b6SLiu Zhe // Verify firstexternalsheet 377d01630b6SLiu Zhe assertEquals("Expect formula should be =2*2", "=2*2", 378d01630b6SLiu Zhe SCUtil.getFormulaFromCell(firstexternalsheet, 1, 2)); 379d01630b6SLiu Zhe assertEquals("Expect formula result should be 4", "4", 380d01630b6SLiu Zhe SCUtil.getTextFromCell(firstexternalsheet, 1, 2)); 381d01630b6SLiu Zhe 382d01630b6SLiu Zhe // Verify secondexternalsheet 383d01630b6SLiu Zhe assertEquals("Expect formula should be 4", "4", 384d01630b6SLiu Zhe SCUtil.getFormulaFromCell(secondexternalsheet, 1, 2)); 385d01630b6SLiu Zhe assertEquals("Expect formula result should be 4", "4", 386d01630b6SLiu Zhe SCUtil.getTextFromCell(secondexternalsheet, 1, 2)); 387d01630b6SLiu Zhe 388d01630b6SLiu Zhe // Verify thirdexternalsheet 389d01630b6SLiu Zhe assertEquals("Expect formula should be blank", "", 390d01630b6SLiu Zhe SCUtil.getFormulaFromCell(thirdexternalsheet, 1, 2)); 391d01630b6SLiu Zhe assertEquals("Expect formula result should be blank", "", 392d01630b6SLiu Zhe SCUtil.getTextFromCell(thirdexternalsheet, 1, 2)); 393d01630b6SLiu Zhe 394d01630b6SLiu Zhe // save document and verify the linked sheet again 395d01630b6SLiu Zhe SCUtil.saveFileAs(scComponent, "linked", "ods"); 396d01630b6SLiu Zhe XSpreadsheetDocument tempscDocument = SCUtil.reloadFile(unoApp, 397d01630b6SLiu Zhe scDocument, "linked.ods"); 398d01630b6SLiu Zhe scDocument = tempscDocument; 399d01630b6SLiu Zhe firstexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 3); 400d01630b6SLiu Zhe secondexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 4); 401d01630b6SLiu Zhe thirdexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 5); 402d01630b6SLiu Zhe 403d01630b6SLiu Zhe // Verify firstexternalsheet 404d01630b6SLiu Zhe assertEquals("Expect formula should be =2*2", "=2*2", 405d01630b6SLiu Zhe SCUtil.getFormulaFromCell(firstexternalsheet, 1, 2)); 406d01630b6SLiu Zhe assertEquals("Expect formula result should be 4", "4", 407d01630b6SLiu Zhe SCUtil.getTextFromCell(firstexternalsheet, 1, 2)); 408d01630b6SLiu Zhe 409d01630b6SLiu Zhe // Verify secondexternalsheet 410d01630b6SLiu Zhe assertEquals("Expect formula should be 4", "4", 411d01630b6SLiu Zhe SCUtil.getFormulaFromCell(secondexternalsheet, 1, 2)); 412d01630b6SLiu Zhe assertEquals("Expect formula result should be 4", "4", 413d01630b6SLiu Zhe SCUtil.getTextFromCell(secondexternalsheet, 1, 2)); 414d01630b6SLiu Zhe 415d01630b6SLiu Zhe // Verify thirdexternalsheet 416d01630b6SLiu Zhe assertEquals("Expect formula should be blank", "", 417d01630b6SLiu Zhe SCUtil.getFormulaFromCell(thirdexternalsheet, 1, 2)); 418d01630b6SLiu Zhe assertEquals("Expect formula result should be blank", "", 419d01630b6SLiu Zhe SCUtil.getTextFromCell(thirdexternalsheet, 1, 2)); 420d01630b6SLiu Zhe 421*cc57ba34Smseidel // save and close document 422d01630b6SLiu Zhe SCUtil.save(scDocument); 423d01630b6SLiu Zhe SCUtil.closeFile(scDocument); 424d01630b6SLiu Zhe 425*cc57ba34Smseidel // Open source document and change the value in source document 426d01630b6SLiu Zhe XSpreadsheetDocument sourcescDocument = SCUtil.reloadFile(unoApp, 427d01630b6SLiu Zhe scDocument, "source.xls"); 428d01630b6SLiu Zhe firstSheet = SCUtil.getSCSheetByIndex(sourcescDocument, (short) 0); 429d01630b6SLiu Zhe secondSheet = SCUtil.getSCSheetByIndex(sourcescDocument, (short) 1); 430d01630b6SLiu Zhe thirdSheet = SCUtil.getSCSheetByIndex(sourcescDocument, (short) 2); 431d01630b6SLiu Zhe SCUtil.setFormulaToCell(firstSheet, 1, 2, "=3*3"); 432d01630b6SLiu Zhe SCUtil.setFormulaToCell(secondSheet, 1, 2, "=3*3"); 433d01630b6SLiu Zhe SCUtil.setFormulaToCell(thirdSheet, 1, 2, "=3*3"); 434d01630b6SLiu Zhe SCUtil.save(sourcescDocument); 435d01630b6SLiu Zhe SCUtil.closeFile(sourcescDocument); 436d01630b6SLiu Zhe 437*cc57ba34Smseidel // Open link document 438d01630b6SLiu Zhe tempscDocument = SCUtil.reloadFile(unoApp, scDocument, "linked.ods"); 439d01630b6SLiu Zhe scDocument = tempscDocument; 440d01630b6SLiu Zhe spreadsheets = scDocument.getSheets(); 441d01630b6SLiu Zhe 442d01630b6SLiu Zhe firstexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 3); 443d01630b6SLiu Zhe secondexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 4); 444d01630b6SLiu Zhe thirdexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 5); 445d01630b6SLiu Zhe 446*cc57ba34Smseidel // get Object SheetLinks for document 447d01630b6SLiu Zhe XPropertySet sheetpropertyset = (XPropertySet) UnoRuntime 448d01630b6SLiu Zhe .queryInterface(XPropertySet.class, scDocument); 449d01630b6SLiu Zhe Object sheetLinks = sheetpropertyset.getPropertyValue("SheetLinks"); 450d01630b6SLiu Zhe 451d01630b6SLiu Zhe XIndexAccess xsheetlinks = (XIndexAccess) UnoRuntime.queryInterface( 452d01630b6SLiu Zhe XIndexAccess.class, sheetLinks); 453d01630b6SLiu Zhe 454*cc57ba34Smseidel // Refresh all links 455d01630b6SLiu Zhe for (int i = 0; i < xsheetlinks.getCount(); i++) { 456d01630b6SLiu Zhe Object sheetlink = xsheetlinks.getByIndex(i); 457d01630b6SLiu Zhe XRefreshable xsheetRefreshable = (XRefreshable) UnoRuntime 458d01630b6SLiu Zhe .queryInterface(XRefreshable.class, sheetlink); 459d01630b6SLiu Zhe xsheetRefreshable.refresh(); 460d01630b6SLiu Zhe } 461d01630b6SLiu Zhe 462d01630b6SLiu Zhe // Verify firstexternalsheet 463d01630b6SLiu Zhe assertEquals("Expect formula should be =3*3", "=3*3", 464d01630b6SLiu Zhe SCUtil.getFormulaFromCell(firstexternalsheet, 1, 2)); 465d01630b6SLiu Zhe assertEquals("Expect formula result should be 9", "9", 466d01630b6SLiu Zhe SCUtil.getTextFromCell(firstexternalsheet, 1, 2)); 467d01630b6SLiu Zhe 468d01630b6SLiu Zhe // Verify secondexternalsheet 469d01630b6SLiu Zhe assertEquals("Expect formula should be 9", "9", 470d01630b6SLiu Zhe SCUtil.getFormulaFromCell(secondexternalsheet, 1, 2)); 471d01630b6SLiu Zhe assertEquals("Expect formula result should be 9", "9", 472d01630b6SLiu Zhe SCUtil.getTextFromCell(secondexternalsheet, 1, 2)); 473d01630b6SLiu Zhe 474d01630b6SLiu Zhe // Verify thirdexternalsheet 475d01630b6SLiu Zhe assertEquals("Expect formula should be blank", "", 476d01630b6SLiu Zhe SCUtil.getFormulaFromCell(thirdexternalsheet, 1, 2)); 477d01630b6SLiu Zhe assertEquals("Expect formula result should be blank", "", 478d01630b6SLiu Zhe SCUtil.getTextFromCell(thirdexternalsheet, 1, 2)); 479d01630b6SLiu Zhe 480*cc57ba34Smseidel // Save the document before close 481d01630b6SLiu Zhe SCUtil.save(scDocument); 482d01630b6SLiu Zhe 48321f666c7SLiu Zhe } 484d01630b6SLiu Zhe 48521f666c7SLiu Zhe } 486