使用Django Celery进行Excel导入和导出

pip install django-celery
pip install pandas

# 启用Celery
CELERY_IMPORTS = ('myapp.tasks',)
CELERY_RESULT_BACKEND = 'djcelery.backends.database:DatabaseBackend'
BROKER_URL = 'redis://localhost:6379/0'

# pandas读取文件编码格式
PANDAS_READ_ENCODING = 'utf-8'

# 导出Excel时的行高和列宽
EXCEL_ROW_HEIGHT = 20
EXCEL_COLUMN_WIDTH = 15

from django.conf.urls import url, include
from django.contrib import admin

import myapp.views

urlpatterns = [
    url(r'^admin/', admin.site.urls),
    url(r'^export-excel/$', myapp.views.export_excel),
    url(r'^import-excel/$', myapp.views.import_excel),
]

from __future__ import absolute_import
from celery import shared_task
import pandas as pd
from django.core.files.uploadedfile import TemporaryUploadedFile
from django.conf import settings


@shared_task
def export_excel(filename, data):
    df = pd.DataFrame(data)
    writer = pd.ExcelWriter(filename)
    df.to_excel(writer, sheet_name='Sheet1', index=False)
    worksheet = writer.sheets['Sheet1']
    for i, col in enumerate(df.columns):
        column_len = df[col].astype(str).str.len().max()
        column_len = max(column_len, len(col))
        worksheet.set_column(i, i, settings.EXCEL_COLUMN_WIDTH)
        worksheet.set_column(i, i, column_len)
        worksheet.set_row(0, settings.EXCEL_ROW_HEIGHT)
    writer.save()


@shared_task
def import_excel(file):
    if isinstance(file, TemporaryUploadedFile):
        file_path = file.temporary_file_path()
    else:
        file_path = file
    df = pd.read_excel(file_path, encoding=settings.PANDAS_READ_ENCODING)
    return df.to_dict(orient='records')

from django.shortcuts import render, redirect
from django.http import HttpResponse
from django.core.files.storage import FileSystemStorage
from django.urls import reverse
from django.contrib import messages
from django.conf import settings

from myapp.tasks import export_excel, import_excel


def export_excel(request):
    if request.method == 'POST':
        data = [
            {'name': '张三', 'age': 18},
            {'name': '李四', 'age': 20},
            {'name': '王五', 'age': 16},
        ]
        filename = 'export.xlsx'
        export_excel.delay(filename, data)
        return redirect(reverse('myapp:export_excel'))
    else:
        return render(request, 'myapp/export_excel.html')


def import_excel(request):
    if request.method == 'POST':
        uploaded_file = request.FILES['file']
        fs = FileSystemStorage()
        filename = fs.save(uploaded_file.name, uploaded_file)
        file_url = fs.url(filename)
        result = import_excel.delay(file_url)
        result_str = '导入成功，共导入了%d条记录' % len(result.get())
        messages.success(request, result_str)
        return redirect(reverse('myapp:import_excel'))
    else:
        return render(request, 'myapp/import_excel.html')

{% extends "base.html" %}

{% block content %}
    <h1>导出Excel</h1>
    <form method="post">
        {% csrf_token %}
        <input type="submit" value="导出Excel">
    </form>
{% endblock %}

{% extends "base.html" %}

{% block content %}
    <h1>导入Excel</h1>
    {% if messages %}
        <ul class="messages">
            {% for message in messages %}
                <li{% if message.tags %} class="{{ message.tags }}"{% endif %}>{{ message }}</li>
            {% endfor %}
        </ul>
    {% endif %}
    <form method="post" enctype="multipart/form-data">
        {% csrf_token %}
        <input type="file" name="file">
        <input type="submit" value="导入Excel">
    </form>
{% endblock %}