EasyExcel下载模板文件并填充数据导出

以下是使用EasyExcel下载模板文件并填充数据导出的示例代码：

import os
import time
import uuid
import pymysql
import requests
import pandas as pd
from django.http import HttpResponse
from django.conf import settings
import openpyxl
from openpyxl.utils import get_column_letter
from openpyxl.styles import Alignment
from openpyxl.worksheet.datavalidation import DataValidation
import easyexcel

# 从数据库中获取数据
def get_data_from_db():
    conn = pymysql.connect(host='localhost', user='root', password='123456', db='test', charset='utf8')
    cursor = conn.cursor()
    sql = 'SELECT * FROM student'
    cursor.execute(sql)
    data = cursor.fetchall()
    cursor.close()
    conn.close()
    return data

# 下载模板文件
def download_template_file():
    url = 'http://example.com/template.xlsx'
    response = requests.get(url)
    file_name = str(uuid.uuid4()) + '.xlsx'
    file_path = os.path.join(settings.MEDIA_ROOT, file_name)
    with open(file_path, 'wb') as f:
        f.write(response.content)
    return file_path

# 填充数据到模板文件
def fill_data_to_template_file(file_path, data):
    wb = openpyxl.load_workbook(file_path)
    ws = wb.active
    # 填充数据
    for row, row_data in enumerate(data, start=2):
        for col, cell_data in enumerate(row_data, start=1):
            cell = ws.cell(row=row, column=col)
            cell.value = cell_data
    # 设置单元格样式
    for col in range(1, len(data[0]) + 1):
        column_letter = get_column_letter(col)
        ws.column_dimensions[column_letter].width = 20
        for row in range(1, len(data) + 2):
            cell = ws.cell(row=row, column=col)
            cell.alignment = Alignment(horizontal='center', vertical='center')
    # 设置数据有效性
    dv = DataValidation(type="list", formula1='"男,女"', allow_blank=True)
    ws.add_data_validation(dv)
    dv.add('D2:D100')
    # 合并单元格
    ws.merge_cells('A1:E1')
    # 保存文件
    wb.save(file_path)

# 导出Excel文件
def export_excel_file(file_path):
    file_name = 'export_' + time.strftime('%Y%m%d%H%M%S') + '.xlsx'
    response = HttpResponse(content_type='application/vnd.ms-excel')
    response['Content-Disposition'] = 'attachment; filename={}'.format(file_name)
    easyexcel.write(response, file_path)
    os.remove(file_path)
    return response

# 下载模板文件并填充数据导出
def download_and_export(request):
    data = get_data_from_db()
    file_path = download_template_file()
    fill_data_to_template_file(file_path, data)
    response = export_excel_file(file_path)
    return response