跳至正文

엑셀 자동화

Python의 openpyxl 라이브러리를 사용하면 Excel 파일을 프로그래밍 방식으로 생성하고 편집할 수 있습니다. 반복적인 데이터 입력, 보고서 생성, 데이터 분석 등을 자동화해보겠습니다.

설치하기​

# openpyxl 설치
pip install openpyxl

# 차트 기능까지 사용하려면
pip install openpyxl[charts]

기본 작업​

워크북 생성 및 저장​

from openpyxl import Workbook

# 새 워크북 생성
wb = Workbook()

# 활성 시트 가져오기
ws = wb.active

# 시트 이름 변경
ws.title = "매출 데이터"

# 셀에 값 쓰기
ws['A1'] = "안녕하세요"
ws['B1'] = 42
ws['C1'] = 3.14

# 파일 저장
wb.save('example.xlsx')

기존 파일 읽기​

from openpyxl import load_workbook

# 엑셀 파일 열기
wb = load_workbook('example.xlsx')

# 시트 이름 목록
print(wb.sheetnames)  # ['매출 데이터', 'Sheet2']

# 특정 시트 선택
ws = wb['매출 데이터']

# 또는 활성 시트
ws = wb.active

# 셀 값 읽기
value = ws['A1'].value
print(value)

셀 다루기​

셀 접근 방법​

# 방법 1: 셀 좌표 사용
ws['A1'] = "이름"

# 방법 2: row, column 인덱스 사용 (1부터 시작)
ws.cell(row=1, column=1).value = "이름"
ws.cell(row=1, column=2).value = "나이"

# 셀 값 읽기
name = ws['A1'].value
age = ws.cell(row=1, column=2).value

여러 셀에 데이터 쓰기​

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

# 행 단위로 데이터 추가
data = [
    ["이름", "나이", "직업"],
    ["김철수", 30, "개발자"],
    ["이영희", 28, "디자이너"],
    ["박민수", 35, "기획자"]
]

for row in data:
    ws.append(row)

wb.save('직원명단.xlsx')

범위로 셀 읽기​

# 특정 범위 읽기
for row in ws['A1:C4']:
    for cell in row:
        print(cell.value, end='\t')
    print()

# 모든 행 순회
for row in ws.iter_rows(min_row=2, values_only=True):
    print(row)

# 모든 열 순회
for col in ws.iter_cols(min_col=1, max_col=3, values_only=True):
    print(col)

# 특정 범위의 값만 가져오기
for row in ws.iter_rows(min_row=2, max_row=5, min_col=1, max_col=3, values_only=True):
    name, age, job = row
    print(f"{name}: {age}세, {job}")

셀 스타일링​

폰트 스타일​

from openpyxl.styles import Font

# 굵게, 크기, 색상
ws['A1'].font = Font(
    name='맑은 고딕',
    size=14,
    bold=True,
    italic=False,
    color='FF0000'  # 빨간색 (RGB 헥스)
)

셀 배경색​

from openpyxl.styles import PatternFill

# 배경색 채우기
ws['A1'].fill = PatternFill(
    start_color='FFFF00',  # 노란색
    end_color='FFFF00',
    fill_type='solid'
)

테두리​

from openpyxl.styles import Border, Side

# 테두리 스타일
thin_border = Border(
    left=Side(style='thin'),
    right=Side(style='thin'),
    top=Side(style='thin'),
    bottom=Side(style='thin')
)

ws['A1'].border = thin_border

정렬​

from openpyxl.styles import Alignment

# 가운데 정렬
ws['A1'].alignment = Alignment(
    horizontal='center',
    vertical='center',
    wrap_text=True  # 텍스트 줄바꿈
)

숫자 형식​

# 통화 형식
ws['B2'].number_format = '#,##0원'

# 백분율
ws['C2'].number_format = '0.00%'

# 날짜
ws['D2'].number_format = 'yyyy-mm-dd'

# 소수점
ws['E2'].number_format = '0.00'

수식 사용하기​

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

# 데이터 입력
ws['A1'] = "상품"
ws['B1'] = "단가"
ws['C1'] = "수량"
ws['D1'] = "합계"

ws['A2'] = "사과"
ws['B2'] = 1000
ws['C2'] = 5

# 수식 입력 (B2 * C2)
ws['D2'] = "=B2*C2"

# SUM 함수
ws['A10'] = "총합"
ws['D10'] = "=SUM(D2:D9)"

