odk/examples/java/Spreadsheet/SCalc.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
  37 // comment: Step 1: get the remote component context from the office
  38 //          Step 2: open an empty calc document
  39 //          Step 3: create cell styles
  40 //          Step 4: get the sheet an insert some data
  41 //          Step 5: apply the created cell styles
  42 //          Step 6: insert a 3D Chart
  43
  44
  45 import com.sun.star.awt.Rectangle;
  46
  47 import com.sun.star.beans.PropertyValue;
  48 import com.sun.star.beans.XPropertySet;
  49
  50 import com.sun.star.chart.XDiagram;
  51 import com.sun.star.chart.XChartDocument;
  52
  53 import com.sun.star.container.XIndexAccess;
  54 import com.sun.star.container.XNameAccess;
  55 import com.sun.star.container.XNameContainer;
  56
  57 import com.sun.star.document.XEmbeddedObjectSupplier;
  58
  59 import com.sun.star.frame.XComponentLoader;
  60
  61 import com.sun.star.lang.XComponent;
  62 import com.sun.star.lang.XMultiServiceFactory;
  63 import com.sun.star.lang.XMultiComponentFactory;
  64
  65 import com.sun.star.uno.UnoRuntime;
  66 import com.sun.star.uno.XInterface;
  67 import com.sun.star.uno.XComponentContext;
  68
  69 import com.sun.star.sheet.XCellRangeAddressable;
  70 import com.sun.star.sheet.XSpreadsheet;
  71 import com.sun.star.sheet.XSpreadsheets;
  72 import com.sun.star.sheet.XSpreadsheetDocument;
  73
  74 import com.sun.star.style.XStyleFamiliesSupplier;
  75
  76 import com.sun.star.table.CellRangeAddress;
  77 import com.sun.star.table.XCell;
  78 import com.sun.star.table.XCellRange;
  79 import com.sun.star.table.XTableChart;
  80 import com.sun.star.table.XTableCharts;
  81 import com.sun.star.table.XTableChartsSupplier;
  82
  83
  84 public class SCalc  {
  85
  86     public static void main(String args[]) {
  87
  88         //oooooooooooooooooooooooooooStep 1oooooooooooooooooooooooooooooooooooooooooo
  89         // call UNO bootstrap method and get the remote component context form
  90         // the a running office (office will be started if necessary)
  91
  92         XComponentContext xContext = null;
  93
  94         // get the remote office component context
  95         try {
  96             xContext = com.sun.star.comp.helper.Bootstrap.bootstrap();
  97             System.out.println("Connected to a running office ...");
  98         } catch( Exception e) {
  99             e.printStackTrace(System.err);
 100             System.exit(1);
 101         }
 102
 103         //oooooooooooooooooooooooooooStep 2oooooooooooooooooooooooooooooooooooooooooo
 104         // open an empty document. In this case it's a calc document.
 105         // For this purpose an instance of com.sun.star.frame.Desktop
 106         // is created. The desktop provides the XComponentLoader interface,
 107         // which is used to open the document via loadComponentFromURL
 108
 109
 110         //Open document
 111
 112         //Calc
 113         XSpreadsheetDocument myDoc = null;
 114 //        XCell oCell = null;
 115
 116         System.out.println("Opening an empty Calc document");
 117         myDoc = openCalc(xContext);
 118
 119
 120
 121
 122         //oooooooooooooooooooooooooooStep 3oooooooooooooooooooooooooooooooooooooooooo
 123         // create cell styles.
 124         // For this purpose get the StyleFamiliesSupplier and the family
 125         // CellStyle. Create an instance of com.sun.star.style.CellStyle and
 126         // add it to the family. Now change some properties
 127
 128
 129         try {
 130             XStyleFamiliesSupplier xSFS = UnoRuntime.queryInterface(XStyleFamiliesSupplier.class, myDoc);
 131             XNameAccess xSF = xSFS.getStyleFamilies();
 132             XNameAccess xCS = UnoRuntime.queryInterface(
 133                 XNameAccess.class, xSF.getByName("CellStyles"));
 134             XMultiServiceFactory oDocMSF = UnoRuntime.queryInterface(XMultiServiceFactory.class, myDoc );
 135             XNameContainer oStyleFamilyNameContainer = UnoRuntime.queryInterface(
 136             XNameContainer.class, xCS);
 137             XInterface oInt1 = (XInterface) oDocMSF.createInstance(
 138                 "com.sun.star.style.CellStyle");
 139             oStyleFamilyNameContainer.insertByName("My Style", oInt1);
 140             XPropertySet oCPS1 = UnoRuntime.queryInterface(
 141                 XPropertySet.class, oInt1 );
 142             oCPS1.setPropertyValue("IsCellBackgroundTransparent", Boolean.FALSE);
 143             oCPS1.setPropertyValue("CellBackColor",Integer.valueOf(6710932));
 144             oCPS1.setPropertyValue("CharColor",Integer.valueOf(16777215));
 145             XInterface oInt2 = (XInterface) oDocMSF.createInstance(
 146                 "com.sun.star.style.CellStyle");
 147             oStyleFamilyNameContainer.insertByName("My Style2", oInt2);
 148             XPropertySet oCPS2 = UnoRuntime.queryInterface(
 149                 XPropertySet.class, oInt2 );
 150             oCPS2.setPropertyValue("IsCellBackgroundTransparent", Boolean.FALSE);
 151             oCPS2.setPropertyValue("CellBackColor",Integer.valueOf(13421823));
 152         } catch (Exception e) {
 153             e.printStackTrace(System.err);
 154         }
 155
 156
 157
 158         //oooooooooooooooooooooooooooStep 4oooooooooooooooooooooooooooooooooooooooooo
 159         // get the sheet an insert some data.
 160         // Get the sheets from the document and then the first from this container.
 161         // Now some data can be inserted. For this purpose get a Cell via
 162         // getCellByPosition and insert into this cell via setValue() (for floats)
 163         // or setFormula() for formulas and Strings
 164
 165
 166
 167         XSpreadsheet xSheet=null;
 168
 169         try {
 170             System.out.println("Getting spreadsheet") ;
 171             XSpreadsheets xSheets = myDoc.getSheets() ;
 172             XIndexAccess oIndexSheets = UnoRuntime.queryInterface(
 173                 XIndexAccess.class, xSheets);
 174             xSheet = UnoRuntime.queryInterface(
 175                 XSpreadsheet.class, oIndexSheets.getByIndex(0));
 176
 177         } catch (Exception e) {
 178             System.out.println("Couldn't get Sheet " +e);
 179             e.printStackTrace(System.err);
 180         }
 181
 182
 183
 184         System.out.println("Creating the Header") ;
 185
 186         insertIntoCell(1,0,"JAN",xSheet,"");
 187         insertIntoCell(2,0,"FEB",xSheet,"");
 188         insertIntoCell(3,0,"MAR",xSheet,"");
 189         insertIntoCell(4,0,"APR",xSheet,"");
 190         insertIntoCell(5,0,"MAI",xSheet,"");
 191         insertIntoCell(6,0,"JUN",xSheet,"");
 192         insertIntoCell(7,0,"JUL",xSheet,"");
 193         insertIntoCell(8,0,"AUG",xSheet,"");
 194         insertIntoCell(9,0,"SEP",xSheet,"");
 195         insertIntoCell(10,0,"OCT",xSheet,"");
 196         insertIntoCell(11,0,"NOV",xSheet,"");
 197         insertIntoCell(12,0,"DEC",xSheet,"");
 198         insertIntoCell(13,0,"SUM",xSheet,"");
 199
 200
 201         System.out.println("Fill the lines");
 202
 203         insertIntoCell(0,1,"Smith",xSheet,"");
 204         insertIntoCell(1,1,"42",xSheet,"V");
 205         insertIntoCell(2,1,"58.9",xSheet,"V");
 206         insertIntoCell(3,1,"-66.5",xSheet,"V");
 207         insertIntoCell(4,1,"43.4",xSheet,"V");
 208         insertIntoCell(5,1,"44.5",xSheet,"V");
 209         insertIntoCell(6,1,"45.3",xSheet,"V");
 210         insertIntoCell(7,1,"-67.3",xSheet,"V");
 211         insertIntoCell(8,1,"30.5",xSheet,"V");
 212         insertIntoCell(9,1,"23.2",xSheet,"V");
 213         insertIntoCell(10,1,"-97.3",xSheet,"V");
 214         insertIntoCell(11,1,"22.4",xSheet,"V");
 215         insertIntoCell(12,1,"23.5",xSheet,"V");
 216         insertIntoCell(13,1,"=SUM(B2:M2)",xSheet,"");
 217
 218
 219         insertIntoCell(0,2,"Jones",xSheet,"");
 220         insertIntoCell(1,2,"21",xSheet,"V");
 221         insertIntoCell(2,2,"40.9",xSheet,"V");
 222         insertIntoCell(3,2,"-57.5",xSheet,"V");
 223         insertIntoCell(4,2,"-23.4",xSheet,"V");
 224         insertIntoCell(5,2,"34.5",xSheet,"V");
 225         insertIntoCell(6,2,"59.3",xSheet,"V");
 226         insertIntoCell(7,2,"27.3",xSheet,"V");
 227         insertIntoCell(8,2,"-38.5",xSheet,"V");
 228         insertIntoCell(9,2,"43.2",xSheet,"V");
 229         insertIntoCell(10,2,"57.3",xSheet,"V");
 230         insertIntoCell(11,2,"25.4",xSheet,"V");
 231         insertIntoCell(12,2,"28.5",xSheet,"V");
 232         insertIntoCell(13,2,"=SUM(B3:M3)",xSheet,"");
 233
 234         insertIntoCell(0,3,"Brown",xSheet,"");
 235         insertIntoCell(1,3,"31.45",xSheet,"V");
 236         insertIntoCell(2,3,"-20.9",xSheet,"V");
 237         insertIntoCell(3,3,"-117.5",xSheet,"V");
 238         insertIntoCell(4,3,"23.4",xSheet,"V");
 239         insertIntoCell(5,3,"-114.5",xSheet,"V");
 240         insertIntoCell(6,3,"115.3",xSheet,"V");
 241         insertIntoCell(7,3,"-171.3",xSheet,"V");
 242         insertIntoCell(8,3,"89.5",xSheet,"V");
 243         insertIntoCell(9,3,"41.2",xSheet,"V");
 244         insertIntoCell(10,3,"71.3",xSheet,"V");
 245         insertIntoCell(11,3,"25.4",xSheet,"V");
 246         insertIntoCell(12,3,"38.5",xSheet,"V");
 247         insertIntoCell(13,3,"=SUM(A4:L4)",xSheet,"");
 248
 249
 250
 251         //oooooooooooooooooooooooooooStep 5oooooooooooooooooooooooooooooooooooooooooo
 252         // apply the created cell style.
 253         // For this purpose get the PropertySet of the Cell and change the
 254         // property CellStyle to the appropriate value.
 255
 256
 257         // change backcolor
 258         chgbColor( 1 , 0, 13, 0, "My Style", xSheet );
 259         chgbColor( 0 , 1, 0, 3, "My Style", xSheet );
 260         chgbColor( 1 , 1, 13, 3, "My Style2", xSheet );
 261
 262
 263
 264         //oooooooooooooooooooooooooooStep 6oooooooooooooooooooooooooooooooooooooooooo
 265         // insert a 3D chart.
 266         // get the CellRange which holds the data for the chart and its RangeAddress
 267         // get the TableChartSupplier from the sheet and then the TableCharts from it.
 268         // add a new chart based on the data to the TableCharts.
 269         // get the ChartDocument, which provide the Diagram. Change the properties
 270         // Dim3D (3 dimension) and String (the title) of the diagram.
 271
 272
 273         // insert a chart
 274
 275         Rectangle oRect = new Rectangle();
 276         oRect.X = 500;
 277         oRect.Y = 3000;
 278         oRect.Width = 25000;
 279         oRect.Height = 11000;
 280
 281         XCellRange oRange = UnoRuntime.queryInterface(
 282             XCellRange.class, xSheet);
 283         XCellRange myRange = oRange.getCellRangeByName("A1:N4");
 284         XCellRangeAddressable oRangeAddr = UnoRuntime.queryInterface(XCellRangeAddressable.class, myRange);
 285         CellRangeAddress myAddr = oRangeAddr.getRangeAddress();
 286
 287         CellRangeAddress[] oAddr = new CellRangeAddress[1];
 288         oAddr[0] = myAddr;
 289         XTableChartsSupplier oSupp = UnoRuntime.queryInterface(
 290             XTableChartsSupplier.class, xSheet);
 291
 292         XTableChart oChart = null;
 293
 294         System.out.println("Insert Chart");
 295
 296         XTableCharts oCharts = oSupp.getCharts();
 297         oCharts.addNewByName("Example", oRect, oAddr, true, true);
 298
 299         // get the diagram and change some of the properties
 300
 301         try {
 302             oChart = (UnoRuntime.queryInterface(
 303                 XTableChart.class, UnoRuntime.queryInterface(
 304                             XNameAccess.class, oCharts).getByName("Example")));
 305             XEmbeddedObjectSupplier oEOS = UnoRuntime.queryInterface(XEmbeddedObjectSupplier.class, oChart);
 306             XInterface oInt = oEOS.getEmbeddedObject();
 307             XChartDocument xChart = UnoRuntime.queryInterface(
 308                 XChartDocument.class,oInt);
 309             XDiagram oDiag = xChart.getDiagram();
 310             System.out.println("Change Diagram to 3D");
 311             XPropertySet oCPS = UnoRuntime.queryInterface(
 312                 XPropertySet.class, oDiag );
 313             oCPS.setPropertyValue("Dim3D", Boolean.TRUE);
 314             System.out.println("Change the title");
 315             Thread.sleep(200);
 316             XPropertySet oTPS = UnoRuntime.queryInterface(
 317                 XPropertySet.class, xChart.getTitle() );
 318             oTPS.setPropertyValue("String","The new title");
 319         } catch (Exception e){
 320             System.err.println("Changing Properties failed "+e);
 321             e.printStackTrace(System.err);
 322         }
 323
 324         System.out.println("done");
 325         System.exit(0);
 326     }
 327
 328     public static XSpreadsheetDocument openCalc(XComponentContext xContext)
 329     {
 330         //define variables
 331         XMultiComponentFactory xMCF = null;
 332         XComponentLoader xCLoader;
 333         XSpreadsheetDocument xSpreadSheetDoc = null;
 334         XComponent xComp = null;
 335
 336         try {
 337             // get the service manager from the office
 338             xMCF = xContext.getServiceManager();
 339
 340             // create a new instance of the desktop
 341             Object oDesktop = xMCF.createInstanceWithContext(
 342                 "com.sun.star.frame.Desktop", xContext );
 343
 344             // query the desktop object for the XComponentLoader
 345             xCLoader = UnoRuntime.queryInterface(
 346                 XComponentLoader.class, oDesktop );
 347
 348             PropertyValue [] szEmptyArgs = new PropertyValue [0];
 349             String strDoc = "private:factory/scalc";
 350
 351             xComp = xCLoader.loadComponentFromURL(strDoc, "_blank", 0, szEmptyArgs );
 352             xSpreadSheetDoc = UnoRuntime.queryInterface(
 353                 XSpreadsheetDocument.class, xComp);
 354
 355         } catch(Exception e){
 356             System.err.println(" Exception " + e);
 357             e.printStackTrace(System.err);
 358         }
 359
 360         return xSpreadSheetDoc;
 361     }
 362
 363
 364     public static void insertIntoCell(int CellX, int CellY, String theValue,
 365                                       XSpreadsheet TT1, String flag)
 366     {
 367         XCell xCell = null;
 368
 369         try {
 370             xCell = TT1.getCellByPosition(CellX, CellY);
 371         } catch (com.sun.star.lang.IndexOutOfBoundsException ex) {
 372             System.err.println("Could not get Cell");
 373             ex.printStackTrace(System.err);
 374         }
 375
 376         if (flag.equals("V")) {
 377             xCell.setValue((Float.valueOf(theValue)).floatValue());
 378         } else {
 379             xCell.setFormula(theValue);
 380         }
 381
 382     }
 383
 384     public static void chgbColor( int x1, int y1, int x2, int y2,
 385                                   String template, XSpreadsheet TT )
 386     {
 387         XCellRange xCR = null;
 388         try {
 389             xCR = TT.getCellRangeByPosition(x1,y1,x2,y2);
 390         } catch (com.sun.star.lang.IndexOutOfBoundsException ex) {
 391             System.err.println("Could not get CellRange");
 392             ex.printStackTrace(System.err);
 393         }
 394
 395         XPropertySet xCPS = UnoRuntime.queryInterface(
 396             XPropertySet.class, xCR );
 397
 398         try {
 399             xCPS.setPropertyValue("CellStyle", template);
 400         } catch (Exception e) {
 401             System.err.println("Can't change colors chgbColor" + e);
 402             e.printStackTrace(System.err);
 403         }
 404     }
 405
 406 }
 407
 408 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */