파이썬으로 엑셀 일간 업무보고서 자동 생성하는 완벽 가이드

매일 반복되는 엑셀 업무보고서 작성에 지쳐있다면, 파이썬의 pandas와 openpyxl 라이브러리로 완전 자동화할 수 있어요. CSV나 엑셀 로그 파일을 읽어서 분석하고, 보고서 템플릿에 자동으로 데이터를 채워넣는 방법을 실제 코드와 함께 알아볼게요.

 

필수 라이브러리 설치하기

 

macOS 터미널을 열고 다음 명령어로 필요한 라이브러리를 설치해요.

pip install pandas openpyxl matplotlib schedule

 

pandas는 데이터 분석과 가공을, openpyxl은 엑셀 파일 읽기와 쓰기를 담당해요. matplotlib는 차트 생성용이고, schedule은 자동 실행을 위해 필요해요.

 

반응형

 

업무 로그 데이터 불러오고 분석하기

 

일단 하루치 업무 로그가 담긴 CSV 파일을 pandas로 읽어볼게요. 실무에서는 보통 부서명, 업무ID, 처리시간, 담당자 같은 정보가 들어있죠.

import pandas as pd
import datetime

# CSV 파일 읽기
df = pd.read_csv('daily_log.csv', encoding='utf-8')

# 데이터 구조 확인
print(df.head())
print(f"전체 데이터 건수: {len(df)}")

# 부서별 업무 건수 집계
daily_summary = df.groupby('부서')['업무ID'].count().reset_index()
daily_summary.columns = ['부서', '업무처리건수']

# 추가 분석: 담당자별 처리 건수
person_summary = df.groupby('담당자')['업무ID'].count().reset_index()
person_summary.columns = ['담당자', '처리건수']

# 평균 처리시간 계산 (처리시간 컬럼이 있다면)
if '처리시간' in df.columns:
    avg_time = df.groupby('부서')['처리시간'].mean().reset_index()
    avg_time.columns = ['부서', '평균처리시간(분)']

 

데이터가 엑셀 파일에 있다면 pd.read_excel() 함수를 사용하면 돼요. 여러 시트가 있는 경우 sheet_name 파라미터로 특정 시트를 지정할 수 있어요.

 

분석 결과를 차트로 시각화하기

 

보고서에 차트를 넣으면 한눈에 현황을 파악할 수 있어요. matplotlib로 간단한 막대 그래프를 만들어볼게요.

import matplotlib.pyplot as plt
import matplotlib.font_manager as fm

# macOS 한글 폰트 설정
plt.rcParams['font.family'] = 'AppleGothic'
plt.rcParams['axes.unicode_minus'] = False

# 부서별 업무 건수 차트
plt.figure(figsize=(10, 6))
plt.bar(daily_summary['부서'], daily_summary['업무처리건수'])
plt.xlabel('부서')
plt.ylabel('처리 건수')
plt.title(f'{datetime.date.today()} 부서별 업무 처리 현황')
plt.xticks(rotation=45)
plt.tight_layout()

# 차트를 이미지로 저장
plt.savefig('업무건수_차트.png', dpi=150)
plt.close()

# 담당자별 원형 차트
plt.figure(figsize=(8, 8))
plt.pie(person_summary['처리건수'], labels=person_summary['담당자'], autopct='%1.1f%%')
plt.title('담당자별 업무 처리 비율')
plt.savefig('담당자별_차트.png', dpi=150)
plt.close()

 

엑셀 보고서 템플릿에 데이터 자동 입력하기

 

이제 본격적으로 엑셀 템플릿에 분석 결과를 넣어볼게요. openpyxl을 사용하면 기존 엑셀 파일의 특정 셀에 값을 입력하거나, 서식을 유지한 채로 데이터만 업데이트할 수 있어요.

from openpyxl import load_workbook
from openpyxl.drawing.image import Image
from openpyxl.styles import PatternFill, Font, Alignment

# 기존 템플릿 파일 불러오기
wb = load_workbook('보고서_템플릿.xlsx')
ws = wb.active

# 오늘 날짜 입력 (B2 셀에)
today = datetime.date.today().strftime("%Y년 %m월 %d일")
ws['B2'] = today

# 전체 처리 건수 입력 (D2 셀에)
total_count = len(df)
ws['D2'] = total_count

# 부서별 데이터 테이블에 입력 (5행부터 시작한다고 가정)
start_row = 5
for idx, row in daily_summary.iterrows():
    ws.cell(row=start_row + idx, column=2).value = row['부서']
    ws.cell(row=start_row + idx, column=3).value = row['업무처리건수']
    
    # 셀 서식 적용 (선택사항)
    cell = ws.cell(row=start_row + idx, column=3)
    cell.alignment = Alignment(horizontal='center')
    
    # 처리건수가 100건 이상이면 노란색 배경
    if row['업무처리건수'] >= 100:
        cell.fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")

# 차트 이미지 삽입
img1 = Image('업무건수_차트.png')
img1.width = 400
img1.height = 300
ws.add_image(img1, 'E5')  # E5 셀 위치에 이미지 삽입

# 담당자별 데이터는 다른 시트에 입력
if '담당자별' in wb.sheetnames:
    ws2 = wb['담당자별']
else:
    ws2 = wb.create_sheet('담당자별')

# 헤더 작성
ws2['A1'] = '담당자별 업무 처리 현황'
ws2['A1'].font = Font(size=16, bold=True)
ws2['A3'] = '담당자'
ws2['B3'] = '처리건수'

# 데이터 입력
for idx, row in person_summary.iterrows():
    ws2.cell(row=4 + idx, column=1).value = row['담당자']
    ws2.cell(row=4 + idx, column=2).value = row['처리건수']

