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

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

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

2. 엑셀 데이터 읽기

실무를 하다 보면 기존에 만들어 둔 엑셀 파일을 불러와서 데이터를 추가하거나 수정하는 경우도 많습니다. 여기서는 엑셀 파일을 읽어 작업하는 방법을 알아보겠습니다. 그리고 하나 미리 밝혀둘 점은 openpyxl은 모든 엑셀 파일의 구성 요소를 가지고 올 수 없다는 것입니다. 예를 들어 이미지나 차트는 가져오지 못합니다. 만약 이런 요소를 포함하고 있는 파일이 있다면 텍스트 위주의 작업만 할 수 있도록 복사본을 준비하여 작업하는 것이 바람직합니다.

00 실습을 시작하기 전에

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

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

01 액셀 파일 불러오기

‘월별구매고객리스트.xlsx’ 엑셀 파일을 다운로드하여 열어봅시다.

10월부터 12월까지의 시트와 각 시트의 데이터를 취합한 2023년_전체 시트가 있습니다. 구체적으로 데이터를 살펴보면 각 제품별로 고객명, 소속, 고객이 구매한 물건과 수량, 단가, 매출액이 나와 있습니다. 파일을 살펴보았다면 파일을 다시 닫기 바랍니다. 본격적인 실습을 위해 파일의 위치를 주피터 노트북을 실행하는 위치와 동일한 곳에 두고 실습을 시작하겠습니다. 다음은 파일을 읽고, 시트를 활성화하는 코드입니다.

# ❶ openpyxl 라이브러리에서 load_workbook() 함수 가져오기
from openpyxl import load_workbook

# ❷ '월별구매고객리스트.xlsx' 엑셀 파일 불러오기
wb = load_workbook(filename='월별구매고객리스트.xlsx')

# ❸ 활성화된 시트 불러온 다음 제목 출력해보기
ws = wb.active
print(ws.title)

12월

❶ openpyxl 라이브러리에서 load_workbook( ) 함수를 가져옵니다. openpyxl의 load_workbook( ) 함수는 엑셀 파일을 Workbook 객체로 불러올 수 있습니다.

❷ load_workbook( ) 함수를 호출해 엑셀 파일을 불러옵니다. filename 매개변수에는 파일이 있는 위치와 함께 파일 이름을 문자열로 전달합니다. 지금은 같은 경로에 파일이 저장되어 있으므로 파일명만 문자열로 전달했습니다.

❸ 워크북의 active 속성으로 활성화된 시트를 선택합니다. 활성화된 시트는 실제로 엑셀 파일을 열어 작업한 후 마지막으로 파일을 저장할 때 활성화되어 있던 시트입니다. 따라서 여러분이 엑셀 파일을 열어서 확인했을 때 마지막에 활성화한 시트가 무엇인지에 따라 ws에 저장되어 있는 활성화 시트가 달라질 수 있습니다. 활성화된 시트를 가져온 다음 워크시트의 title 속성으로 시트 제목을 출력해보았습니다.

결과를 보면 ‘12월’이라고 나왔습니다. 앞에서 파일 내용을 확인할 때 12월 시트를 마지막으로 확인한 다음 파일을 저장하여 종료했기 때문입니다. 다른 시트를 연 상태에서 파일을 저장하고 종료한 다음 다시 실행하면 다른 결과가 나올 것입니다.

이처럼 ws.active( )는 마지막으로 파일을 저장할 때 활성화되어 있던 시트를 불러오므로 어떤 시트가 활성화되어 있는지 확인하는 것이 중요합니다. 앞으로도 엑셀 파일을 열어 작업할 때는 어떤 시트가 열려 있는지 확인하고 진행하세요.

02 시트 이름 지정하여 불러오기

마지막으로 활성화된 시트가 아닌 원하는 특정 시트를 지정해 불러오기 위해서는 대괄호 [ ]를 사용합니다. ws[‘A1’]과 같이 워크시트에서 대괄호를 사용하면 셀을 선택할 수 있다고 공부했습니다. 이와 비슷한 방식으로 엑셀 파일을 불러온 워크북 객체에서 대괄호를 사용하면 특정 시트를 선택할 수 있습니다. 월별구매고객리스트.xlsx 파일에서 10월 시트를 불러온 다음 해당 시트의 A1 셀에 어떤 값이 있는지 출력해보겠습니다.

