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( ".*" );
143 break;
144 case '\\': case '.': case '|': case '(': case ')': case '^': case '$':
145 // quote RE meta characters
146 aBuffer.append( "\\" + OUStringChar(*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 if (const SvxBrushItem* pItem = rItemSet.GetItem<SvxBrushItem>(ATTR_BACKGROUND))
444 {
445 ::Color aColor = pItem->GetFiltColor();
446 util::Color nColor(aColor);
447 aSettings.appendField(true, nColor, mbIsBackgroundColor);
448 }
449 return aSettings;
450}
451
453 mnOperator( XML_equal ),
454 mnDataType( BIFF_FILTER_DATATYPE_NONE )
455{
456}
457
459{
460 static const sal_Int32 spnOperators[] = { XML_TOKEN_INVALID,
461 XML_lessThan, XML_equal, XML_lessThanOrEqual, XML_greaterThan, XML_notEqual, XML_greaterThanOrEqual };
462 mnOperator = STATIC_ARRAY_SELECT( spnOperators, nOperator, XML_TOKEN_INVALID );
463}
464
466{
467 sal_uInt8 nOperator;
468 mnDataType = rStrm.readuChar();
469 nOperator = rStrm.readuChar();
470 setBiffOperator( nOperator );
471
472 switch( mnDataType )
473 {
474 case BIFF_FILTER_DATATYPE_DOUBLE:
475 maValue <<= rStrm.readDouble();
476 break;
477 case BIFF_FILTER_DATATYPE_STRING:
478 {
479 rStrm.skip( 8 );
480 OUString aValue = BiffHelper::readString( rStrm ).trim();
481 if( !aValue.isEmpty() )
482 maValue <<= aValue;
483 }
484 break;
485 case BIFF_FILTER_DATATYPE_BOOLEAN:
486 maValue <<= (rStrm.readuInt8() != 0);
487 rStrm.skip( 7 );
488 break;
489 case BIFF_FILTER_DATATYPE_EMPTY:
490 rStrm.skip( 8 );
491 if( mnOperator == XML_equal )
492 maValue <<= OUString();
493 break;
494 case BIFF_FILTER_DATATYPE_NOTEMPTY:
495 rStrm.skip( 8 );
496 if( mnOperator == XML_notEqual )
497 maValue <<= OUString();
498 break;
499 default:
500 OSL_ENSURE( false, "FilterCriterionModel::readBiffData - unexpected data type" );
501 rStrm.skip( 8 );
502 }
503}
504
506 FilterSettingsBase( rHelper ),
507 mbAnd( false )
508{
509}
510
511void CustomFilter::importAttribs( sal_Int32 nElement, const AttributeList& rAttribs )
512{
513 switch( nElement )
514 {
515 case XLS_TOKEN( customFilters ):
516 mbAnd = rAttribs.getBool( XML_and, false );
517 break;
518
519 case XLS_TOKEN( customFilter ):
520 {
521 FilterCriterionModel aCriterion;
522 aCriterion.mnOperator = rAttribs.getToken( XML_operator, XML_equal );
523 OUString aValue = rAttribs.getXString( XML_val, OUString() ).trim();
524 if( (aCriterion.mnOperator == XML_equal) || (aCriterion.mnOperator == XML_notEqual) || (!aValue.isEmpty()) )
525 aCriterion.maValue <<= aValue;
526 appendCriterion( aCriterion );
527 }
528 break;
529 }
530}
531
532void CustomFilter::importRecord( sal_Int32 nRecId, SequenceInputStream& rStrm )
533{
534 switch( nRecId )
535 {
537 mbAnd = rStrm.readInt32() == 0;
538 break;
539
541 {
542 FilterCriterionModel aCriterion;
543 aCriterion.readBiffData( rStrm );
544 appendCriterion( aCriterion );
545 }
546 break;
547 }
548}
549
551{
552 ApiFilterSettings aSettings;
553 OSL_ENSURE( maCriteria.size() <= 2, "CustomFilter::finalizeImport - too many filter criteria" );
554 for( const auto& rCriterion : maCriteria )
555 {
556 // first extract the filter operator
557 sal_Int32 nOperator = 0;
558 bool bValidOperator = lclGetApiOperatorFromToken( nOperator, rCriterion.mnOperator );
559 if( bValidOperator )
560 {
561 if( rCriterion.maValue.has< OUString >() )
562 {
563 // string argument
564 OUString aValue;
565 rCriterion.maValue >>= aValue;
566 // check for 'empty', 'contains', 'begins with', or 'ends with' text filters
567 bool bEqual = nOperator == FilterOperator2::EQUAL;
568 bool bNotEqual = nOperator == FilterOperator2::NOT_EQUAL;
569 if( bEqual || bNotEqual )
570 {
571 if( aValue.isEmpty() )
572 {
573 // empty comparison string: create empty/not empty filters
574 nOperator = bNotEqual ? FilterOperator2::NOT_EMPTY : FilterOperator2::EMPTY;
575 }
576 else
577 {
578 // compare to something: try to find begins/ends/contains
579 bool bHasLeadingAsterisk = lclTrimLeadingAsterisks( aValue );
580 bool bHasTrailingAsterisk = lclTrimTrailingAsterisks( aValue );
581 // just '***' matches everything, do not create a filter field
582 bValidOperator = !aValue.isEmpty();
583 if( bValidOperator )
584 {
585 if( bHasLeadingAsterisk && bHasTrailingAsterisk )
586 nOperator = bNotEqual ? FilterOperator2::DOES_NOT_CONTAIN : FilterOperator2::CONTAINS;
587 else if( bHasLeadingAsterisk )
588 nOperator = bNotEqual ? FilterOperator2::DOES_NOT_END_WITH : FilterOperator2::ENDS_WITH;
589 else if( bHasTrailingAsterisk )
590 nOperator = bNotEqual ? FilterOperator2::DOES_NOT_BEGIN_WITH : FilterOperator2::BEGINS_WITH;
591 // else: no asterisks, stick to equal/not equal
592 }
593 }
594 }
595
596 if( bValidOperator )
597 {
598 // if wildcards are present, require RE mode, otherwise keep don't care state
599 if( lclConvertWildcardsToRegExp( aValue ) )
600 aSettings.mobNeedsRegExp = true;
601 // create a new UNO API filter field
602 aSettings.appendField( mbAnd, nOperator, aValue );
603 }
604 }
605 else if( rCriterion.maValue.has< double >() )
606 {
607 // floating-point argument
608 double fValue = 0.0;
609 rCriterion.maValue >>= fValue;
610 aSettings.appendField( mbAnd, nOperator, fValue );
611 }
612 }
613 }
614 return aSettings;
615}
616
618{
619 if( (rCriterion.mnOperator != XML_TOKEN_INVALID) && rCriterion.maValue.hasValue() )
620 maCriteria.push_back( rCriterion );
621}
622
624 WorkbookHelper( rHelper ),
625 mnColId( -1 ),
626 mbHiddenButton( false ),
627 mbShowButton( true )
628{
629}
630
632{
633 mnColId = rAttribs.getInteger( XML_colId, -1 );
634 mbHiddenButton = rAttribs.getBool( XML_hiddenButton, false );
635 mbShowButton = rAttribs.getBool( XML_showButton, true );
636}
637
639{
640 sal_uInt16 nFlags;
641 mnColId = rStrm.readInt32();
642 nFlags = rStrm.readuInt16();
643 mbHiddenButton = getFlag( nFlags, BIFF12_FILTERCOLUMN_HIDDENBUTTON );
644 mbShowButton = getFlag( nFlags, BIFF12_FILTERCOLUMN_SHOWBUTTON );
645}
646
648{
649 ApiFilterSettings aSettings;
650 if( (0 <= mnColId) && mxSettings )
651 {
652 // filter settings object creates a sequence of filter fields
653 aSettings = mxSettings->finalizeImport();
654 // add column index to all filter fields
655 for( auto& rFilterField : aSettings.maFilterFields )
656 rFilterField.Field = mnColId;
657 }
658 return aSettings;
659}
660
662{
663 return (mbShowButton == false) || (mbHiddenButton == true);
664}
665
666// SortCondition
667
669 WorkbookHelper( rHelper ),
670 mbDescending( false )
671{
672}
673
674void SortCondition::importSortCondition( const AttributeList& rAttribs, sal_Int16 nSheet )
675{
676 OUString aRangeStr = rAttribs.getString( XML_ref, OUString() );
678
679 maSortCustomList = rAttribs.getString( XML_customList, OUString() );
680 mbDescending = rAttribs.getBool( XML_descending, false );
681}
682
683// AutoFilter
684
686 WorkbookHelper( rHelper )
687{
688}
689
690void AutoFilter::importAutoFilter( const AttributeList& rAttribs, sal_Int16 nSheet )
691{
692 OUString aRangeStr = rAttribs.getString( XML_ref, OUString() );
694}
695
696void AutoFilter::importAutoFilter( SequenceInputStream& rStrm, sal_Int16 nSheet )
697{
698 BinRange aBinRange;
699 rStrm >> aBinRange;
701}
702
703void AutoFilter::importSortState( const AttributeList& rAttribs, sal_Int16 nSheet )
704{
705 OUString aRangeStr = rAttribs.getString( XML_ref, OUString() );
707}
708
710{
711 FilterColumnVector::value_type xFilterColumn = std::make_shared<FilterColumn>( *this );
712 maFilterColumns.push_back( xFilterColumn );
713 return *xFilterColumn;
714}
715
717{
718 SortConditionVector::value_type xSortCondition = std::make_shared<SortCondition>( *this );
719 maSortConditions.push_back( xSortCondition );
720 return *xSortCondition;
721}
722
723void AutoFilter::finalizeImport( const Reference< XDatabaseRange >& rxDatabaseRange, sal_Int16 nSheet )
724{
725 // convert filter settings using the filter descriptor of the database range
726 const Reference<XSheetFilterDescriptor3> xFilterDesc( rxDatabaseRange->getFilterDescriptor(), UNO_QUERY_THROW );
727 if( !xFilterDesc.is() )
728 return;
729
730 // set some common properties for the auto filter range
731 PropertySet aDescProps( xFilterDesc );
732 aDescProps.setProperty( PROP_IsCaseSensitive, false );
733 aDescProps.setProperty( PROP_SkipDuplicates, false );
734 aDescProps.setProperty( PROP_Orientation, TableOrientation_ROWS );
735 aDescProps.setProperty( PROP_ContainsHeader, true );
736 aDescProps.setProperty( PROP_CopyOutputData, false );
737
738 // resulting list of all UNO API filter fields
739 ::std::vector<TableFilterField3> aFilterFields;
740
741 // track if columns require to enable or disable regular expressions
742 std::optional< bool > obNeedsRegExp;
743
744 /* Track whether the filter fields of the first filter column are
745 connected with 'or'. In this case, other filter fields cannot be
746 inserted without altering the result of the entire filter, due to
747 Calc's precedence for the 'and' connection operator. Example:
748 Excel's filter conditions 'A1 and (B1 or B2) and C1' where B1 and
749 B2 belong to filter column B, will be evaluated by Calc as
750 '(A1 and B1) or (B2 and C1)'. */
751 bool bHasOrConnection = false;
752
753 ScDocument& rDoc = getScDocument();
754 SCCOL nCol = maRange.aStart.Col();
755 SCROW nRow = maRange.aStart.Row();
756 SCTAB nTab = maRange.aStart.Tab();
757
758 // process all filter column objects, exit when 'or' connection exists
759 for( const auto& rxFilterColumn : maFilterColumns )
760 {
761 // the filter settings object creates a list of filter fields
762 ApiFilterSettings aSettings = rxFilterColumn->finalizeImport();
763 ApiFilterSettings::FilterFieldVector& rColumnFields = aSettings.maFilterFields;
764
765 if (rxFilterColumn->isButtonHidden())
766 {
767 auto nFlag = rDoc.GetAttr(nCol, nRow, nTab, ATTR_MERGE_FLAG)->GetValue();
768 rDoc.ApplyAttr(nCol, nRow, nTab, ScMergeFlagAttr(nFlag & ~ScMF::Auto));
769 }
770 nCol++;
771
772 /* Check whether mode for regular expressions is compatible with
773 the global mode in obNeedsRegExp. If either one is still in
774 don't-care state, all is fine. If both are set, they must be
775 equal. */
776 bool bRegExpCompatible = !obNeedsRegExp || !aSettings.mobNeedsRegExp || (obNeedsRegExp.value() == aSettings.mobNeedsRegExp.value());
777
778 // check whether fields are connected by 'or' (see comments above).
779 if( rColumnFields.size() >= 2 )
780 bHasOrConnection = std::any_of(rColumnFields.begin() + 1, rColumnFields.end(),
781 [](const css::sheet::TableFilterField3& rColumnField) { return rColumnField.Connection == FilterConnection_OR; });
782
783 /* Skip the column filter, if no filter fields have been created,
784 and if the mode for regular expressions of the
785 filter column does not fit. */
786 if( !rColumnFields.empty() && bRegExpCompatible )
787 {
788 /* Add 'and' connection to the first filter field to connect
789 it to the existing filter fields of other columns. */
790 rColumnFields[ 0 ].Connection = FilterConnection_AND;
791
792 // insert the new filter fields
793 aFilterFields.insert( aFilterFields.end(), rColumnFields.begin(), rColumnFields.end() );
794
795 // update the regular expressions mode
796 assignIfUsed( obNeedsRegExp, aSettings.mobNeedsRegExp );
797 }
798
799 if( bHasOrConnection )
800 break;
801 }
802
803 // insert all filter fields to the filter descriptor
804 if( !aFilterFields.empty() )
805 xFilterDesc->setFilterFields3( comphelper::containerToSequence( aFilterFields ) );
806
807 // regular expressions
808 bool bUseRegExp = obNeedsRegExp.value_or( false );
809 aDescProps.setProperty( PROP_UseRegularExpressions, bUseRegExp );
810
811 // sort
812 if (maSortConditions.empty())
813 return;
814
815 const SortConditionVector::value_type& xSortConditionPointer = *maSortConditions.begin();
816 const SortCondition& rSorConditionLoaded = *xSortConditionPointer;
817
818 ScSortParam aParam;
819 aParam.bUserDef = false;
820 aParam.nUserIndex = 0;
821 aParam.bByRow = false;
822
823 ScUserList* pUserList = ScGlobal::GetUserList();
824 if (!rSorConditionLoaded.maSortCustomList.isEmpty())
825 {
826 for (size_t i=0; pUserList && i < pUserList->size(); i++)
827 {
828 const OUString aEntry((*pUserList)[i].GetString());
829 if (aEntry.equalsIgnoreAsciiCase(rSorConditionLoaded.maSortCustomList))
830 {
831 aParam.bUserDef = true;
832 aParam.nUserIndex = i;
833 break;
834 }
835 }
836 }
837
838 if (!aParam.bUserDef)
839 {
840 pUserList->emplace_back(rSorConditionLoaded.maSortCustomList);
841 aParam.bUserDef = true;
842 aParam.nUserIndex = pUserList->size()-1;
843 }
844
845 // set sort parameter if we have detected it
846 if (!aParam.bUserDef)
847 return;
848
849 SCCOLROW nStartPos = aParam.bByRow ? maRange.aStart.Col() : maRange.aStart.Row();
850 if (rSorConditionLoaded.mbDescending)
851 {
852 // descending sort - need to enable 1st SortParam slot
853 assert(aParam.GetSortKeyCount() == DEFSORT);
854
855 aParam.maKeyState[0].bDoSort = true;
856 aParam.maKeyState[0].bAscending = false;
857 aParam.maKeyState[0].nField += nStartPos;
858 }
859
860 ScDBData* pDBData = rDoc.GetDBAtArea(
861 nSheet,
864
865 if (pDBData)
866 pDBData->SetSortParam(aParam);
867 else
868 OSL_FAIL("AutoFilter::finalizeImport(): cannot find matching DBData");
869}
870
872 WorkbookHelper( rHelper )
873{
874}
875
877{
878 AutoFilterVector::value_type xAutoFilter = std::make_shared<AutoFilter>( *this );
879 maAutoFilters.push_back( xAutoFilter );
880 return *xAutoFilter;
881}
882
883void AutoFilterBuffer::finalizeImport( sal_Int16 nSheet )
884{
885 // rely on existence of the defined name '_FilterDatabase' containing the range address of the filtered area
886 const DefinedName* pFilterDBName = getDefinedNames().getByBuiltinId( BIFF_DEFNAME_FILTERDATABASE, nSheet ).get();
887 if(!pFilterDBName)
888 return;
889
890 ScRange aFilterRange;
891 if( !(pFilterDBName->getAbsoluteRange( aFilterRange ) && (aFilterRange.aStart.Tab() == nSheet)) )
892 return;
893
894 // use the same name for the database range as used for the defined name '_FilterDatabase'
895 Reference< XDatabaseRange > xDatabaseRange = createUnnamedDatabaseRangeObject( aFilterRange );
896 // first, try to create an auto filter
897 bool bHasAutoFilter = finalizeImport( xDatabaseRange, nSheet );
898 // no success: try to create an advanced filter
899 if( bHasAutoFilter || !xDatabaseRange.is() )
900 return;
901
902 // the built-in defined name 'Criteria' must exist
903 const DefinedName* pCriteriaName = getDefinedNames().getByBuiltinId( BIFF_DEFNAME_CRITERIA, nSheet ).get();
904 if( !pCriteriaName )
905 return;
906
907 ScRange aCriteriaRange;
908 if( !pCriteriaName->getAbsoluteRange( aCriteriaRange ) )
909 return;
910
911 // set some common properties for the filter descriptor
912 PropertySet aDescProps( xDatabaseRange->getFilterDescriptor() );
913 aDescProps.setProperty( PROP_IsCaseSensitive, false );
914 aDescProps.setProperty( PROP_SkipDuplicates, false );
915 aDescProps.setProperty( PROP_Orientation, TableOrientation_ROWS );
916 aDescProps.setProperty( PROP_ContainsHeader, true );
917 // criteria range may contain wildcards, but these are incompatible with REs
918 aDescProps.setProperty( PROP_UseRegularExpressions, false );
919
920 // position of output data (if built-in defined name 'Extract' exists)
922 ScRange aOutputRange;
923 bool bHasOutputRange = xExtractName && xExtractName->getAbsoluteRange( aOutputRange );
924 aDescProps.setProperty( PROP_CopyOutputData, bHasOutputRange );
925 if( bHasOutputRange )
926 {
927 aDescProps.setProperty( PROP_SaveOutputPosition, true );
928 aDescProps.setProperty( PROP_OutputPosition, CellAddress( aOutputRange.aStart.Tab(), aOutputRange.aStart.Col(), aOutputRange.aStart.Row() ) );
929 }
930
931 /* Properties of the database range (must be set after
932 modifying properties of the filter descriptor,
933 otherwise the 'FilterCriteriaSource' property gets
934 deleted). */
935 PropertySet aRangeProps( xDatabaseRange );
936 aRangeProps.setProperty( PROP_AutoFilter, false );
937 aRangeProps.setProperty( PROP_FilterCriteriaSource,
938 CellRangeAddress( aCriteriaRange.aStart.Tab(),
939 aCriteriaRange.aStart.Col(), aCriteriaRange.aStart.Row(),
940 aCriteriaRange.aEnd.Col(), aCriteriaRange.aEnd.Row() ));
941}
942
943bool AutoFilterBuffer::finalizeImport( const Reference< XDatabaseRange >& rxDatabaseRange, sal_Int16 nSheet )
944{
945 AutoFilter* pAutoFilter = getActiveAutoFilter();
946 if( pAutoFilter && rxDatabaseRange.is() ) try
947 {
948 // the property 'AutoFilter' enables the drop-down buttons
949 PropertySet aRangeProps( rxDatabaseRange );
950 aRangeProps.setProperty( PROP_AutoFilter, true );
951
952 pAutoFilter->finalizeImport( rxDatabaseRange, nSheet );
953
954 // return true to indicate enabled autofilter
955 return true;
956 }
957 catch( Exception& )
958 {
959 }
960 return false;
961}
962
964{
965 // Excel expects not more than one auto filter per sheet or table
966 OSL_ENSURE( maAutoFilters.size() <= 1, "AutoFilterBuffer::getActiveAutoFilter - too many auto filters" );
967 // stick to the last imported auto filter
968 return maAutoFilters.empty() ? nullptr : maAutoFilters.back().get();
969}
970
971} // namespace oox
972
973/* 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:411
SC_DLLPUBLIC const ScDBData * GetDBAtArea(SCTAB nTab, SCCOL nCol1, SCROW nRow1, SCCOL nCol2, SCROW nRow2) const
Definition: documen3.cxx:338
SC_DLLPUBLIC void ApplyAttr(SCCOL nCol, SCROW nRow, SCTAB nTab, const SfxPoolItem &rAttr)
Definition: document.cxx:4741
SC_DLLPUBLIC ScStyleSheetPool * GetStyleSheetPool() const
Definition: document.cxx:6055
SC_DLLPUBLIC const SfxPoolItem * GetAttr(SCCOL nCol, SCROW nRow, SCTAB nTab, sal_uInt16 nWhich) const
Definition: document.cxx:4684
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:133
Collection of user-defined sort lists.
Definition: userlist.hxx:62
void emplace_back(Args &&... args)
Definition: userlist.hxx:85
size_t size() const
Definition: userlist.hxx:84
const SfxPoolItem * GetItem(sal_uInt16 nWhich, bool bSearchInParent=true) const
virtual SfxStyleSheetBase * Find(const OUString &, SfxStyleFamily eFam, SfxStyleSearchBits n=SfxStyleSearchBits::All)
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< ScMergeFlagAttr > ATTR_MERGE_FLAG(145)
constexpr TypedWhichId< SvxBrushItem > ATTR_BACKGROUND(148)
#define DEFSORT
Definition: sortparam.hxx:22
::std::vector< ScSortKeyState > maKeyState
Definition: sortparam.hxx:130
sal_uInt16 nUserIndex
Definition: sortparam.hxx:119
sal_uInt16 GetSortKeyCount() const
Definition: sortparam.hxx:148
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
sal_Int16 SCTAB
Definition: types.hxx:22
sal_Int16 SCCOL
Definition: types.hxx:21
sal_Int32 SCROW
Definition: types.hxx:17
std::unique_ptr< char[]> aBuffer
sal_Int32 nLength