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