Python 将excel第二列英文翻译到第三列 示例代码

添加了百度翻译api、deelx翻译api、腾讯翻译api
除了deelx可以直接使用以外，百度、腾讯均需要去开发者平台自行注册账号获取appid和key（都有免费额度）

代码会读取1.xlsx 表格里面第一个sheet页，从第二行开始

# -*- coding: utf-8 -*-
 
"""
[url=home.php?mod=space&uid=267492]@file[/url]       : translate.py
@Time       : 2024/4/2 9:59
@Description:
 翻译
"""
import time
 
 
def baidu_translate(string):
    # 百度通用翻译API,不包含词典、tts语音合成等资源，如有相关需求请联系translate_api@baidu.com
    # coding=utf-8
 
    import http.client
    import hashlib
    import urllib
    import random
    import json
 
    appid = '20198'  # 填写你的appid
    secretKey = 'jCjJw_vNbqmiHky'  # 填写你的密钥
 
    httpClient = None
    myurl = '/api/trans/vip/translate'
 
    fromLang = 'auto'  # 原文语种
    toLang = 'zh'  # 译文语种
    salt = random.randint(32768, 65536)
    q = string
    sign = appid + q + str(salt) + secretKey
    sign = hashlib.md5(sign.encode()).hexdigest()
    myurl = myurl + '?appid=' + appid + '&q=' + urllib.parse.quote(
        q) + '&from=' + fromLang + '&to=' + toLang + '&salt=' + str(
        salt) + '&sign=' + sign
 
    try:
        httpClient = http.client.HTTPConnection('api.fanyi.baidu.com')
        httpClient.request('GET', myurl)
 
        # response是HTTPResponse对象
        response = httpClient.getresponse()
        result_all = response.read().decode("utf-8")
        result = json.loads(result_all)
 
        print(result)
        r = result['trans_result'][0]['dst']
    except Exception as e:
        print(e)
        r = e
    finally:
        if httpClient:
            httpClient.close()
        time.sleep(1)
    return r
 
def deelx_translate(string):
    import httpx, json
 
    deeplx_api = "https://api.deeplx.org/translate"
 
    data = {
        "text": string,
        "source_lang": "EN",
        "target_lang": "ZH"
    }
 
    post_data = json.dumps(data)
 
    try:
        r = httpx.post(url=deeplx_api, data=post_data).text
        print(r)
        r = json.loads(r)['data']
    except Exception as e:
        print(e)
        r = e
    return r
 
 
def tencent_translate(string):
    from tencentcloud.common import credential  # 这里需要安装腾讯翻译sdk
    from tencentcloud.common.profile.client_profile import ClientProfile
    from tencentcloud.common.profile.http_profile import HttpProfile
    from tencentcloud.common.exception.tencent_cloud_sdk_exception import TencentCloudSDKException
    from tencentcloud.tmt.v20180321 import tmt_client, models
    import json
 
    try:
        cred = credential.Credential("AKID99vkMk", "A7CFCGCtMaL")  # "xxxx"改为SecretId，"yyyyy"改为SecretKey
        httpProfile = HttpProfile()
        httpProfile.endpoint = "tmt.tencentcloudapi.com"
 
        clientProfile = ClientProfile()
        clientProfile.httpProfile = httpProfile
        client = tmt_client.TmtClient(cred, "ap-beijing", clientProfile)
 
        req = models.TextTranslateRequest()
        req.SourceText = string  # 要翻译的语句
        req.Source = 'en'  # 源语言类型
        req.Target = 'zh'  # 目标语言类型
        req.ProjectId = 0
 
        resp = client.TextTranslate(req)
        data = json.loads(resp.to_json_string())
        print(data)
        r = data['TargetText']
        print(r)
 
 
    except TencentCloudSDKException as err:
        r = err
        print(err)
    return r
 
if __name__ == '__main__':
    from openpyxl import load_workbook
 
    # 打开 Excel 文件
    workbook = load_workbook('1.xlsx')
 
    # 选择第一个工作表
    sheet = workbook.active
 
    # 遍历 2 列（从第二行开始），并将其值写入 3 列
    for row_idx, row in enumerate(sheet.iter_rows(min_row=2, min_col=2, max_col=2, values_only=True), start=2):
 
        # 获取 2 列的值
        d_value = row[0]
 
        # 如果 2 列的值不为空，则写入 3 列
        if d_value is not None:
            translate_str = deelx_translate(d_value)
            print(f"原始：{d_value}\n翻译：{translate_str}")
            # 写入 3 列
            sheet.cell(row=row_idx, column=3, value=translate_str)
        # 保存修改后的 Excel 文件
        workbook.save('1.xlsx')