Django-客户管理模块（6）模糊搜索、排序和导入导出
Python
Django-客户管理模块（6）模糊搜索、排序和导入导出
Python

Django-客户管理模块（6）模糊搜索、排序和导入导出

昨天好不容易搞上线，今天不太想搞新的东西了，不如把这个乱七八糟的客户管理再优化下吧。

之前我们在视图函数里写过搜索和排序，也就没把这个东西放上去，今天就来看下。

模糊搜索和排序

看了半天，似乎后端不要改，Django里面的search_fields和ordering_fields似乎就满足需要了……操作逻辑处理啥的都是前端的工作，突然发现前端工作好像更贴合以前编程任务。似乎后续需要稍微学一下前端的东东，要不然总不能每次都指望AI来处理吧。

后端保持原有就可以：

class CustomersSet(viewsets.ModelViewSet):
    serializer_class = CustomerSerializer
    filter_backends = [SearchFilter, OrderingFilter]
    search_fields = ["name", "email", "phone", "address"]
    ordering_fields = ["created_at", "updated_at", "name"]
    # 启用 DRF 自带的过滤后端。
    # SearchFilter：允许在 URL 上用 ?search=关键字 来搜索数据。
    # OrderingFilter：允许在 URL 上用 ?ordering=字段名 来排序。
    permission_classes = [IsAuthenticated]  # 加上这行，给 API 上锁！

    def get_queryset(self):
        # 查询函数
        return Customer.objects.filter(owner=self.request.user)  # 只返回当前用户

    def perform_create(self, serializer):
        serializer.save(owner=self.request.user)

前端贴上来做个备份吧（在最后），我其实已经看不太懂了……之前功能少还能看看。

excel导入、导出

这个总归需要后端写点什么了吧，hh。

导入：前端上传 Excel 文件 → 解析里面的行 → 批量写入数据库。所以，我们先安装一个python库，用于处理excel。POST /api/customers/import_excel/

pip install openpyxl

然后，需要在后端客户那个类里面加入一个新函数，作为导入的方法。这儿有几个点需要说明一下。

  • 首先是@action，是一个装饰器。在 DRF 的 ViewSet中，它用来标记一个​​自定义的方法​​，告诉 DRF，这个方法不是标准的 list, create, retrieve, update, destroy之一，但它也是一个需要对外提供 API 的操作，能自动生成路由。
  • 其次是当读取文件失败或者空的时候，return会带上一个状态码，以便前端或调用方能准确判断请求状态。
  • 为什么没有导入更新或者创建时间？这是因为model里面created_at = models.DateTimeField(auto_now_add=True)、updated_at = models.DateTimeField(auto_now=True)，可以自动更新。
  • 不过需要注意，导入文件表头： customer_id, name, phone, email, address, owner_username, *_。之前还嘲笑某老哥做个功能表头还写死，现在轮到自己写了发现写死真香，匹配多麻烦，花里胡哨的，又不是不能用，啧啧。
    @action(detail=False, methods=['post'])
    def import_excel(self, request):
        file = request.FILES.get("file")
        if not file:
            return Response({"error": "请上传文件"}, status=status.HTTP_400_BAD_REQUEST)

        try:
            wb = openpyxl.load_workbook(file)
            ws = wb.active
        except Exception as e:
            return Response({"error": f"文件解析失败: {str(e)}"}, status=status.HTTP_400_BAD_REQUEST)

        # 假设第一行是表头，从第二行开始
        created_count, updated_count = 0, 0
        for idx, row in enumerate(ws.iter_rows(min_row=2, values_only=True), start=2):
            if not row[1]:  # 必须有姓名
                continue

            customer_id, name, phone, email, address, owner_username, *_ = row

            # 如果有 ID，尝试更新；否则新建
            if customer_id:
                try:
                    customer = Customer.objects.get(id=customer_id)
                    customer.name = name
                    customer.phone = phone
                    customer.email = email
                    customer.address = address
                    customer.save()
                    updated_count += 1
                except Customer.DoesNotExist:
                    continue
            else:
                Customer.objects.create(
                    name=name,
                    phone=phone,
                    email=email,
                    address=address,
                    owner=request.user  # 默认当前用户
                )
                created_count += 1

        return Response({
            "msg": "导入完成",
            "created": created_count,
            "updated": updated_count
        })

