图片文字识别翻译 · 中英对照表

pip install rapidocr-onnxruntime pillow

from openpyxl import load_workbook

def load_terminology_library(file_path):
    """加载术语库; 文件不存在返回空字典(稍后创建), 表名做模糊匹配"""
    import os
    if not os.path.exists(file_path):
        print(f"[WARN] 术语库不存在({file_path}), 将创建新术语库")
        return {}
    wb = load_workbook(file_path)
    # 表名模糊匹配: 优先"主术语表", 否则取含"术语"的第一个表
    sheet = None
    for cand in wb.sheetnames:
        if cand == '主术语表':
            sheet = wb[cand]; break
    if sheet is None:
        for cand in wb.sheetnames:
            if '术语' in cand or 'terminology' in cand.lower():
                sheet = wb[cand]; break
    if sheet is None:
        print(f"[WARN] 未找到术语表(现有: {wb.sheetnames}), 返回空术语库")
        return {}
    terminology = {}
    for row in sheet.iter_rows(min_row=2, values_only=True):
        if row[1] and row[2]:
            terminology[row[1]] = {
                'translation': row[2],
                'category': row[3] if len(row) > 3 else '',
                'notes': row[4] if len(row) > 4 else ''
            }
    return terminology

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment

# 术语库路径(可用环境变量 TRANSLATION_GLOSSARY 覆盖; 本技能无独立 CLI, --glossary 仅是向 AI 传达路径的约定)
import os
TERMINOLOGY_PATH = os.environ.get('TRANSLATION_GLOSSARY', './翻译术语库.xlsx')
terminology = load_terminology_library(TERMINOLOGY_PATH)

wb = Workbook()
sheet = wb.active
sheet.title = '翻译'

# 表头
headers = ['序号', '位置/区域', '中文', 'English']
for col, header in enumerate(headers, 1):
    cell = sheet.cell(row=1, column=col, value=header)
    cell.font = Font(bold=True)
    cell.fill = PatternFill('solid', start_color='FFFF00')
    cell.alignment = Alignment(horizontal='center')

# 翻译数据
data = [
    [1, '顶部导航', '主页', 'Home'],
    [2, '顶部导航', '搜索', 'Search'],
]

inconsistencies = []
glossary_loaded = bool(terminology)
for row_idx, row_data in enumerate(data, 2):
    for col_idx, value in enumerate(row_data, 1):
        sheet.cell(row=row_idx, column=col_idx, value=value)
    
    chinese_text = row_data[2]
    english_text = row_data[3]
    # 一致性检查仅在术语库已加载时执行; 未加载不得报告"通过"
    if glossary_loaded:
        for term_cn, term_data in terminology.items():
            if term_cn in chinese_text:
                if term_data['translation'].lower() not in english_text.lower():
                    sheet.cell(row=row_idx, column=4).fill = PatternFill('solid', start_color='FFCC99')
                    inconsistencies.append((row_idx, term_cn))
if not glossary_loaded:
    print("[INFO] 术语库未加载, 跳过一致性检查 —— 输出须写明'术语库未加载, 已跳过一致性检查', 严禁写'通过'")
else:
    print(f"[OK] 一致性检查完成: {'发现 %d 处不一致' % len(inconsistencies) if inconsistencies else '全部一致'}")

# 列宽
sheet.column_dimensions['A'].width = 8
sheet.column_dimensions['B'].width = 18
sheet.column_dimensions['C'].width = 45
sheet.column_dimensions['D'].width = 55

wb.save('翻译对照表.xlsx')

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment

import os
TERMINOLOGY_PATH = os.environ.get('TRANSLATION_GLOSSARY', './翻译术语库.xlsx')
terminology = load_terminology_library(TERMINOLOGY_PATH)

wb = Workbook()
wb.remove(wb.active)

images_data = {
    '图片A': [[1, '区域', '中文', 'English']],
    '图片B': [[1, '区域', '中文', 'English']],
}

import re as _re

def _clean_sheet_name(name, used, max_len=31):
    """清理 Excel 工作表名：用正则移除全部非法字符 [:\/*?"<>|]，截断到 31 字符，
    并对截断/重名追加 _2/_3 后缀去重，确保 create_sheet 不抛错。"""
    cleaned = _re.sub(r'[:\/*?"<>|]', '_', str(name)).strip().strip('_')
    if not cleaned:
        cleaned = 'Sheet'
    base = cleaned[:max_len]
    candidate = base
    n = 2
    existing = lambda: {ws.title for ws in wb.worksheets} | used
    while candidate in existing():
        suffix = f'_{n}'
        if len(base) + len(suffix) > max_len:
            base = base[:max_len - len(suffix)]
        candidate = base + suffix
        n += 1
    used.add(candidate)
    return candidate

_used = set()
for sheet_name, data in images_data.items():
    clean_name = _clean_sheet_name(sheet_name, _used)
    sheet = wb.create_sheet(title=clean_name)
    
    headers = ['序号', '位置/区域', '中文', 'English']
    for col, header in enumerate(headers, 1):
        cell = sheet.cell(row=1, column=col, value=header)
        cell.font = Font(bold=True)
        cell.fill = PatternFill('solid', start_color='FFFF00')
        cell.alignment = Alignment(horizontal='center')
    
    for row_idx, row_data in enumerate(data, 2):
        for col_idx, value in enumerate(row_data, 1):
            sheet.cell(row=row_idx, column=col_idx, value=value)
        # 一致性检查: 批量同样要做(单张模板已做), 术语库加载时才执行
        if terminology:
            chinese_text = row_data[2]
            english_text = row_data[3]
            for term_cn, term_data in terminology.items():
                if term_cn in chinese_text and term_data['translation'].lower() not in english_text.lower():
                    sheet.cell(row=row_idx, column=4).fill = PatternFill('solid', start_color='FFCC99')
    
    sheet.column_dimensions['A'].width = 8
    sheet.column_dimensions['B'].width = 18
    sheet.column_dimensions['C'].width = 45
    sheet.column_dimensions['D'].width = 55

# 添加说明页
summary = wb.create_sheet(title='00_说明', index=0)
summary.cell(1, 1, value='翻译对照表汇总')
summary.cell(1, 1).font = Font(bold=True, size=14)
summary.cell(2, 1, value='本文件包含以下图片的翻译内容：')
for idx, sheet_name in enumerate(images_data.keys(), 3):
    summary.cell(idx, 1, value=f'• {sheet_name}')
summary.column_dimensions['A'].width = 50

wb.save('批量翻译对照表.xlsx')