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