22. 파이썬 엑셀(Excel) 다루기1 - openpyxl

파이썬으로 파일 뿐만 아니라 엑셀(excel)도 다룰 수 있다.

 

대부분의 사무업무를 다루는 직군에서 엑셀은 거의 뗄 수 없는 업무 환경이기 때문에

파이썬과 엑셀, 또는 크롤링 등을 혼합한 업무자동화를 통해 업무 환경을 개선할 수 있다.

 

파이썬에서 엑셀을 다루는 대표적인 라이브러리는 openpyxl과 xlsxwriter가 있으나, 

하나를 익히면 크게 다른 하나가 필요 없을 정도로 대부분의 원하는 동작을 수행할 수 있다.

(장인은 연장을 탓하지 않는다...)

 

1. openpyxl 설치 하기

 - 위의 설명된 2개의 라이브러리 중 우리는 openpyxl 을 배워볼텐데, 기본적으로 지원하지 않는 라이브러리이기때문에 추가로 설치를 진행해준다.

 - 외부 라이브러리는 특수한 상황이나 업무 환경을 위해 제한적으로 사용할 수도 있으며, 

   지난 글을 통해 가상환경을 구성한뒤 작업을 진행해도 되고, 그냥 진행해도 된다.

    

   가상환경이 뭔지 모르신다구요? 그렇다면 아래 글을 참고해주세요.

2023.05.30 - [IT 배우기/Python] - venv 가상환경 구성하기 01

venv 가상환경 구성하기 01

 

  - 가상환경을 구성할지 말지는 본인의 판단에 맡기고, 이제 프롬프트창에서 pip openpyxl 을 설치해준다.

# 해당 환경에서 설치되어 있는 라이브러리 확인
pip list 

# openpyxl 설치
pip install openpyxl

 - 처음 pip list 로 설치된 라이브러리를 확인할 떄에는 openpyxl이 없지만, pip install openpyxl 후 다시 확인해보면 3.1.2 버전이 설치된 것을 확인할 수 있다.

  

2. 라이브러리 사용하기 및 Excel 파일 객체 만들기

 - 잠시 파이썬을 떠나 엑셀을 떠올려보면, 새로 만들기를 진행할 때 통합 문서가 1차로 생성이 되고,

   해당 통합 문서 내에 각 Sheet를 추가할 수 있다.

   당연히 구성도 1개의 통합문서 내에 여러개의 Sheet로 구성된다는 것을 떠올릴 수 있다. 

 - 파이썬의 코드 또한 동일하다. 통합문서(Workbook)을 먼저 1차로 생성하고 각 Sheet를 생성 또는 수정하여 다루게 된다.

from openpyxl import Workbook

# 엑셀파일 쓰기
# 통합문서(Workbook) 만들기
wb = Workbook()

wb.save("./excel_prac1.xlsx")

 - 위의 코드를 실행하면 해당 경로에 아무런 내용이 없는 엑셀 통합문서(Workbook)을 생성할 수 있다.

 

3. worksheet 관리(생성,삭제)하기

 - 위의 자동으로 생성된 excel 파일을 열어보면, 우리가 생성하지 않아도 sheet1이 기본으로 생성되어 있다.

 - 파이썬으로 엑셀 파일을 만들 때에도 동일하게 별도의 설정을 하지 않아도 sheet1이 기본 값으로 생성 및 활성화된다.

 - 이제 해당 시트를 활성해서 사용하거나 별도로 sheet를 생성한 후 사용할 수 있는데, 기본 sheet1을 사용할 때에는 wb.active 없이 값입력이 가능하나, 다른 sheet를 다룰 때에는 해당 sheet에 대한 활성화(wb.active)가 필요하다.

  (아래의 값 입력은 활성화에 대한 결과 확인용으로 이후 다시 자세히 다뤄보겠다.)

from openpyxl import Workbook


# 엑셀파일 쓰기
# 통합문서(Workbook) 만들기
wb = Workbook()

ws = wb.active # 기본 sheet로 활성화 됨
ws['A1'] = '테스트' # A1 셀에 테스트 입력


wb.save("./excel_prac1.xlsx")

 - 아래와 같이 기본 값으로 ws(worksheet)를 활성화하고 A1셀에 '테스트'라는 텍스트를 입력하면 정상적으로 저장되는 것을 확인할 수 있다.

 

 - 해당 이제 기본 sheet 외에 별도의 sheet를 추가 및 삭제 그리고 활성화를 진행해보겠다.

 

