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