python生成excel文件报扩展名错误_Python 读取与写入Excel文件（后缀名.xlsx）

测试表格数据如下图：

表格名字：data1.xlsx

使用模块 import xlrd

下载：python -m pip install xlrd

1、读取所有的Sheet表

# -*- encoding=utf-8 -*-

import xlrd

if __name__ == '__main__':

pass

filename = 'data1.xlsx'

f = xlrd.open_workbook(filename) # 打开Excel

all_sheets = f.sheets() # 找到所有的表

print('表的数量'.center(50, '-'))

print(len(all_sheets)) # 打印表的数量

print(''.center(50, '-'))

for sheet in all_sheets:

name = sheet.name

print('每个表的名字'.center(50, '-'))

print(name) # 遍历打印表的名字

print(''.center(50, '-'))

运行结果

-----------------------表的数量-----------------------

2

--------------------------------------------------

----------------------每个表的名字----------------------Sheet1--------------------------------------------------

----------------------每个表的名字----------------------表格2--------------------------------------------------

2、读取第一个表格数据(采用读取单元格形式)

# -*- encoding=utf-8 -*-

import xlrd

if __name__ == '__main__':

pass

filename = 'data1.xlsx'

f = xlrd.open_workbook(filename) # 打开Excel

all_sheets = f.sheets() # 找到所有的表

# print('表的数量'.center(50, '-'))

# print(len(all_sheets)) # 打印表的数量

# print(''.center(50, '-'))

for sheet in all_sheets:

name = sheet.name

# print('每个表的名字'.center(50, '-'))

# print(name) # 遍历打印表的名字

# print(''.center(50, '-'))

if len(all_sheets) >= 1:

sheet1 = all_sheets[0]

print('sheet1 表名'.center(50, '-'))

print(sheet1.name) # 打印表1的名字

print(''.center(50, '-'))

rows = sheet1.nrows

print('sheet1 的行数'.center(50, '-'))

print(rows)

print(''.center(50, '-'))

lines = sheet1.ncols

print('sheet1 的列数'.center(50, '-'))

print(lines)

print(''.center(50, '-'))

# 遍历Sheet1的数据

print('sheet1 的数据如下'.center(50, '-'))

for row in range(rows):

for line in range(lines):

data = sheet1.cell(row, line).value

print(data)

print(''.center(50, '-'))

运行结果

--------------------sheet1 表名---------------------Sheet1--------------------------------------------------

--------------------sheet1 的行数--------------------

21

--------------------------------------------------

--------------------sheet1 的列数--------------------

3

--------------------------------------------------

-------------------sheet1 的数据如下-------------------height

weight

sex160.0

95.0

1.0

165.0

100.0

1.0

166.0

95.0

1.0

170.0

150.0

1.0

180.0

150.0

1.0

175.0

95.0

1.0

167.0

96.0

1.0

177.0

105.0

1.0

185.0

130.0

1.0

178.0

98.0

1.0

166.0

95.0

0.0

156.0

85.0

0.0

157.0

89.0

0.0

165.0

90.0

0.0

170.0

89.0

0.0

175.0

90.0

0.0

178.0

95.0

0.0

169.0

90.0

0.0

164.0

90.0

0.0

170.0

90.0

0.0

--------------------------------------------------

读取第一个表格数据(采用一行一行读取形式)

#-*- encoding=utf-8 -*-

importxlrdif __name__ == '__main__':passfilename= 'data1.xlsx'f= xlrd.open_workbook(filename) #打开Excel

all_sheets = f.sheets() #找到所有的表

#print('表的数量'.center(50, '-'))

#print(len(all_sheets)) # 打印表的数量

#print(''.center(50, '-'))

for sheet inall_sheets:

name=sheet.name#print('每个表的名字'.center(50, '-'))

#print(name) # 遍历打印表的名字

#print(''.center(50, '-'))

if len(all_sheets) >= 1:

sheet1=all_sheets[0]print('sheet1 表名'.center(50, '-'))print(sheet1.name) #打印表1的名字

print(''.center(50, '-'))

rows=sheet1.nrowsprint('sheet1 的行数'.center(50, '-'))print(rows)print(''.center(50, '-'))

lines=sheet1.ncolsprint('sheet1 的列数'.center(50, '-'))print(lines)print(''.center(50, '-'))#遍历Sheet1的数据

#print('sheet1 的数据如下'.center(50, '-'))

#for row in range(rows):

#for line in range(lines):

#data = sheet1.cell(row, line).value

#print(data)

#print(''.center(50, '-'))

for row inrange(rows):

row_data=sheet1.row_values(row)print(row_data) #一行一行遍历

运行结果

D:\Python37_64\python.exe D:/B_CODE/Python/读取CSV文件/ReadXLSX.py--------------------sheet1 表名---------------------Sheet1--------------------------------------------------

--------------------sheet1 的行数--------------------

21

--------------------------------------------------

--------------------sheet1 的列数--------------------

3

--------------------------------------------------['height', 'weight', 'sex']

[160.0, 95.0, 1.0]

[165.0, 100.0, '']

[166.0, 95.0, 1.0]

[170.0, 150.0, 1.0]

[180.0, 150.0, 1.0]

[175.0, 95.0, 1.0]

[167.0, 96.0, 1.0]

[177.0, 105.0, 1.0]

[185.0, 130.0, 1.0]

[178.0, 98.0, 1.0]

[166.0, 95.0, 0.0]

[156.0, 85.0, 0.0]

[157.0, 89.0, 0.0]

[165.0, 90.0, 0.0]

[170.0, 89.0, 0.0]

[175.0, 90.0, 0.0]

[178.0, 95.0, 0.0]

[169.0, 90.0, 0.0]

[164.0, 90.0, 0.0]

[170.0, 90.0, 0.0]

Process finished with exit code 0

读取第一个表格数据(采用一列一列读取形式)

#-*- encoding=utf-8 -*-

importxlrdif __name__ == '__main__':passfilename= 'data1.xlsx'f= xlrd.open_workbook(filename) #打开Excel

all_sheets = f.sheets() #找到所有的表

#print('表的数量'.center(50, '-'))

#print(len(all_sheets)) # 打印表的数量

#print(''.center(50, '-'))

for sheet inall_sheets:

name=sheet.name#print('每个表的名字'.center(50, '-'))

#print(name) # 遍历打印表的名字

#print(''.center(50, '-'))

if len(all_sheets) >= 1:

sheet1=all_sheets[0]print('sheet1 表名'.center(50, '-'))print(sheet1.name) #打印表1的名字

print(''.center(50, '-'))

rows=sheet1.nrowsprint('sheet1 的行数'.center(50, '-'))print(rows)print(''.center(50, '-'))

lines=sheet1.ncolsprint('sheet1 的列数'.center(50, '-'))print(lines)print(''.center(50, '-'))#遍历Sheet1的数据

#print('sheet1 的数据如下'.center(50, '-'))

#for row in range(rows):

#for line in range(lines):

#data = sheet1.cell(row, line).value

#print(data)

#print(''.center(50, '-'))

#for row in range(rows):

#row_data = sheet1.row_values(row)

#print(row_data) # 一行一行遍历

for line inrange(lines):

line_data=sheet1.col_values(line)print(line_data) #一列一列遍历

运行结果

D:\Python37_64\python.exe D:/B_CODE/Python/读取CSV文件/ReadXLSX.py--------------------sheet1 表名---------------------Sheet1--------------------------------------------------

--------------------sheet1 的行数--------------------

21

--------------------------------------------------

--------------------sheet1 的列数--------------------

3

--------------------------------------------------['height', 160.0, 165.0, 166.0, 170.0, 180.0, 175.0, 167.0, 177.0, 185.0, 178.0, 166.0, 156.0, 157.0, 165.0, 170.0, 175.0, 178.0, 169.0, 164.0, 170.0]

['weight', 95.0, 100.0, 95.0, 150.0, 150.0, 95.0, 96.0, 105.0, 130.0, 98.0, 95.0, 85.0, 89.0, 90.0, 89.0, 90.0, 95.0, 90.0, 90.0, 90.0]

['sex', 1.0, '', 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0]

Process finished with exit code 0

3、读取日期类型（读取生日）

我们直接读取表格，会发现生日读出来不是datatime类型，而是float类型，例如：

#-*- encoding=utf-8 -*-

importxlrdif __name__ == '__main__':passfilename= 'data1.xlsx'f= xlrd.open_workbook(filename) #打开Excel

all_sheets = f.sheets() #找到所有的表

