使用这两个库的最基本示例,每行描述如下:
import xlrd
import csv
with xlrd.open_workbook('a_file.xls') as wb:
sh = wb.sheet_by_index(0) # or wb.sheet_by_name('name_of_the_sheet_here')
with open('a_file.csv', 'wb') as f: # open('a_file.csv', 'w', newline="") for python 3
c = csv.writer(f)
for r in range(sh.nrows):
c.writerow(sh.row_values(r))
import openpyxl
import csv
wb = openpyxl.load_workbook('test.xlsx')
sh = wb.active
with open('test.csv', 'wb') as f: # open('test.csv', 'w', newline="") for python 3
c = csv.writer(f)
for r in sh.rows:
c.writerow([cell.value for cell in r])
使用 pandas
会更简短:
import pandas as pd
df = pd.read_excel('my_file', sheet_name='my_sheet_name') # sheet_name is optional
df.to_csv('output_file_name', index=False) # index=False prevents pandas from writing a row index to the CSV.
# oneliner
pd.read_excel('my_file', sheetname='my_sheet_name').to_csv('output_file_name', index=False)
sheetname
改为 sheet_name
,因为这是一个打字错误。 - Keivan Ipchi Haghopenpyxl
API已经发生了相当大的变化(请参见https://openpyxl.readthedocs.io/en/stable/usage.html),我已根据@Boud(现在是@Zeugma?)的回答进行了更新。import openpyxl
import csv
wb = openpyxl.load_workbook('test.xlsx')
sh = wb.active # was .get_active_sheet()
with open('test.csv', 'w', newline="") as file_handle:
csv_writer = csv.writer(file_handle)
for row in sh.iter_rows(): # generator; was sh.rows
csv_writer.writerow([cell.value for cell in row])
@Leonid提供了一些有用的评论-特别是:
csv.writer
提供了一些附加选项,例如自定义分隔符:
csv_writer = csv.writer(fout, delimiter='|', quotechar='"', quoting=csv.QUOTE_MINIMAL)
HTH
pylama
不喜欢单个字母的变量名,而对 csv.writer
的调用提供了额外的选项(如自定义分隔符),这将很酷。例如:csv_writer = csv.writer(fout, delimiter='|', quotechar='"', quoting=csv.QUOTE_MINIMAL)
- Leonidimport pandas as pd
df = pd.read_excel('YourExcel.xlsx', sheet_name=None)
df['worksheet_name'].to_csv('output.csv')
openpyxl
评估 Excel 公式:wb = openpyxl.load_workbook('test.xlsx', data_only=True)
。 - Leonid