将列表列表写入csv，并在python中使用UnicodeEncodeError

Question

我正在使用openpyxl库将xlsm文件转换为csv。我有一个问题，当我试图将列表列表传递给csv时，python返回以下错误：

追踪(最近一次通话最后一次(：文件"；test.py"；，第21行，inlistIntoCsv(最终列表(文件"；test.py"；，第16行，在列表IntoCsv中write.writerows(列表(UnicodeEncodeError："ascii"编解码器无法对位置20中的字符u"\xfa"进行编码：序号不在范围(128(内

以下是最终列表中1个列表的2个示例：

[
[u'[e-mail]', u'[e-mail], [e-mail]', 
datetime.datetime(2022, 7, 18, 10, 7, 16),
1L, '0', 1L, 2L, 'NO', 
None, '[e-mail]',
'[e-mail]', None,
None, False, False, None,None,
False, False, False, None, None,
True, 'SI', 'N/A',3182L, 0L, None,
None, None, '#N/A', 'RE: N< >LWWM',
u'a9e5bbbb497', u'Com: xd1<GR A', None]...
]

我正在用python2执行代码。我尝试应用不同的解决方案，使代码看起来像这样：

from openpyxl import load_workbook
import csv
import codec
excelFile = load_workbook('test.xlsm', data_only=True)
currentSheet = excelFile.worksheets[0]

def iter_rows(currentSheet):
for row in currentSheet.iter_rows():
yield [cell.value for cell in row]
def listIntoCsv(list):
with codecs.open('test','w',encoding='UTF-8') as f:
write = csv.writer(f)
write.writerows(list)

finalList = list(iter_rows(currentSheet))
print(finalList)
listIntoCsv(finalList)

Answer 1

由于python 2.7不支持unicode格式，为了更改列表中每个元素的类型，我们必须手动将其类型更改为字符串。因此，解决方案是：

from types import NoneType
import unicodedata
from openpyxl import load_workbook
import csv
from datetime import datetime
import os

def dateTimeToUnixTIme(dateTime):
dataTimeObject = datetime.datetime.strptime(dateTime, "%Y-%m-%d 
%H:%M:%S.%f")
unixTime = time.mktime(dataTimeObject.timetuple()) + 
(dataTimeObject.microsecond / 1000000.0)
return unixTime
def iterRows(currentSheet):
for row in currentSheet.iter_rows():
yield [cell.value for cell in row]
def listIntoCsv(list):
with open('excel.csv','w') as f:
write = csv.writer(f)
write.writerows(list)

def decodeList(list):
for x,y in enumerate(list):
for j,element in enumerate(y):
if isinstance(element,unicode):
element = unicodedata.normalize('NFKD', 
element).encode('ascii', 'ignore')
if (", ") in element and '@' in element:
element = element.replace(' ','')
element = element.split(",")
list[x][j] = element
list[x][j] = element
elif isinstance(element,date):
element = element.strftime('%Y-%m-%d %H:%M:%S.%f')
element = dateTimeToUnixTIme(element)
list[x][j] = str(element)
elif isinstance(element,(long,int,float)):
element = str(element)
list[x][j] = element
elif isinstance(element,NoneType):
element = str(element).replace('None','Nada')
list[x][j] = element
elif isinstance(element,bool):
element = str(element)
list[x][j] = element
elif isinstance(element,str):
element = str(element)
list[x][j] = element
else:
list[x][j] = element
return list
ubicationExcel = 'bin/mailList/Investigacion_DLP_enmascarado.xlsm'
excelFile = load_workbook(ubicationExcel, data_only=True)
currentSheet = excelFile.worksheets[0]
dirtyList = list(iterRows(currentSheet))
finalList = encodeList(dirtyList)
listIntoCsv(finalList)