[Python] 파이썬으로 엑셀 다루기 | ❶ 엑셀 데이터 사용하기

이 글은 [파이썬 업무 자동화 일잘러 되기 + 챗GPT]에서 발췌했습니다.
글 메이허 / 골든래빗 출판사

실제 업무에서 가장 많이 다루는 프로그램 중 하나인 엑셀을 파이썬으로 자동화하는 방법을 알아보겠습니다. 파이썬으로 엑셀 데이터를 다루면 수백 개의 엑셀 파일을 하나로 합치거나 특정 조건에 맞는 데이터만 추출하는 등의 반복 작업을 코드 몇 줄로 자동화할 수 있습니다. 파이썬을 통해 엑셀 프로그램보다 더 빠르게 엑셀 데이터를 처리해봅시다.

1. 엑셀 데이터 사용하기

파이썬으로 엑셀 파일을 생성한 다음 데이터를 사용해보겠습니다. 엑셀 데이터를 사용하기에 앞서 실습에 필요한 예제 데이터를 깃허브에서 다운로드합니다. 그런 다음 엑셀 파일을 다루는 파이썬 라이브러리를 설치하여 엑셀 데이터를 사용해봅시다.

00 실습을 시작하기 전에

실습 데이터가 있는 깃허브에서 데이터를 다운로드하세요.

• 깃허브 URL : bit.ly/3OeD8vc

01 OpenPyXL 라이브러리 설치하기

파이썬에는 엑셀 파일을 다룰 수 있는 라이브러리가 많습니다. 대표적으로 판다스, OpenPyXL, xlrd 등이 있습니다. 여기서는 엑셀 파일을 다양하게 다룰 수 있는 OpenPyXL 라이브러리로 엑셀을 다룹니다.

• OpenPyXL은 엑셀 2010 이후 버전의 파일부터 지원하며, 2010보다 이전 버전을 사용해야 하는 경우 XlsxWriter나 xlrd/xlwt 등 다른 라이브러리를 사용해야 합니다.
• OpenPyXL 표기는 앞으로 openpyxl로 하겠습니다.

외장 라이브러리 설치하기

지금까지 실습에 사용한 라이브러리는 파이썬에 설치되어 있는 내장 라이브러리라서 따로 설치할 필요가 없었습니다. 하지만 openpyxl 라이브러리는 파이썬에 설치된 내장 라이브러리가 아닌 외장 라이브러리입니다. 외장 라이브러리는 따로 설치하는 과정을 거쳐야 합니다. 외장 라이브러리 설치는 파이썬과 함께 설치되는 라이브러리 관리 프로그램인 pip를 사용해야 합니다. 명령 프롬프트에 pip install openpyxl과 같이 명령어를 입력하여 외장 라이브러리를 설치할 수 있습니다.

이 책의 실습 환경인 주피터 노트북에서는 외장 라이브러리를 설치할 때도 pip 명령어를 동일하게 실행하면 됩니다. 주피터 노트북에서 openpyxl을 설치하려면 동일하게 pip 명령어를 그대로 주피터 노트북에서 실행하면 됩니다. 주피터 노트북에 명령어를 입력하고 실행하여 openpyxl을 설치합시다. 명령어를 실행하면 [*] 표시였다가 다 설치되면 숫자로 바뀌고 결과가 아래에 출력됩니다.

• 이전 버전의 주피터 노트북을 사용 중이면 !pip명령어를 사용해 외부 라이브러리를 설치해야 합니다. 느낌표 !는 주피터 노트북에게 ‘이건 파이썬 코드가 아니라 터미널 명령어야’라고 알려주는 일종의 약속이라고 생각하면 됩니다.

02 엑셀에 데이터 입력하고 확인해보기

다음 코드를 실행하여 파이썬으로 엑셀 파일을 생성하겠습니다. 코드는 다음과 같은 순서로 구성되어 있습니다.

