08. 엑셀 파일 다루기

송지윤·2025년 8월 1일
python

Python

목록 보기
15/20

파이썬 서드파티 라이브러리인 openpyxl과 xlsxwriter를 이용해 마이크로소프트 오피스의 엑셀 파일을 다루는 파이썬 프로그래밍

엑셀

엑셀은 윈도우 운영체제에서 인기 있는 강력한 스프레드시트 프로그램으로, 앞서 배운 텍스트 형식의 CSV 파일과는 다르게 바이너리(binary) 형식의 파일이다.
엑셀 파일은 워크북(workbook)이라고 하는데, 워크북은 하나 이상의 워크시트(worksheets)를 포함하므로 엑셀 파일을 스프레드시트(spreadsheets)라고도 한다. 스프레드시트는 열과 행, 즉 셀(cell)로 이루어진 표 형식의 데이터를 조작, 분석, 저장하는 애플리케이션을 의미한다.
엑셀로는 데이터를 생성하고 수식을 사용하여 각종 통꼐 및 분석 기능 수행하거나 차트 그리기, 데이터의 일부를 선택하거나 편집하는 등의 다양한 작업을 수행할 수 있다. 다음과 같은 라이브러리를 사용하면 엑셀로 할 수 있는 작업들을 파이썬 프로그램에서도 자유롭게 할 수 있다.

  • xlrd : 엑셀 파일의 데이터 읽기
  • xlwt : 엑셀 파일을 생성하고 데이터 쓰기
  • openpyxl : 엑셀 파일의 데이터를 읽고 쓰기
  • xlsxwriter : 엑셀 파일을 생성하고 데이터 쓰기, 현재 읽기 작업은 미지원

openpyxl 라이브러리

openpyxl은 엑셀과 관련된 XLSX, XLSM, XLTX,XLTM 파일을 읽고 쓰기 위한 파이썬 라이브러리이다. 엑셀 통합 문서 작성, 문서 읽기, 수식, 셀 병합, 이미지 삽입, 데이터 분석 라이브러리인 판다스(pandas)와의 연계, 차트 삽입, 피벗 테이블, 필터 및 정렬 등의 다양한 기능을 제공한다. openpyxl은 라이브러리명과 모듈명이 동일하다.

pip show openpyxl

pip show 명령어로 라이브러리 기본 정보, 버전, 홈페이지, 의존성 라이브러리 등을 확인할 수 있다.

openpyxl 모듈을 사용하는 방법
1. 모듈을 임포트한다.

import openpyxl
  1. 전체 엑셀 시트를 읽어온다. 엑셀을 다루는 모듈에서는 제일 먼저 엑셀 파일을 의미하는 워크북 객체를 생성한다.
    load_workbook() 함수를 통해 매개변수로 주어진 엑셀 파일이 객체 형태로 메모리에 로딩된다.
workbook = openpyxl.load_workbook('file.xlsx')
  1. 워크북에 존재하는 모든 시트의 정보를 가져오거나 특정 워크시트를 가져온다.
# 워크북에 존재하는 모든 시트의 정보를 가져온다.
workbook.sheetnames

# 주어진 시트 이름의 워크시트 객체를 가져와서 worksheet 변수에 할당한다.
worksheet = workbook['sheet_name']
  1. 워크시트 내의 셀을 읽어와서 다양한 작업을 수행한다.
    셀을 읽어오려면 셀의 열과 행 정보를 알아야 하는데, 엑셀에서 셀을 선택하면 맨 왼쪽 위에 열 이름과 행 번호가 합쳐진 A1 형태의 셀 정보를 보여준다. 열은 A부터 시작하여 증가하고, 행은 1부터 시작하여 증가한다.
    예를 들어 worksheet['A1']이라고 하면 워크시트의 AQ에 해당하는 셀을 의미하고, 셀의 값을 가져오려면 worksheet['A1'].value를 사용한다. 이처럼 셀의 정보를 가져올 때는 셀의 이름을 사용하거나 행과 열의 번호, 즉 위치 정보를 사용한다.
    셀 객체는 cell() 메소드를 사용하여 접근한다. 예를 들어 3행 2열의 셀 객체 정보를 가져오려면 다음처럼 cell() 메소드의 매개변수로 행과 열을 명시한다.
