python导出xls

问题：python如何导出xls

解决：使用xlrd, xlwt, xlutils

方法：
一、无模板文件导出数据，导出为StringIO流

import xlwt
from io import BytesIO

# 设置响应头
self.set_header('Content-Type', 'application/x-xls')
# filename 不能为中文
self.set_header('Content-Disposition', 'attachment; filename=download.xls')
wb = xlwt.Workbook()
ws = wb.add_sheet('Sheet1')
style0 = xlwt.easyxf('font: name Microsoft YaHei Light, height 220; align: vert centre, horiz center')
# title
ws.write_merge(0, 0, 0, 5, '%s统计表' % school_name, style0)
header = ['年份', '年级', '班级', '人数', '班主任']
for i in range(len(header)):
    ws.write(1, i, header[i], style0)
    ws.col(i).width = 5000

for k, s in enumerate(schools):
    ws.write(k+2, 0, s.year, style0)
    ws.write(k+2, 1, s.grade, style0)
    ws.write(k+2, 2, s.classes, style0)
    ws.write(k+2, 3, s.student_num, style0)
    ws.write(k+2, 4, s.teacher, style0)

sio = BytesIO()
# 这点很重要，传给save函数的不是保存文件名，而是一个StringIO流
wb.save(sio)
self.write(sio.getvalue())

二、有模板文件导出

import xlrd
import xlwt
from xlutils.copy import copy
from io import BytesIO

# 注意：模板建议使用xls，xlsx会有错
tmp_path = os.path.realpath(
    os.path.join(os.path.dirname(__file__),
                 "../../static/download/donwload_tmp.xls"))
rb = xlrd.open_workbook(tmp_path, formatting_info=True, on_demand=True)

wb = copy(rb)
w_sheet = wb.get_sheet(0)

style0 = xlwt.easyxf(
    'font: name Microsoft YaHei Light;\
    borders: left 1, right 1, top 1, bottom 1;\
    align: vert centre, horiz center')

w_sheet.write(2, 1, '李三', style0)
w_sheet.write(2, 3, '男', style0)
w_sheet.write(2, 5, '23', style0)
w_sheet.write(2, 7, classes, style0)
w_sheet.write(3, 1, teacher, style0)

sio = BytesIO()
wb.save(sio)
self.write(sio.getvalue())