python工具之exccel模板生成报表

from Db import Db
from log import log
import xlwt
import xlrd
from xlutils.copy import copy
import sys
import os
from sched import sched
from datetime import datetime
import config

sql = '''
'''


@sched.scheduled_job('cron', second='0', minute='0', hour='10,14', id='supplierXlsGen')
# @sched.scheduled_job('cron',second='*/1',id='supplierXlsGen')
def supplierXlsGen():
    try:
        descfilePath = config.descfilePath + datetime.now().strftime('%Y%m%d_%H%M%S')
        if not os.path.exists(descfilePath):
            os.mkdir(descfilePath)
        # 获取所有模板，对模板进行遍历即可
        temlist = os.listdir(sys.path[0] + '/template/')
        for tem in temlist:
            log.info('开始处理%s' % tem)
            # 根据文件名获取供应商id
            id = tem.split('.')[0]
            db = Db()
            # 获取数据源
            data = db.query(sql, (id))
            if len(data) == 0:
                log.info('数据为空，不处理')
                continue
            xls = xlrd.open_workbook(sys.path[0] + '/template/' + tem)
            excel = copy(xls)
            sheets = xls.sheets()
            # 解决多sheet兼容问题
            for tableidx, temtable in enumerate(sheets):
                # table = xls.sheets()[0]
                # 获取模板取的字段
                collist = temtable.row_values(1)
                table = excel.get_sheet(tableidx)
                for idx, d in enumerate(data):
                    for i, col in enumerate(collist):
                        if col != '':
                            # 判断下数据类型
                            if type(d[col]) == datetime:
                                table.write(
                                    idx + 1, i, d[col].strftime('%Y-%m-%d %H:%M:%S'))
                            else:
                                table.write(idx + 1, i, d[col])
            dateStr = datetime.now().strftime('%Y%m%d_%H%M%S')
            # 获取供应商名称
            name = db.queryOne(
                'SELECT * from qmall_supplier where id=%s', (id))['name']
            excel.save(descfilePath + "/%s_%s_%s.xls" % (id, name, dateStr))
        log.info('处理结束')
        tarfileName = datetime.now().strftime('%Y%m%d_%H%M%S') + '.tar'
        os.system('tar -cvPf /mall_image/report/deliverXls/%s %s' %
                  (tarfileName, descfilePath))
    except Exception as e:
        log.error(e)
    log.info('处理完成')