xlrd是读取excel表格数据；
支持 xlsx和xls 格式的excel表格；
三方模块安装方式：pip3 install xlrd；
模块导入方式： import xlrd
Xler的操作主要分两步：
其一时获取book对象，
其二book对象再次进行excel的读取操作。
xlrd.open_workbook(filename[,logfile,file_contents,…])
如果filename 文件名不存在，则会报错 FilenotFoundError。
如果filename 文件名存在，则会返回一个xrld.book.Book 对象。 import xlrd
Workbook = xlrd.open_workbook("C:\\Users\li\Desktop\银联测试案例.xls")
print(Workbook)
Names = Workbook.sheet_names()
workbook = xlrd.open_workbook("C:\\Users\li\Desktop\测试用例.xlsx")
names = workbook.sheet_names()
print(names)
Sheets = workbook.sheets()
workbook = xlrd.open_workbook("C:\\Users\li\Desktop\测试用例.xlsx")
names = workbook.sheets()
print(names)
获取单个的sheet页对象
三种方式 ：
第一种 worksheet1 = workbook.sheet_by_index()
第二种 worksheet2 = workbook.sheet_by_name()
第三种 worksheet3 = workbook.sheets()[0]
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]
print(worksheet1,worksheet2,worksheet3)
通过文件名
workbook = xlrd.open_workbook("C:\\Users\lw\Desktop\测试用例.xlsx")
sheets = workbook.sheets()
print(workbook.sheet_loaded("公司分部"))
通过索引
workbook = xlrd.open_workbook("C:\\Users\lw\Desktop\测试用例.xlsx")
sheets = workbook.sheets()
print(workbook.sheet_loaded(0))
①获取所有行数
Rows = sheet.nrows 特别注意，这是属性而不是方法，不加括号。
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]
print(worksheet1.nrows)
②获取某行的数据，值为列表形式
Value = sheet.row_values()
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)
③获取某行的类型及数据
Sheet.row()
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(1)
print(value)
④获取某行的类型的列表
Sheet.row_types()
单元类型ctype：empty为0，string为1，number为2，date为3，boolean为4， error为5（左边为类型，右边为类型对应的值）；
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_types(1)
print(value)
⑤以切片形式获取某行的类型及数据
Sheet.row_slice() 记录分隔符为\n
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_slice(1)
print(value)
⑥获取某行的长度
Sheet.len()
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()
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]
row = worksheet1.get_rows()
for one in row:
print(one)
①获取有效列数
Sheet.cols 注意：此处为属性不加括号
②获取某列数据
Sheet.values()
③获取某列类型
Sheet.types()
单元类型ctype：empty为0，string为1，number为2，date为3，boolean为4， error为5（左边为类型，右边为类型对应的值）；
④以slice切片方式获取某列数据
Sheet.value_slice()
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]
cols = worksheet1.col
value = worksheet1.col_values(0)
type = worksheet1.col_types(0)
valuesl = worksheet1.col_slice(0)
print(cols)
print("----------------------")
print(value)
print("----------------------")
print(type)
print("----------------------")
print(valuesl)
①获取单元格数据对象。 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)
单元类型ctype：empty为0，string为1，number为2，date为3，boolean为4， error为5（左边为类型，右边为类型对应的值）；
①xlrd.xldate_as_tuple()
“{}-{:0>2}-{:0>2}”.format(date[0],date[1],date[2])
②xlrd.xldate_as_datetime(value,mode).strftime(“%Y-%m-%d”)
workbook = xlrd.open_workbook("C:\\Users\li\Desktop\测试用例.xlsx")
import datetime
sheet2_object = workbook.sheet_by_index(0)
value_type = sheet2_object.cell(0, 1).ctype
value_type = sheet2_object.cell_value(1, 4)
data = xlrd.xldate.xldate_as_datetime(value_type,0)
print(data.strftime("%Y-%m-%d"))
date = xlrd.xldate.xldate_as_tuple(value_type,0)
print("{}-{:0>2}-{:0>2}".format(date[0],date[1],date[2]))
