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