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