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