[python] python으로 excel을 조작할 수 있는 openpyxl 사용법

 excel 파일을 python으로 다룰 수 있는 openyxl 패키지에 대한 내용과 기본적인 사용법을 정리해보았다. 우선 OpenPyXL 문서는 여기서 확인이 가능하다.

 

 

OpenPyXL이란

---

 OpenPyXL은 Excel 파일을 읽고 쓰기를 Python으로 할 수 있는 일종의 모듈이다. 예를 들어 아래와 같은 조작이 가능하다.

- 셀 번호를 지정해서 이미지나 문자열을 입력할 수 있다.

- Excel 파일의 시트 데이터를 복사해서 다른 시트에 붙여넣기 할 수 있다.

- 시트를 추가하거나 삭제할 수 있다.

 다만 이 모듈을 사용하기 위해서 주의해야할 점이 한 가지가 있는데 바로 확장자가 ".xlsx"여야한다는 것이다. 이번 포스팅에서는 정말 기초적인 방법만 다루도록 하겠다. 

 

 

OpenPyXL 설치법

---

 다른 패키지나 라이브러리의 설치 방식과 동일하게 pip 명령어로 다운로드할 수 있다.

pip install openpyxl

 

 

예제 데이터

---

 예제 데이터로 위 데이터를 사용할 것이다. 동일한 데이터를 사용해 연습해 보고 싶다면 이 git링크에서 파일을 다운로드하면 된다.

 

 

Excel 파일 열기

---

 

import openpyxl
wb=openpyxl.load_workbook('example.xlsx') #파일명 혹은 패스+파일명 입력

openpyxl.load_workbook(파일명)

openpyxl.load_workbook(파일명) 메소드는 파일명을 인수로 하여, 워크북의 객체로 반환한다. 경로 혹은 파일명에 해당하는 Excel파일이 없다면, 에러가 발생한다. 

 

 

시트명 취득

---

import openpyxl
wb=openpyxl.load_workbook('example.xlsx')
wb.get_sheet_names()

워크북 객체.get_sheet_names()

 위 메소도를 이용하면 워크북에 존재하고 있는 시트명이 리스트 형태로 취득할 수 있다. 추가로 원하는 시트에 있는 데이터만을 취득하고 싶다면 워크북 객체.get_sheet_by_name(‘Sheet1’)로 얻을 수 있다.

 시트 데이터를 얻으면, 셀의 값을 다룰 수 있게 된다. 그럼 아래에서는 셀의 값을 구하는 방법에 대해 알아보자.

 

 

Excel 셀의 값 구하기

---

방법1

import openpyxl
wb =openpyxl.load_workbook('example.xlsx')
sheet = wb.get_sheet_by_name('Sheet1')
sheet['A1'].value

 예제 데이터를 사용했을 경우 datetime.datetime(2015, 4, 5, 13, 34, 2) 가 반환되면 성공이다. 셀 오브젝트에는 value이라는 속성이 있으므로 sheet['A1'].value로 기재하면, 셀의 값을 얻을 수 있다. 그러나, 이러한 지정 방법은 프로그램으로써 다루기 힘든 부분이 있다. 그 이유는 반복문을 이용해 엑셀의 정보를 얻을 경우를 고려했을 때, A1, B1, C1 …로 지정하는 것 보다는 11, 21, 31 …의 쪽이 났기 때문이다.

방법2

 먼저 샘플 코드를 살펴보자.

import openpyxl
wb =openpyxl.load_workbook('example.xlsx')
sheet = wb.get_sheet_by_name('Sheet1')
sheet.cell(row=1, column=1).value

 동작은 동일한 코드이므로,  datetime.datetime(2015, 4, 5, 13, 34, 2)가 출력되면 성공이다. 행과 열을 지정해, 셀의 값을 취득하는 코드이다.

시트 객체.cell(row=행의 번호, column=열의 번호)

행이나 열을 지정하여 셀 객체를 얻을 수 있다. 그리고 뒤에 .value를 붙이면 언급했듯이, 셀의 값을 얻을 수 있다. 이용시의 주의점은 열 번호는 ABC… ➔ 123…에 대응된다는 것이다. 따라서 위의 샘플 코드의 sheet.cell(row=1, column=1)부분은 A1의 셀을 의미한다.

 

 

Excel 시트명 변경하기

---

import openpyxl
wb = openpyxl.load_workbook('example.xlsx')
sheet = wb.active
sheet.title = 'test_sheet_1'
wb.save('example.xlsx')

워크북 객체.active

 워크북 객체에는 active라는 속성이있다. active 속성을 통해 워크북에서 액티브 상태인 시트 객체 데이터를 얻을 수 있다. 그리고 해당 시트 객체의 title 속성을 이용해 시트명을 변경할 수 있다.

 

 

새로운 Excel 파일 만들기

---

import openpyxl

wb = openpyxl.Workbook()
wb.save('test.xlsx')