from openpyxl import load_workbook

wb = load_workbook(filename='월별구매고객리스트.xlsx')
# ❶ '10월' 시트 불러오기
ws = wb['10월']
# ❷ 'A1' 셀의 값 가져오기
print(ws['A1'].value)

10월

❶ wb[‘10월’]로 파일의 10월 시트를 불러온 다음 ws에 할당합니다.

❷ 해당 시트에서 A1 셀에 어떤 값이 있는지 확인하기 위해 ws[‘A1’].value로 ‘A1’ 셀의 값을 출력합니다. 결괏값을 보면 해당 셀에 10월이라는 값이 있는 것을 알 수 있습니다.

셀의 값은 쓸 때와 읽을 때가 달라요!

셀에 값을 쓸 때는 ws[‘A1’] = ‘이철수’와 같이 셀 객체를 선택해 바로 값을 입력할 수 있지만, 셀에 입력된 값을 읽을 때는 value 속성을 사용해야 합니다. 만약 셀의 값을 읽어 출력하려고 print(ws[‘A1’])를 실행하면 셀 객체의 문자열 표현이 출력됩니다.

print('A1객체 출력: ', ws['A1'])
print('A1의 값 출력: ', ws['A1'].value)

A1객체 출력: <Cell '10월'.A1>
A1의 값 출력: 10월

그 이유는 ws[‘A1’]은 셀에 입력된 값이 아닌 셀 객체를 반환하기 때문입니다. 셀 객체에는 셀의 위치, 스타일, 값 등 다양한 속성을 포함합니다. ws[‘A1’].value가 셀 객체의 값을 가진 속성입니다. 물론 셀에 데이터를 입력할 때도 ws[‘A1’].value = ‘이철수’와 같이 할 수도 있지만 ws[‘A1’] = ‘이철수’가 더 간결하고 직관적이기 때문에 주로 이 방식을 사용합니다.

03 전체 시트 목록 확인하기

작업 중인 엑셀 파일에서 특정 시트를 지정할 때 시트 이름을 모르거나, 엑셀 파일에 있는 시트들의 이름 확인이 필요한 경우도 있습니다. 이럴 때 워크북 객체의 sheetnames 속성을 사용하면 엑셀 파일을 직접 열어보지 않아도 확인할 수 있습니다. sheetnames 속성은 엑셀 파일의 모든 시트 이름을 리스트로 반환합니다.

wb = load_workbook(filename='월별구매고객리스트.xlsx')

# 전체 시트 목록 가져오기
sheet_names = wb.sheetnames
print(sheet_names)

['10월', '11월', '12월', '2023년_전체']

월별구매고객리스트.xlsx 파일에서 wb.sheetnames를 실행하여 sheet_names 변수에 할당한 다음 출력하니 전체 시트 이름이 포함된 리스트가 반환되었습니다.

04 시트 전체 데이터 가져오기

셀 하나의 값이 아닌 시트의 전체 데이터를 읽기 위해서는 행마다 순회하며 행에 있는 모든 셀 값을 가져올 수 있습니다.

from openpyxl import load_workbook

wb = load_workbook(filename='월별구매고객리스트.xlsx')
ws = wb['10월']
# ❶ 시트의 모든 행을 순회
for row in ws.rows:
  # ❷ 각 행의 모든 셀을 순회하며 셀의 값을 가져와 리스트로 저장
  row_values = [cell.value for cell in row]
  print(row_values)

