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