Python - 엑셀 .xlsm 파일 다루기

.XLSM 파일이란? 

확장자가 XLSM인 파일은 매크로를 지원하는 스프레드시트 파일 유형입니다. 매크로는 Visu

al Basic Editor를 사용하여 Excel 통합 문서 내에서 Microsoft의 VBA(Visual Basic for Applications)로 프로그래밍되며 직접 실행할 수 있습니다.

엑셀 xlsm 형식의 파일을 다룰 수 있는 파이썬 라이브러리는  openpyxl , xlwings, editpyxl, asposecells, pandas 등등 많은 라이브러리가 있습니다.

openpyxl  라이브러리는 xlsm 파일 저장 시 컨트롤 버튼이 사라지는 현상이 있습니다.

본 문서에서는 xlwings 라이브러리를 사용하도록 하겠습니다.

 

🟢 파이썬 환경

-OS : Window10

- Python : 3.10.1

- xlwings : 0.30.10

- pip : 23.2.1

 

🟢 xlwings  인스톨

xlwings 라이브러리를 사용하기 위해 인스톨합니다.

pip install xlwings

🟢 xlsm 파일 열고 저장하기

파일이 있는 절대 경로를 지정하거나 작업하는 디렉토리에 있는 파일이 있어야 합니다. 윈도우의 디렉토리 경로는 '/'를 쓰거나 '\\' 를 사용합니다. '\' 문자는 escape 문자이기때문에 하나를 더 써야 합니다.

아래는 xlsm 파일을 열고 A1 열에 데이터를 쓴 후 저장하는 파일썬 파일입니다.

import xlwings as xw

work_dir = 'D:/python_project/'
wb = xw.Book(work_dir + 'excel_macro.xlsm')

sheet = wb.sheets['Sheet1']
sheet.range('A1').value = 'xlwings 라이브러리'

wb.save()

다른 이름으로 저장하려면 다음과 같이 save 함수에 저장하고자 하는 파일명을 쓰면 됩니다.

wb.save(work_dir + 'result_excel_macro.xlsm')

 

🟢 범위 확장

아래와 같이 쓰면 A1 셀을 기준으로 2개의 행과 2개의 열에 값을 입력합니다.

sheet['A1'].value = [['Foo 1', 'Foo 2', 'Foo 3'], [10.0, 20.0, 30.0]]
sheet['A1'].expand().value

 

📑 엑셀 Macro에서 파이썬 호출

xlwings 엑셀 Add-in 설치

명령창에 아래와 같이 명령어를 실행하여 xlwings add-in 을 엑셀에 설치합니다.

xlwings addin install

엑셀에서 도구모음. 

 

엑셀에 추가된 add-in 메뉴의 xlwings에서 [Run main] 버튼을 클릭하거나 RunPython 함수를 사용하여 VBA에서 Python 함수를 호출할 수 있습니다.

[Run main]  버튼에는 엑셀파일과 같은 이름의 Python 모듈에 main이라는 함수가 필요합니다. 이 방식은 엑셀 문서에 매크로를 사용할 필요 없이 xlsx로 저장할 수 있습니다.

엑셀 파일이 있는 위치에 hello_world.xlsx 파일을 만들고 같은 위치에 hello_world.py 파일을 아래와 같이 생성합니다.

# hello_world.py

# hello_world.py
import xlwings as xw

def main():    
    wb = xw.Book.caller()
    wb.sheets[0]['A1'].value = 'Hello World!'

엑셀파일에서 xlwings 메뉴의 [Run main] 버튼을 클릭합니다. 아래와 같이 A1 Cell에 Hello World! 문자가 입력됩니다.

모듈 이름과 관계없이 Python 함수를 호출하려면 RunPython을 사용합니다.

엑셀의 VBA에 [도구] - [참조] 버튼을 클릭하고 xlwings를 참조하도록 선택합니다.

RunPython 함수를 사용한 VBA 스크립트 입니다. hello.xlsm 파일을 만들고 아래처럼 VBA 스크립트를 작성합니다.

Sub HelloWorld()
    RunPython "import hello; hello.world()"
End Sub

기본적으로 "RunPython"은 Excel 과 파이썬 파일이 동일한 디렉터리에 같은 이름을 가졌다고 가정하지만, 파일 이름과 디렉토리 위치를 다르게 지정할 수 있습니다. Python 파일이 다른 폴더에 있는 경우 PYTHONPATH에 해당 폴더를 추가합니다. 파일 이름이 다른 경우 RunPython 명령어에 파일 이름을 지정합니다.