worksheet.cell(row=3, column=2)

== B3
엑셀은 1부터 시작함

엑셀 파일 데이터 읽기

import openpyxl
# openpyxl warning이 뜨는 것들이 있음
import warnings
warnings.simplefilter('ignore')
# warnings 메세지 무시하겠다.
workbook = openpyxl.load_workbook('common.xlsx')

print('워크북 내의 워크시트 :', workbook.sheetnames)
print('=' * 30)

# 고객 이름의 워크시트 가져오기
customer = workbook['고객']
# 셀 객체 읽기
print('첫번째 셀 객체 :', customer['A1'])
print('첫번째 셀 객체의 값 :', customer['A1'].value)

print('3행 2열 셀 :', customer.cell(row=3, column=2))
print('3행 2열 셀 :', customer.cell(row=3, column=2).value)

print('이름 :', customer['B3'].value, customer['C3'].value)

출력 결과

워크북 내의 워크시트 : ['common', '고객', '상품']
==============================
첫번째 셀 객체 : <Cell '고객'.A1>
첫번째 셀 객체의 값 : 번호
3행 2열 셀 : <Cell '고객'.B3>
3행 2열 셀 : 이
이름 : 이 순신

xlsxwriter 라이브러리

xlsxwriter는 엑셀 파일을 생성하고 내용을 기록할 때 사용하는 파이썬 라이브러리이다. 엑셀 파일에 텍스트와 이미지, 차트, 수식, 하이퍼링크 등을 삽입할 수 있고 데이터 분석용 라이브러리인 pandas 모듈과의 연동, 필터, 조건부 서식 등의 기능을 지원한다. xlsxwriter는 라이브러리명과 모듈명이 동일하다.
1. 모듈을 임포트한다.

import xlsxwriter
  1. 워크북 객체를 생성한다. 엑셀을 다루는 모듈에서는 제일 먼저 워크북 객체를 생성한다. Workbook() 함수를 사용하여 workbook 객체를 생성하고, 매개변수로 전달한 이름으로 엑셀 파일이 생성된다.
workbook = xlsxwriter.Wrokbook('file_name.xlsx')
  1. 앞에서 생성한 workbook 객체의 add_worksheet() 함수를 사용하여 워크시트를 추가한다.
worksheet = workbook.add_worksheet()
  1. worksheet 객체의 write() 메소드를 사용하여 데이터를 엑셀 시트에 추가하는 등 다양한 작업을 수행한다.
    write() 메소드의 첫 번째 매개변수에는 셀 이름을 문자열로 전달한다.
    write() 메소드에 첫 번째 매개변수로 행의 번호를, 두 번째 매개변수로 열의 번호를 사용하여 지정된 행과 열에 값을 추가할 수 있다. 이때 행과 열의 번호는 0부터 시작한다.
worksheet.write('A1', '안녕')

worksheet.write(0, 0, '안녕')
  1. close() 메소드를 호출하여 workbook 객체를 닫는다. 파일 쓰기를 한 뒤에는 workbook 객체에 close() 메소드를 호출해야 작성한 내용이 실제 파일에 기록된다.
workbook.close()

엑셀 파일 생성

import xlsxwriter

workbook = xlsxwriter.Workbook('result_excel_write.xlsx')
worksheet = workbook.add_worksheet()

items = [
    ['홍길동', 100],
    ['신사임당', 200],
    ['이순신', 300],
    ['장보고', 400]
]

row = 0
col = 0

