1 /* Licensed to the Apache Software Foundation (ASF) under one
2  * or more contributor license agreements.  See the NOTICE file
3  * distributed with this work for additional information
4  * regarding copyright ownership.  The ASF licenses this file
5  * to you under the Apache License, Version 2.0 (the
6  * "License"); you may not use this file except in compliance
7  * with the License.  You may obtain a copy of the License at
8  *
9  *   http://www.apache.org/licenses/LICENSE-2.0
10  *
11  * Unless required by applicable law or agreed to in writing,
12  * software distributed under the License is distributed on an
13  * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
14  * KIND, either express or implied.  See the License for the
15  * specific language governing permissions and limitations
16  * under the License.
17  *
18  *************************************************************/
19 
20 package fvt.uno.sc.sheet;
21 
22 import static org.junit.Assert.*;
23 import org.junit.After;
24 import org.junit.AfterClass;
25 import org.junit.Before;
26 import org.junit.BeforeClass;
27 import org.junit.Test;
28 import org.openoffice.test.common.Testspace;
29 import org.openoffice.test.uno.UnoApp;
30 import testlib.uno.SCUtil;
31 import com.sun.star.beans.XPropertySet;
32 import com.sun.star.container.XIndexAccess;
33 import com.sun.star.lang.XComponent;
34 import com.sun.star.sheet.SheetLinkMode;
35 import com.sun.star.sheet.XSheetLinkable;
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.uno.UnoRuntime;
40 import com.sun.star.util.XRefreshable;
41 
42 /**
43  * Basic sheet operator testing
44  *
45  */
46 public class SheetBasicTest {
47 	UnoApp unoApp = new UnoApp();
48 	XSpreadsheetDocument scDocument = null;
49 	XComponent scComponent = null;
50 
51 	@BeforeClass
52 	public static void setUpBeforeClass() throws Exception {
53 
54 	}
55 
56 	@AfterClass
57 	public static void tearDownAfterClass() throws Exception {
58 	}
59 
60 	@Before
61 	public void setUp() throws Exception {
62 		unoApp.start();
63 		// New a SC document
64 		scComponent = unoApp.newDocument("scalc");
65 	}
66 
67 	@After
68 	public void tearDown() throws Exception {
69 		unoApp.closeDocument(scComponent);
70 		unoApp.close();
71 	}
72 
73 	/**
74 	 * test insert a sheet, rename sheet name and delete sheet
75 	 */
76 	@Test
77 	public void insertRenameDeleteSheet() throws Exception {
78 		// Insert a sheet named aa after first sheet
79 		String sheetname = "aa";
80 		scDocument = SCUtil.getSCDocument(scComponent);
81 		XSpreadsheets spreadsheets = scDocument.getSheets();
82 		spreadsheets.insertNewByName(sheetname, (short) 1);
83 
84 		// active the sheet second sheet aa
85 		XSpreadsheet newSpreadSheet = SCUtil.getSCSheetByIndex(scDocument,
86 				(short) 1);
87 		SCUtil.setCurrentSheet(scDocument, newSpreadSheet);
88 
89 		// get the new speadsheet name
90 		assertEquals("actual should equals aa", sheetname,
91 				SCUtil.getSCSheetNameByIndex(scDocument, (short) 1));
92 
93 		// Change the Spreadsheet name
94 		String changedname = "SpeadsheetAfterChange";
95 		SCUtil.setSCSheetNameByIndex(scDocument, (short) 1, changedname);
96 
97 		// Save and reload document
98 		SCUtil.saveFileAs(scComponent, "TestSpreadsheet", "ods");
99 		XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp,
100 				scDocument, "TestSpreadsheet.ods");
101 
102 		scDocument = scDocumentTemp;
103 		String sheetnameaftermove = SCUtil.getSCSheetNameByIndex(scDocument,
104 				(short) 1);
105 
106 		// Verify the changed Spreadsheet name
107 		assertEquals("actual should equals SpeadsheetAfterChange", changedname,
108 				sheetnameaftermove);
109 
110 		scDocument.getSheets().removeByName(changedname);
111 
112 		assertFalse("actual should equals false",
113 				spreadsheets.hasByName(changedname));
114 		SCUtil.save(scDocumentTemp);
115 	}
116 
117 	/**
118 	 * Test copy and past sheet
119 	 */
120 	@Test
121 	public void copypastesheet() throws Exception {
122 		// Insert some value into cells
123 		scDocument = SCUtil.getSCDocument(scComponent);
124 		String souceSheetName = "sourcesheet";
125 		SCUtil.setSCSheetNameByIndex(scDocument, (short) 0, souceSheetName);
126 		String[][] stringValues = { { "", "Jan", "Feb", "Mar", "Apr", "Mai" },
127 				{ "Profit", "12.3", "43.2", "5.1", "76", "56.8" },
128 				{ "Rival in business", "12.2", "12.6", "17.7", "20.4", "100" }, };
129 		XSpreadsheet sourceSpreadSheet = SCUtil.getSCSheetByName(scDocument,
130 				souceSheetName);
131 		// input strings into sheet1
132 		SCUtil.setTextToCellRange(sourceSpreadSheet, 0, 0, stringValues);
133 		// copy the sheet from sourcesheet to copysheet
134 		String newcopysheet = "copysheet";
135 		XSpreadsheets spreadsheets = scDocument.getSheets();
136 		spreadsheets.copyByName(souceSheetName, newcopysheet, (short) 2);
137 
138 		// Save and reload document
139 		SCUtil.saveFileAs(scComponent, "TestCopysheet", "xls");
140 		XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp,
141 				scDocument, "TestCopysheet.xls");
142 		scDocument = scDocumentTemp;
143 
144 		XSpreadsheet copysheet = SCUtil
145 				.getSCSheetByIndex(scDocument, (short) 2);
146 		String[][] CopystringValues = SCUtil.getTextFromCellRange(copysheet, 0,
147 				0, 5, 2);
148 		assertArrayEquals("Expect string value should be stringValues",
149 				stringValues, CopystringValues);
150 
151 	}
152 
153 	/**
154 	 * Test move sheet
155 	 */
156 	@Test
157 	public void movesheet() throws Exception {
158 
159 		// new sc document
160 		scDocument = SCUtil.getSCDocument(scComponent);
161 		XSpreadsheets spreadsheets = scDocument.getSheets();
162 
163 		// change the first sheet name and input same value into the sheet cell
164 		String sheetname = "sourcesheet";
165 		SCUtil.setSCSheetNameByIndex(scDocument, (short) 0, sheetname);
166 		String[][] stringValues = { { "", "Jan", "Feb", "Mar", "Apr", "Mai" },
167 				{ "Profit", "12.3", "43.2", "5.1", "76", "56.8" },
168 				{ "Rival in business", "12.2", "12.6", "17.7", "20.4", "100" }, };
169 		XSpreadsheet movesheet = SCUtil
170 				.getSCSheetByIndex(scDocument, (short) 0);
171 		SCUtil.setTextToCellRange(movesheet, 0, 0,stringValues);
172 
173 		// Before move, get the 2nd sheet name
174 		String secondSheetNameBeforeMove = SCUtil.getSCSheetNameByIndex(
175 				scDocument, (short) 1);
176 
177 		// move the first sheet
178 		spreadsheets.moveByName(sheetname, (short) 2);
179 
180 		// Save and reload document
181 		SCUtil.saveFileAs(scComponent, "Testmovesheet", "xls");
182 		XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp,
183 				scDocument, "Testmovesheet.xls");
184 		scDocument = scDocumentTemp;
185 
186 		// After move, get the first sheet name, and verify it same as 2nd sheet
187 		// name before move
188 		String firstsheetnameAfterMove = SCUtil.getSCSheetNameByIndex(
189 				scDocument, (short) 0);
190 		assertEquals("Expect result should be Sheet2",
191 				secondSheetNameBeforeMove, firstsheetnameAfterMove);
192 
193 		// Get the target sheet name after move
194 		String sheetnameAfterMove = SCUtil.getSCSheetNameByIndex(scDocument,
195 				(short) 1);
196 		assertEquals("Expect result should be sourcesheet", sheetname,
197 				sheetnameAfterMove);
198 
199 		// Check the cell value after move
200 		XSpreadsheet sheetaftermove = SCUtil.getSCSheetByIndex(scDocument,
201 				(short) 1);
202 		String[][] stringValuesaftermove = SCUtil.getTextFromCellRange(
203 				sheetaftermove, 0, 0, 5, 2);
204 
205 		assertArrayEquals("Expect result should be stringValues", stringValues,
206 				stringValuesaftermove);
207 	}
208 
209 	/**
210 	 * Test hide and show sheet
211 	 */
212 	@Test
213 	public void hideShowSheet() throws Exception {
214 		// Insert a sheet named hide sheet after first sheet
215 		String sheetname = "hide sheet";
216 		scDocument = SCUtil.getSCDocument(scComponent);
217 		XSpreadsheets spreadsheets = scDocument.getSheets();
218 		spreadsheets.insertNewByName(sheetname, (short) 1);
219 
220 		// active the sheet second sheet "hide sheet"
221 		XSpreadsheet secondSpreadSheet = SCUtil.getSCSheetByIndex(scDocument,
222 				(short) 1);
223 		SCUtil.setCurrentSheet(scDocument, secondSpreadSheet);
224 		// get second sheet name and verify it should be "hide sheet"
225 		assertEquals("expect active sheet name will be hide sheet", sheetname,
226 				SCUtil.getSCSheetNameByIndex(scDocument, (short) 1));
227 
228 		// hide the sheet you insert
229 		XPropertySet sheetPropertySet = (XPropertySet) UnoRuntime
230 				.queryInterface(XPropertySet.class, secondSpreadSheet);
231 		boolean isvisiable = false;
232 		sheetPropertySet.setPropertyValue("IsVisible", isvisiable);
233 
234 		// Save and reload document
235 		SCUtil.saveFileAs(scComponent, "Testhideshowsheet", "xls");
236 		XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp,
237 				scDocument, "Testhideshowsheet.xls");
238 		scDocument = scDocumentTemp;
239 
240 		// get the active sheet name after hide sheet, it should be Sheet2
241 		String sheet2Name = SCUtil.getSCSheetNameByIndex(scDocument, (short) 2);
242 		String activesheetname = SCUtil.getSCActiveSheetName(scDocument);
243 		assertEquals("Expect sheet name should be Sheet2", sheet2Name,
244 				activesheetname);
245 
246 		// show sheet "hide sheet"
247 		sheetPropertySet = (XPropertySet) UnoRuntime.queryInterface(
248 				XPropertySet.class,
249 				SCUtil.getSCSheetByIndex(scDocument, (short) 1));
250 		isvisiable = true;
251 		sheetPropertySet.setPropertyValue("IsVisible", isvisiable);
252 
253 		// active sheet "hide sheet"
254 		secondSpreadSheet = SCUtil.getSCSheetByIndex(scDocument, (short) 1);
255 		SCUtil.setCurrentSheet(scDocument, secondSpreadSheet);
256 
257 		// Get current active sheet name, verify it same as "hide sheet"
258 		String currentactivesheetname = SCUtil.getSCActiveSheetName(scDocument);
259 		assertEquals("Expect active sheet name is hidesheet", sheetname,
260 				currentactivesheetname);
261 		SCUtil.save(scDocument);
262 	}
263 
264 	/**
265 	 * Test sheet tab color
266 	 */
267 	@Test
268 	public void sheetColor() throws Exception {
269 		// get first sheet propertyset
270 		scDocument = SCUtil.getSCDocument(scComponent);
271 		XSpreadsheets spreadsheets = scDocument.getSheets();
272 		XSpreadsheet firstSpreadSheet = SCUtil.getSCSheetByIndex(scDocument,
273 				(short) 0);
274 		XPropertySet sheet1PropertySet = (XPropertySet) UnoRuntime
275 				.queryInterface(XPropertySet.class, firstSpreadSheet);
276 
277 		// Set sheet tab color to 111
278 		sheet1PropertySet.setPropertyValue("TabColor", 111);
279 
280 		// copy the color sheet to new sheet
281 		spreadsheets.copyByName(
282 				SCUtil.getSCSheetNameByIndex(scDocument, (short) 0),
283 				"newsheet", (short) 3);
284 
285 		// Save and reopen the document
286 		SCUtil.saveFileAs(scComponent, "Testcolorsheet", "ods");
287 		XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp,
288 				scDocument, "Testcolorsheet.ods");
289 		scDocument = scDocumentTemp;
290 
291 		// Get first sheet color
292 		sheet1PropertySet = (XPropertySet) UnoRuntime.queryInterface(
293 				XPropertySet.class,
294 				SCUtil.getSCSheetByIndex(scDocument, (short) 0));
295 		int firstSheetcolorid = (Integer) sheet1PropertySet
296 				.getPropertyValue("TabColor");
297 
298 		// Get the copyed sheet color
299 		XPropertySet newsheetPropertySet = (XPropertySet) UnoRuntime
300 				.queryInterface(XPropertySet.class,
301 						SCUtil.getSCSheetByIndex(scDocument, (short) 3));
302 		int copySheetcolorid = (Integer) newsheetPropertySet
303 				.getPropertyValue("TabColor");
304 
305 		// Verify first sheet color changed successfully
306 		assertEquals("Expect color should be 111", 111, firstSheetcolorid);
307 
308 		// Verify first sheet color same as copy sheet color
309 		assertEquals("Expect color should be 111", firstSheetcolorid,
310 				copySheetcolorid);
311 	}
312 
313 	/**
314 	 * test insert sheet from other file
315 	 */
316 	// FIXME: locks up on update link confirmation dialog.
317 	@Test(timeout = 15000)
318 	public void insertSheetFromfile() throws Exception {
319 		// New a document source.xls, add value to 3 sheet
320 		scDocument = SCUtil.getSCDocument(scComponent);
321 		XSpreadsheets spreadsheets = scDocument.getSheets();
322 		XSpreadsheet firstSheet = SCUtil.getSCSheetByIndex(scDocument,
323 				(short) 0);
324 		XSpreadsheet secondSheet = SCUtil.getSCSheetByIndex(scDocument,
325 				(short) 1);
326 		XSpreadsheet thirdSheet = SCUtil.getSCSheetByIndex(scDocument,
327 				(short) 2);
328 		SCUtil.setFormulaToCell(firstSheet, 1, 2, "=2*2");
329 		SCUtil.setFormulaToCell(secondSheet, 1, 2, "=2*2");
330 		SCUtil.setFormulaToCell(thirdSheet, 1, 2, "=2*2");
331 
332 		// Save and close this document
333 		SCUtil.saveFileAs(scComponent, "source", "xls");
334 		SCUtil.closeFile(scDocument);
335 
336 		// get source document URL
337 		String SourcestoreUrl = Testspace.getUrl("output/sc/" + "source" + "."
338 				+ "xls");
339 
340 		// New a document
341 		scComponent = unoApp.newDocument("scalc");
342 		scDocument = SCUtil.getSCDocument(scComponent);
343 		spreadsheets = scDocument.getSheets();
344 		// Insert firstexternalsheet sheet, link with Sheet1 in source document
345 		// and the link mode is NORMAL
346 		spreadsheets.insertNewByName("firstexternalsheet", (short) 3);
347 		XSpreadsheet firstexternalsheet = SCUtil.getSCSheetByIndex(scDocument,
348 				(short) 3);
349 		XSheetLinkable xfirstSheetLinkable = (XSheetLinkable) UnoRuntime
350 				.queryInterface(XSheetLinkable.class, firstexternalsheet);
351 		xfirstSheetLinkable.link(SourcestoreUrl, "", "MS Excel 97", "",
352 				SheetLinkMode.NORMAL);
353 
354 		// Insert secondexternalsheet sheet, link with Sheet2 in source document
355 		// and the link mode is VALUE
356 		spreadsheets.insertNewByName("secondexternalsheet", (short) 4);
357 		XSpreadsheet secondexternalsheet = SCUtil.getSCSheetByIndex(scDocument,
358 				(short) 4);
359 		XSheetLinkable xsecondSheetLinkable = (XSheetLinkable) UnoRuntime
360 				.queryInterface(XSheetLinkable.class, secondexternalsheet);
361 		xsecondSheetLinkable.link(SourcestoreUrl, "Sheet2", "MS Excel 97", "",
362 				SheetLinkMode.VALUE);
363 
364 		// Insert secondexternalsheet sheet, link with Sheet2 in source document
365 		// and the link mode is NONE
366 		spreadsheets.insertNewByName("thirdexternalsheet", (short) 5);
367 		XSpreadsheet thirdexternalsheet = SCUtil.getSCSheetByIndex(scDocument,
368 				(short) 5);
369 		XSheetLinkable xthirdSheetLinkable = (XSheetLinkable) UnoRuntime
370 				.queryInterface(XSheetLinkable.class, thirdexternalsheet);
371 		xthirdSheetLinkable.link(SourcestoreUrl, "Sheet3", "MS Excel 97", "",
372 				SheetLinkMode.NONE);
373 
374 		// Verify firstexternalsheet
375 		assertEquals("Expect formula should be =2*2", "=2*2",
376 				SCUtil.getFormulaFromCell(firstexternalsheet, 1, 2));
377 		assertEquals("Expect formula result should be 4", "4",
378 				SCUtil.getTextFromCell(firstexternalsheet, 1, 2));
379 
380 		// Verify secondexternalsheet
381 		assertEquals("Expect formula should be 4", "4",
382 				SCUtil.getFormulaFromCell(secondexternalsheet, 1, 2));
383 		assertEquals("Expect formula result should be 4", "4",
384 				SCUtil.getTextFromCell(secondexternalsheet, 1, 2));
385 
386 		// Verify thirdexternalsheet
387 		assertEquals("Expect formula should be blank", "",
388 				SCUtil.getFormulaFromCell(thirdexternalsheet, 1, 2));
389 		assertEquals("Expect formula result should be blank", "",
390 				SCUtil.getTextFromCell(thirdexternalsheet, 1, 2));
391 
392 		// save document and verify the linked sheet again
393 		SCUtil.saveFileAs(scComponent, "linked", "ods");
394 		XSpreadsheetDocument tempscDocument = SCUtil.reloadFile(unoApp,
395 				scDocument, "linked.ods");
396 		scDocument = tempscDocument;
397 		firstexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 3);
398 		secondexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 4);
399 		thirdexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 5);
400 
401 		// Verify firstexternalsheet
402 		assertEquals("Expect formula should be =2*2", "=2*2",
403 				SCUtil.getFormulaFromCell(firstexternalsheet, 1, 2));
404 		assertEquals("Expect formula result should be 4", "4",
405 				SCUtil.getTextFromCell(firstexternalsheet, 1, 2));
406 
407 		// Verify secondexternalsheet
408 		assertEquals("Expect formula should be 4", "4",
409 				SCUtil.getFormulaFromCell(secondexternalsheet, 1, 2));
410 		assertEquals("Expect formula result should be 4", "4",
411 				SCUtil.getTextFromCell(secondexternalsheet, 1, 2));
412 
413 		// Verify thirdexternalsheet
414 		assertEquals("Expect formula should be blank", "",
415 				SCUtil.getFormulaFromCell(thirdexternalsheet, 1, 2));
416 		assertEquals("Expect formula result should be blank", "",
417 				SCUtil.getTextFromCell(thirdexternalsheet, 1, 2));
418 
419 		//save and close document
420 		SCUtil.save(scDocument);
421 		SCUtil.closeFile(scDocument);
422 
423 		//Open souce document and change the value in souce document
424 		XSpreadsheetDocument sourcescDocument = SCUtil.reloadFile(unoApp,
425 				scDocument, "source.xls");
426 		firstSheet = SCUtil.getSCSheetByIndex(sourcescDocument, (short) 0);
427 		secondSheet = SCUtil.getSCSheetByIndex(sourcescDocument, (short) 1);
428 		thirdSheet = SCUtil.getSCSheetByIndex(sourcescDocument, (short) 2);
429 		SCUtil.setFormulaToCell(firstSheet, 1, 2, "=3*3");
430 		SCUtil.setFormulaToCell(secondSheet, 1, 2, "=3*3");
431 		SCUtil.setFormulaToCell(thirdSheet, 1, 2, "=3*3");
432 		SCUtil.save(sourcescDocument);
433 		SCUtil.closeFile(sourcescDocument);
434 
435 		//Open link document
436 		tempscDocument = SCUtil.reloadFile(unoApp, scDocument, "linked.ods");
437 		scDocument = tempscDocument;
438 		spreadsheets = scDocument.getSheets();
439 
440 		firstexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 3);
441 		secondexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 4);
442 		thirdexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 5);
443 
444 		//get Object SheetLinks for document
445 		XPropertySet sheetpropertyset = (XPropertySet) UnoRuntime
446 				.queryInterface(XPropertySet.class, scDocument);
447 		Object sheetLinks = sheetpropertyset.getPropertyValue("SheetLinks");
448 
449 		XIndexAccess xsheetlinks = (XIndexAccess) UnoRuntime.queryInterface(
450 				XIndexAccess.class, sheetLinks);
451 
452 		//Refresh all links
453 		for (int i = 0; i < xsheetlinks.getCount(); i++) {
454 			Object sheetlink = xsheetlinks.getByIndex(i);
455 			XRefreshable xsheetRefreshable = (XRefreshable) UnoRuntime
456 					.queryInterface(XRefreshable.class, sheetlink);
457 			xsheetRefreshable.refresh();
458 		}
459 
460 		// Verify firstexternalsheet
461 		assertEquals("Expect formula should be =3*3", "=3*3",
462 				SCUtil.getFormulaFromCell(firstexternalsheet, 1, 2));
463 		assertEquals("Expect formula result should be 9", "9",
464 				SCUtil.getTextFromCell(firstexternalsheet, 1, 2));
465 
466 		// Verify secondexternalsheet
467 		assertEquals("Expect formula should be 9", "9",
468 				SCUtil.getFormulaFromCell(secondexternalsheet, 1, 2));
469 		assertEquals("Expect formula result should be 9", "9",
470 				SCUtil.getTextFromCell(secondexternalsheet, 1, 2));
471 
472 		// Verify thirdexternalsheet
473 		assertEquals("Expect formula should be blank", "",
474 				SCUtil.getFormulaFromCell(thirdexternalsheet, 1, 2));
475 		assertEquals("Expect formula result should be blank", "",
476 				SCUtil.getTextFromCell(thirdexternalsheet, 1, 2));
477 
478 		//Save the document before close
479 		SCUtil.save(scDocument);
480 
481 	}
482 
483 }
484