['10월', None, None, None, None, None]
['구매일자', '구매제품', '수량', '고객명', '소속', '기존고객여부', '단가', '총액']
[datetime.datetime(2023, 10, 3, 0, 0), 'A제품', 2, '김철원', '최강회사', '기존', 20000, '=C3*G3']
[datetime.datetime(2023, 10, 16, 0, 0), 'B제품', 1, '이남연', '다알아연구소', '신규', 18000, '=C4*G4']
[datetime.datetime(2023, 10, 22, 0, 0), 'B제품', 1, '최연화', '일일컴퍼니', '신규', 18000, '=C5*G5']
[datetime.datetime(2023, 10, 29, 0, 0), 'C제품', 4, '황수지', '소속 없음', '신규', 36000, '=C6*G6']
[datetime.datetime(2023, 10, 29, 0, 0), 'D제품', 2, '유진태', '소속 없음', '기존', 68000, '=C7*G7']

❶ ws.rows는 시트의 모든 행을 순회할 수 있는 객체를 만듭니다. 이를 통해 시트에서 데이터가 있는 영역의 첫 번째 행부터 마지막 행까지 순차적으로 접근할 수 있습니다. ws.rows로 생성한 객체에는 다음과 같은 형태의 튜플들이 포함되어 있습니다.

• 첫 번째 행 : (<Cell ‘10월’.A1>, <Cell ‘10월’.B1>, <Cell ‘10월’.C1>, <Cell ‘10월’.D1>, <Cell ‘10월’.E1>, <Cell ‘10월’.F1>, <Cell ‘10월’.G1>, <Cell ‘10월’.H1>)
• 두 번째 행 : (<Cell ‘10월’.A2>, <Cell ‘10월’.B2>, <Cell ‘10월’.C2>, <Cell ‘10월’.D2>, <Cell ‘10월’.E2>, <Cell ‘10월’.F2>, <Cell ‘10월’.G2>, <Cell ‘10월’.H2>)
• ⋯ 생략 ⋯

❷ 앞에서 본 셀 객체가 튜플에 들어 있는 모습입니다. 셀의 값을 가져오려면 튜플을 다시 순회하면서 셀의 value 속성을 사용하면 됩니다. for cell in row로 튜플을 순회하며 cell.value로 셀 객체에 접근해 셀의 값을 가져오도록 한 것입니다. 동시에 결괏값을 리스트로 반환하기 위해 [ ]로 감싸는 리스트 컴프리헨션 기법을 사용했습니다.

결괏값을 보면 행별로 전체 데이터가 리스트로 반환되었습니다. 파일과 직접 비교해도 모든 데이터를 행별로 가져온 것을 알 수 있습니다.

• 파이썬에서 튜플이란 여러 개의 값을 하나의 변수에 담을 수 있는 자료형입니다. 리스트와 비슷하지만, 한 번 생성된 이후에는 값을 변경할 수 없다는 특징이 있으며, 고정된 데이터를 다룰 때 유용합니다.

리스트 컴프리헨션이란?

앞에서 row_values = [cell.value for cell in row]와 같은 리스트 컴프리헨션 기법을 처음 본 독자라면 코드가 조금 낯설게 느껴질 수도 있습니다만, 이 코드는 다음 코드를 간결하게 표현한 것일 뿐입니다.

# 빈 리스트 생성
row_values = []
# 각 행의 셀을 순회
for cell in row:
  # 셀의 값을 리스트에 추가
  row_values.append(cell.value)

리스트 컴프리헨션을 사용하면 빈 리스트를 새로 생성하고, for문으로 순회하면서 append( ) 함수를 쓰지 않아도 되어 간결하고 편리합니다. 또한 리스트 컴프리헨션에 if문을 조합하면 조건도 설정할 수 있습니다. 예를 들어 셀 값이 ‘신규’인 경우에만 리스트에 추가하려면 다음과 같이 할 수 있습니다.

row_values = [cell.value for cell in row if cell.value == '신규']

위 코드는 리스트 컴프리헨션을 사용했다면 다음은 for문과 if문을 사용해서 만든 코드입니다.

# 빈 리스트 생성
row_values = []
# 각 행의 셀을 순회
for cell in row:
  # 셀 값이 '신규'인 경우
  if cell.value == 'Hello':
    # 셀의 값을 리스트에 추가
    row_values.append(cell.value)

