[Python] 07. 파이썬으로 엑셀 자동 보고서 만들기 (서식과 차트)

---

파이썬으로 엑셀 자동 보고서 만들기 (서식과 차트)

지난번에 흩어진 엑셀 파일 여러 개를 하나로 합쳤잖아요. 그거 열어보셨어요?

저는 막상 합쳐놓고 좀 민망했거든요. 데이터는 다 들어왔는데, 그냥 흑백 표 덩어리예요. 제목 줄도 본문이랑 똑같이 밋밋하고, 숫자는 자릿수가 안 맞아서 한눈에 안 들어오고요. 이걸 그대로 상사한테 보내자니 영 손이 안 가더라고요.

그래서 결국 어떻게 했냐면, 엑셀을 직접 열어서 제목 줄 드래그해서 색칠하고, 굵게 누르고, 테두리 치고, 맨 아래 합계 칸 만들고, 막대 차트까지 손으로 그렸어요. 매달요. 이게 은근히 시간 잡아먹는 야근의 주범이잖아요.

근데 이거, 파이썬한테 시키면 한 번에 끝나요. 그것도 매달 똑같이.

---

흑백 복사물 vs 컬러 보고서

생각해보면 서식이라는 게 별거 아니에요. 똑같은 내용을 복사기로 막 뽑은 흑백 표로 내미느냐, 제목에 색 넣고 숫자 깔끔하게 정리한 컬러 보고서로 내미느냐의 차이거든요.

내용은 같아요. 근데 받는 사람이 느끼는 인상이 완전히 다르잖아요.

그래서 오늘 할 건 딱 네 가지예요. 헤더(제목 줄) 강조, 천 단위 콤마, 합계 수식, 그리고 막대 차트. 이 네 개만 입히면 휑하던 표가 보고서처럼 보여요.

---

헤더 굵게, 색 넣기 — openpyxl 서식의 기본

서식을 다루는 도구들은 openpyxl.styles에서 가져와요. 제일 자주 쓰는 게 글자 모양을 정하는 Font랑 셀 배경색을 칠하는 PatternFill이에요.

from openpyxl import load_workbook                          # 엑셀 파일 여는 도구
from openpyxl.styles import Font, PatternFill, Alignment    # 서식 도구들

wb = load_workbook("통합.xlsx")  # 지난번에 합친 파일 열기
ws = wb.active                    # 활성 시트 선택

# 남색 배경 만들기 (fill_type="solid" 꼭 넣어야 색이 칠해져요)
header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")

for cell in ws[1]:                                  # 1행(제목 줄) 셀을 하나씩 돌기
    cell.font = Font(bold=True, color="FFFFFF")     # 굵게 + 흰 글자
    cell.fill = header_fill                         # 남색 배경 칠하기
    cell.alignment = Alignment(horizontal="center") # 가운데 정렬

wb.save("보고서.xlsx")  # 저장

 

여기서 처음 배우는 분들이 거의 다 막히는 지점이 두 개 있어요.

첫째, 색상은 #을 떼고 6자리만 적어요. 흰색은 "FFFFFF", 남색은 "4472C4" 이런 식이에요. 저도 처음에 CSS 하던 버릇으로 "#FF0000" 넣고 "왜 색이 안 칠해지지?" 하고 한참 들여다봤거든요. #만 빼면 됩니다. 원하는 색을 모르겠으면 구글에 "남색 hex"처럼 검색하면 6자리 코드가 나와요.

둘째, 서식은 범위에 한 번에 못 줘요. 엑셀에서는 제목 줄을 드래그해서 한 번에 색칠하잖아요. 근데 openpyxl에는 ws["A1:C1"].font = ... 같은 게 없어요. 그래서 위 코드처럼 for cell in ws[1]: 로 셀을 하나씩 돌면서 각각 지정해줘야 해요. 처음엔 "이걸 왜 반복문으로?" 싶은데, openpyxl에서는 이게 정해진 방식이에요. 익숙해지면 오히려 편해요.

---

숫자에 천 단위 콤마, 합계 수식까지

