使用python的xlrd模块读取excel内容
基础操作
测试Excel的内容如下
代码
import xlrd as x local_filepath = r"D:\Python\Students.xlsx" # Open work book, no need to close it wb = x.open_workbook(local_filepath) # Open work sheet ws = wb.sheet_by_index(0) # sheet_by_name # ws = wb.sheet_by_name('Sheet1') # Get row count and column count print("Work sheet row count: {0}, column count: {1}".format(ws.nrows, ws.ncols)) # Get cell value print("Get Cell(0, 0) value:", ws.cell_value(0, 0)) # Get row values for i in range(ws.nrows): print(ws.row_values(i)) # Get rows with generator ws.get_rows() # Get column values for i in range(ws.ncols): print(ws.col_values(i)) ############################################################### # Cell is empty emptycell = ws.cell_value(2, 1) emptycell == '' # Output True # Cell is date from datetime import datetime from xlrd import xldate_as_tuple date = datetime(*xldate_as_tuple(ws.cell_value(2, 3), 0)) datecell = date.strftime('%m/%d/%y %H:%M') print(datecell)
输出
注意知识点:
1. 打开是不需要显示关闭的.
2. Excel的单元格内容可以为空,读出来的值就是空字符 ''
3. Excel是允许使用函数产生特定值的,比如当前日期.
4. xlrd读的单元格内容是有类型的. 具体参考ctype. 类型值是类似1,2,3,4.比如日期就是3.
5. 对于日期格式是需要做特殊处理格式转换,不做处理就是一串浮点数.
伪python爱好者,正宗测试实践者。