Update ooo320-m1
[ooovba.git] / odk / examples / DevelopersGuide / Spreadsheet / SpreadsheetDocHelper.java
blob882580b008897a28a019e5a06c540dae0937c23b
1 /*************************************************************************
3 * $RCSfile: SpreadsheetDocHelper.java,v $
5 * $Revision: 1.5 $
7 * last change: $Author: rt $ $Date: 2005-01-31 16:55:12 $
9 * The Contents of this file are made available subject to the terms of
10 * the BSD license.
12 * Copyright (c) 2003 by Sun Microsystems, Inc.
13 * All rights reserved.
15 * Redistribution and use in source and binary forms, with or without
16 * modification, are permitted provided that the following conditions
17 * are met:
18 * 1. Redistributions of source code must retain the above copyright
19 * notice, this list of conditions and the following disclaimer.
20 * 2. Redistributions in binary form must reproduce the above copyright
21 * notice, this list of conditions and the following disclaimer in the
22 * documentation and/or other materials provided with the distribution.
23 * 3. Neither the name of Sun Microsystems, Inc. nor the names of its
24 * contributors may be used to endorse or promote products derived
25 * from this software without specific prior written permission.
27 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
28 * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
29 * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS
30 * FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE
31 * COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,
32 * INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
33 * BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS
34 * OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
35 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR
36 * TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE
37 * USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
39 *************************************************************************/
41 import com.sun.star.comp.servicemanager.ServiceManager;
42 import com.sun.star.bridge.XUnoUrlResolver;
43 import com.sun.star.uno.XNamingService;
44 import com.sun.star.frame.XDesktop;
45 import com.sun.star.frame.XComponentLoader;
46 import com.sun.star.lang.XMultiServiceFactory;
47 import com.sun.star.lang.XComponent;
49 import com.sun.star.uno.UnoRuntime;
50 import com.sun.star.uno.RuntimeException;
52 // __________ implementation ____________________________________
54 /** This is a helper class for the spreadsheet and table samples.
55 It connects to a running office and creates a spreadsheet document.
56 Additionally it contains various helper functions.
58 public class SpreadsheetDocHelper
61 // __ private members ___________________________________________
63 private final String msDataSheetName = "Data";
65 private com.sun.star.uno.XComponentContext mxRemoteContext;
66 private com.sun.star.lang.XMultiComponentFactory mxRemoteServiceManager;
67 // private com.sun.star.lang.XMultiServiceFactory mxMSFactory;
68 private com.sun.star.sheet.XSpreadsheetDocument mxDocument;
70 // ________________________________________________________________
72 public SpreadsheetDocHelper( String[] args )
74 // Connect to a running office and get the service manager
75 connect();
77 // Create a new spreadsheet document
78 try
80 mxDocument = initDocument();
82 catch (Exception ex)
84 System.err.println( "Couldn't create document: " + ex );
85 System.err.println( "Error: Couldn't create Document\nException Message = "
86 + ex.getMessage());
87 ex.printStackTrace();
88 System.exit( 1 );
92 // __ helper methods ____________________________________________
94 /** Returns the service manager of the connected office.
95 @return XMultiComponentFactory interface of the service manager. */
96 public com.sun.star.lang.XMultiComponentFactory getServiceManager()
98 return mxRemoteServiceManager;
101 /** Returns the component context of the connected office
102 @return XComponentContext interface of the context. */
103 public com.sun.star.uno.XComponentContext getContext()
105 return mxRemoteContext;
108 /** Returns the whole spreadsheet document.
109 @return XSpreadsheetDocument interface of the document. */
110 public com.sun.star.sheet.XSpreadsheetDocument getDocument()
112 return mxDocument;
115 /** Returns the spreadsheet with the specified index (0-based).
116 @param nIndex The index of the sheet.
117 @return XSpreadsheet interface of the sheet. */
118 public com.sun.star.sheet.XSpreadsheet getSpreadsheet( int nIndex )
120 // Collection of sheets
121 com.sun.star.sheet.XSpreadsheets xSheets = mxDocument.getSheets();
122 com.sun.star.sheet.XSpreadsheet xSheet = null;
125 com.sun.star.container.XIndexAccess xSheetsIA =
126 (com.sun.star.container.XIndexAccess)UnoRuntime.queryInterface(
127 com.sun.star.container.XIndexAccess.class, xSheets );
128 xSheet = (com.sun.star.sheet.XSpreadsheet) UnoRuntime.queryInterface(
129 com.sun.star.sheet.XSpreadsheet.class, xSheetsIA.getByIndex(nIndex));
131 catch (Exception ex)
133 System.err.println( "Error: caught exception in getSpreadsheet()!\nException Message = "
134 + ex.getMessage());
135 ex.printStackTrace();
137 return xSheet;
140 /** Inserts a new empty spreadsheet with the specified name.
141 @param aName The name of the new sheet.
142 @param nIndex The insertion index.
143 @return The XSpreadsheet interface of the new sheet. */
144 public com.sun.star.sheet.XSpreadsheet insertSpreadsheet(
145 String aName, short nIndex )
147 // Collection of sheets
148 com.sun.star.sheet.XSpreadsheets xSheets = mxDocument.getSheets();
149 com.sun.star.sheet.XSpreadsheet xSheet = null;
152 xSheets.insertNewByName( aName, nIndex );
153 xSheet = (com.sun.star.sheet.XSpreadsheet)
154 UnoRuntime.queryInterface(com.sun.star.sheet.XSpreadsheet.class,
155 xSheets.getByName( aName ));
157 catch (Exception ex)
159 System.err.println( "Error: caught exception in insertSpreadsheet()!\nException Message = "
160 + ex.getMessage());
161 ex.printStackTrace();
163 return xSheet;
166 // ________________________________________________________________
167 // Methods to fill values into cells.
169 /** Writes a double value into a spreadsheet.
170 @param xSheet The XSpreadsheet interface of the spreadsheet.
171 @param aCellName The address of the cell (or a named range).
172 @param fValue The value to write into the cell. */
173 public void setValue(
174 com.sun.star.sheet.XSpreadsheet xSheet,
175 String aCellName,
176 double fValue ) throws RuntimeException, Exception
178 xSheet.getCellRangeByName( aCellName ).getCellByPosition( 0, 0 ).setValue( fValue );
181 /** Writes a formula into a spreadsheet.
182 @param xSheet The XSpreadsheet interface of the spreadsheet.
183 @param aCellName The address of the cell (or a named range).
184 @param aFormula The formula to write into the cell. */
185 public void setFormula(
186 com.sun.star.sheet.XSpreadsheet xSheet,
187 String aCellName,
188 String aFormula ) throws RuntimeException, Exception
190 xSheet.getCellRangeByName( aCellName ).getCellByPosition( 0, 0 ).setFormula( aFormula );
193 /** Writes a date with standard date format into a spreadsheet.
194 @param xSheet The XSpreadsheet interface of the spreadsheet.
195 @param aCellName The address of the cell (or a named range).
196 @param nDay The day of the date.
197 @param nMonth The month of the date.
198 @param nYear The year of the date. */
199 public void setDate(
200 com.sun.star.sheet.XSpreadsheet xSheet,
201 String aCellName,
202 int nDay, int nMonth, int nYear ) throws RuntimeException, Exception
204 // Set the date value.
205 com.sun.star.table.XCell xCell = xSheet.getCellRangeByName( aCellName ).getCellByPosition( 0, 0 );
206 String aDateStr = nMonth + "/" + nDay + "/" + nYear;
207 xCell.setFormula( aDateStr );
209 // Set standard date format.
210 com.sun.star.util.XNumberFormatsSupplier xFormatsSupplier =
211 (com.sun.star.util.XNumberFormatsSupplier) UnoRuntime.queryInterface(
212 com.sun.star.util.XNumberFormatsSupplier.class, getDocument() );
213 com.sun.star.util.XNumberFormatTypes xFormatTypes =
214 (com.sun.star.util.XNumberFormatTypes) UnoRuntime.queryInterface(
215 com.sun.star.util.XNumberFormatTypes.class, xFormatsSupplier.getNumberFormats() );
216 int nFormat = xFormatTypes.getStandardFormat(
217 com.sun.star.util.NumberFormat.DATE, new com.sun.star.lang.Locale() );
219 com.sun.star.beans.XPropertySet xPropSet = (com.sun.star.beans.XPropertySet)
220 UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xCell );
221 xPropSet.setPropertyValue( "NumberFormat", new Integer( nFormat ) );
224 /** Draws a colored border around the range and writes the headline in the
225 first cell.
226 @param xSheet The XSpreadsheet interface of the spreadsheet.
227 @param aRange The address of the cell range (or a named range).
228 @param aHeadline The headline text. */
229 public void prepareRange(
230 com.sun.star.sheet.XSpreadsheet xSheet,
231 String aRange, String aHeadline ) throws RuntimeException, Exception
233 com.sun.star.beans.XPropertySet xPropSet = null;
234 com.sun.star.table.XCellRange xCellRange = null;
236 // draw border
237 xCellRange = xSheet.getCellRangeByName( aRange );
238 xPropSet = (com.sun.star.beans.XPropertySet)
239 UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xCellRange );
240 com.sun.star.table.BorderLine aLine = new com.sun.star.table.BorderLine();
241 aLine.Color = 0x99CCFF;
242 aLine.InnerLineWidth = aLine.LineDistance = 0;
243 aLine.OuterLineWidth = 100;
244 com.sun.star.table.TableBorder aBorder = new com.sun.star.table.TableBorder();
245 aBorder.TopLine = aBorder.BottomLine = aBorder.LeftLine = aBorder.RightLine = aLine;
246 aBorder.IsTopLineValid = aBorder.IsBottomLineValid = true;
247 aBorder.IsLeftLineValid = aBorder.IsRightLineValid = true;
248 xPropSet.setPropertyValue( "TableBorder", aBorder );
250 // draw headline
251 com.sun.star.sheet.XCellRangeAddressable xAddr = (com.sun.star.sheet.XCellRangeAddressable)
252 UnoRuntime.queryInterface( com.sun.star.sheet.XCellRangeAddressable.class, xCellRange );
253 com.sun.star.table.CellRangeAddress aAddr = xAddr.getRangeAddress();
255 xCellRange = xSheet.getCellRangeByPosition(
256 aAddr.StartColumn, aAddr.StartRow, aAddr.EndColumn, aAddr.StartRow );
257 xPropSet = (com.sun.star.beans.XPropertySet)
258 UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xCellRange );
259 xPropSet.setPropertyValue( "CellBackColor", new Integer( 0x99CCFF ) );
260 // write headline
261 com.sun.star.table.XCell xCell = xCellRange.getCellByPosition( 0, 0 );
262 xCell.setFormula( aHeadline );
263 xPropSet = (com.sun.star.beans.XPropertySet)
264 UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xCell );
265 xPropSet.setPropertyValue( "CharColor", new Integer( 0x003399 ) );
266 xPropSet.setPropertyValue( "CharWeight", new Float( com.sun.star.awt.FontWeight.BOLD ) );
269 // ________________________________________________________________
270 // Methods to create cell addresses and range addresses.
272 /** Creates a com.sun.star.table.CellAddress and initializes it
273 with the given range.
274 @param xSheet The XSpreadsheet interface of the spreadsheet.
275 @param aCell The address of the cell (or a named cell). */
276 public com.sun.star.table.CellAddress createCellAddress(
277 com.sun.star.sheet.XSpreadsheet xSheet,
278 String aCell ) throws RuntimeException, Exception
280 com.sun.star.sheet.XCellAddressable xAddr = (com.sun.star.sheet.XCellAddressable)
281 UnoRuntime.queryInterface( com.sun.star.sheet.XCellAddressable.class,
282 xSheet.getCellRangeByName( aCell ).getCellByPosition( 0, 0 ) );
283 return xAddr.getCellAddress();
286 /** Creates a com.sun.star.table.CellRangeAddress and initializes
287 it with the given range.
288 @param xSheet The XSpreadsheet interface of the spreadsheet.
289 @param aRange The address of the cell range (or a named range). */
290 public com.sun.star.table.CellRangeAddress createCellRangeAddress(
291 com.sun.star.sheet.XSpreadsheet xSheet, String aRange )
293 com.sun.star.sheet.XCellRangeAddressable xAddr = (com.sun.star.sheet.XCellRangeAddressable)
294 UnoRuntime.queryInterface( com.sun.star.sheet.XCellRangeAddressable.class,
295 xSheet.getCellRangeByName( aRange ) );
296 return xAddr.getRangeAddress();
299 // ________________________________________________________________
300 // Methods to convert cell addresses and range addresses to strings.
302 /** Returns the text address of the cell.
303 @param nColumn The column index.
304 @param nRow The row index.
305 @return A string containing the cell address. */
306 public String getCellAddressString( int nColumn, int nRow )
308 String aStr = "";
309 if (nColumn > 25)
310 aStr += (char) ('A' + nColumn / 26 - 1);
311 aStr += (char) ('A' + nColumn % 26);
312 aStr += (nRow + 1);
313 return aStr;
316 /** Returns the text address of the cell range.
317 @param aCellRange The cell range address.
318 @return A string containing the cell range address. */
319 public String getCellRangeAddressString(
320 com.sun.star.table.CellRangeAddress aCellRange )
322 return
323 getCellAddressString( aCellRange.StartColumn, aCellRange.StartRow )
324 + ":"
325 + getCellAddressString( aCellRange.EndColumn, aCellRange.EndRow );
328 /** Returns the text address of the cell range.
329 @param xCellRange The XSheetCellRange interface of the cell range.
330 @param bWithSheet true = Include sheet name.
331 @return A string containing the cell range address. */
332 public String getCellRangeAddressString(
333 com.sun.star.sheet.XSheetCellRange xCellRange,
334 boolean bWithSheet )
336 String aStr = "";
337 if (bWithSheet)
339 com.sun.star.sheet.XSpreadsheet xSheet = xCellRange.getSpreadsheet();
340 com.sun.star.container.XNamed xNamed = (com.sun.star.container.XNamed)
341 UnoRuntime.queryInterface( com.sun.star.container.XNamed.class, xSheet );
342 aStr += xNamed.getName() + ".";
344 com.sun.star.sheet.XCellRangeAddressable xAddr = (com.sun.star.sheet.XCellRangeAddressable)
345 UnoRuntime.queryInterface( com.sun.star.sheet.XCellRangeAddressable.class, xCellRange );
346 aStr += getCellRangeAddressString( xAddr.getRangeAddress() );
347 return aStr;
350 /** Returns a list of addresses of all cell ranges contained in the collection.
351 @param xRangesIA The XIndexAccess interface of the collection.
352 @return A string containing the cell range address list. */
353 public String getCellRangeListString(
354 com.sun.star.container.XIndexAccess xRangesIA ) throws RuntimeException, Exception
356 String aStr = "";
357 int nCount = xRangesIA.getCount();
358 for (int nIndex = 0; nIndex < nCount; ++nIndex)
360 if (nIndex > 0)
361 aStr += " ";
362 Object aRangeObj = xRangesIA.getByIndex( nIndex );
363 com.sun.star.sheet.XSheetCellRange xCellRange = (com.sun.star.sheet.XSheetCellRange)
364 UnoRuntime.queryInterface( com.sun.star.sheet.XSheetCellRange.class, aRangeObj );
365 aStr += getCellRangeAddressString( xCellRange, false );
367 return aStr;
370 // ________________________________________________________________
372 // Connect to a running office that is accepting connections.
373 private void connect()
375 if (mxRemoteContext == null && mxRemoteServiceManager == null) {
376 try {
377 // First step: get the remote office component context
378 mxRemoteContext = com.sun.star.comp.helper.Bootstrap.bootstrap();
379 System.out.println("Connected to a running office ...");
381 mxRemoteServiceManager = mxRemoteContext.getServiceManager();
383 catch( Exception e) {
384 System.err.println("ERROR: can't get a component context from a running office ...");
385 e.printStackTrace();
386 System.exit(1);
391 /** Creates an empty spreadsheet document.
392 @return The XSpreadsheetDocument interface of the document. */
393 private com.sun.star.sheet.XSpreadsheetDocument initDocument()
394 throws RuntimeException, Exception
396 XComponentLoader aLoader = (XComponentLoader)
397 UnoRuntime.queryInterface(
398 XComponentLoader.class,
399 mxRemoteServiceManager.createInstanceWithContext(
400 "com.sun.star.frame.Desktop", mxRemoteContext));
402 XComponent xComponent = aLoader.loadComponentFromURL(
403 "private:factory/scalc", "_blank", 0,
404 new com.sun.star.beans.PropertyValue[0] );
406 return (com.sun.star.sheet.XSpreadsheetDocument)UnoRuntime.queryInterface(
407 com.sun.star.sheet.XSpreadsheetDocument.class, xComponent );
410 // ________________________________________________________________