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