import xlrd,re
from datetime import datetime
from xlrd import xldate_as_tuple # 判断上传表格是否与模板要求一致
def checkExcel(path,field_attr):
#1.打开当前excel表格
data = xlrd.open_workbook(path)
now_table = data.sheet_by_index(0)
#当前表格的行数
rows_numn = now_table.nrows #2.1验证长度
table_index = now_table.row_values(0)
if len(field_attr) != len(table_index):
return False,'表格行数应为{0}行'.format(len(field_attr))
else:
#2.2验证内容
flag = True
msg = ''
for k in range(1, rows_numn):
# 2.2.1验证内容12,13是否马满足时间格式%y-%m-%d %H
start_time = now_table.cell(k,12)
end_time = now_table.cell(k,13)
pattern = r"(\d{4}-\d{2}-\d{1,2}\s\d{2})"
if re.match(pattern,start_time) == None:
flag = False
msg += '第{0}行第{1}列格式用为 2018-01-01 00 格式'.format(k,12+1)
if re.match(pattern,end_time) == None:
flag = False
msg += '第{0}行第{1}列格式用为 2018-01-01 00 格式'.format(k,13+1)
if len(msg) >=100:
msg += '.....'
break
return flag, msg #数据库表添加一条数据的方法
def addObj(obj):
pass#不同数据实现方法不一样 #将表格数据插入数据库 path上传表格路径 field_attr值表格行数对应字段如{0:'name',1:'age',2:'sex'}
def exportExcel(path,field_attr):
#1.检查表格
flag,msg = checkExcel(path,field_attr)
if not flag:
return msg
else:#通过验证
# 2.读取excel
data = xlrd.open_workbook(path)
now_table = data.sheet_by_index(0)
# 获得当前表格的行数
rows_numn = now_table.nrows
# 将当前的sheet插入到数据库
for k in range(1, rows_numn):
row_vlaue = now_table.row_values(k)
obj = {}
# 处理要插入的数据,把非字符串的数据转换成字符串类型,同事将字符串变成 sql语句需要的类型 for a in range(0, len(row_vlaue)):
ctype = now_table.cell(k, a).ctype
# ctype: 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error
if ctype == 1:
row_vlaue[a] = row_vlaue[a].replace("'","/'")
if ctype == 2 and row_vlaue[a] % 1 == 0:
tmp = int(row_vlaue[a])
row_vlaue[a] = str(tmp)
if ctype == 3:
d = datetime(*xldate_as_tuple(row_vlaue[a], 0))
row_vlaue[a] = d.strftime('%Y-%m-%d %H')
obj[field_attr[a]] = row_vlaue[a]
#3.将该行数据插入数据库
try:
addObj(obj)
except:
return '第{0}行数据插入失败'.format(k)
return 'ok'
支持.xlsx结尾的excel文件