?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
Update ooo320-m1
[ooovba.git] / odk / examples / DevelopersGuide / Spreadsheet / SpreadsheetSample.java
blobd311732d68e5ef65a7e320d21829f8ddb2b670c6
1 /*************************************************************************
2 *
3 * $RCSfile: SpreadsheetSample.java,v $
4 *
5 * $Revision: 1.5 $
6 *
7 * last change: $Author: rt $ $Date: 2005-01-31 16:55:25 $
8 *
9 * The Contents of this file are made available subject to the terms of
10 * the BSD license.
11 *
12 * Copyright (c) 2003 by Sun Microsystems, Inc.
13 * All rights reserved.
14 *
15 * Redistribution and use in source and binary forms, with or without
16 * modification, are permitted provided that the following conditions
17 * are met:
18 * 1. Redistributions of source code must retain the above copyright
19 * notice, this list of conditions and the following disclaimer.
20 * 2. Redistributions in binary form must reproduce the above copyright
21 * notice, this list of conditions and the following disclaimer in the
22 * documentation and/or other materials provided with the distribution.
23 * 3. Neither the name of Sun Microsystems, Inc. nor the names of its
24 * contributors may be used to endorse or promote products derived
25 * from this software without specific prior written permission.
26 *
27 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
28 * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
29 * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS
30 * FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE
31 * COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,
32 * INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
33 * BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS
34 * OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
35 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR
36 * TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE
37 * USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
38 *
39 *************************************************************************/
40
41 import com.sun.star.uno.UnoRuntime;
42 import com.sun.star.uno.RuntimeException;
43 import com.sun.star.uno.AnyConverter;
44
45 // __________ implementation ____________________________________
46
47 /** Create and modify a spreadsheet document.
48 */
49 public class SpreadsheetSample extends SpreadsheetDocHelper
50 {
51
52 // ________________________________________________________________
53
54 public static void main( String args[] )
55 {
56 try
57 {
58 SpreadsheetSample aSample = new SpreadsheetSample( args );
59 aSample.doSampleFunction();
60 }
61 catch (Exception ex)
62 {
63 System.out.println( "Error: Sample caught exception!\nException Message = "
64 + ex.getMessage());
65 ex.printStackTrace();
66 System.exit( 1 );
67 }
68 System.out.println( "\nSamples done." );
69 System.exit( 0 );
70 }
71
72 // ________________________________________________________________
73
74 public SpreadsheetSample( String[] args )
75 {
76 super( args );
77 }
78
79 // ________________________________________________________________
80
81 /** This sample function performs all changes on the document. */
82 public void doSampleFunction()
83 {
84 try
85 {
86 doCellSamples();
87 }
88 catch (Exception ex)
89 {
90 System.out.println( "\nError: Cell sample caught exception!\nException Message = "
91 + ex.getMessage());
92 ex.printStackTrace();
93 }
94
95 try
96 {
97 doCellRangeSamples();
98 }
99 catch (Exception ex)
100 {
101 System.out.println( "\nError: Cell range sample caught exception!\nException Message = "
102 + ex.getMessage());
103 ex.printStackTrace();
104 }
105
106 try
107 {
108 doCellRangesSamples();
109 }
110 catch (Exception ex)
111 {
112 System.out.println( "\nError: Cell range container sample caught exception!\nException Message = "
113 + ex.getMessage());
114 ex.printStackTrace();
115 }
116
117 try
118 {
119 doCellCursorSamples();
120 }
121 catch (Exception ex)
122 {
123 System.out.println( "\nError: Cell cursor sample caught exception!\nException Message = "
124 + ex.getMessage());
125 ex.printStackTrace();
126 }
127
128 try
129 {
130 doFormattingSamples();
131 }
132 catch (Exception ex)
133 {
134 System.out.println( "\nError: Formatting sample caught exception!\nException Message = "
135 + ex.getMessage());
136 ex.printStackTrace();
137 }
138
139 try
140 {
141 doDocumentSamples();
142 }
143 catch (Exception ex)
144 {
145 System.out.println( "\nError: Document sample caught exception!\nException Message = "
146 + ex.getMessage());
147 ex.printStackTrace();
148 }
149
150 try
151 {
152 doDatabaseSamples();
153 }
154 catch( Exception ex )
155 {
156 System.out.println( "\nError: Database sample caught exception!\nException Message = "
157 + ex.getMessage());
158 ex.printStackTrace();
159 }
160
161 try
162 {
163 doDataPilotSamples();
164 }
165 catch (Exception ex)
166 {
167 System.out.println( "\nError: Dota pilot sample caught exception!\nException Message = "
168 + ex.getMessage());
169 ex.printStackTrace();
170 }
171
172 try
173 {
174 doNamedRangesSamples();
175 }
176 catch( Exception ex )
177 {
178 System.out.println( "\nError: Named ranges sample caught exception!\nException Message = "
179 + ex.getMessage());
180 ex.printStackTrace();
181 }
182
183 try
184 {
185 doFunctionAccessSamples();
186 }
187 catch (Exception ex)
188 {
189 System.out.println( "\nError: Function access sample caught exception!\nException Message = "
190 + ex.getMessage());
191 ex.printStackTrace();
192 }
193
194 try
195 {
196 doApplicationSettingsSamples();
197 }
198 catch (Exception ex)
199 {
200 System.out.println( "\nError: Application settings sample caught exception!\nException Message = "
201 + ex.getMessage());
202 ex.printStackTrace();
203 }
204 }
205
206 // ________________________________________________________________
207
208 /** All samples regarding the service com.sun.star.sheet.SheetCell. */
209 private void doCellSamples() throws RuntimeException, Exception
210 {
211 System.out.println( "\n*** Samples for service sheet.SheetCell ***\n" );
212 com.sun.star.sheet.XSpreadsheet xSheet = getSpreadsheet( 0 );
213 com.sun.star.table.XCell xCell = null;
214 com.sun.star.beans.XPropertySet xPropSet = null;
215 String aText;
216 prepareRange( xSheet, "A1:C7", "Cells and Cell Ranges" );
217
218 // --- Get cell B3 by position - (column, row) ---
219 xCell = xSheet.getCellByPosition( 1, 2 );
220
221
222 // --- Insert two text paragraphs into the cell. ---
223 com.sun.star.text.XText xText = (com.sun.star.text.XText)
224 UnoRuntime.queryInterface( com.sun.star.text.XText.class, xCell );
225 com.sun.star.text.XTextCursor xTextCursor = xText.createTextCursor();
226
227 xText.insertString( xTextCursor, "Text in first line.", false );
228 xText.insertControlCharacter( xTextCursor,
229 com.sun.star.text.ControlCharacter.PARAGRAPH_BREAK, false );
230 xText.insertString( xTextCursor, "And a ", false );
231
232 // create a hyperlink
233 com.sun.star.lang.XMultiServiceFactory xServiceMan = (com.sun.star.lang.XMultiServiceFactory)
234 UnoRuntime.queryInterface( com.sun.star.lang.XMultiServiceFactory.class, getDocument() );
235 Object aHyperlinkObj = xServiceMan.createInstance( "com.sun.star.text.TextField.URL" );
236 xPropSet = (com.sun.star.beans.XPropertySet)
237 UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, aHyperlinkObj );
238 xPropSet.setPropertyValue( "URL", "http://www.example.org" );
239 xPropSet.setPropertyValue( "Representation", "hyperlink" );
240 // ... and insert
241 com.sun.star.text.XTextContent xContent = (com.sun.star.text.XTextContent)
242 UnoRuntime.queryInterface( com.sun.star.text.XTextContent.class, aHyperlinkObj );
243 xText.insertTextContent( xTextCursor, xContent, false );
244
245
246 // --- Query the separate paragraphs. ---
247 com.sun.star.container.XEnumerationAccess xParaEA =
248 (com.sun.star.container.XEnumerationAccess) UnoRuntime.queryInterface(
249 com.sun.star.container.XEnumerationAccess.class, xCell );
250 com.sun.star.container.XEnumeration xParaEnum = xParaEA.createEnumeration();
251 // Go through the paragraphs
252 while( xParaEnum.hasMoreElements() )
253 {
254 Object aPortionObj = xParaEnum.nextElement();
255 com.sun.star.container.XEnumerationAccess xPortionEA =
256 (com.sun.star.container.XEnumerationAccess) UnoRuntime.queryInterface(
257 com.sun.star.container.XEnumerationAccess.class, aPortionObj );
258 com.sun.star.container.XEnumeration xPortionEnum = xPortionEA.createEnumeration();
259 aText = "";
260 // Go through all text portions of a paragraph and construct string.
261 Object nextElement;
262 while( xPortionEnum.hasMoreElements() )
263 {
264 com.sun.star.text.XTextRange xRange = (com.sun.star.text.XTextRange)
265 UnoRuntime.queryInterface(com.sun.star.text.XTextRange.class,
266 xPortionEnum.nextElement());
267 aText += xRange.getString();
268 }
269 System.out.println( "Paragraph text: " + aText );
270 }
271
272
273 // --- Change cell properties. ---
274 xPropSet = (com.sun.star.beans.XPropertySet)
275 UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xCell );
276 // from styles.CharacterProperties
277 xPropSet.setPropertyValue( "CharColor", new Integer( 0x003399 ) );
278 xPropSet.setPropertyValue( "CharHeight", new Float( 20.0 ) );
279 // from styles.ParagraphProperties
280 xPropSet.setPropertyValue( "ParaLeftMargin", new Integer( 500 ) );
281 // from table.CellProperties
282 xPropSet.setPropertyValue( "IsCellBackgroundTransparent", new Boolean( false ) );
283 xPropSet.setPropertyValue( "CellBackColor", new Integer( 0x99CCFF ) );
284
285
286 // --- Get cell address. ---
287 com.sun.star.sheet.XCellAddressable xCellAddr = (com.sun.star.sheet.XCellAddressable)
288 UnoRuntime.queryInterface( com.sun.star.sheet.XCellAddressable.class, xCell );
289 com.sun.star.table.CellAddress aAddress = xCellAddr.getCellAddress();
290 aText = "Address of this cell: Column=" + aAddress.Column;
291 aText += "; Row=" + aAddress.Row;
292 aText += "; Sheet=" + aAddress.Sheet;
293 System.out.println( aText );
294
295
296 // --- Insert an annotation ---
297 com.sun.star.sheet.XSheetAnnotationsSupplier xAnnotationsSupp =
298 (com.sun.star.sheet.XSheetAnnotationsSupplier) UnoRuntime.queryInterface(
299 com.sun.star.sheet.XSheetAnnotationsSupplier.class, xSheet );
300 com.sun.star.sheet.XSheetAnnotations xAnnotations = xAnnotationsSupp.getAnnotations();
301 xAnnotations.insertNew( aAddress, "This is an annotation" );
302
303 com.sun.star.sheet.XSheetAnnotationAnchor xAnnotAnchor = (com.sun.star.sheet.XSheetAnnotationAnchor)
304 UnoRuntime.queryInterface( com.sun.star.sheet.XSheetAnnotationAnchor.class, xCell );
305 com.sun.star.sheet.XSheetAnnotation xAnnotation = xAnnotAnchor.getAnnotation();
306 xAnnotation.setIsVisible( true );
307 }
308
309 // ________________________________________________________________
310
311 /** All samples regarding the service com.sun.star.sheet.SheetCellRange. */
312 private void doCellRangeSamples() throws RuntimeException, Exception
313 {
314 System.out.println( "\n*** Samples for service sheet.SheetCellRange ***\n" );
315 com.sun.star.sheet.XSpreadsheet xSheet = getSpreadsheet( 0 );
316 com.sun.star.table.XCellRange xCellRange = null;
317 com.sun.star.beans.XPropertySet xPropSet = null;
318 com.sun.star.table.CellRangeAddress aRangeAddress = null;
319 String aText;
320
321 // Preparation
322 setFormula( xSheet, "B5", "First cell" );
323 setFormula( xSheet, "B6", "Second cell" );
324 // Get cell range B5:B6 by position - (column, row, column, row)
325 xCellRange = xSheet.getCellRangeByPosition( 1, 4, 1, 5 );
326
327
328 // --- Change cell range properties. ---
329 xPropSet = (com.sun.star.beans.XPropertySet)
330 UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xCellRange );
331 // from com.sun.star.styles.CharacterProperties
332 xPropSet.setPropertyValue( "CharColor", new Integer( 0x003399 ) );
333 xPropSet.setPropertyValue( "CharHeight", new Float( 20.0 ) );
334 // from com.sun.star.styles.ParagraphProperties
335 xPropSet.setPropertyValue( "ParaLeftMargin", new Integer( 500 ) );
336 // from com.sun.star.table.CellProperties
337 xPropSet.setPropertyValue( "IsCellBackgroundTransparent", new Boolean( false ) );
338 xPropSet.setPropertyValue( "CellBackColor", new Integer( 0x99CCFF ) );
339
340
341 // --- Replace text in all cells. ---
342 com.sun.star.util.XReplaceable xReplace = (com.sun.star.util.XReplaceable)
343 UnoRuntime.queryInterface( com.sun.star.util.XReplaceable.class, xCellRange );
344 com.sun.star.util.XReplaceDescriptor xReplaceDesc = xReplace.createReplaceDescriptor();
345 xReplaceDesc.setSearchString( "cell" );
346 xReplaceDesc.setReplaceString( "text" );
347 // property SearchWords searches for whole cells!
348 xReplaceDesc.setPropertyValue( "SearchWords", new Boolean( false ) );
349 int nCount = xReplace.replaceAll( xReplaceDesc );
350 System.out.println( "Search text replaced " + nCount + " times." );
351
352
353 // --- Merge cells. ---
354 xCellRange = xSheet.getCellRangeByName( "F3:G6" );
355 prepareRange( xSheet, "E1:H7", "XMergeable" );
356 com.sun.star.util.XMergeable xMerge = (com.sun.star.util.XMergeable)
357 UnoRuntime.queryInterface( com.sun.star.util.XMergeable.class, xCellRange );
358 xMerge.merge( true );
359
360
361 // --- Change indentation. ---
362 /* does not work (bug in XIndent implementation)
363 prepareRange( xSheet, "I20:I23", "XIndent" );
364 setValue( xSheet, "I21", 1 );
365 setValue( xSheet, "I22", 1 );
366 setValue( xSheet, "I23", 1 );
367
368 xCellRange = xSheet.getCellRangeByName( "I21:I22" );
369 com.sun.star.util.XIndent xIndent = (com.sun.star.util.XIndent)
370 UnoRuntime.queryInterface( com.sun.star.util.XIndent.class, xCellRange );
371 xIndent.incrementIndent();
372
373 xCellRange = xSheet.getCellRangeByName( "I22:I23" );
374 xIndent = (com.sun.star.util.XIndent)
375 UnoRuntime.queryInterface( com.sun.star.util.XIndent.class, xCellRange );
376 xIndent.incrementIndent();
377 */
378
379
380 // --- Column properties. ---
381 xCellRange = xSheet.getCellRangeByName( "B1" );
382 com.sun.star.table.XColumnRowRange xColRowRange = (com.sun.star.table.XColumnRowRange)
383 UnoRuntime.queryInterface( com.sun.star.table.XColumnRowRange.class, xCellRange );
384 com.sun.star.table.XTableColumns xColumns = xColRowRange.getColumns();
385
386 Object aColumnObj = xColumns.getByIndex( 0 );
387 xPropSet = (com.sun.star.beans.XPropertySet) UnoRuntime.queryInterface(
388 com.sun.star.beans.XPropertySet.class, aColumnObj );
389 xPropSet.setPropertyValue( "Width", new Integer( 6000 ) );
390
391 com.sun.star.container.XNamed xNamed = (com.sun.star.container.XNamed)
392 UnoRuntime.queryInterface( com.sun.star.container.XNamed.class, aColumnObj );
393 System.out.println( "The name of the wide column is " + xNamed.getName() + "." );
394
395
396 // --- Cell range data ---
397 prepareRange( xSheet, "A9:C30", "XCellRangeData" );
398
399 xCellRange = xSheet.getCellRangeByName( "A10:C30" );
400 com.sun.star.sheet.XCellRangeData xData = (com.sun.star.sheet.XCellRangeData)
401 UnoRuntime.queryInterface( com.sun.star.sheet.XCellRangeData.class, xCellRange );
402 Object[][] aValues =
403 {
404 { "Name", "Fruit", "Quantity" },
405 { "Alice", "Apples", new Double( 3.0 ) },
406 { "Alice", "Oranges", new Double( 7.0 ) },
407 { "Bob", "Apples", new Double( 3.0 ) },
408 { "Alice", "Apples", new Double( 9.0 ) },
409 { "Bob", "Apples", new Double( 5.0 ) },
410 { "Bob", "Oranges", new Double( 6.0 ) },
411 { "Alice", "Oranges", new Double( 3.0 ) },
412 { "Alice", "Apples", new Double( 8.0 ) },
413 { "Alice", "Oranges", new Double( 1.0 ) },
414 { "Bob", "Oranges", new Double( 2.0 ) },
415 { "Bob", "Oranges", new Double( 7.0 ) },
416 { "Bob", "Apples", new Double( 1.0 ) },
417 { "Alice", "Apples", new Double( 8.0 ) },
418 { "Alice", "Oranges", new Double( 8.0 ) },
419 { "Alice", "Apples", new Double( 7.0 ) },
420 { "Bob", "Apples", new Double( 1.0 ) },
421 { "Bob", "Oranges", new Double( 9.0 ) },
422 { "Bob", "Oranges", new Double( 3.0 ) },
423 { "Alice", "Oranges", new Double( 4.0 ) },
424 { "Alice", "Apples", new Double( 9.0 ) }
425 };
426 xData.setDataArray( aValues );
427
428
429 // --- Get cell range address. ---
430 com.sun.star.sheet.XCellRangeAddressable xRangeAddr = (com.sun.star.sheet.XCellRangeAddressable)
431 UnoRuntime.queryInterface( com.sun.star.sheet.XCellRangeAddressable.class, xCellRange );
432 aRangeAddress = xRangeAddr.getRangeAddress();
433 System.out.println( "Address of this range: Sheet=" + aRangeAddress.Sheet );
434 System.out.println( "Start column=" + aRangeAddress.StartColumn + "; Start row=" + aRangeAddress.StartRow );
435 System.out.println( "End column =" + aRangeAddress.EndColumn + "; End row =" + aRangeAddress.EndRow );
436
437
438 // --- Sheet operation. ---
439 // uses the range filled with XCellRangeData
440 com.sun.star.sheet.XSheetOperation xSheetOp = (com.sun.star.sheet.XSheetOperation)
441 UnoRuntime.queryInterface( com.sun.star.sheet.XSheetOperation.class, xData );
442 double fResult = xSheetOp.computeFunction( com.sun.star.sheet.GeneralFunction.AVERAGE );
443 System.out.println( "Average value of the data table A10:C30: " + fResult );
444
445
446 // --- Fill series ---
447 // Prepare the example
448 setValue( xSheet, "E10", 1 );
449 setValue( xSheet, "E11", 4 );
450 setDate( xSheet, "E12", 30, 1, 2002 );
451 setFormula( xSheet, "I13", "Text 10" );
452 setFormula( xSheet, "E14", "Jan" );
453 setValue( xSheet, "K14", 10 );
454 setValue( xSheet, "E16", 1 );
455 setValue( xSheet, "F16", 2 );
456 setDate( xSheet, "E17", 28, 2, 2002 );
457 setDate( xSheet, "F17", 28, 1, 2002 );
458 setValue( xSheet, "E18", 6 );
459 setValue( xSheet, "F18", 4 );
460
461 com.sun.star.sheet.XCellSeries xSeries = null;
462 // Fill 2 rows linear with end value -> 2nd series is not filled completely
463 xSeries = getCellSeries( xSheet, "E10:I11" );
464 xSeries.fillSeries( com.sun.star.sheet.FillDirection.TO_RIGHT, com.sun.star.sheet.FillMode.LINEAR,
465 com.sun.star.sheet.FillDateMode.FILL_DATE_DAY, 2, 9 );
466 // Add months to a date
467 xSeries = getCellSeries( xSheet, "E12:I12" );
468 xSeries.fillSeries( com.sun.star.sheet.FillDirection.TO_RIGHT, com.sun.star.sheet.FillMode.DATE,
469 com.sun.star.sheet.FillDateMode.FILL_DATE_MONTH, 1, 0x7FFFFFFF );
470 // Fill right to left with a text containing a value
471 xSeries = getCellSeries( xSheet, "E13:I13" );
472 xSeries.fillSeries( com.sun.star.sheet.FillDirection.TO_LEFT, com.sun.star.sheet.FillMode.LINEAR,
473 com.sun.star.sheet.FillDateMode.FILL_DATE_DAY, 10, 0x7FFFFFFF );
474 // Fill with an user defined list
475 xSeries = getCellSeries( xSheet, "E14:I14" );
476 xSeries.fillSeries( com.sun.star.sheet.FillDirection.TO_RIGHT, com.sun.star.sheet.FillMode.AUTO,
477 com.sun.star.sheet.FillDateMode.FILL_DATE_DAY, 1, 0x7FFFFFFF );
478 // Fill bottom to top with a geometric series
479 xSeries = getCellSeries( xSheet, "K10:K14" );
480 xSeries.fillSeries( com.sun.star.sheet.FillDirection.TO_TOP, com.sun.star.sheet.FillMode.GROWTH,
481 com.sun.star.sheet.FillDateMode.FILL_DATE_DAY, 2, 0x7FFFFFFF );
482 // Auto fill
483 xSeries = getCellSeries( xSheet, "E16:K18" );
484 xSeries.fillAuto( com.sun.star.sheet.FillDirection.TO_RIGHT, 2 );
485 // Fill series copies cell formats -> draw border here
486 prepareRange( xSheet, "E9:K18", "XCellSeries" );
487
488
489 // --- Array formulas ---
490 xCellRange = xSheet.getCellRangeByName( "E21:G23" );
491 prepareRange( xSheet, "E20:G23", "XArrayFormulaRange" );
492 com.sun.star.sheet.XArrayFormulaRange xArrayFormula = (com.sun.star.sheet.XArrayFormulaRange)
493 UnoRuntime.queryInterface( com.sun.star.sheet.XArrayFormulaRange.class, xCellRange );
494 // Insert a 3x3 unit matrix.
495 xArrayFormula.setArrayFormula( "=A10:C12" );
496 System.out.println( "Array formula is: " + xArrayFormula.getArrayFormula() );
497
498
499 // --- Multiple operations ---
500 setFormula( xSheet, "E26", "=E27^F26" );
501 setValue( xSheet, "E27", 1 );
502 setValue( xSheet, "F26", 1 );
503 getCellSeries( xSheet, "E27:E31" ).fillAuto( com.sun.star.sheet.FillDirection.TO_BOTTOM, 1 );
504 getCellSeries( xSheet, "F26:J26" ).fillAuto( com.sun.star.sheet.FillDirection.TO_RIGHT, 1 );
505 setFormula( xSheet, "F33", "=SIN(E33)" );
506 setFormula( xSheet, "G33", "=COS(E33)" );
507 setFormula( xSheet, "H33", "=TAN(E33)" );
508 setValue( xSheet, "E34", 0 );
509 setValue( xSheet, "E35", 0.2 );
510 getCellSeries( xSheet, "E34:E38" ).fillAuto( com.sun.star.sheet.FillDirection.TO_BOTTOM, 2 );
511 prepareRange( xSheet, "E25:J38", "XMultipleOperation" );
512
513 com.sun.star.table.CellRangeAddress aFormulaRange = createCellRangeAddress( xSheet, "E26" );
514 com.sun.star.table.CellAddress aColCell = createCellAddress( xSheet, "E27" );
515 com.sun.star.table.CellAddress aRowCell = createCellAddress( xSheet, "F26" );
516
517 xCellRange = xSheet.getCellRangeByName( "E26:J31" );
518 com.sun.star.sheet.XMultipleOperation xMultOp = (com.sun.star.sheet.XMultipleOperation)
519 UnoRuntime.queryInterface( com.sun.star.sheet.XMultipleOperation.class, xCellRange );
520 xMultOp.setTableOperation(
521 aFormulaRange, com.sun.star.sheet.TableOperationMode.BOTH, aColCell, aRowCell );
522
523 aFormulaRange = createCellRangeAddress( xSheet, "F33:H33" );
524 aColCell = createCellAddress( xSheet, "E33" );
525 // Row cell not needed
526
527 xCellRange = xSheet.getCellRangeByName( "E34:H38" );
528 xMultOp = (com.sun.star.sheet.XMultipleOperation)
529 UnoRuntime.queryInterface( com.sun.star.sheet.XMultipleOperation.class, xCellRange );
530 xMultOp.setTableOperation(
531 aFormulaRange, com.sun.star.sheet.TableOperationMode.COLUMN, aColCell, aRowCell );
532
533
534 // --- Cell Ranges Query ---
535 xCellRange = xSheet.getCellRangeByName( "A10:C30" );
536 com.sun.star.sheet.XCellRangesQuery xRangesQuery = (com.sun.star.sheet.XCellRangesQuery)
537 UnoRuntime.queryInterface( com.sun.star.sheet.XCellRangesQuery.class, xCellRange );
538 com.sun.star.sheet.XSheetCellRanges xCellRanges =
539 xRangesQuery.queryContentCells( (short)com.sun.star.sheet.CellFlags.STRING );
540 System.out.println(
541 "Cells in A10:C30 containing text: "
542 + xCellRanges.getRangeAddressesAsString() );
543 }
544
545 /** Returns the XCellSeries interface of a cell range.
546 @param xSheet The spreadsheet containing the cell range.
547 @param aRange The address of the cell range.
548 @return The XCellSeries interface. */
549 private com.sun.star.sheet.XCellSeries getCellSeries(
550 com.sun.star.sheet.XSpreadsheet xSheet, String aRange )
551 {
552 return (com.sun.star.sheet.XCellSeries) UnoRuntime.queryInterface(
553 com.sun.star.sheet.XCellSeries.class, xSheet.getCellRangeByName( aRange ) );
554 }
555
556 // ________________________________________________________________
557
558 /** All samples regarding cell range collections. */
559 private void doCellRangesSamples() throws RuntimeException, Exception
560 {
561 System.out.println( "\n*** Samples for cell range collections ***\n" );
562
563 // Create a new cell range container
564 com.sun.star.lang.XMultiServiceFactory xDocFactory =
565 (com.sun.star.lang.XMultiServiceFactory) UnoRuntime.queryInterface(
566 com.sun.star.lang.XMultiServiceFactory.class, getDocument() );
567 com.sun.star.sheet.XSheetCellRangeContainer xRangeCont =
568 (com.sun.star.sheet.XSheetCellRangeContainer) UnoRuntime.queryInterface(
569 com.sun.star.sheet.XSheetCellRangeContainer.class,
570 xDocFactory.createInstance( "com.sun.star.sheet.SheetCellRanges" ) );
571
572
573 // --- Insert ranges ---
574 insertRange( xRangeCont, 0, 0, 0, 0, 0, false ); // A1:A1
575 insertRange( xRangeCont, 0, 0, 1, 0, 2, true ); // A2:A3
576 insertRange( xRangeCont, 0, 1, 0, 1, 2, false ); // B1:B3
577
578
579 // --- Query the list of filled cells ---
580 System.out.print( "All filled cells: " );
581 com.sun.star.container.XEnumerationAccess xCellsEA = xRangeCont.getCells();
582 com.sun.star.container.XEnumeration xEnum = xCellsEA.createEnumeration();
583 while( xEnum.hasMoreElements() )
584 {
585 Object aCellObj = xEnum.nextElement();
586 com.sun.star.sheet.XCellAddressable xAddr = (com.sun.star.sheet.XCellAddressable)
587 UnoRuntime.queryInterface( com.sun.star.sheet.XCellAddressable.class, aCellObj );
588 com.sun.star.table.CellAddress aAddr = xAddr.getCellAddress();
589 System.out.print( getCellAddressString( aAddr.Column, aAddr.Row ) + " " );
590 }
591 System.out.println();
592 }
593
594 /** Inserts a cell range address into a cell range container and prints
595 a message.
596 @param xContainer The com.sun.star.sheet.XSheetCellRangeContainer interface of the container.
597 @param nSheet Index of sheet of the range.
598 @param nStartCol Index of first column of the range.
599 @param nStartRow Index of first row of the range.
600 @param nEndCol Index of last column of the range.
601 @param nEndRow Index of last row of the range.
602 @param bMerge Determines whether the new range should be merged with the existing ranges. */
603 private void insertRange(
604 com.sun.star.sheet.XSheetCellRangeContainer xContainer,
605 int nSheet, int nStartCol, int nStartRow, int nEndCol, int nEndRow,
606 boolean bMerge ) throws RuntimeException, Exception
607 {
608 com.sun.star.table.CellRangeAddress aAddress = new com.sun.star.table.CellRangeAddress();
609 aAddress.Sheet = (short)nSheet;
610 aAddress.StartColumn = nStartCol;
611 aAddress.StartRow = nStartRow;
612 aAddress.EndColumn = nEndCol;
613 aAddress.EndRow = nEndRow;
614 xContainer.addRangeAddress( aAddress, bMerge );
615 System.out.println(
616 "Inserting " + getCellRangeAddressString( aAddress )
617 + " " + (bMerge ? " with" : "without") + " merge,"
618 + " resulting list: " + xContainer.getRangeAddressesAsString() );
619 }
620
621 // ________________________________________________________________
622
623 /** All samples regarding cell cursors. */
624 private void doCellCursorSamples() throws RuntimeException, Exception
625 {
626 System.out.println( "\n*** Samples for cell cursor ***\n" );
627 com.sun.star.sheet.XSpreadsheet xSheet = getSpreadsheet( 0 );
628
629
630 // --- Find the array formula using a cell cursor ---
631 com.sun.star.table.XCellRange xRange = xSheet.getCellRangeByName( "F22" );
632 com.sun.star.sheet.XSheetCellRange xCellRange = (com.sun.star.sheet.XSheetCellRange)
633 UnoRuntime.queryInterface( com.sun.star.sheet.XSheetCellRange.class, xRange );
634 com.sun.star.sheet.XSheetCellCursor xCursor = xSheet.createCursorByRange( xCellRange );
635
636 xCursor.collapseToCurrentArray();
637 com.sun.star.sheet.XArrayFormulaRange xArray = (com.sun.star.sheet.XArrayFormulaRange)
638 UnoRuntime.queryInterface( com.sun.star.sheet.XArrayFormulaRange.class, xCursor );
639 System.out.println(
640 "Array formula in " + getCellRangeAddressString( xCursor, false )
641 + " contains formula " + xArray.getArrayFormula() );
642
643
644 // --- Find the used area ---
645 com.sun.star.sheet.XUsedAreaCursor xUsedCursor = (com.sun.star.sheet.XUsedAreaCursor)
646 UnoRuntime.queryInterface( com.sun.star.sheet.XUsedAreaCursor.class, xCursor );
647 xUsedCursor.gotoStartOfUsedArea( false );
648 xUsedCursor.gotoEndOfUsedArea( true );
649 // xUsedCursor and xCursor are interfaces of the same object -
650 // so modifying xUsedCursor takes effect on xCursor:
651 System.out.println( "The used area is: " + getCellRangeAddressString( xCursor, true ) );
652 }
653
654 // ________________________________________________________________
655
656 /** All samples regarding the formatting of cells and ranges. */
657 private void doFormattingSamples() throws RuntimeException, Exception
658 {
659 System.out.println( "\n*** Formatting samples ***\n" );
660 com.sun.star.sheet.XSpreadsheet xSheet = getSpreadsheet( 1 );
661 com.sun.star.table.XCellRange xCellRange;
662 com.sun.star.beans.XPropertySet xPropSet = null;
663 com.sun.star.container.XIndexAccess xRangeIA = null;
664 com.sun.star.lang.XMultiServiceFactory xDocServiceManager;
665
666
667 // --- Cell styles ---
668 // get the cell style container
669 com.sun.star.style.XStyleFamiliesSupplier xFamiliesSupplier = (com.sun.star.style.XStyleFamiliesSupplier)
670 UnoRuntime.queryInterface( com.sun.star.style.XStyleFamiliesSupplier.class, getDocument() );
671 com.sun.star.container.XNameAccess xFamiliesNA = xFamiliesSupplier.getStyleFamilies();
672 Object aCellStylesObj = xFamiliesNA.getByName( "CellStyles" );
673 com.sun.star.container.XNameContainer xCellStylesNA = (com.sun.star.container.XNameContainer)
674 UnoRuntime.queryInterface( com.sun.star.container.XNameContainer.class, aCellStylesObj );
675
676 // create a new cell style
677 xDocServiceManager = (com.sun.star.lang.XMultiServiceFactory)
678 UnoRuntime.queryInterface( com.sun.star.lang.XMultiServiceFactory.class, getDocument() );
679 Object aCellStyle = xDocServiceManager.createInstance( "com.sun.star.style.CellStyle" );
680 String aStyleName = "MyNewCellStyle";
681 xCellStylesNA.insertByName( aStyleName, aCellStyle );
682
683 // modify properties of the new style
684 xPropSet = (com.sun.star.beans.XPropertySet)
685 UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, aCellStyle );
686 xPropSet.setPropertyValue( "CellBackColor", new Integer( 0x888888 ) );
687 xPropSet.setPropertyValue( "IsCellBackgroundTransparent", new Boolean( false ) );
688
689
690
691 // --- Query equal-formatted cell ranges ---
692 // prepare example, use the new cell style
693 xCellRange = xSheet.getCellRangeByName( "D2:F2" );
694 xPropSet = (com.sun.star.beans.XPropertySet) UnoRuntime.queryInterface(
695 com.sun.star.beans.XPropertySet.class, xCellRange );
696 xPropSet.setPropertyValue( "CellStyle", aStyleName );
697
698 xCellRange = xSheet.getCellRangeByName( "A3:G3" );
699 xPropSet = (com.sun.star.beans.XPropertySet) UnoRuntime.queryInterface(
700 com.sun.star.beans.XPropertySet.class, xCellRange );
701 xPropSet.setPropertyValue( "CellStyle", aStyleName );
702
703 // All ranges in one container
704 xCellRange = xSheet.getCellRangeByName( "A1:G3" );
705 System.out.println( "Service CellFormatRanges:" );
706 com.sun.star.sheet.XCellFormatRangesSupplier xFormatSupp =
707 (com.sun.star.sheet.XCellFormatRangesSupplier) UnoRuntime.queryInterface(
708 com.sun.star.sheet.XCellFormatRangesSupplier.class, xCellRange );
709 xRangeIA = xFormatSupp.getCellFormatRanges();
710 System.out.println( getCellRangeListString( xRangeIA ) );
711
712 // Ranges sorted in SheetCellRanges containers
713 System.out.println( "\nService UniqueCellFormatRanges:" );
714 com.sun.star.sheet.XUniqueCellFormatRangesSupplier xUniqueFormatSupp =
715 (com.sun.star.sheet.XUniqueCellFormatRangesSupplier) UnoRuntime.queryInterface(
716 com.sun.star.sheet.XUniqueCellFormatRangesSupplier.class, xCellRange );
717 com.sun.star.container.XIndexAccess xRangesIA = xUniqueFormatSupp.getUniqueCellFormatRanges();
718 int nCount = xRangesIA.getCount();
719 for (int nIndex = 0; nIndex < nCount; ++nIndex)
720 {
721 Object aRangesObj = xRangesIA.getByIndex( nIndex );
722 xRangeIA = (com.sun.star.container.XIndexAccess) UnoRuntime.queryInterface(
723 com.sun.star.container.XIndexAccess.class, aRangesObj );
724 System.out.println(
725 "Container " + (nIndex + 1) + ": " + getCellRangeListString( xRangeIA ) );
726 }
727
728
729 // --- Table auto formats ---
730 // get the global collection of table auto formats, use global service
731 // manager
732 com.sun.star.lang.XMultiComponentFactory xServiceManager = getServiceManager();
733
734 Object aAutoFormatsObj = xServiceManager.createInstanceWithContext(
735 "com.sun.star.sheet.TableAutoFormats", getContext());
736 com.sun.star.container.XNameContainer xAutoFormatsNA =
737 (com.sun.star.container.XNameContainer)UnoRuntime.queryInterface(
738 com.sun.star.container.XNameContainer.class, aAutoFormatsObj );
739
740 // create a new table auto format and insert into the container
741 String aAutoFormatName = "Temp_Example";
742 boolean bExistsAlready = xAutoFormatsNA.hasByName( aAutoFormatName );
743 Object aAutoFormatObj = null;
744 if (bExistsAlready)
745 // auto format already exists -> use it
746 aAutoFormatObj = xAutoFormatsNA.getByName( aAutoFormatName );
747 else
748 {
749 // create a new auto format (with document service manager!)
750 // xDocServiceManager = (com.sun.star.lang.XMultiServiceFactory)
751 // UnoRuntime.queryInterface( com.sun.star.lang.XMultiServiceFactory.class, getDocument() );
752 aAutoFormatObj = xDocServiceManager.createInstance(
753 "com.sun.star.sheet.TableAutoFormat" );
754 xAutoFormatsNA.insertByName( aAutoFormatName, aAutoFormatObj );
755 }
756 // index access to the auto format fields
757 com.sun.star.container.XIndexAccess xAutoFormatIA =
758 (com.sun.star.container.XIndexAccess)UnoRuntime.queryInterface(
759 com.sun.star.container.XIndexAccess.class, aAutoFormatObj );
760
761 // set properties of all auto format fields
762 for (int nRow = 0; nRow < 4; ++nRow)
763 {
764 int nRowColor = 0;
765 switch (nRow)
766 {
767 case 0: nRowColor = 0x999999; break;
768 case 1: nRowColor = 0xFFFFCC; break;
769 case 2: nRowColor = 0xEEEEEE; break;
770 case 3: nRowColor = 0x999999; break;
771 }
772
773 for (int nColumn = 0; nColumn < 4; ++nColumn)
774 {
775 int nColor = nRowColor;
776 if ((nColumn == 0) || (nColumn == 3))
777 nColor -= 0x333300;
778
779 // get the auto format field and apply properties
780 Object aFieldObj = xAutoFormatIA.getByIndex( 4 * nRow + nColumn );
781 xPropSet = (com.sun.star.beans.XPropertySet)
782 UnoRuntime.queryInterface(
783 com.sun.star.beans.XPropertySet.class, aFieldObj );
784 xPropSet.setPropertyValue( "CellBackColor", new Integer( nColor ) );
785 }
786 }
787
788 // set the auto format to the spreadsheet
789 xCellRange = xSheet.getCellRangeByName( "A5:H25" );
790 com.sun.star.table.XAutoFormattable xAutoForm = (com.sun.star.table.XAutoFormattable)
791 UnoRuntime.queryInterface( com.sun.star.table.XAutoFormattable.class, xCellRange );
792 xAutoForm.autoFormat( aAutoFormatName );
793
794 // remove the auto format
795 if (!bExistsAlready)
796 xAutoFormatsNA.removeByName( aAutoFormatName );
797
798
799 // --- Conditional formats ---
800 xSheet = getSpreadsheet( 0 );
801 prepareRange( xSheet, "K20:K23", "Cond. Format" );
802 setValue( xSheet, "K21", 1 );
803 setValue( xSheet, "K22", 2 );
804 setValue( xSheet, "K23", 3 );
805
806 // get the conditional format object of the cell range
807 xCellRange = xSheet.getCellRangeByName( "K21:K23" );
808 xPropSet = (com.sun.star.beans.XPropertySet)
809 UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xCellRange );
810 com.sun.star.sheet.XSheetConditionalEntries xEntries =
811 (com.sun.star.sheet.XSheetConditionalEntries) UnoRuntime.queryInterface(
812 com.sun.star.sheet.XSheetConditionalEntries.class,
813 xPropSet.getPropertyValue( "ConditionalFormat" ));
814
815 // create a condition and apply it to the range
816 com.sun.star.beans.PropertyValue[] aCondition = new com.sun.star.beans.PropertyValue[3];
817 aCondition[0] = new com.sun.star.beans.PropertyValue();
818 aCondition[0].Name = "Operator";
819 aCondition[0].Value = com.sun.star.sheet.ConditionOperator.GREATER;
820 aCondition[1] = new com.sun.star.beans.PropertyValue();
821 aCondition[1].Name = "Formula1";
822 aCondition[1].Value = "1";
823 aCondition[2] = new com.sun.star.beans.PropertyValue();
824 aCondition[2].Name = "StyleName";
825 aCondition[2].Value = aStyleName;
826 xEntries.addNew( aCondition );
827 xPropSet.setPropertyValue( "ConditionalFormat", xEntries );
828 }
829
830 // ________________________________________________________________
831
832 /** All samples regarding the spreadsheet document. */
833 private void doDocumentSamples() throws RuntimeException, Exception
834 {
835 System.out.println( "\n*** Samples for spreadsheet document ***\n" );
836
837
838 // --- Insert a new spreadsheet ---
839 com.sun.star.sheet.XSpreadsheet xSheet = insertSpreadsheet( "A new sheet", (short)0x7FFF );
840
841
842 // --- Copy a cell range ---
843 prepareRange( xSheet, "A1:B3", "Copy from" );
844 prepareRange( xSheet, "D1:E3", "To" );
845 setValue( xSheet, "A2", 123 );
846 setValue( xSheet, "B2", 345 );
847 setFormula( xSheet, "A3", "=SUM(A2:B2)" );
848 setFormula( xSheet, "B3", "=FORMULA(A3)" );
849
850 com.sun.star.sheet.XCellRangeMovement xMovement = (com.sun.star.sheet.XCellRangeMovement)
851 UnoRuntime.queryInterface( com.sun.star.sheet.XCellRangeMovement.class, xSheet );
852 com.sun.star.table.CellRangeAddress aSourceRange = createCellRangeAddress( xSheet, "A2:B3" );
853 com.sun.star.table.CellAddress aDestCell = createCellAddress( xSheet, "D2" );
854 xMovement.copyRange( aDestCell, aSourceRange );
855
856
857 // --- Print automatic column page breaks ---
858 com.sun.star.sheet.XSheetPageBreak xPageBreak = (com.sun.star.sheet.XSheetPageBreak)
859 UnoRuntime.queryInterface( com.sun.star.sheet.XSheetPageBreak.class, xSheet );
860 com.sun.star.sheet.TablePageBreakData[] aPageBreakArray = xPageBreak.getColumnPageBreaks();
861
862 System.out.print( "Automatic column page breaks:" );
863 for (int nIndex = 0; nIndex < aPageBreakArray.length; ++nIndex)
864 if (!aPageBreakArray[nIndex].ManualBreak)
865 System.out.print( " " + aPageBreakArray[nIndex].Position );
866 System.out.println();
867
868
869 // --- Document properties ---
870 com.sun.star.beans.XPropertySet xPropSet = (com.sun.star.beans.XPropertySet)
871 UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, getDocument() );
872
873 AnyConverter aAnyConv = new AnyConverter();
874 String aText = "Value of property IsIterationEnabled: ";
875 aText += aAnyConv.toBoolean(xPropSet.getPropertyValue( "IsIterationEnabled" ));
876 System.out.println( aText );
877 aText = "Value of property IterationCount: ";
878 aText += aAnyConv.toInt(xPropSet.getPropertyValue( "IterationCount" ));
879 System.out.println( aText );
880 aText = "Value of property NullDate: ";
881 com.sun.star.util.Date aDate = (com.sun.star.util.Date)
882 aAnyConv.toObject(com.sun.star.util.Date.class, xPropSet.getPropertyValue( "NullDate" ));
883 aText += aDate.Year + "-" + aDate.Month + "-" + aDate.Day;
884 System.out.println( aText );
885
886
887 // --- Data validation ---
888 prepareRange( xSheet, "A5:C7", "Validation" );
889 setFormula( xSheet, "A6", "Insert values between 0.0 and 5.0 below:" );
890
891 com.sun.star.table.XCellRange xCellRange = xSheet.getCellRangeByName( "A7:C7" );
892 com.sun.star.beans.XPropertySet xCellPropSet = (com.sun.star.beans.XPropertySet)
893 UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xCellRange );
894 // validation properties
895 com.sun.star.beans.XPropertySet xValidPropSet = (com.sun.star.beans.XPropertySet)
896 UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class,
897 xCellPropSet.getPropertyValue( "Validation" ));
898 xValidPropSet.setPropertyValue( "Type", com.sun.star.sheet.ValidationType.DECIMAL );
899 xValidPropSet.setPropertyValue( "ShowErrorMessage", new Boolean( true ) );
900 xValidPropSet.setPropertyValue( "ErrorMessage", "This is an invalid value!" );
901 xValidPropSet.setPropertyValue( "ErrorAlertStyle", com.sun.star.sheet.ValidationAlertStyle.STOP );
902 // condition
903 com.sun.star.sheet.XSheetCondition xCondition = (com.sun.star.sheet.XSheetCondition)
904 UnoRuntime.queryInterface( com.sun.star.sheet.XSheetCondition.class, xValidPropSet );
905 xCondition.setOperator( com.sun.star.sheet.ConditionOperator.BETWEEN );
906 xCondition.setFormula1( "0.0" );
907 xCondition.setFormula2( "5.0" );
908 // apply on cell range
909 xCellPropSet.setPropertyValue( "Validation", xValidPropSet );
910
911 // --- Scenarios ---
912 Object[][] aValues = new Object[2][2];
913
914 aValues[0][0] = new Double( 11 );
915 aValues[0][1] = new Double( 12 );
916 aValues[1][0] = "Test13";
917 aValues[1][1] = "Test14";
918 insertScenario( xSheet, "B10:C11", aValues, "First Scenario", "The first scenario." );
919
920 aValues[0][0] = "Test21";
921 aValues[0][1] = "Test22";
922 aValues[1][0] = new Double( 23 );
923 aValues[1][1] = new Double( 24 );
924 insertScenario( xSheet, "B10:C11", aValues, "Second Scenario", "The visible scenario." );
925
926 aValues[0][0] = new Double( 31 );
927 aValues[0][1] = new Double( 32 );
928 aValues[1][0] = "Test33";
929 aValues[1][1] = "Test34";
930 insertScenario( xSheet, "B10:C11", aValues, "Third Scenario", "The last scenario." );
931
932 // show second scenario
933 showScenario( xSheet, "Second Scenario" );
934 }
935
936 /** Inserts a scenario containing one cell range into a sheet and
937 applies the value array.
938 @param xSheet The XSpreadsheet interface of the spreadsheet.
939 @param aRange The range address for the scenario.
940 @param aValueArray The array of cell contents.
941 @param aScenarioName The name of the new scenario.
942 @param aScenarioComment The user comment for the scenario. */
943 private void insertScenario(
944 com.sun.star.sheet.XSpreadsheet xSheet,
945 String aRange,
946 Object[][] aValueArray,
947 String aScenarioName,
948 String aScenarioComment ) throws RuntimeException, Exception
949 {
950 // get the cell range with the given address
951 com.sun.star.table.XCellRange xCellRange = xSheet.getCellRangeByName( aRange );
952
953 // create the range address sequence
954 com.sun.star.sheet.XCellRangeAddressable xAddr = (com.sun.star.sheet.XCellRangeAddressable)
955 UnoRuntime.queryInterface( com.sun.star.sheet.XCellRangeAddressable.class, xCellRange );
956 com.sun.star.table.CellRangeAddress[] aRangesSeq = new com.sun.star.table.CellRangeAddress[1];
957 aRangesSeq[0] = xAddr.getRangeAddress();
958
959 // create the scenario
960 com.sun.star.sheet.XScenariosSupplier xScenSupp = (com.sun.star.sheet.XScenariosSupplier)
961 UnoRuntime.queryInterface( com.sun.star.sheet.XScenariosSupplier.class, xSheet );
962 com.sun.star.sheet.XScenarios xScenarios = xScenSupp.getScenarios();
963 xScenarios.addNewByName( aScenarioName, aRangesSeq, aScenarioComment );
964
965 // insert the values into the range
966 com.sun.star.sheet.XCellRangeData xData = (com.sun.star.sheet.XCellRangeData)
967 UnoRuntime.queryInterface( com.sun.star.sheet.XCellRangeData.class, xCellRange );
968 xData.setDataArray( aValueArray );
969 }
970
971 /** Activates a scenario.
972 @param xSheet The XSpreadsheet interface of the spreadsheet.
973 @param aScenarioName The name of the scenario. */
974 private void showScenario(
975 com.sun.star.sheet.XSpreadsheet xSheet,
976 String aScenarioName ) throws RuntimeException, Exception
977 {
978 // get the scenario set
979 com.sun.star.sheet.XScenariosSupplier xScenSupp = (com.sun.star.sheet.XScenariosSupplier)
980 UnoRuntime.queryInterface( com.sun.star.sheet.XScenariosSupplier.class, xSheet );
981 com.sun.star.sheet.XScenarios xScenarios = xScenSupp.getScenarios();
982
983 // get the scenario and activate it
984 Object aScenarioObj = xScenarios.getByName( aScenarioName );
985 com.sun.star.sheet.XScenario xScenario = (com.sun.star.sheet.XScenario)
986 UnoRuntime.queryInterface( com.sun.star.sheet.XScenario.class, aScenarioObj );
987 xScenario.apply();
988 }
989
990 // ________________________________________________________________
991
992 private void doNamedRangesSamples() throws RuntimeException, Exception
993 {
994 System.out.println( "\n*** Samples for named ranges ***\n" );
995 com.sun.star.sheet.XSpreadsheetDocument xDocument = getDocument();
996 com.sun.star.sheet.XSpreadsheet xSheet = getSpreadsheet( 0 );
997
998
999 // --- Named ranges ---
1000 prepareRange( xSheet, "G42:H45", "Named ranges" );
1001 xSheet.getCellByPosition( 6, 42 ).setValue( 1 );
1002 xSheet.getCellByPosition( 6, 43 ).setValue( 2 );
1003 xSheet.getCellByPosition( 7, 42 ).setValue( 3 );
1004 xSheet.getCellByPosition( 7, 43 ).setValue( 4 );
1005
1006 // insert a named range
1007 com.sun.star.beans.XPropertySet xDocProp = (com.sun.star.beans.XPropertySet)
1008 UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xDocument );
1009 Object aRangesObj = xDocProp.getPropertyValue( "NamedRanges" );
1010 com.sun.star.sheet.XNamedRanges xNamedRanges = (com.sun.star.sheet.XNamedRanges)
1011 UnoRuntime.queryInterface( com.sun.star.sheet.XNamedRanges.class, aRangesObj );
1012 com.sun.star.table.CellAddress aRefPos = new com.sun.star.table.CellAddress();
1013 aRefPos.Sheet = 0;
1014 aRefPos.Column = 6;
1015 aRefPos.Row = 44;
1016 xNamedRanges.addNewByName( "ExampleName", "SUM(G43:G44)", aRefPos, 0 );
1017
1018 // use the named range in formulas
1019 xSheet.getCellByPosition( 6, 44 ).setFormula( "=ExampleName" );
1020 xSheet.getCellByPosition( 7, 44 ).setFormula( "=ExampleName" );
1021
1022
1023 // --- Label ranges ---
1024 prepareRange( xSheet, "G47:I50", "Label ranges" );
1025 com.sun.star.table.XCellRange xRange = xSheet.getCellRangeByPosition( 6, 47, 7, 49 );
1026 com.sun.star.sheet.XCellRangeData xData = ( com.sun.star.sheet.XCellRangeData )
1027 UnoRuntime.queryInterface( com.sun.star.sheet.XCellRangeData.class, xRange );
1028 Object[][] aValues =
1029 {
1030 { "Apples", "Oranges" },
1031 { new Double( 5 ), new Double( 7 ) },
1032 { new Double( 6 ), new Double( 8 ) }
1033 };
1034 xData.setDataArray( aValues );
1035
1036 // insert a column label range
1037 Object aLabelsObj = xDocProp.getPropertyValue( "ColumnLabelRanges" );
1038 com.sun.star.sheet.XLabelRanges xLabelRanges = (com.sun.star.sheet.XLabelRanges)
1039 UnoRuntime.queryInterface( com.sun.star.sheet.XLabelRanges.class, aLabelsObj );
1040 com.sun.star.table.CellRangeAddress aLabelArea = new com.sun.star.table.CellRangeAddress();
1041 aLabelArea.Sheet = 0;
1042 aLabelArea.StartColumn = 6;
1043 aLabelArea.StartRow = 47;
1044 aLabelArea.EndColumn = 7;
1045 aLabelArea.EndRow = 47;
1046 com.sun.star.table.CellRangeAddress aDataArea = new com.sun.star.table.CellRangeAddress();
1047 aDataArea.Sheet = 0;
1048 aDataArea.StartColumn = 6;
1049 aDataArea.StartRow = 48;
1050 aDataArea.EndColumn = 7;
1051 aDataArea.EndRow = 49;
1052 xLabelRanges.addNew( aLabelArea, aDataArea );
1053
1054 // use the label range in formulas
1055 xSheet.getCellByPosition( 8, 48 ).setFormula( "=Apples+Oranges" );
1056 xSheet.getCellByPosition( 8, 49 ).setFormula( "=Apples+Oranges" );
1057 }
1058
1059 // ________________________________________________________________
1060
1061 /** Helper for doDatabaseSamples: get name of first database. */
1062 private String getFirstDatabaseName()
1063 {
1064 String aDatabase = null;
1065 try
1066 {
1067 com.sun.star.lang.XMultiComponentFactory xServiceManager = getServiceManager();
1068 com.sun.star.container.XNameAccess xContext =
1069 (com.sun.star.container.XNameAccess) UnoRuntime.queryInterface(
1070 com.sun.star.container.XNameAccess.class,
1071 xServiceManager.createInstanceWithContext(
1072 "com.sun.star.sdb.DatabaseContext", getContext()) );
1073 String[] aNames = xContext.getElementNames();
1074 if ( aNames.length > 0 )
1075 aDatabase = aNames[0];
1076 }
1077 catch ( Exception e )
1078 {
1079 System.out.println( "\nError: caught exception in getFirstDatabaseName()!\n" +
1080 "Exception Message = "
1081 + e.getMessage());
1082 e.printStackTrace();
1083 }
1084 return aDatabase;
1085 }
1086
1087 /** Helper for doDatabaseSamples: get name of first table in a database. */
1088 private String getFirstTableName( String aDatabase )
1089 {
1090 if ( aDatabase == null )
1091 return null;
1092
1093 String aTable = null;
1094 try
1095 {
1096 com.sun.star.lang.XMultiComponentFactory xServiceManager = getServiceManager();
1097 com.sun.star.container.XNameAccess xContext = (com.sun.star.container.XNameAccess)
1098 UnoRuntime.queryInterface( com.sun.star.container.XNameAccess.class,
1099 xServiceManager.createInstanceWithContext(
1100 "com.sun.star.sdb.DatabaseContext", getContext()) );
1101 com.sun.star.sdb.XCompletedConnection xSource =
1102 (com.sun.star.sdb.XCompletedConnection)UnoRuntime.queryInterface(
1103 com.sun.star.sdb.XCompletedConnection.class,
1104 xContext.getByName( aDatabase ) );
1105 com.sun.star.task.XInteractionHandler xHandler =
1106 (com.sun.star.task.XInteractionHandler)UnoRuntime.queryInterface(
1107 com.sun.star.task.XInteractionHandler.class,
1108 xServiceManager.createInstanceWithContext(
1109 "com.sun.star.sdb.InteractionHandler", getContext()) );
1110 com.sun.star.sdbcx.XTablesSupplier xSupplier =
1111 (com.sun.star.sdbcx.XTablesSupplier)UnoRuntime.queryInterface(
1112 com.sun.star.sdbcx.XTablesSupplier.class,
1113 xSource.connectWithCompletion( xHandler ) );
1114 com.sun.star.container.XNameAccess xTables = xSupplier.getTables();
1115 String[] aNames = xTables.getElementNames();
1116 if ( aNames.length > 0 )
1117 aTable = aNames[0];
1118 }
1119 catch ( Exception e )
1120 {
1121 System.out.println( "\nError: caught exception in getFirstTableName()!\n" +
1122 "Exception Message = "
1123 + e.getMessage());
1124 e.printStackTrace();
1125 }
1126 return aTable;
1127 }
1128
1129 private void doDatabaseSamples() throws Exception
1130 {
1131 System.out.println( "\n*** Samples for database operations ***\n" );
1132 com.sun.star.sheet.XSpreadsheet xSheet = getSpreadsheet( 2 );
1133
1134
1135 // --- put some example data into the sheet ---
1136 com.sun.star.table.XCellRange xRange = xSheet.getCellRangeByName( "B3:D24" );
1137 com.sun.star.sheet.XCellRangeData xData = ( com.sun.star.sheet.XCellRangeData )
1138 UnoRuntime.queryInterface( com.sun.star.sheet.XCellRangeData.class, xRange );
1139 Object[][] aValues =
1140 {
1141 { "Name", "Year", "Sales" },
1142 { "Alice", new Double( 2001 ), new Double( 4.0 ) },
1143 { "Carol", new Double( 1997 ), new Double( 3.0 ) },
1144 { "Carol", new Double( 1998 ), new Double( 8.0 ) },
1145 { "Bob", new Double( 1997 ), new Double( 8.0 ) },
1146 { "Alice", new Double( 2002 ), new Double( 9.0 ) },
1147 { "Alice", new Double( 1999 ), new Double( 7.0 ) },
1148 { "Alice", new Double( 1996 ), new Double( 3.0 ) },
1149 { "Bob", new Double( 2000 ), new Double( 1.0 ) },
1150 { "Carol", new Double( 1999 ), new Double( 5.0 ) },
1151 { "Bob", new Double( 2002 ), new Double( 1.0 ) },
1152 { "Carol", new Double( 2001 ), new Double( 5.0 ) },
1153 { "Carol", new Double( 2000 ), new Double( 1.0 ) },
1154 { "Carol", new Double( 1996 ), new Double( 8.0 ) },
1155 { "Bob", new Double( 1996 ), new Double( 7.0 ) },
1156 { "Alice", new Double( 1997 ), new Double( 3.0 ) },
1157 { "Alice", new Double( 2000 ), new Double( 9.0 ) },
1158 { "Bob", new Double( 1998 ), new Double( 1.0 ) },
1159 { "Bob", new Double( 1999 ), new Double( 6.0 ) },
1160 { "Carol", new Double( 2002 ), new Double( 8.0 ) },
1161 { "Alice", new Double( 1998 ), new Double( 5.0 ) },
1162 { "Bob", new Double( 2001 ), new Double( 6.0 ) }
1163 };
1164 xData.setDataArray( aValues );
1165
1166
1167 // --- filter for second column >= 1998 ---
1168 com.sun.star.sheet.XSheetFilterable xFilter = ( com.sun.star.sheet.XSheetFilterable )
1169 UnoRuntime.queryInterface( com.sun.star.sheet.XSheetFilterable.class, xRange );
1170 com.sun.star.sheet.XSheetFilterDescriptor xFilterDesc =
1171 xFilter.createFilterDescriptor( true );
1172 com.sun.star.sheet.TableFilterField[] aFilterFields =
1173 new com.sun.star.sheet.TableFilterField[1];
1174 aFilterFields[0] = new com.sun.star.sheet.TableFilterField();
1175 aFilterFields[0].Field = 1;
1176 aFilterFields[0].IsNumeric = true;
1177 aFilterFields[0].Operator = com.sun.star.sheet.FilterOperator.GREATER_EQUAL;
1178 aFilterFields[0].NumericValue = 1998;
1179 xFilterDesc.setFilterFields( aFilterFields );
1180 com.sun.star.beans.XPropertySet xFilterProp = (com.sun.star.beans.XPropertySet)
1181 UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xFilterDesc );
1182 xFilterProp.setPropertyValue( "ContainsHeader", new Boolean( true ) );
1183 xFilter.filter( xFilterDesc );
1184
1185
1186 // --- do the same filter as above, using criteria from a cell range ---
1187 com.sun.star.table.XCellRange xCritRange = xSheet.getCellRangeByName( "B27:B28" );
1188 com.sun.star.sheet.XCellRangeData xCritData = ( com.sun.star.sheet.XCellRangeData )
1189 UnoRuntime.queryInterface( com.sun.star.sheet.XCellRangeData.class, xCritRange );
1190 Object[][] aCritValues =
1191 {
1192 { "Year" },
1193 { ">= 1998" }
1194 };
1195 xCritData.setDataArray( aCritValues );
1196 com.sun.star.sheet.XSheetFilterableEx xCriteria = ( com.sun.star.sheet.XSheetFilterableEx )
1197 UnoRuntime.queryInterface( com.sun.star.sheet.XSheetFilterableEx.class, xCritRange );
1198 xFilterDesc = xCriteria.createFilterDescriptorByObject( xFilter );
1199 if ( xFilterDesc != null )
1200 xFilter.filter( xFilterDesc );
1201
1202
1203 // --- sort by second column, ascending ---
1204 com.sun.star.table.TableSortField[] aSortFields = new com.sun.star.table.TableSortField[1];
1205 aSortFields[0] = new com.sun.star.table.TableSortField();
1206 aSortFields[0].Field = 1;
1207 aSortFields[0].IsAscending = false;
1208 aSortFields[0].IsCaseSensitive = false;
1209
1210
1211 com.sun.star.beans.PropertyValue[] aSortDesc = new com.sun.star.beans.PropertyValue[2];
1212 aSortDesc[0] = new com.sun.star.beans.PropertyValue();
1213 aSortDesc[0].Name = "SortFields";
1214 aSortDesc[0].Value = aSortFields;
1215 aSortDesc[1] = new com.sun.star.beans.PropertyValue();
1216 aSortDesc[1].Name = "ContainsHeader";
1217 aSortDesc[1].Value = new Boolean( true );
1218
1219 com.sun.star.util.XSortable xSort = ( com.sun.star.util.XSortable )
1220 UnoRuntime.queryInterface( com.sun.star.util.XSortable.class, xRange );
1221 xSort.sort( aSortDesc );
1222
1223
1224 // --- insert subtotals ---
1225 com.sun.star.sheet.XSubTotalCalculatable xSub = ( com.sun.star.sheet.XSubTotalCalculatable )
1226 UnoRuntime.queryInterface( com.sun.star.sheet.XSubTotalCalculatable.class, xRange );
1227 com.sun.star.sheet.XSubTotalDescriptor xSubDesc = xSub.createSubTotalDescriptor( true );
1228 com.sun.star.sheet.SubTotalColumn[] aColumns = new com.sun.star.sheet.SubTotalColumn[1];
1229 // calculate sum of third column
1230 aColumns[0] = new com.sun.star.sheet.SubTotalColumn();
1231 aColumns[0].Column = 2;
1232 aColumns[0].Function = com.sun.star.sheet.GeneralFunction.SUM;
1233 // group by first column
1234 xSubDesc.addNew( aColumns, 0 );
1235 xSub.applySubTotals( xSubDesc, true );
1236
1237 String aDatabase = getFirstDatabaseName();
1238 String aTableName = getFirstTableName( aDatabase );
1239 if ( aDatabase != null && aTableName != null )
1240 {
1241 // --- import from database ---
1242 com.sun.star.beans.PropertyValue[] aImportDesc = new com.sun.star.beans.PropertyValue[3];
1243 aImportDesc[0] = new com.sun.star.beans.PropertyValue();
1244 aImportDesc[0].Name = "DatabaseName";
1245 aImportDesc[0].Value = aDatabase;
1246 aImportDesc[1] = new com.sun.star.beans.PropertyValue();
1247 aImportDesc[1].Name = "SourceType";
1248 aImportDesc[1].Value = com.sun.star.sheet.DataImportMode.TABLE;
1249 aImportDesc[2] = new com.sun.star.beans.PropertyValue();
1250 aImportDesc[2].Name = "SourceObject";
1251 aImportDesc[2].Value = aTableName;
1252
1253 com.sun.star.table.XCellRange xImportRange = xSheet.getCellRangeByName( "B35:B35" );
1254 com.sun.star.util.XImportable xImport = ( com.sun.star.util.XImportable )
1255 UnoRuntime.queryInterface( com.sun.star.util.XImportable.class, xImportRange );
1256 xImport.doImport( aImportDesc );
1257
1258
1259 // --- use the temporary database range to find the imported data's size ---
1260 com.sun.star.beans.XPropertySet xDocProp = (com.sun.star.beans.XPropertySet)
1261 UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, getDocument() );
1262 Object aRangesObj = xDocProp.getPropertyValue( "DatabaseRanges" );
1263 com.sun.star.container.XNameAccess xRanges =
1264 (com.sun.star.container.XNameAccess) UnoRuntime.queryInterface(
1265 com.sun.star.container.XNameAccess.class, aRangesObj );
1266 String[] aNames = xRanges.getElementNames();
1267 AnyConverter aAnyConv = new AnyConverter();
1268 for ( int i=0; i<aNames.length; i++ )
1269 {
1270 Object aRangeObj = xRanges.getByName( aNames[i] );
1271 com.sun.star.beans.XPropertySet xRangeProp = (com.sun.star.beans.XPropertySet)
1272 UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, aRangeObj );
1273 boolean bUser = aAnyConv.toBoolean(xRangeProp.getPropertyValue( "IsUserDefined" ));
1274 if ( !bUser )
1275 {
1276 // this is the temporary database range - get the cell range and format it
1277 com.sun.star.sheet.XCellRangeReferrer xRef = ( com.sun.star.sheet.XCellRangeReferrer )
1278 UnoRuntime.queryInterface( com.sun.star.sheet.XCellRangeReferrer.class, aRangeObj );
1279 com.sun.star.table.XCellRange xResultRange = xRef.getReferredCells();
1280 com.sun.star.beans.XPropertySet xResultProp = (com.sun.star.beans.XPropertySet)
1281 UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xResultRange );
1282 xResultProp.setPropertyValue( "IsCellBackgroundTransparent", new Boolean( false ) );
1283 xResultProp.setPropertyValue( "CellBackColor", new Integer( 0xFFFFCC ) );
1284 }
1285 }
1286 }
1287 else
1288 System.out.println("can't get database");
1289 }
1290
1291 // ________________________________________________________________
1292
1293 private void doDataPilotSamples() throws Exception
1294 {
1295 System.out.println( "\n*** Samples for Data Pilot ***\n" );
1296 com.sun.star.sheet.XSpreadsheet xSheet = getSpreadsheet( 0 );
1297
1298
1299 // --- Create a new DataPilot table ---
1300 prepareRange( xSheet, "A38:C38", "Data Pilot" );
1301 com.sun.star.sheet.XDataPilotTablesSupplier xDPSupp = (com.sun.star.sheet.XDataPilotTablesSupplier)
1302 UnoRuntime.queryInterface( com.sun.star.sheet.XDataPilotTablesSupplier.class, xSheet );
1303 com.sun.star.sheet.XDataPilotTables xDPTables = xDPSupp.getDataPilotTables();
1304 com.sun.star.sheet.XDataPilotDescriptor xDPDesc = xDPTables.createDataPilotDescriptor();
1305 // set source range (use data range from CellRange test)
1306 com.sun.star.table.CellRangeAddress aSourceAddress = createCellRangeAddress( xSheet, "A10:C30" );
1307 xDPDesc.setSourceRange( aSourceAddress );
1308 // settings for fields
1309 com.sun.star.container.XIndexAccess xFields = xDPDesc.getDataPilotFields();
1310 Object aFieldObj;
1311 com.sun.star.beans.XPropertySet xFieldProp;
1312 // use first column as column field
1313 aFieldObj = xFields.getByIndex(0);
1314 xFieldProp = (com.sun.star.beans.XPropertySet)
1315 UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, aFieldObj );
1316 xFieldProp.setPropertyValue( "Orientation", com.sun.star.sheet.DataPilotFieldOrientation.COLUMN );
1317 // use second column as row field
1318 aFieldObj = xFields.getByIndex(1);
1319 xFieldProp = (com.sun.star.beans.XPropertySet)
1320 UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, aFieldObj );
1321 xFieldProp.setPropertyValue( "Orientation", com.sun.star.sheet.DataPilotFieldOrientation.ROW );
1322 // use third column as data field, calculating the sum
1323 aFieldObj = xFields.getByIndex(2);
1324 xFieldProp = (com.sun.star.beans.XPropertySet)
1325 UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, aFieldObj );
1326 xFieldProp.setPropertyValue( "Orientation", com.sun.star.sheet.DataPilotFieldOrientation.DATA );
1327 xFieldProp.setPropertyValue( "Function", com.sun.star.sheet.GeneralFunction.SUM );
1328 // select output position
1329 com.sun.star.table.CellAddress aDestAddress = createCellAddress( xSheet, "A40" );
1330 xDPTables.insertNewByName( "DataPilotExample", aDestAddress, xDPDesc );
1331
1332
1333 // --- Modify the DataPilot table ---
1334 Object aDPTableObj = xDPTables.getByName( "DataPilotExample" );
1335 xDPDesc = (com.sun.star.sheet.XDataPilotDescriptor)
1336 UnoRuntime.queryInterface( com.sun.star.sheet.XDataPilotDescriptor.class, aDPTableObj );
1337 xFields = xDPDesc.getDataPilotFields();
1338 // add a second data field from the third column, calculating the average
1339 aFieldObj = xFields.getByIndex(2);
1340 xFieldProp = (com.sun.star.beans.XPropertySet)
1341 UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, aFieldObj );
1342 xFieldProp.setPropertyValue( "Orientation", com.sun.star.sheet.DataPilotFieldOrientation.DATA );
1343 xFieldProp.setPropertyValue( "Function", com.sun.star.sheet.GeneralFunction.AVERAGE );
1344 }
1345
1346 // ________________________________________________________________
1347
1348 private void doFunctionAccessSamples() throws RuntimeException, Exception
1349 {
1350 System.out.println( "\n*** Samples for function handling ***\n" );
1351 com.sun.star.lang.XMultiComponentFactory xServiceManager = getServiceManager();
1352
1353
1354 // --- Calculate a function ---
1355 Object aFuncInst = xServiceManager.createInstanceWithContext(
1356 "com.sun.star.sheet.FunctionAccess", getContext());
1357 com.sun.star.sheet.XFunctionAccess xFuncAcc =
1358 (com.sun.star.sheet.XFunctionAccess)UnoRuntime.queryInterface(
1359 com.sun.star.sheet.XFunctionAccess.class, aFuncInst );
1360 // put the data in a two-dimensional array
1361 double[][] aData = { { 1.0, 2.0, 3.0 } };
1362 // construct the array of function arguments
1363 Object[] aArgs = new Object[2];
1364 aArgs[0] = aData;
1365 aArgs[1] = new Double( 2.0 );
1366 Object aResult = xFuncAcc.callFunction( "ZTEST", aArgs );
1367 System.out.println("ZTEST result for data {1,2,3} and value 2 is "
1368 + ((Double)aResult).doubleValue() );
1369
1370
1371 // --- Get the list of recently used functions ---
1372 Object aRecInst = xServiceManager.createInstanceWithContext(
1373 "com.sun.star.sheet.RecentFunctions", getContext());
1374 com.sun.star.sheet.XRecentFunctions xRecFunc =
1375 (com.sun.star.sheet.XRecentFunctions)UnoRuntime.queryInterface(
1376 com.sun.star.sheet.XRecentFunctions.class, aRecInst );
1377 int[] nRecentIds = xRecFunc.getRecentFunctionIds();
1378
1379
1380 // --- Get the names for these functions ---
1381 Object aDescInst = xServiceManager.createInstanceWithContext(
1382 "com.sun.star.sheet.FunctionDescriptions", getContext());
1383 com.sun.star.sheet.XFunctionDescriptions xFuncDesc =
1384 (com.sun.star.sheet.XFunctionDescriptions)UnoRuntime.queryInterface(
1385 com.sun.star.sheet.XFunctionDescriptions.class, aDescInst );
1386 System.out.print("Recently used functions: ");
1387 for (int nFunction=0; nFunction<nRecentIds.length; nFunction++)
1388 {
1389 com.sun.star.beans.PropertyValue[] aProperties =
1390 xFuncDesc.getById( nRecentIds[nFunction] );
1391 for (int nProp=0; nProp<aProperties.length; nProp++)
1392 if ( aProperties[nProp].Name.equals( "Name" ) )
1393 System.out.print( aProperties[nProp].Value + " " );
1394 }
1395 System.out.println();
1396 }
1397
1398 // ________________________________________________________________
1399
1400 private void doApplicationSettingsSamples() throws RuntimeException, Exception
1401 {
1402 System.out.println( "\n*** Samples for application settings ***\n" );
1403 com.sun.star.lang.XMultiComponentFactory xServiceManager = getServiceManager();
1404
1405
1406 // --- Get the user defined sort lists ---
1407 Object aSettings = xServiceManager.createInstanceWithContext(
1408 "com.sun.star.sheet.GlobalSheetSettings", getContext());
1409 com.sun.star.beans.XPropertySet xPropSet =
1410 (com.sun.star.beans.XPropertySet)UnoRuntime.queryInterface(
1411 com.sun.star.beans.XPropertySet.class, aSettings );
1412 AnyConverter aAnyConv = new AnyConverter();
1413 String[] aEntries = (String[])
1414 aAnyConv.toObject(String[].class,
1415 xPropSet.getPropertyValue( "UserLists" ));
1416 System.out.println("User defined sort lists:");
1417 for ( int i=0; i<aEntries.length; i++ )
1418 System.out.println( aEntries[i] );
1419 }
1420
1421 // ________________________________________________________________
1422
1423 }
vba interoperability for OOo