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