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

Cristy

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

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

[Python] 纯文本查看 复制代码

# -*- 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')

236560739

只有第二列不好，我有很多表格需要把中文翻译成中英文，我都是直接把文件丢谷歌翻译成英文，然后用python把两份文件合并

Laurenceo278

感谢分享，我又学到了

cxx0515

感谢楼主分享

uxin

厉害了 我的哥

开辟道路的菜鸡

感谢分享

wjbg2022

大哥还把自己的秘钥分享啊，先生大义!

rxw1001

好东西，学习了

jessylake

我这里怎么报错？第二行开始，单词放在第二列

jzfxly

学到了，谢谢。

feng61328

好分享，学习了