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