1) sheet 추가

 - sheet 의 추가는 wb의 create_sheet 함수를 통해 생성할 수 있는데, 파라미터로 'sheet명' 과 '위치(순서)' 를 전달해준다. 위치(순서) 값이 없으면 가장 마지막에 추가된다.

 - 11~12 라인 처럼 해당 위치를 할당해줄 수도 있으며, '-'를 이용하여 뒤쪽부터 위치를 설정 할 수도 있다.

from openpyxl import Workbook


# 엑셀파일 쓰기
# 통합문서(Workbook) 만들기
wb = Workbook()


ws1 = wb.create_sheet('1_매출') # 끝에 삽입 / 기본으로 생성된 sheet 가 있기 때문에 2번째 생성됨
ws2 = wb.create_sheet('2_매입') # 끝에 삽입 / 1_매출 sheet 다음에 생성됨
ws3 = wb.create_sheet('0_합계', 0) # 첫번째 위치에 생성
ws4 = wb.create_sheet('1_매출세부내역',2) # 2번째 sheet 위치에 생성하며 '-' 를 이용하여 뒤에서 부터 위치를 설정할 수도 있음

ws = wb.active # 기본 첫 번째 sheet로 활성화 됨
ws['A1'] = '테스트' # A1 셀에 테스트 입력


wb.save("./excel_prac1.xlsx")

 - 여기서 주의할 사항은 sheet의 추가 후 값을 입력해보면 가장 앞에 있는 sheet에 값이 입력된다.

 - 해당 값을 특정 sheet에 입력하기 위해서는 해당 sheet를 활성화 해주어야한다.

from openpyxl import Workbook


# 엑셀파일 쓰기
# 통합문서(Workbook) 만들기
wb = Workbook()


ws1 = wb.create_sheet('1_매출') # 끝에 삽입 / 기본으로 생성된 sheet 가 있기 때문에 2번째 생성됨
ws2 = wb.create_sheet('2_매입') # 끝에 삽입 / 1_매출 sheet 다음에 생성됨
ws3 = wb.create_sheet('0_합계', 0) # 첫번째 위치에 생성
ws4 = wb.create_sheet('1_매출세부내역',2) # 2번째 sheet 위치에 생성하며 '-' 를 이용하여 뒤에서 부터 위치를 설정할 수도 있음

ws = wb.active # 기본 첫 번째 sheet로 활성화 됨
ws['A1'] = '테스트' # A1 셀에 테스트 입력

ws1 = wb['1_매출세부내역'] # 1_매출세부내역 sheet 를 worksheet(ws1)로 객체 생성
ws1['A1'] = '거래처명' # ws1 sheet A1 셀에 '거래처명' 입력

wb.save("./excel_prac1.xlsx")

 - 14~15라인과 같이 sheet 명 없이 Workbook을 활성화하게 되면, 첫번째 sheet에 내용이 입력되고,

   17~18라인과 같이 sheet 명을 지정해주면 해당 sheet에 입력이 되는 것을 확인할 수 있다.

 

2) sheet 확인 방법

 - 위에서 sheet 명을 통해 해당 sheet를 제어하는 것을 확인하였다면, 해당 sheet 의 이름을 확인할 수는 없을까?

    당연히 가능하다. 

 - 2가지 방법이 있는데, 하나는 Workbook의 sheetnames 변수를 확인하는 방법이고, 두번째는 해당 Worksheet의 title 변수를 확인하는 방법이다.

from openpyxl import Workbook


# 엑셀파일 쓰기
# 통합문서(Workbook) 만들기
wb = Workbook()


ws1 = wb.create_sheet('1_매출') # 끝에 삽입 / 기본으로 생성된 sheet 가 있기 때문에 2번째 생성됨
ws2 = wb.create_sheet('2_매입') # 끝에 삽입 / 1_매출 sheet 다음에 생성됨
ws3 = wb.create_sheet('0_합계', 0) # 첫번째 위치에 생성
ws4 = wb.create_sheet('1_매출세부내역',2) # 2번째 sheet 위치에 생성하며 '-' 를 이용하여 뒤에서 부터 위치를 설정할 수도 있음


sheets_name = wb.sheetnames # Workbook 내의 sheet들의 name 확인(list)
print(sheets_name)


wb.save("./excel_prac1.xlsx")

 

 - wb.sheetnames 로 제어에 사용할 수 있는 sheet 명이 list형태로 나타나는 것을 볼 수 있는데,

   이말은 해당 sheet를 이용하면 동일한 값을 반복문을 통해 입력할 수도 있다는 뜻이다. 

   만약 모든 sheet 2열에 동일한 항목명을 만들려 한다면 아래와 같이 for 반복문을 통해 사용할 수 있다.

 - 마찬가지로 ws.title 의 경우도 for 반복문을 통해 확인되고 있기 때문에 위와 같은 기능을 수행할 수 있다.

