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('处理完成')