qadevOOo/tests/java/ifc/sheet/_XDataPilotTable2.java

   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
  19
  20 package ifc.sheet;
  21
  22 import java.util.ArrayList;
  23
  24 import com.sun.star.beans.XPropertySet;
  25 import com.sun.star.container.XIndexAccess;
  26 import com.sun.star.container.XNamed;
  27 import com.sun.star.lang.IllegalArgumentException;
  28 import com.sun.star.sheet.*;
  29 import com.sun.star.table.CellAddress;
  30 import com.sun.star.table.CellRangeAddress;
  31 import com.sun.star.table.XCell;
  32 import com.sun.star.table.XCellCursor;
  33 import com.sun.star.table.XCellRange;
  34 import com.sun.star.uno.AnyConverter;
  35 import com.sun.star.uno.UnoRuntime;
  36
  37 import lib.MultiMethodTest;
  38 import lib.Status;
  39 import lib.StatusException;
  40
  41 /**
  42  * Testing <code>com.sun.star.sheet.XDataPilotTable2</code>
  43  * interface methods :
  44  * <ul>
  45  *  <li><code> getDrillDownData()</code><li>
  46  *  <li><code> getPositionData()</code></li>
  47  *  <li><code> insertDrillDownSheet()</code></li>
  48  *  <li><code> getOutputRangeByType</code></li>
  49  * </ul>
  50  *
  51  * @see com.sun.star.sheet.XDataPilotTable2
  52  * @see com.sun.star.table.CellAddress
  53  *
  54  */
  55 public class _XDataPilotTable2 extends MultiMethodTest
  56 {
  57     private XSpreadsheetDocument xSheetDoc = null;
  58     private XDataPilotTable2 xDPTab2 = null;
  59     private CellRangeAddress mRangeWhole = null;
  60     private CellRangeAddress mRangeTable = null;
  61     private CellRangeAddress mRangeResult = null;
  62     private ArrayList<Integer> mDataFieldDims = null;
  63     private ArrayList<CellAddress> mResultCells = null;
  64
  65     /**
  66      * exception to be thrown when obtaining a result data for a cell fails
  67      * (probably because the cell is not a result cell).
  68      */
  69     private class ResultCellFailure extends com.sun.star.uno.Exception {}
  70
  71     protected void before()
  72     {
  73         Object o = tEnv.getObjRelation("DATAPILOTTABLE2");
  74         xDPTab2 = UnoRuntime.queryInterface(
  75             XDataPilotTable2.class, o);
  76
  77         if (xDPTab2 == null)
  78             throw new StatusException(Status.failed("Relation not found"));
  79
  80         xSheetDoc = (XSpreadsheetDocument)tEnv.getObjRelation("SHEETDOCUMENT");
  81
  82         getOutputRanges();
  83         buildDataFields();
  84         try
  85         {
  86             buildResultCells();
  87         }
  88         catch (ResultCellFailure e)
  89         {
  90             e.printStackTrace(log);
  91             throw new StatusException( "Failed to build result cells.", e);
  92         }
  93     }
  94
  95     public void _getDrillDownData()
  96     {
  97         boolean testResult = true;
  98         int cellCount = mResultCells.size();
  99         for (int i = 0; i < cellCount; ++i)
 100         {
 101             CellAddress addr = mResultCells.get(i);
 102             DataPilotTablePositionData posData = xDPTab2.getPositionData(addr);
 103             DataPilotTableResultData resData = (DataPilotTableResultData)posData.PositionData;
 104             int dim = mDataFieldDims.get(resData.DataFieldIndex).intValue();
 105             DataResult res = resData.Result;
 106             double val = res.Value;
 107
 108             Object[][] data = xDPTab2.getDrillDownData(addr);
 109             double sum = 0.0;
 110             if (data.length > 1)
 111             {
 112                 for (int row = 1; row < data.length; ++row)
 113                 {
 114                     Object o = data[row][dim];
 115                     if (AnyConverter.isDouble(o))
 116                         sum += ((Double)o).doubleValue();
 117                 }
 118             }
 119             log.println(formatCell(addr) + ": " + data.length + " rows (" + (data.length-1) + " records)");
 120
 121             if (val != sum)
 122                 testResult = false;
 123         }
 124         tRes.tested("getDrillDownData()", testResult);
 125     }
 126
 127     public void _getPositionData()
 128     {
 129         boolean testResult = false;
 130
 131         do
 132         {
 133             CellAddress addr = new CellAddress();
 134             addr.Sheet  = mRangeTable.Sheet;
 135
 136             boolean rangeGood = true;
 137             for (int x = mRangeTable.StartColumn; x <= mRangeTable.EndColumn && rangeGood; ++x)
 138             {
 139                 for (int y = mRangeTable.StartRow; y <= mRangeTable.EndRow && rangeGood; ++y)
 140                 {
 141                     addr.Column = x;
 142                     addr.Row = y;
 143                     log.println("checking " + formatCell(addr));
 144                     DataPilotTablePositionData posData = xDPTab2.getPositionData(addr);
 145                     if (posData.PositionType == DataPilotTablePositionType.NOT_IN_TABLE)
 146                     {
 147                         log.println("specified cell address not in table: " + formatCell(addr));
 148                         rangeGood = false;
 149                         continue;
 150                     }
 151
 152                     switch (posData.PositionType)
 153                     {
 154                     case DataPilotTablePositionType.NOT_IN_TABLE:
 155                         break;
 156                     case DataPilotTablePositionType.COLUMN_HEADER:
 157                         printHeaderData(posData);
 158                         break;
 159                     case DataPilotTablePositionType.ROW_HEADER:
 160                         printHeaderData(posData);
 161                         break;
 162                     case DataPilotTablePositionType.RESULT:
 163                         printResultData(posData);
 164                         break;
 165                     case DataPilotTablePositionType.OTHER:
 166                         break;
 167                     default:
 168                         log.println("unknown position");
 169                     }
 170                 }
 171             }
 172
 173             if (!rangeGood)
 174             {
 175                 log.println("table range check failed");
 176                 break;
 177             }
 178
 179             testResult = true;
 180         }
 181         while (false);
 182
 183         tRes.tested("getPositionData()", testResult);
 184     }
 185
 186     public void _insertDrillDownSheet()
 187     {
 188         boolean testResult = true;
 189         int cellCount = mResultCells.size();
 190         XSpreadsheets xSheets = xSheetDoc.getSheets();
 191         XIndexAccess xIA = UnoRuntime.queryInterface(
 192             XIndexAccess.class, xSheets);
 193         int sheetCount = xIA.getCount();
 194         for (int i = 0; i < cellCount && testResult; ++i)
 195         {
 196             CellAddress addr = mResultCells.get(i);
 197
 198             Object[][] data = xDPTab2.getDrillDownData(addr);
 199
 200             // sheet is always inserted at the current sheet position.
 201             xDPTab2.insertDrillDownSheet(addr);
 202
 203             int newSheetCount = xIA.getCount();
 204             if (newSheetCount == sheetCount + 1)
 205             {
 206                 log.println("drill-down sheet for " + formatCell(addr) + " inserted");
 207                 if (data.length < 2)
 208                 {
 209                     // There is no data for this result.  It should never have
 210                     // inserted a drill-down sheet.
 211                     log.println("new sheet inserted; however, there is no data for this result");
 212                     testResult = false;
 213                     continue;
 214                 }
 215
 216                 // Retrieve the object of the sheet just inserted.
 217                 XSpreadsheet xSheet = null;
 218                 try
 219                 {
 220                     xSheet = UnoRuntime.queryInterface(
 221                         XSpreadsheet.class, xIA.getByIndex(addr.Sheet));
 222                 }
 223                 catch (com.sun.star.uno.Exception e)
 224                 {
 225                     e.printStackTrace();
 226                     throw new StatusException("Failed to get the spreadsheet object.", e);
 227                 }
 228
 229                 // Check the integrity of the data on the inserted sheet.
 230                 if (!checkDrillDownSheetContent(xSheet, data))
 231                 {
 232                     log.println("dataintegrity check on the inserted sheet failed");
 233                     testResult = false;
 234                     continue;
 235                 }
 236
 237                 log.println("  sheet data integrity check passed");
 238
 239                 // Remove the sheet just inserted.
 240
 241                 XNamed xNamed = UnoRuntime.queryInterface(XNamed.class, xSheet);
 242                 String name = xNamed.getName();
 243                 try
 244                 {
 245                     xSheets.removeByName(name);
 246                 }
 247                 catch (com.sun.star.uno.Exception e)
 248                 {
 249                     e.printStackTrace();
 250                     throw new StatusException("Failed to removed the inserted sheet named " + name + ".", e);
 251                 }
 252             }
 253             else if (newSheetCount == sheetCount)
 254             {
 255                 if (data.length > 1)
 256                 {
 257                     // There is data for this result.  It should have inserted
 258                     // a new sheet.
 259                     log.println("no new sheet is inserted, despite the data being present.");
 260                     testResult = false;
 261                 }
 262             }
 263             else
 264             {
 265                 log.println("what just happened!?");
 266                 testResult = false;
 267             }
 268         }
 269
 270         tRes.tested("insertDrillDownSheet()", testResult);
 271     }
 272
 273     public void _getOutputRangeByType()
 274     {
 275         boolean testResult = false;
 276
 277         do
 278         {
 279             // Let's make sure this doesn't cause a crash.  A range returned for an
 280             // out-of-bound condition is undefined.
 281             try
 282             {
 283                 xDPTab2.getOutputRangeByType(-1);
 284                 log.println("exception not raised");
 285                 break;
 286             }
 287             catch (IllegalArgumentException e)
 288             {
 289                 log.println("exception raised on invalid range type (good)");
 290             }
 291
 292             try
 293             {
 294                 xDPTab2.getOutputRangeByType(100);
 295                 log.println("exception not raised");
 296                 break;
 297             }
 298             catch (IllegalArgumentException e)
 299             {
 300                 log.println("exception raised on invalid range type (good)");
 301             }
 302
 303             // Check to make sure the whole range is not empty.
 304             if (mRangeWhole.EndColumn - mRangeWhole.StartColumn <= 0 ||
 305                 mRangeWhole.EndRow - mRangeWhole.EndColumn <= 0)
 306             {
 307                 log.println("whole range is empty");
 308                 break;
 309             }
 310
 311             log.println("whole range is not empty (good)");
 312
 313             // Table range must be of equal width with the whole range, and the same
 314             // bottom.
 315             if (mRangeTable.Sheet != mRangeWhole.Sheet ||
 316                 mRangeTable.StartColumn != mRangeWhole.StartColumn ||
 317                 mRangeTable.EndColumn != mRangeWhole.EndColumn ||
 318                 mRangeTable.EndRow != mRangeWhole.EndRow)
 319             {
 320                 log.println("table range is incorrect");
 321                 break;
 322             }
 323
 324             log.println("table range is correct");
 325
 326             // Result range must be smaller than the table range, and must share the
 327             // same lower-right corner.
 328             if (mRangeResult.Sheet != mRangeTable.Sheet ||
 329                 mRangeResult.StartColumn < mRangeTable.StartColumn ||
 330                 mRangeResult.StartRow < mRangeTable.StartRow ||
 331                 mRangeResult.EndColumn != mRangeTable.EndColumn ||
 332                 mRangeResult.EndRow != mRangeTable.EndRow)
 333                 break;
 334
 335             log.println("result range is correct");
 336
 337             testResult = true;
 338         }
 339         while (false);
 340
 341         tRes.tested("getOutputRangeByType()", testResult);
 342     }
 343
 344     private void printHeaderData(DataPilotTablePositionData posData)
 345     {
 346         DataPilotTableHeaderData header = (DataPilotTableHeaderData)posData.PositionData;
 347         String posType = "";
 348         if (posData.PositionType == DataPilotTablePositionType.COLUMN_HEADER)
 349             posType = "column header";
 350         else if (posData.PositionType == DataPilotTablePositionType.ROW_HEADER)
 351             posType = "row header";
 352
 353         log.println(posType + "; member name: " + header.MemberName + "; dimension: " +
 354                     header.Dimension + "; hierarchy: " + header.Hierarchy +
 355                     "; level: " + header.Level);
 356     }
 357
 358     private void printResultData(DataPilotTablePositionData posData)
 359     {
 360         DataPilotTableResultData resultData = (DataPilotTableResultData)posData.PositionData;
 361         int dataId = resultData.DataFieldIndex;
 362         DataResult res = resultData.Result;
 363         double val = res.Value;
 364         int flags = res.Flags;
 365         int filterCount = resultData.FieldFilters.length;
 366         log.println("result; data field index: " + dataId + "; value: " + val + "; flags: " + flags +
 367                     "; filter count: " + filterCount);
 368
 369         for (int i = 0; i < filterCount; ++i)
 370         {
 371             DataPilotFieldFilter fil = resultData.FieldFilters[i];
 372             log.println("  field name: " + fil.FieldName + "; match value: " + fil.MatchValue);
 373         }
 374     }
 375
 376     private String formatCell(CellAddress addr)
 377     {
 378         String str = "(" + addr.Column + "," + addr.Row + ")";
 379         return str;
 380     }
 381
 382     private void printRange(String text, CellRangeAddress rangeAddr)
 383     {
 384         log.println(text + ": (" + rangeAddr.StartColumn + "," + rangeAddr.StartRow + ") - (" +
 385                     rangeAddr.EndColumn + "," + rangeAddr.EndRow + ")");
 386     }
 387
 388     private void buildResultCells() throws ResultCellFailure
 389     {
 390         if (mResultCells != null)
 391             return;
 392
 393         getOutputRanges();
 394
 395         mResultCells = new ArrayList<CellAddress>();
 396         for (int x = mRangeResult.StartColumn; x <= mRangeResult.EndColumn; ++x)
 397         {
 398             for (int y = mRangeResult.StartRow; y <= mRangeResult.EndRow; ++y)
 399             {
 400                 CellAddress addr = new CellAddress();
 401                 addr.Sheet = mRangeResult.Sheet;
 402                 addr.Column = x;
 403                 addr.Row = y;
 404                 DataPilotTablePositionData posData = xDPTab2.getPositionData(addr);
 405                 if (posData.PositionType != DataPilotTablePositionType.RESULT)
 406                 {
 407                     log.println(formatCell(addr) + ": this is not a result cell");
 408                     throw new ResultCellFailure();
 409                 }
 410                 mResultCells.add(addr);
 411             }
 412         }
 413     }
 414
 415     private void buildDataFields()
 416     {
 417         mDataFieldDims = new ArrayList<Integer>();
 418         XDataPilotDescriptor xDesc = UnoRuntime.queryInterface(
 419             XDataPilotDescriptor.class, xDPTab2);
 420
 421         XIndexAccess xFields = xDesc.getDataPilotFields();
 422         int fieldCount = xFields.getCount();
 423         for (int i = 0; i < fieldCount; ++i)
 424         {
 425             try
 426             {
 427                 Object field = xFields.getByIndex(i);
 428                 XPropertySet propSet = UnoRuntime.queryInterface(
 429                     XPropertySet.class, field);
 430                 DataPilotFieldOrientation orient =
 431                     (DataPilotFieldOrientation)propSet.getPropertyValue("Orientation");
 432                 if (orient == DataPilotFieldOrientation.DATA)
 433                 {
 434                     Integer item = new Integer(i);
 435                     mDataFieldDims.add(item);
 436                 }
 437             }
 438             catch (com.sun.star.uno.Exception e)
 439             {
 440                 e.printStackTrace(log);
 441                 throw new StatusException( "Failed to get a field.", e);
 442             }
 443         }
 444     }
 445
 446     private void getOutputRanges()
 447     {
 448         if (mRangeWhole != null && mRangeTable != null && mRangeResult != null)
 449             return;
 450
 451         try
 452         {
 453             mRangeWhole = xDPTab2.getOutputRangeByType(DataPilotOutputRangeType.WHOLE);
 454             printRange("whole range ", mRangeWhole);
 455             mRangeTable = xDPTab2.getOutputRangeByType(DataPilotOutputRangeType.TABLE);
 456             printRange("table range ", mRangeTable);
 457             mRangeResult = xDPTab2.getOutputRangeByType(DataPilotOutputRangeType.RESULT);
 458             printRange("result range", mRangeResult);
 459         }
 460         catch (IllegalArgumentException e)
 461         {
 462             e.printStackTrace(log);
 463             throw new StatusException( "Failed to get output range by type.", e);
 464         }
 465     }
 466
 467     private boolean checkDrillDownSheetContent(XSpreadsheet xSheet, Object[][] data)
 468     {
 469         CellAddress lastCell = getLastUsedCellAddress(xSheet, 0, 0);
 470         if (data.length <= 0 || lastCell.Row == 0 || lastCell.Column == 0)
 471         {
 472             log.println("empty data condition");
 473             return false;
 474         }
 475
 476         if (data.length != lastCell.Row + 1 || data[0].length != lastCell.Column + 1)
 477         {
 478             log.println("data size differs");
 479             return false;
 480         }
 481
 482         XCellRange xCR = null;
 483         try
 484         {
 485             xCR = xSheet.getCellRangeByPosition(0, 0, lastCell.Column, lastCell.Row);
 486         }
 487         catch (com.sun.star.lang.IndexOutOfBoundsException e)
 488         {
 489             return false;
 490         }
 491
 492         XCellRangeData xCRD = UnoRuntime.queryInterface(
 493             XCellRangeData.class, xCR);
 494
 495         Object[][] sheetData = xCRD.getDataArray();
 496         for (int x = 0; x < sheetData.length; ++x)
 497         {
 498             for (int y = 0; y < sheetData[x].length; ++y)
 499             {
 500                 Object cell1 = sheetData[x][y];
 501                 Object cell2 = data[x][y];
 502                 if (AnyConverter.isString(cell1) && AnyConverter.isString(cell2))
 503                 {
 504                     String s1 = (String)cell1, s2 = (String)(cell2);
 505                     if (!s1.equals(s2))
 506                     {
 507                         log.println("string cell values differ");
 508                         return false;
 509                     }
 510                 }
 511                 else if (AnyConverter.isDouble(cell1) && AnyConverter.isDouble(cell2))
 512                 {
 513                     double f1 = 0.0, f2 = 0.0;
 514                     try
 515                     {
 516                         f1 = AnyConverter.toDouble(cell1);
 517                         f2 = AnyConverter.toDouble(cell2);
 518                     }
 519                     catch (com.sun.star.lang.IllegalArgumentException e)
 520                     {
 521                         log.println("failed to convert cells to double");
 522                         return false;
 523                     }
 524
 525                     if (f1 != f2)
 526                     {
 527                         log.println("numerical cell values differ");
 528                         return false;
 529                     }
 530                 }
 531                 else
 532                 {
 533                     log.println("cell types differ");
 534                     return false;
 535                 }
 536             }
 537         }
 538
 539         return true;
 540     }
 541
 542     private CellAddress getLastUsedCellAddress(XSpreadsheet xSheet, int nCol, int nRow)
 543     {
 544         try
 545         {
 546             XCellRange xRng = xSheet.getCellRangeByPosition(nCol, nRow, nCol, nRow);
 547             XSheetCellRange xSCR = UnoRuntime.queryInterface(
 548                 XSheetCellRange.class, xRng);
 549
 550             XSheetCellCursor xCursor = xSheet.createCursorByRange(xSCR);
 551             XCellCursor xCellCursor = UnoRuntime.queryInterface(
 552                 XCellCursor.class, xCursor);
 553
 554             xCellCursor.gotoEnd();
 555             XCell xCell = xCursor.getCellByPosition(0, 0);
 556             XCellAddressable xCellAddr = UnoRuntime.queryInterface(
 557                 XCellAddressable.class, xCell);
 558
 559             return xCellAddr.getCellAddress();
 560         }
 561         catch (com.sun.star.lang.IndexOutOfBoundsException ex)
 562         {
 563         }
 564         return null;
 565     }
 566 }
 567