Has anyone been able to write out UTF-8 characters using python's xlwt?

StormShadow picture StormShadow · Aug 25, 2011 · Viewed 11.5k times · Source

I'm trying to write data to an excel file that includes Japanese characters. I'm using codec.open() to get the data, and that seems to work fine, but I run into this error when I try to write the data:

UnicodeEncodeError: 'ascii' codec can't encode characters in position 16-17: ordinal not in range(128)

I don't understand why the program would be insisting on using ascii here. When I created a new workbook object, I did so using

wb = xlwt.Workbook(encoding='utf-8')

and both the program file itself and the file it's reading in are saved as UTF-8.

Anybody have any ideas?

EDIT: Here's a link to the xlwt package. http://pypi.python.org/pypi/xlwt

Answer

John Machin picture John Machin · Sep 1, 2011

In an Excel 97-2003 XLS file, each piece of text is encoded in latin1 if that is possible, otherwise UTF-16LE, with a flag to show which. To do that, xlwt nees a unicode object. If the caller supplies a str object, xlwt will attempt to decode it using the encoding specified in the Workbook() call (default is ascii).

This works; try running the following short script and open the resultant file with Excel.

import xlwt
wb = xlwt.Workbook(encoding="UTF-8")
uc = u"".join(unichr(0x0410 + i) for i in xrange(32)) # some Cyrillic characters
u8 = uc.encode("UTF-8")
ws = wb.add_sheet("demo")
ws.write(0, 0, uc)
ws.write(1, 0, u8)
ws.write(2, 0, xlwt.Formula("A1=A2"))
ws.write(3, 0, "ASCII is a subset of UTF-8")
wb.save("xlwt_write_utf8.xls")

The fact that you are getting an encode error, not a decode error, indicates a possible problem in the file input part of your script. Please supply the shortest possible script that causes the error that you are getting. The script should contain something like print repr(your_utf8_text) immediately prior to the failing statement, so that we can see exactly what the text data is. Please include the full error message and the full traceback, and the contents (print repr(contents)) of your very short input file.