Django后端DataTables服务端处理：搜索、排序及导出按钮配置求助

一、修复搜索功能 #

当前代码已获取搜索关键词但未实际使用，且分页逻辑错误，需调整过滤与分页的顺序，实现全局模糊搜索：

修改json_response函数如下：

def json_response(request):
    draw = int(request.GET.get('draw'))
    length = int(request.GET.get('length'))
    start = int(request.GET.get('start'))
    substring = str(request.GET.get('search[value]', ''))  # 增加默认值避免空值异常
    
    path = '/XX/XX/XXX/XXX.csv'
    clients = pd.read_csv(path)
    recordsTotal = len(clients) 
    
    # 1. 全局模糊搜索过滤
    if substring.strip():
        # 对所有列进行大小写不敏感的模糊匹配，数值列转字符串处理
        mask = clients.apply(lambda x: x.astype(str).str.contains(substring, case=False).any(), axis=1)
        clients = clients[mask]
    recordsFiltered = len(clients)  # 更新过滤后的总条数
    
    # 列格式处理（重命名、填充空值）
    clients = clients.rename(columns={
        "Client description":"client_descr",
        "Client IP":"client_ip",
        "Client MAC":"client_mac",
        "Client Manufacturer":"client_man",
        "Client OS":"client_os",
        "Connected to device":"connected_to"
    })
    clients['VLAN'] = clients['VLAN'].fillna(0)
    clients['Switchport'] = clients['Switchport'].fillna(0)
    clients['client_man'] = clients['client_man'].fillna('Unknown')
    clients['client_os'] = clients['client_os'].fillna('Unknown')
    clients = clients.astype({"VLAN":int, "Switchport":str})
    clients = clients.fillna('None')
    
    # 2. 分页（先过滤后分页）
    clients = clients.iloc[start:start+length]
    clients = clients.values.tolist()
    
    response = {
        "draw": draw,
        "recordsTotal": recordsTotal,
        "recordsFiltered": recordsFiltered,
        "data": clients,
    }
    
    return JsonResponse(response, safe=False)

二、修复排序功能 #

DataTables会传递排序的列索引与方向，需映射到CSV列名并实现排序逻辑：

更新json_response函数，添加排序处理：

def json_response(request):
    draw = int(request.GET.get('draw'))
    length = int(request.GET.get('length'))
    start = int(request.GET.get('start'))
    substring = str(request.GET.get('search[value]', ''))
    
    # 获取排序参数
    order_column_index = int(request.GET.get('order[0][column]', 0))
    order_dir = request.GET.get('order[0][dir]', 'asc')
    
    path = '/XX/XX/XXX/XXX.csv'
    clients = pd.read_csv(path)
    recordsTotal = len(clients) 
    
    # 搜索过滤
    if substring.strip():
        mask = clients.apply(lambda x: x.astype(str).str.contains(substring, case=False).any(), axis=1)
        clients = clients[mask]
    recordsFiltered = len(clients)
    
    # 列索引与CSV原始列名映射（需与HTML表头顺序完全对应）
    column_mapping = [
        'Network',
        'Client description',
        'Client IP',
        'Client MAC',
        'Client Manufacturer',
        'Client OS',
        'Connected to device',
        'VLAN',
        'Switchport',
        'Status'
    ]
    sort_column = column_mapping[order_column_index]
    
    # 执行排序
    clients = clients.sort_values(by=sort_column, ascending=(order_dir == 'asc'))
    
    # 列格式处理（同之前的代码）
    clients = clients.rename(columns={
        "Client description":"client_descr",
        "Client IP":"client_ip",
        "Client MAC":"client_mac",
        "Client Manufacturer":"client_man",
        "Client OS":"client_os",
        "Connected to device":"connected_to"
    })
    clients['VLAN'] = clients['VLAN'].fillna(0)
    clients['Switchport'] = clients['Switchport'].fillna(0)
    clients['client_man'] = clients['client_man'].fillna('Unknown')
    clients['client_os'] = clients['client_os'].fillna('Unknown')
    clients = clients.astype({"VLAN":int, "Switchport":str})
    clients = clients.fillna('None')
    
    # 分页
    clients = clients.iloc[start:start+length]
    clients = clients.values.tolist()
    
    response = {
        "draw": draw,
        "recordsTotal": recordsTotal,
        "recordsFiltered": recordsFiltered,
        "data": clients,
    }
    
    return JsonResponse(response, safe=False)

注意：需确保column_mapping中的列名与你的CSV原始列名完全一致，否则排序会失效。另外HTML表头中的"Conntect to device"存在拼写错误，建议更正为"Connect to device"。

三、添加PDF/CSV/Excel导出按钮 #

使用DataTables的Buttons扩展实现导出功能，步骤如下：

1. 引入必要的资源文件 #

在HTML的<head>中添加：

<!-- DataTables Buttons样式 -->
<link rel="stylesheet" href="https://cdn.datatables.net/buttons/2.4.2/css/buttons.dataTables.min.css">

<!-- 依赖JS -->
<script src="https://code.jquery.com/jquery-3.7.0.min.js"></script>
<script src="https://cdn.datatables.net/1.13.6/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/buttons/2.4.2/js/dataTables.buttons.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.10.1/jszip.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.2.7/pdfmake.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.2.7/vfs_fonts.js"></script>
<script src="https://cdn.datatables.net/buttons/2.4.2/js/buttons.html5.min.js"></script>

2. 修改DataTables初始化代码 #

添加按钮配置，并支持导出全部数据：

<script>
$( document ).ready(function() {
    $("#table").DataTable({
        "processing": true,
        "serverSide": true,
        "scrollX": true,
        "ajax": {
            url:'{% url "json_response" %}',
            dataSrc:"data"
        },
        // 配置布局，让按钮显示在表格上方
        "dom": 'Bfrtip',
        "buttons": [
            {
                extend: 'csvHtml5',
                text: '导出CSV',
                charset: 'UTF-8',
                bom: true, // 解决中文乱码
                exportOptions: {
                    modifier: { page: 'all' } // 导出所有过滤后的数据
                }
            },
            {
                extend: 'excelHtml5',
                text: '导出Excel',
                exportOptions: {
                    modifier: { page: 'all' }
                }
            },
            {
                extend: 'pdfHtml5',
                text: '导出PDF',
                exportOptions: {
                    modifier: { page: 'all' }
                }
            }
        ],
        "order": [[0, 'asc']] // 默认排序规则
    });
});
</script>

3. 后端适配导出全部数据的请求 #

当导出按钮触发时，DataTables会传递length=-1，需在json_response中处理：

# 搜索过滤后添加以下代码
if length == -1:
    length = recordsFiltered  # 导出所有过滤后的数据

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