Django实现Excel文件上传并导入数据至模型求助

Hey there! I’ve helped a lot of folks solve this exact problem—let’s get your Excel upload working smoothly. We’ll use openpyxl (a robust library for reading Excel files) and Django’s built-in form/request handling to make this happen.

Step 1: Install Required Library #

First, install openpyxl to parse Excel files:

pip install openpyxl

Step 2: Define Your Model (Example) #

Let’s assume you have a simple model (adjust this to match your actual model):

# models.py
from django.db import models

class Product(models.Model):
    name = models.CharField(max_length=255)
    price = models.DecimalField(max_digits=10, decimal_places=2)
    sku = models.CharField(max_length=50, unique=True)
    created_at = models.DateTimeField(auto_now_add=True)

    def __str__(self):
        return self.name

Step 3: Create the Upload Form #

Update your existing form to ensure it handles file uploads correctly. The key here is using FileField and making sure the form uses multipart/form-data encoding:

# forms.py
from django import forms

class ExcelUploadForm(forms.Form):
    excel_file = forms.FileField(label="Upload Excel File", help_text="Only .xlsx files are supported")

Step 4: Build the View to Handle Upload & Import #

This is the core part—we’ll process the uploaded file, read its contents, and save data to the model. We’ll add error handling to catch common issues like invalid files or bad data:

# views.py
from django.shortcuts import render, redirect
from django.contrib import messages
from openpyxl import load_workbook
from .forms import ExcelUploadForm
from .models import Product

def excel_upload(request):
    if request.method == 'POST':
        form = ExcelUploadForm(request.POST, request.FILES)
        if form.is_valid():
            excel_file = request.FILES['excel_file']
            
            # Check if the file is an Excel file
            if not excel_file.name.endswith('.xlsx'):
                messages.error(request, "Please upload only .xlsx files!")
                return redirect('excel_upload')
            
            try:
                # Load the workbook
                wb = load_workbook(excel_file)
                worksheet = wb.active  # Get the first sheet
                
                # Skip header row (adjust if your Excel has no header)
                for row in worksheet.iter_rows(min_row=2, values_only=True):
                    name, price, sku = row  # Match these to your Excel columns
                    
                    # Validate data before saving (customize as needed)
                    if not name or not price or not sku:
                        messages.warning(request, f"Skipping invalid row: {row}")
                        continue
                    
                    # Create or update the model instance
                    Product.objects.get_or_create(
                        sku=sku,  # Use unique field to avoid duplicates
                        defaults={'name': name, 'price': price}
                    )
                
                messages.success(request, "Excel data imported successfully!")
                return redirect('excel_upload')
            
            except Exception as e:
                messages.error(request, f"Error processing file: {str(e)}")
                return redirect('excel_upload')
    else:
        form = ExcelUploadForm()
    
    return render(request, 'excel_upload.html', {'form': form})

Step 5: Create the Template #

Make sure your template renders the form with the correct encoding:

<!-- templates/excel_upload.html -->
<!DOCTYPE html>
<html>
<head>
    <title>Excel Upload</title>
</head>
<body>
    <h1>Upload Excel File</h1>
    {% if messages %}
        {% for message in messages %}
            <div class="{{ message.tags }}">{{ message }}</div>
        {% endfor %}
    {% endif %}
    <form method="post" enctype="multipart/form-data">
        {% csrf_token %}
        {{ form.as_p }}
        <button type="submit">Upload & Import</button>
    </form>
</body>
</html>

Step 6: Configure the URL #

Add a route to your urls.py:

# urls.py
from django.urls import path
from .views import excel_upload

urlpatterns = [
    path('upload-excel/', excel_upload, name='excel_upload'),
]

Key Notes & Optimizations #

• Bulk Import: If you’re importing hundreds/thousands of rows, use Product.objects.bulk_create() instead of get_or_create in a loop for better performance. Just collect all valid instances first, then save them in one go.
• Data Validation: Expand the validation logic to match your model’s requirements (e.g., check that price is a number, sku format is correct).
• File Size Limits: Django has default file size limits—you can adjust these in your settings.py with FILE_UPLOAD_MAX_MEMORY_SIZE and MAX_UPLOAD_SIZE.
• Excel Column Matching: Ensure the order of columns in your Excel matches how you’re unpacking the row variable in the view. If columns are in a different order, adjust the unpacking (e.g., sku, name, price = row).

That should get your upload and import working! Let me know if you run into any specific issues with your model or file structure.

内容的提问来源于stack exchange，提问作者Vineet Bhikonde