[OpenPyXL] 7. 데이터 필터 적용하기(feat. add_filter_column)

---

OpenPyXL에서 add_filter_column을 이용하여 저장할 수 있다. 이번 포스팅에서는 OpenPyXL에서 제공하는 add_filter_column의 사용법과 이를 이용하여 엑셀 파일에 데이터 필터를 적용하는 방법을 알아보려고 한다. 

 

- 목차 -

1) 필터만 생성하기

2) 필터 적용하기

3) 사용자 지정 필터

---

   데이터 필터 적용하기 : add_filter_column

1) 필터만 생성하기

필터를 생성주는 것은 2가지 과정을 거치게 된다. 먼저 1) 데이터 필터 적용 범위를 설정하고 2) add_filter_column을 이용하여 필터를 생성할 수 있다. 그러고 나서 저장하면 첫 번째 행에 필터 버튼이 생성되어 있다.

 

add_filter_column은 필터를 적용할 칼럼 인덱스와 필터 값을 문자열로 바꾸고 이를 리스트에 담아서 인자로 넘겨준다.

---

add_filter_column( '칼럼 인덱스', '필터 적용 값 리스트')

---

이제 코드를 통해서 add_filter_column 사용법을 확인해보자. 먼저 데이터를 생성하고 전체 데이터에 대한 범위를 셀 주소로 바꾼다. 이때 범위를 나타내는 셀 주소는 '좌상단 셀 주소 : 우하단 셀 주소' 형식으로 나타내어야 하며 이를 Worksheet.auto_filter.ref에 넘겨줘야 한다(line 46).

 

다음으로 Worksheet.auto_filter의 add_filter_column 메서드를 이용하여 필터를 추가해야한다. 여기서는 필터만 생성(첫 번째 행에 필터 버튼을 생성)하려고 한다. 이때에는 칼럼 인덱스를 0, 필터 적용 값은 빈 리스트로 지정만 하면 된다(line 47).

 

import numpy as np

from openpyxl import Workbook
from openpyxl.utils import get_column_letter

## 행과 열을 셀번지로 바꿈
def number_to_coordinate(rc):
    row_idx, col_idx = rc[0], rc[1]
    col_string = get_column_letter(col_idx)
    return f'{col_string}{row_idx}'

## 엑셀에 넣을 데이터
data = np.array([
   ['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]
])

wb = Workbook()
ws = wb.active

for i in range(data.shape[0]):
    ws.append(data[i, :].tolist())

## 필터 범위
start_row, start_col = 1, 1
end_row, end_col = data.shape

cell_range = f'{number_to_coordinate((start_row, start_col))}:{number_to_coordinate((end_row, end_col))}'
print(cell_range)

ws.auto_filter.ref = cell_range ## 필터 범위 지정
ws.auto_filter.add_filter_column(0, []) ## 필터 생성

wb.save('filtered_file.xlsx')
wb.close()

 

위 코드를 실행하면 아래 그림과 같이 첫 번째 행에 필터가 적용된 것을 알 수 있다.

 

---

2) 필터 적용하기

첫 번째 칼럼에 대하여 값이 'East'인 것을 필터링해보자. 그렇다면 다음과 같이 하면 될 것이다(line 7~8).

 

wb = Workbook()
ws = wb.active

for i in range(data.shape[0]):
    ws.append(data[i, :].tolist())

ws.auto_filter.ref = cell_range
ws.auto_filter.add_filter_column(0, ['East']) ## 첫 번째 칼럼에서 East인 것들만 필터링한다.

wb.save('filtered_file.xlsx')
wb.close()

 

하지만 실제로 파일을 열어보면 필터 버튼에서 'East'는 표시되어 있지만 'East'가 아닌 'West', 'North', 'South' 값도 표시되어 있다. 즉, 필터가 적용되지 않은 것이다.

 

필터 박스에 체크는 되어 있지만 실제로 필터가 적용되진 않았다.

OpenPyXL 문서에 가보니 다음과 같이 필터 박스는 체크되어 있지만 실제로 필터나 정렬은 이루어지지 않는다고 써져 있었다.

 

 

그렇다면 방법은 없는 것인가?...

OpenPyXL 문서에서 안된다고 하니 필터를 자동적으로 적용할 수는 없다. 다만 적용된 것처럼 보일 수는 있다. 즉, 'East'가 아닌 행을 숨기는 것이다.

 

아래 코드에서 line 13~15을 보면 2번째 행부터 맨 끝 행까지 돌면서 셀 값이 'East'가 아닌 경우 그 행을 숨김처리하는 과정이 추가된 것을 알 수 있다. 이때 row_dimensions와 hidden 필드를 True로 설정한 것을 기억하자.

 

wb = Workbook()
ws = wb.active

for i in range(data.shape[0]):
    ws.append(data[i, :].tolist())

target_column_idx = 1
condition = ['East']    

ws.auto_filter.ref = cell_range
ws.auto_filter.add_filter_column(target_column_idx-1, condition) ## 첫 번째 칼럼에서 East인 것들만 필터링한다.

for row_idx in range(2, ws.max_row+1):
    if ws.cell(row_idx, target_column_idx).value not in condition:
        ws.row_dimensions[row_idx].hidden = True ## East가 아닌 행은 숨기기

