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 main(String args[])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 family 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 openCalc(XComponentContext xContext)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 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 insertIntoCell(int CellX, int CellY, String theValue, XSpreadsheet TT1, String flag)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 chgbColor( int x1, int y1, int x2, int y2, String template, XSpreadsheet TT )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