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