from openpyxl import Workbook


# 엑셀파일 쓰기
# 통합문서(Workbook) 만들기
wb = Workbook()


ws1 = wb.create_sheet('1_매출') # 끝에 삽입 / 기본으로 생성된 sheet 가 있기 때문에 2번째 생성됨
ws2 = wb.create_sheet('2_매입') # 끝에 삽입 / 1_매출 sheet 다음에 생성됨
ws3 = wb.create_sheet('0_합계', 0) # 첫번째 위치에 생성
ws4 = wb.create_sheet('1_매출세부내역',2) # 2번째 sheet 위치에 생성하며 '-' 를 이용하여 뒤에서 부터 위치를 설정할 수도 있음



#방법 1 / wb.sheetnames 변수 이용
print('- sheet 명 출력방법 1 : ')
sheets_name = wb.sheetnames
print(sheets_name)

for st in sheets_name:
    wb[st]['A2'] = '구분'


#방법 2 / ws.title 변수 이용
print('- sheet 명 출력방법 2 : ')
for st_name in wb:
    print(st_name.title)

    wb[st_name.title]['B2'] = '날짜'

wb.save("./excel_prac1.xlsx")

 - 해당 Workbook의 모든 sheet에 sheetnames(방법1)과 sheet.title(방법2)를 통해 A2와 B2셀에 값이 입력된 것을 확인할 수 있다.

 

3) sheet 삭제

 - sheet 를 확인할 수 있다면, 삭제는 어렵지 않다. 

 - 삭제 또한 2가지 방법이 있는데 하나는 Workbook의 매서드를 통해 sheet를 지우는 방법이고, 두번째는 del 을 통해 지우는 방법이다.

from openpyxl import Workbook

# 통합문서(Workbook) 만들기
wb = Workbook()

ws1 = wb.create_sheet('1_매출') # 끝에 삽입 / 기본으로 생성된 sheet 가 있기 때문에 2번째 생성됨
ws2 = wb.create_sheet('2_매입') # 끝에 삽입 / 1_매출 sheet 다음에 생성됨
ws3 = wb.create_sheet('0_합계', 0) # 첫번째 위치에 생성
ws4 = wb.create_sheet('1_매출세부내역',2) # 2번째 sheet 위치에 생성하며 '-' 를 이용하여 뒤에서 부터 위치를 설정할 수도 있음

print('- 전체 sheet 명 출력 : ')
sheets_name = wb.sheetnames
print(sheets_name)


#방법 1 / wb.remove 매서드 이용
print('- remove를 이용한 sheet 삭제 : ')
wb.remove(wb['Sheet'])

print('- 전체 sheet 명 출력 : ')
sheets_name = wb.sheetnames
print(sheets_name)


#방법 2 / del wb[ws] 매서드 이용
print('- del 을 이용한 sheet 삭제 : ')
del wb['1_매출세부내역']

print('- 전체 sheet 명 출력 : ')
sheets_name = wb.sheetnames
print(sheets_name)


wb.save("./excel_prac1.xlsx")

  - 이때 주의할 사항은 sheet명의 대소문자를 구별한다는 것이다. 만약 18라인의 'Sheet'를 소문자 'sheet'로 입력 후 삭제를 진행할 경우, 해당 sheet 가 없기 때문에 Error가 발생한다.

 

 

4) sheet명 수정

 - 수정 또한 wb[sheet명].title 변수를 이용하여 변경 할 수 있다.

from openpyxl import Workbook

# 통합문서(Workbook) 만들기
wb = Workbook()

ws1 = wb.create_sheet('1_매출') # 끝에 삽입 / 기본으로 생성된 sheet 가 있기 때문에 2번째 생성됨
ws2 = wb.create_sheet('2_매입') # 끝에 삽입 / 1_매출 sheet 다음에 생성됨
ws3 = wb.create_sheet('0_합계', 0) # 첫번째 위치에 생성
ws4 = wb.create_sheet('1_매출세부내역',2) # 2번째 sheet 위치에 생성하며 '-' 를 이용하여 뒤에서 부터 위치를 설정할 수도 있음

print('- 전체 sheet 명 출력 : ')
sheets_name = wb.sheetnames
print(sheets_name)

# sheet 명 변경
wb['0_합계'].title = '0_결산'

print('- 전체 sheet 명 출력 : ')
sheets_name = wb.sheetnames
print(sheets_name)

wb.save("./excel_prac1.xlsx")