1 /* Licensed to the Apache Software Foundation (ASF) under one 2 * or more contributor license agreements. See the NOTICE file 3 * distributed with this work for additional information 4 * regarding copyright ownership. The ASF licenses this file 5 * to you under the Apache License, Version 2.0 (the 6 * "License"); you may not use this file except in compliance 7 * with the License. You may obtain a copy of the License at 8 * 9 * http://www.apache.org/licenses/LICENSE-2.0 10 * 11 * Unless required by applicable law or agreed to in writing, 12 * software distributed under the License is distributed on an 13 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY 14 * KIND, either express or implied. See the License for the 15 * specific language governing permissions and limitations 16 * under the License. 17 * 18 *************************************************************/ 19 20 package fvt.uno.sc.sheet; 21 22 import static org.junit.Assert.*; 23 import org.junit.After; 24 import org.junit.AfterClass; 25 import org.junit.Before; 26 import org.junit.BeforeClass; 27 import org.junit.Test; 28 import org.openoffice.test.common.FileUtil; 29 import org.openoffice.test.common.Testspace; 30 import org.openoffice.test.uno.UnoApp; 31 import testlib.uno.SCUtil; 32 import com.sun.star.beans.Property; 33 import com.sun.star.beans.PropertyAttribute; 34 import com.sun.star.beans.PropertyValue; 35 import com.sun.star.beans.XPropertySet; 36 import com.sun.star.beans.XPropertySetInfo; 37 import com.sun.star.container.XEnumerationAccess; 38 import com.sun.star.container.XIndexAccess; 39 import com.sun.star.container.XNamed; 40 import com.sun.star.frame.XModel; 41 import com.sun.star.frame.XStorable; 42 import com.sun.star.io.IOException; 43 import com.sun.star.lang.IndexOutOfBoundsException; 44 import com.sun.star.lang.WrappedTargetException; 45 import com.sun.star.lang.XComponent; 46 import com.sun.star.sheet.SheetLinkMode; 47 import com.sun.star.sheet.XCalculatable; 48 import com.sun.star.sheet.XExternalDocLink; 49 import com.sun.star.sheet.XExternalDocLinks; 50 import com.sun.star.sheet.XSheetLinkable; 51 import com.sun.star.sheet.XSpreadsheet; 52 import com.sun.star.sheet.XSpreadsheetDocument; 53 import com.sun.star.sheet.XSpreadsheetView; 54 import com.sun.star.sheet.XSpreadsheets; 55 import com.sun.star.table.XCell; 56 import com.sun.star.uno.UnoRuntime; 57 import com.sun.star.util.XRefreshable; 58 59 public class SheetBasicTest { 60 UnoApp unoApp = new UnoApp(); 61 62 XSpreadsheetDocument scDocument = null; 63 XComponent scComponent = null; 64 65 @BeforeClass 66 public static void setUpBeforeClass() throws Exception { 67 68 } 69 70 @AfterClass 71 public static void tearDownAfterClass() throws Exception { 72 } 73 74 @Before 75 public void setUp() throws Exception { 76 unoApp.start(); 77 // New a SC document 78 scComponent = unoApp.newDocument("scalc"); 79 } 80 81 @After 82 public void tearDown() throws Exception { 83 unoApp.closeDocument(scComponent); 84 unoApp.close(); 85 } 86 87 @Test 88 public void insertRenameDeleteSheet() throws Exception { 89 // Insert a sheet named aa after first sheet 90 String sheetname = "aa"; 91 scDocument = SCUtil.getSCDocument(scComponent); 92 XSpreadsheets spreadsheets = scDocument.getSheets(); 93 spreadsheets.insertNewByName(sheetname, (short) 1); 94 95 // active the sheet second sheet aa 96 XSpreadsheet newSpreadSheet = SCUtil.getSCSheetByIndex(scDocument, 97 (short) 1); 98 SCUtil.setCurrentSheet(scDocument, newSpreadSheet); 99 100 // get the new speadsheet name 101 assertEquals("actual should equals aa", sheetname, 102 SCUtil.getSCSheetNameByIndex(scDocument, (short) 1)); 103 104 // Change the Spreadsheet name 105 String changedname = "SpeadsheetAfterChange"; 106 SCUtil.setSCSheetNameByIndex(scDocument, (short) 1, changedname); 107 108 // Save and reload document 109 SCUtil.saveFileAs(scComponent, "TestSpreadsheet", "ods"); 110 XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp, 111 scDocument, "TestSpreadsheet.ods"); 112 113 scDocument = scDocumentTemp; 114 String sheetnameaftermove = SCUtil.getSCSheetNameByIndex(scDocument, 115 (short) 1); 116 117 // Verify the changed Spreadsheet name 118 assertEquals("actual should equals SpeadsheetAfterChange", changedname, 119 sheetnameaftermove); 120 121 scDocument.getSheets().removeByName(changedname); 122 123 assertFalse("actual should equals false", 124 spreadsheets.hasByName(changedname)); 125 SCUtil.save(scDocumentTemp); 126 } 127 128 @Test 129 public void copypastesheet() throws Exception { 130 // Insert some value into cells 131 scDocument = SCUtil.getSCDocument(scComponent); 132 String souceSheetName = "sourcesheet"; 133 SCUtil.setSCSheetNameByIndex(scDocument, (short) 0, souceSheetName); 134 String[][] stringValues = { { "", "Jan", "Feb", "Mar", "Apr", "Mai" }, 135 { "Profit", "12.3", "43.2", "5.1", "76", "56.8" }, 136 { "Rival in business", "12.2", "12.6", "17.7", "20.4", "100" }, }; 137 XSpreadsheet sourceSpreadSheet = SCUtil.getSCSheetByName(scDocument, 138 souceSheetName); 139 // input strings into sheet1 140 SCUtil.setTextToCellRange(sourceSpreadSheet, 0, 0, 5, 2, stringValues); 141 // copy the sheet from sourcesheet to copysheet 142 String newcopysheet = "copysheet"; 143 XSpreadsheets spreadsheets = scDocument.getSheets(); 144 spreadsheets.copyByName(souceSheetName, newcopysheet, (short) 2); 145 146 // Save and reload document 147 SCUtil.saveFileAs(scComponent, "TestCopysheet", "xls"); 148 XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp, 149 scDocument, "TestCopysheet.xls"); 150 scDocument = scDocumentTemp; 151 152 XSpreadsheet copysheet = SCUtil 153 .getSCSheetByIndex(scDocument, (short) 2); 154 String[][] CopystringValues = SCUtil.getTextFromCellRange(copysheet, 0, 155 0, 5, 2); 156 assertArrayEquals("Expect string value should be stringValues", 157 stringValues, CopystringValues); 158 159 } 160 161 @Test 162 public void movesheet() throws Exception { 163 164 // new sc document 165 scDocument = SCUtil.getSCDocument(scComponent); 166 XSpreadsheets spreadsheets = scDocument.getSheets(); 167 168 // change the first sheet name and input same value into the sheet cell 169 String sheetname = "sourcesheet"; 170 SCUtil.setSCSheetNameByIndex(scDocument, (short) 0, sheetname); 171 String[][] stringValues = { { "", "Jan", "Feb", "Mar", "Apr", "Mai" }, 172 { "Profit", "12.3", "43.2", "5.1", "76", "56.8" }, 173 { "Rival in business", "12.2", "12.6", "17.7", "20.4", "100" }, }; 174 XSpreadsheet movesheet = SCUtil 175 .getSCSheetByIndex(scDocument, (short) 0); 176 SCUtil.setTextToCellRange(movesheet, 0, 0, 5, 2, stringValues); 177 178 // Before move, get the 2nd sheet name 179 String secondSheetNameBeforeMove = SCUtil.getSCSheetNameByIndex( 180 scDocument, (short) 1); 181 182 // move the first sheet 183 spreadsheets.moveByName(sheetname, (short) 2); 184 185 // Save and reload document 186 SCUtil.saveFileAs(scComponent, "Testmovesheet", "xls"); 187 XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp, 188 scDocument, "Testmovesheet.xls"); 189 scDocument = scDocumentTemp; 190 191 // After move, get the first sheet name, and verify it same as 2nd sheet 192 // name before move 193 String firstsheetnameAfterMove = SCUtil.getSCSheetNameByIndex( 194 scDocument, (short) 0); 195 assertEquals("Expect result should be Sheet2", 196 secondSheetNameBeforeMove, firstsheetnameAfterMove); 197 198 // Get the target sheet name after move 199 String sheetnameAfterMove = SCUtil.getSCSheetNameByIndex(scDocument, 200 (short) 1); 201 assertEquals("Expect result should be sourcesheet", sheetname, 202 sheetnameAfterMove); 203 204 // Check the cell value after move 205 XSpreadsheet sheetaftermove = SCUtil.getSCSheetByIndex(scDocument, 206 (short) 1); 207 String[][] stringValuesaftermove = SCUtil.getTextFromCellRange( 208 sheetaftermove, 0, 0, 5, 2); 209 210 assertArrayEquals("Expect result should be stringValues", stringValues, 211 stringValuesaftermove); 212 213 } 214 215 @Test 216 public void hideShowSheet() throws Exception { 217 // Insert a sheet named hide sheet after first sheet 218 String sheetname = "hide sheet"; 219 scDocument = SCUtil.getSCDocument(scComponent); 220 XSpreadsheets spreadsheets = scDocument.getSheets(); 221 spreadsheets.insertNewByName(sheetname, (short) 1); 222 223 // active the sheet second sheet "hide sheet" 224 XSpreadsheet secondSpreadSheet = SCUtil.getSCSheetByIndex(scDocument, 225 (short) 1); 226 SCUtil.setCurrentSheet(scDocument, secondSpreadSheet); 227 // get second sheet name and verify it should be "hide sheet" 228 assertEquals("expect active sheet name will be hide sheet", sheetname, 229 SCUtil.getSCSheetNameByIndex(scDocument, (short) 1)); 230 231 // hide the sheet you insert 232 XPropertySet sheetPropertySet = (XPropertySet) UnoRuntime 233 .queryInterface(XPropertySet.class, secondSpreadSheet); 234 boolean isvisiable = false; 235 sheetPropertySet.setPropertyValue("IsVisible", isvisiable); 236 237 // Save and reload document 238 SCUtil.saveFileAs(scComponent, "Testhideshowsheet", "xls"); 239 XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp, 240 scDocument, "Testhideshowsheet.xls"); 241 scDocument = scDocumentTemp; 242 243 // get the active sheet name after hide sheet, it should be Sheet2 244 String sheet2Name = SCUtil.getSCSheetNameByIndex(scDocument, (short) 2); 245 String activesheetname = SCUtil.getSCActiveSheetName(scDocument); 246 assertEquals("Expect sheet name should be Sheet2", sheet2Name, 247 activesheetname); 248 249 // show sheet "hide sheet" 250 sheetPropertySet = (XPropertySet) UnoRuntime.queryInterface( 251 XPropertySet.class, 252 SCUtil.getSCSheetByIndex(scDocument, (short) 1)); 253 isvisiable = true; 254 sheetPropertySet.setPropertyValue("IsVisible", isvisiable); 255 256 // active sheet "hide sheet" 257 secondSpreadSheet = SCUtil.getSCSheetByIndex(scDocument, (short) 1); 258 SCUtil.setCurrentSheet(scDocument, secondSpreadSheet); 259 260 // Get current active sheet name, verify it same as "hide sheet" 261 String currentactivesheetname = SCUtil.getSCActiveSheetName(scDocument); 262 assertEquals("Expect active sheet name is hidesheet", sheetname, 263 currentactivesheetname); 264 SCUtil.save(scDocument); 265 } 266 267 @Test 268 public void sheetColor() throws Exception { 269 // get first sheet propertyset 270 scDocument = SCUtil.getSCDocument(scComponent); 271 XSpreadsheets spreadsheets = scDocument.getSheets(); 272 XSpreadsheet firstSpreadSheet = SCUtil.getSCSheetByIndex(scDocument, 273 (short) 0); 274 XPropertySet sheet1PropertySet = (XPropertySet) UnoRuntime 275 .queryInterface(XPropertySet.class, firstSpreadSheet); 276 277 // Set tabcolor to 111 278 sheet1PropertySet.setPropertyValue("TabColor", 111); 279 280 // copy the color sheet to new sheet 281 spreadsheets.copyByName( 282 SCUtil.getSCSheetNameByIndex(scDocument, (short) 0), 283 "newsheet", (short) 3); 284 285 // Save and reopen the document 286 SCUtil.saveFileAs(scComponent, "Testcolorsheet", "ods"); 287 XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp, 288 scDocument, "Testcolorsheet.ods"); 289 scDocument = scDocumentTemp; 290 291 // Get first sheet color 292 sheet1PropertySet = (XPropertySet) UnoRuntime.queryInterface( 293 XPropertySet.class, 294 SCUtil.getSCSheetByIndex(scDocument, (short) 0)); 295 int firstSheetcolorid = (Integer) sheet1PropertySet 296 .getPropertyValue("TabColor"); 297 298 // Get the copyed sheet color 299 XPropertySet newsheetPropertySet = (XPropertySet) UnoRuntime 300 .queryInterface(XPropertySet.class, 301 SCUtil.getSCSheetByIndex(scDocument, (short) 3)); 302 int copySheetcolorid = (Integer) newsheetPropertySet 303 .getPropertyValue("TabColor"); 304 305 // Verify first sheet color changed successfully 306 assertEquals("Expect color should be 111", 111, firstSheetcolorid); 307 308 // Verify first sheet color same as copy sheet color 309 assertEquals("Expect color should be 111", firstSheetcolorid, 310 copySheetcolorid); 311 } 312 313 @Test 314 public void insertSheetFromfile() throws Exception { 315 // New a document source.xls, add value to 3 sheet 316 scDocument = SCUtil.getSCDocument(scComponent); 317 XSpreadsheets spreadsheets = scDocument.getSheets(); 318 XSpreadsheet firstSheet = SCUtil.getSCSheetByIndex(scDocument, 319 (short) 0); 320 XSpreadsheet secondSheet = SCUtil.getSCSheetByIndex(scDocument, 321 (short) 1); 322 XSpreadsheet thirdSheet = SCUtil.getSCSheetByIndex(scDocument, 323 (short) 2); 324 SCUtil.setFormulaToCell(firstSheet, 1, 2, "=2*2"); 325 SCUtil.setFormulaToCell(secondSheet, 1, 2, "=2*2"); 326 SCUtil.setFormulaToCell(thirdSheet, 1, 2, "=2*2"); 327 328 // Save and close this document 329 SCUtil.saveFileAs(scComponent, "source", "xls"); 330 SCUtil.closeFile(scDocument); 331 332 // get source document URL 333 String SourcestoreUrl = Testspace.getUrl("output/sc/" + "source" + "." 334 + "xls"); 335 336 // New a document 337 scComponent = unoApp.newDocument("scalc"); 338 scDocument = SCUtil.getSCDocument(scComponent); 339 spreadsheets = scDocument.getSheets(); 340 // Insert firstexternalsheet sheet, link with Sheet1 in source document 341 // and the link mode is NORMAL 342 spreadsheets.insertNewByName("firstexternalsheet", (short) 3); 343 XSpreadsheet firstexternalsheet = SCUtil.getSCSheetByIndex(scDocument, 344 (short) 3); 345 XSheetLinkable xfirstSheetLinkable = (XSheetLinkable) UnoRuntime 346 .queryInterface(XSheetLinkable.class, firstexternalsheet); 347 xfirstSheetLinkable.link(SourcestoreUrl, "", "MS Excel 97", "", 348 SheetLinkMode.NORMAL); 349 350 // Insert secondexternalsheet sheet, link with Sheet2 in source document 351 // and the link mode is VALUE 352 spreadsheets.insertNewByName("secondexternalsheet", (short) 4); 353 XSpreadsheet secondexternalsheet = SCUtil.getSCSheetByIndex(scDocument, 354 (short) 4); 355 XSheetLinkable xsecondSheetLinkable = (XSheetLinkable) UnoRuntime 356 .queryInterface(XSheetLinkable.class, secondexternalsheet); 357 xsecondSheetLinkable.link(SourcestoreUrl, "Sheet2", "MS Excel 97", "", 358 SheetLinkMode.VALUE); 359 360 // Insert secondexternalsheet sheet, link with Sheet2 in source document 361 // and the link mode is NONE 362 spreadsheets.insertNewByName("thirdexternalsheet", (short) 5); 363 XSpreadsheet thirdexternalsheet = SCUtil.getSCSheetByIndex(scDocument, 364 (short) 5); 365 XSheetLinkable xthirdSheetLinkable = (XSheetLinkable) UnoRuntime 366 .queryInterface(XSheetLinkable.class, thirdexternalsheet); 367 xthirdSheetLinkable.link(SourcestoreUrl, "Sheet3", "MS Excel 97", "", 368 SheetLinkMode.NONE); 369 370 // Verify firstexternalsheet 371 assertEquals("Expect formula should be =2*2", "=2*2", 372 SCUtil.getFormulaFromCell(firstexternalsheet, 1, 2)); 373 assertEquals("Expect formula result should be 4", "4", 374 SCUtil.getTextFromCell(firstexternalsheet, 1, 2)); 375 376 // Verify secondexternalsheet 377 assertEquals("Expect formula should be 4", "4", 378 SCUtil.getFormulaFromCell(secondexternalsheet, 1, 2)); 379 assertEquals("Expect formula result should be 4", "4", 380 SCUtil.getTextFromCell(secondexternalsheet, 1, 2)); 381 382 // Verify thirdexternalsheet 383 assertEquals("Expect formula should be blank", "", 384 SCUtil.getFormulaFromCell(thirdexternalsheet, 1, 2)); 385 assertEquals("Expect formula result should be blank", "", 386 SCUtil.getTextFromCell(thirdexternalsheet, 1, 2)); 387 388 // save document and verify the linked sheet again 389 SCUtil.saveFileAs(scComponent, "linked", "ods"); 390 XSpreadsheetDocument tempscDocument = SCUtil.reloadFile(unoApp, 391 scDocument, "linked.ods"); 392 scDocument = tempscDocument; 393 firstexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 3); 394 secondexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 4); 395 thirdexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 5); 396 397 // Verify firstexternalsheet 398 assertEquals("Expect formula should be =2*2", "=2*2", 399 SCUtil.getFormulaFromCell(firstexternalsheet, 1, 2)); 400 assertEquals("Expect formula result should be 4", "4", 401 SCUtil.getTextFromCell(firstexternalsheet, 1, 2)); 402 403 // Verify secondexternalsheet 404 assertEquals("Expect formula should be 4", "4", 405 SCUtil.getFormulaFromCell(secondexternalsheet, 1, 2)); 406 assertEquals("Expect formula result should be 4", "4", 407 SCUtil.getTextFromCell(secondexternalsheet, 1, 2)); 408 409 // Verify thirdexternalsheet 410 assertEquals("Expect formula should be blank", "", 411 SCUtil.getFormulaFromCell(thirdexternalsheet, 1, 2)); 412 assertEquals("Expect formula result should be blank", "", 413 SCUtil.getTextFromCell(thirdexternalsheet, 1, 2)); 414 415 //save and close document 416 SCUtil.save(scDocument); 417 SCUtil.closeFile(scDocument); 418 419 //Open souce document and change the value in souce document 420 XSpreadsheetDocument sourcescDocument = SCUtil.reloadFile(unoApp, 421 scDocument, "source.xls"); 422 firstSheet = SCUtil.getSCSheetByIndex(sourcescDocument, (short) 0); 423 secondSheet = SCUtil.getSCSheetByIndex(sourcescDocument, (short) 1); 424 thirdSheet = SCUtil.getSCSheetByIndex(sourcescDocument, (short) 2); 425 SCUtil.setFormulaToCell(firstSheet, 1, 2, "=3*3"); 426 SCUtil.setFormulaToCell(secondSheet, 1, 2, "=3*3"); 427 SCUtil.setFormulaToCell(thirdSheet, 1, 2, "=3*3"); 428 SCUtil.save(sourcescDocument); 429 SCUtil.closeFile(sourcescDocument); 430 431 //Open link document 432 tempscDocument = SCUtil.reloadFile(unoApp, scDocument, "linked.ods"); 433 scDocument = tempscDocument; 434 spreadsheets = scDocument.getSheets(); 435 436 firstexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 3); 437 secondexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 4); 438 thirdexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 5); 439 440 //get Object SheetLinks for document 441 XPropertySet sheetpropertyset = (XPropertySet) UnoRuntime 442 .queryInterface(XPropertySet.class, scDocument); 443 Object sheetLinks = sheetpropertyset.getPropertyValue("SheetLinks"); 444 445 XIndexAccess xsheetlinks = (XIndexAccess) UnoRuntime.queryInterface( 446 XIndexAccess.class, sheetLinks); 447 448 //Refresh all links 449 for (int i = 0; i < xsheetlinks.getCount(); i++) { 450 Object sheetlink = xsheetlinks.getByIndex(i); 451 XRefreshable xsheetRefreshable = (XRefreshable) UnoRuntime 452 .queryInterface(XRefreshable.class, sheetlink); 453 xsheetRefreshable.refresh(); 454 } 455 456 // Verify firstexternalsheet 457 assertEquals("Expect formula should be =3*3", "=3*3", 458 SCUtil.getFormulaFromCell(firstexternalsheet, 1, 2)); 459 assertEquals("Expect formula result should be 9", "9", 460 SCUtil.getTextFromCell(firstexternalsheet, 1, 2)); 461 462 // Verify secondexternalsheet 463 assertEquals("Expect formula should be 9", "9", 464 SCUtil.getFormulaFromCell(secondexternalsheet, 1, 2)); 465 assertEquals("Expect formula result should be 9", "9", 466 SCUtil.getTextFromCell(secondexternalsheet, 1, 2)); 467 468 // Verify thirdexternalsheet 469 assertEquals("Expect formula should be blank", "", 470 SCUtil.getFormulaFromCell(thirdexternalsheet, 1, 2)); 471 assertEquals("Expect formula result should be blank", "", 472 SCUtil.getTextFromCell(thirdexternalsheet, 1, 2)); 473 474 //Save the document before close 475 SCUtil.save(scDocument); 476 477 } 478 479 } 480