리스트 컴프리헨션은 for문을 사용해 리스트를 만드는 패턴을 간략하게 할 수 있어 자주 사용합니다. 이 책에서도 리스트 컴프리헨션을 자주 사용합니다.

05 특정 행을 지정하여 가져오기

첫 번째 행에는 A1 셀에 ‘10월’이라는 값만 있고 두 번째 행에는 표의 열 이름이 저장되어 있습니다. 만약 여러분이 실제 데이터 값만 이용하여 작업하고 싶다면 세 번째 행부터 작업을 시작해야 합니다. 데이터를 전부 가져온 다음 제외하는 작업을 하기보다 처음부터 불필요한 데이터를 가져오지 않는 것이 더 좋겠죠. 이번에는 특정 행만 가져오는 방법을 알아보겠습니다.

리스트 슬라이싱으로 특정 행 가져오기

처음 알아볼 것은 리스트 슬라이싱을 활용한 방법입니다.

from openpyxl import load_workbook

wb = load_workbook(filename='월별구매고객리스트.xlsx')
ws = wb['10월']

# ❶ 첫 번째와 두 번째 행을 제외하고 나머지 행을 리스트로 변환
new_rows = list(ws.rows)[2:]

# 나머지 행을 순회하며 읽기
for row in new_rows:
 row_values = [cell.value for cell in row]
 print(row_values)

[datetime.datetime(2023, 10, 3, 0, 0), 'A제품', 2, '김철원', '최강회사', '기존', 20000, '=C3*G3']
[datetime.datetime(2023, 10, 16, 0, 0), 'B제품', 1, '이남연', '다알아연구소', '신규', 18000, '=C4*G4']
[datetime.datetime(2023, 10, 22, 0, 0), 'B제품', 1, '최연화', '일일컴퍼니', '신규', 18000, '=C5*G5']
[datetime.datetime(2023, 10, 29, 0, 0), 'C제품', 4, '황수지', '소속 없음', '신규', 36000, '=C6*G6']
[datetime.datetime(2023, 10, 29, 0, 0), 'D제품', 2, '유진태', '소속 없음', '기존', 68000, '=C7*G7']

❶ ws.rows로 가져온 전체 행 객체를 list( ) 함수를 이용하여 리스트로 변환합니다. 그런 다음 리스트 슬라이싱 [2:]로 세 번째 행부터 마지막 데이터 행까지만 선택합니다.

iter_rows( ) : 특정 범위의 행 순회하기

iter_rows( ) 함수는 워크시트의 특정 행 범위를 순회하기 위해 사용합니다. 이전에 특정 범위의 열을 순회하는 iter_cols( ) 함수를 공부했었죠. iter_cols( ) 함수가 열에 대한 함수였다면 iter_rows( ) 함수는 행에 대한 함수입니다. 사용 방법을 바로 알아봅시다.

from openpyxl import load_workbook

wb = load_workbook(filename='월별구매고객리스트.xlsx')
ws = wb['10월']
# 세 번째 행부터 마지막 행까지 읽기(min_row = 3)
for row in ws.iter_rows(min_row=3, values_only=True):
 print(row)

(datetime.datetime(2023, 10, 3, 0, 0), 'A제품', 2, '김철원', '최강회사', '기존', 20000, '=C3*G3')
(datetime.datetime(2023, 10, 16, 0, 0), 'B제품', 1, '이남연', '다알아연구소', '신규', 18000, '=C4*G4')
(datetime.datetime(2023, 10, 22, 0, 0), 'B제품', 1, '최연화', '일일컴퍼니', '신규', 18000, '=C5*G5')
(datetime.datetime(2023, 10, 29, 0, 0), 'C제품', 4, '황수지', '소속 없음', '신규', 36000, '=C6*G6')
(datetime.datetime(2023, 10, 29, 0, 0), 'D제품', 2, '유진태', '소속 없음', '기존', 68000, '=C7*G7')

iter_rows( ) 함수는 다음과 같이 행 단위로 셀을 반복할 수 있는 객체를 생성합니다.