# 평균
ws['A11'] = "평균"
ws['D11'] = "=AVERAGE(D2:D9)"

# IF 함수
ws['E2'] = "=IF(D2>5000, '고가', '저가')"

wb.save('수식예제.xlsx')

차트 만들기​

막대 차트​

from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference

wb = Workbook()
ws = wb.active

# 데이터 입력
data = [
    ["월", "매출"],
    ["1월", 1000],
    ["2월", 1500],
    ["3월", 1200],
    ["4월", 1800],
]

for row in data:
    ws.append(row)

# 차트 생성
chart = BarChart()
chart.title = "월별 매출"
chart.x_axis.title = "월"
chart.y_axis.title = "매출 (만원)"

# 데이터 범위 설정
data_ref = Reference(ws, min_col=2, min_row=1, max_row=5)
categories = Reference(ws, min_col=1, min_row=2, max_row=5)

chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(categories)

# 차트 추가
ws.add_chart(chart, "D2")

wb.save('차트예제.xlsx')

선 차트​

from openpyxl.chart import LineChart, Reference

# 선 차트 생성
chart = LineChart()
chart.title = "주간 방문자 수"
chart.style = 10
chart.y_axis.title = '방문자'
chart.x_axis.title = '요일'

data_ref = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=8)
chart.add_data(data_ref, titles_from_data=True)

categories = Reference(ws, min_col=1, min_row=2, max_row=8)
chart.set_categories(categories)

ws.add_chart(chart, "E2")

원 차트​

from openpyxl.chart import PieChart, Reference

chart = PieChart()
chart.title = "판매 비율"

data_ref = Reference(ws, min_col=2, min_row=1, max_row=5)
labels = Reference(ws, min_col=1, min_row=2, max_row=5)

chart.add_data(data_ref, titles_from_data=True)
chart.set_categories(labels)

ws.add_chart(chart, "D2")

실전 예제​

1. 자동 매출 보고서 생성​

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.chart import BarChart, Reference
from datetime import datetime

def create_sales_report(sales_data, filename):
    """매출 보고서 자동 생성"""

    wb = Workbook()
    ws = wb.active
    ws.title = "매출 현황"

    # 제목
    ws['A1'] = "월간 매출 보고서"
    ws['A1'].font = Font(size=16, bold=True)
    ws['A1'].alignment = Alignment(horizontal='center')
    ws.merge_cells('A1:E1')

    # 작성일
    ws['A2'] = f"작성일: {datetime.now().strftime('%Y-%m-%d')}"

    # 헤더
    headers = ["제품명", "판매수량", "단가", "매출액", "비고"]
    ws.append([])  # 빈 행
    ws.append(headers)

    # 헤더 스타일
    header_fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')
    header_font = Font(color='FFFFFF', bold=True)

    for col in range(1, 6):
        cell = ws.cell(row=4, column=col)
        cell.fill = header_fill
        cell.font = header_font
        cell.alignment = Alignment(horizontal='center')

    # 데이터 입력
    total_sales = 0
    for item in sales_data:
        product, quantity, price = item
        sales = quantity * price
        total_sales += sales

        remark = "목표달성" if sales >= 1000000 else "노력필요"

        ws.append([product, quantity, price, sales, remark])

    # 데이터 스타일
    thin_border = Border(
        left=Side(style='thin'),
        right=Side(style='thin'),
        top=Side(style='thin'),
        bottom=Side(style='thin')
    )

    for row in ws.iter_rows(min_row=4, max_row=ws.max_row, min_col=1, max_col=5):
        for cell in row:
            cell.border = thin_border
            cell.alignment = Alignment(horizontal='center')

    # 숫자 형식
    for row in range(5, ws.max_row + 1):
        ws.cell(row=row, column=2).number_format = '#,##0'  # 수량
        ws.cell(row=row, column=3).number_format = '#,##0원'  # 단가
        ws.cell(row=row, column=4).number_format = '#,##0원'  # 매출액

    # 합계 행
    total_row = ws.max_row + 1
    ws.cell(row=total_row, column=1).value = "총 매출"
    ws.cell(row=total_row, column=4).value = f"=SUM(D5:D{ws.max_row})"
    ws.cell(row=total_row, column=1).font = Font(bold=True)
    ws.cell(row=total_row, column=4).font = Font(bold=True)
    ws.cell(row=total_row, column=4).fill = PatternFill(
        start_color='FFC000', end_color='FFC000', fill_type='solid'
    )

    # 차트 추가
    chart = BarChart()
    chart.title = "제품별 매출 현황"
    chart.y_axis.title = '매출액 (원)'

    data_ref = Reference(ws, min_col=4, min_row=4, max_row=ws.max_row - 1)
    categories = Reference(ws, min_col=1, min_row=5, max_row=ws.max_row - 1)

    chart.add_data(data_ref, titles_from_data=True)
    chart.set_categories(categories)

    ws.add_chart(chart, "G4")

    # 열 너비 조정
    ws.column_dimensions['A'].width = 15
    ws.column_dimensions['B'].width = 12
    ws.column_dimensions['C'].width = 12
    ws.column_dimensions['D'].width = 15
    ws.column_dimensions['E'].width = 12

    wb.save(filename)
    print(f"보고서 생성 완료: {filename}")

