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 complex.cellRanges;
25 
26 import com.sun.star.container.XIndexAccess;
27 // import complexlib.ComplexTestCase;
28 import com.sun.star.lang.XMultiServiceFactory;
29 // import com.sun.star.sheet.CellFlags;
30 import com.sun.star.sheet.XCellRangesQuery;
31 import com.sun.star.sheet.XSheetCellRanges;
32 import com.sun.star.sheet.XSpreadsheet;
33 import com.sun.star.sheet.XSpreadsheetDocument;
34 import com.sun.star.sheet.XSpreadsheets;
35 import com.sun.star.table.CellAddress;
36 // import com.sun.star.table.XColumnRowRange;
37 // import com.sun.star.table.XTableColumns;
38 // import com.sun.star.table.XTableRows;
39 import com.sun.star.uno.AnyConverter;
40 import com.sun.star.uno.Type;
41 import com.sun.star.uno.UnoRuntime;
42 import com.sun.star.uno.XInterface;
43 // import java.io.PrintWriter;
44 import com.sun.star.util.XCloseable;
45 import util.SOfficeFactory;
46 
47 import org.junit.After;
48 import org.junit.AfterClass;
49 import org.junit.Before;
50 import org.junit.BeforeClass;
51 import org.junit.Test;
52 import org.openoffice.test.OfficeConnection;
53 import static org.junit.Assert.*;
54 
55 /**
56  * Check the XCellRangesQuery interface on the SheetCell service. test was
57  * created for bug i20044.
58  */
59 public class CheckXCellRangesQuery /* extends ComplexTestCase */ {
60     XSpreadsheetDocument m_xSheetDoc = null;
61     XCellRangesQuery m_xCell = null;
62     XSpreadsheet m_xSpreadSheet = null;
63 
64     /**
65      * Get all test methods.
66      * @return The test methods.
67      */
68 //     public String[] getTestMethodNames() {
69 //         return new String[] {"checkEmptyCell", "checkFilledCell"};
70 //     }
71 
72     /**
73     * Creates Spreadsheet document and the test object,
74     * before the actual test starts.
75     */
76     @Before public void before() {
77         // create a calc document
78         // SOfficeFactory SOF = SOfficeFactory.getFactory( (XMultiServiceFactory)param.getMSF() );
79         final XMultiServiceFactory xMsf = UnoRuntime.queryInterface(XMultiServiceFactory.class, connection.getComponentContext().getServiceManager());
80         SOfficeFactory SOF = SOfficeFactory.getFactory(xMsf);
81 
82         try {
83             System.out.println( "creating a Spreadsheet document" );
84             m_xSheetDoc = SOF.createCalcDoc(null);
85         } catch ( com.sun.star.uno.Exception e ) {
86             // Some exception occures.FAILED
87             e.printStackTrace(  );
88             fail( "Couldn?t create document");
89         }
90         XInterface oObj = null;
91 
92         try {
93             System.out.println("Getting spreadsheet") ;
94             XSpreadsheets oSheets = m_xSheetDoc.getSheets() ;
95             XIndexAccess oIndexSheets =
96             UnoRuntime.queryInterface(XIndexAccess.class, oSheets);
97             m_xSpreadSheet = (XSpreadsheet) AnyConverter.toObject(
98                     new Type(XSpreadsheet.class),oIndexSheets.getByIndex(0));
99 
100             // get the cell
101             System.out.println("Getting a cell from sheet") ;
102             oObj = m_xSpreadSheet.getCellByPosition(2, 3);
103             m_xCell = UnoRuntime.queryInterface(XCellRangesQuery.class, oObj);
104 
105         } catch (com.sun.star.lang.WrappedTargetException e) {
106             e.printStackTrace();
107             fail("Error getting cell object from spreadsheet document");
108         } catch (com.sun.star.lang.IndexOutOfBoundsException e) {
109             e.printStackTrace();
110             fail("Error getting cell object from spreadsheet document");
111         } catch (com.sun.star.lang.IllegalArgumentException e) {
112             e.printStackTrace();
113             fail("Error getting cell object from spreadsheet document");
114         }
115 
116         // set one value for comparison.
117         try {
118             m_xSpreadSheet.getCellByPosition(1, 1).setValue(15);
119             m_xSpreadSheet.getCellByPosition(1, 3).setValue(5);
120             m_xSpreadSheet.getCellByPosition(2, 1).setFormula("=B2+B4");
121 /*            m_xSpreadSheet.getCellByPosition(2, 1).setFormula("=B2+B3");
122             m_xSpreadSheet.getCellByPosition(3, 2).setFormula("");
123             m_xSpreadSheet.getCellByPosition(3, 3).setFormula("");            */
124         } catch (com.sun.star.lang.IndexOutOfBoundsException e) {
125             e.printStackTrace();
126             fail("Could not fill cell (1, 1) with a value.");
127         }
128 
129     }
130 
131        /*
132      * this method closes a calc document and resets the corresponding class variable xSheetDoc
133      */
134     protected boolean closeSpreadsheetDocument() {
135         boolean worked = true;
136 
137         System.out.println("    disposing xSheetDoc ");
138 
139         try {
140             XCloseable oCloser =  UnoRuntime.queryInterface(
141                                          XCloseable.class, m_xSheetDoc);
142             oCloser.close(true);
143         } catch (com.sun.star.util.CloseVetoException e) {
144             worked = false;
145             System.out.println("Couldn't close document");
146         } catch (com.sun.star.lang.DisposedException e) {
147             worked = false;
148             System.out.println("Document already disposed");
149         } catch (java.lang.NullPointerException e) {
150             worked = false;
151             System.out.println("Couldn't get XCloseable");
152         }
153 
154         m_xSheetDoc = null;
155 
156         return worked;
157     }
158 
159     @After public void after()
160         {
161             closeSpreadsheetDocument();
162         }
163 
164     /**
165      * Perform some tests on an empty cell:
166      * <ol>
167      * <li>compare an empty cell with a cell with a value in the same column</li>
168      * <li>compare an empty cell with a cell with a value in the same row</li>
169      * <li>query for empty cells</li>
170      * <ol>
171      */
172     @Test public void checkEmptyCell() {
173         System.out.println("Checking an empty cell...");
174         // compare an empty cell with a cell with a value
175         assertTrue("\tQuery column differences did not return the correct value.", _queryColumnDifferences("Sheet1.C4"));
176         // compare an empty cell with a cell with a value
177         assertTrue("\tQuery column differences did not return the correct value.", _queryRowDifferences("Sheet1.C4"));
178         // try to get this cell
179 //         assertTrue("\tQuery empty cells did not return the correct value.", _queryEmptyCells("Sheet1.C4"));
180         System.out.println("...done");
181     }
182 
183     /**
184      * Perform some tests on a filled cell:
185      * <ol>
186      * <li>compare an cell with value 5 with a cell with value 15 in the same column</li>
187      * <li>compare an cell with value 5 with a cell with value 15 in the same row</li>
188      * <li>query for an empty cell.</li>
189      * <ol>
190      */
191     @Test public void checkFilledCell() {
192         System.out.println("Checking a filled cell...");
193 
194         // fill the cell with a value
195         try {
196             m_xSpreadSheet.getCellByPosition(2, 3).setValue(15);
197         } catch (com.sun.star.lang.IndexOutOfBoundsException e) {
198             e.printStackTrace();
199             fail("Could not fill cell (2, 3) with a value.");
200         }
201 
202         // compare an cell with value 5 with a cell with value 15
203         assertTrue("\tQuery column differences did not return the correct value.", _queryColumnDifferences("Sheet1.C4"));
204         // compare an cell with value 5 with a cell with value 15
205         assertTrue("\tQuery column differences did not return the correct value.", _queryRowDifferences("Sheet1.C4"));
206         // try to get nothing
207         assertTrue("\tQuery empty cells did not return the correct value.", _queryEmptyCells(""));
208         System.out.println("...done");
209     }
210 
211 
212     /**
213      *  Query column differences between my cell(2,3) and (1,1).
214      *  @param expected The expected outcome value.
215      *  @return True, if the result equals the expected result.
216      */
217     public boolean _queryColumnDifferences(String expected) {
218         System.out.println("\tQuery column differences");
219         XSheetCellRanges ranges = m_xCell.queryColumnDifferences(
220                                           new CellAddress((short) 0, 1, 1));
221         String getting = ranges.getRangeAddressesAsString();
222 
223         if (!getting.equals(expected)) {
224             System.out.println("\tGetting: " + getting);
225             System.out.println("\tShould have been: " + expected);
226             return false;
227         }
228         return true;
229     }
230 
231     /**
232      * Query for an empty cell.
233      *  @param expected The expected outcome value.
234      *  @return True, if the result equals the expected result.
235      */
236     public boolean _queryEmptyCells(String expected) {
237         System.out.println("\tQuery empty cells");
238         XSheetCellRanges ranges = m_xCell.queryEmptyCells();
239         String getting = ranges.getRangeAddressesAsString();
240 
241         if (!getting.equals(expected)) {
242             System.out.println("\tGetting: " + getting);
243             System.out.println("\tShould have been: " + expected);
244             return false;
245         }
246         return true;
247     }
248 
249     /**
250      *  Query row differences between my cell(2,3) and (1,1).
251      *  @param expected The expected outcome value.
252      *  @return True, if the result equals the expected result.
253      */
254     public boolean _queryRowDifferences(String expected) {
255         System.out.println("\tQuery row differences");
256         XSheetCellRanges ranges = m_xCell.queryRowDifferences(
257                                           new CellAddress((short) 0, 1, 1));
258         String getting = ranges.getRangeAddressesAsString();
259 
260         if (!getting.equals(expected)) {
261             System.out.println("\tGetting: " + getting);
262             System.out.println("\tShould have been: " + expected);
263             return false;
264         }
265 
266         return true;
267     }
268 
269 
270     @BeforeClass public static void setUpConnection() throws Exception {
271         connection.setUp();
272     }
273 
274     @AfterClass public static void tearDownConnection()
275         throws InterruptedException, com.sun.star.uno.Exception
276     {
277         connection.tearDown();
278     }
279 
280     private static final OfficeConnection connection = new OfficeConnection();
281 
282 }
283