python使用flask框架生成excle文件,文件中包含圖片和表格,其中表格要包含圖片、表格、表頭灰色、表格加邊框,照片和表格不重疊。
邏輯:獲得圖片的高度,根據高度計算表格從第幾行開始插入。
效果圖:
程式碼:
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()