Python CSV写入文件在Excel中无法读取(中文字符)

对于UTF-8编码,Excel要求在文件开头写入BOM(字节顺序标记)代码点,否则它将采用ANSI与语言环境相关的编码. U+FEFF是Unicode BOM.这是一个将在Excel中正确打开的示例:

#!python2
#coding:utf8
import csv

data = [[u'American',u'???'],
        [u'Chinese',u'???']]

with open('results.csv','wb') as f:
    f.write(u'\ufeff'.encode('utf8'))
    w = csv.writer(f)
    for row in data:
        w.writerow([item.encode('utf8') for item in row])

为了完整起见,Python 3使这更容易.注意newline=''参数而不是wb和utf-8-sig编码自动添加BOM.Unicode字符串是直接编写的,而不是需要编码每个项目.

#!python3
#coding:utf8
import csv

data = [[u'American',u'???'],
        [u'Chinese',u'???']]

with open('results.csv','w',newline='',encoding='utf-8-sig') as f:
    w = csv.writer(f)
    w.writerows(data)

还有第三方模块unicodecsv也使Python 2更容易:

#!python2
#coding:utf8
import unicodecsv

data = [[u'American',u'???'],
        [u'Chinese',u'???']]

with open('results.csv','wb') as f:
    w = unicodecsv.writer(f,encoding='utf-8-sig')
    w.writerows(data)