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