python导出 excel文件

最近工作中用到了，原来导出 excel 文件是这样容易

# fontHeight = 字号 * 20，据此指定字号
def getHeadStyle(fontHeight = 400):
    '''设置excel文件单元格格式'''
    import xlwt
    headFont = xlwt.Font()
    # 设置为粗体
    headFont.bold = True
    # 字体设置为 宋体
    headFont.name = 'SimSun'
    headFont.height = fontHeight

    style = xlwt.XFStyle()
    # 设置居中
    style.alignment.horz = xlwt.Alignment.HORZ_CENTER
    style.font = headFont

    return style


def getCellStyle():
    import xlwt
    style = xlwt.XFStyle()
    # 设置居中
    style.alignment.horz = xlwt.Alignment.HORZ_CENTER
    cellFont = xlwt.Font()
    cellFont.name = 'TimesNew Roman'
    style.font = cellFont

    return style


def setColumnWidth(column, title):
    '''设置列宽度'''
    import  chardet
    encode = chardet.detect(title)
    if encode['encoding'] == 'ascii':
        column.width = int(300 * len(title) * 2.2)
    else:
        column.width = int(300 * len(title) * 1.5)

def createExcel(registerStatis, userStatis, taskStatis, couponStatis, expectDate=''):
    '''分析导出的数据，输出为4个sheet的excel文件
    '''
    import xlwt
    import time
    # 统计信息 sheet
    statisHeaders = {'注册设备':['注册了用户','未注册用户'],'注册用户':['有上线id','使用优惠码','自然用户']}
    # 注册用户 sheet
    userHeaders = ['uid', 'email', 'uin', '上线id/优惠码', '注册时间', '状态（1表示正常）', '城市id', '最近登陆ip', 'imei', 'aid']
    # 优惠码统计 sheet
    couponHeaders = ['优惠码', '备注', '链接地址', '当日使用人数', 'uid']
    # 商家 sheet
    companyHeaders = ['合作商家名', '任务id', '任务类型', '任务名', '完成人数']
    fp = xlwt.Workbook(encoding='utf-8')
    sheet1 = fp.add_sheet('注册统计')
    sheet2 = fp.add_sheet('新增用户')
    sheet3 = fp.add_sheet('优惠码使用统计')
    sheet4 = fp.add_sheet('合作商家任务完成情况统计')

    headStyle = getHeadStyle()
    cellStyle = getCellStyle()

    colNum = 0
    for key in statisHeaders:
        # 记下当前写到了第几列（本列是空的）
        leftCol = colNum
        # 把子标题写入第二行
        listSubTitle = statisHeaders[key]
        for subIndex in range(0, len(listSubTitle)):
            subTitle = listSubTitle[subIndex]
            sheet1.write(1, colNum, subTitle, style=getHeadStyle(fontHeight=320))
            # 设置宽度
            col = sheet1.col(colNum)
            setColumnWidth(col, subTitle)
            sheet1.write(2, colNum, registerStatis[key][subIndex], style=cellStyle)
            # 加一列
            colNum += 1
        # 把大标题写入第一行，通过合并单元格来实现大标题
        sheet1.write_merge(0, 0, leftCol, colNum - 1, key, style=headStyle)
    for i in range(0, len(userHeaders)):
        sheet2.write(0, i, userHeaders[i], style=headStyle)
        col = sheet2.col(i)
        setColumnWidth(col, userHeaders[i])
    userRow = 1
    for uid in userStatis:
        for i in range(0, len(userStatis[uid])):
            sheet2.write(userRow, i, str(userStatis[uid][i]), style=cellStyle)
        userRow += 1
    for i in range(0, len(couponHeaders)):
        sheet3.write(0, i, couponHeaders[i], style=headStyle)
        col = sheet3.col(i)
        setColumnWidth(col, couponHeaders[i])
    couponRow = 1
    for code in couponStatis:
        couponRecord = couponStatis[code]
        for i in range(0, len(couponHeaders)):
            sheet3.write(couponRow, i, couponRecord[i], style=cellStyle)
        couponRow += 1
    for i in range(0, len(companyHeaders)):
        sheet4.write(0, i, companyHeaders[i], style=headStyle)
        col = sheet4.col(i)
        setColumnWidth(col, companyHeaders[i])
    taskRow = 1
    for swType in taskStatis:
        taskIds = taskStatis[swType].keys()
        sheet4.write_merge(taskRow, (taskRow + len(taskIds) - 1), 0, 0, getSwtypeStr(swType), style = getHeadStyle(fontHeight=260))
        for taskId in taskIds:
            taskInfo = taskStatis[swType][taskId]
            for i in range(0, len(taskInfo)):
                if i == 1:
                    offerType = getOfferTypeName(taskInfo[i])
                    sheet4.write(taskRow, i+1, offerType, style=cellStyle)
                else:
                    sheet4.write(taskRow, i + 1, taskInfo[i], style=cellStyle)
            taskRow += 1
    if expectDate:
        expectDate = expectDate[:10]
        fileName = 'linkcel_%s.xls' % expectDate
    else:
        now = time.strftime('%Y-%m-%d', time.localtime(time.time()))
        fileName = 'linkcel_%s.xls' % now
    fp.save(fileName)

最终效果如图：