sc/source/ui/vba/vbawsfunction.cxx

   1 /* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
   2 /*
   3  * This file is part of the LibreOffice project.
   4  *
   5  * This Source Code Form is subject to the terms of the Mozilla Public
   6  * License, v. 2.0. If a copy of the MPL was not distributed with this
   7  * file, You can obtain one at http://mozilla.org/MPL/2.0/.
   8  *
   9  * This file incorporates work covered by the following license notice:
  10  *
  11  *   Licensed to the Apache Software Foundation (ASF) under one or more
  12  *   contributor license agreements. See the NOTICE file distributed
  13  *   with this work for additional information regarding copyright
  14  *   ownership. The ASF licenses this file to you under the Apache
  15  *   License, Version 2.0 (the "License"); you may not use this file
  16  *   except in compliance with the License. You may obtain a copy of
  17  *   the License at http://www.apache.org/licenses/LICENSE-2.0 .
  18  */
  19 #include <com/sun/star/beans/XPropertySet.hpp>
  20 #include <com/sun/star/table/XCell.hpp>
  21 #include <com/sun/star/table/XColumnRowRange.hpp>
  22 #include <com/sun/star/beans/XIntrospection.hpp>
  23 #include <com/sun/star/beans/XIntrospectionAccess.hpp>
  24 #include <com/sun/star/sheet/XFunctionAccess.hpp>
  25 #include <com/sun/star/sheet/XCellRangesQuery.hpp>
  26 #include <com/sun/star/sheet/XCellRangeAddressable.hpp>
  27 #include <com/sun/star/sheet/CellFlags.hpp>
  28 #include <com/sun/star/reflection/XIdlMethod.hpp>
  29 #include <com/sun/star/beans/MethodConcept.hpp>
  30 #include <comphelper/processfactory.hxx>
  31 #include <cppuhelper/queryinterface.hxx>
  32 #include <comphelper/anytostring.hxx>
  33
  34 #include "vbawsfunction.hxx"
  35 #include "compiler.hxx"
  36
  37 using namespace com::sun::star;
  38 using namespace ooo::vba;
  39
  40 namespace {
  41
  42 void lclConvertDoubleToBoolean( uno::Any& rAny )
  43 {
  44     if( rAny.has< double >() )
  45     {
  46         double fValue = rAny.get< double >();
  47         if( fValue == 0.0 )
  48             rAny <<= false;
  49         else if( fValue == 1.0 )
  50             rAny <<= true;
  51         // do nothing for other values or types
  52     }
  53 }
  54
  55 void lclConvertBooleanToDouble( uno::Any& rAny )
  56 {
  57     bool bValue( false );
  58     if ( rAny >>= bValue )
  59     {
  60         if ( bValue )
  61            rAny <<= double( 1.0 );
  62         else
  63            rAny <<= double( 0.0 );
  64     }
  65 }
  66
  67 } // namespace
  68
  69 ScVbaWSFunction::ScVbaWSFunction( const uno::Reference< XHelperInterface >& xParent, const css::uno::Reference< css::uno::XComponentContext >& xContext ) :
  70     ScVbaWSFunction_BASE( xParent, xContext )
  71 {
  72 }
  73
  74 uno::Reference< beans::XIntrospectionAccess >
  75 ScVbaWSFunction::getIntrospection()  throw(uno::RuntimeException, std::exception)
  76 {
  77     return uno::Reference<beans::XIntrospectionAccess>();
  78 }
  79
  80 uno::Any SAL_CALL
  81 ScVbaWSFunction::invoke(const OUString& FunctionName, const uno::Sequence< uno::Any >& Params, uno::Sequence< sal_Int16 >& /*OutParamIndex*/, uno::Sequence< uno::Any >& /*OutParam*/) throw(lang::IllegalArgumentException, script::CannotConvertException, reflection::InvocationTargetException, uno::RuntimeException, std::exception)
  82 {
  83     // create copy of parameters, replace Excel range objects with UNO range objects
  84     uno::Sequence< uno::Any > aParamTemp( Params );
  85     if( aParamTemp.hasElements() )
  86     {
  87         uno::Any* pArray = aParamTemp.getArray();
  88         uno::Any* pArrayEnd = pArray + aParamTemp.getLength();
  89         for( ; pArray < pArrayEnd; ++pArray )
  90         {
  91             switch( pArray->getValueType().getTypeClass()  )
  92             {
  93                 case uno::TypeClass_BOOLEAN:
  94                     lclConvertBooleanToDouble( *pArray );
  95                     break;
  96                 case uno::TypeClass_INTERFACE:
  97                 {
  98                     uno::Reference< excel::XRange > myRange( *pArray, uno::UNO_QUERY );
  99                     if( myRange.is() )
 100                         *pArray = myRange->getCellRange();
 101                 }
 102                     break;
 103                 case uno::TypeClass_SEQUENCE:
 104                 {
 105                     // the sheet.FunctionAccess service doesn't deal with Sequences, only Sequences of Sequence
 106                     uno::Type aType = pArray->getValueType();
 107                     if ( aType.equals( cppu::UnoType<uno::Sequence<sal_Int16>>::get() ) )
 108                     {
 109                         uno::Sequence< uno::Sequence< sal_Int16 > >  aTmp(1);
 110                         (*pArray) >>= aTmp[ 0 ];
 111                         (*pArray) <<= aTmp;
 112                     }
 113                     else if ( aType.equals( cppu::UnoType<uno::Sequence<sal_Int32>>::get() ) )
 114                     {
 115                         uno::Sequence< uno::Sequence< sal_Int32 > > aTmp(1);
 116                         (*pArray) >>= aTmp[ 0 ];
 117                         (*pArray) <<= aTmp;
 118                     }
 119                     else if ( aType.equals( cppu::UnoType<uno::Sequence<double>>::get() ) )
 120                     {
 121                         uno::Sequence< uno::Sequence< double > > aTmp(1);
 122                         (*pArray) >>= aTmp[ 0 ];
 123                         (*pArray) <<= aTmp;
 124                     }
 125                     else if ( aType.equals( cppu::UnoType<uno::Sequence<OUString>>::get() ) )
 126                     {
 127                         uno::Sequence< uno::Sequence< OUString > > aTmp(1);
 128                         (*pArray) >>= aTmp[ 0 ];
 129                         (*pArray) <<= aTmp;
 130                     }
 131                     else if ( aType.equals( cppu::UnoType<uno::Sequence<uno::Any>>::get() ) )
 132                     {
 133                         uno::Sequence< uno::Sequence<uno::Any > > aTmp(1);
 134                         (*pArray) >>= aTmp[ 0 ];
 135                         (*pArray) <<= aTmp;
 136                     }
 137                 }
 138                     break;
 139                 default:
 140                     break;
 141             }
 142             OSL_TRACE("Param[%d] is %s", (int)(pArray - aParamTemp.getConstArray()), OUStringToOString( comphelper::anyToString( *pArray ), RTL_TEXTENCODING_UTF8 ).getStr() );
 143         }
 144     }
 145
 146     uno::Any aRet;
 147     bool bAsArray = true;
 148
 149     // special handing for some functions that don't work correctly in FunctionAccess
 150     ScCompiler aCompiler( 0, ScAddress() );
 151     OpCode eOpCode = aCompiler.GetEnglishOpCode( FunctionName.toAsciiUpperCase() );
 152     switch( eOpCode )
 153     {
 154         // ISLOGICAL does not work in array formula mode
 155         case ocIsLogical:
 156         {
 157             if( aParamTemp.getLength() != 1 )
 158                 throw lang::IllegalArgumentException();
 159             const uno::Any& rParam = aParamTemp[ 0 ];
 160             if( rParam.has< bool >() )
 161             {
 162                 aRet <<= true;
 163             }
 164             else if( rParam.has< uno::Reference< table::XCellRange > >() ) try
 165             {
 166                 uno::Reference< sheet::XCellRangeAddressable > xRangeAddr( rParam, uno::UNO_QUERY_THROW );
 167                 table::CellRangeAddress aRangeAddr = xRangeAddr->getRangeAddress();
 168                 bAsArray = (aRangeAddr.StartColumn != aRangeAddr.EndColumn) || (aRangeAddr.StartRow != aRangeAddr.EndRow);
 169             }
 170             catch( uno::Exception& )
 171             {
 172             }
 173         }
 174         break;
 175         default:;
 176     }
 177
 178     if( !aRet.hasValue() )
 179     {
 180         uno::Reference< lang::XMultiComponentFactory > xSMgr( mxContext->getServiceManager(), uno::UNO_QUERY_THROW );
 181         uno::Reference< sheet::XFunctionAccess > xFunctionAccess( xSMgr->createInstanceWithContext(
 182             OUString( "com.sun.star.sheet.FunctionAccess" ), mxContext ),
 183             uno::UNO_QUERY_THROW );
 184         uno::Reference< beans::XPropertySet > xPropSet( xFunctionAccess, uno::UNO_QUERY_THROW );
 185         xPropSet->setPropertyValue("IsArrayFunction", uno::Any( bAsArray ) );
 186         aRet = xFunctionAccess->callFunction( FunctionName, aParamTemp );
 187     }
 188
 189     /*  Convert return value from double to Boolean for some functions that
 190         return Booleans. */
 191     typedef uno::Sequence< uno::Sequence< uno::Any > > AnySeqSeq;
 192     if( (eOpCode == ocIsEmpty) || (eOpCode == ocIsString) || (eOpCode == ocIsNonString) || (eOpCode == ocIsLogical) ||
 193         (eOpCode == ocIsRef) || (eOpCode == ocIsValue) || (eOpCode == ocIsFormula) || (eOpCode == ocIsNA) ||
 194         (eOpCode == ocIsErr) || (eOpCode == ocIsError) || (eOpCode == ocIsEven) || (eOpCode == ocIsOdd) ||
 195         (eOpCode == ocAnd) || (eOpCode == ocOr) || (eOpCode == ocXor) || (eOpCode == ocNot) || (eOpCode == ocTrue) || (eOpCode == ocFalse) )
 196     {
 197         if( aRet.has< AnySeqSeq >() )
 198         {
 199             AnySeqSeq aAnySeqSeq = aRet.get< AnySeqSeq >();
 200             for( sal_Int32 nRow = 0; nRow < aAnySeqSeq.getLength(); ++nRow )
 201                 for( sal_Int32 nCol = 0; nCol < aAnySeqSeq[ nRow ].getLength(); ++nCol )
 202                     lclConvertDoubleToBoolean( aAnySeqSeq[ nRow ][ nCol ] );
 203             aRet <<= aAnySeqSeq;
 204         }
 205         else
 206         {
 207             lclConvertDoubleToBoolean( aRet );
 208         }
 209     }
 210
 211     /*  Hack/workaround (?): shorten single-row matrix to simple array, shorten
 212         1x1 matrix to single value. */
 213     if( aRet.has< AnySeqSeq >() )
 214     {
 215         AnySeqSeq aAnySeqSeq = aRet.get< AnySeqSeq >();
 216         if( aAnySeqSeq.getLength() == 1 )
 217         {
 218             if( aAnySeqSeq[ 0 ].getLength() == 1 )
 219                 aRet = aAnySeqSeq[ 0 ][ 0 ];
 220             else
 221                 aRet <<= aAnySeqSeq[ 0 ];
 222         }
 223     }
 224
 225 #if 0
 226     // MATCH function should alwayse return a double value, but currently if the first argument is XCellRange, MATCH function returns an array instead of a double value. Don't know why?
 227     // To fix this issue in safe, current solution is to convert this array to a double value just for MATCH function.
 228     OUString aUpper( FunctionName.toAsciiUpperCase() );
 229     ScCompiler aCompiler( NULL, ScAddress() );
 230     OpCode eOp = aCompiler.GetEnglishOpCode( aUpper );
 231     if( eOp == ocMatch )
 232     {
 233         double fVal = 0.0;
 234         if( aRet >>= fVal )
 235             return aRet;
 236         uno::Sequence< uno::Sequence< uno::Any > > aSequence;
 237         if( !( ( aRet >>= aSequence ) && ( aSequence.getLength() > 0 ) &&
 238             ( aSequence[0].getLength() > 0 ) && ( aSequence[0][0] >>= fVal ) ) )
 239                 throw uno::RuntimeException();
 240         aRet <<= fVal;
 241     }
 242 #endif
 243
 244     return aRet;
 245 }
 246
 247 void SAL_CALL
 248 ScVbaWSFunction::setValue(const OUString& /*PropertyName*/, const uno::Any& /*Value*/) throw(beans::UnknownPropertyException, script::CannotConvertException, reflection::InvocationTargetException, uno::RuntimeException, std::exception)
 249 {
 250     throw beans::UnknownPropertyException();
 251 }
 252
 253 uno::Any SAL_CALL
 254 ScVbaWSFunction::getValue(const OUString& /*PropertyName*/) throw(beans::UnknownPropertyException, uno::RuntimeException, std::exception)
 255 {
 256     throw beans::UnknownPropertyException();
 257 }
 258
 259 sal_Bool SAL_CALL
 260 ScVbaWSFunction::hasMethod(const OUString& Name)  throw(uno::RuntimeException, std::exception)
 261 {
 262     bool bIsFound = false;
 263     try
 264     {
 265     // the function name contained in the com.sun.star.sheet.FunctionDescription service is alwayse localized.
 266         // but the function name used in WorksheetFunction is a programmatic name (seems English).
 267         // So m_xNameAccess->hasByName( Name ) may fail to find name when a function name has a localized name.
 268         ScCompiler aCompiler( NULL, ScAddress() );
 269         if( aCompiler.IsEnglishSymbol( Name ) )
 270             bIsFound = true;
 271     }
 272     catch( uno::Exception& /*e*/ )
 273     {
 274         // failed to find name
 275     }
 276     return bIsFound;
 277 }
 278
 279 sal_Bool SAL_CALL
 280 ScVbaWSFunction::hasProperty(const OUString& /*Name*/)  throw(uno::RuntimeException, std::exception)
 281 {
 282      return false;
 283 }
 284
 285 OUString SAL_CALL
 286 ScVbaWSFunction::getExactName( const OUString& aApproximateName ) throw (css::uno::RuntimeException, std::exception)
 287 {
 288     OUString sName = aApproximateName.toAsciiUpperCase();
 289     if ( !hasMethod( sName ) )
 290         return OUString();
 291     return sName;
 292 }
 293
 294 OUString
 295 ScVbaWSFunction::getServiceImplName()
 296 {
 297     return OUString("ScVbaWSFunction");
 298 }
 299
 300 uno::Sequence< OUString >
 301 ScVbaWSFunction::getServiceNames()
 302 {
 303     static uno::Sequence< OUString > aServiceNames;
 304     if ( aServiceNames.getLength() == 0 )
 305     {
 306         aServiceNames.realloc( 1 );
 307         aServiceNames[ 0 ] = "ooo.vba.excel.WorksheetFunction";
 308     }
 309     return aServiceNames;
 310 }
 311
 312 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */