diff options
Diffstat (limited to 'odk/examples/DevelopersGuide/Spreadsheet')
13 files changed, 3870 insertions, 0 deletions
diff --git a/odk/examples/DevelopersGuide/Spreadsheet/DataPilotReadme.txt b/odk/examples/DevelopersGuide/Spreadsheet/DataPilotReadme.txt new file mode 100644 index 000000000..7876b6a33 --- /dev/null +++ b/odk/examples/DevelopersGuide/Spreadsheet/DataPilotReadme.txt @@ -0,0 +1,48 @@ +# +# This file is part of the LibreOffice project. +# +# This Source Code Form is subject to the terms of the Mozilla Public +# License, v. 2.0. If a copy of the MPL was not distributed with this +# file, You can obtain one at http://mozilla.org/MPL/2.0/. +# +# This file incorporates work covered by the following license notice: +# +# 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 . +# + +ExampleDataPilotSource +====================== + +This example shows how you can implement an external DataPilot +component. + +Simple usage scenario: +====================== +1. build and deploy the component +2. open a new spreadsheet document +3. select for example cell B:2 +4. start the DataPilot dialog + Data->DataPilot->Start... +5. select "External source/interface -> Ok +6. select the example DataPilot + Use the drop down list and select the service + "ExampleDataPilotSource" -> Ok +7. Place some number fields in the field areas + For example: + - select the "ones" field and drop it in the "Row Fields" area + - repeat this step with the "tens" and "hundreds" field + - select the "thousands" field and drop it in the "Column Fields" + area. + -> press Ok +8. you see a matrix with rows and columns for the selected fields + and a value area with a sum up of all fields valid for this + coordinate. For example cell F:21=1221 (ones=B:21=1, tens=c:21=2 + hundreds=D:21=2 and thousands=F:3=1) + + diff --git a/odk/examples/DevelopersGuide/Spreadsheet/ExampleAddIn.components b/odk/examples/DevelopersGuide/Spreadsheet/ExampleAddIn.components new file mode 100644 index 000000000..6212c72d4 --- /dev/null +++ b/odk/examples/DevelopersGuide/Spreadsheet/ExampleAddIn.components @@ -0,0 +1,9 @@ +<?xml version="1.0" encoding="UTF-8"?> +<components xmlns="http://openoffice.org/2010/uno-components"> + <component loader="com.sun.star.loader.Java2" uri="ExampleAddIn.uno.jar"> + <implementation name="ExampleAddIn$_ExampleAddIn"> + <service name="com.sun.star.sheet.AddIn"/> + <service name="org.openoffice.sheet.addin.ExampleAddIn"/> + </implementation> + </component> +</components> diff --git a/odk/examples/DevelopersGuide/Spreadsheet/ExampleAddIn.java b/odk/examples/DevelopersGuide/Spreadsheet/ExampleAddIn.java new file mode 100644 index 000000000..59cac873f --- /dev/null +++ b/odk/examples/DevelopersGuide/Spreadsheet/ExampleAddIn.java @@ -0,0 +1,304 @@ +/* -*- Mode: Java; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */ +import com.sun.star.sheet.XResultListener; + +/************************************************************************* + * + * The Contents of this file are made available subject to the terms of + * the BSD license. + * + * Copyright 2000, 2010 Oracle and/or its affiliates. + * All rights reserved. + * + * Redistribution and use in source and binary forms, with or without + * modification, are permitted provided that the following conditions + * are met: + * 1. Redistributions of source code must retain the above copyright + * notice, this list of conditions and the following disclaimer. + * 2. Redistributions in binary form must reproduce the above copyright + * notice, this list of conditions and the following disclaimer in the + * documentation and/or other materials provided with the distribution. + * 3. Neither the name of Sun Microsystems, Inc. nor the names of its + * contributors may be used to endorse or promote products derived + * from this software without specific prior written permission. + * + * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS + * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT + * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS + * FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE + * COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, + * INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, + * BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS + * OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND + * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR + * TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE + * USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. + * + *************************************************************************/ + +class ExampleAddInResult implements com.sun.star.sheet.XVolatileResult +{ + private final String aName; + private int nValue; + private final java.util.ArrayList<XResultListener> aListeners = new java.util.ArrayList<XResultListener>(); + + public ExampleAddInResult( String aNewName ) + { + aName = aNewName; + } + + private com.sun.star.sheet.ResultEvent getResult() + { + com.sun.star.sheet.ResultEvent aEvent = + new com.sun.star.sheet.ResultEvent(); + aEvent.Value = aName + " " + nValue; + aEvent.Source = this; + return aEvent; + } + + public void addResultListener(com.sun.star.sheet.XResultListener aListener) + { + aListeners.add( aListener ); + + // immediately notify of initial value + aListener.modified( getResult() ); + } + + public void removeResultListener(com.sun.star.sheet.XResultListener aListener) + { + aListeners.remove( aListener ); + } + + public void incrementValue() + { + ++nValue; + com.sun.star.sheet.ResultEvent aEvent = getResult(); + + for( XResultListener l : aListeners) + l.modified(aEvent); + } +} + +class ExampleAddInThread extends Thread +{ + private final java.util.HashMap<String, ExampleAddInResult> aCounters; + + public ExampleAddInThread( java.util.HashMap<String, ExampleAddInResult> aResults ) + { + aCounters = aResults; + } + + @Override + public void run() + { + while ( true ) + { + try + { + sleep(1000); + } + catch( InterruptedException exception ) + { + } + + // increment all counters + for (ExampleAddInResult r : aCounters.values()) + r.incrementValue(); + } + } +} + +public class ExampleAddIn +{ + public static class _ExampleAddIn extends com.sun.star.lib.uno.helper.WeakBase + implements org.openoffice.sheet.addin.XExampleAddIn, + com.sun.star.sheet.XAddIn, + com.sun.star.lang.XServiceName, + com.sun.star.lang.XServiceInfo + { + private static final String aExampleService = "org.openoffice.sheet.addin.ExampleAddIn"; + private static final String aAddInService = "com.sun.star.sheet.AddIn"; + private static final String aImplName = _ExampleAddIn.class.getName(); + + private static final short FUNCTION_INVALID = -1; + + private static final String[] aFunctionNames = + { + "getIncremented", + "getCounter" + }; + private static final String[] aDisplayFunctionNames = + { + "Increment", + "Counter" + }; + private static final String[] aDescriptions = + { + "Increments a value", + "Returns a counter" + }; + private static final String[] aFirstArgumentNames = + { + "Value", + "Name" + }; + private static final String[] aFirstArgumentDescriptions = + { + "The value that is incremented", + "The name of the counter" + }; + + private com.sun.star.lang.Locale aFuncLocale; + private java.util.HashMap<String, ExampleAddInResult> aResults; + + public _ExampleAddIn( com.sun.star.lang.XMultiServiceFactory xFactory ) + { + } + + private int getFunctionID( String aProgrammaticFunctionName ) + { + for ( int i = 0; i < aFunctionNames.length; i++ ) + if ( aProgrammaticFunctionName.equals(aFunctionNames[i]) ) + return i; + return FUNCTION_INVALID; + } + + // XExampleAddIn + + public int getIncremented( int nValue ) + { + return nValue + 1; + } + + public com.sun.star.sheet.XVolatileResult getCounter(String aName) + { + if ( aResults == null ) + { + // create the table of results, and start a thread to increment + // all counters + aResults = new java.util.HashMap<String, ExampleAddInResult>(); + ExampleAddInThread aThread = new ExampleAddInThread( aResults ); + aThread.start(); + } + + ExampleAddInResult aResult = aResults.get(aName); + if ( aResult == null ) + { + aResult = new ExampleAddInResult(aName); + aResults.put( aName, aResult ); + } + return aResult; + } + + // XAddIn + + public String getProgrammaticFuntionName(String aDisplayName) + { + for ( int i = 0; i < aFunctionNames.length; i++ ) + if ( aDisplayName.equals(aDisplayFunctionNames[i]) ) + return aFunctionNames[i]; + return ""; + } + + public String getDisplayFunctionName(String aProgrammaticName) + { + int nFunction = getFunctionID( aProgrammaticName ); + return ( nFunction == FUNCTION_INVALID ) ? "" : + aDisplayFunctionNames[nFunction]; + } + + public String getFunctionDescription(String aProgrammaticName) + { + int nFunction = getFunctionID( aProgrammaticName ); + return ( nFunction == FUNCTION_INVALID ) ? "" : + aDescriptions[nFunction]; + } + + public String getDisplayArgumentName(String aProgrammaticFunctionName, + int nArgument) + { + // both functions in this example only have a first argument + int nFunction = getFunctionID( aProgrammaticFunctionName ); + return ( nFunction == FUNCTION_INVALID || nArgument != 0) ? "" : + aFirstArgumentNames[nFunction]; + } + + public String getArgumentDescription(String aProgrammaticFunctionName, + int nArgument ) + { + // both functions in this example only have a first argument + int nFunction = getFunctionID( aProgrammaticFunctionName ); + return ( nFunction == FUNCTION_INVALID || nArgument != 0) ? "" : + aFirstArgumentDescriptions[nFunction]; + } + + public String getProgrammaticCategoryName(String aProgrammaticFunctionName) + { + return( "Add-In" ); + } + + public String getDisplayCategoryName(String aProgrammaticFunctionName) + { + return( "Add-In" ); + } + + // XLocalizable + + public void setLocale( com.sun.star.lang.Locale aLocale ) + { + // the locale is stored and used for getLocale, but otherwise + // ignored in this example + aFuncLocale = aLocale; + } + + public com.sun.star.lang.Locale getLocale() + { + return aFuncLocale; + } + + // XServiceName + + public String getServiceName() + { + return aExampleService; + } + + // XServiceInfo + + public String getImplementationName() + { + return aImplName; + } + + public String[] getSupportedServiceNames() + { + String [] aSupportedServices = new String[ 2 ]; + aSupportedServices[ 0 ] = aExampleService; + aSupportedServices[ 1 ] = aAddInService; + return aSupportedServices; + } + + public boolean supportsService( String aService ) + { + return (aService.equals( aExampleService ) || + aService.equals( aAddInService ) ); + } + + } + + + public static com.sun.star.lang.XSingleServiceFactory __getServiceFactory( + String implName, + com.sun.star.lang.XMultiServiceFactory multiFactory, + com.sun.star.registry.XRegistryKey regKey) + { + com.sun.star.lang.XSingleServiceFactory xSingleServiceFactory = null; + if ( implName.equals(_ExampleAddIn.aImplName) ) + xSingleServiceFactory = + com.sun.star.comp.loader.FactoryHelper.getServiceFactory( + _ExampleAddIn.class, _ExampleAddIn.aExampleService, + multiFactory, regKey); + return xSingleServiceFactory; + } +} + +/* vim:set shiftwidth=4 softtabstop=4 expandtab: */ diff --git a/odk/examples/DevelopersGuide/Spreadsheet/ExampleAddIn.ods b/odk/examples/DevelopersGuide/Spreadsheet/ExampleAddIn.ods Binary files differnew file mode 100644 index 000000000..b86f24915 --- /dev/null +++ b/odk/examples/DevelopersGuide/Spreadsheet/ExampleAddIn.ods diff --git a/odk/examples/DevelopersGuide/Spreadsheet/ExampleDataPilotSource.components b/odk/examples/DevelopersGuide/Spreadsheet/ExampleDataPilotSource.components new file mode 100644 index 000000000..f0881c1f5 --- /dev/null +++ b/odk/examples/DevelopersGuide/Spreadsheet/ExampleDataPilotSource.components @@ -0,0 +1,8 @@ +<?xml version="1.0" encoding="UTF-8"?> +<components xmlns="http://openoffice.org/2010/uno-components"> + <component loader="com.sun.star.loader.Java2" uri="ExampleDataPilotSource.uno.jar"> + <implementation name="ExampleDataPilotSource$_ExampleDataPilotSource"> + <service name="com.sun.star.sheet.DataPilotSource"/> + </implementation> + </component> +</components> diff --git a/odk/examples/DevelopersGuide/Spreadsheet/ExampleDataPilotSource.java b/odk/examples/DevelopersGuide/Spreadsheet/ExampleDataPilotSource.java new file mode 100644 index 000000000..6b10da93c --- /dev/null +++ b/odk/examples/DevelopersGuide/Spreadsheet/ExampleDataPilotSource.java @@ -0,0 +1,990 @@ +/* -*- Mode: Java; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */ +import com.sun.star.sheet.DataPilotFieldFilter; + +/************************************************************************* + * + * The Contents of this file are made available subject to the terms of + * the BSD license. + * + * Copyright 2000, 2010 Oracle and/or its affiliates. + * All rights reserved. + * + * Redistribution and use in source and binary forms, with or without + * modification, are permitted provided that the following conditions + * are met: + * 1. Redistributions of source code must retain the above copyright + * notice, this list of conditions and the following disclaimer. + * 2. Redistributions in binary form must reproduce the above copyright + * notice, this list of conditions and the following disclaimer in the + * documentation and/or other materials provided with the distribution. + * 3. Neither the name of Sun Microsystems, Inc. nor the names of its + * contributors may be used to endorse or promote products derived + * from this software without specific prior written permission. + * + * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS + * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT + * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS + * FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE + * COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, + * INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, + * BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS + * OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND + * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR + * TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE + * USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. + * + *************************************************************************/ + +// Example DataPilot source component + +// helper class to hold the settings + +class ExampleSettings +{ + public static final int nDimensionCount = 6; + public static final int nValueDimension = 4; + public static final int nDataDimension = 5; + public static final String [] aDimensionNames = { + "ones", "tens", "hundreds", "thousands", "value", "" }; + + public static final String getMemberName( int nMember ) + { + return String.valueOf( nMember ); + } + + public int nMemberCount = 3; + public java.util.List<Integer> aColDimensions = new java.util.ArrayList<Integer>(); + public java.util.List<Integer> aRowDimensions = new java.util.ArrayList<Integer>(); +} + +// XPropertySetInfo implementation for getPropertySetInfo + +class ExamplePropertySetInfo implements com.sun.star.beans.XPropertySetInfo +{ + private final com.sun.star.beans.Property[] aProperties; + + public ExamplePropertySetInfo( com.sun.star.beans.Property[] aProps ) + { + aProperties = aProps; + } + + public com.sun.star.beans.Property[] getProperties() + { + return aProperties; + } + + public com.sun.star.beans.Property getPropertyByName( String aName ) + throws com.sun.star.beans.UnknownPropertyException + { + for ( int i=0; i<aProperties.length; i++ ) + if ( aProperties[i].Name.equals( aName ) ) + return aProperties[i]; + throw new com.sun.star.beans.UnknownPropertyException(); + } + + public boolean hasPropertyByName( String aName ) + { + for ( int i=0; i<aProperties.length; i++ ) + if ( aProperties[i].Name.equals( aName ) ) + return true; + return false; + } +} + +// implementation of com.sun.star.sheet.DataPilotSourceMember + +class ExampleMember implements com.sun.star.container.XNamed, + com.sun.star.beans.XPropertySet +{ + private final int nMember; + + public ExampleMember( int nMbr ) + { + nMember = nMbr; + } + + // XNamed + + public String getName() + { + return ExampleSettings.getMemberName( nMember ); + } + + public void setName( String aName ) + { + // ignored + } + + // XPropertySet + + public com.sun.star.beans.XPropertySetInfo getPropertySetInfo() + { + return new ExamplePropertySetInfo( new com.sun.star.beans.Property[] { + new com.sun.star.beans.Property( "IsVisible", -1, + new com.sun.star.uno.Type( Boolean.class ), (short) 0), + new com.sun.star.beans.Property( "ShowDetails", -1, + new com.sun.star.uno.Type( Boolean.class ), (short) 0) }); + } + + public void setPropertyValue( String aPropertyName, Object aValue ) + throws com.sun.star.beans.UnknownPropertyException + { + if ( aPropertyName.equals( "IsVisible" ) || + aPropertyName.equals( "ShowDetails" ) ) + { + // ignored + } + else + throw new com.sun.star.beans.UnknownPropertyException(); + } + + public Object getPropertyValue( String aPropertyName ) + throws com.sun.star.beans.UnknownPropertyException + { + if ( aPropertyName.equals( "IsVisible" ) || + aPropertyName.equals( "ShowDetails" ) ) + { + return Boolean.TRUE; // always true + } + else + throw new com.sun.star.beans.UnknownPropertyException(); + } + + public void addPropertyChangeListener( + String aPropertyName, com.sun.star.beans.XPropertyChangeListener xListener) + { + } + public void removePropertyChangeListener( + String aPropertyName, com.sun.star.beans.XPropertyChangeListener aListener) + { + } + public void addVetoableChangeListener( + String PropertyName, com.sun.star.beans.XVetoableChangeListener aListener) + { + } + public void removeVetoableChangeListener( + String PropertyName, com.sun.star.beans.XVetoableChangeListener aListener) + { + } +} + +// implementation of com.sun.star.sheet.DataPilotSourceMembers + +class ExampleMembers implements com.sun.star.sheet.XMembersAccess +{ + private final ExampleSettings aSettings; + private ExampleMember[] aMembers; + + public ExampleMembers( ExampleSettings aSet ) + { + aSettings = aSet; + aMembers = new ExampleMember[ aSettings.nMemberCount ]; + } + + // XNameAccess + + public com.sun.star.uno.Type getElementType() + { + return new com.sun.star.uno.Type( com.sun.star.container.XNamed.class ); + } + + public boolean hasElements() + { + return true; // always has elements + } + + public Object getByName( String aName ) + throws com.sun.star.container.NoSuchElementException + { + int nCount = aSettings.nMemberCount; + for ( int i=0; i<nCount; i++ ) + if ( aName.equals( ExampleSettings.getMemberName( i ) ) ) + { + if ( aMembers[i] == null ) + aMembers[i] = new ExampleMember( i ); + return aMembers[i]; + } + throw new com.sun.star.container.NoSuchElementException(); + } + + public String[] getElementNames() + { + int nCount = aSettings.nMemberCount; + String [] aNames = new String[ nCount ]; + for ( int i=0; i<nCount; i++ ) + aNames[i] = ExampleSettings.getMemberName( i ); + return aNames; + } + + public boolean hasByName( String aName ) + { + int nCount = aSettings.nMemberCount; + for ( int i=0; i<nCount; i++ ) + if ( aName.equals( ExampleSettings.getMemberName( i ) ) ) + return true; + return false; + } + + // XMembersAccess + + public String[] getLocaleIndependentElementNames() + { + return getElementNames(); + } +} + +// implementation of com.sun.star.sheet.DataPilotSourceLevel + +class ExampleLevel implements + com.sun.star.container.XNamed, + com.sun.star.sheet.XMembersSupplier, + com.sun.star.sheet.XDataPilotMemberResults, + com.sun.star.beans.XPropertySet +{ + private final ExampleSettings aSettings; + private final int nDimension; + private ExampleMembers aMembers; + + public ExampleLevel( ExampleSettings aSet, int nDim ) + { + aSettings = aSet; + nDimension = nDim; + } + + // XNamed + + public String getName() + { + return ExampleSettings.aDimensionNames[ nDimension ]; + } + + public void setName( String aName ) + { + // ignored + } + + // XMembersSupplier + + public com.sun.star.sheet.XMembersAccess getMembers() + { + if ( aMembers == null ) + aMembers = new ExampleMembers( aSettings ); + return aMembers; + } + + // XDataPilotMemberResults + + public com.sun.star.sheet.MemberResult[] getResults() + { + int nDimensions = 0; + int nPosition = aSettings.aColDimensions.indexOf( Integer.valueOf(nDimension)); + if ( nPosition >= 0 ) + nDimensions = aSettings.aColDimensions.size(); + else + { + nPosition = aSettings.aRowDimensions.indexOf( Integer.valueOf(nDimension)); + if ( nPosition >= 0 ) + nDimensions = aSettings.aRowDimensions.size(); + } + + if ( nPosition < 0 ) + return new com.sun.star.sheet.MemberResult[0]; + + int nMembers = aSettings.nMemberCount; + int nRepeat = 1; + int nFill = 1; + for ( int i=0; i<nDimensions; i++ ) + { + if ( i < nPosition ) + nRepeat *= nMembers; + else if ( i > nPosition ) + nFill *= nMembers; + } + int nSize = nRepeat * nMembers * nFill; + + com.sun.star.sheet.MemberResult[] aResults = + new com.sun.star.sheet.MemberResult[nSize]; + int nResultPos = 0; + for (int nOuter=0; nOuter<nRepeat; nOuter++) + { + for (int nMember=0; nMember<nMembers; nMember++) + { + aResults[nResultPos] = new com.sun.star.sheet.MemberResult(); + aResults[nResultPos].Name = ExampleSettings.getMemberName(nMember); + aResults[nResultPos].Caption = aResults[nResultPos].Name; + aResults[nResultPos].Flags = + com.sun.star.sheet.MemberResultFlags.HASMEMBER; + ++nResultPos; + + for (int nInner=1; nInner<nFill; nInner++) + { + aResults[nResultPos] = new com.sun.star.sheet.MemberResult(); + aResults[nResultPos].Flags = + com.sun.star.sheet.MemberResultFlags.CONTINUE; + ++nResultPos; + } + } + } + return aResults; + } + + // XPropertySet + + public com.sun.star.beans.XPropertySetInfo getPropertySetInfo() + { + return new ExamplePropertySetInfo( new com.sun.star.beans.Property[] { + new com.sun.star.beans.Property( "SubTotals", -1, + new com.sun.star.uno.Type( + com.sun.star.sheet.GeneralFunction[].class ), + (short) 0 ), + new com.sun.star.beans.Property( "ShowEmpty", -1, + new com.sun.star.uno.Type( Boolean.class ), + (short) 0 ) } ); + } + + public void setPropertyValue( String aPropertyName, Object aValue ) + throws com.sun.star.beans.UnknownPropertyException + { + if ( aPropertyName.equals( "SubTotals" ) || + aPropertyName.equals( "ShowEmpty" ) ) + { + // ignored + } + else + throw new com.sun.star.beans.UnknownPropertyException(); + } + + public Object getPropertyValue( String aPropertyName ) + throws com.sun.star.beans.UnknownPropertyException + { + if ( aPropertyName.equals( "SubTotals" ) ) + return new com.sun.star.sheet.GeneralFunction[0]; + else if ( aPropertyName.equals( "ShowEmpty" ) ) + return Boolean.TRUE; + else + throw new com.sun.star.beans.UnknownPropertyException(); + } + + public void addPropertyChangeListener( + String aPropertyName, com.sun.star.beans.XPropertyChangeListener xListener) + { + } + public void removePropertyChangeListener( + String aPropertyName, com.sun.star.beans.XPropertyChangeListener aListener) + { + } + public void addVetoableChangeListener( + String PropertyName, com.sun.star.beans.XVetoableChangeListener aListener) + { + } + public void removeVetoableChangeListener( + String PropertyName, com.sun.star.beans.XVetoableChangeListener aListener) + { + } +} + +// implementation of com.sun.star.sheet.DataPilotSourceLevels + +class ExampleLevels implements com.sun.star.container.XNameAccess +{ + private final ExampleSettings aSettings; + private final int nDimension; + private ExampleLevel aLevel; + + public ExampleLevels( ExampleSettings aSet, int nDim ) + { + aSettings = aSet; + nDimension = nDim; + } + + // XNameAccess + + public com.sun.star.uno.Type getElementType() + { + return new com.sun.star.uno.Type( com.sun.star.container.XNamed.class ); + } + + public boolean hasElements() + { + return true; // always has elements + } + + public Object getByName( String aName ) + throws com.sun.star.container.NoSuchElementException + { + // there's only one level with the same name as the dimension / hierarchy + if ( aName.equals( ExampleSettings.aDimensionNames[nDimension] ) ) + { + if ( aLevel == null ) + aLevel = new ExampleLevel( aSettings, nDimension ); + return aLevel; + } + throw new com.sun.star.container.NoSuchElementException(); + } + + public String[] getElementNames() + { + String [] aNames = new String[ 1 ]; + aNames[0] = ExampleSettings.aDimensionNames[nDimension]; + return aNames; + } + + public boolean hasByName( String aName ) + { + return aName.equals( ExampleSettings.aDimensionNames[nDimension] ); + } +} + +// implementation of com.sun.star.sheet.DataPilotSourceHierarchy + +class ExampleHierarchy implements com.sun.star.container.XNamed, + com.sun.star.sheet.XLevelsSupplier +{ + private final ExampleSettings aSettings; + private final int nDimension; + private ExampleLevels aLevels; + + public ExampleHierarchy( ExampleSettings aSet, int nDim ) + { + aSettings = aSet; + nDimension = nDim; + } + + // XNamed + + public String getName() + { + return ExampleSettings.aDimensionNames[ nDimension ]; + } + + public void setName( String aName ) + { + // ignored + } + + // XLevelsSupplier + + public com.sun.star.container.XNameAccess getLevels() + { + if ( aLevels == null ) + aLevels = new ExampleLevels( aSettings, nDimension ); + return aLevels; + } +} + +// implementation of com.sun.star.sheet.DataPilotSourceHierarchies + +class ExampleHierarchies implements com.sun.star.container.XNameAccess +{ + private final ExampleSettings aSettings; + private final int nDimension; + private ExampleHierarchy aHierarchy; + + public ExampleHierarchies( ExampleSettings aSet, int nDim ) + { + aSettings = aSet; + nDimension = nDim; + } + + // XNameAccess + + public com.sun.star.uno.Type getElementType() + { + return new com.sun.star.uno.Type( com.sun.star.container.XNamed.class ); + } + + public boolean hasElements() + { + return true; // always has elements + } + + public Object getByName( String aName ) + throws com.sun.star.container.NoSuchElementException + { + // there's only one hierarchy with the same name as the dimension + if ( aName.equals( ExampleSettings.aDimensionNames[nDimension] ) ) + { + if ( aHierarchy == null ) + aHierarchy = new ExampleHierarchy( aSettings, nDimension ); + return aHierarchy; + } + throw new com.sun.star.container.NoSuchElementException(); + } + + public String[] getElementNames() + { + String [] aNames = new String[ 1 ]; + aNames[0] = ExampleSettings.aDimensionNames[nDimension]; + return aNames; + } + + public boolean hasByName( String aName ) + { + return aName.equals( ExampleSettings.aDimensionNames[nDimension] ); + } +} + +// implementation of com.sun.star.sheet.DataPilotSourceDimension + +class ExampleDimension implements + com.sun.star.container.XNamed, + com.sun.star.sheet.XHierarchiesSupplier, + com.sun.star.util.XCloneable, + com.sun.star.beans.XPropertySet +{ + private final ExampleSettings aSettings; + private final int nDimension; + private ExampleHierarchies aHierarchies; + private com.sun.star.sheet.DataPilotFieldOrientation eOrientation; + + public ExampleDimension( ExampleSettings aSet, int nDim ) + { + aSettings = aSet; + nDimension = nDim; + eOrientation = ( nDim == ExampleSettings.nValueDimension ) ? + com.sun.star.sheet.DataPilotFieldOrientation.DATA : + com.sun.star.sheet.DataPilotFieldOrientation.HIDDEN; + } + + // XNamed + + public String getName() + { + return ExampleSettings.aDimensionNames[ nDimension ]; + } + + public void setName( String aName ) + { + // ignored + } + + // XHierarchiesSupplier + + public com.sun.star.container.XNameAccess getHierarchies() + { + if ( aHierarchies == null ) + aHierarchies = new ExampleHierarchies( aSettings, nDimension ); + return aHierarchies; + } + + // XCloneable + + public com.sun.star.util.XCloneable createClone() + { + return null; // not supported + } + + // XPropertySet + + public com.sun.star.beans.XPropertySetInfo getPropertySetInfo() + { + return new ExamplePropertySetInfo( new com.sun.star.beans.Property[] { + new com.sun.star.beans.Property( "Original", -1, + new com.sun.star.uno.Type( com.sun.star.container.XNamed.class), + com.sun.star.beans.PropertyAttribute.READONLY ), + new com.sun.star.beans.Property( "IsDataLayoutDimension", -1, + new com.sun.star.uno.Type( Boolean.class), + com.sun.star.beans.PropertyAttribute.READONLY ), + new com.sun.star.beans.Property( "Orientation", -1, + new com.sun.star.uno.Type( + com.sun.star.sheet.DataPilotFieldOrientation.class), (short) 0), + new com.sun.star.beans.Property( "Position", -1, + new com.sun.star.uno.Type( Integer.class ), (short) 0), + new com.sun.star.beans.Property( "Function", -1, + new com.sun.star.uno.Type(com.sun.star.sheet.GeneralFunction.class), + (short) 0 ), + new com.sun.star.beans.Property( "UsedHierarchy", -1, + new com.sun.star.uno.Type( Integer.class ), (short) 0 ), + new com.sun.star.beans.Property( "Filter", -1, + new com.sun.star.uno.Type( + com.sun.star.sheet.TableFilterField[].class), (short) 0) }); + } + + public void setPropertyValue( String aPropertyName, Object aValue ) + throws com.sun.star.beans.UnknownPropertyException + { + if ( aPropertyName.equals( "Orientation" ) ) + { + com.sun.star.sheet.DataPilotFieldOrientation eNewOrient = + (com.sun.star.sheet.DataPilotFieldOrientation) aValue; + if ( nDimension != ExampleSettings.nValueDimension && + nDimension != ExampleSettings.nDataDimension && + eNewOrient != com.sun.star.sheet.DataPilotFieldOrientation.DATA ) + { + // remove from list for old orientation and add for new one + Integer aDimInt = Integer.valueOf(nDimension); + if ( eOrientation == com.sun.star.sheet.DataPilotFieldOrientation.COLUMN ) + aSettings.aColDimensions.remove( aDimInt ); + else if ( eOrientation == com.sun.star.sheet.DataPilotFieldOrientation.ROW ) + aSettings.aRowDimensions.remove( aDimInt ); + if ( eNewOrient == com.sun.star.sheet.DataPilotFieldOrientation.COLUMN ) + aSettings.aColDimensions.add( aDimInt ); + else if ( eNewOrient == com.sun.star.sheet.DataPilotFieldOrientation.ROW ) + aSettings.aRowDimensions.add( aDimInt ); + + // change orientation + eOrientation = eNewOrient; + } + } + else if ( aPropertyName.equals( "Position" ) ) + { + int nNewPos = ((Integer) aValue).intValue(); + Integer aDimInt = Integer.valueOf(nDimension); + if ( eOrientation == com.sun.star.sheet.DataPilotFieldOrientation.COLUMN ) + { + aSettings.aColDimensions.remove( aDimInt ); + aSettings.aColDimensions.add( nNewPos, aDimInt ); + } + else if ( eOrientation == com.sun.star.sheet.DataPilotFieldOrientation.ROW ) + { + aSettings.aRowDimensions.remove( aDimInt ); + aSettings.aRowDimensions.add( nNewPos, aDimInt ); + } + } + else if ( aPropertyName.equals( "Function" ) || aPropertyName.equals( "UsedHierarchy" ) || + aPropertyName.equals( "Filter" ) ) + { + // ignored + } + else + throw new com.sun.star.beans.UnknownPropertyException(); + } + + public Object getPropertyValue( String aPropertyName ) + throws com.sun.star.beans.UnknownPropertyException + { + if ( aPropertyName.equals( "Original" ) ) + return null; + else if ( aPropertyName.equals( "IsDataLayoutDimension" ) ) + return Boolean.valueOf( nDimension == ExampleSettings.nDataDimension ); + else if ( aPropertyName.equals( "Orientation" ) ) + return eOrientation; + else if ( aPropertyName.equals( "Position" ) ) + { + int nPosition; + if ( eOrientation == com.sun.star.sheet.DataPilotFieldOrientation.COLUMN ) + nPosition = aSettings.aColDimensions.indexOf( Integer.valueOf(nDimension) ); + else if ( eOrientation == com.sun.star.sheet.DataPilotFieldOrientation.ROW ) + nPosition = aSettings.aRowDimensions.indexOf( Integer.valueOf(nDimension) ); + else + nPosition = nDimension; + return Integer.valueOf( nPosition ); + } + else if ( aPropertyName.equals( "Function" ) ) + return com.sun.star.sheet.GeneralFunction.SUM; + else if ( aPropertyName.equals( "UsedHierarchy" ) ) + return Integer.valueOf(0); + else if ( aPropertyName.equals( "Filter" ) ) + return new com.sun.star.sheet.TableFilterField[0]; + else + throw new com.sun.star.beans.UnknownPropertyException(); + } + + public void addPropertyChangeListener( + String aPropertyName, com.sun.star.beans.XPropertyChangeListener xListener) + { + } + public void removePropertyChangeListener( + String aPropertyName, com.sun.star.beans.XPropertyChangeListener aListener) + { + } + public void addVetoableChangeListener( + String PropertyName, com.sun.star.beans.XVetoableChangeListener aListener) + { + } + public void removeVetoableChangeListener( + String PropertyName, com.sun.star.beans.XVetoableChangeListener aListener) + { + } +} + +// implementation of com.sun.star.sheet.DataPilotSourceDimensions + +class ExampleDimensions implements com.sun.star.container.XNameAccess +{ + private final ExampleSettings aSettings; + private ExampleDimension[] aDimensions; + + public ExampleDimensions( ExampleSettings aSet ) + { + aSettings = aSet; + } + + // XNameAccess + + public com.sun.star.uno.Type getElementType() + { + return new com.sun.star.uno.Type( com.sun.star.container.XNamed.class ); + } + + public boolean hasElements() + { + return true; // always has elements + } + + public Object getByName( String aName ) + throws com.sun.star.container.NoSuchElementException + { + for (int i=0; i<ExampleSettings.nDimensionCount; i++) + if ( aName.equals( ExampleSettings.aDimensionNames[i] ) ) + { + if ( aDimensions == null ) + aDimensions = new ExampleDimension[ ExampleSettings.nDimensionCount ]; + if ( aDimensions[i] == null ) + aDimensions[i] = new ExampleDimension( aSettings, i ); + return aDimensions[i]; + } + throw new com.sun.star.container.NoSuchElementException(); + } + + public String[] getElementNames() + { + String [] aNames = new String[ ExampleSettings.nDimensionCount ]; + for (int i=0; i<ExampleSettings.nDimensionCount; i++) + aNames[ i ] = ExampleSettings.aDimensionNames[i]; + return aNames; + } + + public boolean hasByName( String aName ) + { + for (int i=0; i<ExampleSettings.nDimensionCount; i++) + if ( aName.equals( ExampleSettings.aDimensionNames[i] ) ) + return true; + return false; + } +} + +// outer class for service implementation + +public class ExampleDataPilotSource +{ + // implementation of com.sun.star.sheet.DataPilotSource + + public static class _ExampleDataPilotSource implements + com.sun.star.sheet.XDimensionsSupplier, + com.sun.star.sheet.XDataPilotResults, + com.sun.star.util.XRefreshable, + com.sun.star.beans.XPropertySet, + com.sun.star.lang.XInitialization, + com.sun.star.lang.XServiceInfo + { + private static final String aServiceName = "com.sun.star.sheet.DataPilotSource"; + private static final String aImplName = _ExampleDataPilotSource.class.getName(); + + private final ExampleSettings aSettings = new ExampleSettings(); + private ExampleDimensions aDimensions; + + public _ExampleDataPilotSource( com.sun.star.lang.XMultiServiceFactory xFactory ) + { + } + + // XInitialization + + public void initialize( Object[] aArguments ) + { + // If the first argument (Source) is a number between 2 and 10, + // use it as member count, otherwise keep the default value. + try + { + if ( aArguments.length >= 1 ) + { + String aSource = com.sun.star.uno.AnyConverter.toString(aArguments[0]); + if ( aSource != null && aSource.length() > 0) + { + int nValue = Integer.parseInt( aSource ); + if ( nValue >= 2 && nValue <= 10 ) + aSettings.nMemberCount = nValue; + } + } + } + catch ( NumberFormatException e ) + { + System.out.println( "Error: caught exception in " + + "ExampleDataPilotSource.initialize!\nException Message = " + + e.getMessage()); + e.printStackTrace(); + } + catch ( com.sun.star.lang.IllegalArgumentException e ) + { + System.out.println( "Error: caught exception in " + + "ExampleDataPilotSource.initialize!\nException Message = " + + e.getMessage()); + e.printStackTrace(); + } + } + + // XDataPilotResults + + public com.sun.star.sheet.DataResult[][] getResults() + { + int[] nDigits = new int[ExampleSettings.nDimensionCount]; + int nValue = 1; + for (int i=0; i<ExampleSettings.nDimensionCount; i++) + { + nDigits[i] = nValue; + nValue *= 10; + } + + int nMemberCount = aSettings.nMemberCount; + int nRowDimCount = aSettings.aRowDimensions.size(); + int nColDimCount = aSettings.aColDimensions.size(); + + int nRows = 1; + for (int i=0; i<nRowDimCount; i++) + nRows *= nMemberCount; + int nColumns = 1; + for (int i=0; i<nColDimCount; i++) + nColumns *= nMemberCount; + + com.sun.star.sheet.DataResult[][] aResults = new com.sun.star.sheet.DataResult[nRows][]; + for (int nRow=0; nRow<nRows; nRow++) + { + int nRowVal = nRow; + int nRowResult = 0; + for (int nRowDim=0; nRowDim<nRowDimCount; nRowDim++) + { + int nDim = aSettings.aRowDimensions.get(nRowDimCount-nRowDim-1).intValue(); + nRowResult += ( nRowVal % nMemberCount ) * nDigits[nDim]; + nRowVal /= nMemberCount; + } + + aResults[nRow] = new com.sun.star.sheet.DataResult[nColumns]; + for (int nCol=0; nCol<nColumns; nCol++) + { + int nColVal = nCol; + int nResult = nRowResult; + for (int nColDim=0; nColDim<nColDimCount; nColDim++) + { + int nDim = aSettings.aColDimensions.get(nColDimCount-nColDim-1).intValue(); + nResult += ( nColVal % nMemberCount ) * nDigits[nDim]; + nColVal /= nMemberCount; + } + + aResults[nRow][nCol] = new com.sun.star.sheet.DataResult(); + aResults[nRow][nCol].Flags = com.sun.star.sheet.DataResultFlags.HASDATA; + aResults[nRow][nCol].Value = nResult; + } + } + return aResults; + } + + public double[] getFilteredResults(DataPilotFieldFilter[] aFilters) { + // FIXME + return new double[0]; + } + + // XDimensionsSupplier + + public com.sun.star.container.XNameAccess getDimensions() + { + if ( aDimensions == null ) + aDimensions = new ExampleDimensions( aSettings ); + return aDimensions; + } + + // XPropertySet + + public com.sun.star.beans.XPropertySetInfo getPropertySetInfo() + { + return new ExamplePropertySetInfo( new com.sun.star.beans.Property[] { + new com.sun.star.beans.Property( "ColumnGrand", -1, + new com.sun.star.uno.Type( Boolean.class ), (short) 0), + new com.sun.star.beans.Property( "RowGrand", -1, + new com.sun.star.uno.Type( Boolean.class ), (short) 0) }); + } + + public void setPropertyValue( String aPropertyName, Object aValue ) + throws com.sun.star.beans.UnknownPropertyException + { + if ( aPropertyName.equals( "ColumnGrand" ) || + aPropertyName.equals( "RowGrand" ) ) + { + // ignored + } + else + throw new com.sun.star.beans.UnknownPropertyException(); + } + + public Object getPropertyValue( String aPropertyName ) + throws com.sun.star.beans.UnknownPropertyException + { + if ( aPropertyName.equals( "ColumnGrand" ) || + aPropertyName.equals( "RowGrand" ) ) + { + return Boolean.FALSE; // always false + } + else + throw new com.sun.star.beans.UnknownPropertyException(); + } + + public void addPropertyChangeListener( + String aPropertyName, + com.sun.star.beans.XPropertyChangeListener xListener ) + { + } + public void removePropertyChangeListener( + String aPropertyName, + com.sun.star.beans.XPropertyChangeListener aListener ) + { + } + public void addVetoableChangeListener( + String PropertyName, + com.sun.star.beans.XVetoableChangeListener aListener ) + { + } + public void removeVetoableChangeListener( + String PropertyName, + com.sun.star.beans.XVetoableChangeListener aListener ) + { + } + + // XRefreshable + + public void refresh() + { + } + public void addRefreshListener( com.sun.star.util.XRefreshListener l ) + { + } + public void removeRefreshListener( com.sun.star.util.XRefreshListener l ) + { + } + + // XServiceInfo + + public String getImplementationName() + { + return aImplName; + } + + public String[] getSupportedServiceNames() + { + String [] aSupportedServices = new String[ 1 ]; + aSupportedServices[ 0 ] = aServiceName; + return aSupportedServices; + } + + public boolean supportsService( String aService ) + { + return aService.equals( aServiceName ); + } + } + + public static com.sun.star.lang.XSingleServiceFactory __getServiceFactory( + String implName, + com.sun.star.lang.XMultiServiceFactory multiFactory, + com.sun.star.registry.XRegistryKey regKey) + { + com.sun.star.lang.XSingleServiceFactory xSingleServiceFactory = null; + + if ( implName.equals(_ExampleDataPilotSource.aImplName) ) + xSingleServiceFactory = + com.sun.star.comp.loader.FactoryHelper.getServiceFactory( + _ExampleDataPilotSource.class, + _ExampleDataPilotSource.aServiceName, multiFactory, regKey); + + return xSingleServiceFactory; + } +} + +/* vim:set shiftwidth=4 softtabstop=4 expandtab: */ diff --git a/odk/examples/DevelopersGuide/Spreadsheet/GeneralTableSample.java b/odk/examples/DevelopersGuide/Spreadsheet/GeneralTableSample.java new file mode 100644 index 000000000..655447eff --- /dev/null +++ b/odk/examples/DevelopersGuide/Spreadsheet/GeneralTableSample.java @@ -0,0 +1,231 @@ +/* -*- Mode: Java; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */ +/************************************************************************* + * + * The Contents of this file are made available subject to the terms of + * the BSD license. + * + * Copyright 2000, 2010 Oracle and/or its affiliates. + * All rights reserved. + * + * Redistribution and use in source and binary forms, with or without + * modification, are permitted provided that the following conditions + * are met: + * 1. Redistributions of source code must retain the above copyright + * notice, this list of conditions and the following disclaimer. + * 2. Redistributions in binary form must reproduce the above copyright + * notice, this list of conditions and the following disclaimer in the + * documentation and/or other materials provided with the distribution. + * 3. Neither the name of Sun Microsystems, Inc. nor the names of its + * contributors may be used to endorse or promote products derived + * from this software without specific prior written permission. + * + * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS + * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT + * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS + * FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE + * COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, + * INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, + * BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS + * OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND + * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR + * TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE + * USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. + * + *************************************************************************/ + +import com.sun.star.uno.UnoRuntime; +import com.sun.star.uno.RuntimeException; + + +// __________ implementation ____________________________________ + +/** Create a spreadsheet document and provide access to table contents. + */ +public class GeneralTableSample extends SpreadsheetDocHelper +{ + + + + public static void main( String args[] ) + { + try + { + GeneralTableSample aSample = new GeneralTableSample( args ); + aSample.doSampleFunction(); + } + catch (Exception ex) + { + System.out.println( "Error: Sample caught exception!\nException Message = " + + ex.getMessage()); + ex.printStackTrace(); + System.exit( 1 ); + } + + System.out.println( "Sample done." ); + System.exit( 0 ); + } + + + + /// This sample function modifies cells and cell ranges. + public void doSampleFunction() throws RuntimeException, Exception + { + // for common usage + com.sun.star.sheet.XSpreadsheet xSheet = getSpreadsheet( 0 ); + com.sun.star.beans.XPropertySet xPropSet = null; + com.sun.star.table.XCell xCell = null; + com.sun.star.table.XCellRange xCellRange = null; + + // *** Access and modify a VALUE CELL *** + System.out.println( "*** Sample for service table.Cell ***" ); + + xCell = xSheet.getCellByPosition( 0, 0 ); + // Set cell value. + xCell.setValue( 1234 ); + + // Get cell value. + double nDblValue = xCell.getValue() * 2; + xSheet.getCellByPosition( 0, 1 ).setValue( nDblValue ); + + // *** Create a FORMULA CELL and query error type *** + xCell = xSheet.getCellByPosition( 0, 2 ); + // Set formula string. + xCell.setFormula( "=1/0" ); + + // Get error type. + boolean bValid = (xCell.getError() == 0); + // Get formula string. + String aText = "The formula " + xCell.getFormula() + " is "; + aText += bValid ? "valid." : "erroneous."; + + // *** Insert a TEXT CELL using the XText interface *** + xCell = xSheet.getCellByPosition( 0, 3 ); + com.sun.star.text.XText xCellText = UnoRuntime.queryInterface( com.sun.star.text.XText.class, xCell ); + com.sun.star.text.XTextCursor xTextCursor = xCellText.createTextCursor(); + xCellText.insertString( xTextCursor, aText, false ); + + // *** Change cell properties *** + int nValue = bValid ? 0x00FF00 : 0xFF4040; + xPropSet = UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xCell ); + xPropSet.setPropertyValue( "CellBackColor", Integer.valueOf( nValue ) ); + + + // *** Accessing a CELL RANGE *** + System.out.println( "*** Sample for service table.CellRange ***" ); + + // Accessing a cell range over its position. + xCellRange = xSheet.getCellRangeByPosition( 2, 0, 3, 1 ); + + // Change properties of the range. + xPropSet = UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xCellRange ); + xPropSet.setPropertyValue( "CellBackColor", Integer.valueOf( 0x8080FF ) ); + + // Accessing a cell range over its name. + xCellRange = xSheet.getCellRangeByName( "C4:D5" ); + + // Change properties of the range. + xPropSet = UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xCellRange ); + xPropSet.setPropertyValue( "CellBackColor", Integer.valueOf( 0xFFFF80 ) ); + + + // *** Using the CELL CURSOR to add some data below of the filled area *** + System.out.println( "*** Sample for service table.CellCursor ***" ); + + // Create a cursor using the XSpreadsheet method createCursorByRange() + xCellRange = xSheet.getCellRangeByName( "A1" ); + com.sun.star.sheet.XSheetCellRange xSheetCellRange = UnoRuntime.queryInterface( com.sun.star.sheet.XSheetCellRange.class, xCellRange ); + + com.sun.star.sheet.XSheetCellCursor xSheetCellCursor = + xSheet.createCursorByRange( xSheetCellRange ); + com.sun.star.table.XCellCursor xCursor = UnoRuntime.queryInterface( com.sun.star.table.XCellCursor.class, xSheetCellCursor ); + + // Move to the last filled cell. + xCursor.gotoEnd(); + // Move one row down. + xCursor.gotoOffset( 0, 1 ); + xCursor.getCellByPosition( 0, 0 ).setFormula( "Beyond of the last filled cell." ); + + + // *** Modifying COLUMNS and ROWS *** + System.out.println( "*** Sample for services table.TableRows and table.TableColumns ***" ); + + com.sun.star.table.XColumnRowRange xCRRange = UnoRuntime.queryInterface( com.sun.star.table.XColumnRowRange.class, xSheet ); + com.sun.star.table.XTableColumns xColumns = xCRRange.getColumns(); + com.sun.star.table.XTableRows xRows = xCRRange.getRows(); + + // Get column C by index (interface XIndexAccess). + Object aColumnObj = xColumns.getByIndex( 2 ); + xPropSet = UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, aColumnObj ); + xPropSet.setPropertyValue( "Width", Integer.valueOf( 5000 ) ); + + // Get the name of the column. + com.sun.star.container.XNamed xNamed = UnoRuntime.queryInterface( com.sun.star.container.XNamed.class, aColumnObj ); + aText = "The name of this column is " + xNamed.getName() + "."; + xSheet.getCellByPosition( 2, 2 ).setFormula( aText ); + + // Get column D by name (interface XNameAccess). + com.sun.star.container.XNameAccess xColumnsName = UnoRuntime.queryInterface( com.sun.star.container.XNameAccess.class, xColumns ); + + aColumnObj = xColumnsName.getByName( "D" ); + xPropSet = UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, aColumnObj ); + xPropSet.setPropertyValue( "IsVisible", Boolean.FALSE ); + + // Get row 7 by index (interface XIndexAccess) + Object aRowObj = xRows.getByIndex( 6 ); + xPropSet = UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, aRowObj ); + xPropSet.setPropertyValue( "Height", Integer.valueOf( 5000 ) ); + + xSheet.getCellByPosition( 2, 6 ).setFormula( "What a big cell." ); + + // Create a cell series with the values 1 ... 7. + for (int nRow = 8; nRow < 15; ++nRow) + xSheet.getCellByPosition( 0, nRow ).setValue( nRow - 7 ); + // Insert a row between 1 and 2 + xRows.insertByIndex( 9, 1 ); + // Delete the rows with the values 3 and 4. + xRows.removeByIndex( 11, 2 ); + + // *** Inserting CHARTS *** + System.out.println( "*** Sample for service table.TableCharts ***" ); + + com.sun.star.table.XTableChartsSupplier xChartsSupp = + UnoRuntime.queryInterface( + com.sun.star.table.XTableChartsSupplier.class, xSheet ); + com.sun.star.table.XTableCharts xCharts = xChartsSupp.getCharts(); + + // The chart will base on the last cell series, initializing all values. + String aName = "newChart"; + com.sun.star.awt.Rectangle aRect = new com.sun.star.awt.Rectangle(); + aRect.X = 10000; + aRect.Y = 3000; + aRect.Width = aRect.Height = 5000; + com.sun.star.table.CellRangeAddress[] aRanges = new com.sun.star.table.CellRangeAddress[1]; + aRanges[0] = createCellRangeAddress( xSheet, "A9:A14" ); + + // Create the chart. + xCharts.addNewByName( aName, aRect, aRanges, false, false ); + + // Get the chart by name. + Object aChartObj = xCharts.getByName( aName ); + com.sun.star.table.XTableChart xChart = UnoRuntime.queryInterface( com.sun.star.table.XTableChart.class, aChartObj ); + + // Query the state of row and column headers. + aText = "Chart has column headers: "; + aText += xChart.getHasColumnHeaders() ? "yes" : "no"; + xSheet.getCellByPosition( 2, 8 ).setFormula( aText ); + aText = "Chart has row headers: "; + aText += xChart.getHasRowHeaders() ? "yes" : "no"; + xSheet.getCellByPosition( 2, 9 ).setFormula( aText ); + } + + + + public GeneralTableSample( String[] args ) + { + super( args ); + } + + +} + +/* vim:set shiftwidth=4 softtabstop=4 expandtab: */ diff --git a/odk/examples/DevelopersGuide/Spreadsheet/Makefile b/odk/examples/DevelopersGuide/Spreadsheet/Makefile new file mode 100644 index 000000000..e5b8401d6 --- /dev/null +++ b/odk/examples/DevelopersGuide/Spreadsheet/Makefile @@ -0,0 +1,304 @@ +#************************************************************************* +# +# The Contents of this file are made available subject to the terms of +# the BSD license. +# +# Copyright 2000, 2010 Oracle and/or its affiliates. +# All rights reserved. +# +# Redistribution and use in source and binary forms, with or without +# modification, are permitted provided that the following conditions +# are met: +# 1. Redistributions of source code must retain the above copyright +# notice, this list of conditions and the following disclaimer. +# 2. Redistributions in binary form must reproduce the above copyright +# notice, this list of conditions and the following disclaimer in the +# documentation and/or other materials provided with the distribution. +# 3. Neither the name of Sun Microsystems, Inc. nor the names of its +# contributors may be used to endorse or promote products derived +# from this software without specific prior written permission. +# +# THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS +# "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT +# LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS +# FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE +# COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, +# INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, +# BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS +# OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND +# ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR +# TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE +# USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. +# +#************************************************************************** + +# Builds the SpreadSheet examples of the Developers Guide. + +PRJ=../../.. +SETTINGS=$(PRJ)/settings + +include $(SETTINGS)/settings.mk +include $(SETTINGS)/std.mk + +# Define non-platform/compiler specific settings +SAMPLE_NAME=DevGuideSpreadsheetExamples +SAMPLE_CLASS_OUT=$(OUT_CLASS)/$(SAMPLE_NAME) +SAMPLE_GEN_OUT=$(OUT_MISC)/$(SAMPLE_NAME) + +# Example add-in component +COMP1_NAME=ExampleAddIn +COMP1_CLASS_OUT=$(SAMPLE_CLASS_OUT)/$(COMP1_NAME) +COMP1_GEN_OUT=$(SAMPLE_GEN_OUT)/$(COMP1_NAME) +COMP1_RDB_NAME=$(COMP1_NAME).uno.rdb +COMP1_RDB=$(COMP1_GEN_OUT)/$(COMP1_RDB_NAME) +COMP1_PACKAGE=$(OUT_BIN)/$(COMP1_NAME).$(UNOOXT_EXT) +COMP1_PACKAGE_URL=$(subst \\,\,"$(COMP_PACKAGE_DIR)$(PS)$(COMP1_NAME).$(UNOOXT_EXT)") +COMP1_JAR_NAME=$(COMP1_NAME).uno.jar +COMP1_JAR=$(SAMPLE_CLASS_OUT)/$(COMP1_JAR_NAME) +COMP1_MANIFESTFILE=$(COMP1_GEN_OUT)/$(COMP1_NAME).uno.Manifest +COMP1_UNOPKG_MANIFEST=$(COMP1_GEN_OUT)/$(COMP1_NAME)/META-INF/manifest.xml +COMP1_REGISTERFLAG=$(SAMPLE_GEN_OUT)/devguide_$(COMP1_NAME)_register_component.flag +COMP1_COMPONENTS=$(COMP1_NAME).components + +# Example DataPilot component +COMP2_NAME=ExampleDataPilotSource +COMP2_CLASS_OUT=$(SAMPLE_CLASS_OUT)/$(COMP2_NAME) +COMP2_GEN_OUT=$(SAMPLE_GEN_OUT)/$(COMP2_NAME) +COMP2_PACKAGE=$(OUT_BIN)/$(COMP2_NAME).$(UNOOXT_EXT) +COMP2_PACKAGE_URL=$(subst \\,\,"$(COMP_PACKAGE_DIR)$(PS)$(COMP2_NAME).$(UNOOXT_EXT)") +COMP2_JAR_NAME=$(COMP2_NAME).uno.jar +COMP2_JAR=$(SAMPLE_CLASS_OUT)/$(COMP2_JAR_NAME) +COMP2_MANIFESTFILE=$(COMP2_GEN_OUT)/$(COMP2_NAME).uno.Manifest +COMP2_UNOPKG_MANIFEST=$(COMP2_GEN_OUT)/$(COMP2_NAME)/META-INF/manifest.xml +COMP2_REGISTERFLAG=$(SAMPLE_GEN_OUT)/devguide_$(COMP2_NAME)_register_component.flag +COMP2_COMPONENTS=$(COMP2_NAME).components + +#REGISTERFLAG = $(OUT_MISC)$(PS)devguide_$(SAMPLE_NAME)_register_component.flag + +APP1_NAME=GeneralTableSample +APP1_CLASS_OUT=$(SAMPLE_CLASS_OUT)/$(APP1_NAME) +APP1_GEN_OUT=$(SAMPLE_GEN_OUT)/$(APP1_NAME) +APP1_JAR=$(SAMPLE_CLASS_OUT)/$(APP1_NAME).jar + +APP2_NAME=SpreadsheetSample +APP2_CLASS_OUT=$(SAMPLE_CLASS_OUT)/$(APP2_NAME) +APP2_GEN_OUT=$(SAMPLE_GEN_OUT)/$(APP2_NAME) +APP2_JAR=$(SAMPLE_CLASS_OUT)/$(APP2_NAME).jar + +APP3_NAME=ViewSample +APP3_CLASS_OUT=$(SAMPLE_CLASS_OUT)/$(APP3_NAME) +APP3_GEN_OUT=$(SAMPLE_GEN_OUT)/$(APP3_NAME) +APP3_JAR=$(SAMPLE_CLASS_OUT)/$(APP3_NAME).jar + + +COMP1_IDLFILES = XExampleAddIn.idl + +# normally the idl file should be stored in a directory tree fitting the +# module structure, for the example we know the module structure +PACKAGE = org/openoffice/sheet/addin + +COMP1_JAVAFILES = \ + ExampleAddIn.java + +COMP2_JAVAFILES = \ + ExampleDataPilotSource.java + +APP_JAVAFILES = \ + SpreadsheetDocHelper.java + +APP_CLASSFILES = $(patsubst %.java,$(SAMPLE_CLASS_OUT)/%.class,$(APP_JAVAFILES)) +APP_CLASSNAMES = $(patsubst %.java,%.class,$(APP_JAVAFILES)) + +COMP1_GENCLASSFILES = $(patsubst %.idl,$(COMP1_CLASS_OUT)/$(PACKAGE)/%.class,$(COMP1_IDLFILES)) +COMP1_GENTYPELIST = $(subst /,.,$(patsubst %.idl,-T$(PACKAGE)/% ,$(COMP1_IDLFILES))) + +COMP1_CLASSFILES = $(patsubst %.java,$(COMP1_CLASS_OUT)/%.class,$(COMP1_JAVAFILES)) + +COMP2_CLASSFILES = $(patsubst %.java,$(COMP2_CLASS_OUT)/%.class,$(COMP2_JAVAFILES)) + + +SDK_CLASSPATH = $(subst $(EMPTYSTRING) $(PATH_SEPARATOR),$(PATH_SEPARATOR),$(CLASSPATH)\ + $(PATH_SEPARATOR)$(SAMPLE_CLASS_OUT)\ + $(PATH_SEPARATOR)$(COMP1_CLASS_OUT)\ + $(PATH_SEPARATOR)$(COMP2_CLASS_OUT)\ + $(PATH_SEPARATOR)$(APP1_CLASS_OUT)\ + $(PATH_SEPARATOR)$(APP2_CLASS_OUT)\ + $(PATH_SEPARATOR)$(APP3_CLASS_OUT)) + + +# Targets +.PHONY: ALL +ALL : $(SAMPLE_NAME) + +include $(SETTINGS)/stdtarget.mk + +$(SAMPLE_GEN_OUT)/%.Manifest : + -$(MKDIR) $(subst /,$(PS),$(@D)) + @echo UNO-Type-Path: $(basename $(notdir $*)).uno.jar> $@ + @echo RegistrationClassName: $(basename $(basename $(@F)))>> $@ + +# IDLs only for component 1 relevant +$(COMP1_RDB) : $(COMP1_IDLFILES) + -$(DEL) $(subst \\,\,$(subst /,$(PS),$@)) + -$(MKDIR) $(subst /,$(PS),$(@D)) + $(UNOIDLWRITE) $(URE_TYPES) $(OFFICE_TYPES) $< $@ + +$(COMP1_CLASS_OUT)/$(PACKAGE)/%.class : $(COMP1_RDB) + -$(MKDIR) $(subst /,$(PS),$(@D)) + $(JAVAMAKER) -nD $(COMP1_GENTYPELIST) -O$(COMP1_CLASS_OUT) $(COMP1_RDB) -X$(URE_TYPES) -X$(OFFICE_TYPES) + +$(COMP1_CLASSFILES) : $(COMP1_JAVAFILES) $(COMP1_GENCLASSFILES) + -$(MKDIR) $(subst /,$(PS),$(@D)) + $(SDK_JAVAC) $(JAVAC_FLAGS) -classpath "$(SDK_CLASSPATH)" -d $(COMP1_CLASS_OUT) $(COMP1_JAVAFILES) + +$(COMP2_CLASSFILES) : $(COMP2_JAVAFILES) + -$(MKDIR) $(subst /,$(PS),$(@D)) + $(SDK_JAVAC) $(JAVAC_FLAGS) -classpath "$(SDK_CLASSPATH)" -d $(COMP2_CLASS_OUT) $^ + +$(COMP1_JAR) : $(COMP1_MANIFESTFILE) $(COMP1_CLASSFILES) + -$(DEL) $(subst \\,\,$(subst /,$(PS),$@)) + -$(MKDIR) $(subst /,$(PS),$(@D)) + $(SDK_JAR) cvfm $@ $< -C $(COMP1_CLASS_OUT) . + +$(COMP2_JAR) : $(COMP2_MANIFESTFILE) $(COMP2_CLASSFILES) + -$(DEL) $(subst \\,\,$(subst /,$(PS),$@)) + -$(MKDIR) $(subst /,$(PS),$(@D)) + $(SDK_JAR) cvfm $@ $< -C $(COMP2_CLASS_OUT) . + +$(COMP1_UNOPKG_MANIFEST) : + -$(MKDIR) $(subst /,$(PS),$(@D)) + @echo $(OSEP)?xml version="$(QM)1.0$(QM)" encoding="$(QM)UTF-8$(QM)"?$(CSEP) > $@ + @echo $(OSEP)!DOCTYPE manifest:manifest PUBLIC "$(QM)-//OpenOffice.org//DTD Manifest 1.0//EN$(QM)" "$(QM)Manifest.dtd$(QM)"$(CSEP) >> $@ + @echo $(OSEP)manifest:manifest xmlns:manifest="$(QM)http://openoffice.org/2001/manifest$(QM)"$(CSEP) >> $@ + @echo $(SQM) $(SQM)$(OSEP)manifest:file-entry manifest:media-type="$(QM)application/vnd.sun.star.uno-typelibrary;type=RDB$(QM)" >> $@ + @echo $(SQM) $(SQM)manifest:full-path="$(QM)$(subst /META-INF,,$(subst $(COMP1_GEN_OUT)/,,$(@D))).uno.rdb$(QM)"/$(CSEP) >> $@ + @echo $(SQM) $(SQM)$(OSEP)manifest:file-entry manifest:media-type="$(QM)application/vnd.sun.star.uno-components$(QM)">> $@ + @echo $(SQM) $(SQM)manifest:full-path="$(QM)$(COMP1_COMPONENTS)$(QM)"/$(CSEP)>> $@ + @echo $(OSEP)/manifest:manifest$(CSEP) >> $@ + +$(COMP2_UNOPKG_MANIFEST) : + -$(MKDIR) $(subst /,$(PS),$(@D)) + @echo $(OSEP)?xml version="$(QM)1.0$(QM)" encoding="$(QM)UTF-8$(QM)"?$(CSEP) > $@ + @echo $(OSEP)!DOCTYPE manifest:manifest PUBLIC "$(QM)-//OpenOffice.org//DTD Manifest 1.0//EN$(QM)" "$(QM)Manifest.dtd$(QM)"$(CSEP) >> $@ + @echo $(OSEP)manifest:manifest xmlns:manifest="$(QM)http://openoffice.org/2001/manifest$(QM)"$(CSEP) >> $@ + @echo $(SQM) $(SQM)$(OSEP)manifest:file-entry manifest:media-type="$(QM)application/vnd.sun.star.uno-components$(QM)">> $@ + @echo $(SQM) $(SQM)manifest:full-path="$(QM)$(COMP2_COMPONENTS)$(QM)"/$(CSEP)>> $@ + @echo $(OSEP)/manifest:manifest$(CSEP) >> $@ + +$(COMP1_PACKAGE) : $(COMP1_RDB) $(COMP1_JAR) $(COMP1_UNOPKG_MANIFEST) $(COMP1_COMPONENTS) description.xml + echo "####" $(@) + -$(DEL) $(subst \\,\,$(subst /,$(PS),$@)) + -$(MKDIR) $(subst /,$(PS),$(@D)) + $(SDK_ZIP) $@ $(COMP1_COMPONENTS) description.xml + cd $(subst /,$(PS),$(COMP1_GEN_OUT)) && $(SDK_ZIP) -u ../../../bin/$(@F) $(<F) + cd $(subst /,$(PS),$(SAMPLE_CLASS_OUT)) && $(SDK_ZIP) -u ../../bin/$(@F) $(COMP1_JAR_NAME) + cd $(subst /,$(PS),$(COMP1_GEN_OUT)/$(subst .$(UNOOXT_EXT),,$(@F))) && $(SDK_ZIP) -u ../../../../bin/$(@F) META-INF/manifest.xml + +$(COMP2_PACKAGE) : $(COMP2_JAR) $(COMP2_UNOPKG_MANIFEST) $(COMP2_COMPONENTS) + -$(DEL) $(subst \\,\,$(subst /,$(PS),$@)) + -$(MKDIR) $(subst /,$(PS),$(@D)) + $(SDK_ZIP) $@ $(COMP2_COMPONENTS) + cd $(subst /,$(PS),$(SAMPLE_CLASS_OUT)) && $(SDK_ZIP) -u ../../bin/$(@F) $(<F) + cd $(subst /,$(PS),$(COMP2_GEN_OUT)/$(subst .$(UNOOXT_EXT),,$(@F))) && $(SDK_ZIP) -u ../../../../bin/$(@F) META-INF/manifest.xml + +$(SAMPLE_GEN_OUT)/devguide_$(COMP1_NAME)_register_component.flag : $(OUT_BIN)/$(COMP1_NAME).$(UNOOXT_EXT) +ifeq "$(SDK_AUTO_DEPLOYMENT)" "YES" + -$(DEL) $(subst \\,\,$(subst /,$(PS),$@)) + -$(MKDIR) $(subst /,$(PS),$(@D)) + $(DEPLOYTOOL) $(subst \\,\,"$(COMP_PACKAGE_DIR)$(PS)$(COMP1_NAME).$(UNOOXT_EXT)") + @echo flagged > $(subst /,$(PS),$@) +else + @echo -------------------------------------------------------------------------------- + @echo If you want to install your component automatically, please set the environment + @echo variable SDK_AUTO_DEPLOYMENT = YES. But note that auto deployment is only + @echo possible if no office instance is running. + @echo -------------------------------------------------------------------------------- +endif + +$(SAMPLE_GEN_OUT)/devguide_$(COMP2_NAME)_register_component.flag : $(OUT_BIN)/$(COMP2_NAME).$(UNOOXT_EXT) +ifeq "$(SDK_AUTO_DEPLOYMENT)" "YES" + -$(DEL) $(subst \\,\,$(subst /,$(PS),$@)) + -$(MKDIR) $(subst /,$(PS),$(@D)) + $(DEPLOYTOOL) $(subst \\,\,"$(COMP_PACKAGE_DIR)$(PS)$(COMP2_NAME).$(UNOOXT_EXT)") + @echo flagged > $(subst /,$(PS),$@) +else + @echo -------------------------------------------------------------------------------- + @echo If you want to install your component automatically, please set the environment + @echo variable SDK_AUTO_DEPLOYMENT = YES. But note that auto deployment is only + @echo possible if no office instance is running. + @echo -------------------------------------------------------------------------------- +endif + +$(APP_CLASSFILES) : $(APP_JAVAFILES) + -$(MKDIR) $(subst /,$(PS),$(@D)) + $(SDK_JAVAC) $(JAVAC_FLAGS) -classpath "$(SDK_CLASSPATH)" -d $(SAMPLE_CLASS_OUT) $(APP_JAVAFILES) + +$(APP1_CLASS_OUT)/%.class : %.java $(APP_CLASSFILES) + -$(MKDIR) $(subst /,$(PS),$(@D)) + $(SDK_JAVAC) $(JAVAC_FLAGS) -classpath "$(SDK_CLASSPATH)" -d $(APP1_CLASS_OUT) $< + +$(APP2_CLASS_OUT)/%.class : %.java $(APP_CLASSFILES) + -$(MKDIR) $(subst /,$(PS),$(@D)) + $(SDK_JAVAC) $(JAVAC_FLAGS) -classpath "$(SDK_CLASSPATH)" -d $(APP2_CLASS_OUT) $< + +$(APP3_CLASS_OUT)/%.class : %.java $(APP_CLASSFILES) + -$(MKDIR) $(subst /,$(PS),$(@D)) + $(SDK_JAVAC) $(JAVAC_FLAGS) -classpath "$(SDK_CLASSPATH)" -d $(APP3_CLASS_OUT) $< + +$(SAMPLE_GEN_OUT)/%.mf : + -$(MKDIR) $(subst /,$(PS),$(@D)) + @echo Main-Class: com.sun.star.lib.loader.Loader> $@ + $(ECHOLINE)>> $@ + @echo Name: com/sun/star/lib/loader/Loader.class>> $@ + @echo Application-Class: $(basename $(@F))>> $@ + +$(APP1_JAR) : $(APP1_GEN_OUT)/$(APP1_NAME).mf $(APP1_CLASS_OUT)/$(APP1_NAME).class $(APP_CLASSFILES) + -$(DEL) $(subst \\,\,$(subst /,$(PS),$@)) + -$(MKDIR) $(subst /,$(PS),$(@D)) + +$(SDK_JAR) cvfm $@ $< -C $(APP1_CLASS_OUT) . + +$(SDK_JAR) uvf $@ $(SDK_JAVA_UNO_BOOTSTRAP_FILES) + +cd $(subst /,$(PS),$(SAMPLE_CLASS_OUT)) && $(SDK_JAR) uvf $(@F) $(APP_CLASSNAMES) + +$(APP2_JAR) : $(APP2_GEN_OUT)/$(APP2_NAME).mf $(APP2_CLASS_OUT)/$(APP2_NAME).class $(APP_CLASSFILES) + -$(DEL) $(subst \\,\,$(subst /,$(PS),$@)) + -$(MKDIR) $(subst /,$(PS),$(@D)) + +$(SDK_JAR) cvfm $@ $< -C $(APP2_CLASS_OUT) . + +$(SDK_JAR) uvf $@ $(SDK_JAVA_UNO_BOOTSTRAP_FILES) + +cd $(subst /,$(PS),$(SAMPLE_CLASS_OUT)) && $(SDK_JAR) uvf $(@F) $(APP_CLASSNAMES) + +$(APP3_JAR) : $(APP3_GEN_OUT)/$(APP3_NAME).mf $(APP3_CLASS_OUT)/$(APP3_NAME).class $(APP_CLASSFILES) + -$(DEL) $(subst \\,\,$(subst /,$(PS),$@)) + -$(MKDIR) $(subst /,$(PS),$(@D)) + +$(SDK_JAR) cvfm $@ $< -C $(APP3_CLASS_OUT) . + +$(SDK_JAR) uvf $@ $(SDK_JAVA_UNO_BOOTSTRAP_FILES) + +cd $(subst /,$(PS),$(SAMPLE_CLASS_OUT)) && $(SDK_JAR) uvf $(@F) $(APP_CLASSNAMES) + +$(SAMPLE_NAME) : $(COMP1_REGISTERFLAG) $(COMP2_REGISTERFLAG) $(APP1_JAR) $(APP2_JAR) $(APP3_JAR) + @echo -------------------------------------------------------------------------------- + @echo Please use one of the following commands to execute the examples! + @echo - + @echo $(MAKE) $(APP1_NAME).run + @echo $(MAKE) $(APP2_NAME).run + @echo $(MAKE) $(APP3_NAME).run + @echo -------- + @echo The "$(QM)$(COMP1_NAME)$(QM)" and "$(QM)$(COMP2_NAME)$(QM)" component are installed if + @echo SDK_AUTO_DEPLOYMENT = YES. You can use this components inside your office + @echo installation, see the example descriptions. You can also load the "$(QM)ExampleAddIn.ods$(QM)" + @echo document to see how the add-in functions can be used. For more details about the + @echo "$(QM)$(COMP2_NAME)$(QM)" component see the "$(QM)DataPilotReadme.txt"$(QM). + @echo - + @echo $(MAKE) ExampleAddIn.ods.load + @echo -------------------------------------------------------------------------------- + +%.run: $(SAMPLE_CLASS_OUT)/%.jar + $(SDK_JAVA) -Dcom.sun.star.lib.loader.unopath="$(OFFICE_PROGRAM_PATH)" -jar $< + +ExampleAddIn.ods.load : $(COMP1_REGISTERFLAG) + "$(OFFICE_PROGRAM_PATH)$(PS)soffice" $(basename $@) + +.PHONY: clean +clean : + -$(DELRECURSIVE) $(subst /,$(PS),$(SAMPLE_CLASS_OUT)) + -$(DELRECURSIVE) $(subst /,$(PS),$(SAMPLE_GEN_OUT)) + -$(DEL) $(subst \\,\,$(subst /,$(PS),$(COMP1_PACKAGE_URL))) + -$(DEL) $(subst \\,\,$(subst /,$(PS),$(COMP2_PACKAGE_URL))) diff --git a/odk/examples/DevelopersGuide/Spreadsheet/SpreadsheetDocHelper.java b/odk/examples/DevelopersGuide/Spreadsheet/SpreadsheetDocHelper.java new file mode 100644 index 000000000..0696a866c --- /dev/null +++ b/odk/examples/DevelopersGuide/Spreadsheet/SpreadsheetDocHelper.java @@ -0,0 +1,388 @@ +/* -*- Mode: Java; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */ +/************************************************************************* + * + * The Contents of this file are made available subject to the terms of + * the BSD license. + * + * Copyright 2000, 2010 Oracle and/or its affiliates. + * All rights reserved. + * + * Redistribution and use in source and binary forms, with or without + * modification, are permitted provided that the following conditions + * are met: + * 1. Redistributions of source code must retain the above copyright + * notice, this list of conditions and the following disclaimer. + * 2. Redistributions in binary form must reproduce the above copyright + * notice, this list of conditions and the following disclaimer in the + * documentation and/or other materials provided with the distribution. + * 3. Neither the name of Sun Microsystems, Inc. nor the names of its + * contributors may be used to endorse or promote products derived + * from this software without specific prior written permission. + * + * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS + * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT + * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS + * FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE + * COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, + * INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, + * BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS + * OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND + * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR + * TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE + * USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. + * + *************************************************************************/ + +import com.sun.star.frame.XComponentLoader; +import com.sun.star.lang.XComponent; + +import com.sun.star.uno.UnoRuntime; +import com.sun.star.uno.RuntimeException; + +// __________ implementation ____________________________________ + +/** This is a helper class for the spreadsheet and table samples. + It connects to a running office and creates a spreadsheet document. + Additionally it contains various helper functions. + */ +public class SpreadsheetDocHelper +{ + +// __ private members ___________________________________________ + + private com.sun.star.uno.XComponentContext mxRemoteContext; + private com.sun.star.lang.XMultiComponentFactory mxRemoteServiceManager; + private com.sun.star.sheet.XSpreadsheetDocument mxDocument; + + + + public SpreadsheetDocHelper( String[] args ) + { + // Connect to a running office and get the service manager + connect(); + + // Create a new spreadsheet document + try + { + mxDocument = initDocument(); + } + catch (Exception ex) + { + System.err.println( "Couldn't create document: " + ex ); + System.err.println( "Error: Couldn't create Document\nException Message = " + + ex.getMessage()); + ex.printStackTrace(); + System.exit( 1 ); + } + } + +// __ helper methods ____________________________________________ + + /** Returns the service manager of the connected office. + @return XMultiComponentFactory interface of the service manager. */ + public com.sun.star.lang.XMultiComponentFactory getServiceManager() + { + return mxRemoteServiceManager; + } + + /** Returns the component context of the connected office + @return XComponentContext interface of the context. */ + public com.sun.star.uno.XComponentContext getContext() + { + return mxRemoteContext; + } + + /** Returns the whole spreadsheet document. + @return XSpreadsheetDocument interface of the document. */ + public com.sun.star.sheet.XSpreadsheetDocument getDocument() + { + return mxDocument; + } + + /** Returns the spreadsheet with the specified index (0-based). + @param nIndex The index of the sheet. + @return XSpreadsheet interface of the sheet. */ + public com.sun.star.sheet.XSpreadsheet getSpreadsheet( int nIndex ) + { + // Collection of sheets + com.sun.star.sheet.XSpreadsheets xSheets = mxDocument.getSheets(); + com.sun.star.sheet.XSpreadsheet xSheet = null; + try + { + com.sun.star.container.XIndexAccess xSheetsIA = + UnoRuntime.queryInterface( + com.sun.star.container.XIndexAccess.class, xSheets ); + xSheet = UnoRuntime.queryInterface( + com.sun.star.sheet.XSpreadsheet.class, xSheetsIA.getByIndex(nIndex)); + } + catch (Exception ex) + { + System.err.println( "Error: caught exception in getSpreadsheet()!\nException Message = " + + ex.getMessage()); + ex.printStackTrace(); + } + return xSheet; + } + + /** Inserts a new empty spreadsheet with the specified name. + @param aName The name of the new sheet. + @param nIndex The insertion index. + @return The XSpreadsheet interface of the new sheet. */ + public com.sun.star.sheet.XSpreadsheet insertSpreadsheet( + String aName, short nIndex ) + { + // Collection of sheets + com.sun.star.sheet.XSpreadsheets xSheets = mxDocument.getSheets(); + com.sun.star.sheet.XSpreadsheet xSheet = null; + try + { + xSheets.insertNewByName( aName, nIndex ); + xSheet = UnoRuntime.queryInterface(com.sun.star.sheet.XSpreadsheet.class, + xSheets.getByName( aName )); + } + catch (Exception ex) + { + System.err.println( "Error: caught exception in insertSpreadsheet()!\nException Message = " + + ex.getMessage()); + ex.printStackTrace(); + } + return xSheet; + } + + +// Methods to fill values into cells. + + /** Writes a double value into a spreadsheet. + @param xSheet The XSpreadsheet interface of the spreadsheet. + @param aCellName The address of the cell (or a named range). + @param fValue The value to write into the cell. */ + public void setValue( + com.sun.star.sheet.XSpreadsheet xSheet, + String aCellName, + double fValue ) throws RuntimeException, Exception + { + xSheet.getCellRangeByName( aCellName ).getCellByPosition( 0, 0 ).setValue( fValue ); + } + + /** Writes a formula into a spreadsheet. + @param xSheet The XSpreadsheet interface of the spreadsheet. + @param aCellName The address of the cell (or a named range). + @param aFormula The formula to write into the cell. */ + public void setFormula( + com.sun.star.sheet.XSpreadsheet xSheet, + String aCellName, + String aFormula ) throws RuntimeException, Exception + { + xSheet.getCellRangeByName( aCellName ).getCellByPosition( 0, 0 ).setFormula( aFormula ); + } + + /** Writes a date with standard date format into a spreadsheet. + @param xSheet The XSpreadsheet interface of the spreadsheet. + @param aCellName The address of the cell (or a named range). + @param nDay The day of the date. + @param nMonth The month of the date. + @param nYear The year of the date. */ + public void setDate( + com.sun.star.sheet.XSpreadsheet xSheet, + String aCellName, + int nDay, int nMonth, int nYear ) throws RuntimeException, Exception + { + // Set the date value. + com.sun.star.table.XCell xCell = xSheet.getCellRangeByName( aCellName ).getCellByPosition( 0, 0 ); + String aDateStr = nMonth + "/" + nDay + "/" + nYear; + xCell.setFormula( aDateStr ); + + // Set standard date format. + com.sun.star.util.XNumberFormatsSupplier xFormatsSupplier = + UnoRuntime.queryInterface( + com.sun.star.util.XNumberFormatsSupplier.class, getDocument() ); + com.sun.star.util.XNumberFormatTypes xFormatTypes = + UnoRuntime.queryInterface( + com.sun.star.util.XNumberFormatTypes.class, xFormatsSupplier.getNumberFormats() ); + int nFormat = xFormatTypes.getStandardFormat( + com.sun.star.util.NumberFormat.DATE, new com.sun.star.lang.Locale() ); + + com.sun.star.beans.XPropertySet xPropSet = UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xCell ); + xPropSet.setPropertyValue( "NumberFormat", Integer.valueOf( nFormat ) ); + } + + /** Draws a colored border around the range and writes the headline in the + first cell. + @param xSheet The XSpreadsheet interface of the spreadsheet. + @param aRange The address of the cell range (or a named range). + @param aHeadline The headline text. */ + public void prepareRange( + com.sun.star.sheet.XSpreadsheet xSheet, + String aRange, String aHeadline ) throws RuntimeException, Exception + { + com.sun.star.beans.XPropertySet xPropSet = null; + com.sun.star.table.XCellRange xCellRange = null; + + // draw border + xCellRange = xSheet.getCellRangeByName( aRange ); + xPropSet = UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xCellRange ); + com.sun.star.table.BorderLine aLine = new com.sun.star.table.BorderLine(); + aLine.Color = 0x99CCFF; + aLine.InnerLineWidth = aLine.LineDistance = 0; + aLine.OuterLineWidth = 100; + com.sun.star.table.TableBorder aBorder = new com.sun.star.table.TableBorder(); + aBorder.TopLine = aBorder.BottomLine = aBorder.LeftLine = aBorder.RightLine = aLine; + aBorder.IsTopLineValid = aBorder.IsBottomLineValid = true; + aBorder.IsLeftLineValid = aBorder.IsRightLineValid = true; + xPropSet.setPropertyValue( "TableBorder", aBorder ); + + // draw headline + com.sun.star.sheet.XCellRangeAddressable xAddr = UnoRuntime.queryInterface( com.sun.star.sheet.XCellRangeAddressable.class, xCellRange ); + com.sun.star.table.CellRangeAddress aAddr = xAddr.getRangeAddress(); + + xCellRange = xSheet.getCellRangeByPosition( + aAddr.StartColumn, aAddr.StartRow, aAddr.EndColumn, aAddr.StartRow ); + xPropSet = UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xCellRange ); + xPropSet.setPropertyValue( "CellBackColor", Integer.valueOf( 0x99CCFF ) ); + // write headline + com.sun.star.table.XCell xCell = xCellRange.getCellByPosition( 0, 0 ); + xCell.setFormula( aHeadline ); + xPropSet = UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xCell ); + xPropSet.setPropertyValue( "CharColor", Integer.valueOf( 0x003399 ) ); + xPropSet.setPropertyValue( "CharWeight", new Float( com.sun.star.awt.FontWeight.BOLD ) ); + } + + +// Methods to create cell addresses and range addresses. + + /** Creates a com.sun.star.table.CellAddress and initializes it + with the given range. + @param xSheet The XSpreadsheet interface of the spreadsheet. + @param aCell The address of the cell (or a named cell). */ + public com.sun.star.table.CellAddress createCellAddress( + com.sun.star.sheet.XSpreadsheet xSheet, + String aCell ) throws RuntimeException, Exception + { + com.sun.star.sheet.XCellAddressable xAddr = UnoRuntime.queryInterface( com.sun.star.sheet.XCellAddressable.class, + xSheet.getCellRangeByName( aCell ).getCellByPosition( 0, 0 ) ); + return xAddr.getCellAddress(); + } + + /** Creates a com.sun.star.table.CellRangeAddress and initializes + it with the given range. + @param xSheet The XSpreadsheet interface of the spreadsheet. + @param aRange The address of the cell range (or a named range). */ + public com.sun.star.table.CellRangeAddress createCellRangeAddress( + com.sun.star.sheet.XSpreadsheet xSheet, String aRange ) + { + com.sun.star.sheet.XCellRangeAddressable xAddr = UnoRuntime.queryInterface( com.sun.star.sheet.XCellRangeAddressable.class, + xSheet.getCellRangeByName( aRange ) ); + return xAddr.getRangeAddress(); + } + + +// Methods to convert cell addresses and range addresses to strings. + + /** Returns the text address of the cell. + @param nColumn The column index. + @param nRow The row index. + @return A string containing the cell address. */ + public String getCellAddressString( int nColumn, int nRow ) + { + String aStr = ""; + if (nColumn > 25) + aStr += (char) ('A' + nColumn / 26 - 1); + aStr += (char) ('A' + nColumn % 26); + aStr += (nRow + 1); + return aStr; + } + + /** Returns the text address of the cell range. + @param aCellRange The cell range address. + @return A string containing the cell range address. */ + public String getCellRangeAddressString( + com.sun.star.table.CellRangeAddress aCellRange ) + { + return + getCellAddressString( aCellRange.StartColumn, aCellRange.StartRow ) + + ":" + + getCellAddressString( aCellRange.EndColumn, aCellRange.EndRow ); + } + + /** Returns the text address of the cell range. + @param xCellRange The XSheetCellRange interface of the cell range. + @param bWithSheet true = Include sheet name. + @return A string containing the cell range address. */ + public String getCellRangeAddressString( + com.sun.star.sheet.XSheetCellRange xCellRange, + boolean bWithSheet ) + { + String aStr = ""; + if (bWithSheet) + { + com.sun.star.sheet.XSpreadsheet xSheet = xCellRange.getSpreadsheet(); + com.sun.star.container.XNamed xNamed = UnoRuntime.queryInterface( com.sun.star.container.XNamed.class, xSheet ); + aStr += xNamed.getName() + "."; + } + com.sun.star.sheet.XCellRangeAddressable xAddr = UnoRuntime.queryInterface( com.sun.star.sheet.XCellRangeAddressable.class, xCellRange ); + aStr += getCellRangeAddressString( xAddr.getRangeAddress() ); + return aStr; + } + + /** Returns a list of addresses of all cell ranges contained in the collection. + @param xRangesIA The XIndexAccess interface of the collection. + @return A string containing the cell range address list. */ + public String getCellRangeListString( + com.sun.star.container.XIndexAccess xRangesIA ) throws RuntimeException, Exception + { + String aStr = ""; + int nCount = xRangesIA.getCount(); + for (int nIndex = 0; nIndex < nCount; ++nIndex) + { + if (nIndex > 0) + aStr += " "; + Object aRangeObj = xRangesIA.getByIndex( nIndex ); + com.sun.star.sheet.XSheetCellRange xCellRange = UnoRuntime.queryInterface( com.sun.star.sheet.XSheetCellRange.class, aRangeObj ); + aStr += getCellRangeAddressString( xCellRange, false ); + } + return aStr; + } + + + + // Connect to a running office that is accepting connections. + private void connect() + { + if (mxRemoteContext == null && mxRemoteServiceManager == null) { + try { + // First step: get the remote office component context + mxRemoteContext = com.sun.star.comp.helper.Bootstrap.bootstrap(); + System.out.println("Connected to a running office ..."); + + mxRemoteServiceManager = mxRemoteContext.getServiceManager(); + } + catch( Exception e) { + System.err.println("ERROR: can't get a component context from a running office ..."); + e.printStackTrace(); + System.exit(1); + } + } + } + + /** Creates an empty spreadsheet document. + @return The XSpreadsheetDocument interface of the document. */ + private com.sun.star.sheet.XSpreadsheetDocument initDocument() + throws RuntimeException, Exception + { + XComponentLoader aLoader = UnoRuntime.queryInterface( + XComponentLoader.class, + mxRemoteServiceManager.createInstanceWithContext( + "com.sun.star.frame.Desktop", mxRemoteContext)); + + XComponent xComponent = aLoader.loadComponentFromURL( + "private:factory/scalc", "_blank", 0, + new com.sun.star.beans.PropertyValue[0] ); + + return UnoRuntime.queryInterface( + com.sun.star.sheet.XSpreadsheetDocument.class, xComponent ); + } + + +} + +/* vim:set shiftwidth=4 softtabstop=4 expandtab: */ diff --git a/odk/examples/DevelopersGuide/Spreadsheet/SpreadsheetSample.java b/odk/examples/DevelopersGuide/Spreadsheet/SpreadsheetSample.java new file mode 100644 index 000000000..ce6555528 --- /dev/null +++ b/odk/examples/DevelopersGuide/Spreadsheet/SpreadsheetSample.java @@ -0,0 +1,1347 @@ +/* -*- Mode: Java; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */ +/************************************************************************* + * + * The Contents of this file are made available subject to the terms of + * the BSD license. + * + * Copyright 2000, 2010 Oracle and/or its affiliates. + * All rights reserved. + * + * Redistribution and use in source and binary forms, with or without + * modification, are permitted provided that the following conditions + * are met: + * 1. Redistributions of source code must retain the above copyright + * notice, this list of conditions and the following disclaimer. + * 2. Redistributions in binary form must reproduce the above copyright + * notice, this list of conditions and the following disclaimer in the + * documentation and/or other materials provided with the distribution. + * 3. Neither the name of Sun Microsystems, Inc. nor the names of its + * contributors may be used to endorse or promote products derived + * from this software without specific prior written permission. + * + * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS + * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT + * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS + * FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE + * COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, + * INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, + * BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS + * OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND + * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR + * TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE + * USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. + * + *************************************************************************/ + +import com.sun.star.uno.UnoRuntime; +import com.sun.star.uno.RuntimeException; +import com.sun.star.uno.AnyConverter; + +// __________ implementation ____________________________________ + +/** Create and modify a spreadsheet document. + */ +public class SpreadsheetSample extends SpreadsheetDocHelper +{ + + + + public static void main( String args[] ) + { + try + { + SpreadsheetSample aSample = new SpreadsheetSample( args ); + aSample.doSampleFunction(); + } + catch (Exception ex) + { + System.out.println( "Error: Sample caught exception!\nException Message = " + + ex.getMessage()); + ex.printStackTrace(); + System.exit( 1 ); + } + System.out.println( "\nSamples done." ); + System.exit( 0 ); + } + + + + public SpreadsheetSample( String[] args ) + { + super( args ); + } + + + + /** This sample function performs all changes on the document. */ + public void doSampleFunction() + { + try + { + doCellSamples(); + } + catch (Exception ex) + { + System.out.println( "\nError: Cell sample caught exception!\nException Message = " + + ex.getMessage()); + ex.printStackTrace(); + } + + try + { + doCellRangeSamples(); + } + catch (Exception ex) + { + System.out.println( "\nError: Cell range sample caught exception!\nException Message = " + + ex.getMessage()); + ex.printStackTrace(); + } + + try + { + doCellRangesSamples(); + } + catch (Exception ex) + { + System.out.println( "\nError: Cell range container sample caught exception!\nException Message = " + + ex.getMessage()); + ex.printStackTrace(); + } + + try + { + doCellCursorSamples(); + } + catch (Exception ex) + { + System.out.println( "\nError: Cell cursor sample caught exception!\nException Message = " + + ex.getMessage()); + ex.printStackTrace(); + } + + try + { + doFormattingSamples(); + } + catch (Exception ex) + { + System.out.println( "\nError: Formatting sample caught exception!\nException Message = " + + ex.getMessage()); + ex.printStackTrace(); + } + + try + { + doDocumentSamples(); + } + catch (Exception ex) + { + System.out.println( "\nError: Document sample caught exception!\nException Message = " + + ex.getMessage()); + ex.printStackTrace(); + } + + try + { + doDatabaseSamples(); + } + catch( Exception ex ) + { + System.out.println( "\nError: Database sample caught exception!\nException Message = " + + ex.getMessage()); + ex.printStackTrace(); + } + + try + { + doDataPilotSamples(); + } + catch (Exception ex) + { + System.out.println( "\nError: Data pilot sample caught exception!\nException Message = " + + ex.getMessage()); + ex.printStackTrace(); + } + + try + { + doNamedRangesSamples(); + } + catch( Exception ex ) + { + System.out.println( "\nError: Named ranges sample caught exception!\nException Message = " + + ex.getMessage()); + ex.printStackTrace(); + } + + try + { + doFunctionAccessSamples(); + } + catch (Exception ex) + { + System.out.println( "\nError: Function access sample caught exception!\nException Message = " + + ex.getMessage()); + ex.printStackTrace(); + } + + try + { + doApplicationSettingsSamples(); + } + catch (Exception ex) + { + System.out.println( "\nError: Application settings sample caught exception!\nException Message = " + + ex.getMessage()); + ex.printStackTrace(); + } + } + + + + /** All samples regarding the service com.sun.star.sheet.SheetCell. */ + private void doCellSamples() throws RuntimeException, Exception + { + System.out.println( "\n*** Samples for service sheet.SheetCell ***\n" ); + com.sun.star.sheet.XSpreadsheet xSheet = getSpreadsheet( 0 ); + com.sun.star.table.XCell xCell = null; + com.sun.star.beans.XPropertySet xPropSet = null; + String aText; + prepareRange( xSheet, "A1:C7", "Cells and Cell Ranges" ); + + // --- Get cell B3 by position - (column, row) --- + xCell = xSheet.getCellByPosition( 1, 2 ); + + + // --- Insert two text paragraphs into the cell. --- + com.sun.star.text.XText xText = UnoRuntime.queryInterface( com.sun.star.text.XText.class, xCell ); + com.sun.star.text.XTextCursor xTextCursor = xText.createTextCursor(); + + xText.insertString( xTextCursor, "Text in first line.", false ); + xText.insertControlCharacter( xTextCursor, + com.sun.star.text.ControlCharacter.PARAGRAPH_BREAK, false ); + xText.insertString( xTextCursor, "And a ", false ); + + // create a hyperlink + com.sun.star.lang.XMultiServiceFactory xServiceMan = UnoRuntime.queryInterface( com.sun.star.lang.XMultiServiceFactory.class, getDocument() ); + Object aHyperlinkObj = xServiceMan.createInstance( "com.sun.star.text.TextField.URL" ); + xPropSet = UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, aHyperlinkObj ); + xPropSet.setPropertyValue( "URL", "http://www.example.org" ); + xPropSet.setPropertyValue( "Representation", "hyperlink" ); + // ... and insert + com.sun.star.text.XTextContent xContent = UnoRuntime.queryInterface( com.sun.star.text.XTextContent.class, aHyperlinkObj ); + xText.insertTextContent( xTextCursor, xContent, false ); + + + // --- Query the separate paragraphs. --- + com.sun.star.container.XEnumerationAccess xParaEA = + UnoRuntime.queryInterface( + com.sun.star.container.XEnumerationAccess.class, xCell ); + com.sun.star.container.XEnumeration xParaEnum = xParaEA.createEnumeration(); + // Go through the paragraphs + while( xParaEnum.hasMoreElements() ) + { + Object aPortionObj = xParaEnum.nextElement(); + com.sun.star.container.XEnumerationAccess xPortionEA = + UnoRuntime.queryInterface( + com.sun.star.container.XEnumerationAccess.class, aPortionObj ); + com.sun.star.container.XEnumeration xPortionEnum = xPortionEA.createEnumeration(); + aText = ""; + // Go through all text portions of a paragraph and construct string. + while( xPortionEnum.hasMoreElements() ) + { + com.sun.star.text.XTextRange xRange = UnoRuntime.queryInterface(com.sun.star.text.XTextRange.class, + xPortionEnum.nextElement()); + aText += xRange.getString(); + } + System.out.println( "Paragraph text: " + aText ); + } + + + // --- Change cell properties. --- + xPropSet = UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xCell ); + // from styles.CharacterProperties + xPropSet.setPropertyValue( "CharColor", Integer.valueOf( 0x003399 ) ); + xPropSet.setPropertyValue( "CharHeight", new Float( 20.0 ) ); + // from styles.ParagraphProperties + xPropSet.setPropertyValue( "ParaLeftMargin", Integer.valueOf( 500 ) ); + // from table.CellProperties + xPropSet.setPropertyValue( "IsCellBackgroundTransparent", Boolean.FALSE ); + xPropSet.setPropertyValue( "CellBackColor", Integer.valueOf( 0x99CCFF ) ); + + + // --- Get cell address. --- + com.sun.star.sheet.XCellAddressable xCellAddr = UnoRuntime.queryInterface( com.sun.star.sheet.XCellAddressable.class, xCell ); + com.sun.star.table.CellAddress aAddress = xCellAddr.getCellAddress(); + aText = "Address of this cell: Column=" + aAddress.Column; + aText += "; Row=" + aAddress.Row; + aText += "; Sheet=" + aAddress.Sheet; + System.out.println( aText ); + + + // --- Insert an annotation --- + com.sun.star.sheet.XSheetAnnotationsSupplier xAnnotationsSupp = + UnoRuntime.queryInterface( + com.sun.star.sheet.XSheetAnnotationsSupplier.class, xSheet ); + com.sun.star.sheet.XSheetAnnotations xAnnotations = xAnnotationsSupp.getAnnotations(); + xAnnotations.insertNew( aAddress, "This is an annotation" ); + + com.sun.star.sheet.XSheetAnnotationAnchor xAnnotAnchor = UnoRuntime.queryInterface( com.sun.star.sheet.XSheetAnnotationAnchor.class, xCell ); + com.sun.star.sheet.XSheetAnnotation xAnnotation = xAnnotAnchor.getAnnotation(); + xAnnotation.setIsVisible( true ); + } + + + + /** All samples regarding the service com.sun.star.sheet.SheetCellRange. */ + private void doCellRangeSamples() throws RuntimeException, Exception + { + System.out.println( "\n*** Samples for service sheet.SheetCellRange ***\n" ); + com.sun.star.sheet.XSpreadsheet xSheet = getSpreadsheet( 0 ); + com.sun.star.table.XCellRange xCellRange = null; + com.sun.star.beans.XPropertySet xPropSet = null; + com.sun.star.table.CellRangeAddress aRangeAddress = null; + + // Preparation + setFormula( xSheet, "B5", "First cell" ); + setFormula( xSheet, "B6", "Second cell" ); + // Get cell range B5:B6 by position - (column, row, column, row) + xCellRange = xSheet.getCellRangeByPosition( 1, 4, 1, 5 ); + + + // --- Change cell range properties. --- + xPropSet = UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xCellRange ); + // from com.sun.star.styles.CharacterProperties + xPropSet.setPropertyValue( "CharColor", Integer.valueOf( 0x003399 ) ); + xPropSet.setPropertyValue( "CharHeight", new Float( 20.0 ) ); + // from com.sun.star.styles.ParagraphProperties + xPropSet.setPropertyValue( "ParaLeftMargin", Integer.valueOf( 500 ) ); + // from com.sun.star.table.CellProperties + xPropSet.setPropertyValue( "IsCellBackgroundTransparent", Boolean.FALSE ); + xPropSet.setPropertyValue( "CellBackColor", Integer.valueOf( 0x99CCFF ) ); + + + // --- Replace text in all cells. --- + com.sun.star.util.XReplaceable xReplace = UnoRuntime.queryInterface( com.sun.star.util.XReplaceable.class, xCellRange ); + com.sun.star.util.XReplaceDescriptor xReplaceDesc = xReplace.createReplaceDescriptor(); + xReplaceDesc.setSearchString( "cell" ); + xReplaceDesc.setReplaceString( "text" ); + // property SearchWords searches for whole cells! + xReplaceDesc.setPropertyValue( "SearchWords", Boolean.FALSE ); + int nCount = xReplace.replaceAll( xReplaceDesc ); + System.out.println( "Search text replaced " + nCount + " times." ); + + + // --- Merge cells. --- + xCellRange = xSheet.getCellRangeByName( "F3:G6" ); + prepareRange( xSheet, "E1:H7", "XMergeable" ); + com.sun.star.util.XMergeable xMerge = UnoRuntime.queryInterface( com.sun.star.util.XMergeable.class, xCellRange ); + xMerge.merge( true ); + + + // --- Change indentation. --- +/* does not work (bug in XIndent implementation) + prepareRange( xSheet, "I20:I23", "XIndent" ); + setValue( xSheet, "I21", 1 ); + setValue( xSheet, "I22", 1 ); + setValue( xSheet, "I23", 1 ); + + xCellRange = xSheet.getCellRangeByName( "I21:I22" ); + com.sun.star.util.XIndent xIndent = (com.sun.star.util.XIndent) + UnoRuntime.queryInterface( com.sun.star.util.XIndent.class, xCellRange ); + xIndent.incrementIndent(); + + xCellRange = xSheet.getCellRangeByName( "I22:I23" ); + xIndent = (com.sun.star.util.XIndent) + UnoRuntime.queryInterface( com.sun.star.util.XIndent.class, xCellRange ); + xIndent.incrementIndent(); +*/ + + + // --- Column properties. --- + xCellRange = xSheet.getCellRangeByName( "B1" ); + com.sun.star.table.XColumnRowRange xColRowRange = UnoRuntime.queryInterface( com.sun.star.table.XColumnRowRange.class, xCellRange ); + com.sun.star.table.XTableColumns xColumns = xColRowRange.getColumns(); + + Object aColumnObj = xColumns.getByIndex( 0 ); + xPropSet = UnoRuntime.queryInterface( + com.sun.star.beans.XPropertySet.class, aColumnObj ); + xPropSet.setPropertyValue( "Width", Integer.valueOf( 6000 ) ); + + com.sun.star.container.XNamed xNamed = UnoRuntime.queryInterface( com.sun.star.container.XNamed.class, aColumnObj ); + System.out.println( "The name of the wide column is " + xNamed.getName() + "." ); + + + // --- Cell range data --- + prepareRange( xSheet, "A9:C30", "XCellRangeData" ); + + xCellRange = xSheet.getCellRangeByName( "A10:C30" ); + com.sun.star.sheet.XCellRangeData xData = UnoRuntime.queryInterface( com.sun.star.sheet.XCellRangeData.class, xCellRange ); + Object[][] aValues = + { + { "Name", "Fruit", "Quantity" }, + { "Alice", "Apples", new Double( 3.0 ) }, + { "Alice", "Oranges", new Double( 7.0 ) }, + { "Bob", "Apples", new Double( 3.0 ) }, + { "Alice", "Apples", new Double( 9.0 ) }, + { "Bob", "Apples", new Double( 5.0 ) }, + { "Bob", "Oranges", new Double( 6.0 ) }, + { "Alice", "Oranges", new Double( 3.0 ) }, + { "Alice", "Apples", new Double( 8.0 ) }, + { "Alice", "Oranges", new Double( 1.0 ) }, + { "Bob", "Oranges", new Double( 2.0 ) }, + { "Bob", "Oranges", new Double( 7.0 ) }, + { "Bob", "Apples", new Double( 1.0 ) }, + { "Alice", "Apples", new Double( 8.0 ) }, + { "Alice", "Oranges", new Double( 8.0 ) }, + { "Alice", "Apples", new Double( 7.0 ) }, + { "Bob", "Apples", new Double( 1.0 ) }, + { "Bob", "Oranges", new Double( 9.0 ) }, + { "Bob", "Oranges", new Double( 3.0 ) }, + { "Alice", "Oranges", new Double( 4.0 ) }, + { "Alice", "Apples", new Double( 9.0 ) } + }; + xData.setDataArray( aValues ); + + + // --- Get cell range address. --- + com.sun.star.sheet.XCellRangeAddressable xRangeAddr = UnoRuntime.queryInterface( com.sun.star.sheet.XCellRangeAddressable.class, xCellRange ); + aRangeAddress = xRangeAddr.getRangeAddress(); + System.out.println( "Address of this range: Sheet=" + aRangeAddress.Sheet ); + System.out.println( "Start column=" + aRangeAddress.StartColumn + "; Start row=" + aRangeAddress.StartRow ); + System.out.println( "End column =" + aRangeAddress.EndColumn + "; End row =" + aRangeAddress.EndRow ); + + + // --- Sheet operation. --- + // uses the range filled with XCellRangeData + com.sun.star.sheet.XSheetOperation xSheetOp = UnoRuntime.queryInterface( com.sun.star.sheet.XSheetOperation.class, xData ); + double fResult = xSheetOp.computeFunction( com.sun.star.sheet.GeneralFunction.AVERAGE ); + System.out.println( "Average value of the data table A10:C30: " + fResult ); + + + // --- Fill series --- + // Prepare the example + setValue( xSheet, "E10", 1 ); + setValue( xSheet, "E11", 4 ); + setDate( xSheet, "E12", 30, 1, 2002 ); + setFormula( xSheet, "I13", "Text 10" ); + setFormula( xSheet, "E14", "Jan" ); + setValue( xSheet, "K14", 10 ); + setValue( xSheet, "E16", 1 ); + setValue( xSheet, "F16", 2 ); + setDate( xSheet, "E17", 28, 2, 2002 ); + setDate( xSheet, "F17", 28, 1, 2002 ); + setValue( xSheet, "E18", 6 ); + setValue( xSheet, "F18", 4 ); + + com.sun.star.sheet.XCellSeries xSeries = null; + // Fill 2 rows linear with end value -> 2nd series is not filled completely + xSeries = getCellSeries( xSheet, "E10:I11" ); + xSeries.fillSeries( com.sun.star.sheet.FillDirection.TO_RIGHT, com.sun.star.sheet.FillMode.LINEAR, + com.sun.star.sheet.FillDateMode.FILL_DATE_DAY, 2, 9 ); + // Add months to a date + xSeries = getCellSeries( xSheet, "E12:I12" ); + xSeries.fillSeries( com.sun.star.sheet.FillDirection.TO_RIGHT, com.sun.star.sheet.FillMode.DATE, + com.sun.star.sheet.FillDateMode.FILL_DATE_MONTH, 1, 0x7FFFFFFF ); + // Fill right to left with a text containing a value + xSeries = getCellSeries( xSheet, "E13:I13" ); + xSeries.fillSeries( com.sun.star.sheet.FillDirection.TO_LEFT, com.sun.star.sheet.FillMode.LINEAR, + com.sun.star.sheet.FillDateMode.FILL_DATE_DAY, 10, 0x7FFFFFFF ); + // Fill with an user defined list + xSeries = getCellSeries( xSheet, "E14:I14" ); + xSeries.fillSeries( com.sun.star.sheet.FillDirection.TO_RIGHT, com.sun.star.sheet.FillMode.AUTO, + com.sun.star.sheet.FillDateMode.FILL_DATE_DAY, 1, 0x7FFFFFFF ); + // Fill bottom to top with a geometric series + xSeries = getCellSeries( xSheet, "K10:K14" ); + xSeries.fillSeries( com.sun.star.sheet.FillDirection.TO_TOP, com.sun.star.sheet.FillMode.GROWTH, + com.sun.star.sheet.FillDateMode.FILL_DATE_DAY, 2, 0x7FFFFFFF ); + // Auto fill + xSeries = getCellSeries( xSheet, "E16:K18" ); + xSeries.fillAuto( com.sun.star.sheet.FillDirection.TO_RIGHT, 2 ); + // Fill series copies cell formats -> draw border here + prepareRange( xSheet, "E9:K18", "XCellSeries" ); + + + // --- Array formulas --- + xCellRange = xSheet.getCellRangeByName( "E21:G23" ); + prepareRange( xSheet, "E20:G23", "XArrayFormulaRange" ); + com.sun.star.sheet.XArrayFormulaRange xArrayFormula = UnoRuntime.queryInterface( com.sun.star.sheet.XArrayFormulaRange.class, xCellRange ); + // Insert a 3x3 unit matrix. + xArrayFormula.setArrayFormula( "=A10:C12" ); + System.out.println( "Array formula is: " + xArrayFormula.getArrayFormula() ); + + + // --- Multiple operations --- + setFormula( xSheet, "E26", "=E27^F26" ); + setValue( xSheet, "E27", 1 ); + setValue( xSheet, "F26", 1 ); + getCellSeries( xSheet, "E27:E31" ).fillAuto( com.sun.star.sheet.FillDirection.TO_BOTTOM, 1 ); + getCellSeries( xSheet, "F26:J26" ).fillAuto( com.sun.star.sheet.FillDirection.TO_RIGHT, 1 ); + setFormula( xSheet, "F33", "=SIN(E33)" ); + setFormula( xSheet, "G33", "=COS(E33)" ); + setFormula( xSheet, "H33", "=TAN(E33)" ); + setValue( xSheet, "E34", 0 ); + setValue( xSheet, "E35", 0.2 ); + getCellSeries( xSheet, "E34:E38" ).fillAuto( com.sun.star.sheet.FillDirection.TO_BOTTOM, 2 ); + prepareRange( xSheet, "E25:J38", "XMultipleOperation" ); + + com.sun.star.table.CellRangeAddress aFormulaRange = createCellRangeAddress( xSheet, "E26" ); + com.sun.star.table.CellAddress aColCell = createCellAddress( xSheet, "E27" ); + com.sun.star.table.CellAddress aRowCell = createCellAddress( xSheet, "F26" ); + + xCellRange = xSheet.getCellRangeByName( "E26:J31" ); + com.sun.star.sheet.XMultipleOperation xMultOp = UnoRuntime.queryInterface( com.sun.star.sheet.XMultipleOperation.class, xCellRange ); + xMultOp.setTableOperation( + aFormulaRange, com.sun.star.sheet.TableOperationMode.BOTH, aColCell, aRowCell ); + + aFormulaRange = createCellRangeAddress( xSheet, "F33:H33" ); + aColCell = createCellAddress( xSheet, "E33" ); + // Row cell not needed + + xCellRange = xSheet.getCellRangeByName( "E34:H38" ); + xMultOp = UnoRuntime.queryInterface( com.sun.star.sheet.XMultipleOperation.class, xCellRange ); + xMultOp.setTableOperation( + aFormulaRange, com.sun.star.sheet.TableOperationMode.COLUMN, aColCell, aRowCell ); + + + // --- Cell Ranges Query --- + xCellRange = xSheet.getCellRangeByName( "A10:C30" ); + com.sun.star.sheet.XCellRangesQuery xRangesQuery = UnoRuntime.queryInterface( com.sun.star.sheet.XCellRangesQuery.class, xCellRange ); + com.sun.star.sheet.XSheetCellRanges xCellRanges = + xRangesQuery.queryContentCells( (short)com.sun.star.sheet.CellFlags.STRING ); + System.out.println( + "Cells in A10:C30 containing text: " + + xCellRanges.getRangeAddressesAsString() ); + } + + /** Returns the XCellSeries interface of a cell range. + @param xSheet The spreadsheet containing the cell range. + @param aRange The address of the cell range. + @return The XCellSeries interface. */ + private com.sun.star.sheet.XCellSeries getCellSeries( + com.sun.star.sheet.XSpreadsheet xSheet, String aRange ) + { + return UnoRuntime.queryInterface( + com.sun.star.sheet.XCellSeries.class, xSheet.getCellRangeByName( aRange ) ); + } + + + + /** All samples regarding cell range collections. */ + private void doCellRangesSamples() throws RuntimeException, Exception + { + System.out.println( "\n*** Samples for cell range collections ***\n" ); + + // Create a new cell range container + com.sun.star.lang.XMultiServiceFactory xDocFactory = + UnoRuntime.queryInterface( + com.sun.star.lang.XMultiServiceFactory.class, getDocument() ); + com.sun.star.sheet.XSheetCellRangeContainer xRangeCont = + UnoRuntime.queryInterface( + com.sun.star.sheet.XSheetCellRangeContainer.class, + xDocFactory.createInstance( "com.sun.star.sheet.SheetCellRanges" ) ); + + + // --- Insert ranges --- + insertRange( xRangeCont, 0, 0, 0, 0, 0, false ); // A1:A1 + insertRange( xRangeCont, 0, 0, 1, 0, 2, true ); // A2:A3 + insertRange( xRangeCont, 0, 1, 0, 1, 2, false ); // B1:B3 + + + // --- Query the list of filled cells --- + System.out.print( "All filled cells: " ); + com.sun.star.container.XEnumerationAccess xCellsEA = xRangeCont.getCells(); + com.sun.star.container.XEnumeration xEnum = xCellsEA.createEnumeration(); + while( xEnum.hasMoreElements() ) + { + Object aCellObj = xEnum.nextElement(); + com.sun.star.sheet.XCellAddressable xAddr = UnoRuntime.queryInterface( com.sun.star.sheet.XCellAddressable.class, aCellObj ); + com.sun.star.table.CellAddress aAddr = xAddr.getCellAddress(); + System.out.print( getCellAddressString( aAddr.Column, aAddr.Row ) + " " ); + } + System.out.println(); + } + + /** Inserts a cell range address into a cell range container and prints + a message. + @param xContainer The com.sun.star.sheet.XSheetCellRangeContainer interface of the container. + @param nSheet Index of sheet of the range. + @param nStartCol Index of first column of the range. + @param nStartRow Index of first row of the range. + @param nEndCol Index of last column of the range. + @param nEndRow Index of last row of the range. + @param bMerge Determines whether the new range should be merged with the existing ranges. */ + private void insertRange( + com.sun.star.sheet.XSheetCellRangeContainer xContainer, + int nSheet, int nStartCol, int nStartRow, int nEndCol, int nEndRow, + boolean bMerge ) throws RuntimeException, Exception + { + com.sun.star.table.CellRangeAddress aAddress = new com.sun.star.table.CellRangeAddress(); + aAddress.Sheet = (short)nSheet; + aAddress.StartColumn = nStartCol; + aAddress.StartRow = nStartRow; + aAddress.EndColumn = nEndCol; + aAddress.EndRow = nEndRow; + xContainer.addRangeAddress( aAddress, bMerge ); + System.out.println( + "Inserting " + getCellRangeAddressString( aAddress ) + + " " + (bMerge ? " with" : "without") + " merge," + + " resulting list: " + xContainer.getRangeAddressesAsString() ); + } + + + + /** All samples regarding cell cursors. */ + private void doCellCursorSamples() throws RuntimeException, Exception + { + System.out.println( "\n*** Samples for cell cursor ***\n" ); + com.sun.star.sheet.XSpreadsheet xSheet = getSpreadsheet( 0 ); + + + // --- Find the array formula using a cell cursor --- + com.sun.star.table.XCellRange xRange = xSheet.getCellRangeByName( "F22" ); + com.sun.star.sheet.XSheetCellRange xCellRange = UnoRuntime.queryInterface( com.sun.star.sheet.XSheetCellRange.class, xRange ); + com.sun.star.sheet.XSheetCellCursor xCursor = xSheet.createCursorByRange( xCellRange ); + + xCursor.collapseToCurrentArray(); + com.sun.star.sheet.XArrayFormulaRange xArray = UnoRuntime.queryInterface( com.sun.star.sheet.XArrayFormulaRange.class, xCursor ); + System.out.println( + "Array formula in " + getCellRangeAddressString( xCursor, false ) + + " contains formula " + xArray.getArrayFormula() ); + + + // --- Find the used area --- + com.sun.star.sheet.XUsedAreaCursor xUsedCursor = UnoRuntime.queryInterface( com.sun.star.sheet.XUsedAreaCursor.class, xCursor ); + xUsedCursor.gotoStartOfUsedArea( false ); + xUsedCursor.gotoEndOfUsedArea( true ); + // xUsedCursor and xCursor are interfaces of the same object - + // so modifying xUsedCursor takes effect on xCursor: + System.out.println( "The used area is: " + getCellRangeAddressString( xCursor, true ) ); + } + + + + /** All samples regarding the formatting of cells and ranges. */ + private void doFormattingSamples() throws RuntimeException, Exception + { + System.out.println( "\n*** Formatting samples ***\n" ); + com.sun.star.sheet.XSpreadsheet xSheet = getSpreadsheet( 1 ); + com.sun.star.table.XCellRange xCellRange; + com.sun.star.beans.XPropertySet xPropSet = null; + com.sun.star.container.XIndexAccess xRangeIA = null; + com.sun.star.lang.XMultiServiceFactory xDocServiceManager; + + + // --- Cell styles --- + // get the cell style container + com.sun.star.style.XStyleFamiliesSupplier xFamiliesSupplier = UnoRuntime.queryInterface( com.sun.star.style.XStyleFamiliesSupplier.class, getDocument() ); + com.sun.star.container.XNameAccess xFamiliesNA = xFamiliesSupplier.getStyleFamilies(); + Object aCellStylesObj = xFamiliesNA.getByName( "CellStyles" ); + com.sun.star.container.XNameContainer xCellStylesNA = UnoRuntime.queryInterface( com.sun.star.container.XNameContainer.class, aCellStylesObj ); + + // create a new cell style + xDocServiceManager = UnoRuntime.queryInterface( com.sun.star.lang.XMultiServiceFactory.class, getDocument() ); + Object aCellStyle = xDocServiceManager.createInstance( "com.sun.star.style.CellStyle" ); + String aStyleName = "MyNewCellStyle"; + xCellStylesNA.insertByName( aStyleName, aCellStyle ); + + // modify properties of the new style + xPropSet = UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, aCellStyle ); + xPropSet.setPropertyValue( "CellBackColor", Integer.valueOf( 0x888888 ) ); + xPropSet.setPropertyValue( "IsCellBackgroundTransparent", Boolean.FALSE ); + + + + // --- Query equal-formatted cell ranges --- + // prepare example, use the new cell style + xCellRange = xSheet.getCellRangeByName( "D2:F2" ); + xPropSet = UnoRuntime.queryInterface( + com.sun.star.beans.XPropertySet.class, xCellRange ); + xPropSet.setPropertyValue( "CellStyle", aStyleName ); + + xCellRange = xSheet.getCellRangeByName( "A3:G3" ); + xPropSet = UnoRuntime.queryInterface( + com.sun.star.beans.XPropertySet.class, xCellRange ); + xPropSet.setPropertyValue( "CellStyle", aStyleName ); + + // All ranges in one container + xCellRange = xSheet.getCellRangeByName( "A1:G3" ); + System.out.println( "Service CellFormatRanges:" ); + com.sun.star.sheet.XCellFormatRangesSupplier xFormatSupp = + UnoRuntime.queryInterface( + com.sun.star.sheet.XCellFormatRangesSupplier.class, xCellRange ); + xRangeIA = xFormatSupp.getCellFormatRanges(); + System.out.println( getCellRangeListString( xRangeIA ) ); + + // Ranges sorted in SheetCellRanges containers + System.out.println( "\nService UniqueCellFormatRanges:" ); + com.sun.star.sheet.XUniqueCellFormatRangesSupplier xUniqueFormatSupp = + UnoRuntime.queryInterface( + com.sun.star.sheet.XUniqueCellFormatRangesSupplier.class, xCellRange ); + com.sun.star.container.XIndexAccess xRangesIA = xUniqueFormatSupp.getUniqueCellFormatRanges(); + int nCount = xRangesIA.getCount(); + for (int nIndex = 0; nIndex < nCount; ++nIndex) + { + Object aRangesObj = xRangesIA.getByIndex( nIndex ); + xRangeIA = UnoRuntime.queryInterface( + com.sun.star.container.XIndexAccess.class, aRangesObj ); + System.out.println( + "Container " + (nIndex + 1) + ": " + getCellRangeListString( xRangeIA ) ); + } + + + // --- Table auto formats --- + // get the global collection of table auto formats, use global service + // manager + com.sun.star.lang.XMultiComponentFactory xServiceManager = getServiceManager(); + + Object aAutoFormatsObj = xServiceManager.createInstanceWithContext( + "com.sun.star.sheet.TableAutoFormats", getContext()); + com.sun.star.container.XNameContainer xAutoFormatsNA = + UnoRuntime.queryInterface( + com.sun.star.container.XNameContainer.class, aAutoFormatsObj ); + + // create a new table auto format and insert into the container + String aAutoFormatName = "Temp_Example"; + boolean bExistsAlready = xAutoFormatsNA.hasByName( aAutoFormatName ); + Object aAutoFormatObj = null; + if (bExistsAlready) + // auto format already exists -> use it + aAutoFormatObj = xAutoFormatsNA.getByName( aAutoFormatName ); + else + { + aAutoFormatObj = xDocServiceManager.createInstance( + "com.sun.star.sheet.TableAutoFormat" ); + xAutoFormatsNA.insertByName( aAutoFormatName, aAutoFormatObj ); + } + // index access to the auto format fields + com.sun.star.container.XIndexAccess xAutoFormatIA = + UnoRuntime.queryInterface( + com.sun.star.container.XIndexAccess.class, aAutoFormatObj ); + + // set properties of all auto format fields + for (int nRow = 0; nRow < 4; ++nRow) + { + int nRowColor = 0; + switch (nRow) + { + case 0: nRowColor = 0x999999; break; + case 1: nRowColor = 0xFFFFCC; break; + case 2: nRowColor = 0xEEEEEE; break; + case 3: nRowColor = 0x999999; break; + } + + for (int nColumn = 0; nColumn < 4; ++nColumn) + { + int nColor = nRowColor; + if ((nColumn == 0) || (nColumn == 3)) + nColor -= 0x333300; + + // get the auto format field and apply properties + Object aFieldObj = xAutoFormatIA.getByIndex( 4 * nRow + nColumn ); + xPropSet = UnoRuntime.queryInterface( + com.sun.star.beans.XPropertySet.class, aFieldObj ); + xPropSet.setPropertyValue( "CellBackColor", Integer.valueOf( nColor ) ); + } + } + + // set the auto format to the spreadsheet + xCellRange = xSheet.getCellRangeByName( "A5:H25" ); + com.sun.star.table.XAutoFormattable xAutoForm = UnoRuntime.queryInterface( com.sun.star.table.XAutoFormattable.class, xCellRange ); + xAutoForm.autoFormat( aAutoFormatName ); + + // remove the auto format + if (!bExistsAlready) + xAutoFormatsNA.removeByName( aAutoFormatName ); + + + // --- Conditional formats --- + xSheet = getSpreadsheet( 0 ); + prepareRange( xSheet, "K20:K23", "Cond. Format" ); + setValue( xSheet, "K21", 1 ); + setValue( xSheet, "K22", 2 ); + setValue( xSheet, "K23", 3 ); + + // get the conditional format object of the cell range + xCellRange = xSheet.getCellRangeByName( "K21:K23" ); + xPropSet = UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xCellRange ); + com.sun.star.sheet.XSheetConditionalEntries xEntries = + UnoRuntime.queryInterface( + com.sun.star.sheet.XSheetConditionalEntries.class, + xPropSet.getPropertyValue( "ConditionalFormat" )); + + // create a condition and apply it to the range + com.sun.star.beans.PropertyValue[] aCondition = new com.sun.star.beans.PropertyValue[3]; + aCondition[0] = new com.sun.star.beans.PropertyValue(); + aCondition[0].Name = "Operator"; + aCondition[0].Value = com.sun.star.sheet.ConditionOperator.GREATER; + aCondition[1] = new com.sun.star.beans.PropertyValue(); + aCondition[1].Name = "Formula1"; + aCondition[1].Value = "1"; + aCondition[2] = new com.sun.star.beans.PropertyValue(); + aCondition[2].Name = "StyleName"; + aCondition[2].Value = aStyleName; + xEntries.addNew( aCondition ); + xPropSet.setPropertyValue( "ConditionalFormat", xEntries ); + } + + + + /** All samples regarding the spreadsheet document. */ + private void doDocumentSamples() throws RuntimeException, Exception + { + System.out.println( "\n*** Samples for spreadsheet document ***\n" ); + + + // --- Insert a new spreadsheet --- + com.sun.star.sheet.XSpreadsheet xSheet = insertSpreadsheet( "A new sheet", (short)0x7FFF ); + + + // --- Copy a cell range --- + prepareRange( xSheet, "A1:B3", "Copy from" ); + prepareRange( xSheet, "D1:E3", "To" ); + setValue( xSheet, "A2", 123 ); + setValue( xSheet, "B2", 345 ); + setFormula( xSheet, "A3", "=SUM(A2:B2)" ); + setFormula( xSheet, "B3", "=FORMULA(A3)" ); + + com.sun.star.sheet.XCellRangeMovement xMovement = UnoRuntime.queryInterface( com.sun.star.sheet.XCellRangeMovement.class, xSheet ); + com.sun.star.table.CellRangeAddress aSourceRange = createCellRangeAddress( xSheet, "A2:B3" ); + com.sun.star.table.CellAddress aDestCell = createCellAddress( xSheet, "D2" ); + xMovement.copyRange( aDestCell, aSourceRange ); + + + // --- Print automatic column page breaks --- + com.sun.star.sheet.XSheetPageBreak xPageBreak = UnoRuntime.queryInterface( com.sun.star.sheet.XSheetPageBreak.class, xSheet ); + com.sun.star.sheet.TablePageBreakData[] aPageBreakArray = xPageBreak.getColumnPageBreaks(); + + System.out.print( "Automatic column page breaks:" ); + for (int nIndex = 0; nIndex < aPageBreakArray.length; ++nIndex) + if (!aPageBreakArray[nIndex].ManualBreak) + System.out.print( " " + aPageBreakArray[nIndex].Position ); + System.out.println(); + + + // --- Document properties --- + com.sun.star.beans.XPropertySet xPropSet = UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, getDocument() ); + + String aText = "Value of property IsIterationEnabled: "; + aText += AnyConverter.toBoolean(xPropSet.getPropertyValue( "IsIterationEnabled" )); + System.out.println( aText ); + aText = "Value of property IterationCount: "; + aText += AnyConverter.toInt(xPropSet.getPropertyValue( "IterationCount" )); + System.out.println( aText ); + aText = "Value of property NullDate: "; + com.sun.star.util.Date aDate = (com.sun.star.util.Date) + AnyConverter.toObject(com.sun.star.util.Date.class, xPropSet.getPropertyValue( "NullDate" )); + aText += aDate.Year + "-" + aDate.Month + "-" + aDate.Day; + System.out.println( aText ); + + + // --- Data validation --- + prepareRange( xSheet, "A5:C7", "Validation" ); + setFormula( xSheet, "A6", "Insert values between 0.0 and 5.0 below:" ); + + com.sun.star.table.XCellRange xCellRange = xSheet.getCellRangeByName( "A7:C7" ); + com.sun.star.beans.XPropertySet xCellPropSet = UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xCellRange ); + // validation properties + com.sun.star.beans.XPropertySet xValidPropSet = UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, + xCellPropSet.getPropertyValue( "Validation" )); + xValidPropSet.setPropertyValue( "Type", com.sun.star.sheet.ValidationType.DECIMAL ); + xValidPropSet.setPropertyValue( "ShowErrorMessage", Boolean.TRUE ); + xValidPropSet.setPropertyValue( "ErrorMessage", "This is an invalid value!" ); + xValidPropSet.setPropertyValue( "ErrorAlertStyle", com.sun.star.sheet.ValidationAlertStyle.STOP ); + // condition + com.sun.star.sheet.XSheetCondition xCondition = UnoRuntime.queryInterface( com.sun.star.sheet.XSheetCondition.class, xValidPropSet ); + xCondition.setOperator( com.sun.star.sheet.ConditionOperator.BETWEEN ); + xCondition.setFormula1( "0.0" ); + xCondition.setFormula2( "5.0" ); + // apply on cell range + xCellPropSet.setPropertyValue( "Validation", xValidPropSet ); + + // --- Scenarios --- + Object[][] aValues = new Object[2][2]; + + aValues[0][0] = new Double( 11 ); + aValues[0][1] = new Double( 12 ); + aValues[1][0] = "Test13"; + aValues[1][1] = "Test14"; + insertScenario( xSheet, "B10:C11", aValues, "First Scenario", "The first scenario." ); + + aValues[0][0] = "Test21"; + aValues[0][1] = "Test22"; + aValues[1][0] = new Double( 23 ); + aValues[1][1] = new Double( 24 ); + insertScenario( xSheet, "B10:C11", aValues, "Second Scenario", "The visible scenario." ); + + aValues[0][0] = new Double( 31 ); + aValues[0][1] = new Double( 32 ); + aValues[1][0] = "Test33"; + aValues[1][1] = "Test34"; + insertScenario( xSheet, "B10:C11", aValues, "Third Scenario", "The last scenario." ); + + // show second scenario + showScenario( xSheet, "Second Scenario" ); + } + + /** Inserts a scenario containing one cell range into a sheet and + applies the value array. + @param xSheet The XSpreadsheet interface of the spreadsheet. + @param aRange The range address for the scenario. + @param aValueArray The array of cell contents. + @param aScenarioName The name of the new scenario. + @param aScenarioComment The user comment for the scenario. */ + private void insertScenario( + com.sun.star.sheet.XSpreadsheet xSheet, + String aRange, + Object[][] aValueArray, + String aScenarioName, + String aScenarioComment ) throws RuntimeException, Exception + { + // get the cell range with the given address + com.sun.star.table.XCellRange xCellRange = xSheet.getCellRangeByName( aRange ); + + // create the range address sequence + com.sun.star.sheet.XCellRangeAddressable xAddr = UnoRuntime.queryInterface( com.sun.star.sheet.XCellRangeAddressable.class, xCellRange ); + com.sun.star.table.CellRangeAddress[] aRangesSeq = new com.sun.star.table.CellRangeAddress[1]; + aRangesSeq[0] = xAddr.getRangeAddress(); + + // create the scenario + com.sun.star.sheet.XScenariosSupplier xScenSupp = UnoRuntime.queryInterface( com.sun.star.sheet.XScenariosSupplier.class, xSheet ); + com.sun.star.sheet.XScenarios xScenarios = xScenSupp.getScenarios(); + xScenarios.addNewByName( aScenarioName, aRangesSeq, aScenarioComment ); + + // insert the values into the range + com.sun.star.sheet.XCellRangeData xData = UnoRuntime.queryInterface( com.sun.star.sheet.XCellRangeData.class, xCellRange ); + xData.setDataArray( aValueArray ); + } + + /** Activates a scenario. + @param xSheet The XSpreadsheet interface of the spreadsheet. + @param aScenarioName The name of the scenario. */ + private void showScenario( + com.sun.star.sheet.XSpreadsheet xSheet, + String aScenarioName ) throws RuntimeException, Exception + { + // get the scenario set + com.sun.star.sheet.XScenariosSupplier xScenSupp = UnoRuntime.queryInterface( com.sun.star.sheet.XScenariosSupplier.class, xSheet ); + com.sun.star.sheet.XScenarios xScenarios = xScenSupp.getScenarios(); + + // get the scenario and activate it + Object aScenarioObj = xScenarios.getByName( aScenarioName ); + com.sun.star.sheet.XScenario xScenario = UnoRuntime.queryInterface( com.sun.star.sheet.XScenario.class, aScenarioObj ); + xScenario.apply(); + } + + + + private void doNamedRangesSamples() throws RuntimeException, Exception + { + System.out.println( "\n*** Samples for named ranges ***\n" ); + com.sun.star.sheet.XSpreadsheetDocument xDocument = getDocument(); + com.sun.star.sheet.XSpreadsheet xSheet = getSpreadsheet( 0 ); + + + // --- Named ranges --- + prepareRange( xSheet, "G42:H45", "Named ranges" ); + xSheet.getCellByPosition( 6, 42 ).setValue( 1 ); + xSheet.getCellByPosition( 6, 43 ).setValue( 2 ); + xSheet.getCellByPosition( 7, 42 ).setValue( 3 ); + xSheet.getCellByPosition( 7, 43 ).setValue( 4 ); + + // insert a named range + com.sun.star.beans.XPropertySet xDocProp = UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xDocument ); + Object aRangesObj = xDocProp.getPropertyValue( "NamedRanges" ); + com.sun.star.sheet.XNamedRanges xNamedRanges = UnoRuntime.queryInterface( com.sun.star.sheet.XNamedRanges.class, aRangesObj ); + com.sun.star.table.CellAddress aRefPos = new com.sun.star.table.CellAddress(); + aRefPos.Sheet = 0; + aRefPos.Column = 6; + aRefPos.Row = 44; + xNamedRanges.addNewByName( "ExampleName", "SUM(G43:G44)", aRefPos, 0 ); + + // use the named range in formulas + xSheet.getCellByPosition( 6, 44 ).setFormula( "=ExampleName" ); + xSheet.getCellByPosition( 7, 44 ).setFormula( "=ExampleName" ); + + + // --- Label ranges --- + prepareRange( xSheet, "G47:I50", "Label ranges" ); + com.sun.star.table.XCellRange xRange = xSheet.getCellRangeByPosition( 6, 47, 7, 49 ); + com.sun.star.sheet.XCellRangeData xData = UnoRuntime.queryInterface( com.sun.star.sheet.XCellRangeData.class, xRange ); + Object[][] aValues = + { + { "Apples", "Oranges" }, + { new Double( 5 ), new Double( 7 ) }, + { new Double( 6 ), new Double( 8 ) } + }; + xData.setDataArray( aValues ); + + // insert a column label range + Object aLabelsObj = xDocProp.getPropertyValue( "ColumnLabelRanges" ); + com.sun.star.sheet.XLabelRanges xLabelRanges = UnoRuntime.queryInterface( com.sun.star.sheet.XLabelRanges.class, aLabelsObj ); + com.sun.star.table.CellRangeAddress aLabelArea = new com.sun.star.table.CellRangeAddress(); + aLabelArea.Sheet = 0; + aLabelArea.StartColumn = 6; + aLabelArea.StartRow = 47; + aLabelArea.EndColumn = 7; + aLabelArea.EndRow = 47; + com.sun.star.table.CellRangeAddress aDataArea = new com.sun.star.table.CellRangeAddress(); + aDataArea.Sheet = 0; + aDataArea.StartColumn = 6; + aDataArea.StartRow = 48; + aDataArea.EndColumn = 7; + aDataArea.EndRow = 49; + xLabelRanges.addNew( aLabelArea, aDataArea ); + + // use the label range in formulas + xSheet.getCellByPosition( 8, 48 ).setFormula( "=Apples+Oranges" ); + xSheet.getCellByPosition( 8, 49 ).setFormula( "=Apples+Oranges" ); + } + + + + /** Helper for doDatabaseSamples: get name of first database. */ + private String getFirstDatabaseName() + { + String aDatabase = null; + try + { + com.sun.star.lang.XMultiComponentFactory xServiceManager = getServiceManager(); + com.sun.star.container.XNameAccess xContext = + UnoRuntime.queryInterface( + com.sun.star.container.XNameAccess.class, + xServiceManager.createInstanceWithContext( + "com.sun.star.sdb.DatabaseContext", getContext()) ); + String[] aNames = xContext.getElementNames(); + if ( aNames.length > 0 ) + aDatabase = aNames[0]; + } + catch ( Exception e ) + { + System.out.println( "\nError: caught exception in getFirstDatabaseName()!\n" + + "Exception Message = " + + e.getMessage()); + e.printStackTrace(); + } + return aDatabase; + } + + /** Helper for doDatabaseSamples: get name of first table in a database. */ + private String getFirstTableName( String aDatabase ) + { + if ( aDatabase == null ) + return null; + + String aTable = null; + try + { + com.sun.star.lang.XMultiComponentFactory xServiceManager = getServiceManager(); + com.sun.star.container.XNameAccess xContext = UnoRuntime.queryInterface( com.sun.star.container.XNameAccess.class, + xServiceManager.createInstanceWithContext( + "com.sun.star.sdb.DatabaseContext", getContext()) ); + com.sun.star.sdb.XCompletedConnection xSource = + UnoRuntime.queryInterface( + com.sun.star.sdb.XCompletedConnection.class, + xContext.getByName( aDatabase ) ); + com.sun.star.task.XInteractionHandler xHandler = + UnoRuntime.queryInterface( + com.sun.star.task.XInteractionHandler.class, + xServiceManager.createInstanceWithContext( + "com.sun.star.task.InteractionHandler", getContext()) ); + com.sun.star.sdbcx.XTablesSupplier xSupplier = + UnoRuntime.queryInterface( + com.sun.star.sdbcx.XTablesSupplier.class, + xSource.connectWithCompletion( xHandler ) ); + com.sun.star.container.XNameAccess xTables = xSupplier.getTables(); + String[] aNames = xTables.getElementNames(); + if ( aNames.length > 0 ) + aTable = aNames[0]; + } + catch ( Exception e ) + { + System.out.println( "\nError: caught exception in getFirstTableName()!\n" + + "Exception Message = " + + e.getMessage()); + e.printStackTrace(); + } + return aTable; + } + + private void doDatabaseSamples() throws Exception + { + System.out.println( "\n*** Samples for database operations ***\n" ); + com.sun.star.sheet.XSpreadsheet xSheet = getSpreadsheet( 2 ); + + + // --- put some example data into the sheet --- + com.sun.star.table.XCellRange xRange = xSheet.getCellRangeByName( "B3:D24" ); + com.sun.star.sheet.XCellRangeData xData = UnoRuntime.queryInterface( com.sun.star.sheet.XCellRangeData.class, xRange ); + Object[][] aValues = + { + { "Name", "Year", "Sales" }, + { "Alice", new Double( 2001 ), new Double( 4.0 ) }, + { "Carol", new Double( 1997 ), new Double( 3.0 ) }, + { "Carol", new Double( 1998 ), new Double( 8.0 ) }, + { "Bob", new Double( 1997 ), new Double( 8.0 ) }, + { "Alice", new Double( 2002 ), new Double( 9.0 ) }, + { "Alice", new Double( 1999 ), new Double( 7.0 ) }, + { "Alice", new Double( 1996 ), new Double( 3.0 ) }, + { "Bob", new Double( 2000 ), new Double( 1.0 ) }, + { "Carol", new Double( 1999 ), new Double( 5.0 ) }, + { "Bob", new Double( 2002 ), new Double( 1.0 ) }, + { "Carol", new Double( 2001 ), new Double( 5.0 ) }, + { "Carol", new Double( 2000 ), new Double( 1.0 ) }, + { "Carol", new Double( 1996 ), new Double( 8.0 ) }, + { "Bob", new Double( 1996 ), new Double( 7.0 ) }, + { "Alice", new Double( 1997 ), new Double( 3.0 ) }, + { "Alice", new Double( 2000 ), new Double( 9.0 ) }, + { "Bob", new Double( 1998 ), new Double( 1.0 ) }, + { "Bob", new Double( 1999 ), new Double( 6.0 ) }, + { "Carol", new Double( 2002 ), new Double( 8.0 ) }, + { "Alice", new Double( 1998 ), new Double( 5.0 ) }, + { "Bob", new Double( 2001 ), new Double( 6.0 ) } + }; + xData.setDataArray( aValues ); + + + // --- filter for second column >= 1998 --- + com.sun.star.sheet.XSheetFilterable xFilter = UnoRuntime.queryInterface( com.sun.star.sheet.XSheetFilterable.class, xRange ); + com.sun.star.sheet.XSheetFilterDescriptor xFilterDesc = + xFilter.createFilterDescriptor( true ); + com.sun.star.sheet.TableFilterField[] aFilterFields = + new com.sun.star.sheet.TableFilterField[1]; + aFilterFields[0] = new com.sun.star.sheet.TableFilterField(); + aFilterFields[0].Field = 1; + aFilterFields[0].IsNumeric = true; + aFilterFields[0].Operator = com.sun.star.sheet.FilterOperator.GREATER_EQUAL; + aFilterFields[0].NumericValue = 1998; + xFilterDesc.setFilterFields( aFilterFields ); + com.sun.star.beans.XPropertySet xFilterProp = UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xFilterDesc ); + xFilterProp.setPropertyValue( "ContainsHeader", Boolean.TRUE ); + xFilter.filter( xFilterDesc ); + + + // --- do the same filter as above, using criteria from a cell range --- + com.sun.star.table.XCellRange xCritRange = xSheet.getCellRangeByName( "B27:B28" ); + com.sun.star.sheet.XCellRangeData xCritData = UnoRuntime.queryInterface( com.sun.star.sheet.XCellRangeData.class, xCritRange ); + Object[][] aCritValues = + { + { "Year" }, + { ">= 1998" } + }; + xCritData.setDataArray( aCritValues ); + com.sun.star.sheet.XSheetFilterableEx xCriteria = UnoRuntime.queryInterface( com.sun.star.sheet.XSheetFilterableEx.class, xCritRange ); + xFilterDesc = xCriteria.createFilterDescriptorByObject( xFilter ); + if ( xFilterDesc != null ) + xFilter.filter( xFilterDesc ); + + + // --- sort by second column, ascending --- + com.sun.star.table.TableSortField[] aSortFields = new com.sun.star.table.TableSortField[1]; + aSortFields[0] = new com.sun.star.table.TableSortField(); + aSortFields[0].Field = 1; + aSortFields[0].IsAscending = false; + aSortFields[0].IsCaseSensitive = false; + + + com.sun.star.beans.PropertyValue[] aSortDesc = new com.sun.star.beans.PropertyValue[2]; + aSortDesc[0] = new com.sun.star.beans.PropertyValue(); + aSortDesc[0].Name = "SortFields"; + aSortDesc[0].Value = aSortFields; + aSortDesc[1] = new com.sun.star.beans.PropertyValue(); + aSortDesc[1].Name = "ContainsHeader"; + aSortDesc[1].Value = Boolean.TRUE; + + com.sun.star.util.XSortable xSort = UnoRuntime.queryInterface( com.sun.star.util.XSortable.class, xRange ); + xSort.sort( aSortDesc ); + + + // --- insert subtotals --- + com.sun.star.sheet.XSubTotalCalculatable xSub = UnoRuntime.queryInterface( com.sun.star.sheet.XSubTotalCalculatable.class, xRange ); + com.sun.star.sheet.XSubTotalDescriptor xSubDesc = xSub.createSubTotalDescriptor( true ); + com.sun.star.sheet.SubTotalColumn[] aColumns = new com.sun.star.sheet.SubTotalColumn[1]; + // calculate sum of third column + aColumns[0] = new com.sun.star.sheet.SubTotalColumn(); + aColumns[0].Column = 2; + aColumns[0].Function = com.sun.star.sheet.GeneralFunction.SUM; + // group by first column + xSubDesc.addNew( aColumns, 0 ); + xSub.applySubTotals( xSubDesc, true ); + + String aDatabase = getFirstDatabaseName(); + String aTableName = getFirstTableName( aDatabase ); + if ( aDatabase != null && aTableName != null ) + { + // --- import from database --- + com.sun.star.beans.PropertyValue[] aImportDesc = new com.sun.star.beans.PropertyValue[3]; + aImportDesc[0] = new com.sun.star.beans.PropertyValue(); + aImportDesc[0].Name = "DatabaseName"; + aImportDesc[0].Value = aDatabase; + aImportDesc[1] = new com.sun.star.beans.PropertyValue(); + aImportDesc[1].Name = "SourceType"; + aImportDesc[1].Value = com.sun.star.sheet.DataImportMode.TABLE; + aImportDesc[2] = new com.sun.star.beans.PropertyValue(); + aImportDesc[2].Name = "SourceObject"; + aImportDesc[2].Value = aTableName; + + com.sun.star.table.XCellRange xImportRange = xSheet.getCellRangeByName( "B35:B35" ); + com.sun.star.util.XImportable xImport = UnoRuntime.queryInterface( com.sun.star.util.XImportable.class, xImportRange ); + xImport.doImport( aImportDesc ); + + + // --- use the temporary database range to find the imported data's size --- + com.sun.star.beans.XPropertySet xDocProp = UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, getDocument() ); + Object aRangesObj = xDocProp.getPropertyValue( "DatabaseRanges" ); + com.sun.star.container.XNameAccess xRanges = + UnoRuntime.queryInterface( + com.sun.star.container.XNameAccess.class, aRangesObj ); + String[] aNames = xRanges.getElementNames(); + for ( int i=0; i<aNames.length; i++ ) + { + Object aRangeObj = xRanges.getByName( aNames[i] ); + com.sun.star.beans.XPropertySet xRangeProp = UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, aRangeObj ); + boolean bUser = AnyConverter.toBoolean(xRangeProp.getPropertyValue( "IsUserDefined" )); + if ( !bUser ) + { + // this is the temporary database range - get the cell range and format it + com.sun.star.sheet.XCellRangeReferrer xRef = UnoRuntime.queryInterface( com.sun.star.sheet.XCellRangeReferrer.class, aRangeObj ); + com.sun.star.table.XCellRange xResultRange = xRef.getReferredCells(); + com.sun.star.beans.XPropertySet xResultProp = UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xResultRange ); + xResultProp.setPropertyValue( "IsCellBackgroundTransparent", Boolean.FALSE ); + xResultProp.setPropertyValue( "CellBackColor", Integer.valueOf( 0xFFFFCC ) ); + } + } + } + else + System.out.println("can't get database"); + } + + + + private void doDataPilotSamples() throws Exception + { + System.out.println( "\n*** Samples for Data Pilot ***\n" ); + com.sun.star.sheet.XSpreadsheet xSheet = getSpreadsheet( 0 ); + + + // --- Create a new DataPilot table --- + prepareRange( xSheet, "A38:C38", "Data Pilot" ); + com.sun.star.sheet.XDataPilotTablesSupplier xDPSupp = UnoRuntime.queryInterface( com.sun.star.sheet.XDataPilotTablesSupplier.class, xSheet ); + com.sun.star.sheet.XDataPilotTables xDPTables = xDPSupp.getDataPilotTables(); + com.sun.star.sheet.XDataPilotDescriptor xDPDesc = xDPTables.createDataPilotDescriptor(); + // set source range (use data range from CellRange test) + com.sun.star.table.CellRangeAddress aSourceAddress = createCellRangeAddress( xSheet, "A10:C30" ); + xDPDesc.setSourceRange( aSourceAddress ); + // settings for fields + com.sun.star.container.XIndexAccess xFields = xDPDesc.getDataPilotFields(); + Object aFieldObj; + com.sun.star.beans.XPropertySet xFieldProp; + // use first column as column field + aFieldObj = xFields.getByIndex(0); + xFieldProp = UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, aFieldObj ); + xFieldProp.setPropertyValue( "Orientation", com.sun.star.sheet.DataPilotFieldOrientation.COLUMN ); + // use second column as row field + aFieldObj = xFields.getByIndex(1); + xFieldProp = UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, aFieldObj ); + xFieldProp.setPropertyValue( "Orientation", com.sun.star.sheet.DataPilotFieldOrientation.ROW ); + // use third column as data field, calculating the sum + aFieldObj = xFields.getByIndex(2); + xFieldProp = UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, aFieldObj ); + xFieldProp.setPropertyValue( "Orientation", com.sun.star.sheet.DataPilotFieldOrientation.DATA ); + xFieldProp.setPropertyValue( "Function", com.sun.star.sheet.GeneralFunction.SUM ); + // select output position + com.sun.star.table.CellAddress aDestAddress = createCellAddress( xSheet, "A40" ); + xDPTables.insertNewByName( "DataPilotExample", aDestAddress, xDPDesc ); + + + // --- Modify the DataPilot table --- + Object aDPTableObj = xDPTables.getByName( "DataPilotExample" ); + xDPDesc = UnoRuntime.queryInterface( com.sun.star.sheet.XDataPilotDescriptor.class, aDPTableObj ); + xFields = xDPDesc.getDataPilotFields(); + // add a second data field from the third column, calculating the average + aFieldObj = xFields.getByIndex(2); + xFieldProp = UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, aFieldObj ); + xFieldProp.setPropertyValue( "Orientation", com.sun.star.sheet.DataPilotFieldOrientation.DATA ); + xFieldProp.setPropertyValue( "Function", com.sun.star.sheet.GeneralFunction.AVERAGE ); + } + + + + private void doFunctionAccessSamples() throws RuntimeException, Exception + { + System.out.println( "\n*** Samples for function handling ***\n" ); + com.sun.star.lang.XMultiComponentFactory xServiceManager = getServiceManager(); + + + // --- Calculate a function --- + Object aFuncInst = xServiceManager.createInstanceWithContext( + "com.sun.star.sheet.FunctionAccess", getContext()); + com.sun.star.sheet.XFunctionAccess xFuncAcc = + UnoRuntime.queryInterface( + com.sun.star.sheet.XFunctionAccess.class, aFuncInst ); + // put the data in a two-dimensional array + double[][] aData = { { 1.0, 2.0, 3.0 } }; + // construct the array of function arguments + Object[] aArgs = new Object[2]; + aArgs[0] = aData; + aArgs[1] = new Double( 2.0 ); + Object aResult = xFuncAcc.callFunction( "ZTEST", aArgs ); + System.out.println("ZTEST result for data {1,2,3} and value 2 is " + + ((Double)aResult).doubleValue() ); + + + // --- Get the list of recently used functions --- + Object aRecInst = xServiceManager.createInstanceWithContext( + "com.sun.star.sheet.RecentFunctions", getContext()); + com.sun.star.sheet.XRecentFunctions xRecFunc = + UnoRuntime.queryInterface( + com.sun.star.sheet.XRecentFunctions.class, aRecInst ); + int[] nRecentIds = xRecFunc.getRecentFunctionIds(); + + + // --- Get the names for these functions --- + Object aDescInst = xServiceManager.createInstanceWithContext( + "com.sun.star.sheet.FunctionDescriptions", getContext()); + com.sun.star.sheet.XFunctionDescriptions xFuncDesc = + UnoRuntime.queryInterface( + com.sun.star.sheet.XFunctionDescriptions.class, aDescInst ); + System.out.print("Recently used functions: "); + for (int nFunction=0; nFunction<nRecentIds.length; nFunction++) + { + com.sun.star.beans.PropertyValue[] aProperties = + xFuncDesc.getById( nRecentIds[nFunction] ); + for (int nProp=0; nProp<aProperties.length; nProp++) + if ( aProperties[nProp].Name.equals( "Name" ) ) + System.out.print( aProperties[nProp].Value + " " ); + } + System.out.println(); + } + + + + private void doApplicationSettingsSamples() throws RuntimeException, Exception + { + System.out.println( "\n*** Samples for application settings ***\n" ); + com.sun.star.lang.XMultiComponentFactory xServiceManager = getServiceManager(); + + + // --- Get the user defined sort lists --- + Object aSettings = xServiceManager.createInstanceWithContext( + "com.sun.star.sheet.GlobalSheetSettings", getContext()); + com.sun.star.beans.XPropertySet xPropSet = + UnoRuntime.queryInterface( + com.sun.star.beans.XPropertySet.class, aSettings ); + String[] aEntries = (String[]) + AnyConverter.toObject(String[].class, + xPropSet.getPropertyValue( "UserLists" )); + System.out.println("User defined sort lists:"); + for ( int i=0; i<aEntries.length; i++ ) + System.out.println( aEntries[i] ); + } + + + +} + +/* vim:set shiftwidth=4 softtabstop=4 expandtab: */ diff --git a/odk/examples/DevelopersGuide/Spreadsheet/ViewSample.java b/odk/examples/DevelopersGuide/Spreadsheet/ViewSample.java new file mode 100644 index 000000000..e1256f04c --- /dev/null +++ b/odk/examples/DevelopersGuide/Spreadsheet/ViewSample.java @@ -0,0 +1,164 @@ +/* -*- Mode: Java; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */ +/************************************************************************* + * + * The Contents of this file are made available subject to the terms of + * the BSD license. + * + * Copyright 2000, 2010 Oracle and/or its affiliates. + * All rights reserved. + * + * Redistribution and use in source and binary forms, with or without + * modification, are permitted provided that the following conditions + * are met: + * 1. Redistributions of source code must retain the above copyright + * notice, this list of conditions and the following disclaimer. + * 2. Redistributions in binary form must reproduce the above copyright + * notice, this list of conditions and the following disclaimer in the + * documentation and/or other materials provided with the distribution. + * 3. Neither the name of Sun Microsystems, Inc. nor the names of its + * contributors may be used to endorse or promote products derived + * from this software without specific prior written permission. + * + * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS + * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT + * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS + * FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE + * COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, + * INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, + * BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS + * OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND + * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR + * TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE + * USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. + * + *************************************************************************/ + +import com.sun.star.uno.UnoRuntime; + +// __________ implementation ____________________________________ + +/** Create and modify a spreadsheet view. + */ +public class ViewSample extends SpreadsheetDocHelper +{ + + + + public static void main( String args[] ) + { + try + { + ViewSample aSample = new ViewSample( args ); + aSample.doSampleFunction(); + } + catch (Exception ex) + { + System.out.println( "Sample caught exception! " + ex ); + System.exit( 1 ); + } + System.out.println( "\nSamples done." ); + System.exit( 0 ); + } + + + + public ViewSample( String[] args ) + { + super( args ); + } + + + + /** This sample function performs all changes on the view. */ + public void doSampleFunction() throws Exception + { + com.sun.star.sheet.XSpreadsheetDocument xDoc = getDocument(); + com.sun.star.frame.XModel xModel = UnoRuntime.queryInterface( com.sun.star.frame.XModel.class, xDoc); + com.sun.star.frame.XController xController = xModel.getCurrentController(); + + // --- Spreadsheet view --- + // freeze the first column and first two rows + com.sun.star.sheet.XViewFreezable xFreeze = UnoRuntime.queryInterface( com.sun.star.sheet.XViewFreezable.class, xController ); + if ( null != xFreeze ) + System.out.println( "got xFreeze" ); + xFreeze.freezeAtPosition( 1, 2 ); + + // --- View pane --- + // get the cell range shown in the second pane and assign a cell background to them + com.sun.star.container.XIndexAccess xIndex = UnoRuntime.queryInterface( com.sun.star.container.XIndexAccess.class, xController ); + Object aPane = xIndex.getByIndex(1); + com.sun.star.sheet.XCellRangeReferrer xRefer = UnoRuntime.queryInterface( com.sun.star.sheet.XCellRangeReferrer.class, aPane ); + com.sun.star.table.XCellRange xRange = xRefer.getReferredCells(); + com.sun.star.beans.XPropertySet xRangeProp = UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xRange ); + xRangeProp.setPropertyValue( "IsCellBackgroundTransparent", Boolean.FALSE ); + xRangeProp.setPropertyValue( "CellBackColor", Integer.valueOf( 0xFFFFCC ) ); + + // --- View settings --- + // change the view to display green grid lines + com.sun.star.beans.XPropertySet xProp = UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xController ); + xProp.setPropertyValue( "ShowGrid", Boolean.TRUE ); + xProp.setPropertyValue( "GridColor", Integer.valueOf(0x00CC00) ); + + // --- Range selection --- + // let the user select a range and use it as the view's selection + com.sun.star.sheet.XRangeSelection xRngSel = UnoRuntime.queryInterface( com.sun.star.sheet.XRangeSelection.class, xController ); + ExampleRangeListener aListener = new ExampleRangeListener(); + xRngSel.addRangeSelectionListener( aListener ); + com.sun.star.beans.PropertyValue[] aArguments = new com.sun.star.beans.PropertyValue[2]; + aArguments[0] = new com.sun.star.beans.PropertyValue(); + aArguments[0].Name = "Title"; + aArguments[0].Value = "Please select a cell range (e.g. C4:E6)"; + aArguments[1] = new com.sun.star.beans.PropertyValue(); + aArguments[1].Name = "CloseOnMouseRelease"; + aArguments[1].Value = Boolean.TRUE; + xRngSel.startRangeSelection( aArguments ); + synchronized (aListener) + { + aListener.wait(); // wait until the selection is done + } + xRngSel.removeRangeSelectionListener( aListener ); + if ( aListener.aResult != null && aListener.aResult.length() != 0 ) + { + com.sun.star.view.XSelectionSupplier xSel = UnoRuntime.queryInterface( com.sun.star.view.XSelectionSupplier.class, xController ); + com.sun.star.sheet.XSpreadsheetView xView = UnoRuntime.queryInterface( com.sun.star.sheet.XSpreadsheetView.class, xController ); + com.sun.star.sheet.XSpreadsheet xSheet = xView.getActiveSheet(); + com.sun.star.table.XCellRange xResultRange = xSheet.getCellRangeByName( aListener.aResult ); + xSel.select( xResultRange ); + } + } + + + + // listener to react on finished selection + + private class ExampleRangeListener implements com.sun.star.sheet.XRangeSelectionListener + { + public String aResult; + + public void done( com.sun.star.sheet.RangeSelectionEvent aEvent ) + { + aResult = aEvent.RangeDescriptor; + synchronized (this) + { + notify(); + } + } + + public void aborted( com.sun.star.sheet.RangeSelectionEvent aEvent ) + { + synchronized (this) + { + notify(); + } + } + + public void disposing( com.sun.star.lang.EventObject aObj ) + { + } + } + + + +} + +/* vim:set shiftwidth=4 softtabstop=4 expandtab: */ diff --git a/odk/examples/DevelopersGuide/Spreadsheet/XExampleAddIn.idl b/odk/examples/DevelopersGuide/Spreadsheet/XExampleAddIn.idl new file mode 100644 index 000000000..2d75f437b --- /dev/null +++ b/odk/examples/DevelopersGuide/Spreadsheet/XExampleAddIn.idl @@ -0,0 +1,62 @@ +/* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */ +/************************************************************************* + * + * The Contents of this file are made available subject to the terms of + * the BSD license. + * + * Copyright 2000, 2010 Oracle and/or its affiliates. + * All rights reserved. + * + * Redistribution and use in source and binary forms, with or without + * modification, are permitted provided that the following conditions + * are met: + * 1. Redistributions of source code must retain the above copyright + * notice, this list of conditions and the following disclaimer. + * 2. Redistributions in binary form must reproduce the above copyright + * notice, this list of conditions and the following disclaimer in the + * documentation and/or other materials provided with the distribution. + * 3. Neither the name of Sun Microsystems, Inc. nor the names of its + * contributors may be used to endorse or promote products derived + * from this software without specific prior written permission. + * + * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS + * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT + * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS + * FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE + * COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, + * INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, + * BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS + * OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND + * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR + * TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE + * USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. + * + *************************************************************************/ + +#ifndef _ORG_OPENOFFICE_SHEET_ADDIN_XEXAMPLEADDIN_IDL_ +#define _ORG_OPENOFFICE_SHEET_ADDIN_XEXAMPLEADDIN_IDL_ + +#include <com/sun/star/uno/XInterface.idl> +#include <com/sun/star/sheet/XVolatileResult.idl> + +module org { + module openoffice { + module sheet { + module addin { + /// The interface that the example addin implements + interface XExampleAddIn : com::sun::star::uno::XInterface + { + /// Sample function that just increments a value. + long getIncremented( [in] long nValue ); + + /// Sample function that returns a volatile result. + com::sun::star::sheet::XVolatileResult getCounter( [in] string aName ); + }; + }; + }; + }; +}; + +#endif + +/* vim:set shiftwidth=4 softtabstop=4 expandtab: */ diff --git a/odk/examples/DevelopersGuide/Spreadsheet/description.xml b/odk/examples/DevelopersGuide/Spreadsheet/description.xml new file mode 100644 index 000000000..6c4e2396b --- /dev/null +++ b/odk/examples/DevelopersGuide/Spreadsheet/description.xml @@ -0,0 +1,15 @@ +<?xml version="1.0" encoding="UTF-8"?> +<!-- + * This file is part of the LibreOffice project. + * + * This Source Code Form is subject to the terms of the Mozilla Public + * License, v. 2.0. If a copy of the MPL was not distributed with this + * file, You can obtain one at http://mozilla.org/MPL/2.0/. + * +--> +<d:description xmlns:d="http://openoffice.org/extensions/description/2006"> + <d:dependencies> + <l:LibreOffice-minimal-version value="4.1" d:name="LibreOffice 4.1" + xmlns:l="http://libreoffice.org/extensions/description/2011"/> + </d:dependencies> +</d:description> |