xlwings.Book.caller()를 사용하여 Excel  Workbook을 호출하는 예제입니다.

아래는 hello.py 파일 내용입니다.

# hello.py
import xlwings as xw

def world():
    wb = xw.Book.caller()
    wb.sheets[0]['B1'].value = 'RunPython : Hello World! '

이제 위에서 작성한 엑셀의 "HelloWorld" VBA 모듈을 실행합니다.

 

 

데이터가 없는 마지막 row 찾기

num_row = sheet.range('A1').end('down').row

데이터가 없는 마지막 column 찾기

num_col = sheet.range('A1').end('right').column

 

⚙ 예제 소스 : 

# 1. ERWind에서 csv 파일 생성
# [복제], [외부], [연계] row 삭제
# 3. xlsx 복사해서 표준 xlsm 파일로 붙여넣기
# 4. 매크로 실생
# 5. 저장

import win32com.client
import xlwings as xw

work_dir = 'D:/ERD/report/entity.csv/'
csv_wb = xw.Book(work_dir + 'SSGD_validation.csv')
xls_wb = xw.Book(work_dir + 'TOBE_SSGD_표준관리_로컬_v1.0.xlsm')
csv_sheet = csv_wb.sheets['SSGD_validation']
xls_sheet1 = xls_wb.sheets['변환기']
xls_sheet2 = xls_wb.sheets['테이블컬럼점검']

all_data_range = csv_sheet.range ("A1").expand ('table')
all_data_range.api.WrapText = False

# [복제], [외부], [연계] 삭제
check_char = '['
before_del_last_row = csv_sheet.range(1,1).end('down').row
for i in range(before_del_last_row, 1, -1):
    if csv_sheet.range(i,1).value[0] == check_char:
        csv_sheet.api.Rows(i).Delete()

# [복제], [외부], [연계] 삭제 후 row, column 건수
last_row = csv_sheet.range(1,1).end('down').row
csv_sheet.range(1,11).value = 'DataType Check'
csv_sheet.range((2,11), (last_row, 11)).options(ndim=2).value = '=exact(H2,J2)'
# last_col = csv_sheet.range(1,1).end('right').column

# 데이터를 복사하기 전에 표준 xlxm 파일을 정리한다.
standard_last_row = xls_sheet1.range(1,1).end('down').row
xls_sheet1.range((2,1), (standard_last_row, 3)).options(ndim=2).value = None
standard_last_row = xls_sheet2.range(1,1).end('down').row
xls_sheet2.range((2,1), (standard_last_row, 10)).options(ndim=2).value = None
xls_sheet2.range((2,13), (standard_last_row, 13)).options(ndim=2).value = None
xls_sheet2.range((2,14), (standard_last_row, 14)).options(ndim=2).value = None

# cvs 내용을 xlsm 으로 전체 복사
xls_sheet2.range((2,1), (last_row, 10)).options(ndim=2).value = csv_sheet.range((2,1), (last_row, 10)).options(ndim=2).value

# cvs 에서 엔터티명을 xlsm 으로 복사
xls_sheet1.range((2,1), (last_row, 1)).options(ndim=2).value = csv_sheet.range((2,1), (last_row, 1)).options(ndim=2).value

# [표준체크] Macro 수행
ko_to_en_macro = xls_wb.macro('한영변환')
ko_to_en_macro()
# [표준체크] Macro 수행결과를 [테이블컬럼점검] tab의 테이블명으로 복사
xls_sheet2.range((2,13), (last_row, 13)).options(ndim=2).value = xls_sheet1.range((2,3), (last_row, 3)).options(ndim=2).value

# cvs 에서 속성명을 xlsm 으로 복사
xls_sheet1.range((2,1), (last_row, 1)).options(ndim=2).value = csv_sheet.range((2,4), (last_row, 4)).options(ndim=2).value
# [표준체크] Macro 수행
ko_to_en_macro = xls_wb.macro('한영변환')
ko_to_en_macro()

# [표준체크] Macro 수행결과를 [테이블컬럼점검] tab의 컬럼명으로 복사
xls_sheet2.range((2,14), (last_row, 14)).options(ndim=2).value = xls_sheet1.range((2,3), (last_row, 3)).options(ndim=2).value


# [테이블컬럼점검] tab을 활성화(Visible)
xls_sheet2.activate()

all_data_range = xls_sheet2.range ("A1").expand ('table')
all_data_range.api.WrapText = False

xls_wb.save()
# csv_wb.save()