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.Ignore; 30 import org.junit.Test; 31 import org.openoffice.test.common.Testspace; 32 import org.openoffice.test.uno.UnoApp; 33 import testlib.uno.SCUtil; 34 import com.sun.star.beans.XPropertySet; 35 import com.sun.star.container.XIndexAccess; 36 import com.sun.star.lang.XComponent; 37 import com.sun.star.sheet.SheetLinkMode; 38 import com.sun.star.sheet.XSheetLinkable; 39 import com.sun.star.sheet.XSpreadsheet; 40 import com.sun.star.sheet.XSpreadsheetDocument; 41 import com.sun.star.sheet.XSpreadsheets; 42 import com.sun.star.uno.UnoRuntime; 43 import com.sun.star.util.XRefreshable; 44 45 /** 46 * Basic sheet operator testing 47 * 48 */ 49 public class SheetBasicTest { 50 UnoApp unoApp = new UnoApp(); 51 XSpreadsheetDocument scDocument = null; 52 XComponent scComponent = null; 53 54 @BeforeClass 55 public static void setUpBeforeClass() throws Exception { 56 57 } 58 59 @AfterClass 60 public static void tearDownAfterClass() throws Exception { 61 } 62 63 @Before 64 public void setUp() throws Exception { 65 unoApp.start(); 66 // New a SC document 67 scComponent = unoApp.newDocument("scalc"); 68 } 69 70 @After 71 public void tearDown() throws Exception { 72 unoApp.closeDocument(scComponent); 73 unoApp.close(); 74 } 75 76 /** 77 * test insert a sheet, rename sheet name and delete sheet 78 */ 79 @Test 80 public void insertRenameDeleteSheet() throws Exception { 81 // Insert a sheet named aa after first sheet 82 String sheetname = "aa"; 83 scDocument = SCUtil.getSCDocument(scComponent); 84 XSpreadsheets spreadsheets = scDocument.getSheets(); 85 spreadsheets.insertNewByName(sheetname, (short) 1); 86 87 // active the sheet second sheet aa 88 XSpreadsheet newSpreadSheet = SCUtil.getSCSheetByIndex(scDocument, 89 (short) 1); 90 SCUtil.setCurrentSheet(scDocument, newSpreadSheet); 91 92 // get the new spreadsheet name 93 assertEquals("actual should equals aa", sheetname, 94 SCUtil.getSCSheetNameByIndex(scDocument, (short) 1)); 95 96 // Change the Spreadsheet name 97 String changedname = "SpeadsheetAfterChange"; 98 SCUtil.setSCSheetNameByIndex(scDocument, (short) 1, changedname); 99 100 // Save and reload document 101 SCUtil.saveFileAs(scComponent, "TestSpreadsheet", "ods"); 102 XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp, 103 scDocument, "TestSpreadsheet.ods"); 104 105 scDocument = scDocumentTemp; 106 String sheetnameaftermove = SCUtil.getSCSheetNameByIndex(scDocument, 107 (short) 1); 108 109 // Verify the changed Spreadsheet name 110 assertEquals("actual should equals SpeadsheetAfterChange", changedname, 111 sheetnameaftermove); 112 113 scDocument.getSheets().removeByName(changedname); 114 115 assertFalse("actual should equals false", 116 spreadsheets.hasByName(changedname)); 117 SCUtil.save(scDocumentTemp); 118 } 119 120 /** 121 * Test copy and paste sheet 122 */ 123 @Test 124 public void copypastesheet() throws Exception { 125 // Insert some value into cells 126 scDocument = SCUtil.getSCDocument(scComponent); 127 String souceSheetName = "sourcesheet"; 128 SCUtil.setSCSheetNameByIndex(scDocument, (short) 0, souceSheetName); 129 String[][] stringValues = { { "", "Jan", "Feb", "Mar", "Apr", "Mai" }, 130 { "Profit", "12.3", "43.2", "5.1", "76", "56.8" }, 131 { "Rival in business", "12.2", "12.6", "17.7", "20.4", "100" }, }; 132 XSpreadsheet sourceSpreadSheet = SCUtil.getSCSheetByName(scDocument, 133 souceSheetName); 134 // input strings into sheet1 135 SCUtil.setTextToCellRange(sourceSpreadSheet, 0, 0, stringValues); 136 // copy the sheet from sourcesheet to copysheet 137 String newcopysheet = "copysheet"; 138 XSpreadsheets spreadsheets = scDocument.getSheets(); 139 spreadsheets.copyByName(souceSheetName, newcopysheet, (short) 2); 140 141 // Save and reload document 142 SCUtil.saveFileAs(scComponent, "TestCopysheet", "xls"); 143 XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp, 144 scDocument, "TestCopysheet.xls"); 145 scDocument = scDocumentTemp; 146 147 XSpreadsheet copysheet = SCUtil 148 .getSCSheetByIndex(scDocument, (short) 2); 149 String[][] CopystringValues = SCUtil.getTextFromCellRange(copysheet, 0, 150 0, 5, 2); 151 assertArrayEquals("Expect string value should be stringValues", 152 stringValues, CopystringValues); 153 154 } 155 156 /** 157 * Test move sheet 158 */ 159 @Test 160 public void movesheet() throws Exception { 161 162 // new sc document 163 scDocument = SCUtil.getSCDocument(scComponent); 164 XSpreadsheets spreadsheets = scDocument.getSheets(); 165 166 // change the first sheet name and input same value into the sheet cell 167 String sheetname = "sourcesheet"; 168 SCUtil.setSCSheetNameByIndex(scDocument, (short) 0, sheetname); 169 String[][] stringValues = { { "", "Jan", "Feb", "Mar", "Apr", "Mai" }, 170 { "Profit", "12.3", "43.2", "5.1", "76", "56.8" }, 171 { "Rival in business", "12.2", "12.6", "17.7", "20.4", "100" }, }; 172 XSpreadsheet movesheet = SCUtil 173 .getSCSheetByIndex(scDocument, (short) 0); 174 SCUtil.setTextToCellRange(movesheet, 0, 0,stringValues); 175 176 // Before move, get the 2nd sheet name 177 String secondSheetNameBeforeMove = SCUtil.getSCSheetNameByIndex( 178 scDocument, (short) 1); 179 180 // move the first sheet 181 spreadsheets.moveByName(sheetname, (short) 2); 182 183 // Save and reload document 184 SCUtil.saveFileAs(scComponent, "Testmovesheet", "xls"); 185 XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp, 186 scDocument, "Testmovesheet.xls"); 187 scDocument = scDocumentTemp; 188 189 // After move, get the first sheet name, and verify it same as 2nd sheet 190 // name before move 191 String firstsheetnameAfterMove = SCUtil.getSCSheetNameByIndex( 192 scDocument, (short) 0); 193 assertEquals("Expect result should be Sheet2", 194 secondSheetNameBeforeMove, firstsheetnameAfterMove); 195 196 // Get the target sheet name after move 197 String sheetnameAfterMove = SCUtil.getSCSheetNameByIndex(scDocument, 198 (short) 1); 199 assertEquals("Expect result should be sourcesheet", sheetname, 200 sheetnameAfterMove); 201 202 // Check the cell value after move 203 XSpreadsheet sheetaftermove = SCUtil.getSCSheetByIndex(scDocument, 204 (short) 1); 205 String[][] stringValuesaftermove = SCUtil.getTextFromCellRange( 206 sheetaftermove, 0, 0, 5, 2); 207 208 assertArrayEquals("Expect result should be stringValues", stringValues, 209 stringValuesaftermove); 210 } 211 212 /** 213 * Test hide and show sheet 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 /** 268 * Test sheet tab color 269 */ 270 @Test 271 public void sheetColor() throws Exception { 272 // get first sheet propertyset 273 scDocument = SCUtil.getSCDocument(scComponent); 274 XSpreadsheets spreadsheets = scDocument.getSheets(); 275 XSpreadsheet firstSpreadSheet = SCUtil.getSCSheetByIndex(scDocument, 276 (short) 0); 277 XPropertySet sheet1PropertySet = (XPropertySet) UnoRuntime 278 .queryInterface(XPropertySet.class, firstSpreadSheet); 279 280 // Set sheet tab color to 111 281 sheet1PropertySet.setPropertyValue("TabColor", 111); 282 283 // copy the color sheet to new sheet 284 spreadsheets.copyByName( 285 SCUtil.getSCSheetNameByIndex(scDocument, (short) 0), 286 "newsheet", (short) 3); 287 288 // Save and reopen the document 289 SCUtil.saveFileAs(scComponent, "Testcolorsheet", "ods"); 290 XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp, 291 scDocument, "Testcolorsheet.ods"); 292 scDocument = scDocumentTemp; 293 294 // Get first sheet color 295 sheet1PropertySet = (XPropertySet) UnoRuntime.queryInterface( 296 XPropertySet.class, 297 SCUtil.getSCSheetByIndex(scDocument, (short) 0)); 298 int firstSheetcolorid = (Integer) sheet1PropertySet 299 .getPropertyValue("TabColor"); 300 301 // Get the copied sheet color 302 XPropertySet newsheetPropertySet = (XPropertySet) UnoRuntime 303 .queryInterface(XPropertySet.class, 304 SCUtil.getSCSheetByIndex(scDocument, (short) 3)); 305 int copySheetcolorid = (Integer) newsheetPropertySet 306 .getPropertyValue("TabColor"); 307 308 // Verify first sheet color changed successfully 309 assertEquals("Expect color should be 111", 111, firstSheetcolorid); 310 311 // Verify first sheet color same as copy sheet color 312 assertEquals("Expect color should be 111", firstSheetcolorid, 313 copySheetcolorid); 314 } 315 316 /** 317 * test insert sheet from other file 318 */ 319 @Ignore("Bug #128536 locks up on update link confirmation dialog. replaced by InsertExcelSheetTest class.") 320 @Test(timeout = 15000) 321 public void insertSheetFromfile() throws Exception { 322 // New a document source.xls, add value to 3 sheet 323 scDocument = SCUtil.getSCDocument(scComponent); 324 XSpreadsheets spreadsheets = scDocument.getSheets(); 325 XSpreadsheet firstSheet = SCUtil.getSCSheetByIndex(scDocument, 326 (short) 0); 327 XSpreadsheet secondSheet = SCUtil.getSCSheetByIndex(scDocument, 328 (short) 1); 329 XSpreadsheet thirdSheet = SCUtil.getSCSheetByIndex(scDocument, 330 (short) 2); 331 SCUtil.setFormulaToCell(firstSheet, 1, 2, "=2*2"); 332 SCUtil.setFormulaToCell(secondSheet, 1, 2, "=2*2"); 333 SCUtil.setFormulaToCell(thirdSheet, 1, 2, "=2*2"); 334 335 // Save and close this document 336 SCUtil.saveFileAs(scComponent, "source", "xls"); 337 SCUtil.closeFile(scDocument); 338 339 // get source document URL 340 String SourcestoreUrl = Testspace.getUrl("output/sc/" + "source" + "." 341 + "xls"); 342 343 // New a document 344 scComponent = unoApp.newDocument("scalc"); 345 scDocument = SCUtil.getSCDocument(scComponent); 346 spreadsheets = scDocument.getSheets(); 347 // Insert firstexternalsheet sheet, link with Sheet1 in source document 348 // and the link mode is NORMAL 349 spreadsheets.insertNewByName("firstexternalsheet", (short) 3); 350 XSpreadsheet firstexternalsheet = SCUtil.getSCSheetByIndex(scDocument, 351 (short) 3); 352 XSheetLinkable xfirstSheetLinkable = (XSheetLinkable) UnoRuntime 353 .queryInterface(XSheetLinkable.class, firstexternalsheet); 354 xfirstSheetLinkable.link(SourcestoreUrl, "", "MS Excel 97", "", 355 SheetLinkMode.NORMAL); 356 357 // Insert secondexternalsheet sheet, link with Sheet2 in source document 358 // and the link mode is VALUE 359 spreadsheets.insertNewByName("secondexternalsheet", (short) 4); 360 XSpreadsheet secondexternalsheet = SCUtil.getSCSheetByIndex(scDocument, 361 (short) 4); 362 XSheetLinkable xsecondSheetLinkable = (XSheetLinkable) UnoRuntime 363 .queryInterface(XSheetLinkable.class, secondexternalsheet); 364 xsecondSheetLinkable.link(SourcestoreUrl, "Sheet2", "MS Excel 97", "", 365 SheetLinkMode.VALUE); 366 367 // Insert secondexternalsheet sheet, link with Sheet2 in source document 368 // and the link mode is NONE 369 spreadsheets.insertNewByName("thirdexternalsheet", (short) 5); 370 XSpreadsheet thirdexternalsheet = SCUtil.getSCSheetByIndex(scDocument, 371 (short) 5); 372 XSheetLinkable xthirdSheetLinkable = (XSheetLinkable) UnoRuntime 373 .queryInterface(XSheetLinkable.class, thirdexternalsheet); 374 xthirdSheetLinkable.link(SourcestoreUrl, "Sheet3", "MS Excel 97", "", 375 SheetLinkMode.NONE); 376 377 // Verify firstexternalsheet 378 assertEquals("Expect formula should be =2*2", "=2*2", 379 SCUtil.getFormulaFromCell(firstexternalsheet, 1, 2)); 380 assertEquals("Expect formula result should be 4", "4", 381 SCUtil.getTextFromCell(firstexternalsheet, 1, 2)); 382 383 // Verify secondexternalsheet 384 assertEquals("Expect formula should be 4", "4", 385 SCUtil.getFormulaFromCell(secondexternalsheet, 1, 2)); 386 assertEquals("Expect formula result should be 4", "4", 387 SCUtil.getTextFromCell(secondexternalsheet, 1, 2)); 388 389 // Verify thirdexternalsheet 390 assertEquals("Expect formula should be blank", "", 391 SCUtil.getFormulaFromCell(thirdexternalsheet, 1, 2)); 392 assertEquals("Expect formula result should be blank", "", 393 SCUtil.getTextFromCell(thirdexternalsheet, 1, 2)); 394 395 // save document and verify the linked sheet again 396 SCUtil.saveFileAs(scComponent, "linked", "ods"); 397 XSpreadsheetDocument tempscDocument = SCUtil.reloadFile(unoApp, 398 scDocument, "linked.ods"); 399 scDocument = tempscDocument; 400 firstexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 3); 401 secondexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 4); 402 thirdexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 5); 403 404 // Verify firstexternalsheet 405 assertEquals("Expect formula should be =2*2", "=2*2", 406 SCUtil.getFormulaFromCell(firstexternalsheet, 1, 2)); 407 assertEquals("Expect formula result should be 4", "4", 408 SCUtil.getTextFromCell(firstexternalsheet, 1, 2)); 409 410 // Verify secondexternalsheet 411 assertEquals("Expect formula should be 4", "4", 412 SCUtil.getFormulaFromCell(secondexternalsheet, 1, 2)); 413 assertEquals("Expect formula result should be 4", "4", 414 SCUtil.getTextFromCell(secondexternalsheet, 1, 2)); 415 416 // Verify thirdexternalsheet 417 assertEquals("Expect formula should be blank", "", 418 SCUtil.getFormulaFromCell(thirdexternalsheet, 1, 2)); 419 assertEquals("Expect formula result should be blank", "", 420 SCUtil.getTextFromCell(thirdexternalsheet, 1, 2)); 421 422 // save and close document 423 SCUtil.save(scDocument); 424 SCUtil.closeFile(scDocument); 425 426 // Open source document and change the value in source document 427 XSpreadsheetDocument sourcescDocument = SCUtil.reloadFile(unoApp, 428 scDocument, "source.xls"); 429 firstSheet = SCUtil.getSCSheetByIndex(sourcescDocument, (short) 0); 430 secondSheet = SCUtil.getSCSheetByIndex(sourcescDocument, (short) 1); 431 thirdSheet = SCUtil.getSCSheetByIndex(sourcescDocument, (short) 2); 432 SCUtil.setFormulaToCell(firstSheet, 1, 2, "=3*3"); 433 SCUtil.setFormulaToCell(secondSheet, 1, 2, "=3*3"); 434 SCUtil.setFormulaToCell(thirdSheet, 1, 2, "=3*3"); 435 SCUtil.save(sourcescDocument); 436 SCUtil.closeFile(sourcescDocument); 437 438 // Open link document 439 tempscDocument = SCUtil.reloadFile(unoApp, scDocument, "linked.ods"); 440 scDocument = tempscDocument; 441 spreadsheets = scDocument.getSheets(); 442 443 firstexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 3); 444 secondexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 4); 445 thirdexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 5); 446 447 // get Object SheetLinks for document 448 XPropertySet sheetpropertyset = (XPropertySet) UnoRuntime 449 .queryInterface(XPropertySet.class, scDocument); 450 Object sheetLinks = sheetpropertyset.getPropertyValue("SheetLinks"); 451 452 XIndexAccess xsheetlinks = (XIndexAccess) UnoRuntime.queryInterface( 453 XIndexAccess.class, sheetLinks); 454 455 // Refresh all links 456 for (int i = 0; i < xsheetlinks.getCount(); i++) { 457 Object sheetlink = xsheetlinks.getByIndex(i); 458 XRefreshable xsheetRefreshable = (XRefreshable) UnoRuntime 459 .queryInterface(XRefreshable.class, sheetlink); 460 xsheetRefreshable.refresh(); 461 } 462 463 // Verify firstexternalsheet 464 assertEquals("Expect formula should be =3*3", "=3*3", 465 SCUtil.getFormulaFromCell(firstexternalsheet, 1, 2)); 466 assertEquals("Expect formula result should be 9", "9", 467 SCUtil.getTextFromCell(firstexternalsheet, 1, 2)); 468 469 // Verify secondexternalsheet 470 assertEquals("Expect formula should be 9", "9", 471 SCUtil.getFormulaFromCell(secondexternalsheet, 1, 2)); 472 assertEquals("Expect formula result should be 9", "9", 473 SCUtil.getTextFromCell(secondexternalsheet, 1, 2)); 474 475 // Verify thirdexternalsheet 476 assertEquals("Expect formula should be blank", "", 477 SCUtil.getFormulaFromCell(thirdexternalsheet, 1, 2)); 478 assertEquals("Expect formula result should be blank", "", 479 SCUtil.getTextFromCell(thirdexternalsheet, 1, 2)); 480 481 // Save the document before close 482 SCUtil.save(scDocument); 483 484 } 485 486 } 487