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

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