Pythonでやってみた１５：Excelのデータの自動翻訳（DeepL-API／GoogleTranslate関数）
１．概要
過去記事で「Word文章をDeepL-APIで自動翻訳」を作成しました。
今回はExcelファイルを自動翻訳します。目的として「ここにあるExcelが日本語なんだけど英訳しといて」と言われた方向けです。
１－１．設計思想
設計思想に関して下記手順で実装しようと思います。
Excelファイルから文字列を抽出できるようにする
抽出した文字を翻訳(英訳)
翻訳した文字を置換
これらの操作は1つのファイルで実行して結果は別シートに保存
Excelファイルの操作はPandasが好きなのですがExcelの体裁を維持できません。既にあるファイルの体裁を維持したまま操作するならopenpyxlが便利であるため、こちらを使用します。
翻訳機能は下記を想定しています。
DeepL-API
GoogleのGOOGLETRANSLATE関数をうまく使う
１－２．サンプルデータ
サンプルデータは適当に作成しました。塗りつぶしや罫線は体裁が崩れないか確認用で追加しています。
２．openpyxlでのデータ抽出
openpyxlで実装していきます。本章では動作検証も含めてどのような処理をしているか確認します。
２－１．Excelの読み込み
Excelファイルは"pyxl.load_workbook(<ファイルパス>)"で"Book”を読み込み、辞書のようして"Sheet"を選択できます。また属性を指定するとファイル情報が取得できます。
今回Excelシートは1枚であり"wb.active"でも動作しますが、一般的には複数のシートがあるためシート名は指定しました。
[IN]
import openpyxl as pyxl
filepath ='sampledata.xlsx'
sheetname = 'Sheet1'
wb = pyxl.load_workbook(filepath)
_ws = wb[sheetname] # Sheet1を指定※シートが1つならwb.activeでも可
print(wb)
print(wb.sheetnames)
print(_ws)
print(f'Sheet情報 タイトル：{_ws.title} 最終行：{_ws.max_row} 最終列：{_ws.max_column} \n')[OUT]
<openpyxl.workbook.workbook.Workbook object at 0x0000020103F59A90>
['Sheet1']
<Worksheet "Sheet1">
Sheet情報 タイトル：Sheet1 最終行：26 最終列：6 ２－２．シートのコピー
シートのコピーは”wb.copy_worksheet(<Sheetオブジェクト>)”を使用します。コピーしたオブジェクトのタイトルを変更して上書き保存すれば、元ファイルにシートが追加されています。
ただしこのままでは同じシートが追加できる問題が残ります。
[IN]
ws = wb.copy_worksheet(_ws) #指定シートをコピー
ws.title = sheetname + '_翻訳' #シート名を変更
wb.save(filepath) #保存[OUT]
そこで関数化して、既存のシート名がある場合は処理を実行しないように変更しました。このようにすることで2回目以降で同じシート名が追加されることがありません。他にも下記機能を追加しました。
初期の状態では指定シートをコピーして新しいシートを追加
既に同じシート名がある場合は処理をしない
追加したシートを上書きできるように"overwrite"引数追加
コピー元を修正した後に再度翻訳をかけたい場合に利用
本記事では２回連続して翻訳することを想定していないため、もし翻訳済みの有無を判断する場合、特性セルに文字を入れて判断させるコードを追加してもよいと思います（今回は未実装）。
[IN]
#新しいシートの追加
def copy_sheet(wb, sheetname, suffix='_翻訳', overwrite=False):
sheetname_new = sheetname + suffix
if sheetname_new in wb.sheetnames:
if overwrite:
wb.remove(wb[sheetname_new])
print(f'既存の「{sheetname_new}」シートを上書きします。')
else:
print(f'「{sheetname_new}」は既に存在します。既存を読み込みます。')
ws = wb[sheetname_new]
return ws
ws = wb.copy_worksheet(wb[sheetname]) #指定シートをコピー
ws.title = sheetname_new #シート名を変更
wb.save(filepath) #上書き保存
return ws
ws = copy_sheet(wb, sheetname, suffix='_翻訳', overwrite=False)
ws[OUT]
「Sheet1_翻訳」は既に存在します。既存を読み込みます。２－３．セル情報の取得
"sheetオブジェクト.iter_rows()"で行ごとのオブジェクトを取得し、rowをループで回すと各セルの情報が取得できます。
セルに文字列の値がある場合だけ情報取得できることを確認しました。次章で翻訳した値を"cell.value = <翻訳した情報>"とすることで値を上書きできます。
[IN]
#各セル情報を取得
for row in ws.iter_rows():
for cell in row:
if cell.value and isinstance(cell.value, str): #セルに値がある、かつ文字列型
print(cell.value)[OUT]
大分類
小分類
サンプル文章
重要度
備考
経済:
供給
供給が増えたため、価格が下がりました。
需要
高い需要が続いているこの商品は、すぐに売り切れるでしょう。
インフレーション
インフレーションの影響で、物価が上昇しています。
デフレーション
経済の停滞によりデフレーションが続いている。
資本
企業は新しい事業のための資本を集めています。
金利
金利の上昇は、住宅ローンに大きな影響を及ぼすでしょう。
化学:
反応式
反応式を正しく記述することは、化学実験の基本です。
触媒
触媒の存在下で、反応はより迅速に進行します。
電解質
塩は水に溶けると電解質として働きます。
コンデンサー
コンデンサーは、化学実験で液体を冷却するのに使われます。
分極
分極の影響で、分子間の反応が変わることがあります。
有機化合物
エタノールは有機化合物の一例です。
工学:
力学
力学は物体の運動や力に関する学問です。
サーキット
サーキットの設計は、電子工学の基本です。
トルク
モーターのトルクは、その性能を示す重要な指標です。
設計
新しい機械の設計には多くの時間と労力がかかります。
熱伝導
熱伝導の性質を持つ材料は、冷却装置に使用されます。
機械要素
歯車やベアリングは機械要素の例です。
経営:
戦略
新しい戦略の下で、会社はさらなる成長を目指しています。
マーケティング
効果的なマーケティングは、売上の増加に直結します。
ロジスティクス
ロジスティクスの最適化により、配送コストを削減することができます。
ブランディング
強力なブランディングは、消費者の信頼を獲得するための鍵となります。
人材管理
人材管理の改善は、従業員のモチベーションを高めるために必要です。
SWOT分析
SWOT分析を行うことで、企業の強みや弱みを明確にすることができます。３．自動翻訳１：DeepL-API
前回の記事を参考にしながらDeepLで翻訳していきます。
無料Ver.は文字数制限があるため情報量が多い方は課金お願いします。
３－１．API_KEYの取得
私は既に登録済みのため「アカウント」タブ->「アカウント」の「DeepL APIで使用する認証キー」からAPI Keyをコピーしました。
登録がまだの方は前回記事をご確認ください。
３－２．HTTPクライアントでAPI処理
DeepL-APIの使い方は公式Docsに記載の通りｃURLやHTTPリクエストを送ることで処理できます。
リクエストを送るためのHTTPクライアントとしてRequestsを使用します。
関数は下記の通りです。
[IN]
import requests
API_KEY = '自分のＡＰＩ＿ＫＥＹを入れてね'
def get_api(text:str) -> str:
params = {
"auth_key": API_KEY,
"text": text,
"source_lang": 'JA',
"target_lang": 'EN'
}
request = requests.post("https://api-free.deepl.com/v2/translate", data=params)
result = request.json()
text_EN = result["translations"][0]["text"]
return text_EN
sample_text1 = '''台風7号は小笠原諸島に接近後、暴風域を伴ったまま14日(月)にかけて北上し、
15日(火)午前3時には強い勢力で紀伊半島沖へ進む予想です。西日本や東日本を
中心に、台風7号の影響を受ける時間が長くなるでしょう。また比較的コンパクト
な台風なため、近づくと急に雨や風が強まりそうです。'''
res = get_api(sample_text1)
print(res)[OUT]
After approaching the Ogasawara Islands, Typhoon No. 7 is expected to move northward with a storm area through Monday, March 14,
By 3:00 a.m. on Tuesday, March 15, it is expected to move into the offshore area of the Kii Peninsula with strong winds. Centering on western and eastern Japan
The typhoon will have a longer period of time to affect mainly western and eastern Japan. It is a relatively compact typhoon.
Rain and winds are likely to suddenly intensify as it approaches.４．自動翻訳２：GoogleTranslate
DeepL-APIとは別にGoogleの翻訳機能も使用してみます。Googleのスプレッドシートには"GOOGLETRANSLATE関数"があり、これを利用することで翻訳が可能です。
コピペは手間のためうまく自働化できるか検証しました。結果は出ましたが、認証の部分が微妙なのでセキュリティを気にする方は自分で再検証お願いします。
４－１．必要ライブラリのインストール
スプレッドシートをPythonから操作するためのライブラリをインストールします。
Googleスプレッドシート操作用：gspread
Google APIs の認証関係：
google-auth：認証対応で推奨されるライブラリ
oauth2client：記事は多いが現在は非推奨。よって本記事でも未使用
[Terminal]
pip install gspread
pip install google【gspead】
【oauth2client/google】
４－２．Google Cloudの設定
基本的に下記記事の通りに実行したらうまくいきました。
忘れそうなポイントは下記の通りです。
「新しい鍵を作成」で取得できるJSONファイル内の"client_email"は次で使用するため、保存後にコピーしておく。
４－３．SpreadSheetの設定
まずはGoogle driveに適当なSpreadSheetを作成します。
（※URLからファイルを読み込むため適当な場所でOKです。）
ファイルを開いて共有ボタンを押し「ユーザーやグループを追加」に先ほど保存したJSONファイル内の”client_email”の値を入力して「送信」ボタンを押します。
この作業は前節のGoogle Clouldで作成したサービスアカウントに割り当てられたE-mailアドレスに権限を渡すことでAPI経由でSpreadSheetにアクセスできるようにしています。
完了するとサービスアカウントの”client_email”が登録されました。
【動作検証】
APIErorrなく動くか確認します。まずは適当な文字を書き込み、URLをコピーします。
コードの中で下記２か所を修正して実行することでエラーなく実行できれば問題ありません。
Google Cloudのサービスアカウント作成時の鍵(JSONファイル）のパス
SpreadSheetのURL
[IN]
from google.oauth2.service_account import Credentials
import gspread
scopes = [
'https://www.googleapis.com/auth/spreadsheets',
'https://www.googleapis.com/auth/drive'
]
credentials = Credentials.from_service_account_file(
'<ダウンロードしたJSONファイルのファイル名>.json', # ダウンロードしたjsonファイル
scopes=scopes
)
gc = gspread.authorize(credentials)
spreadsheet_url = "https://docs.google.com/spreadsheets/d/1ukBnalgrFQ5zEaNz9qeWYUgvNELUXwr2PzXf0IARc7I/edit#gid=0"
#読み込み
spreadsheet = gc.open_by_url(spreadsheet_url)
worksheet = spreadsheet.sheet1
print(worksheet.get_all_values())
#書き込み
worksheet.update_acell('B1', '文字を追加しました!')[OUT]
[['hello', '', ''], ['', 'こんにちは', ''], ['', '', '早上好']]
４－４．GOOGLETRANLATE関数の動作
GOOGLETRANSLATE関数は下記のように使用します。
GOOGLETRANSLATE （テキスト, ソース言語, ターゲット言語） よって適当なセル(A1)に翻訳したい文字を加えてGOOGLETRANSLATE関数を実行させ、処理後のセルの値を抽出することで翻訳値を取得しました。
[IN]
#GOOGLETRANSLATE関数
cell = 'A1'
text = '非常に強い台風第7号は、12日12時現在、父島の北北西約190kmにあって、北西へゆっくり進んでいます。'
text_EN = worksheet.update_acell(cell, f'=GOOGLETRANSLATE("{text}","ja","en")')
worksheet.acell(cell).value[OUT]
'The very strong typhoon No. 7 is slowly proceeding northwest at 190 km north -northwest of Chichijima as of 12:00.'
翻訳した値が得られておりSpreadSheetの値も変更されています。つまりSpreadsheetはバッファー（中間経路）で使用しています。
４－５．翻訳用関数の実装
GOOGLETRANSLATE関数をPythonで使用しやすいように関数化しました。
元関数と同じく”翻訳したい文字”、”元言語”、”翻訳言語”を指定するだけで結果が得られるようにしています。
[IN]
def googletranslate(text:str, lang_from:str='ja', lang_to:str='en') -> str:
cell = 'A1'
worksheet.update_acell(cell1, f'=GOOGLETRANSLATE("{text}", "{lang_from}", "{lang_to}")')
text_EN = worksheet.acell(cell1).value
return text_EN
text = '非常に強い台風第7号は、12日12時現在、父島の北北西約190kmにあって、北西へゆっくり進んでいます。'
googletranslate(text)[OUT]
'The very strong typhoon No. 7 is slowly proceeding northwest at 190 km north -northwest of Chichijima as of 12:00.'
５．openpyxl×翻訳の統合
５－１．DeepL-API編
前述の機能を統合して下記を実装します。
Excelファイルの読み込み、Sheetコピー、セル情報抽出
overwrite=Trueとしているため毎回「シート読み込み×翻訳」を実施
翻訳済み判定機能は入れていないため必要なら別途要実装
抽出した文字を翻訳(英訳)
API_KEYは自分の物に変更お願いします
翻訳した文字を置換
２－３をループ処理
上書き保存
詳細は下記の通りです。この量では約1minくらいで処理が完了します。
[IN]
import openpyxl as pyxl
import requests
API_KEY = '自分のＡＰＩ＿ＫＥＹ入れてね'
def get_api(text:str) -> str:
params = {
"auth_key": API_KEY,
"text": text,
"source_lang": 'JA',
"target_lang": 'EN'
}
request = requests.post("https://api-free.deepl.com/v2/translate", data=params)
result = request.json()
text_EN = result["translations"][0]["text"]
return text_EN
def copy_sheet(wb, sheetname, suffix='_翻訳', overwrite=False):
sheetname_new = sheetname + suffix
if sheetname_new in wb.sheetnames:
if overwrite:
wb.remove(wb[sheetname_new])
print(f'既存の「{sheetname_new}」シートを上書きします。')
else:
print(f'「{sheetname_new}」は既に存在します。既存を読み込みます。')
ws = wb[sheetname_new]
return ws
ws = wb.copy_worksheet(wb[sheetname]) #指定シートをコピー
ws.title = sheetname_new #シート名を変更
wb.save(filepath) #上書き保存
return ws
filepath ='sampledata.xlsx'
sheetname = 'Sheet1'
wb = pyxl.load_workbook(filepath)
ws = copy_sheet(wb, sheetname, suffix='_翻訳', overwrite=True)
#セル情報を取得して翻訳
for row in ws.iter_rows():
for cell in row:
if cell.value and isinstance(cell.value, str): #セルに値がある、かつ文字列型
text_EN = get_api(cell.value)
cell.value = text_EN
wb.save(filepath)[OUT]
結論は「結構いいんじゃない！」と思います。
５－２．GOOGLETRANSLATE関数編
前回の翻訳関数”get_api”を"googletranslate"に変更するだけとなります。
[IN]
import openpyxl as pyxl
from google.oauth2.service_account import Credentials
import gspread
def googletranslate(worksheet, text:str, lang_from:str='ja', lang_to:str='en') -> str:
cell = 'A1'
worksheet.update_acell(cell1, f'=GOOGLETRANSLATE("{text}", "{lang_from}", "{lang_to}")')
text_EN = worksheet.acell(cell1).value
return text_EN
def copy_sheet(wb, sheetname, suffix='_翻訳', overwrite=False):
sheetname_new = sheetname + suffix
if sheetname_new in wb.sheetnames:
if overwrite:
wb.remove(wb[sheetname_new])
print(f'既存の「{sheetname_new}」シートを上書きします。')
else:
print(f'「{sheetname_new}」は既に存在します。既存を読み込みます。')
ws = wb[sheetname_new]
return ws
ws = wb.copy_worksheet(wb[sheetname]) #指定シートをコピー
ws.title = sheetname_new #シート名を変更
wb.save(filepath) #上書き保存
return ws
#Excelファイル
filepath ='sampledata.xlsx'
sheetname = 'Sheet1'
wb = pyxl.load_workbook(filepath)
ws = copy_sheet(wb, sheetname, suffix='_翻訳', overwrite=True)
#Spreadsheet
scopes = [
'https://www.googleapis.com/auth/spreadsheets',
'https://www.googleapis.com/auth/drive'
]
credentials = Credentials.from_service_account_file(
'<ダウンロードしたJSONファイルのファイル名>.json', # ダウンロードしたjsonファイル
scopes=scopes
)
gc = gspread.authorize(credentials)
spreadsheet_url = "https://docs.google.com/spreadsheets/d/1ukBnalgrFQ5zEaNz9qeWYUgvNELUXwr2PzXf0IARc7I/edit#gid=0"
#読み込み
spreadsheet = gc.open_by_url(spreadsheet_url)
worksheet = spreadsheet.sheet1
#セル情報を取得して翻訳
for row in ws.iter_rows():
for cell in row:
if cell.value and isinstance(cell.value, str): #セルに値がある、かつ文字列型
text_EN = googletranslate(worksheet, cell.value)
cell.value = text_EN
wb.save(filepath)[OUT]
５－３．コラム：DeepLとGOOGLETRANSLATEの差
この手の記事は山ほどあるため詳細は省きますが、個人的な意見としては大きな差はないけど微妙なニュアンスはDeePLの方が良いです。
６．完成コード
今までのコードを使いやすいようにまとめました。分かりやすさもかねてclassとして扱います。
後々見返すとあまり良い記事ではないですがclassの記事は下記参考のこと
６－１．完成コード：DeepL-API編
今までのコードをclassにしました。設計思想は下記の通りです。
渡す値はExcelファイルパス、シート名、API_KEYのみ
translateメソッドを呼び出すことで一括処理したい。この時、シートの上書き、ブックの上書きは選択できるようにする
その他の設計は全て各章と同じ
[IN]
import openpyxl as pyxl
import requests
class Translator_DeepLAPI:
def __init__(self, filepath:str = None, sheetname:str = None, api_key:str = None):
self.filepath = filepath #Excelファイルのパス
self.sheetname = sheetname #コピー元のシート名
self.api_key = api_key #DeepLのAPIキー
def get_api(self, text:str):
params = {
'auth_key': self.api_key,
'text': text,
'source_lang': 'JA',
'target_lang': 'EN'
}
res = requests.post('https://api-free.deepl.com/v2/translate', data=params)
result = res.json()
text_EN = result['translations'][0]['text']
return text_EN
def copy_Excelsheet(self, wb, sheetname:str, suffix:str = '_翻訳', overwrite:bool = False):
sheetname_new = sheetname + suffix
if sheetname_new in wb.sheetnames:
if overwrite:
wb.remove(wb[sheetname_new])
print(f'既存の「{sheetname_new}」シートを上書きします。')
else:
print(f'「{sheetname_new}」は既に存在します。既存を読み込みます。')
ws = wb[sheetname_new]
return ws
ws = wb.copy_worksheet(wb[sheetname]) #指定シートをコピー
ws.title = sheetname_new #シート名を変更
return ws
def translate(self, overwrite:bool = False, save:bool = True):
wb = pyxl.load_workbook(self.filepath)
ws = self.copy_Excelsheet(wb, self.sheetname, overwrite = overwrite)
#翻訳
for row in ws.iter_rows():
for cell in row:
if cell.value and isinstance(cell.value, str):
text_EN = self.get_api(cell.value)
cell.value = text_EN
#上書き保存
if save:
wb.save(self.filepath)
return ws
#翻訳実行
filepath ='sampledata.xlsx'
sheetname = 'Sheet1'
API_KEY = '自分のＡＰＩ＿ＫＥＹを入れてね'
translator = Translator_DeepLAPI(filepath, sheetname, API_KEY)
ws = translator.translate(overwrite = True, save = True)
ws[OUT]
<Worksheet "Sheet1_翻訳">同じ結果が得られることが確認できました。
６－２．完成コード：GOOGLETRANSLATE
今までのコードをclassにしました。設計思想は下記の通りです。
渡す値はExcelファイルパス、シート名、APIのJSONファイルパスのみ
translateメソッドを呼び出すことで一括処理したい。この時、シートの上書き、ブックの上書きは選択できるようにする
その他の設計は全て各章と同じ
[IN]
class Translator_Google:
def __init__(self, filepath:str, sheetname:str = None, path_API:str = None):
self.filepath = filepath
self.sheetname = sheetname
self.path_API = path_API
def googletranslate(self, worksheet, text:str, lang_from:str='ja', lang_to:str='en') -> str:
cell = 'A1'
worksheet.update_acell(cell1, f'=GOOGLETRANSLATE("{text}", "{lang_from}", "{lang_to}")')
text_EN = worksheet.acell(cell1).value
return text_EN
def open_Worksheet(self):
scopes = [
'https://www.googleapis.com/auth/spreadsheets',
'https://www.googleapis.com/auth/drive'
]
credentials = Credentials.from_service_account_file(
self.path_API,
scopes=scopes
)
gc = gspread.authorize(credentials)
spreadsheet_url = "https://docs.google.com/spreadsheets/d/1ukBnalgrFQ5zEaNz9qeWYUgvNELUXwr2PzXf0IARc7I/edit#gid=0"
spreadsheet = gc.open_by_url(spreadsheet_url)
worksheet = spreadsheet.sheet1
return worksheet
def copy_Excelsheet(self, wb, sheetname:str, suffix:str = '_翻訳', overwrite:bool = False):
sheetname_new = sheetname + suffix
if sheetname_new in wb.sheetnames:
if overwrite:
wb.remove(wb[sheetname_new])
print(f'既存の「{sheetname_new}」シートを上書きします。')
else:
print(f'「{sheetname_new}」は既に存在します。既存を読み込みます。')
ws = wb[sheetname_new]
return ws
ws = wb.copy_worksheet(wb[sheetname]) #指定シートをコピー
ws.title = sheetname_new #シート名を変更
wb.save(filepath) #上書き保存
return ws
def translate(self, overwrite:bool = False, save:bool = True, lang_from:str='ja', lang_to:str='en'):
wb = pyxl.load_workbook(self.filepath)
ws = self.copy_Excelsheet(wb, self.sheetname, overwrite = overwrite)
worksheet = self.open_Worksheet()
#翻訳
for row in ws.iter_rows():
for cell in row:
if cell.value and isinstance(cell.value, str):
text_EN = self.googletranslate(worksheet, cell.value, lang_from=lang_from, lang_to=lang_to)
cell.value = text_EN
#上書き保存
if save:
wb.save(self.filepath)
return ws
#翻訳実行
filepath ='sampledata.xlsx'
sheetname = 'Sheet1'
path_API = '自分のJSONファイルパスを指定してね' # ダウンロードしたjsonファイル
translator = Translator_Google(filepath, sheetname, path_API)
ws = translator.translate(overwrite = True, save = True, lang_from='ja', lang_to='en')
ws[OUT]
<Worksheet "Sheet1_翻訳">参考資料
あとがき
あんまり翻訳ソフトを使うと英語力が落ちるのだけど、雑務はさっさと終わらせたいね。
この記事が気に入ったらサポートをしてみませんか？