그리고 iter_rows( ) 함수도 iter_cols( ) 함수처럼 시작 행과 끝 행, 그리고 시작 열과 끝 열을 매개변수로 받아서 특정 범위 안에서만 행을 순회할 수 있습니다. 여기서는 min_row를 3으로 설정하여 세 번째 행부터 마지막 행까지를 범위로 설정했습니다. 그리고 values를 True로 지정하여 셀 객체 대신 셀의 값을 바로 반환하도록 했습니다.

• 셀 객체를 반환하면 셀 객체의 값을 다시 가져오기 위한 추가 작업을 해야 하므로 values_only를 사용하면 더 편리합니다.

iter_rows와 iter_cols 매개변수 복습하기

iter_rows( ) 함수와 iter_cols( ) 함수에서 사용하는 매개변수는 다음과 같이 동일합니다.

• min_row : 반복을 시작할 행의 인덱스(지정하지 않으면 첫 번째 행부터 시작)
• max_row : 반복을 마칠 행의 인덱스(지정하지 않으면 마지막 행까지 반복)
• min_col : 반복을 시작할 열의 인덱스(지정하지 않으면 첫 번째 열부터 시작)
• max_col : 반복을 마칠 열의 인덱스(지정하지 않으면 마지막 열까지 반복)
• values_only : Ture일 경우 셀 값을 반환(지정하지 않으면 셀 객체를 반환)

매개변수가 동일해서 ‘결국 아무거나 써도 똑같은 거 아닌가?’라는 생각을 할 수도 있습니다. 범위는 동일하게 지정할 수 있어도 열 단위냐 행 단위냐가 다르므로 필요에 따라 사용해야 하는 것을 잊지마세요.

06 수식을 제외한 결괏값 가져오기

앞에서 특정 행의 범위를 지정하여 데이터를 가져오는 실습 코드의 결과로 마지막에 출력된 열을 보면, 특정한 값이 아닌 =C3*G3과 같이 수식이 출력된 것을 알 수 있습니다.

(datetime.datetime(2023, 10, 3, 0, 0), 'A제품', 2, '김철원', '최강회사', '기존', 20000, '=C3*G3')
(datetime.datetime(2023, 10, 16, 0, 0), 'B제품', 1, '이남연', '다알아연구소', '신규', 18000, '=C4*G4')

엑셀에서는 값으로 보이지만 수식이 입력되어 있기 때문에 함께 가져온 것입니다.

수식이 아닌 엑셀에서 본 결괏값이 필요하다면 load_workbook( ) 함수로 데이터를 가져올 때 data_only 매개변수에 True 인수를 추가하면 됩니다.

from openpyxl import load_workbook

# 수식이 계산된 결괏값을 가져오려면 data_only=True
wb = load_workbook(filename='월별구매고객리스트.xlsx', data_only=True)
ws = wb['10월']

for row in ws.iter_rows(min_row=3, values_only=True):
  print(row)

(datetime.datetime(2023, 10, 3, 0, 0), 'A제품', 2, '김철원', '최강회사', '기존', 20000, 40000)
(datetime.datetime(2023, 10, 16, 0, 0), 'B제품', 1, '이남연', '다알아연구소', '신규', 18000, 18000)
(datetime.datetime(2023, 10, 22, 0, 0), 'B제품', 1, '최연화', '일일컴퍼니', '신규', 18000, 18000)
(datetime.datetime(2023, 10, 29, 0, 0), 'C제품', 4, '황수지', '소속 없음', '신규', 36000, 144000)
(datetime.datetime(2023, 10, 29, 0, 0), 'D제품', 2, '유진태', '소속 없음', '기존', 68000, 136000)

결괏값을 보면 수식이 아니라 수식 계산이 완료된 값의 형태로 반환된 것을 알 수 있습니다. 실무에서는 수식이 포함되어 있는 엑셀 파일을 자주 사용하므로 이 방법이 유용할 것입니다. 다만 파이썬에서 작업 완료 후 같은 이름으로 파일을 쓰면 수식이 사라질 수 있으니 이 점도 기억하기 바랍니다.

