xlwt 创建表格

通过xlwt创建表格，以及按照需求对表格样式进行调整

1.创建表格对象以及样式对象

def create_excel_sheet_style(sheet_name):
    sheet_name = sheet_name
    xl = xlwt.Workbook(encoding="utf-8")
    sheet = xl.add_sheet(sheet_name, cell_overwrite_ok=True)

    # 字体
    font = xlwt.Font()
    font.name = '宋体'
    font.height = 20 * 12

    # 表格边框
    borders = xlwt.Borders()
    borders.left = 1
    borders.right = 1
    borders.top = 1
    borders.bottom = 1
    borders.bottom_colour = 0x3A

    # 居中格式
    alignment = xlwt.Alignment()
    alignment.horz = xlwt.Alignment.HORZ_CENTER
    alignment.vert = xlwt.Alignment.VERT_CENTER

    # 背景色
    pattern = xlwt.Pattern()
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
    pattern.pattern_fore_colour = 22

    # 表头样式
    style_h = xlwt.XFStyle()
    style_h.font = font
    style_h.alignment = alignment
    style_h.pattern = pattern

    # 内容样式
    style_t = xlwt.XFStyle()
    style_t.font = font
    style_t.borders = borders
    style_t.alignment = alignment

    return xl, sheet, style_h, style_t
    
    
    # 为方便使用，此函数创建了两个样式对象，用于后续表头样式和内容样式的使用

2.表格数据添加

def details_resp(sheet_name, header_row, data, file_name):
    # 创建表头和样式对象
    xl, sheet, style_h, style_t = cls.create_excel_sheet_style(sheet_name)

    # 设置所有列宽（该表格固定列数及列宽，有需要可自己调整）
    for itm in range(7):
        if itm == 6:
            sheet.col(itm).width = 24000  # 可优化为根据内容宽度自适应列宽
            continue
        sheet.col(itm).width = 7000
        
    # 添加表头
    tab_title_column = 0
    sheet.row(0).set_style(xlwt.easyxf('font: height 500'))  # 设置行高
    for value in header_row:
        sheet.write(0, tab_title_column, value, style_h)
        tab_title_column += 1

    # 添加数据
    row = 1
    for itm in data:
        column = 0
        sheet.row(row).set_style(xlwt.easyxf('font: height 500'))
        
        # 合并单元格
        sheet.write_merge(row, row - 1 + len(itm["cls"]), 0, 0, itm["gradeName"], style_t)
        sheet.write_merge(row, row - 1 + len(itm["cls"]), 1, 1, itm["count"], style_t)
        sheet.write_merge(row, row - 1 + len(itm["cls"]), 2, 2, itm["rate"], style_t)
        # 班级数据
        for c in itm["cls"]:
            sheet.row(row).set_style(xlwt.easyxf('font: height 500'))
            sheet.write(row, column + 3, c["clsName"], style_t)
            sheet.write(row, column + 4, c["count"], style_t)
            sheet.write(row, column + 5, c["rate"], style_t)
            sheet.write(row, column + 6, " ".join(["{}{}次".format(k, v) for k, v in c["giftType"].items()]), style_t)
            row += 1
    resp = cls.file_response(xl, file_name)  # 可保存为文件或返回文件流
    return resp

3.文件流

def file_response(xl, file_name):
    sio = io.BytesIO()
    xl.save(sio)
    resp = make_response(sio.getvalue())
    sio.close()
    fn = quote(file_name)
    resp.headers["Content-Disposition"] = "attachment; filename={file_name}; filename*=UTF-8".format(file_name=fn)
    resp.headers[
        'Content-Type'] = 'application/vnd.ms-excel,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    resp.headers["filename"] = fn
    return resp

4.效果图