# 사용 예제
sales_data = [
    ("노트북", 15, 1200000),
    ("마우스", 50, 25000),
    ("키보드", 30, 80000),
    ("모니터", 20, 350000),
    ("헤드셋", 25, 120000)
]

create_sales_report(sales_data, "매출보고서.xlsx")

2. 여러 엑셀 파일 통합​

from openpyxl import Workbook, load_workbook
import os

def merge_excel_files(folder_path, output_file):
    """여러 엑셀 파일을 하나로 통합"""

    wb_output = Workbook()
    ws_output = wb_output.active
    ws_output.title = "통합 데이터"

    # 헤더를 한 번만 추가하기 위한 플래그
    header_written = False

    # 폴더 내 모든 엑셀 파일 처리
    for filename in os.listdir(folder_path):
        if not filename.endswith(('.xlsx', '.xls')):
            continue

        file_path = os.path.join(folder_path, filename)
        print(f"처리 중: {filename}")

        try:
            wb = load_workbook(file_path)
            ws = wb.active

            # 데이터 복사
            for i, row in enumerate(ws.iter_rows(values_only=True), 1):
                # 첫 번째 파일의 헤더만 포함
                if i == 1:
                    if not header_written:
                        ws_output.append(row)
                        header_written = True
                else:
                    ws_output.append(row)

        except Exception as e:
            print(f"오류 ({filename}): {e}")

    wb_output.save(output_file)
    print(f"\n통합 완료: {output_file}")
    print(f"총 행 수: {ws_output.max_row}")

# 사용 예제
merge_excel_files('/Users/username/분기별데이터', '통합데이터.xlsx')

3. 송장 자동 생성​

from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, Border, Side
from datetime import datetime

