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