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