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 <autofilterbuffer.hxx>
21
22 #include <com/sun/star/beans/XPropertySet.hpp>
23 #include <com/sun/star/sheet/FilterFieldType.hpp>
24 #include <com/sun/star/sheet/FilterConnection.hpp>
25 #include <com/sun/star/sheet/FilterOperator2.hpp>
26 #include <com/sun/star/sheet/TableFilterField3.hpp>
27 #include <com/sun/star/sheet/XDatabaseRange.hpp>
28 #include <com/sun/star/sheet/XSheetFilterDescriptor3.hpp>
29 #include <com/sun/star/table/TableOrientation.hpp>
30 #include <com/sun/star/table/CellAddress.hpp>
31 #include <editeng/colritem.hxx>
32 #include <editeng/brushitem.hxx>
33 #include <rtl/ustrbuf.hxx>
34 #include <osl/diagnose.h>
35 #include <oox/helper/attributelist.hxx>
36 #include <oox/helper/containerhelper.hxx>
37 #include <oox/helper/propertyset.hxx>
38 #include <oox/helper/binaryinputstream.hxx>
39 #include <oox/token/namespaces.hxx>
40 #include <oox/token/properties.hxx>
41 #include <oox/token/tokens.hxx>
42 #include <addressconverter.hxx>
43 #include <defnamesbuffer.hxx>
44 #include <biffhelper.hxx>
45 #include <document.hxx>
46 #include <dbdata.hxx>
47 #include <scitems.hxx>
48 #include <sortparam.hxx>
49 #include <stlpool.hxx>
50 #include <stlsheet.hxx>
51 #include <stylesbuffer.hxx>
52 #include <userlist.hxx>
53
54 namespace oox::xls {
55
56 using namespace css;
57 using namespace ::com::sun::star::sheet;
58 using namespace ::com::sun::star::table;
59 using namespace ::com::sun::star::uno;
60
61 namespace {
62
63 const sal_uInt8 BIFF12_TOP10FILTER_TOP = 0x01;
64 const sal_uInt8 BIFF12_TOP10FILTER_PERCENT = 0x02;
65
66 const sal_uInt16 BIFF12_FILTERCOLUMN_HIDDENBUTTON = 0x0001;
67 const sal_uInt16 BIFF12_FILTERCOLUMN_SHOWBUTTON = 0x0002;
68
69 const sal_uInt8 BIFF_FILTER_DATATYPE_NONE = 0;
70 const sal_uInt8 BIFF_FILTER_DATATYPE_DOUBLE = 4;
71 const sal_uInt8 BIFF_FILTER_DATATYPE_STRING = 6;
72 const sal_uInt8 BIFF_FILTER_DATATYPE_BOOLEAN = 8;
73 const sal_uInt8 BIFF_FILTER_DATATYPE_EMPTY = 12;
74 const sal_uInt8 BIFF_FILTER_DATATYPE_NOTEMPTY = 14;
75
lclGetApiOperatorFromToken(sal_Int32 & rnApiOperator,sal_Int32 nToken)76 bool lclGetApiOperatorFromToken( sal_Int32& rnApiOperator, sal_Int32 nToken )
77 {
78 switch( nToken )
79 {
80 case XML_lessThan: rnApiOperator = FilterOperator2::LESS; return true;
81 case XML_equal: rnApiOperator = FilterOperator2::EQUAL; return true;
82 case XML_lessThanOrEqual: rnApiOperator = FilterOperator2::LESS_EQUAL; return true;
83 case XML_greaterThan: rnApiOperator = FilterOperator2::GREATER; return true;
84 case XML_notEqual: rnApiOperator = FilterOperator2::NOT_EQUAL; return true;
85 case XML_greaterThanOrEqual: rnApiOperator = FilterOperator2::GREATER_EQUAL; return true;
86 }
87 return false;
88 }
89
90 /** Removes leading asterisk characters from the passed string.
91 @return True = at least one asterisk character has been removed. */
lclTrimLeadingAsterisks(OUString & rValue)92 bool lclTrimLeadingAsterisks( OUString& rValue )
93 {
94 sal_Int32 nLength = rValue.getLength();
95 sal_Int32 nPos = 0;
96 while( (nPos < nLength) && (rValue[ nPos ] == '*') )
97 ++nPos;
98 if( nPos > 0 )
99 {
100 rValue = rValue.copy( nPos );
101 return true;
102 }
103 return false;
104 }
105
106 /** Removes trailing asterisk characters from the passed string.
107 @return True = at least one asterisk character has been removed. */
lclTrimTrailingAsterisks(OUString & rValue)108 bool lclTrimTrailingAsterisks( OUString& rValue )
109 {
110 sal_Int32 nLength = rValue.getLength();
111 sal_Int32 nPos = nLength;
112 while( (nPos > 0) && (rValue[ nPos - 1 ] == '*') )
113 --nPos;
114 if( nPos < nLength )
115 {
116 rValue = rValue.copy( 0, nPos );
117 return true;
118 }
119 return false;
120 }
121
122 /** Converts wildcard characters '*' and '?' to regular expressions and quotes
123 RE meta characters.
124 @return True = passed string has been changed (RE needs to be enabled). */
lclConvertWildcardsToRegExp(OUString & rValue)125 bool lclConvertWildcardsToRegExp( OUString& rValue )
126 {
127 // check existence of the wildcard characters '*' and '?'
128 if( !rValue.isEmpty() && ((rValue.indexOf( '*' ) >= 0) || (rValue.indexOf( '?' ) >= 0)) )
129 {
130 OUStringBuffer aBuffer;
131 aBuffer.ensureCapacity( rValue.getLength() + 5 );
132 const sal_Unicode* pcChar = rValue.getStr();
133 const sal_Unicode* pcEnd = pcChar + rValue.getLength();
134 for( ; pcChar < pcEnd; ++pcChar )
135 {
136 switch( *pcChar )
137 {
138 case '?':
139 aBuffer.append( '.' );
140 break;
141 case '*':
142 aBuffer.append( '.' ).append( '*' );
143 break;
144 case '\\': case '.': case '|': case '(': case ')': case '^': case '$':
145 // quote RE meta characters
146 aBuffer.append( '\\' ).append( *pcChar );
147 break;
148 default:
149 aBuffer.append( *pcChar );
150 }
151 }
152 rValue = aBuffer.makeStringAndClear();
153 return true;
154 }
155 return false;
156 }
157
158 } // namespace
159
ApiFilterSettings()160 ApiFilterSettings::ApiFilterSettings()
161 {
162 }
163
appendField(bool bAnd,sal_Int32 nOperator,double fValue)164 void ApiFilterSettings::appendField( bool bAnd, sal_Int32 nOperator, double fValue )
165 {
166 maFilterFields.emplace_back();
167 TableFilterField3& rFilterField = maFilterFields.back();
168 rFilterField.Connection = bAnd ? FilterConnection_AND : FilterConnection_OR;
169 rFilterField.Operator = nOperator;
170 rFilterField.Values.realloc(1);
171 rFilterField.Values[0].FilterType = FilterFieldType::NUMERIC;
172 rFilterField.Values[0].NumericValue = fValue;
173 }
174
appendField(bool bAnd,sal_Int32 nOperator,const OUString & rValue)175 void ApiFilterSettings::appendField( bool bAnd, sal_Int32 nOperator, const OUString& rValue )
176 {
177 maFilterFields.emplace_back();
178 TableFilterField3& rFilterField = maFilterFields.back();
179 rFilterField.Connection = bAnd ? FilterConnection_AND : FilterConnection_OR;
180 rFilterField.Operator = nOperator;
181 rFilterField.Values.realloc(1);
182 rFilterField.Values[0].FilterType = FilterFieldType::STRING;
183 rFilterField.Values[0].StringValue = rValue;
184 }
185
appendField(bool bAnd,util::Color aColor,bool bIsBackgroundColor)186 void ApiFilterSettings::appendField(bool bAnd, util::Color aColor, bool bIsBackgroundColor)
187 {
188 maFilterFields.emplace_back();
189 TableFilterField3& rFilterField = maFilterFields.back();
190 rFilterField.Connection = bAnd ? FilterConnection_AND : FilterConnection_OR;
191 rFilterField.Operator = FilterOperator2::EQUAL;
192 rFilterField.Values.realloc(1);
193 rFilterField.Values[0].FilterType
194 = bIsBackgroundColor ? FilterFieldType::BACKGROUND_COLOR : FilterFieldType::TEXT_COLOR;
195 rFilterField.Values[0].ColorValue = aColor;
196 }
197
appendField(bool bAnd,const std::vector<std::pair<OUString,bool>> & rValues)198 void ApiFilterSettings::appendField( bool bAnd, const std::vector<std::pair<OUString, bool>>& rValues )
199 {
200 maFilterFields.emplace_back();
201 TableFilterField3& rFilterField = maFilterFields.back();
202 rFilterField.Connection = bAnd ? FilterConnection_AND : FilterConnection_OR;
203 rFilterField.Operator = FilterOperator2::EQUAL;
204 rFilterField.Values.realloc(rValues.size());
205 size_t i = 0;
206
207 for( auto const& it : rValues )
208 {
209 rFilterField.Values[i].StringValue = it.first;
210 rFilterField.Values[i++].FilterType
211 = it.second ? FilterFieldType::DATE : FilterFieldType::STRING;
212 }
213 }
214
FilterSettingsBase(const WorkbookHelper & rHelper)215 FilterSettingsBase::FilterSettingsBase( const WorkbookHelper& rHelper ) :
216 WorkbookHelper( rHelper )
217 {
218 }
219
importAttribs(sal_Int32,const AttributeList &)220 void FilterSettingsBase::importAttribs( sal_Int32 /*nElement*/, const AttributeList& /*rAttribs*/ )
221 {
222 }
223
importRecord(sal_Int32,SequenceInputStream &)224 void FilterSettingsBase::importRecord( sal_Int32 /*nRecId*/, SequenceInputStream& /*rStrm*/ )
225 {
226 }
227
finalizeImport()228 ApiFilterSettings FilterSettingsBase::finalizeImport()
229 {
230 return ApiFilterSettings();
231 }
232
DiscreteFilter(const WorkbookHelper & rHelper)233 DiscreteFilter::DiscreteFilter( const WorkbookHelper& rHelper ) :
234 FilterSettingsBase( rHelper ),
235 mnCalendarType( XML_none ),
236 mbShowBlank( false )
237 {
238 }
239
importAttribs(sal_Int32 nElement,const AttributeList & rAttribs)240 void DiscreteFilter::importAttribs( sal_Int32 nElement, const AttributeList& rAttribs )
241 {
242 switch( nElement )
243 {
244 case XLS_TOKEN( filters ):
245 mnCalendarType = rAttribs.getToken( XML_calendarType, XML_none );
246 mbShowBlank = rAttribs.getBool( XML_blank, false );
247 break;
248
249 case XLS_TOKEN( filter ):
250 {
251 OUString aValue = rAttribs.getXString( XML_val, OUString() );
252 if( !aValue.isEmpty() )
253 maValues.push_back( std::make_pair(aValue, false) );
254 }
255 break;
256
257 case XLS_TOKEN( dateGroupItem ):
258 {
259 OUString aDateValue;
260 // it is just a fallback, we do not need the XML_day as default value,
261 // because if the dateGroupItem exists also XML_dateTimeGrouping exists!
262 sal_uInt16 nToken = rAttribs.getToken(XML_dateTimeGrouping, XML_day);
263 if( nToken == XML_year || nToken == XML_month || nToken == XML_day ||
264 nToken == XML_hour || nToken == XML_minute || nToken == XML_second )
265 {
266 aDateValue = rAttribs.getString(XML_year, OUString());
267
268 if( nToken == XML_month || nToken == XML_day || nToken == XML_hour ||
269 nToken == XML_minute || nToken == XML_second )
270 {
271 OUString aMonthName = rAttribs.getString(XML_month, OUString());
272 if( aMonthName.getLength() == 1 )
273 aMonthName = "0" + aMonthName;
274 aDateValue += "-" + aMonthName;
275
276 if( nToken == XML_day || nToken == XML_hour || nToken == XML_minute ||
277 nToken == XML_second )
278 {
279 OUString aDayName = rAttribs.getString(XML_day, OUString());
280 if( aDayName.getLength() == 1 )
281 aDayName = "0" + aDayName;
282 aDateValue += "-" + aDayName;
283
284 if( nToken == XML_hour || nToken == XML_minute || nToken == XML_second )
285 {
286 OUString aHourName = rAttribs.getString(XML_hour, OUString());
287 if( aHourName.getLength() == 1 )
288 aHourName = "0" + aHourName;
289 aDateValue += " " + aHourName;
290
291 if( nToken == XML_minute || nToken == XML_second )
292 {
293 OUString aMinName = rAttribs.getString(XML_minute, OUString());
294 if( aMinName.getLength() == 1 )
295 aMinName = "0" + aMinName;
296 aDateValue += ":" + aMinName;
297
298 if( nToken == XML_second )
299 {
300 OUString aSecName = rAttribs.getString(XML_second, OUString());
301 if( aSecName.getLength() == 1 )
302 aSecName = "0" + aSecName;
303 aDateValue += ":" + aSecName;
304 }
305 }
306 }
307 }
308 }
309 }
310 if( !aDateValue.isEmpty() )
311 maValues.push_back( std::make_pair(aDateValue, true) );
312 }
313 break;
314 }
315 }
316
importRecord(sal_Int32 nRecId,SequenceInputStream & rStrm)317 void DiscreteFilter::importRecord( sal_Int32 nRecId, SequenceInputStream& rStrm )
318 {
319 switch( nRecId )
320 {
321 case BIFF12_ID_DISCRETEFILTERS:
322 {
323 sal_Int32 nShowBlank, nCalendarType;
324 nShowBlank = rStrm.readInt32();
325 nCalendarType = rStrm.readInt32();
326
327 static const sal_Int32 spnCalendarTypes[] = {
328 XML_none, XML_gregorian, XML_gregorianUs, XML_japan, XML_taiwan, XML_korea, XML_hijri, XML_thai, XML_hebrew,
329 XML_gregorianMeFrench, XML_gregorianArabic, XML_gregorianXlitEnglish, XML_gregorianXlitFrench };
330 mnCalendarType = STATIC_ARRAY_SELECT( spnCalendarTypes, nCalendarType, XML_none );
331 mbShowBlank = nShowBlank != 0;
332 }
333 break;
334
335 case BIFF12_ID_DISCRETEFILTER:
336 {
337 OUString aValue = BiffHelper::readString( rStrm );
338 if( !aValue.isEmpty() )
339 maValues.push_back( std::make_pair(aValue, false) );
340 }
341 break;
342 }
343 }
344
finalizeImport()345 ApiFilterSettings DiscreteFilter::finalizeImport()
346 {
347 ApiFilterSettings aSettings;
348 aSettings.maFilterFields.reserve( maValues.size() );
349
350 // insert all filter values
351 aSettings.appendField( true, maValues );
352
353 // extra field for 'show empty'
354 if( mbShowBlank )
355 aSettings.appendField( false, FilterOperator2::EMPTY, OUString() );
356
357 /* Require disabled regular expressions, filter entries may contain
358 any RE meta characters. */
359 if( !maValues.empty() )
360 aSettings.mobNeedsRegExp = false;
361
362 return aSettings;
363 }
364
Top10Filter(const WorkbookHelper & rHelper)365 Top10Filter::Top10Filter( const WorkbookHelper& rHelper ) :
366 FilterSettingsBase( rHelper ),
367 mfValue( 0.0 ),
368 mbTop( true ),
369 mbPercent( false )
370 {
371 }
372
importAttribs(sal_Int32 nElement,const AttributeList & rAttribs)373 void Top10Filter::importAttribs( sal_Int32 nElement, const AttributeList& rAttribs )
374 {
375 if( nElement == XLS_TOKEN( top10 ) )
376 {
377 mfValue = rAttribs.getDouble( XML_val, 0.0 );
378 mbTop = rAttribs.getBool( XML_top, true );
379 mbPercent = rAttribs.getBool( XML_percent, false );
380 }
381 }
382
importRecord(sal_Int32 nRecId,SequenceInputStream & rStrm)383 void Top10Filter::importRecord( sal_Int32 nRecId, SequenceInputStream& rStrm )
384 {
385 if( nRecId == BIFF12_ID_TOP10FILTER )
386 {
387 sal_uInt8 nFlags;
388 nFlags = rStrm.readuChar();
389 mfValue = rStrm.readDouble();
390 mbTop = getFlag( nFlags, BIFF12_TOP10FILTER_TOP );
391 mbPercent = getFlag( nFlags, BIFF12_TOP10FILTER_PERCENT );
392 }
393 }
394
finalizeImport()395 ApiFilterSettings Top10Filter::finalizeImport()
396 {
397 sal_Int32 nOperator = mbTop ?
398 (mbPercent ? FilterOperator2::TOP_PERCENT : FilterOperator2::TOP_VALUES) :
399 (mbPercent ? FilterOperator2::BOTTOM_PERCENT : FilterOperator2::BOTTOM_VALUES);
400 ApiFilterSettings aSettings;
401 aSettings.appendField( true, nOperator, mfValue );
402 return aSettings;
403 }
404
ColorFilter(const WorkbookHelper & rHelper)405 ColorFilter::ColorFilter(const WorkbookHelper& rHelper)
406 : FilterSettingsBase(rHelper)
407 , mbIsBackgroundColor(false)
408 {
409 }
410
importAttribs(sal_Int32 nElement,const AttributeList & rAttribs)411 void ColorFilter::importAttribs(sal_Int32 nElement, const AttributeList& rAttribs)
412 {
413 if (nElement == XLS_TOKEN(colorFilter))
414 {
415 // When cellColor attribute not found, it means cellColor = true
416 // cellColor = 0 (false) -> TextColor
417 // cellColor = 1 (true) -> BackgroundColor
418 mbIsBackgroundColor = rAttribs.getBool(XML_cellColor, true);
419 msStyleName = getStyles().createDxfStyle( rAttribs.getInteger(XML_dxfId, -1) );
420 }
421 }
422
importRecord(sal_Int32,SequenceInputStream &)423 void ColorFilter::importRecord(sal_Int32 /* nRecId */, SequenceInputStream& /* rStrm */)
424 {
425 // TODO
426 }
427
finalizeImport()428 ApiFilterSettings ColorFilter::finalizeImport()
429 {
430 ApiFilterSettings aSettings;
431 ScDocument& rDoc = getScDocument();
432 ScStyleSheet* pStyleSheet = static_cast<ScStyleSheet*>(
433 rDoc.GetStyleSheetPool()->Find(msStyleName, SfxStyleFamily::Para));
434 if (!pStyleSheet)
435 return aSettings;
436
437 const SfxItemSet& rItemSet = pStyleSheet->GetItemSet();
438 // Color (whether text or background color) is always stored in ATTR_BACKGROUND
439 const SvxBrushItem* pItem = rItemSet.GetItem<SvxBrushItem>(ATTR_BACKGROUND);
440 ::Color aColor = pItem->GetColor();
441 util::Color nColor(aColor);
442 aSettings.appendField(true, nColor, mbIsBackgroundColor);
443 return aSettings;
444 }
445
FilterCriterionModel()446 FilterCriterionModel::FilterCriterionModel() :
447 mnOperator( XML_equal ),
448 mnDataType( BIFF_FILTER_DATATYPE_NONE )
449 {
450 }
451
setBiffOperator(sal_uInt8 nOperator)452 void FilterCriterionModel::setBiffOperator( sal_uInt8 nOperator )
453 {
454 static const sal_Int32 spnOperators[] = { XML_TOKEN_INVALID,
455 XML_lessThan, XML_equal, XML_lessThanOrEqual, XML_greaterThan, XML_notEqual, XML_greaterThanOrEqual };
456 mnOperator = STATIC_ARRAY_SELECT( spnOperators, nOperator, XML_TOKEN_INVALID );
457 }
458
readBiffData(SequenceInputStream & rStrm)459 void FilterCriterionModel::readBiffData( SequenceInputStream& rStrm )
460 {
461 sal_uInt8 nOperator;
462 mnDataType = rStrm.readuChar();
463 nOperator = rStrm.readuChar();
464 setBiffOperator( nOperator );
465
466 switch( mnDataType )
467 {
468 case BIFF_FILTER_DATATYPE_DOUBLE:
469 maValue <<= rStrm.readDouble();
470 break;
471 case BIFF_FILTER_DATATYPE_STRING:
472 {
473 rStrm.skip( 8 );
474 OUString aValue = BiffHelper::readString( rStrm ).trim();
475 if( !aValue.isEmpty() )
476 maValue <<= aValue;
477 }
478 break;
479 case BIFF_FILTER_DATATYPE_BOOLEAN:
480 maValue <<= (rStrm.readuInt8() != 0);
481 rStrm.skip( 7 );
482 break;
483 case BIFF_FILTER_DATATYPE_EMPTY:
484 rStrm.skip( 8 );
485 if( mnOperator == XML_equal )
486 maValue <<= OUString();
487 break;
488 case BIFF_FILTER_DATATYPE_NOTEMPTY:
489 rStrm.skip( 8 );
490 if( mnOperator == XML_notEqual )
491 maValue <<= OUString();
492 break;
493 default:
494 OSL_ENSURE( false, "FilterCriterionModel::readBiffData - unexpected data type" );
495 rStrm.skip( 8 );
496 }
497 }
498
CustomFilter(const WorkbookHelper & rHelper)499 CustomFilter::CustomFilter( const WorkbookHelper& rHelper ) :
500 FilterSettingsBase( rHelper ),
501 mbAnd( false )
502 {
503 }
504
importAttribs(sal_Int32 nElement,const AttributeList & rAttribs)505 void CustomFilter::importAttribs( sal_Int32 nElement, const AttributeList& rAttribs )
506 {
507 switch( nElement )
508 {
509 case XLS_TOKEN( customFilters ):
510 mbAnd = rAttribs.getBool( XML_and, false );
511 break;
512
513 case XLS_TOKEN( customFilter ):
514 {
515 FilterCriterionModel aCriterion;
516 aCriterion.mnOperator = rAttribs.getToken( XML_operator, XML_equal );
517 OUString aValue = rAttribs.getXString( XML_val, OUString() ).trim();
518 if( (aCriterion.mnOperator == XML_equal) || (aCriterion.mnOperator == XML_notEqual) || (!aValue.isEmpty()) )
519 aCriterion.maValue <<= aValue;
520 appendCriterion( aCriterion );
521 }
522 break;
523 }
524 }
525
importRecord(sal_Int32 nRecId,SequenceInputStream & rStrm)526 void CustomFilter::importRecord( sal_Int32 nRecId, SequenceInputStream& rStrm )
527 {
528 switch( nRecId )
529 {
530 case BIFF12_ID_CUSTOMFILTERS:
531 mbAnd = rStrm.readInt32() == 0;
532 break;
533
534 case BIFF12_ID_CUSTOMFILTER:
535 {
536 FilterCriterionModel aCriterion;
537 aCriterion.readBiffData( rStrm );
538 appendCriterion( aCriterion );
539 }
540 break;
541 }
542 }
543
finalizeImport()544 ApiFilterSettings CustomFilter::finalizeImport()
545 {
546 ApiFilterSettings aSettings;
547 OSL_ENSURE( maCriteria.size() <= 2, "CustomFilter::finalizeImport - too many filter criteria" );
548 for( const auto& rCriterion : maCriteria )
549 {
550 // first extract the filter operator
551 sal_Int32 nOperator = 0;
552 bool bValidOperator = lclGetApiOperatorFromToken( nOperator, rCriterion.mnOperator );
553 if( bValidOperator )
554 {
555 if( rCriterion.maValue.has< OUString >() )
556 {
557 // string argument
558 OUString aValue;
559 rCriterion.maValue >>= aValue;
560 // check for 'empty', 'contains', 'begins with', or 'ends with' text filters
561 bool bEqual = nOperator == FilterOperator2::EQUAL;
562 bool bNotEqual = nOperator == FilterOperator2::NOT_EQUAL;
563 if( bEqual || bNotEqual )
564 {
565 if( aValue.isEmpty() )
566 {
567 // empty comparison string: create empty/not empty filters
568 nOperator = bNotEqual ? FilterOperator2::NOT_EMPTY : FilterOperator2::EMPTY;
569 }
570 else
571 {
572 // compare to something: try to find begins/ends/contains
573 bool bHasLeadingAsterisk = lclTrimLeadingAsterisks( aValue );
574 bool bHasTrailingAsterisk = lclTrimTrailingAsterisks( aValue );
575 // just '***' matches everything, do not create a filter field
576 bValidOperator = !aValue.isEmpty();
577 if( bValidOperator )
578 {
579 if( bHasLeadingAsterisk && bHasTrailingAsterisk )
580 nOperator = bNotEqual ? FilterOperator2::DOES_NOT_CONTAIN : FilterOperator2::CONTAINS;
581 else if( bHasLeadingAsterisk )
582 nOperator = bNotEqual ? FilterOperator2::DOES_NOT_END_WITH : FilterOperator2::ENDS_WITH;
583 else if( bHasTrailingAsterisk )
584 nOperator = bNotEqual ? FilterOperator2::DOES_NOT_BEGIN_WITH : FilterOperator2::BEGINS_WITH;
585 // else: no asterisks, stick to equal/not equal
586 }
587 }
588 }
589
590 if( bValidOperator )
591 {
592 // if wildcards are present, require RE mode, otherwise keep don't care state
593 if( lclConvertWildcardsToRegExp( aValue ) )
594 aSettings.mobNeedsRegExp = true;
595 // create a new UNO API filter field
596 aSettings.appendField( mbAnd, nOperator, aValue );
597 }
598 }
599 else if( rCriterion.maValue.has< double >() )
600 {
601 // floating-point argument
602 double fValue = 0.0;
603 rCriterion.maValue >>= fValue;
604 aSettings.appendField( mbAnd, nOperator, fValue );
605 }
606 }
607 }
608 return aSettings;
609 }
610
appendCriterion(const FilterCriterionModel & rCriterion)611 void CustomFilter::appendCriterion( const FilterCriterionModel& rCriterion )
612 {
613 if( (rCriterion.mnOperator != XML_TOKEN_INVALID) && rCriterion.maValue.hasValue() )
614 maCriteria.push_back( rCriterion );
615 }
616
FilterColumn(const WorkbookHelper & rHelper)617 FilterColumn::FilterColumn( const WorkbookHelper& rHelper ) :
618 WorkbookHelper( rHelper ),
619 mnColId( -1 ),
620 mbHiddenButton( false ),
621 mbShowButton( true )
622 {
623 }
624
importFilterColumn(const AttributeList & rAttribs)625 void FilterColumn::importFilterColumn( const AttributeList& rAttribs )
626 {
627 mnColId = rAttribs.getInteger( XML_colId, -1 );
628 mbHiddenButton = rAttribs.getBool( XML_hiddenButton, false );
629 mbShowButton = rAttribs.getBool( XML_showButton, true );
630 }
631
importFilterColumn(SequenceInputStream & rStrm)632 void FilterColumn::importFilterColumn( SequenceInputStream& rStrm )
633 {
634 sal_uInt16 nFlags;
635 mnColId = rStrm.readInt32();
636 nFlags = rStrm.readuInt16();
637 mbHiddenButton = getFlag( nFlags, BIFF12_FILTERCOLUMN_HIDDENBUTTON );
638 mbShowButton = getFlag( nFlags, BIFF12_FILTERCOLUMN_SHOWBUTTON );
639 }
640
finalizeImport()641 ApiFilterSettings FilterColumn::finalizeImport()
642 {
643 ApiFilterSettings aSettings;
644 if( (0 <= mnColId) && mxSettings )
645 {
646 // filter settings object creates a sequence of filter fields
647 aSettings = mxSettings->finalizeImport();
648 // add column index to all filter fields
649 for( auto& rFilterField : aSettings.maFilterFields )
650 rFilterField.Field = mnColId;
651 }
652 return aSettings;
653 }
654
655 // SortCondition
656
SortCondition(const WorkbookHelper & rHelper)657 SortCondition::SortCondition( const WorkbookHelper& rHelper ) :
658 WorkbookHelper( rHelper ),
659 mbDescending( false )
660 {
661 }
662
importSortCondition(const AttributeList & rAttribs,sal_Int16 nSheet)663 void SortCondition::importSortCondition( const AttributeList& rAttribs, sal_Int16 nSheet )
664 {
665 OUString aRangeStr = rAttribs.getString( XML_ref, OUString() );
666 AddressConverter::convertToCellRangeUnchecked( maRange, aRangeStr, nSheet );
667
668 maSortCustomList = rAttribs.getString( XML_customList, OUString() );
669 mbDescending = rAttribs.getBool( XML_descending, false );
670 }
671
672 // AutoFilter
673
AutoFilter(const WorkbookHelper & rHelper)674 AutoFilter::AutoFilter( const WorkbookHelper& rHelper ) :
675 WorkbookHelper( rHelper )
676 {
677 }
678
importAutoFilter(const AttributeList & rAttribs,sal_Int16 nSheet)679 void AutoFilter::importAutoFilter( const AttributeList& rAttribs, sal_Int16 nSheet )
680 {
681 OUString aRangeStr = rAttribs.getString( XML_ref, OUString() );
682 AddressConverter::convertToCellRangeUnchecked( maRange, aRangeStr, nSheet );
683 }
684
importAutoFilter(SequenceInputStream & rStrm,sal_Int16 nSheet)685 void AutoFilter::importAutoFilter( SequenceInputStream& rStrm, sal_Int16 nSheet )
686 {
687 BinRange aBinRange;
688 rStrm >> aBinRange;
689 AddressConverter::convertToCellRangeUnchecked( maRange, aBinRange, nSheet );
690 }
691
importSortState(const AttributeList & rAttribs,sal_Int16 nSheet)692 void AutoFilter::importSortState( const AttributeList& rAttribs, sal_Int16 nSheet )
693 {
694 OUString aRangeStr = rAttribs.getString( XML_ref, OUString() );
695 AddressConverter::convertToCellRangeUnchecked( maSortRange, aRangeStr, nSheet );
696 }
697
createFilterColumn()698 FilterColumn& AutoFilter::createFilterColumn()
699 {
700 FilterColumnVector::value_type xFilterColumn = std::make_shared<FilterColumn>( *this );
701 maFilterColumns.push_back( xFilterColumn );
702 return *xFilterColumn;
703 }
704
createSortCondition()705 SortCondition& AutoFilter::createSortCondition()
706 {
707 SortConditionVector::value_type xSortCondition = std::make_shared<SortCondition>( *this );
708 maSortConditions.push_back( xSortCondition );
709 return *xSortCondition;
710 }
711
finalizeImport(const Reference<XDatabaseRange> & rxDatabaseRange,sal_Int16 nSheet)712 void AutoFilter::finalizeImport( const Reference< XDatabaseRange >& rxDatabaseRange, sal_Int16 nSheet )
713 {
714 // convert filter settings using the filter descriptor of the database range
715 const Reference<XSheetFilterDescriptor3> xFilterDesc( rxDatabaseRange->getFilterDescriptor(), UNO_QUERY_THROW );
716 if( !xFilterDesc.is() )
717 return;
718
719 // set some common properties for the auto filter range
720 PropertySet aDescProps( xFilterDesc );
721 aDescProps.setProperty( PROP_IsCaseSensitive, false );
722 aDescProps.setProperty( PROP_SkipDuplicates, false );
723 aDescProps.setProperty( PROP_Orientation, TableOrientation_ROWS );
724 aDescProps.setProperty( PROP_ContainsHeader, true );
725 aDescProps.setProperty( PROP_CopyOutputData, false );
726
727 // resulting list of all UNO API filter fields
728 ::std::vector<TableFilterField3> aFilterFields;
729
730 // track if columns require to enable or disable regular expressions
731 OptValue< bool > obNeedsRegExp;
732
733 /* Track whether the filter fields of the first filter column are
734 connected with 'or'. In this case, other filter fields cannot be
735 inserted without altering the result of the entire filter, due to
736 Calc's precedence for the 'and' connection operator. Example:
737 Excel's filter conditions 'A1 and (B1 or B2) and C1' where B1 and
738 B2 belong to filter column B, will be evaluated by Calc as
739 '(A1 and B1) or (B2 and C1)'. */
740 bool bHasOrConnection = false;
741
742 // process all filter column objects, exit when 'or' connection exists
743 for( const auto& rxFilterColumn : maFilterColumns )
744 {
745 // the filter settings object creates a list of filter fields
746 ApiFilterSettings aSettings = rxFilterColumn->finalizeImport();
747 ApiFilterSettings::FilterFieldVector& rColumnFields = aSettings.maFilterFields;
748
749 /* Check whether mode for regular expressions is compatible with
750 the global mode in obNeedsRegExp. If either one is still in
751 don't-care state, all is fine. If both are set, they must be
752 equal. */
753 bool bRegExpCompatible = !obNeedsRegExp || !aSettings.mobNeedsRegExp || (obNeedsRegExp.get() == aSettings.mobNeedsRegExp.get());
754
755 // check whether fields are connected by 'or' (see comments above).
756 if( rColumnFields.size() >= 2 )
757 bHasOrConnection = std::any_of(rColumnFields.begin() + 1, rColumnFields.end(),
758 [](const css::sheet::TableFilterField3& rColumnField) { return rColumnField.Connection == FilterConnection_OR; });
759
760 /* Skip the column filter, if no filter fields have been created,
761 and if the mode for regular expressions of the
762 filter column does not fit. */
763 if( !rColumnFields.empty() && bRegExpCompatible )
764 {
765 /* Add 'and' connection to the first filter field to connect
766 it to the existing filter fields of other columns. */
767 rColumnFields[ 0 ].Connection = FilterConnection_AND;
768
769 // insert the new filter fields
770 aFilterFields.insert( aFilterFields.end(), rColumnFields.begin(), rColumnFields.end() );
771
772 // update the regular expressions mode
773 obNeedsRegExp.assignIfUsed( aSettings.mobNeedsRegExp );
774 }
775
776 if( bHasOrConnection )
777 break;
778 }
779
780 // insert all filter fields to the filter descriptor
781 if( !aFilterFields.empty() )
782 xFilterDesc->setFilterFields3( ContainerHelper::vectorToSequence( aFilterFields ) );
783
784 // regular expressions
785 bool bUseRegExp = obNeedsRegExp.get( false );
786 aDescProps.setProperty( PROP_UseRegularExpressions, bUseRegExp );
787
788 // sort
789 if (maSortConditions.empty())
790 return;
791
792 const SortConditionVector::value_type& xSortConditionPointer = *maSortConditions.begin();
793 const SortCondition& rSorConditionLoaded = *xSortConditionPointer;
794
795 ScSortParam aParam;
796 aParam.bUserDef = false;
797 aParam.nUserIndex = 0;
798 aParam.bByRow = false;
799
800 ScUserList* pUserList = ScGlobal::GetUserList();
801 if (!rSorConditionLoaded.maSortCustomList.isEmpty())
802 {
803 for (size_t i=0; pUserList && i < pUserList->size(); i++)
804 {
805 const OUString aEntry((*pUserList)[i].GetString());
806 if (aEntry.equalsIgnoreAsciiCase(rSorConditionLoaded.maSortCustomList))
807 {
808 aParam.bUserDef = true;
809 aParam.nUserIndex = i;
810 break;
811 }
812 }
813 }
814
815 if (!aParam.bUserDef)
816 {
817 pUserList->push_back(new ScUserListData(rSorConditionLoaded.maSortCustomList));
818 aParam.bUserDef = true;
819 aParam.nUserIndex = pUserList->size()-1;
820 }
821
822 // set sort parameter if we have detected it
823 if (aParam.bUserDef)
824 {
825 SCCOLROW nStartPos = aParam.bByRow ? maRange.aStart.Col() : maRange.aStart.Row();
826 if (rSorConditionLoaded.mbDescending)
827 {
828 // descending sort - need to enable 1st SortParam slot
829 assert(aParam.GetSortKeyCount() == DEFSORT);
830
831 aParam.maKeyState[0].bDoSort = true;
832 aParam.maKeyState[0].bAscending = false;
833 aParam.maKeyState[0].nField += nStartPos;
834 }
835
836 ScDocument& rDoc = getScDocument();
837 ScDBData* pDBData = rDoc.GetDBAtArea(
838 nSheet,
839 maRange.aStart.Col(), maRange.aStart.Row(),
840 maRange.aEnd.Col(), maRange.aEnd.Row());
841
842 if (pDBData)
843 pDBData->SetSortParam(aParam);
844 else
845 OSL_FAIL("AutoFilter::finalizeImport(): cannot find matching DBData");
846 }
847 }
848
AutoFilterBuffer(const WorkbookHelper & rHelper)849 AutoFilterBuffer::AutoFilterBuffer( const WorkbookHelper& rHelper ) :
850 WorkbookHelper( rHelper )
851 {
852 }
853
createAutoFilter()854 AutoFilter& AutoFilterBuffer::createAutoFilter()
855 {
856 AutoFilterVector::value_type xAutoFilter = std::make_shared<AutoFilter>( *this );
857 maAutoFilters.push_back( xAutoFilter );
858 return *xAutoFilter;
859 }
860
finalizeImport(sal_Int16 nSheet)861 void AutoFilterBuffer::finalizeImport( sal_Int16 nSheet )
862 {
863 // rely on existence of the defined name '_FilterDatabase' containing the range address of the filtered area
864 const DefinedName* pFilterDBName = getDefinedNames().getByBuiltinId( BIFF_DEFNAME_FILTERDATABASE, nSheet ).get();
865 if(!pFilterDBName)
866 return;
867
868 ScRange aFilterRange;
869 if( !(pFilterDBName->getAbsoluteRange( aFilterRange ) && (aFilterRange.aStart.Tab() == nSheet)) )
870 return;
871
872 // use the same name for the database range as used for the defined name '_FilterDatabase'
873 Reference< XDatabaseRange > xDatabaseRange = createUnnamedDatabaseRangeObject( aFilterRange );
874 // first, try to create an auto filter
875 bool bHasAutoFilter = finalizeImport( xDatabaseRange, nSheet );
876 // no success: try to create an advanced filter
877 if( bHasAutoFilter || !xDatabaseRange.is() )
878 return;
879
880 // the built-in defined name 'Criteria' must exist
881 const DefinedName* pCriteriaName = getDefinedNames().getByBuiltinId( BIFF_DEFNAME_CRITERIA, nSheet ).get();
882 if( !pCriteriaName )
883 return;
884
885 ScRange aCriteriaRange;
886 if( !pCriteriaName->getAbsoluteRange( aCriteriaRange ) )
887 return;
888
889 // set some common properties for the filter descriptor
890 PropertySet aDescProps( xDatabaseRange->getFilterDescriptor() );
891 aDescProps.setProperty( PROP_IsCaseSensitive, false );
892 aDescProps.setProperty( PROP_SkipDuplicates, false );
893 aDescProps.setProperty( PROP_Orientation, TableOrientation_ROWS );
894 aDescProps.setProperty( PROP_ContainsHeader, true );
895 // criteria range may contain wildcards, but these are incompatible with REs
896 aDescProps.setProperty( PROP_UseRegularExpressions, false );
897
898 // position of output data (if built-in defined name 'Extract' exists)
899 DefinedNameRef xExtractName = getDefinedNames().getByBuiltinId( BIFF_DEFNAME_EXTRACT, nSheet );
900 ScRange aOutputRange;
901 bool bHasOutputRange = xExtractName && xExtractName->getAbsoluteRange( aOutputRange );
902 aDescProps.setProperty( PROP_CopyOutputData, bHasOutputRange );
903 if( bHasOutputRange )
904 {
905 aDescProps.setProperty( PROP_SaveOutputPosition, true );
906 aDescProps.setProperty( PROP_OutputPosition, CellAddress( aOutputRange.aStart.Tab(), aOutputRange.aStart.Col(), aOutputRange.aStart.Row() ) );
907 }
908
909 /* Properties of the database range (must be set after
910 modifying properties of the filter descriptor,
911 otherwise the 'FilterCriteriaSource' property gets
912 deleted). */
913 PropertySet aRangeProps( xDatabaseRange );
914 aRangeProps.setProperty( PROP_AutoFilter, false );
915 aRangeProps.setProperty( PROP_FilterCriteriaSource,
916 CellRangeAddress( aCriteriaRange.aStart.Tab(),
917 aCriteriaRange.aStart.Col(), aCriteriaRange.aStart.Row(),
918 aCriteriaRange.aEnd.Col(), aCriteriaRange.aEnd.Row() ));
919 }
920
finalizeImport(const Reference<XDatabaseRange> & rxDatabaseRange,sal_Int16 nSheet)921 bool AutoFilterBuffer::finalizeImport( const Reference< XDatabaseRange >& rxDatabaseRange, sal_Int16 nSheet )
922 {
923 AutoFilter* pAutoFilter = getActiveAutoFilter();
924 if( pAutoFilter && rxDatabaseRange.is() ) try
925 {
926 // the property 'AutoFilter' enables the drop-down buttons
927 PropertySet aRangeProps( rxDatabaseRange );
928 aRangeProps.setProperty( PROP_AutoFilter, true );
929
930 pAutoFilter->finalizeImport( rxDatabaseRange, nSheet );
931
932 // return true to indicate enabled autofilter
933 return true;
934 }
935 catch( Exception& )
936 {
937 }
938 return false;
939 }
940
getActiveAutoFilter()941 AutoFilter* AutoFilterBuffer::getActiveAutoFilter()
942 {
943 // Excel expects not more than one auto filter per sheet or table
944 OSL_ENSURE( maAutoFilters.size() <= 1, "AutoFilterBuffer::getActiveAutoFilter - too many auto filters" );
945 // stick to the last imported auto filter
946 return maAutoFilters.empty() ? nullptr : maAutoFilters.back().get();
947 }
948
949 } // namespace oox
950
951 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
952