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 // __________ Imports __________ 36 37 import com.sun.star.beans.PropertyValue; 38 import com.sun.star.container.XIndexAccess; 39 import com.sun.star.frame.XComponentLoader; 40 import com.sun.star.lang.XMultiComponentFactory; 41 import com.sun.star.sheet.XSpreadsheet; 42 import com.sun.star.sheet.XSpreadsheets; 43 import com.sun.star.sheet.XSpreadsheetDocument; 44 import com.sun.star.table.XCell; 45 import com.sun.star.uno.UnoRuntime; 46 import com.sun.star.uno.XComponentContext; 47 48 49 // __________ Implementation __________ 50 51 /** Create a spreadsheet document and provide access to a sheet framework that 52 is then used to modify some number formats. 53 @author Eike Rathke 54 */ 55 public class Number_Formats 56 { 57 // __________ public members and methods __________ 58 59 60 // ____________________ 61 62 public static void main( String args[] ) 63 { 64 try 65 { 66 Number_Formats aSample = new Number_Formats( args ); 67 aSample.doFunction(); 68 } 69 catch( Exception ex ) 70 { 71 System.err.println( "Sample caught exception! " + ex ); 72 ex.printStackTrace(); 73 System.exit(1); 74 } 75 76 System.out.println( "Sample done." ); 77 System.exit(0); 78 } 79 80 // ____________________ 81 82 public void doFunction() throws RuntimeException, Exception 83 { 84 // Assume: 85 // com.sun.star.sheet.XSpreadsheetDocument maSpreadsheetDoc; 86 // com.sun.star.sheet.XSpreadsheet maSheet; 87 88 // Query the number formats supplier of the spreadsheet document 89 com.sun.star.util.XNumberFormatsSupplier xNumberFormatsSupplier = 90 (com.sun.star.util.XNumberFormatsSupplier) 91 UnoRuntime.queryInterface( 92 com.sun.star.util.XNumberFormatsSupplier.class, maSpreadsheetDoc ); 93 94 // Get the number formats from the supplier 95 com.sun.star.util.XNumberFormats xNumberFormats = 96 xNumberFormatsSupplier.getNumberFormats(); 97 98 // Query the XNumberFormatTypes interface 99 com.sun.star.util.XNumberFormatTypes xNumberFormatTypes = 100 (com.sun.star.util.XNumberFormatTypes) 101 UnoRuntime.queryInterface( 102 com.sun.star.util.XNumberFormatTypes.class, xNumberFormats ); 103 104 // Get the number format index key of the default currency format, 105 // note the empty locale for default locale 106 com.sun.star.lang.Locale aLocale = new com.sun.star.lang.Locale(); 107 int nCurrencyKey = xNumberFormatTypes.getStandardFormat( 108 com.sun.star.util.NumberFormat.CURRENCY, aLocale ); 109 110 // Get cell range B3:B11 111 com.sun.star.table.XCellRange xCellRange = 112 maSheet.getCellRangeByPosition( 1, 2, 1, 10 ); 113 114 // Query the property set of the cell range 115 com.sun.star.beans.XPropertySet xCellProp = 116 (com.sun.star.beans.XPropertySet) 117 UnoRuntime.queryInterface( 118 com.sun.star.beans.XPropertySet.class, xCellRange ); 119 120 // Set number format to default currency 121 xCellProp.setPropertyValue( "NumberFormat", new Integer(nCurrencyKey) ); 122 123 // Get cell B3 124 com.sun.star.table.XCell xCell = maSheet.getCellByPosition( 1, 2 ); 125 126 // Query the property set of the cell 127 xCellProp = (com.sun.star.beans.XPropertySet) 128 UnoRuntime.queryInterface( 129 com.sun.star.beans.XPropertySet.class, xCell ); 130 131 // Get the number format index key of the cell's properties 132 int nIndexKey = ((Integer) xCellProp.getPropertyValue( "NumberFormat" )).intValue(); 133 if ( nIndexKey != nCurrencyKey ) 134 System.out.println( "Number format doesn't match!" ); 135 136 // Get the properties of the number format 137 com.sun.star.beans.XPropertySet xProp = xNumberFormats.getByKey( nIndexKey ); 138 139 // Get the format code string of the number format's properties 140 String aFormatCode = (String) xProp.getPropertyValue( "FormatString" ); 141 System.out.println( "FormatString: `" + aFormatCode + "'" ); 142 143 // Create an arbitrary format code 144 aFormatCode = "\"wonderful \"" + aFormatCode; 145 146 // Test if it's already present 147 nIndexKey = xNumberFormats.queryKey( aFormatCode, aLocale, false ); 148 149 // If not, add to number formats collection 150 if ( nIndexKey == -1 ) 151 { 152 try 153 { 154 nIndexKey = xNumberFormats.addNew( aFormatCode, aLocale ); 155 } 156 catch( com.sun.star.util.MalformedNumberFormatException ex ) 157 { 158 System.err.println( "Bad number format code: " + ex ); 159 ex.printStackTrace(); 160 nIndexKey = -1; 161 } 162 } 163 164 // Set the new format at the cell 165 if ( nIndexKey != -1 ) 166 xCellProp.setPropertyValue( "NumberFormat", new Integer(nIndexKey) ); 167 168 169 // Set column containing the example values to optimal width to show 170 // the new format of cell B3 171 com.sun.star.table.XColumnRowRange xColRowRange = 172 (com.sun.star.table.XColumnRowRange) 173 UnoRuntime.queryInterface(com.sun.star.table.XColumnRowRange.class, 174 maSheet); 175 176 com.sun.star.container.XIndexAccess xIndexAccess = 177 (com.sun.star.container.XIndexAccess) 178 UnoRuntime.queryInterface(com.sun.star.container.XIndexAccess.class, 179 xColRowRange.getColumns()); 180 181 com.sun.star.beans.XPropertySet xColPropSet = 182 (com.sun.star.beans.XPropertySet) 183 UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, 184 xIndexAccess.getByIndex(1)); 185 186 xColPropSet.setPropertyValue( "OptimalWidth", new Boolean(true) ); 187 } 188 189 // ____________________ 190 191 public Number_Formats( String[] args ) throws java.lang.Exception 192 { 193 // get the remote office context. If necessary a new office 194 // process is started 195 maOfficeContext = com.sun.star.comp.helper.Bootstrap.bootstrap(); 196 System.out.println("Connected to a running office ..."); 197 maServiceManager = maOfficeContext.getServiceManager(); 198 199 // create a new spreadsheet document 200 XComponentLoader aLoader = (XComponentLoader) UnoRuntime.queryInterface( 201 XComponentLoader.class, maServiceManager.createInstanceWithContext( 202 "com.sun.star.frame.Desktop", maOfficeContext) ); 203 204 maSpreadsheetDoc = (XSpreadsheetDocument) UnoRuntime.queryInterface( 205 XSpreadsheetDocument.class, 206 aLoader.loadComponentFromURL( "private:factory/scalc", 207 "_blank", 208 0, 209 new PropertyValue[ 0 ] ) ); 210 211 if ( !initSpreadsheet() ) 212 System.exit( 0 ); 213 } 214 215 216 // __________ private members and methods __________ 217 private final String msDataSheetName = "Data"; 218 219 private XComponentContext maOfficeContext; 220 private XMultiComponentFactory maServiceManager; 221 private XSpreadsheetDocument maSpreadsheetDoc; 222 private XSpreadsheet maSheet; // the first sheet 223 224 225 // ____________________ 226 227 /** init the first sheet 228 */ 229 private boolean initSpreadsheet() 230 { 231 boolean bOk = true; 232 XSpreadsheets aSheets = maSpreadsheetDoc.getSheets(); 233 try 234 { 235 XIndexAccess aSheetsIA = (XIndexAccess) UnoRuntime.queryInterface( XIndexAccess.class, aSheets ); 236 maSheet = (XSpreadsheet) UnoRuntime.queryInterface(XSpreadsheet.class, aSheetsIA.getByIndex( 0 )); 237 238 // enter some values in B3:B11 239 for( int iCounter=1; iCounter < 10; iCounter++ ) 240 { 241 XCell aCell = maSheet.getCellByPosition( 1, 1 + iCounter ); 242 aCell.setValue( (double) iCounter ); 243 } 244 } 245 catch( Exception ex ) 246 { 247 System.err.println( "Couldn't initialize Spreadsheet Document: " + ex ); 248 ex.printStackTrace(); 249 bOk = false; 250 } 251 return bOk; 252 } 253 } 254