【详解】Python带历史数据写Excel表

本例中，我们需要写一个统计数据的脚本。

每个月的数据在一张表中，即，每个月的第一天，需要重新开始写一张新的表。

也是比较轻车熟路的脚本，我们只对这个过程中遇到的问题进行记录与思考。

1. 【write_merge函数的使用】

本例我们的Excel模板函数如下：

def createTemplateExcel():    '''创建Excel文件模板'''    wb = xlwt.Workbook(encoding = "UTF-8", style_compression = True)    sht0 = wb.add_sheet("sheet1", cell_overwrite_ok = True)    sht0.col(1).width=3500    sht0.col(9).width=3500    sht0.col(10).width=3500    sht0.col(17).width=3500    sht0.write_merge(0, 1, 0, 0, '日期', style1)    sht0.write_merge(0, 1, 1, 1, '参与签到人数', style1)    sht0.write_merge(0, 0, 2, 9, '积分奖励情况', style1)    sht0.write(1, 2, '5积分', style1)    sht0.write(1, 3, '10积分', style1)    sht0.write(1, 4, '15积分', style1)    sht0.write(1, 5, '20积分', style1)    sht0.write(1, 6, '25积分', style1)    sht0.write(1, 7, '30积分', style1)    sht0.write(1, 8, '35积分', style1)    sht0.write(1, 9, '积分发放总量', style1)    sht0.write_merge(0, 1, 10, 10, '参与抽奖人数', style1)    sht0.write_merge(0, 0, 11, 16, '抽奖奖励情况', style1)    sht0.write(1, 11, '再来一次', style1)    sht0.write(1, 12, '1元', style1)    sht0.write(1, 13, '1.5元', style1)    sht0.write(1, 14, '2元', style1)    sht0.write(1, 15, '5元', style1)    sht0.write(1, 16, '10元', style1)    sht0.write(1, 17, '奖励发放总量', style1)    wb.save(tempFileName)

其中用到了write_merge函数。

sht0.write_merge(0, 1, 0, 0, '日期', style1)sht0.write_merge(0, 1, 1, 1, '参与签到人数', style1)sht0.write_merge(0, 0, 2, 9, '积分奖励情况', style1)

这个函数的参数设置有些绕，所以着重介绍一下。

如上这三行代码运行的效果如下：

write_merge函数。。的参数，一三为一组，二四为一组。

一三表示，起始位置。二四，表示多跨越的行数与列数  与  起始位置相加的结果。

日期：0，0开始，多跨1行0列，相加得1,0

参与签到人数：0，1开始，多跨1行0列，相加得1,1

积分奖励情况：0，2开始，多跨0行7列，相加得0,9

最终结果就是代码中的数字了。需要着重注意的就是，二四参数是和！

2.【风格中设置居中】

我们在写积分奖励情况的表头时，一开始它是左对齐的，我们需要它居中。需要在风格中进行设置。

居中的设置如下三句：

alignment = xlwt.Alignment()alignment.horz = xlwt.Alignment.HORZ_CENTERstyle1.alignment = alignment

这里是水平居中，如果需要垂直居中的话。horz改为vert就可以了！

其实可以这么写的

alignment = xlwt.Alignment()alignment.horz = xlwt.Alignment.HORZ_CENTERalignment.vert = xlwt.Alignment.VERT_CENTER

3.【月初新表开始的判断逻辑】

根据这个脚本的需求，我们需要在每个月开始的时候新建一张表出来。

第一天写入第一天的数据，第二天写入第一天与第二天的数据，第三天写入一二三天的数据。。。

到了下一个月的时候，再写一张新表出来。

即，每个月月初需要进行一个判断。是以昨天的数据为基础模板写数据呢？还是重新开始写一张新表呢？

最终的代码如下：

def writeExcel():    '''写报表'''    rowIndex = handleDate.day+1    if handleDate.day == 1:        createTemplateExcel()        readFile = tempFileName    else:        if os.path.exists(beforeFilePath):            readFile = beforeFilePath        else:            createTemplateExcel()            readFile = tempFileName    rb = xlrd.open_workbook(readFile, on_demand = True, formatting_info = True)    wb = copy(rb)

