How to translate a excel file to another language using python while retaining the format

Question

I have a bunch of excel file that I need to translate in to English so I tried to create a python program to do it since I found limitation with Translate My Sheet. I managed to read and translate each cell using xlrd and googletrans, but I run in to a problem where I can't write back to the original file with xlwt.

The code that I managed to come up with

import xlrd
import xlwt
from xlwt import Workbook
from googletrans import Translator

translator =Translator()

location = (r'test.xlsx')

#Writing to file
wb_w = Workbook()
sheet1 = wb_w.add_sheet('sheet 1')
#Reading file
wb_r = xlrd.open_workbook(location)
sheet = wb_r.sheet_by_index(0)
sheet.cell_value(0,0)

#Going through each cell to translate and rewriting
for column in range(sheet.nrows):
    for row in range(sheet.ncols):
        print(sheet.cell_value(column, row))
        sheet1.write(column, row, translator.translate(sheet.cell_value(column, row), dest='en').text)

wb_w.save(r'test.xlsx')

This is the error I'm getting

Traceback (most recent call last):
  File "C:/Users/bobi_/PycharmProjects/Translator/Translator.py", line 22, in <module>
    sheet1.write(column, row, translator.translate(sheet.cell_value(column, row), dest='en').text)
  File "C:\Users\bobi_\PycharmProjects\Translator\venv\lib\site-packages\googletrans\client.py", line 182, in translate
    data = self._translate(text, dest, src, kwargs)
  File "C:\Users\bobi_\PycharmProjects\Translator\venv\lib\site-packages\googletrans\client.py", line 78, in _translate
    token = self.token_acquirer.do(text)
  File "C:\Users\bobi_\PycharmProjects\Translator\venv\lib\site-packages\googletrans\gtoken.py", line 195, in do
    tk = self.acquire(text)
  File "C:\Users\bobi_\PycharmProjects\Translator\venv\lib\site-packages\googletrans\gtoken.py", line 140, in acquire
    for i in text:
TypeError: 'float' object is not iterable

Thanks for the help

score 0 · Answer 1 · answered Jul 05 '20 at 13:03

Based on your error we can see the problem is on the translation, not the xlwt or xlrd. The problem is that the translate function expects a string, not a number. The sheet.cell_value(...) will return a float if the cell value is a number. My guess is that you only tested the translate function only for cells that contain texts.

You can either do one of these things:

Check the type of the value. Translate only if the type is a string.

for column in range(sheet.nrows):
    for row in range(sheet.ncols):
        value = sh.cell_value(column, row)
        if type(value) == str:
            value = translate.translate(value, dest='en').text
        sheet1.write(column, row, value)

Or you can convert the value to a string first, then translate it.

value = str(sh.cell_value(column, row))
translated = translate.translate(value, dest='en').text
sheet1.write(column, row, translated)

You need to try both methods and see which works out the best for you.

You might want to change the output file with another name. In the code you pasted you load the original file "test.xlsx" and write the translated file also to "test.xlsx". I don't know whether this will work, but if it does you will lose your original file.

By the way, as I have tested xlwt doesn't support .xlsx format (see an example here). You either have to write it to an .xls file or you have to look for other modules that support .xlsx.

How to translate a excel file to another language using python while retaining the format

1 Answers1