odk/examples/DevelopersGuide/Spreadsheet/SpreadsheetSample.java

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