1. 새 Workbook 객체를 생성
2. 수강생_정보 워크시트에서 작업
3. A1 셀에 ‘이철수’라고 입력
4. 수강생_리스트.xlsx라는 엑셀 파일로 저장

openpyxl은 엑셀 파일을 객체로 다루기 위한 Workbook 클래스를 제공합니다. 이것을 import하여 파일을 생성하고 저장하겠습니다.

from openpyxl import Workbook
# ❶ Workbook 객체 생성
wb = Workbook()
# ❷ 현재 활성화된 워크시트 선택
ws = wb.active
# ❸ 시트 제목을 '수강생_정보'로 변경 ws.title = '수강생_정보'
# ❹ A1셀에'이철수'입력
ws['A1'] = '이철수'
# ❺ '수강생_리스트.xlsx'로 파일 저장 wb.save('수강생_리스트.xlsx')
# ❻ 워크북 닫기
wb.close()

❶ Workbook( )으로 Workbook 객체를 생성해 wb에 할당합니다. Workbook 객체란 openpyxl 라이브러리에서 엑셀 파일을 다루기 위한 기본 객체입니다. 객체를 통해 시트(Sheet), 셀(Cell), 데이터 등을 다룰 수 있으며, 엑셀 파일을 생성하거나 읽기, 수정, 저장 등의 작업을 할 수 있습니다. 쉽게 말해 Workbook( )으로 만든 객체가 엑셀 파일이라고 생각하면 됩니다.

❷ Workbook 객체를 생성하면 기본적으로 Sheet라는 이름의 시트를 하나 생성합니다. 엑셀 프로그램에서 새 엑셀 파일을 만들면 Sheet1이라는 이름의 시트가 하나 생성되어 있는 것과 동일합니다. wb.active는 워크북 wb에서 현재 활성화된 시트를 나타내는 객체를 반환하는 속성입니다. 따라서 Workbook 객체 생성 후 wb.active를 실행하면 자동으로 생성된 기본 시트를 선택할 수 있습니다. 이 시트 객체를 ws에 할당한 것입니다.

❸ 선택한 시트의 제목을 변경하기 위해 title 속성을 사용합니다. title은 읽거나 쓸 수 있는 속성이므로 바로 ws.title = ‘시트 제목’와 같이 사용하여 시트 이름을 변경합니다.

❹ 시트에서 셀에 접근할 때는 ws[‘셀 주소’]와 같이 코드를 작성합니다. 셀 주소란 A1과 같이 열, 행을 붙여 쓴 문자열을 의미합니다. 여기서는 ws[‘A1’] 셀에 접근하여 ‘이철수’라는 값을 셀에 저장합니다.

❺ 엑셀 파일을 저장할 때는 시트만 저장하는 게 아니라 워크북을 저장해야 하므로 Workbook 객체를 사용해야 합니다. 따라서 wb.save(‘저장할 파일 경로’)와 같이 엑셀 파일을 저장하면 됩니다.

❻ 마지막으로 wb.close( )로 사용 중이던 Workbook 객체를 닫습니다. Workbook 객체를 사용한 후 wb.close( )를 호출하여 닫지 않아도 일반적으로는 큰 문제가 발생하지 않습니다. 그렇지만 많은 파일에서 작업을 반복하려면 파일을 닫아야 합니다. 왜냐하면 파일을 닫지 않고 다른 작업을 추가하면 시스템이 해당 객체를 열어둔 상태에서 작업하므로 리소스를 많이 잡아먹기 때문입니다. 또한 파일을 제대로 닫지 않으면 파일 시스템에서 변경 사항이 즉시 반영되지 않거나 데이터 손실이 발생할 수도 있습니다. 따라서 워크북을 사용한 후에는 반드시 close( )를 호출하여 파일을 안전하게 닫는 것이 좋습니다.

코드를 실행하면 수강생_리스트.xlsx 파일이 생성됩니다. 파일을 열어봅시다. 파일을 보면 코드에서 작성한 대로 A1 셀에 ‘이철수’라는 값이 입력된 것을 확인할 수 있습니다.

