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"
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
35using namespace ::ooo::vba;
36using namespace ::com::sun::star;
37
38static void
39lcl_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
45static uno::Reference< beans::XPropertySet >
46lcl_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
54sal_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
63void SAL_CALL
65{
66 uno::Reference< beans::XPropertySet > xProps( lcl_getValidationProps( m_xRange ) );
67 xProps->setPropertyValue( SC_UNONAME_IGNOREBL, uno::Any( _ignoreblank ) );
69}
70
71sal_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
80void 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::Any( nDropDown ) );
89}
90
91sal_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
100void SAL_CALL
102{
103 uno::Reference< beans::XPropertySet > xProps( lcl_getValidationProps(m_xRange) );
104 xProps->setPropertyValue( SC_UNONAME_IGNOREBL, uno::Any( _showinput ) );
106}
107
108sal_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
117void SAL_CALL
119{
120 uno::Reference< beans::XPropertySet > xProps( lcl_getValidationProps( m_xRange ) );
121 xProps->setPropertyValue( SC_UNONAME_SHOWERR, uno::Any( _showerror ) );
123}
124
125OUString 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
134void
135ScVbaValidation::setErrorTitle( const OUString& _errormessage )
136{
137 uno::Reference< beans::XPropertySet > xProps( lcl_getValidationProps( m_xRange ) );
138 xProps->setPropertyValue( SC_UNONAME_ERRTITLE, uno::Any( _errormessage ) );
140}
141
142OUString 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
151void SAL_CALL
152ScVbaValidation::setInputMessage( const OUString& _inputmessage )
153{
154 uno::Reference< beans::XPropertySet > xProps( lcl_getValidationProps( m_xRange ) );
155 xProps->setPropertyValue( SC_UNONAME_INPMESS, uno::Any( _inputmessage ) );
157}
158
159OUString 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
168void SAL_CALL
169ScVbaValidation::setInputTitle( const OUString& _inputtitle )
170{
171 uno::Reference< beans::XPropertySet > xProps( lcl_getValidationProps( m_xRange ) );
172 xProps->setPropertyValue( SC_UNONAME_INPTITLE, uno::Any( _inputtitle ) );
174}
175
176OUString 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
185void SAL_CALL
186ScVbaValidation::setErrorMessage( const OUString& _errormessage )
187{
188 uno::Reference< beans::XPropertySet > xProps( lcl_getValidationProps( m_xRange ) );
189 xProps->setPropertyValue( SC_UNONAME_ERRMESS, uno::Any( _errormessage ) );
191}
192
193void 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::Any( true ) );
200 xProps->setPropertyValue( SC_UNONAME_SHOWINP, uno::Any( true ) );
201 xProps->setPropertyValue( SC_UNONAME_SHOWERR, uno::Any( true ) );
202 xProps->setPropertyValue( SC_UNONAME_ERRTITLE, uno::Any( sBlank ) );
203 xProps->setPropertyValue( SC_UNONAME_INPMESS, uno::Any( sBlank) );
204 xProps->setPropertyValue( SC_UNONAME_ERRALSTY, uno::Any( sheet::ValidationAlertStyle_STOP) );
205 xProps->setPropertyValue( SC_UNONAME_TYPE, uno::Any( sheet::ValidationType_ANY ) );
206 xCond->setFormula1( sBlank );
207 xCond->setFormula2( sBlank );
208 xCond->setOperator( sheet::ConditionOperator_NONE );
209
211}
212
213// Fix the defect that validation cannot work when the input should be limited between a lower bound and an upper bound
214void SAL_CALL
215ScVbaValidation::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::Any(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::Any(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::Any( 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
294}
295
296OUString SAL_CALL
298{
299 uno::Reference< sheet::XSheetCondition > xCond( lcl_getValidationProps( m_xRange ), uno::UNO_QUERY_THROW );
300 OUString sString = xCond->getFormula1();
301
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
316OUString SAL_CALL
318{
319 uno::Reference< sheet::XSheetCondition > xCond( lcl_getValidationProps( m_xRange ), uno::UNO_QUERY_THROW );
320 return xCond->getFormula2();
321}
322
323sal_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
366OUString
368{
369 return "ScVbaValidation";
370}
371
372uno::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: */
ScRefFlags
Definition: address.hxx:158
static css::sheet::ConditionOperator retrieveAPIOperator(const css::uno::Any &_aOperator)
static bool getCellRangesForAddress(ScRefFlags &rResFlags, std::u16string_view sAddress, ScDocShell *pDocSh, ScRangeList &rCellRanges, formula::FormulaGrammar::AddressConvention eConv, char cDelimiter)
Definition: vbarange.cxx:1155
virtual sal_Bool SAL_CALL getShowError() override
virtual sal_Bool SAL_CALL getIgnoreBlank() override
virtual sal_Bool SAL_CALL getInCellDropdown() override
virtual OUString SAL_CALL getErrorMessage() 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 OUString SAL_CALL getInputTitle() override
virtual void SAL_CALL Delete() override
css::uno::Reference< css::table::XCellRange > m_xRange
virtual OUString SAL_CALL getFormula2() override
virtual OUString SAL_CALL getInputMessage() override
virtual OUString getServiceImplName() override
virtual void SAL_CALL setErrorTitle(const OUString &_errortitle) override
virtual void SAL_CALL setInCellDropdown(sal_Bool _incelldropdown) override
virtual OUString SAL_CALL getFormula1() override
virtual void SAL_CALL setIgnoreBlank(sal_Bool _ignoreblank) override
virtual void SAL_CALL setInputTitle(const OUString &_inputtitle) override
virtual void SAL_CALL setShowError(sal_Bool _showerror) override
virtual sal_Int32 SAL_CALL getType() override
virtual void SAL_CALL setErrorMessage(const OUString &_errormessage) override
virtual sal_Bool SAL_CALL getShowInput() override
virtual OUString SAL_CALL getErrorTitle() override
virtual void SAL_CALL setInputMessage(const OUString &_inputmessage) override
virtual void SAL_CALL setShowInput(sal_Bool _showinput) override
virtual css::uno::Sequence< OUString > getServiceNames() override
Sequence< OUString > aServiceNames
Type
ScDocShell * GetDocShellFromRange(const uno::Reference< uno::XInterface > &xRange)
QPRO_FUNC_TYPE nType
Definition: qproform.cxx:398
bool hasValue()
unsigned char sal_Bool
#define SC_UNONAME_ERRTITLE
Definition: unonames.hxx:357
#define SC_UNONAME_SHOWINP
Definition: unonames.hxx:362
#define SC_UNONAME_ERRMESS
Definition: unonames.hxx:356
#define SC_UNONAME_VALIDAT
Definition: unonames.hxx:208
#define SC_UNONAME_ERRALSTY
Definition: unonames.hxx:355
#define SC_UNONAME_SHOWERR
Definition: unonames.hxx:361
#define SC_UNONAME_SHOWLIST
Definition: unonames.hxx:363
#define SC_UNONAME_TYPE
Definition: unonames.hxx:364
#define SC_UNONAME_INPTITLE
Definition: unonames.hxx:360
#define SC_UNONAME_INPMESS
Definition: unonames.hxx:359
#define SC_UNONAME_IGNOREBL
Definition: unonames.hxx:358
static void lcl_setValidationProps(const uno::Reference< table::XCellRange > &xRange, const uno::Reference< beans::XPropertySet > &xProps)
static uno::Reference< beans::XPropertySet > lcl_getValidationProps(const uno::Reference< table::XCellRange > &xRange)