1 from Db import Db
2 from log import log
3 import xlwt
4 import xlrd
5 from xlutils.copy import copy
6 import sys
7 import os
8 from sched import sched
9 from datetime import datetime
10 import config
11
12 sql = '''
13 '''
14
15
16 @sched.scheduled_job('cron', second='0', minute='0', hour='10,14', id='supplierXlsGen')
17 # @sched.scheduled_job('cron',second='*/1',id='supplierXlsGen')
18 def supplierXlsGen():
19 try:
20 descfilePath = config.descfilePath + datetime.now().strftime('%Y%m%d_%H%M%S')
21 if not os.path.exists(descfilePath):
22 os.mkdir(descfilePath)
23 # 获取所有模板，对模板进行遍历即可
24 temlist = os.listdir(sys.path[0] + '/template/')
25 for tem in temlist:
26 log.info('开始处理%s' % tem)
27 # 根据文件名获取供应商id
28 id = tem.split('.')[0]
29 db = Db()
30 # 获取数据源
31 data = db.query(sql, (id))
32 if len(data) == 0:
33 log.info('数据为空，不处理')
34 continue
35 xls = xlrd.open_workbook(sys.path[0] + '/template/' + tem)
36 excel = copy(xls)
37 sheets = xls.sheets()
38 # 解决多sheet兼容问题
39 for tableidx, temtable in enumerate(sheets):
40 # table = xls.sheets()[0]
41 # 获取模板取的字段
42 collist = temtable.row_values(1)
43 table = excel.get_sheet(tableidx)
44 for idx, d in enumerate(data):
45 for i, col in enumerate(collist):
46 if col != '':
47 # 判断下数据类型
48 if type(d[col]) == datetime:
49 table.write(
50 idx + 1, i, d[col].strftime('%Y-%m-%d %H:%M:%S'))
51 else:
52 table.write(idx + 1, i, d[col])
53 dateStr = datetime.now().strftime('%Y%m%d_%H%M%S')
54 # 获取供应商名称
55 name = db.queryOne(
56 'SELECT * from qmall_supplier where id=%s', (id))['name']
57 excel.save(descfilePath + "/%s_%s_%s.xls" % (id, name, dateStr))
58 log.info('处理结束')
59 tarfileName = datetime.now().strftime('%Y%m%d_%H%M%S') + '.tar'
60 os.system('tar -cvPf /mall_image/report/deliverXls/%s %s' %
61 (tarfileName, descfilePath))
62 except Exception as e:
63 log.error(e)
64 log.info('处理完成')
