openpyxl.worksheet.filters module

class openpyxl.worksheet.filters.AutoFilter(ref=None, filterColumn=(), sortState=None, extLst=None)[source]

Bases: Serialisable

add_filter_column(col_id, vals, blank=False)[source]

Add row filter for specified column.

Parameters:
  • col_id (int) – Zero-origin column id. 0 means first column.

  • vals (str[]) – Value list to show.

  • blank (bool) – Show rows that have blank cell if True (default=``False``)

add_sort_condition(ref, descending=False)[source]

Add sort condition for cpecified range of cells.

Parameters:
  • ref (string, is the same as that of the filter) – range of the cells (e.g. ‘A2:A150’)

  • descending (bool) – Descending sort order (default=``False``)

extLst

Values must be of type <class ‘openpyxl.descriptors.excel.ExtensionList’>

filterColumn

A sequence (list or tuple) that may only contain objects of the declared type

ref
sortState

Values must be of type <class ‘openpyxl.worksheet.filters.SortState’>

tagname = 'autoFilter'
class openpyxl.worksheet.filters.BlankFilter(**kw)[source]

Bases: CustomFilter

Exclude blanks

property operator

Value must be one of {‘lessThanOrEqual’, ‘notEqual’, ‘greaterThanOrEqual’, ‘equal’, ‘lessThan’, ‘greaterThan’}

property val

Values must be of type <class ‘str’>

class openpyxl.worksheet.filters.ColorFilter(dxfId=None, cellColor=None)[source]

Bases: Serialisable

cellColor

Values must be of type <class ‘bool’>

dxfId

Values must be of type <class ‘int’>

tagname = 'colorFilter'
class openpyxl.worksheet.filters.CustomFilter(operator='equal', val=None)[source]

Bases: Serialisable

convert()[source]

Convert to more specific filter

operator

Value must be one of {‘lessThanOrEqual’, ‘notEqual’, ‘greaterThanOrEqual’, ‘equal’, ‘lessThan’, ‘greaterThan’}

tagname = 'customFilter'
val

Values must be of type <class ‘str’>

class openpyxl.worksheet.filters.CustomFilters(_and=None, customFilter=())[source]

Bases: Serialisable

customFilter

A sequence (list or tuple) that may only contain objects of the declared type

tagname = 'customFilters'
class openpyxl.worksheet.filters.DateGroupItem(year=None, month=None, day=None, hour=None, minute=None, second=None, dateTimeGrouping=None)[source]

Bases: Serialisable

dateTimeGrouping

Value must be one of {‘year’, ‘month’, ‘second’, ‘day’, ‘hour’, ‘minute’}

day

Values must be of type <class ‘float’>

hour

Values must be of type <class ‘float’>

minute

Values must be of type <class ‘float’>

month

Values must be of type <class ‘float’>

second

Values must be of type <class ‘int’>

tagname = 'dateGroupItem'
year

Values must be of type <class ‘int’>

class openpyxl.worksheet.filters.DynamicFilter(type=None, val=None, valIso=None, maxVal=None, maxValIso=None)[source]

Bases: Serialisable

maxVal

Values must be of type <class ‘float’>

maxValIso

Values must be of type <class ‘datetime.datetime’>

tagname = 'dynamicFilter'
type

Value must be one of {‘Q3’, ‘M1’, ‘today’, ‘M3’, ‘lastMonth’, ‘M5’, ‘nextYear’, ‘thisYear’, ‘yearToDate’, ‘aboveAverage’, ‘Q2’, ‘Q4’, ‘M4’, ‘M2’, ‘M10’, ‘lastWeek’, ‘thisQuarter’, ‘nextMonth’, ‘nextQuarter’, ‘null’, ‘thisWeek’, ‘M8’, ‘belowAverage’, ‘M11’, ‘Q1’, ‘thisMonth’, ‘tomorrow’, ‘M12’, ‘M9’, ‘yesterday’, ‘M6’, ‘M7’, ‘lastQuarter’, ‘nextWeek’, ‘lastYear’}

val

Values must be of type <class ‘float’>

valIso

Values must be of type <class ‘datetime.datetime’>

class openpyxl.worksheet.filters.FilterColumn(colId=None, hiddenButton=False, showButton=True, filters=None, top10=None, customFilters=None, dynamicFilter=None, colorFilter=None, iconFilter=None, extLst=None, blank=None, vals=None)[source]

Bases: Serialisable

colId

Values must be of type <class ‘int’>

col_id

Values must be of type <class ‘int’>

colorFilter

Values must be of type <class ‘openpyxl.worksheet.filters.ColorFilter’>

customFilters

Values must be of type <class ‘openpyxl.worksheet.filters.CustomFilters’>

dynamicFilter

