这篇教程Python中的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 Workbook = xlrd.open_workbook("C://Users/li/Desktop/银联测试案例.xls")print(Workbook)
2.2获取xlrd.book.Book 对象中的所有sheet名称Names = Workbook.sheet_names()
workbook = xlrd.open_workbook("C://Users/li/Desktop/测试用例.xlsx")names = workbook.sheet_names()print(names)
2.3获取xlrd.book.Book对象中的所有sheet对象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)
2.4判断xlrd.book.Book对象中某个sheet对象是否导入通过文件名 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))
3.sheet对象的基本操作
3.1行操作①获取所有行数 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)
3.2列操作①获取有效列数 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.colvalue = 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)
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)
3.4对单元格中的日期/时间操作单元类型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 datetimesheet2_object = workbook.sheet_by_index(0)value_type = sheet2_object.cell(0, 1).ctypevalue_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]))
到此这篇关于Python中的xlrd模块使用整理的文章就介绍到这了,更多相关xlrd模块的使用内容请搜索51zixue.net以前的文章或继续浏览下面的相关文章希望大家以后多多支持51zixue.net！
浅谈python中的多态
2021年最新用于图像处理的Python库总结 |
