Python中的CSV到XLSX

Question

我试图使用python2.7版本将csv转换为xlsx，但它给我的错误如下

Traceback (most recent call last):
  File "C:/Users/XYZ/Desktop/converter.py", line 13, in <module>
    ws.cell(row=r, column=c).value = val
  File "C:\Python27\lib\openpyxl\cell\cell.py", line 291, in value
    self._bind_value(value)
  File "C:\Python27\lib\openpyxl\cell\cell.py", line 190, in _bind_value
    value = self.check_string(value)
  File "C:\Python27\lib\openpyxl\cell\cell.py", line 149, in check_string
    value = unicode(value, self.encoding)
UnicodeDecodeError: 'utf8' codec can't decode byte 0x93 in position 1037: invalid start byte

我的代码是

import os
import glob
import csv
import openpyxl 
for csvfile in glob.glob(os.path.join('.', 'i.csv')):
    wb = openpyxl.Workbook()
    ws = wb.active
    with open(csvfile, 'rb') as f:
        reader = csv.reader(f)
        for r, row in enumerate(reader, start=1):
            for c, val in enumerate(row, start=1):
                ws.cell(row=r, column=c).value = val
    wb.save(csvfile + '.xlsx')

下面是CSV文件名i.csv 受影响平台列名和1行单元格数据。

"•  Adobe Photoshop CC 2018 version 19.1.3 and earlier. <br />
•   Adobe Photoshop CC 2017 version 18.1.3 and earlier for mac OS.<br />
•   Adobe Photoshop CC 2017 version 18.1.2 and earlier for Windows.<br />
•   Adobe Acrobat DC Consumer version 2018.011.20038 and earlier.<br />
•   Adobe Acrobat Reader DC Consumer version 2018.011.20038 and earlier.    <br />
•   Adobe Acrobat 2017 Classic 2017 version 2017.011.30079 and earlier. <br />
•   Adobe Acrobat Reader 2017 Classic 2017 version 2017.011.30079 and earlier.  <br />
•   Adobe Acrobat DC Classic 2015 version 2015.006.30417 and earlier. <br />
•   Adobe Acrobat Reader DC Classic 2015 version 2015.006.30417 and earlier.<br />
"

Answer 1

您可能使用Windows编码保存了您的文件，因此unicodecsv库可以按以下方式使用：

import os
import glob
import unicodecsv as csv
import openpyxl 

for csvfile in glob.glob(os.path.join('.', 'i.csv')):
    wb = openpyxl.Workbook()
    ws = wb.active
    print csvfile
    with open(csvfile, 'rb') as f:
        reader = csv.reader(f, encoding='windows-1252')
        for r, row in enumerate(reader, start=1):
            for c, val in enumerate(row, start=1):
                ws.cell(row=r, column=c).value = val
    wb.save(csvfile + '.xlsx')

但是，对于您提供的单元格，这将导致单个单元格包含多个行。如果要将这些条目转换为多个单元格，则需要进一步处理。为此需要一个更好的示例CSV文件。

例如，以下内容可用于删除HTML标记：

ws.cell(row=r, column=c).value = val.replace('<br />', '')