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 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 57 public SpreadsheetSample( String[] args ) 58 { 59 super( args ); 60 } 61 62 // ________________________________________________________________ 63 64 /** This sample function performs all changes on the document. */ 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. */ 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. */ 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. */ 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. */ 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. */ 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. */ 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. */ 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. */ 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. */ 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. */ 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 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. */ 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. */ 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 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 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 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 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