Python - 엑셀 자료 분석(셀 참조 with xlwings)

오늘은 KB 부동산시세 사이트에서 제공하는 데이터를 이용해 각 셀의 값을 참조하는 방법에 대해 알아보겠습니다. 테스트를 위해 엑셀은 서식을 없애고 숫자만 표시되도록 했는데요, 아래의 엑셀 차트에 대해 일정 범위별로 수식을 적용하여 색깔을 표시하도록 하는 방법을 알아보겠습니다. (물론 엑셀 활용 측면에서는 조건부 서식으로 지정하는 것이 더 간단할 수도 있습니다만, xlwings 공부하는 차원에서 봐주시기 바랍니다.)

원본

작업 후

1. 엑셀 매크로 추가

엑셀 파일의 이름은 pyxl.xlsm으로 매크로가 가능한 엑셀파일 형태로 저장해둡니다. 그리고 엑셀에서 Python 코드를 불러올 수 있도록 VBA를 작성해줍니다.

Sub some_code()
    mymodule = Left(ThisWorkbook.Name, (InStrRev(ThisWorkbook.Name, ".", -1, vbTextCompare) - 1))
    RunPython "import " & mymodule & ";" & mymodule & ".main()"
End Sub

 

2. Python 스크립트 작성

아래와 같이 Python 스크립트를 작성하여 pyxl.py로 같은 폴더에 저장해둡니다.

코드를 보시면, 2번째 시트(wb.sheets[2])를 sht로 지정하고, 데이터의 첫번째 열('B4:GG4')를 선택 후 끝까지 확장/지정하여 rng 변수에 할당합니다. 그리고 각 열, 각 칼럼을 순차적으로 범위 체크하여 셀의 색상을 지정합니다. 셀에 아무 값이 없을 경우 에러가 가므로 'is not None' 을 추가하여 빈 셀을 걸러주었습니다.

import xlwings as xw

def main():
    # object_file
    wb = xw.Book.caller()
    sht = wb.sheets[2]

    rng = sht.range('B4:GG4').expand('down')
    # 셀 별 색상 입히기
    for row in rng.rows:
        for cell in row.columns:
            if cell.value is not None:
                if cell.value < -0.4:
                    cell.color = (43, 104, 213)
                elif cell.value < -0.3:
                    cell.color = (103, 143, 215)
                elif cell.value < -0.2:
                    cell.color = (164, 188, 230)
                elif cell.value < -0.1:
                    cell.color = (209, 221, 243)
                elif cell.value < 0.1:
                    cell.color = (255, 255, 255)
                elif cell.value < 0.2:
                    cell.color = (253, 228, 157)
                elif cell.value < 0.3:
                    cell.color = (252, 202, 62)
                elif cell.value < 0.4:
                    cell.color = (255, 156, 25)
                else:
                    cell.color = (255, 117, 13)
                    
if __name__ == "__main__":
    xw.Book("pyxl.xlsm").set_mock_caller()
    main()

 

<결과>

스크립트 작성이 완료되면, 엑셀의 xlwings탭에서 'Run main'으로 실행합니다. 그런데 숫자가 아닌 텍스트가 들어있는 부분에서 에러가 나네요. 데이터를 정비하던 스크립트를 추가하던 해야겠네요.

오늘은 간단히 여기까지만 하겠습니다.

 

~~끝~~