python xlrd主要功能

This module is part of the xlrd package, which is released under a  BSD-style licence. Basically, use function xlrd.open_workbook to get meta data from a xls file path. Available data types are: 0. empty, 1. string (text), 2. number, 3. date, 4. boolean, 5. error, 6. blank.

1.导入模块与加载某路径的xls文件的数据

import xlrd
book=xlrd.open_workbook("文件路径")
book
<xlrd.book.Book object at 0x00000134918A6F70>

从路径读取的数据为工作簿，每个工作簿可能有多个工作表。其中工作簿常命名为data、workbook、wb、book；工作表常命名为worksheet、sheet、sheets、table、tables等。

2.获取工作簿中的工作簿

共三种方式：1，按照sheet的名字读取，2，按照sheet的index读取，3，读取所有sheets再按index读取

book=xlrd.open_workbook("文件路径")#<class 'xlrd.book.Book'>
<xlrd.book.Book object at 0x00000134918A6F70>
sheet = book.sheet_by_name('Sheet1')#<class 'xlrd.sheet.Sheet'>
sheet = book.sheet_by_index(0)#<class 'xlrd.sheet.Sheet'>
sheets = book.sheets()#<class 'list'>
sheet = book.sheets()[0]#<class 'xlrd.sheet.Sheet'>

3.常用的切片方法

#worksheet的属性
sheet.nrows#获取总行数
sheet.ncols#获取总列数

#方法加_value与否的区别在于 不加是number:1.0 加了是 1.0
#单个单元格
sheet.cell(0,0).value
sheet.cell_value(0,0)
sheet.cell_type(0,0)#返回cell的对象类型
sheet.cell(0,0).ctype#返回cell的对象类型
typeDict = {0:'empty', 1:'string', 2:'number', 3:'date', 4:'boolean', 5:'error',6:'blank'}
'''其中
xlrd.xldate_as_datetime
<function xldate_as_datetime at 0x00000134918B5DC0>
xlrd.xldate_as_tuple
<function xldate_as_tuple at 0x00000134918B5CA0>
用来处理sheet的cell的数据与date格式的转化问题，这个留作读者发现
'''

#输出每行
sheet.row(0)
sheet.row_values(0)
#输出每列
sheet.col(0)
sheet.col_values(0)

4.常用的遍历方法

# 遍历sheet 有列数sheet.ncols 4和行数sheet.nrows 26
for i in sheet:
    print(i)#默认输出行的内容
'''
[number:0.0, number:1.0, number:2.0, number:3.0]#这一行为表头的数据
[number:100.0, number:101.0, number:102.0, number:103.0]
...
[number:196.0, number:197.0, number:198.0, number:199.0]
'''#中间省去了一些行总数据为100-199生成的25×4的数据列
#类似的可以用两层for loop进行单个输出
for i in sheet:
    for j in i:
        print(j)#顺序为先cols in row再rows
'''
number:0.0
...
number:199.0
'''

附：work_sheet包含的非私有方法或属性

#每个work_sheet包含的非私有方法如下：
method = [i for i in dir(wb00) if not i[0]=='_']
for i in method:
    print(i,eval('wb00.'+str(i)))
#其中常见的sheet的属性如下：
name Sheet1
ncols 4
nrows 26
utter_max_cols 256
utter_max_rows 65536
#sheet的非属性的方法或其他用法如下：
book <xlrd.book.Book object at 0x00000134918A6F70>
cell <bound method Sheet.cell of Sheet  0:<Sheet1>>
cell_type <bound method Sheet.cell_type of Sheet  0:<Sheet1>>
cell_value <bound method Sheet.cell_value of Sheet  0:<Sheet1>>
cell_xf_index <bound method Sheet.cell_xf_index of Sheet  0:<Sheet1>>
col <bound method Sheet.col_slice of Sheet  0:<Sheet1>>
col_slice <bound method Sheet.col_slice of Sheet  0:<Sheet1>>
col_types <bound method Sheet.col_types of Sheet  0:<Sheet1>>
col_values <bound method Sheet.col_values of Sheet  0:<Sheet1>>
computed_column_width <bound method Sheet.computed_column_width of Sheet  0:<Sheet1>>
get_rows <bound method Sheet.get_rows of Sheet  0:<Sheet1>>
handle_feat11 <bound method Sheet.handle_feat11 of Sheet  0:<Sheet1>>
handle_hlink <bound method Sheet.handle_hlink of Sheet  0:<Sheet1>>
handle_msodrawingetc <bound method Sheet.handle_msodrawingetc of Sheet  0:<Sheet1>>
handle_note <bound method Sheet.handle_note of Sheet  0:<Sheet1>>
handle_obj <bound method Sheet.handle_obj of Sheet  0:<Sheet1>>
handle_quicktip <bound method Sheet.handle_quicktip of Sheet  0:<Sheet1>>
handle_txo <bound method Sheet.handle_txo of Sheet  0:<Sheet1>>
logfile <debug.tserver.dbgutils.CNotifyingStringIO object at 0x0000013490AD6B80>
put_cell <bound method Sheet.put_cell_unragged of Sheet  0:<Sheet1>>
put_cell_ragged <bound method Sheet.put_cell_ragged of Sheet  0:<Sheet1>>
put_cell_unragged <bound method Sheet.put_cell_unragged of Sheet  0:<Sheet1>>
read <bound method Sheet.read of Sheet  0:<Sheet1>>
req_fmt_info <bound method Sheet.req_fmt_info of Sheet  0:<Sheet1>>
row <bound method Sheet.row of Sheet  0:<Sheet1>>
row_len <bound method Sheet.row_len of Sheet  0:<Sheet1>>
row_slice <bound method Sheet.row_slice of Sheet  0:<Sheet1>>
row_types <bound method Sheet.row_types of Sheet  0:<Sheet1>>
row_values <bound method Sheet.row_values of Sheet  0:<Sheet1>>
string_record_contents <bound method Sheet.string_record_contents of Sheet  0:<Sheet1>>
tidy_dimensions <bound method Sheet.tidy_dimensions of Sheet  0:<Sheet1>>