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