[Toy Project] 자동매매 프로그램 만들기 - 6. openpyxl 모듈 알아보기

 

[목차]

 

1. openpyxl 모듈이란?

 

2. 엑셀 파일 불러오기

 

3. 셀의 데이터 불러오기

 

4. 엑셀 파일/시트 만들기

 

5. 셀에 데이터 작성하기

 

 

 

자동매매 프로그램으로 매매를 하는 경우, 내가 정한 매매 전략이 효과적으로 작용하는지 알아보기 위해서는 매매한 기록들을 모두 저장한 후 분석하는 과정이 필요하다. 

하지만, 매번 매매한 정보를 직접 기록하는 것은 자동매매의 성격과 맞지 않으므로, 자동으로 매매를 수행할 때마다 이에 대한 정보를 실시간으로 알아서 기록해주는 기능이 필요하다. 

 

따라서 이 기능을 프로그램에 적용하기 앞서, 파이썬을 이용하여 엑셀에 읽고 쓰는 기능을 할 수 있게 해주는 openpyxl 모듈에 대해서 먼저 알아보자.

 

 

  1. openpyxl 모듈이란?

 

파이썬에서 엑셀을 다룰 수 있도록 하는 패키지는 많이 있지만, 그중 openpyxl이 가장 많이 사용된다. 

xlsx 파일을 메모리에 로딩한 후 읽거나 쓰는 작업들을 수행하고 이후 저장까지 수행한다. 

openpyxl에 대한 사이트가 존재하므로, 자세한 설명이 필요한 경우 해당 사이트를 참고하면 도움이 많이 될 것 같다.

 

기본적으로 openpyxl을 이용하기 위해서는 별도로 설치를 해주어야 한다. 

pip install openpyxl

 

 

  2. 엑셀 파일 불러오기

 

extest.xlsx

현재 위와 같이 작성되어있는 extest.xlsx 엑셀 파일이 저장되어있다고 가정하자.

 

우선 엑셀 파일을 불러오기 위해서는 openpyxl 모듈의 load_workbook 함수를 이용한다. 

load_workbook 함수는 다음과 같이 이루어져 있다.

 

load_workbook(filename, read_only=False, keep_vba=KEEP_VBA, data_only=False, keep_links=True)

 

filename은 불러올 파일 경로를 말한다. 만약 동일한 디렉터리 내에 존재한다면 단순히 파일 이름만 넣어줘도 된다. 

read_only는 읽기 전용으로 불러올지 쓰기도 가능하도록 불러올지를 선택하는 변수이다. 

keep_vba는 엑셀에 있는 vba 기능을 살려서 가져올지를 선택하는 변수이다.

data_only는 엑셀의 셀에 적힌 수식 값을 가져올 것인지, 수식이 계산된 값인 data를 가져올 것인지 선택하는 변수이다.

keep_links는 엑셀 파일에 있는 링크를 그대로 사용할지를 선택하는 변수이다. 

 

동일한 폴더에서 extent.xlsx를 불러오는 작업을 해보자. 

from openpyxl import load_workbook

wb = load_workbook('extest.xlsx')
print(wb)

# OUTPUT
# openpyxl.workbook.workbook.Workbook object at 0x07462610

 

wb 변수에 workbook 객체가 잘 저장된 것을 볼 수 있다. 

다음은 시트를 불러오는 방법이다. 

from openpyxl import load_workbook

wb = load_workbook('extest.xlsx')
print(wb.sheetnames) # ['시트1', 'Sheet2']

print(wb.get_sheet_names()) # ['시트1', 'Sheet2']

print(wb['시트1']) # Worksheet "시트1"
print(wb['Sheet2']) # Worksheet "Sheet2"

a = wb.get_sheet_by_name('시트1')
print(a) # Worksheet "시트1"

 

load_workbook으로 불러온 workbook의 시트 목록을 가져오기 위해서는 sheetnames 또는 get_sheet_names() 함수를 이용하면 된다. 리스트 형식으로 반환된다. 

그리고 특정 시트에 접근하고 싶다면 딕셔너리와 같이 workbook 객체에 원하는 시트명을 넣어주면 해당 시트가 반환된다.

 

 

  3. 셀의 데이터 불러오기

 

이제, 시트까지 불러오는 방법을 알았으니, 특정 셀에 있는 데이터를 불러오는 방법을 알아보자. 

ws 변수에 시트1을 받아온다. 

from openpyxl import load_workbook

wb = load_workbook('extest.xlsx')
ws = wb['시트1'] 
print(ws) # Worksheet "시트1"
ws = wb[wb.sheetnames[0]]
print(ws) # Worksheet "시트1"
ws = wb.get_sheet_by_name(wb.sheetnames[0])
print(ws) # Worksheet "시트1"

 

만약 시트의 이름을 모르는 경우에 두 번째나 세 번째 예시와 같이 sheetnames[0]을 통해서 시트 중 첫 번째 시트의 이름을 받아온 뒤에 사용해도 된다. 

 

먼저, 한 셀에 대해서 불러오는 작업을 수행해보자.

from openpyxl import load_workbook

wb = load_workbook('extest.xlsx')
ws = wb['시트1'] 

print(ws['A1'].value) # openpyxl
print(ws.cell(row=1, column=1).value) # openpyxl
print(ws['B1'].value) # 테스트용
print(ws['A2'].value) # None
print(ws.cell(row=2, column=3).value) # 파일

 

하나의 셀에 대해서 값을 불러오는 두 방법이 있다. 

첫 번째는 직접 셀 위치를 넣어주는 방식으로, 시트명['셀위치'].value 의 형태로 사용한다.

