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