Python Xlsxwriter 简明教程

Python XlsxWriter - Applying Filter

在 Excel 中，你可以根据使用逻辑表达式的条件，对表格数据设置筛选器。在 XlsxWriter 的工作表类中，我们有 autofilter() 方法或其用途。该方法的必需参数是单元格区域。这会在标题行中创建下拉选择器。要应用一些条件，我们有两个可用的方法 − filter_column() 或 filter_column_list() 。

In Excel, you can set filter on a tabular data based upon criteria using logical expressions. In XlsxWriter’s worksheet class, we have autofilter() method or the purpose. The mandatory argument to this method is the cell range. This creates drop-down selectors in the heading row. To apply some criteria, we have two methods available − filter_column() or filter_column_list().

Applying Filter Criteria for a Column

在以下示例中，范围 A1:D51（即单元格 0,0 到 50,3）中的数据被用作方法 autofilter() 的范围参数。筛选条件 'Region == East' 被用方法 filter_column() 设置在第 0 列（区域标题）。

In the following example, the data in the range A1:D51 (i.e. cells 0,0 to 50,3) is used as the range argument for autofilter() method. The filter criteria 'Region == East' is set on 0th column (with Region heading) with filter_column() method.

Example

数据范围中所有不满足筛选条件的行都通过为工作表对象的 set_row() 方法设置隐藏选项为 true 而隐藏。

All the rows in the data range not meeting the filter criteria are hidden by setting hidden option to true for the set_row() method of the worksheet object.

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
ws = wb.add_worksheet()

data = (
   ['Region', 'SalesRep', 'Product', 'Units'],
   ['East',   'Tom',      'Apple',    6380],
   ['West',   'Fred',     'Grape',    5619],
   ['North',  'Amy',      'Pear',     4565],
   ['South',  'Sal',      'Banana',   5323],
   ['East',   'Fritz',    'Apple',    4394],
   ['West',   'Sravan',   'Grape',    7195],
   ['North',  'Xi',       'Pear',     5231],
   ['South',  'Hector',   'Banana',   2427],
   ['East',   'Tom',      'Banana',   4213],
   ['West',   'Fred',     'Pear',     3239],
   ['North',  'Amy',      'Grape',    6520],
   ['South',  'Sal',      'Apple',    1310],
   ['East',   'Fritz',    'Banana',   6274],
   ['West',   'Sravan',   'Pear',     4894],
   ['North',  'Xi',       'Grape',    7580],
   ['South',  'Hector',   'Apple',    9814]
)
for row in range(len(data)):
   ws.write_row(row,0, data[row])

ws.autofilter(0, 0, 50, 3)

ws.filter_column(0, 'Region == East')

row = 1
for row_data in (data):
   region = row_data[0]

   if region != 'East':
      ws.set_row(row, options={'hidden': True})

   ws.write_row(row, 0, row_data)

   row += 1
wb.close()

Output

当我们使用 Excel 打开工作表时，我们会发现只有区域='东部’的行是可见的，其他行被隐藏了（你可以通过清除筛选器来再次显示它们）。

When we open the worksheet with the help of Excel, we will find that only the rows with Region='East' are visible and others are hidden (which you can display again by clearing the filter).

列参数可以是零索引列号或字符串列名。在 Python 中允许的所有逻辑运算符都可以在条件中使用（==、!=、<、>、⇐、>=）。可以针对多个列定义筛选条件，并且可以通过 and 或 or 运算符将它们组合起来。带逻辑运算符的条件示例如下 −

The column parameter can either be a zero indexed column number or a string column name. All the logical operators allowed in Python can be used in criteria (==, !=, <, >, ⇐, >=). Filter criteria can be defined on more than one columns and they can be combined by and or or operators. An example of criteria with logical operator can be as follows −

ws.filter_column('A', 'x > 2000')
ws.filter_column('A', 'x != 2000')
ws.filter_column('A', 'x > 2000 and x<5000')

请注意条件参数中的“ x ”只是一个形式上的占位符，它可以是任何适当的字符串，因为无论如何它在内部被忽略。

Note that "x" in the criteria argument is just a formal place holder and can be any suitable string as it is ignored anyway internally.

ws.filter_column('A', 'price > 2000')
ws.filter_column('A', 'x != 2000')
ws.filter_column('A', 'marks > 60 and x<75')

XlsxWriter 还允许在包含字符串数据的列上使用筛选条件中的通配符“ * ”和“ ? ”。

XlsxWriter also allows the use of wild cards "*" and "?" in the filter criteria on columns containing string data.