导出：把数据库里的 Customer 数据生成一个 Excel 文件返回给前端下载。GET /api/customers/export_excel/

    @action(detail=False, methods=['get'])
    def export_excel(self, request):
        wb = openpyxl.Workbook()
        ws = wb.active
        ws.title = "Customers"

        # 表头
        headers = ["ID", "姓名", "电话", "邮箱", "地址", "负责人", "创建时间", "更新时间"]
        ws.append(headers)

        # 数据行
        for c in self.get_queryset():
            ws.append([
                c.id,
                c.name,
                c.phone,
                c.email,
                c.address,
                c.owner.username if c.owner else "",
                c.created_at.strftime("%Y-%m-%d %H:%M:%S"),
                c.updated_at.strftime("%Y-%m-%d %H:%M:%S"),
            ])

        # 设置列宽
        for i, col in enumerate(headers, 1):
            ws.column_dimensions[get_column_letter(i)].width = 20

        # 返回 response
        response = HttpResponse(
            content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
        )
        response['Content-Disposition'] = 'attachment; filename=customers.xlsx'
        wb.save(response)
        return response

客户列表前端代码

{% extends "customer/base.html" %}

{% block title %}我的客户 - 喵喵CRM{% endblock %}

{% block content %}
<style>
    /* --- Modal 样式 --- */
    .modal-overlay {
        display: none;
        position: fixed;
        top: 0; left: 0;
        width: 100%; height: 100%;
        background-color: rgba(0,0,0,0.5);
        justify-content: center; align-items: center;
        z-index: 1000;
    }
    .modal-content {
        background-color: white;
        padding: 25px;
        border-radius: 8px;
        box-shadow: 0 4px 10px rgba(0,0,0,0.2);
        width: 400px; max-width: 90%;
    }
    .modal-header { display: flex; justify-content: space-between; align-items: center; border-bottom: 1px solid #eee; padding-bottom: 15px; margin-bottom: 20px;}
    .close-btn { background: none; border: none; font-size: 24px; cursor: pointer; color: #888; }

    .modal-form-group { margin-bottom: 15px; }
    .modal-form-group label { display: block; margin-bottom: 5px; font-weight: bold; }
    .modal-form-group input { width: 100%; padding: 8px; border: 1px solid #ccc; border-radius: 4px; box-sizing: border-box; }

    .modal-actions { text-align: right; margin-top: 20px; }
    .action-btn { padding: 10px 15px; border: none; border-radius: 4px; cursor: pointer; font-size: 14px; margin-left: 10px; }
    .save-btn { background-color: #28a745; color: white; }
    .cancel-btn { background-color: #6c757d; color: white; }

    .table-action-btn { padding: 5px 10px; border: 1px solid #ddd; border-radius: 4px; cursor: pointer; font-size: 14px; margin-right: 5px; }
    .edit-btn { background-color: #ffc107; color: #333; }
    .delete-btn { background-color: #dc3545; color: white; }

    .toolbar { display: flex; gap: 10px; align-items: center; margin-bottom: 15px; }
    .toolbar button { padding: 8px 15px; border: none; border-radius: 4px; cursor: pointer; font-size: 14px; }
    .download-btn { background-color: #17a2b8; color: white; }
    .upload-btn { background-color: #6f42c1; color: white; }
    .toolbar label.upload-btn {
        padding: 8px 15px;
        border: none;
        border-radius: 4px;
        cursor: pointer;
        font-size: 14px;
        display: inline-block;
        text-align: center;
    }

    th.sortable { cursor: pointer; }
    th.sortable span { font-size: 12px; margin-left: 4px; }
</style>

<div style="max-width: 1100px; margin: 0 auto; padding: 20px;">
    <div style="display: flex; justify-content: space-between; align-items: center; margin-bottom: 15px;">
        <h2 style="margin: 0;">我的客户列表</h2>
        <div class="toolbar">
            <button id="download-excel-btn" class="download-btn">导出 Excel</button>
            <label for="upload-excel-input" class="upload-btn" style="cursor:pointer;">导入 Excel</label>
            <input type="file" id="upload-excel-input" accept=".xlsx" style="display:none;">
            <button id="add-customer-btn" style="background-color: #28a745; color: white;">新增客户</button>
        </div>
    </div>

    <!-- 搜索区域 -->
    <form id="search-form" style="display: flex; gap: 10px; margin-bottom: 15px;">
        <input type="text" id="search-input" placeholder="输入姓名、电话、邮箱或地址搜索..." style="flex: 1; padding: 8px; border: 1px solid #ccc; border-radius: 4px;">
        <button type="submit" style="padding: 8px 15px; background-color: #007bff; color: white; border: none; border-radius: 4px;">搜索</button>
        <button type="button" id="clear-search-btn" style="padding: 8px 15px; background-color: #6c757d; color: white; border: none; border-radius: 4px;">清空</button>
    </form>

    <table style="width: 100%; border-collapse: collapse;">
        <thead style="background-color: #333; color: white;">
            <tr>
                <th class="sortable" data-field="name" style="padding: 12px; text-align: left;">姓名 <span></span></th>
                <th style="padding: 12px; text-align: left;">邮箱</th>
                <th style="padding: 12px; text-align: left;">电话</th>
                <th style="padding: 12px; text-align: left;">地址</th>
                <th style="padding: 12px; text-align: left;">负责人</th>
                <th class="sortable" data-field="created_at" style="padding: 12px; text-align: left;">创建时间 <span></span></th>
                <th class="sortable" data-field="updated_at" style="padding: 12px; text-align: left;">更新时间 <span></span></th>
                <th style="padding: 12px; text-align: left; width: 120px;">操作</th>
            </tr>
        </thead>
        <tbody id="customer-table-body">
            <tr><td colspan="8" style="text-align: center; padding: 20px; color: #777;">加载中...</td></tr>
        </tbody>
    </table>
</div>

<!-- Modal -->
<div id="customer-modal" class="modal-overlay">
    <div class="modal-content">
        <div class="modal-header">
            <h3 id="modal-title">新增客户</h3>
            <button id="close-modal-btn" class="close-btn">&times;</button>
        </div>
        <form id="modal-form">
            <input type="hidden" id="customer-id-input">
            <div class="modal-form-group"><label for="name-input">姓名</label><input type="text" id="name-input" required></div>
            <div class="modal-form-group"><label for="phone-input">电话</label><input type="tel" id="phone-input"></div>
            <div class="modal-form-group"><label for="email-input">邮箱</label><input type="email" id="email-input"></div>
            <div class="modal-form-group"><label for="address-input">地址</label><input type="text" id="address-input"></div>
            <div class="modal-actions">
                <button type="button" class="action-btn cancel-btn">取消</button>
                <button type="submit" class="action-btn save-btn">保存</button>
            </div>
        </form>
    </div>
</div>

<script>
document.addEventListener('DOMContentLoaded', function() {
    function getCookie(name) {
        let cookieValue = null;
        if (document.cookie && document.cookie !== '') {
            for (const cookie of document.cookie.split(';')) {
                const c = cookie.trim();
                if (c.startsWith(name + '=')) { cookieValue = decodeURIComponent(c.substring(name.length + 1)); break; }
            }
        }
        return cookieValue;
    }
    const csrftoken = getCookie('csrftoken');
    const API_BASE_URL = '/api/customers/';

    // DOM
    const addBtn = document.getElementById('add-customer-btn');
    const customerTableBody = document.getElementById('customer-table-body');
    const modal = document.getElementById('customer-modal');
    const modalForm = document.getElementById('modal-form');
    const modalTitle = document.getElementById('modal-title');
    const customerIdInput = document.getElementById('customer-id-input');
    const nameInput = document.getElementById('name-input');
    const phoneInput = document.getElementById('phone-input');
    const emailInput = document.getElementById('email-input');
    const addressInput = document.getElementById('address-input');
    const searchForm = document.getElementById('search-form');
    const searchInput = document.getElementById('search-input');
    const clearSearchBtn = document.getElementById('clear-search-btn');
    const sortableHeaders = document.querySelectorAll('th.sortable');
    const downloadBtn = document.getElementById('download-excel-btn');
    const uploadInput = document.getElementById('upload-excel-input');
    let currentSearch = '';
    let currentOrdering = '';

    // fetch + 渲染
    function fetchCustomers() {
        let url = `${API_BASE_URL}?search=${encodeURIComponent(currentSearch)}&ordering=${currentOrdering}`;
        fetch(url, { headers: { 'X-CSRFToken': csrftoken } })
        .then(r => r.json())
        .then(data => {
            customerTableBody.innerHTML = '';
            if (data.length === 0) {
                customerTableBody.innerHTML = '<tr><td colspan="8" style="text-align:center; padding:20px; color:#777;">没有找到客户</td></tr>';
                return;
            }
            for (const c of data) {
                const row = `
                <tr style="border-bottom:1px solid #ddd;" data-id="${c.id}">
                    <td data-field="name">${c.name}</td>
                    <td data-field="email">${c.email || '--'}</td>
                    <td data-field="phone">${c.phone || '--'}</td>
                    <td data-field="address">${c.address || '--'}</td>
                    <td data-field="owner">${c.owner_username || 'N/A'}</td>
                    <td data-field="created_at">${new Date(c.created_at).toLocaleString()}</td>
                    <td data-field="updated_at">${new Date(c.updated_at).toLocaleString()}</td>
                    <td>
                        <button class="table-action-btn edit-btn">编辑</button>
                        <button class="table-action-btn delete-btn">删除</button>
                    </td>
                </tr>`;
                customerTableBody.insertAdjacentHTML('beforeend', row);
            }
        })
        .catch(err => {
            console.error(err);
            customerTableBody.innerHTML = '<tr><td colspan="8" style="text-align:center; color:red;">加载失败</td></tr>';
        });
    }

    // 搜索
    searchForm.addEventListener('submit', e => {
        e.preventDefault();
        currentSearch = searchInput.value.trim();
        fetchCustomers();
    });
    clearSearchBtn.addEventListener('click', () => {
        searchInput.value = '';
        currentSearch = '';
        fetchCustomers();
    });

    // 排序
    sortableHeaders.forEach(th => {
        th.addEventListener('click', () => {
            const field = th.dataset.field;
            if (currentOrdering === field) {
                currentOrdering = '-' + field; // 切换到降序
            } else {
                currentOrdering = field; // 切换到升序
            }
            // 更新箭头
            sortableHeaders.forEach(h => h.querySelector('span').textContent = '');
            th.querySelector('span').textContent = currentOrdering.startsWith('-') ? '↓' : '↑';
            fetchCustomers();
        });
    });

    // --- Excel 导出 ---
    downloadBtn.addEventListener('click', () => {
        fetch(`${API_BASE_URL}export_excel/`, {
            method: 'GET',
            headers: { 'X-CSRFToken': csrftoken }
        })
        .then(response => response.blob())
        .then(blob => {
            const url = window.URL.createObjectURL(blob);
            const a = document.createElement('a');
            a.href = url;
            a.download = 'customers.xlsx';
            document.body.appendChild(a);
            a.click();
            a.remove();
            window.URL.revokeObjectURL(url);
        })
        .catch(() => alert('导出失败'));
    });

    // --- Excel 导入 ---
    uploadInput.addEventListener('change', () => {
        const file = uploadInput.files[0];
        if (!file) return;

        const formData = new FormData();
        formData.append('file', file);

        fetch(`${API_BASE_URL}import_excel/`, {
            method: 'POST',
            headers: { 'X-CSRFToken': csrftoken },
            body: formData
        })
        .then(response => response.json())
        .then(data => {
            alert(`导入完成：新增 ${data.created} 条，更新 ${data.updated} 条`);
            fetchCustomers();
        })
        .catch(() => alert('导入失败'));
    });

    // Modal 控制
    function openModal() { modal.style.display = 'flex'; }
    function closeModal() { modal.style.display = 'none'; modalForm.reset(); customerIdInput.value = ''; }
    document.getElementById('close-modal-btn').onclick = closeModal;
    modal.querySelector('.cancel-btn').onclick = closeModal;
    modal.addEventListener('click', e => { if (e.target === modal) closeModal(); });

    // 新增
    addBtn.addEventListener('click', () => { modalTitle.textContent = '新增客户'; openModal(); });

    // 提交（新增/编辑）
    modalForm.addEventListener('submit', e => {
        e.preventDefault();
        const customerId = customerIdInput.value;
        const isEditing = !!customerId;
        const url = isEditing ? `${API_BASE_URL}${customerId}/` : API_BASE_URL;
        const method = isEditing ? 'PATCH' : 'POST';
        const body = JSON.stringify({ name: nameInput.value, phone: phoneInput.value, email: emailInput.value, address: addressInput.value });
        fetch(url, { method, headers: { 'Content-Type': 'application/json', 'X-CSRFToken': csrftoken }, body })
        .then(r => { if (!r.ok) throw new Error(); return r.json(); })
        .then(() => { closeModal(); fetchCustomers(); alert(isEditing ? '客户更新成功！' : '客户添加成功！'); })
        .catch(() => alert('操作失败'));
    });

    // 表格事件（编辑 / 删除）
    customerTableBody.addEventListener('click', e => {
        const row = e.target.closest('tr'); if (!row) return;
        const id = row.dataset.id;
        if (e.target.classList.contains('edit-btn')) {
            modalTitle.textContent = '编辑客户';
            customerIdInput.value = id;
            nameInput.value = row.querySelector('[data-field="name"]').textContent;
            phoneInput.value = row.querySelector('[data-field="phone"]').textContent.replace('--','');
            emailInput.value = row.querySelector('[data-field="email"]').textContent.replace('--','');
            addressInput.value = row.querySelector('[data-field="address"]').textContent.replace('--','');
            openModal();
        }
        if (e.target.classList.contains('delete-btn')) {
            if (confirm('确定要删除这位客户吗？')) {
                fetch(`${API_BASE_URL}${id}/`, { method: 'DELETE', headers: { 'X-CSRFToken': csrftoken } })
                .then(r => { if (r.ok) { row.remove(); alert('删除成功'); } else alert('删除失败'); });
            }
        }
    });

    // 首次加载
    fetchCustomers();
});
</script>
{% endblock %}

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注