逻辑是，如果是月初第一天。新建一个模板，并将此作为基础。

如果不是第一天，检测是否有昨天的数据存在，如果存在，以昨天的数据表作为基础模板；如果不存在，也是重新写一张表出来。

接下来的wb，都来自于这个readFile的rb。

判断的就是这个readFile来自于哪儿。其实算是比较简单的逻辑的。

4.【Python写Excel表时的copy数据缺陷】

我们在写一系列表的过程中遇到过一个问题。

生成的数据表中，有一项数据，除了当天的数据有，前面的数据都是空白。而翻到前几天的数据表，也是只有当天的该数据。

这项有空白异常的数据，我们发现是经过了Excel表的函数计算得来的。

造成空白的原因是，Python写第二天的脚本的时候，以昨天的数据为基础，copy数据的时候没有把表中的Excel函数copy过来。也没有办法copy过来。这是Python写Excel表的一个缺陷。

解决办法是：每次在写入死数据以后，再进行计算写入。

于是这里就需要一个循环了，因为每次的这项数据对应的行列号是不一样的，根据天数来变化的，自然要进行遍历。

for i in range(3, rowIndex+2):    sheet1.write(i-1, 11, xlwt.Formula('K%s-M%s-N%s-O%s-P%s-Q%s'%(i, i, i, i, i, i)), style1)

注意这里参数的引入方式，直接在字符串后面跟%号。

5. 【整个脚本的代码结构问题】

先贴出最终版线上的代码。

