openpyxl로 엑셀 파일(.xlsx) 불러오기

회사에서 엑셀을 많이 씁니다. 문서를 만들때마다 일일히 문서를 다시 쓴다면 너무 힘들겁니다.

이런 엑셀 노가다를 피하기 위해 파이썬으로 업무자동화시스템을 구축할 수 있습니다.

업무자동화를 위해 이번 포스팅에서는 엑셀파일을 읽어오고 쓰는 방법을 소개할까 합니다.

 

엑셀을 다룰수 있는 다양한 모듈이 있지만 openpyxl만을 가지고 엑셀을 다루겠습니다.

 

openpyxl 모듈 설치

먼저 openpyxl을 설치합시다

Jupyter에서 다음과 같이 입력해서 설치를 요청합니다.

!pip install openpyxl

엑셀파일을 불러오려면 엑셀파일이 필요합니다. 저는 국고채(3년),국고채(10년) 금리표 파일이 있어서

시장금리 파일을 불러오겠습니다.

시장금리.xlsx로 저장했습니다.

 

엑셀파일 불러오기

먼저 다음과 같이 import를 합니다.

from openpyxl import load_workbook

load_workbook 모듈을 쓰면 엑셀을 불러올 수 있습니다. load_workbook을 살펴보면 다음과 같은 파라미터가 있습니다.

기본적으로 파라미터는 filename, read_only,keep_vba,data_only,keep_links 으로 되어있습니다.

filename 에는 불러올 파일경로를 입력하고

read_only 는 읽기만 해서 쓰기제한을 둘것인지 설정하는 파라미터입니다. 아무 언급을 안하면 쓰기도 가능한 상태로 엑셀을 불러들입니다.

keep_vba 는 엑셀에 있는 vba 기능을 살려서 가져올것인지 아닌지를 설정합니다. 기본적으로 엑셀에 저장되어 있는 vba은 가져오지 않는 것으로 설정되어있습니다. vba가 필요하다면 True로 설정을 바꾸셔야합니다.

data_only 는 엑셀의 수식값을 가져올것인지 수식의 결과인 data를 가져올지를 묻는 설정입니다. 

keep_links 는 엑셀파일안에 있는 링크를 그대로 쓸것인지를 설정할 수 있습니다.

 

 

파라미터를 잘 설정해서 엑세파일을 불러오겠습니다.

load_xlsx=load_workbook('H:/autopython/시장금리.xlsx',data_only=True)

경로를 설정할때 \으로 무심결에 했다가는 오류가 발생할수있습니다.

\a는 파이썬에서 ASCII Bell character 으로 인식하기 때문에 \으로 경로를 쓰면 해당경로를 찾을 수 없다는 오류가 나옵니다.

 

그러니 '/' 로 경로를 설정해주셔야 합니다. 

 

sheet1의 정보를 가지고 오고 싶으니 sheet도 설정합니다.

load_sheet=load_xlsx['sheet1']

이제 데이터를 잘 불러왔는지 확인해보겠습니다.

load_sheet['A1'].value

여러 cell을 불러오려면 for을 이용해 불러올 수 있습니다.

range_cell=load_sheet['B1':'E3']
for row in range_cell:
    for cell in row:
        print(cell.value)

한꺼번에 다 불러오려면 반복문을 통해 불러와야 하는 불편함이 있어서 보통 pandas로 바꿉니다. 

 

Pandas로 바꾸기

df = pd.DataFrame(load_sheet.values)
df

엑셀에 저장되어 있던 모든 데이터를 pandas로 옮겼습니다.

pandas를 잘 다룬다면 pandas로 수정해서 다시 엑셀에 저장하면 되겠습니다.

pandas에 대해 잘 모르신다면 아래의 포스팅을 참고해주세요.

[Pandas] DataFrame 살펴보기(생성,데이터파악)

[Pandas] DataFrame 살펴보기(생성,데이터파악)

사실 엑셀의 전체 데이터를 사용하고자 한다면 pandas에 내장되어 있는 read_excel함수를 이용하는것이 더 편리합니다.

sheet=pd.read_excel('H:/autopython/시장금리.xlsx')
sheet

pandas로 엑셀 다루는 법은 따로 포스팅을 하겠습니다.

 

openpyxl로 엑셀파일 만들기

이번에는 파이썬으로 엑셀파일을 만들겠습니다. 사실 openpyxl를 쓰는 가장 큰 이유 중에 하나가 바로 파이썬으로 엑셀을 켜지도 않은채 파일을 만들 수 있다는 것입니다. 반복 문서 작업의 양을 확실히 줄일 수 있습니다. 파일을 일일히 수정하지 않아도 문서를 작성할 수 있는 큰 장점이 있습니다. 

지금 포스팅에서는 어떤 방식으로 엑셀 파일을 만들 수 있는지 설명하겠습니다.

from openpyxl import Workbook
wb = Workbook()
ws = wb.create_sheet('test')
# 특정 cell에 입력
ws['A1'] = 'Sequence'
#행단위로 추가
ws.append([2,4,6,8,10])
ws.append([3,5,7,9,11])
#셀 단위 추가
ws.cell(5,3,'다음열')
wb.save('h:/autopython/sequence.xlsx')

Workbook이라는 모듈을 씁니다. wb를 Workbook()로 정의하고 ws는 wb의 sheet이름을 정하고 지정했습니다.

행단위로 추가를 하면 언급이 없다면 행단위로 밑으로 하나씩 써집니다.

즉, 1row에 'Sequence'를 써놓은 상태에서 ws.append([2,4,6,8,10])를 추가하면 2 row에 써지고

ws.append([3,5,7,9,11])은 3row에 써지게 됩니다.

특정위치를 정하고 싶다면 cell()을 이용하면 됩니다. 저는 5row 3column에 '다음열'을 입력했습니다.

save로 저장을 하고 엑셀파일을 열어보면 요청한대로 값이 나오는 것을 볼 수 있습니다.