def TranslateTable(sInputFilename, sOutputFilename):
    # 讀取表格A并選擇需要翻譯的列
    df_a = pd.read_excel(sInputFilename) # 獲取df對象
    df_a = df_a.iloc[:, [1, 2]]  # iloc和loc很像，i=index，
    # 翻譯英文列
    df_a['translation'] = df_a.iloc[:, 0].apply(Translate)
    # 創(chuàng)建表格B并保存
    df_b = pd.DataFrame({
        '原文': df_a.iloc[:, 0],
        '譯文': df_a.iloc[:, 2]
    })
    df_b.to_excel(sOutputFilename, index=False)

def Translate(sText, from_lang='en', to_lang='zh'):
    appid = 'xxxxxx'
    secret_key = 'xxxxxx'
    url = 'https://fanyi-api.baidu.com/api/trans/vip/translate'
    salt = random.randint(32768, 65536)
    sign = hashlib.md5((appid + sText + str(salt) + secret_key).encode()).hexdigest()
    params = {
        'q': sText,
        'from': from_lang,
        'to': to_lang,
        'appid': appid,
        'salt': salt,
        'sign': sign
    }
    response = requests.get(url, params=params)
    result = json.loads(response.content.decode())
    if result.get('error_code') is not None:
        return None
    return result['trans_result'][0]['dst']

def TranslateTables(sInputFolder, sOutputFolder):
    sInputFilenames = [os.path.join(sInputFolder, f) for f in os.listdir(sInputFolder) if f.endswith('.xlsx')]
    with ThreadPoolExecutor() as executor:
        lstFutures = []
        for sInputFilename in sInputFilenames:
            sFilename = os.path.splitext(os.path.basename(sInputFilename))[0]
            sOutputFilename = os.path.join(sOutputFolder, sFilename + '_翻譯結(jié)果.xlsx')
            lstFutures.append(executor.submit(TranslateTable, sInputFilename, sOutputFilename))
        for future in tqdm(as_completed(lstFutures), total=len(lstFutures)):
            pass

# -*- coding: utf-8 -*-
# time: 2022/2/17 03:06
# file: test.py
# author: Shi Yasong

"""
主要功能功能：
? ? 1、讀取一個(gè)表格文件，獲取需要翻譯的文本。
? ? 2、使用百度翻譯 API 進(jìn)行翻譯，獲取翻譯結(jié)果。
? ? 3、將翻譯結(jié)果保存到原表格中，然后提取需要的列組成一個(gè)新的 DataFrame。
? ? 4、處理多個(gè)表格文件，將它們的翻譯結(jié)果合并到一個(gè) DataFrame 中，然后分別保存。
? ? 5、使用線程池加速翻譯過程，可以同時(shí)翻譯多個(gè)表格
? ? 6、使用 ?concurrent.futures.as_completed 函數(shù)顯示進(jìn)度條。
"""


from concurrent.futures import ThreadPoolExecutor, as_completed
from tqdm import tqdm ?# 進(jìn)度條庫，需要先安裝

import pandas as pd
import requests
import json
import os
import hashlib
import random


def Translate(sText, from_lang='en', to_lang='zh'):
? ? appid = 'xxxx'
? ? secret_key = 'xxxxx'
? ? url = 'https://fanyi-api.baidu.com/api/trans/vip/translate'
? ? salt = random.randint(32768, 65536)
? ? sign = hashlib.md5((appid + sText + str(salt) + secret_key).encode()).hexdigest()
? ? params = {
? ? ? ? 'q': sText,
? ? ? ? 'from': from_lang,
? ? ? ? 'to': to_lang,
? ? ? ? 'appid': appid,
? ? ? ? 'salt': salt,
? ? ? ? 'sign': sign
? ? }
? ? response = requests.get(url, params=params)
? ? result = json.loads(response.content.decode())
? ? if result.get('error_code') is not None:
? ? ? ? return None
? ? return result['trans_result'][0]['dst']


def TranslateTable(sInputFilename, sOutputFilename):
? ? # 讀取表格A并選擇需要翻譯的列
? ? df_a = pd.read_excel(sInputFilename) # 獲取df對象
? ? df_a = df_a.iloc[:, [1, 2]] ?# iloc和loc很像，i=index，
? ? # 翻譯英文列
? ? df_a['translation'] = df_a.iloc[:, 0].apply(Translate)
? ? # 創(chuàng)建表格B并保存
? ? df_b = pd.DataFrame({
? ? ? ? '原文': df_a.iloc[:, 0],
? ? ? ? '譯文': df_a.iloc[:, 2]
? ? })
? ? df_b.to_excel(sOutputFilename, index=False)


def TranslateTables(sInputFolder, sOutputFolder):
? ? sInputFilenames = [os.path.join(sInputFolder, f) for f in os.listdir(sInputFolder) if f.endswith('.xlsx')]
? ? with ThreadPoolExecutor() as executor:
? ? ? ? lstFutures = []
? ? ? ? for sInputFilename in sInputFilenames:
? ? ? ? ? ? sFilename = os.path.splitext(os.path.basename(sInputFilename))[0]
? ? ? ? ? ? sOutputFilename = os.path.join(sOutputFolder, sFilename + '_翻譯結(jié)果.xlsx')
? ? ? ? ? ? lstFutures.append(executor.submit(TranslateTable, sInputFilename, sOutputFilename))
? ? ? ? for future in tqdm(as_completed(lstFutures), total=len(lstFutures)):
? ? ? ? ? ? pass


# 調(diào)用函數(shù)翻譯多個(gè)表格
sInputFolder = r'C:\Users\lenovo\Desktop\english' ?# 修改為實(shí)際的表格文件夾路徑
sOutputFolder = r'C:\Users\lenovo\Desktop\zh' ?# 修改為實(shí)際的表格文件夾路徑
TranslateTables(sInputFolder, sOutputFolder)