import xlrd
import os
from sys import getsizeof
path = os.path.dirname(os.path.realpath(__file__))
# 读取excel表的数据
data = xlrd.open_workbook(os.path.dirname(path) + '\\DATA.xlsx')
# 选取需要读取数据的那一页
sheet1 = data.sheet_by_index(0)
sheet2 = data.sheet_by_index(1)
# 获得行数和列数
rows1 = sheet1.nrows
cols1 = sheet1.ncols
def aaa():
listvalue = []
for i in range(1, rows1):
value = []
for j in range(0, cols1 - 1): # 不读取后1列
# 读取表格内容按行,列取值
'''ctype: 0 empty, 1 string, 2 number, 3 date, 4 boolean, 5 error'''
ctype = sheet1.cell(i, j).ctype # 获取数据的类型
cell = sheet1.cell(i, j).value # 获取数据的值
# 判断Excel数据是否为整型
if ctype == 2 and cell % 1 == 0: # 如果是整形
cell = int(cell)
# 判断Excel数据是否为时间格式
elif ctype == 3:
# 转成datetime对象
from datetime import datetime
from xlrd import xldate_as_tuple
date = datetime(*xldate_as_tuple(cell, 0))
cell = date.strftime('%Y-%d-%m %H:%M:%S')
# 判断Excel数据是否为布尔值
elif ctype == 4:
cell = True if cell == 1 else False
# 存储到数组
value.append(cell)
listvalue.append(value)
return listvalue
'''
返回一个列表
i[0]:参数名;
i[1]:参数值;
i[2]:备注
'''
def getData(value):
for i in aaa():
if i[0] == value:
return str(i[1])
break
# print(getData('test'))
rows2 = sheet2.nrows
cols2 = sheet2.ncols
def bbb():
listvalue = []
for i in range(1, rows2):
value = []
for j in range(0, cols2 - 4): # 不读取后4列
a = sheet2.cell(i, j).value
value.append(a)
listvalue.append(value)
# print(listvalue)
# 查看数据大小,单位:byte
# print(getsizeof(listvalue))
return listvalue
'''
返回一个列表
i[0]:参数名;
i[1]:参数类型;
i[2]:参数值;
i[3]:备注
'''
def getEmt(value):
for i in bbb():
if i[0] == value:
return i
break
# print(getEmt('user'))
python读取Excel文件数据(用于脚本数据驱动)
最新推荐文章于 2023-06-05 21:11:17 发布