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