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