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/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
  32 #include <comphelper/sequence.hxx>
  33 #include <editeng/brushitem.hxx>
  34 #include <rtl/ustrbuf.hxx>
  35 #include <osl/diagnose.h>
  36 #include <oox/helper/attributelist.hxx>
  37 #include <oox/helper/propertyset.hxx>
  38 #include <oox/helper/binaryinputstream.hxx>
  39 #include <oox/token/namespaces.hxx>
  40 #include <oox/token/properties.hxx>
  41 #include <oox/token/tokens.hxx>
  42 #include <addressconverter.hxx>
  43 #include <defnamesbuffer.hxx>
  44 #include <biffhelper.hxx>
  45 #include <document.hxx>
  46 #include <dbdata.hxx>
  47 #include <scitems.hxx>
  48 #include <sortparam.hxx>
  49 #include <stlpool.hxx>
  50 #include <stlsheet.hxx>
  51 #include <stylesbuffer.hxx>
  52 #include <userlist.hxx>
  53
  54 namespace oox::xls {
  55
  56 using namespace css;
  57 using namespace ::com::sun::star::sheet;
  58 using namespace ::com::sun::star::table;
  59 using namespace ::com::sun::star::uno;
  60
  61 namespace {
  62
  63 const sal_uInt8 BIFF12_TOP10FILTER_TOP              = 0x01;
  64 const sal_uInt8 BIFF12_TOP10FILTER_PERCENT          = 0x02;
  65
  66 const sal_uInt16 BIFF12_FILTERCOLUMN_HIDDENBUTTON   = 0x0001;
  67 const sal_uInt16 BIFF12_FILTERCOLUMN_SHOWBUTTON     = 0x0002;
  68
  69 const sal_uInt8 BIFF_FILTER_DATATYPE_NONE           = 0;
  70 const sal_uInt8 BIFF_FILTER_DATATYPE_DOUBLE         = 4;
  71 const sal_uInt8 BIFF_FILTER_DATATYPE_STRING         = 6;
  72 const sal_uInt8 BIFF_FILTER_DATATYPE_BOOLEAN        = 8;
  73 const sal_uInt8 BIFF_FILTER_DATATYPE_EMPTY          = 12;
  74 const sal_uInt8 BIFF_FILTER_DATATYPE_NOTEMPTY       = 14;
  75
  76 bool lclGetApiOperatorFromToken( sal_Int32& rnApiOperator, sal_Int32 nToken )
  77 {
  78     switch( nToken )
  79     {
  80         case XML_lessThan:              rnApiOperator = FilterOperator2::LESS;          return true;
  81         case XML_equal:                 rnApiOperator = FilterOperator2::EQUAL;         return true;
  82         case XML_lessThanOrEqual:       rnApiOperator = FilterOperator2::LESS_EQUAL;    return true;
  83         case XML_greaterThan:           rnApiOperator = FilterOperator2::GREATER;       return true;
  84         case XML_notEqual:              rnApiOperator = FilterOperator2::NOT_EQUAL;     return true;
  85         case XML_greaterThanOrEqual:    rnApiOperator = FilterOperator2::GREATER_EQUAL; return true;
  86     }
  87     return false;
  88 }
  89
  90 /** Removes leading asterisk characters from the passed string.
  91     @return  True = at least one asterisk character has been removed. */
  92 bool lclTrimLeadingAsterisks( OUString& rValue )
  93 {
  94     sal_Int32 nLength = rValue.getLength();
  95     sal_Int32 nPos = 0;
  96     while( (nPos < nLength) && (rValue[ nPos ] == '*') )
  97         ++nPos;
  98     if( nPos > 0 )
  99     {
 100         rValue = rValue.copy( nPos );
 101         return true;
 102     }
 103     return false;
 104 }
 105
 106 /** Removes trailing asterisk characters from the passed string.
 107     @return  True = at least one asterisk character has been removed. */
 108 bool lclTrimTrailingAsterisks( OUString& rValue )
 109 {
 110     sal_Int32 nLength = rValue.getLength();
 111     sal_Int32 nPos = nLength;
 112     while( (nPos > 0) && (rValue[ nPos - 1 ] == '*') )
 113         --nPos;
 114     if( nPos < nLength )
 115     {
 116         rValue = rValue.copy( 0, nPos );
 117         return true;
 118     }
 119     return false;
 120 }
 121
 122 /** Converts wildcard characters '*' and '?' to regular expressions and quotes
 123     RE meta characters.
 124     @return  True = passed string has been changed (RE needs to be enabled). */
 125 bool lclConvertWildcardsToRegExp( OUString& rValue )
 126 {
 127     // check existence of the wildcard characters '*' and '?'
 128     if( !rValue.isEmpty() && ((rValue.indexOf( '*' ) >= 0) || (rValue.indexOf( '?' ) >= 0)) )
 129     {
 130         OUStringBuffer aBuffer;
 131         aBuffer.ensureCapacity( rValue.getLength() + 5 );
 132         const sal_Unicode* pcChar = rValue.getStr();
 133         const sal_Unicode* pcEnd = pcChar + rValue.getLength();
 134         for( ; pcChar < pcEnd; ++pcChar )
 135         {
 136             switch( *pcChar )
 137             {
 138                 case '?':
 139                     aBuffer.append( '.' );
 140                 break;
 141                 case '*':
 142                     aBuffer.append( ".*" );
 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
 160 ApiFilterSettings::ApiFilterSettings()
 161 {
 162 }
 163
 164 void ApiFilterSettings::appendField( bool bAnd, sal_Int32 nOperator, double fValue )
 165 {
 166     maFilterFields.emplace_back();
 167     TableFilterField3& rFilterField = maFilterFields.back();
 168     rFilterField.Connection = bAnd ? FilterConnection_AND : FilterConnection_OR;
 169     rFilterField.Operator = nOperator;
 170     rFilterField.Values.realloc(1);
 171     auto pValues = rFilterField.Values.getArray();
 172     pValues[0].FilterType = FilterFieldType::NUMERIC;
 173     pValues[0].NumericValue = fValue;
 174 }
 175
 176 void 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
 188 void 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
 201 void 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
 219 FilterSettingsBase::FilterSettingsBase( const WorkbookHelper& rHelper ) :
 220     WorkbookHelper( rHelper )
 221 {
 222 }
 223
 224 void FilterSettingsBase::importAttribs( sal_Int32 /*nElement*/, const AttributeList& /*rAttribs*/ )
 225 {
 226 }
 227
 228 void FilterSettingsBase::importRecord( sal_Int32 /*nRecId*/, SequenceInputStream& /*rStrm*/ )
 229 {
 230 }
 231
 232 ApiFilterSettings FilterSettingsBase::finalizeImport()
 233 {
 234     return ApiFilterSettings();
 235 }
 236
 237 DiscreteFilter::DiscreteFilter( const WorkbookHelper& rHelper ) :
 238     FilterSettingsBase( rHelper ),
 239     mnCalendarType( XML_none ),
 240     mbShowBlank( false )
 241 {
 242 }
 243
 244 void 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
 321 void DiscreteFilter::importRecord( sal_Int32 nRecId, SequenceInputStream& rStrm )
 322 {
 323     switch( nRecId )
 324     {
 325         case BIFF12_ID_DISCRETEFILTERS:
 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
 339         case BIFF12_ID_DISCRETEFILTER:
 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
 349 ApiFilterSettings DiscreteFilter::finalizeImport()
 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
 369 Top10Filter::Top10Filter( const WorkbookHelper& rHelper ) :
 370     FilterSettingsBase( rHelper ),
 371     mfValue( 0.0 ),
 372     mbTop( true ),
 373     mbPercent( false )
 374 {
 375 }
 376
 377 void 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
 387 void 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
 399 ApiFilterSettings Top10Filter::finalizeImport()
 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
 409 ColorFilter::ColorFilter(const WorkbookHelper& rHelper)
 410     : FilterSettingsBase(rHelper)
 411     , mbIsBackgroundColor(false)
 412 {
 413 }
 414
 415 void 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
 427 void ColorFilter::importRecord(sal_Int32 /* nRecId */, SequenceInputStream& /* rStrm */)
 428 {
 429     // TODO
 430 }
 431
 432 ApiFilterSettings ColorFilter::finalizeImport()
 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
 452 FilterCriterionModel::FilterCriterionModel() :
 453     mnOperator( XML_equal ),
 454     mnDataType( BIFF_FILTER_DATATYPE_NONE )
 455 {
 456 }
 457
 458 void FilterCriterionModel::setBiffOperator( sal_uInt8 nOperator )
 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
 465 void FilterCriterionModel::readBiffData( SequenceInputStream& rStrm )
 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
 505 CustomFilter::CustomFilter( const WorkbookHelper& rHelper ) :
 506     FilterSettingsBase( rHelper ),
 507     mbAnd( false )
 508 {
 509 }
 510
 511 void 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
 532 void CustomFilter::importRecord( sal_Int32 nRecId, SequenceInputStream& rStrm )
 533 {
 534     switch( nRecId )
 535     {
 536         case BIFF12_ID_CUSTOMFILTERS:
 537             mbAnd = rStrm.readInt32() == 0;
 538         break;
 539
 540         case BIFF12_ID_CUSTOMFILTER:
 541         {
 542             FilterCriterionModel aCriterion;
 543             aCriterion.readBiffData( rStrm );
 544             appendCriterion( aCriterion );
 545         }
 546         break;
 547     }
 548 }
 549
 550 ApiFilterSettings CustomFilter::finalizeImport()
 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
 617 void CustomFilter::appendCriterion( const FilterCriterionModel& rCriterion )
 618 {
 619     if( (rCriterion.mnOperator != XML_TOKEN_INVALID) && rCriterion.maValue.hasValue() )
 620         maCriteria.push_back( rCriterion );
 621 }
 622
 623 FilterColumn::FilterColumn( const WorkbookHelper& rHelper ) :
 624     WorkbookHelper( rHelper ),
 625     mnColId( -1 ),
 626     mbHiddenButton( false ),
 627     mbShowButton( true )
 628 {
 629 }
 630
 631 void FilterColumn::importFilterColumn( const AttributeList& rAttribs )
 632 {
 633     mnColId = rAttribs.getInteger( XML_colId, -1 );
 634     mbHiddenButton = rAttribs.getBool( XML_hiddenButton, false );
 635     mbShowButton = rAttribs.getBool( XML_showButton, true );
 636 }
 637
 638 void FilterColumn::importFilterColumn( SequenceInputStream& rStrm )
 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
 647 ApiFilterSettings FilterColumn::finalizeImport()
 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
 661 bool FilterColumn::isButtonHidden()
 662 {
 663     return (mbShowButton == false) || (mbHiddenButton == true);
 664 }
 665
 666 // SortCondition
 667
 668 SortCondition::SortCondition( const WorkbookHelper& rHelper ) :
 669     WorkbookHelper( rHelper ),
 670     mbDescending( false )
 671 {
 672 }
 673
 674 void SortCondition::importSortCondition( const AttributeList& rAttribs, sal_Int16 nSheet )
 675 {
 676     OUString aRangeStr = rAttribs.getString( XML_ref, OUString() );
 677     AddressConverter::convertToCellRangeUnchecked(maRange, aRangeStr, nSheet, getScDocument());
 678
 679     maSortCustomList = rAttribs.getString( XML_customList, OUString() );
 680     mbDescending = rAttribs.getBool( XML_descending, false );
 681 }
 682
 683 // AutoFilter
 684
 685 AutoFilter::AutoFilter( const WorkbookHelper& rHelper ) :
 686     WorkbookHelper( rHelper )
 687 {
 688 }
 689
 690 void AutoFilter::importAutoFilter( const AttributeList& rAttribs, sal_Int16 nSheet )
 691 {
 692     OUString aRangeStr = rAttribs.getString( XML_ref, OUString() );
 693     AddressConverter::convertToCellRangeUnchecked(maRange, aRangeStr, nSheet, getScDocument());
 694 }
 695
 696 void AutoFilter::importAutoFilter( SequenceInputStream& rStrm, sal_Int16 nSheet )
 697 {
 698     BinRange aBinRange;
 699     rStrm >> aBinRange;
 700     AddressConverter::convertToCellRangeUnchecked( maRange, aBinRange, nSheet );
 701 }
 702
 703 void AutoFilter::importSortState( const AttributeList& rAttribs, sal_Int16 nSheet )
 704 {
 705     OUString aRangeStr = rAttribs.getString( XML_ref, OUString() );
 706     AddressConverter::convertToCellRangeUnchecked(maSortRange, aRangeStr, nSheet, getScDocument());
 707 }
 708
 709 FilterColumn& AutoFilter::createFilterColumn()
 710 {
 711     FilterColumnVector::value_type xFilterColumn = std::make_shared<FilterColumn>( *this );
 712     maFilterColumns.push_back( xFilterColumn );
 713     return *xFilterColumn;
 714 }
 715
 716 SortCondition& AutoFilter::createSortCondition()
 717 {
 718     SortConditionVector::value_type xSortCondition = std::make_shared<SortCondition>( *this );
 719     maSortConditions.push_back( xSortCondition );
 720     return *xSortCondition;
 721 }
 722
 723 void 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.has_value() || !aSettings.mobNeedsRegExp.has_value() || (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& rUserList = ScGlobal::GetUserList();
 824     if (!rSorConditionLoaded.maSortCustomList.isEmpty())
 825     {
 826         for (size_t i=0; i < rUserList.size(); i++)
 827         {
 828             const OUString aEntry(rUserList[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         rUserList.emplace_back(rSorConditionLoaded.maSortCustomList);
 841         aParam.bUserDef = true;
 842         aParam.nUserIndex = rUserList.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     // descending sort - need to enable 1st SortParam slot
 851     if (rSorConditionLoaded.mbDescending)
 852         assert(aParam.GetSortKeyCount() == DEFSORT);
 853
 854     aParam.maKeyState[0].bDoSort = true;
 855     aParam.maKeyState[0].bAscending = !rSorConditionLoaded.mbDescending;
 856     aParam.maKeyState[0].nField += nStartPos;
 857
 858     ScDBData* pDBData = rDoc.GetDBAtArea(
 859         nSheet,
 860         maRange.aStart.Col(), maRange.aStart.Row(),
 861         maRange.aEnd.Col(), maRange.aEnd.Row());
 862
 863     if (pDBData)
 864         pDBData->SetSortParam(aParam);
 865     else
 866         OSL_FAIL("AutoFilter::finalizeImport(): cannot find matching DBData");
 867 }
 868
 869 AutoFilterBuffer::AutoFilterBuffer( const WorkbookHelper& rHelper ) :
 870     WorkbookHelper( rHelper )
 871 {
 872 }
 873
 874 AutoFilter& AutoFilterBuffer::createAutoFilter()
 875 {
 876     AutoFilterVector::value_type xAutoFilter = std::make_shared<AutoFilter>( *this );
 877     maAutoFilters.push_back( xAutoFilter );
 878     return *xAutoFilter;
 879 }
 880
 881 void AutoFilterBuffer::finalizeImport( sal_Int16 nSheet )
 882 {
 883     // rely on existence of the defined name '_FilterDatabase' containing the range address of the filtered area
 884     const DefinedName* pFilterDBName = getDefinedNames().getByBuiltinId( BIFF_DEFNAME_FILTERDATABASE, nSheet ).get();
 885     if(!pFilterDBName)
 886         return;
 887
 888     ScRange aFilterRange;
 889     if( !(pFilterDBName->getAbsoluteRange( aFilterRange ) && (aFilterRange.aStart.Tab() == nSheet)) )
 890         return;
 891
 892     // use the same name for the database range as used for the defined name '_FilterDatabase'
 893     Reference< XDatabaseRange > xDatabaseRange = createUnnamedDatabaseRangeObject( aFilterRange );
 894     // first, try to create an auto filter
 895     bool bHasAutoFilter = finalizeImport( xDatabaseRange, nSheet );
 896     // no success: try to create an advanced filter
 897     if( bHasAutoFilter || !xDatabaseRange.is() )
 898         return;
 899
 900     // the built-in defined name 'Criteria' must exist
 901     const DefinedName* pCriteriaName = getDefinedNames().getByBuiltinId( BIFF_DEFNAME_CRITERIA, nSheet ).get();
 902     if( !pCriteriaName )
 903         return;
 904
 905     ScRange aCriteriaRange;
 906     if( !pCriteriaName->getAbsoluteRange( aCriteriaRange ) )
 907         return;
 908
 909     // set some common properties for the filter descriptor
 910     PropertySet aDescProps( xDatabaseRange->getFilterDescriptor() );
 911     aDescProps.setProperty( PROP_IsCaseSensitive, false );
 912     aDescProps.setProperty( PROP_SkipDuplicates, false );
 913     aDescProps.setProperty( PROP_Orientation, TableOrientation_ROWS );
 914     aDescProps.setProperty( PROP_ContainsHeader, true );
 915     // criteria range may contain wildcards, but these are incompatible with REs
 916     aDescProps.setProperty( PROP_UseRegularExpressions, false );
 917
 918     // position of output data (if built-in defined name 'Extract' exists)
 919     DefinedNameRef xExtractName = getDefinedNames().getByBuiltinId( BIFF_DEFNAME_EXTRACT, nSheet );
 920     ScRange aOutputRange;
 921     bool bHasOutputRange = xExtractName && xExtractName->getAbsoluteRange( aOutputRange );
 922     aDescProps.setProperty( PROP_CopyOutputData, bHasOutputRange );
 923     if( bHasOutputRange )
 924     {
 925         aDescProps.setProperty( PROP_SaveOutputPosition, true );
 926         aDescProps.setProperty( PROP_OutputPosition, CellAddress( aOutputRange.aStart.Tab(), aOutputRange.aStart.Col(), aOutputRange.aStart.Row() ) );
 927     }
 928
 929     /*  Properties of the database range (must be set after
 930         modifying properties of the filter descriptor,
 931         otherwise the 'FilterCriteriaSource' property gets
 932         deleted). */
 933     PropertySet aRangeProps( xDatabaseRange );
 934     aRangeProps.setProperty( PROP_AutoFilter, false );
 935     aRangeProps.setProperty( PROP_FilterCriteriaSource,
 936                              CellRangeAddress( aCriteriaRange.aStart.Tab(),
 937                                                aCriteriaRange.aStart.Col(), aCriteriaRange.aStart.Row(),
 938                                                aCriteriaRange.aEnd.Col(), aCriteriaRange.aEnd.Row() ));
 939 }
 940
 941 bool AutoFilterBuffer::finalizeImport( const Reference< XDatabaseRange >& rxDatabaseRange, sal_Int16 nSheet )
 942 {
 943     AutoFilter* pAutoFilter = getActiveAutoFilter();
 944     if( pAutoFilter && rxDatabaseRange.is() ) try
 945     {
 946         // the property 'AutoFilter' enables the drop-down buttons
 947         PropertySet aRangeProps( rxDatabaseRange );
 948         aRangeProps.setProperty( PROP_AutoFilter, true );
 949
 950         pAutoFilter->finalizeImport( rxDatabaseRange, nSheet );
 951
 952         // return true to indicate enabled autofilter
 953         return true;
 954     }
 955     catch( Exception& )
 956     {
 957     }
 958     return false;
 959 }
 960
 961 AutoFilter* AutoFilterBuffer::getActiveAutoFilter()
 962 {
 963     // Excel expects not more than one auto filter per sheet or table
 964     OSL_ENSURE( maAutoFilters.size() <= 1, "AutoFilterBuffer::getActiveAutoFilter - too many auto filters" );
 965     // stick to the last imported auto filter
 966     return maAutoFilters.empty() ? nullptr : maAutoFilters.back().get();
 967 }
 968
 969 } // namespace oox
 970
 971 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */