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