| /************************************************************** |
| * |
| * Licensed to the Apache Software Foundation (ASF) under one |
| * or more contributor license agreements. See the NOTICE file |
| * distributed with this work for additional information |
| * regarding copyright ownership. The ASF licenses this file |
| * to you under the Apache License, Version 2.0 (the |
| * "License"); you may not use this file except in compliance |
| * with the License. You may obtain a copy of the License at |
| * |
| * http://www.apache.org/licenses/LICENSE-2.0 |
| * |
| * Unless required by applicable law or agreed to in writing, |
| * software distributed under the License is distributed on an |
| * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY |
| * KIND, either express or implied. See the License for the |
| * specific language governing permissions and limitations |
| * under the License. |
| * |
| *************************************************************/ |
| |
| |
| |
| //*************************************************************************** |
| // comment: Step 1: get the Desktop object from the office |
| // Step 2: open an empty Calc document |
| // Step 3: enter a example text, set the numberformat to DM |
| // Step 4: change the numberformat to EUR (Euro) |
| // Step 5: use the DM/EUR factor on each cell with a content |
| //*************************************************************************** |
| |
| import com.sun.star.beans.PropertyValue; |
| import com.sun.star.beans.XPropertySet; |
| |
| import com.sun.star.container.XEnumeration; |
| import com.sun.star.container.XIndexAccess; |
| import com.sun.star.container.XEnumerationAccess; |
| |
| import com.sun.star.document.XActionLockable; |
| |
| import com.sun.star.frame.XDesktop; |
| import com.sun.star.frame.XComponentLoader; |
| |
| import com.sun.star.lang.Locale; |
| import com.sun.star.lang.XComponent; |
| import com.sun.star.lang.XMultiComponentFactory; |
| |
| import com.sun.star.table.XCell; |
| import com.sun.star.table.XCellRange; |
| |
| import com.sun.star.sheet.XSpreadsheet; |
| import com.sun.star.sheet.XSpreadsheets; |
| import com.sun.star.sheet.XSheetCellRanges; |
| import com.sun.star.sheet.XCellRangesQuery; |
| import com.sun.star.sheet.XCellFormatRangesSupplier; |
| import com.sun.star.sheet.XCellRangesQuery; |
| import com.sun.star.sheet.XSpreadsheetDocument; |
| |
| import com.sun.star.uno.UnoRuntime; |
| import com.sun.star.uno.AnyConverter; |
| import com.sun.star.uno.XInterface; |
| import com.sun.star.uno.XComponentContext; |
| |
| import com.sun.star.util.NumberFormat; |
| import com.sun.star.util.XNumberFormats; |
| import com.sun.star.util.XNumberFormatsSupplier; |
| |
| |
| public class EuroAdaption { |
| |
| public static void main(String args[]) { |
| // You need the desktop to create a document |
| // The getDesktop method does the UNO bootstrapping, gets the |
| // remote servie manager and the desktop object. |
| com.sun.star.frame.XDesktop xDesktop = null; |
| xDesktop = getDesktop(); |
| |
| // create a sheet document |
| XSpreadsheetDocument xSheetdocument = null; |
| xSheetdocument = ( XSpreadsheetDocument ) createSheetdocument( xDesktop ); |
| System.out.println( "Create a new Spreadsheet" ); |
| |
| // get the collection of all sheets from the document |
| XSpreadsheets xSheets = null; |
| xSheets = (XSpreadsheets) xSheetdocument.getSheets(); |
| |
| // the Action Interface provides methods to hide actions, |
| // like inserting data, on a sheet, that increase the performance |
| XActionLockable xActionInterface = null; |
| xActionInterface = (XActionLockable) UnoRuntime.queryInterface( |
| XActionLockable.class, xSheetdocument ); |
| |
| // lock all actions |
| xActionInterface.addActionLock(); |
| |
| com.sun.star.sheet.XSpreadsheet xSheet = null; |
| try { |
| // get via the index access the first sheet |
| XIndexAccess xElements = (XIndexAccess) UnoRuntime.queryInterface( |
| XIndexAccess.class, xSheets ); |
| |
| // specify the first sheet from the spreadsheet |
| xSheet = (XSpreadsheet) UnoRuntime.queryInterface( |
| XSpreadsheet.class, xElements.getByIndex( 0 )); |
| } |
| catch( Exception e) { |
| e.printStackTrace(System.err); |
| } |
| |
| // get the interface to apply and create new numberformats |
| XNumberFormatsSupplier xNumberFormatSupplier = null; |
| xNumberFormatSupplier = (XNumberFormatsSupplier) UnoRuntime.queryInterface( |
| XNumberFormatsSupplier.class, xSheetdocument ); |
| XNumberFormats xNumberFormats = null; |
| xNumberFormats = xNumberFormatSupplier.getNumberFormats(); |
| |
| // insert some example data in a sheet |
| createExampleData( xSheet, xNumberFormats ); |
| System.out.println( "Insert example data and use the number format with the currency 'DM'" ); |
| |
| // Change the currency from the cells from DM to Euro |
| Convert( xSheet, xNumberFormats, "DM", "EUR", 1.95583f ); |
| System.out.println( "Change the number format to EUR and divide the values with the factor 1.95583" ); |
| |
| // remove all locks, the user see all changes |
| xActionInterface.removeActionLock(); |
| |
| System.out.println("done"); |
| System.exit(0); |
| } |
| |
| |
| public static void Convert( XSpreadsheet xSheet, XNumberFormats xNumberFormats, |
| String sOldSymbol, String sNewSymbol, |
| float fFactor ) { |
| try { |
| Locale xLanguage = new Locale(); |
| xLanguage.Country = "de"; // Germany -> DM |
| xLanguage.Language = "de"; // German |
| |
| // Numberformat string with sNewSymbol |
| String sSimple = "0 [$" + sNewSymbol + "]"; |
| // create a number format key with the sNewSymbol |
| int iSimpleKey = NumberFormat( xNumberFormats, sSimple, xLanguage ); |
| |
| // you have to use the FormatSupplier interface to get the |
| // CellFormat enumeration |
| XCellFormatRangesSupplier xCellFormatSupplier = |
| (XCellFormatRangesSupplier)UnoRuntime.queryInterface( |
| XCellFormatRangesSupplier.class, xSheet ); |
| |
| // getCellFormatRanges() has the interfaces for the enumeration |
| XEnumerationAccess xEnumerationAccess = |
| (XEnumerationAccess)UnoRuntime.queryInterface( |
| XEnumerationAccess.class, |
| xCellFormatSupplier.getCellFormatRanges() ); |
| |
| XEnumeration xRanges = xEnumerationAccess.createEnumeration(); |
| |
| // create an AnyConverter for later use |
| AnyConverter aAnyConv = new AnyConverter(); |
| |
| while( xRanges.hasMoreElements() ) { |
| // the enumeration returns a cellrange |
| XCellRange xCellRange = (XCellRange) UnoRuntime.queryInterface( |
| XCellRange.class, xRanges.nextElement()); |
| |
| // the PropertySet the get and set the properties from the cellrange |
| XPropertySet xCellProp = (XPropertySet)UnoRuntime.queryInterface( |
| XPropertySet.class, xCellRange ); |
| |
| // getPropertyValue returns an Object, you have to cast it to |
| // type that you need |
| Object oNumberObject = xCellProp.getPropertyValue( "NumberFormat" ); |
| int iNumberFormat = aAnyConv.toInt(oNumberObject); |
| |
| // get the properties from the cellrange numberformat |
| XPropertySet xFormat = (XPropertySet) |
| xNumberFormats.getByKey(iNumberFormat ); |
| |
| short fType = aAnyConv.toShort(xFormat.getPropertyValue("Type")); |
| String sCurrencySymbol = aAnyConv.toString( |
| xFormat.getPropertyValue("CurrencySymbol")); |
| |
| // change the numberformat only on cellranges with a |
| // currency numberformat |
| if( ( (fType & com.sun.star.util.NumberFormat.CURRENCY) > 0) && |
| ( sCurrencySymbol.compareTo( sOldSymbol ) == 0 ) ) { |
| boolean bThousandSep = aAnyConv.toBoolean( |
| xFormat.getPropertyValue("ThousandsSeparator")); |
| boolean bNegativeRed = aAnyConv.toBoolean( |
| xFormat.getPropertyValue("NegativeRed")); |
| short fDecimals = aAnyConv.toShort( |
| xFormat.getPropertyValue("Decimals")); |
| short fLeadingZeros = aAnyConv.toShort( |
| xFormat.getPropertyValue("LeadingZeros")); |
| Locale oLocale = (Locale) aAnyConv.toObject( |
| new com.sun.star.uno.Type(Locale.class), |
| xFormat.getPropertyValue("Locale")); |
| |
| // create a new numberformat string |
| String sNew = xNumberFormats.generateFormat( iSimpleKey, |
| oLocale, bThousandSep, bNegativeRed, |
| fDecimals, fLeadingZeros ); |
| |
| // get the NumberKey from the numberformat |
| int iNewNumberFormat = NumberFormat( xNumberFormats, |
| sNew, oLocale ); |
| |
| // set the new numberformat to the cellrange DM->EUR |
| xCellProp.setPropertyValue( "NumberFormat", |
| new Integer( iNewNumberFormat ) ); |
| |
| // interate over all cells from the cellrange with an |
| // content and use the DM/EUR factor |
| XCellRangesQuery xCellRangesQuery = (XCellRangesQuery) |
| UnoRuntime.queryInterface( |
| XCellRangesQuery.class, xCellRange ); |
| |
| XSheetCellRanges xSheetCellRanges = |
| xCellRangesQuery.queryContentCells( |
| (short) com.sun.star.sheet.CellFlags.VALUE ); |
| |
| if( xSheetCellRanges.getCount() > 0 ) { |
| XEnumerationAccess xCellEnumerationAccess = |
| xSheetCellRanges.getCells(); |
| XEnumeration xCellEnumeration = |
| xCellEnumerationAccess.createEnumeration(); |
| |
| while( xCellEnumeration.hasMoreElements() ) { |
| XCell xCell = (XCell) UnoRuntime.queryInterface( |
| XCell.class, xCellEnumeration.nextElement()); |
| xCell.setValue( (double) xCell.getValue() / fFactor ); |
| } |
| } |
| } |
| } |
| } |
| catch( Exception e) { |
| e.printStackTrace(System.err); |
| } |
| } |
| |
| |
| public static int NumberFormat( XNumberFormats xNumberFormat, String sFormat, |
| com.sun.star.lang.Locale xLanguage ) { |
| int nRetKey = 0; |
| |
| try { |
| // exists the numberformat |
| nRetKey = xNumberFormat.queryKey( sFormat, xLanguage, true ); |
| |
| // if not, create a new one |
| if( nRetKey == -1 ) { |
| nRetKey = xNumberFormat.addNew( sFormat, xLanguage ); |
| if( nRetKey == -1 ) |
| nRetKey = 0; |
| } |
| } |
| catch( Exception e) { |
| e.printStackTrace(System.err); |
| } |
| |
| return( nRetKey ); |
| } |
| |
| |
| public static void createExampleData( XSpreadsheet xSheet, |
| XNumberFormats xNumberFormat ) { |
| |
| // enter in a cellrange numbers and change the numberformat to DM |
| XCell xCell = null; |
| XCellRange xCellRange = null; |
| |
| try { |
| Locale xLanguage = new Locale(); |
| xLanguage.Country = "de"; // Germany -> DM |
| xLanguage.Language = "de"; // German |
| |
| // Numberformat string from DM |
| String sSimple = "0 [$DM]"; |
| |
| // get the numberformat key |
| int iNumberFormatKey = NumberFormat(xNumberFormat, sSimple, xLanguage); |
| |
| for( int iCounter=1; iCounter < 10; iCounter++ ) { |
| // get one cell and insert a number |
| xCell = xSheet.getCellByPosition( 2, 1 + iCounter ); |
| xCell.setValue( (double) iCounter * 2 ); |
| xCellRange = xSheet.getCellRangeByPosition( 2, 1 + iCounter, |
| 2, 1 + iCounter ); |
| |
| // get the ProperySet from the cell, to change the numberformat |
| XPropertySet xCellProp = (XPropertySet)UnoRuntime.queryInterface( |
| XPropertySet.class, xCellRange ); |
| xCellProp.setPropertyValue( "NumberFormat", |
| new Integer(iNumberFormatKey) ); |
| } |
| } |
| catch( Exception e) { |
| e.printStackTrace(System.err); |
| } |
| } |
| |
| public static XDesktop getDesktop() { |
| XDesktop xDesktop = null; |
| XMultiComponentFactory xMCF = null; |
| |
| try { |
| XComponentContext xContext = null; |
| |
| // get the remote office component context |
| xContext = com.sun.star.comp.helper.Bootstrap.bootstrap(); |
| |
| // get the remote office service manager |
| xMCF = xContext.getServiceManager(); |
| if( xMCF != null ) { |
| System.out.println("Connected to a running office ..."); |
| |
| Object oDesktop = xMCF.createInstanceWithContext( |
| "com.sun.star.frame.Desktop", xContext); |
| xDesktop = (XDesktop) UnoRuntime.queryInterface( |
| XDesktop.class, oDesktop); |
| } |
| else |
| System.out.println( "Can't create a desktop. No connection, no remote servicemanager available!" ); |
| } |
| catch( Exception e) { |
| e.printStackTrace(System.err); |
| System.exit(1); |
| } |
| |
| |
| return xDesktop; |
| } |
| |
| |
| public static XSpreadsheetDocument createSheetdocument( XDesktop xDesktop ) { |
| XSpreadsheetDocument aSheetDocument = null; |
| |
| try { |
| XComponent xComponent = null; |
| xComponent = CreateNewDocument( xDesktop, "scalc" ); |
| |
| aSheetDocument = (XSpreadsheetDocument) UnoRuntime.queryInterface( |
| XSpreadsheetDocument.class, xComponent); |
| } |
| catch( Exception e) { |
| e.printStackTrace(System.err); |
| } |
| |
| return aSheetDocument; |
| } |
| |
| protected static XComponent CreateNewDocument( XDesktop xDesktop, |
| String sDocumentType ) { |
| String sURL = "private:factory/" + sDocumentType; |
| |
| XComponent xComponent = null; |
| XComponentLoader xComponentLoader = null; |
| PropertyValue xValues[] = new PropertyValue[1]; |
| PropertyValue xEmptyArgs[] = new PropertyValue[0]; |
| |
| try { |
| xComponentLoader = (XComponentLoader) UnoRuntime.queryInterface( |
| XComponentLoader.class, xDesktop ); |
| |
| xComponent = xComponentLoader.loadComponentFromURL( |
| sURL, "_blank", 0, xEmptyArgs); |
| } |
| catch( Exception e) { |
| e.printStackTrace(System.err); |
| } |
| |
| return xComponent ; |
| } |
| |
| } |
