我该如何使用openpyxl
模块将带有冒号分隔符的CSV文件转换为XLS(Excel表格)?
我该如何使用openpyxl
模块将带有冒号分隔符的CSV文件转换为XLS(Excel表格)?
一种更简单、更极简的解决方案:
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)
enumerate(start=1)
是消除那些 + 1
的好方法。另外请注意,您可以直接指定 cell(row=row_index, column=column_index)
而不是构建一个字符串。 - John McGeheefrom openpyxl.utils import get_column_letter
,然后再将倒数第二行改为 ws['%s%s'%(column_letter, (row_index + 1))].value = cell
。 - dingles这里是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认为“非法”的字符。代码只是用空字符串替换这些字符。
ws.append([ILLEGAL_CHARACTERS_RE.sub('', _i) for _i in row])
- KyleKingimport 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)