LibreOffice Module sc (master) 1
autofilterbuffer.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 <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
33#include <editeng/brushitem.hxx>
34#include <rtl/ustrbuf.hxx>
35#include <osl/diagnose.h>
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
54namespace oox::xls {
55
56using namespace css;
57using namespace ::com::sun::star::sheet;
58using namespace ::com::sun::star::table;
59using namespace ::com::sun::star::uno;
60
61namespace {
62
63const sal_uInt8 BIFF12_TOP10FILTER_TOP = 0x01;
64const sal_uInt8 BIFF12_TOP10FILTER_PERCENT = 0x02;
65
66const sal_uInt16 BIFF12_FILTERCOLUMN_HIDDENBUTTON = 0x0001;
67const sal_uInt16 BIFF12_FILTERCOLUMN_SHOWBUTTON = 0x0002;
68
69const sal_uInt8 BIFF_FILTER_DATATYPE_NONE = 0;
70const sal_uInt8 BIFF_FILTER_DATATYPE_DOUBLE = 4;
71const sal_uInt8 BIFF_FILTER_DATATYPE_STRING = 6;
72const sal_uInt8 BIFF_FILTER_DATATYPE_BOOLEAN = 8;
73const sal_uInt8 BIFF_FILTER_DATATYPE_EMPTY = 12;
74const sal_uInt8 BIFF_FILTER_DATATYPE_NOTEMPTY = 14;
75
76bool 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
92bool 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
108bool 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
125bool 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
161{
162}
163
164void 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 auto pValues = rFilterField.Values.getArray();
172 pValues[0].FilterType = FilterFieldType::NUMERIC;
173 pValues[0].NumericValue = fValue;
174}
175
176void ApiFilterSettings::appendField( bool bAnd, sal_Int32 nOperator, const OUString& rValue )
177{
178 maFilterFields.emplace_back();
179 TableFilterField3& rFilterField = maFilterFields.back();
180 rFilterField.Connection = bAnd ? FilterConnection_AND : FilterConnection_OR;
181 rFilterField.Operator = nOperator;
182 rFilterField.Values.realloc(1);
183 auto pValues = rFilterField.Values.getArray();
184 pValues[0].FilterType = FilterFieldType::STRING;
185 pValues[0].StringValue = rValue;
186}
187
188void ApiFilterSettings::appendField(bool bAnd, util::Color aColor, bool bIsBackgroundColor)
189{
190 maFilterFields.emplace_back();
191 TableFilterField3& rFilterField = maFilterFields.back();
192 rFilterField.Connection = bAnd ? FilterConnection_AND : FilterConnection_OR;
193 rFilterField.Operator = FilterOperator2::EQUAL;
194 rFilterField.Values.realloc(1);
195 auto pValues = rFilterField.Values.getArray();
196 pValues[0].FilterType
197 = bIsBackgroundColor ? FilterFieldType::BACKGROUND_COLOR : FilterFieldType::TEXT_COLOR;
198 pValues[0].ColorValue = aColor;
199}
200
201void ApiFilterSettings::appendField( bool bAnd, const std::vector<std::pair<OUString, bool>>& rValues )
202{
203 maFilterFields.emplace_back();
204 TableFilterField3& rFilterField = maFilterFields.back();
205 rFilterField.Connection = bAnd ? FilterConnection_AND : FilterConnection_OR;
206 rFilterField.Operator = FilterOperator2::EQUAL;
207 rFilterField.Values.realloc(rValues.size());
208 auto pValues = rFilterField.Values.getArray();
209 size_t i = 0;
210
211 for( auto const& it : rValues )
212 {
213 pValues[i].StringValue = it.first;
214 pValues[i++].FilterType
215 = it.second ? FilterFieldType::DATE : FilterFieldType::STRING;
216 }
217}
218
220 WorkbookHelper( rHelper )
221{
222}
223
224void FilterSettingsBase::importAttribs( sal_Int32 /*nElement*/, const AttributeList& /*rAttribs*/ )
225{
226}
227
228void FilterSettingsBase::importRecord( sal_Int32 /*nRecId*/, SequenceInputStream& /*rStrm*/ )
229{
230}
231
233{
234 return ApiFilterSettings();
235}
236
238 FilterSettingsBase( rHelper ),
239 mnCalendarType( XML_none ),
240 mbShowBlank( false )
241{
242}
243
244void DiscreteFilter::importAttribs( sal_Int32 nElement, const AttributeList& rAttribs )
245{
246 switch( nElement )
247 {
248 case XLS_TOKEN( filters ):
249 mnCalendarType = rAttribs.getToken( XML_calendarType, XML_none );
250 mbShowBlank = rAttribs.getBool( XML_blank, false );
251 break;
252
253 case XLS_TOKEN( filter ):
254 {
255 OUString aValue = rAttribs.getXString( XML_val, OUString() );
256 if( !aValue.isEmpty() )
257 maValues.push_back( std::make_pair(aValue, false) );
258 }
259 break;
260
261 case XLS_TOKEN( dateGroupItem ):
262 {
263 OUString aDateValue;
264 // it is just a fallback, we do not need the XML_day as default value,
265 // because if the dateGroupItem exists also XML_dateTimeGrouping exists!
266 sal_uInt16 nToken = rAttribs.getToken(XML_dateTimeGrouping, XML_day);
267 if( nToken == XML_year || nToken == XML_month || nToken == XML_day ||
268 nToken == XML_hour || nToken == XML_minute || nToken == XML_second )
269 {
270 aDateValue = rAttribs.getString(XML_year, OUString());
271
272 if( nToken == XML_month || nToken == XML_day || nToken == XML_hour ||
273 nToken == XML_minute || nToken == XML_second )
274 {
275 OUString aMonthName = rAttribs.getString(XML_month, OUString());
276 if( aMonthName.getLength() == 1 )
277 aMonthName = "0" + aMonthName;
278 aDateValue += "-" + aMonthName;
279
280 if( nToken == XML_day || nToken == XML_hour || nToken == XML_minute ||
281 nToken == XML_second )
282 {
283 OUString aDayName = rAttribs.getString(XML_day, OUString());
284 if( aDayName.getLength() == 1 )
285 aDayName = "0" + aDayName;
286 aDateValue += "-" + aDayName;
287
288 if( nToken == XML_hour || nToken == XML_minute || nToken == XML_second )
289 {
290 OUString aHourName = rAttribs.getString(XML_hour, OUString());
291 if( aHourName.getLength() == 1 )
292 aHourName = "0" + aHourName;
293 aDateValue += " " + aHourName;
294
295 if( nToken == XML_minute || nToken == XML_second )
296 {
297 OUString aMinName = rAttribs.getString(XML_minute, OUString());
298 if( aMinName.getLength() == 1 )
299 aMinName = "0" + aMinName;
300 aDateValue += ":" + aMinName;
301
302 if( nToken == XML_second )
303 {
304 OUString aSecName = rAttribs.getString(XML_second, OUString());
305 if( aSecName.getLength() == 1 )
306 aSecName = "0" + aSecName;
307 aDateValue += ":" + aSecName;
308 }
309 }
310 }
311 }
312 }
313 }
314 if( !aDateValue.isEmpty() )
315 maValues.push_back( std::make_pair(aDateValue, true) );
316 }
317 break;
318 }
319}
320
321void DiscreteFilter::importRecord( sal_Int32 nRecId, SequenceInputStream& rStrm )
322{
323 switch( nRecId )
324 {
326 {
327 sal_Int32 nShowBlank, nCalendarType;
328 nShowBlank = rStrm.readInt32();
329 nCalendarType = rStrm.readInt32();
330
331 static const sal_Int32 spnCalendarTypes[] = {
332 XML_none, XML_gregorian, XML_gregorianUs, XML_japan, XML_taiwan, XML_korea, XML_hijri, XML_thai, XML_hebrew,
333 XML_gregorianMeFrench, XML_gregorianArabic, XML_gregorianXlitEnglish, XML_gregorianXlitFrench };
334 mnCalendarType = STATIC_ARRAY_SELECT( spnCalendarTypes, nCalendarType, XML_none );
335 mbShowBlank = nShowBlank != 0;
336 }
337 break;
338
340 {
341 OUString aValue = BiffHelper::readString( rStrm );
342 if( !aValue.isEmpty() )
343 maValues.push_back( std::make_pair(aValue, false) );
344 }
345 break;
346 }
347}
348
350{
351 ApiFilterSettings aSettings;
352 aSettings.maFilterFields.reserve( maValues.size() );
353
354 // insert all filter values
355 aSettings.appendField( true, maValues );
356
357 // extra field for 'show empty'
358 if( mbShowBlank )
359 aSettings.appendField( false, FilterOperator2::EMPTY, OUString() );
360
361 /* Require disabled regular expressions, filter entries may contain
362 any RE meta characters. */
363 if( !maValues.empty() )
364 aSettings.mobNeedsRegExp = false;
365
366 return aSettings;
367}
368
370 FilterSettingsBase( rHelper ),
371 mfValue( 0.0 ),
372 mbTop( true ),
373 mbPercent( false )
374{
375}
376
377void Top10Filter::importAttribs( sal_Int32 nElement, const AttributeList& rAttribs )
378{
379 if( nElement == XLS_TOKEN( top10 ) )
380 {
381 mfValue = rAttribs.getDouble( XML_val, 0.0 );
382 mbTop = rAttribs.getBool( XML_top, true );
383 mbPercent = rAttribs.getBool( XML_percent, false );
384 }
385}
386
387void Top10Filter::importRecord( sal_Int32 nRecId, SequenceInputStream& rStrm )
388{
389 if( nRecId == BIFF12_ID_TOP10FILTER )
390 {
391 sal_uInt8 nFlags;
392 nFlags = rStrm.readuChar();
393 mfValue = rStrm.readDouble();
394 mbTop = getFlag( nFlags, BIFF12_TOP10FILTER_TOP );
395 mbPercent = getFlag( nFlags, BIFF12_TOP10FILTER_PERCENT );
396 }
397}
398
400{
401 sal_Int32 nOperator = mbTop ?
402 (mbPercent ? FilterOperator2::TOP_PERCENT : FilterOperator2::TOP_VALUES) :
403 (mbPercent ? FilterOperator2::BOTTOM_PERCENT : FilterOperator2::BOTTOM_VALUES);
404 ApiFilterSettings aSettings;
405 aSettings.appendField( true, nOperator, mfValue );
406 return aSettings;
407}
408
410 : FilterSettingsBase(rHelper)
411 , mbIsBackgroundColor(false)
412{
413}
414
415void ColorFilter::importAttribs(sal_Int32 nElement, const AttributeList& rAttribs)
416{
417 if (nElement == XLS_TOKEN(colorFilter))
418 {
419 // When cellColor attribute not found, it means cellColor = true
420 // cellColor = 0 (false) -> TextColor
421 // cellColor = 1 (true) -> BackgroundColor
422 mbIsBackgroundColor = rAttribs.getBool(XML_cellColor, true);
423 msStyleName = getStyles().createDxfStyle( rAttribs.getInteger(XML_dxfId, -1) );
424 }
425}
426
427void ColorFilter::importRecord(sal_Int32 /* nRecId */, SequenceInputStream& /* rStrm */)
428{
429 // TODO
430}
431
433{
434 ApiFilterSettings aSettings;
435 ScDocument& rDoc = getScDocument();
436 ScStyleSheet* pStyleSheet = static_cast<ScStyleSheet*>(
437 rDoc.GetStyleSheetPool()->Find(msStyleName, SfxStyleFamily::Para));
438 if (!pStyleSheet)
439 return aSettings;
440
441 const SfxItemSet& rItemSet = pStyleSheet->GetItemSet();
442 // Color (whether text or background color) is always stored in ATTR_BACKGROUND
443 const SvxBrushItem* pItem = rItemSet.GetItem<SvxBrushItem>(ATTR_BACKGROUND);
444 ::Color aColor = pItem->GetFiltColor();
445 util::Color nColor(aColor);
446 aSettings.appendField(true, nColor, mbIsBackgroundColor);
447 return aSettings;
448}
449
451 mnOperator( XML_equal ),
452 mnDataType( BIFF_FILTER_DATATYPE_NONE )
453{
454}
455
457{
458 static const sal_Int32 spnOperators[] = { XML_TOKEN_INVALID,
459 XML_lessThan, XML_equal, XML_lessThanOrEqual, XML_greaterThan, XML_notEqual, XML_greaterThanOrEqual };
460 mnOperator = STATIC_ARRAY_SELECT( spnOperators, nOperator, XML_TOKEN_INVALID );
461}
462
464{
465 sal_uInt8 nOperator;
466 mnDataType = rStrm.readuChar();
467 nOperator = rStrm.readuChar();
468 setBiffOperator( nOperator );
469
470 switch( mnDataType )
471 {
472 case BIFF_FILTER_DATATYPE_DOUBLE:
473 maValue <<= rStrm.readDouble();
474 break;
475 case BIFF_FILTER_DATATYPE_STRING:
476 {
477 rStrm.skip( 8 );
478 OUString aValue = BiffHelper::readString( rStrm ).trim();
479 if( !aValue.isEmpty() )
480 maValue <<= aValue;
481 }
482 break;
483 case BIFF_FILTER_DATATYPE_BOOLEAN:
484 maValue <<= (rStrm.readuInt8() != 0);
485 rStrm.skip( 7 );
486 break;
487 case BIFF_FILTER_DATATYPE_EMPTY:
488 rStrm.skip( 8 );
489 if( mnOperator == XML_equal )
490 maValue <<= OUString();
491 break;
492 case BIFF_FILTER_DATATYPE_NOTEMPTY:
493 rStrm.skip( 8 );
494 if( mnOperator == XML_notEqual )
495 maValue <<= OUString();
496 break;
497 default:
498 OSL_ENSURE( false, "FilterCriterionModel::readBiffData - unexpected data type" );
499 rStrm.skip( 8 );
500 }
501}
502
504 FilterSettingsBase( rHelper ),
505 mbAnd( false )
506{
507}
508
509void CustomFilter::importAttribs( sal_Int32 nElement, const AttributeList& rAttribs )
510{
511 switch( nElement )
512 {
513 case XLS_TOKEN( customFilters ):
514 mbAnd = rAttribs.getBool( XML_and, false );
515 break;
516
517 case XLS_TOKEN( customFilter ):
518 {
519 FilterCriterionModel aCriterion;
520 aCriterion.mnOperator = rAttribs.getToken( XML_operator, XML_equal );
521 OUString aValue = rAttribs.getXString( XML_val, OUString() ).trim();
522 if( (aCriterion.mnOperator == XML_equal) || (aCriterion.mnOperator == XML_notEqual) || (!aValue.isEmpty()) )
523 aCriterion.maValue <<= aValue;
524 appendCriterion( aCriterion );
525 }
526 break;
527 }
528}
529
530void CustomFilter::importRecord( sal_Int32 nRecId, SequenceInputStream& rStrm )
531{
532 switch( nRecId )
533 {
535 mbAnd = rStrm.readInt32() == 0;
536 break;
537
539 {
540 FilterCriterionModel aCriterion;
541 aCriterion.readBiffData( rStrm );
542 appendCriterion( aCriterion );
543 }
544 break;
545 }
546}
547
549{
550 ApiFilterSettings aSettings;
551 OSL_ENSURE( maCriteria.size() <= 2, "CustomFilter::finalizeImport - too many filter criteria" );
552 for( const auto& rCriterion : maCriteria )
553 {
554 // first extract the filter operator
555 sal_Int32 nOperator = 0;
556 bool bValidOperator = lclGetApiOperatorFromToken( nOperator, rCriterion.mnOperator );
557 if( bValidOperator )
558 {
559 if( rCriterion.maValue.has< OUString >() )
560 {
561 // string argument
562 OUString aValue;
563 rCriterion.maValue >>= aValue;
564 // check for 'empty', 'contains', 'begins with', or 'ends with' text filters
565 bool bEqual = nOperator == FilterOperator2::EQUAL;
566 bool bNotEqual = nOperator == FilterOperator2::NOT_EQUAL;
567 if( bEqual || bNotEqual )
568 {
569 if( aValue.isEmpty() )
570 {
571 // empty comparison string: create empty/not empty filters
572 nOperator = bNotEqual ? FilterOperator2::NOT_EMPTY : FilterOperator2::EMPTY;
573 }
574 else
575 {
576 // compare to something: try to find begins/ends/contains
577 bool bHasLeadingAsterisk = lclTrimLeadingAsterisks( aValue );
578 bool bHasTrailingAsterisk = lclTrimTrailingAsterisks( aValue );
579 // just '***' matches everything, do not create a filter field
580 bValidOperator = !aValue.isEmpty();
581 if( bValidOperator )
582 {
583 if( bHasLeadingAsterisk && bHasTrailingAsterisk )
584 nOperator = bNotEqual ? FilterOperator2::DOES_NOT_CONTAIN : FilterOperator2::CONTAINS;
585 else if( bHasLeadingAsterisk )
586 nOperator = bNotEqual ? FilterOperator2::DOES_NOT_END_WITH : FilterOperator2::ENDS_WITH;
587 else if( bHasTrailingAsterisk )
588 nOperator = bNotEqual ? FilterOperator2::DOES_NOT_BEGIN_WITH : FilterOperator2::BEGINS_WITH;
589 // else: no asterisks, stick to equal/not equal
590 }
591 }
592 }
593
594 if( bValidOperator )
595 {
596 // if wildcards are present, require RE mode, otherwise keep don't care state
597 if( lclConvertWildcardsToRegExp( aValue ) )
598 aSettings.mobNeedsRegExp = true;
599 // create a new UNO API filter field
600 aSettings.appendField( mbAnd, nOperator, aValue );
601 }
602 }
603 else if( rCriterion.maValue.has< double >() )
604 {
605 // floating-point argument
606 double fValue = 0.0;
607 rCriterion.maValue >>= fValue;
608 aSettings.appendField( mbAnd, nOperator, fValue );
609 }
610 }
611 }
612 return aSettings;
613}
614
616{
617 if( (rCriterion.mnOperator != XML_TOKEN_INVALID) && rCriterion.maValue.hasValue() )
618 maCriteria.push_back( rCriterion );
619}
620
622 WorkbookHelper( rHelper ),
623 mnColId( -1 ),
624 mbHiddenButton( false ),
625 mbShowButton( true )
626{
627}
628
630{
631 mnColId = rAttribs.getInteger( XML_colId, -1 );
632 mbHiddenButton = rAttribs.getBool( XML_hiddenButton, false );
633 mbShowButton = rAttribs.getBool( XML_showButton, true );
634}
635
637{
638 sal_uInt16 nFlags;
639 mnColId = rStrm.readInt32();
640 nFlags = rStrm.readuInt16();
641 mbHiddenButton = getFlag( nFlags, BIFF12_FILTERCOLUMN_HIDDENBUTTON );
642 mbShowButton = getFlag( nFlags, BIFF12_FILTERCOLUMN_SHOWBUTTON );
643}
644
646{
647 ApiFilterSettings aSettings;
648 if( (0 <= mnColId) && mxSettings )
649 {
650 // filter settings object creates a sequence of filter fields
651 aSettings = mxSettings->finalizeImport();
652 // add column index to all filter fields
653 for( auto& rFilterField : aSettings.maFilterFields )
654 rFilterField.Field = mnColId;
655 }
656 return aSettings;
657}
658
659// SortCondition
660
662 WorkbookHelper( rHelper ),
663 mbDescending( false )
664{
665}
666
667void SortCondition::importSortCondition( const AttributeList& rAttribs, sal_Int16 nSheet )
668{
669 OUString aRangeStr = rAttribs.getString( XML_ref, OUString() );
671
672 maSortCustomList = rAttribs.getString( XML_customList, OUString() );
673 mbDescending = rAttribs.getBool( XML_descending, false );
674}
675
676// AutoFilter
677
679 WorkbookHelper( rHelper )
680{
681}
682
683void AutoFilter::importAutoFilter( const AttributeList& rAttribs, sal_Int16 nSheet )
684{
685 OUString aRangeStr = rAttribs.getString( XML_ref, OUString() );
687}
688
689void AutoFilter::importAutoFilter( SequenceInputStream& rStrm, sal_Int16 nSheet )
690{
691 BinRange aBinRange;
692 rStrm >> aBinRange;
694}
695
696void AutoFilter::importSortState( const AttributeList& rAttribs, sal_Int16 nSheet )
697{
698 OUString aRangeStr = rAttribs.getString( XML_ref, OUString() );
700}
701
703{
704 FilterColumnVector::value_type xFilterColumn = std::make_shared<FilterColumn>( *this );
705 maFilterColumns.push_back( xFilterColumn );
706 return *xFilterColumn;
707}
708
710{
711 SortConditionVector::value_type xSortCondition = std::make_shared<SortCondition>( *this );
712 maSortConditions.push_back( xSortCondition );
713 return *xSortCondition;
714}
715
716void AutoFilter::finalizeImport( const Reference< XDatabaseRange >& rxDatabaseRange, sal_Int16 nSheet )
717{
718 // convert filter settings using the filter descriptor of the database range
719 const Reference<XSheetFilterDescriptor3> xFilterDesc( rxDatabaseRange->getFilterDescriptor(), UNO_QUERY_THROW );
720 if( !xFilterDesc.is() )
721 return;
722
723 // set some common properties for the auto filter range
724 PropertySet aDescProps( xFilterDesc );
725 aDescProps.setProperty( PROP_IsCaseSensitive, false );
726 aDescProps.setProperty( PROP_SkipDuplicates, false );
727 aDescProps.setProperty( PROP_Orientation, TableOrientation_ROWS );
728 aDescProps.setProperty( PROP_ContainsHeader, true );
729 aDescProps.setProperty( PROP_CopyOutputData, false );
730
731 // resulting list of all UNO API filter fields
732 ::std::vector<TableFilterField3> aFilterFields;
733
734 // track if columns require to enable or disable regular expressions
735 std::optional< bool > obNeedsRegExp;
736
737 /* Track whether the filter fields of the first filter column are
738 connected with 'or'. In this case, other filter fields cannot be
739 inserted without altering the result of the entire filter, due to
740 Calc's precedence for the 'and' connection operator. Example:
741 Excel's filter conditions 'A1 and (B1 or B2) and C1' where B1 and
742 B2 belong to filter column B, will be evaluated by Calc as
743 '(A1 and B1) or (B2 and C1)'. */
744 bool bHasOrConnection = false;
745
746 // process all filter column objects, exit when 'or' connection exists
747 for( const auto& rxFilterColumn : maFilterColumns )
748 {
749 // the filter settings object creates a list of filter fields
750 ApiFilterSettings aSettings = rxFilterColumn->finalizeImport();
751 ApiFilterSettings::FilterFieldVector& rColumnFields = aSettings.maFilterFields;
752
753 /* Check whether mode for regular expressions is compatible with
754 the global mode in obNeedsRegExp. If either one is still in
755 don't-care state, all is fine. If both are set, they must be
756 equal. */
757 bool bRegExpCompatible = !obNeedsRegExp || !aSettings.mobNeedsRegExp || (obNeedsRegExp.value() == aSettings.mobNeedsRegExp.value());
758
759 // check whether fields are connected by 'or' (see comments above).
760 if( rColumnFields.size() >= 2 )
761 bHasOrConnection = std::any_of(rColumnFields.begin() + 1, rColumnFields.end(),
762 [](const css::sheet::TableFilterField3& rColumnField) { return rColumnField.Connection == FilterConnection_OR; });
763
764 /* Skip the column filter, if no filter fields have been created,
765 and if the mode for regular expressions of the
766 filter column does not fit. */
767 if( !rColumnFields.empty() && bRegExpCompatible )
768 {
769 /* Add 'and' connection to the first filter field to connect
770 it to the existing filter fields of other columns. */
771 rColumnFields[ 0 ].Connection = FilterConnection_AND;
772
773 // insert the new filter fields
774 aFilterFields.insert( aFilterFields.end(), rColumnFields.begin(), rColumnFields.end() );
775
776 // update the regular expressions mode
777 assignIfUsed( obNeedsRegExp, aSettings.mobNeedsRegExp );
778 }
779
780 if( bHasOrConnection )
781 break;
782 }
783
784 // insert all filter fields to the filter descriptor
785 if( !aFilterFields.empty() )
786 xFilterDesc->setFilterFields3( comphelper::containerToSequence( aFilterFields ) );
787
788 // regular expressions
789 bool bUseRegExp = obNeedsRegExp.value_or( false );
790 aDescProps.setProperty( PROP_UseRegularExpressions, bUseRegExp );
791
792 // sort
793 if (maSortConditions.empty())
794 return;
795
796 const SortConditionVector::value_type& xSortConditionPointer = *maSortConditions.begin();
797 const SortCondition& rSorConditionLoaded = *xSortConditionPointer;
798
799 ScSortParam aParam;
800 aParam.bUserDef = false;
801 aParam.nUserIndex = 0;
802 aParam.bByRow = false;
803
804 ScUserList* pUserList = ScGlobal::GetUserList();
805 if (!rSorConditionLoaded.maSortCustomList.isEmpty())
806 {
807 for (size_t i=0; pUserList && i < pUserList->size(); i++)
808 {
809 const OUString aEntry((*pUserList)[i].GetString());
810 if (aEntry.equalsIgnoreAsciiCase(rSorConditionLoaded.maSortCustomList))
811 {
812 aParam.bUserDef = true;
813 aParam.nUserIndex = i;
814 break;
815 }
816 }
817 }
818
819 if (!aParam.bUserDef)
820 {
821 pUserList->push_back(new ScUserListData(rSorConditionLoaded.maSortCustomList));
822 aParam.bUserDef = true;
823 aParam.nUserIndex = pUserList->size()-1;
824 }
825
826 // set sort parameter if we have detected it
827 if (!aParam.bUserDef)
828 return;
829
830 SCCOLROW nStartPos = aParam.bByRow ? maRange.aStart.Col() : maRange.aStart.Row();
831 if (rSorConditionLoaded.mbDescending)
832 {
833 // descending sort - need to enable 1st SortParam slot
834 assert(aParam.GetSortKeyCount() == DEFSORT);
835
836 aParam.maKeyState[0].bDoSort = true;
837 aParam.maKeyState[0].bAscending = false;
838 aParam.maKeyState[0].nField += nStartPos;
839 }
840
841 ScDocument& rDoc = getScDocument();
842 ScDBData* pDBData = rDoc.GetDBAtArea(
843 nSheet,
846
847 if (pDBData)
848 pDBData->SetSortParam(aParam);
849 else
850 OSL_FAIL("AutoFilter::finalizeImport(): cannot find matching DBData");
851}
852
854 WorkbookHelper( rHelper )
855{
856}
857
859{
860 AutoFilterVector::value_type xAutoFilter = std::make_shared<AutoFilter>( *this );
861 maAutoFilters.push_back( xAutoFilter );
862 return *xAutoFilter;
863}
864
865void AutoFilterBuffer::finalizeImport( sal_Int16 nSheet )
866{
867 // rely on existence of the defined name '_FilterDatabase' containing the range address of the filtered area
868 const DefinedName* pFilterDBName = getDefinedNames().getByBuiltinId( BIFF_DEFNAME_FILTERDATABASE, nSheet ).get();
869 if(!pFilterDBName)
870 return;
871
872 ScRange aFilterRange;
873 if( !(pFilterDBName->getAbsoluteRange( aFilterRange ) && (aFilterRange.aStart.Tab() == nSheet)) )
874 return;
875
876 // use the same name for the database range as used for the defined name '_FilterDatabase'
877 Reference< XDatabaseRange > xDatabaseRange = createUnnamedDatabaseRangeObject( aFilterRange );
878 // first, try to create an auto filter
879 bool bHasAutoFilter = finalizeImport( xDatabaseRange, nSheet );
880 // no success: try to create an advanced filter
881 if( bHasAutoFilter || !xDatabaseRange.is() )
882 return;
883
884 // the built-in defined name 'Criteria' must exist
885 const DefinedName* pCriteriaName = getDefinedNames().getByBuiltinId( BIFF_DEFNAME_CRITERIA, nSheet ).get();
886 if( !pCriteriaName )
887 return;
888
889 ScRange aCriteriaRange;
890 if( !pCriteriaName->getAbsoluteRange( aCriteriaRange ) )
891 return;
892
893 // set some common properties for the filter descriptor
894 PropertySet aDescProps( xDatabaseRange->getFilterDescriptor() );
895 aDescProps.setProperty( PROP_IsCaseSensitive, false );
896 aDescProps.setProperty( PROP_SkipDuplicates, false );
897 aDescProps.setProperty( PROP_Orientation, TableOrientation_ROWS );
898 aDescProps.setProperty( PROP_ContainsHeader, true );
899 // criteria range may contain wildcards, but these are incompatible with REs
900 aDescProps.setProperty( PROP_UseRegularExpressions, false );
901
902 // position of output data (if built-in defined name 'Extract' exists)
904 ScRange aOutputRange;
905 bool bHasOutputRange = xExtractName && xExtractName->getAbsoluteRange( aOutputRange );
906 aDescProps.setProperty( PROP_CopyOutputData, bHasOutputRange );
907 if( bHasOutputRange )
908 {
909 aDescProps.setProperty( PROP_SaveOutputPosition, true );
910 aDescProps.setProperty( PROP_OutputPosition, CellAddress( aOutputRange.aStart.Tab(), aOutputRange.aStart.Col(), aOutputRange.aStart.Row() ) );
911 }
912
913 /* Properties of the database range (must be set after
914 modifying properties of the filter descriptor,
915 otherwise the 'FilterCriteriaSource' property gets
916 deleted). */
917 PropertySet aRangeProps( xDatabaseRange );
918 aRangeProps.setProperty( PROP_AutoFilter, false );
919 aRangeProps.setProperty( PROP_FilterCriteriaSource,
920 CellRangeAddress( aCriteriaRange.aStart.Tab(),
921 aCriteriaRange.aStart.Col(), aCriteriaRange.aStart.Row(),
922 aCriteriaRange.aEnd.Col(), aCriteriaRange.aEnd.Row() ));
923}
924
925bool AutoFilterBuffer::finalizeImport( const Reference< XDatabaseRange >& rxDatabaseRange, sal_Int16 nSheet )
926{
927 AutoFilter* pAutoFilter = getActiveAutoFilter();
928 if( pAutoFilter && rxDatabaseRange.is() ) try
929 {
930 // the property 'AutoFilter' enables the drop-down buttons
931 PropertySet aRangeProps( rxDatabaseRange );
932 aRangeProps.setProperty( PROP_AutoFilter, true );
933
934 pAutoFilter->finalizeImport( rxDatabaseRange, nSheet );
935
936 // return true to indicate enabled autofilter
937 return true;
938 }
939 catch( Exception& )
940 {
941 }
942 return false;
943}
944
946{
947 // Excel expects not more than one auto filter per sheet or table
948 OSL_ENSURE( maAutoFilters.size() <= 1, "AutoFilterBuffer::getActiveAutoFilter - too many auto filters" );
949 // stick to the last imported auto filter
950 return maAutoFilters.empty() ? nullptr : maAutoFilters.back().get();
951}
952
953} // namespace oox
954
955/* vim:set shiftwidth=4 softtabstop=4 expandtab: */
const PropertyValue * pValues
SCTAB Tab() const
Definition: address.hxx:283
SCROW Row() const
Definition: address.hxx:274
SCCOL Col() const
Definition: address.hxx:279
SC_DLLPUBLIC void SetSortParam(const ScSortParam &rSortParam)
Definition: dbdata.cxx:413
SC_DLLPUBLIC const ScDBData * GetDBAtArea(SCTAB nTab, SCCOL nCol1, SCROW nRow1, SCCOL nCol2, SCROW nRow2) const
Definition: documen3.cxx:344
SC_DLLPUBLIC ScStyleSheetPool * GetStyleSheetPool() const
Definition: document.cxx:6175
static SC_DLLPUBLIC ScUserList * GetUserList()
Definition: global.cxx:288
ScAddress aEnd
Definition: address.hxx:498
ScAddress aStart
Definition: address.hxx:497
virtual SC_DLLPUBLIC SfxItemSet & GetItemSet() override
Definition: stlsheet.cxx:128
Stores individual user-defined sort list.
Definition: userlist.hxx:33
Collection of user-defined sort lists.
Definition: userlist.hxx:67
void push_back(ScUserListData *p)
Definition: userlist.cxx:351
size_t size() const
Definition: userlist.cxx:346
const SfxPoolItem * GetItem(sal_uInt16 nWhich, bool bSearchInParent=true) const
virtual SfxStyleSheetBase * Find(const OUString &, SfxStyleFamily eFam, SfxStyleSearchBits n=SfxStyleSearchBits::All) const
const Color & GetFiltColor() const
std::optional< OUString > getXString(sal_Int32 nAttrToken) const
std::optional< sal_Int32 > getInteger(sal_Int32 nAttrToken) const
std::optional< OUString > getString(sal_Int32 nAttrToken) const
std::optional< bool > getBool(sal_Int32 nAttrToken) const
std::optional< sal_Int32 > getToken(sal_Int32 nAttrToken) const
std::optional< double > getDouble(sal_Int32 nAttrToken) const
bool setProperty(sal_Int32 nPropId, const Type &rValue)
value_type get(sal_Int32 nIndex) const
container_type::value_type value_type
static bool convertToCellRangeUnchecked(ScRange &orRange, std::u16string_view aString, sal_Int16 nSheet)
Converts the passed string to a cell range address, without checking any sheet limits.
AutoFilter * getActiveAutoFilter()
Returns the auto filter object used to perform auto filtering.
void finalizeImport(sal_Int16 nSheet)
Applies filter settings to a new database range object (used for sheet autofilter or advanced filter ...
AutoFilter & createAutoFilter()
Creates a new auto filter and stores it internally.
AutoFilterBuffer(const WorkbookHelper &rHelper)
AutoFilter(const WorkbookHelper &rHelper)
SortCondition & createSortCondition()
void importSortState(const AttributeList &rAttribs, sal_Int16 nSheet)
void finalizeImport(const css::uno::Reference< css::sheet::XDatabaseRange > &rxDatabaseRange, sal_Int16 nSheet)
Applies the filter to the passed filter descriptor.
void importAutoFilter(const AttributeList &rAttribs, sal_Int16 nSheet)
Imports auto filter settings from the autoFilter element.
FilterColumnVector maFilterColumns
FilterColumn & createFilterColumn()
Creates a new auto filter column and stores it internally.
SortConditionVector maSortConditions
static OUString readString(SequenceInputStream &rStrm, bool b32BitLen=true)
Reads a BIFF12 string with leading 16-bit or 32-bit length field.
Definition: biffhelper.cxx:79
OUString msStyleName
Style name to retrieve the color from.
virtual void importRecord(sal_Int32 nRecId, SequenceInputStream &rStrm) override
Imports filter settings from the FILTERS and FILTER records.
ColorFilter(const WorkbookHelper &rHelper)
virtual void importAttribs(sal_Int32 nElement, const AttributeList &rAttribs) override
Imports filter settings from the filters and filter elements.
virtual ApiFilterSettings finalizeImport() override
Returns converted UNO API filter settings representing all filter settings.
bool mbIsBackgroundColor
Whether we are dealing with the background color (vs. text color)
virtual void importAttribs(sal_Int32 nElement, const AttributeList &rAttribs) override
Imports filter settings from the filters and filter elements.
void appendCriterion(const FilterCriterionModel &rCriterion)
Appends the passed filter criterion, if it contains valid settings.
FilterCriterionVector maCriteria
CustomFilter(const WorkbookHelper &rHelper)
virtual ApiFilterSettings finalizeImport() override
Returns converted UNO API filter settings representing all filter settings.
virtual void importRecord(sal_Int32 nRecId, SequenceInputStream &rStrm) override
Imports filter settings from the FILTERS and FILTER records.
bool getAbsoluteRange(ScRange &orRange) const
Tries to resolve the defined name to an absolute cell range.
DefinedNameRef getByBuiltinId(sal_Unicode cBuiltinId, sal_Int16 nCalcSheet) const
Returns a built-in defined name by its built-in identifier.
DiscreteFilter(const WorkbookHelper &rHelper)
virtual void importRecord(sal_Int32 nRecId, SequenceInputStream &rStrm) override
Imports filter settings from the FILTERS and FILTER records.
std::vector< std::pair< OUString, bool > > maValues
virtual ApiFilterSettings finalizeImport() override
Returns converted UNO API filter settings representing all filter settings.
virtual void importAttribs(sal_Int32 nElement, const AttributeList &rAttribs) override
Imports filter settings from the filters and filter elements.
A column in a filtered range.
FilterColumn(const WorkbookHelper &rHelper)
void importFilterColumn(const AttributeList &rAttribs)
Imports auto filter column settings from the filterColumn element.
ApiFilterSettings finalizeImport()
Returns converted UNO API filter settings representing all filter settings of this column.
std::shared_ptr< FilterSettingsBase > mxSettings
Base class for specific filter settings for a column in a filtered range.
virtual void importAttribs(sal_Int32 nElement, const AttributeList &rAttribs)
Derived classes import filter settings from the passed attribute list.
virtual void importRecord(sal_Int32 nRecId, SequenceInputStream &rStrm)
Derived classes import filter settings from the passed record.
FilterSettingsBase(const WorkbookHelper &rHelper)
virtual ApiFilterSettings finalizeImport()
Derived classes return converted UNO API filter settings representing all filter settings.
SortCondition(const WorkbookHelper &rHelper)
void importSortCondition(const AttributeList &rAttribs, sal_Int16 nSheet)
OUString createDxfStyle(sal_Int32 nDxfId) const
Creates the style sheet described by the DXF with the passed identifier.
Top10Filter(const WorkbookHelper &rHelper)
bool mbTop
Number of items or percentage.
virtual void importAttribs(sal_Int32 nElement, const AttributeList &rAttribs) override
Imports filter settings from the filters and filter elements.
virtual void importRecord(sal_Int32 nRecId, SequenceInputStream &rStrm) override
Imports filter settings from the FILTERS and FILTER records.
virtual ApiFilterSettings finalizeImport() override
Returns converted UNO API filter settings representing all filter settings.
bool mbPercent
True = show top (greatest) items/percentage.
Helper class to provide access to global workbook data.
StylesBuffer & getStyles() const
Returns all cell formatting objects read from the styles substream.
DefinedNamesBuffer & getDefinedNames() const
Returns the defined names read from the workbook globals.
css::uno::Reference< css::sheet::XDatabaseRange > createUnnamedDatabaseRangeObject(const ScRange &rRangeAddr) const
Creates and returns an unnamed database range on-the-fly in the Calc document.
#define STATIC_ARRAY_SELECT(array, index, def)
sal_uInt16 nPos
OUString GetString(int nId)
css::uno::Sequence< DstElementType > containerToSequence(const SrcType &i_Container)
int i
void SvStream & rStrm
const sal_Unicode BIFF_DEFNAME_CRITERIA
const sal_Int32 BIFF12_ID_DISCRETEFILTER
Definition: biffhelper.hxx:95
const sal_Int32 BIFF12_ID_CUSTOMFILTER
Definition: biffhelper.hxx:79
const sal_Int32 BIFF12_ID_CUSTOMFILTERS
Definition: biffhelper.hxx:80
std::shared_ptr< DefinedName > DefinedNameRef
const sal_Int32 BIFF12_ID_DISCRETEFILTERS
Definition: biffhelper.hxx:96
const sal_Int32 BIFF12_ID_TOP10FILTER
Definition: biffhelper.hxx:230
const sal_Unicode BIFF_DEFNAME_EXTRACT
const sal_Unicode BIFF_DEFNAME_FILTERDATABASE
void assignIfUsed(std::optional< Type > &rDestValue, const std::optional< Type > &rSourceValue)
bool getFlag(Type nBitField, Type nMask)
XML_none
XML_TOKEN_INVALID
DefTokenId nToken
Definition: qproform.cxx:397
constexpr TypedWhichId< SvxBrushItem > ATTR_BACKGROUND(148)
#define DEFSORT
Definition: sortparam.hxx:22
::std::vector< ScSortKeyState > maKeyState
Definition: sortparam.hxx:121
sal_uInt16 nUserIndex
Definition: sortparam.hxx:110
sal_uInt16 GetSortKeyCount() const
Definition: sortparam.hxx:139
Contains UNO API filter settings for a column in a filtered range.
::std::vector< css::sheet::TableFilterField3 > FilterFieldVector
FilterFieldVector maFilterFields
std::optional< bool > mobNeedsRegExp
List of UNO API filter settings.
void appendField(bool bAnd, sal_Int32 nOperator, double fValue)
ApiFilterSettings()
If set, requires regular expressions to be enabled/disabled.
A 2D cell range address struct for binary filters.
A filter criterion for a custom filter.
void readBiffData(SequenceInputStream &rStrm)
Imports the criterion model from the passed BIFF12 stream.
sal_Int32 mnOperator
Comparison operand.
void setBiffOperator(sal_uInt8 nOperator)
Sets the passed BIFF operator constant.
FilterCriterionModel()
Operand data type (BIFF only).
sal_uInt8 mnDataType
Comparison operator.
unsigned char sal_uInt8
sal_uInt16 sal_Unicode
sal_Int32 SCCOLROW
a type capable of holding either SCCOL or SCROW
Definition: types.hxx:23
std::unique_ptr< char[]> aBuffer
sal_Int32 nLength