直接上代码
参数
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)
在设置上Excel单元格的背景色时,fore_colour 支持的颜色是有限的,仅支持一下颜色
aqua 0x31 black 0x08 blue 0x0C blue_gray 0x36 bright_green 0x0B brown 0x3C coral 0x1D cyan_ega 0x0F dark_blue 0x12 dark_blue_ega 0x12 dark_green 0x3A dark_green_ega 0x11 dark_purple 0x1C dark_red 0x10 dark_red_ega 0x10 dark_teal 0x38 dark_yellow 0x13 gold 0x33 gray_ega 0x17 gray25 0x16 gray40 0x37 gray50 0x17 gray80 0x3F green 0x11 ice_blue 0x1F indigo 0x3E ivory 0x1A lavender 0x2E light_blue 0x30 light_green 0x2A light_orange 0x34 light_turquoise 0x29 light_yellow 0x2B lime 0x32 magenta_ega 0x0E ocean_blue 0x1E olive_ega 0x13 olive_green 0x3B orange 0x35 pale_blue 0x2C periwinkle 0x18 pink 0x0E plum 0x3D purple_ega 0x14 red 0x0A rose 0x2D sea_green 0x39 silver_ega 0x16 sky_blue 0x28 tan 0x2F teal 0x15 teal_ega 0x15 turquoise 0x0F violet 0x14 white 0x09 yellow 0x0D"