xref: /trunk/main/oox/source/xls/autofilterbuffer.cxx (revision 79aad27f)
1 /**************************************************************
2  *
3  * Licensed to the Apache Software Foundation (ASF) under one
4  * or more contributor license agreements.  See the NOTICE file
5  * distributed with this work for additional information
6  * regarding copyright ownership.  The ASF licenses this file
7  * to you under the Apache License, Version 2.0 (the
8  * "License"); you may not use this file except in compliance
9  * with the License.  You may obtain a copy of the License at
10  *
11  *   http://www.apache.org/licenses/LICENSE-2.0
12  *
13  * Unless required by applicable law or agreed to in writing,
14  * software distributed under the License is distributed on an
15  * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
16  * KIND, either express or implied.  See the License for the
17  * specific language governing permissions and limitations
18  * under the License.
19  *
20  *************************************************************/
21 
22 
23 
24 #include "oox/xls/autofilterbuffer.hxx"
25 
26 #include <com/sun/star/sheet/FilterConnection.hpp>
27 #include <com/sun/star/sheet/FilterOperator2.hpp>
28 #include <com/sun/star/sheet/TableFilterField2.hpp>
29 #include <com/sun/star/sheet/XDatabaseRange.hpp>
30 #include <com/sun/star/sheet/XSheetFilterDescriptor2.hpp>
31 #include <com/sun/star/table/TableOrientation.hpp>
32 #include <rtl/ustrbuf.hxx>
33 #include "oox/helper/attributelist.hxx"
34 #include "oox/helper/containerhelper.hxx"
35 #include "oox/helper/propertyset.hxx"
36 #include "oox/xls/addressconverter.hxx"
37 #include "oox/xls/biffinputstream.hxx"
38 #include "oox/xls/defnamesbuffer.hxx"
39 
40 namespace oox {
41 namespace xls {
42 
43 using namespace ::com::sun::star::sheet;
44 using namespace ::com::sun::star::table;
45 using namespace ::com::sun::star::uno;
46 
47 using ::rtl::OUString;
48 using ::rtl::OUStringBuffer;
49 
50 // ============================================================================
51 
52 namespace {
53 
54 const sal_uInt8 BIFF12_TOP10FILTER_TOP              = 0x01;
55 const sal_uInt8 BIFF12_TOP10FILTER_PERCENT          = 0x02;
56 
57 const sal_uInt16 BIFF12_FILTERCOLUMN_HIDDENBUTTON   = 0x0001;
58 const sal_uInt16 BIFF12_FILTERCOLUMN_SHOWBUTTON     = 0x0002;
59 
60 const sal_uInt16 BIFF_FILTERCOLUMN_OR               = 0x0001;
61 const sal_uInt16 BIFF_FILTERCOLUMN_TOP10FILTER      = 0x0010;
62 const sal_uInt16 BIFF_FILTERCOLUMN_TOP              = 0x0020;
63 const sal_uInt16 BIFF_FILTERCOLUMN_PERCENT          = 0x0040;
64 
65 const sal_uInt8 BIFF_FILTER_DATATYPE_NONE           = 0;
66 const sal_uInt8 BIFF_FILTER_DATATYPE_RK             = 2;
67 const sal_uInt8 BIFF_FILTER_DATATYPE_DOUBLE         = 4;
68 const sal_uInt8 BIFF_FILTER_DATATYPE_STRING         = 6;
69 const sal_uInt8 BIFF_FILTER_DATATYPE_BOOLEAN        = 8;
70 const sal_uInt8 BIFF_FILTER_DATATYPE_EMPTY          = 12;
71 const sal_uInt8 BIFF_FILTER_DATATYPE_NOTEMPTY       = 14;
72 
73 // ----------------------------------------------------------------------------
74 
lclGetApiOperatorFromToken(sal_Int32 & rnApiOperator,sal_Int32 nToken)75 bool lclGetApiOperatorFromToken( sal_Int32& rnApiOperator, sal_Int32 nToken )
76 {
77     switch( nToken )
78     {
79         case XML_lessThan:              rnApiOperator = FilterOperator2::NOT_EQUAL;     return true;
80         case XML_equal:                 rnApiOperator = FilterOperator2::EQUAL;         return true;
81         case XML_lessThanOrEqual:       rnApiOperator = FilterOperator2::LESS_EQUAL;    return true;
82         case XML_greaterThan:           rnApiOperator = FilterOperator2::GREATER;       return true;
83         case XML_notEqual:              rnApiOperator = FilterOperator2::NOT_EQUAL;     return true;
84         case XML_greaterThanOrEqual:    rnApiOperator = FilterOperator2::GREATER_EQUAL; return true;
85     }
86     return false;
87 }
88 
89 /** Removes leading asterisk characters from the passed string.
90     @return  True = at least one asterisk character has been removed. */
lclTrimLeadingAsterisks(OUString & rValue)91 bool lclTrimLeadingAsterisks( OUString& rValue )
92 {
93     sal_Int32 nLength = rValue.getLength();
94     sal_Int32 nPos = 0;
95     while( (nPos < nLength) && (rValue[ nPos ] == '*') )
96         ++nPos;
97     if( nPos > 0 )
98     {
99         rValue = rValue.copy( nPos );
100         return true;
101     }
102     return false;
103 }
104 
105 /** Removes trailing asterisk characters from the passed string.
106     @return  True = at least one asterisk character has been removed. */
lclTrimTrailingAsterisks(OUString & rValue)107 bool lclTrimTrailingAsterisks( OUString& rValue )
108 {
109     sal_Int32 nLength = rValue.getLength();
110     sal_Int32 nPos = nLength;
111     while( (nPos > 0) && (rValue[ nPos - 1 ] == '*') )
112         --nPos;
113     if( nPos < nLength )
114     {
115         rValue = rValue.copy( 0, nPos );
116         return true;
117     }
118     return false;
119 }
120 
121 /** Converts wildcard characters '*' and '?' to regular expressions and quotes
122     RE meta characters.
123     @return  True = passed string has been changed (RE needs to be enabled). */
lclConvertWildcardsToRegExp(OUString & rValue)124 bool lclConvertWildcardsToRegExp( OUString& rValue )
125 {
126     // check existence of the wildcard characters '*' and '?'
127     if( (rValue.getLength() > 0) && ((rValue.indexOf( '*' ) >= 0) || (rValue.indexOf( '?' ) >= 0)) )
128     {
129         OUStringBuffer aBuffer;
130         aBuffer.ensureCapacity( rValue.getLength() + 5 );
131         const sal_Unicode* pcChar = rValue.getStr();
132         const sal_Unicode* pcEnd = pcChar + rValue.getLength();
133         for( ; pcChar < pcEnd; ++pcChar )
134         {
135             switch( *pcChar )
136             {
137                 case '?':
138                     aBuffer.append( sal_Unicode( '.' ) );
139                 break;
140                 case '*':
141                     aBuffer.append( sal_Unicode( '.' ) ).append( sal_Unicode( '*' ) );
142                 break;
143                 case '\\': case '.': case '|': case '(': case ')': case '^': case '$':
144                     // quote RE meta characters
145                     aBuffer.append( sal_Unicode( '\\' ) ).append( *pcChar );
146                 break;
147                 default:
148                     aBuffer.append( *pcChar );
149             }
150         }
151         rValue = aBuffer.makeStringAndClear();
152         return true;
153     }
154     return false;
155 }
156 
157 } // namespace
158 
159 // ============================================================================
160 
ApiFilterSettings()161 ApiFilterSettings::ApiFilterSettings()
162 {
163 }
164 
appendField(bool bAnd,sal_Int32 nOperator,double fValue)165 void ApiFilterSettings::appendField( bool bAnd, sal_Int32 nOperator, double fValue )
166 {
167     maFilterFields.resize( maFilterFields.size() + 1 );
168     TableFilterField2& rFilterField = maFilterFields.back();
169     rFilterField.Connection = bAnd ? FilterConnection_AND : FilterConnection_OR;
170     rFilterField.Operator = nOperator;
171     rFilterField.IsNumeric = sal_True;
172     rFilterField.NumericValue = fValue;
173 }
174 
appendField(bool bAnd,sal_Int32 nOperator,const OUString & rValue)175 void ApiFilterSettings::appendField( bool bAnd, sal_Int32 nOperator, const OUString& rValue )
176 {
177     maFilterFields.resize( maFilterFields.size() + 1 );
178     TableFilterField2& rFilterField = maFilterFields.back();
179     rFilterField.Connection = bAnd ? FilterConnection_AND : FilterConnection_OR;
180     rFilterField.Operator = nOperator;
181     rFilterField.IsNumeric = sal_False;
182     rFilterField.StringValue = rValue;
183 }
184 
185 // ============================================================================
186 
FilterSettingsBase(const WorkbookHelper & rHelper)187 FilterSettingsBase::FilterSettingsBase( const WorkbookHelper& rHelper ) :
188     WorkbookHelper( rHelper )
189 {
190 }
191 
importAttribs(sal_Int32,const AttributeList &)192 void FilterSettingsBase::importAttribs( sal_Int32 /*nElement*/, const AttributeList& /*rAttribs*/ )
193 {
194 }
195 
importRecord(sal_Int32,SequenceInputStream &)196 void FilterSettingsBase::importRecord( sal_Int32 /*nRecId*/, SequenceInputStream& /*rStrm*/ )
197 {
198 }
199 
importBiffRecord(BiffInputStream &,sal_uInt16)200 void FilterSettingsBase::importBiffRecord( BiffInputStream& /*rStrm*/, sal_uInt16 /*nFlags*/ )
201 {
202 }
203 
finalizeImport(sal_Int32)204 ApiFilterSettings FilterSettingsBase::finalizeImport( sal_Int32 /*nMaxCount*/ )
205 {
206     return ApiFilterSettings();
207 }
208 
209 // ============================================================================
210 
DiscreteFilter(const WorkbookHelper & rHelper)211 DiscreteFilter::DiscreteFilter( const WorkbookHelper& rHelper ) :
212     FilterSettingsBase( rHelper ),
213     mnCalendarType( XML_none ),
214     mbShowBlank( false )
215 {
216 }
217 
importAttribs(sal_Int32 nElement,const AttributeList & rAttribs)218 void DiscreteFilter::importAttribs( sal_Int32 nElement, const AttributeList& rAttribs )
219 {
220     switch( nElement )
221     {
222         case XLS_TOKEN( filters ):
223             mnCalendarType = rAttribs.getToken( XML_calendarType, XML_none );
224             mbShowBlank = rAttribs.getBool( XML_blank, false );
225         break;
226 
227         case XLS_TOKEN( filter ):
228         {
229             OUString aValue = rAttribs.getXString( XML_val, OUString() );
230             if( aValue.getLength() > 0 )
231                 maValues.push_back( aValue );
232         }
233         break;
234     }
235 }
236 
importRecord(sal_Int32 nRecId,SequenceInputStream & rStrm)237 void DiscreteFilter::importRecord( sal_Int32 nRecId, SequenceInputStream& rStrm )
238 {
239     switch( nRecId )
240     {
241         case BIFF12_ID_DISCRETEFILTERS:
242         {
243             sal_Int32 nShowBlank, nCalendarType;
244             rStrm >> nShowBlank >> nCalendarType;
245 
246             static const sal_Int32 spnCalendarTypes[] = {
247                 XML_none, XML_gregorian, XML_gregorianUs, XML_japan, XML_taiwan, XML_korea, XML_hijri, XML_thai, XML_hebrew,
248                 XML_gregorianMeFrench, XML_gregorianArabic, XML_gregorianXlitEnglish, XML_gregorianXlitFrench };
249             mnCalendarType = STATIC_ARRAY_SELECT( spnCalendarTypes, nCalendarType, XML_none );
250             mbShowBlank = nShowBlank != 0;
251         }
252         break;
253 
254         case BIFF12_ID_DISCRETEFILTER:
255         {
256             OUString aValue = BiffHelper::readString( rStrm );
257             if( aValue.getLength() > 0 )
258                 maValues.push_back( aValue );
259         }
260         break;
261     }
262 }
263 
finalizeImport(sal_Int32 nMaxCount)264 ApiFilterSettings DiscreteFilter::finalizeImport( sal_Int32 nMaxCount )
265 {
266     ApiFilterSettings aSettings;
267     if( static_cast< sal_Int32 >( maValues.size() ) <= nMaxCount )
268     {
269         aSettings.maFilterFields.reserve( maValues.size() );
270 
271         // insert all filter values
272         for( FilterValueVector::iterator aIt = maValues.begin(), aEnd = maValues.end(); aIt != aEnd; ++aIt )
273             aSettings.appendField( false, FilterOperator2::EQUAL, *aIt );
274 
275         // extra field for 'show empty'
276         if( mbShowBlank )
277             aSettings.appendField( false, FilterOperator2::EMPTY, OUString() );
278 
279         /*  Require disabled regular expressions, filter entries may contain
280             any RE meta characters. */
281         if( !maValues.empty() )
282             aSettings.mobNeedsRegExp = false;
283     }
284     return aSettings;
285 }
286 
287 // ============================================================================
288 
Top10Filter(const WorkbookHelper & rHelper)289 Top10Filter::Top10Filter( const WorkbookHelper& rHelper ) :
290     FilterSettingsBase( rHelper ),
291     mfValue( 0.0 ),
292     mbTop( true ),
293     mbPercent( false )
294 {
295 }
296 
importAttribs(sal_Int32 nElement,const AttributeList & rAttribs)297 void Top10Filter::importAttribs( sal_Int32 nElement, const AttributeList& rAttribs )
298 {
299     if( nElement == XLS_TOKEN( top10 ) )
300     {
301         mfValue = rAttribs.getDouble( XML_val, 0.0 );
302         mbTop = rAttribs.getBool( XML_top, true );
303         mbPercent = rAttribs.getBool( XML_percent, false );
304     }
305 }
306 
importRecord(sal_Int32 nRecId,SequenceInputStream & rStrm)307 void Top10Filter::importRecord( sal_Int32 nRecId, SequenceInputStream& rStrm )
308 {
309     if( nRecId == BIFF12_ID_TOP10FILTER )
310     {
311         sal_uInt8 nFlags;
312         rStrm >> nFlags >> mfValue;
313         mbTop = getFlag( nFlags, BIFF12_TOP10FILTER_TOP );
314         mbPercent = getFlag( nFlags, BIFF12_TOP10FILTER_PERCENT );
315     }
316 }
317 
importBiffRecord(BiffInputStream &,sal_uInt16 nFlags)318 void Top10Filter::importBiffRecord( BiffInputStream& /*rStrm*/, sal_uInt16 nFlags )
319 {
320     mfValue = extractValue< sal_uInt16 >( nFlags, 7, 9 );
321     mbTop = getFlag( nFlags, BIFF_FILTERCOLUMN_TOP );
322     mbPercent = getFlag( nFlags, BIFF_FILTERCOLUMN_PERCENT );
323 }
324 
finalizeImport(sal_Int32)325 ApiFilterSettings Top10Filter::finalizeImport( sal_Int32 /*nMaxCount*/ )
326 {
327     sal_Int32 nOperator = mbTop ?
328         (mbPercent ? FilterOperator2::TOP_PERCENT : FilterOperator2::TOP_VALUES) :
329         (mbPercent ? FilterOperator2::BOTTOM_PERCENT : FilterOperator2::BOTTOM_VALUES);
330     ApiFilterSettings aSettings;
331     aSettings.appendField( true, nOperator, mfValue );
332     return aSettings;
333 }
334 
335 // ============================================================================
336 
FilterCriterionModel()337 FilterCriterionModel::FilterCriterionModel() :
338     mnOperator( XML_equal ),
339     mnDataType( BIFF_FILTER_DATATYPE_NONE ),
340     mnStrLen( 0 )
341 {
342 }
343 
setBiffOperator(sal_uInt8 nOperator)344 void FilterCriterionModel::setBiffOperator( sal_uInt8 nOperator )
345 {
346     static const sal_Int32 spnOperators[] = { XML_TOKEN_INVALID,
347         XML_lessThan, XML_equal, XML_lessThanOrEqual, XML_greaterThan, XML_notEqual, XML_greaterThanOrEqual };
348     mnOperator = STATIC_ARRAY_SELECT( spnOperators, nOperator, XML_TOKEN_INVALID );
349 }
350 
readBiffData(SequenceInputStream & rStrm)351 void FilterCriterionModel::readBiffData( SequenceInputStream& rStrm )
352 {
353     sal_uInt8 nOperator;
354     rStrm >> mnDataType >> nOperator;
355     setBiffOperator( nOperator );
356 
357     switch( mnDataType )
358     {
359         case BIFF_FILTER_DATATYPE_DOUBLE:
360             maValue <<= rStrm.readDouble();
361         break;
362         case BIFF_FILTER_DATATYPE_STRING:
363         {
364             rStrm.skip( 8 );
365             OUString aValue = BiffHelper::readString( rStrm ).trim();
366             if( aValue.getLength() > 0 )
367                 maValue <<= aValue;
368         }
369         break;
370         case BIFF_FILTER_DATATYPE_BOOLEAN:
371             maValue <<= (rStrm.readuInt8() != 0);
372             rStrm.skip( 7 );
373         break;
374         case BIFF_FILTER_DATATYPE_EMPTY:
375             rStrm.skip( 8 );
376             if( mnOperator == XML_equal )
377                 maValue <<= OUString();
378         break;
379         case BIFF_FILTER_DATATYPE_NOTEMPTY:
380             rStrm.skip( 8 );
381             if( mnOperator == XML_notEqual )
382                 maValue <<= OUString();
383         break;
384         default:
385             OSL_ENSURE( false, "FilterCriterionModel::readBiffData - unexpected data type" );
386             rStrm.skip( 8 );
387     }
388 }
389 
readBiffData(BiffInputStream & rStrm)390 void FilterCriterionModel::readBiffData( BiffInputStream& rStrm )
391 {
392     sal_uInt8 nOperator;
393     rStrm >> mnDataType >> nOperator;
394     setBiffOperator( nOperator );
395 
396     switch( mnDataType )
397     {
398         case BIFF_FILTER_DATATYPE_NONE:
399             rStrm.skip( 8 );
400         break;
401         case BIFF_FILTER_DATATYPE_RK:
402             maValue <<= BiffHelper::calcDoubleFromRk( rStrm.readInt32() );
403             rStrm.skip( 4 );
404         break;
405         case BIFF_FILTER_DATATYPE_DOUBLE:
406             maValue <<= rStrm.readDouble();
407         break;
408         case BIFF_FILTER_DATATYPE_STRING:
409             rStrm.skip( 4 );
410             rStrm >> mnStrLen;
411             rStrm.skip( 3 );
412         break;
413         case BIFF_FILTER_DATATYPE_BOOLEAN:
414         {
415             sal_uInt8 nValue, nType;
416             rStrm >> nValue >> nType;
417             rStrm.skip( 6 );
418             switch( nType )
419             {
420                 case BIFF_BOOLERR_BOOL:     maValue <<= (nValue != 0);                              break;
421                 case BIFF_BOOLERR_ERROR:    maValue <<= BiffHelper::calcDoubleFromError( nValue );  break;
422                 default:                    OSL_ENSURE( false, "FilterCriterionModel::readBiffData - unknown type" );
423             }
424         }
425         break;
426         case BIFF_FILTER_DATATYPE_EMPTY:
427             rStrm.skip( 8 );
428             if( mnOperator == XML_equal )
429                 maValue <<= OUString();
430         break;
431         case BIFF_FILTER_DATATYPE_NOTEMPTY:
432             rStrm.skip( 8 );
433             if( mnOperator == XML_notEqual )
434                 maValue <<= OUString();
435         break;
436         default:
437             OSL_ENSURE( false, "FilterCriterionModel::readBiffData - unexpected data type" );
438             rStrm.skip( 8 );
439     }
440 }
441 
readString(BiffInputStream & rStrm,BiffType eBiff,rtl_TextEncoding eTextEnc)442 void FilterCriterionModel::readString( BiffInputStream& rStrm, BiffType eBiff, rtl_TextEncoding eTextEnc )
443 {
444     if( (mnDataType == BIFF_FILTER_DATATYPE_STRING) && (mnStrLen > 0) )
445     {
446         OUString aValue = (eBiff == BIFF8) ?
447             rStrm.readUniStringBody( mnStrLen, true ) :
448             rStrm.readCharArrayUC( mnStrLen, eTextEnc, true );
449         aValue = aValue.trim();
450         if( aValue.getLength() > 0 )
451             maValue <<= aValue;
452     }
453 }
454 
455 // ----------------------------------------------------------------------------
456 
CustomFilter(const WorkbookHelper & rHelper)457 CustomFilter::CustomFilter( const WorkbookHelper& rHelper ) :
458     FilterSettingsBase( rHelper ),
459     mbAnd( false )
460 {
461 }
462 
importAttribs(sal_Int32 nElement,const AttributeList & rAttribs)463 void CustomFilter::importAttribs( sal_Int32 nElement, const AttributeList& rAttribs )
464 {
465     switch( nElement )
466     {
467         case XLS_TOKEN( customFilters ):
468             mbAnd = rAttribs.getBool( XML_and, false );
469         break;
470 
471         case XLS_TOKEN( customFilter ):
472         {
473             FilterCriterionModel aCriterion;
474             aCriterion.mnOperator = rAttribs.getToken( XML_operator, XML_equal );
475             OUString aValue = rAttribs.getXString( XML_val, OUString() ).trim();
476             if( (aCriterion.mnOperator == XML_equal) || (aCriterion.mnOperator == XML_notEqual) || (aValue.getLength() > 0) )
477                 aCriterion.maValue <<= aValue;
478             appendCriterion( aCriterion );
479         }
480         break;
481     }
482 }
483 
importRecord(sal_Int32 nRecId,SequenceInputStream & rStrm)484 void CustomFilter::importRecord( sal_Int32 nRecId, SequenceInputStream& rStrm )
485 {
486     switch( nRecId )
487     {
488         case BIFF12_ID_CUSTOMFILTERS:
489             mbAnd = rStrm.readInt32() == 0;
490         break;
491 
492         case BIFF12_ID_CUSTOMFILTER:
493         {
494             FilterCriterionModel aCriterion;
495             aCriterion.readBiffData( rStrm );
496             appendCriterion( aCriterion );
497         }
498         break;
499     }
500 }
501 
importBiffRecord(BiffInputStream & rStrm,sal_uInt16 nFlags)502 void CustomFilter::importBiffRecord( BiffInputStream& rStrm, sal_uInt16 nFlags )
503 {
504     mbAnd = !getFlag( nFlags, BIFF_FILTERCOLUMN_OR );
505 
506     FilterCriterionModel aCriterion1, aCriterion2;
507     aCriterion1.readBiffData( rStrm );
508     aCriterion2.readBiffData( rStrm );
509     aCriterion1.readString( rStrm, getBiff(), getTextEncoding() );
510     aCriterion2.readString( rStrm, getBiff(), getTextEncoding() );
511     appendCriterion( aCriterion1 );
512     appendCriterion( aCriterion2 );
513 }
514 
finalizeImport(sal_Int32)515 ApiFilterSettings CustomFilter::finalizeImport( sal_Int32 /*nMaxCount*/ )
516 {
517     ApiFilterSettings aSettings;
518     OSL_ENSURE( maCriteria.size() <= 2, "CustomFilter::finalizeImport - too many filter criteria" );
519     for( FilterCriterionVector::iterator aIt = maCriteria.begin(), aEnd = maCriteria.end(); aIt != aEnd; ++aIt )
520     {
521         // first extract the filter operator
522         sal_Int32 nOperator = 0;
523         bool bValidOperator = lclGetApiOperatorFromToken( nOperator, aIt->mnOperator );
524         if( bValidOperator )
525         {
526             if( aIt->maValue.has< OUString >() )
527             {
528                 // string argument
529                 OUString aValue;
530                 aIt->maValue >>= aValue;
531                 // check for 'empty', 'contains', 'begins with', or 'ends with' text filters
532                 bool bEqual = nOperator == FilterOperator2::EQUAL;
533                 bool bNotEqual = nOperator == FilterOperator2::NOT_EQUAL;
534                 if( bEqual || bNotEqual )
535                 {
536                     if( aValue.getLength() == 0 )
537                     {
538                         // empty comparison string: create empty/not empty filters
539                         nOperator = bNotEqual ? FilterOperator2::NOT_EMPTY : FilterOperator2::EMPTY;
540                     }
541                     else
542                     {
543                         // compare to something: try to find begins/ends/contains
544                         bool bHasLeadingAsterisk = lclTrimLeadingAsterisks( aValue );
545                         bool bHasTrailingAsterisk = lclTrimTrailingAsterisks( aValue );
546                         // just '***' matches everything, do not create a filter field
547                         bValidOperator = aValue.getLength() > 0;
548                         if( bValidOperator )
549                         {
550                             if( bHasLeadingAsterisk && bHasTrailingAsterisk )
551                                 nOperator = bNotEqual ? FilterOperator2::DOES_NOT_CONTAIN : FilterOperator2::CONTAINS;
552                             else if( bHasLeadingAsterisk )
553                                 nOperator = bNotEqual ? FilterOperator2::DOES_NOT_END_WITH : FilterOperator2::ENDS_WITH;
554                             else if( bHasTrailingAsterisk )
555                                 nOperator = bNotEqual ? FilterOperator2::DOES_NOT_BEGIN_WITH : FilterOperator2::BEGINS_WITH;
556                             // else: no asterisks, stick to equal/not equal
557                         }
558                     }
559                 }
560 
561                 if( bValidOperator )
562                 {
563                     // if wildcards are present, require RE mode, otherwise keep don't care state
564                     if( lclConvertWildcardsToRegExp( aValue ) )
565                         aSettings.mobNeedsRegExp = true;
566                     // create a new UNO API filter field
567                     aSettings.appendField( mbAnd, nOperator, aValue );
568                 }
569             }
570             else if( aIt->maValue.has< double >() )
571             {
572                 // floating-point argument
573                 double fValue = 0.0;
574                 aIt->maValue >>= fValue;
575                 aSettings.appendField( mbAnd, nOperator, fValue );
576             }
577         }
578     }
579     return aSettings;
580 }
581 
appendCriterion(const FilterCriterionModel & rCriterion)582 void CustomFilter::appendCriterion( const FilterCriterionModel& rCriterion )
583 {
584     if( (rCriterion.mnOperator != XML_TOKEN_INVALID) && rCriterion.maValue.hasValue() )
585         maCriteria.push_back( rCriterion );
586 }
587 
588 // ============================================================================
589 
FilterColumn(const WorkbookHelper & rHelper)590 FilterColumn::FilterColumn( const WorkbookHelper& rHelper ) :
591     WorkbookHelper( rHelper ),
592     mnColId( -1 ),
593     mbHiddenButton( false ),
594     mbShowButton( true )
595 {
596 }
597 
importFilterColumn(const AttributeList & rAttribs)598 void FilterColumn::importFilterColumn( const AttributeList& rAttribs )
599 {
600     mnColId = rAttribs.getInteger( XML_colId, -1 );
601     mbHiddenButton = rAttribs.getBool( XML_hiddenButton, false );
602     mbShowButton = rAttribs.getBool( XML_showButton, true );
603 }
604 
importFilterColumn(SequenceInputStream & rStrm)605 void FilterColumn::importFilterColumn( SequenceInputStream& rStrm )
606 {
607     sal_uInt16 nFlags;
608     rStrm >> mnColId >> nFlags;
609     mbHiddenButton = getFlag( nFlags, BIFF12_FILTERCOLUMN_HIDDENBUTTON );
610     mbShowButton = getFlag( nFlags, BIFF12_FILTERCOLUMN_SHOWBUTTON );
611 }
612 
importFilterColumn(BiffInputStream & rStrm)613 void FilterColumn::importFilterColumn( BiffInputStream& rStrm )
614 {
615     sal_uInt16 nFlags;
616     mnColId = rStrm.readuInt16();
617     rStrm >> nFlags;
618 
619     // BIFF5/BIFF8 support top-10 filters and custom filters
620     if( getFlag( nFlags, BIFF_FILTERCOLUMN_TOP10FILTER ) )
621         createFilterSettings< Top10Filter >().importBiffRecord( rStrm, nFlags );
622     else
623         createFilterSettings< CustomFilter >().importBiffRecord( rStrm, nFlags );
624 }
625 
finalizeImport(sal_Int32 nMaxCount)626 ApiFilterSettings FilterColumn::finalizeImport( sal_Int32 nMaxCount )
627 {
628     ApiFilterSettings aSettings;
629     if( (0 <= mnColId) && mxSettings.get() )
630     {
631         // filter settings object creates a sequence of filter fields
632         aSettings = mxSettings->finalizeImport( nMaxCount );
633         // add column index to all filter fields
634         for( ApiFilterSettings::FilterFieldVector::iterator aIt = aSettings.maFilterFields.begin(), aEnd = aSettings.maFilterFields.end(); aIt != aEnd; ++aIt )
635             aIt->Field = mnColId;
636     }
637     return aSettings;
638 }
639 
640 // ============================================================================
641 
AutoFilter(const WorkbookHelper & rHelper)642 AutoFilter::AutoFilter( const WorkbookHelper& rHelper ) :
643     WorkbookHelper( rHelper )
644 {
645 }
646 
importAutoFilter(const AttributeList & rAttribs,sal_Int16 nSheet)647 void AutoFilter::importAutoFilter( const AttributeList& rAttribs, sal_Int16 nSheet )
648 {
649     OUString aRangeStr = rAttribs.getString( XML_ref, OUString() );
650     getAddressConverter().convertToCellRangeUnchecked( maRange, aRangeStr, nSheet );
651 }
652 
importAutoFilter(SequenceInputStream & rStrm,sal_Int16 nSheet)653 void AutoFilter::importAutoFilter( SequenceInputStream& rStrm, sal_Int16 nSheet )
654 {
655     BinRange aBinRange;
656     rStrm >> aBinRange;
657     getAddressConverter().convertToCellRangeUnchecked( maRange, aBinRange, nSheet );
658 }
659 
createFilterColumn()660 FilterColumn& AutoFilter::createFilterColumn()
661 {
662     FilterColumnVector::value_type xFilterColumn( new FilterColumn( *this ) );
663     maFilterColumns.push_back( xFilterColumn );
664     return *xFilterColumn;
665 }
666 
finalizeImport(const Reference<XSheetFilterDescriptor2> & rxFilterDesc)667 void AutoFilter::finalizeImport( const Reference< XSheetFilterDescriptor2 >& rxFilterDesc )
668 {
669     if( rxFilterDesc.is() )
670     {
671         // set some common properties for the auto filter range
672         PropertySet aDescProps( rxFilterDesc );
673         aDescProps.setProperty( PROP_IsCaseSensitive, false );
674         aDescProps.setProperty( PROP_SkipDuplicates, false );
675         aDescProps.setProperty( PROP_Orientation, TableOrientation_ROWS );
676         aDescProps.setProperty( PROP_ContainsHeader, true );
677         aDescProps.setProperty( PROP_CopyOutputData, false );
678 
679         // maximum number of UNO API filter fields
680         sal_Int32 nMaxCount = 0;
681         aDescProps.getProperty( nMaxCount, PROP_MaxFieldCount );
682         OSL_ENSURE( nMaxCount > 0, "AutoFilter::finalizeImport - invalid maximum filter field count" );
683 
684         // resulting list of all UNO API filter fields
685         ::std::vector< TableFilterField2 > aFilterFields;
686 
687         // track if columns require to enable or disable regular expressions
688         OptValue< bool > obNeedsRegExp;
689 
690         /*  Track whether the filter fields of the first filter column are
691             connected with 'or'. In this case, other filter fields cannot be
692             inserted without altering the result of the entire filter, due to
693             Calc's precedence for the 'and' connection operator. Example:
694             Excel's filter conditions 'A1 and (B1 or B2) and C1' where B1 and
695             B2 belong to filter column B, will be evaluated by Calc as
696             '(A1 and B1) or (B2 and C1)'. */
697         bool bHasOrConnection = false;
698 
699         // process all filter column objects, exit when 'or' connection exists
700         for( FilterColumnVector::iterator aIt = maFilterColumns.begin(), aEnd = maFilterColumns.end(); !bHasOrConnection && (aIt != aEnd); ++aIt )
701         {
702             // the filter settings object creates a list of filter fields
703             ApiFilterSettings aSettings = (*aIt)->finalizeImport( nMaxCount );
704             ApiFilterSettings::FilterFieldVector& rColumnFields = aSettings.maFilterFields;
705 
706             // new total number of filter fields
707             sal_Int32 nNewCount = static_cast< sal_Int32 >( aFilterFields.size() + rColumnFields.size() );
708 
709             /*  Check whether mode for regular expressions is compatible with
710                 the global mode in obNeedsRegExp. If either one is still in
711                 don't-care state, all is fine. If both are set, they must be
712                 equal. */
713             bool bRegExpCompatible = !obNeedsRegExp || !aSettings.mobNeedsRegExp || (obNeedsRegExp.get() == aSettings.mobNeedsRegExp.get());
714 
715             // check whether fields are connected by 'or' (see comments above).
716             if( rColumnFields.size() >= 2 )
717                 for( ApiFilterSettings::FilterFieldVector::iterator aSIt = rColumnFields.begin() + 1, aSEnd = rColumnFields.end(); !bHasOrConnection && (aSIt != aSEnd); ++aSIt )
718                     bHasOrConnection = aSIt->Connection == FilterConnection_OR;
719 
720             /*  Skip the column filter, if no filter fields have been created,
721                 if the number of new filter fields would exceed the total limit
722                 of filter fields, or if the mode for regular expressions of the
723                 filter column does not fit. */
724             if( !rColumnFields.empty() && (nNewCount <= nMaxCount) && bRegExpCompatible )
725             {
726                 /*  Add 'and' connection to the first filter field to connect
727                     it to the existing filter fields of other columns. */
728                 rColumnFields[ 0 ].Connection = FilterConnection_AND;
729 
730                 // insert the new filter fields
731                 aFilterFields.insert( aFilterFields.end(), rColumnFields.begin(), rColumnFields.end() );
732 
733                 // update the regular expressions mode
734                 obNeedsRegExp.assignIfUsed( aSettings.mobNeedsRegExp );
735             }
736         }
737 
738         // insert all filter fields to the filter descriptor
739         if( !aFilterFields.empty() )
740             rxFilterDesc->setFilterFields2( ContainerHelper::vectorToSequence( aFilterFields ) );
741 
742         // regular expressions
743         bool bUseRegExp = obNeedsRegExp.get( false );
744         aDescProps.setProperty( PROP_UseRegularExpressions, bUseRegExp );
745     }
746 }
747 
748 // ============================================================================
749 
AutoFilterBuffer(const WorkbookHelper & rHelper)750 AutoFilterBuffer::AutoFilterBuffer( const WorkbookHelper& rHelper ) :
751     WorkbookHelper( rHelper )
752 {
753 }
754 
createAutoFilter()755 AutoFilter& AutoFilterBuffer::createAutoFilter()
756 {
757     AutoFilterVector::value_type xAutoFilter( new AutoFilter( *this ) );
758     maAutoFilters.push_back( xAutoFilter );
759     return *xAutoFilter;
760 }
761 
finalizeImport(sal_Int16 nSheet)762 void AutoFilterBuffer::finalizeImport( sal_Int16 nSheet )
763 {
764     // rely on existence of the defined name '_FilterDatabase' containing the range address of the filtered area
765     if( const DefinedName* pFilterDBName = getDefinedNames().getByBuiltinId( BIFF_DEFNAME_FILTERDATABASE, nSheet ).get() )
766     {
767         CellRangeAddress aFilterRange;
768         if( pFilterDBName->getAbsoluteRange( aFilterRange ) && (aFilterRange.Sheet == nSheet) )
769         {
770             // use the same name for the database range as used for the defined name '_FilterDatabase'
771             OUString aDBRangeName = pFilterDBName->getCalcName();
772             Reference< XDatabaseRange > xDatabaseRange = createDatabaseRangeObject( aDBRangeName, aFilterRange );
773             // first, try to create an auto filter
774             bool bHasAutoFilter = finalizeImport( xDatabaseRange );
775             // no success: try to create an advanced filter
776             if( !bHasAutoFilter && xDatabaseRange.is() )
777             {
778                 // the built-in defined name 'Criteria' must exist
779                 if( const DefinedName* pCriteriaName = getDefinedNames().getByBuiltinId( BIFF_DEFNAME_CRITERIA, nSheet ).get() )
780                 {
781                     CellRangeAddress aCriteriaRange;
782                     if( pCriteriaName->getAbsoluteRange( aCriteriaRange ) )
783                     {
784                         // set some common properties for the filter descriptor
785                         PropertySet aDescProps( xDatabaseRange->getFilterDescriptor() );
786                         aDescProps.setProperty( PROP_IsCaseSensitive, false );
787                         aDescProps.setProperty( PROP_SkipDuplicates, false );
788                         aDescProps.setProperty( PROP_Orientation, TableOrientation_ROWS );
789                         aDescProps.setProperty( PROP_ContainsHeader, true );
790                         // criteria range may contain wildcards, but these are incompatible with REs
791                         aDescProps.setProperty( PROP_UseRegularExpressions, false );
792 
793                         // position of output data (if built-in defined name 'Extract' exists)
794                         DefinedNameRef xExtractName = getDefinedNames().getByBuiltinId( BIFF_DEFNAME_EXTRACT, nSheet );
795                         CellRangeAddress aOutputRange;
796                         bool bHasOutputRange = xExtractName.get() && xExtractName->getAbsoluteRange( aOutputRange );
797                         aDescProps.setProperty( PROP_CopyOutputData, bHasOutputRange );
798                         if( bHasOutputRange )
799                         {
800                             aDescProps.setProperty( PROP_SaveOutputPosition, true );
801                             aDescProps.setProperty( PROP_OutputPosition, CellAddress( aOutputRange.Sheet, aOutputRange.StartColumn, aOutputRange.StartRow ) );
802                         }
803 
804                         /*  Properties of the database range (must be set after
805                             modifying properties of the filter descriptor,
806                             otherwise the 'FilterCriteriaSource' property gets
807                             deleted). */
808                         PropertySet aRangeProps( xDatabaseRange );
809                         aRangeProps.setProperty( PROP_AutoFilter, false );
810                         aRangeProps.setProperty( PROP_FilterCriteriaSource, aCriteriaRange );
811                     }
812                 }
813             }
814         }
815     }
816 }
817 
finalizeImport(const Reference<XDatabaseRange> & rxDatabaseRange)818 bool AutoFilterBuffer::finalizeImport( const Reference< XDatabaseRange >& rxDatabaseRange )
819 {
820     AutoFilter* pAutoFilter = getActiveAutoFilter();
821     if( pAutoFilter && rxDatabaseRange.is() ) try
822     {
823         // the property 'AutoFilter' enables the drop-down buttons
824         PropertySet aRangeProps( rxDatabaseRange );
825         aRangeProps.setProperty( PROP_AutoFilter, true );
826         // convert filter settings using the filter descriptor of the database range
827         Reference< XSheetFilterDescriptor2 > xFilterDesc( rxDatabaseRange->getFilterDescriptor(), UNO_QUERY_THROW );
828         pAutoFilter->finalizeImport( xFilterDesc );
829         // return true to indicate enabled autofilter
830         return true;
831     }
832     catch( Exception& )
833     {
834     }
835     return false;
836 }
837 
getActiveAutoFilter()838 AutoFilter* AutoFilterBuffer::getActiveAutoFilter()
839 {
840     // Excel expects not more than one auto filter per sheet or table
841     OSL_ENSURE( maAutoFilters.size() <= 1, "AutoFilterBuffer::getActiveAutoFilter - too many auto filters" );
842     // stick to the last imported auto filter
843     return maAutoFilters.empty() ? 0 : maAutoFilters.back().get();
844 }
845 
846 // ============================================================================
847 
848 } // namespace xls
849 } // namespace oox
850