07 열을 지정하여 데이터 가져오기

이번에는 특정 열을 지정하는 방법을 알아보겠습니다. ws.rows로 모든 행이 포함된 객체를 생성한 다음 각 행을 순회할 때 인덱싱을 사용하면 특정 열의 값만 가져올 수 있습니다.

인덱싱으로 특정 열 가져오기

인덱싱을 사용해 월별구매고객리스트.xlsx 파일의 B열과 C열의 값만 출력해보겠습니다.

from openpyxl import load_workbook
wb = load_workbook(filename='월별구매고객리스트.xlsx')
ws = wb['10월']

# 1 시트의 모든 행 순회하기
for row in ws.rows:
  # 2 각 행의 B열 값과 C열 값을 출력
  print(row[1].value, row[2].value)

None None
구매제품 수량
A제품 2
B제품 1
B제품 1
C제품 4
D제품 2

❶ ws.rows( )로 각 행 튜플이 포함된 전체 데이터 객체를 생성하고 이 객체를 for문과 조합해 각행을 순회합니다.

❷ row[1], row[2]와 같이 인덱싱을 사용하여 B열과 C열의 셀 객체에 접근하고 value 속성을 사용해 셀 값을 가져옵니다.

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

iter_cols( ) 함수를 활용하면 열을 지정하여 데이터를 가져올 수 있습니다. 첫 번째와 두 번째 행을 제외하고 데이터를 가져오려면 이렇게 하면 됩니다.

from openpyxl import load_workbook
wb = load_workbook(filename='월별구매고객리스트.xlsx')
ws = wb['10월']

# ❶ 세 번째 행부터 마지막까지 B열과 C열까지의 범위로 열 단위로 순회
for col in ws.iter_cols(min_col=2, max_col=3, min_row=3):
  # ❷ 각 열의 셀 객체들을 순회
  for cell in col:
    # ❸ 셀의 값을 출력
    print(cell.value)

A제품
B제품
B제품
C제품
D제품
2
1
1
4
2

❶ min_col=2와 max_col=3을 설정하여 두 번째 열 B부터 세 번째 열 C까지 열 범위를 지정하고, min_row=3으로 세 번째 행부터 마지막 행까지 행 범위를 지정하였습니다.

❷ 각 열에 포함된 셀 객체에 접근하기 위해 이중 for문으로 열 객체 col을 순회합니다.

❸ 내부 for문으로 접근한 각 셀 객체에 대해 value를 출력합니다.

‘values_only=True를 쓰면 코드가 더 간단해지는 게 아닌가?’라는 생각이 들 수 있습니다. 하지만 그렇게 하면 결괏값이 달라지므로 고민해야 합니다. 여기서는 다음과 같이 코드를 작성해봅시다.

from openpyxl import load_workbook
wb = load_workbook(filename='월별구매고객리스트.xlsx')
ws = wb['10월']

# 세 번째 행부터 마지막까지 B열과 C열 데이터 출력하기
for col in ws.iter_cols(min_col=2, max_col=3, min_row=3, values_only=True):
  print(col)

('A제품', 'B제품', 'B제품', 'C제품', 'D제품')
(2, 1, 1, 4, 2)

values_only=True를 사용하면 iter_cols( ) 함수로 생성한 객체에 포함된 각 열 튜플의 각 요소는 셀 객체가 아닌 셀 값이 됩니다. 다시 말해 열 튜플을 바로 출력하기 때문에 반환값이 튜플이 됩니다. 만약 결괏값으로 튜플을 사용할 것이라면 이 방식이 유용할 수 있습니다.

08 조건으로 데이터 가져오기

엑셀에 있는 필터 기능을 활용하는 것처럼 조건을 걸어 데이터를 가져올 수 있습니다. 월별구매고객리스트.xlsx 파일의 10월 시트에서 ‘수량 >= 2’라는 조건으로 데이터를 가져오겠습니다.

from openpyxl import load_workbook

wb = load_workbook(filename='월별구매고객리스트.xlsx')
ws = wb['10월']

