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