#!/usr/bin/python# -*- coding: UTF-8 -*-__author__ = "$Author: wangxin.xie$"__version__ = "$Revision: 1.0 $"__date__ = "$Date: 2015-12-17 09:40$"################################################################功能： 微信公众号签到、抽奖活动数据报表,每天上午9:00发送###############################################################import osimport sysimport datetimeimport xlwtimport xlrdfrom xlutils.copy import copyfrom myyutil.DBUtil import DBUtilfrom myyutil.ScriptExecuteUtil import ScriptExecuteUtilreload(sys)sys.setdefaultencoding('utf8')#######################全局变量####################################pointDBUtil = DBUtil('moyoyo_point')ngTradeDBUtil = DBUtil('netgame_trade')delayday = 1today = datetime.datetime.today()todayStr = datetime.datetime.strftime(today, "%Y-%m-%d")handleDate = today - datetime.timedelta(delayday)handleStr = datetime.datetime.strftime(handleDate, "%Y-%m-%d")beforeDate = handleDate - datetime.timedelta(days = 1)execute = ScriptExecuteUtil(handleDate = handleDate)fileDir = '/usr/local/bin/myy_script/report/data/'fileName = fileDir + execute._reportNametempFileName = fileDir + 'wechat_account_info_report_temp.xls'beforeFilePath = fileDir + execute.getReportFileName(beforeDate)style1 = xlwt.XFStyle()font1 = xlwt.Font()font1.height = 220font1.name = 'SimSun'style1.font = font1alignment = xlwt.Alignment()alignment.horz = xlwt.Alignment.HORZ_CENTERstyle1.alignment = alignment##################################################################def writeExcel():    '''写报表'''    rowIndex = handleDate.day+1    if handleDate.day == 1:        createTemplateExcel()        readFile = tempFileName    else:        if os.path.exists(beforeFilePath):            readFile = beforeFilePath        else:            createTemplateExcel()            readFile = tempFileName    rb = xlrd.open_workbook(readFile, on_demand = True, formatting_info = True)    wb = copy(rb)    sheet1 = wb.get_sheet(0)    sheet1.write(rowIndex, 0, handleStr.split('-')[1]+'月'.decode('utf-8')+handleStr.split('-')[2]+'日'.decode('utf-8'), style1)    sheet1.write(rowIndex, 1, genTotalAttendance(), style1)    sheet1.write(rowIndex, 2, gen5pointsNumber(), style1)    sheet1.write(rowIndex, 3, gen10pointsNumber(), style1)    sheet1.write(rowIndex, 4, gen15pointsNumber(), style1)    sheet1.write(rowIndex, 5, gen20pointsNumber(), style1)    sheet1.write(rowIndex, 6, gen25pointsNumber(), style1)    sheet1.write(rowIndex, 7, gen30pointsNumber(), style1)    sheet1.write(rowIndex, 8, gen35pointsNumber(), style1)    sheet1.write(rowIndex, 9, genSumPointsNumber(), style1)    sheet1.write(rowIndex, 10, genParticipantNumber(), style1)    sheet1.write(rowIndex, 12, gen1yuanNumber(), style1)    sheet1.write(rowIndex, 13, gen1point5yuanNumber(), style1)    sheet1.write(rowIndex, 14, gen2yuanNumber(), style1)    sheet1.write(rowIndex, 15, gen5yuanNumber(), style1)    sheet1.write(rowIndex, 16, gen10yuanNumber(), style1)    sheet1.write(rowIndex, 17, genTotalRewordNumber(), style1)    for i in range(3, rowIndex+2):        sheet1.write(i-1, 11, xlwt.Formula('K%s-M%s-N%s-O%s-P%s-Q%s'%(i, i, i, i, i, i)), style1)    wb.save(fileName)def createTemplateExcel():    '''创建Excel文件模板'''    wb = xlwt.Workbook(encoding = "UTF-8", style_compression = True)    sht0 = wb.add_sheet("sheet1", cell_overwrite_ok = True)    sht0.col(1).width=3500    sht0.col(9).width=3500    sht0.col(10).width=3500    sht0.col(17).width=3500    sht0.write_merge(0, 1, 0, 0, '日期', style1)    sht0.write_merge(0, 1, 1, 1, '参与签到人数', style1)    sht0.write_merge(0, 0, 2, 9, '积分奖励情况', style1)    sht0.write(1, 2, '5积分', style1)    sht0.write(1, 3, '10积分', style1)    sht0.write(1, 4, '15积分', style1)    sht0.write(1, 5, '20积分', style1)    sht0.write(1, 6, '25积分', style1)    sht0.write(1, 7, '30积分', style1)    sht0.write(1, 8, '35积分', style1)    sht0.write(1, 9, '积分发放总量', style1)    sht0.write_merge(0, 1, 10, 10, '参与抽奖人数', style1)    sht0.write_merge(0, 0, 11, 16, '抽奖奖励情况', style1)    sht0.write(1, 11, '再来一次', style1)    sht0.write(1, 12, '1元', style1)    sht0.write(1, 13, '1.5元', style1)    sht0.write(1, 14, '2元', style1)    sht0.write(1, 15, '5元', style1)    sht0.write(1, 16, '10元', style1)    sht0.write(1, 17, '奖励发放总量', style1)    wb.save(tempFileName)def genTotalAttendance():    '''参与签到总人数'''    sql = '''        SELECT COUNT(DISTINCT MEMBER_ID)        FROM moyoyo_point.MEMBER_POINT_HISTORY        WHERE CREATED_DATE >= '%s'        AND CREATED_DATE < '%s'        AND TYPE = 1005;    ''' %(handleStr, todayStr)    rs = pointDBUtil.queryCount(sql, ())    if not rs: return 0    return rsdef gen5pointsNumber():    '''5积分人数'''    sql = '''        SELECT COUNT(DISTINCT MEMBER_ID)        FROM moyoyo_point.MEMBER_POINT_HISTORY        WHERE CREATED_DATE >= '%s'        AND CREATED_DATE < '%s'        AND POINT = 5        AND TYPE = 1005;    ''' %(handleStr, todayStr)    rs = pointDBUtil.queryCount(sql, ())    if not rs: return 0    return rsdef gen10pointsNumber():    '''10积分人数'''    sql = '''        SELECT COUNT(DISTINCT MEMBER_ID)        FROM moyoyo_point.MEMBER_POINT_HISTORY        WHERE CREATED_DATE >= '%s'        AND CREATED_DATE < '%s'        AND POINT = 10        AND TYPE = 1005;    ''' %(handleStr, todayStr)    rs = pointDBUtil.queryCount(sql, ())    if not rs: return 0    return rsdef gen15pointsNumber():    '''15积分人数'''    sql = '''        SELECT COUNT(DISTINCT MEMBER_ID)        FROM moyoyo_point.MEMBER_POINT_HISTORY        WHERE CREATED_DATE >= '%s'        AND CREATED_DATE < '%s'        AND POINT = 15        AND TYPE = 1005;    ''' %(handleStr, todayStr)    rs = pointDBUtil.queryCount(sql, ())    if not rs: return 0    return rsdef gen20pointsNumber():    '''20积分人数'''    sql = '''        SELECT COUNT(DISTINCT MEMBER_ID)        FROM moyoyo_point.MEMBER_POINT_HISTORY        WHERE CREATED_DATE >= '%s'        AND CREATED_DATE < '%s'        AND POINT = 20        AND TYPE = 1005;    ''' %(handleStr, todayStr)    rs = pointDBUtil.queryCount(sql, ())    if not rs: return 0    return rsdef gen25pointsNumber():    '''25积分人数'''    sql = '''        SELECT COUNT(DISTINCT MEMBER_ID)        FROM moyoyo_point.MEMBER_POINT_HISTORY        WHERE CREATED_DATE >= '%s'        AND CREATED_DATE < '%s'        AND POINT = 25        AND TYPE = 1005;    ''' %(handleStr, todayStr)    rs = pointDBUtil.queryCount(sql, ())    if not rs: return 0    return rsdef gen30pointsNumber():    '''30积分人数'''    sql = '''        SELECT COUNT(DISTINCT MEMBER_ID)        FROM moyoyo_point.MEMBER_POINT_HISTORY        WHERE CREATED_DATE >= '%s'        AND CREATED_DATE < '%s'        AND POINT = 30        AND TYPE = 1005;    ''' %(handleStr, todayStr)    rs = pointDBUtil.queryCount(sql, ())    if not rs: return 0    return rsdef gen35pointsNumber():    '''35积分人数'''    sql = '''        SELECT COUNT(DISTINCT MEMBER_ID)        FROM moyoyo_point.MEMBER_POINT_HISTORY        WHERE CREATED_DATE >= '%s'        AND CREATED_DATE < '%s'        AND POINT = 35        AND TYPE = 1005;    ''' %(handleStr, todayStr)    rs = pointDBUtil.queryCount(sql, ())    if not rs: return 0    return rsdef genSumPointsNumber():    '''积分发放总量'''    sql = '''        SELECT SUM(POINT)        FROM moyoyo_point.MEMBER_POINT_HISTORY        WHERE CREATED_DATE >= '%s'        AND CREATED_DATE < '%s'        AND TYPE = 1005;    ''' %(handleStr, todayStr)    rs = pointDBUtil.queryCount(sql, ())    if not rs: return 0    return rsdef genParticipantNumber():    '''参与抽奖总人数'''    sql = '''        SELECT COUNT(DISTINCT MEMBER_ID)        FROM moyoyo_point.MEMBER_POINT_HISTORY        WHERE CREATED_DATE >= '%s'        AND CREATED_DATE < '%s'        AND TYPE = 1004;    ''' %(handleStr, todayStr)    rs = pointDBUtil.queryCount(sql, ())    if not rs: return 0    return rsdef gen1yuanNumber():    '''1元奖励人数'''    sql = '''        SELECT COUNT(DISTINCT MEMBER_ID)        FROM netgame_trade.BLOCKED_FUND        WHERE CHARGE_DATE >= '%s'        AND CHARGE_DATE < '%s'        AND CHARGE_TYPE = 1005        AND CHARGE_AMOUNT = 1;    ''' %(handleStr, todayStr)    rs = ngTradeDBUtil.queryCount(sql, ())    if not rs: return 0    return rsdef gen1point5yuanNumber():    '''1.5元奖励人数'''    sql = '''        SELECT COUNT(DISTINCT MEMBER_ID)        FROM netgame_trade.BLOCKED_FUND        WHERE CHARGE_DATE >= '%s'        AND CHARGE_DATE < '%s'        AND CHARGE_TYPE = 1005        AND CHARGE_AMOUNT = 1.5;    ''' %(handleStr, todayStr)    rs = ngTradeDBUtil.queryCount(sql, ())    if not rs: return 0    return rsdef gen2yuanNumber():    '''2元奖励人数'''    sql = '''        SELECT COUNT(DISTINCT MEMBER_ID)        FROM netgame_trade.BLOCKED_FUND        WHERE CHARGE_DATE >= '%s'        AND CHARGE_DATE < '%s'        AND CHARGE_TYPE = 1005        AND CHARGE_AMOUNT = 2;    ''' %(handleStr, todayStr)    rs = ngTradeDBUtil.queryCount(sql, ())    if not rs: return 0    return rsdef gen5yuanNumber():    '''5元奖励人数'''    sql = '''        SELECT COUNT(DISTINCT MEMBER_ID)        FROM netgame_trade.BLOCKED_FUND        WHERE CHARGE_DATE >= '%s'        AND CHARGE_DATE < '%s'        AND CHARGE_TYPE = 1005        AND CHARGE_AMOUNT = 5;    ''' %(handleStr, todayStr)    rs = ngTradeDBUtil.queryCount(sql, ())    if not rs: return 0    return rsdef gen10yuanNumber():    '''10元奖励人数'''    sql = '''        SELECT COUNT(DISTINCT MEMBER_ID)        FROM netgame_trade.BLOCKED_FUND        WHERE CHARGE_DATE >= '%s'        AND CHARGE_DATE < '%s'        AND CHARGE_TYPE = 1005        AND CHARGE_AMOUNT = 10;    ''' %(handleStr, todayStr)    rs = ngTradeDBUtil.queryCount(sql, ())    if not rs: return 0    return rsdef genTotalRewordNumber():    '''奖励发放总量'''    sql = '''        SELECT SUM(CHARGE_AMOUNT)        FROM netgame_trade.BLOCKED_FUND        WHERE CHARGE_DATE >= '%s'        AND CHARGE_DATE < '%s'        AND CHARGE_TYPE = 1005;    ''' %(handleStr, todayStr)    rs = ngTradeDBUtil.queryCount(sql, ())    if not rs: return 0    return rsdef main():    print "===%s start===%s"%(sys.argv[0], datetime.datetime.strftime(datetime.datetime.now(), "%Y-%m-%d %H:%M:%S"))    writeExcel()    print "===%s end===%s"%(sys.argv[0], datetime.datetime.strftime(datetime.datetime.now(), "%Y-%m-%d %H:%M:%S"))if __name__ == '__main__':    try:        execute.start(main)        execute.addMailTask()        execute.pushStart(fileDir)        execute.end()    finally:        if pointDBUtil: pointDBUtil.close()        if ngTradeDBUtil: ngTradeDBUtil.close()

