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

openpyxl이라는 패키지를 접하고 엑셀 파일을 가공하는 데 재미를 붙이고 있습니다. 사용법도 쉽고 엑셀에서 하는 많은 일들이 가능합니다. 하지만 세상 모든 일이 뜻대로 되지는 않는 법. 약간의 한계와 어려움도 존재합니다. 오늘은 파이썬 openpyxl, 엑셀 필터(filter) 걸기에 대해서 알아보겠습니다.

 

 

1. auto_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")

 

 

 

2. auto_filter로 되지 않는 부분

그런데 수치형 데이터 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를 이용하라고 하는데, 워크시트 객체에는 이런 모듈이 정의되어 있지 않습니다. 위와 같은 방법으로 정의해서 엑셀 문서에도 반영이 됐는데, 실제 필터가 작동하지 않는 문제가 발생했습니다. 아래와 같이 필터는 입력되었지만, 값이 필터 되지 않는 것인데요.

 

 

auto_filter-필터-결과

 

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

 

 

여기저기 찾아본 결과 아래와 같은 코드로 필터의 효과를 내기는 했습니다. 방법은 이렇습니다.

워크 시트를 새로 만들고, 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")