엑셀로 인보이스나 주문서를 만들어 보신 분들은 공감하실 겁니다.
매번 새 파일을 열어서 표 만들고, 제목 쓰고, 테두리 긋고, 합계 수식 넣는 작업이 생각보다 손이 많이 갑니다. 자동화를 하려고 파이썬을 붙여도, 파일을 처음부터 새로 생성하는 방식이면 결국 디자인을 다시 맞춰야 합니다.
그래서 엑셀 자동화에서 많이 쓰는 방법이 엑셀 양식(템플릿)을 먼저 만들어 두고, 그 빈칸만 채우는 방식입니다. 말 그대로 흰 도화지에 그림을 새로 그리는 게 아니라, 이미 그려진 양식 파일에 데이터만 입력하는 구조입니다.
예를 들어 인보이스 엑셀 파일을 하나 만들어 두고,
10행에는 ‘상호’, ‘품목’, ‘수량’, ‘단가’ 같은 제목을 배치해 둡니다.
회사 로고, 도장 이미지, 테두리, 글꼴 크기, 색상도 미리 다 정리해 둡니다.
그 다음 파이썬에서는 이 파일을 새로 만드는 게 아니라, 기존 template.xlsx 파일을 불러와서 필요한 셀에 값만 써 넣습니다. 이렇게 하면 엑셀 서식, 레이아웃, 계산 수식은 그대로 유지되고 데이터만 바뀝니다.
아래 방식은 크게 보면 네 가지 기능으로 작동되요
- Gmail(네이버)에서 주문 메일을 읽어오는 기능
- 메일 내용을 분석해서 상호, 품목, 수량, 단가를 추출하는 AI 처리
- 숫자만 정리해 주는 정규표현식 처리
- 엑셀 템플릿을 불러와 데이터를 채워 넣는 작업
상세한 절차와 코드를 설명해 드릴게요.
목차
1. 준비물: 양식 파일 (template.xlsx)
먼저 예: E:\파이썬프로그램\인보이스 폴더에 template.xlsx라는 이름의 양식 엑셀 파일을 만드세요.
- 예시: B10 셀에는 ‘상호’, C10 셀에는 ‘품목’, D10 셀에는 ‘수량’, E10 셀에는 ‘단가’가 들어가도록 디자인해 두었다고 가정합니다.
2. 핵심 라이브러리 설치
처음 컴퓨터 세팅을 하시는 거라면, 터미널에 아래 명령어만 딱 한 번 입력하시면 됩니다.
pip install requests openpyxl이 프로그램은 총 4가지 핵심 기능을 쓰는데, 그중 2개는 파이썬을 깔면 이미 들어있고, 나머지 2개만 따로 설치하면 됩니다.
| 라이브러리 이름 | 설치 명령어 | 하는 일 |
| requests | pip install requests | AI(Gemini)와 대화할 때 사용합니다. (인터넷 통신용) |
| openpyxl | pip install openpyxl | 엑셀 파일을 읽고, 쓰고, 양식을 불러올 때 사용합니다. |
| imaplib | (기본 설치됨) | Gmail 서버에 접속해서 메일을 가져올 때 사용합니다. |
| re | (기본 설치됨) | “100개”에서 “개”를 빼고 숫자만 추출할 때 사용합니다. |
3. 엑셀 양식 적용하기
1. 엑셀 양식 만들기
예제 입니다.
- 엑셀을 켭니다.
- 10행에 제목을 적습니다.
- B10 칸에 “상호”
- C10 칸에 “품목”
- D10 칸에 “수량”
- E10 칸에 “단가”
- 이 파일을 E:\파이썬프로그램\인보이스 폴더 안에 template.xlsx라는 이름으로 저장합니다.
- 시작 위치: 엑셀에서 데이터 입력을 시작하고 싶은 ‘행 번호’를 코드의
start_row뒤에 적어주면 됩니. ( 예:코내 엑셀 양식이 12번 줄부터 데이터를 채워야 한다면? 코드 수정:start_row = 12) - 칸 위치: 내 엑셀 양식에서 ‘상호’가 써질 칸이 어떤 알파벳 열(Column)인지 확인하고, 코드에 적힌 알파벳을 똑같이 맞춰주세요.
- 엑셀을 보시면 가로는 숫자(1, 2, 3…)고 세로는 알파벳(A, B, C…)이죠? 파이썬에게 “어느 기둥(열)에 데이터를 넣을지” 주소를 가르쳐주는 과정입니다.
2. 코드에서 이 양식을 어떻게 알아볼까요?
코드에는 아래와 같이 “어느 칸에 어떤 정보를 넣을지” 적어둔 지도가 들어있습니다.
- ws[f”B{current_row}”]: B열에는 AI가 찾은 상호를 넣어라!
- ws[f”C{current_row}”]: C열에는 AI가 찾은 품목을 넣어라!
- ws[f”D{current_row}”]: D열에는 AI가 찾은 수량을 넣어라!
여기서 current_row는 파이썬이 “아, 지금은 10번 줄에 쓰고 있구나”라고 인식하는 숫자입니다. 첫 번째 주문은 10행, 두 번째 주문은 11행… 이렇게 자동으로 늘어납니다.
3. 양식 맞춤형 수정 코드
엑셀 템플릿 AI 인보이스 파이썬 코드PDF 문서 | 외부 저장소
아래 코드에서 엑셀 양식에 맞게 수정 교체하면 됩니다.
import imaplib
import email
from email.header import decode_header
import requests
import json
import os
import re
from openpyxl import load_workbook
# ==========================================================
# 1. 설정 정보 (접속 정보 및 경로)
# ==========================================================
EMAIL_USER = "[email protected]" # 본인 메일
EMAIL_PASS = "zhxn tdnv uzzy asrv" # 앱 비밀번호
API_KEY = "AIzaSyAjE9dMwwdBRkz4quEQAWQch8FQichJELA"
MODEL_NAME = "models/gemini-2.5-flash-lite"
TEMPLATE_PATH = r"E:\파이썬프로그램\인보이스\template.xlsx"
RESULT_PATH = r"E:\파이썬프로그램\인보이스\final_completed_invoice.xlsx"
def get_latest_order_email():
try:
print("1. Gmail 서버 연결 중...")
mail = imaplib.IMAP4_SSL("imap.gmail.com")
mail.login(EMAIL_USER, EMAIL_PASS)
mail.select("INBOX")
status, messages = mail.search(None, 'ALL')
if not messages[0]: return None
email_ids = messages[0].split()
for e_id in reversed(email_ids):
_, data = mail.fetch(e_id, "(RFC822)")
msg = email.message_from_bytes(data[0][1])
subject, encoding = decode_header(msg.get("Subject"))[0]
if isinstance(subject, bytes):
subject = subject.decode(encoding if encoding else "utf-8", errors="ignore")
if "주문" in subject:
print(f"✅ 처리할 메일 발견: {subject}")
if msg.is_multipart():
for part in msg.walk():
if part.get_content_type() == "text/plain":
return part.get_payload(decode=True).decode("utf-8", errors="ignore")
else:
return msg.get_payload(decode=True).decode("utf-8", errors="ignore")
return None
except Exception as e:
print(f"❌ 메일 읽기 오류: {e}")
return None
def process_with_gemini(content):
print("2. AI(Gemini) 분석 요청 중...")
url = f"https://generativelanguage.googleapis.com/v1beta/{MODEL_NAME}:generateContent?key={API_KEY}"
prompt = (
f"추출:상호명,품목명,수량,단가. 결과는 반드시 JSON 배열로만 응답해.\n"
f"형식: [{{'customer': '상호', 'item': '품목', 'qty': '수량', 'price': '단가'}}]\n"
f"본문: {content}"
)
try:
response = requests.post(url, json={"contents": [{"parts": [{"text": prompt}]}]})
return response.json()['candidates'][0]['content']['parts'][0]['text']
except Exception as e:
print(f"❌ AI 오류: {e}")
return None
def save_to_template_excel(ai_text):
print("3. 양식에 데이터 입력 중...")
try:
# JSON 데이터만 쏙 뽑아내는 정교한 필터링
match = re.search(r'\[.*\]', ai_text, re.DOTALL)
if match:
clean_json = match.group().replace("'", "\"")
items = json.loads(clean_json)
else:
print("❌ AI 응답에서 데이터를 찾을 수 없습니다.")
return
if not os.path.exists(TEMPLATE_PATH):
print(f"❌ 오류: {TEMPLATE_PATH} 파일이 없습니다!")
return
wb = load_workbook(TEMPLATE_PATH)
ws = wb.active
# 시작 행 번호 (양식에 맞게 수정)
start_row = 10
for idx, i in enumerate(items):
def clean_num(val):
if not val: return 0
num_str = re.sub(r'[^0-9]', '', str(val))
return int(num_str) if num_str else 0
current_row = start_row + idx
# 엑셀 칸 주소 지정 (B:상호, C:품목, D:수량, E:단가)
ws[f"B{current_row}"] = i.get('customer', '상호미상')
ws[f"C{current_row}"] = i.get('item', '품목미상')
ws[f"D{current_row}"] = clean_num(i.get('qty'))
ws[f"E{current_row}"] = clean_num(i.get('price'))
ws[f"F{current_row}"] = f"=D{current_row}*E{current_row}"
wb.save(RESULT_PATH)
print(f"🎉 모든 작업 성공! 파일 확인: {RESULT_PATH}")
except Exception as e:
print(f"❌ 엑셀 저장 오류: {e}")
if __name__ == "__main__":
content = get_latest_order_email()
if content:
ai_res = process_with_gemini(content)
if ai_res:
save_to_template_excel(ai_res)
else:
print("❌ 처리할 새 주문 메일이 없습니다.")- 디자인 보존: 회사 로고, 도장 이미지, 복잡한 테두리, 미리 설정된 폰트 크기가 그대로 유지됩니다.
- 엑셀 기능 활용: 엑셀 양식 하단에 =SUM(F10:F20) 같은 합계 수식을 미리 걸어두면, 파이썬이 숫자만 채워 넣었을 때 최종 금액이 자동으로 계산되어 보입니다.
- 반복 작업 감소: 매번 파이썬 코드로 선을 긋고 색을 칠할 필요가 없습니다.
가장 중요한 것은 사용하시는 양식의 실제 칸 번호입니다.
- 업체명이 들어갈 칸이 B5인가요? 아니면 C8인가요?
- 위 코드의 ws[f”B{current_row}”] 부분에서 알파벳만 실제 칸에 맞게 고치시면 됩니다.