1000000 이렇게 적힌 숫자, 한눈에 백만인지 천만인지 안 보이잖아요. 여기에 천 단위 콤마를 넣어줄게요.

for r in range(2, ws.max_row + 1):                 # 2행부터 마지막 행까지
    ws.cell(row=r, column=2).number_format = "#,##0"  # 1000000 -> 1,000,000

 

재밌는 건, number_format은 셀 안의 진짜 값은 안 바꿔요. 보이는 옷만 갈아입히는 거예요. 1000000이라는 사람은 그대로인데 "1,000,000"이라는 옷만 입혀준 셈이죠. 그래서 나중에 계산할 때도 아무 문제 없어요.

다음은 맨 아래 합계 칸이에요. 여기서 진짜 중요한 개념 하나 짚고 갈게요.

last = ws.max_row                                       # 데이터 마지막 행 번호
ws.cell(row=last + 1, column=1, value="합계")          # A열에 '합계' 글자
total = ws.cell(row=last + 1, column=2, value=f"=SUM(B2:B{last})")  # 수식 입력
total.font = Font(bold=True)                            # 합계는 굵게
total.number_format = "#,##0"                           # 콤마도

 

"=SUM(B2:B{last})"를 넣었는데, 파이썬이 더해주는 게 아니에요. 파이썬은 그냥 =SUM(...)이라는 글자를 칸에 적어둘 뿐이고, 실제 계산은 엑셀이 파일을 열 때 해줘요. 엑셀한테 "이 칸은 위 숫자들 다 더해서 보여줘"라고 포스트잇 붙여두는 느낌이에요.

그래서 파이썬에서 이 셀을 출력하면 합계 숫자가 아니라 =SUM(...) 글자가 나와요. "왜 계산이 안 돼?" 하고 당황하실 텐데, 정상이에요. 엑셀로 열어보면 합계가 떡 하니 들어가 있어요.

---

표를 그림으로 — 파이썬 엑셀 차트 넣기

숫자가 쭉 적힌 표는 "어느 달이 제일 높지?" 한 번에 안 보이잖아요. 막대 차트로 바꾸면 0.5초면 보여요. 차트 도구는 openpyxl.chart에서 가져와요.

from openpyxl.chart import BarChart, Reference

chart = BarChart()             # 막대 차트 만들기
chart.title = "월별 매출"      # 차트 제목

data = Reference(ws, min_col=2, min_row=1, max_row=last)  # 매출 열 (헤더 포함)
cats = Reference(ws, min_col=1, min_row=2, max_row=last)  # 월 이름 (라벨, 헤더 제외)
chart.add_data(data, titles_from_data=True)              # 1행을 범례 제목으로
chart.set_categories(cats)                               # 가로축 라벨 지정

ws.add_chart(chart, "D2")      # D2 위치에 차트 얹기

 

Reference가 "표의 어느 칸부터 어느 칸까지를 차트에 쓸지" 정하는 부분이에요. 값(매출) 범위 따로, 라벨(월 이름) 범위 따로 잡아준다고만 기억하시면 돼요.

그리고 여기 진짜 조심할 게 하나 있어요.

차트 데이터 범위는 합계 행 직전까지만 잡아야 해요. 위 코드에서 max_row=last로 끝낸 게 그 이유예요. last는 합계를 넣기 전의 마지막 데이터 행이거든요. 만약 합계까지 범위에 들어가면, 막대 차트에 엄청 큰 합계 막대가 하나 끼어서 다른 막대들이 다 쪼그라들어 보여요. 그러면 차트가 영 이상해지죠.

---

이거 한 번 돌리면 보고서 끝 (메인 예제)

자, 위에서 본 걸 다 합쳤어요. 이 한 덩어리를 돌리면 휑하던 표가 보고서로 변해요.

from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.chart import BarChart, Reference

wb = load_workbook("통합.xlsx")   # 합친 파일 (월 / 매출 두 열)
ws = wb.active

last = ws.max_row                  # 합계 넣기 전, 데이터 마지막 행 (차트용으로 미리 저장)

