odk/examples/java/Spreadsheet/SCalc.java

   1 /*************************************************************************
   2  *
   3  *  $RCSfile: SCalc.java,v $
   4  *
   5  *  $Revision: 1.5 $
   6  *
   7  *  last change: $Author: rt $ $Date: 2005-01-31 17:16:10 $
   8  *
   9  *  The Contents of this file are made available subject to the terms of
  10  *  the BSD license.
  11  *
  12  *  Copyright (c) 2003 by Sun Microsystems, Inc.
  13  *  All rights reserved.
  14  *
  15  *  Redistribution and use in source and binary forms, with or without
  16  *  modification, are permitted provided that the following conditions
  17  *  are met:
  18  *  1. Redistributions of source code must retain the above copyright
  19  *     notice, this list of conditions and the following disclaimer.
  20  *  2. Redistributions in binary form must reproduce the above copyright
  21  *     notice, this list of conditions and the following disclaimer in the
  22  *     documentation and/or other materials provided with the distribution.
  23  *  3. Neither the name of Sun Microsystems, Inc. nor the names of its
  24  *     contributors may be used to endorse or promote products derived
  25  *     from this software without specific prior written permission.
  26  *
  27  *  THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
  28  *  "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
  29  *  LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS
  30  *  FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE
  31  *  COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,
  32  *  INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
  33  *  BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS
  34  *  OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
  35  *  ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR
  36  *  TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE
  37  *  USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
  38  *
  39  *************************************************************************/
  40
  41 //***************************************************************************
  42 // comment: Step 1: get the remote component context from the office
  43 //          Step 2: open an empty calc document
  44 //          Step 3: create cell styles
  45 //          Step 4: get the sheet an insert some data
  46 //          Step 5: apply the created cell syles
  47 //          Step 6: insert a 3D Chart
  48 //***************************************************************************
  49
  50 import com.sun.star.awt.Rectangle;
  51
  52 import com.sun.star.beans.PropertyValue;
  53 import com.sun.star.beans.XPropertySet;
  54
  55 import com.sun.star.chart.XDiagram;
  56 import com.sun.star.chart.XChartDocument;
  57
  58 import com.sun.star.container.XIndexAccess;
  59 import com.sun.star.container.XNameAccess;
  60 import com.sun.star.container.XNameContainer;
  61
  62 import com.sun.star.document.XEmbeddedObjectSupplier;
  63
  64 import com.sun.star.frame.XDesktop;
  65 import com.sun.star.frame.XComponentLoader;
  66
  67 import com.sun.star.lang.XComponent;
  68 import com.sun.star.lang.XMultiServiceFactory;
  69 import com.sun.star.lang.XMultiComponentFactory;
  70
  71 import com.sun.star.uno.UnoRuntime;
  72 import com.sun.star.uno.XInterface;
  73 import com.sun.star.uno.XComponentContext;
  74
  75 import com.sun.star.sheet.XCellRangeAddressable;
  76 import com.sun.star.sheet.XSpreadsheet;
  77 import com.sun.star.sheet.XSpreadsheets;
  78 import com.sun.star.sheet.XSpreadsheetDocument;
  79
  80 import com.sun.star.style.XStyleFamiliesSupplier;
  81
  82 import com.sun.star.table.CellRangeAddress;
  83 import com.sun.star.table.XCell;
  84 import com.sun.star.table.XCellRange;
  85 import com.sun.star.table.XTableChart;
  86 import com.sun.star.table.XTableCharts;
  87 import com.sun.star.table.XTableChartsSupplier;
  88
  89
  90 public class SCalc  {
  91
  92     public static void main(String args[]) {
  93
  94         //oooooooooooooooooooooooooooStep 1oooooooooooooooooooooooooooooooooooooooooo
  95         // call UNO bootstrap method and get the remote component context form
  96         // the a running office (office will be started if necessary)
  97         //***************************************************************************
  98         XComponentContext xContext = null;
  99
 100         // get the remote office component context
 101         try {
 102             xContext = com.sun.star.comp.helper.Bootstrap.bootstrap();
 103             System.out.println("Connected to a running office ...");
 104         } catch( Exception e) {
 105             e.printStackTrace(System.err);
 106             System.exit(1);
 107         }
 108
 109         //oooooooooooooooooooooooooooStep 2oooooooooooooooooooooooooooooooooooooooooo
 110         // open an empty document. In this case it's a calc document.
 111         // For this purpose an instance of com.sun.star.frame.Desktop
 112         // is created. The desktop provides the XComponentLoader interface,
 113         // which is used to open the document via loadComponentFromURL
 114         //***************************************************************************
 115
 116         //Open document
 117
 118         //Calc
 119         XSpreadsheetDocument myDoc = null;
 120 //        XCell oCell = null;
 121
 122         System.out.println("Opening an empty Calc document");
 123         myDoc = openCalc(xContext);
 124
 125         //***************************************************************************
 126
 127
 128         //oooooooooooooooooooooooooooStep 3oooooooooooooooooooooooooooooooooooooooooo
 129         // create cell styles.
 130         // For this purpose get the StyleFamiliesSupplier and the the familiy
 131         // CellStyle. Create an instance of com.sun.star.style.CellStyle and
 132         // add it to the family. Now change some properties
 133         //***************************************************************************
 134
 135         try {
 136             XStyleFamiliesSupplier xSFS = (XStyleFamiliesSupplier)
 137                 UnoRuntime.queryInterface(XStyleFamiliesSupplier.class, myDoc);
 138             XNameAccess xSF = (XNameAccess) xSFS.getStyleFamilies();
 139             XNameAccess xCS = (XNameAccess) UnoRuntime.queryInterface(
 140                 XNameAccess.class, xSF.getByName("CellStyles"));
 141             XMultiServiceFactory oDocMSF = (XMultiServiceFactory)
 142                 UnoRuntime.queryInterface(XMultiServiceFactory.class, myDoc );
 143             XNameContainer oStyleFamilyNameContainer = (XNameContainer)
 144                 UnoRuntime.queryInterface(
 145                 XNameContainer.class, xCS);
 146             XInterface oInt1 = (XInterface) oDocMSF.createInstance(
 147                 "com.sun.star.style.CellStyle");
 148             oStyleFamilyNameContainer.insertByName("My Style", oInt1);
 149             XPropertySet oCPS1 = (XPropertySet)UnoRuntime.queryInterface(
 150                 XPropertySet.class, oInt1 );
 151             oCPS1.setPropertyValue("IsCellBackgroundTransparent", new Boolean(false));
 152             oCPS1.setPropertyValue("CellBackColor",new Integer(6710932));
 153             oCPS1.setPropertyValue("CharColor",new Integer(16777215));
 154             XInterface oInt2 = (XInterface) oDocMSF.createInstance(
 155                 "com.sun.star.style.CellStyle");
 156             oStyleFamilyNameContainer.insertByName("My Style2", oInt2);
 157             XPropertySet oCPS2 = (XPropertySet)UnoRuntime.queryInterface(
 158                 XPropertySet.class, oInt2 );
 159             oCPS2.setPropertyValue("IsCellBackgroundTransparent", new Boolean(false));
 160             oCPS2.setPropertyValue("CellBackColor",new Integer(13421823));
 161         } catch (Exception e) {
 162             e.printStackTrace(System.err);
 163         }
 164
 165         //***************************************************************************
 166
 167         //oooooooooooooooooooooooooooStep 4oooooooooooooooooooooooooooooooooooooooooo
 168         // get the sheet an insert some data.
 169         // Get the sheets from the document and then the first from this container.
 170         // Now some data can be inserted. For this purpose get a Cell via
 171         // getCellByPosition and insert into this cell via setValue() (for floats)
 172         // or setFormula() for formulas and Strings
 173         //***************************************************************************
 174
 175
 176         XSpreadsheet xSheet=null;
 177
 178         try {
 179             System.out.println("Getting spreadsheet") ;
 180             XSpreadsheets xSheets = myDoc.getSheets() ;
 181             XIndexAccess oIndexSheets = (XIndexAccess) UnoRuntime.queryInterface(
 182                 XIndexAccess.class, xSheets);
 183             xSheet = (XSpreadsheet) UnoRuntime.queryInterface(
 184                 XSpreadsheet.class, oIndexSheets.getByIndex(0));
 185
 186         } catch (Exception e) {
 187             System.out.println("Couldn't get Sheet " +e);
 188             e.printStackTrace(System.err);
 189         }
 190
 191
 192
 193         System.out.println("Creating the Header") ;
 194
 195         insertIntoCell(1,0,"JAN",xSheet,"");
 196         insertIntoCell(2,0,"FEB",xSheet,"");
 197         insertIntoCell(3,0,"MAR",xSheet,"");
 198         insertIntoCell(4,0,"APR",xSheet,"");
 199         insertIntoCell(5,0,"MAI",xSheet,"");
 200         insertIntoCell(6,0,"JUN",xSheet,"");
 201         insertIntoCell(7,0,"JUL",xSheet,"");
 202         insertIntoCell(8,0,"AUG",xSheet,"");
 203         insertIntoCell(9,0,"SEP",xSheet,"");
 204         insertIntoCell(10,0,"OCT",xSheet,"");
 205         insertIntoCell(11,0,"NOV",xSheet,"");
 206         insertIntoCell(12,0,"DEC",xSheet,"");
 207         insertIntoCell(13,0,"SUM",xSheet,"");
 208
 209
 210         System.out.println("Fill the lines");
 211
 212         insertIntoCell(0,1,"Smith",xSheet,"");
 213         insertIntoCell(1,1,"42",xSheet,"V");
 214         insertIntoCell(2,1,"58.9",xSheet,"V");
 215         insertIntoCell(3,1,"-66.5",xSheet,"V");
 216         insertIntoCell(4,1,"43.4",xSheet,"V");
 217         insertIntoCell(5,1,"44.5",xSheet,"V");
 218         insertIntoCell(6,1,"45.3",xSheet,"V");
 219         insertIntoCell(7,1,"-67.3",xSheet,"V");
 220         insertIntoCell(8,1,"30.5",xSheet,"V");
 221         insertIntoCell(9,1,"23.2",xSheet,"V");
 222         insertIntoCell(10,1,"-97.3",xSheet,"V");
 223         insertIntoCell(11,1,"22.4",xSheet,"V");
 224         insertIntoCell(12,1,"23.5",xSheet,"V");
 225         insertIntoCell(13,1,"=SUM(B2:M2)",xSheet,"");
 226
 227
 228         insertIntoCell(0,2,"Jones",xSheet,"");
 229         insertIntoCell(1,2,"21",xSheet,"V");
 230         insertIntoCell(2,2,"40.9",xSheet,"V");
 231         insertIntoCell(3,2,"-57.5",xSheet,"V");
 232         insertIntoCell(4,2,"-23.4",xSheet,"V");
 233         insertIntoCell(5,2,"34.5",xSheet,"V");
 234         insertIntoCell(6,2,"59.3",xSheet,"V");
 235         insertIntoCell(7,2,"27.3",xSheet,"V");
 236         insertIntoCell(8,2,"-38.5",xSheet,"V");
 237         insertIntoCell(9,2,"43.2",xSheet,"V");
 238         insertIntoCell(10,2,"57.3",xSheet,"V");
 239         insertIntoCell(11,2,"25.4",xSheet,"V");
 240         insertIntoCell(12,2,"28.5",xSheet,"V");
 241         insertIntoCell(13,2,"=SUM(B3:M3)",xSheet,"");
 242
 243         insertIntoCell(0,3,"Brown",xSheet,"");
 244         insertIntoCell(1,3,"31.45",xSheet,"V");
 245         insertIntoCell(2,3,"-20.9",xSheet,"V");
 246         insertIntoCell(3,3,"-117.5",xSheet,"V");
 247         insertIntoCell(4,3,"23.4",xSheet,"V");
 248         insertIntoCell(5,3,"-114.5",xSheet,"V");
 249         insertIntoCell(6,3,"115.3",xSheet,"V");
 250         insertIntoCell(7,3,"-171.3",xSheet,"V");
 251         insertIntoCell(8,3,"89.5",xSheet,"V");
 252         insertIntoCell(9,3,"41.2",xSheet,"V");
 253         insertIntoCell(10,3,"71.3",xSheet,"V");
 254         insertIntoCell(11,3,"25.4",xSheet,"V");
 255         insertIntoCell(12,3,"38.5",xSheet,"V");
 256         insertIntoCell(13,3,"=SUM(A4:L4)",xSheet,"");
 257
 258         //***************************************************************************
 259
 260         //oooooooooooooooooooooooooooStep 5oooooooooooooooooooooooooooooooooooooooooo
 261         // apply the created cell style.
 262         // For this purpose get the PropertySet of the Cell and change the
 263         // property CellStyle to the appropriate value.
 264         //***************************************************************************
 265
 266         // change backcolor
 267         chgbColor( 1 , 0, 13, 0, "My Style", xSheet );
 268         chgbColor( 0 , 1, 0, 3, "My Style", xSheet );
 269         chgbColor( 1 , 1, 13, 3, "My Style2", xSheet );
 270
 271         //***************************************************************************
 272
 273         //oooooooooooooooooooooooooooStep 6oooooooooooooooooooooooooooooooooooooooooo
 274         // insert a 3D chart.
 275         // get the CellRange which holds the data for the chart and its RangeAddress
 276         // get the TableChartSupplier from the sheet and then the TableCharts from it.
 277         // add a new chart based on the data to the TableCharts.
 278         // get the ChartDocument, which provide the Diagramm. Change the properties
 279         // Dim3D (3 dimension) and String (the title) of the diagramm.
 280         //***************************************************************************
 281
 282         // insert a chart
 283
 284         Rectangle oRect = new Rectangle();
 285         oRect.X = 500;
 286         oRect.Y = 3000;
 287         oRect.Width = 25000;
 288         oRect.Height = 11000;
 289
 290         XCellRange oRange = (XCellRange)UnoRuntime.queryInterface(
 291             XCellRange.class, xSheet);
 292         XCellRange myRange = oRange.getCellRangeByName("A1:N4");
 293         XCellRangeAddressable oRangeAddr = (XCellRangeAddressable)
 294             UnoRuntime.queryInterface(XCellRangeAddressable.class, myRange);
 295         CellRangeAddress myAddr = oRangeAddr.getRangeAddress();
 296
 297         CellRangeAddress[] oAddr = new CellRangeAddress[1];
 298         oAddr[0] = myAddr;
 299         XTableChartsSupplier oSupp = (XTableChartsSupplier)UnoRuntime.queryInterface(
 300             XTableChartsSupplier.class, xSheet);
 301
 302         XTableChart oChart = null;
 303
 304         System.out.println("Insert Chart");
 305
 306         XTableCharts oCharts = oSupp.getCharts();
 307         oCharts.addNewByName("Example", oRect, oAddr, true, true);
 308
 309         // get the diagramm and Change some of the properties
 310
 311         try {
 312             oChart = (XTableChart) (UnoRuntime.queryInterface(
 313                 XTableChart.class, ((XNameAccess)UnoRuntime.queryInterface(
 314                             XNameAccess.class, oCharts)).getByName("Example")));
 315             XEmbeddedObjectSupplier oEOS = (XEmbeddedObjectSupplier)
 316                 UnoRuntime.queryInterface(XEmbeddedObjectSupplier.class, oChart);
 317             XInterface oInt = oEOS.getEmbeddedObject();
 318             XChartDocument xChart = (XChartDocument) UnoRuntime.queryInterface(
 319                 XChartDocument.class,oInt);
 320             XDiagram oDiag = (XDiagram) xChart.getDiagram();
 321             System.out.println("Change Diagramm to 3D");
 322             XPropertySet oCPS = (XPropertySet)UnoRuntime.queryInterface(
 323                 XPropertySet.class, oDiag );
 324             oCPS.setPropertyValue("Dim3D", new Boolean(true));
 325             System.out.println("Change the title");
 326             Thread.sleep(200);
 327             XPropertySet oTPS = (XPropertySet)UnoRuntime.queryInterface(
 328                 XPropertySet.class, xChart.getTitle() );
 329             oTPS.setPropertyValue("String","The new title");
 330             //oDiag.Dim3D();
 331         } catch (Exception e){
 332             System.err.println("Changin Properties failed "+e);
 333             e.printStackTrace(System.err);
 334         }
 335
 336         System.out.println("done");
 337         System.exit(0);
 338     }
 339
 340     public static XSpreadsheetDocument openCalc(XComponentContext xContext)
 341     {
 342         //define variables
 343         XMultiComponentFactory xMCF = null;
 344         XComponentLoader xCLoader;
 345         XSpreadsheetDocument xSpreadSheetDoc = null;
 346         XComponent xComp = null;
 347
 348         try {
 349             // get the servie manager rom the office
 350             xMCF = xContext.getServiceManager();
 351
 352             // create a new instance of the the desktop
 353             Object oDesktop = xMCF.createInstanceWithContext(
 354                 "com.sun.star.frame.Desktop", xContext );
 355
 356             // query the desktop object for the XComponentLoader
 357             xCLoader = ( XComponentLoader ) UnoRuntime.queryInterface(
 358                 XComponentLoader.class, oDesktop );
 359
 360             PropertyValue [] szEmptyArgs = new PropertyValue [0];
 361             String strDoc = "private:factory/scalc";
 362
 363             xComp = xCLoader.loadComponentFromURL(strDoc, "_blank", 0, szEmptyArgs );
 364             xSpreadSheetDoc = (XSpreadsheetDocument) UnoRuntime.queryInterface(
 365                 XSpreadsheetDocument.class, xComp);
 366
 367         } catch(Exception e){
 368             System.err.println(" Exception " + e);
 369             e.printStackTrace(System.err);
 370         }
 371
 372         return xSpreadSheetDoc;
 373     }
 374
 375
 376     public static void insertIntoCell(int CellX, int CellY, String theValue,
 377                                       XSpreadsheet TT1, String flag)
 378     {
 379         XCell xCell = null;
 380
 381         try {
 382             xCell = TT1.getCellByPosition(CellX, CellY);
 383         } catch (com.sun.star.lang.IndexOutOfBoundsException ex) {
 384             System.err.println("Could not get Cell");
 385             ex.printStackTrace(System.err);
 386         }
 387
 388         if (flag.equals("V")) {
 389             xCell.setValue((new Float(theValue)).floatValue());
 390         } else {
 391             xCell.setFormula(theValue);
 392         }
 393
 394     }
 395
 396     public static void chgbColor( int x1, int y1, int x2, int y2,
 397                                   String template, XSpreadsheet TT )
 398     {
 399         XCellRange xCR = null;
 400         try {
 401             xCR = TT.getCellRangeByPosition(x1,y1,x2,y2);
 402         } catch (com.sun.star.lang.IndexOutOfBoundsException ex) {
 403             System.err.println("Could not get CellRange");
 404             ex.printStackTrace(System.err);
 405         }
 406
 407         XPropertySet xCPS = (XPropertySet)UnoRuntime.queryInterface(
 408             XPropertySet.class, xCR );
 409
 410         try {
 411             xCPS.setPropertyValue("CellStyle", template);
 412         } catch (Exception e) {
 413             System.err.println("Can't change colors chgbColor" + e);
 414             e.printStackTrace(System.err);
 415         }
 416     }
 417
 418 }