python使用flask框架生成excle返回前端（包含图片、表格、表头灰色、表格加边框）

　　python使用flask框架生成excle文档，文档中包含图片和表格，其中表格要包含图片、表格、表头灰色、表格加边框，照片和表格不重叠。

逻辑：获得图片的高度，根据高度计算表格从第几行开始插入。

效果图：

代码：

100

101

import openpyxl
from openpyxl.styles import PatternFill
from openpyxl.drawing.image import Image
from flask import Flask, send_file, request
import base64
import io
from io import BytesIO
from PIL import Image as PillowImage
import math
 
app = Flask(__name__)
 
@app.route('/download-excel', methods=['POST'])
def download_excel():
    base64_swdt = request.form.get('base64')
 
    # 获取思维导图高度
    # 解码base64字符串为图片数据
    decoded_string = base64.b64decode(base64_swdt)
    image_temp = BytesIO(decoded_string)
    image = PillowImage.open(image_temp)
    # 获取图片的高度
    height = image.size[1]
    print("图片高度：", height)
    temp_num = math.ceil(height / 17.2)
 
    # 添加表格数据
    data = [
        ['姓名', '年龄', '性别'],
        ['张三21312312312', 25, '男1111111111111'],
        ['李四', 30, '女'],
        ['王五', 28, '男11111111111111111111111111111111111111']
    ]
 
    # 创建一个新的Excel工作簿
    workbook = openpyxl.Workbook()
    sheet = workbook.active
 
    # 指定从第几行开始插入数据
    start_row = int(temp_num)
 
 
    # 插入空行
    for _ in range(start_row - 1):
        sheet.append([])
 
    for row in data:
        sheet.append(row)
 
    # 调整列宽以适应内容
    for column in sheet.columns:
        max_length = 0
        column = [cell for cell in column]
        for cell in column:
            try:
                if len(str(cell.value)) > max_length:
                    max_length = len(cell.value)
            except:
                pass
        adjusted_width = (max_length + 2) * 1.2
        sheet.column_dimensions[column[0].column_letter].width = adjusted_width
 
    # 添加边框
    border = openpyxl.styles.Border(left=openpyxl.styles.Side(style='thin'),
                                    right=openpyxl.styles.Side(style='thin'),
                                    top=openpyxl.styles.Side(style='thin'),
                                    bottom=openpyxl.styles.Side(style='thin'))
 
    # 指定起始行和结束行
    end_row = len(data) + start_row - 1
 
    for row in range(start_row, end_row + 1):
        for cell in sheet[row]:
            cell.border = border
 
    # 设置表头颜色为灰色
    green_fill = PatternFill(start_color="C0C0C0", end_color="C0C0C0", fill_type="solid")
    for cell in sheet[start_row]:
        cell.fill = green_fill
 
 
 
    # 插入图片
    # 将base64编码的图片解码为字节流
    image_data = base64.b64decode(base64_swdt)
    image_stream = io.BytesIO(image_data)
    # 插入图片到工作表
    img = Image(image_stream)
    sheet.add_image(img, 'A1')  # 将图片插入到A1单元格
 
 
    # 保存工作簿到临时文件
    temp_file = "temp_excel.xlsx"
    workbook.save(temp_file)
 
 
    # 将临时文件发送给前端
    return send_file(temp_file, as_attachment=True, download_name='example.xlsx',
                     mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
if __name__ == '__main__':
    app.run()