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 package ifc.sheet;
25 
26 import java.util.Random;
27 import java.util.StringTokenizer;
28 
29 import lib.MultiMethodTest;
30 import lib.Status;
31 import lib.StatusException;
32 
33 import com.sun.star.container.XIndexAccess;
34 import com.sun.star.sheet.Border;
35 import com.sun.star.sheet.NamedRangeFlag;
36 import com.sun.star.sheet.XCellRangeAddressable;
37 import com.sun.star.sheet.XCellRangeReferrer;
38 import com.sun.star.sheet.XNamedRanges;
39 import com.sun.star.sheet.XSpreadsheet;
40 import com.sun.star.table.CellAddress;
41 import com.sun.star.table.CellRangeAddress;
42 import com.sun.star.table.XCell;
43 import com.sun.star.table.XCellRange;
44 import com.sun.star.text.XTextRange;
45 import com.sun.star.uno.UnoRuntime;
46 
47 /**
48 * Testing <code>com.sun.star.sheet.XNamedRanges</code>
49 * interface methods :
50 * <ul>
51 *  <li><code> addNewByName()</code></li>
52 *  <li><code> addNewFromTitles()</code></li>
53 *  <li><code> removeByName()</code></li>
54 *  <li><code> outputList()</code></li>
55 * </ul> <p>
56 * This test needs the following object relations :
57 * <ul>
58 *  <li> <code>'SHEET'</code> (of type <code>XSpreadsheet</code>):
59 *   to have a spreadsheet </li>
60 * <ul> <p>
61 * @see com.sun.star.sheet.XNamedRanges
62 * @see com.sun.star.sheet.XSpreadsheet
63 */
64 public class _XNamedRanges extends MultiMethodTest {
65 
66     public XNamedRanges oObj = null;
67     String name = "_XNamedRanges";
68     XSpreadsheet oSheet = null;
69 
70     /**
71     * Retrieves object relations.
72     * @throws StatusException If one of relations not found.
73     */
before()74     protected void before() {
75         oSheet = (XSpreadsheet)tEnv.getObjRelation("SHEET");
76         if (oSheet == null) throw new StatusException(Status.failed
77             ("Relation 'SHEET' not found"));
78     }
79 
80     /**
81     * Test creates and stores random content and random type, calls the method
82     * and checks that new range exists in collection using method
83     * <code>hasByName()</code>. <p>
84     * Has <b> OK </b> status if new range exists in collection
85     * and no exceptions were thrown. <p>
86     */
_addNewByName()87     public void _addNewByName() {
88         boolean bResult = true;
89         CellAddress aPosition = new CellAddress((short)0, 2, 2);
90         int nType = getRandomType();
91         String sContent = getRandomContent("D3;A6:B9;=F12");
92         name += sContent;
93         log.println("Adding new range with name=\"" + name +
94                     "\", sContent = \"" + sContent +
95                     "\", aPosition = (" + aPosition.Sheet + ", "
96                                         + aPosition.Column + ", "
97                                         + aPosition.Row +
98                     "), Type = " + nType + ".");
99 
100         oObj.addNewByName(name, sContent, aPosition, nType);
101 
102         //inserted for a bug
103         CellAddress listOutputPosition = new CellAddress((short)0, 1, 1);
104         oObj.outputList(listOutputPosition);
105         String s = null;
106         String s1 = null;
107         try {
108             s = oSheet.getCellByPosition(1, 1).getFormula();
109             s1 = oSheet.getCellByPosition(2, 1).getFormula();
110         } catch(com.sun.star.lang.IndexOutOfBoundsException e) {
111             log.println("Can't get cell by position: " + e);
112             bResult = false;
113         }
114         log.println("Outputlist returns: " + s + " " + s1);
115         //end of insertion
116 
117         bResult &= oObj.hasByName(name);
118 
119         tRes.tested("addNewByName()", bResult);
120     }
121 
122     /**
123     * Test creates a table with left and top titles, creates new ranges from
124     * top titles and left titles, checks all new title ranges. <p>
125     * Has <b> OK </b> status if all required title ranges are present
126     * in collection, if each of them have valid size and position and
127     * no exceptions were thrown. <p>
128     */
_addNewFromTitles()129     public void _addNewFromTitles() {
130         boolean bResult = true;
131 
132         // First, create a small table.
133         log.println("Creating a small table.");
134         try {
135             XCell cell = null;
136             XTextRange textrange = null;
137 
138             for (int i = 1; i < 4; i++) {
139                 cell = oSheet.getCellByPosition(0, i);
140                 textrange = (XTextRange)UnoRuntime.
141                                     queryInterface(XTextRange.class, cell);
142                 textrange.setString("Row" + i);
143 
144                 cell = oSheet.getCellByPosition(i, 0);
145                 textrange = (XTextRange)UnoRuntime.
146                                     queryInterface(XTextRange.class, cell);
147                 textrange.setString("Column" + i);
148             }
149 
150             for (int i = 1; i < 4; i++)
151                 for (int j = 1; j < 4; j++) {
152                     cell = oSheet.getCellByPosition(i, j);
153                     textrange = (XTextRange)UnoRuntime.
154                                     queryInterface(XTextRange.class, cell);
155                     textrange.setString("Val" + ((j - 1) * 3 + i));
156                 }
157             log.println("Finished creating table.");
158             log.println("Creating new ranges from titles");
159 
160             CellRangeAddress CRA = new CellRangeAddress((short)0, 0, 0, 3, 3);
161             Border border = Border.TOP;
162             oObj.addNewFromTitles(CRA, border);
163             for (int i = 1; i < 4; i++) {
164                 bResult &= oObj.hasByName("Column" + i);
165 
166                 Object range = oObj.getByName("Column" + i);
167                 XCellRangeReferrer CRR = (XCellRangeReferrer)UnoRuntime.
168                                 queryInterface(XCellRangeReferrer.class,range);
169 
170                 XCellRange CR = CRR.getReferredCells();
171                 XCellRangeAddressable xCRA = (XCellRangeAddressable)
172                     UnoRuntime.queryInterface(XCellRangeAddressable.class, CR);
173 
174                 CellRangeAddress objCRA = xCRA.getRangeAddress();
175 
176                 bResult &= (objCRA.EndColumn == i && objCRA.StartColumn == i);
177                 bResult &= objCRA.StartRow == 1;
178                 bResult &= objCRA.EndRow == 3;
179                 bResult &= objCRA.Sheet == 0;
180             }
181 
182             border = Border.LEFT;
183             oObj.addNewFromTitles(CRA, border);
184             for (int i = 1; i < 4; i++) {
185                 bResult &= oObj.hasByName("Row" + i);
186 
187                 Object range = oObj.getByName("Row" + i);
188                 XCellRangeReferrer CRR = (XCellRangeReferrer)UnoRuntime.
189                                 queryInterface(XCellRangeReferrer.class,range);
190 
191                 XCellRange CR = CRR.getReferredCells();
192                 XCellRangeAddressable xCRA = (XCellRangeAddressable)
193                     UnoRuntime.queryInterface(XCellRangeAddressable.class, CR);
194 
195                 CellRangeAddress objCRA = xCRA.getRangeAddress();
196 
197                 bResult &= (objCRA.EndRow == i && objCRA.StartRow == i);
198                 bResult &= objCRA.StartColumn == 1;
199                 bResult &= objCRA.EndColumn == 3;
200                 bResult &= objCRA.Sheet == 0;
201             }
202 
203             oObj.outputList(new CellAddress((short)0, 5, 5));
204 
205         } catch (com.sun.star.lang.IndexOutOfBoundsException e) {
206             e.printStackTrace(log);
207             bResult = false;
208         } catch (com.sun.star.lang.WrappedTargetException e) {
209             e.printStackTrace(log);
210             bResult = false;
211         } catch (com.sun.star.container.NoSuchElementException e) {
212             e.printStackTrace(log);
213             bResult = false;
214         }
215 
216         tRes.tested("addNewFromTitles()", bResult);
217     }
218 
219     /**
220     * Test calls the method and checks existing of named ranges obtained
221     * by relation <code>'SHEET'</code>. <p>
222     * Has <b> OK </b> status if all output named ranges exist
223     * and no exceptions were thrown. <p>
224     */
_outputList()225     public void _outputList() {
226         boolean bResult = true;
227         CellAddress CA = new CellAddress((short)0, 0, 0);
228 
229         XIndexAccess IA = (XIndexAccess)UnoRuntime.
230                             queryInterface(XIndexAccess.class, oObj);
231 
232         int elementsCount = IA.getCount();
233 
234         oObj.outputList(CA);
235 
236         try {
237             for (int i = 0; i < elementsCount; i++) {
238                 XCell cell = oSheet.getCellByPosition(0, i);
239                 XTextRange textrange = (XTextRange)
240                     UnoRuntime.queryInterface(XTextRange.class, cell);
241                 String str = textrange.getString();
242                 bResult &= oObj.hasByName(str);
243             }
244         } catch(com.sun.star.lang.IndexOutOfBoundsException e) {
245             e.printStackTrace(log);
246             bResult = false;
247         }
248 
249         tRes.tested("outputList()", bResult);
250     }
251 
252     /**
253     * Test calls the method for existing range, checks number of ranges in
254     * collection after method call, calls method for non-existent named range.
255     * <p>Has <b> OK </b> status if number of named ranges is less by one than
256     * before method call and exception was thrown during second call. <p>
257     * The following method tests are to be completed successfully before :
258     * <ul>
259     *  <li> <code> addNewByName() </code> : to have name of existent
260     *  named range </li>
261     * </ul>
262     */
_removeByName()263     public void _removeByName() {
264         requiredMethod("addNewByName()");
265         boolean bResult = true;
266         XIndexAccess IA = (XIndexAccess)UnoRuntime.
267                             queryInterface(XIndexAccess.class, oObj);
268 
269         int elementsCount = IA.getCount();
270 
271         // Removing existent element
272         oObj.removeByName(name);
273         bResult = elementsCount == IA.getCount() + 1;
274 
275         try {
276             // Removing unexistent element.
277             oObj.removeByName(name);
278             log.println("Exception expected when removed unexistent element!");
279             bResult = false;
280         } catch (com.sun.star.uno.RuntimeException e) {
281             log.println("Expected exception occurred while testing" +
282                       "removeByName() when removed unexistent element.");
283 
284         }
285 
286         tRes.tested("removeByName()", bResult);
287     }
288 
289     /**
290     * Method make string of random content.
291     * @return string of random content
292     */
getRandomContent(String str)293     String getRandomContent(String str) {
294         String gRS = "none";
295         Random rnd = new Random();
296 
297         StringTokenizer ST = new StringTokenizer(str, ";");
298         int nr = rnd.nextInt(ST.countTokens());
299         if (nr < 1)
300             nr++;
301 
302         for (int i=1; i < nr + 1; i++)
303             gRS = ST.nextToken();
304 
305         return gRS;
306     }
307 
308     /**
309      * Returns random value of named range flag.
310      */
getRandomType()311     int getRandomType(){
312         int types[] = { 0,
313                         NamedRangeFlag.COLUMN_HEADER,
314                         NamedRangeFlag.FILTER_CRITERIA,
315                         NamedRangeFlag.PRINT_AREA,
316                         NamedRangeFlag.ROW_HEADER
317                       };
318 
319         Random rnd = new Random();
320         return types[rnd.nextInt(5)];
321     }
322 }
323 
324