Python과 ChatGPT API로 Excel을 번역하는 방법【2026년판・작동하는 코드 포함】

export OPENAI_API_KEY="your_api_key_here"

$env:OPENAI_API_KEY="your_api_key_here"

pip install openpyxl

from openpyxl import load_workbook
 
workbook = load_workbook(filename="sample.xlsx")

sheet = workbook.active          # 활성 시트
another_sheet = workbook["Sheet2"]  # 이름으로 지정한 시트

cell_value = sheet["A1"].value   # 읽기
sheet["B1"] = "Hello, Excel!"    # 쓰기

for row in sheet.iter_rows(values_only=True):
    for value in row:
        print(value)

pip install openai

import os
from openai import OpenAI
 
# 키는 환경 변수에서 읽어 옵니다 — 스크립트에 직접 적어 넣지 마세요.
client = OpenAI(api_key=os.environ["OPENAI_API_KEY"])
 
def translate_text(text, target_lang="Korean"):
    response = client.chat.completions.create(
        model="gpt-4o-mini",  # 빠르고 저렴함. 최고 품질이 필요하면 "gpt-4o" 사용
        messages=[
            {
                "role": "system",
                "content": (
                    f"You are a professional translator. Translate the user's text "
                    f"into {target_lang}. Return only the translation, with no extra commentary."
                ),
            },
            {"role": "user", "content": text},
        ],
    )
    return response.choices[0].message.content.strip()

from openpyxl import load_workbook
 
workbook = load_workbook(filename="your_file.xlsx")
 
for sheet in workbook.worksheets:
    for row in sheet.iter_rows():
        for cell in row:
            if isinstance(cell.value, str):
                cell.value = translate_text(cell.value)
 
workbook.save("your_translated_file.xlsx")

def is_translatable(cell):
    # 빈 셀, 숫자, 수식 셀은 건너뜀.
    value = cell.value
    return (
        isinstance(value, str)
        and value.strip() != ""
        and cell.data_type != "f"   # "f" = 수식
    )

cache = {}
 
def translate_cached(text, target_lang="Korean"):
    if text not in cache:
        cache[text] = translate_text(text, target_lang)
    return cache[text]

import json
 
def translate_batch(texts, target_lang="Korean"):
    numbered = {str(i): t for i, t in enumerate(texts)}
    response = client.chat.completions.create(
        model="gpt-4o-mini",
        messages=[
            {
                "role": "system",
                "content": (
                    f"You are a professional translator. Translate each value in the "
                    f"JSON object into {target_lang}. Keep the same keys. "
                    f"Return only a JSON object."
                ),
            },
            {"role": "user", "content": json.dumps(numbered, ensure_ascii=False)},
        ],
        response_format={"type": "json_object"},
    )
    result = json.loads(response.choices[0].message.content)
    return [result[str(i)] for i in range(len(texts))]

import time
 
def with_retry(func, *args, retries=5, **kwargs):
    for attempt in range(retries):
        try:
            return func(*args, **kwargs)
        except Exception as e:
            wait = 2 ** attempt
            print(f"Error: {e}. Retrying in {wait}s...")
            time.sleep(wait)
    raise RuntimeError("Translation failed after several retries.")

pip install defusedxml

import json
import os
import time
from openai import OpenAI
from openpyxl import load_workbook
 
client = OpenAI(api_key=os.environ["OPENAI_API_KEY"])
 
TARGET_LANG = "Korean"
MODEL = "gpt-4o-mini"   # 더 높은 품질이 필요하면 "gpt-4o"
BATCH_SIZE = 40
 
 
def is_translatable(cell):
    value = cell.value
    return (
        isinstance(value, str)
        and value.strip() != ""
        and cell.data_type != "f"   # "f" = 수식
    )
 
 
def translate_batch(texts, target_lang=TARGET_LANG, retries=5):
    numbered = {str(i): t for i, t in enumerate(texts)}
    system = (
        f"You are a professional translator. Translate each value in the JSON object "
        f"into {target_lang}. Keep the same keys. Return only a JSON object."
    )
    for attempt in range(retries):
        try:
            response = client.chat.completions.create(
                model=MODEL,
                messages=[
                    {"role": "system", "content": system},
                    {"role": "user", "content": json.dumps(numbered, ensure_ascii=False)},
                ],
                response_format={"type": "json_object"},
            )
            result = json.loads(response.choices[0].message.content)
            return [result[str(i)] for i in range(len(texts))]
        except Exception as e:
            wait = 2 ** attempt
            print(f"Error: {e}. Retrying in {wait}s...")
            time.sleep(wait)
    raise RuntimeError("Translation failed after several retries.")
 
 
# 1. 워크북 불러오기
workbook = load_workbook(filename="your_file.xlsx")
 
# 2. 번역 대상이 되는 고유한 문자열 모두 모으기
unique_texts = set()
for sheet in workbook.worksheets:
    for row in sheet.iter_rows():
        for cell in row:
            if is_translatable(cell):
                unique_texts.add(cell.value)
unique_texts = list(unique_texts)
 
# 3. 배치로 번역하고 {원문: 번역문} 캐시 만들기
cache = {}
for start in range(0, len(unique_texts), BATCH_SIZE):
    chunk = unique_texts[start:start + BATCH_SIZE]
    cache.update(dict(zip(chunk, translate_batch(chunk))))
 
# 4. 수식과 스타일은 건드리지 않고 번역문만 다시 써넣기
for sheet in workbook.worksheets:
    for row in sheet.iter_rows():
        for cell in row:
            if is_translatable(cell):
                cell.value = cache[cell.value]
 
# 5. 새 파일로 저장
workbook.save("your_translated_file.xlsx")
print("Done!")

from openai import RateLimitError
 
def translate_batch(texts, target_lang=TARGET_LANG, retries=5):
    numbered = {str(i): t for i, t in enumerate(texts)}
    system = (
        f"You are a professional translator. Translate each value in the JSON object "
        f"into {target_lang}. Keep the same keys. Return only a JSON object."
    )
    for attempt in range(retries):
        try:
            response = client.chat.completions.create(
                model=MODEL,
                messages=[
                    {"role": "system", "content": system},
                    {"role": "user", "content": json.dumps(numbered, ensure_ascii=False)},
                ],
                response_format={"type": "json_object"},
            )
            result = json.loads(response.choices[0].message.content)
            missing = [str(i) for i in range(len(texts)) if str(i) not in result]
            if missing:
                raise ValueError(f"Missing translations for keys: {missing}")
            return [result[str(i)] for i in range(len(texts))]
        except RateLimitError:
            wait = 2 ** attempt
            print(f"Rate limited. Retrying in {wait}s...")
            time.sleep(wait)
    raise RuntimeError("Translation failed after several retries.")

# 기존: unique_texts = list(unique_texts)  (set() 사용)
unique_texts = list(dict.fromkeys(collected_texts))