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'