方法的顺序是：

写报表

创建模板

取数据

主方法

这样看起来结构清晰，且查看最核心的写报表代码时，直接看最上面就可以了，不用往下翻。

取数据和主方法，算是比较死的东西，放到最下面。

上面的两个方法，创建模板的重要性次于写报表，所以放到第二位。

6.【写关于历史数据的Excel表的代码改进】

改进原因，前一篇博客已经阐述的很明白了。是因为没有必要生成一个模板文件保存下来。

只需要把创建模板的方法，作为一个写表头的方法来用就可以了。

原版：

def writeExcel():    '''写报表'''    rowIndex = handleDate.day+1    if handleDate.day == 1:        createTemplateExcel()        readFile = tempFileName    else:        if os.path.exists(beforeFilePath):            readFile = beforeFilePath        else:            createTemplateExcel()            readFile = tempFileName    rb = xlrd.open_workbook(readFile, on_demand = True, formatting_info = True)    wb = copy(rb)

def createTemplateExcel():    '''创建Excel文件模板'''    wb = xlwt.Workbook(encoding = "UTF-8", style_compression = True)    sht0 = wb.add_sheet("sheet1", cell_overwrite_ok = True)    sht0.col(1).width=3500    sht0.col(9).width=3500    sht0.col(10).width=3500    sht0.col(17).width=3500    sht0.write_merge(0, 1, 0, 0, '日期', style1)    sht0.write_merge(0, 1, 1, 1, '参与签到人数', style1)    sht0.write_merge(0, 0, 2, 9, '积分奖励情况', style1)    sht0.write(1, 2, '5积分', style1)    sht0.write(1, 3, '10积分', style1)    sht0.write(1, 4, '15积分', style1)    sht0.write(1, 5, '20积分', style1)    sht0.write(1, 6, '25积分', style1)    sht0.write(1, 7, '30积分', style1)    sht0.write(1, 8, '35积分', style1)    sht0.write(1, 9, '积分发放总量', style1)    sht0.write_merge(0, 1, 10, 10, '参与抽奖人数', style1)    sht0.write_merge(0, 0, 11, 16, '抽奖奖励情况', style1)    sht0.write(1, 11, '再来一次', style1)    sht0.write(1, 12, '1元', style1)    sht0.write(1, 13, '1.5元', style1)    sht0.write(1, 14, '2元', style1)    sht0.write(1, 15, '5元', style1)    sht0.write(1, 16, '10元', style1)    sht0.write(1, 17, '奖励发放总量', style1)    wb.save(tempFileName)

