sc/source/ui/vba/vbavalidation.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
  20 #include "vbavalidation.hxx"
  21 #include "vbaformatcondition.hxx" //#i108860
  22 #include <com/sun/star/sheet/XSheetCondition.hpp>
  23 #include <com/sun/star/sheet/ValidationType.hpp>
  24 #include <com/sun/star/sheet/ValidationAlertStyle.hpp>
  25 #include <com/sun/star/beans/XPropertySet.hpp>
  26 #include <ooo/vba/excel/XlDVType.hpp>
  27 #include <ooo/vba/excel/XlFormatConditionOperator.hpp>
  28 #include <ooo/vba/excel/XlDVAlertStyle.hpp>
  29
  30 #include "unonames.hxx"
  31 #include "rangelst.hxx"
  32 #include "excelvbahelper.hxx"
  33 #include "vbarange.hxx"
  34
  35 using namespace ::ooo::vba;
  36 using namespace ::com::sun::star;
  37
  38 const static OUString VALIDATION( SC_UNONAME_VALIDAT );
  39 const static OUString IGNOREBLANK( SC_UNONAME_IGNOREBL );
  40 const static OUString SHOWINPUT( SC_UNONAME_SHOWINP  );
  41 const static OUString SHOWERROR( SC_UNONAME_SHOWERR  );
  42 const static OUString ERRORTITLE(  SC_UNONAME_ERRTITLE  );
  43 const static OUString INPUTTITLE(  SC_UNONAME_INPTITLE  );
  44 const static OUString INPUTMESS(  SC_UNONAME_INPMESS  );
  45 const static OUString ERRORMESS(  SC_UNONAME_ERRMESS  );
  46 const static OUString STYPE(  SC_UNONAME_TYPE  );
  47 const static OUString SHOWLIST(  SC_UNONAME_SHOWLIST  );
  48 const static OUString ALERTSTYLE(  SC_UNONAME_ERRALSTY  );
  49
  50 static void
  51 lcl_setValidationProps( const uno::Reference< table::XCellRange >& xRange, const uno::Reference< beans::XPropertySet >& xProps )
  52 {
  53     uno::Reference< beans::XPropertySet > xRangeProps( xRange, uno::UNO_QUERY_THROW );
  54     xRangeProps->setPropertyValue( VALIDATION , uno::makeAny( xProps ) );
  55 }
  56
  57 static uno::Reference< beans::XPropertySet >
  58 lcl_getValidationProps( const uno::Reference< table::XCellRange >& xRange )
  59 {
  60     uno::Reference< beans::XPropertySet > xProps( xRange, uno::UNO_QUERY_THROW );
  61     uno::Reference< beans::XPropertySet > xValProps;
  62     xValProps.set( xProps->getPropertyValue( VALIDATION ), uno::UNO_QUERY_THROW );
  63     return xValProps;
  64 }
  65
  66 ::sal_Bool SAL_CALL
  67 ScVbaValidation::getIgnoreBlank() throw (uno::RuntimeException)
  68 {
  69     uno::Reference< beans::XPropertySet > xProps( lcl_getValidationProps( m_xRange ) );
  70     sal_Bool bBlank = false;
  71     xProps->getPropertyValue( IGNOREBLANK )  >>= bBlank;
  72     return bBlank;
  73 }
  74
  75 void SAL_CALL
  76 ScVbaValidation::setIgnoreBlank( ::sal_Bool _ignoreblank ) throw (uno::RuntimeException)
  77 {
  78     uno::Reference< beans::XPropertySet > xProps( lcl_getValidationProps( m_xRange ) );
  79     xProps->setPropertyValue( IGNOREBLANK, uno::makeAny( _ignoreblank ) );
  80     lcl_setValidationProps( m_xRange, xProps );
  81 }
  82
  83 ::sal_Bool SAL_CALL
  84 ScVbaValidation::getInCellDropdown() throw (uno::RuntimeException)
  85 {
  86     uno::Reference< beans::XPropertySet > xProps = lcl_getValidationProps( m_xRange );
  87     sal_Int32 nShowList = 0;
  88     xProps->getPropertyValue( SHOWLIST )  >>= nShowList;
  89     return ( nShowList ? sal_True : false );
  90 }
  91
  92 void SAL_CALL
  93 ScVbaValidation::setInCellDropdown( ::sal_Bool  _incelldropdown  ) throw (uno::RuntimeException)
  94 {
  95     sal_Int32 nDropDown = 0;
  96     if ( _incelldropdown )
  97         nDropDown = 1;
  98     uno::Reference< beans::XPropertySet > xProps( lcl_getValidationProps(m_xRange) );
  99     xProps->setPropertyValue( SHOWLIST, uno::makeAny( nDropDown ) );
 100     lcl_setValidationProps( m_xRange, xProps );
 101 }
 102
 103 ::sal_Bool SAL_CALL
 104 ScVbaValidation::getShowInput() throw (uno::RuntimeException)
 105 {
 106     uno::Reference< beans::XPropertySet > xProps = lcl_getValidationProps( m_xRange );
 107     sal_Bool bShowInput = false;
 108     xProps->getPropertyValue( SHOWINPUT )  >>= bShowInput;
 109     return bShowInput;
 110 }
 111
 112 void SAL_CALL
 113 ScVbaValidation:: setShowInput( ::sal_Bool _showinput ) throw (uno::RuntimeException)
 114 {
 115     uno::Reference< beans::XPropertySet > xProps( lcl_getValidationProps(m_xRange) );
 116     xProps->setPropertyValue( IGNOREBLANK, uno::makeAny( _showinput ) );
 117     lcl_setValidationProps( m_xRange, xProps );
 118 }
 119
 120 ::sal_Bool SAL_CALL
 121 ScVbaValidation::getShowError() throw (uno::RuntimeException)
 122 {
 123     uno::Reference< beans::XPropertySet > xProps = lcl_getValidationProps( m_xRange );
 124     sal_Bool bShowError = false;
 125     xProps->getPropertyValue( SHOWERROR )  >>= bShowError;
 126     return bShowError;
 127 }
 128
 129 void SAL_CALL
 130 ScVbaValidation::setShowError( ::sal_Bool _showerror ) throw (uno::RuntimeException)
 131 {
 132     uno::Reference< beans::XPropertySet > xProps( lcl_getValidationProps( m_xRange ) );
 133     xProps->setPropertyValue( SHOWERROR, uno::makeAny( _showerror ) );
 134     lcl_setValidationProps( m_xRange, xProps );
 135 }
 136
 137 OUString SAL_CALL
 138 ScVbaValidation::getErrorTitle() throw (uno::RuntimeException)
 139 {
 140     uno::Reference< beans::XPropertySet > xProps = lcl_getValidationProps( m_xRange );
 141     OUString sErrorTitle;
 142     xProps->getPropertyValue( ERRORTITLE )  >>= sErrorTitle;
 143     return sErrorTitle;
 144 }
 145
 146 void
 147 ScVbaValidation::setErrorTitle( const OUString& _errormessage ) throw (uno::RuntimeException)
 148 {
 149     uno::Reference< beans::XPropertySet > xProps( lcl_getValidationProps( m_xRange ) );
 150     xProps->setPropertyValue( ERRORTITLE, uno::makeAny( _errormessage ) );
 151     lcl_setValidationProps( m_xRange, xProps );
 152 }
 153
 154 OUString SAL_CALL
 155 ScVbaValidation::getInputMessage() throw (uno::RuntimeException)
 156 {
 157     uno::Reference< beans::XPropertySet > xProps = lcl_getValidationProps( m_xRange );
 158     OUString sMsg;
 159     xProps->getPropertyValue( INPUTMESS )  >>= sMsg;
 160     return sMsg;
 161 }
 162
 163 void SAL_CALL
 164 ScVbaValidation::setInputMessage( const OUString& _inputmessage ) throw (uno::RuntimeException)
 165 {
 166     uno::Reference< beans::XPropertySet > xProps( lcl_getValidationProps( m_xRange ) );
 167     xProps->setPropertyValue( INPUTMESS, uno::makeAny( _inputmessage ) );
 168     lcl_setValidationProps( m_xRange, xProps );
 169 }
 170
 171 OUString SAL_CALL
 172 ScVbaValidation::getInputTitle() throw (uno::RuntimeException)
 173 {
 174     uno::Reference< beans::XPropertySet > xProps = lcl_getValidationProps( m_xRange );
 175     OUString sString;
 176     xProps->getPropertyValue( INPUTTITLE )  >>= sString;
 177     return sString;
 178 }
 179
 180 void SAL_CALL
 181 ScVbaValidation::setInputTitle( const OUString& _inputtitle ) throw (uno::RuntimeException)
 182 {
 183     uno::Reference< beans::XPropertySet > xProps( lcl_getValidationProps( m_xRange ) );
 184     xProps->setPropertyValue( INPUTTITLE, uno::makeAny( _inputtitle ) );
 185     lcl_setValidationProps( m_xRange, xProps );
 186 }
 187
 188 OUString SAL_CALL
 189 ScVbaValidation::getErrorMessage() throw (uno::RuntimeException)
 190 {
 191     uno::Reference< beans::XPropertySet > xProps = lcl_getValidationProps( m_xRange );
 192     OUString sString;
 193     xProps->getPropertyValue( ERRORMESS )  >>= sString;
 194     return sString;
 195 }
 196
 197 void SAL_CALL
 198 ScVbaValidation::setErrorMessage( const OUString& _errormessage ) throw (uno::RuntimeException)
 199 {
 200     uno::Reference< beans::XPropertySet > xProps( lcl_getValidationProps( m_xRange ) );
 201     xProps->setPropertyValue( ERRORMESS, uno::makeAny( _errormessage ) );
 202     lcl_setValidationProps( m_xRange, xProps );
 203 }
 204
 205
 206 void SAL_CALL
 207 ScVbaValidation::Delete(  ) throw (uno::RuntimeException)
 208 {
 209     OUString sBlank;
 210     uno::Reference< beans::XPropertySet > xProps( lcl_getValidationProps( m_xRange ) );
 211     uno::Reference< sheet::XSheetCondition > xCond( xProps, uno::UNO_QUERY_THROW );
 212     xProps->setPropertyValue( IGNOREBLANK, uno::makeAny( sal_True ) );
 213     xProps->setPropertyValue( SHOWINPUT, uno::makeAny( sal_True ) );
 214     xProps->setPropertyValue( SHOWERROR, uno::makeAny( sal_True ) );
 215     xProps->setPropertyValue( ERRORTITLE, uno::makeAny( sBlank ) );
 216     xProps->setPropertyValue( INPUTMESS, uno::makeAny( sBlank) );
 217     xProps->setPropertyValue( ALERTSTYLE, uno::makeAny( sheet::ValidationAlertStyle_STOP) );
 218     xProps->setPropertyValue( STYPE, uno::makeAny( sheet::ValidationType_ANY ) );
 219     xCond->setFormula1( sBlank );
 220     xCond->setFormula2( sBlank );
 221     xCond->setOperator( sheet::ConditionOperator_NONE );
 222
 223     lcl_setValidationProps( m_xRange, xProps );
 224 }
 225
 226 // Fix the defect that validatation cannot work when the input should be limited between a lower bound and an upper bound
 227 void SAL_CALL
 228 ScVbaValidation::Add( const uno::Any& Type, const uno::Any& AlertStyle, const uno::Any& Operator, const uno::Any& Formula1, const uno::Any& Formula2 ) throw (uno::RuntimeException)
 229 {
 230     uno::Reference< beans::XPropertySet > xProps( lcl_getValidationProps( m_xRange ) );
 231     uno::Reference< sheet::XSheetCondition > xCond( xProps, uno::UNO_QUERY_THROW );
 232
 233     sheet::ValidationType nValType = sheet::ValidationType_ANY;
 234     xProps->getPropertyValue( STYPE )  >>= nValType;
 235     if ( nValType  != sheet::ValidationType_ANY  )
 236         throw uno::RuntimeException( OUString( "validation object already exists" ), uno::Reference< uno::XInterface >() );
 237     sal_Int32 nType = -1;
 238     if ( !Type.hasValue()  || !( Type >>= nType ) )
 239         throw uno::RuntimeException( OUString( "missing required param" ), uno::Reference< uno::XInterface >() );
 240
 241     Delete(); // set up defaults
 242     OUString sFormula1;
 243     Formula1 >>= sFormula1;
 244     OUString sFormula2;
 245     Formula2 >>= sFormula2;
 246     switch ( nType )
 247     {
 248         case excel::XlDVType::xlValidateList:
 249             {
 250                 // for validate list
 251                 // at least formula1 is required
 252                 if ( !Formula1.hasValue() )
 253                     throw uno::RuntimeException( OUString( "missing param" ), uno::Reference< uno::XInterface >() );
 254                 nValType = sheet::ValidationType_LIST;
 255                 xProps->setPropertyValue( STYPE, uno::makeAny(nValType ));
 256                 // #TODO validate required params
 257                 // #TODO need to correct the ';' delimited formula on get/set
 258                 break;
 259             }
 260         case excel::XlDVType::xlValidateWholeNumber:
 261             nValType = sheet::ValidationType_WHOLE;
 262             xProps->setPropertyValue( STYPE, uno::makeAny(nValType ));
 263             break;
 264         default:
 265             throw uno::RuntimeException( OUString( "unsupported operation..." ), uno::Reference< uno::XInterface >() );
 266     }
 267
 268     sheet::ValidationAlertStyle eStyle = sheet::ValidationAlertStyle_STOP;
 269     sal_Int32 nVbaAlertStyle = excel::XlDVAlertStyle::xlValidAlertStop;
 270     if ( AlertStyle.hasValue() && ( AlertStyle >>= nVbaAlertStyle ) )
 271     {
 272         switch( nVbaAlertStyle )
 273         {
 274             case excel::XlDVAlertStyle::xlValidAlertStop:
 275                 // yes I know it's already defaulted but safer to assume
 276                 // someone propbably could change the code above
 277                 eStyle = sheet::ValidationAlertStyle_STOP;
 278                 break;
 279             case excel::XlDVAlertStyle::xlValidAlertWarning:
 280                 eStyle = sheet::ValidationAlertStyle_WARNING;
 281                 break;
 282             case excel::XlDVAlertStyle::xlValidAlertInformation:
 283                 eStyle = sheet::ValidationAlertStyle_INFO;
 284                 break;
 285             default:
 286             throw uno::RuntimeException( OUString( "bad param..." ), uno::Reference< uno::XInterface >() );
 287
 288         }
 289     }
 290
 291     xProps->setPropertyValue( ALERTSTYLE, uno::makeAny( eStyle ) );
 292
 293     // i#108860: fix the defect that validation cannot work when the input
 294     // should be limited between a lower bound and an upper bound
 295     if ( Operator.hasValue() )
 296     {
 297         css::sheet::ConditionOperator conOperator = ScVbaFormatCondition::retrieveAPIOperator( Operator );
 298         xCond->setOperator( conOperator );
 299     }
 300
 301     if ( !sFormula1.isEmpty() )
 302         xCond->setFormula1( sFormula1 );
 303     if ( !sFormula2.isEmpty() )
 304         xCond->setFormula2( sFormula2 );
 305
 306     lcl_setValidationProps( m_xRange, xProps );
 307 }
 308
 309 OUString SAL_CALL
 310 ScVbaValidation::getFormula1() throw (uno::RuntimeException)
 311 {
 312     uno::Reference< sheet::XSheetCondition > xCond( lcl_getValidationProps( m_xRange ), uno::UNO_QUERY_THROW );
 313     OUString sString = xCond->getFormula1();
 314
 315     sal_uInt16 nFlags = 0;
 316     ScRangeList aCellRanges;
 317     formula::FormulaGrammar::AddressConvention eConv = formula::FormulaGrammar::CONV_XL_A1;
 318
 319     ScDocShell* pDocSh = excel::GetDocShellFromRange( m_xRange );
 320     // in calc validation formula is either a range or formula
 321     // that results in range.
 322     // In VBA both formula and address can have a leading '='
 323     // in result of getFormula1, however it *seems* that a named range or
 324     // real formula has to (or is expected to) have the '='
 325     if ( pDocSh && !ScVbaRange::getCellRangesForAddress(  nFlags, sString, pDocSh, aCellRanges, eConv ) )
 326         sString = "=" + sString;
 327     return sString;
 328 }
 329
 330 OUString SAL_CALL
 331 ScVbaValidation::getFormula2() throw (uno::RuntimeException)
 332 {
 333         uno::Reference< sheet::XSheetCondition > xCond( lcl_getValidationProps( m_xRange ), uno::UNO_QUERY_THROW );
 334     return xCond->getFormula2();
 335 }
 336
 337 sal_Int32 SAL_CALL
 338 ScVbaValidation::getType() throw (uno::RuntimeException)
 339 {
 340     uno::Reference< beans::XPropertySet > xProps( lcl_getValidationProps( m_xRange ) );
 341     sheet::ValidationType nValType = sheet::ValidationType_ANY;
 342     xProps->getPropertyValue( STYPE )  >>= nValType;
 343     sal_Int32 nExcelType = excel::XlDVType::xlValidateList; // pick a default
 344     if ( xProps.is() )
 345     {
 346         switch ( nValType )
 347         {
 348             case sheet::ValidationType_LIST:
 349                 nExcelType = excel::XlDVType::xlValidateList;
 350                 break;
 351             case sheet::ValidationType_ANY: // not ANY not really a great match for anything I fear:-(
 352                 nExcelType = excel::XlDVType::xlValidateInputOnly;
 353                 break;
 354             case sheet::ValidationType_CUSTOM:
 355                 nExcelType = excel::XlDVType::xlValidateCustom;
 356                 break;
 357             case sheet::ValidationType_WHOLE:
 358                 nExcelType = excel::XlDVType::xlValidateWholeNumber;
 359                 break;
 360             case sheet::ValidationType_DECIMAL:
 361                 nExcelType = excel::XlDVType::xlValidateDecimal;
 362                 break;
 363             case sheet::ValidationType_DATE:
 364                 nExcelType = excel::XlDVType::xlValidateDate;
 365                 break;
 366             case sheet::ValidationType_TIME:
 367                 nExcelType = excel::XlDVType::xlValidateTime;
 368                 break;
 369             case sheet::ValidationType_TEXT_LEN:
 370                 nExcelType = excel::XlDVType::xlValidateTextLength;
 371                 break;
 372             case sheet::ValidationType_MAKE_FIXED_SIZE:
 373             default:
 374                 break;
 375         };
 376     }
 377     return nExcelType;
 378 }
 379
 380 OUString
 381 ScVbaValidation::getServiceImplName()
 382 {
 383     return OUString("ScVbaValidation");
 384 }
 385
 386 uno::Sequence< OUString >
 387 ScVbaValidation::getServiceNames()
 388 {
 389     static uno::Sequence< OUString > aServiceNames;
 390     if ( aServiceNames.getLength() == 0 )
 391     {
 392         aServiceNames.realloc( 1 );
 393         aServiceNames[ 0 ] = OUString("ooo.vba.excel.Validation" );
 394     }
 395     return aServiceNames;
 396 }
 397
 398 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */