[toc]

xlrd

1.简述：

  • xlrd是读取excel表格数据；
  • 支持 xlsx和xls 格式的excel表格；
  • 三方模块安装方式：pip3 install xlrd；
  • 模块导入方式： import xlrd

2.xlrd模块的基本操作

Xler的操作主要分两步：
其一时获取book对象，
其二book对象再次进行excel的读取操作。

2.1打开excel文件获取book对象

xlrd.open_workbook(filename[,logfile,file_contents,…])

  • 如果filename 文件名不存在，则会报错 FilenotFoundError。
  • 如果filename 文件名存在，则会返回一个xrld.book.Book 对象。 import xlrd
1
2
Workbook = xlrd.open_workbook("C:\\Users\li\Desktop\银联测试案例.xls")
print(Workbook)

2.2获取xlrd.book.Book 对象中的所有sheet名称

Names = Workbook.sheet_names()

2.3获取xlrd.book.Book对象中的所有sheet对象

Sheets = workbook.sheets()

2.4判断xlrd.book.Book对象中某个sheet对象是否导入

  • 通过索引
1
2
3
workbook = xlrd.open_workbook("C:\\Users\lw\Desktop\测试用例.xlsx")
sheets = workbook.sheets()
print(workbook.sheet_loaded(0))

3.sheet对象的基本操作

3.1行操作

①获取所有行数

Rows = sheet.nrows 特别注意，这是属性而不是方法，不加括号。

②获取某行的数据，值为列表形式

Value = sheet.row_values()

1
2
3
4
5
6
7
8
9
workbook = xlrd.open_workbook("C:\\Users\lw\Desktop\测试用例.xlsx")
sheets = workbook.sheets()

worksheet1 = workbook.sheet_by_index(0)
worksheet2 = workbook.sheet_by_name("公司分部")
worksheet3 = workbook.sheets()[0]

value = worksheet1.row_values(1)
print(value)

⑥获取某行的长度

1
2
3
4
5
6
7
8
9
workbook = xlrd.open_workbook("C:\\Users\li\Desktop\测试用例.xlsx")
sheets = workbook.sheets()

worksheet1 = workbook.sheet_by_index(0)
worksheet2 = workbook.sheet_by_name("公司分部")
worksheet3 = workbook.sheets()[0]

value = worksheet1.row_len(1)
print(value)

⑦获取sheet的所有行

Sheet.get_rows()

3.2列操作

①获取有效列数

Sheet.cols 注意：此处为属性不加括号

②获取某列数据

Sheet.values()

③获取某列类型

Sheet.types()

3.3单元格操作

①获取单元格数据对象。 sheet.cell(rowx,colx)类型为xlrd.sheet.Cell

②获取单元格类型。Sheet.cell_type(rowx,colx)

单元类型ctype：empty为0，string为1，number为2，date为3，boolean为4， error为5（左边为类型，右边为类型对应的值）；

③获取单元格数据。

Sheet.cell_value(rowx,colx)

1
2
3
4
5
6
7
import xlrd
 
# 打开Excel文件
workbook = xlrd.open_workbook('filename.xls')
 
# 通过工作表名称获取工作表
worksheet = workbook.sheet_by_name('Sheet1')

4.python读取excel文件内容时报错

raise XLRDError(FILE_FORMAT_DESCRIPTIONS[file_format]+‘； not supported‘)xlrd.

img

解决方法：

xlrd.2.0.1是不支持.xlsx的excel文件的，解决方法有两种：

1、将.xlsx文件格式改为.xls文件格式。

2、将xlrd.2.0.1版本改为xlrd.2.0.0或者xlrd.1.2.0。方法如下：

3、在Terminal 窗口执行命令pip install xlrd==1.2.0,下载好之后重新运行就可以了。

xlwt

1
2
3
4
5
6
# 创建excel对象
workbook = xlwt.Workbook(encoding='utf8')
#新建表格
sheet1 = workbook.add_sheet('output')
#将该对象存储在新建文件里头
workbook.save('output6.xlsx')

进阶练习

实现两个表格的全连接：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
import xlrd
import xlwt

# input 从data.xlsx里读取sheet内容
workbook = xlrd.open_workbook('output5.xlsx')
worksheets = workbook.sheet_names()
print('worksheets is %s' %worksheets)

wb_chigubili = workbook.sheet_by_name('output')
wb_worker = workbook.sheet_by_name('Sheet1')

map_result = {}

# handle
#获得所有行数
chigubili_rows = wb_chigubili.nrows
for curr_row in range(chigubili_rows):
  time_nian = int(wb_chigubili.cell_value(curr_row,0))
  id = int(wb_chigubili.cell_value(curr_row,1))
  name = wb_chigubili.cell_value(curr_row,2)
  bili = wb_chigubili.cell_value(curr_row,3)
  num = wb_chigubili.cell_value(curr_row,4)
  total = wb_chigubili.cell_value(curr_row,5)
  total_avg = wb_chigubili.cell_value(curr_row,6)
  total_TTM = wb_chigubili.cell_value(curr_row,7)
  fuzhailv = wb_chigubili.cell_value(curr_row,8)
  tongjikoujing = wb_chigubili.cell_value(curr_row,9)
  dongshirenshu = wb_chigubili.cell_value(curr_row,10)
  duli = wb_chigubili.cell_value(curr_row,11)
  gaojiguanli = wb_chigubili.cell_value(curr_row,12)
  LargestHolderRate = wb_chigubili.cell_value(curr_row,13)
  TopTenHoldersRate = wb_chigubili.cell_value(curr_row,14)
  dongshijinglijianren = wb_chigubili.cell_value(curr_row,15)
  result = {'time_nian' : time_nian, 'id': id,'name':name,'bili':bili,'num':num,'total': total,'total_avg':total_avg,'total_TTM':total_TTM,'fuzhailv':fuzhailv,'tongjikoujing':tongjikoujing,'dongshirenshu':dongshirenshu,'duli':duli,'gaojiguanli':gaojiguanli,'LargestHolderRate':LargestHolderRate,'TopTenHoldersRate':TopTenHoldersRate,'dongshijinglijianren':dongshijinglijianren}
  map_result[(time_nian,id)] = result

worker_rows = wb_worker.nrows
for curr_row in range(worker_rows):
  id = int(wb_worker.cell_value(curr_row,0))
  time_nian = int(wb_worker.cell_value(curr_row,1))
  dongshihuicishu = wb_worker.cell_value(curr_row,2)
  if (time_nian,id) in map_result :
    result = map_result[(time_nian,id)]
    result['dongshihuicishu']=dongshihuicishu
    
  else:
    result = {'time_nian' : time_nian, 'id' : id,'dongshihuicishu':dongshihuicishu}

  map_result[(time_nian,id)] = result

print(map_result)

# output 将结果输出到新建的excel文件 output.xls
# 创建excel对象
workbook = xlwt.Workbook(encoding='utf8')
#新建表格
sheet1 = workbook.add_sheet('output')

i=0
for value in map_result.values():
  sheet1.write(i,0,value['time_nian'])
  sheet1.write(i,1,value.get('id'))
  sheet1.write(i,2,value.get('name'))
  sheet1.write(i,3,value.get('bili'))
  sheet1.write(i,4,value.get('num'))
  sheet1.write(i,5,value.get('total'))
  sheet1.write(i,6,value.get('total_avg'))
  sheet1.write(i,7,value.get('total_TTM'))
  sheet1.write(i,8,value.get('fuzhailv'))
  sheet1.write(i,9,value.get('tongjikoujing'))
  sheet1.write(i,10,value.get('dongshirenshu'))
  sheet1.write(i,11,value.get('duli'))
  sheet1.write(i,12,value.get('gaojiguanli'))
  sheet1.write(i,13,value.get('LargestHolderRate'))
  sheet1.write(i,14,value.get('TopTenHoldersRate'))
  sheet1.write(i,15,value.get('dongshijinglijianren'))
  sheet1.write(i,16,value.get('dongshihuicishu'))
  i = i+1
workbook.save('output6.xlsx')

筛选表格中以某一元素为键的个数：并打印出来

实例个数为7

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
# -*- coding:utf-8 -*-
import xlrd
import xlwt
infos = []
# 目标文件
report = 'output6.xlsx'

info_file = xlrd.open_workbook(report)
info_sheet = info_file.sheets()[0]
row_count = info_sheet.nrows
print(row_count)
# 得到所有行
infos = []
for i in range(row_count):
    infos.append(info_sheet.row_values(i))

print(infos)
map = {"": []}

for value in infos:
    if value[1] in map:
        map[value[1]].append(value)
    else:
        map[value[1]] = [value]

result = []
for s in map:
    if len(map.get(s)) == 7:
        for v in map.get(s):
            result.append(v)

workbook = xlwt.Workbook(encoding='utf8')
sheet1 = workbook.add_sheet('shaixuanhou')

i = 0
for tuple in result:
    sheet1.write(i, 0, tuple[0])
    sheet1.write(i, 1, tuple[1])
    sheet1.write(i, 2, tuple[2])
    sheet1.write(i, 3, tuple[3])
    sheet1.write(i, 4, tuple[4])
    sheet1.write(i, 5, tuple[5])
    sheet1.write(i, 6, tuple[6])
    sheet1.write(i, 7, tuple[7])
    sheet1.write(i, 8, tuple[8])
    sheet1.write(i, 9, tuple[9])
    sheet1.write(i, 10, tuple[10])
    sheet1.write(i, 11, tuple[11])
    sheet1.write(i, 12, tuple[12])
    sheet1.write(i, 13, tuple[13])
    sheet1.write(i, 14, tuple[14])
    sheet1.write(i, 15, tuple[15])
    sheet1.write(i, 16, tuple[16])
    i = i+1
workbook.save('shaixuan.xlsx')