odk/examples/DevelopersGuide/Spreadsheet/SpreadsheetSample.java

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