如何在Django中实现Excel文件批量导入Product数据表功能？

我已经定义了用于存储电脑信息的Product模型，现在需要添加Excel文件批量导入功能，避免数据量较大时手动录入数据。现有代码如下：

现有代码 #

models.py #

from django.db import models
from django.utils import timezone

class Product(models.Model):
    
    model=models.CharField(max_length=50, null=True)
    serial=models.CharField(max_length=50, null=True)
    hd_size=models.CharField(max_length=50,null=True)
    ram=models.CharField(max_length=50, null=True)
    processor=models.CharField(max_length=50, null=True)
    date_created = models.DateTimeField(default=timezone.now)
    date_updated = models.DateTimeField(auto_now=True)

    def __str__(self):
        return self.serial + ' - ' + self.model

views.py #

from django.shortcuts import render
from django.contrib.auth.decorators import login_required
from .models import Product

@login_required
def product_mgt(request):
    context['page_title'] = "Computer List"
    products = Product.objects.all()
    context['products'] = products

    return render(request, 'product_mgt.html', context)

forms.py #

from django import forms
from .models import Product

class SaveProduct(forms.ModelForm):
    model= forms.CharField(max_length=50,)
    serial= forms.CharField(max_length=50, )
    hd_size= forms.CharField(max_length=50, )
    ram= forms.CharField(max_length=50, )
    processor= forms.CharField(max_length=50, )

    class Meta:
        model = Product
        fields = ('model','serial','hd_size','ram', 'processor')

    def clean_serial(self):
        id = self.instance.id if self.instance.id else 0
        serial = self.cleaned_data['serial']
        try:
            if int(id) > 0:
                product = Product.objects.exclude(id=id).get(serial = serial)
            else:
                product = Product.objects.get(serial = serial)
        except:
            return serial
        raise forms.ValidationError(f"{serial} Computer Already Exists.")

批量导入功能实现步骤 #

1. 安装Excel处理依赖 #

首先安装openpyxl库，用于读取Excel文件：

pip install openpyxl

2. 添加Excel上传表单 #

在forms.py中新增一个专门用于上传Excel文件的表单：

class ExcelUploadForm(forms.Form):
    excel_file = forms.FileField(label="选择Excel文件", widget=forms.FileInput(attrs={'accept': '.xlsx, .xls'}))

3. 修改视图函数处理导入逻辑 #

更新views.py中的product_mgt函数，添加文件上传接收、Excel解析、数据校验及入库的逻辑，同时复用已有的SaveProduct表单的校验规则：

from django.shortcuts import render, redirect
from django.contrib import messages
from django.contrib.auth.decorators import login_required
from .models import Product
from .forms import SaveProduct, ExcelUploadForm
from openpyxl import load_workbook
from django.utils import timezone

@login_required
def product_mgt(request):
    context = {'page_title': "电脑列表"}
    products = Product.objects.all()
    context['products'] = products

    # 处理Excel导入的POST请求
    if request.method == 'POST':
        upload_form = ExcelUploadForm(request.POST, request.FILES)
        if upload_form.is_valid():
            excel_file = request.FILES['excel_file']
            try:
                # 加载Excel文件
                wb = load_workbook(excel_file)
                sheet = wb.active
                # 跳过第一行表头，从第二行开始读取数据
                for row_num, row in enumerate(sheet.iter_rows(min_row=2, values_only=True), start=2):
                    model, serial, hd_size, ram, processor = row
                    # 跳过空行（序列号为空的行）
                    if not serial:
                        messages.warning(request, f"第{row_num}行因序列号为空，已跳过")
                        continue
                    # 使用SaveProduct表单校验数据合法性
                    product_form = SaveProduct(data={
                        'model': model,
                        'serial': serial,
                        'hd_size': hd_size,
                        'ram': ram,
                        'processor': processor
                    })
                    if product_form.is_valid():
                        # 校验通过则创建新记录
                        Product.objects.create(
                            model=model,
                            serial=serial,
                            hd_size=hd_size,
                            ram=ram,
                            processor=processor,
                            date_created=timezone.now()
                        )
                    else:
                        # 收集校验错误信息
                        for err_msg in product_form.errors.values():
                            messages.error(request, f"第{row_num}行导入失败: {err_msg[0]}")
                messages.success(request, "Excel文件导入流程已完成！")
            except Exception as e:
                messages.error(request, f"导入失败: {str(e)}")
            return redirect('product_mgt')
        else:
            messages.error(request, "请选择有效的Excel文件（支持.xlsx/.xls格式）")
    else:
        # 渲染页面时传入上传表单
        context['upload_form'] = ExcelUploadForm()

    return render(request, 'product_mgt.html', context)

4. 更新模板页面添加上传入口 #

修改product_mgt.html，添加上传Excel的表单以及消息提示区域：

<!-- 批量导入区域 -->
<div class="mb-4">
    <h3>批量导入电脑信息</h3>
    <form method="post" enctype="multipart/form-data">
        {% csrf_token %}
        {{ upload_form.as_p }}
        <button type="submit" class="btn btn-success">开始导入</button>
    </form>
</div>

<!-- 消息提示区域 -->
{% if messages %}
    {% for message in messages %}
        <div class="alert alert-{{ message.tags }} alert-dismissible fade show" role="alert">
            {{ message }}
            <button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>
        </div>
    {% endfor %}
{% endif %}

<!-- 原有电脑列表展示部分 -->
<h3>电脑列表</h3>
<table class="table table-striped">
    <thead>
        <tr>
            <th>序列号</th>
            <th>型号</th>
            <th>硬盘大小</th>
            <th>内存</th>
            <th>处理器</th>
            <th>创建时间</th>
        </tr>
    </thead>
    <tbody>
        {% for product in products %}
        <tr>
            <td>{{ product.serial }}</td>
            <td>{{ product.model }}</td>
            <td>{{ product.hd_size }}</td>
            <td>{{ product.ram }}</td>
            <td>{{ product.processor }}</td>
            <td>{{ product.date_created|date:"Y-m-d H:i" }}</td>
        </tr>
        {% endfor %}
    </tbody>
</table>

5. Excel文件格式规范 #

确保你的Excel文件符合以下格式要求：

• 第一行作为表头，依次为：model、serial、hd_size、ram、processor
• 从第二行开始填写实际数据，serial字段为必填项（系统会校验唯一性）
• 支持.xlsx和.xls格式的Excel文件

内容的提问来源于stack exchange，提问作者David Mutuku