# 파일 저장
output_filename = f'일간업무보고_{datetime.date.today().strftime("%Y%m%d")}.xlsx'
wb.save(output_filename)
print(f"보고서 생성 완료: {output_filename}")

 

고급 기능: 조건부 서식과 수식 적용하기

 

 

엑셀의 고급 기능들도 파이썬으로 구현할 수 있어요. 합계, 평균 같은 수식이나 조건부 서식을 추가해볼게요.

from openpyxl.utils import get_column_letter
from openpyxl.formatting.rule import ColorScaleRule

# 합계 수식 추가
last_data_row = start_row + len(daily_summary) - 1
total_row = last_data_row + 2

ws.cell(row=total_row, column=2).value = "합계"
ws.cell(row=total_row, column=2).font = Font(bold=True)

# SUM 수식 입력
sum_formula = f"=SUM(C{start_row}:C{last_data_row})"
ws.cell(row=total_row, column=3).value = sum_formula
ws.cell(row=total_row, column=3).font = Font(bold=True)

# 조건부 서식: 색상 스케일 적용
rule = ColorScaleRule(
    start_type='min', start_color='63BE7B',  # 녹색
    mid_type='percentile', mid_value=50, mid_color='FFEB84',  # 노란색
    end_type='max', end_color='F8696B'  # 빨간색
)

# C열(처리건수)에 조건부 서식 적용
ws.conditional_formatting.add(f'C{start_row}:C{last_data_row}', rule)

# 열 너비 자동 조정
for column in ws.columns:
    max_length = 0
    column_letter = get_column_letter(column[0].column)
    
    for cell in column:
        try:
            if len(str(cell.value)) > max_length:
                max_length = len(str(cell.value))
        except:
            pass
    
    adjusted_width = (max_length + 2) * 1.2
    ws.column_dimensions[column_letter].width = adjusted_width

 

매일 자동 실행 설정하기

 

이제 만든 스크립트를 매일 자동으로 실행하도록 설정해볼게요. macOS에서는 cron을 사용하거나, 파이썬의 schedule 라이브러리를 활용할 수 있어요.

import schedule
import time
import os

def generate_daily_report():
    """일간 보고서 생성 함수"""
    try:
        # 위에서 작성한 모든 코드를 함수로 묶기
        print(f"보고서 생성 시작: {datetime.datetime.now()}")
        
        # 데이터 읽기
        df = pd.read_csv('daily_log.csv', encoding='utf-8')
        
        # 분석 실행
        daily_summary = df.groupby('부서')['업무ID'].count().reset_index()
        daily_summary.columns = ['부서', '업무처리건수']
        
        # 엑셀 생성 (위의 코드 재사용)
        # ... (엑셀 생성 코드)
        
        print(f"보고서 생성 완료: {datetime.datetime.now()}")
        
        # 생성 완료 알림 (macOS 알림 센터 활용)
        os.system(f'osascript -e \'display notification "일간 업무보고서가 생성되었습니다." with title "보고서 자동 생성"\'')
        
    except Exception as e:
        print(f"오류 발생: {e}")
        os.system(f'osascript -e \'display notification "보고서 생성 중 오류가 발생했습니다: {e}" with title "오류 알림"\'')

# 매일 오전 8시에 실행
schedule.every().day.at("08:00").do(generate_daily_report)

# 테스트용: 1분마다 실행
# schedule.every(1).minutes.do(generate_daily_report)

print("자동 보고서 생성 스케줄러가 시작되었습니다.")
print("종료하려면 Ctrl+C를 누르세요.")

while True:
    schedule.run_pending()
    time.sleep(60)  # 60초마다 스케줄 확인

 

macOS에서 백그라운드 실행 설정

 

스크립트를 백그라운드에서 계속 실행하려면 launchd를 사용해요. plist 파일을 만들어서 설정할 수 있어요.

http://www.apple.com/DTDs/PropertyList-1.0.dtd">


    Label
    com.user.daily-report
    ProgramArguments
    
        /usr/local/bin/python3
        /Users/사용자명/Documents/daily_report.py
    
    StartCalendarInterval
    
        Hour
        8
        Minute
        0
    
    StandardOutPath
    /Users/사용자명/Documents/report_log.txt
    StandardErrorPath
    /Users/사용자명/Documents/report_error.txt

 

plist 파일을 생성한 후 터미널에서 다음 명령어로 등록해요.

launchctl load ~/Library/LaunchAgents/com.user.daily-report.plist

 

실전 활용 팁과 주의사항

 

데이터가 없거나 형식이 맞지 않을 때를 대비한 예외 처리는 필수예요. 특히 날짜별로 데이터가 없을 수도 있으니 체크하는 로직을 추가하면 좋아요.

# 데이터 유효성 검사
if df.empty:
    print("오늘의 데이터가 없습니다.")
    # 빈 보고서 생성 또는 알림 발송
    return

# 필수 컬럼 확인
required_columns = ['부서', '업무ID', '담당자']
missing_columns = [col for col in required_columns if col not in df.columns]
if missing_columns:
    print(f"필수 컬럼이 없습니다: {missing_columns}")
    return

 

템플릿 파일이 변경되거나 이동했을 때를 대비해서 파일 경로를 설정 파일로 관리하는 것도 좋은 방법이에요. JSON이나 YAML 파일로 설정을 분리하면 코드 수정 없이 경로나 설정을 변경할 수 있어요.

 

메모리 사용량이 걱정된다면 대용량 파일은 청크 단위로 읽어서 처리하는 방법도 있어요. pandas의 chunksize 파라미터를 활용하면 돼요.

 

 

2025.07.23 - [생산성] - 구글 캘린더 일정 생기면 슬랙으로 자동 알림 받는 방법

구글 캘린더 일정 생기면 슬랙으로 자동 알림 받는 방법