파이썬 openpyxl, 엑셀 필터(filter) 걸기!

2019. 11. 29. 06:30

openpyxl이라는 패키지를 접하고 엑셀 파일을 가공하는 데 재미를 붙이고 있다. 사용법도 쉽고 엑셀에서 하는 많은 일들이 가능하다. 하지만 세상 모든 일이 뜻대로 되지는 않는 법. 약간의 한계와 어려움도 존재한다.

오늘은 파이썬 openpyxl, 엑셀 필터(filter) 걸기에 대해서 알아보도록 하겠다.


엑셀_필터



openpyxl 패키지에는 filter를 걸 수 있는 기능이 있다. 바로 auto_filter이다. 하지만 값 단위로 필터를 걸 수 있어 약간의 한계가 존재한다.

방법은 간단한다. 엑셀 파일을 불러오고, 필터를 걸 영역을 설정해준다. 이 때 dimensions라는 변수를 이용하면 데이터가 있는 영역을 쉽게 선택할 수 있다. 다음으로 몇 번째 칼럼에 필터를 걸지와 어떤 값들을 지정할 지를 선택해주면 된다. 코드는 아래와 같다.

> import openpyxl


> wb = openpyxl.load_workbook(filename="text.xlsx")


> test.auto_filter.ref = test.dimensions  #필터를 걸 영역을 지정해준다.

> test.auto_filter.add_filter_column(3, vals=["1","2"])


> wb.save("test.xlsx")


그런데, 수치형 데이터 OO이하를 필터로 걸려고 하니 위의 방법대로 되지 않는다. 이것저것 검색해보며 아래와 같이 OO 이상의 값을 필터 걸 수 있었다.

> new_filter_1 = openpyxl.worksheet.filters.CustomFilter("greaterThan", val="90.0%")

> new_filter = openpyxl.worksheet.filters.CustomFilters(customFilter=[new_filter_1])


> test.auto_filter.ref = test.dimensions

> test.auto_filter.filterColumn.append(openpyxl.worksheet.filters.FilterColumn(5, customFilters=new_filter))


openpyxl 도큐먼트 싸이트에 가면 filter.CustomFilter를 이용하라고 하는데, 워크시트 객체에는 이런 모듈이 정의되어 있지 않다. 위와 같은 방법으로 정의해서 엑셀 문서에도 반영이 됐는데, 실제 필터가 작동하지 않는 문제가 발생했다. 아래와 같이 필터는 입력되었지만, 값이 필터되지 않는 것이다.

openpyxl_필터
( 90%이하로 필터했는데, 그 이상의 값이 남아 있다. )


확인해 본 결과, 필터를 입력할 수 있지만 필터의 조건이 반영되지는 않는다고 한다. 엑셀에서 필터를 적용하려면 어떤 기능들이 동작해야 한다. 하지만, openpyxl은 엑셀 파일에 필터 조건을 그냥 쓴 것이 때문에 필터 조건이 동작하지는 않는다. 이는 아래의 공식문서에서도 확인할 수 있다.
( 참조: openpyxl 공식 문서 filter부분 확인하기 )


여기저기 찾아본 결과 아래와 같은 코드로 필터의 효과를 내기는 했다. 정리해보면 워크 시트를 새로 만들고, for문을 돌면서 전체 행 중에서 필터 조건에 맞는 행을 찾는다. 그리고 새로운 워크시트에 붙여넣는다. 조건에 맞는 데이터로 새로운 워크시트를 만드는 것이다.

> import openpyxl


> wb = openpyxl.load_workbook(filename="file_name.xlsx")


> sh = wb["Sheet2"]

> sh2 = wb.create_sheet()


> for idx, row in enumerate(sh.iter_rows()):

>     if idx>1:

>             sh2.append((cell.value for cell in row))

>     else:

>         if float(row[5].value) <0.9 :

>             sh2.append((cell.value for cell in row))


> wb.save("test_filter.xlsx")



오늘은 이렇게 파이썬 openpyxl에서 엑셀 필터(filter) 거는 방법에 대해서 알아보았다. 필터를 걸 수는 있지만 동작하지 않는 문제가 있었다. 하지만, 조건에 맞는 행만 새롭게 만드는 방식으로 동일한 효과를 낼 수 있기는 하다. 좀 아쉽지만 언제가 업그레이드가 되면 개선될 수도 있지 않을까 기대해본다.


오픈API를 사용하면 다양한 재미있는 일들을 해 볼 수 있다. 파이썬을 활용한 오픈API 사용이 궁금하다면 아래 글을 참조해보자.

(참조: 오픈API를 활용한 사례는 어떤 것들이 있을까?)


댓글()