最近工作中用到了,原来导出 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)
最终效果如图: