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