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