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 577