def create_invoice(invoice_data, filename):
    """송장 자동 생성"""

    wb = Workbook()
    ws = wb.active

    # 회사 정보
    ws['A1'] = "주식회사 테크솔루션"
    ws['A1'].font = Font(size=18, bold=True)

    ws['A2'] = "서울시 강남구 테헤란로 123"
    ws['A3'] = "Tel: 02-1234-5678"
    ws['A4'] = "사업자번호: 123-45-67890"

    # 송장 제목
    ws['A6'] = "거 래 명 세 서"
    ws['A6'].font = Font(size=16, bold=True)
    ws['A6'].alignment = Alignment(horizontal='center')
    ws.merge_cells('A6:F6')

    # 고객 정보
    ws['A8'] = "받는 분:"
    ws['B8'] = invoice_data['customer_name']
    ws['A9'] = "날짜:"
    ws['B9'] = datetime.now().strftime('%Y년 %m월 %d일')
    ws['A10'] = "송장번호:"
    ws['B10'] = invoice_data['invoice_number']

    # 테이블 헤더
    headers = ["번호", "품목", "수량", "단가", "금액", "비고"]
    ws.append([])  # 빈 행
    ws.append(headers)

    # 헤더 스타일
    header_row = 12
    for col in range(1, 7):
        cell = ws.cell(row=header_row, column=col)
        cell.font = Font(bold=True)
        cell.alignment = Alignment(horizontal='center')

    # 품목 데이터
    total = 0
    for i, item in enumerate(invoice_data['items'], 1):
        product, quantity, price, note = item
        amount = quantity * price
        total += amount

        ws.append([i, product, quantity, price, amount, note])

        # 숫자 형식
        row = ws.max_row
        ws.cell(row=row, column=3).number_format = '#,##0'
        ws.cell(row=row, column=4).number_format = '#,##0원'
        ws.cell(row=row, column=5).number_format = '#,##0원'

    # 테두리
    thin_border = Border(
        left=Side(style='thin'),
        right=Side(style='thin'),
        top=Side(style='thin'),
        bottom=Side(style='thin')
    )

    for row in ws.iter_rows(min_row=12, max_row=ws.max_row, min_col=1, max_col=6):
        for cell in row:
            cell.border = thin_border
            cell.alignment = Alignment(horizontal='center')

    # 합계
    ws.append([])
    total_row = ws.max_row + 1
    ws.cell(row=total_row, column=4).value = "합계"
    ws.cell(row=total_row, column=5).value = total
    ws.cell(row=total_row, column=4).font = Font(bold=True, size=12)
    ws.cell(row=total_row, column=5).font = Font(bold=True, size=12)
    ws.cell(row=total_row, column=5).number_format = '#,##0원'

    # VAT (10%)
    vat_row = total_row + 1
    vat = total * 0.1
    ws.cell(row=vat_row, column=4).value = "VAT (10%)"
    ws.cell(row=vat_row, column=5).value = vat
    ws.cell(row=vat_row, column=5).number_format = '#,##0원'

    # 총액
    grand_total_row = vat_row + 1
    ws.cell(row=grand_total_row, column=4).value = "총액"
    ws.cell(row=grand_total_row, column=5).value = total + vat
    ws.cell(row=grand_total_row, column=4).font = Font(bold=True, size=14)
    ws.cell(row=grand_total_row, column=5).font = Font(bold=True, size=14)
    ws.cell(row=grand_total_row, column=5).number_format = '#,##0원'

    # 열 너비
    ws.column_dimensions['A'].width = 8
    ws.column_dimensions['B'].width = 20
    ws.column_dimensions['C'].width = 10
    ws.column_dimensions['D'].width = 12
    ws.column_dimensions['E'].width = 15
    ws.column_dimensions['F'].width = 15

    wb.save(filename)
    print(f"송장 생성 완료: {filename}")

# 사용 예제
invoice_data = {
    'customer_name': '(주)글로벌테크',
    'invoice_number': 'INV-2024-001',
    'items': [
        ("웹 사이트 개발", 1, 5000000, "메인 프로젝트"),
        ("유지보수 (1개월)", 1, 500000, "월간"),
        ("도메인 등록", 1, 15000, "연간"),
        ("호스팅", 12, 30000, "월간")
    ]
}

create_invoice(invoice_data, "송장_2024_001.xlsx")

4. 데이터 분석 및 피벗 테이블​

from openpyxl import Workbook
from collections import defaultdict

def create_sales_analysis(raw_data, filename):
    """매출 데이터 분석 및 요약"""

    wb = Workbook()

    # 원본 데이터 시트
    ws_raw = wb.active
    ws_raw.title = "원본 데이터"

    # 헤더
    headers = ["날짜", "제품", "카테고리", "수량", "단가", "매출"]
    ws_raw.append(headers)

    # 데이터 입력
    for row in raw_data:
        ws_raw.append(row)

    # 카테고리별 집계
    category_sales = defaultdict(int)
    product_sales = defaultdict(int)

    for row in raw_data:
        date, product, category, quantity, price, sales = row
        category_sales[category] += sales
        product_sales[product] += sales

    # 카테고리별 요약 시트
    ws_category = wb.create_sheet("카테고리별 매출")
    ws_category.append(["카테고리", "매출액"])

    for category, sales in sorted(category_sales.items(), key=lambda x: x[1], reverse=True):
        ws_category.append([category, sales])

    # 제품별 요약 시트
    ws_product = wb.create_sheet("제품별 매출")
    ws_product.append(["제품", "매출액"])

    for product, sales in sorted(product_sales.items(), key=lambda x: x[1], reverse=True):
        ws_product.append([product, sales])

    # 스타일 적용 (모든 시트)
    for ws in wb.worksheets:
        # 헤더 스타일
        for cell in ws[1]:
            cell.font = Font(bold=True)

        # 숫자 형식
        if ws.max_row > 1:
            for row in range(2, ws.max_row + 1):
                if ws.max_column >= 2:
                    ws.cell(row=row, column=ws.max_column).number_format = '#,##0원'

    wb.save(filename)
    print(f"분석 완료: {filename}")