wb.save('filtered_file.xlsx')
wb.close()

 

 

위 코드를 실행하면 위와 같이 필터링되어 저장된 것을 알 수 있다(사실 필터링 적용된 것처럼 보이게 한 것이다).

---

3) 사용자 지정 필터

만약 특정 칼럼이 숫자형인 경우 사용자 지정 필터를 설정할 수도 있다. 이때에는 CustomFilter, CustomFilters, FilterColumn 객체를 이용해야 한다. 아래 코드는 Units라는 칼럼에 대해서 7000보다 큰 것들만 필터링하는 사용자 지정 필터를 만들고 필터 체크 박스에 해당 값들을 표시하며 실제로 필터가 적용된 결과를 저장한다.

 

from openpyxl.worksheet.filters import CustomFilter, CustomFilters, FilterColumn

wb = Workbook()
ws = wb.active

## 데이터 삽입
for i in range(data.shape[0]):
    ws.append(data[i, :].tolist())

target_column = 4 ## 4 번째 칼럼
condition = '7000' ## 기준 값

filter_condition = CustomFilter(operator='greaterThan', val=condition) ## CustomFilter 객체 생성
custom_filters = CustomFilters(customFilter=[filter_condition]) ## CustomFilter 객체가 담긴 리스트를 넣어야한다.

## 정수형으로 바꿔준다.
start_row = number_to_coordinate((2, target_column)) ## D2
end_row = number_to_coordinate((ws.max_row, target_column)) ## D17
for x in ws[start_row:end_row]:
    cell = x[0]
    cell.value = int(cell.value)

value_str = [str(x) for x in data[1:, 3].astype(int) if x > 7000]
value_int = [x for x in data[1:, 3].astype(int) if x > 7000]
print(value_str) ## 필터링 값 출력
ws.auto_filter.add_filter_column(target_column-1, value_int) ## 필터 체크 박스 표시

## 필터 범위 설정하고 fillterColoumn에 FilterColumn 객체 append
ws.auto_filter.ref = cell_range
ws.auto_filter.filterColumn.append(FilterColumn(target_column-1, 
                                                customFilters=custom_filters, 
                                                ))

## 조건을 만족하지 않는 행은 숨김처리
for row_idx in range(2, ws.max_row+1):
    if ws.cell(row_idx, target_column).value not in value_int:
        ws.row_dimensions[row_idx].hidden = True

wb.save('filtered_file.xlsx')
wb.close()

 

line 13~14

먼저 CustomFilter 객체를 생성한다. operator 인자에 부등호 같은 조건식을 나타내는 문자열을 입력하고 val 인자에는 기준 값을 (아무리 숫자라 하더라도) 문자열로 넣어준다. 참고로 operator에 넣을 수 있는 문자열은 다음과 같다(line 13). 

 

greaterThanOrEqual
lessThan
lessThanOrEqual
equal
notEqual
greaterThan

 

그런 다음 CustomFilters 객체에 앞에서 정의한 CustomFilter 객체를 리스트에 넣어서 customFilter인자에 넣어준다(line 14).

 

line 17~21

이때 주의할 점은 필터 체크 박스에 조건에 만족하는 값을 체크하기 위하여 반드시 숫자형으로 바꿔줘야한다는 것이다.

 

line 23~26

필터 체크 박스에서 조건에 만족하는 값에 체크를 하기 위한 작업이다. 이때 add_filter_column에서 칼럼 인덱스와 필터 박스 체크할 값을 숫자라 하더라도 문자로 바꾸어서 리스트에 담아야 한다(line 26).

 

line 29~32

데이터 필터 범위를 지정하고(line 29) FilterColumn 객체를 추가해준다. 이때 첫 번째 인자에는 칼럼 인덱스, 그리고 customFilters 인자에는 앞에서 만들어준 CustomFilters를 넘겨준다(line 30~32).

 

line 35~37

앞에서와 마찬가지로 필터는 생성되었으나 실제로 필터가 되진 않는다. 따라서 조건에 맞지 않는 행은 숨겨준다.

 

위 코드를 수행하고 필터링 버튼 클릭 -> 숫자 필터 -> 사용자 지정 필터로 들어가면 우리가 정의한 필터 조건식이 들어가 있음을 확인할 수 있다.

 

 

그러나....

아래 그림과 같이 조건을 만족하는 7195, 7580, 9814 값이 체크가 안되어 있었다. 이게 순서를 바꿔서 해봐도 문서를 참고해서 값을 설정해봐도 안됐다. 이게 사용자 지정 필터를 적용하면 체크 박스에 체크를 못하고, 체크 박스에 체크를 하면 사용자 지정 필터가 인식이 안되었다. 즉, 둘 중 하나는 포기해야 했다. 혹시 사용자 지정 필터와 체크 박스에 체크를 동시에 할 수 있는 방법을 아는 분이 있다면 알려주셨으면 좋겠다.

 

---

참고자료

파이썬으로 엑셀(xlsx) 파일 다루기, openpyxl 사용법! - https://m.blog.naver.com/huangha/221871202916

---