LibreOffice Module sc (master)  1
autofilterbuffer.cxx
Go to the documentation of this file.
1 /* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
2 /*
3  * This file is part of the LibreOffice project.
4  *
5  * This Source Code Form is subject to the terms of the Mozilla Public
6  * License, v. 2.0. If a copy of the MPL was not distributed with this
7  * file, You can obtain one at http://mozilla.org/MPL/2.0/.
8  *
9  * This file incorporates work covered by the following license notice:
10  *
11  * Licensed to the Apache Software Foundation (ASF) under one or more
12  * contributor license agreements. See the NOTICE file distributed
13  * with this work for additional information regarding copyright
14  * ownership. The ASF licenses this file to you under the Apache
15  * License, Version 2.0 (the "License"); you may not use this file
16  * except in compliance with the License. You may obtain a copy of
17  * the License at http://www.apache.org/licenses/LICENSE-2.0 .
18  */
19 
20 #include <autofilterbuffer.hxx>
21 
22 #include <com/sun/star/beans/XPropertySet.hpp>
23 #include <com/sun/star/sheet/FilterFieldType.hpp>
24 #include <com/sun/star/sheet/FilterConnection.hpp>
25 #include <com/sun/star/sheet/FilterOperator2.hpp>
26 #include <com/sun/star/sheet/TableFilterField3.hpp>
27 #include <com/sun/star/sheet/XDatabaseRange.hpp>
28 #include <com/sun/star/sheet/XSheetFilterDescriptor3.hpp>
29 #include <com/sun/star/table/TableOrientation.hpp>
30 #include <com/sun/star/table/CellAddress.hpp>
31 #include <editeng/colritem.hxx>
32 #include <editeng/brushitem.hxx>
33 #include <rtl/ustrbuf.hxx>
34 #include <osl/diagnose.h>
39 #include <oox/token/namespaces.hxx>
40 #include <oox/token/properties.hxx>
41 #include <oox/token/tokens.hxx>
42 #include <addressconverter.hxx>
43 #include <defnamesbuffer.hxx>
44 #include <biffhelper.hxx>
45 #include <document.hxx>
46 #include <dbdata.hxx>
47 #include <scitems.hxx>
48 #include <sortparam.hxx>
49 #include <stlpool.hxx>
50 #include <stlsheet.hxx>
51 #include <stylesbuffer.hxx>
52 #include <userlist.hxx>
53 
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 
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 
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 
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( '.' ).append( '*' );
143  break;
144  case '\\': case '.': case '|': case '(': case ')': case '^': case '$':
145  // quote RE meta characters
146  aBuffer.append( '\\' ).append( *pcChar );
147  break;
148  default:
149  aBuffer.append( *pcChar );
150  }
151  }
152  rValue = aBuffer.makeStringAndClear();
153  return true;
154  }
155  return false;
156 }
157 
158 } // namespace
159 
161 {
162 }
163 
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  rFilterField.Values[0].FilterType = FilterFieldType::NUMERIC;
172  rFilterField.Values[0].NumericValue = fValue;
173 }
174 
175 void ApiFilterSettings::appendField( bool bAnd, sal_Int32 nOperator, const OUString& rValue )
176 {
177  maFilterFields.emplace_back();
178  TableFilterField3& rFilterField = maFilterFields.back();
179  rFilterField.Connection = bAnd ? FilterConnection_AND : FilterConnection_OR;
180  rFilterField.Operator = nOperator;
181  rFilterField.Values.realloc(1);
182  rFilterField.Values[0].FilterType = FilterFieldType::STRING;
183  rFilterField.Values[0].StringValue = rValue;
184 }
185 
186 void ApiFilterSettings::appendField(bool bAnd, util::Color aColor, bool bIsBackgroundColor)
187 {
188  maFilterFields.emplace_back();
189  TableFilterField3& rFilterField = maFilterFields.back();
190  rFilterField.Connection = bAnd ? FilterConnection_AND : FilterConnection_OR;
191  rFilterField.Operator = FilterOperator2::EQUAL;
192  rFilterField.Values.realloc(1);
193  rFilterField.Values[0].FilterType
194  = bIsBackgroundColor ? FilterFieldType::BACKGROUND_COLOR : FilterFieldType::TEXT_COLOR;
195  rFilterField.Values[0].ColorValue = aColor;
196 }
197 
198 void ApiFilterSettings::appendField( bool bAnd, const std::vector<std::pair<OUString, bool>>& rValues )
199 {
200  maFilterFields.emplace_back();
201  TableFilterField3& rFilterField = maFilterFields.back();
202  rFilterField.Connection = bAnd ? FilterConnection_AND : FilterConnection_OR;
203  rFilterField.Operator = FilterOperator2::EQUAL;
204  rFilterField.Values.realloc(rValues.size());
205  size_t i = 0;
206 
207  for( auto const& it : rValues )
208  {
209  rFilterField.Values[i].StringValue = it.first;
210  rFilterField.Values[i++].FilterType
211  = it.second ? FilterFieldType::DATE : FilterFieldType::STRING;
212  }
213 }
214 
216  WorkbookHelper( rHelper )
217 {
218 }
219 
220 void FilterSettingsBase::importAttribs( sal_Int32 /*nElement*/, const AttributeList& /*rAttribs*/ )
221 {
222 }
223 
224 void FilterSettingsBase::importRecord( sal_Int32 /*nRecId*/, SequenceInputStream& /*rStrm*/ )
225 {
226 }
227 
229 {
230  return ApiFilterSettings();
231 }
232 
234  FilterSettingsBase( rHelper ),
235  mnCalendarType( XML_none ),
236  mbShowBlank( false )
237 {
238 }
239 
240 void DiscreteFilter::importAttribs( sal_Int32 nElement, const AttributeList& rAttribs )
241 {
242  switch( nElement )
243  {
244  case XLS_TOKEN( filters ):
245  mnCalendarType = rAttribs.getToken( XML_calendarType, XML_none );
246  mbShowBlank = rAttribs.getBool( XML_blank, false );
247  break;
248 
249  case XLS_TOKEN( filter ):
250  {
251  OUString aValue = rAttribs.getXString( XML_val, OUString() );
252  if( !aValue.isEmpty() )
253  maValues.push_back( std::make_pair(aValue, false) );
254  }
255  break;
256 
257  case XLS_TOKEN( dateGroupItem ):
258  {
259  OUString aDateValue;
260  // it is just a fallback, we do not need the XML_day as default value,
261  // because if the dateGroupItem exists also XML_dateTimeGrouping exists!
262  sal_uInt16 nToken = rAttribs.getToken(XML_dateTimeGrouping, XML_day);
263  if( nToken == XML_year || nToken == XML_month || nToken == XML_day ||
264  nToken == XML_hour || nToken == XML_minute || nToken == XML_second )
265  {
266  aDateValue = rAttribs.getString(XML_year, OUString());
267 
268  if( nToken == XML_month || nToken == XML_day || nToken == XML_hour ||
269  nToken == XML_minute || nToken == XML_second )
270  {
271  OUString aMonthName = rAttribs.getString(XML_month, OUString());
272  if( aMonthName.getLength() == 1 )
273  aMonthName = "0" + aMonthName;
274  aDateValue += "-" + aMonthName;
275 
276  if( nToken == XML_day || nToken == XML_hour || nToken == XML_minute ||
277  nToken == XML_second )
278  {
279  OUString aDayName = rAttribs.getString(XML_day, OUString());
280  if( aDayName.getLength() == 1 )
281  aDayName = "0" + aDayName;
282  aDateValue += "-" + aDayName;
283 
284  if( nToken == XML_hour || nToken == XML_minute || nToken == XML_second )
285  {
286  OUString aHourName = rAttribs.getString(XML_hour, OUString());
287  if( aHourName.getLength() == 1 )
288  aHourName = "0" + aHourName;
289  aDateValue += " " + aHourName;
290 
291  if( nToken == XML_minute || nToken == XML_second )
292  {
293  OUString aMinName = rAttribs.getString(XML_minute, OUString());
294  if( aMinName.getLength() == 1 )
295  aMinName = "0" + aMinName;
296  aDateValue += ":" + aMinName;
297 
298  if( nToken == XML_second )
299  {
300  OUString aSecName = rAttribs.getString(XML_second, OUString());
301  if( aSecName.getLength() == 1 )
302  aSecName = "0" + aSecName;
303  aDateValue += ":" + aSecName;
304  }
305  }
306  }
307  }
308  }
309  }
310  if( !aDateValue.isEmpty() )
311  maValues.push_back( std::make_pair(aDateValue, true) );
312  }
313  break;
314  }
315 }
316 
317 void DiscreteFilter::importRecord( sal_Int32 nRecId, SequenceInputStream& rStrm )
318 {
319  switch( nRecId )
320  {
322  {
323  sal_Int32 nShowBlank, nCalendarType;
324  nShowBlank = rStrm.readInt32();
325  nCalendarType = rStrm.readInt32();
326 
327  static const sal_Int32 spnCalendarTypes[] = {
328  XML_none, XML_gregorian, XML_gregorianUs, XML_japan, XML_taiwan, XML_korea, XML_hijri, XML_thai, XML_hebrew,
329  XML_gregorianMeFrench, XML_gregorianArabic, XML_gregorianXlitEnglish, XML_gregorianXlitFrench };
330  mnCalendarType = STATIC_ARRAY_SELECT( spnCalendarTypes, nCalendarType, XML_none );
331  mbShowBlank = nShowBlank != 0;
332  }
333  break;
334 
336  {
337  OUString aValue = BiffHelper::readString( rStrm );
338  if( !aValue.isEmpty() )
339  maValues.push_back( std::make_pair(aValue, false) );
340  }
341  break;
342  }
343 }
344 
346 {
347  ApiFilterSettings aSettings;
348  aSettings.maFilterFields.reserve( maValues.size() );
349 
350  // insert all filter values
351  aSettings.appendField( true, maValues );
352 
353  // extra field for 'show empty'
354  if( mbShowBlank )
355  aSettings.appendField( false, FilterOperator2::EMPTY, OUString() );
356 
357  /* Require disabled regular expressions, filter entries may contain
358  any RE meta characters. */
359  if( !maValues.empty() )
360  aSettings.mobNeedsRegExp = false;
361 
362  return aSettings;
363 }
364 
366  FilterSettingsBase( rHelper ),
367  mfValue( 0.0 ),
368  mbTop( true ),
369  mbPercent( false )
370 {
371 }
372 
373 void Top10Filter::importAttribs( sal_Int32 nElement, const AttributeList& rAttribs )
374 {
375  if( nElement == XLS_TOKEN( top10 ) )
376  {
377  mfValue = rAttribs.getDouble( XML_val, 0.0 );
378  mbTop = rAttribs.getBool( XML_top, true );
379  mbPercent = rAttribs.getBool( XML_percent, false );
380  }
381 }
382 
383 void Top10Filter::importRecord( sal_Int32 nRecId, SequenceInputStream& rStrm )
384 {
385  if( nRecId == BIFF12_ID_TOP10FILTER )
386  {
387  sal_uInt8 nFlags;
388  nFlags = rStrm.readuChar();
389  mfValue = rStrm.readDouble();
390  mbTop = getFlag( nFlags, BIFF12_TOP10FILTER_TOP );
391  mbPercent = getFlag( nFlags, BIFF12_TOP10FILTER_PERCENT );
392  }
393 }
394 
396 {
397  sal_Int32 nOperator = mbTop ?
398  (mbPercent ? FilterOperator2::TOP_PERCENT : FilterOperator2::TOP_VALUES) :
399  (mbPercent ? FilterOperator2::BOTTOM_PERCENT : FilterOperator2::BOTTOM_VALUES);
400  ApiFilterSettings aSettings;
401  aSettings.appendField( true, nOperator, mfValue );
402  return aSettings;
403 }
404 
406  : FilterSettingsBase(rHelper)
407  , mbIsBackgroundColor(false)
408 {
409 }
410 
411 void ColorFilter::importAttribs(sal_Int32 nElement, const AttributeList& rAttribs)
412 {
413  if (nElement == XLS_TOKEN(colorFilter))
414  {
415  // When cellColor attribute not found, it means cellColor = true
416  // cellColor = 0 (false) -> TextColor
417  // cellColor = 1 (true) -> BackgroundColor
418  mbIsBackgroundColor = rAttribs.getBool(XML_cellColor, true);
419  msStyleName = getStyles().createDxfStyle( rAttribs.getInteger(XML_dxfId, -1) );
420  }
421 }
422 
423 void ColorFilter::importRecord(sal_Int32 /* nRecId */, SequenceInputStream& /* rStrm */)
424 {
425  // TODO
426 }
427 
429 {
430  ApiFilterSettings aSettings;
431  ScDocument& rDoc = getScDocument();
432  ScStyleSheet* pStyleSheet = static_cast<ScStyleSheet*>(
433  rDoc.GetStyleSheetPool()->Find(msStyleName, SfxStyleFamily::Para));
434  if (!pStyleSheet)
435  return aSettings;
436 
437  const SfxItemSet& rItemSet = pStyleSheet->GetItemSet();
438  ::Color aColor;
440  {
441  const SvxBrushItem* pItem = rItemSet.GetItem<SvxBrushItem>(ATTR_BACKGROUND);
442  aColor = pItem->GetColor();
443  }
444  else
445  {
446  const SvxColorItem* pItem = rItemSet.GetItem<SvxColorItem>(ATTR_FONT_COLOR);
447  aColor = pItem->GetValue();
448  }
449  util::Color nColor(aColor);
450  aSettings.appendField(true, nColor, mbIsBackgroundColor);
451  return aSettings;
452 }
453 
455  mnOperator( XML_equal ),
456  mnDataType( BIFF_FILTER_DATATYPE_NONE )
457 {
458 }
459 
461 {
462  static const sal_Int32 spnOperators[] = { XML_TOKEN_INVALID,
463  XML_lessThan, XML_equal, XML_lessThanOrEqual, XML_greaterThan, XML_notEqual, XML_greaterThanOrEqual };
464  mnOperator = STATIC_ARRAY_SELECT( spnOperators, nOperator, XML_TOKEN_INVALID );
465 }
466 
468 {
469  sal_uInt8 nOperator;
470  mnDataType = rStrm.readuChar();
471  nOperator = rStrm.readuChar();
472  setBiffOperator( nOperator );
473 
474  switch( mnDataType )
475  {
476  case BIFF_FILTER_DATATYPE_DOUBLE:
477  maValue <<= rStrm.readDouble();
478  break;
479  case BIFF_FILTER_DATATYPE_STRING:
480  {
481  rStrm.skip( 8 );
482  OUString aValue = BiffHelper::readString( rStrm ).trim();
483  if( !aValue.isEmpty() )
484  maValue <<= aValue;
485  }
486  break;
487  case BIFF_FILTER_DATATYPE_BOOLEAN:
488  maValue <<= (rStrm.readuInt8() != 0);
489  rStrm.skip( 7 );
490  break;
491  case BIFF_FILTER_DATATYPE_EMPTY:
492  rStrm.skip( 8 );
493  if( mnOperator == XML_equal )
494  maValue <<= OUString();
495  break;
496  case BIFF_FILTER_DATATYPE_NOTEMPTY:
497  rStrm.skip( 8 );
498  if( mnOperator == XML_notEqual )
499  maValue <<= OUString();
500  break;
501  default:
502  OSL_ENSURE( false, "FilterCriterionModel::readBiffData - unexpected data type" );
503  rStrm.skip( 8 );
504  }
505 }
506 
508  FilterSettingsBase( rHelper ),
509  mbAnd( false )
510 {
511 }
512 
513 void CustomFilter::importAttribs( sal_Int32 nElement, const AttributeList& rAttribs )
514 {
515  switch( nElement )
516  {
517  case XLS_TOKEN( customFilters ):
518  mbAnd = rAttribs.getBool( XML_and, false );
519  break;
520 
521  case XLS_TOKEN( customFilter ):
522  {
523  FilterCriterionModel aCriterion;
524  aCriterion.mnOperator = rAttribs.getToken( XML_operator, XML_equal );
525  OUString aValue = rAttribs.getXString( XML_val, OUString() ).trim();
526  if( (aCriterion.mnOperator == XML_equal) || (aCriterion.mnOperator == XML_notEqual) || (!aValue.isEmpty()) )
527  aCriterion.maValue <<= aValue;
528  appendCriterion( aCriterion );
529  }
530  break;
531  }
532 }
533 
534 void CustomFilter::importRecord( sal_Int32 nRecId, SequenceInputStream& rStrm )
535 {
536  switch( nRecId )
537  {
539  mbAnd = rStrm.readInt32() == 0;
540  break;
541 
543  {
544  FilterCriterionModel aCriterion;
545  aCriterion.readBiffData( rStrm );
546  appendCriterion( aCriterion );
547  }
548  break;
549  }
550 }
551 
553 {
554  ApiFilterSettings aSettings;
555  OSL_ENSURE( maCriteria.size() <= 2, "CustomFilter::finalizeImport - too many filter criteria" );
556  for( const auto& rCriterion : maCriteria )
557  {
558  // first extract the filter operator
559  sal_Int32 nOperator = 0;
560  bool bValidOperator = lclGetApiOperatorFromToken( nOperator, rCriterion.mnOperator );
561  if( bValidOperator )
562  {
563  if( rCriterion.maValue.has< OUString >() )
564  {
565  // string argument
566  OUString aValue;
567  rCriterion.maValue >>= aValue;
568  // check for 'empty', 'contains', 'begins with', or 'ends with' text filters
569  bool bEqual = nOperator == FilterOperator2::EQUAL;
570  bool bNotEqual = nOperator == FilterOperator2::NOT_EQUAL;
571  if( bEqual || bNotEqual )
572  {
573  if( aValue.isEmpty() )
574  {
575  // empty comparison string: create empty/not empty filters
576  nOperator = bNotEqual ? FilterOperator2::NOT_EMPTY : FilterOperator2::EMPTY;
577  }
578  else
579  {
580  // compare to something: try to find begins/ends/contains
581  bool bHasLeadingAsterisk = lclTrimLeadingAsterisks( aValue );
582  bool bHasTrailingAsterisk = lclTrimTrailingAsterisks( aValue );
583  // just '***' matches everything, do not create a filter field
584  bValidOperator = !aValue.isEmpty();
585  if( bValidOperator )
586  {
587  if( bHasLeadingAsterisk && bHasTrailingAsterisk )
588  nOperator = bNotEqual ? FilterOperator2::DOES_NOT_CONTAIN : FilterOperator2::CONTAINS;
589  else if( bHasLeadingAsterisk )
590  nOperator = bNotEqual ? FilterOperator2::DOES_NOT_END_WITH : FilterOperator2::ENDS_WITH;
591  else if( bHasTrailingAsterisk )
592  nOperator = bNotEqual ? FilterOperator2::DOES_NOT_BEGIN_WITH : FilterOperator2::BEGINS_WITH;
593  // else: no asterisks, stick to equal/not equal
594  }
595  }
596  }
597 
598  if( bValidOperator )
599  {
600  // if wildcards are present, require RE mode, otherwise keep don't care state
601  if( lclConvertWildcardsToRegExp( aValue ) )
602  aSettings.mobNeedsRegExp = true;
603  // create a new UNO API filter field
604  aSettings.appendField( mbAnd, nOperator, aValue );
605  }
606  }
607  else if( rCriterion.maValue.has< double >() )
608  {
609  // floating-point argument
610  double fValue = 0.0;
611  rCriterion.maValue >>= fValue;
612  aSettings.appendField( mbAnd, nOperator, fValue );
613  }
614  }
615  }
616  return aSettings;
617 }
618 
620 {
621  if( (rCriterion.mnOperator != XML_TOKEN_INVALID) && rCriterion.maValue.hasValue() )
622  maCriteria.push_back( rCriterion );
623 }
624 
626  WorkbookHelper( rHelper ),
627  mnColId( -1 ),
628  mbHiddenButton( false ),
629  mbShowButton( true )
630 {
631 }
632 
634 {
635  mnColId = rAttribs.getInteger( XML_colId, -1 );
636  mbHiddenButton = rAttribs.getBool( XML_hiddenButton, false );
637  mbShowButton = rAttribs.getBool( XML_showButton, true );
638 }
639 
641 {
642  sal_uInt16 nFlags;
643  mnColId = rStrm.readInt32();
644  nFlags = rStrm.readuInt16();
645  mbHiddenButton = getFlag( nFlags, BIFF12_FILTERCOLUMN_HIDDENBUTTON );
646  mbShowButton = getFlag( nFlags, BIFF12_FILTERCOLUMN_SHOWBUTTON );
647 }
648 
650 {
651  ApiFilterSettings aSettings;
652  if( (0 <= mnColId) && mxSettings )
653  {
654  // filter settings object creates a sequence of filter fields
655  aSettings = mxSettings->finalizeImport();
656  // add column index to all filter fields
657  for( auto& rFilterField : aSettings.maFilterFields )
658  rFilterField.Field = mnColId;
659  }
660  return aSettings;
661 }
662 
663 // SortCondition
664 
666  WorkbookHelper( rHelper ),
667  mbDescending( false )
668 {
669 }
670 
671 void SortCondition::importSortCondition( const AttributeList& rAttribs, sal_Int16 nSheet )
672 {
673  OUString aRangeStr = rAttribs.getString( XML_ref, OUString() );
675 
676  maSortCustomList = rAttribs.getString( XML_customList, OUString() );
677  mbDescending = rAttribs.getBool( XML_descending, false );
678 }
679 
680 // AutoFilter
681 
683  WorkbookHelper( rHelper )
684 {
685 }
686 
687 void AutoFilter::importAutoFilter( const AttributeList& rAttribs, sal_Int16 nSheet )
688 {
689  OUString aRangeStr = rAttribs.getString( XML_ref, OUString() );
691 }
692 
693 void AutoFilter::importAutoFilter( SequenceInputStream& rStrm, sal_Int16 nSheet )
694 {
695  BinRange aBinRange;
696  rStrm >> aBinRange;
698 }
699 
700 void AutoFilter::importSortState( const AttributeList& rAttribs, sal_Int16 nSheet )
701 {
702  OUString aRangeStr = rAttribs.getString( XML_ref, OUString() );
704 }
705 
707 {
708  FilterColumnVector::value_type xFilterColumn = std::make_shared<FilterColumn>( *this );
709  maFilterColumns.push_back( xFilterColumn );
710  return *xFilterColumn;
711 }
712 
714 {
715  SortConditionVector::value_type xSortCondition = std::make_shared<SortCondition>( *this );
716  maSortConditions.push_back( xSortCondition );
717  return *xSortCondition;
718 }
719 
720 void AutoFilter::finalizeImport( const Reference< XDatabaseRange >& rxDatabaseRange, sal_Int16 nSheet )
721 {
722  // convert filter settings using the filter descriptor of the database range
723  const Reference<XSheetFilterDescriptor3> xFilterDesc( rxDatabaseRange->getFilterDescriptor(), UNO_QUERY_THROW );
724  if( !xFilterDesc.is() )
725  return;
726 
727  // set some common properties for the auto filter range
728  PropertySet aDescProps( xFilterDesc );
729  aDescProps.setProperty( PROP_IsCaseSensitive, false );
730  aDescProps.setProperty( PROP_SkipDuplicates, false );
731  aDescProps.setProperty( PROP_Orientation, TableOrientation_ROWS );
732  aDescProps.setProperty( PROP_ContainsHeader, true );
733  aDescProps.setProperty( PROP_CopyOutputData, false );
734 
735  // resulting list of all UNO API filter fields
736  ::std::vector<TableFilterField3> aFilterFields;
737 
738  // track if columns require to enable or disable regular expressions
739  OptValue< bool > obNeedsRegExp;
740 
741  /* Track whether the filter fields of the first filter column are
742  connected with 'or'. In this case, other filter fields cannot be
743  inserted without altering the result of the entire filter, due to
744  Calc's precedence for the 'and' connection operator. Example:
745  Excel's filter conditions 'A1 and (B1 or B2) and C1' where B1 and
746  B2 belong to filter column B, will be evaluated by Calc as
747  '(A1 and B1) or (B2 and C1)'. */
748  bool bHasOrConnection = false;
749 
750  // process all filter column objects, exit when 'or' connection exists
751  for( const auto& rxFilterColumn : maFilterColumns )
752  {
753  // the filter settings object creates a list of filter fields
754  ApiFilterSettings aSettings = rxFilterColumn->finalizeImport();
755  ApiFilterSettings::FilterFieldVector& rColumnFields = aSettings.maFilterFields;
756 
757  /* Check whether mode for regular expressions is compatible with
758  the global mode in obNeedsRegExp. If either one is still in
759  don't-care state, all is fine. If both are set, they must be
760  equal. */
761  bool bRegExpCompatible = !obNeedsRegExp || !aSettings.mobNeedsRegExp || (obNeedsRegExp.get() == aSettings.mobNeedsRegExp.get());
762 
763  // check whether fields are connected by 'or' (see comments above).
764  if( rColumnFields.size() >= 2 )
765  bHasOrConnection = std::any_of(rColumnFields.begin() + 1, rColumnFields.end(),
766  [](const css::sheet::TableFilterField3& rColumnField) { return rColumnField.Connection == FilterConnection_OR; });
767 
768  /* Skip the column filter, if no filter fields have been created,
769  and if the mode for regular expressions of the
770  filter column does not fit. */
771  if( !rColumnFields.empty() && bRegExpCompatible )
772  {
773  /* Add 'and' connection to the first filter field to connect
774  it to the existing filter fields of other columns. */
775  rColumnFields[ 0 ].Connection = FilterConnection_AND;
776 
777  // insert the new filter fields
778  aFilterFields.insert( aFilterFields.end(), rColumnFields.begin(), rColumnFields.end() );
779 
780  // update the regular expressions mode
781  obNeedsRegExp.assignIfUsed( aSettings.mobNeedsRegExp );
782  }
783 
784  if( bHasOrConnection )
785  break;
786  }
787 
788  // insert all filter fields to the filter descriptor
789  if( !aFilterFields.empty() )
790  xFilterDesc->setFilterFields3( ContainerHelper::vectorToSequence( aFilterFields ) );
791 
792  // regular expressions
793  bool bUseRegExp = obNeedsRegExp.get( false );
794  aDescProps.setProperty( PROP_UseRegularExpressions, bUseRegExp );
795 
796  // sort
797  if (maSortConditions.empty())
798  return;
799 
800  const SortConditionVector::value_type& xSortConditionPointer = *maSortConditions.begin();
801  const SortCondition& rSorConditionLoaded = *xSortConditionPointer;
802 
803  ScSortParam aParam;
804  aParam.bUserDef = false;
805  aParam.nUserIndex = 0;
806  aParam.bByRow = false;
807 
808  ScUserList* pUserList = ScGlobal::GetUserList();
809  if (!rSorConditionLoaded.maSortCustomList.isEmpty())
810  {
811  for (size_t i=0; pUserList && i < pUserList->size(); i++)
812  {
813  const OUString aEntry((*pUserList)[i].GetString());
814  if (aEntry.equalsIgnoreAsciiCase(rSorConditionLoaded.maSortCustomList))
815  {
816  aParam.bUserDef = true;
817  aParam.nUserIndex = i;
818  break;
819  }
820  }
821  }
822 
823  if (!aParam.bUserDef)
824  {
825  pUserList->push_back(new ScUserListData(rSorConditionLoaded.maSortCustomList));
826  aParam.bUserDef = true;
827  aParam.nUserIndex = pUserList->size()-1;
828  }
829 
830  // set sort parameter if we have detected it
831  if (aParam.bUserDef)
832  {
833  SCCOLROW nStartPos = aParam.bByRow ? maRange.aStart.Col() : maRange.aStart.Row();
834  if (rSorConditionLoaded.mbDescending)
835  {
836  // descending sort - need to enable 1st SortParam slot
837  assert(aParam.GetSortKeyCount() == DEFSORT);
838 
839  aParam.maKeyState[0].bDoSort = true;
840  aParam.maKeyState[0].bAscending = false;
841  aParam.maKeyState[0].nField += nStartPos;
842  }
843 
844  ScDocument& rDoc = getScDocument();
845  ScDBData* pDBData = rDoc.GetDBAtArea(
846  nSheet,
848  maRange.aEnd.Col(), maRange.aEnd.Row());
849 
850  if (pDBData)
851  pDBData->SetSortParam(aParam);
852  else
853  OSL_FAIL("AutoFilter::finalizeImport(): cannot find matching DBData");
854  }
855 }
856 
858  WorkbookHelper( rHelper )
859 {
860 }
861 
863 {
864  AutoFilterVector::value_type xAutoFilter = std::make_shared<AutoFilter>( *this );
865  maAutoFilters.push_back( xAutoFilter );
866  return *xAutoFilter;
867 }
868 
869 void AutoFilterBuffer::finalizeImport( sal_Int16 nSheet )
870 {
871  // rely on existence of the defined name '_FilterDatabase' containing the range address of the filtered area
872  const DefinedName* pFilterDBName = getDefinedNames().getByBuiltinId( BIFF_DEFNAME_FILTERDATABASE, nSheet ).get();
873  if(!pFilterDBName)
874  return;
875 
876  ScRange aFilterRange;
877  if( !(pFilterDBName->getAbsoluteRange( aFilterRange ) && (aFilterRange.aStart.Tab() == nSheet)) )
878  return;
879 
880  // use the same name for the database range as used for the defined name '_FilterDatabase'
881  Reference< XDatabaseRange > xDatabaseRange = createUnnamedDatabaseRangeObject( aFilterRange );
882  // first, try to create an auto filter
883  bool bHasAutoFilter = finalizeImport( xDatabaseRange, nSheet );
884  // no success: try to create an advanced filter
885  if( bHasAutoFilter || !xDatabaseRange.is() )
886  return;
887 
888  // the built-in defined name 'Criteria' must exist
889  const DefinedName* pCriteriaName = getDefinedNames().getByBuiltinId( BIFF_DEFNAME_CRITERIA, nSheet ).get();
890  if( !pCriteriaName )
891  return;
892 
893  ScRange aCriteriaRange;
894  if( !pCriteriaName->getAbsoluteRange( aCriteriaRange ) )
895  return;
896 
897  // set some common properties for the filter descriptor
898  PropertySet aDescProps( xDatabaseRange->getFilterDescriptor() );
899  aDescProps.setProperty( PROP_IsCaseSensitive, false );
900  aDescProps.setProperty( PROP_SkipDuplicates, false );
901  aDescProps.setProperty( PROP_Orientation, TableOrientation_ROWS );
902  aDescProps.setProperty( PROP_ContainsHeader, true );
903  // criteria range may contain wildcards, but these are incompatible with REs
904  aDescProps.setProperty( PROP_UseRegularExpressions, false );
905 
906  // position of output data (if built-in defined name 'Extract' exists)
908  ScRange aOutputRange;
909  bool bHasOutputRange = xExtractName && xExtractName->getAbsoluteRange( aOutputRange );
910  aDescProps.setProperty( PROP_CopyOutputData, bHasOutputRange );
911  if( bHasOutputRange )
912  {
913  aDescProps.setProperty( PROP_SaveOutputPosition, true );
914  aDescProps.setProperty( PROP_OutputPosition, CellAddress( aOutputRange.aStart.Tab(), aOutputRange.aStart.Col(), aOutputRange.aStart.Row() ) );
915  }
916 
917  /* Properties of the database range (must be set after
918  modifying properties of the filter descriptor,
919  otherwise the 'FilterCriteriaSource' property gets
920  deleted). */
921  PropertySet aRangeProps( xDatabaseRange );
922  aRangeProps.setProperty( PROP_AutoFilter, false );
923  aRangeProps.setProperty( PROP_FilterCriteriaSource,
924  CellRangeAddress( aCriteriaRange.aStart.Tab(),
925  aCriteriaRange.aStart.Col(), aCriteriaRange.aStart.Row(),
926  aCriteriaRange.aEnd.Col(), aCriteriaRange.aEnd.Row() ));
927 }
928 
929 bool AutoFilterBuffer::finalizeImport( const Reference< XDatabaseRange >& rxDatabaseRange, sal_Int16 nSheet )
930 {
931  AutoFilter* pAutoFilter = getActiveAutoFilter();
932  if( pAutoFilter && rxDatabaseRange.is() ) try
933  {
934  // the property 'AutoFilter' enables the drop-down buttons
935  PropertySet aRangeProps( rxDatabaseRange );
936  aRangeProps.setProperty( PROP_AutoFilter, true );
937 
938  pAutoFilter->finalizeImport( rxDatabaseRange, nSheet );
939 
940  // return true to indicate enabled autofilter
941  return true;
942  }
943  catch( Exception& )
944  {
945  }
946  return false;
947 }
948 
950 {
951  // Excel expects not more than one auto filter per sheet or table
952  OSL_ENSURE( maAutoFilters.size() <= 1, "AutoFilterBuffer::getActiveAutoFilter - too many auto filters" );
953  // stick to the last imported auto filter
954  return maAutoFilters.empty() ? nullptr : maAutoFilters.back().get();
955 }
956 
957 } // namespace oox
958 
959 /* 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:413
SCROW Row() const
Definition: address.hxx:261
virtual void importRecord(sal_Int32 nRecId, SequenceInputStream &rStrm) override
Imports filter settings from the FILTERS and FILTER records.
virtual ApiFilterSettings finalizeImport() override
Returns converted UNO API filter settings representing all filter settings.
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
ColorFilter(const WorkbookHelper &rHelper)
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
OUString createDxfStyle(sal_Int32 nDxfId) const
Creates the style sheet described by the DXF with the passed identifier.
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.
StylesBuffer & getStyles() const
Returns all cell formatting objects read from the styles substream.
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.
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)
const Color & GetColor() const
virtual ApiFilterSettings finalizeImport()
Derived classes return converted UNO API filter settings representing all filter settings.
FilterColumn(const WorkbookHelper &rHelper)
virtual void importAttribs(sal_Int32 nElement, const AttributeList &rAttribs) override
Imports filter settings from the filters and filter elements.
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
bool mbIsBackgroundColor
Whether we are dealing with the background color (vs. text color)
static OUString readString(SequenceInputStream &rStrm, bool b32BitLen=true)
Reads a BIFF12 string with leading 16-bit or 32-bit length field.
Definition: biffhelper.cxx:80
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.
constexpr TypedWhichId< SvxBrushItem > ATTR_BACKGROUND(148)
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 SC_DLLPUBLIC SfxItemSet & GetItemSet() override
Definition: stlsheet.cxx:127
virtual ApiFilterSettings finalizeImport() override
Returns converted UNO API filter settings representing all filter settings.
SC_DLLPUBLIC ScStyleSheetPool * GetStyleSheetPool() const
Definition: document.cxx:6092
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.
const SfxPoolItem * GetItem(sal_uInt16 nWhich, bool bSearchInParent=true) const
virtual SfxStyleSheetBase * Find(const OUString &, SfxStyleFamily eFam, SfxStyleSearchBits n=SfxStyleSearchBits::All)
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.
constexpr TypedWhichId< SvxColorItem > ATTR_FONT_COLOR(109)
AutoFilterBuffer(const WorkbookHelper &rHelper)
FilterFieldVector maFilterFields
virtual ApiFilterSettings finalizeImport() override
Returns converted UNO API filter settings representing all filter settings.
DefinedNamesBuffer & getDefinedNames() const
Returns the defined names read from the workbook globals.
bool setProperty(sal_Int32 nPropId, const Type &rValue)
const Color & GetValue() const
sal_uInt16 nPos
OptValue< sal_Int32 > getToken(sal_Int32 nAttrToken) const
OUString msStyleName
Style name to retrieve the color from.
std::vector< std::pair< OUString, bool > > maValues