一个简单得多的极简解决方案：

import csv
import openpyxl

wb = openpyxl.Workbook()
ws = wb.active

with open('file.csv') as f:
    reader = csv.reader(f, delimiter=':')
    for row in reader:
        ws.append(row)

wb.save('file.xlsx')

import csv
from openpyxl import Workbook
from openpyxl.cell import get_column_letter

f = open(r'C:\Users\Asus\Desktop\herp.csv')

csv.register_dialect('colons', delimiter=':')

reader = csv.reader(f, dialect='colons')

wb = Workbook()
dest_filename = r"C:\Users\Asus\Desktop\herp.xlsx"

ws = wb.worksheets[0]
ws.title = "A Snazzy Title"

for row_index, row in enumerate(reader):
    for column_index, cell in enumerate(row):
        column_letter = get_column_letter((column_index + 1))
        ws.cell('%s%s'%(column_letter, (row_index + 1))).value = cell

wb.save(filename = dest_filename)

下面是Adam的解决方案，扩展后去掉了openpyxl认为非法并会抛出异常的字符：

import re
from openpyxl.cell.cell import ILLEGAL_CHARACTERS_RE
...
##ws.append(row) - Replace with the code below
for i in row:
    ws.append([ILLEGAL_CHARACTERS_RE.sub('',i)])

ILLEGAL_CHARACTERS_RE是一个编译后的正则表达式，包含openpyxl认为“非法”的字符，代码只是用一个空字符串替换这些字符。
来源：Bitbucket openpyxl issue #873 - Remove illegal characters instead of throwing an exception

除了John的建议之外，我还使用function对脚本进行了轻微修改，删除了所有原始数据的字符串撇号。（字符串和数字），它们也被放置在各自的单元格中。最后，我从第20行开始将数字数据分配为float类型。这是因为所有数字数据都从第20行开始存在。而上述所有数据仅为文本。

单元格值=单元格.替换（'"'，''）

下面是我的脚本：

import csv
from openpyxl import Workbook

wb = Workbook()
ws = wb.active

with open(filepath1_csv) as f:
reader = csv.reader(f)
for row_index, row in enumerate(reader):
    for column_index, cell in enumerate(row):
        column_letter = column_index + 1
        cell_value = cell.replace('"', '')
        ws.cell(row = row_index + 1, column = column_letter).value = cell_value

for row in ws.iter_rows(min_row=20, min_col=1, max_col=5, 
max_row=ws.max_row):
for cell in row:
    if cell.value is None:
        break
    else:
        cell.value = float(cell.value)

wb.save(filename = filepath1_xlsx)