策划最近提了个工具的需求，做一个机翻自动填表的工具，方便应急的时候临时将语言表填快速填上。
其实之前是有想过做，后来发现机翻有点鸡肋，所以就打断了这个念头，没想到策划提了。
其实实现起来是不难的，关键是API的接口，查询过多容易超时或者没有响应，所以一定程度上还是要把握下间隙的速度。
并且刚做出来的版本其实是有点问题的，例如遇到HTML的代码或者是一些带有变量填法的时候就会出现问题。
具体的解决思路是这样的，首先就是遍历出带有ID，且填了中文的，但其他语言没有填的，并且以中文为翻译依据。
存储变量，其中一个变量则通过正则匹配的方式将中文输出，并且翻译再逐渐匹配查找替换掉之前的中文字符，从而最终形成新的翻译语句。最后再将翻译语言填入数据表并且保存。
整体的思路就是这样，废话也不多说了，直接上代码。当然代码后续还会优化一下
这里需要调用的谷歌API接口是：translators；顺便附上git的地址：https://github.com/uliontse/translators
#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Author : 黑羽青衣
# @File : TranslateTool.py
from openpyxl.reader.excel import load_workbook
import translators as ts
import re
import time
from zhconv import convert
import os
import time
class Translate():
def __init__(self, excel_path:str, sFileName, no_list, language):
'''打开一个excel'''
self.excel_path = excel_path
self.sFileName = sFileName
self.no_list = no_list
self.language = language
#print(excel_path)
if not os.path.exists(excel_path):
print("路径错误，请检查当前路径")
os.system('pause')
def Get_FileName(self, excel_path):
print("开始检查文件")
sFileName = []
filename = os.listdir(excel_path)
try:
for Name in filename:
if os.path.exists(excel_path + r"\\" + Name):
suffix = Name.split(".")[1]
test = Name.split(".", 2)
if len(test) == 2:
if suffix == "xlsx" or suffix == "xlsm" or suffix == "xltx" or suffix == "xltm":
name = Name.split(".")[0]
if "~$" not in name:
sFileName.append(Name)
else:
print("发现冲突的文件，请检查文件名：{}".format(Name.split(".")[0]))
continue
print("检查完毕！")
return sFileName
except Exception as e:
print(e)
pass
def get_needchecksheetname(self, excel_path, sFileName):
needchecksheetname = []
if sFileName == None:
return
wb = load_workbook(excel_path + r"\\" + sFileName)
if wb != None:
sheetnames = wb.get_sheet_names()
for sheet in sheetnames:
if sheet not in no_list:
#print("现在检查的sheetname:{}".format(sheet), flush=True)
needchecksheetname.append(sheet)
return needchecksheetname
def run(self):
sFileName = self.Get_FileName(excel_path)
#print("==============================开始查表==============================")
#help(ts.deepl)
self.check_Language()
os.system('pause')
def check_Language(self):
print("一键翻译语言工具")
text = ""
new_text = ""
try:
wb = load_workbook(excel_path + r"\\语言总表_Language.xlsx")
sheetnames = self.get_needchecksheetname(excel_path, "语言总表_Language.xlsx")
for a in sheetnames:
self.ws = wb.get_sheet_by_name(a)
for y in range(2, self.ws.max_column + 1):
if self.ws.cell(1, y).value in language:
lan = self.ws.cell(1, y).value
for i in range(6, self.ws.max_row + 1):
if self.ws.cell(i, y).value == None and self.ws.cell(i, 1).value != None and self.ws.cell(i,2).value != None:
print("打印Sheet:{} 语言：{} 第{}行 第{}列 为空，开始翻译！".format(a, lan, i, y))
text = self.ws.cell(i, 2).value
new_text = self.ws.cell(i, 2).value
if text != None:
re_text = re.findall(r'[\u4E00-\u9FA5]+',text)
if re_text:
for x in re_text:
#print(a)
time.sleep(2)
data = self.translate(x, lan)
if data != None:
new_text = re.sub(x, data, new_text, count=1)
print("翻译前的语言：" + text + " 翻译后的语言：" + new_text)
self.ws.cell(row=i, column=y, value=new_text)
else:
print("发现有不是中文的情况！sheet:{} 第{}行 第{}列 内容{}".format(a, i, 2, text))
print("检查完毕，没有需要翻译的！")
wb.save(excel_path + r"\\语言总表_Language.xlsx")
except Exception as e:
print(e)
pass
def translate(self, text, lan):
if lan == "繁體":
time.sleep(1)
sentence = ts.google(text, to_language='zh-TW', if_use_cn_host=True, sleep_seconds=1.0)
if sentence:
return sentence
if lan == "繁体":
time.sleep(1)
sentence = ts.google(text, to_language='zh-TW', if_use_cn_host=True, sleep_seconds=1.0)
if sentence:
return sentence
if lan == "英文":
time.sleep(1)
sentence = ts.google(text, if_use_cn_host=True, sleep_seconds=1.0)
if sentence:
return sentence
if lan == "泰文":
time.sleep(1)
sentence = ts.google(text, to_language='th', if_use_cn_host=True, sleep_seconds=1.0)
if sentence:
return sentence
if lan == "印尼文":
time.sleep(1)
sentence = ts.google(text, to_language='id', if_use_cn_host=True, sleep_seconds=1.0)
if sentence:
return sentence
if lan == "越南语":
time.sleep(1)
sentence = ts.google(text, to_language='vi', if_use_cn_host=True, sleep_seconds=1.0)
if sentence:
return sentence
if __name__ == '__main__':
excel_path = os.path.abspath("数据表")
sFileName = ""
no_list = [
"语言总表附录",
"LanguageType",
]
language = [
"简体中文",
"繁體",
"繁体",
"英文",
"泰文",
"印尼文",
"越南语",
]
s = Translate(excel_path, sFileName, no_list, language)
s.run()