利用python批量将excel中文翻譯成英文

目錄

• 操作過程中不斷遇到新的問題，思路的轉換過程
• 背景
• • 第一天 操作過程
  • 第二天
  • • 正規表達式是個好東西
  • 第三天
  • 第四天
  • 第五天
  • 遇到的小問題

操作過程中不斷遇到新的問題，思路的轉換過程

背景

今天接到一個任務，需要将EXCEL檔案中的中文翻譯成英文，由于表格内容非常大，我想着 這要是手動翻譯，三天啥都不用幹了，就翻譯這個吧

于是有了用python批量翻譯的想法

目前的思路是，将excel中每個單元的内容拿出來，放在一個清單中，然後用翻譯軟體翻譯再批量存入新的表格（應該可以用API接口，将所有單元内容依次拿出、翻譯、存入，這個還沒學會。。。）

參考文章：pandas操作

第一天 操作過程

1.發現一個問題，google翻譯有字數上限。。。一次最多5000字元

于是想到分幾步粘貼，需要翻譯的表格差不多兩萬多。自己多動幾次手就好了。

好，終于翻譯完了，開始運作

新問題來了，谷歌在翻譯的大段字元的時候，把有的引号 ‘’ 給吞了，這就造成了清單中間的元素發生了紊亂，這個就比較麻煩了，剛開始嘗試手動修改，把吞掉的引号給他補上，手動操作5分鐘之後，我放棄了，這尼瑪也太坑了。就在我準備砸電腦放棄的時候，突然靈光一閃，想到可以用正規表達式批量計算啊，簡直不要太機智

import pandas as pd

path = r'C:\Users\hao\Desktop\批量翻譯\源檔案\兩廣-住宅調研問卷.xlsx'
df = pd.read_excel(path, header=None)
contents = []
for i in range(len(df[0])):
	for j in range(len(df.iloc[0])):
		content = df.iloc[i,j]
		print('第{}行第{}列元素：%s'.format(i, j)%content)
		contents.append(content)
print('行和列的長度：%d,%d'%(len(df[0]), len(df.iloc[0])))
print(contents)
           

第二天

正規表達式是個好東西

正規表達式常見操作

先導入所有翻譯好的資料

在編譯器的提示下，可以很明顯地看出出現錯誤的地方（白色就代表出錯了），英文裡面所有格 's中會帶來引号，與清單中的引号發生了沖突，于是想到用正則把其他的引号改為雙引号，

然後把開頭和結尾的單引号也改為雙引号，但依然提示錯誤。。。發現是由于數字不是字元串沒有引号，沒有被正則比對上，于是又回去修改代碼，把int和float型加上引号，再接着運作，發現還是有錯誤，中間有些空白字元被吞了，就導緻後面的整個秩序對不上，導入到表格中發生錯位。。。最後就放棄用正則修改了。。。

第三天

既然批量提取，翻譯之後再批量導入的方法行不通，就隻能試一試之前想到的API了，提取一個，翻譯一個，這樣就不會錯位了，說幹就幹，百度了一個方法，代碼如下

#利用translate子產品，但有兩個缺點，一個是限制翻譯數量，每天就能翻譯1000個詞
#再一個是隻能由英文翻譯成中文，比較瓜
import pandas as pd
from translate import Translator
path = r'C:\Users\hao\Desktop\批量翻譯\源檔案\test_db1.xlsx'
df = pd.read_excel(path, header=None)
#content_1, content_2, content_2, content_2, 
for i in range(len(df[0])):   #df[0]表示提取第一列
	for j in range(len(df.iloc[0])):  #df.iloc[0]表示提取第一行
		#print(str(df.iloc[i,j]))
		translator = Translator(to_lang="zh")
		translation = translator.translate(df.iloc[i,j])
		print(translation)
		df.iloc[i,j] = translation
df.to_excel(r'C:\Users\hao\Desktop\批量翻譯\結果\EtoC.xlsx', index=False)
           

第四天

既然谷歌提供的API隻讓翻譯1000個詞，我就在翻譯界面看了一下頁面源碼

發現，源碼上有直接的翻譯結果的，那我幹脆自己直接用爬蟲抓取得了，還要他的API幹嘛，talk is cheap，show me the code

#自己利用爬蟲寫
import requests
from bs4 import BeautifulSoup
import re
import time
import pandas as pd

headers = {'User-Agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_3) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/56.0.2924.87 Safari/537.36'}

