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