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 Main( String [] args )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 SpreadsheetSample( String[] args )48 public SpreadsheetSample( String[] args ) 49 : base( args ) 50 { 51 } 52 53 /** This sample function performs all changes on the document. */ doSampleFunctions()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. */ doCellSamples()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. */ doCellRangeSamples()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. */ getCellSeries( unoidl.com.sun.star.sheet.XSpreadsheet xSheet, String aRange )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. */ doCellRangesSamples()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 */ insertRange( unoidl.com.sun.star.sheet.XSheetCellRangeContainer xContainer, int nSheet, int nStartCol, int nStartRow, int nEndCol, int nEndRow, bool bMerge )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. */ doCellCursorSamples()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. */ doFormattingSamples()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. */ doDocumentSamples()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. */ insertScenario( unoidl.com.sun.star.sheet.XSpreadsheet xSheet, String aRange, uno.Any [][] aValueArray, String aScenarioName, String aScenarioComment )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. */ showScenario( unoidl.com.sun.star.sheet.XSpreadsheet xSheet, String aScenarioName )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 doNamedRangesSamples()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. */ getFirstDatabaseName()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. */ getFirstTableName( String aDatabase )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 doDatabaseSamples()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 doDataPilotSamples()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 doFunctionAccessSamples()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 doApplicationSettingsSamples()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