用 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="Chinese"):
    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" = formula
    )

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

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