Pythonスクリプト:excelテーブルをデータベースにインポートし、mysql、postgresql、MongoDBをサポート

 1 import xlrd,re
 2 from datetime import datetime
 3 from xlrd import xldate_as_tuple
 4 
 5 
 6 #                
 7 def checkExcel(path,field_attr):
 8     #1.    excel  
 9     data = xlrd.open_workbook(path)
10     now_table = data.sheet_by_index(0)
11     #       
12     rows_numn = now_table.nrows
13 
14     #2.1    
15     table_index = now_table.row_values(0)
16     if len(field_attr) != len(table_index):
17         return False,'      {0} '.format(len(field_attr))
18     else:
19         #2.2    
20         flag = True
21         msg = ''
22         for k in range(1, rows_numn):
23             # 2.2.1    12,13         %y-%m-%d %H
24             start_time = now_table.cell(k,12)
25             end_time = now_table.cell(k,13)
26             pattern = r"(\d{4}-\d{2}-\d{1,2}\s\d{2})"
27             if re.match(pattern,start_time) == None:
28                 flag = False
29                 msg += ' {0}  {1}      2018-01-01 00   '.format(k,12+1)
30             if re.match(pattern,end_time) == None:
31                 flag = False
32                 msg += ' {0}  {1}      2018-01-01 00   '.format(k,13+1)
33             if len(msg) >=100:
34                 msg += '.....'
35                 break
36         return flag, msg
37 
38 
39 
40 #             
41 def addObj(obj):
42     pass#           
43 
44 
45 
46 #           path       field_attr          {0:'name',1:'age',2:'sex'}
47 def exportExcel(path,field_attr):
48     #1.    
49     flag,msg = checkExcel(path,field_attr)
50     if not flag:
51         return msg
52     else:#    
53         # 2.  excel
54         data = xlrd.open_workbook(path)
55         now_table = data.sheet_by_index(0)
56         #          
57         rows_numn = now_table.nrows
58         #     sheet      
59         for k in range(1, rows_numn):
60             row_vlaue = now_table.row_values(k)
61             obj = {}
62             #         ，                ，         sql       
63 
64             for a in range(0, len(row_vlaue)):
65                 ctype = now_table.cell(k, a).ctype
66                 # ctype： 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error
67                 if ctype == 1:
68                     row_vlaue[a] = row_vlaue[a].replace("'","/'")
69                 if ctype == 2 and row_vlaue[a] % 1 == 0:
70                     tmp = int(row_vlaue[a])
71                     row_vlaue[a] = str(tmp)
72                 if ctype == 3:
73                     d = datetime(*xldate_as_tuple(row_vlaue[a], 0))
74                     row_vlaue[a] = d.strftime('%Y-%m-%d %H')
75                 obj[field_attr[a]] = row_vlaue[a]
76             #3.          
77             try:
78                 addObj(obj)
79             except:
80                 return ' {0}       '.format(k)
81         return 'ok'

支持するxlsx末尾のexcelファイル
転載先:https://www.cnblogs.com/lelexiu/p/10191888.html