# 첫 번째와 두 번째 행을 제외하고 나머지 행을 리스트로 변환
new_rows = list(ws.rows)[2:]

# 새로운 행 리스트 new_rows를 순회하며 조건에 맞는 데이터 출력
for row in new_rows:
  # 1 세 번째 열의 값이 2 이상인 경우로 조건 설정
  if row[2].value >= 2:
    # 각 행의 B열 값과 C열 값을 출력
    print(row[1].value, row[2].value)

A제품 2
C제품 4
D제품 2

1 수량이 입력되어 있는 세 번째 열 객체를 row[2]로 선택한 다음 row[2].value >= 2라는 조건식으로 필터링합니다. 유의할 점은 숫자를 조건으로 걸 때는 해당 열의 데이터가 파이썬에서 문자열이 아닌 숫자여야 합니다.

추가로 조건을 판단할 때는 빈 값이 포함되어 있는 경우에도 주의해야 합니다. 아마도 ‘어차피 조건으로 거를 거라면 전체 데이터를 가져오면 안 되나?’라고 생각할 수도 있습니다. 그렇게 하면 빈 값을 조건으로 비교하여 오류가 발생하므로 안 됩니다. 정말 그런지 코드로 확인해보겠습니다.

• 만약 결과가 원하는 대로 나오지 않으면 파이썬으로 가져온 value의 type이 문자열이 아닌지 확인해보세요. 만약 문자열이라면 int( )나 float( ) 등으로 변환해야 합니다.

from openpyxl import load_workbook

wb = load_workbook(filename='월별구매고객리스트.xlsx')
ws = wb['10월']

# ws.rows로 전체 데이터가 포함된 객체 생성
for row in ws.rows:
  # 세 번째 열의 값이 2 이상인 경우로 조건 설정
  if row[2].value >= 2:
    # 각 행의 B열 값과 C열 값을 출력
    print(row[1].value, row[2].value)

------------------------------------------------------------------------
TypeError                              Traceback (most recent call last)
Cell In[37], line 9
      6 # ws.rows로 전체 데이터가 포함된 객체 생성
      7 for row in ws.rows:
      8   # 세 번째 열의 값이 2 이상인 경우로 조건 설정
----> 9   if row[2].value >= 2:
     10     # 각 행의 B열 값과 C열 값을 출력
     11.    print(row[1].value, row[2].value)

TypeError: '>=' not supported between instances of 'NoneType' and 'int'

>= 연산자가 NoneType과 int 타입을 비교할 수 없으므로 오류가 발생했습니다. 파일의 첫 행에는 A1 셀을 제외하면 데이터가 없으므로 C1의 값을 비교할 때 오류가 발생합니다. None이 있다면 그 행을 제외하거나 해당 값이 None이 아닌 경우에만 비교를 수행하도록 조건을 추가해야 합니다.

09 암호화된 엑셀 파일 다루기

openpyxl 라이브러리는 암호화된 파일은 지원하지 않습니다. 실무에서는 보안 등의 이유로 암호화된 엑셀 파일을 종종 사용하는데 파이썬으로 작업할 때 이것을 열 수 없다면 곤란하겠죠. 그럴 때는 엑셀 파일의 암호를 해제하여 다시 저장한 다음에 코드를 실행하거나 msoffcrypto-tool 라이브러리를 사용하여 암호를 해제해야 합니다. msoffcrypto-tool 라이브러리는 암호화된 MS 오피스 파일을 다룰 수 있습니다. pip 명령어로 msoffcrypto-tool을 설치합니다.

pip install msoffcrypto-tool openpyxl

설치 완료했다면 msoffcrypto-tool openpyxl로 미리 다운로드한 암호화된 월별구매고객리스트_잠김.xlsx 파일을 읽으면 됩니다. 암호는 1234이므로 이 값을 password 매개변수에 문자열로 전달하여 읽습니다.

import msoffcrypto
from openpyxl import load_workbook
import os

# ❶ 암호화된 엑셀 파일을 바이너리 읽기 모드로 열고
# ❷ msoffcrypto.OfficeFile 객체 생성
file = msoffcrypto.OfficeFile(open('월별구매고객리스트_잠김.xlsx', 'rb'))