Values must be of type <class ‘openpyxl.worksheet.filters.DynamicFilter’>

extLst

Values must be of type <class ‘openpyxl.descriptors.excel.ExtensionList’>

filters

Values must be of type <class ‘openpyxl.worksheet.filters.Filters’>

hiddenButton

Values must be of type <class ‘bool’>

iconFilter

Values must be of type <class ‘openpyxl.worksheet.filters.IconFilter’>

showButton

Values must be of type <class ‘bool’>

tagname = 'filterColumn'
top10

Values must be of type <class ‘openpyxl.worksheet.filters.Top10’>

class openpyxl.worksheet.filters.Filters(blank=None, calendarType=None, filter=(), dateGroupItem=())[source]

Bases: Serialisable

blank

Values must be of type <class ‘bool’>

calendarType

Value must be one of {‘hijri’, ‘taiwan’, ‘gregorianUs’, ‘thai’, ‘gregorianXlitEnglish’, ‘korea’, ‘saka’, ‘gregorianArabic’, ‘gregorian’, ‘gregorianXlitFrench’, ‘japan’, ‘gregorianMeFrench’, ‘hebrew’}

dateGroupItem

A sequence (list or tuple) that may only contain objects of the declared type

filter

A sequence of primitive types that are stored as a single attribute. “val” is the default attribute

tagname = 'filters'
class openpyxl.worksheet.filters.IconFilter(iconSet=None, iconId=None)[source]

Bases: Serialisable

iconId

Values must be of type <class ‘int’>

iconSet

Value must be one of {‘3TrafficLights1’, ‘3TrafficLights2’, ‘3Symbols2’, ‘4Arrows’, ‘3Symbols’, ‘4RedToBlack’, ‘3ArrowsGray’, ‘5Quarters’, ‘4TrafficLights’, ‘5Rating’, ‘4Rating’, ‘5ArrowsGray’, ‘3Flags’, ‘3Signs’, ‘5Arrows’, ‘3Arrows’, ‘4ArrowsGray’}

tagname = 'iconFilter'
class openpyxl.worksheet.filters.NumberFilter(operator='equal', val=None)[source]

Bases: CustomFilter

operator

Value must be one of {‘lessThanOrEqual’, ‘notEqual’, ‘greaterThanOrEqual’, ‘equal’, ‘lessThan’, ‘greaterThan’}

val

Values must be of type <class ‘float’>

class openpyxl.worksheet.filters.SortCondition(ref=None, descending=None, sortBy=None, customList=None, dxfId=None, iconSet=None, iconId=None)[source]

Bases: Serialisable

customList

Values must be of type <class ‘str’>

descending

Values must be of type <class ‘bool’>

dxfId

Values must be of type <class ‘int’>

iconId

Values must be of type <class ‘int’>

iconSet

Value must be one of {‘3TrafficLights1’, ‘3TrafficLights2’, ‘3Symbols2’, ‘4Arrows’, ‘3Symbols’, ‘4RedToBlack’, ‘3ArrowsGray’, ‘5Quarters’, ‘4TrafficLights’, ‘5Rating’, ‘4Rating’, ‘5ArrowsGray’, ‘3Flags’, ‘3Signs’, ‘5Arrows’, ‘3Arrows’, ‘4ArrowsGray’}

ref
sortBy

Value must be one of {‘cellColor’, ‘icon’, ‘fontColor’, ‘value’}

tagname = 'sortCondition'
class openpyxl.worksheet.filters.SortState(columnSort=None, caseSensitive=None, sortMethod=None, ref=None, sortCondition=(), extLst=None)[source]

Bases: Serialisable

caseSensitive

Values must be of type <class ‘bool’>

columnSort

Values must be of type <class ‘bool’>

extLst

Values must be of type <class ‘openpyxl.descriptors.excel.ExtensionList’>

ref
sortCondition

A sequence (list or tuple) that may only contain objects of the declared type

sortMethod

Value must be one of {‘pinYin’, ‘stroke’}

tagname = 'sortState'
class openpyxl.worksheet.filters.StringFilter(operator='contains', val=None, exclude=False)[source]

Bases: CustomFilter

exclude

Values must be of type <class ‘bool’>

operator

Value must be one of {‘endswith’, ‘contains’, ‘startswith’, ‘wildcard’}

to_tree(tagname=None, idx=None, namespace=None)[source]
val

Values must be of type <class ‘str’>

class openpyxl.worksheet.filters.Top10(top=None, percent=None, val=None, filterVal=None)[source]

Bases: Serialisable

filterVal

Values must be of type <class ‘float’>

percent

Values must be of type <class ‘bool’>

tagname = 'top10'
top

Values must be of type <class ‘bool’>

val

Values must be of type <class ‘float’>