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 Desktop object from the office 37 // Step 2: open an empty Calc document 38 // Step 3: enter a example text, set the numberformat to DM 39 // Step 4: change the numberformat to EUR (Euro) 40 // Step 5: use the DM/EUR factor on each cell with a content 41 //*************************************************************************** 42 43 import com.sun.star.beans.PropertyValue; 44 import com.sun.star.beans.XPropertySet; 45 46 import com.sun.star.container.XEnumeration; 47 import com.sun.star.container.XIndexAccess; 48 import com.sun.star.container.XEnumerationAccess; 49 50 import com.sun.star.document.XActionLockable; 51 52 import com.sun.star.frame.XDesktop; 53 import com.sun.star.frame.XComponentLoader; 54 55 import com.sun.star.lang.Locale; 56 import com.sun.star.lang.XComponent; 57 import com.sun.star.lang.XMultiComponentFactory; 58 59 import com.sun.star.table.XCell; 60 import com.sun.star.table.XCellRange; 61 62 import com.sun.star.sheet.XSpreadsheet; 63 import com.sun.star.sheet.XSpreadsheets; 64 import com.sun.star.sheet.XSheetCellRanges; 65 import com.sun.star.sheet.XCellRangesQuery; 66 import com.sun.star.sheet.XCellFormatRangesSupplier; 67 import com.sun.star.sheet.XCellRangesQuery; 68 import com.sun.star.sheet.XSpreadsheetDocument; 69 70 import com.sun.star.uno.UnoRuntime; 71 import com.sun.star.uno.AnyConverter; 72 import com.sun.star.uno.XInterface; 73 import com.sun.star.uno.XComponentContext; 74 75 import com.sun.star.util.NumberFormat; 76 import com.sun.star.util.XNumberFormats; 77 import com.sun.star.util.XNumberFormatsSupplier; 78 79 80 public class EuroAdaption { 81 82 public static void main(String args[]) { 83 // You need the desktop to create a document 84 // The getDesktop method does the UNO bootstrapping, gets the 85 // remote servie manager and the desktop object. 86 com.sun.star.frame.XDesktop xDesktop = null; 87 xDesktop = getDesktop(); 88 89 // create a sheet document 90 XSpreadsheetDocument xSheetdocument = null; 91 xSheetdocument = ( XSpreadsheetDocument ) createSheetdocument( xDesktop ); 92 System.out.println( "Create a new Spreadsheet" ); 93 94 // get the collection of all sheets from the document 95 XSpreadsheets xSheets = null; 96 xSheets = (XSpreadsheets) xSheetdocument.getSheets(); 97 98 // the Action Interface provides methods to hide actions, 99 // like inserting data, on a sheet, that increase the performance 100 XActionLockable xActionInterface = null; 101 xActionInterface = (XActionLockable) UnoRuntime.queryInterface( 102 XActionLockable.class, xSheetdocument ); 103 104 // lock all actions 105 xActionInterface.addActionLock(); 106 107 com.sun.star.sheet.XSpreadsheet xSheet = null; 108 try { 109 // get via the index access the first sheet 110 XIndexAccess xElements = (XIndexAccess) UnoRuntime.queryInterface( 111 XIndexAccess.class, xSheets ); 112 113 // specify the first sheet from the spreadsheet 114 xSheet = (XSpreadsheet) UnoRuntime.queryInterface( 115 XSpreadsheet.class, xElements.getByIndex( 0 )); 116 } 117 catch( Exception e) { 118 e.printStackTrace(System.err); 119 } 120 121 // get the interface to apply and create new numberformats 122 XNumberFormatsSupplier xNumberFormatSupplier = null; 123 xNumberFormatSupplier = (XNumberFormatsSupplier) UnoRuntime.queryInterface( 124 XNumberFormatsSupplier.class, xSheetdocument ); 125 XNumberFormats xNumberFormats = null; 126 xNumberFormats = xNumberFormatSupplier.getNumberFormats(); 127 128 // insert some example data in a sheet 129 createExampleData( xSheet, xNumberFormats ); 130 System.out.println( "Insert example data and use the number format with the currency 'DM'" ); 131 132 // Change the currency from the cells from DM to Euro 133 Convert( xSheet, xNumberFormats, "DM", "EUR", 1.95583f ); 134 System.out.println( "Change the number format to EUR and divide the values with the factor 1.95583" ); 135 136 // remove all locks, the user see all changes 137 xActionInterface.removeActionLock(); 138 139 System.out.println("done"); 140 System.exit(0); 141 } 142 143 144 public static void Convert( XSpreadsheet xSheet, XNumberFormats xNumberFormats, 145 String sOldSymbol, String sNewSymbol, 146 float fFactor ) { 147 try { 148 Locale xLanguage = new Locale(); 149 xLanguage.Country = "de"; // Germany -> DM 150 xLanguage.Language = "de"; // German 151 152 // Numberformat string with sNewSymbol 153 String sSimple = "0 [$" + sNewSymbol + "]"; 154 // create a number format key with the sNewSymbol 155 int iSimpleKey = NumberFormat( xNumberFormats, sSimple, xLanguage ); 156 157 // you have to use the FormatSupplier interface to get the 158 // CellFormat enumeration 159 XCellFormatRangesSupplier xCellFormatSupplier = 160 (XCellFormatRangesSupplier)UnoRuntime.queryInterface( 161 XCellFormatRangesSupplier.class, xSheet ); 162 163 // getCellFormatRanges() has the interfaces for the enumeration 164 XEnumerationAccess xEnumerationAccess = 165 (XEnumerationAccess)UnoRuntime.queryInterface( 166 XEnumerationAccess.class, 167 xCellFormatSupplier.getCellFormatRanges() ); 168 169 XEnumeration xRanges = xEnumerationAccess.createEnumeration(); 170 171 // create an AnyConverter for later use 172 AnyConverter aAnyConv = new AnyConverter(); 173 174 while( xRanges.hasMoreElements() ) { 175 // the enumeration returns a cellrange 176 XCellRange xCellRange = (XCellRange) UnoRuntime.queryInterface( 177 XCellRange.class, xRanges.nextElement()); 178 179 // the PropertySet the get and set the properties from the cellrange 180 XPropertySet xCellProp = (XPropertySet)UnoRuntime.queryInterface( 181 XPropertySet.class, xCellRange ); 182 183 // getPropertyValue returns an Object, you have to cast it to 184 // type that you need 185 Object oNumberObject = xCellProp.getPropertyValue( "NumberFormat" ); 186 int iNumberFormat = aAnyConv.toInt(oNumberObject); 187 188 // get the properties from the cellrange numberformat 189 XPropertySet xFormat = (XPropertySet) 190 xNumberFormats.getByKey(iNumberFormat ); 191 192 short fType = aAnyConv.toShort(xFormat.getPropertyValue("Type")); 193 String sCurrencySymbol = aAnyConv.toString( 194 xFormat.getPropertyValue("CurrencySymbol")); 195 196 // change the numberformat only on cellranges with a 197 // currency numberformat 198 if( ( (fType & com.sun.star.util.NumberFormat.CURRENCY) > 0) && 199 ( sCurrencySymbol.compareTo( sOldSymbol ) == 0 ) ) { 200 boolean bThousandSep = aAnyConv.toBoolean( 201 xFormat.getPropertyValue("ThousandsSeparator")); 202 boolean bNegativeRed = aAnyConv.toBoolean( 203 xFormat.getPropertyValue("NegativeRed")); 204 short fDecimals = aAnyConv.toShort( 205 xFormat.getPropertyValue("Decimals")); 206 short fLeadingZeros = aAnyConv.toShort( 207 xFormat.getPropertyValue("LeadingZeros")); 208 Locale oLocale = (Locale) aAnyConv.toObject( 209 new com.sun.star.uno.Type(Locale.class), 210 xFormat.getPropertyValue("Locale")); 211 212 // create a new numberformat string 213 String sNew = xNumberFormats.generateFormat( iSimpleKey, 214 oLocale, bThousandSep, bNegativeRed, 215 fDecimals, fLeadingZeros ); 216 217 // get the NumberKey from the numberformat 218 int iNewNumberFormat = NumberFormat( xNumberFormats, 219 sNew, oLocale ); 220 221 // set the new numberformat to the cellrange DM->EUR 222 xCellProp.setPropertyValue( "NumberFormat", 223 new Integer( iNewNumberFormat ) ); 224 225 // interate over all cells from the cellrange with an 226 // content and use the DM/EUR factor 227 XCellRangesQuery xCellRangesQuery = (XCellRangesQuery) 228 UnoRuntime.queryInterface( 229 XCellRangesQuery.class, xCellRange ); 230 231 XSheetCellRanges xSheetCellRanges = 232 xCellRangesQuery.queryContentCells( 233 (short) com.sun.star.sheet.CellFlags.VALUE ); 234 235 if( xSheetCellRanges.getCount() > 0 ) { 236 XEnumerationAccess xCellEnumerationAccess = 237 xSheetCellRanges.getCells(); 238 XEnumeration xCellEnumeration = 239 xCellEnumerationAccess.createEnumeration(); 240 241 while( xCellEnumeration.hasMoreElements() ) { 242 XCell xCell = (XCell) UnoRuntime.queryInterface( 243 XCell.class, xCellEnumeration.nextElement()); 244 xCell.setValue( (double) xCell.getValue() / fFactor ); 245 } 246 } 247 } 248 } 249 } 250 catch( Exception e) { 251 e.printStackTrace(System.err); 252 } 253 } 254 255 256 public static int NumberFormat( XNumberFormats xNumberFormat, String sFormat, 257 com.sun.star.lang.Locale xLanguage ) { 258 int nRetKey = 0; 259 260 try { 261 // exists the numberformat 262 nRetKey = xNumberFormat.queryKey( sFormat, xLanguage, true ); 263 264 // if not, create a new one 265 if( nRetKey == -1 ) { 266 nRetKey = xNumberFormat.addNew( sFormat, xLanguage ); 267 if( nRetKey == -1 ) 268 nRetKey = 0; 269 } 270 } 271 catch( Exception e) { 272 e.printStackTrace(System.err); 273 } 274 275 return( nRetKey ); 276 } 277 278 279 public static void createExampleData( XSpreadsheet xSheet, 280 XNumberFormats xNumberFormat ) { 281 282 // enter in a cellrange numbers and change the numberformat to DM 283 XCell xCell = null; 284 XCellRange xCellRange = null; 285 286 try { 287 Locale xLanguage = new Locale(); 288 xLanguage.Country = "de"; // Germany -> DM 289 xLanguage.Language = "de"; // German 290 291 // Numberformat string from DM 292 String sSimple = "0 [$DM]"; 293 294 // get the numberformat key 295 int iNumberFormatKey = NumberFormat(xNumberFormat, sSimple, xLanguage); 296 297 for( int iCounter=1; iCounter < 10; iCounter++ ) { 298 // get one cell and insert a number 299 xCell = xSheet.getCellByPosition( 2, 1 + iCounter ); 300 xCell.setValue( (double) iCounter * 2 ); 301 xCellRange = xSheet.getCellRangeByPosition( 2, 1 + iCounter, 302 2, 1 + iCounter ); 303 304 // get the ProperySet from the cell, to change the numberformat 305 XPropertySet xCellProp = (XPropertySet)UnoRuntime.queryInterface( 306 XPropertySet.class, xCellRange ); 307 xCellProp.setPropertyValue( "NumberFormat", 308 new Integer(iNumberFormatKey) ); 309 } 310 } 311 catch( Exception e) { 312 e.printStackTrace(System.err); 313 } 314 } 315 316 public static XDesktop getDesktop() { 317 XDesktop xDesktop = null; 318 XMultiComponentFactory xMCF = null; 319 320 try { 321 XComponentContext xContext = null; 322 323 // get the remote office component context 324 xContext = com.sun.star.comp.helper.Bootstrap.bootstrap(); 325 326 // get the remote office service manager 327 xMCF = xContext.getServiceManager(); 328 if( xMCF != null ) { 329 System.out.println("Connected to a running office ..."); 330 331 Object oDesktop = xMCF.createInstanceWithContext( 332 "com.sun.star.frame.Desktop", xContext); 333 xDesktop = (XDesktop) UnoRuntime.queryInterface( 334 XDesktop.class, oDesktop); 335 } 336 else 337 System.out.println( "Can't create a desktop. No connection, no remote servicemanager available!" ); 338 } 339 catch( Exception e) { 340 e.printStackTrace(System.err); 341 System.exit(1); 342 } 343 344 345 return xDesktop; 346 } 347 348 349 public static XSpreadsheetDocument createSheetdocument( XDesktop xDesktop ) { 350 XSpreadsheetDocument aSheetDocument = null; 351 352 try { 353 XComponent xComponent = null; 354 xComponent = CreateNewDocument( xDesktop, "scalc" ); 355 356 aSheetDocument = (XSpreadsheetDocument) UnoRuntime.queryInterface( 357 XSpreadsheetDocument.class, xComponent); 358 } 359 catch( Exception e) { 360 e.printStackTrace(System.err); 361 } 362 363 return aSheetDocument; 364 } 365 366 protected static XComponent CreateNewDocument( XDesktop xDesktop, 367 String sDocumentType ) { 368 String sURL = "private:factory/" + sDocumentType; 369 370 XComponent xComponent = null; 371 XComponentLoader xComponentLoader = null; 372 PropertyValue xValues[] = new PropertyValue[1]; 373 PropertyValue xEmptyArgs[] = new PropertyValue[0]; 374 375 try { 376 xComponentLoader = (XComponentLoader) UnoRuntime.queryInterface( 377 XComponentLoader.class, xDesktop ); 378 379 xComponent = xComponentLoader.loadComponentFromURL( 380 sURL, "_blank", 0, xEmptyArgs); 381 } 382 catch( Exception e) { 383 e.printStackTrace(System.err); 384 } 385 386 return xComponent ; 387 } 388 389 } 390