for item, cost in items :
    worksheet.write(row, col, item)
    worksheet.write(row, col+1, cost)
    row += 1

# 마지막 행에 TOTAL
worksheet.write(row, 0, 'Total')
worksheet.write(row, 1, '=SUM(B1:B4)')

workbook.close()

엑셀 수식과 차트

xlsxwriter를 사용하면 기본적인 읽기와 쓰기 외에도 데이터의 표준편차, 로그, 다른 작업 간의 추세 파악 같은 엑셀 시트가 지원하는 수많은 작업을 수행할 수 있다. 특히 수식을 사용하여 다양한 연산을 수행할 수 있고, 차트를 생성할 수도 있다.
앞에서 write() 메소드에 문자열 형태로 '=SUM(B1:B4)'처럼 수식을 입력해봤는데, worksheet 객체의 write_formula() 메소드를 통해서도 문자열로 수식을 사용할 수 있다.

worksheet.write(row, 1, '=SUM(B1:B4)')
worksheet.write_formula('A1', '=SUM(1, 2, 3)')

또한, write_formula() 메소드에는 지정된 셀에 수식 또는 함수 등을 사용할 수 있다.

worksheet.write_formula(0, 0, '=B3 + B4')
worksheet.write_formula(2, 0, '=SUM(B1:B5)')
worksheet.write_formula('A5', '=AVERAGE(1, 2, 3, 4)')

xlsxwriter 모듈로는 선형 차트(line, chart), 막대 차트(bar chart), 파이 차트(pie chart) 등 다양한 형태의 차트도 그릴 수 있다.
차트를 그릴 때는 add_chart(), add_series(), insert_chart() 메소드를 사용한다.
1. workbook 객체의 add_chart() 메소드를 사용하여 차트 유형 만든다. 매개변수에는 딕셔너리 형태로 차트의 형태를 전달한다.

chart = workbook.add_chart({'type' : 'line'})
  1. chart 객체의 add_series() 메소드로 차트를 그리는 데 필요한 데이터가 있는 셀을 지정한다. 특이한 점은 'A1' 이라는 이름을 표현할 때 'A$1' 의 형태로 $를 붙여서 사용한다는 점이다. 예를 들어 Sheet1 시트의 A1부터 A6까지의 데이터로 차트를 그린다면 '=Sheet1!A1:1:A$6'라고 입력한다. 'Sheet1'은 시트명이다.
    chart 객체의 add_series() 메소드는 차트에 데이터 시리즈를 추가할 때 사용하며, 매개변수는 차트 시리즈 옵션을 딕셔너리 형태로 지정한다.. 차트 시리즈란 엑셀에서 값, 축 레이블 및 서식과 같이 표시되는 데이터를 정의하는 정보다.
chart.add_series({'values' : '=Sheet1!$A$1:$A$6'})

add_series() 메소드에 설정할 수 있는 주요 속성

  • values : 차트와 워크시트 데이터를 연결하는 시리즈의 가장 중요한 속성이며 모든 차트 개체의 유일한 필수 옵션이다. 데이터 범위는 수식을 사용하거나 값 목록을 사용하여 설정할 수 있다.
  • categories : 차트 카테고리의 레이블을 설정한다. 범주는 X축과 거의 같으며 차트는 1_n 형식이다.
  • name : 수식 입력줄에 표시되는 시리즈의 이름을 설정한다. 원형/도넛형이 아닌 차트에는 범례에도 표시된다.
  • line : 선의 종류와 색상, 너비 등의 속성을 설정한다.
  • border : 테두리 색상, 스타일 등의 속성을 설정한다.
  • fill : 단색 채우기 속성을 설정한다.
  • pattern : 패턴 채우기 속성을 설정한다.
  • gradient : 그라데이션 채우기 속성을 설정한다.
  • marker : 스타일, 색상 등 시리즈 마커의 속성을 설정한다.