# 1) 헤더 강조
fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
for cell in ws[1]:
    cell.font = Font(bold=True, color="FFFFFF")
    cell.fill = fill
    cell.alignment = Alignment(horizontal="center")

# 2) 매출(B열) 천 단위 콤마
for r in range(2, last + 1):
    ws.cell(row=r, column=2).number_format = "#,##0"

# 3) 합계 행
ws.cell(row=last + 1, column=1, value="합계").font = Font(bold=True)
total = ws.cell(row=last + 1, column=2, value=f"=SUM(B2:B{last})")
total.font = Font(bold=True)
total.number_format = "#,##0"

# 4) 막대 차트 (합계 행은 빼고 last까지만!)
chart = BarChart()
chart.title = "월별 매출"
data = Reference(ws, min_col=2, min_row=1, max_row=last)
cats = Reference(ws, min_col=1, min_row=2, max_row=last)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
ws.add_chart(chart, "D2")

# 5) 열 너비 살짝 넓히기
ws.column_dimensions["A"].width = 12
ws.column_dimensions["B"].width = 16

wb.save("월별매출_보고서.xlsx")
print("보고서 완성! 월별매출_보고서.xlsx 열어보세요")

# 실행 결과:
# 보고서 완성! 월별매출_보고서.xlsx 열어보세요

 

last를 맨 위에서 미리 저장해둔 거 보이시죠? 합계 행을 추가하고 나면 ws.max_row가 한 칸 늘어나거든요. 그러니 차트 범위가 흔들리지 않게, 데이터만 있을 때의 마지막 행을 먼저 챙겨두는 게 깔끔해요.

---

자주 만나는 에러: 색이 안 먹어요 / #NAME? 이 떠요

처음에 이 부분에서 많이들 막히시더라고요. 미리 알아두면 덜 당황해요.

색이 안 칠해질 때. 십중팔구 둘 중 하나예요. 색상에 #을 붙였거나("#FF0000" → "FF0000"로), PatternFill에 fill_type="solid"를 빼먹었거나요. 둘 다 에러도 안 나고 조용히 무시돼서 더 헷갈려요.

합계 칸에 #NAME? 이 뜰 때. 엑셀로 열었더니 합계 자리에 #NAME?이 떠 있으면, 수식 함수 이름에 오타가 난 거예요. =SUM을 =SUMM으로 쓴다거나요. 엑셀에 직접 같은 수식을 쳐보면 금방 찾아져요.

AttributeError가 날 때. from openpyxl.styles import ... 나 from openpyxl.chart import ... 를 빼먹은 거예요. 서식은 styles, 차트는 chart에서 가져온다는 것만 기억하세요.

아, 그리고 저장할 때 그 엑셀 파일을 열어둔 채면 PermissionError가 나요. 엑셀 닫고 다시 돌리면 됩니다. 이거 직장인 단골 함정이에요.

---

직접 해보기

가진 엑셀이 없으면 손으로 대충 만들어도 돼요. 월(또는 항목)이랑 숫자, 두 열짜리 표 하나면 충분해요.

1. 위 메인 예제를 그대로 돌려서 헤더 색 + 콤마 + 합계 + 차트가 한 번에 들어가는지 확인해보세요.
2. 헤더 색을 본인 취향으로 바꿔보세요. 초록은 "70AD47" 이에요. (힌트: start_color랑 end_color 둘 다 바꿔야 해요.)

보너스로, BarChart() 자리를 PieChart()로 바꾸면 비율을 보여주는 원형 차트가 돼요. from openpyxl.chart import PieChart 한 줄만 더 추가하면 됩니다.

이렇게 하면 내 컴퓨터 안에서 멋진 엑셀 보고서가 자동으로 만들어져요. 근데 요즘은 팀이 다 같이 구글 시트로 보는 경우가 많잖아요. 다음에는 엑셀 파일을 넘어서, 파이썬으로 구글 시트에 직접 연동해서 클라우드 위의 표를 자동으로 채우는 법을 해볼게요.

---

 

제 글이 도움이 되셨다면 댓글 & 공감 부탁드려요 😀