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 @Test insertSheetFromfile()317 public void insertSheetFromfile() throws Exception { 318 // New a document source.xls, add value to 3 sheet 319 scDocument = SCUtil.getSCDocument(scComponent); 320 XSpreadsheets spreadsheets = scDocument.getSheets(); 321 XSpreadsheet firstSheet = SCUtil.getSCSheetByIndex(scDocument, 322 (short) 0); 323 XSpreadsheet secondSheet = SCUtil.getSCSheetByIndex(scDocument, 324 (short) 1); 325 XSpreadsheet thirdSheet = SCUtil.getSCSheetByIndex(scDocument, 326 (short) 2); 327 SCUtil.setFormulaToCell(firstSheet, 1, 2, "=2*2"); 328 SCUtil.setFormulaToCell(secondSheet, 1, 2, "=2*2"); 329 SCUtil.setFormulaToCell(thirdSheet, 1, 2, "=2*2"); 330 331 // Save and close this document 332 SCUtil.saveFileAs(scComponent, "source", "xls"); 333 SCUtil.closeFile(scDocument); 334 335 // get source document URL 336 String SourcestoreUrl = Testspace.getUrl("output/sc/" + "source" + "." 337 + "xls"); 338 339 // New a document 340 scComponent = unoApp.newDocument("scalc"); 341 scDocument = SCUtil.getSCDocument(scComponent); 342 spreadsheets = scDocument.getSheets(); 343 // Insert firstexternalsheet sheet, link with Sheet1 in source document 344 // and the link mode is NORMAL 345 spreadsheets.insertNewByName("firstexternalsheet", (short) 3); 346 XSpreadsheet firstexternalsheet = SCUtil.getSCSheetByIndex(scDocument, 347 (short) 3); 348 XSheetLinkable xfirstSheetLinkable = (XSheetLinkable) UnoRuntime 349 .queryInterface(XSheetLinkable.class, firstexternalsheet); 350 xfirstSheetLinkable.link(SourcestoreUrl, "", "MS Excel 97", "", 351 SheetLinkMode.NORMAL); 352 353 // Insert secondexternalsheet sheet, link with Sheet2 in source document 354 // and the link mode is VALUE 355 spreadsheets.insertNewByName("secondexternalsheet", (short) 4); 356 XSpreadsheet secondexternalsheet = SCUtil.getSCSheetByIndex(scDocument, 357 (short) 4); 358 XSheetLinkable xsecondSheetLinkable = (XSheetLinkable) UnoRuntime 359 .queryInterface(XSheetLinkable.class, secondexternalsheet); 360 xsecondSheetLinkable.link(SourcestoreUrl, "Sheet2", "MS Excel 97", "", 361 SheetLinkMode.VALUE); 362 363 // Insert secondexternalsheet sheet, link with Sheet2 in source document 364 // and the link mode is NONE 365 spreadsheets.insertNewByName("thirdexternalsheet", (short) 5); 366 XSpreadsheet thirdexternalsheet = SCUtil.getSCSheetByIndex(scDocument, 367 (short) 5); 368 XSheetLinkable xthirdSheetLinkable = (XSheetLinkable) UnoRuntime 369 .queryInterface(XSheetLinkable.class, thirdexternalsheet); 370 xthirdSheetLinkable.link(SourcestoreUrl, "Sheet3", "MS Excel 97", "", 371 SheetLinkMode.NONE); 372 373 // Verify firstexternalsheet 374 assertEquals("Expect formula should be =2*2", "=2*2", 375 SCUtil.getFormulaFromCell(firstexternalsheet, 1, 2)); 376 assertEquals("Expect formula result should be 4", "4", 377 SCUtil.getTextFromCell(firstexternalsheet, 1, 2)); 378 379 // Verify secondexternalsheet 380 assertEquals("Expect formula should be 4", "4", 381 SCUtil.getFormulaFromCell(secondexternalsheet, 1, 2)); 382 assertEquals("Expect formula result should be 4", "4", 383 SCUtil.getTextFromCell(secondexternalsheet, 1, 2)); 384 385 // Verify thirdexternalsheet 386 assertEquals("Expect formula should be blank", "", 387 SCUtil.getFormulaFromCell(thirdexternalsheet, 1, 2)); 388 assertEquals("Expect formula result should be blank", "", 389 SCUtil.getTextFromCell(thirdexternalsheet, 1, 2)); 390 391 // save document and verify the linked sheet again 392 SCUtil.saveFileAs(scComponent, "linked", "ods"); 393 XSpreadsheetDocument tempscDocument = SCUtil.reloadFile(unoApp, 394 scDocument, "linked.ods"); 395 scDocument = tempscDocument; 396 firstexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 3); 397 secondexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 4); 398 thirdexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 5); 399 400 // Verify firstexternalsheet 401 assertEquals("Expect formula should be =2*2", "=2*2", 402 SCUtil.getFormulaFromCell(firstexternalsheet, 1, 2)); 403 assertEquals("Expect formula result should be 4", "4", 404 SCUtil.getTextFromCell(firstexternalsheet, 1, 2)); 405 406 // Verify secondexternalsheet 407 assertEquals("Expect formula should be 4", "4", 408 SCUtil.getFormulaFromCell(secondexternalsheet, 1, 2)); 409 assertEquals("Expect formula result should be 4", "4", 410 SCUtil.getTextFromCell(secondexternalsheet, 1, 2)); 411 412 // Verify thirdexternalsheet 413 assertEquals("Expect formula should be blank", "", 414 SCUtil.getFormulaFromCell(thirdexternalsheet, 1, 2)); 415 assertEquals("Expect formula result should be blank", "", 416 SCUtil.getTextFromCell(thirdexternalsheet, 1, 2)); 417 418 //save and close document 419 SCUtil.save(scDocument); 420 SCUtil.closeFile(scDocument); 421 422 //Open souce document and change the value in souce document 423 XSpreadsheetDocument sourcescDocument = SCUtil.reloadFile(unoApp, 424 scDocument, "source.xls"); 425 firstSheet = SCUtil.getSCSheetByIndex(sourcescDocument, (short) 0); 426 secondSheet = SCUtil.getSCSheetByIndex(sourcescDocument, (short) 1); 427 thirdSheet = SCUtil.getSCSheetByIndex(sourcescDocument, (short) 2); 428 SCUtil.setFormulaToCell(firstSheet, 1, 2, "=3*3"); 429 SCUtil.setFormulaToCell(secondSheet, 1, 2, "=3*3"); 430 SCUtil.setFormulaToCell(thirdSheet, 1, 2, "=3*3"); 431 SCUtil.save(sourcescDocument); 432 SCUtil.closeFile(sourcescDocument); 433 434 //Open link document 435 tempscDocument = SCUtil.reloadFile(unoApp, scDocument, "linked.ods"); 436 scDocument = tempscDocument; 437 spreadsheets = scDocument.getSheets(); 438 439 firstexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 3); 440 secondexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 4); 441 thirdexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 5); 442 443 //get Object SheetLinks for document 444 XPropertySet sheetpropertyset = (XPropertySet) UnoRuntime 445 .queryInterface(XPropertySet.class, scDocument); 446 Object sheetLinks = sheetpropertyset.getPropertyValue("SheetLinks"); 447 448 XIndexAccess xsheetlinks = (XIndexAccess) UnoRuntime.queryInterface( 449 XIndexAccess.class, sheetLinks); 450 451 //Refresh all links 452 for (int i = 0; i < xsheetlinks.getCount(); i++) { 453 Object sheetlink = xsheetlinks.getByIndex(i); 454 XRefreshable xsheetRefreshable = (XRefreshable) UnoRuntime 455 .queryInterface(XRefreshable.class, sheetlink); 456 xsheetRefreshable.refresh(); 457 } 458 459 // Verify firstexternalsheet 460 assertEquals("Expect formula should be =3*3", "=3*3", 461 SCUtil.getFormulaFromCell(firstexternalsheet, 1, 2)); 462 assertEquals("Expect formula result should be 9", "9", 463 SCUtil.getTextFromCell(firstexternalsheet, 1, 2)); 464 465 // Verify secondexternalsheet 466 assertEquals("Expect formula should be 9", "9", 467 SCUtil.getFormulaFromCell(secondexternalsheet, 1, 2)); 468 assertEquals("Expect formula result should be 9", "9", 469 SCUtil.getTextFromCell(secondexternalsheet, 1, 2)); 470 471 // Verify thirdexternalsheet 472 assertEquals("Expect formula should be blank", "", 473 SCUtil.getFormulaFromCell(thirdexternalsheet, 1, 2)); 474 assertEquals("Expect formula result should be blank", "", 475 SCUtil.getTextFromCell(thirdexternalsheet, 1, 2)); 476 477 //Save the document before close 478 SCUtil.save(scDocument); 479 480 } 481 482 } 483