使用前,先确保你有批量要翻译的excel列表,因为是直接覆盖原有列的数据,所以需要先复制一份文件。然后pycharm中更换appid和appkey、以及更换数据的列(是哪一列),即可直接使用。
import time
import requests
import random
from hashlib import md5
from openpyxl import Workbook, load_workbook
class Baidufanyi:
def __init__(self):
# Set your own appid/appkey.
self.list_result = []
self.appid = '你的appid'
self.appkey = '你的appkey'
# For list of language codes, please refer to `https://api.fanyi.baidu.com/doc/21`
self.from_lang = 'zh'
self.to_lang = 'en'
endpoint = 'http://api.fanyi.baidu.com'
path = '/api/trans/vip/translate'
self.url = endpoint + path
# 处理拼接字符串的MD5加密
def make_md5(self, encoding='utf-8'):
self.salt = random.randint(32768, 65536)
s = (self.appid + self.query + str(self.salt) + self.appkey)
self.sign = md5(s.encode(encoding)).hexdigest()
def requests(self):
# 构造headers和参数params数据
headers = {'Content-Type': 'application/x-www-form-urlencoded'}
payload = {'appid': self.appid, 'q': self.query, 'from': self.from_lang, 'to': self.to_lang, 'salt': self.salt,
'sign': self.sign}
# 发送请求
r = requests.post(self.url, params=payload, headers=headers)
result = r.json()
result_final = result['trans_result'][0]['dst']
return result_final
# 从表格中提取数据并写入翻译后的数据
def get_writein_data(self):
# 加载工作簿
wb = load_workbook('你需要翻译的数据的表格.xlsx')
ws = wb.active
### ‘C’替换为你想翻译的列
for cell_read in ws['C']:
try:
self.query = cell_read.value
print(self.query)
self.make_md5()
self.requests()
cell_read.value = self.requests()
time.sleep(1)
wb.save('你需要翻译的数据的表格.xlsx')
except:
time.sleep(60)
wb.save('你需要翻译的数据的表格.xlsx')
# 创建实例,执行程序
if __name__ == "__main__":
result_trans = Baidufanyi()
result_trans.get_writein_data()