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 java.util.Random;
38 
39 // base classes
40 import com.sun.star.uno.XInterface;
41 import com.sun.star.uno.UnoRuntime;
42 import com.sun.star.lang.*;
43 
44 // factory for creating components
45 import com.sun.star.comp.servicemanager.ServiceManager;
46 import com.sun.star.lang.XMultiServiceFactory;
47 import com.sun.star.bridge.XUnoUrlResolver;
48 import com.sun.star.uno.XNamingService;
49 import com.sun.star.frame.XDesktop;
50 import com.sun.star.frame.XComponentLoader;
51 
52 // property access
53 import com.sun.star.beans.*;
54 
55 // container access
56 import com.sun.star.container.*;
57 
58 // application specific classes
59 import com.sun.star.sheet.*;
60 import com.sun.star.table.*;
61 import com.sun.star.chart.*;
62 import com.sun.star.text.XText;
63 
64 import com.sun.star.document.XEmbeddedObjectSupplier;
65 import com.sun.star.frame.XModel;
66 import com.sun.star.frame.XController;
67 
68 // base graphics things
69 import com.sun.star.awt.Point;
70 import com.sun.star.awt.Size;
71 import com.sun.star.awt.Rectangle;
72 
73 // Exceptions
74 import com.sun.star.uno.RuntimeException;
75 import com.sun.star.container.NoSuchElementException;
76 import com.sun.star.beans.UnknownPropertyException;
77 import com.sun.star.lang.IndexOutOfBoundsException;
78 
79 // __________ Implementation __________
80 
81 /** Helper for accessing a calc document
82     @author Björn Milcke
83  */
84 public class CalcHelper
85 {
86     public CalcHelper( XSpreadsheetDocument aDoc )
87     {
88         maSpreadSheetDoc = aDoc;
89         initSpreadSheet();
90     }
91 
92     // ____________________
93 
94     public XSpreadsheet getChartSheet() throws RuntimeException
95     {
96         XNameAccess aSheetsNA = (XNameAccess) UnoRuntime.queryInterface(
97             XNameAccess.class, maSpreadSheetDoc.getSheets() );
98 
99         XSpreadsheet aSheet = null;
100         try
101         {
102             aSheet = (XSpreadsheet) UnoRuntime.queryInterface(
103                 XSpreadsheet.class, aSheetsNA.getByName( msChartSheetName ) );
104         }
105         catch( NoSuchElementException ex )
106         {
107             System.out.println( "Couldn't find sheet with name " + msChartSheetName + ": " + ex );
108         }
109         catch( Exception ex )
110         {}
111 
112         return aSheet;
113     }
114 
115     // ____________________
116 
117     public XSpreadsheet getDataSheet() throws RuntimeException
118     {
119         XNameAccess aSheetsNA = (XNameAccess) UnoRuntime.queryInterface(
120             XNameAccess.class, maSpreadSheetDoc.getSheets() );
121 
122         XSpreadsheet aSheet = null;
123         if( aSheetsNA != null )
124         {
125             try
126             {
127                 aSheet = (XSpreadsheet) UnoRuntime.queryInterface(
128                     XSpreadsheet.class, aSheetsNA.getByName( msDataSheetName ) );
129             }
130             catch( NoSuchElementException ex )
131             {
132                 System.out.println( "Couldn't find sheet with name " + msDataSheetName + ": " + ex );
133             }
134             catch( Exception ex )
135             {}
136         }
137 
138         return aSheet;
139     }
140 
141     // ____________________
142 
143     /** Insert a chart using the given name as name of the OLE object and the range as correspoding
144         range of data to be used for rendering.  The chart is placed in the sheet for charts at
145         position aUpperLeft extending as large as given in aExtent.
146 
147         The service name must be the name of a diagram service that can be instantiated via the
148         factory of the chart document
149      */
150     public XChartDocument insertChart(
151         String               sChartName,
152         CellRangeAddress     aRange,
153         Point                aUpperLeft,
154         Size                 aExtent,
155         String               sChartServiceName )
156     {
157         XChartDocument aResult = null;
158         XTableChartsSupplier aSheet;
159 
160         // get the sheet to insert the chart
161         try
162         {
163             aSheet = (XTableChartsSupplier) UnoRuntime.queryInterface(
164                 XTableChartsSupplier.class, getChartSheet() );
165         }
166         catch( Exception ex )
167         {
168             System.out.println( "Sheet not found" + ex );
169             return aResult;
170         }
171 
172         XTableCharts aChartCollection = aSheet.getCharts();
173         XNameAccess  aChartCollectionNA = (XNameAccess) UnoRuntime.queryInterface(
174             XNameAccess.class, aChartCollection );
175 
176         if( aChartCollectionNA != null &&
177             ! aChartCollectionNA.hasByName( sChartName ) )
178         {
179             Rectangle aRect = new Rectangle( aUpperLeft.X, aUpperLeft.Y, aExtent.Width, aExtent.Height );
180 
181             CellRangeAddress[] aAddresses = new CellRangeAddress[ 1 ];
182             aAddresses[ 0 ] = aRange;
183 
184             // first bool: ColumnHeaders
185             // second bool: RowHeaders
186             aChartCollection.addNewByName( sChartName, aRect, aAddresses, true, false );
187 
188             try
189             {
190                 XTableChart aTableChart = (XTableChart) UnoRuntime.queryInterface(
191                     XTableChart.class, aChartCollectionNA.getByName( sChartName ));
192 
193                 // the table chart is an embedded object which contains the chart document
194                 aResult = (XChartDocument) UnoRuntime.queryInterface(
195                     XChartDocument.class,
196                     ((XEmbeddedObjectSupplier) UnoRuntime.queryInterface(
197                         XEmbeddedObjectSupplier.class,
198                         aTableChart )).getEmbeddedObject());
199 
200                 // create a diagram via the factory and set this as new diagram
201                 aResult.setDiagram(
202                     (XDiagram) UnoRuntime.queryInterface(
203                         XDiagram.class,
204                         ((XMultiServiceFactory) UnoRuntime.queryInterface(
205                             XMultiServiceFactory.class,
206                             aResult )).createInstance( sChartServiceName )));
207             }
208             catch( NoSuchElementException ex )
209             {
210                 System.out.println( "Couldn't find chart with name " + sChartName + ": " + ex );
211             }
212             catch( Exception ex )
213             {}
214         }
215 
216         return aResult;
217     }
218 
219     // ____________________
220 
221     /** Fill a rectangular range with random numbers.
222         The first column has increasing values
223      */
224     public XCellRange insertRandomRange( int nColumnCount, int nRowCount )
225     {
226         XCellRange aRange = null;
227 
228         // get the sheet to insert the chart
229         try
230         {
231             XSpreadsheet aSheet = getDataSheet();
232             XCellRange aSheetRange = (XCellRange) UnoRuntime.queryInterface( XCellRange.class, aSheet );
233 
234             aRange = aSheetRange.getCellRangeByPosition(
235                 0, 0,
236                 nColumnCount - 1, nRowCount - 1 );
237 
238             int nCol, nRow;
239             double fBase  = 0.0;
240             double fRange = 10.0;
241             double fValue;
242             Random aGenerator = new Random();
243 
244 
245             for( nCol = 0; nCol < nColumnCount; nCol++ )
246             {
247                 if( 0 == nCol )
248                 {
249                     (aSheet.getCellByPosition( nCol, 0 )).setFormula( "X" );
250                 }
251                 else
252                 {
253                     (aSheet.getCellByPosition( nCol, 0 )).setFormula( "Random " + nCol );
254                 }
255 
256                 for( nRow = 1; nRow < nRowCount; nRow++ )
257                 {
258                     if( 0 == nCol )
259                     {
260                         // x values: ascending numbers
261                         fValue = (double)nRow + aGenerator.nextDouble();
262                     }
263                     else
264                     {
265                         fValue = fBase + ( aGenerator.nextGaussian() * fRange );
266                     }
267 
268                     // put value into cell
269 
270                     // note: getCellByPosition is a method at ...table.XCellRange which
271                     //       the XSpreadsheet inherits via ...sheet.XSheetCellRange
272                     (aSheet.getCellByPosition( nCol, nRow )).setValue( fValue );
273                 }
274             }
275 
276         }
277         catch( Exception ex )
278         {
279             System.out.println( "Sheet not found" + ex );
280         }
281 
282         return aRange;
283     }
284 
285     // ____________________
286 
287     public XCellRange insertFormulaRange( int nColumnCount, int nRowCount )
288     {
289         XCellRange aRange = null;
290 
291         // get the sheet to insert the chart
292         try
293         {
294             XSpreadsheet aSheet = getDataSheet();
295             XCellRange aSheetRange = (XCellRange) UnoRuntime.queryInterface( XCellRange.class, aSheet );
296 
297             aRange = aSheetRange.getCellRangeByPosition(
298                 0, 0,
299                 nColumnCount - 1, nRowCount - 1 );
300 
301             int nCol, nRow;
302             double fValue;
303             double fFactor = 2.0 * java.lang.Math.PI / (double)(nRowCount - 1);
304             String aFormula;
305 
306             // set variable factor for cos formula
307             int nFactorCol = nColumnCount + 2;
308             (aSheet.getCellByPosition( nFactorCol - 1, 0 )).setValue( 0.2 );
309 
310             XText xCellText = (XText) UnoRuntime.queryInterface( XText.class, aSheet.getCellByPosition( nFactorCol - 1, 1 ) );
311             xCellText.setString( "Change the factor above and\nwatch the changes in the chart" );
312 
313             for( nCol = 0; nCol < nColumnCount; nCol++ )
314             {
315                 for( nRow = 0; nRow < nRowCount; nRow++ )
316                 {
317                     if( 0 == nCol )
318                     {
319                         // x values: ascending numbers
320                         fValue = (double)nRow * fFactor;
321                         (aSheet.getCellByPosition( nCol, nRow )).setValue( fValue );
322                     }
323                     else
324                     {
325                         aFormula = new String( "=" );
326                         if( nCol % 2 == 0 )
327                             aFormula += "SIN";
328                         else
329                             aFormula += "COS";
330                         aFormula += "(INDIRECT(ADDRESS(" + (nRow + 1) + ";1)))+RAND()*INDIRECT(ADDRESS(1;" + nFactorCol + "))";
331                         (aSheet.getCellByPosition( nCol, nRow )).setFormula( aFormula );
332                     }
333                 }
334             }
335 
336         }
337         catch( Exception ex )
338         {
339             System.out.println( "Sheet not found" + ex );
340         }
341 
342         return aRange;
343     }
344 
345     // ____________________
346 
347     /** Bring the sheet containing charts visually to the foreground
348      */
349     public void raiseChartSheet()
350     {
351         ((XSpreadsheetView) UnoRuntime.queryInterface(
352             XSpreadsheetView.class,
353             ((XModel) UnoRuntime.queryInterface(
354                 XModel.class,
355                 maSpreadSheetDoc )).getCurrentController()) ).setActiveSheet( getChartSheet() );
356     }
357 
358 
359     // __________ private members and methods __________
360 
361     private final String  msDataSheetName  = "Data";
362     private final String  msChartSheetName = "Chart";
363 
364     private XSpreadsheetDocument   maSpreadSheetDoc;
365 
366 
367     // ____________________
368 
369     /** create two sheets, one for data and one for charts in the document
370      */
371     private void initSpreadSheet()
372     {
373         if( maSpreadSheetDoc != null )
374         {
375             XSpreadsheets  aSheets    = maSpreadSheetDoc.getSheets();
376             XNameContainer aSheetsNC  = (XNameContainer)  UnoRuntime.queryInterface(
377                 XNameContainer.class, aSheets );
378             XIndexAccess   aSheetsIA  = (XIndexAccess)    UnoRuntime.queryInterface(
379                 XIndexAccess.class, aSheets );
380 
381             if( aSheets   != null &&
382                 aSheetsNC != null &&
383                 aSheetsIA != null )
384             {
385                 try
386                 {
387                     // remove all sheets except one
388                     for( int i = aSheetsIA.getCount() - 1; i > 0; i-- )
389                     {
390                         aSheetsNC.removeByName(
391                             ( (XNamed) UnoRuntime.queryInterface(
392                                 XNamed.class, aSheetsIA.getByIndex( i ) )).getName() );
393                     }
394 
395                     XNamed aFirstSheet = (XNamed) UnoRuntime.queryInterface(
396                         XNamed.class,
397                         aSheetsIA.getByIndex( 0 ));
398 
399                     // first sheet becomes data sheet
400                     aFirstSheet.setName( msDataSheetName );
401 
402                     // second sheet becomes chart sheet
403                     aSheets.insertNewByName( msChartSheetName, (short)1 );
404                 }
405                 catch( Exception ex )
406                 {
407                     System.out.println( "Couldn't initialize Spreadsheet Document: " + ex );
408                 }
409             }
410         }
411     }
412 }
413