改进版：

创建模板的方法return一个wb

def createTemplateExcel():    '''创建Excel文件模板'''    wb = xlwt.Workbook(encoding = "UTF-8", style_compression = True)    sht0 = wb.add_sheet("sheet1", cell_overwrite_ok = True)    sht0.col(1).width=3500    sht0.col(9).width=3500    sht0.col(10).width=3500    sht0.col(17).width=3500    sht0.write_merge(0, 1, 0, 0, '日期', style1)    sht0.write_merge(0, 1, 1, 1, '参与签到人数', style1)    sht0.write_merge(0, 0, 2, 9, '积分奖励情况', style1)    sht0.write(1, 2, '5积分', style1)    sht0.write(1, 3, '10积分', style1)    sht0.write(1, 4, '15积分', style1)    sht0.write(1, 5, '20积分', style1)    sht0.write(1, 6, '25积分', style1)    sht0.write(1, 7, '30积分', style1)    sht0.write(1, 8, '35积分', style1)    sht0.write(1, 9, '积分发放总量', style1)    sht0.write_merge(0, 1, 10, 10, '参与抽奖人数', style1)    sht0.write_merge(0, 0, 11, 16, '抽奖奖励情况', style1)    sht0.write(1, 11, '再来一次', style1)    sht0.write(1, 12, '1元', style1)    sht0.write(1, 13, '1.5元', style1)    sht0.write(1, 14, '2元', style1)    sht0.write(1, 15, '5元', style1)    sht0.write(1, 16, '10元', style1)    sht0.write(1, 17, '奖励发放总量', style1)
        return wb

def writeExcel():    '''写报表'''    rowIndex = handleDate.day+1    if handleDate.day == 1:        wb=createTemplateExcel()    else:        if os.path.exists(beforeFilePath):            readFile = beforeFilePath
            rb = xlrd.open_workbook(readFile, on_demand = True, formatting_info = True)            wb = copy(rb)
       else:            wb=createTemplateExcel()  

然后接下来

sheet1 = wb.get_sheet(0)sheet1.write(rowIndex, 0, handleS。。。。。。。。

写数据就可以了。

这样就不用保存模板文件了。