openpyxl.Workbook()

 openpyxl.Workbook()함수를 호출하면, 안이 비어 있는 새로운 워크북 객체가 생성된다. 이 타이밍에서는 새로운 엑셀 "파일"이 생성됐다고 할 수 없다. 반드시 아래에서 소개할 save 메소드를 이용해서 저장까지해야한다.

워크북 객체.save('파일명.xlsx')

 이 메소드를 실행시켜야 엑셀 파일이 생성된다. 주의 점은 경로 설정이나 파일명 설정이다. 기존의 파일을 덮어쓰지 않도록 확인한 후 실행시키도록 하자.

 

 

Excel 시트 추가/삭제/복제(복사)하기

---

시트 추가

import openpyxl
wb = openpyxl.Workbook()
wb.create_sheet()

워크북 객체.create_sheet()

 create_sheet() 메소드를 사용하면 워크북의 맨 끝에 시트가 추가된다. 또한, 시트의 이름은 특별한 지정이 없으므로 "Sheet숫자"가 된다. 그러나 위 코드의 실행 후, 실제로 살펴보면 "Sheet", "Sheet1"의 형태로 되어있다. 숫자가 없는 Sheet는 openpyxl.Workbook()함수를 실행시켰을 때에 자동으로 작성되는 시트로, create_sheet()메소드의 작동과는 관계가 없다.

워크북 객체.create_sheet(index=숫자, title=’시트명’)

시트를 삽입할 위치를 지정하여 시트를 추가할 수 있다. 메소드의 index 속성을 사용하면 된다. 더불어 그 시트명은 title 속성으로 정할 수 있다. 참고로 index는 0에서부터 시작하므로 주의하자. 즉, Excel파일의 맨 처음 시트에 시트를 추가하고 싶은 경우 index = 0으로 작성하면 된다.

시트 삭제

wb.remove_sheet(wb.get_sheet_by_name('Sheet1'))

 

워크북 객체.remove_sheet(시트 객체)

워크북 객체의 remove_sheet()메소드는 인수로 얻어낸 시트 객체를 삭제할 수 있다. 또한, 시트 객체의 취득은 앞에서 보았듯 get_sheet_by_name()메소드를 사용할 수 있다.

시트 복제(복사)

wb.copy_worksheet(wb["Sheet2"])

워크북 객체.copy_worksheet(시트 객체)

인수로 지정한 시트 객체를 복사한다. 시트명은 "시트 객체명 Copy"가 된다.

 

 

Excel에 데이터(문자열, 이미지) 추가(입력)

---

문자열 추가

import openpyxl
wb = openpyxl.Workbook()
sheet = wb.active
sheet['A1'] = 'test'
wb.save('test_write.xlsx')

 시트 객체를 지정해서 문자열을 직접 입력할 수 있다. 물론 이 외에 정말 다양한 방법이 존재하지만, 여기서는 간략하게 한 가지 방법만 소개하겠다.

이미지 추가

workbook = openpyxl.load_workbook('insert.xlsx')
sheet = workbook['Sheet1']
img = openpyxl.drawing.image.Image(img.png)
img.width = 72 * 7
img.height = 25 * 10
sheet.add_image(img, 'A1')

시트에 추가할 이미지의 경로를 지정해 준 뒤 사이드 조정후 add_image메소드로 추가할 이미지와 위치를 지정한다.

 

 

번외 ) openyxl에서 Pandas의 DataFrame으로의 변환

---

워크북 객체를 pandas DataFrame으로 변환할 수 있다. 

from openpyxl import load_workbook
import pandas as pd

wb = load_workbook('example.xlsx')
ws = wb['sheet']
 
f = pd.DataFrame(ws.values)

 위 코드로 실행했을시 열 변호가 다음과 같이 헤더에 들어가게 된다(이해를 돕기위한 임의의 데이터).

	0	1	2
0	1	김xx	남
1	2	이yy	여
2	3	박zz	남

 기존의 헤더가 존재하고 있는 경우, 아래의 코드를 실행시키면 헤더가 잘 출력됨을 확인할 수 있다.

from openpyxl import load_workbook
import pandas as pd

wb = load_workbook('sample.xlsx')
ws = wb['sample']

data = ws.values
# 맨 처음의 행을 헤더로써 취득한다. 
columns = next(data)[0:]
# 이후의 데이터부터 DataFrame을 작성
df = pd.DataFrame(data, columns=columns)

	ID	이름	성별
0	1	김xx	남
1	2	이yy	여
2	3	박zz	남

---

참고자료

https://www.soudegesu.com/post/python/pandas-with-openpyxl/

https://qiita.com/quryu/items/7e281dcb11b0e3db3a99

https://tonari-it.com/python-openpyxl-beginner-edit-excel/

https://gammasoft.jp/support/how-to-use-openpyxl-for-excel-file/#copy-sheet

https://tonari-it.com/python-openpyxl-beginner-add-excel-sheet/

https://tonari-it.com/python-openpyxl-beginner/