qadevOOo/tests/java/mod/_sc/ScTableSheetObj.java

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