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