1 /*************************************************************************
3 * $RCSfile: CalcHelper.java,v $
7 * last change: $Author: rt $ $Date: 2005-01-31 16:09:40 $
9 * The Contents of this file are made available subject to the terms of
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
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 // __________ Imports __________
43 import java
.util
.Random
;
46 import com
.sun
.star
.uno
.XInterface
;
47 import com
.sun
.star
.uno
.UnoRuntime
;
48 import com
.sun
.star
.lang
.*;
50 // factory for creating components
51 import com
.sun
.star
.comp
.servicemanager
.ServiceManager
;
52 import com
.sun
.star
.lang
.XMultiServiceFactory
;
53 import com
.sun
.star
.bridge
.XUnoUrlResolver
;
54 import com
.sun
.star
.uno
.XNamingService
;
55 import com
.sun
.star
.frame
.XDesktop
;
56 import com
.sun
.star
.frame
.XComponentLoader
;
59 import com
.sun
.star
.beans
.*;
62 import com
.sun
.star
.container
.*;
64 // application specific classes
65 import com
.sun
.star
.sheet
.*;
66 import com
.sun
.star
.table
.*;
67 import com
.sun
.star
.chart
.*;
68 import com
.sun
.star
.text
.XText
;
70 import com
.sun
.star
.document
.XEmbeddedObjectSupplier
;
71 import com
.sun
.star
.frame
.XModel
;
72 import com
.sun
.star
.frame
.XController
;
74 // base graphics things
75 import com
.sun
.star
.awt
.Point
;
76 import com
.sun
.star
.awt
.Size
;
77 import com
.sun
.star
.awt
.Rectangle
;
80 import com
.sun
.star
.uno
.RuntimeException
;
81 import com
.sun
.star
.container
.NoSuchElementException
;
82 import com
.sun
.star
.beans
.UnknownPropertyException
;
83 import com
.sun
.star
.lang
.IndexOutOfBoundsException
;
85 // __________ Implementation __________
87 /** Helper for accessing a calc document
88 @author Björn Milcke
90 public class CalcHelper
92 public CalcHelper( XSpreadsheetDocument aDoc
)
94 maSpreadSheetDoc
= aDoc
;
98 // ____________________
100 public XSpreadsheet
getChartSheet() throws RuntimeException
102 XNameAccess aSheetsNA
= (XNameAccess
) UnoRuntime
.queryInterface(
103 XNameAccess
.class, maSpreadSheetDoc
.getSheets() );
105 XSpreadsheet aSheet
= null;
108 aSheet
= (XSpreadsheet
) UnoRuntime
.queryInterface(
109 XSpreadsheet
.class, aSheetsNA
.getByName( msChartSheetName
) );
111 catch( NoSuchElementException ex
)
113 System
.out
.println( "Couldn't find sheet with name " + msChartSheetName
+ ": " + ex
);
115 catch( Exception ex
)
121 // ____________________
123 public XSpreadsheet
getDataSheet() throws RuntimeException
125 XNameAccess aSheetsNA
= (XNameAccess
) UnoRuntime
.queryInterface(
126 XNameAccess
.class, maSpreadSheetDoc
.getSheets() );
128 XSpreadsheet aSheet
= null;
129 if( aSheetsNA
!= null )
133 aSheet
= (XSpreadsheet
) UnoRuntime
.queryInterface(
134 XSpreadsheet
.class, aSheetsNA
.getByName( msDataSheetName
) );
136 catch( NoSuchElementException ex
)
138 System
.out
.println( "Couldn't find sheet with name " + msDataSheetName
+ ": " + ex
);
140 catch( Exception ex
)
147 // ____________________
149 /** Insert a chart using the given name as name of the OLE object and the range as correspoding
150 range of data to be used for rendering. The chart is placed in the sheet for charts at
151 position aUpperLeft extending as large as given in aExtent.
153 The service name must be the name of a diagram service that can be instantiated via the
154 factory of the chart document
156 public XChartDocument
insertChart(
158 CellRangeAddress aRange
,
161 String sChartServiceName
)
163 XChartDocument aResult
= null;
164 XTableChartsSupplier aSheet
;
166 // get the sheet to insert the chart
169 aSheet
= (XTableChartsSupplier
) UnoRuntime
.queryInterface(
170 XTableChartsSupplier
.class, getChartSheet() );
172 catch( Exception ex
)
174 System
.out
.println( "Sheet not found" + ex
);
178 XTableCharts aChartCollection
= aSheet
.getCharts();
179 XNameAccess aChartCollectionNA
= (XNameAccess
) UnoRuntime
.queryInterface(
180 XNameAccess
.class, aChartCollection
);
182 if( aChartCollectionNA
!= null &&
183 ! aChartCollectionNA
.hasByName( sChartName
) )
185 Rectangle aRect
= new Rectangle( aUpperLeft
.X
, aUpperLeft
.Y
, aExtent
.Width
, aExtent
.Height
);
187 CellRangeAddress
[] aAddresses
= new CellRangeAddress
[ 1 ];
188 aAddresses
[ 0 ] = aRange
;
190 // first bool: ColumnHeaders
191 // second bool: RowHeaders
192 aChartCollection
.addNewByName( sChartName
, aRect
, aAddresses
, true, false );
196 XTableChart aTableChart
= (XTableChart
) UnoRuntime
.queryInterface(
197 XTableChart
.class, aChartCollectionNA
.getByName( sChartName
));
199 // the table chart is an embedded object which contains the chart document
200 aResult
= (XChartDocument
) UnoRuntime
.queryInterface(
201 XChartDocument
.class,
202 ((XEmbeddedObjectSupplier
) UnoRuntime
.queryInterface(
203 XEmbeddedObjectSupplier
.class,
204 aTableChart
)).getEmbeddedObject());
206 // create a diagram via the factory and set this as new diagram
208 (XDiagram
) UnoRuntime
.queryInterface(
210 ((XMultiServiceFactory
) UnoRuntime
.queryInterface(
211 XMultiServiceFactory
.class,
212 aResult
)).createInstance( sChartServiceName
)));
214 catch( NoSuchElementException ex
)
216 System
.out
.println( "Couldn't find chart with name " + sChartName
+ ": " + ex
);
218 catch( Exception ex
)
225 // ____________________
227 /** Fill a rectangular range with random numbers.
228 The first column has increasing values
230 public XCellRange
insertRandomRange( int nColumnCount
, int nRowCount
)
232 XCellRange aRange
= null;
234 // get the sheet to insert the chart
237 XSpreadsheet aSheet
= getDataSheet();
238 XCellRange aSheetRange
= (XCellRange
) UnoRuntime
.queryInterface( XCellRange
.class, aSheet
);
240 aRange
= aSheetRange
.getCellRangeByPosition(
242 nColumnCount
- 1, nRowCount
- 1 );
246 double fRange
= 10.0;
248 Random aGenerator
= new Random();
251 for( nCol
= 0; nCol
< nColumnCount
; nCol
++ )
255 (aSheet
.getCellByPosition( nCol
, 0 )).setFormula( "X" );
259 (aSheet
.getCellByPosition( nCol
, 0 )).setFormula( "Random " + nCol
);
262 for( nRow
= 1; nRow
< nRowCount
; nRow
++ )
266 // x values: ascending numbers
267 fValue
= (double)nRow
+ aGenerator
.nextDouble();
271 fValue
= fBase
+ ( aGenerator
.nextGaussian() * fRange
);
274 // put value into cell
276 // note: getCellByPosition is a method at ...table.XCellRange which
277 // the XSpreadsheet inherits via ...sheet.XSheetCellRange
278 (aSheet
.getCellByPosition( nCol
, nRow
)).setValue( fValue
);
283 catch( Exception ex
)
285 System
.out
.println( "Sheet not found" + ex
);
291 // ____________________
293 public XCellRange
insertFormulaRange( int nColumnCount
, int nRowCount
)
295 XCellRange aRange
= null;
297 // get the sheet to insert the chart
300 XSpreadsheet aSheet
= getDataSheet();
301 XCellRange aSheetRange
= (XCellRange
) UnoRuntime
.queryInterface( XCellRange
.class, aSheet
);
303 aRange
= aSheetRange
.getCellRangeByPosition(
305 nColumnCount
- 1, nRowCount
- 1 );
309 double fFactor
= 2.0 * java
.lang
.Math
.PI
/ (double)(nRowCount
- 1);
312 // set variable factor for cos formula
313 int nFactorCol
= nColumnCount
+ 2;
314 (aSheet
.getCellByPosition( nFactorCol
- 1, 0 )).setValue( 0.2 );
316 XText xCellText
= (XText
) UnoRuntime
.queryInterface( XText
.class, aSheet
.getCellByPosition( nFactorCol
- 1, 1 ) );
317 xCellText
.setString( "Change the factor above and\nwatch the changes in the chart" );
319 for( nCol
= 0; nCol
< nColumnCount
; nCol
++ )
321 for( nRow
= 0; nRow
< nRowCount
; nRow
++ )
325 // x values: ascending numbers
326 fValue
= (double)nRow
* fFactor
;
327 (aSheet
.getCellByPosition( nCol
, nRow
)).setValue( fValue
);
331 aFormula
= new String( "=" );
336 aFormula
+= "(INDIRECT(ADDRESS(" + (nRow
+ 1) + ";1)))+RAND()*INDIRECT(ADDRESS(1;" + nFactorCol
+ "))";
337 (aSheet
.getCellByPosition( nCol
, nRow
)).setFormula( aFormula
);
343 catch( Exception ex
)
345 System
.out
.println( "Sheet not found" + ex
);
351 // ____________________
353 /** Bring the sheet containing charts visually to the foreground
355 public void raiseChartSheet()
357 ((XSpreadsheetView
) UnoRuntime
.queryInterface(
358 XSpreadsheetView
.class,
359 ((XModel
) UnoRuntime
.queryInterface(
361 maSpreadSheetDoc
)).getCurrentController()) ).setActiveSheet( getChartSheet() );
365 // __________ private members and methods __________
367 private final String msDataSheetName
= "Data";
368 private final String msChartSheetName
= "Chart";
370 private XSpreadsheetDocument maSpreadSheetDoc
;
373 // ____________________
375 /** create two sheets, one for data and one for charts in the document
377 private void initSpreadSheet()
379 if( maSpreadSheetDoc
!= null )
381 XSpreadsheets aSheets
= maSpreadSheetDoc
.getSheets();
382 XNameContainer aSheetsNC
= (XNameContainer
) UnoRuntime
.queryInterface(
383 XNameContainer
.class, aSheets
);
384 XIndexAccess aSheetsIA
= (XIndexAccess
) UnoRuntime
.queryInterface(
385 XIndexAccess
.class, aSheets
);
387 if( aSheets
!= null &&
393 // remove all sheets except one
394 for( int i
= aSheetsIA
.getCount() - 1; i
> 0; i
-- )
396 aSheetsNC
.removeByName(
397 ( (XNamed
) UnoRuntime
.queryInterface(
398 XNamed
.class, aSheetsIA
.getByIndex( i
) )).getName() );
401 XNamed aFirstSheet
= (XNamed
) UnoRuntime
.queryInterface(
403 aSheetsIA
.getByIndex( 0 ));
405 // first sheet becomes data sheet
406 aFirstSheet
.setName( msDataSheetName
);
408 // second sheet becomes chart sheet
409 aSheets
.insertNewByName( msChartSheetName
, (short)1 );
411 catch( Exception ex
)
413 System
.out
.println( "Couldn't initialize Spreadsheet Document: " + ex
);