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 package fvt.uno.sc.cell;
23 
24 import static org.junit.Assert.*;
25 
26 import org.junit.After;
27 import org.junit.Before;
28 import org.junit.Test;
29 
30 import org.openoffice.test.uno.UnoApp;
31 
32 import com.sun.star.container.XIndexAccess;
33 import com.sun.star.lang.XComponent;
34 import com.sun.star.sheet.CellDeleteMode;
35 import com.sun.star.sheet.CellInsertMode;
36 import com.sun.star.sheet.XCellRangeAddressable;
37 import com.sun.star.sheet.XSpreadsheet;
38 import com.sun.star.sheet.XSpreadsheetDocument;
39 import com.sun.star.sheet.XSpreadsheets;
40 import com.sun.star.table.XCell;
41 import com.sun.star.uno.UnoRuntime;
42 import com.sun.star.table.XCellRange;
43 import com.sun.star.table.CellRangeAddress;
44 import com.sun.star.sheet.XCellRangeMovement;
45 
46 /**
47  * Test insert or delete cells
48  * @author BinGuo 8/30/2012
49  *
50  */
51 
52 public class InsertDeleteCells {
53 
54 	UnoApp unoApp = new UnoApp();
55 	XSpreadsheetDocument scDocument = null;
56 	XComponent scComponent = null;
57 
58 	@Before
setUp()59 	public void setUp() throws Exception {
60 		unoApp.start();
61 	}
62 
63 	@After
tearDown()64 	public void tearDown() throws Exception {
65 		unoApp.closeDocument(scComponent);
66 		unoApp.close();
67 		}
68 
69 	/**
70 	 * New spreadsheet
71 	 * Create 3x3 cell range A2:C4
72 	 * Execute insert empty A2 & B2 cells shift other existing cells in Column A & B down
73 	 * Execute insert empty A2 & B2 cells shift other existing cells in row 2 move right
74 	 * Execute insert entire empty Row 2 make the whole existing cell range moves down
75 	 * Execute insert entire empty Columns A & B make the whole existing cell range moves right
76 	 * Verify results after insert cells
77 	 */
78 
79 	@Test
testInsertCells()80 	public void testInsertCells() throws Exception {
81 
82 		scComponent = unoApp.newDocument("scalc");
83 		scDocument = (XSpreadsheetDocument) UnoRuntime.queryInterface(XSpreadsheetDocument.class, scComponent);
84 		XSpreadsheets xSpreadsheets = scDocument.getSheets();
85 
86 		// Gets the first sheet in the document.
87         XIndexAccess xSheetsIA = (XIndexAccess)UnoRuntime.queryInterface(XIndexAccess.class, xSpreadsheets);
88         Object sheetObj = (XSpreadsheet)UnoRuntime.queryInterface(XSpreadsheet.class, xSheetsIA.getByIndex(0));
89 		XSpreadsheet xSheet = (XSpreadsheet) UnoRuntime.queryInterface(XSpreadsheet.class, sheetObj);
90 
91 	    // Create a 3x3 cell range "A2:C4" with the values 0 ... 8.
92 	    int nCol = 0;
93 	    int nValue = 0;
94 
95 	    for (int n = 1; n < 4; ++n){
96 	    	int nRow = 1;
97 	    	for (int i = 1; i < 4; ++i) {
98 			   xSheet.getCellByPosition( nCol, nRow ).setValue( nValue );
99 		       nRow += 1;
100 		       nValue += 1;
101 		    }
102 	        nCol += 1;
103 	    }
104 
105 	    //Insert 2 cells in A2:B2 and shift other existing cells in Column A & B down
106 
107 		// Get cell range A2:B2 by position - (column, row, column, row)
108         XCellRange xCellRange = xSheet.getCellRangeByPosition( 0, 1, 1, 1 );
109         XCellRangeMovement xCellRangeMovement = (XCellRangeMovement)
110         		UnoRuntime.queryInterface(XCellRangeMovement.class, xSheet);
111 
112         // Gets the selected range's address/location.
113  	    XCellRangeAddressable xCellRangeAddr = (XCellRangeAddressable)
114 	            UnoRuntime.queryInterface( XCellRangeAddressable.class, xCellRange );
115 	    CellRangeAddress address = xCellRangeAddr.getRangeAddress();
116 
117 	    //Execute Insert cells in A2:B2 and shift other existing cells in Column A & B down
118 	    xCellRangeMovement.insertCells(address, CellInsertMode.DOWN);
119 
120 	    //Get value of cell A2, B2 and C2
121         XCell cellA2 = xSheet.getCellByPosition(0, 1);
122         XCell cellB2 = xSheet.getCellByPosition(1, 1);
123         XCell cellC2 = xSheet.getCellByPosition(2, 1);
124         double expectValueA2 = 0.0;
125         double expectValueB2 = 0.0;
126         double expectValueC2 = 6;
127 
128         //Verify results after execute Insert cells in A2:B2 and shift other existing cells in Column A & B down
129         assertEquals("Verify value of A2 after execute Insert cells in A2:B2 and shift cells down.",
130         		expectValueA2, cellA2.getValue(),0);
131         assertEquals("Verify value of B2 after execute Insert cells in A2:B2 and shift cells down.",
132         		expectValueB2, cellB2.getValue(),0);
133         assertEquals("Verify value of C2 after execute Insert cells in A2:B2 and shift cells down.",
134         		expectValueC2, cellC2.getValue(),0);
135 
136         //Execute Insert cells in A2:B2 and shift other existing cells in row 2 move right
137 	    xCellRangeMovement.insertCells(address, CellInsertMode.RIGHT);
138 
139 	    //Get value of cell C2, D2, E2 and C3
140 	    cellC2 = xSheet.getCellByPosition(2, 1);
141         XCell cellD2 = xSheet.getCellByPosition(3, 1);
142         XCell cellE2 = xSheet.getCellByPosition(4, 1);
143         XCell cellC3 = xSheet.getCellByPosition(2, 2);
144         double expectValueC2right = 0.0;
145         double expectValueD2 = 0.0;
146         double expectValueE2 = 6;
147         double expectValueC3 = 7;
148 
149         //Verify results after execute Insert cells in A2:B2 and shift other existing cells in row 2 move right
150         assertEquals("Verify value of C2 after execute Insert cells in A2:B2 and shift cells Right.",
151         		expectValueC2right, cellC2.getValue(),0);
152         assertEquals("Verify value of D2 after execute Insert cells in A2:B2 and shift cells Right.",
153         		expectValueD2, cellD2.getValue(),0);
154         assertEquals("Verify value of E2 after execute Insert cells in A2:B2 and shift cells Right.",
155         		expectValueE2, cellE2.getValue(),0);
156         assertEquals("Verify value of C3 after execute Insert cells in A2:B2 and shift cells Right.",
157         		expectValueC3, cellC3.getValue(),0);
158 
159         //Execute Insert Entire Row 2 make the whole existing cell range moves down
160 	    xCellRangeMovement.insertCells(address, CellInsertMode.ROWS);
161 
162 	    //Get value of cell E2, E3 and C3
163 	    cellE2 = xSheet.getCellByPosition(4, 1);
164         XCell cellE3 = xSheet.getCellByPosition(4, 2);
165         cellC3 = xSheet.getCellByPosition(2, 2);
166         double expectValueE2rows = 0.0;
167         double expectValueE3 = 6;
168         double expectValueC3rows = 0.0;
169 
170         //Verify results after execute Insert Entire Row 2 make the whole existing cell range moves down
171         assertEquals("Verify value of E2 after execute Insert Entire Row 2 make the whole existing cell range moves down.",
172         		expectValueE2rows, cellE2.getValue(),0);
173         assertEquals("Verify value of E3 after execute Insert Entire Row 2 make the whole existing cell range moves down.",
174         		expectValueE3, cellE3.getValue(),0);
175         assertEquals("Verify value of C3 after execute Insert Entire Row 2 make the whole existing cell range moves down.",
176         		expectValueC3rows, cellC3.getValue(),0);
177 
178 	    //Execute Insert Entire Columns make the whole existing cell range moves right
179 	    xCellRangeMovement.insertCells(address, CellInsertMode.COLUMNS);
180 
181 	    //Get value of cell C4, C5 and C6
182 	    XCell cellC4 = xSheet.getCellByPosition(2, 3);
183         XCell cellC5 = xSheet.getCellByPosition(2, 4);
184         XCell cellC6 = xSheet.getCellByPosition(2, 5);
185         double expectValueC4 = 0.0;
186         double expectValueC5 = 1;
187         double expectValueC6 = 2;
188 
189         //Verify results after execute Insert Entire Columns make the whole existing cell range moves right
190         assertEquals("Verify value of E2 after execute Insert Entire Row 2 make the whole existing cell range moves down.",
191     		   expectValueC4, cellC4.getValue(),0);
192         assertEquals("Verify value of E3 after execute Insert Entire Row 2 make the whole existing cell range moves down.",
193     		   expectValueC5, cellC5.getValue(),0);
194         assertEquals("Verify value of C3 after execute Insert Entire Row 2 make the whole existing cell range moves down.",
195     		   expectValueC6, cellC6.getValue(),0);
196 
197     }
198 
199 	/**
200 	 * New spreadsheet
201 	 * Create 3x3 cell range A2:C4
202 	 * Execute delete cells A2 & B2 shift other existing cells in column A & B move up
203 	 * Execute delete cells A2 & B2 shift other existing cells in row 2 move left
204 	 * Execute delete entire Row 2 make the whole existing cell range moves up
205 	 * Execute delete entire Columns A & B make the whole existing cell range moves left
206 	 * Verify results after delete cells
207 	 */
208 
209     @Test
testDeleteCells()210     public void testDeleteCells() throws Exception {
211 
212 	    scComponent = unoApp.newDocument("scalc");
213 	    scDocument = (XSpreadsheetDocument) UnoRuntime.queryInterface(XSpreadsheetDocument.class, scComponent);
214 	    XSpreadsheets xSpreadsheets = scDocument.getSheets();
215 
216 	    // Gets the first sheet in the document.
217         XIndexAccess xSheetsIA = (XIndexAccess)UnoRuntime.queryInterface(XIndexAccess.class, xSpreadsheets);
218         Object sheetObj = (XSpreadsheet)UnoRuntime.queryInterface(XSpreadsheet.class, xSheetsIA.getByIndex(0));
219 	    XSpreadsheet xSheet = (XSpreadsheet) UnoRuntime.queryInterface(XSpreadsheet.class, sheetObj);
220 
221         // Create a 3x3 cell range "A2:C4" with the values 0 ... 8.
222         int nCol = 0;
223         int nValue = 0;
224 
225         for (int n = 1; n < 4; ++n){
226     	    int nRow = 1;
227     	    for (int i = 1; i < 4; ++i) {
228 		        xSheet.getCellByPosition( nCol, nRow ).setValue( nValue );
229 	            nRow += 1;
230 	            nValue += 1;
231 	        }
232             nCol += 1;
233        }
234 
235         //Insert 2 cells in A2:B2 and shift cells up
236 
237 	    // Get cell range A2:B2 by position - (column, row, column, row)
238         XCellRange xCellRange = xSheet.getCellRangeByPosition( 0, 1, 1, 1 );
239         XCellRangeMovement xCellRangeMovement = (XCellRangeMovement)
240     		UnoRuntime.queryInterface(XCellRangeMovement.class, xSheet);
241 
242         // Gets the selected range's address/location.
243 	    XCellRangeAddressable xCellRangeAddr = (XCellRangeAddressable)
244             UnoRuntime.queryInterface( XCellRangeAddressable.class, xCellRange );
245         CellRangeAddress address = xCellRangeAddr.getRangeAddress();
246 
247         //Execute delete cells in A2:B2 and shift cells in column A & B move up
248         xCellRangeMovement.removeRange(address,CellDeleteMode.UP);
249 
250         //Get value of cell A2, B2 and C2
251         XCell cellA2 = xSheet.getCellByPosition(0, 1);
252         XCell cellB2 = xSheet.getCellByPosition(1, 1);
253         XCell cellC2 = xSheet.getCellByPosition(2, 1);
254         double expectValueA2up = 1;
255         double expectValueB2up = 4;
256         double expectValueC2up = 6;
257 
258         //Verify results after execute delete cells in A2:B2 and shift cells in column A & B move up
259         assertEquals("Verify value of A2 after execute delete cells in A2:B2 and shift cells up.",
260     		expectValueA2up, cellA2.getValue(),0);
261         assertEquals("Verify value of B2 after execute delete cells in A2:B2 and shift cells up.",
262     		expectValueB2up, cellB2.getValue(),0);
263         assertEquals("Verify value of C2 after execute delete cells in A2:B2 and shift cells up.",
264     		expectValueC2up, cellC2.getValue(),0);
265 
266         //Execute delete cells in A2:B2 and shift other existing cells in row 2 move left
267         xCellRangeMovement.removeRange(address,CellDeleteMode.LEFT);
268 
269         //Get value of cell A2, B2 and C2
270         cellA2 = xSheet.getCellByPosition(0, 1);
271         cellB2 = xSheet.getCellByPosition(1, 1);
272         cellC2 = xSheet.getCellByPosition(2, 1);
273         double expectValueA2left = 6;
274         double expectValueB2left = 0.0;
275         double expectValueC2left = 0.0;
276 
277         //Verify results after execute delete cells in A2:B2 and shift other existing cells in row 2 move left
278         assertEquals("Verify value of A2 after execute delete cells in A2:B2 and shift cells left.",
279     		expectValueA2left, cellA2.getValue(),0);
280         assertEquals("Verify value of B2 after execute delete cells in A2:B2 and shift cells left.",
281     		expectValueB2left, cellB2.getValue(),0);
282         assertEquals("Verify value of C2 after execute delete cells in A2:B2 and shift cells left.",
283     		expectValueC2left, cellC2.getValue(),0);
284 
285         //Execute delete Entire Row 2 make the whole existing cell range moves up
286         xCellRangeMovement.removeRange(address,CellDeleteMode.ROWS);
287 
288         //Get value of cell A2, B2 and C2
289         cellA2 = xSheet.getCellByPosition(0, 1);
290         cellB2 = xSheet.getCellByPosition(1, 1);
291         cellC2 = xSheet.getCellByPosition(2, 1);
292         double expectValueA2rows = 2;
293         double expectValueB2rows = 5;
294         double expectValueC2rows = 7;
295 
296         //Verify results after delete Entire Row 2 make the whole existing cell range moves up
297         assertEquals("Verify value of A2 after delete Entire Row 2 make the whole existing cell range moves up.",
298     		expectValueA2rows, cellA2.getValue(),0);
299         assertEquals("Verify value of B2 after delete Entire Row 2 make the whole existing cell range moves up.",
300     		expectValueB2rows, cellB2.getValue(),0);
301         assertEquals("Verify value of C2 after delete Entire Row 2 make the whole existing cell range moves up.",
302     		expectValueC2rows, cellC2.getValue(),0);
303 
304         //Execute delete Entire Columns make the whole existing cell range moves left
305         xCellRangeMovement.removeRange(address,CellDeleteMode.COLUMNS);
306 
307         //Get value of cell A2, B2 and C2
308         cellA2 = xSheet.getCellByPosition(0, 1);
309         cellB2 = xSheet.getCellByPosition(1, 1);
310         cellC2 = xSheet.getCellByPosition(2, 1);
311         double expectValueA2columns = 7;
312         double expectValueB2columns = 0.0;
313         double expectValueC2columns = 0.0;
314 
315         //Verify results after execute delete Entire Columns make the whole existing cell range moves left
316         assertEquals("Verify value of A2 after delete Entire Columns make the whole existing cell range moves left.",
317     		expectValueA2columns, cellA2.getValue(),0);
318         assertEquals("Verify value of B2 after delete Entire Columns make the whole existing cell range moves left.",
319     		expectValueB2columns, cellB2.getValue(),0);
320         assertEquals("Verify value of C2 after delete Entire Columns make the whole existing cell range moves left.",
321     		expectValueC2columns, cellC2.getValue(),0);
322 
323     }
324 
325 }
326 
327 
328