#print('表的数量'.center(50, '-'))

#print(len(all_sheets)) # 打印表的数量

#print(''.center(50, '-'))

for sheet inall_sheets:

name=sheet.name#print('每个表的名字'.center(50, '-'))

#print(name) # 遍历打印表的名字

#print(''.center(50, '-'))

if len(all_sheets) >= 2:

sheet1= all_sheets[1]print('sheet1 表名'.center(50, '-'))print(sheet1.name)

print(''.center(50, '-'))

rows=sheet1.nrowsprint('sheet1 的行数'.center(50, '-'))print(rows)print(''.center(50, '-'))

lines=sheet1.ncolsprint('sheet1 的列数'.center(50, '-'))print(lines)print(''.center(50, '-'))#遍历Sheet1的数据

#print('sheet1 的数据如下'.center(50, '-'))

#for row in range(rows):

#for line in range(lines):

#data = sheet1.cell(row, line).value

#print(data)

#print(''.center(50, '-'))

for row inrange(rows):

row_data=sheet1.row_values(row)print(row_data) #一行一行遍历

#for line in range(lines):

#line_data = sheet1.col_values(line)

#print(line_data) # 一列一列遍历

运行结果

D:\Python37_64\python.exe D:/B_CODE/Python/读取CSV文件/ReadXLSX.py--------------------sheet1 表名---------------------表格2--------------------------------------------------

--------------------sheet1 的行数--------------------

21

--------------------------------------------------

--------------------sheet1 的列数--------------------

3

--------------------------------------------------['姓名', '年龄', '生日']

['李华1', 20.0, 43831.0]

['李华2', 21.0, 43832.0]

['李华3', 22.0, 43833.0]

['李华4', 23.0, 43834.0]

['李华5', 24.0, 43835.0]

['李华6', 25.0, 43836.0]

['李华7', 26.0, 43837.0]

['李华8', 27.0, 43838.0]

['李华9', 28.0, 43839.0]

['李华10', 29.0, 43840.0]

['李华11', 30.0, 43841.0]

['李华12', 31.0, 43842.0]

['李华13', 32.0, 43843.0]

['李华14', 33.0, 43844.0]

['李华15', 34.0, 43845.0]

['李华16', 35.0, 43846.0]

['李华17', 36.0, 43847.0]

['李华18', 37.0, 43848.0]

['李华19', 38.0, 43849.0]

['李华20', 39.0, 43850.0]

Process finished with exit code 0

可以采用这种读取方式（先判断读出类型是什么，再进行转换）

类型有 0 代表empty,1代表 string, 2代表 number, 3代表 date, 4 代表boolean, 5 代表error

读取返回类型sheet1.cell(row, line).ctype

判断如果类型ctype等于3，则使用datetime(*xldate_as_tuple(val, 0))进行转换

需要import的模块

from datetime import datetime

import xlrd

from xlrd import xldate_as_tuple

#-*- encoding=utf-8 -*-

from datetime importdatetimeimportxlrdfrom xlrd importxldate_as_tupleif __name__ == '__main__':passfilename= 'data1.xlsx'f= xlrd.open_workbook(filename) #打开Excel

all_sheets = f.sheets() #找到所有的表

#print('表的数量'.center(50, '-'))

#print(len(all_sheets)) # 打印表的数量

#print(''.center(50, '-'))

for sheet inall_sheets:

name=sheet.name#print('每个表的名字'.center(50, '-'))

#print(name) # 遍历打印表的名字

#print(''.center(50, '-'))

if len(all_sheets) >= 2:

sheet1= all_sheets[1]print('sheet1 表名'.center(50, '-'))print(sheet1.name) #打印表1的名字

print(''.center(50, '-'))

rows=sheet1.nrowsprint('sheet1 的行数'.center(50, '-'))print(rows)print(''.center(50, '-'))

lines=sheet1.ncolsprint('sheet1 的列数'.center(50, '-'))print(lines)print(''.center(50, '-'))#遍历Sheet1的数据

#print('sheet1 的数据如下'.center(50, '-'))

for row inrange(rows):for line inrange(lines):

data_type= sheet1.cell(row, line).ctypeif data_type == 3:print('日期类型')

val=sheet1.cell(row, line).value

data= datetime(*xldate_as_tuple(val, 0))else:

