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