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="Japanese"):
    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="Japanese"):
    if text not in cache:
        cache[text] = translate_text(text, target_lang)
    return cache[text]

import json
 
def translate_batch(texts, target_lang="Japanese"):
    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 = "Japanese"
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))