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>
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>
45 #include <document.hxx>
49 #include <stlpool.hxx>
50 #include <stlsheet.hxx>
63 const sal_uInt8 BIFF12_TOP10FILTER_TOP = 0x01;
64 const sal_uInt8 BIFF12_TOP10FILTER_PERCENT = 0x02;
66 const sal_uInt16 BIFF12_FILTERCOLUMN_HIDDENBUTTON = 0x0001;
67 const sal_uInt16 BIFF12_FILTERCOLUMN_SHOWBUTTON = 0x0002;
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;
76 bool lclGetApiOperatorFromToken( sal_Int32& rnApiOperator, sal_Int32
nToken )
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;
92 bool lclTrimLeadingAsterisks( OUString& rValue )
94 sal_Int32
nLength = rValue.getLength();
96 while( (nPos < nLength) && (rValue[ nPos ] ==
'*') )
100 rValue = rValue.copy( nPos );
108 bool lclTrimTrailingAsterisks( OUString& rValue )
110 sal_Int32 nLength = rValue.getLength();
111 sal_Int32 nPos = nLength;
112 while( (nPos > 0) && (rValue[ nPos - 1 ] ==
'*') )
116 rValue = rValue.copy( 0, nPos );
125 bool lclConvertWildcardsToRegExp( OUString& rValue )
128 if( !rValue.isEmpty() && ((rValue.indexOf(
'*' ) >= 0) || (rValue.indexOf(
'?' ) >= 0)) )
131 aBuffer.ensureCapacity( rValue.getLength() + 5 );
133 const sal_Unicode* pcEnd = pcChar + rValue.getLength();
134 for( ; pcChar < pcEnd; ++pcChar )
139 aBuffer.append(
'.' );
142 aBuffer.append(
'.' ).append(
'*' );
144 case '\\':
case '.':
case '|':
case '(':
case ')':
case '^':
case '$':
146 aBuffer.append(
'\\' ).append( *pcChar );
149 aBuffer.append( *pcChar );
152 rValue = aBuffer.makeStringAndClear();
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;
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;
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();
197 = bIsBackgroundColor ? FilterFieldType::BACKGROUND_COLOR : FilterFieldType::TEXT_COLOR;
198 pValues[0].ColorValue = aColor;
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();
211 for(
auto const& it : rValues )
213 pValues[i].StringValue = it.first;
248 case XLS_TOKEN( filters ):
255 OUString aValue = rAttribs.
getXString( XML_val, OUString() );
256 if( !aValue.isEmpty() )
257 maValues.push_back( std::make_pair(aValue,
false) );
261 case XLS_TOKEN( dateGroupItem ):
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 )
270 aDateValue = rAttribs.
getString(XML_year, OUString());
272 if( nToken == XML_month || nToken == XML_day || nToken == XML_hour ||
273 nToken == XML_minute || nToken == XML_second )
275 OUString aMonthName = rAttribs.
getString(XML_month, OUString());
276 if( aMonthName.getLength() == 1 )
277 aMonthName =
"0" + aMonthName;
278 aDateValue +=
"-" + aMonthName;
280 if( nToken == XML_day || nToken == XML_hour || nToken == XML_minute ||
281 nToken == XML_second )
283 OUString aDayName = rAttribs.
getString(XML_day, OUString());
284 if( aDayName.getLength() == 1 )
285 aDayName =
"0" + aDayName;
286 aDateValue +=
"-" + aDayName;
288 if( nToken == XML_hour || nToken == XML_minute || nToken == XML_second )
290 OUString aHourName = rAttribs.
getString(XML_hour, OUString());
291 if( aHourName.getLength() == 1 )
292 aHourName =
"0" + aHourName;
293 aDateValue +=
" " + aHourName;
295 if( nToken == XML_minute || nToken == XML_second )
297 OUString aMinName = rAttribs.
getString(XML_minute, OUString());
298 if( aMinName.getLength() == 1 )
299 aMinName =
"0" + aMinName;
300 aDateValue +=
":" + aMinName;
302 if( nToken == XML_second )
304 OUString aSecName = rAttribs.
getString(XML_second, OUString());
305 if( aSecName.getLength() == 1 )
306 aSecName =
"0" + aSecName;
307 aDateValue +=
":" + aSecName;
314 if( !aDateValue.isEmpty() )
315 maValues.push_back( std::make_pair(aDateValue,
true) );
327 sal_Int32 nShowBlank, nCalendarType;
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 };
342 if( !aValue.isEmpty() )
343 maValues.push_back( std::make_pair(aValue,
false) );
359 aSettings.
appendField(
false, FilterOperator2::EMPTY, OUString() );
379 if( nElement == XLS_TOKEN( top10 ) )
401 sal_Int32 nOperator =
mbTop ?
402 (
mbPercent ? FilterOperator2::TOP_PERCENT : FilterOperator2::TOP_VALUES) :
403 (
mbPercent ? FilterOperator2::BOTTOM_PERCENT : FilterOperator2::BOTTOM_VALUES);
411 , mbIsBackgroundColor(
false)
417 if (nElement == XLS_TOKEN(colorFilter))
445 util::Color nColor(aColor);
451 mnOperator( XML_equal ),
452 mnDataType( BIFF_FILTER_DATATYPE_NONE )
459 XML_lessThan, XML_equal, XML_lessThanOrEqual, XML_greaterThan, XML_notEqual, XML_greaterThanOrEqual };
472 case BIFF_FILTER_DATATYPE_DOUBLE:
475 case BIFF_FILTER_DATATYPE_STRING:
479 if( !aValue.isEmpty() )
483 case BIFF_FILTER_DATATYPE_BOOLEAN:
487 case BIFF_FILTER_DATATYPE_EMPTY:
492 case BIFF_FILTER_DATATYPE_NOTEMPTY:
498 OSL_ENSURE(
false,
"FilterCriterionModel::readBiffData - unexpected data type" );
513 case XLS_TOKEN( customFilters ):
517 case XLS_TOKEN( customFilter ):
521 OUString aValue = rAttribs.
getXString( XML_val, OUString() ).trim();
522 if( (aCriterion.
mnOperator == XML_equal) || (aCriterion.
mnOperator == XML_notEqual) || (!aValue.isEmpty()) )
551 OSL_ENSURE(
maCriteria.size() <= 2,
"CustomFilter::finalizeImport - too many filter criteria" );
555 sal_Int32 nOperator = 0;
556 bool bValidOperator = lclGetApiOperatorFromToken( nOperator, rCriterion.mnOperator );
559 if( rCriterion.maValue.has< OUString >() )
563 rCriterion.maValue >>= aValue;
565 bool bEqual = nOperator == FilterOperator2::EQUAL;
566 bool bNotEqual = nOperator == FilterOperator2::NOT_EQUAL;
567 if( bEqual || bNotEqual )
569 if( aValue.isEmpty() )
572 nOperator = bNotEqual ? FilterOperator2::NOT_EMPTY : FilterOperator2::EMPTY;
577 bool bHasLeadingAsterisk = lclTrimLeadingAsterisks( aValue );
578 bool bHasTrailingAsterisk = lclTrimTrailingAsterisks( aValue );
580 bValidOperator = !aValue.isEmpty();
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;
597 if( lclConvertWildcardsToRegExp( aValue ) )
603 else if( rCriterion.maValue.has<
double >() )
607 rCriterion.maValue >>= fValue;
624 mbHiddenButton(
false ),
663 mbDescending(
false )
669 OUString aRangeStr = rAttribs.
getString( XML_ref, OUString() );
685 OUString aRangeStr = rAttribs.
getString( XML_ref, OUString() );
698 OUString aRangeStr = rAttribs.
getString( XML_ref, OUString() );
706 return *xFilterColumn;
713 return *xSortCondition;
719 const Reference<XSheetFilterDescriptor3> xFilterDesc( rxDatabaseRange->getFilterDescriptor(), UNO_QUERY_THROW );
720 if( !xFilterDesc.is() )
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 );
732 ::std::vector<TableFilterField3> aFilterFields;
744 bool bHasOrConnection =
false;
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; });
767 if( !rColumnFields.empty() && bRegExpCompatible )
771 rColumnFields[ 0 ].Connection = FilterConnection_AND;
774 aFilterFields.insert( aFilterFields.end(), rColumnFields.begin(), rColumnFields.end() );
780 if( bHasOrConnection )
785 if( !aFilterFields.empty() )
789 bool bUseRegExp = obNeedsRegExp.
get(
false );
790 aDescProps.
setProperty( PROP_UseRegularExpressions, bUseRegExp );
797 const SortCondition& rSorConditionLoaded = *xSortConditionPointer;
807 for (
size_t i=0; pUserList &&
i < pUserList->
size();
i++)
809 const OUString aEntry((*pUserList)[
i].
GetString());
850 OSL_FAIL(
"AutoFilter::finalizeImport(): cannot find matching DBData");
881 if( bHasAutoFilter || !xDatabaseRange.is() )
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 );
900 aDescProps.setProperty( PROP_UseRegularExpressions,
false );
905 bool bHasOutputRange = xExtractName && xExtractName->getAbsoluteRange( aOutputRange );
906 aDescProps.setProperty( PROP_CopyOutputData, bHasOutputRange );
907 if( bHasOutputRange )
909 aDescProps.setProperty( PROP_SaveOutputPosition,
true );
910 aDescProps.setProperty( PROP_OutputPosition, CellAddress( aOutputRange.
aStart.
Tab(), aOutputRange.
aStart.
Col(), aOutputRange.
aStart.
Row() ) );
919 aRangeProps.
setProperty( PROP_FilterCriteriaSource,
920 CellRangeAddress( aCriteriaRange.
aStart.
Tab(),
928 if( pAutoFilter && rxDatabaseRange.is() )
try
932 aRangeProps.setProperty( PROP_AutoFilter,
true );
948 OSL_ENSURE(
maAutoFilters.size() <= 1,
"AutoFilterBuffer::getActiveAutoFilter - too many auto filters" );
Helper class to provide access to global workbook data.
::std::vector< ScSortKeyState > maKeyState
const sal_Int32 BIFF12_ID_CUSTOMFILTER
virtual ApiFilterSettings finalizeImport() override
Returns converted UNO API filter settings representing all filter settings.
Collection of user-defined sort lists.
void importSortState(const AttributeList &rAttribs, sal_Int16 nSheet)
OptValue< bool > getBool(sal_Int32 nAttrToken) const
::std::vector< css::sheet::TableFilterField3 > FilterFieldVector
SC_DLLPUBLIC void SetSortParam(const ScSortParam &rSortParam)
OUString maSortCustomList
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
value_type get(sal_Int32 nIndex) const
ColorFilter(const WorkbookHelper &rHelper)
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.
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)
void push_back(ScUserListData *p)
AutoFilter & createAutoFilter()
Creates a new auto filter and stores it internally.
Stores individual user-defined sort list.
A filter criterion for a custom filter.
sal_Int32 SCCOLROW
a type capable of holding either SCCOL or SCROW
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
void importSortCondition(const AttributeList &rAttribs, sal_Int16 nSheet)
AutoFilter(const WorkbookHelper &rHelper)
FilterCriterionVector maCriteria
DiscreteFilter(const WorkbookHelper &rHelper)
CustomFilter(const WorkbookHelper &rHelper)
bool getFlag(Type nBitField, Type nMask)
const Color & GetColor() const
AutoFilterVector maAutoFilters
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.
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.
virtual void importAttribs(sal_Int32 nElement, const AttributeList &rAttribs) override
Imports filter settings from the filters and filter elements.
ScDocument & getScDocument()
const sal_Int32 BIFF12_ID_CUSTOMFILTERS
static SC_DLLPUBLIC ScUserList * GetUserList()
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
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
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
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
const sal_Int32 BIFF12_ID_TOP10FILTER
virtual SC_DLLPUBLIC SfxItemSet & GetItemSet() override
virtual ApiFilterSettings finalizeImport() override
Returns converted UNO API filter settings representing all filter settings.
SC_DLLPUBLIC ScStyleSheetPool * GetStyleSheetPool() const
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.
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.
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)
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