chart.add_series({
	'categories' : 'Sheet1!$A$1:$A$5',
    'values' : 'Sheet1!$B$1:$B$5',
    'line' : {'color' : 'red'}
})

chart.add_series({
	'categories' : ['Sheet1', 0, 0, 4, 0],
    'values' : ['Sheet1', 0, 1, 4, 1],
    'line' : {'color' : 'red'}
})
  1. worksheet 객체가 가진 insert_chart() 메소드로 엑셀 시트에 차트를 추가한다. insert_chart() 메소드의 첫 번째 매개변수에는 셀 이름으로 위치를 지정하고, 두 번째 매개변수에는 앞서 생성한 chart 객체를 전달한다.
worksheet.insert_chart('C1', chart)

엑셀 차트 그리기

chart = workbook.add_chart({'type' : 'line'})
chart.add_series({'values' : '=Sheet1!$A$1:$A$5'})

chart.add_series({
	'categories' : 'Sheet1!$A$1:$A$5',
    'values' : 'Sheet1!$B$1:$B$5',
    'line' : {'color' : 'red'}
})

worksheet.insert_chart('C1', chart)

close() 이전에 코드 추가

케이스 스터디 - 엑셀 작업

파이썬을 이용해 엑셀로 데이터를 분석하는 케이스 스터디를 진행해보자. 우리는 회사의 최근 4년간 내부 현황을 비교하여 엑셀 시트에 차트로 시각화해야 한다. 년도, 수익, 매출 원가, 이익으로 구성된 데이터는 파이썬의 리스트 형태로 제공되며, 엑셀 수식을 사용하여 백분율로 순수익을 계산하는 업무를 파이썬으로 실행한다.

import xlsxwriter

# Workbook 객체를 생성하고 워크시트를 추가
workbook = xlsxwriter.Workbook('result_case.xlsx')
worksheet = workbook.add_worksheet()

# bar chart 추가
chart = workbook.add_chart({'type' : 'bar'})

# 2차원 리스트 형태로 데이터 제공
items = [
    ['년도', '2022', '2023', '2024', '2025'],
    ['수익', 3000, 5000, 5500, 6000],
    ['매출 원가', 1500, 3000, 2000, 3000],
    ['이익', 1500, 2000, 3500, 3000]
]

# write_row() -> 행 단위로 write
row = len(items)
for r in range(row) :
    worksheet.write_row('A' + str(r+1), items[r])
    # worksheet.write_row('A1', items[0]) ['년도', '2022', '2023', '2024', '2025']

# 차트에 데이터 추가
# 리스트로 입력할 값들을 만들어둔다.
series_datas = ['=Sheet1!$B$2:$B$4' # 3000, 1500, 1500 2022년도 데이터
                ,'=Sheet1!$C$2:$C$4' # 2023
                ,'=Sheet1!$D$2:$D$4' # 2024
                ,'=Sheet1!$E$2:$E$4'] # 2025

# items 첫번째 행의 열의 개수 : 5
first_row = len(items[0])
for r in range(first_row) :
    # 첫번째 열이 다 한글임 열 건너뛰기
    if r == 0 :
        continue
    chart.add_series({'values': series_datas[r-1], 'name': items[0][r]})
    # series_datas 는 0부터 가져와야함
    # names 범례
    # chart.add_series({'values' : '=Sheet1!$B$2:$B$4', 'name' : '2022'})
worksheet.insert_chart('A8', chart)

# 수식으로 증가율 추가
formulas = ['증가율', '=(B4/B2)*100'
            , '=(C4/C2)*100'
            , '=(D4/D2)*100'
            , '=(E4/E2)*100']

col = 0
for formula in formulas :
    worksheet.write(5, col, formula)
    col += 1

workbook.close()

엑셀 파일 확인

profile
송지윤
이전 포스트

07. CSV와 JSON 파일 다루기

다음 포스트

09. 워드 파일 다루기

0개의 댓글