[파이썬/업무자동화] Openpyxl 통해 엑셀 함수 사용하기 2편

※ OpenPyxl 관련 포스팅 모음

2021.03.19 - [코딩/업무자동화] - [파이썬/업무자동화] Openpyxl로 엑셀 함수 사용하기, 엑셀 수식 넣기

2021.08.21 - [코딩/업무자동화] - [파이썬/업무자동화] Openpyxl 통해 엑셀 함수 사용하기 2편

2021.08.30 - [코딩/업무자동화] - [파이썬/업무자동화] 엑셀에 입력 된 파일명 따라 이미지 삽입하기 - openpyxl

2021.10.04 - [코딩/업무자동화] - [파이썬/업무자동화] openpyxl.style 사용법 정리

2021.10.16 - [코딩/업무자동화] - [파이썬/업무자동화] openypyxl 통해 엑셀 조건부 서식 적용하기

2021.11.09 - [코딩/업무자동화] - [파이썬/업무자동화] openpyxl 시트 조작 방법 4가지

---

 

  이전에 openpyxl 통해 엑셀 함수 사용하기라는 포스팅을 작성한 적이 있다. 말 그대로 엑셀의 함수를 파이썬 문자열로 취급하여 직접 엑셀에 입력하는 방식이다. 이 방식은 한가지 단점을 가지고 있다. 예를 들면 아래와 같은 경우다.

 

1) 파이썬으로 엑셀 함수,수식을 입력한 후, 엑셀 파일로 저장한다.(Workbook 객체 저장)

2) 이 저장 된 엑셀 파일을 실행시키지 않고 다시 또다른 Workbook 객체로 생성하여 접근한다.

3) 함수로 입력 된 셀을 읽어오면 결과가 None으로 표시된다.

 

  말로 하면 이해가 힘들 수 있으니 아래 예시를 들어보려고 한다. 먼저 엑셀 파일을 한개 생성해서 "A1"에 오늘 날짜를 표시하고 "result.xlsx"라는 파일명으로 저장하는 함수 하나를 작성해보았다.

 

 

<코드>

import openpyxl as op

def writeExcelFunction():
    wb = op.Workbook() #새로운 Workbook 객체 생성
    ws = wb.active #활성화 된 Sheet를 WorkSheet 객체로 생성

    #"A1"에 오늘 날짜 엑셀 함수로 입력해보기
    ws.cell(row=1, Column=1).value = "=TODAY()"
    
    #result.xlsx 파일명으로 저장
    wb.save("result.xlsx")

 

<결과>

 

  결과 파일을 열어보면 "A1"에 오늘 날짜가 표시 되어 있다.(그냥 숫자가 표시되어 있는 사람은 셀서식을 날짜로 바꿔보면 된다.)

 

결과

 

 

  이번에는 위 코드에서 "A1"에 입력 된 값을 다시 읽어오는 함수 코드를 추가로 작성했다. 참고로, openpyxl에서 엑셀의 수식이나 함수 계산값을 읽어올 때는 load_workbook( ) 함수 파라미터에 data_only=True를 입력해줘야 한다. data_only라는 항목은 엑셀에서 셀의 값이 수식이라면 수식을 읽어올건지 값을 읽어올건지 설정한다. True로 설정하면 계산된 값을 출력한다.

 

 

<코드>

def LoadExcelFunction():
    wb = op.load_workbook("result.xlsx", data_only=True)
    ws = wb.active
    date = ws.cell(row=1, column=1).value
    print(date)

 

 그리고 다시 함수 2개를 연달아 실행해보자. 

 

 

<실행부 코드>

if __name__ == "__main__":
    writeExcelFunction()
    LoadExcelFunction()

 

<결과>

None

 

 

  엑셀에 출력 된 날짜가 아니라 데이터가 없다는 None이 출력된다. 이 상태에서 결과 엑셀 파일 "result.xlsx" 파일을 열어서 저장하고 닫고 다시 실행시켜보자.

 

 

<결과>

2021-08-21 00:00:00

 

  엑셀 파일을 저장하고 재실행하면 위 결과처럼 "=TODAY()"라는 함수 결과가 정상적으로 출력된다.

 

  위 현상의 원인은 openpyxl의 특성 때문이다. 위 작성한 코드를 실행하면 함수 수식 "=TODAY()"는 정상적으로 엑셀 파일에 입력되고 저장된다. 그래서 data_only=True를 빼고 데이터를 읽어오면 아래처럼 함수 문자열을 그대로 결과로 읽어온다.

 

 

=TODAY()

 

 

  그런데, data_only=True를 넣으면 값을 읽어오지 못하고 None으로 출력된다. openpyxl은 수식을 문자열로 인식하여 엑셀에 직접 입력할 수는 있지만 실제 함수나 수식에 대한 계산을 실행하지는 않는다. 실제 함수,수식에 대한 계산은 엑셀 프로그램의 영역이기 때문이다. 그래서 엑셀을 실행시키고 저장하여 코드를 실행하면 함수가 정상적으로 적용되기 때문에 원하는 결과값을 읽어올 수 있다.

 

  그럼 무조건 사용자가 엑셀을 실행해야 함수값을 읽어올 수 있을까? 실제 openpyxl을 사용하는 사람들은 엑셀 자동화를 목적으로 하기 때문에, 매우 번거로운 작업이 된다. 이럴 때는 win32com.client 라는 모듈을 사용하면 된다. wim32com.client는 MS Office(Excel, Word, ppt) 등을 파이썬으로 제어할 수 있는 모듈이다. 이 모듈을 활용하면 실제 엑셀 파일을 열고 저장하는 것처럼 코드를 작성할 수 있다.

 

  그럼 위 LoadExcelFunction을 일부 수정해보도록 하자.

 

 

<수정 코드>

import win32com.client

def LoadExcelFunction():
    #win32com
    #excel 사용할 수 있게 설정
    excel = win32com.client.Dispatch("Excel.Application")
    #임시 Workbook 객체 생성 및 엑셀 열기
    temp_wb = excel.Workbooks.Open("result.xlsx")
    #저장
    temp_wb.Save()
    #excel 종료
    excel.quit()
    
    #openpyxl(기존코드)
    wb = op.load_workbook("result.xlsx", data_only=True)
    ws = wb.active
    date = ws.cell(row=1, column=1).value
    print(date)

 

 

  위 코드에서 win32com 부분을 추가하면 된다. 엑셀 파일을 열고 저장하고 닫는 코드를 추가한 것이다. 위 코드를 추가하면 None이 아닌 정상적인 결과를 확인할 수 있다.