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 package testlib.uno;
24 
25 import java.util.HashMap;
26 
27 import org.openoffice.test.common.FileUtil;
28 import org.openoffice.test.common.Testspace;
29 import org.openoffice.test.uno.UnoApp;
30 
31 import com.sun.star.beans.PropertyValue;
32 import com.sun.star.beans.XPropertySet;
33 import com.sun.star.container.XIndexAccess;
34 import com.sun.star.container.XNamed;
35 import com.sun.star.frame.XController;
36 import com.sun.star.frame.XModel;
37 import com.sun.star.frame.XStorable;
38 import com.sun.star.lang.XComponent;
39 import com.sun.star.sheet.XSpreadsheet;
40 import com.sun.star.sheet.XSpreadsheetDocument;
41 import com.sun.star.sheet.XSpreadsheetView;
42 import com.sun.star.sheet.XSpreadsheets;
43 import com.sun.star.table.XCell;
44 import com.sun.star.table.XCellRange;
45 import com.sun.star.table.XColumnRowRange;
46 import com.sun.star.table.XTableColumns;
47 import com.sun.star.table.XTableRows;
48 import com.sun.star.text.XText;
49 import com.sun.star.uno.UnoRuntime;
50 import com.sun.star.util.XCloseable;
51 
52 
53 /**
54  * Utilities of Spreadsheet
55  *
56  */
57 
58 public class SCUtil {
59 
60 	private static final String scTempDir = "output/sc/"; //Spreadsheet temp file directory
61 	private static HashMap filterName = new HashMap();
62 
63 	private SCUtil() {
64 
65 	}
66 
67 	/**
68 	 * Get spreadsheet document object
69 	 * @param xSpreadsheetComponent
70 	 * @return
71 	 * @throws Exception
72 	 */
73     public static XSpreadsheetDocument getSCDocument(XComponent xSpreadsheetComponent) throws Exception {
74     	XSpreadsheetDocument xSpreadsheetDocument =
75         		(XSpreadsheetDocument) UnoRuntime.queryInterface(XSpreadsheetDocument.class, xSpreadsheetComponent);
76 
77         return xSpreadsheetDocument;
78     }
79 
80     /**
81      * Get sheet object by sheet name
82      * @param xSpreadsheetDocument
83      * @param sheetName
84      * @return
85      * @throws Exception
86      */
87 	public static XSpreadsheet getSCSheetByName(XSpreadsheetDocument xSpreadsheetDocument, String sheetName) throws Exception {
88 		XSpreadsheets xSpreadsheets = xSpreadsheetDocument.getSheets();
89 		XSpreadsheet xSpreadsheet =
90 				(XSpreadsheet) UnoRuntime.queryInterface(XSpreadsheet.class, xSpreadsheets.getByName(sheetName));
91 
92 		return xSpreadsheet;
93 	}
94 
95 	/**
96 	 * Get sheet object by sheet index
97 	 * @param xSpreadsheetDocument
98 	 * @param index   (Short) 0,1,2,...
99 	 * @return
100 	 * @throws Exception
101 	 */
102 	public static XSpreadsheet getSCSheetByIndex(XSpreadsheetDocument xSpreadsheetDocument, short index) throws Exception {
103 		XSpreadsheets xSpreadsheets = xSpreadsheetDocument.getSheets();
104 		XIndexAccess xIndexAccess =
105 				(XIndexAccess) UnoRuntime.queryInterface(XIndexAccess.class, xSpreadsheets);
106 		XSpreadsheet xSpreadsheet =
107 				(XSpreadsheet) UnoRuntime.queryInterface(XSpreadsheet.class, xIndexAccess.getByIndex(index));
108 
109 		return xSpreadsheet;
110 	}
111 
112 	/**
113 	 * Get sheet name by sheet index
114 	 *
115 	 * @param xSpreadsheetDocument
116 	 * @param index
117 	 *            (Short) 0,1,2,...
118 	 * @return
119 	 * @throws Exception
120 	 */
121 	public static String getSCSheetNameByIndex(
122 			XSpreadsheetDocument xSpreadsheetDocument, short index)
123 			throws Exception {
124 		XSpreadsheets xSpreadsheets = xSpreadsheetDocument.getSheets();
125 		XIndexAccess xIndexAccess = (XIndexAccess) UnoRuntime.queryInterface(
126 				XIndexAccess.class, xSpreadsheets);
127 		XSpreadsheet xSpreadsheet = (XSpreadsheet) UnoRuntime.queryInterface(
128 				XSpreadsheet.class, xIndexAccess.getByIndex(index));
129 		XNamed xsheetname = (XNamed) UnoRuntime.queryInterface(XNamed.class,
130 				xSpreadsheet);
131 		return xsheetname.getName();
132 	}
133 
134 	/**
135 	 * Set sheet name by sheet index
136 	 *
137 	 * @param xSpreadsheetDocument
138 	 * @param index
139 	 *            (Short) 0,1,2,...
140 	 * @return
141 	 * @throws Exception
142 	 */
143 	public static void setSCSheetNameByIndex(
144 			XSpreadsheetDocument xSpreadsheetDocument, short index,
145 			String sheetname) throws Exception {
146 		XSpreadsheets xSpreadsheets = xSpreadsheetDocument.getSheets();
147 		XIndexAccess xIndexAccess = (XIndexAccess) UnoRuntime.queryInterface(
148 				XIndexAccess.class, xSpreadsheets);
149 		XSpreadsheet xSpreadsheet = (XSpreadsheet) UnoRuntime.queryInterface(
150 				XSpreadsheet.class, xIndexAccess.getByIndex(index));
151 		XNamed xsheetname = (XNamed) UnoRuntime.queryInterface(XNamed.class,
152 				xSpreadsheet);
153 		xsheetname.setName(sheetname);
154 	}
155 
156 	/**
157 	 * Get rows object
158 	 * @param xSpreadsheet
159 	 * @return
160 	 * @throws Exception
161 	 */
162 	public static XTableRows getSCRows(XSpreadsheet xSpreadsheet) throws Exception {
163 		XColumnRowRange xColumnRowRange =
164 				(XColumnRowRange) UnoRuntime.queryInterface(XColumnRowRange.class, xSpreadsheet);
165 		XTableRows xTableRows = xColumnRowRange.getRows();
166 
167 		return xTableRows;
168 	}
169 
170 	/**
171 	 * Get columns object
172 	 * @param xSpreadsheet
173 	 * @return
174 	 * @throws Exception
175 	 */
176 	public static XTableColumns getSCColumns(XSpreadsheet xSpreadsheet) throws Exception {
177 		XColumnRowRange xColumnRowRange =
178 				(XColumnRowRange) UnoRuntime.queryInterface(XColumnRowRange.class, xSpreadsheet);
179 		XTableColumns xTableColumns = xColumnRowRange.getColumns();
180 
181 		return xTableColumns;
182 	}
183 
184 	/**
185 	 * Set floating number into specific cell
186 	 * @param xSpreadsheet
187 	 * @param column
188 	 * @param row
189 	 * @param value
190 	 * @throws Exception
191 	 */
192 	public static void setValueToCell(XSpreadsheet xSpreadsheet, int column, int row, double value) throws Exception {
193 		XCell xCell = xSpreadsheet.getCellByPosition(column, row);
194 		xCell.setValue(value);
195 	}
196 
197 	/**
198 	 * Set text into specific cell
199 	 * @param xSpreadsheet
200 	 * @param column
201 	 * @param row
202 	 * @param text
203 	 * @throws Exception
204 	 */
205 	public static void setTextToCell(XSpreadsheet xSpreadsheet, int column, int row, String text) throws Exception {
206 		XCell xCell = xSpreadsheet.getCellByPosition(column, row);
207 		XText xText = (XText) UnoRuntime.queryInterface(XText.class, xCell);
208 		xText.setString(text);
209 	}
210 
211 	/**
212 	 * Set text into specific cell
213 	 * @param xCell
214 	 * @param text
215 	 * @throws Exception
216 	 */
217 	public static void setTextToCell(XCell xCell, String text) throws Exception {
218 		XText xText = (XText) UnoRuntime.queryInterface(XText.class, xCell);
219 		xText.setString(text);
220 	}
221 
222 	/**
223 	 * Set formula into specific cell
224 	 * @param xSpreadsheet
225 	 * @param column
226 	 * @param row
227 	 * @param formula
228 	 * @throws Exception
229 	 */
230 	public static void setFormulaToCell(XSpreadsheet xSpreadsheet, int column, int row, String formula) throws Exception {
231 		XCell xCell = xSpreadsheet.getCellByPosition(column, row);
232 		xCell.setFormula(formula);
233 	}
234 
235 	/**
236 	 * Get value from specific cell
237 	 * @param xSpreadsheet
238 	 * @param column
239 	 * @param row
240 	 * @return
241 	 * @throws Exception
242 	 */
243 	public static double getValueFromCell(XSpreadsheet xSpreadsheet, int column, int row) throws Exception {
244 		XCell xCell = xSpreadsheet.getCellByPosition(column, row);
245 		double cellValue = xCell.getValue();
246 
247 		return cellValue;
248 	}
249 
250 	/**
251 	 * Get text from specific cell
252 	 * @param xSpreadsheet
253 	 * @param column
254 	 * @param row
255 	 *
256 	 * @return
257 	 * @throws Exception
258 	 */
259 	public static String getTextFromCell(XSpreadsheet xSpreadsheet, int column, int row) throws Exception {
260 		XCell xCell = xSpreadsheet.getCellByPosition(column, row);
261 		XText xText = (XText) UnoRuntime.queryInterface(XText.class, xCell);
262 
263 		return xText.getString();
264 	}
265 
266 	/**
267 	 * Get formula string from specific cell
268 	 * @param xSpreadsheet
269 	 * @param column
270 	 * @param row
271 	 * @return
272 	 * @throws Exception
273 	 */
274 	public static String getFormulaFromCell(XSpreadsheet xSpreadsheet, int column, int row) throws Exception {
275 		XCell xCell = xSpreadsheet.getCellByPosition(column, row);
276 		String cellFormula = xCell.getFormula();
277 
278 		return cellFormula;
279 	}
280 
281 	/**
282 	 * Set numbers into a cell range
283 	 * @param xSpreadsheet
284 	 * @param start_col
285 	 * @param start_row
286 	 * @param end_col
287 	 * @param end_row
288 	 * @param values
289 	 * @throws Exception
290 	 */
291 	public static void setValueToCellRange(XSpreadsheet xSpreadsheet, int start_col, int start_row, int end_col, int end_row,  double[][] values) throws Exception {
292 		XCellRange xCellRange = xSpreadsheet.getCellRangeByPosition(start_col, start_row, end_col, end_row);
293 		XCell xCell = null;
294 		for (int i = 0; i <= (end_row - start_row); i++ ) {
295 			for(int j = 0; j <= (end_col - start_col); j++) {
296 				xCell = xCellRange.getCellByPosition(j, i);
297 				xCell.setValue(values[i][j]);
298 			}
299 		}
300 	}
301 
302 	/**
303 	 * Set text into a cell range
304 	 * @param xSpreadsheet
305 	 * @param start_col
306 	 * @param start_row
307 	 * @param end_col
308 	 * @param end_row
309 	 * @param texts
310 	 * @throws Exception
311 	 */
312 	public static void setTextToCellRange(XSpreadsheet xSpreadsheet, int start_col, int start_row, int end_col, int end_row,  String[][] texts) throws Exception {
313 		XCellRange xCellRange = xSpreadsheet.getCellRangeByPosition(start_col, start_row, end_col, end_row);
314 		XCell xCell = null;
315 		XText xText = null;
316 		for (int i = 0; i <= (end_row - start_row); i++ ) {
317 			for(int j = 0; j <= (end_col - start_col); j++) {
318 				xCell = xCellRange.getCellByPosition(j, i);
319 				xText = (XText) UnoRuntime.queryInterface(XText.class, xCell);
320 				xText.setString(texts[i][j]);
321 			}
322 		}
323 	}
324 
325 	/**
326 	 * Get number content from a cell range
327 	 * @param xSpreadsheet
328 	 * @param start_col
329 	 * @param start_row
330 	 * @param end_col
331 	 * @param end_row
332 	 * @return
333 	 * @throws Exception
334 	 */
335 	public static double[][] getValueFromCellRange(XSpreadsheet xSpreadsheet, int start_col, int start_row, int end_col, int end_row) throws Exception {
336 		XCellRange xCellRange = xSpreadsheet.getCellRangeByPosition(start_col, start_row, end_col, end_row);
337 		XCell xCell = null;
338 		double[][] cellValues = new double[end_row - start_row+1][end_col - start_col +1];
339 
340 		for (int i = 0; i <= (end_row - start_row); i++ ) {
341 			for(int j = 0; j <= (end_col - start_col); j++) {
342 				xCell = xCellRange.getCellByPosition(j, i);
343 				cellValues[i][j] = xCell.getValue();
344 			}
345 		}
346 
347 		return cellValues;
348 	}
349 
350 	/**
351 	 * Get text content from a cell range
352 	 * @param xSpreadsheet
353 	 * @param start_col
354 	 * @param start_row
355 	 * @param end_col
356 	 * @param end_row
357 	 * @return
358 	 * @throws Exception
359 	 */
360 	public static String[][] getTextFromCellRange(XSpreadsheet xSpreadsheet, int start_col, int start_row, int end_col, int end_row) throws Exception {
361 		XCellRange xCellRange = xSpreadsheet.getCellRangeByPosition(start_col, start_row, end_col, end_row);
362 		XCell xCell = null;
363 		XText xText = null;
364 		String[][] cellTexts = new String[end_row - start_row+1][end_col - start_col +1];
365 
366 		for (int i = 0; i <= (end_row - start_row); i++ ) {
367 			for (int j = 0; j <= (end_col - start_col); j++) {
368 				xCell = xCellRange.getCellByPosition(j, i);
369 				xText = (XText) UnoRuntime.queryInterface(XText.class, xCell);
370 				cellTexts[i][j] = xText.getString();
371 			}
372 		}
373 
374 		return cellTexts;
375 	}
376 
377 	//TODO ZS - public static String[][] getAllFromCellRange
378 
379 	/**
380 	 * Switch to specific sheet
381 	 * @param xSpreadsheetDocument
382 	 * @param xSpreadsheet
383 	 */
384 	public static void setCurrentSheet(XSpreadsheetDocument xSpreadsheetDocument, XSpreadsheet xSpreadsheet) throws Exception {
385 		XModel xModel = (XModel) UnoRuntime.queryInterface(XModel.class, xSpreadsheetDocument);
386 		XController xController = xModel.getCurrentController();
387 		XSpreadsheetView xSpreadsheetView = (XSpreadsheetView) UnoRuntime.queryInterface(XSpreadsheetView.class, xController);
388 		xSpreadsheetView.setActiveSheet(xSpreadsheet);
389 	}
390 
391 	/**
392 	 * Get sheet object of current active sheet
393 	 * @param xSpreadsheetDocument
394 	 * @return
395 	 */
396 	public static XSpreadsheet getCurrentSheet(XSpreadsheetDocument xSpreadsheetDocument) throws Exception {
397 		XModel xModel = (XModel) UnoRuntime.queryInterface(XModel.class, xSpreadsheetDocument);
398 		XController xController = xModel.getCurrentController();
399 		XSpreadsheetView xSpreadsheetView = (XSpreadsheetView) UnoRuntime.queryInterface(XSpreadsheetView.class, xController);
400 		XSpreadsheet xSpreadsheet = xSpreadsheetView.getActiveSheet();
401 
402 		return xSpreadsheet;
403 	}
404 
405 	/**
406 	 * Get sheet object by sheet index
407 	 *
408 	 * @param xSpreadsheetDocument
409 	 * @return
410 	 * @throws Exception
411 	 */
412 	public static String getSCActiveSheetName(
413 			XSpreadsheetDocument xSpreadsheetDocument) throws Exception {
414 		XModel xSpreadsheetModel = (XModel) UnoRuntime.queryInterface(
415 				XModel.class, xSpreadsheetDocument);
416 		XSpreadsheetView xSpeadsheetView = (XSpreadsheetView) UnoRuntime
417 				.queryInterface(XSpreadsheetView.class,
418 						xSpreadsheetModel.getCurrentController());
419 		XSpreadsheet activesheet = xSpeadsheetView.getActiveSheet();
420 		XNamed activesheetName = (XNamed) UnoRuntime.queryInterface(
421 				XNamed.class, activesheet);
422 		return activesheetName.getName();
423 	}
424 
425 	/**
426 	 * Set value of specific property from a cell
427 	 * @param xCell
428 	 * @param propName
429 	 * @param value
430 	 * @throws Exception
431 	 */
432 	public static void setCellProperties(XCell xCell, String propName, Object value) throws Exception {
433 
434 		XPropertySet xPropertySet = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, xCell);
435 		xPropertySet.setPropertyValue(propName, value);
436 	}
437 
438 	/**
439 	 * Get value of specific property from a cell
440 	 * @param xCell
441 	 * @param propName
442 	 * @return
443 	 * @throws Exception
444 	 */
445 	public static Object getCellProperties(XCell xCell, String propName) throws Exception {
446 		XPropertySet xPropertySet = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, xCell);
447 		Object value = xPropertySet.getPropertyValue(propName);
448 
449 		return value;
450 	}
451 
452 	/**
453 	 * Clear temp file directory
454 	 */
455 	public static void clearTempDir() {
456 		FileUtil.deleteFile(Testspace.getFile(Testspace.getPath(scTempDir)));
457 	}
458 
459 	/**
460 	 * Save file as specific file format into spreadsheet temp file folder.
461 	 * @param scComponent
462 	 * @param fileName  File name string without extension name (e.g. "sampleFile")
463 	 * @param extName ("ods", "ots", "xls", "xlt", "csv")
464 	 * @throws Exception
465 	 */
466 	public static void saveFileAs(XComponent scComponent, String fileName, String extName) throws Exception {
467 
468 		initFilterName();
469 
470 		String storeUrl = Testspace.getUrl(scTempDir + fileName + "." + extName);
471 
472 		PropertyValue[] storeProps = new PropertyValue[2];
473 		storeProps[0] = new PropertyValue();
474 		storeProps[0].Name = "FilterName";
475 		storeProps[0].Value = filterName.get(extName);
476 		storeProps[1] = new PropertyValue();
477 		storeProps[1].Name = "Overwrite";
478 		storeProps[1].Value = new Boolean(true);
479 
480 		XStorable scStorable =
481 				(XStorable) UnoRuntime.queryInterface(XStorable.class, scComponent);
482 		scStorable.storeAsURL(storeUrl, storeProps);
483 	}
484 
485 	/**
486 	 * Save file after open file.
487 	 *
488 	 * @param xSpreadsheetDocument
489 	 * @throws Exception
490 	 */
491 	public static void save(XSpreadsheetDocument xSpreadsheetDocument)
492 			throws Exception {
493 
494 		XStorable scStorable = (XStorable) UnoRuntime.queryInterface(
495 				XStorable.class, xSpreadsheetDocument);
496 		scStorable.store();
497 
498 	}
499 
500 
501 	/**
502 	 * Close specific opening spreadsheet file which has been saved
503 	 * @param xSpreadsheetDocument
504 	 * @throws Exception
505 	 */
506 	public static void closeFile(XSpreadsheetDocument xSpreadsheetDocument) throws Exception {
507 		XCloseable xCloseable = (XCloseable) UnoRuntime.queryInterface(XCloseable.class, xSpreadsheetDocument);
508 		xCloseable.close(false);
509 	}
510 
511 	/**
512 	 * Close a opening file saved in spreadsheet temp file direction and reopen it in Spreadsheet. For save&reload test scenario only.
513 	 * @param unoApp
514 	 * @param xSpreadsheetDocument
515 	 * @param fullFileName   File name with the extension name. (e.g. "sc.ods")
516 	 * @return
517 	 * @throws Exception
518 	 */
519 	public static XSpreadsheetDocument reloadFile(UnoApp unoApp, XSpreadsheetDocument xSpreadsheetDocument, String fullFileName) throws Exception {
520 		closeFile(xSpreadsheetDocument);
521 
522 		String filePath = Testspace.getPath(scTempDir + fullFileName);
523 		XSpreadsheetDocument xScDocument = UnoRuntime.queryInterface(XSpreadsheetDocument.class, unoApp.loadDocument(filePath));
524 
525 		return xScDocument;
526 	}
527 
528 	/**
529 	 * open file in Spreadsheet.
530 	 * @param unoApp
531 	 * @param filtpath   File path with the extension name. (e.g. "testcase/uno/sc/data/sample.xls")
532 	 * @return
533 	 * @throws Exception
534 	 */
535 	public static XSpreadsheetDocument openFile(String filePath, UnoApp app) throws Exception {
536 		return (XSpreadsheetDocument) UnoRuntime.queryInterface(XSpreadsheetDocument.class, app.loadDocument(filePath));
537 	}
538 
539 	/**
540 	 * Initial the filter name list
541 	 * @throws Exception
542 	 */
543 	private static void initFilterName() throws Exception {
544 		if (filterName.size() > 0) {
545 			return;
546 		}
547 
548 		filterName.put("ods", "calc8");
549 		filterName.put("ots", "calc8_template");
550 		filterName.put("xls", "MS Excel 97");
551 		filterName.put("xlt", "MS Excel 97 Vorlage/Template");
552 		filterName.put("csv", "Text - txt - csv (StarCalc)");
553 	}
554 
555 }
556