1. set up anaconda (Python included)

download (for Linux only)
    wget https://repo.anaconda.com/archive/Anaconda3-2019.03-Linux-x86_64.sh

install
    bash Anaconda3-2019.03-Linux-x86_64.sh

create new env. (With Python3.7)
    conda create --name google_translate_py37 python=3.7

2. install dependencies

conda install -y requests xlrd openpyxl pandas

3. run python script

3.1 Google will frequent request with google-translate API

So please set up a proper time interval for each request like this: (set interval time: 3 seconds, and it can be set to a larger value according to your own situation)

image.png

3.2 run

source activate google_translate_py37
python simple_google_translate-20190804.py

• 3.2.1 Ok to request

image.png

image.png

• 3.2.2 Failed due to frequent requests

image.png

[Details]

4. Script

import csv
import os
import requests
import sys

#import xlrd
import pandas as pd
import numpy as np


ERR_429     = 'request_err_429'
ERR_UNKNOWN = 'request_err_unknown'

new_xlsx_name  = 'google_translate.xlsx'
new_sheet_name = 'google_translate_sheet'


# https://www.jb51.net/article/163320.htm
def showExcelSheetHead(data_frame):
    #print('----------------------------------------------------------------------------------------')
    data = data_frame.head()
    print("[+] showExcelSheetHead:\n{}".format(data))


# https://blog.csdn.net/weixin_43245453/article/details/90747259
def selectExcelSheet(file_path):
    print('----------------------------------------------------------------------------------------')
    print("[+] selectExcelSheet")

    data = pd.read_excel(file_path, None)
    #print("sheets\t\t{}".format(data.keys()))

    sheets = []
    for i, sheet_name in enumerate(data.keys()):
        sheets.append(sheet_name)
        print("\t[-] sheet {}:\t{}".format(i, sheet_name))

    chooice = int(input('Please select a sheet [sheet number] '))
    return sheets[chooice]


def selectExcelTitle(file_path, sheet_name):
    print('----------------------------------------------------------------------------------------')
    print("[+] selectExcelColumn")
    
    sheet_content = pd.DataFrame(pd.read_excel(file_path, sheet_name))
    showExcelSheetHead(sheet_content)

    print()
    titles = []
    titles.append( (sheet_content.columns)[0] )
    titles.append( (sheet_content.columns)[1] )

    for i, title in enumerate(titles):
        print('\t[-] title {}:\t{}'.format(i, title))

    chooice = int(input('Please select a column (keywords_title) to translate [column number] '))
    return titles[chooice], sheet_content


def getExcelShape(data_frame):
    return data_frame.shape
    
def getExcelRows(data_frame):
    return data_frame.shape[0]
    
def getExcelColumns(data_frame):
    return data_frame.shape[1]


# http://pytolearn.csd.auth.gr/b4-pandas/40/moddfcols.html
def insertExcelRow(data_frame, row_index, row_list):
    # insert a row
    data_frame.loc[row_index] = row_list

def insertExcelColumn(data_frame, column_index, column_name, column_list):
    # insert a column
    data_frame.insert(column_index, column_name, column_list)

def appendExcelColumn(data_frame, column_name, column_list):
    # append a column
    #data_frame[column_name] = column_list

    # append an empty column
    data_frame[column_name] = pd.Series()

# https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.replace.html
# https://blog.csdn.net/sscc_learning/article/details/76993816
def updateExcelCell(data_frame, row_index, column_name, value):
    data_frame[column_name][row_index] = value


# Translate Excel columns
def translateExcelColumns(data_frame, keywords_title):
    print('----------------------------------------------------------------------------------------')
    print("[+] translateExcelColumns")

    global ERR_429
    global ERR_UNKNOWN
    global new_xlsx_name
    global new_sheet_name

    if new_sheet_name == '':
        new_xlsx_name  = input('Please name the new xlsx  [xlsx  name] ')
    if new_sheet_name == '':
        new_sheet_name = input('Please name the new sheet [sheet name] ')

    translation_title = 'Google Translate'
    appendExcelColumn(data_frame, translation_title, [])

    nrows = getExcelRows(data_frame)
    print('[*] number of keywords to be translated:\t{}\n\n'.format(nrows))

    for i in range(0, nrows):
        keywords = data_frame[keywords_title][i]
        #print("[-] translate keywords:", keywords)
        res = translateLine(keywords)

        if res == ERR_429:
            print('(T_T) You are requesting "Google Translate" too frequently, please have a rest...')
            return
        elif res == ERR_UNKNOWN:
            print('(T_T) Unknown error happened')
            return

        updateExcelCell(data_frame, i, translation_title, res)
        if i % 10 == 0:
            data_frame.to_excel(new_xlsx_name, sheet_name=new_sheet_name)


# simple way: ok
# https://blog.csdn.net/zcoutman/article/details/69062422
def translateLine(keywords):
    print("\t[-] translateLine:\t{}".format(keywords))

    global ERR_429
    global ERR_UNKNOWN

    # 1. isnan
    if type(keywords) == float:
        if np.isnan(keywords):
            #print("[-] keywords is nan:", keywords)
            return ''

    # 2. strip blank space
    keywords = str(keywords).strip()

    # 3. skip empty keywords
    if keywords == None:
        #print("[-] keywords is None:", keywords)
        return ''
    elif len(keywords) == 0:
        #print("[-] keywords len is 0:", keywords)
        return ''
    elif keywords.isspace():
        #print("[-] keywords is blank space:", keywords)
        return ''

    # 4. translate
    source_language = 'en'
    target_language = 'zh-CN'

    content = 'http://translate.google.cn/translate_a/single?client=gtx&sl=' + source_language + '&tl=' + target_language + '&dt=t&q=' + keywords
    response = requests.post(content)
    #print("[-] response: ", response)
    #print("[-] response status: ", response.status_code)

    if response.status_code == 200:
        res = response.json()
        #print("[-] res: ", res)

        search = res[0][0][1]
        result = res[0][0][0]
        print("\t\t\t\t---> {}".format(result))

    elif response.status_code == 429:
        result = ERR_429
    else:
        result = ERR_UNKNOWN

    return result
    

def selectExcelFile():
    print('----------------------------------------------------------------------------------------')
    print("[+] selectExcelFile")

    files = []
    for i, file in enumerate(os.listdir('./')):
        files.append(file)
        if file.endswith('.xlsx'):
            print('\t[-] file {}:\t{}'.format(i, file))

    chooice = int(input('Please select a xlsx file [file number] '))
    return files[chooice]


def translateOneWords(keywords):
    translateLine(keywords)


def translateExcel():
    file_path  = selectExcelFile()
    print('[*] file_path:\t{}'.format(file_path))

    sheet_name = selectExcelSheet(file_path)
    print('[*] sheet_name:\t{}'.format(sheet_name))

    keywords_title, data_frame = selectExcelTitle(file_path, sheet_name)
    print('[*] keywords_title:\t{}'.format(keywords_title))

    pd.set_option('mode.chained_assignment', None)
    translateExcelColumns(data_frame, keywords_title)


# start from here
if __name__ == '__main__':

    #translateOneWords('pancake')

    translateExcel()

    print('----------------------------------------------------------------------------------------')