python openpyxl 操作excel 生成xlsx 可通过http直接返回文件

from openpyxl.styles import Color, Fill, Font, Alignment, PatternFill, Border, Side
from io import BytesIO
from zipfile import ZipFile, ZIP_DEFLATED
from flask import make_response
from openpyxl.writer.excel import ExcelWriter
from openpyxl import Workbook


def openxltest():
    wb = Workbook()
    ws = wb.worksheets[0]
    ws.title = 'MyTest'
    ws.merge_cells(start_row=1, end_row=1, start_column=1, end_column=3)
    ws.unmerge_cells(start_row=1, end_row=1, start_column=1, end_column=3)
    ws.merge_cells(start_row=1, end_row=1, start_column=1, end_column=4)
    write_excel_cell(ws.cell(row=1, column=1), 'center', 'center', 18, "MyTest")
    ws.merge_cells(start_row=2, end_row=2, start_column=1, end_column=3)
    # 3行18列写入，左右上下居中，字号12，设置边框，背景填充
    write_excel_cell(ws.cell(row=3, column=18), 'center', 'center', 12, '备注', side_style='thin',side_color='000000', fill_type='solid', fill_color='ADADAD')
    ws.row_dimensions[3].height = 18   # 行高
    ws.column_dimensions["A"].width = 4  # 列宽
    wb.save("info.xlsx")
    # for http return xlsx file
    file_name = "测试.xlsx"
    content = save_virtual_workbook(wb)
    resp = make_response(content)
    resp.headers["Content-Disposition"] = 'attachment; filename={}.xlsx'.format(file_name.encode().decode('latin-1'))
    resp.headers['Content-Type'] = 'application/x-xlsx'
    return resp


def save_virtual_workbook(workbook,):
    """Return an in-memory workbook, suitable for a Django response."""
    temp_buffer = BytesIO()
    archive = ZipFile(temp_buffer, 'w', ZIP_DEFLATED, allowZip64=True)

    writer = ExcelWriter(workbook, archive)

    try:
        writer.write_data()
    finally:
        archive.close()

    virtual_workbook = temp_buffer.getvalue()
    temp_buffer.close()
    return virtual_workbook


def write_excel_cell(cell_obj, horiz, verti, font_size, value, bold=False, wrap=False, shri=False, side_style=None,
                     side_color=None, fill_type=None, fill_color=None):
    """
    :param cell_obj:
    :param horiz: 水平位置 "general", "left", "center", "right",
    :param verti: 垂直位置 "top", "center", "bottom", "justify", "distributed"
    :param font_size: 字体大小
    :param value: 值
    :param wrap: 是否换行
    :param shri: 是否缩小填充
    :param bold: 是否加粗，True-False
    :param side_style: 边框样式 thin
    :param side_color: 边框颜色 000000
    :param fill_type: 背景样式 solid
    :param fill_color: 背景颜色 0d5330
    :return:
    """
    alignment_style = Alignment(horizontal=horiz, vertical=verti, wrapText=wrap, shrinkToFit=shri)
    cell_obj.font = Font(size=font_size, bold=bold)
    cell_obj.value = value
    cell_obj.alignment = alignment_style
    # 定义Border边框样式
    if side_style is not None:
        left = Side(style=side_style, color=side_color)
        right = Side(style=side_style, color=side_color)
        top = Side(style=side_style, color=side_color)
        bottom = Side(style=side_style, color=side_color)
        border_style = Border(left=left, right=right, top=top, bottom=bottom)
        cell_obj.border = border_style
    # 背景色
    if fill_type is not None:
        cell_obj.fill = PatternFill(fill_type=fill_type, fgColor=fill_color)

# 设置边框
def set_border(cell_obj, side_style, side_color):
    left = Side(style=side_style, color=side_color)
    right = Side(style=side_style, color=side_color)
    top = Side(style=side_style, color=side_color)
    bottom = Side(style=side_style, color=side_color)
    border_style = Border(left=left, right=right, top=top, bottom=bottom)
    cell_obj.border = border_style


if __name__ == '__main__':
    openxltest()