1 /************************************************************************* 2 * 3 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER. 4 * 5 * Copyright 2000, 2010 Oracle and/or its affiliates. 6 * 7 * OpenOffice.org - a multi-platform office productivity suite 8 * 9 * This file is part of OpenOffice.org. 10 * 11 * OpenOffice.org is free software: you can redistribute it and/or modify 12 * it under the terms of the GNU Lesser General Public License version 3 13 * only, as published by the Free Software Foundation. 14 * 15 * OpenOffice.org is distributed in the hope that it will be useful, 16 * but WITHOUT ANY WARRANTY; without even the implied warranty of 17 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 18 * GNU Lesser General Public License version 3 for more details 19 * (a copy is included in the LICENSE file that accompanied this code). 20 * 21 * You should have received a copy of the GNU Lesser General Public License 22 * version 3 along with OpenOffice.org. If not, see 23 * <http://www.openoffice.org/license.html> 24 * for a copy of the LGPLv3 License. 25 * 26 ************************************************************************/ 27 package mod._sc; 28 29 import com.sun.star.beans.XPropertySet; 30 import com.sun.star.container.XIndexAccess; 31 import com.sun.star.lang.XMultiServiceFactory; 32 import com.sun.star.sheet.XScenariosSupplier; 33 import com.sun.star.sheet.XSpreadsheet; 34 import com.sun.star.sheet.XSpreadsheetDocument; 35 import com.sun.star.sheet.XSpreadsheets; 36 import com.sun.star.table.CellAddress; 37 import com.sun.star.table.CellRangeAddress; 38 import com.sun.star.table.XCell; 39 import com.sun.star.table.XCellRange; 40 import com.sun.star.uno.AnyConverter; 41 import com.sun.star.uno.Type; 42 import com.sun.star.uno.UnoRuntime; 43 import com.sun.star.uno.XInterface; 44 import com.sun.star.util.XCloseable; 45 46 import ifc.sheet._XCellRangesQuery; 47 48 import java.io.PrintWriter; 49 50 import lib.StatusException; 51 import lib.TestCase; 52 import lib.TestEnvironment; 53 import lib.TestParameters; 54 55 import util.SOfficeFactory; 56 import util.ValueComparer; 57 58 59 /** 60 * Test for object which is represented by service 61 * <code>com.sun.star.sheet.Spreadsheet</code>. <p> 62 * Object implements the following interfaces : 63 * <ul> 64 * <li> <code>com::sun::star::style::ParagraphProperties</code></li> 65 * <li> <code>com::sun::star::table::XTableChartsSupplier</code></li> 66 * <li> <code>com::sun::star::sheet::XSpreadsheet</code></li> 67 * <li> <code>com::sun::star::table::CellProperties</code></li> 68 * <li> <code>com::sun::star::sheet::XSheetAnnotationsSupplier</code></li> 69 * <li> <code>com::sun::star::table::XCellRange</code></li> 70 * <li> <code>com::sun::star::sheet::XCellRangeAddressable</code></li> 71 * <li> <code>com::sun::star::sheet::XDataPilotTablesSupplier</code></li> 72 * <li> <code>com::sun::star::sheet::Spreadsheet</code></li> 73 * <li> <code>com::sun::star::sheet::XCellRangeMovement</code></li> 74 * <li> <code>com::sun::star::style::CharacterProperties</code></li> 75 * <li> <code>com::sun::star::sheet::XSheetOperation</code></li> 76 * <li> <code>com::sun::star::sheet::XScenariosSupplier</code></li> 77 * <li> <code>com::sun::star::sheet::XSheetPageBreak</code></li> 78 * <li> <code>com::sun::star::sheet::XArrayFormulaRange</code></li> 79 * <li> <code>com::sun::star::sheet::XSheetCellRange</code></li> 80 * <li> <code>com::sun::star::container::XNamed</code></li> 81 * <li> <code>com::sun::star::sheet::SheetCellRange</code></li> 82 * <li> <code>com::sun::star::chart::XChartData</code></li> 83 * <li> <code>com::sun::star::beans::XPropertySet</code></li> 84 * <li> <code>com::sun::star::util::XMergeable</code></li> 85 * <li> <code>com::sun::star::table::XColumnRowRange</code></li> 86 * </ul> 87 * @see com.sun.star.style.ParagraphProperties 88 * @see com.sun.star.table.XTableChartsSupplier 89 * @see com.sun.star.sheet.XSpreadsheet 90 * @see com.sun.star.table.CellProperties 91 * @see com.sun.star.sheet.XSheetAnnotationsSupplier 92 * @see com.sun.star.table.XCellRange 93 * @see com.sun.star.sheet.XCellRangeAddressable 94 * @see com.sun.star.sheet.XDataPilotTablesSupplier 95 * @see com.sun.star.sheet.Spreadsheet 96 * @see com.sun.star.sheet.XCellRangeMovement 97 * @see com.sun.star.style.CharacterProperties 98 * @see com.sun.star.sheet.XSheetOperation 99 * @see com.sun.star.sheet.XScenariosSupplier 100 * @see com.sun.star.sheet.XSheetPageBreak 101 * @see com.sun.star.sheet.XArrayFormulaRange 102 * @see com.sun.star.sheet.XSheetCellRange 103 * @see com.sun.star.container.XNamed 104 * @see com.sun.star.sheet.SheetCellRange 105 * @see com.sun.star.chart.XChartData 106 * @see com.sun.star.beans.XPropertySet 107 * @see com.sun.star.util.XMergeable 108 * @see com.sun.star.table.XColumnRowRange 109 * @see ifc.style._ParagraphProperties 110 * @see ifc.table._XTableChartsSupplier 111 * @see ifc.sheet._XSpreadsheet 112 * @see ifc.table._CellProperties 113 * @see ifc.sheet._XSheetAnnotationsSupplier 114 * @see ifc.table._XCellRange 115 * @see ifc.sheet._XCellRangeAddressable 116 * @see ifc.sheet._XDataPilotTablesSupplier 117 * @see ifc.sheet._Spreadsheet 118 * @see ifc.sheet._XCellRangeMovement 119 * @see ifc.style._CharacterProperties 120 * @see ifc.sheet._XSheetOperation 121 * @see ifc.sheet._XScenariosSupplier 122 * @see ifc.sheet._XSheetPageBreak 123 * @see ifc.sheet._XArrayFormulaRange 124 * @see ifc.sheet._XSheetCellRange 125 * @see ifc.container._XNamed 126 * @see ifc.sheet._SheetCellRange 127 * @see ifc.chart._XChartData 128 * @see ifc.beans._XPropertySet 129 * @see ifc.util._XMergeable 130 * @see ifc.table._XColumnRowRange 131 */ 132 public class ScTableSheetObj extends TestCase { 133 static XSpreadsheetDocument xSheetDoc = null; 134 135 /** 136 * Creates Spreadsheet document. 137 */ 138 protected void initialize(TestParameters tParam, PrintWriter log) { 139 // get a soffice factory object 140 SOfficeFactory SOF = SOfficeFactory.getFactory( 141 (XMultiServiceFactory) tParam.getMSF()); 142 143 try { 144 log.println("creating a sheetdocument"); 145 xSheetDoc = SOF.createCalcDoc(null); 146 } catch (com.sun.star.uno.Exception e) { 147 // Some exception occures.FAILED 148 e.printStackTrace(log); 149 throw new StatusException("Couldn't create document", e); 150 } 151 } 152 153 /** 154 * Disposes Spreadsheet document. 155 */ 156 protected void cleanup(TestParameters tParam, PrintWriter log) { 157 log.println(" disposing xSheetDoc "); 158 159 try { 160 XCloseable oCloser = (XCloseable) UnoRuntime.queryInterface( 161 XCloseable.class, xSheetDoc); 162 oCloser.close(true); 163 } catch (com.sun.star.util.CloseVetoException e) { 164 log.println("Couldn't close document"); 165 } catch (com.sun.star.lang.DisposedException e) { 166 log.println("Document already disposed"); 167 } catch (java.lang.NullPointerException e) { 168 log.println("Couldn't get XCloseable"); 169 } 170 171 try { 172 log.println("Sleeping 500 Millis"); 173 Thread.sleep(500); 174 } catch (InterruptedException ex) { 175 } 176 177 log.println("... Done"); 178 } 179 180 /** 181 * Creating a Testenvironment for the interfaces to be tested. 182 * Retrieves a collection of spreadsheets from the document and takes one of 183 * them. Fills some cell in the spreadsheet. The retrieved spreadsheet is the 184 * instance of the service <code>com.sun.star.sheet.Spreadsheet</code>. 185 * Object relations created : 186 * <ul> 187 * <li> <code>'noArray'</code> for 188 * {@link ifc.sheet._XArrayFormulaRange}(to avoid the test of 189 * the interface <code>XArrayFormulaRange</code>)</li> 190 * </ul> 191 * @see com.sun.star.sheet.XArrayFormulaRange 192 */ 193 protected synchronized TestEnvironment createTestEnvironment(TestParameters Param, 194 PrintWriter log) { 195 XInterface oObj = null; 196 197 log.println("getting sheets"); 198 199 XSpreadsheets xSpreadsheets = (XSpreadsheets) xSheetDoc.getSheets(); 200 201 log.println("getting a sheet"); 202 203 XSpreadsheet oSheet = null; 204 XIndexAccess oIndexAccess = (XIndexAccess) UnoRuntime.queryInterface( 205 XIndexAccess.class, xSpreadsheets); 206 207 try { 208 oSheet = (XSpreadsheet) AnyConverter.toObject( 209 new Type(XSpreadsheet.class), 210 oIndexAccess.getByIndex(0)); 211 } catch (com.sun.star.lang.WrappedTargetException e) { 212 e.printStackTrace(log); 213 throw new StatusException("Couldn't get a spreadsheet", e); 214 } catch (com.sun.star.lang.IndexOutOfBoundsException e) { 215 e.printStackTrace(log); 216 throw new StatusException("Couldn't get a spreadsheet", e); 217 } catch (com.sun.star.lang.IllegalArgumentException e) { 218 e.printStackTrace(log); 219 throw new StatusException("Couldn't get a spreadsheet", e); 220 } 221 222 log.println("filling some cells"); 223 224 try { 225 oSheet.getCellByPosition(5, 5).setValue(15); 226 oSheet.getCellByPosition(1, 4).setValue(10); 227 oSheet.getCellByPosition(2, 0).setValue(-5.15); 228 oSheet.getCellByPosition(8, 8).setFormula("= B5 + C1"); 229 // fill cells for XSheetOtline::autoutline 230 oSheet.getCellByPosition(6, 6).setValue(3); 231 oSheet.getCellByPosition(7, 6).setValue(3); 232 oSheet.getCellByPosition(8, 6).setFormula("= SUM(G7:H7)"); 233 oSheet.getCellByPosition(9, 6).setFormula("= G7*I7"); 234 } catch (com.sun.star.lang.IndexOutOfBoundsException e) { 235 e.printStackTrace(log); 236 throw new StatusException("Exception occurred while filling cells", e); 237 } 238 239 oObj = (XInterface) UnoRuntime.queryInterface(XInterface.class, oSheet); 240 241 log.println("creating a new environment for object"); 242 243 TestEnvironment tEnv = new TestEnvironment(oObj); 244 245 // do not execute com::sun::star::sheets::XCellSeries::fillAuto() 246 tEnv.addObjRelation("XCELLSERIES_FILLAUTO", new Boolean(false)); 247 248 // set the adress ranges of the cells (see values set above): for e.g. XSheetOutline test 249 tEnv.addObjRelation("CellRangeAddress", 250 new CellRangeAddress((short)0, 6, 6, 8, 8)); 251 tEnv.addObjRelation("CellRangeSubAddress", 252 new CellRangeAddress((short)0, 6, 6, 7, 8)); 253 // pick a cell with a formula for XSheetAuditing, a dependent cell and a precedent cell 254 tEnv.addObjRelation("XSheetAuditing.CellAddress", new CellAddress((short)0, 8, 6)); 255 tEnv.addObjRelation("XSheetAuditing.PrecedentCellAddress", new CellAddress((short)0, 7, 6)); 256 tEnv.addObjRelation("XSheetAuditing.DependentCellAddress", new CellAddress((short)0, 9, 6)); 257 258 // add an existing sheet for linking 259 tEnv.addObjRelation("XSheetLinkable.LinkSheet", "ScSheetLinksObj.sdc"); 260 261 //adding Scenario and with that a ScenarioSheet-Relation for Scenario and XScenarioEnhanced 262 XScenariosSupplier scene = (XScenariosSupplier) UnoRuntime.queryInterface( 263 XScenariosSupplier.class, 264 tEnv.getTestObject()); 265 scene.getScenarios() 266 .addNewByName("Scenario", 267 new CellRangeAddress[] { 268 new CellRangeAddress((short) 0, 0, 0, 10, 10) 269 }, "Comment"); 270 271 XSpreadsheet sSheet = null; 272 273 try { 274 sSheet = (XSpreadsheet) UnoRuntime.queryInterface( 275 XSpreadsheet.class, 276 xSpreadsheets.getByName("Scenario")); 277 } catch (com.sun.star.container.NoSuchElementException e) { 278 log.println("Couldn't get Scenario"); 279 } catch (com.sun.star.lang.WrappedTargetException e) { 280 log.println("Couldn't get Scenario"); 281 } 282 283 tEnv.addObjRelation("ScenarioSheet", sSheet); 284 285 log.println("adding ObjRelation 'noArray' to avoid the test" + 286 " 'XArrayFormulaRange'"); 287 tEnv.addObjRelation("noArray", "ScTableSheetObj"); 288 289 XPropertySet PropSet = (XPropertySet) UnoRuntime.queryInterface( 290 XPropertySet.class, oObj); 291 tEnv.addObjRelation("PropSet", PropSet); 292 tEnv.addObjRelation("SHEET", oSheet); 293 294 // add expected results for the XCellRangesQuery interface test 295 String[] expectedResults = new String[7]; 296 expectedResults[_XCellRangesQuery.QUERYCOLUMNDIFFERENCES] = "Sheet1.B5;Sheet1.C1"; 297 expectedResults[_XCellRangesQuery.QUERYCONTENTCELLS] = "Sheet1.B5;Sheet1.C1;Sheet1.F6"; 298 expectedResults[_XCellRangesQuery.QUERYEMPTYCELLS] = "Sheet1.A1 ... Sheet1.B1 ... Sheet1.B6 ... Sheet1.C2 ... Sheet1.D1 ... Sheet1.F1 ... Sheet1.F7 ... Sheet1.G1"; 299 expectedResults[_XCellRangesQuery.QUERYFORMULACELLS] = "Sheet1.I7:J7;Sheet1.I9"; 300 expectedResults[_XCellRangesQuery.QUERYINTERSECTION] = "Sheet1.D4"; 301 expectedResults[_XCellRangesQuery.QUERYROWDIFFERENCES] = "Sheet1.A5;Sheet1.C1"; 302 expectedResults[_XCellRangesQuery.QUERYVISIBLECELLS] = "Sheet1.A2"; 303 tEnv.addObjRelation("XCellRangesQuery.EXPECTEDRESULTS", 304 expectedResults); 305 306 // for XFormulaQuery interface test 307 try { 308 tEnv.addObjRelation("MAKEENTRYINCELL", 309 oSheet.getCellByPosition(15, 15)); 310 tEnv.addObjRelation("RANGEINDICES", new int[] { 0, 0 }); 311 tEnv.addObjRelation("EXPECTEDDEPENDENTVALUES", 312 new int[] { 0, 255, 0, 65535 }); 313 tEnv.addObjRelation("EXPECTEDPRECEDENTVALUES", 314 new int[] { 0, 255, 0, 65535 }); 315 } catch (com.sun.star.lang.IndexOutOfBoundsException e) { 316 log.println( 317 "Cannot add the necessary object relation for XFormulaQuery test."); 318 } 319 320 // XSearchable interface test 321 try { 322 tEnv.addObjRelation("XSearchable.MAKEENTRYINCELL", 323 new XCell[] { 324 oSheet.getCellByPosition(15, 15), 325 oSheet.getCellByPosition(15, 16) 326 }); 327 } catch (com.sun.star.lang.IndexOutOfBoundsException e) { 328 e.printStackTrace((PrintWriter) log); 329 log.println( 330 "Cannot make required object relation 'XSearchable.MAKEENTRYINCELL'."); 331 } 332 333 //Adding relation for util.XSortable 334 final PrintWriter finalLog = log; 335 final XCellRange oTable = oSheet; 336 tEnv.addObjRelation("SORTCHECKER", 337 new ifc.util._XSortable.XSortChecker() { 338 PrintWriter out = finalLog; 339 340 public void setPrintWriter(PrintWriter log) { 341 out = log; 342 } 343 344 public void prepareToSort() { 345 try { 346 oTable.getCellByPosition(0, 0).setValue(4); 347 oTable.getCellByPosition(0, 1).setFormula("b"); 348 oTable.getCellByPosition(0, 2).setValue(3); 349 oTable.getCellByPosition(0, 3).setValue(23); 350 } catch (com.sun.star.lang.IndexOutOfBoundsException e) { 351 out.println("Exception while checking sort"); 352 } 353 } 354 355 public boolean checkSort(boolean isSortNumbering, 356 boolean isSortAscending) { 357 out.println("Sort checking..."); 358 359 boolean res = false; 360 String[] value = new String[4]; 361 362 for (int i = 0; i < 4; i++) { 363 try { 364 XCell cell = oTable.getCellByPosition(0, i); 365 value[i] = cell.getFormula(); 366 } catch (com.sun.star.lang.IndexOutOfBoundsException e) { 367 out.println("Exception while checking sort"); 368 } 369 } 370 371 if (isSortNumbering) { 372 if (isSortAscending) { 373 out.println("Sorting ascending"); 374 375 String[] rightVal = { "3", "4", "23", "b" }; 376 String[] vals = { value[0], value[1], value[2], value[3] }; 377 res = ValueComparer.equalValue(vals, rightVal); 378 out.println("Expected 3, 4, 23, b"); 379 out.println("getting: " + value[0] + ", " + 380 value[1] + ", " + value[2] + ", " + 381 value[3]); 382 } else { 383 String[] rightVal = { "b", "23", "4", "3" }; 384 String[] vals = { value[0], value[1], value[2], value[3] }; 385 res = ValueComparer.equalValue(vals, rightVal); 386 out.println("Expected b, 23, 4, 3"); 387 out.println("getting: " + value[0] + ", " + 388 value[1] + ", " + value[2] + ", " + 389 value[3]); 390 } 391 } else { 392 if (isSortAscending) { 393 String[] rightVal = { "3", "4", "23", "b" }; 394 res = ValueComparer.equalValue(value, rightVal); 395 out.println("Expected 3, 4, 23, b"); 396 out.println("getting: " + value[0] + ", " + 397 value[1] + ", " + value[2] + ", " + 398 value[3]); 399 } else { 400 String[] rightVal = { "b", "23", "4", "3" }; 401 res = ValueComparer.equalValue(value, rightVal); 402 out.println("Expected b, 23, 4, 3"); 403 out.println("getting: " + value[0] + ", " + 404 value[1] + ", " + value[2] + ", " + 405 value[3]); 406 } 407 } 408 409 if (res) { 410 out.println("Sorted correctly"); 411 } else { 412 out.println("Sorted uncorrectly"); 413 } 414 415 return res; 416 } 417 }); 418 419 return tEnv; 420 } 421 } 422