Python读取xls(包括读取日期类型)
1、按行读取xls
xls文件为两个表格,内容都是一样的
# -*- encoding=utf-8 -*- import os import xlrd filename = os.path.abspath('readxls.xls') book = xlrd.open_workbook(filename) sheets = book.sheets() print(sheets) # 所有的表对象 names = book.sheet_names() print(names) # 所有的表名 sheet1 = book.sheet_by_index(0) column_rows = sheet1.nrows # 行数 print('行数:{}'.format(column_rows)) column_number = sheet1.ncols # 列数 print('列数:{}'.format(column_number)) for row in range(column_rows): data = sheet1.row_values(row) # 按行读取,读出来是list print(data)
运行截图
2、按列读取xls
# -*- encoding=utf-8 -*- import os import xlrd filename = os.path.abspath('readxls.xls') book = xlrd.open_workbook(filename) sheets = book.sheets() print(sheets) # 所有的表对象 names = book.sheet_names() print(names) # 所有的表名 sheet1 = book.sheet_by_index(0) column_rows = sheet1.nrows # 行数 print('行数:{}'.format(column_rows)) column_number = sheet1.ncols # 列数 print('列数:{}'.format(column_number)) for row in range(column_number): data = sheet1.col_values(row) # 按列读取,读出来是list print(data)
运行截图
3、按单元格读取xls
# -*- encoding=utf-8 -*- import os import xlrd filename = os.path.abspath('readxls.xls') book = xlrd.open_workbook(filename) sheets = book.sheets() print(sheets) # 所有的表对象 names = book.sheet_names() print(names) # 所有的表名 sheet1 = book.sheet_by_index(0) column_rows = sheet1.nrows # 行数 print('行数:{}'.format(column_rows)) column_number = sheet1.ncols # 列数 print('列数:{}'.format(column_number)) for row in range(column_rows): for col in range(column_number): data = sheet1.cell(row, col).value # 按单元格读取 print(data, '\t', end='') print()
运行截图
4、读取日期类型的单元格
修改xls文件中出生年月为日期类型,则读取出来是float
解决办法:通过判断读取出的类型进行处理
读取的xls中类型有 5种:
0 代表empty
1代表 string
2代表 number
3代表 date
4 代表boolean
5 代表error
# -*- encoding=utf-8 -*- import os from datetime import datetime import xlrd from xlrd import xldate_as_tuple filename = os.path.abspath('readxls.xls') book = xlrd.open_workbook(filename) sheets = book.sheets() print(sheets) # 所有的表对象 names = book.sheet_names() print(names) # 所有的表名 sheet1 = book.sheet_by_index(0) column_rows = sheet1.nrows # 行数 print('行数:{}'.format(column_rows)) column_number = sheet1.ncols # 列数 print('列数:{}'.format(column_number)) for row in range(column_rows): for col in range(column_number): # 0 代表empty,1代表 string, 2代表 number, 3代表 date, 4 代表boolean, 5 代表error data_type = sheet1.cell(row, col).ctype # print(data_type, '\t', end='') if data_type == 3: value = sheet1.cell(row, col).value data_time = datetime(*xldate_as_tuple(value, 0)) data = data_time.strftime('%Y-%m-%d') else: data = sheet1.cell(row, col).value # 按单元格读取 print(data, '\t', end='') print()
运行截图