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 import com.sun.star.comp.servicemanager.ServiceManager; 36 import com.sun.star.bridge.XUnoUrlResolver; 37 import com.sun.star.uno.XNamingService; 38 import com.sun.star.frame.XDesktop; 39 import com.sun.star.frame.XComponentLoader; 40 import com.sun.star.lang.XMultiServiceFactory; 41 import com.sun.star.lang.XComponent; 42 43 import com.sun.star.uno.UnoRuntime; 44 import com.sun.star.uno.RuntimeException; 45 46 // __________ implementation ____________________________________ 47 48 /** This is a helper class for the spreadsheet and table samples. 49 It connects to a running office and creates a spreadsheet document. 50 Additionally it contains various helper functions. 51 */ 52 public class SpreadsheetDocHelper 53 { 54 55 // __ private members ___________________________________________ 56 57 private final String msDataSheetName = "Data"; 58 59 private com.sun.star.uno.XComponentContext mxRemoteContext; 60 private com.sun.star.lang.XMultiComponentFactory mxRemoteServiceManager; 61 // private com.sun.star.lang.XMultiServiceFactory mxMSFactory; 62 private com.sun.star.sheet.XSpreadsheetDocument mxDocument; 63 64 // ________________________________________________________________ 65 66 public SpreadsheetDocHelper( String[] args ) 67 { 68 // Connect to a running office and get the service manager 69 connect(); 70 71 // Create a new spreadsheet document 72 try 73 { 74 mxDocument = initDocument(); 75 } 76 catch (Exception ex) 77 { 78 System.err.println( "Couldn't create document: " + ex ); 79 System.err.println( "Error: Couldn't create Document\nException Message = " 80 + ex.getMessage()); 81 ex.printStackTrace(); 82 System.exit( 1 ); 83 } 84 } 85 86 // __ helper methods ____________________________________________ 87 88 /** Returns the service manager of the connected office. 89 @return XMultiComponentFactory interface of the service manager. */ 90 public com.sun.star.lang.XMultiComponentFactory getServiceManager() 91 { 92 return mxRemoteServiceManager; 93 } 94 95 /** Returns the component context of the connected office 96 @return XComponentContext interface of the context. */ 97 public com.sun.star.uno.XComponentContext getContext() 98 { 99 return mxRemoteContext; 100 } 101 102 /** Returns the whole spreadsheet document. 103 @return XSpreadsheetDocument interface of the document. */ 104 public com.sun.star.sheet.XSpreadsheetDocument getDocument() 105 { 106 return mxDocument; 107 } 108 109 /** Returns the spreadsheet with the specified index (0-based). 110 @param nIndex The index of the sheet. 111 @return XSpreadsheet interface of the sheet. */ 112 public com.sun.star.sheet.XSpreadsheet getSpreadsheet( int nIndex ) 113 { 114 // Collection of sheets 115 com.sun.star.sheet.XSpreadsheets xSheets = mxDocument.getSheets(); 116 com.sun.star.sheet.XSpreadsheet xSheet = null; 117 try 118 { 119 com.sun.star.container.XIndexAccess xSheetsIA = 120 (com.sun.star.container.XIndexAccess)UnoRuntime.queryInterface( 121 com.sun.star.container.XIndexAccess.class, xSheets ); 122 xSheet = (com.sun.star.sheet.XSpreadsheet) UnoRuntime.queryInterface( 123 com.sun.star.sheet.XSpreadsheet.class, xSheetsIA.getByIndex(nIndex)); 124 } 125 catch (Exception ex) 126 { 127 System.err.println( "Error: caught exception in getSpreadsheet()!\nException Message = " 128 + ex.getMessage()); 129 ex.printStackTrace(); 130 } 131 return xSheet; 132 } 133 134 /** Inserts a new empty spreadsheet with the specified name. 135 @param aName The name of the new sheet. 136 @param nIndex The insertion index. 137 @return The XSpreadsheet interface of the new sheet. */ 138 public com.sun.star.sheet.XSpreadsheet insertSpreadsheet( 139 String aName, short nIndex ) 140 { 141 // Collection of sheets 142 com.sun.star.sheet.XSpreadsheets xSheets = mxDocument.getSheets(); 143 com.sun.star.sheet.XSpreadsheet xSheet = null; 144 try 145 { 146 xSheets.insertNewByName( aName, nIndex ); 147 xSheet = (com.sun.star.sheet.XSpreadsheet) 148 UnoRuntime.queryInterface(com.sun.star.sheet.XSpreadsheet.class, 149 xSheets.getByName( aName )); 150 } 151 catch (Exception ex) 152 { 153 System.err.println( "Error: caught exception in insertSpreadsheet()!\nException Message = " 154 + ex.getMessage()); 155 ex.printStackTrace(); 156 } 157 return xSheet; 158 } 159 160 // ________________________________________________________________ 161 // Methods to fill values into cells. 162 163 /** Writes a double value into a spreadsheet. 164 @param xSheet The XSpreadsheet interface of the spreadsheet. 165 @param aCellName The address of the cell (or a named range). 166 @param fValue The value to write into the cell. */ 167 public void setValue( 168 com.sun.star.sheet.XSpreadsheet xSheet, 169 String aCellName, 170 double fValue ) throws RuntimeException, Exception 171 { 172 xSheet.getCellRangeByName( aCellName ).getCellByPosition( 0, 0 ).setValue( fValue ); 173 } 174 175 /** Writes a formula into a spreadsheet. 176 @param xSheet The XSpreadsheet interface of the spreadsheet. 177 @param aCellName The address of the cell (or a named range). 178 @param aFormula The formula to write into the cell. */ 179 public void setFormula( 180 com.sun.star.sheet.XSpreadsheet xSheet, 181 String aCellName, 182 String aFormula ) throws RuntimeException, Exception 183 { 184 xSheet.getCellRangeByName( aCellName ).getCellByPosition( 0, 0 ).setFormula( aFormula ); 185 } 186 187 /** Writes a date with standard date format into a spreadsheet. 188 @param xSheet The XSpreadsheet interface of the spreadsheet. 189 @param aCellName The address of the cell (or a named range). 190 @param nDay The day of the date. 191 @param nMonth The month of the date. 192 @param nYear The year of the date. */ 193 public void setDate( 194 com.sun.star.sheet.XSpreadsheet xSheet, 195 String aCellName, 196 int nDay, int nMonth, int nYear ) throws RuntimeException, Exception 197 { 198 // Set the date value. 199 com.sun.star.table.XCell xCell = xSheet.getCellRangeByName( aCellName ).getCellByPosition( 0, 0 ); 200 String aDateStr = nMonth + "/" + nDay + "/" + nYear; 201 xCell.setFormula( aDateStr ); 202 203 // Set standard date format. 204 com.sun.star.util.XNumberFormatsSupplier xFormatsSupplier = 205 (com.sun.star.util.XNumberFormatsSupplier) UnoRuntime.queryInterface( 206 com.sun.star.util.XNumberFormatsSupplier.class, getDocument() ); 207 com.sun.star.util.XNumberFormatTypes xFormatTypes = 208 (com.sun.star.util.XNumberFormatTypes) UnoRuntime.queryInterface( 209 com.sun.star.util.XNumberFormatTypes.class, xFormatsSupplier.getNumberFormats() ); 210 int nFormat = xFormatTypes.getStandardFormat( 211 com.sun.star.util.NumberFormat.DATE, new com.sun.star.lang.Locale() ); 212 213 com.sun.star.beans.XPropertySet xPropSet = (com.sun.star.beans.XPropertySet) 214 UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xCell ); 215 xPropSet.setPropertyValue( "NumberFormat", new Integer( nFormat ) ); 216 } 217 218 /** Draws a colored border around the range and writes the headline in the 219 first cell. 220 @param xSheet The XSpreadsheet interface of the spreadsheet. 221 @param aRange The address of the cell range (or a named range). 222 @param aHeadline The headline text. */ 223 public void prepareRange( 224 com.sun.star.sheet.XSpreadsheet xSheet, 225 String aRange, String aHeadline ) throws RuntimeException, Exception 226 { 227 com.sun.star.beans.XPropertySet xPropSet = null; 228 com.sun.star.table.XCellRange xCellRange = null; 229 230 // draw border 231 xCellRange = xSheet.getCellRangeByName( aRange ); 232 xPropSet = (com.sun.star.beans.XPropertySet) 233 UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xCellRange ); 234 com.sun.star.table.BorderLine aLine = new com.sun.star.table.BorderLine(); 235 aLine.Color = 0x99CCFF; 236 aLine.InnerLineWidth = aLine.LineDistance = 0; 237 aLine.OuterLineWidth = 100; 238 com.sun.star.table.TableBorder aBorder = new com.sun.star.table.TableBorder(); 239 aBorder.TopLine = aBorder.BottomLine = aBorder.LeftLine = aBorder.RightLine = aLine; 240 aBorder.IsTopLineValid = aBorder.IsBottomLineValid = true; 241 aBorder.IsLeftLineValid = aBorder.IsRightLineValid = true; 242 xPropSet.setPropertyValue( "TableBorder", aBorder ); 243 244 // draw headline 245 com.sun.star.sheet.XCellRangeAddressable xAddr = (com.sun.star.sheet.XCellRangeAddressable) 246 UnoRuntime.queryInterface( com.sun.star.sheet.XCellRangeAddressable.class, xCellRange ); 247 com.sun.star.table.CellRangeAddress aAddr = xAddr.getRangeAddress(); 248 249 xCellRange = xSheet.getCellRangeByPosition( 250 aAddr.StartColumn, aAddr.StartRow, aAddr.EndColumn, aAddr.StartRow ); 251 xPropSet = (com.sun.star.beans.XPropertySet) 252 UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xCellRange ); 253 xPropSet.setPropertyValue( "CellBackColor", new Integer( 0x99CCFF ) ); 254 // write headline 255 com.sun.star.table.XCell xCell = xCellRange.getCellByPosition( 0, 0 ); 256 xCell.setFormula( aHeadline ); 257 xPropSet = (com.sun.star.beans.XPropertySet) 258 UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xCell ); 259 xPropSet.setPropertyValue( "CharColor", new Integer( 0x003399 ) ); 260 xPropSet.setPropertyValue( "CharWeight", new Float( com.sun.star.awt.FontWeight.BOLD ) ); 261 } 262 263 // ________________________________________________________________ 264 // Methods to create cell addresses and range addresses. 265 266 /** Creates a com.sun.star.table.CellAddress and initializes it 267 with the given range. 268 @param xSheet The XSpreadsheet interface of the spreadsheet. 269 @param aCell The address of the cell (or a named cell). */ 270 public com.sun.star.table.CellAddress createCellAddress( 271 com.sun.star.sheet.XSpreadsheet xSheet, 272 String aCell ) throws RuntimeException, Exception 273 { 274 com.sun.star.sheet.XCellAddressable xAddr = (com.sun.star.sheet.XCellAddressable) 275 UnoRuntime.queryInterface( com.sun.star.sheet.XCellAddressable.class, 276 xSheet.getCellRangeByName( aCell ).getCellByPosition( 0, 0 ) ); 277 return xAddr.getCellAddress(); 278 } 279 280 /** Creates a com.sun.star.table.CellRangeAddress and initializes 281 it with the given range. 282 @param xSheet The XSpreadsheet interface of the spreadsheet. 283 @param aRange The address of the cell range (or a named range). */ 284 public com.sun.star.table.CellRangeAddress createCellRangeAddress( 285 com.sun.star.sheet.XSpreadsheet xSheet, String aRange ) 286 { 287 com.sun.star.sheet.XCellRangeAddressable xAddr = (com.sun.star.sheet.XCellRangeAddressable) 288 UnoRuntime.queryInterface( com.sun.star.sheet.XCellRangeAddressable.class, 289 xSheet.getCellRangeByName( aRange ) ); 290 return xAddr.getRangeAddress(); 291 } 292 293 // ________________________________________________________________ 294 // Methods to convert cell addresses and range addresses to strings. 295 296 /** Returns the text address of the cell. 297 @param nColumn The column index. 298 @param nRow The row index. 299 @return A string containing the cell address. */ 300 public String getCellAddressString( int nColumn, int nRow ) 301 { 302 String aStr = ""; 303 if (nColumn > 25) 304 aStr += (char) ('A' + nColumn / 26 - 1); 305 aStr += (char) ('A' + nColumn % 26); 306 aStr += (nRow + 1); 307 return aStr; 308 } 309 310 /** Returns the text address of the cell range. 311 @param aCellRange The cell range address. 312 @return A string containing the cell range address. */ 313 public String getCellRangeAddressString( 314 com.sun.star.table.CellRangeAddress aCellRange ) 315 { 316 return 317 getCellAddressString( aCellRange.StartColumn, aCellRange.StartRow ) 318 + ":" 319 + getCellAddressString( aCellRange.EndColumn, aCellRange.EndRow ); 320 } 321 322 /** Returns the text address of the cell range. 323 @param xCellRange The XSheetCellRange interface of the cell range. 324 @param bWithSheet true = Include sheet name. 325 @return A string containing the cell range address. */ 326 public String getCellRangeAddressString( 327 com.sun.star.sheet.XSheetCellRange xCellRange, 328 boolean bWithSheet ) 329 { 330 String aStr = ""; 331 if (bWithSheet) 332 { 333 com.sun.star.sheet.XSpreadsheet xSheet = xCellRange.getSpreadsheet(); 334 com.sun.star.container.XNamed xNamed = (com.sun.star.container.XNamed) 335 UnoRuntime.queryInterface( com.sun.star.container.XNamed.class, xSheet ); 336 aStr += xNamed.getName() + "."; 337 } 338 com.sun.star.sheet.XCellRangeAddressable xAddr = (com.sun.star.sheet.XCellRangeAddressable) 339 UnoRuntime.queryInterface( com.sun.star.sheet.XCellRangeAddressable.class, xCellRange ); 340 aStr += getCellRangeAddressString( xAddr.getRangeAddress() ); 341 return aStr; 342 } 343 344 /** Returns a list of addresses of all cell ranges contained in the collection. 345 @param xRangesIA The XIndexAccess interface of the collection. 346 @return A string containing the cell range address list. */ 347 public String getCellRangeListString( 348 com.sun.star.container.XIndexAccess xRangesIA ) throws RuntimeException, Exception 349 { 350 String aStr = ""; 351 int nCount = xRangesIA.getCount(); 352 for (int nIndex = 0; nIndex < nCount; ++nIndex) 353 { 354 if (nIndex > 0) 355 aStr += " "; 356 Object aRangeObj = xRangesIA.getByIndex( nIndex ); 357 com.sun.star.sheet.XSheetCellRange xCellRange = (com.sun.star.sheet.XSheetCellRange) 358 UnoRuntime.queryInterface( com.sun.star.sheet.XSheetCellRange.class, aRangeObj ); 359 aStr += getCellRangeAddressString( xCellRange, false ); 360 } 361 return aStr; 362 } 363 364 // ________________________________________________________________ 365 366 // Connect to a running office that is accepting connections. 367 private void connect() 368 { 369 if (mxRemoteContext == null && mxRemoteServiceManager == null) { 370 try { 371 // First step: get the remote office component context 372 mxRemoteContext = com.sun.star.comp.helper.Bootstrap.bootstrap(); 373 System.out.println("Connected to a running office ..."); 374 375 mxRemoteServiceManager = mxRemoteContext.getServiceManager(); 376 } 377 catch( Exception e) { 378 System.err.println("ERROR: can't get a component context from a running office ..."); 379 e.printStackTrace(); 380 System.exit(1); 381 } 382 } 383 } 384 385 /** Creates an empty spreadsheet document. 386 @return The XSpreadsheetDocument interface of the document. */ 387 private com.sun.star.sheet.XSpreadsheetDocument initDocument() 388 throws RuntimeException, Exception 389 { 390 XComponentLoader aLoader = (XComponentLoader) 391 UnoRuntime.queryInterface( 392 XComponentLoader.class, 393 mxRemoteServiceManager.createInstanceWithContext( 394 "com.sun.star.frame.Desktop", mxRemoteContext)); 395 396 XComponent xComponent = aLoader.loadComponentFromURL( 397 "private:factory/scalc", "_blank", 0, 398 new com.sun.star.beans.PropertyValue[0] ); 399 400 return (com.sun.star.sheet.XSpreadsheetDocument)UnoRuntime.queryInterface( 401 com.sun.star.sheet.XSpreadsheetDocument.class, xComponent ); 402 } 403 404 // ________________________________________________________________ 405 } 406