openpyxlのオートフィルタ設定のメモ

# 背景
先日、複数のエクセル出力したテーブルに対して、「オートフィルタを設定して、空白のみ除外したい」といった状況になりまして、Pythonで自動化してみました。VBAでやるのが簡単なのかもしれませんが、個人的に最近Pythonをよく使うようになったので、**オートフィルタ設定を簡単にできる関数**の作成にトライしました。今回まとめたことを自分用にメモしておきます。

# オートフィルタの設定方法
まずは**openpyxl**を使ってオートフィルタの設定をするために必要となるAPIの使い方についてまとめます。APIの詳細は[公式](https://openpyxl.readthedocs.io/en/stable/filters.html)を参照して下さい。(英文ですので、適宜翻訳して下さい)

オートフィルタを行うために、今回は大きく次の2つの設定を利用しました。
- オートフィルタの範囲指定
- フィルタの絞り込み

公式によると他にもソートを行う設定があるようですが、本記事では扱いません。

それでは一つづつ、使い方を示します。

## オートフィルタの範囲指定
オートフィルタの範囲指定は、`ws.auto_filter.ref`にセルの範囲を指定することで行います。

(例)
```python
import openpyxl

if __name__ == '__main__':
    filename = 'エクセルファイル名'
    sheetname = 'シート名'
    wb = openpyxl.load_workbook(filename)
    ws = wb[sheetname]
    ws.auto_filter.ref = 'A1:F10'  # ← オートフィルタ範囲の設定
```

以降で示す今回作った関数では、上記のように範囲を直接指定することに加え、自動で全範囲を設定する対策も入れました。


## フィルタの絞り込み
オートフィルタの絞り込みを設定するには、`filterColumn`の設定を行います。`FilterColumn`オブジェクトを用いてフィルター対象の列指定および、絞り込みを行う文字列(`Filters`オブジェクトを使用)を以下のように指定します。

```python
import openpyxl
from openpyxl.worksheet.filters import (FilterColumn, Filters)

if __name__ == '__main__':
    filename = 'エクセルファイル名'
    sheetname = 'シート名'
    words = ['フィルタ表示文字1', 'フィルタ表示文字2', ...]
    wb = openpyxl.load_workbook(filename)
    ws = wb[sheetname]
    ws.auto_filter.ref = 'A1:F10'

    col = FilterColumn(colId=2)
    col.filters = Filters(filter=words)
    ws.auto_filter.filterColumn.append(col)  # ← C列の文字列がwordsに含まれる行のみを表示する
```

ただし、上記で絞り込み設定を行うのみでは、非表示となった行もエクセル上では表示状態となり、一度ファイルをエクセルで開き手動でオートフィルタを適用しなおす必要があります。今回作った関数ではこの手間をなくすため、非表示行に別途非表示設定を適用する対策を入れました。



# できたもの
今回作った`auto_filter`関数について説明します。

## 内容
引数で受け取ったワークシートに対して、オートフィルタの設定を適用する関数です。オプションで特定列の絞り込み表示、もしくは非表示を行えるように作りました。オプションを指定しない場合は、シート全範囲にオートフィルタを適用するのみの挙動となります。

## 引数
まずは引数についてですが、次の表のとおりです。

|引数名|内容|入力例|
|---|---|---|
|ws|openpyxlのワークシートオブジェクト。オートフィルタ設定の対象シート。|-|
|str_col|フィルタの絞り込みを設定する列名。省略時は'A'。|'B'|
|ref|フィルタを設定する範囲。省略時は全範囲。|'B1:D10'|
|shows|表示対象の文字列リスト。指定したもののみフィルタで表示。省略時は全て表示。|['Kiwi', 'Banana']|
|hiddens|非表示対象の文字列リスト。指定したものはフィルタで非表示。省略時は全て表示。|['Kiwi', 'Banana']|


## プログラム
続いて、プログラムの全体を示してます。

```auto_filter.py
import openpyxl
from openpyxl.utils import get_column_letter, column_index_from_string
from openpyxl.utils.cell import coordinate_from_string
from openpyxl.worksheet.filters import (FilterColumn, Filters)


def auto_filter(ws, str_col='A', ref=None, shows=None, hiddens=None):
    # オートフィルタの適用
    start = 'A1'
    end = get_column_letter(ws.max_column) + str(ws.max_row)
    if ref:
        start = ref.split(':')[0]
        end = ref.split(':')[1]
    ws.auto_filter.ref = start + ':' + end

    # 座標を数値に直す
    coord_s = coordinate_from_string(start)
    coord_e = coordinate_from_string(end)
    col = column_index_from_string(str_col)
    col_s, row_s = column_index_from_string(coord_s[0]), coord_s[1]
    if col < col_s:
        raise ValueError(f'col={str_col} < col_s={coord_s[0]}')
    col_e, row_e = column_index_from_string(coord_e[0]), coord_e[1]
    if col_e < col:
        raise ValueError(f'col_e={coord_e[0]} < col={str_col}')

    # 絞り込みなし
    if shows is None and hiddens is None:
        return

    # 表示フィルタの設定
    index = col - 1
    all_words = (set([row[index].value for row in ws.rows]))
    words = []
    if shows:
        words = shows
        if None in words:
            words.append('')
        elif '' in words:
            words.append(None)
        hiddens = list(all_words - set(words))
    else:
        if None in hiddens:
            hiddens.append('')
        elif '' in hiddens:
            hiddens.append(None)
        words = list(all_words - set(hiddens))
    col = FilterColumn(colId=index-col_s+1)
    col.filters = Filters(filter=words)
    ws.auto_filter.filterColumn.append(col)

    # 非表示の設定
    if hiddens:
        for row in ws.rows:
            target = row[index]
            if target.row > row_s:
                if target.value not in words:
                    ws.row_dimensions[target.row].hidden = True
```






## サンプル実行
サンプルとして、適当な値を入れたワークシートを作成し、そこにオートフィルタ設定を適用したうえで空白セルを非表示に絞り込み設定する例を示します。先ほどの*auto_filter.py*を同じフォルダに置いておいてください。

```sample.py
import openpyxl
from auto_filter import auto_filter

if __name__ == '__main__':
    wb = openpyxl.Workbook()
    ws = wb.active
    data = [
        ['Fruit', 'Quantity'],
        ['Kiwi', 3],
        ['Grape', 15],
        ['Apple', ''],
        ['Peach', None],
        ['Pomegranate', 3],
        ['Pear', 3],
        ['Tangerine', 3],
        ['Blueberry', 3],
        ['Mango', 3],
        ['Watermelon', ''],
        ['Blackberry', None],
        ['Orange', 3],
        ['Raspberry', 3],
        ['Banana', 3]
    ]
    for r in data:
        ws.append(r)
    auto_filter(ws, 'B', hiddens=[None])
    wb.save('filtered.xlsx')
```

(実行方法)
```
python sample.py
```

(実行結果)
上記を実行すると、カレントフォルダに*filterd.xlsx*が作成されます。非表示する対象に`None`を設定しており、空白セルは除外しています。(なお、`None`と`''`は同様に空白セルとみなします)

作成されたエクセルは以下の状態となっています。
![filterd.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2653390/978caaf6-c2e3-19ba-db69-60fe090674a7.png)

元もとの内容は次のとおりでした。
![unfilterd.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2653390/b2d4b3d6-fd6a-fa62-f8dd-b97ba19c9df0.png)

エクセル上のフィルタの設定は以下となっています。
![filtered2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2653390/a648eee3-2717-9dc2-0e23-85b8eef57ce6.png)




# 動作確認環境
今回の実行には以下の環境を使いました。

- [Python3.9以上](https://www.python.org/downloads/)
- openpyxl3.0.10

(openpyxlのインストール)
```
> pip install openpyxl
```

# 解説
作った関数の詳細を説明します。

## オートフィルタの適用
```python
start = 'A1'
end = get_column_letter(ws.max_column) + str(ws.max_row)
if ref:
    start = ref.split(':')[0]
    end = ref.split(':')[1]
ws.auto_filter.ref = start + ':' + end
```
`ref`引数を指定しない場合は、A1セルからシートの一番右下セルの範囲にフィルタを適用します。

セルの右下の範囲は以下で取得します。`get_column_letter`は引数の数値をAなど列の文字列に変換します。
```python
end = get_column_letter(ws.max_column) + str(ws.max_row)
```

`ref`の書式は`左上セル:右下セル`を想定しており、`:`でスプリットし、`start`に左上セル、`end`に右下セルを格納しています。
```python
if ref:
    start = ref.split(':')[0]
    end = ref.split(':')[1]
```

指定範囲にオートフィルタを設定する部分は、以下のように`ws.auto_filter.ref`に範囲を代入しています。指定がない場合は、シートの最大範囲としています。

```python
ws.auto_filter.ref = start + ':' + end
```

## 座標を数値に直す
```python
coord_s = coordinate_from_string(start)
coord_e = coordinate_from_string(end)
col = column_index_from_string(str_col)
col_s, row_s = column_index_from_string(coord_s[0]), coord_s[1]
if col < col_s:
    raise ValueError(f'col={str_col} < col_s={coord_s[0]}')
col_e, row_e = column_index_from_string(coord_e[0]), coord_e[1]
if col_e < col:
    raise ValueError(f'col_e={coord_e[0]} < col={str_col}')
```
`coordinate_from_string`でA1などセル指定の文字列から、`(列の文字列, 行の数字)`のタプルを取得しています。
また、`column_index_from_string`はAなど列の文字列の引数を元に、列の数値(1はじまり)を取得しています。

`ref`で指定した列の範囲の外に`col`の列が指定されていた場合は`ValuerError`例外としています。

## 絞り込みなし
`shows`や`hiddens`の絞り込みを設定しない場合は、フィルタ設定のみで終了します。
```python
if shows is None and hiddens is None:
    return
```

## 表示フィルタの設定
`shows`が指定された場合はそれらをフィルタで表示設定し、`shows`以外を非表示に設定します。また、`hiddens`が設定されていた場合は、全ての値から`hiddens`の値を除外してフィルタで表示設定します。

なお、空白セルは`None`または`''`(空文字)とし同等に扱います。


```python
index = col - 1
all_words = (set([row[index].value for row in ws.rows]))
words = []
if shows:
    words = shows
    if None in words:
        words.append('')
    elif '' in words:
        words.append(None)
    hiddens = list(all_words - set(words))
else:
    if None in hiddens:
        hiddens.append('')
    elif '' in hiddens:
        hiddens.append(None)
    words = list(all_words - set(hiddens))
col = FilterColumn(colId=index-col_s+1)
col.filters = Filters(filter=words)
ws.auto_filter.filterColumn.append(col)
```

## 非表示の設定
オートフィルタ設定のみではエクセルを開いた時に反映されないため、プログラム処理にて行の`hidden`属性を`True`に設定し個別に非表示するようにしています。
```python
if hiddens:
    for row in ws.rows:
        target = row[index]
        if target.row > row_s:
            if target.value not in words:
                ws.row_dimensions[target.row].hidden = True
```

以上です。よかったら参考にして下さい。