/************************************************************** * * 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. * *************************************************************/ // __________ Imports __________ import com.sun.star.beans.PropertyValue; import com.sun.star.container.XIndexAccess; import com.sun.star.frame.XComponentLoader; import com.sun.star.lang.XMultiComponentFactory; import com.sun.star.sheet.XSpreadsheet; import com.sun.star.sheet.XSpreadsheets; import com.sun.star.sheet.XSpreadsheetDocument; import com.sun.star.table.XCell; import com.sun.star.uno.UnoRuntime; import com.sun.star.uno.XComponentContext; // __________ Implementation __________ /** Create a spreadsheet document and provide access to a sheet framework that is then used to modify some number formats. @author Eike Rathke */ public class Number_Formats { // __________ public members and methods __________ // ____________________ public static void main( String args[] ) { try { Number_Formats aSample = new Number_Formats( args ); aSample.doFunction(); } catch( Exception ex ) { System.err.println( "Sample caught exception! " + ex ); ex.printStackTrace(); System.exit(1); } System.out.println( "Sample done." ); System.exit(0); } // ____________________ public void doFunction() throws RuntimeException, Exception { // Assume: // com.sun.star.sheet.XSpreadsheetDocument maSpreadsheetDoc; // com.sun.star.sheet.XSpreadsheet maSheet; // Query the number formats supplier of the spreadsheet document com.sun.star.util.XNumberFormatsSupplier xNumberFormatsSupplier = (com.sun.star.util.XNumberFormatsSupplier) UnoRuntime.queryInterface( com.sun.star.util.XNumberFormatsSupplier.class, maSpreadsheetDoc ); // Get the number formats from the supplier com.sun.star.util.XNumberFormats xNumberFormats = xNumberFormatsSupplier.getNumberFormats(); // Query the XNumberFormatTypes interface com.sun.star.util.XNumberFormatTypes xNumberFormatTypes = (com.sun.star.util.XNumberFormatTypes) UnoRuntime.queryInterface( com.sun.star.util.XNumberFormatTypes.class, xNumberFormats ); // Get the number format index key of the default currency format, // note the empty locale for default locale com.sun.star.lang.Locale aLocale = new com.sun.star.lang.Locale(); int nCurrencyKey = xNumberFormatTypes.getStandardFormat( com.sun.star.util.NumberFormat.CURRENCY, aLocale ); // Get cell range B3:B11 com.sun.star.table.XCellRange xCellRange = maSheet.getCellRangeByPosition( 1, 2, 1, 10 ); // Query the property set of the cell range com.sun.star.beans.XPropertySet xCellProp = (com.sun.star.beans.XPropertySet) UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xCellRange ); // Set number format to default currency xCellProp.setPropertyValue( "NumberFormat", new Integer(nCurrencyKey) ); // Get cell B3 com.sun.star.table.XCell xCell = maSheet.getCellByPosition( 1, 2 ); // Query the property set of the cell xCellProp = (com.sun.star.beans.XPropertySet) UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xCell ); // Get the number format index key of the cell's properties int nIndexKey = ((Integer) xCellProp.getPropertyValue( "NumberFormat" )).intValue(); if ( nIndexKey != nCurrencyKey ) System.out.println( "Number format doesn't match!" ); // Get the properties of the number format com.sun.star.beans.XPropertySet xProp = xNumberFormats.getByKey( nIndexKey ); // Get the format code string of the number format's properties String aFormatCode = (String) xProp.getPropertyValue( "FormatString" ); System.out.println( "FormatString: `" + aFormatCode + "'" ); // Create an arbitrary format code aFormatCode = "\"wonderful \"" + aFormatCode; // Test if it's already present nIndexKey = xNumberFormats.queryKey( aFormatCode, aLocale, false ); // If not, add to number formats collection if ( nIndexKey == -1 ) { try { nIndexKey = xNumberFormats.addNew( aFormatCode, aLocale ); } catch( com.sun.star.util.MalformedNumberFormatException ex ) { System.err.println( "Bad number format code: " + ex ); ex.printStackTrace(); nIndexKey = -1; } } // Set the new format at the cell if ( nIndexKey != -1 ) xCellProp.setPropertyValue( "NumberFormat", new Integer(nIndexKey) ); // Set column containing the example values to optimal width to show // the new format of cell B3 com.sun.star.table.XColumnRowRange xColRowRange = (com.sun.star.table.XColumnRowRange) UnoRuntime.queryInterface(com.sun.star.table.XColumnRowRange.class, maSheet); com.sun.star.container.XIndexAccess xIndexAccess = (com.sun.star.container.XIndexAccess) UnoRuntime.queryInterface(com.sun.star.container.XIndexAccess.class, xColRowRange.getColumns()); com.sun.star.beans.XPropertySet xColPropSet = (com.sun.star.beans.XPropertySet) UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, xIndexAccess.getByIndex(1)); xColPropSet.setPropertyValue( "OptimalWidth", new Boolean(true) ); } // ____________________ public Number_Formats( String[] args ) throws java.lang.Exception { // get the remote office context. If necessary a new office // process is started maOfficeContext = com.sun.star.comp.helper.Bootstrap.bootstrap(); System.out.println("Connected to a running office ..."); maServiceManager = maOfficeContext.getServiceManager(); // create a new spreadsheet document XComponentLoader aLoader = (XComponentLoader) UnoRuntime.queryInterface( XComponentLoader.class, maServiceManager.createInstanceWithContext( "com.sun.star.frame.Desktop", maOfficeContext) ); maSpreadsheetDoc = (XSpreadsheetDocument) UnoRuntime.queryInterface( XSpreadsheetDocument.class, aLoader.loadComponentFromURL( "private:factory/scalc", "_blank", 0, new PropertyValue[ 0 ] ) ); if ( !initSpreadsheet() ) System.exit( 0 ); } // __________ private members and methods __________ private final String msDataSheetName = "Data"; private XComponentContext maOfficeContext; private XMultiComponentFactory maServiceManager; private XSpreadsheetDocument maSpreadsheetDoc; private XSpreadsheet maSheet; // the first sheet // ____________________ /** init the first sheet */ private boolean initSpreadsheet() { boolean bOk = true; XSpreadsheets aSheets = maSpreadsheetDoc.getSheets(); try { XIndexAccess aSheetsIA = (XIndexAccess) UnoRuntime.queryInterface( XIndexAccess.class, aSheets ); maSheet = (XSpreadsheet) UnoRuntime.queryInterface(XSpreadsheet.class, aSheetsIA.getByIndex( 0 )); // enter some values in B3:B11 for( int iCounter=1; iCounter < 10; iCounter++ ) { XCell aCell = maSheet.getCellByPosition( 1, 1 + iCounter ); aCell.setValue( (double) iCounter ); } } catch( Exception ex ) { System.err.println( "Couldn't initialize Spreadsheet Document: " + ex ); ex.printStackTrace(); bOk = false; } return bOk; } }