# 사용 예제
sales_data = [
    ["2024-01-01", "노트북", "전자제품", 2, 1200000, 2400000],
    ["2024-01-02", "마우스", "전자제품", 5, 25000, 125000],
    ["2024-01-03", "책상", "가구", 3, 150000, 450000],
    ["2024-01-04", "의자", "가구", 4, 80000, 320000],
    ["2024-01-05", "키보드", "전자제품", 3, 80000, 240000],
]

create_sales_analysis(sales_data, "매출분석.xlsx")

5. CSV to Excel 변환기​

import csv
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill

def csv_to_excel(csv_file, excel_file):
    """CSV 파일을 스타일이 적용된 Excel로 변환"""

    wb = Workbook()
    ws = wb.active

    # CSV 읽기
    with open(csv_file, 'r', encoding='utf-8') as f:
        reader = csv.reader(f)

        for i, row in enumerate(reader, 1):
            ws.append(row)

            # 첫 행 스타일 (헤더)
            if i == 1:
                for cell in ws[i]:
                    cell.font = Font(bold=True, color='FFFFFF')
                    cell.fill = PatternFill(
                        start_color='4472C4',
                        end_color='4472C4',
                        fill_type='solid'
                    )

    # 열 너비 자동 조정
    for column in ws.columns:
        max_length = 0
        column_letter = column[0].column_letter

        for cell in column:
            if cell.value:
                max_length = max(max_length, len(str(cell.value)))

        adjusted_width = min(max_length + 2, 50)
        ws.column_dimensions[column_letter].width = adjusted_width

    wb.save(excel_file)
    print(f"변환 완료: {csv_file} -> {excel_file}")

# 사용 예제
csv_to_excel('data.csv', 'data.xlsx')

자주 묻는 질문​

Q1. .xls 파일도 읽을 수 있나요?​

A: openpyxl은 .xlsx 전용입니다. .xls 파일은 xlrd 라이브러리를 사용하세요.

# .xls 파일 읽기
import xlrd

workbook = xlrd.open_workbook('old_file.xls')
sheet = workbook.sheet_by_index(0)

# 셀 값 읽기
value = sheet.cell_value(0, 0)

Q2. 대용량 엑셀 파일을 어떻게 처리하나요?​

A: read_only=True와 write_only=True 모드를 사용하세요.

# 읽기 전용 모드 (메모리 효율적)
wb = load_workbook('large_file.xlsx', read_only=True)

# 쓰기 전용 모드
wb = Workbook(write_only=True)
ws = wb.create_sheet()

for row in data:
    ws.append(row)

wb.save('output.xlsx')

Q3. 기존 엑셀 파일을 수정하면 서식이 사라져요.​

A: openpyxl은 일부 고급 서식을 보존하지 못할 수 있습니다. 중요한 서식은 백업하고 작업하세요.

# 파일 복사본 만들기
import shutil
shutil.copy('original.xlsx', 'backup.xlsx')

# 복사본 수정
wb = load_workbook('original.xlsx')
# ... 작업 ...
wb.save('original.xlsx')

Q4. 여러 시트를 한 번에 작업할 수 있나요?​

A: 네, 반복문으로 모든 시트를 처리할 수 있습니다.

wb = load_workbook('workbook.xlsx')

for sheet_name in wb.sheetnames:
    ws = wb[sheet_name]
    print(f"처리 중: {sheet_name}")

    # 각 시트 작업
    for row in ws.iter_rows(values_only=True):
        print(row)

Q5. pandas와 openpyxl 중 어떤 것을 사용해야 하나요?​

A: 데이터 분석이 주 목적이면 pandas, 서식과 차트가 중요하면 openpyxl을 사용하세요.

# pandas로 Excel 읽기/쓰기
import pandas as pd

df = pd.read_excel('data.xlsx')
df.to_excel('output.xlsx', index=False)

# openpyxl로 서식 적용
from openpyxl import load_workbook
from openpyxl.styles import Font

wb = load_workbook('output.xlsx')
ws = wb.active
ws['A1'].font = Font(bold=True)
wb.save('output.xlsx')

다음 단계​

엑셀 자동화를 배웠다면, 다음 주제로 넘어가세요:

  • 웹 자동화: Selenium으로 브라우저 제어하기
  • 작업 스케줄링: 정기적인 보고서 자동 생성하기
  • 데이터 시각화: matplotlib, seaborn으로 그래프 만들기

엑셀 자동화는 업무 생산성을 크게 향상시킬 수 있는 실용적인 기술입니다. 실제 업무에 적용해보세요!