03 한 행에 데이터 입력하고 새 시트 추가하기

이번에는 시트에 여러 데이터를 한 번에 입력해보고, 새 시트도 추가해봅니다. 한 행에 데이터를 여러 개 입력하려면 데이터를 리스트 형태를 만들어준 다음 ws.append( ) 함수에 리스트를 전달하면 됩니다. 워크북 만들기부터 데이터 입력까지 다시 한 번 코드를 작성하여 실행해보겠습니다.

• ws.append( )는 openpyxl의 Worksheet 클래스에 속한 메서드이지만, 이 책에서는 편의상 함수와 메서드를 구분하지 않고 모두 ‘함수’라는 용어로 통일하여 지칭합니다.

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = '수강생_정보'
# ❶ 헤더(열 제목) 리스트
column = ['번호', '이름', '과목'] 
# ❷ 첫행에헤더입력 ws.append(column)
# ❸ 두번째행에데이터추가
row = [1, '이철수', '수학'] ws.append(row)
# ❹ 시트 추가 wb.create_sheet('중간평가') 
wb.create_sheet('기말평가') 
wb.save('수강생_리스트.xlsx') 
wb.close()

❶ 열 이름을 추가하기 위해 column 변수에 리스트를 저장합니다.

❷ 첫 번째 행에 append( ) 함수로 column을 헤더로 입력합니다. append( ) 함수는 시트에서 데이터가 입력되어 있는 마지막 행을 찾고, 그다음 행에 값을 입력합니다. 지금은 시트를 생성한 후 아무 데이터를 입력하지 않았으므로 첫 번째 행에 column을 입력할 수 있습니다.

❸ row를 생성하여 append( ) 함수를 실행합니다. 앞에서 열 이름을 첫 번째 줄에 입력했으므로 두번째줄에값이추가될것입니다.

❹ 새 시트도 추가해봅니다. Workbook 객체에 create_sheet( ) 함수를 호출하면 새 시트를 추가합니다. 인수로는 시트 이름을 문자열로 전달하면 되는데, 만약 문자열을 전달하지 않으면 시트 이름은 자동으로 생성됩니다. 여기서는 ‘중간평가’, ‘기말평가’를 문자열로 전달하여 시트를 추가했습니다.

04 여러 행에 데이터 입력하기

이제 ‘03 **한 행에 데이터 입력하고 새 시트 추가하기’**에서 배운 것을 응용하여 한 번에 여러 행의 데이터를 입력하겠습니다. 각 행에 입력할 데이터를 이중 리스트로 만들고, 그런 다음 for문으로 리스트를 순회하여 append( ) 함수를 호출하겠습니다.

• 엑셀 파일을 연 상태에서 코드를 실행하지 말고 파일을 닫은 상태에서 실행하세요.

from openpyxl import Workbook
 
wb = Workbook()
ws = wb.active
ws.title = '수강생_정보'
column = ['번호', '이름', '과목']
ws.append(column)
# 1 행단위로추가할데이터리스트생성
row = [[1, '이철수', '수학'], [2, '최학준', '컴퓨터'], [3, '김미소', '영어']] # 2 리스트를 순회하며 행별로 데이터 입력
for data in row:
  ws.append(data)
  wb.save('수강생_리스트.xlsx')
  wb.close()

❶ 이중 리스트 row를 생성합니다.

❷ 이중 리스트를 for문으로 순회하며 ws.append()로 데이터를 입력합니다.

파일을 열어보면 첫 행에는 column에 입력한 열 이름이 입력되어 있고, 그다음 행부터 이중 리스트로 입력한 데이터가 입력된 것을 확인할 수 있습니다.

05 열 단위로 데이터 입력하기

