Reputation: 33
The below code currently translate each words from the Excel sheet for the words location in Column A, But the results it currently gives me in the editor but I want the translated output/result in the same excel sheet in Column B. The below code gives me an error. please help me with the code for the results to be written in excel in column B.
import xlrd
import goslate
loc = r"C:\path\fruits.xlsx"
gs = goslate.Goslate()
wb = xlrd.open_workbook(loc)
sheet = wb.sheet_by_index(0)
for i in range(sheet.nrows):
print(gs.translate(sheet.cell_value(i, 0), 'de'))
print(sheet.cell_value(i, 1)
I am receiving the below error
return self._cell_values[rowx][colx]
IndexError: list index out of range
Please someone help me to write my output/result in the same excel in Column B
Upvotes: 0
Views: 304
Reputation: 1726
You're getting the error because xlrd addresses columns are zero based and per documentation the xlrd ignores cells with no data.
so you could access column A by doing
sheet.cell_value(i, 0)
and write to column B by doing
sheet._cell_types[i][1] = xlrd.XL_CELL_TEXT
sheet._cell_values[i][1] = source
however xlrd
is only for reading, so you'd have to use xlwt
to save any changes.
Saving changes brings up another issue, you're source file is ".xlsx" extension, while xlrd
does read this format, xlwt
only writes to the older ".xls" format.
To read and write to ".xlsx" format with one library you can use openpyxl
, using this library your code would look like this:
import openpyxl
import goslate
loc = r"C:\path\fruits.xlsx"
gs = goslate.Goslate()
wb = openpyxl.load_workbook(loc)
sheet = wb.active
for i in range(2, sheet.max_row + 1):
original = sheet.cell(row=i, column=1).value
translated = gs.translate(original, 'de')
sheet.cell(row=i, column=2).value = translated
wb.save(loc)
Upvotes: 0
Reputation: 288
The error is caused because you don't have a B
is empty and package does not read the column from sheet.
To write the translated result to the sheet, you can do something like this:
I don't think xlrd
can write to sheet. You will need to use xlwt
package. You will need to install it pip install xlwt
import xlrd
import xlwt # this package is going to write to sheet
import goslate
loc = "dummy.xlsx"
translated = "dummy2.xlsx" # location to where store the modified sheet
gs = goslate.Goslate()
# crate a workbook using xlwt package in order to write to it.
wbt = xlwt.Workbook() # there is a typo here. this should be wbt
ws = wbt.add_sheet('A Test Sheet') # change this to your sheet name
rwb = xlrd.open_workbook(loc)
sheet = wb.sheet_by_index(0)
for i in range(sheet.nrows):
ws.write(i, 0, sheet.cell_value(i, 0)) # this will write the A column value
ws.write(i, 1, gs.translate(sheet.cell_value(i, 0), 'de')) # this will write the B column value
wbt.save(translated) # this will save the sheet.
As for making changes in the same file, I my opinion you should not do that. The file is already opend by another process in read mode. Changing it can result in unexpected behavior. But if you intent to do that, backup your file, and set the loc for both when reading and when saving file.
Upvotes: 0