| /************************************************************** | |
| * | |
| * Licensed to the Apache Software Foundation (ASF) under one | |
| * or more contributor license agreements. See the NOTICE file | |
| * distributed with this work for additional information | |
| * regarding copyright ownership. The ASF licenses this file | |
| * to you under the Apache License, Version 2.0 (the | |
| * "License"); you may not use this file except in compliance | |
| * with the License. You may obtain a copy of the License at | |
| * | |
| * http://www.apache.org/licenses/LICENSE-2.0 | |
| * | |
| * Unless required by applicable law or agreed to in writing, | |
| * software distributed under the License is distributed on an | |
| * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY | |
| * KIND, either express or implied. See the License for the | |
| * specific language governing permissions and limitations | |
| * under the License. | |
| * | |
| *************************************************************/ | |
| // __________ Imports __________ | |
| import java.util.Random; | |
| // base classes | |
| import com.sun.star.uno.XInterface; | |
| import com.sun.star.uno.UnoRuntime; | |
| import com.sun.star.lang.*; | |
| // factory for creating components | |
| import com.sun.star.comp.servicemanager.ServiceManager; | |
| import com.sun.star.lang.XMultiServiceFactory; | |
| import com.sun.star.bridge.XUnoUrlResolver; | |
| import com.sun.star.uno.XNamingService; | |
| import com.sun.star.frame.XDesktop; | |
| import com.sun.star.frame.XComponentLoader; | |
| // property access | |
| import com.sun.star.beans.*; | |
| // container access | |
| import com.sun.star.container.*; | |
| // application specific classes | |
| import com.sun.star.sheet.*; | |
| import com.sun.star.table.*; | |
| import com.sun.star.chart.*; | |
| import com.sun.star.text.XText; | |
| import com.sun.star.document.XEmbeddedObjectSupplier; | |
| import com.sun.star.frame.XModel; | |
| import com.sun.star.frame.XController; | |
| // base graphics things | |
| import com.sun.star.awt.Point; | |
| import com.sun.star.awt.Size; | |
| import com.sun.star.awt.Rectangle; | |
| // Exceptions | |
| import com.sun.star.uno.RuntimeException; | |
| import com.sun.star.container.NoSuchElementException; | |
| import com.sun.star.beans.UnknownPropertyException; | |
| import com.sun.star.lang.IndexOutOfBoundsException; | |
| // __________ Implementation __________ | |
| /** Helper for accessing a calc document | |
| @author Björn Milcke | |
| */ | |
| public class CalcHelper | |
| { | |
| public CalcHelper( XSpreadsheetDocument aDoc ) | |
| { | |
| maSpreadSheetDoc = aDoc; | |
| initSpreadSheet(); | |
| } | |
| // ____________________ | |
| public XSpreadsheet getChartSheet() throws RuntimeException | |
| { | |
| XNameAccess aSheetsNA = UnoRuntime.queryInterface( | |
| XNameAccess.class, maSpreadSheetDoc.getSheets() ); | |
| XSpreadsheet aSheet = null; | |
| try | |
| { | |
| aSheet = UnoRuntime.queryInterface( | |
| XSpreadsheet.class, aSheetsNA.getByName( msChartSheetName ) ); | |
| } | |
| catch( NoSuchElementException ex ) | |
| { | |
| System.out.println( "Couldn't find sheet with name " + msChartSheetName + ": " + ex ); | |
| } | |
| catch( Exception ex ) | |
| {} | |
| return aSheet; | |
| } | |
| // ____________________ | |
| public XSpreadsheet getDataSheet() throws RuntimeException | |
| { | |
| XNameAccess aSheetsNA = UnoRuntime.queryInterface( | |
| XNameAccess.class, maSpreadSheetDoc.getSheets() ); | |
| XSpreadsheet aSheet = null; | |
| if( aSheetsNA != null ) | |
| { | |
| try | |
| { | |
| aSheet = UnoRuntime.queryInterface( | |
| XSpreadsheet.class, aSheetsNA.getByName( msDataSheetName ) ); | |
| } | |
| catch( NoSuchElementException ex ) | |
| { | |
| System.out.println( "Couldn't find sheet with name " + msDataSheetName + ": " + ex ); | |
| } | |
| catch( Exception ex ) | |
| {} | |
| } | |
| return aSheet; | |
| } | |
| // ____________________ | |
| /** Insert a chart using the given name as name of the OLE object and the range as corresponding | |
| range of data to be used for rendering. The chart is placed in the sheet for charts at | |
| position aUpperLeft extending as large as given in aExtent. | |
| The service name must be the name of a diagram service that can be instantiated via the | |
| factory of the chart document | |
| */ | |
| public XChartDocument insertChart( | |
| String sChartName, | |
| CellRangeAddress aRange, | |
| Point aUpperLeft, | |
| Size aExtent, | |
| String sChartServiceName ) | |
| { | |
| XChartDocument aResult = null; | |
| XTableChartsSupplier aSheet; | |
| // get the sheet to insert the chart | |
| try | |
| { | |
| aSheet = UnoRuntime.queryInterface( | |
| XTableChartsSupplier.class, getChartSheet() ); | |
| } | |
| catch( Exception ex ) | |
| { | |
| System.out.println( "Sheet not found" + ex ); | |
| return aResult; | |
| } | |
| XTableCharts aChartCollection = aSheet.getCharts(); | |
| XNameAccess aChartCollectionNA = UnoRuntime.queryInterface( | |
| XNameAccess.class, aChartCollection ); | |
| if( aChartCollectionNA != null && | |
| ! aChartCollectionNA.hasByName( sChartName ) ) | |
| { | |
| Rectangle aRect = new Rectangle( aUpperLeft.X, aUpperLeft.Y, aExtent.Width, aExtent.Height ); | |
| CellRangeAddress[] aAddresses = new CellRangeAddress[ 1 ]; | |
| aAddresses[ 0 ] = aRange; | |
| // first bool: ColumnHeaders | |
| // second bool: RowHeaders | |
| aChartCollection.addNewByName( sChartName, aRect, aAddresses, true, false ); | |
| try | |
| { | |
| XTableChart aTableChart = UnoRuntime.queryInterface( | |
| XTableChart.class, aChartCollectionNA.getByName( sChartName )); | |
| // the table chart is an embedded object which contains the chart document | |
| aResult = UnoRuntime.queryInterface( | |
| XChartDocument.class, | |
| (UnoRuntime.queryInterface( | |
| XEmbeddedObjectSupplier.class, | |
| aTableChart )).getEmbeddedObject()); | |
| // create a diagram via the factory and set this as new diagram | |
| aResult.setDiagram( | |
| UnoRuntime.queryInterface( | |
| XDiagram.class, | |
| (UnoRuntime.queryInterface( | |
| XMultiServiceFactory.class, | |
| aResult )).createInstance( sChartServiceName ))); | |
| } | |
| catch( NoSuchElementException ex ) | |
| { | |
| System.out.println( "Couldn't find chart with name " + sChartName + ": " + ex ); | |
| } | |
| catch( Exception ex ) | |
| {} | |
| } | |
| return aResult; | |
| } | |
| // ____________________ | |
| /** Fill a rectangular range with random numbers. | |
| The first column has increasing values | |
| */ | |
| public XCellRange insertRandomRange( int nColumnCount, int nRowCount ) | |
| { | |
| XCellRange aRange = null; | |
| // get the sheet to insert the chart | |
| try | |
| { | |
| XSpreadsheet aSheet = getDataSheet(); | |
| XCellRange aSheetRange = UnoRuntime.queryInterface( XCellRange.class, aSheet ); | |
| aRange = aSheetRange.getCellRangeByPosition( | |
| 0, 0, | |
| nColumnCount - 1, nRowCount - 1 ); | |
| int nCol, nRow; | |
| double fBase = 0.0; | |
| double fRange = 10.0; | |
| double fValue; | |
| Random aGenerator = new Random(); | |
| for( nCol = 0; nCol < nColumnCount; nCol++ ) | |
| { | |
| if( 0 == nCol ) | |
| { | |
| (aSheet.getCellByPosition( nCol, 0 )).setFormula( "X" ); | |
| } | |
| else | |
| { | |
| (aSheet.getCellByPosition( nCol, 0 )).setFormula( "Random " + nCol ); | |
| } | |
| for( nRow = 1; nRow < nRowCount; nRow++ ) | |
| { | |
| if( 0 == nCol ) | |
| { | |
| // x values: ascending numbers | |
| fValue = (double)nRow + aGenerator.nextDouble(); | |
| } | |
| else | |
| { | |
| fValue = fBase + ( aGenerator.nextGaussian() * fRange ); | |
| } | |
| // put value into cell | |
| // note: getCellByPosition is a method at ...table.XCellRange which | |
| // the XSpreadsheet inherits via ...sheet.XSheetCellRange | |
| (aSheet.getCellByPosition( nCol, nRow )).setValue( fValue ); | |
| } | |
| } | |
| } | |
| catch( Exception ex ) | |
| { | |
| System.out.println( "Sheet not found" + ex ); | |
| } | |
| return aRange; | |
| } | |
| // ____________________ | |
| public XCellRange insertFormulaRange( int nColumnCount, int nRowCount ) | |
| { | |
| XCellRange aRange = null; | |
| // get the sheet to insert the chart | |
| try | |
| { | |
| XSpreadsheet aSheet = getDataSheet(); | |
| XCellRange aSheetRange = UnoRuntime.queryInterface( XCellRange.class, aSheet ); | |
| aRange = aSheetRange.getCellRangeByPosition( | |
| 0, 0, | |
| nColumnCount - 1, nRowCount - 1 ); | |
| int nCol, nRow; | |
| double fValue; | |
| double fFactor = 2.0 * java.lang.Math.PI / (double)(nRowCount - 1); | |
| String aFormula; | |
| // set variable factor for cos formula | |
| int nFactorCol = nColumnCount + 2; | |
| (aSheet.getCellByPosition( nFactorCol - 1, 0 )).setValue( 0.2 ); | |
| XText xCellText = UnoRuntime.queryInterface( XText.class, aSheet.getCellByPosition( nFactorCol - 1, 1 ) ); | |
| xCellText.setString( "Change the factor above and\nwatch the changes in the chart" ); | |
| for( nCol = 0; nCol < nColumnCount; nCol++ ) | |
| { | |
| for( nRow = 0; nRow < nRowCount; nRow++ ) | |
| { | |
| if( 0 == nCol ) | |
| { | |
| // x values: ascending numbers | |
| fValue = (double)nRow * fFactor; | |
| (aSheet.getCellByPosition( nCol, nRow )).setValue( fValue ); | |
| } | |
| else | |
| { | |
| aFormula = new String( "=" ); | |
| if( nCol % 2 == 0 ) | |
| aFormula += "SIN"; | |
| else | |
| aFormula += "COS"; | |
| aFormula += "(INDIRECT(ADDRESS(" + (nRow + 1) + ";1)))+RAND()*INDIRECT(ADDRESS(1;" + nFactorCol + "))"; | |
| (aSheet.getCellByPosition( nCol, nRow )).setFormula( aFormula ); | |
| } | |
| } | |
| } | |
| } | |
| catch( Exception ex ) | |
| { | |
| System.out.println( "Sheet not found" + ex ); | |
| } | |
| return aRange; | |
| } | |
| // ____________________ | |
| /** Bring the sheet containing charts visually to the foreground | |
| */ | |
| public void raiseChartSheet() | |
| { | |
| (UnoRuntime.queryInterface( | |
| XSpreadsheetView.class, | |
| (UnoRuntime.queryInterface( | |
| XModel.class, | |
| maSpreadSheetDoc )).getCurrentController()) ).setActiveSheet( getChartSheet() ); | |
| } | |
| // __________ private members and methods __________ | |
| private final String msDataSheetName = "Data"; | |
| private final String msChartSheetName = "Chart"; | |
| private XSpreadsheetDocument maSpreadSheetDoc; | |
| // ____________________ | |
| /** create two sheets, one for data and one for charts in the document | |
| */ | |
| private void initSpreadSheet() | |
| { | |
| if( maSpreadSheetDoc != null ) | |
| { | |
| XSpreadsheets aSheets = maSpreadSheetDoc.getSheets(); | |
| XNameContainer aSheetsNC = UnoRuntime.queryInterface( | |
| XNameContainer.class, aSheets ); | |
| XIndexAccess aSheetsIA = UnoRuntime.queryInterface( | |
| XIndexAccess.class, aSheets ); | |
| if( aSheets != null && | |
| aSheetsNC != null && | |
| aSheetsIA != null ) | |
| { | |
| try | |
| { | |
| // remove all sheets except one | |
| for( int i = aSheetsIA.getCount() - 1; i > 0; i-- ) | |
| { | |
| aSheetsNC.removeByName( | |
| ( UnoRuntime.queryInterface( | |
| XNamed.class, aSheetsIA.getByIndex( i ) )).getName() ); | |
| } | |
| XNamed aFirstSheet = UnoRuntime.queryInterface( | |
| XNamed.class, | |
| aSheetsIA.getByIndex( 0 )); | |
| // first sheet becomes data sheet | |
| aFirstSheet.setName( msDataSheetName ); | |
| // second sheet becomes chart sheet | |
| aSheets.insertNewByName( msChartSheetName, (short)1 ); | |
| } | |
| catch( Exception ex ) | |
| { | |
| System.out.println( "Couldn't initialize Spreadsheet Document: " + ex ); | |
| } | |
| } | |
| } | |
| } | |
| } |