本文主要是介绍xlrd部分类方法说明，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
使用python读取excle，需安装第三方模块xlrd。
下载地址：
https://pypi.python.org/pypi/xlrd
安装过程略，安装成功后，就可调用xlrd的方法进行去读EXCEL文件操作
显示单元格名称
print xlrd.cellname(0,0)
>>> A1
cellnameabs(rowx, colx, r1c1=0)
格式化显示单元格名称
print xlrd.cellnameabs(0,0,r1c1=0)
>>> $A$1
显示列名称
print xlrd.colname(0)
>>> A
空单元格
print xlrd.empty_cell()
>>> empty:''
error_text_from_code (variable)
报错信息
print xlrd.error_text_from_code
print xlrd.error_text_from_code[7]
>>> {0: '#NULL!', 36: '#NUM!', 7: '#DIV/0!', 42: '#N/A', 15: '#VALUE!', 23: '#REF!', 29: '#NAME?'}
>>> #DIV/0!
open_workbook(filename=None, logfile=sys.stdout, verbosity=0, pickleable=True,use_mmap=USE_MMAP,file_contents=None, encoding_override=None, formatting_info=False, on_demand=False, ragged_rows=False, )
打开一个EXCEL文件，返回一个Book类的实例
book = xlrd.open_workbook(r"C:\Users\Administrator\Desktop\506\test.xls")
print book
>>> <xlrd.book.Book object at 0x01D0F810>
xldate_as_tuple(xldate, datemode)
转换数值为时间格式。0:1900-base 1:1904-based
print table1.cell(1,1).value
print xlrd.xldate_as_tuple(table1.cell(1,1).value, 0)
>>> 200.0
>>> (1900, 7, 18, 0, 0, 0)
BIFF (Binary Interchange File Format)版本。最新的为80，支持EXCEL97.
book = xlrd.open_workbook(r"C:\Users\Administrator\Desktop\506\test.xls")
print book.biff_version
>>> 80
显示文档使用字符编码 1200 代表 Unicode
同样显示字符编码
print book.codepage
print book.encoding
>>> 1200
>>> tuf_16_le
文件总sheet数
book = xlrd.open_workbook(r"C:\Users\Administrator\Desktop\506\test.xls")
print book.nsheets
>>> 3
通过序号索引sheet页，返回一个Sheet类的实例
book = xlrd.open_workbook(r"C:\Users\Administrator\Desktop\506\test.xls")
table=[]
for i in range(book.nsheets):
table.append(book.sheet_by_index(i))
print table[i]
>>> <xlrd.sheet.Sheet object at 0x01BD3270>
>>> <xlrd.sheet.Sheet object at 0x01BD3570>
>>> <xlrd.sheet.Sheet object at 0x01BD35D0>
通过页名称索引sheet页，返回一个Sheet类的实例
book = xlrd.open_workbook(r"C:\Users\Administrator\Desktop\506\test.xls")
table = book.sheet_by_name("Sheet1")
print table
>>> <xlrd.sheet.Sheet object at 0x01B882B0>
sheet_loaded(sheet_name_or_index)
通过名称或编号，载入sheet。成功则返回True,失败则报错
book = xlrd.open_workbook(r"C:\Users\Administrator\Desktop\506\test.xls")
table0 = book.sheet_loaded(0)
table1 = book.sheet_loaded("Sheet2")
print table0,table1
>>> True True
返回sheet名称列表
name = book.sheet_names()
print name
>>> [u'Sheet1', u'Sheet2', u'Sheet3']
book = xlrd.open_workbook(r"C:\Users\Administrator\Desktop\506\test.xls")
table=[]
name = book.sheet_names()
for n in name:
table.append(book.sheet_by_name(n))
print table
>>> [<xlrd.sheet.Sheet object at 0x01C23270>, <xlrd.sheet.Sheet object at 0x01C23570>, <xlrd.sheet.Sheet object at 0x01C235D0>]
返回所有sheet，返回值为Sheet的实例
book = xlrd.open_workbook(r"C:\Users\Administrator\Desktop\506\test.xls")
table = book.sheets()
print table
>>> [<xlrd.sheet.Sheet object at 0x01C8A290>, <xlrd.sheet.Sheet object at 0x01C8A590>, <xlrd.sheet.Sheet object at 0x01C8A5F0>]
unload_sheet(sheet_name_or_index)
通过名称或编号，卸载sheet。成功则返回None,失败则报错
book = xlrd.open_workbook(r"C:\Users\Administrator\Desktop\506\test.xls")
print book.unload_sheet(2)
sheet类实例调用所属的Book对象属性
book = xlrd.open_workbook(r"C:\Users\Administrator\Desktop\506\test.xls")
sheet = book.sheet_by_index(0)
print sheet.book.nsheets
>>> 3
根据横纵值，返回cell对象
cell_value(rowx, colx)
根据横纵值，返回cell值
book = xlrd.open_workbook(r"C:\Users\Administrator\Desktop\506\test.xls")
sheet = book.sheet_by_index(0)
print sheet.cell(0,0)
print sheet.cell_value(0,0)
>>> text:u'\u59d3\u540d'
>>> 姓名
col(colx)
返回指定列的cell对象列表
book = xlrd.open_workbook(r"C:\Users\Administrator\Desktop\506\test.xls")
sheet = book.sheet_by_index(0)
print sheet.col(0)
>>> [text:u'\u59d3\u540d', text:u'\u5f20\u4e09', text:u'\u674e\u56db', text:u'\u738b\u4e94']
col_values(colx, start_rowx=0, end_rowx=None)
查询指定列的值，返回list对象
book = xlrd.open_workbook(r"C:\Users\Administrator\Desktop\506\test.xls")
sheet = book.sheet_by_index(0)
print sheet.col_values(0)
>>> [u'\u59d3\u540d', u'\u5f20\u4e09', u'\u674e\u56db', u'\u738b\u4e94']
computed_column_width(colx)
查询指定列宽，需要打开列表属性formatting_info=True
book=xlrd.open_workbook(r"C:\Users\Administrator\Desktop\506\test.xls",formatting_info=True)
sheet = book.sheet_by_index(0)
print sheet.computed_column_width(0)
>>> 2656
name
查询表名
ncols
查询列数
nrows
查询行数
book=xlrd.open_workbook(r"C:\Users\Administrator\Desktop\506\test.xls",formatting_info=True)
sheet = book.sheet_by_index(0)
print sheet.name
print sheet.ncols
print sheet.nrows
>>> sheet1
>>> 5
>>> 4
row(rowx)
查询指定行，返回cell类实例列表
row_len(rowx)
查询指定行的列数
row_values(rowx, start_colx=0, end_colx=None)
查询指定行的值
book=xlrd.open_workbook(r"C:\Users\Administrator\Desktop\506\test.xls",formatting_info=True)
sheet = book.sheet_by_index(0)
print sheet.row(0)
print sheet.row_len(0)
print sheet.row_values(0)
>>> [text:u'\u59d3\u540d' (XF:15), text:u'\u5e74\u9f84' (XF:15), text:u'\u5de5\u4f5c' (XF:15), text:u'\u5de5\u8d44' (XF:15), text:u'\u5de5\u65f6' (XF:15)]
>>> 5
>>> [u'\u59d3\u540d', u'\u5e74\u9f84', u'\u5de5\u4f5c', u'\u5de5\u8d44', u'\u5de5\u65f6']
通过Sheet类的方法可以获得cell对象，cell对象有三个属性
ctype int类型 代表文本格式
| Type symbol | Type number | Python value |
| XL_CELL_EMPTY | 0 | empty string u'' |
| XL_CELL_TEXT | 1 | a Unicode string |
| XL_CELL_NUMBER | 2 | float |
| XL_CELL_DATE | 3 | float |
| XL_CELL_BOOLEAN | 4 | int; 1 means TRUE, 0 means FALSE |
| XL_CELL_ERROR | 5 | int representing internal Excel codes; for a text representation, refer to the supplied dictionary error_text_from_code |
| XL_CELL_BLANK | 6 | empty string u''. Note: this type will appear only when open_workbook(..., formatting_info=True) is used. |
value 文本内容，取决于ctype格式
xf_index 如果打开文件时 formatting_info不为True,则xf_index为None
merged_cells
返回sheet中合并单元格位置，返回值为list,每个单元格信息为元祖，4个元素代表开始行，结束行+1，开始列，结束列+1
sheet = book.sheet_by_name('table2')
row_lo,row_hi,col_lo,col_hi = sheet.merged_cells[0]
print sheet.cell(row_lo,col_lo).value
print sheet.merged_cells
>>> 3 7 2 5
>>> MERGED
>>> [(3, 7, 2, 5), (8, 10, 2, 4)]
这篇关于xlrd部分类方法说明的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
