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.awt.Rectangle;
32 import com.sun.star.beans.PropertyValue;
33 import com.sun.star.beans.XPropertySet;
34 import com.sun.star.chart.XChartDocument;
35 import com.sun.star.chart.XDiagram;
36 import com.sun.star.container.XIndexAccess;
37 import com.sun.star.container.XNameAccess;
38 import com.sun.star.container.XNamed;
39 import com.sun.star.document.XEmbeddedObjectSupplier;
40 import com.sun.star.frame.XController;
41 import com.sun.star.frame.XModel;
42 import com.sun.star.frame.XStorable;
43 import com.sun.star.lang.XComponent;
44 import com.sun.star.lang.XMultiServiceFactory;
45 import com.sun.star.sheet.XCellRangeAddressable;
46 import com.sun.star.sheet.XSpreadsheet;
47 import com.sun.star.sheet.XSpreadsheetDocument;
48 import com.sun.star.sheet.XSpreadsheetView;
49 import com.sun.star.sheet.XSpreadsheets;
50 import com.sun.star.table.CellRangeAddress;
51 import com.sun.star.table.XCell;
52 import com.sun.star.table.XCellRange;
53 import com.sun.star.table.XColumnRowRange;
54 import com.sun.star.table.XTableChart;
55 import com.sun.star.table.XTableCharts;
56 import com.sun.star.table.XTableChartsSupplier;
57 import com.sun.star.table.XTableColumns;
58 import com.sun.star.table.XTableRows;
59 import com.sun.star.text.XText;
60 import com.sun.star.uno.UnoRuntime;
61 import com.sun.star.util.XCloseable;
62 
63 
64 /**
65  * Utilities of Spreadsheet
66  *
67  */
68 
69 public class SCUtil {
70 
71 	private static final String scTempDir = "output/sc/"; //Spreadsheet temp file directory
72 	private static HashMap filterName = new HashMap();
73 
74 	private SCUtil() {
75 
76 	}
77 
78 	/**
79 	 * Get spreadsheet document object
80 	 * @param xSpreadsheetComponent
81 	 * @return
82 	 * @throws Exception
83 	 */
84     public static XSpreadsheetDocument getSCDocument(XComponent xSpreadsheetComponent) throws Exception {
85     	XSpreadsheetDocument xSpreadsheetDocument =
86         		(XSpreadsheetDocument) UnoRuntime.queryInterface(XSpreadsheetDocument.class, xSpreadsheetComponent);
87 
88         return xSpreadsheetDocument;
89     }
90 
91     /**
92      * Get sheet object by sheet name
93      * @param xSpreadsheetDocument
94      * @param sheetName
95      * @return
96      * @throws Exception
97      */
98 	public static XSpreadsheet getSCSheetByName(XSpreadsheetDocument xSpreadsheetDocument, String sheetName) throws Exception {
99 		XSpreadsheets xSpreadsheets = xSpreadsheetDocument.getSheets();
100 		XSpreadsheet xSpreadsheet =
101 				(XSpreadsheet) UnoRuntime.queryInterface(XSpreadsheet.class, xSpreadsheets.getByName(sheetName));
102 
103 		return xSpreadsheet;
104 	}
105 
106 	/**
107 	 * Get sheet object by sheet index
108 	 * @param xSpreadsheetDocument
109 	 * @param index   (Short) 0,1,2,...
110 	 * @return
111 	 * @throws Exception
112 	 */
113 	public static XSpreadsheet getSCSheetByIndex(XSpreadsheetDocument xSpreadsheetDocument, short index) throws Exception {
114 		XSpreadsheets xSpreadsheets = xSpreadsheetDocument.getSheets();
115 		XIndexAccess xIndexAccess =
116 				(XIndexAccess) UnoRuntime.queryInterface(XIndexAccess.class, xSpreadsheets);
117 		XSpreadsheet xSpreadsheet =
118 				(XSpreadsheet) UnoRuntime.queryInterface(XSpreadsheet.class, xIndexAccess.getByIndex(index));
119 
120 		return xSpreadsheet;
121 	}
122 
123 	/**
124 	 * Get sheet name by sheet index
125 	 *
126 	 * @param xSpreadsheetDocument
127 	 * @param index
128 	 *            (Short) 0,1,2,...
129 	 * @return
130 	 * @throws Exception
131 	 */
132 	public static String getSCSheetNameByIndex(
133 			XSpreadsheetDocument xSpreadsheetDocument, short index)
134 			throws Exception {
135 		XSpreadsheets xSpreadsheets = xSpreadsheetDocument.getSheets();
136 		XIndexAccess xIndexAccess = (XIndexAccess) UnoRuntime.queryInterface(
137 				XIndexAccess.class, xSpreadsheets);
138 		XSpreadsheet xSpreadsheet = (XSpreadsheet) UnoRuntime.queryInterface(
139 				XSpreadsheet.class, xIndexAccess.getByIndex(index));
140 		XNamed xsheetname = (XNamed) UnoRuntime.queryInterface(XNamed.class,
141 				xSpreadsheet);
142 		return xsheetname.getName();
143 	}
144 
145 	/**
146 	 * Set sheet name by sheet index
147 	 *
148 	 * @param xSpreadsheetDocument
149 	 * @param index
150 	 *            (Short) 0,1,2,...
151 	 * @return
152 	 * @throws Exception
153 	 */
154 	public static void setSCSheetNameByIndex(
155 			XSpreadsheetDocument xSpreadsheetDocument, short index,
156 			String sheetname) throws Exception {
157 		XSpreadsheets xSpreadsheets = xSpreadsheetDocument.getSheets();
158 		XIndexAccess xIndexAccess = (XIndexAccess) UnoRuntime.queryInterface(
159 				XIndexAccess.class, xSpreadsheets);
160 		XSpreadsheet xSpreadsheet = (XSpreadsheet) UnoRuntime.queryInterface(
161 				XSpreadsheet.class, xIndexAccess.getByIndex(index));
162 		XNamed xsheetname = (XNamed) UnoRuntime.queryInterface(XNamed.class,
163 				xSpreadsheet);
164 		xsheetname.setName(sheetname);
165 	}
166 
167 	/**
168 	 * Get rows object
169 	 * @param xSpreadsheet
170 	 * @return
171 	 * @throws Exception
172 	 */
173 	public static XTableRows getSCRows(XSpreadsheet xSpreadsheet) throws Exception {
174 		XColumnRowRange xColumnRowRange =
175 				(XColumnRowRange) UnoRuntime.queryInterface(XColumnRowRange.class, xSpreadsheet);
176 		XTableRows xTableRows = xColumnRowRange.getRows();
177 
178 		return xTableRows;
179 	}
180 
181 	/**
182 	 * Get columns object
183 	 * @param xSpreadsheet
184 	 * @return
185 	 * @throws Exception
186 	 */
187 	public static XTableColumns getSCColumns(XSpreadsheet xSpreadsheet) throws Exception {
188 		XColumnRowRange xColumnRowRange =
189 				(XColumnRowRange) UnoRuntime.queryInterface(XColumnRowRange.class, xSpreadsheet);
190 		XTableColumns xTableColumns = xColumnRowRange.getColumns();
191 
192 		return xTableColumns;
193 	}
194 
195 	/**
196 	 * Set floating number into specific cell
197 	 * @param xSpreadsheet
198 	 * @param column
199 	 * @param row
200 	 * @param value
201 	 * @throws Exception
202 	 */
203 	public static void setValueToCell(XSpreadsheet xSpreadsheet, int column, int row, double value) throws Exception {
204 		XCell xCell = xSpreadsheet.getCellByPosition(column, row);
205 		xCell.setValue(value);
206 	}
207 
208 	/**
209 	 * Set text into specific cell
210 	 * @param xSpreadsheet
211 	 * @param column
212 	 * @param row
213 	 * @param text
214 	 * @throws Exception
215 	 */
216 	public static void setTextToCell(XSpreadsheet xSpreadsheet, int column, int row, String text) throws Exception {
217 		XCell xCell = xSpreadsheet.getCellByPosition(column, row);
218 		XText xText = (XText) UnoRuntime.queryInterface(XText.class, xCell);
219 		xText.setString(text);
220 	}
221 
222 	/**
223 	 * Set text into specific cell
224 	 * @param xCell
225 	 * @param text
226 	 * @throws Exception
227 	 */
228 	public static void setTextToCell(XCell xCell, String text) throws Exception {
229 		XText xText = (XText) UnoRuntime.queryInterface(XText.class, xCell);
230 		xText.setString(text);
231 	}
232 
233 	/**
234 	 * Set formula into specific cell
235 	 * @param xSpreadsheet
236 	 * @param column
237 	 * @param row
238 	 * @param formula
239 	 * @throws Exception
240 	 */
241 	public static void setFormulaToCell(XSpreadsheet xSpreadsheet, int column, int row, String formula) throws Exception {
242 		XCell xCell = xSpreadsheet.getCellByPosition(column, row);
243 		xCell.setFormula(formula);
244 	}
245 
246 	/**
247 	 * Get value from specific cell
248 	 * @param xSpreadsheet
249 	 * @param column
250 	 * @param row
251 	 * @return
252 	 * @throws Exception
253 	 */
254 	public static double getValueFromCell(XSpreadsheet xSpreadsheet, int column, int row) throws Exception {
255 		XCell xCell = xSpreadsheet.getCellByPosition(column, row);
256 		double cellValue = xCell.getValue();
257 
258 		return cellValue;
259 	}
260 
261 	/**
262 	 * Get text from specific cell
263 	 * @param xSpreadsheet
264 	 * @param column
265 	 * @param row
266 	 *
267 	 * @return
268 	 * @throws Exception
269 	 */
270 	public static String getTextFromCell(XSpreadsheet xSpreadsheet, int column, int row) throws Exception {
271 		XCell xCell = xSpreadsheet.getCellByPosition(column, row);
272 		XText xText = (XText) UnoRuntime.queryInterface(XText.class, xCell);
273 
274 		return xText.getString();
275 	}
276 
277 	/**
278 	 * Get formula string from specific cell
279 	 * @param xSpreadsheet
280 	 * @param column
281 	 * @param row
282 	 * @return
283 	 * @throws Exception
284 	 */
285 	public static String getFormulaFromCell(XSpreadsheet xSpreadsheet, int column, int row) throws Exception {
286 		XCell xCell = xSpreadsheet.getCellByPosition(column, row);
287 		String cellFormula = xCell.getFormula();
288 
289 		return cellFormula;
290 	}
291 
292 	/**
293 	 * Set numbers into a cell range
294 	 * @param xSpreadsheet
295 	 * @param start_col
296 	 * @param start_row
297 	 * @param end_col
298 	 * @param end_row
299 	 * @param values
300 	 * @throws Exception
301 	 */
302 	@Deprecated
303 	public static void setValueToCellRange(XSpreadsheet xSpreadsheet, int start_col, int start_row, int end_col, int end_row,  double[][] values) throws Exception {
304 		XCellRange xCellRange = xSpreadsheet.getCellRangeByPosition(start_col, start_row, end_col, end_row);
305 		XCell xCell = null;
306 		for (int i = 0; i <= (end_row - start_row); i++ ) {
307 			for(int j = 0; j <= (end_col - start_col); j++) {
308 				xCell = xCellRange.getCellByPosition(j, i);
309 				xCell.setValue(values[i][j]);
310 			}
311 		}
312 	}
313 
314 	public static void setValueToCellRange(XSpreadsheet xSpreadsheet, int start_col, int start_row, double[][] values) throws Exception {
315 		XCellRange xCellRange = xSpreadsheet.getCellRangeByPosition(start_col, start_row, start_col + values[0].length - 1, start_row + values.length - 1);
316 		XCell xCell = null;
317 		for (int i = 0; i < values.length; i++ ) {
318 			for(int j = 0; j < values[0].length; j++) {
319 				xCell = xCellRange.getCellByPosition(j, i);
320 				xCell.setValue(values[i][j]);
321 			}
322 		}
323 	}
324 
325 	/**
326 	 * Set text into a cell range
327 	 * @param xSpreadsheet
328 	 * @param start_col
329 	 * @param start_row
330 	 * @param end_col
331 	 * @param end_row
332 	 * @param texts
333 	 * @throws Exception
334 	 */
335 	@Deprecated
336 	public static void setTextToCellRange(XSpreadsheet xSpreadsheet, int start_col, int start_row, int end_col, int end_row,  String[][] texts) throws Exception {
337 		XCellRange xCellRange = xSpreadsheet.getCellRangeByPosition(start_col, start_row, end_col, end_row);
338 		XCell xCell = null;
339 		XText xText = null;
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 				xText = (XText) UnoRuntime.queryInterface(XText.class, xCell);
344 				xText.setString(texts[i][j]);
345 			}
346 		}
347 	}
348 
349 	public static void setTextToCellRange(XSpreadsheet xSpreadsheet, int start_col, int start_row, String[][] texts) throws Exception {
350 		XCellRange xCellRange = xSpreadsheet.getCellRangeByPosition(start_col, start_row, start_col + texts[0].length - 1, start_row + texts.length - 1);
351 		XCell xCell = null;
352 		XText xText = null;
353 		for (int i = 0; i < texts.length; i++ ) {
354 			for(int j = 0; j < texts[0].length; j++) {
355 				xCell = xCellRange.getCellByPosition(j, i);
356 				xText = (XText) UnoRuntime.queryInterface(XText.class, xCell);
357 				xText.setString(texts[i][j]);
358 			}
359 		}
360 	}
361 
362 	/**
363 	 * Get number content from a cell range
364 	 * @param xSpreadsheet
365 	 * @param start_col
366 	 * @param start_row
367 	 * @param end_col
368 	 * @param end_row
369 	 * @return
370 	 * @throws Exception
371 	 */
372 	public static double[][] getValueFromCellRange(XSpreadsheet xSpreadsheet, int start_col, int start_row, int end_col, int end_row) throws Exception {
373 		XCellRange xCellRange = xSpreadsheet.getCellRangeByPosition(start_col, start_row, end_col, end_row);
374 		XCell xCell = null;
375 		double[][] cellValues = new double[end_row - start_row+1][end_col - start_col +1];
376 
377 		for (int i = 0; i <= (end_row - start_row); i++ ) {
378 			for(int j = 0; j <= (end_col - start_col); j++) {
379 				xCell = xCellRange.getCellByPosition(j, i);
380 				cellValues[i][j] = xCell.getValue();
381 			}
382 		}
383 
384 		return cellValues;
385 	}
386 
387 	/**
388 	 * Get text content from a cell range
389 	 * @param xSpreadsheet
390 	 * @param start_col
391 	 * @param start_row
392 	 * @param end_col
393 	 * @param end_row
394 	 * @return
395 	 * @throws Exception
396 	 */
397 	public static String[][] getTextFromCellRange(XSpreadsheet xSpreadsheet, int start_col, int start_row, int end_col, int end_row) throws Exception {
398 		XCellRange xCellRange = xSpreadsheet.getCellRangeByPosition(start_col, start_row, end_col, end_row);
399 		XCell xCell = null;
400 		XText xText = null;
401 		String[][] cellTexts = new String[end_row - start_row+1][end_col - start_col +1];
402 
403 		for (int i = 0; i <= (end_row - start_row); i++ ) {
404 			for (int j = 0; j <= (end_col - start_col); j++) {
405 				xCell = xCellRange.getCellByPosition(j, i);
406 				xText = (XText) UnoRuntime.queryInterface(XText.class, xCell);
407 				cellTexts[i][j] = xText.getString();
408 			}
409 		}
410 
411 		return cellTexts;
412 	}
413 
414 	//TODO ZS - public static String[][] getAllFromCellRange
415 
416 	/**
417 	 * Switch to specific sheet
418 	 * @param xSpreadsheetDocument
419 	 * @param xSpreadsheet
420 	 */
421 	public static void setCurrentSheet(XSpreadsheetDocument xSpreadsheetDocument, XSpreadsheet xSpreadsheet) throws Exception {
422 		XModel xModel = (XModel) UnoRuntime.queryInterface(XModel.class, xSpreadsheetDocument);
423 		XController xController = xModel.getCurrentController();
424 		XSpreadsheetView xSpreadsheetView = (XSpreadsheetView) UnoRuntime.queryInterface(XSpreadsheetView.class, xController);
425 		xSpreadsheetView.setActiveSheet(xSpreadsheet);
426 	}
427 
428 	/**
429 	 * Get sheet object of current active sheet
430 	 * @param xSpreadsheetDocument
431 	 * @return
432 	 */
433 	public static XSpreadsheet getCurrentSheet(XSpreadsheetDocument xSpreadsheetDocument) throws Exception {
434 		XModel xModel = (XModel) UnoRuntime.queryInterface(XModel.class, xSpreadsheetDocument);
435 		XController xController = xModel.getCurrentController();
436 		XSpreadsheetView xSpreadsheetView = (XSpreadsheetView) UnoRuntime.queryInterface(XSpreadsheetView.class, xController);
437 		XSpreadsheet xSpreadsheet = xSpreadsheetView.getActiveSheet();
438 
439 		return xSpreadsheet;
440 	}
441 
442 	/**
443 	 * Get sheet object by sheet index
444 	 *
445 	 * @param xSpreadsheetDocument
446 	 * @return
447 	 * @throws Exception
448 	 */
449 	public static String getSCActiveSheetName(
450 			XSpreadsheetDocument xSpreadsheetDocument) throws Exception {
451 		XModel xSpreadsheetModel = (XModel) UnoRuntime.queryInterface(
452 				XModel.class, xSpreadsheetDocument);
453 		XSpreadsheetView xSpeadsheetView = (XSpreadsheetView) UnoRuntime
454 				.queryInterface(XSpreadsheetView.class,
455 						xSpreadsheetModel.getCurrentController());
456 		XSpreadsheet activesheet = xSpeadsheetView.getActiveSheet();
457 		XNamed activesheetName = (XNamed) UnoRuntime.queryInterface(
458 				XNamed.class, activesheet);
459 		return activesheetName.getName();
460 	}
461 
462 	/**
463 	 * Set specific property's value for an object
464 	 * @param obj
465 	 * @param propName
466 	 * @param value
467 	 * @throws Exception
468 	 */
469 	public static void setProperties(Object obj, String propName, Object value) throws Exception {
470 		XPropertySet xPropertySet =
471 				(XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, obj);
472 		xPropertySet.setPropertyValue(propName, value);
473 	}
474 
475 	/**
476 	 * Get specific property's value of an object
477 	 * @param obj
478 	 * @param propName
479 	 * @return
480 	 * @throws Exception
481 	 */
482 	public static Object getProperties(Object obj, String propName) throws Exception {
483 		XPropertySet xPropertySet =
484 				(XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, obj);
485 		Object value = xPropertySet.getPropertyValue(propName);
486 
487 		return value;
488 	}
489 
490 	/**
491 	 * Set value of specific property from a cell
492 	 * @param xCell
493 	 * @param propName
494 	 * @param value
495 	 * @throws Exception
496 	 */
497 	public static void setCellProperties(XCell xCell, String propName, Object value) throws Exception {
498 
499 		setProperties(xCell, propName, value);
500 	}
501 
502 	/**
503 	 * Get value of specific property from a cell
504 	 * @param xCell
505 	 * @param propName
506 	 * @return
507 	 * @throws Exception
508 	 */
509 	public static Object getCellProperties(XCell xCell, String propName) throws Exception {
510 			return getProperties(xCell, propName);
511 	}
512 
513 	/**
514 	 * Clear temp file directory
515 	 */
516 	public static void clearTempDir() {
517 		FileUtil.deleteFile(Testspace.getFile(Testspace.getPath(scTempDir)));
518 	}
519 
520 	/**
521 	 * Save file as specific file format into spreadsheet temp file folder.
522 	 * @param scComponent
523 	 * @param fileName  File name string without extension name (e.g. "sampleFile")
524 	 * @param extName ("ods", "ots", "xls", "xlt", "csv")
525 	 * @throws Exception
526 	 */
527 	public static void saveFileAs(XComponent scComponent, String fileName, String extName) throws Exception {
528 
529 		initFilterName();
530 
531 		String storeUrl = Testspace.getUrl(scTempDir + fileName + "." + extName);
532 
533 		PropertyValue[] storeProps = new PropertyValue[2];
534 		storeProps[0] = new PropertyValue();
535 		storeProps[0].Name = "FilterName";
536 		storeProps[0].Value = filterName.get(extName);
537 		storeProps[1] = new PropertyValue();
538 		storeProps[1].Name = "Overwrite";
539 		storeProps[1].Value = new Boolean(true);
540 
541 		XStorable scStorable =
542 				(XStorable) UnoRuntime.queryInterface(XStorable.class, scComponent);
543 		scStorable.storeAsURL(storeUrl, storeProps);
544 	}
545 
546 	/**
547 	 * Save file after open file.
548 	 * @param xSpreadsheetDocument
549 	 * @throws Exception
550 	 */
551 	public static void save(XSpreadsheetDocument xSpreadsheetDocument)
552 			throws Exception {
553 		XStorable scStorable = (XStorable) UnoRuntime.queryInterface(
554 				XStorable.class, xSpreadsheetDocument);
555 		scStorable.store();
556 	}
557 
558 
559 	/**
560 	 * Close specific opening spreadsheet file which has been saved
561 	 * @param xSpreadsheetDocument
562 	 * @throws Exception
563 	 */
564 	public static void closeFile(XSpreadsheetDocument xSpreadsheetDocument) throws Exception {
565 		XCloseable xCloseable = (XCloseable) UnoRuntime.queryInterface(XCloseable.class, xSpreadsheetDocument);
566 		xCloseable.close(false);
567 	}
568 
569 	/**
570 	 * Close a opening file saved in spreadsheet temp file direction and reopen it in Spreadsheet. For save&reload test scenario only.
571 	 * @param unoApp
572 	 * @param xSpreadsheetDocument
573 	 * @param fullFileName   File name with the extension name. (e.g. "sc.ods")
574 	 * @return
575 	 * @throws Exception
576 	 */
577 	public static XSpreadsheetDocument reloadFile(UnoApp unoApp, XSpreadsheetDocument xSpreadsheetDocument, String fullFileName) throws Exception {
578 		closeFile(xSpreadsheetDocument);
579 
580 		String filePath = Testspace.getPath(scTempDir + fullFileName);
581 		XSpreadsheetDocument xScDocument = (XSpreadsheetDocument) UnoRuntime.queryInterface(XSpreadsheetDocument.class, unoApp.loadDocument(filePath));
582 
583 		return xScDocument;
584 	}
585 
586 	/**
587 	 * open file in Spreadsheet.
588 	 * @param app
589 	 * @param filePath   File path with the extension name. (e.g. "testcase/uno/sc/data/sample.xls")
590 	 * @return
591 	 * @throws Exception
592 	 */
593 	public static XSpreadsheetDocument openFile(String filePath, UnoApp app) throws Exception {
594 		return (XSpreadsheetDocument) UnoRuntime.queryInterface(XSpreadsheetDocument.class, app.loadDocument(filePath));
595 	}
596 
597 	/**
598 	 * Initial the filter name list
599 	 * @throws Exception
600 	 */
601 	private static void initFilterName() throws Exception {
602 		if (filterName.size() > 0) {
603 			return;
604 		}
605 
606 		filterName.put("ods", "calc8");
607 		filterName.put("ots", "calc8_template");
608 		filterName.put("xls", "MS Excel 97");
609 		filterName.put("xlt", "MS Excel 97 Vorlage/Template");
610 		filterName.put("csv", "Text - txt - csv (StarCalc)");
611 	}
612 
613 
614 	/***************************************************************
615 	 *      Chart Utility method - using chart interface           *
616 	****************************************************************/
617 
618 	/**
619 	 * Get a CellRangeAddress by cell range reference name
620 	 * @param xSpreadsheet
621 	 * @param rangeName    a cell range reference name(e.g. "A1:B2")
622 	 * @return
623 	 */
624 	public static CellRangeAddress getChartDataRangeByName(XSpreadsheet xSpreadsheet, String rangeName) {
625 		XCellRange cellRange = xSpreadsheet.getCellRangeByName(rangeName);
626 		XCellRangeAddressable xCellRangeAddressable =
627 			(XCellRangeAddressable) UnoRuntime.queryInterface(XCellRangeAddressable.class, cellRange);
628 
629 		CellRangeAddress cellRangeAddress = xCellRangeAddressable.getRangeAddress();
630 		return cellRangeAddress;
631 	}
632 
633 	/**
634 	 * Create a spreadsheet chart with data in a specific cell range.
635 	 * @param xSpreadsheet
636 	 * @param rec   a rectangle shape object
637 	 * @param dataRangeAddress   the CellRangeAddress array of chart data source
638 	 * @param chartName
639 	 * @return
640 	 * @throws Exception
641 	 */
642 	public static XChartDocument createChart(XSpreadsheet xSpreadsheet, Rectangle rec, CellRangeAddress[] dataRangeAddress, String chartName) throws Exception {
643 		XChartDocument xChartDocument = null;
644 		XTableChartsSupplier xTChartSupplier =
645 				(XTableChartsSupplier) UnoRuntime.queryInterface(XTableChartsSupplier.class, xSpreadsheet);
646 		XTableCharts xTableCharts = xTChartSupplier.getCharts();
647 		XNameAccess xNameAccess =
648 				(XNameAccess) UnoRuntime.queryInterface(XNameAccess.class, xTableCharts);
649 		if (xNameAccess != null && !xNameAccess.hasByName(chartName)) {
650 
651 			xTableCharts.addNewByName(chartName, rec, dataRangeAddress, true, false);
652 			XTableChart xTableChart = (XTableChart) UnoRuntime.queryInterface(
653 					XTableChart.class, xNameAccess.getByName(chartName));
654 			XEmbeddedObjectSupplier xEmbeddedObjectSupplier = (XEmbeddedObjectSupplier) UnoRuntime.queryInterface(
655 					XEmbeddedObjectSupplier.class, xTableChart);
656 			xChartDocument = (XChartDocument) UnoRuntime.queryInterface(
657 					XChartDocument.class, xEmbeddedObjectSupplier.getEmbeddedObject());
658 		}
659 
660 		return xChartDocument;
661 	}
662 
663 	/**
664 	 * Get XChartDocument object via the chart name.
665 	 * @param xSpreadsheet
666 	 * @param chartName
667 	 * @return
668 	 * @throws Exception
669 	 */
670 	public static XChartDocument getChartByName(XSpreadsheet xSpreadsheet, String chartName) throws Exception {
671 		XChartDocument xChartDocument = null;
672 		XTableChartsSupplier xTChartSupplier =
673 				(XTableChartsSupplier) UnoRuntime.queryInterface(XTableChartsSupplier.class, xSpreadsheet);
674 		XTableCharts xTableCharts = xTChartSupplier.getCharts();
675 		XNameAccess xNameAccess =
676 				(XNameAccess) UnoRuntime.queryInterface(XNameAccess.class, xTableCharts);
677 
678 		if (xNameAccess != null && xNameAccess.hasByName(chartName)) {
679 			XTableChart xTableChart = (XTableChart) UnoRuntime.queryInterface(
680 					XTableChart.class, xNameAccess.getByName(chartName));
681 			XEmbeddedObjectSupplier xEmbeddedObjectSupplier = (XEmbeddedObjectSupplier) UnoRuntime.queryInterface(
682 					XEmbeddedObjectSupplier.class, xTableChart);
683 			xChartDocument = (XChartDocument) UnoRuntime.queryInterface(
684 					XChartDocument.class, xEmbeddedObjectSupplier.getEmbeddedObject());
685 		}
686 
687 		return xChartDocument;
688 	}
689 
690 	/**
691 	 * Set specific basic type to chart
692 	 * @param xChartDocument
693 	 * @param chartType
694 	 * @throws Exception
695 	 */
696 	public static void setChartType(XChartDocument xChartDocument, String chartType) throws Exception {
697 		XMultiServiceFactory xMultiServiceFactory = (XMultiServiceFactory) UnoRuntime.queryInterface(
698 			XMultiServiceFactory.class, xChartDocument);
699 		XDiagram xDiagram = (XDiagram) UnoRuntime.queryInterface(
700 			XDiagram.class, xMultiServiceFactory.createInstance(chartType));
701 		xChartDocument.setDiagram(xDiagram);
702 	}
703 
704 	/**
705 	 * Get the type string of a chart
706 	 * @param xChartDocument
707 	 * @return
708 	 * @throws Exception
709 	 */
710 	public static String getChartType(XChartDocument xChartDocument) throws Exception {
711 		return xChartDocument.getDiagram().getDiagramType();
712 	}
713 
714 	/**
715 	 * Get the names of charts in specific sheet
716 	 * @param xSpreadsheet
717 	 * @return
718 	 * @throws Exception
719 	 */
720 	public static String[] getChartNameList(XSpreadsheet xSpreadsheet) throws Exception {
721 		XChartDocument xChartDocument = null;
722 		XTableChartsSupplier xTChartSupplier =
723 				(XTableChartsSupplier) UnoRuntime.queryInterface(XTableChartsSupplier.class, xSpreadsheet);
724 		XTableCharts xTableCharts = xTChartSupplier.getCharts();
725 		String[] chartNames = xTableCharts.getElementNames();
726 		return chartNames;
727 	}
728 
729 
730 
731 }
732