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()

运行截图