odk/examples/DevelopersGuide/Charts/CalcHelper.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 // __________ Imports __________
  36
  37 import java.util.Random;
  38
  39 import com.sun.star.uno.UnoRuntime;
  40 import com.sun.star.lang.XMultiServiceFactory;
  41 // container access
  42 import com.sun.star.container.*;
  43
  44 // application specific classes
  45 import com.sun.star.sheet.*;
  46 import com.sun.star.table.*;
  47 import com.sun.star.chart.*;
  48 import com.sun.star.text.XText;
  49
  50 import com.sun.star.document.XEmbeddedObjectSupplier;
  51 import com.sun.star.frame.XModel;
  52 // base graphics things
  53 import com.sun.star.awt.Point;
  54 import com.sun.star.awt.Size;
  55 import com.sun.star.awt.Rectangle;
  56
  57 // Exceptions
  58 import com.sun.star.uno.RuntimeException;
  59 import com.sun.star.container.NoSuchElementException;
  60
  61 // __________ Implementation __________
  62
  63 // Helper for accessing a calc document
  64
  65 public class CalcHelper
  66 {
  67     public CalcHelper( XSpreadsheetDocument aDoc )
  68     {
  69         maSpreadSheetDoc = aDoc;
  70         initSpreadSheet();
  71     }
  72
  73
  74
  75     public XSpreadsheet getChartSheet() throws RuntimeException
  76     {
  77         XNameAccess aSheetsNA = UnoRuntime.queryInterface(
  78             XNameAccess.class, maSpreadSheetDoc.getSheets() );
  79
  80         XSpreadsheet aSheet = null;
  81         try
  82         {
  83             aSheet = UnoRuntime.queryInterface(
  84                 XSpreadsheet.class, aSheetsNA.getByName( msChartSheetName ) );
  85         }
  86         catch( NoSuchElementException ex )
  87         {
  88             System.out.println( "Couldn't find sheet with name " + msChartSheetName + ": " + ex );
  89         }
  90         catch( Exception ex )
  91         {}
  92
  93         return aSheet;
  94     }
  95
  96
  97
  98     public XSpreadsheet getDataSheet() throws RuntimeException
  99     {
 100         XNameAccess aSheetsNA = UnoRuntime.queryInterface(
 101             XNameAccess.class, maSpreadSheetDoc.getSheets() );
 102
 103         XSpreadsheet aSheet = null;
 104         if( aSheetsNA != null )
 105         {
 106             try
 107             {
 108                 aSheet = UnoRuntime.queryInterface(
 109                     XSpreadsheet.class, aSheetsNA.getByName( msDataSheetName ) );
 110             }
 111             catch( NoSuchElementException ex )
 112             {
 113                 System.out.println( "Couldn't find sheet with name " + msDataSheetName + ": " + ex );
 114             }
 115             catch( Exception ex )
 116             {}
 117         }
 118
 119         return aSheet;
 120     }
 121
 122
 123
 124     /** Insert a chart using the given name as name of the OLE object and the range as correspoding
 125         range of data to be used for rendering.  The chart is placed in the sheet for charts at
 126         position aUpperLeft extending as large as given in aExtent.
 127
 128         The service name must be the name of a diagram service that can be instantiated via the
 129         factory of the chart document
 130      */
 131     public XChartDocument insertChart(
 132         String               sChartName,
 133         CellRangeAddress     aRange,
 134         Point                aUpperLeft,
 135         Size                 aExtent,
 136         String               sChartServiceName )
 137     {
 138         XChartDocument aResult = null;
 139         XTableChartsSupplier aSheet;
 140
 141         // get the sheet to insert the chart
 142         try
 143         {
 144             aSheet = UnoRuntime.queryInterface(
 145                 XTableChartsSupplier.class, getChartSheet() );
 146         }
 147         catch( Exception ex )
 148         {
 149             System.out.println( "Sheet not found" + ex );
 150             return aResult;
 151         }
 152
 153         XTableCharts aChartCollection = aSheet.getCharts();
 154         XNameAccess  aChartCollectionNA = UnoRuntime.queryInterface(
 155             XNameAccess.class, aChartCollection );
 156
 157         if( aChartCollectionNA != null &&
 158             ! aChartCollectionNA.hasByName( sChartName ) )
 159         {
 160             Rectangle aRect = new Rectangle( aUpperLeft.X, aUpperLeft.Y, aExtent.Width, aExtent.Height );
 161
 162             CellRangeAddress[] aAddresses = new CellRangeAddress[ 1 ];
 163             aAddresses[ 0 ] = aRange;
 164
 165             // first bool: ColumnHeaders
 166             // second bool: RowHeaders
 167             aChartCollection.addNewByName( sChartName, aRect, aAddresses, true, false );
 168
 169             try
 170             {
 171                 XTableChart aTableChart = UnoRuntime.queryInterface(
 172                     XTableChart.class, aChartCollectionNA.getByName( sChartName ));
 173
 174                 // the table chart is an embedded object which contains the chart document
 175                 aResult = UnoRuntime.queryInterface(
 176                     XChartDocument.class,
 177                     UnoRuntime.queryInterface(
 178                         XEmbeddedObjectSupplier.class,
 179                         aTableChart ).getEmbeddedObject());
 180
 181                 // create a diagram via the factory and set this as new diagram
 182                 aResult.setDiagram(
 183                     UnoRuntime.queryInterface(
 184                         XDiagram.class,
 185                         UnoRuntime.queryInterface(
 186                             XMultiServiceFactory.class,
 187                             aResult ).createInstance( sChartServiceName )));
 188             }
 189             catch( NoSuchElementException ex )
 190             {
 191                 System.out.println( "Couldn't find chart with name " + sChartName + ": " + ex );
 192             }
 193             catch( Exception ex )
 194             {}
 195         }
 196
 197         return aResult;
 198     }
 199
 200
 201
 202     /** Fill a rectangular range with random numbers.
 203         The first column has increasing values
 204      */
 205     public XCellRange insertRandomRange( int nColumnCount, int nRowCount )
 206     {
 207         XCellRange aRange = null;
 208
 209         // get the sheet to insert the chart
 210         try
 211         {
 212             XSpreadsheet aSheet = getDataSheet();
 213             XCellRange aSheetRange = UnoRuntime.queryInterface( XCellRange.class, aSheet );
 214
 215             aRange = aSheetRange.getCellRangeByPosition(
 216                 0, 0,
 217                 nColumnCount - 1, nRowCount - 1 );
 218
 219             int nCol, nRow;
 220             double fBase  = 0.0;
 221             double fRange = 10.0;
 222             double fValue;
 223             Random aGenerator = new Random();
 224
 225
 226             for( nCol = 0; nCol < nColumnCount; nCol++ )
 227             {
 228                 if( 0 == nCol )
 229                 {
 230                     (aSheet.getCellByPosition( nCol, 0 )).setFormula( "X" );
 231                 }
 232                 else
 233                 {
 234                     (aSheet.getCellByPosition( nCol, 0 )).setFormula( "Random " + nCol );
 235                 }
 236
 237                 for( nRow = 1; nRow < nRowCount; nRow++ )
 238                 {
 239                     if( 0 == nCol )
 240                     {
 241                         // x values: ascending numbers
 242                         fValue = nRow + aGenerator.nextDouble();
 243                     }
 244                     else
 245                     {
 246                         fValue = fBase + ( aGenerator.nextGaussian() * fRange );
 247                     }
 248
 249                     // put value into cell
 250
 251                     // note: getCellByPosition is a method at ...table.XCellRange which
 252                     //       the XSpreadsheet inherits via ...sheet.XSheetCellRange
 253                     (aSheet.getCellByPosition( nCol, nRow )).setValue( fValue );
 254                 }
 255             }
 256
 257         }
 258         catch( Exception ex )
 259         {
 260             System.out.println( "Sheet not found" + ex );
 261         }
 262
 263         return aRange;
 264     }
 265
 266
 267
 268     public XCellRange insertFormulaRange( int nColumnCount, int nRowCount )
 269     {
 270         XCellRange aRange = null;
 271
 272         // get the sheet to insert the chart
 273         try
 274         {
 275             XSpreadsheet aSheet = getDataSheet();
 276             XCellRange aSheetRange = UnoRuntime.queryInterface( XCellRange.class, aSheet );
 277
 278             aRange = aSheetRange.getCellRangeByPosition(
 279                 0, 0,
 280                 nColumnCount - 1, nRowCount - 1 );
 281
 282             int nCol, nRow;
 283             double fValue;
 284             double fFactor = 2.0 * Math.PI / (nRowCount - 1);
 285             String aFormula;
 286
 287             // set variable factor for cos formula
 288             int nFactorCol = nColumnCount + 2;
 289             (aSheet.getCellByPosition( nFactorCol - 1, 0 )).setValue( 0.2 );
 290
 291             XText xCellText = UnoRuntime.queryInterface( XText.class, aSheet.getCellByPosition( nFactorCol - 1, 1 ) );
 292             xCellText.setString( "Change the factor above and\nwatch the changes in the chart" );
 293
 294             for( nCol = 0; nCol < nColumnCount; nCol++ )
 295             {
 296                 for( nRow = 0; nRow < nRowCount; nRow++ )
 297                 {
 298                     if( 0 == nCol )
 299                     {
 300                         // x values: ascending numbers
 301                         fValue = nRow * fFactor;
 302                         (aSheet.getCellByPosition( nCol, nRow )).setValue( fValue );
 303                     }
 304                     else
 305                     {
 306                         aFormula = "=";
 307                         if( nCol % 2 == 0 )
 308                             aFormula += "SIN";
 309                         else
 310                             aFormula += "COS";
 311                         aFormula += "(INDIRECT(ADDRESS(" + (nRow + 1) + ";1)))+RAND()*INDIRECT(ADDRESS(1;" + nFactorCol + "))";
 312                         (aSheet.getCellByPosition( nCol, nRow )).setFormula( aFormula );
 313                     }
 314                 }
 315             }
 316
 317         }
 318         catch( Exception ex )
 319         {
 320             System.out.println( "Sheet not found" + ex );
 321         }
 322
 323         return aRange;
 324     }
 325
 326
 327
 328     /** Bring the sheet containing charts visually to the foreground
 329      */
 330     public void raiseChartSheet()
 331     {
 332         UnoRuntime.queryInterface(
 333             XSpreadsheetView.class,
 334             UnoRuntime.queryInterface(
 335                 XModel.class,
 336                 maSpreadSheetDoc ).getCurrentController()).setActiveSheet( getChartSheet() );
 337     }
 338
 339
 340     // __________ private members and methods __________
 341
 342     private static final String  msDataSheetName  = "Data";
 343     private static final String  msChartSheetName = "Chart";
 344
 345     private final XSpreadsheetDocument   maSpreadSheetDoc;
 346
 347
 348
 349
 350     /** create two sheets, one for data and one for charts in the document
 351      */
 352     private void initSpreadSheet()
 353     {
 354         if( maSpreadSheetDoc != null )
 355         {
 356             XSpreadsheets  aSheets    = maSpreadSheetDoc.getSheets();
 357             XNameContainer aSheetsNC  = UnoRuntime.queryInterface(
 358                 XNameContainer.class, aSheets );
 359             XIndexAccess   aSheetsIA  = UnoRuntime.queryInterface(
 360                 XIndexAccess.class, aSheets );
 361
 362             if( aSheets   != null &&
 363                 aSheetsNC != null &&
 364                 aSheetsIA != null )
 365             {
 366                 try
 367                 {
 368                     // remove all sheets except one
 369                     for( int i = aSheetsIA.getCount() - 1; i > 0; i-- )
 370                     {
 371                         aSheetsNC.removeByName(
 372                             UnoRuntime.queryInterface(
 373                                 XNamed.class, aSheetsIA.getByIndex( i ) ).getName() );
 374                     }
 375
 376                     XNamed aFirstSheet = UnoRuntime.queryInterface(
 377                         XNamed.class,
 378                         aSheetsIA.getByIndex( 0 ));
 379
 380                     // first sheet becomes data sheet
 381                     aFirstSheet.setName( msDataSheetName );
 382
 383                     // second sheet becomes chart sheet
 384                     aSheets.insertNewByName( msChartSheetName, (short)1 );
 385                 }
 386                 catch( Exception ex )
 387                 {
 388                     System.out.println( "Couldn't initialize Spreadsheet Document: " + ex );
 389                 }
 390             }
 391         }
 392     }
 393 }