Python 将 Excel 转换为 CSV

Question

似乎有很多关于这个主题的帖子，我的解决方案与最常见的答案似乎是一致的，但是我遇到了一个我不知道如何解决的编码错误。

>>> def Excel2CSV(ExcelFile, SheetName, CSVFile):
     import xlrd
     import csv
     workbook = xlrd.open_workbook(ExcelFile)
     worksheet = workbook.sheet_by_name(SheetName)
     csvfile = open(CSVFile, 'wb')
     wr = csv.writer(csvfile, quoting=csv.QUOTE_ALL)

     for rownum in xrange(worksheet.nrows):
         wr.writerow(worksheet.row_values(rownum))

     csvfile.close()

>>> Excel2CSV(r"C:\Temp\Store List.xls", "Open_Locations", 
              r"C:\Temp\StoreList.csv")

Traceback (most recent call last):
File "<pyshell#2>", line 1, in <module>
Excel2CSV(r"C:\Temp\Store List.xls", "Open_Locations", r"C:\Temp\StoreList.csv")
File "<pyshell#1>", line 10, in Excel2CSV
wr.writerow(worksheet.row_values(rownum))
UnicodeEncodeError: 'ascii' codec can't encode character u'\xa0' in position 14:
ordinal not in range(128)
>>>

非常感谢任何帮助或见解。

Answer 1

正如@davidism 指出的那样，Python 2 csv 模块不适用于 unicode。您可以通过将所有 unicode 对象转换为 str 对象来解决此问题，然后再将它们提交到 csv:

def Excel2CSV(ExcelFile, SheetName, CSVFile):
     import xlrd
     import csv
     workbook = xlrd.open_workbook(ExcelFile)
     worksheet = workbook.sheet_by_name(SheetName)
     csvfile = open(CSVFile, 'wb')
     wr = csv.writer(csvfile, quoting=csv.QUOTE_ALL)

     for rownum in xrange(worksheet.nrows):
         wr.writerow(
             list(x.encode('utf-8') if type(x) == type(u'') else x
                  for x in worksheet.row_values(rownum)))

     csvfile.close()