두 번째는 셀의 좌표를 넣어주는 방식으로, 시트명.cell(row = x, column = y).value의 형태로 사용하면 셀(x, y)에 위치한 데이터가 나오게 된다. 

위에서 저장한 extest.xlsx 파일과 동일하게 결과가 나오는 것을 확인할 수 있다. 셀이 비어있으면 None을 반환한다.

 

다음은 여러 셀에 대해서 동시에 불러오는 작업을 수행해보자. 

from openpyxl import load_workbook

wb = load_workbook('extest.xlsx')
ws = wb['시트1'] 

get_data = ws['A1':'C2']
print(get_data)

# OUTPUT
# ((Cell '시트1'.A1, Cell '시트1'.B1, Cell '시트1'.C1), 
# (Cell '시트1'.A2, Cell '시트1'.B2, Cell '시트1'.C2))

 

원하는 직사각형 셀 구역이 있다면, 해당 구역의 왼쪽 위 시작점과 오른쪽 아래 끝점을 시트명[시작점 : 끝점]의 형태로 사용한다. 

get_data를 출력해보면 튜플의 형태로 셀들이 저장되어 있는 것을 알 수 있으며, 실제로 데이터를 이용하고 싶다면 각 셀별로 .value를 통해 값을 얻어내야 한다. 

from openpyxl import load_workbook

wb = load_workbook('extest.xlsx')
ws = wb['시트1'] 

get_data = ws['A1':'C2']
for row in get_data:
    for cell in row:
        print(cell.value, end=' ')
    print()

# OUTPUT
# openpyxl 테스트용 None 
# None 엑셀 파일

 

추가적으로, 현재 시트의 구조를 파악할 수 있도록, 데이터가 들어있는 최대 행과 최대 열, 그리고 최대 구간을 구하는 변수도 제공한다. 

from openpyxl import load_workbook

wb = load_workbook('extest.xlsx')
ws = wb['시트1'] 

print(ws.max_column) # 3
print(ws.max_row) # 2
print(ws.dimensions) # A1:C2

 

 

  4. 엑셀 파일/시트 만들기

 

이전까지는 기존에 존재하는 엑셀 파일을 불러와서 데이터를 가져오는 작업을 하였다. 

하지만, 새로운 엑셀 파일을 만들어서 해당 엑셀 파일에 쓰는 작업도 가능하며, 이는 엑셀을 켜지 않고도 파이썬으로 엑셀 파일을 작성할 수 있다는 openpyxl의 큰 장점이다. 

 

먼저 엑셀 파일을 생성해보자.

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

 

파일을 만들기 위해서는 Workbook 클래스를 import 해주어야 한다. 그리고 생성자를 이용하여 인스턴스를 생성해준다. 

이전에 load_workbook 함수를 통해서 반환된 wb 변수와 동일한 타입을 갖는다. 

workbook은 항상 최소 하나의 시트를 가지고 생성되고, 첫 시트가 활성화된 채로 만들어지므로 wb.active를 이용하면 활성화되어있는 첫 시트를 얻을 수 있다. 

 

다음은 새로운 시트를 생성하는 방법이다. 

from openpyxl import Workbook

wb = Workbook()

ws1 = wb.create_sheet("mysheet") # insert at the end
ws2 = wb.create_sheet("mysheet", 0) # insert at first position
ws3 = wb.create_sheet("mysheet", -1) # insert at second position from the end

 

새로운 시트는 create_sheet 함수를 통해 만든다. 

첫 번째 인자로는 시트의 이름을 넣고, 두 번째 인자로는 시트의 생성 위치를 지정할 수 있다. 기본적으로 인덱스는 0부터 시작한다. 이름과 인덱스는 생략할 수 있다. 

 

시트의 이름 또한 아래와 같이 변경할 수 있다. 

from openpyxl import Workbook

wb = Workbook()

ws = wb.active
ws.title = "mysheet"

 

동일한 workbook 내에 있는 시트를 copy 할 수도 있다. copy_worksheet 함수를 이용한다. 

from openpyxl import Workbook

wb = Workbook()

ws = wb.active
wscopy = wb.copy_worksheet(ws)

 

이미지나 차트 같은 데이터를 제외한 모든 데이터들이 복사되며, workbook이 읽기 전용이나 쓰기 전용으로 열려있는 경우에는 복사가 불가능하다. 

 

 

  5. 셀에 데이터 작성하기

 

셀의 데이터를 불러올 수 있었듯이 셀에 데이터를 직접 작성할 수도 있다. 

새로운 workbook에 데이터를 작성해보자. 

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

# 셀 단위로 추가
ws['A1'] = 'Hi' 
ws['B1'] = 3
ws.cell(3, 4, 'hello')

# 행 단위로 추가
ws.append([3,4,5,6])

# 저장
wb.save('created.xlsx')

 

우선 한 셀에 데이터를 추가하는 방법이다. 

딕셔너리에 원소를 추가하는 방식과 동일하게 시트명[셀 위치] = 값의 형태로 해당 셀에 값을 넣어줄 수 있다.

혹은 cell(x, y, 값) 함수를 통해서 (x, y) 좌표에 해당하는 셀에 값을 넣어줄 수도 있다. 

 

append 함수를 통해서 행 단위로 데이터를 추가할 수도 있는데, 현재 데이터가 들어있는 마지막행의 다음 행에 첫 번째 열부터 순서대로 원소들을 넣어준다. 

 

최종적으로 save함수를 사용하면 입력한 이름을 파일명으로 갖는 파일이 생성된다. 

단, 새로 만든 workbook은 반드시 기존에 존재하는 파일명과 다른 이름으로 저장해야 한다. 

기존에 존재하던 파일을 load_workbook을 이용하여 불러온 경우는 동일한 이름으로 저장 가능하다.

 

created.xlsx