[win32com-2] 자물쇠 엑셀 불러와서 DataFrame으로 저장하기

보안이나 비밀번호가 걸린 엑셀은 openpyxl이나 xlrd로 열리지 않음. 

xlwings를 이용하려고 했지만,  xlwings를 이용한 코드는 xlwings가 안깔린 pc에서는 안돌아간다고.. 

배포용 exe를 만들어야하기 때문에 다른 방법을 찾기로 함. 

 

win32com 역시 보안 걸린 엑셀을 열 수 있다. 

 

 

 

 

[엑셀 열기]

 

win32com.client.Dispatch("Excel.Application") : 윈도우 백그라운드에 엑셀 프로그램을 실행한다. 

Workbook.Open(##경로##.xlsx) : 경로 내의 엑셀 파일을 열어 창을 띄운다. 엑셀 파일은 wb라고 명명. 

###엑셀어플리케이션####.Visible = True/False : 실행한 엑셀 파일창을 보이게 할지 / 안보이게 할지

###워크북###.Sheets("시트이름") 

excel = win32com.client.Dispatch("Excel.Application")       #자물쇠 엑셀을 열기위한 win32

wb = excel.Workbooks.Open(path1)                            # 입력받은 엑셀을 wb에 저장
excel.Visible = True                                        # False   / 엑셀 안띄우게하기
ws = wb.Sheets("시그널리스트")

 

 

[win32com으로 불러온 엑셀에서 범위 지정하기]

 

###워크시트###.UsedRange() : 내용이 있는 셀만 범위로 지정. 시트의 무한한 셀을 지정하지 않기 위해. 

###워크시트###.Range("A1: C3") / Range("A1","B2") : 특정 셀 혹은 일정 범위를 지정할 수 있다. 

ws.UsedRange()

 

 

[엑셀 데이터 pandas DataFrame으로 옮기기]

 

pd.DataFrame(ws.UsedRange())  : 엑셀 워크북에서 특정 범위만 추출해서 dataframe으로 저장한다. 여기서는 내용이 적힌 셀들만 가져온다. 

df.iloc[:, 7:38]  : 가져온 데이터프레임에서 특정 열만 가져온다 (엑셀의 "H:AL").

       아직 컬럼이름이 정해지기 전이기 때문에 iloc를 이용해서 열 인덱스로 지정한다.

df.rename(columns=) : 컬럼 명을 정해줄 수 있다. =뒤에 '@#$%' 직접 이름을 적어줄 수도 있고, 리스트 이름을 지정해줄 수도 있다.

. loc[]/.iloc[] 로 행을 지정해주면 데이터 프레임의 특정 행을 컬럼으로 설정할 수 있다. 0,1,2,3 .. 으로 자동으로 정해져있던 컬럼 인덱스가 정해준 컬럼이름으로 바뀐다. 

df.drop(df.index[0:7]) : 0~6 까지의 행을 삭제한다.  ('df='를 안붙여줘도 inplace=True 를 해주면 df에 덮어쓴다.) 컬럼 명이 있다면, columns=[#컬럼이름#, ... ] 해줘도 된다. 

df.reset_index() : 행, 열을 삭제함으로써 최초 DataFrame에서 가져올 때의 인덱스가 틀어졌는데, 다시 0 부터 인덱스를 만든다. 이 때 기존에 틀어진 인덱스를 맨 왼쪽 컬럼에 데이터로 저장하는데, 기존 인덱스를 버리려면 drop=True 를 해준다.

candb = pd.DataFrame(ws.UsedRange())        # 데이터 적힌 셀들만 추출해서 dataframe에 저장.
candb = candb.iloc[:, 7:38]                 # 원하는 컬럼만 추출하기
candb = candb.rename(columns=candb.loc[6])  # 컬럼 이름 정하기
candb.drop(candb.index[0:7], inplace=True)  # 필요없는 행 삭제하기
candb.drop(columns=['Address', 'Src MAC', 'Src IP', 'NmNode', 'Remote Network \nWake Up Request', 'HS CAN Routing Factor',
        'UsrMsgPurpose', 'GenMsgStartDelayTime', 'Msg Receiver', 'TPIndex', 'UserSigValidity'], inplace=True)
candb = candb.reset_index(drop=True)        # 인덱스를 다시 0부터 정렬하기

 

 

[중복되는 컬럼 이름 바꾸기]

 

데이터 프레임에서 컬럼으로 지정한 열에 동일한 데이터가 중복되어 있을 때, 

컬럼이름이 중복되면 지정에 불편함이 생긴다. 

중복된 이름을 다른 이름으로 변경해주기 위해, 딕셔너리를 사용한다.

key : '중복된 이름' : [대체할 이름, , ... ]  

d = {'Name': ['Name', 'Name.1', 'Name.2']}
candb = candb.rename(columns=lambda c: d[c].pop(0) if c in d.keys() else c) # 동일한 이름의 컬럼을 딕셔너리껄로 대체

 

 

[엑셀 닫기]

wb.Close() : 열었던 워크북, 엑셀 파일을 닫는다. 

SaveChanges=True/False: 위에서 사용한 .Visible() 이 True건 False건 엑셀을 닫을 때 저장 여부 물어보는 팝업이 뜬다. 해당 팝업이 안뜨게 알아서 저장/저장하지 않음을 지정해 줄 수 있다. 

excel.Quit():  윈도우 백그라운드에 실행했던 엑셀 어플리케이션을 종료한다. 

wb.Close(SaveChanges=False)            # 저장여부- NO
excel.Quit()

 

 

[풀 코드]

import pandas as pd
import PySimpleGUI as sg
import win32com.client

excel = win32com.client.Dispatch("Excel.Application") #자물쇠 엑셀을 열기위한 win32

wb = excel.Workbooks.Open('주소 & 파일명.xls') # 입력받은 엑셀을 wb에 저장
excel.Visible = True                          # False로 바꿔주기   / 엑셀 안띄우게하기
ws = wb.Sheets("MAIN bus")                    # 사용할 시트

candb = pd.DataFrame(ws.UsedRange())          # 빈 영역 제외하고 데이터 적힌 셀들만 추출해서 dataframe에 저장.

candb = candb.rename(columns=candb.loc[0])    # 컬럼 이름을 첫 행으로 정하기

candb.drop(candb.index[0:431], inplace=True)  # 필요없는 행 삭제하기
candb = candb.reset_index(drop=True)          # 인덱스를 다시 0부터 정렬하기

#d = {'Name': ['Name', 'Name.1', 'Name.2']}
#candb = candb.rename(columns=lambda c: d[c].pop(0) if c in d.keys() else c)  # 동일한 이름의 컬럼을 딕셔너리껄로 대체

wb.Close(SaveChanges=False)                   # 저장여부- NO
excel.Quit()                                  # 엑셀 종료 
print(candb)