python 导出excel表格,自适应宽度

直接上代码

参数

 filename 输出的文件名

 tabletitle 表标题

 remark  备注

bodytitle  表头 格式如： ["学生学号", "学生名字", "课程名称", "课程编号", "考试类型", '考试名字', "考试时间", "是否补考", "成绩得分"]

 

datalist 表格数据 格式二维数组如： 
[
    ["学号1234", "张三", "语文", "course001", 期末考试, 2018年语文期末考试, 2018-12-12, 否,100],
    ["学号1235", "李四", "语文", "course001", 期末考试, 2018年语文期末考试, 2018-12-12, 否,88],
    ["学号1236", "王五", "语文", "course001", 期末考试, 2018年语文期末考试, 2018-12-12, 否,99]
]

from flask import Flask, session,request,jsonify,make_response
import hashlib,xlwt,os,xlrd
from urllib.parse import quote
from io import StringIO,BytesIO
# 导出表格
def write_excel(filename,tabletitle,remark,bodytitle,datalist):
    # 设置字体
    font = xlwt.Font()
    font.bold = True

    # 设置边框
    borders = xlwt.Borders()
    borders.left = xlwt.Borders.THIN
    borders.right = xlwt.Borders.THIN
    borders.top = xlwt.Borders.THIN
    borders.bottom = xlwt.Borders.THIN

    # 设置居中
    alignment = xlwt.Alignment()
    alignment.horz = xlwt.Alignment.HORZ_CENTER  # 水平方向
    alignment.vert = xlwt.Alignment.VERT_TOP  # 垂直方向

    # 设置背景颜色
    pattern = xlwt.Pattern()
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN
    pattern.pattern_fore_colour = 3  # 背景颜色

    # 定义不同的excel style
    style1 = xlwt.XFStyle()
    style1.font = font
    style1.borders = borders
    style1.alignment = alignment
    style2 = xlwt.XFStyle()
    style2.borders = borders
    style2.alignment = alignment
    style3 = xlwt.XFStyle()
    style3.borders = borders
    style3.alignment = alignment
    style4 = xlwt.XFStyle()
    style4.borders = borders
    style4.font = font
    style4.pattern = pattern
    style4.alignment = alignment
    wb = xlwt.Workbook(encoding='utf-8')
    worksheet = wb.add_sheet('My Worksheet')
    # 合并单元格并设置样式
    worksheet.row(0).height_mismatch = True
    worksheet.row(0).height = 20 * 25
    # 合并单元格并设置样式
    worksheet.write_merge(0, 0, 0, len(bodytitle), tabletitle, style=style1)
    worksheet.write_merge(1, 1, 0, len(bodytitle), remark)
    # 确定栏位宽度
    col_width = []
    for i in range(len(datalist)):
        for j in range(len(datalist[i])):
            if i == 0:
                col_width.append(len_byte(datalist[i][j]))
            else:
                if len(col_width) <= j:
                    col_width.append(len_byte(datalist[i][j]))
                else:
                    if col_width[j] < len_byte(str(datalist[i][j])):
                        col_width[j] = len_byte(datalist[i][j])
    # 设置栏位宽度，栏位宽度小于10时候采用默认宽度
    for i in range(len(col_width)):
        if col_width[i] > 10:
            worksheet.col(i).width = 256 * (col_width[i] + 1)

    # 设置栏位高度
    # tall_style = xlwt.easyxf('font:height 720;') #设置字体高度
    # row0 = worksheet.row(0)
    # row0.set_style(tall_style)
    for i in range(0, len(bodytitle)):
        worksheet.write(2, i, bodytitle[i])
    # excel内容写入
    for i in range(len(datalist)):
        for j in range(len(datalist[i])):
            worksheet.write(i + 3, j, datalist[i][j])

    sio = BytesIO()
    # 将excel文件保存到字节流里面
    wb.save(sio)
    # 设置响应参数
    response = make_response(sio.getvalue())
    #filename = "表格.xls"
    basename = os.path.basename(filename)
    utf_filename = quote(basename.encode("utf-8"))
    response.headers["Content-Disposition"] = "attachment;filename*=utf-8''{}".format(utf_filename)
    response.headers["Content-Type"] = "application/octet-stream; charset=UTF-8"
    return response

# 获取字符串长度，一个中文的长度为2
def len_byte(value):
    if value is None or value == "":
        return 10
    if type(value) != int:
        length = len(value)
        utf8_length = len(value.encode('utf-8'))
        length = (utf8_length - length) / 2 + length
    else:
        length = len(str(value))
    return int(length)