이번에는 엑셀에 열 단위로 데이터를 입력하는 방법을 알아보겠습니다. 열 단위로 데이터를 입력하는 방법은 아쉽게도 행 단위로 입력할 수 있는 append()와 같은 함수는 없습니다. 열 단위로 입력할 때는 셀에 하나씩 접근하여 입력해야 합니다. 실무에서는 열 단위로도 데이터를 입력할 일이 많으므로 여기서 알아두면 유용할 것입니다. 열 단위로 입력할 수 있는 몇 가지 방법을 소개합니다.

cell() : 특정 셀에 접근하여 값 설정하기

엑셀 시트에서 특정 셀에 접근하거나 해당 셀의 값을 설정하는 cell( )이라는 함수가 있습니다. cell( ) 함수는 다음과 같은 매개변수를 가집니다.

• row : 행 인덱스 설정
• column : 열 번호 설정
• value : 값 설정

예를 들어 column은 그대로 두고 row를 1씩 늘려가며 값을 입력하면 열 단위로 1칸씩 행을 아래로 이동하면서 값을 입력할 수 있습니다. 예를 들어 다음과 같이 두 번째 열에 데이터를 입력하고 싶다면 column을 2로 고정하고 나머지 값을 반복문과 조합하여 입력하면 됩니다.

cell( ) 함수를 사용하여 어떻게 특정 셀의 값을 설정하는지 코드를 살펴봅시다.

wb = Workbook()
ws = wb.active
ws.title = '수강생_정보'
# ❶ 열 단위로 입력할 데이터 리스트 생성
data = [ '이철수', '김미소', '최학준' ]
# ❷ for문으로 리스트를 순회하며 각 요소와 해당 인덱스를 함께 가져와 반복 처리
for i, value in enumerate(data):
  # ❸ 'A'열의 각 셀에 순서대로 접근하여 데이터를 입력
  ws.cell(row=i+1, column=1, value=value)
  
wb.save('수강생_리스트.xlsx')

❶ 먼저 열로 입력할 데이터를 리스트로 만들어 data에 할당합니다.

❷ data를 for문으로 순회하며 값을 입력합니다. 각 요소의 순서값을 가져오기 위해 enumerate( ) 함수를 조합합니다. for문에서 enumerate( ) 함수를 사용하면 data를 순회할 때 인덱스와 값을 함께 가져올 수 있게 해줍니다. 이렇게 코드를 작성하면 다음과 같이 순회합니다.

• 첫 번째 순회 : 1을 i에, ‘이철수’를 value에 할당
• 두 번째 순회 : 2를 i에, ‘김미소’를 value에 할당
• 세 번째 순회 : 3을 i에, ‘최학준’을 value에할당

❸ 이때 cell의 column은 1이므로 첫 번째 열에 고정된 상태입니다. 그 결과 A1, A2, A3에 각각의 값을 입력할 수 있는 것입니다.

이와 같이 cell( ) 함수를 사용할 때 열 번호를 고정시키고 행 인덱스만 변경하면 열 단위로 데이터를 입력할 수 있습니다.

enumerate( ) 함수의 동작 원리가 궁금해요!

enumerate( ) 함수는 반복할 수 있는 객체를 받아 (인덱스, 값) 튜플 형태의 반복할 수 있는 객체를 다시 반환합니다. 이를 for문에 조합하면 반복문의 코드 블록에 인덱스와 값을 함께 활용할 수 있습니다. 다음 코드를 보면 무슨 말인지 금방 알 수 있을 것입니다.

fruits = ['사과', '바나나', '딸기']
for idx, fruit in enumerate(fruits):
  print(idx, fruit)

0 사과
1 바나나
2 딸기

코드에 표시한 idx에 주목합시다. enumerate(fruits)는 for문의 idx, fruit에 순차적으로 다음 값을 넘겨줍니다.

1. (0, ‘사과’) → 0은 idx에, ‘사과’는 fruit에 전달
2. (1, ‘바나나’) → 1은 idx에, ‘바나나’는 fruit에 전달
3. (2, ‘딸기’) → 2은 idx에, ‘딸기’는 fruit에 전달

이렇게 튜플로 넘겨준 값을 idx, fruit이 각각 받아 반복문에 사용하여 출력에 활용한 것입니다. 반복할 수 있는 객체를 출력할 때 순서값이 필요한 경우라면 유용하게 사용할 수 있습니다.

ws[A1] : 셀 주소 지정 방식 활용하기

엑셀에 셀 단위로 데이터를 입력할 때는 ws[A1]과 같이 시트에서 셀 주소를 사용해 셀에 접근할 수 있다고 학습했습니다. 이 방식을 응용해 데이터를 입력할 열의 모든 셀에 각각 접근하여 데이터를 채울 수 있습니다.

from openpyxl.utils import get_column_letter

wb = Workbook()
ws = wb.active
ws.title = '수강생_정보'
data = [ '이철수', '김미소', '최학준' ]

# 1 enumerate() 함수를 사용하여 리스트의 인덱스와 값 가져오기
for i, value in enumerate(data):
  # 2 f-string을 사용해 셀 주소를 지정하고 값을 할당
  ws[f'B{i+1}'] = value
  
wb.save('수강생_리스트.xlsx')

❶ for문으로 리스트의 요소를 순회할 때 enumerate( ) 함수를 함께 사용하여 요소의 인덱스를 가져옵니다.

❷ 열은 B열로 고정하고 인덱스만 변경하기 위해 f-string 포매팅으로 ws[f’B{i+1}’]와 같이 셀 주소를 지정합니다. 그러면 요소를 순회하면서 입력할 셀의 위치값이 B1, B2, … 와 같이 생성됩니다. 이렇게 생성된 셀 위치에 접근하면서 data에서 가져온 value를 셀 값으로 입력합니다.

엑셀 열 이름을 번호로 사용할 순 없을까요?

엑셀은 열 이름이 A, B, C, … Z, AA, AB와 같이 알파벳으로 되어 있습니다. 그런데 이 값은 사람이 바로 몇 번째인지 이해하기 어렵습니다. 예를 들어 AA열에서 13만큼 떨어진 곳의 위치는 무엇일까요? 아마도 바로 대답하기 어려울 것입니다. 이럴 때는 openpyxl.utils.cell에서 다음 함수를 사용하여 엑셀의 열 이름을 번호로 변환하여 사용하면 됩니다.

• openpyxl.utils.cell.get_column_letter(열 번호) : 열 번호로부터 엑셀 열 이름 얻기
• openpyxl.utils.cell.column_index_from_string(열 이름) : 엑셀 열 이름으로부터 열 번호 얻기

함수는 아주 간단하므로 코드를 작성하여 확인해보겠습니다. 여기서는 함수 호출을 간단하게 하기 위해 openpyxl.utils.cell의 함수 get_column_letter( )와 column_index_from_string( )을 지정하여 import했습니다.

from openpyxl.utils.cell import get_column_letter, column_index_from_string

# 열 이름 얻기
index = 16
print('열 번호 16의 열 이름:', get_column_letter(index))
# 열 번호 얻기
print('열 이름 AB의 열 번호:', column_index_from_string('AB'))

열 번호 16의 열 이름: P
열 이름 AB의 열 번호: 28

코드를 실행하면 함수 호출의 결과로 열 번호 16에 해당하는 열 이름 P와, 열 이름 AB에 해당하는 열 번호 28이 나오는 것을 확인할 수 있습니다. 이처럼 엑셀 작업 중 열 이름을 열 번호로 변환하여 계산하거나, 열 번호로 열 이름을 알아내야 하는 경우에 이런 함수들이 유용할 것입니다.

iter_cols( ) : 특정 범위의 열을 순회하기

iter_cols( ) 함수를 사용하면 특정 범위의 열을 순차적으로 순회할 수 있습니다. ‘순차적으로 순회한다’라는 말이 잘 이해가 되지 않을 것입니다. 다음 그림을 봅시다.