def Translatorbyreptile():
	target = '睡覺時關,外面吵、有噪音時關,外面天氣不好時關,出門離家時關,室外有霧霾時'
	url = 'https://translate.google.cn/#view=home&op=translate&sl=zh-CN&tl=en&text=' + target
	response = requests.get(url=url, headers=headers)
	#print(response)
	html = response.text
	print(html)
	soup = BeautifulSoup(html, 'html.parser') #Beautiful Soup是python的一個庫，最主要的功能是從網頁抓取資料
	divs = soup.find_all(class_="tlid-translation translation")
	print(divs)
# path = r'C:\Users\hao\Desktop\批量翻譯\源檔案\東北住宅調研問卷.xlsx'
# df = pd.read_excel(path, header=None)
# #content_1, content_2, content_2, content_2, 
# for i in range(len(df[0])):   #df[0]表示提取第一列
# 	for j in range(len(df.iloc[0])):  #df.iloc[0]表示提取第一行
# 		#print(str(df.iloc[i,j]))
# 		target = df.iloc[i,j]
# 		Translatorbyreptile(target)
# 		#df.iloc[i,j] = translation
# #df.to_excel(r'C:\Users\hao\Desktop\批量翻譯\結果\EtoC.xlsx', index=False)
Translatorbyreptile()

'''
最後發現谷歌翻譯、百度翻譯都應該是進行了反爬，得到的都是一大推亂碼。。。。心累
'''
           

第五天

最後發現谷歌翻譯是可以支援直接翻譯檔案的，輸入導出就可以了，so easy,隻是得到的格式比較亂，隻需要自己重新調整一下格式就好了

#利用谷歌翻譯後的結果，進行格式的轉換
import pandas as pd

path = r'C:\Users\hao\Desktop\批量翻譯\格式待調整\兩廣入戶調查問卷.xlsx'
df = pd.read_excel(path, header=None)
contents = []
#content_1, content_2, content_2, content_2, 
for i in range(len(df[0])):   #df[0]表示提取第一列
	for j in range(len(df.iloc[0])):  #df.iloc[0]表示提取第一行
	#for j in range(81,85):  #df.iloc[0]表示提取第一行	
		print(str(df.iloc[i,j]))
		if str(df.iloc[i,j]) == 'nan':
			content = '{}'.format(df.iloc[i,j])
			print(content)
			contents.append(content)
		elif (str(type(df.iloc[i,j])) == "<class 'int'>") or (str(type(df.iloc[i,j])) == "<class 'float'>"):
			content = '{}'.format(df.iloc[i,j])
			print(content)
			contents.append(content)
		else:
			content = df.iloc[i,j]
			#print('第{}行第{}列元素：%s'.format(i, j)%content)
			contents.append(content)
print('行和列的長度：%d,%d'%(len(df[0]), len(df.iloc[0])))
print(contents)
#df.to_excel(r'C:\Users\hao\Desktop\test_db3.xlsx', index=False)

count_number = 0 
for i in range(len(df[0])):   #df[0]表示提取第一列
	for j in range(len(df.iloc[0])):  #df.iloc[0]表示提取第一行
	#for j in range(81,85):  #df.iloc[0]表示提取第一行	
		# if j == 0:
		# 	df.iloc[i,j] = new_contents[]
		df.iloc[i,j] = contents[count_number]
		count_number += 1
		#print('第{}行第{}列元素：%s'.format(i, j)%content)
print(df)

df.to_excel(r'C:\Users\hao\Desktop\批量翻譯\結果\Guangdong-Guangdong-Home Survey.xlsx', index=False, header=0)
           

遇到的小問題

1.需要隐去表頭索引，header=None 不然讀取的時候會遇到問題

存儲的時候需要重新定義header，不然系統會自動加入數字的表頭，另一個需要注意的是：要把索引給隐去， index=False

2.行和列弄混了，之前是這樣的，如下

for i in range(len(df.iloc[0])):  #df.iloc[0]表示讀取第0行
	for j in range(len(df[0])):   #df[0]讀取第0列
           

實際上應該是讀取的第一行當做列，讀取的第一列當做行，/笑 正确代碼如下

for i in range(len(df[0])):
	for j in range(len(df.iloc[0])):
           

3.為了檢測行和列的運作值，進行了附帶解釋的列印，但之前沒有同時列印過兩個變量，出現了小錯誤，如下

print('行和列的長度：%d%d'%len(df[0])%len(df.iloc[0])) 
>>>TypeError: not enough arguments for format string
           

for i in range(len(df[0])):
	for j in range(len(df.iloc[0])):