data=sheet1.cell(row, line).valueprint(data)print(''.center(50, '-'))#for row in range(rows):

#row_data = sheet1.row_values(row)

#print(row_data) # 一行一行遍历

#for line in range(lines):

#line_data = sheet1.col_values(line)

#print(line_data) # 一列一列遍历

运行结果

D:\Python37_64\python.exe D:/B_CODE/Python/读取CSV文件/ReadXLSX.py--------------------sheet1 表名---------------------表格2--------------------------------------------------

--------------------sheet1 的行数--------------------

21

--------------------------------------------------

--------------------sheet1 的列数--------------------

3

--------------------------------------------------姓名

年龄

生日

李华120.0日期类型2020-01-01 00:00:00李华221.0日期类型2020-01-02 00:00:00李华322.0日期类型2020-01-03 00:00:00李华423.0日期类型2020-01-04 00:00:00李华524.0日期类型2020-01-05 00:00:00李华625.0日期类型2020-01-06 00:00:00李华726.0日期类型2020-01-07 00:00:00李华827.0日期类型2020-01-08 00:00:00李华928.0日期类型2020-01-09 00:00:00李华1029.0日期类型2020-01-10 00:00:00李华1130.0日期类型2020-01-11 00:00:00李华1231.0日期类型2020-01-12 00:00:00李华1332.0日期类型2020-01-13 00:00:00李华1433.0日期类型2020-01-14 00:00:00李华1534.0日期类型2020-01-15 00:00:00李华1635.0日期类型2020-01-16 00:00:00李华1736.0日期类型2020-01-17 00:00:00李华1837.0日期类型2020-01-18 00:00:00李华1938.0日期类型2020-01-19 00:00:00李华2039.0日期类型2020-01-20 00:00:00

--------------------------------------------------Process finished with exit code 0

4、写入

使用模块 import xlwt

下载: python -m pip install xlwt

# -*- encoding=utf-8 -*-

import xlwt

if __name__ == '__main__':

pass

head = ['name', 'age', 'birthday']

data = [[1, 2, 3], [4, 5, 6], [7, 8, 9]]

workbook = xlwt.Workbook()

# 添加一个表，

# cell_overwrite_ok=True表示覆盖，如果下标相同，则覆盖，不写，下标相同，则抛出异常

sheet1 = workbook.add_sheet('Sheet1', cell_overwrite_ok=True)

for index, info in enumerate(head): # 写入表头

sheet1.write(0, index, info)

for index, row_data in enumerate(data): # 写入数据，注意拼接下标

for line, line_data in enumerate(row_data):

sheet1.write(index + 1, line, line_data)

sheet2 = workbook.add_sheet('Sheet2') # 添加一个表

for index, info in enumerate(head): # 写入表头

sheet2.write(0, index, info)

for index, row_data in enumerate(data): # 写入数据，注意拼接下标

for line, line_data in enumerate(row_data):

sheet2.write(index + 1, line, line_data)

workbook.save('save_data.xlsx')

运行结果

5、追加数据（可以读取保存到同一个文件，这里分开是为了清晰对比）

#-*- encoding=utf-8 -*-

importxlrdfrom xlutils.copy importcopyif __name__ == '__main__':passfilename= 'data1.xlsx'f= xlrd.open_workbook(filename) #打开Excel为xlrd对象

old_sheet = f.sheet_by_index(0) #取到第一个旧表

old_sheet_rows = old_sheet.nrows #第一个旧表的行数，下面追加就得在这个后面写入数据

copy_read = copy(f) #把xlrd对象转为xlwt对象

new_sheet = copy_read.add_sheet('new_sheet') #添加新表，表名不能重复

head = ['name', 'age', 'birthday']

data= [[1, 2, 3], [4, '2019/02/01', 6], [7, 8, 9]]for index, info in enumerate(head): #写入表头

new_sheet.write(0, index, info)for index, row_data in enumerate(data): #写入数据，注意拼接下标

for line, line_data inenumerate(row_data):

new_sheet.write(index+ 1, line, line_data)

exist_sheet= copy_read.get_sheet(0) #取旧表

exist_sheet.write(old_sheet_rows, 0, '新数据1')

exist_sheet.write(old_sheet_rows,1, '新数据2')

exist_sheet.write(old_sheet_rows,2, '新数据3')

copy_read.save('append.xlsx')

运行结果