CODEWITHSUNDEEP
pythonexceptionunicodeexport-to-excelxlsx
jhulme
jhulme

Reputation: 110

UnicodeDecodeError in Python xlsxwriter library

I'm trying to write a method to allow the exporting of data from a db to an excel file. I'm using the XlsxWriter library for this.

This is the main function

def user_stats_to_excel():
    workbook = xlsxwriter.Workbook('user_stats_test.xlsx')
    worksheet = workbook.add_worksheet()

    worksheet.write_row(0, 0, UserStats.OUTPUT_ORDER)

    for i, user_stat in enumerate(UserStats.objects.all()):
    worksheet.write_row(i+1, 0, user_stat.output())

workbook.close()

and then in my model I have:

def output(self, data=None):
    """ return a list that's ready for some csv writing """

    if data:
        self.data = data
    dir_self = dir(self)
    lst = []

    for name in self.O:
        if 'do_' + name in dir_self:
            out = getattr(self, "do_" + name)(name)
            if out is None:
                out = u""
            if isinstance(out, list):
                lst.extend(map(self.safe_encode, out))
            else:
                try:
                    lst.append(out.encode('utf-8'))
                except Exception:
                    lst.append(out)
        else:
            try:
                lst.append(self.data.get(name, "").encode('utf-8'))
            except Exception:
                lst.append(self.data.get(name, ""))

    return lst

def safe_encode(self, data):
    try:
        return data.encode('utf-8')
    except Exception:
        return data

As you can see I've put encode everywhere already as this is the usual fix with unicode issues in Python.

My stacktrace is:

Traceback (most recent call last):
File "<console>", line 1, in <module>
File "/Users/james/pubfront/riidr/apps/stats/admin.py", line 34, in user_stats_to_excel
workbook.close()
File "/Users/james/pubfront/riidr_venv/lib/python2.7/site-packages/xlsxwriter/workbook.py", line 286, in close
self._store_workbook()
File "/Users/james/pubfront/riidr_venv/lib/python2.7/site-packages/xlsxwriter/workbook.py", line 509, in _store_workbook
xml_files = packager._create_package()
File "/Users/james/pubfront/riidr_venv/lib/python2.7/site-packages/xlsxwriter/packager.py", line 140, in _create_package
self._write_shared_strings_file()
File "/Users/james/pubfront/riidr_venv/lib/python2.7/site-packages/xlsxwriter/packager.py", line 280, in _write_shared_strings_file
sst._assemble_xml_file()
File "/Users/james/pubfront/riidr_venv/lib/python2.7/site-packages/xlsxwriter/sharedstrings.py", line 53, in _assemble_xml_file
self._write_sst_strings()
File "/Users/james/pubfront/riidr_venv/lib/python2.7/site-packages/xlsxwriter/sharedstrings.py", line 83, in _write_sst_strings
self._write_si(string)
File "/Users/james/pubfront/riidr_venv/lib/python2.7/site-packages/xlsxwriter/sharedstrings.py", line 110, in _write_si
self._xml_si_element(string, attributes)
File "/Users/james/pubfront/riidr_venv/lib/python2.7/site-packages/xlsxwriter/xmlwriter.py", line 122, in _xml_si_element
self.fh.write("""<si><t%s>%s</t></si>""" % (attr, string))
File "/Users/james/pubfront/riidr_venv/lib/python2.7/codecs.py", line 688, in write
return self.writer.write(data)
File "/Users/james/pubfront/riidr_venv/lib/python2.7/codecs.py", line 351, in write
data, consumed = self.encode(object, self.errors)
UnicodeDecodeError: 'ascii' codec can't decode byte 0xc3 in position 8: ordinal not in range(128)

I'm finding this a real hassle to debug as the error is only thrown when workbook.close() is called in user_stats_to_excel, so its going to be difficult to find a user that triggers this issue. I'm hoping I'm not the only person to have this problem

Upvotes: 2

Views: 2291

Answers (1)

jhulme
jhulme

Reputation: 110

We started using django utils to handle the encoding: https://docs.djangoproject.com/en/1.7/ref/utils/#module-django.utils.encoding

If you're not using django then the kitchen library has similar functions: https://pythonhosted.org/kitchen/api-text-converters.html#kitchen.text.converters.to_bytes

The only other thing we had to do was tell xlsxwriter to treat urls as strings https://xlsxwriter.readthedocs.org/workbook.html#Workbook

workbook = xlsxwriter.Workbook(filename, {'strings_to_urls': False})

Upvotes: 1

Related Questions