ws.filter_column('A', name=K*') #starts with K
ws.filter_column('A', name=*K*') #contains K
ws.filter_column('A', name=?K*') # second character as K
ws.filter_column('A', name=*K??') #any two characters after K

Example

在以下示例中，第一列上的第一个筛选器需要区域为西部，第二列筛选器的条件为“ units > 5000 ”。不满足条件“ region = West ”或“ units > 5000 ”的行被隐藏。

In the following example, first filter on column A requires region to be West and second filter’s criteria on column D is "units > 5000". Rows not satisfying the condition "region = West" or "units > 5000" are hidden.

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
ws = wb.add_worksheet()

data = (
   ['Region',  'SalesRep', 'Product', 'Units'],
   ['East',    'Tom',      'Apple',    6380],
   ['West',    'Fred',     'Grape',    5619],
   ['North',   'Amy',      'Pear',     4565],
   ['South',   'Sal',      'Banana',   5323],
   ['East',    'Fritz',    'Apple',    4394],
   ['West',    'Sravan',   'Grape',    7195],
   ['North',   'Xi',       'Pear',     5231],
   ['South',   'Hector',   'Banana',   2427],
   ['East',    'Tom',      'Banana',   4213],
   ['West',    'Fred',     'Pear',     3239],
   ['North',   'Amy',      'Grape',    6520],
   ['South',   'Sal',      'Apple',    1310],
   ['East',    'Fritz',    'Banana',   6274],
   ['West',    'Sravan',   'Pear',     4894],
   ['North',   'Xi',       'Grape',    7580],
   ['South',   'Hector',   'Apple',    9814])

for row in range(len(data)):
   ws.write_row(row,0, data[row])

ws.autofilter(0, 0, 50, 3)

ws.filter_column('A', 'x == West')
ws.filter_column('D', 'x > 5000')

row = 1
for row_data in (data[1:]):
   region = row_data[0]
   volume = int(row_data[3])

   if region == 'West' or volume > 5000:
      pass
   else:
      ws.set_row(row, options={'hidden': True})

   ws.write_row(row, 0, row_data)
   row += 1

wb.close()

Output

在 Excel 中，可以在标题 A 和 D 列上看到筛选器图标。下面是过滤后的数据 −

In Excel, the filter icon can be seen on columns A and D headings. The filtered data is seen as below −

Applying a Column List Filter

方法 filter_column_list() 可以用于表示具有 Excel 2007 样式的多选条件的筛选器。

The filter_column_list() method can be used to represent filters with multiple selected criteria in Excel 2007 style.

ws.filter_column_list(col,list)

第二个参数是给定列中的数据与之匹配的值列表。例如 −

The second argument is a list of values against which the data in a given column is matched. For example −

ws.filter_column_list('C', ['March', 'April', 'May'])

它会导致对数据进行筛选，以便列 C 中的值与列表中的任何项匹配。

It results in filtering the data so that value in column C matches with any item in the list.

Example

在以下示例中，方法 filter_column_list() 用于筛选 rows ，区域等于东部或西部。

In the following example, the filter_column_list() method is used to filter the rows with region equaling either East or West.

import xlsxwriter
wb = xlsxwriter.Workbook('hello.xlsx')
ws = wb.add_worksheet()
data = (
   ['Region', 'SalesRep', 'Product', 'Units'],
   ['East',   'Tom',      'Apple',    6380],
   ['West',   'Fred',     'Grape',    5619],
   ['North',  'Amy',      'Pear',     4565],
   ['South',  'Sal',      'Banana',   5323],
   ['East',   'Fritz',    'Apple',    4394],
   ['West',   'Sravan',   'Grape',    7195],
   ['North',  'Xi',       'Pear',     5231],
   ['South',  'Hector',   'Banana',   2427],
   ['East',   'Tom',      'Banana',   4213],
   ['West',   'Fred',     'Pear',     3239],
   ['North',  'Amy',      'Grape',    6520],
   ['South',  'Sal',      'Apple',    1310],
   ['East',   'Fritz',    'Banana',   6274],
   ['West',   'Sravan',   'Pear',     4894],
   ['North',  'Xi',       'Grape',    7580],
   ['South',  'Hector',   'Apple',    9814]
)
for row in range(len(data)):
   ws.write_row(row,0, data[row])

ws.autofilter(0, 0, 50, 3)

l1= ['East', 'West']
ws.filter_column_list('A', l1)

row = 1
for row_data in (data[1:]):
   region = row_data[0]

   if region not in l1:
      ws.set_row(row, options={'hidden': True})

   ws.write_row(row, 0, row_data)
   row += 1

wb.close()

Output

A 列显示了自动筛选器已应用。区域为东部或西部的所有行都显示，其余行被隐藏。

The Column A shows that the autofilter is applied. All the rows with Region as East or West are displayed and rest are hidden.

从 Excel 软件中，单击标题 Region 中的 filter 选择器箭头，我们应该看到已应用区域等于东部或西部的筛选器。

From the Excel software, click on the filter selector arrow in the Region heading and we should see that the filter on region equal to East or West is applied.