# ❸ 파일의 암호 설정. '1234'는 암호화된 파일의 비밀번호
file.load_key(password='1234')

# ❹ 암호화된 파일을 해제하고 새로운 파일로 저장
with open('월별구매고객리스트_잠김해제.xlsx', 'wb') as f:
  file.decrypt(f)
  
# ❺ 암호 해제된 파일 열기
wb = load_workbook('월별구매고객리스트_잠김해제.xlsx')
ws = wb.active

# ❻ 시트의 모든 행을 순회하며 값을 출력
for row in ws.iter_rows(values_only=True):
  print(row)
  
# ❼ 암호 해제된 임시 파일 삭제
os.remove('월별구매고객리스트_잠김해제.xlsx')

('12월', None, None, None, None, None, None, None)
('구매일자', '구매제품', '수량', '고객명', '소속', '기존고객여부', '단가', '매출액')
(datetime.datetime(2023, 12, 13, 0, 0), 'C제품', 2, '강지원', '학생', '기존', 36000, '=C3*G3')
(datetime.datetime(2023, 12, 17, 0, 0), 'C제품', 1, '최민은', '아자코퍼레이션', '신규', 36000, '=C4*G4')
(datetime.datetime(2023, 12, 22, 0, 0), 'B제품', 1, '박서은', '상상대학교', '신규', 18000, '=C5*G5')
(datetime.datetime(2023, 12, 27, 0, 0), 'A제품', 4, '임지은', '주식회사 지은', '신규', 20000, '=C6*G6')

❶ open( ) 함수로 파일을 바이너리 읽기 모드 rb로 열어 파일 객체를 생성합니다.

❷ 파일 객체를 인라인 방식으로 직접 전달받아 msoffcrypto.OfficeFile 클래스의 객체를 생성합니다. msoffcrypto.OfficeFile( ) 클래스는 암호화된 MS 오피스 문서를 처리할 수 있습니다.

❸ load_key( ) 함수에 비밀번호인 ‘1234’를 문자열로 전달하여 비밀번호를 설정합니다. 잘못된 비밀번호를 입력하면 decrypt( ) 함수 호출 시 오류가 발생합니다.

❹ 암호를 해제한 다음 임시 파일로 저장하기 위해 with문으로 새 파일 월별구매고객리스트_잠김 해제.xlsx을 생성합니다. 파일을 생성할 때도 쓰기 전용 바이너리 모드 wb로 생성합니다. 그런 다음에는 decrypt( ) 함수를 사용하여 파일의 암호를 해제하고 그 내용을 새 파일에 씁니다.

❺ ❻ 암호를 해제한 임시 파일 월별구매고객리스트_잠김해제.xlsx를 load_workbook( ) 함수로 불러온 다음 iter_rows( ) 함수로 시트의 모든 행을 순회하며 데이터를 출력합니다.

❼ 작업 완료 후 코드를 종료할 수도 있지만 여기서는 os.remove( ) 함수로 암호가 해제된 임시 파일을 삭제했습니다. 보안상의 이유로 해당 파일을 남기지 않으려면 이렇게 작업한 파일을 바로 삭제하는 것이 안전합니다.

만약 msoffcrypto-tool을 사용하는 것이 번거롭다면 엑셀 파일의 암호를 수동으로 먼저 해제한 후에 파이썬으로 데이터 작업을 실행해도 됩니다만 이렇게 하면 자동화한 코드가 더욱 편리해질 것입니다.

• 엑셀 파일(.xlsx, .xls)은 바이너리 형식의 파일입니다. 텍스트 파일과 다르게 바이너리 형식으로 저장되어 있으므로 바이너리 모드로 읽고 써야 파일이 손상되지 않습니다.
• 인라인 방식은 프로그래밍에서 중간 변수를 사용하지 않고 직접 값이나 표현식을 사용하는 방법을 말합니다.

10 전체 코드 확인하기

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

• 엑셀 데이터 읽기 : bit.ly/41qZZeH

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