# -*- coding: utf-8 -*-
# This code shows an example of text translation from English to Simplified-Chinese.
# This code runs on Python 2.7.x and Python 3.x.
# You may install `requests` to run this code: pip install requests
# Please refer to `https://api.fanyi.baidu.com/doc/21` for complete api document
# For list of language codes, please refer to `https://api.fanyi.baidu.com/doc/21`
# from_lang = 'zh'
# to_lang = 'en'
# query = 'Hello World! This is 1st paragraph.\nThis is 2nd paragraph.'
# -- 原始代码
# salt = random.randint(32768, 65536)
# sign = make_md5(appid + query + str(salt) + appkey)
#
# # Build request
# headers = {'Content-Type': 'application/x-www-form-urlencoded'}
# payload = {'appid': appid, 'q': query, 'from': from_lang, 'to': to_lang, 'salt': salt, 'sign': sign}
#
# # Send request
# r = requests.post(url, params=payload, headers=headers)
# result = r.json()
#
# # Show response
# # 原始内容:
# # {
# # "from": "zh",
# # "to": "en",
# # "trans_result": [
# # {
# # "src": "Hello World! This is 1st paragraph.",
# # "dst": "Hello World! This is the first paragraph"
# # },
# # {
# # "src": "This is 2nd paragraph.",
# # "dst": "This is the 2nd paragraph"
# # }
# # ]
# # }
# print(json.dumps(result, indent=4, ensure_ascii=False))
import traceback
import requests
import random
import json
import time
import openpyxl
from hashlib import md5
# from config.config_log import log
# Generate salt and sign
def make_md5(s, encoding='utf-8'):
return md5(s.encode(encoding)).hexdigest()
def baidu_api(query, from_lang, to_lang):
appid = ''
appkey = ''
try:
if query is None or len(query) < 1:
print(f"{str(query)} --> {str(None)}")
return None
except Exception as e:
print(f"打印失败({query}):不影响流程-1,失败原因({e})")
salt = random.randint(32768, 65536)
sign = make_md5(appid + query + str(salt) + appkey)
# Build request
endpoint = 'http://api.fanyi.baidu.com'
path = '/api/trans/vip/translate'
url = endpoint + path
headers = {'Content-Type': 'application/x-www-form-urlencoded'}
payload = {'appid': appid, 'q': query, 'from': from_lang, 'to': to_lang, 'salt': salt, 'sign': sign}
# Send request
res = requests.post(url, params=payload, headers=headers)
# Show response
# print(json.dumps(result, indent=4, ensure_ascii=False))
try:
res_value = res.json()["trans_result"][0]['dst']
if "【" in res_value: res_value = str(res_value).replace("【", "[")
if "】" in res_value: res_value = str(res_value).replace("】", "]")
print(f"{query} --> {res_value}")
except Exception as e:
res_value = "exception"
print(f"打印失败({query}):不影响流程-2,失败原因({e})")
return res_value
def deepl_api(query="商机页面,必填字段检查", source_lang="ZH", target_lang="EN"):
url = "http://localhost:1188/translate"
# EN ,ZH
payload = json.dumps({
"text": query,
"source_lang": source_lang,
"target_lang": target_lang
})
headers = {
'Content-Type': 'application/json'
}
res = requests.request("POST", url, headers=headers, data=payload)
# print(response.text)
# {"alternatives":["世界你好","你好世界","Hello World"],"code":200,"data":"你好,世界","id":8317711002,"method":"Free","source_lang":"EN","target_lang":"ZH"}
try:
res_value = res.json()["data"]
# 格式化特殊字符
if "【" in res_value: res_value = str(res_value).replace("【", "[")
if "【" in res_value: res_value = str(res_value).replace("】", "]")
print(f"{query} --> {res_value}")
except Exception as e:
res_value = "exception"
print(f"打印失败({query}):不影响流程-2,失败原因({e})")
return res_value
def main_translate(start_path, sheet_name="SIT测试用例", mode="deepl"):
"""
mode = baidu
mode = deepl
"""
print(f"sheet_name:{sheet_name},mode:{mode}")
wb = openpyxl.load_workbook(start_path)
# sheet = wb.active
sheet = wb[sheet_name]
# 翻译次数来之不易,建议失败了也保存部分内容
try:
for i in range(1, sheet.max_row, 1):
# for i in range(1, 5):
row_line = str(i + 1)
print(f"第{row_line}行".center(100, "-"))
for col in [
"B_B",
"D_D",
"G_G",
"F_F",
"J_J",
"L_L",
"M_M"
]:
start_index, end_index = col.split("_")[0], col.split("_")[1]
if sheet[start_index + row_line].value is None:
# cell = sheet[start_index + row_line]
# merged_start_cell = sheet.cell(cell.range.start_row, cell.range.start_column)
# merged_start_cell.value = "New Value"
pass
else:
# 判断翻译方式:EN ZH
if mode == "baidu":
# 百度普通开发者,每秒只能发送1个请求
sheet[end_index + row_line] = baidu_api(query=sheet[start_index + row_line].value, from_lang="auto", to_lang="en")
elif mode == "deepl":
sheet[end_index + row_line] = deepl_api(query=sheet[start_index + row_line].value, source_lang="ZH", target_lang="EN")
time.sleep(1)
except Exception as e:
print(traceback.format_exc())
end_path = start_path.replace(".xlsx", f"""(EN)_{time.strftime("%Y%m%d_%H%M%S")}.xlsx""")
wb.save(end_path)
if __name__ == '__main__':
start_path = r""
# main_translate(start_path, sheet_name="SIT测试用例", mode="baidu")
main_translate(start_path, sheet_name="SIT测试用例", mode="deepl") # 这种方法,字符串中有【】,翻译后会自动少第二个括号,需要批量改为[]再翻译
【Web_接口测试_Python3_PyDeepLX&openpyxl】批量翻译,将Excel测试用例文件,批量从中文翻译成英文
于 2024-03-11 16:49:00 首次发布