ws.iter_cols( ) 함수를 실행하면 그림에서 보는 것처럼 col_cells 객체를 만듭니다. 여기서 열 객체는 말 그대로 엑셀의 열 작업을 할 수 있는 객체를 의미합니다. 열 객체에는 다시 셀 객체가 들어있습니다. 이 객체들을 이용해서 값을 입력하는 작업을 합니다. iter_cols( ) 함수에는 min_col, max_col, min_row, max_row 매개변수가 있습니다. 각 매개변수는 다음을 의미합니다.

• min_col = 1 → 첫 번째 열부터
• max_col = 2 → 두 번째 열까지의 열 객체
• min_row = 1 → 각 열에서 첫 번째 행부터
• max_row = 3 → 세 번째 행까지의 셀 객체

from openpyxl import Workbook
 
wb = Workbook()
ws = wb.active
ws.title = '수강생_정보'
data = [ ['이철수', '김미소', '최학준'], ['메이허', '브라이스유', '박현규'] ]

# ❶ iter_cols() 함수로 특정 범위를 반복할 수 있는 객체 생성
data_cells = ws.iter_cols(min_col=1, max_col=2, min_row=1, max_row=3)

# ❷ 지정된 셀을 순회하며 데이터를 입력
# ❸ data_cells에서 열을 순차적으로 순회
for col_idx, col_cells in enumerate(data_cells):
  # ❹ 현재 열인 col_cells에서 각 행을 순차적으로 순회
  for row_idx, cell in enumerate(col_cells):
    cell.value = data[col_idx][row_idx] # 5 cell.value 속성으로 셀에 값 입력

wb.save('수강생_리스트.xlsx')

코드를 실행하여 만들어진 파일을 보면 값이 제대로 입력된 것을 볼 수 있습니다.

❶ iter_cols( ) 함수로 열 단위로 셀을 반복할 수 있는 객체를 생성합니다. min_col을 1로, max_col을 2로 설정하여 첫 번째 열부터 두 번째 열까지 반복할 수 있는 객체가 만들어집니다. 행은 min_row는 1, max_row가 3이므로 첫 번째 행부터 세 번째 행까지 반복할 수 있는 객체가 만들어집니다.

❷ 이중반복문으로열→행순서로순회합니다.

❸ 각 열을 먼저 순회합니다. 열을 순회할 때 열의 인덱스까지 가져오기 위해 enumerate( ) 함수를 사용했습니다. data_cells 안에서 각 열 객체가 갖는 인덱스가 col_idx에 차례로 들어가고, 열 객체 자체는 col_cells로 들어갑니다. 그 결과 col_idx는 순서대로 0, 1이, col_cells에는 첫 번째 열 객체, 두 번째 열 객체가 들어있습니다.

❹ 현재 열에서 각 행을 순회합니다. 외부 for문에서와 마찬가지로 enumerate( ) 함수를 함께 사용해 row_idx에는 순서대로 0, 1, 2가, cell에는 첫 번째 셀 객체, 두 번째 셀 객체, 세 번째 셀 객체가 들어옵니다.

❺ 순회로 얻은 셀 객체에 값을 입력합니다. 입력하는 값은 중첩된 인덱싱을 사용해 이중 리스트인 data에서 가져옵니다. 예를 들어 현재 col_idx가 0, row_idx가 1인 단계라고 가정하면, A2 셀에 data[0][1]에 해당하는 값인 ‘김미소’가 입력됩니다.

06 전체 코드 확인하기

전체 코드는 다음과 같습니다. 혹시라도 단계별로 나눠 진행한 코드가 잘 동작하지 않으면 다음 URL을 참고하여 실습을 완료해보세요.

• 엑셀 데이터 사용하기 : bit.ly/4g5U6IA

더 많은 업무 자동화 방법은 신간 <파이썬 업무 자동화 일잘러 되기 + 챗GPT>를 참고하세요!