LibreOffice Module sc (master)  1
vbavalidation.cxx
Go to the documentation of this file.
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::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
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
65 {
66  uno::Reference< beans::XPropertySet > xProps( lcl_getValidationProps( m_xRange ) );
67  xProps->setPropertyValue( SC_UNONAME_IGNOREBL, uno::makeAny( _ignoreblank ) );
69 }
70 
71 sal_Bool SAL_CALL
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
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 ) );
89 }
90 
91 sal_Bool SAL_CALL
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
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
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
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
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 )
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
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 )
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
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 )
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
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 )
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
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( true ) );
200  xProps->setPropertyValue( SC_UNONAME_SHOWINP, uno::makeAny( true ) );
201  xProps->setPropertyValue( SC_UNONAME_SHOWERR, uno::makeAny( 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 validation 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 )
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 probably 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
298 {
299  uno::Reference< sheet::XSheetCondition > xCond( lcl_getValidationProps( m_xRange ), uno::UNO_QUERY_THROW );
300  OUString sString = xCond->getFormula1();
301 
302  ScRefFlags nFlags = ScRefFlags::ZERO;
303  ScRangeList aCellRanges;
304 
306  // in calc validation formula is either a range or formula
307  // that results in range.
308  // In VBA both formula and address can have a leading '='
309  // in result of getFormula1, however it *seems* that a named range or
310  // real formula has to (or is expected to) have the '='
311  if ( pDocSh && !ScVbaRange::getCellRangesForAddress( nFlags, sString, pDocSh, aCellRanges, formula::FormulaGrammar::CONV_XL_A1, 0 ) )
312  sString = "=" + sString;
313  return sString;
314 }
315 
316 OUString SAL_CALL
318 {
319  uno::Reference< sheet::XSheetCondition > xCond( lcl_getValidationProps( m_xRange ), uno::UNO_QUERY_THROW );
320  return xCond->getFormula2();
321 }
322 
323 sal_Int32 SAL_CALL
325 {
326  uno::Reference< beans::XPropertySet > xProps( lcl_getValidationProps( m_xRange ) );
327  sheet::ValidationType nValType = sheet::ValidationType_ANY;
328  xProps->getPropertyValue( SC_UNONAME_TYPE ) >>= nValType;
329  sal_Int32 nExcelType = excel::XlDVType::xlValidateList; // pick a default
330  if ( xProps.is() )
331  {
332  switch ( nValType )
333  {
334  case sheet::ValidationType_LIST:
335  nExcelType = excel::XlDVType::xlValidateList;
336  break;
337  case sheet::ValidationType_ANY: // not ANY not really a great match for anything I fear:-(
338  nExcelType = excel::XlDVType::xlValidateInputOnly;
339  break;
340  case sheet::ValidationType_CUSTOM:
341  nExcelType = excel::XlDVType::xlValidateCustom;
342  break;
343  case sheet::ValidationType_WHOLE:
344  nExcelType = excel::XlDVType::xlValidateWholeNumber;
345  break;
346  case sheet::ValidationType_DECIMAL:
347  nExcelType = excel::XlDVType::xlValidateDecimal;
348  break;
349  case sheet::ValidationType_DATE:
350  nExcelType = excel::XlDVType::xlValidateDate;
351  break;
352  case sheet::ValidationType_TIME:
353  nExcelType = excel::XlDVType::xlValidateTime;
354  break;
355  case sheet::ValidationType_TEXT_LEN:
356  nExcelType = excel::XlDVType::xlValidateTextLength;
357  break;
358  case sheet::ValidationType::ValidationType_MAKE_FIXED_SIZE:
359  default:
360  break;
361  }
362  }
363  return nExcelType;
364 }
365 
366 OUString
368 {
369  return "ScVbaValidation";
370 }
371 
372 uno::Sequence< OUString >
374 {
375  static uno::Sequence< OUString > const aServiceNames
376  {
377  "ooo.vba.excel.Validation"
378  };
379  return aServiceNames;
380 }
381 
382 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
static css::sheet::ConditionOperator retrieveAPIOperator(const css::uno::Any &_aOperator)
Type
#define SC_UNONAME_INPMESS
Definition: unonames.hxx:358
bool hasValue()
virtual void SAL_CALL setErrorMessage(const OUString &_errormessage) override
virtual void SAL_CALL setInputTitle(const OUString &_inputtitle) override
ScDocShell * GetDocShellFromRange(const uno::Reference< uno::XInterface > &xRange)
virtual OUString SAL_CALL getErrorMessage() override
#define SC_UNONAME_SHOWINP
Definition: unonames.hxx:361
virtual void SAL_CALL setShowError(sal_Bool _showerror) override
#define SC_UNONAME_ERRMESS
Definition: unonames.hxx:355
Sequence< OUString > aServiceNames
virtual void SAL_CALL setShowInput(sal_Bool _showinput) override
#define SC_UNONAME_ERRTITLE
Definition: unonames.hxx:356
static uno::Reference< beans::XPropertySet > lcl_getValidationProps(const uno::Reference< table::XCellRange > &xRange)
virtual sal_Int32 SAL_CALL getType() override
#define SC_UNONAME_IGNOREBL
Definition: unonames.hxx:357
#define SC_UNONAME_ERRALSTY
Definition: unonames.hxx:354
virtual void SAL_CALL setIgnoreBlank(sal_Bool _ignoreblank) override
virtual void SAL_CALL setInCellDropdown(sal_Bool _incelldropdown) override
static void lcl_setValidationProps(const uno::Reference< table::XCellRange > &xRange, const uno::Reference< beans::XPropertySet > &xProps)
#define SC_UNONAME_SHOWERR
Definition: unonames.hxx:360
virtual OUString getServiceImplName() override
virtual css::uno::Sequence< OUString > getServiceNames() override
virtual void SAL_CALL Add(const css::uno::Any &Type, const css::uno::Any &AlertStyle, const css::uno::Any &Operator, const css::uno::Any &Formula1, const css::uno::Any &Formula2) override
virtual sal_Bool SAL_CALL getIgnoreBlank() override
virtual OUString SAL_CALL getFormula1() override
virtual sal_Bool SAL_CALL getShowInput() override
virtual void SAL_CALL setErrorTitle(const OUString &_errortitle) override
#define SC_UNONAME_TYPE
Definition: unonames.hxx:363
virtual void SAL_CALL setInputMessage(const OUString &_inputmessage) override
unsigned char sal_Bool
css::uno::Reference< css::table::XCellRange > m_xRange
virtual sal_Bool SAL_CALL getInCellDropdown() override
virtual sal_Bool SAL_CALL getShowError() override
#define SC_UNONAME_VALIDAT
Definition: unonames.hxx:207
#define SC_UNONAME_SHOWLIST
Definition: unonames.hxx:362
virtual OUString SAL_CALL getFormula2() override
QPRO_FUNC_TYPE nType
Definition: qproform.cxx:401
#define SC_UNONAME_INPTITLE
Definition: unonames.hxx:359
virtual OUString SAL_CALL getInputMessage() override
ScRefFlags
Definition: address.hxx:145
virtual OUString SAL_CALL getErrorTitle() override
static bool getCellRangesForAddress(ScRefFlags &rResFlags, const OUString &sAddress, ScDocShell *pDocSh, ScRangeList &rCellRanges, formula::FormulaGrammar::AddressConvention eConv, char cDelimiter)
Definition: vbarange.cxx:1146
virtual void SAL_CALL Delete() override
virtual OUString SAL_CALL getInputTitle() override