sc/source/filter/oox/autofilterbuffer.cxx

   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/FilterConnection.hpp>
  24 #include <com/sun/star/sheet/FilterOperator2.hpp>
  25 #include <com/sun/star/sheet/TableFilterField3.hpp>
  26 #include <com/sun/star/sheet/XDatabaseRange.hpp>
  27 #include <com/sun/star/sheet/XSheetFilterDescriptor3.hpp>
  28 #include <com/sun/star/table/TableOrientation.hpp>
  29 #include <com/sun/star/table/CellAddress.hpp>
  30 #include <rtl/ustrbuf.hxx>
  31 #include <osl/diagnose.h>
  32 #include <oox/helper/attributelist.hxx>
  33 #include <oox/helper/containerhelper.hxx>
  34 #include <oox/helper/propertyset.hxx>
  35 #include <oox/helper/binaryinputstream.hxx>
  36 #include <oox/token/namespaces.hxx>
  37 #include <oox/token/properties.hxx>
  38 #include <oox/token/tokens.hxx>
  39 #include <addressconverter.hxx>
  40 #include <defnamesbuffer.hxx>
  41 #include <biffhelper.hxx>
  42
  43 namespace oox {
  44 namespace xls {
  45
  46 using namespace ::com::sun::star::sheet;
  47 using namespace ::com::sun::star::table;
  48 using namespace ::com::sun::star::uno;
  49
  50 namespace {
  51
  52 const sal_uInt8 BIFF12_TOP10FILTER_TOP              = 0x01;
  53 const sal_uInt8 BIFF12_TOP10FILTER_PERCENT          = 0x02;
  54
  55 const sal_uInt16 BIFF12_FILTERCOLUMN_HIDDENBUTTON   = 0x0001;
  56 const sal_uInt16 BIFF12_FILTERCOLUMN_SHOWBUTTON     = 0x0002;
  57
  58 const sal_uInt8 BIFF_FILTER_DATATYPE_NONE           = 0;
  59 const sal_uInt8 BIFF_FILTER_DATATYPE_DOUBLE         = 4;
  60 const sal_uInt8 BIFF_FILTER_DATATYPE_STRING         = 6;
  61 const sal_uInt8 BIFF_FILTER_DATATYPE_BOOLEAN        = 8;
  62 const sal_uInt8 BIFF_FILTER_DATATYPE_EMPTY          = 12;
  63 const sal_uInt8 BIFF_FILTER_DATATYPE_NOTEMPTY       = 14;
  64
  65 bool lclGetApiOperatorFromToken( sal_Int32& rnApiOperator, sal_Int32 nToken )
  66 {
  67     switch( nToken )
  68     {
  69         case XML_lessThan:              rnApiOperator = FilterOperator2::NOT_EQUAL;     return true;
  70         case XML_equal:                 rnApiOperator = FilterOperator2::EQUAL;         return true;
  71         case XML_lessThanOrEqual:       rnApiOperator = FilterOperator2::LESS_EQUAL;    return true;
  72         case XML_greaterThan:           rnApiOperator = FilterOperator2::GREATER;       return true;
  73         case XML_notEqual:              rnApiOperator = FilterOperator2::NOT_EQUAL;     return true;
  74         case XML_greaterThanOrEqual:    rnApiOperator = FilterOperator2::GREATER_EQUAL; return true;
  75     }
  76     return false;
  77 }
  78
  79 /** Removes leading asterisk characters from the passed string.
  80     @return  True = at least one asterisk character has been removed. */
  81 bool lclTrimLeadingAsterisks( OUString& rValue )
  82 {
  83     sal_Int32 nLength = rValue.getLength();
  84     sal_Int32 nPos = 0;
  85     while( (nPos < nLength) && (rValue[ nPos ] == '*') )
  86         ++nPos;
  87     if( nPos > 0 )
  88     {
  89         rValue = rValue.copy( nPos );
  90         return true;
  91     }
  92     return false;
  93 }
  94
  95 /** Removes trailing asterisk characters from the passed string.
  96     @return  True = at least one asterisk character has been removed. */
  97 bool lclTrimTrailingAsterisks( OUString& rValue )
  98 {
  99     sal_Int32 nLength = rValue.getLength();
 100     sal_Int32 nPos = nLength;
 101     while( (nPos > 0) && (rValue[ nPos - 1 ] == '*') )
 102         --nPos;
 103     if( nPos < nLength )
 104     {
 105         rValue = rValue.copy( 0, nPos );
 106         return true;
 107     }
 108     return false;
 109 }
 110
 111 /** Converts wildcard characters '*' and '?' to regular expressions and quotes
 112     RE meta characters.
 113     @return  True = passed string has been changed (RE needs to be enabled). */
 114 bool lclConvertWildcardsToRegExp( OUString& rValue )
 115 {
 116     // check existence of the wildcard characters '*' and '?'
 117     if( !rValue.isEmpty() && ((rValue.indexOf( '*' ) >= 0) || (rValue.indexOf( '?' ) >= 0)) )
 118     {
 119         OUStringBuffer aBuffer;
 120         aBuffer.ensureCapacity( rValue.getLength() + 5 );
 121         const sal_Unicode* pcChar = rValue.getStr();
 122         const sal_Unicode* pcEnd = pcChar + rValue.getLength();
 123         for( ; pcChar < pcEnd; ++pcChar )
 124         {
 125             switch( *pcChar )
 126             {
 127                 case '?':
 128                     aBuffer.append( '.' );
 129                 break;
 130                 case '*':
 131                     aBuffer.append( '.' ).append( '*' );
 132                 break;
 133                 case '\\': case '.': case '|': case '(': case ')': case '^': case '$':
 134                     // quote RE meta characters
 135                     aBuffer.append( '\\' ).append( *pcChar );
 136                 break;
 137                 default:
 138                     aBuffer.append( *pcChar );
 139             }
 140         }
 141         rValue = aBuffer.makeStringAndClear();
 142         return true;
 143     }
 144     return false;
 145 }
 146
 147 } // namespace
 148
 149 ApiFilterSettings::ApiFilterSettings()
 150 {
 151 }
 152
 153 void ApiFilterSettings::appendField( bool bAnd, sal_Int32 nOperator, double fValue )
 154 {
 155     maFilterFields.emplace_back();
 156     TableFilterField3& rFilterField = maFilterFields.back();
 157     rFilterField.Connection = bAnd ? FilterConnection_AND : FilterConnection_OR;
 158     rFilterField.Operator = nOperator;
 159     rFilterField.Values.realloc(1);
 160     rFilterField.Values[0].IsNumeric = true;
 161     rFilterField.Values[0].NumericValue = fValue;
 162 }
 163
 164 void ApiFilterSettings::appendField( bool bAnd, sal_Int32 nOperator, const OUString& rValue )
 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].IsNumeric = false;
 172     rFilterField.Values[0].StringValue = rValue;
 173 }
 174
 175 void ApiFilterSettings::appendField( bool bAnd, const std::vector<OUString>& rValues )
 176 {
 177     maFilterFields.emplace_back();
 178     TableFilterField3& rFilterField = maFilterFields.back();
 179     rFilterField.Connection = bAnd ? FilterConnection_AND : FilterConnection_OR;
 180     rFilterField.Operator = FilterOperator2::EQUAL;
 181     size_t n = rValues.size();
 182     rFilterField.Values.realloc(n);
 183     for (size_t i = 0; i < n; ++i)
 184     {
 185         rFilterField.Values[i].IsNumeric = false;
 186         rFilterField.Values[i].StringValue = rValues[i];
 187     }
 188 }
 189
 190 FilterSettingsBase::FilterSettingsBase( const WorkbookHelper& rHelper ) :
 191     WorkbookHelper( rHelper )
 192 {
 193 }
 194
 195 void FilterSettingsBase::importAttribs( sal_Int32 /*nElement*/, const AttributeList& /*rAttribs*/ )
 196 {
 197 }
 198
 199 void FilterSettingsBase::importRecord( sal_Int32 /*nRecId*/, SequenceInputStream& /*rStrm*/ )
 200 {
 201 }
 202
 203 ApiFilterSettings FilterSettingsBase::finalizeImport( sal_Int32 /*nMaxCount*/ )
 204 {
 205     return ApiFilterSettings();
 206 }
 207
 208 DiscreteFilter::DiscreteFilter( const WorkbookHelper& rHelper ) :
 209     FilterSettingsBase( rHelper ),
 210     mnCalendarType( XML_none ),
 211     mbShowBlank( false )
 212 {
 213 }
 214
 215 void DiscreteFilter::importAttribs( sal_Int32 nElement, const AttributeList& rAttribs )
 216 {
 217     switch( nElement )
 218     {
 219         case XLS_TOKEN( filters ):
 220             mnCalendarType = rAttribs.getToken( XML_calendarType, XML_none );
 221             mbShowBlank = rAttribs.getBool( XML_blank, false );
 222         break;
 223
 224         case XLS_TOKEN( filter ):
 225         {
 226             OUString aValue = rAttribs.getXString( XML_val, OUString() );
 227             if( !aValue.isEmpty() )
 228                 maValues.push_back( aValue );
 229         }
 230         break;
 231     }
 232 }
 233
 234 void DiscreteFilter::importRecord( sal_Int32 nRecId, SequenceInputStream& rStrm )
 235 {
 236     switch( nRecId )
 237     {
 238         case BIFF12_ID_DISCRETEFILTERS:
 239         {
 240             sal_Int32 nShowBlank, nCalendarType;
 241             nShowBlank = rStrm.readInt32();
 242             nCalendarType = rStrm.readInt32();
 243
 244             static const sal_Int32 spnCalendarTypes[] = {
 245                 XML_none, XML_gregorian, XML_gregorianUs, XML_japan, XML_taiwan, XML_korea, XML_hijri, XML_thai, XML_hebrew,
 246                 XML_gregorianMeFrench, XML_gregorianArabic, XML_gregorianXlitEnglish, XML_gregorianXlitFrench };
 247             mnCalendarType = STATIC_ARRAY_SELECT( spnCalendarTypes, nCalendarType, XML_none );
 248             mbShowBlank = nShowBlank != 0;
 249         }
 250         break;
 251
 252         case BIFF12_ID_DISCRETEFILTER:
 253         {
 254             OUString aValue = BiffHelper::readString( rStrm );
 255             if( !aValue.isEmpty() )
 256                 maValues.push_back( aValue );
 257         }
 258         break;
 259     }
 260 }
 261
 262 ApiFilterSettings DiscreteFilter::finalizeImport( sal_Int32 nMaxCount )
 263 {
 264     ApiFilterSettings aSettings;
 265     if( static_cast< sal_Int32 >( maValues.size() ) <= nMaxCount )
 266     {
 267         aSettings.maFilterFields.reserve( maValues.size() );
 268
 269         // insert all filter values
 270         aSettings.appendField( true, maValues );
 271
 272         // extra field for 'show empty'
 273         if( mbShowBlank )
 274             aSettings.appendField( false, FilterOperator2::EMPTY, OUString() );
 275
 276         /*  Require disabled regular expressions, filter entries may contain
 277             any RE meta characters. */
 278         if( !maValues.empty() )
 279             aSettings.mobNeedsRegExp = false;
 280     }
 281     return aSettings;
 282 }
 283
 284 Top10Filter::Top10Filter( const WorkbookHelper& rHelper ) :
 285     FilterSettingsBase( rHelper ),
 286     mfValue( 0.0 ),
 287     mbTop( true ),
 288     mbPercent( false )
 289 {
 290 }
 291
 292 void Top10Filter::importAttribs( sal_Int32 nElement, const AttributeList& rAttribs )
 293 {
 294     if( nElement == XLS_TOKEN( top10 ) )
 295     {
 296         mfValue = rAttribs.getDouble( XML_val, 0.0 );
 297         mbTop = rAttribs.getBool( XML_top, true );
 298         mbPercent = rAttribs.getBool( XML_percent, false );
 299     }
 300 }
 301
 302 void Top10Filter::importRecord( sal_Int32 nRecId, SequenceInputStream& rStrm )
 303 {
 304     if( nRecId == BIFF12_ID_TOP10FILTER )
 305     {
 306         sal_uInt8 nFlags;
 307         nFlags = rStrm.readuChar();
 308         mfValue = rStrm.readDouble();
 309         mbTop = getFlag( nFlags, BIFF12_TOP10FILTER_TOP );
 310         mbPercent = getFlag( nFlags, BIFF12_TOP10FILTER_PERCENT );
 311     }
 312 }
 313
 314 ApiFilterSettings Top10Filter::finalizeImport( sal_Int32 /*nMaxCount*/ )
 315 {
 316     sal_Int32 nOperator = mbTop ?
 317         (mbPercent ? FilterOperator2::TOP_PERCENT : FilterOperator2::TOP_VALUES) :
 318         (mbPercent ? FilterOperator2::BOTTOM_PERCENT : FilterOperator2::BOTTOM_VALUES);
 319     ApiFilterSettings aSettings;
 320     aSettings.appendField( true, nOperator, mfValue );
 321     return aSettings;
 322 }
 323
 324 FilterCriterionModel::FilterCriterionModel() :
 325     mnOperator( XML_equal ),
 326     mnDataType( BIFF_FILTER_DATATYPE_NONE )
 327 {
 328 }
 329
 330 void FilterCriterionModel::setBiffOperator( sal_uInt8 nOperator )
 331 {
 332     static const sal_Int32 spnOperators[] = { XML_TOKEN_INVALID,
 333         XML_lessThan, XML_equal, XML_lessThanOrEqual, XML_greaterThan, XML_notEqual, XML_greaterThanOrEqual };
 334     mnOperator = STATIC_ARRAY_SELECT( spnOperators, nOperator, XML_TOKEN_INVALID );
 335 }
 336
 337 void FilterCriterionModel::readBiffData( SequenceInputStream& rStrm )
 338 {
 339     sal_uInt8 nOperator;
 340     mnDataType = rStrm.readuChar();
 341     nOperator = rStrm.readuChar();
 342     setBiffOperator( nOperator );
 343
 344     switch( mnDataType )
 345     {
 346         case BIFF_FILTER_DATATYPE_DOUBLE:
 347             maValue <<= rStrm.readDouble();
 348         break;
 349         case BIFF_FILTER_DATATYPE_STRING:
 350         {
 351             rStrm.skip( 8 );
 352             OUString aValue = BiffHelper::readString( rStrm ).trim();
 353             if( !aValue.isEmpty() )
 354                 maValue <<= aValue;
 355         }
 356         break;
 357         case BIFF_FILTER_DATATYPE_BOOLEAN:
 358             maValue <<= (rStrm.readuInt8() != 0);
 359             rStrm.skip( 7 );
 360         break;
 361         case BIFF_FILTER_DATATYPE_EMPTY:
 362             rStrm.skip( 8 );
 363             if( mnOperator == XML_equal )
 364                 maValue <<= OUString();
 365         break;
 366         case BIFF_FILTER_DATATYPE_NOTEMPTY:
 367             rStrm.skip( 8 );
 368             if( mnOperator == XML_notEqual )
 369                 maValue <<= OUString();
 370         break;
 371         default:
 372             OSL_ENSURE( false, "FilterCriterionModel::readBiffData - unexpected data type" );
 373             rStrm.skip( 8 );
 374     }
 375 }
 376
 377 CustomFilter::CustomFilter( const WorkbookHelper& rHelper ) :
 378     FilterSettingsBase( rHelper ),
 379     mbAnd( false )
 380 {
 381 }
 382
 383 void CustomFilter::importAttribs( sal_Int32 nElement, const AttributeList& rAttribs )
 384 {
 385     switch( nElement )
 386     {
 387         case XLS_TOKEN( customFilters ):
 388             mbAnd = rAttribs.getBool( XML_and, false );
 389         break;
 390
 391         case XLS_TOKEN( customFilter ):
 392         {
 393             FilterCriterionModel aCriterion;
 394             aCriterion.mnOperator = rAttribs.getToken( XML_operator, XML_equal );
 395             OUString aValue = rAttribs.getXString( XML_val, OUString() ).trim();
 396             if( (aCriterion.mnOperator == XML_equal) || (aCriterion.mnOperator == XML_notEqual) || (!aValue.isEmpty()) )
 397                 aCriterion.maValue <<= aValue;
 398             appendCriterion( aCriterion );
 399         }
 400         break;
 401     }
 402 }
 403
 404 void CustomFilter::importRecord( sal_Int32 nRecId, SequenceInputStream& rStrm )
 405 {
 406     switch( nRecId )
 407     {
 408         case BIFF12_ID_CUSTOMFILTERS:
 409             mbAnd = rStrm.readInt32() == 0;
 410         break;
 411
 412         case BIFF12_ID_CUSTOMFILTER:
 413         {
 414             FilterCriterionModel aCriterion;
 415             aCriterion.readBiffData( rStrm );
 416             appendCriterion( aCriterion );
 417         }
 418         break;
 419     }
 420 }
 421
 422 ApiFilterSettings CustomFilter::finalizeImport( sal_Int32 /*nMaxCount*/ )
 423 {
 424     ApiFilterSettings aSettings;
 425     OSL_ENSURE( maCriteria.size() <= 2, "CustomFilter::finalizeImport - too many filter criteria" );
 426     for( const auto& rCriterion : maCriteria )
 427     {
 428         // first extract the filter operator
 429         sal_Int32 nOperator = 0;
 430         bool bValidOperator = lclGetApiOperatorFromToken( nOperator, rCriterion.mnOperator );
 431         if( bValidOperator )
 432         {
 433             if( rCriterion.maValue.has< OUString >() )
 434             {
 435                 // string argument
 436                 OUString aValue;
 437                 rCriterion.maValue >>= aValue;
 438                 // check for 'empty', 'contains', 'begins with', or 'ends with' text filters
 439                 bool bEqual = nOperator == FilterOperator2::EQUAL;
 440                 bool bNotEqual = nOperator == FilterOperator2::NOT_EQUAL;
 441                 if( bEqual || bNotEqual )
 442                 {
 443                     if( aValue.isEmpty() )
 444                     {
 445                         // empty comparison string: create empty/not empty filters
 446                         nOperator = bNotEqual ? FilterOperator2::NOT_EMPTY : FilterOperator2::EMPTY;
 447                     }
 448                     else
 449                     {
 450                         // compare to something: try to find begins/ends/contains
 451                         bool bHasLeadingAsterisk = lclTrimLeadingAsterisks( aValue );
 452                         bool bHasTrailingAsterisk = lclTrimTrailingAsterisks( aValue );
 453                         // just '***' matches everything, do not create a filter field
 454                         bValidOperator = !aValue.isEmpty();
 455                         if( bValidOperator )
 456                         {
 457                             if( bHasLeadingAsterisk && bHasTrailingAsterisk )
 458                                 nOperator = bNotEqual ? FilterOperator2::DOES_NOT_CONTAIN : FilterOperator2::CONTAINS;
 459                             else if( bHasLeadingAsterisk )
 460                                 nOperator = bNotEqual ? FilterOperator2::DOES_NOT_END_WITH : FilterOperator2::ENDS_WITH;
 461                             else if( bHasTrailingAsterisk )
 462                                 nOperator = bNotEqual ? FilterOperator2::DOES_NOT_BEGIN_WITH : FilterOperator2::BEGINS_WITH;
 463                             // else: no asterisks, stick to equal/not equal
 464                         }
 465                     }
 466                 }
 467
 468                 if( bValidOperator )
 469                 {
 470                     // if wildcards are present, require RE mode, otherwise keep don't care state
 471                     if( lclConvertWildcardsToRegExp( aValue ) )
 472                         aSettings.mobNeedsRegExp = true;
 473                     // create a new UNO API filter field
 474                     aSettings.appendField( mbAnd, nOperator, aValue );
 475                 }
 476             }
 477             else if( rCriterion.maValue.has< double >() )
 478             {
 479                 // floating-point argument
 480                 double fValue = 0.0;
 481                 rCriterion.maValue >>= fValue;
 482                 aSettings.appendField( mbAnd, nOperator, fValue );
 483             }
 484         }
 485     }
 486     return aSettings;
 487 }
 488
 489 void CustomFilter::appendCriterion( const FilterCriterionModel& rCriterion )
 490 {
 491     if( (rCriterion.mnOperator != XML_TOKEN_INVALID) && rCriterion.maValue.hasValue() )
 492         maCriteria.push_back( rCriterion );
 493 }
 494
 495 FilterColumn::FilterColumn( const WorkbookHelper& rHelper ) :
 496     WorkbookHelper( rHelper ),
 497     mnColId( -1 ),
 498     mbHiddenButton( false ),
 499     mbShowButton( true )
 500 {
 501 }
 502
 503 void FilterColumn::importFilterColumn( const AttributeList& rAttribs )
 504 {
 505     mnColId = rAttribs.getInteger( XML_colId, -1 );
 506     mbHiddenButton = rAttribs.getBool( XML_hiddenButton, false );
 507     mbShowButton = rAttribs.getBool( XML_showButton, true );
 508 }
 509
 510 void FilterColumn::importFilterColumn( SequenceInputStream& rStrm )
 511 {
 512     sal_uInt16 nFlags;
 513     mnColId = rStrm.readInt32();
 514     nFlags = rStrm.readuInt16();
 515     mbHiddenButton = getFlag( nFlags, BIFF12_FILTERCOLUMN_HIDDENBUTTON );
 516     mbShowButton = getFlag( nFlags, BIFF12_FILTERCOLUMN_SHOWBUTTON );
 517 }
 518
 519 ApiFilterSettings FilterColumn::finalizeImport( sal_Int32 nMaxCount )
 520 {
 521     ApiFilterSettings aSettings;
 522     if( (0 <= mnColId) && mxSettings.get() )
 523     {
 524         // filter settings object creates a sequence of filter fields
 525         aSettings = mxSettings->finalizeImport( nMaxCount );
 526         // add column index to all filter fields
 527         for( auto& rFilterField : aSettings.maFilterFields )
 528             rFilterField.Field = mnColId;
 529     }
 530     return aSettings;
 531 }
 532
 533 AutoFilter::AutoFilter( const WorkbookHelper& rHelper ) :
 534     WorkbookHelper( rHelper )
 535 {
 536 }
 537
 538 void AutoFilter::importAutoFilter( const AttributeList& rAttribs, sal_Int16 nSheet )
 539 {
 540     OUString aRangeStr = rAttribs.getString( XML_ref, OUString() );
 541     AddressConverter::convertToCellRangeUnchecked( maRange, aRangeStr, nSheet );
 542 }
 543
 544 void AutoFilter::importAutoFilter( SequenceInputStream& rStrm, sal_Int16 nSheet )
 545 {
 546     BinRange aBinRange;
 547     rStrm >> aBinRange;
 548     AddressConverter::convertToCellRangeUnchecked( maRange, aBinRange, nSheet );
 549 }
 550
 551 FilterColumn& AutoFilter::createFilterColumn()
 552 {
 553     FilterColumnVector::value_type xFilterColumn( new FilterColumn( *this ) );
 554     maFilterColumns.push_back( xFilterColumn );
 555     return *xFilterColumn;
 556 }
 557
 558 void AutoFilter::finalizeImport( const Reference<XSheetFilterDescriptor3>& rxFilterDesc )
 559 {
 560     if( rxFilterDesc.is() )
 561     {
 562         // set some common properties for the auto filter range
 563         PropertySet aDescProps( rxFilterDesc );
 564         aDescProps.setProperty( PROP_IsCaseSensitive, false );
 565         aDescProps.setProperty( PROP_SkipDuplicates, false );
 566         aDescProps.setProperty( PROP_Orientation, TableOrientation_ROWS );
 567         aDescProps.setProperty( PROP_ContainsHeader, true );
 568         aDescProps.setProperty( PROP_CopyOutputData, false );
 569
 570         // maximum number of UNO API filter fields
 571         sal_Int32 nMaxCount = 0;
 572         aDescProps.getProperty( nMaxCount, PROP_MaxFieldCount );
 573         OSL_ENSURE( nMaxCount > 0, "AutoFilter::finalizeImport - invalid maximum filter field count" );
 574
 575         // resulting list of all UNO API filter fields
 576         ::std::vector<TableFilterField3> aFilterFields;
 577
 578         // track if columns require to enable or disable regular expressions
 579         OptValue< bool > obNeedsRegExp;
 580
 581         /*  Track whether the filter fields of the first filter column are
 582             connected with 'or'. In this case, other filter fields cannot be
 583             inserted without altering the result of the entire filter, due to
 584             Calc's precedence for the 'and' connection operator. Example:
 585             Excel's filter conditions 'A1 and (B1 or B2) and C1' where B1 and
 586             B2 belong to filter column B, will be evaluated by Calc as
 587             '(A1 and B1) or (B2 and C1)'. */
 588         bool bHasOrConnection = false;
 589
 590         // process all filter column objects, exit when 'or' connection exists
 591         for( const auto& rxFilterColumn : maFilterColumns )
 592         {
 593             // the filter settings object creates a list of filter fields
 594             ApiFilterSettings aSettings = rxFilterColumn->finalizeImport( nMaxCount );
 595             ApiFilterSettings::FilterFieldVector& rColumnFields = aSettings.maFilterFields;
 596
 597             // new total number of filter fields
 598             sal_Int32 nNewCount = static_cast< sal_Int32 >( aFilterFields.size() + rColumnFields.size() );
 599
 600             /*  Check whether mode for regular expressions is compatible with
 601                 the global mode in obNeedsRegExp. If either one is still in
 602                 don't-care state, all is fine. If both are set, they must be
 603                 equal. */
 604             bool bRegExpCompatible = !obNeedsRegExp || !aSettings.mobNeedsRegExp || (obNeedsRegExp.get() == aSettings.mobNeedsRegExp.get());
 605
 606             // check whether fields are connected by 'or' (see comments above).
 607             if( rColumnFields.size() >= 2 )
 608                 bHasOrConnection = std::any_of(rColumnFields.begin() + 1, rColumnFields.end(),
 609                     [](const css::sheet::TableFilterField3& rColumnField) { return rColumnField.Connection == FilterConnection_OR; });
 610
 611             /*  Skip the column filter, if no filter fields have been created,
 612                 if the number of new filter fields would exceed the total limit
 613                 of filter fields, or if the mode for regular expressions of the
 614                 filter column does not fit. */
 615             if( !rColumnFields.empty() && (nNewCount <= nMaxCount) && bRegExpCompatible )
 616             {
 617                 /*  Add 'and' connection to the first filter field to connect
 618                     it to the existing filter fields of other columns. */
 619                 rColumnFields[ 0 ].Connection = FilterConnection_AND;
 620
 621                 // insert the new filter fields
 622                 aFilterFields.insert( aFilterFields.end(), rColumnFields.begin(), rColumnFields.end() );
 623
 624                 // update the regular expressions mode
 625                 obNeedsRegExp.assignIfUsed( aSettings.mobNeedsRegExp );
 626             }
 627
 628             if( bHasOrConnection )
 629                 break;
 630         }
 631
 632         // insert all filter fields to the filter descriptor
 633         if( !aFilterFields.empty() )
 634             rxFilterDesc->setFilterFields3( ContainerHelper::vectorToSequence( aFilterFields ) );
 635
 636         // regular expressions
 637         bool bUseRegExp = obNeedsRegExp.get( false );
 638         aDescProps.setProperty( PROP_UseRegularExpressions, bUseRegExp );
 639     }
 640 }
 641
 642 AutoFilterBuffer::AutoFilterBuffer( const WorkbookHelper& rHelper ) :
 643     WorkbookHelper( rHelper )
 644 {
 645 }
 646
 647 AutoFilter& AutoFilterBuffer::createAutoFilter()
 648 {
 649     AutoFilterVector::value_type xAutoFilter( new AutoFilter( *this ) );
 650     maAutoFilters.push_back( xAutoFilter );
 651     return *xAutoFilter;
 652 }
 653
 654 void AutoFilterBuffer::finalizeImport( sal_Int16 nSheet )
 655 {
 656     // rely on existence of the defined name '_FilterDatabase' containing the range address of the filtered area
 657     if( const DefinedName* pFilterDBName = getDefinedNames().getByBuiltinId( BIFF_DEFNAME_FILTERDATABASE, nSheet ).get() )
 658     {
 659         ScRange aFilterRange;
 660         if( pFilterDBName->getAbsoluteRange( aFilterRange ) && (aFilterRange.aStart.Tab() == nSheet) )
 661         {
 662             // use the same name for the database range as used for the defined name '_FilterDatabase'
 663             Reference< XDatabaseRange > xDatabaseRange = createUnnamedDatabaseRangeObject( aFilterRange );
 664             // first, try to create an auto filter
 665             bool bHasAutoFilter = finalizeImport( xDatabaseRange );
 666             // no success: try to create an advanced filter
 667             if( !bHasAutoFilter && xDatabaseRange.is() )
 668             {
 669                 // the built-in defined name 'Criteria' must exist
 670                 if( const DefinedName* pCriteriaName = getDefinedNames().getByBuiltinId( BIFF_DEFNAME_CRITERIA, nSheet ).get() )
 671                 {
 672                     ScRange aCriteriaRange;
 673                     if( pCriteriaName->getAbsoluteRange( aCriteriaRange ) )
 674                     {
 675                         // set some common properties for the filter descriptor
 676                         PropertySet aDescProps( xDatabaseRange->getFilterDescriptor() );
 677                         aDescProps.setProperty( PROP_IsCaseSensitive, false );
 678                         aDescProps.setProperty( PROP_SkipDuplicates, false );
 679                         aDescProps.setProperty( PROP_Orientation, TableOrientation_ROWS );
 680                         aDescProps.setProperty( PROP_ContainsHeader, true );
 681                         // criteria range may contain wildcards, but these are incompatible with REs
 682                         aDescProps.setProperty( PROP_UseRegularExpressions, false );
 683
 684                         // position of output data (if built-in defined name 'Extract' exists)
 685                         DefinedNameRef xExtractName = getDefinedNames().getByBuiltinId( BIFF_DEFNAME_EXTRACT, nSheet );
 686                         ScRange aOutputRange;
 687                         bool bHasOutputRange = xExtractName.get() && xExtractName->getAbsoluteRange( aOutputRange );
 688                         aDescProps.setProperty( PROP_CopyOutputData, bHasOutputRange );
 689                         if( bHasOutputRange )
 690                         {
 691                             aDescProps.setProperty( PROP_SaveOutputPosition, true );
 692                             aDescProps.setProperty( PROP_OutputPosition, CellAddress( aOutputRange.aStart.Tab(), aOutputRange.aStart.Col(), aOutputRange.aStart.Row() ) );
 693                         }
 694
 695                         /*  Properties of the database range (must be set after
 696                             modifying properties of the filter descriptor,
 697                             otherwise the 'FilterCriteriaSource' property gets
 698                             deleted). */
 699                         PropertySet aRangeProps( xDatabaseRange );
 700                         aRangeProps.setProperty( PROP_AutoFilter, false );
 701                         aRangeProps.setProperty( PROP_FilterCriteriaSource,
 702                                                  CellRangeAddress( aCriteriaRange.aStart.Tab(),
 703                                                                    aCriteriaRange.aStart.Col(), aCriteriaRange.aStart.Row(),
 704                                                                    aCriteriaRange.aEnd.Col(), aCriteriaRange.aEnd.Row() ));
 705                     }
 706                 }
 707             }
 708         }
 709     }
 710 }
 711
 712 bool AutoFilterBuffer::finalizeImport( const Reference< XDatabaseRange >& rxDatabaseRange )
 713 {
 714     AutoFilter* pAutoFilter = getActiveAutoFilter();
 715     if( pAutoFilter && rxDatabaseRange.is() ) try
 716     {
 717         // the property 'AutoFilter' enables the drop-down buttons
 718         PropertySet aRangeProps( rxDatabaseRange );
 719         aRangeProps.setProperty( PROP_AutoFilter, true );
 720         // convert filter settings using the filter descriptor of the database range
 721         Reference< XSheetFilterDescriptor3 > xFilterDesc( rxDatabaseRange->getFilterDescriptor(), UNO_QUERY_THROW );
 722         pAutoFilter->finalizeImport( xFilterDesc );
 723         // return true to indicate enabled autofilter
 724         return true;
 725     }
 726     catch( Exception& )
 727     {
 728     }
 729     return false;
 730 }
 731
 732 AutoFilter* AutoFilterBuffer::getActiveAutoFilter()
 733 {
 734     // Excel expects not more than one auto filter per sheet or table
 735     OSL_ENSURE( maAutoFilters.size() <= 1, "AutoFilterBuffer::getActiveAutoFilter - too many auto filters" );
 736     // stick to the last imported auto filter
 737     return maAutoFilters.empty() ? nullptr : maAutoFilters.back().get();
 738 }
 739
 740 } // namespace xls
 741 } // namespace oox
 742
 743 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */