AutoFilter
Table of Contents
- DATE_FUNCTIONS = [PhpOfficePhpSpreadsheetWorksheetAutoFilterColumnRule::AUTOFILTER_RULETYPE_DYNAMIC_LASTMONTH => 'dynamicLastMonth', PhpOfficePhpSpreadsheetWorksheetAutoFilterColumnRule::AUTOFILTER_RULETYPE_DYNAMIC_LASTQUARTER => 'dynamicLastQuarter', PhpOfficePhpSpreadsheetWorksheetAutoFilterColumnRule::AUTOFILTER_RULETYPE_DYNAMIC_LASTWEEK => 'dynamicLastWeek', PhpOfficePhpSpreadsheetWorksheetAutoFilterColumnRule::AUTOFILTER_RULETYPE_DYNAMIC_LASTYEAR => 'dynamicLastYear', PhpOfficePhpSpreadsheetWorksheetAutoFilterColumnRule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTMONTH => 'dynamicNextMonth', PhpOfficePhpSpreadsheetWorksheetAutoFilterColumnRule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTQUARTER => 'dynamicNextQuarter', PhpOfficePhpSpreadsheetWorksheetAutoFilterColumnRule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTWEEK => 'dynamicNextWeek', PhpOfficePhpSpreadsheetWorksheetAutoFilterColumnRule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTYEAR => 'dynamicNextYear', PhpOfficePhpSpreadsheetWorksheetAutoFilterColumnRule::AUTOFILTER_RULETYPE_DYNAMIC_THISMONTH => 'dynamicThisMonth', PhpOfficePhpSpreadsheetWorksheetAutoFilterColumnRule::AUTOFILTER_RULETYPE_DYNAMIC_THISQUARTER => 'dynamicThisQuarter', PhpOfficePhpSpreadsheetWorksheetAutoFilterColumnRule::AUTOFILTER_RULETYPE_DYNAMIC_THISWEEK => 'dynamicThisWeek', PhpOfficePhpSpreadsheetWorksheetAutoFilterColumnRule::AUTOFILTER_RULETYPE_DYNAMIC_THISYEAR => 'dynamicThisYear', PhpOfficePhpSpreadsheetWorksheetAutoFilterColumnRule::AUTOFILTER_RULETYPE_DYNAMIC_TODAY => 'dynamicToday', PhpOfficePhpSpreadsheetWorksheetAutoFilterColumnRule::AUTOFILTER_RULETYPE_DYNAMIC_TOMORROW => 'dynamicTomorrow', PhpOfficePhpSpreadsheetWorksheetAutoFilterColumnRule::AUTOFILTER_RULETYPE_DYNAMIC_YEARTODATE => 'dynamicYearToDate', PhpOfficePhpSpreadsheetWorksheetAutoFilterColumnRule::AUTOFILTER_RULETYPE_DYNAMIC_YESTERDAY => 'dynamicYesterday']
- $columns : array<string|int, Column>
- Autofilter Column Ruleset.
- $evaluated : bool
- $range : string
- Autofilter Range.
- $workSheet : null|Worksheet
- Autofilter Worksheet.
- __clone() : mixed
- Implement PHP __clone to create a deep clone, not just a shallow copy.
- __construct() : mixed
- Create a new AutoFilter.
- __toString() : mixed
- toString method replicates previous behavior by returning the range if object is referenced as a property of its parent.
- autoExtendRange() : int
- Magic Range Auto-sizing.
- clearColumn() : $this
- Clear a specified AutoFilter Column.
- getColumn() : Column
- Get a specified AutoFilter Column.
- getColumnByOffset() : Column
- Get a specified AutoFilter Column by it's offset.
- getColumnOffset() : int
- Get a specified AutoFilter Column Offset within the defined AutoFilter range.
- getColumns() : array<string|int, Column>
- Get all AutoFilter Columns.
- getEvaluated() : bool
- getParent() : null|Worksheet
- Get AutoFilter Parent Worksheet.
- getRange() : string
- Get AutoFilter Range.
- setColumn() : $this
- Set AutoFilter.
- setEvaluated() : void
- setParent() : $this
- Set AutoFilter Parent Worksheet.
- setRange() : self
- Set AutoFilter Cell Range.
- setRangeToMaxRow() : self
- shiftColumn() : $this
- Shift an AutoFilter Column Rule to a different column.
- showHideRows() : $this
- Apply the AutoFilter rules to the AutoFilter Range.
- testColumnInRange() : int
- Validate that the specified column is in the AutoFilter range.
- filterTestInCustomDataSet() : bool
- Test if cell value is within a set of values defined by a ruleset.
- filterTestInDateGroupSet() : bool
- Test if cell value is in the defined set of Excel date values.
- filterTestInPeriodDateSet() : bool
- Test if cell date value is matches a set of values defined by a set of months.
- filterTestInSimpleDataSet() : bool
- Test if cell value is in the defined set of values.
- calculateTopTenValue() : mixed
- Apply the AutoFilter rules to the AutoFilter Range.
- dynamicFilterDateRange() : array<string|int, mixed>
- Convert a dynamic rule daterange to a custom filter range expression for ease of calculation.
- dynamicLastMonth() : array<string|int, mixed>
- dynamicLastQuarter() : array<string|int, mixed>
- dynamicLastWeek() : array<string|int, mixed>
- dynamicLastYear() : array<string|int, mixed>
- dynamicNextMonth() : array<string|int, mixed>
- dynamicNextQuarter() : array<string|int, mixed>
- dynamicNextWeek() : array<string|int, mixed>
- dynamicNextYear() : array<string|int, mixed>
- dynamicThisMonth() : array<string|int, mixed>
- dynamicThisQuarter() : array<string|int, mixed>
- dynamicThisWeek() : array<string|int, mixed>
- dynamicThisYear() : array<string|int, mixed>
- dynamicToday() : array<string|int, mixed>
- dynamicTomorrow() : array<string|int, mixed>
- dynamicYearToDate() : array<string|int, mixed>
- dynamicYesterday() : array<string|int, mixed>
- firstDayOfQuarter() : DateTime
- makeDateObject() : DateTime
Constants
DATE_FUNCTIONS
private
mixed
DATE_FUNCTIONS
= [PhpOfficePhpSpreadsheetWorksheetAutoFilterColumnRule::AUTOFILTER_RULETYPE_DYNAMIC_LASTMONTH => 'dynamicLastMonth', PhpOfficePhpSpreadsheetWorksheetAutoFilterColumnRule::AUTOFILTER_RULETYPE_DYNAMIC_LASTQUARTER => 'dynamicLastQuarter', PhpOfficePhpSpreadsheetWorksheetAutoFilterColumnRule::AUTOFILTER_RULETYPE_DYNAMIC_LASTWEEK => 'dynamicLastWeek', PhpOfficePhpSpreadsheetWorksheetAutoFilterColumnRule::AUTOFILTER_RULETYPE_DYNAMIC_LASTYEAR => 'dynamicLastYear', PhpOfficePhpSpreadsheetWorksheetAutoFilterColumnRule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTMONTH => 'dynamicNextMonth', PhpOfficePhpSpreadsheetWorksheetAutoFilterColumnRule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTQUARTER => 'dynamicNextQuarter', PhpOfficePhpSpreadsheetWorksheetAutoFilterColumnRule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTWEEK => 'dynamicNextWeek', PhpOfficePhpSpreadsheetWorksheetAutoFilterColumnRule::AUTOFILTER_RULETYPE_DYNAMIC_NEXTYEAR => 'dynamicNextYear', PhpOfficePhpSpreadsheetWorksheetAutoFilterColumnRule::AUTOFILTER_RULETYPE_DYNAMIC_THISMONTH => 'dynamicThisMonth', PhpOfficePhpSpreadsheetWorksheetAutoFilterColumnRule::AUTOFILTER_RULETYPE_DYNAMIC_THISQUARTER => 'dynamicThisQuarter', PhpOfficePhpSpreadsheetWorksheetAutoFilterColumnRule::AUTOFILTER_RULETYPE_DYNAMIC_THISWEEK => 'dynamicThisWeek', PhpOfficePhpSpreadsheetWorksheetAutoFilterColumnRule::AUTOFILTER_RULETYPE_DYNAMIC_THISYEAR => 'dynamicThisYear', PhpOfficePhpSpreadsheetWorksheetAutoFilterColumnRule::AUTOFILTER_RULETYPE_DYNAMIC_TODAY => 'dynamicToday', PhpOfficePhpSpreadsheetWorksheetAutoFilterColumnRule::AUTOFILTER_RULETYPE_DYNAMIC_TOMORROW => 'dynamicTomorrow', PhpOfficePhpSpreadsheetWorksheetAutoFilterColumnRule::AUTOFILTER_RULETYPE_DYNAMIC_YEARTODATE => 'dynamicYearToDate', PhpOfficePhpSpreadsheetWorksheetAutoFilterColumnRule::AUTOFILTER_RULETYPE_DYNAMIC_YESTERDAY => 'dynamicYesterday']
Properties
$columns
Autofilter Column Ruleset.
private
array<string|int, Column>
$columns
= []
$evaluated
private
bool
$evaluated
= false
$range
Autofilter Range.
private
string
$range
= ''
$workSheet
Autofilter Worksheet.
private
null|Worksheet
$workSheet
Methods
__clone()
Implement PHP __clone to create a deep clone, not just a shallow copy.
public
__clone() : mixed
Return values
mixed —__construct()
Create a new AutoFilter.
public
__construct([AddressRange|array<string|int, int>|string $range = '' ][, Worksheet|null $worksheet = null ]) : mixed
Parameters
- $range : AddressRange|array<string|int, int>|string = ''
-
A simple string containing a Cell range like 'A1:E10' is permitted or passing in an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 8]), or an AddressRange object.
- $worksheet : Worksheet|null = null
Return values
mixed —__toString()
toString method replicates previous behavior by returning the range if object is referenced as a property of its parent.
public
__toString() : mixed
Return values
mixed —autoExtendRange()
Magic Range Auto-sizing.
public
autoExtendRange(int $startRow, int $endRow) : int
For a single row rangeSet, we follow MS Excel rules, and search for the first empty row to determine our range.
Parameters
- $startRow : int
- $endRow : int
Return values
int —clearColumn()
Clear a specified AutoFilter Column.
public
clearColumn(string $column) : $this
Parameters
- $column : string
-
Column name (e.g. A)
Return values
$this —getColumn()
Get a specified AutoFilter Column.
public
getColumn(string $column) : Column
Parameters
- $column : string
-
Column name (e.g. A)
Return values
Column —getColumnByOffset()
Get a specified AutoFilter Column by it's offset.
public
getColumnByOffset(int $columnOffset) : Column
Parameters
- $columnOffset : int
-
Column offset within range (starting from 0)
Return values
Column —getColumnOffset()
Get a specified AutoFilter Column Offset within the defined AutoFilter range.
public
getColumnOffset(string $column) : int
Parameters
- $column : string
-
Column name (e.g. A)
Return values
int —The offset of the specified column within the autofilter range
getColumns()
Get all AutoFilter Columns.
public
getColumns() : array<string|int, Column>
Return values
array<string|int, Column> —getEvaluated()
public
getEvaluated() : bool
Return values
bool —getParent()
Get AutoFilter Parent Worksheet.
public
getParent() : null|Worksheet
Return values
null|Worksheet —getRange()
Get AutoFilter Range.
public
getRange() : string
Return values
string —setColumn()
Set AutoFilter.
public
setColumn(Column|string $columnObjectOrString) : $this
Parameters
- $columnObjectOrString : Column|string
-
A simple string containing a Column ID like 'A' is permitted
Return values
$this —setEvaluated()
public
setEvaluated(bool $value) : void
Parameters
- $value : bool
Return values
void —setParent()
Set AutoFilter Parent Worksheet.
public
setParent([Worksheet|null $worksheet = null ]) : $this
Parameters
- $worksheet : Worksheet|null = null
Return values
$this —setRange()
Set AutoFilter Cell Range.
public
setRange([AddressRange|array<string|int, int>|string $range = '' ]) : self
Parameters
- $range : AddressRange|array<string|int, int>|string = ''
-
A simple string containing a Cell range like 'A1:E10' or a Cell address like 'A1' is permitted or passing in an array of [$fromColumnIndex, $fromRow, $toColumnIndex, $toRow] (e.g. [3, 5, 6, 8]), or an AddressRange object.
Return values
self —setRangeToMaxRow()
public
setRangeToMaxRow() : self
Return values
self —shiftColumn()
Shift an AutoFilter Column Rule to a different column.
public
shiftColumn(string $fromColumn, string $toColumn) : $this
Note: This method bypasses validation of the destination column to ensure it is within this AutoFilter range. Nor does it verify whether any column rule already exists at $toColumn, but will simply override any existing value. Use with caution.
Parameters
- $fromColumn : string
-
Column name (e.g. A)
- $toColumn : string
-
Column name (e.g. B)
Return values
$this —showHideRows()
Apply the AutoFilter rules to the AutoFilter Range.
public
showHideRows() : $this
Return values
$this —testColumnInRange()
Validate that the specified column is in the AutoFilter range.
public
testColumnInRange(string $column) : int
Parameters
- $column : string
-
Column name (e.g. A)
Return values
int —The column offset within the autofilter range
filterTestInCustomDataSet()
Test if cell value is within a set of values defined by a ruleset.
protected
static filterTestInCustomDataSet(mixed $cellValue, array<string|int, mixed> $ruleSet) : bool
Parameters
- $cellValue : mixed
- $ruleSet : array<string|int, mixed>
Return values
bool —filterTestInDateGroupSet()
Test if cell value is in the defined set of Excel date values.
protected
static filterTestInDateGroupSet(mixed $cellValue, array<string|int, mixed> $dataSet) : bool
Parameters
- $cellValue : mixed
- $dataSet : array<string|int, mixed>
Return values
bool —filterTestInPeriodDateSet()
Test if cell date value is matches a set of values defined by a set of months.
protected
static filterTestInPeriodDateSet(mixed $cellValue, array<string|int, mixed> $monthSet) : bool
Parameters
- $cellValue : mixed
- $monthSet : array<string|int, mixed>
Return values
bool —filterTestInSimpleDataSet()
Test if cell value is in the defined set of values.
protected
static filterTestInSimpleDataSet(mixed $cellValue, array<string|int, mixed> $dataSet) : bool
Parameters
- $cellValue : mixed
- $dataSet : array<string|int, mixed>
Return values
bool —calculateTopTenValue()
Apply the AutoFilter rules to the AutoFilter Range.
private
calculateTopTenValue(string $columnID, int $startRow, int $endRow, string|null $ruleType, mixed $ruleValue) : mixed
Parameters
- $columnID : string
- $startRow : int
- $endRow : int
- $ruleType : string|null
- $ruleValue : mixed
Return values
mixed —dynamicFilterDateRange()
Convert a dynamic rule daterange to a custom filter range expression for ease of calculation.
private
dynamicFilterDateRange(string $dynamicRuleType, Column &$filterColumn) : array<string|int, mixed>
Parameters
- $dynamicRuleType : string
- $filterColumn : Column
Return values
array<string|int, mixed> —dynamicLastMonth()
private
static dynamicLastMonth() : array<string|int, mixed>
Return values
array<string|int, mixed> —dynamicLastQuarter()
private
static dynamicLastQuarter() : array<string|int, mixed>
Return values
array<string|int, mixed> —dynamicLastWeek()
private
static dynamicLastWeek() : array<string|int, mixed>
Return values
array<string|int, mixed> —dynamicLastYear()
private
static dynamicLastYear() : array<string|int, mixed>
Return values
array<string|int, mixed> —dynamicNextMonth()
private
static dynamicNextMonth() : array<string|int, mixed>
Return values
array<string|int, mixed> —dynamicNextQuarter()
private
static dynamicNextQuarter() : array<string|int, mixed>
Return values
array<string|int, mixed> —dynamicNextWeek()
private
static dynamicNextWeek() : array<string|int, mixed>
Return values
array<string|int, mixed> —dynamicNextYear()
private
static dynamicNextYear() : array<string|int, mixed>
Return values
array<string|int, mixed> —dynamicThisMonth()
private
static dynamicThisMonth() : array<string|int, mixed>
Return values
array<string|int, mixed> —dynamicThisQuarter()
private
static dynamicThisQuarter() : array<string|int, mixed>
Return values
array<string|int, mixed> —dynamicThisWeek()
private
static dynamicThisWeek() : array<string|int, mixed>
Return values
array<string|int, mixed> —dynamicThisYear()
private
static dynamicThisYear() : array<string|int, mixed>
Return values
array<string|int, mixed> —dynamicToday()
private
static dynamicToday() : array<string|int, mixed>
Return values
array<string|int, mixed> —dynamicTomorrow()
private
static dynamicTomorrow() : array<string|int, mixed>
Return values
array<string|int, mixed> —dynamicYearToDate()
private
static dynamicYearToDate() : array<string|int, mixed>
Return values
array<string|int, mixed> —dynamicYesterday()
private
static dynamicYesterday() : array<string|int, mixed>
Return values
array<string|int, mixed> —firstDayOfQuarter()
private
static firstDayOfQuarter() : DateTime
Return values
DateTime —makeDateObject()
private
static makeDateObject(int $year, int $month, int $day, int $hour, int $minute, int $second) : DateTime
Parameters
- $year : int
- $month : int
- $day : int
- $hour : int
- $minute : int
- $second : int