ASP.NET Core 6 MVC实现类似Excel的DataTable列筛选表头

1. Fix Client-Side DataTable Initialization #

Update your JavaScript code to add column-specific filters using DataTables' initComplete callback. This injects filter inputs/dropdowns into each header cell:

$("#DATATABLE").DataTable({
    serverSide: true,
    searchDelay: 1000,
    scrollY: StaticData.TABLE_HEIGHT + 'px',
    lengthMenu: StaticData.TABLE_PAGE_SIZE,
    scrollCollapse: true,
    ajax: {
        url: '/STUD_MANAGEMENT/LoadStudents',
        type: 'GET',
        datatype: 'json',
        headers: { 'RequestVerificationToken': 'your json token' },
        dataSrc: (json) => json.data // Simplified since checkbox rendering is moved to server
    },
    columnDefs: [{ className: "dt-center", targets: [3], width: '10%' }], // Corrected target index
    columns: [
        { data: 'STUD_ID', title: 'STUD ID', autoWidth: false, visible: false },
        { data: 'NAME', title: 'Name', autoWidth: true },
        { data: 'CLASS', title: 'Class', autoWidth: true },
        { data: 'isactive', title: 'Is Active ?', autoWidth: false, orderable: false },
        { data: 'USER', title: 'User', autoWidth: true },
        { data: 'DATE', title: 'Date', autoWidth: true },
    ],
    initComplete: function () {
        this.api().columns().every(function () {
            var column = this;
            var columnTitle = $(column.header()).text().trim();
            
            if (column.visible()) {
                // Add dropdown for boolean "Is Active" column
                if (columnTitle === 'Is Active ?') {
                    const select = $('<select class="form-control form-control-sm"><option value="">All</option><option value="true">Active</option><option value="false">Inactive</option></select>')
                        .appendTo($(column.header()).empty())
                        .on('change', function () {
                            const val = $(this).val();
                            column.search(val || '', false, false).draw();
                        });
                } 
                // Add text input for other columns
                else {
                    const input = $('<input type="text" placeholder="Filter..." class="form-control form-control-sm">')
                        .appendTo($(column.header()).empty())
                        .on('keyup change clear', function () {
                            if (column.search() !== this.value) {
                                column.search(this.value).draw();
                            }
                        });
                }
            }
        });
    }
});

Key Fixes & Additions: #

• Corrected Column Target: Fixed columnDefs.targets from [7] to [3] (matches the "Is Active ?" column index).
• InitComplete Callback: Adds filter inputs/dropdowns after the table loads.
• Specialized Filter for Boolean Column: Uses a dropdown instead of text input for the "Is Active" column for better UX.
• Simplified DataSrc: Moved checkbox rendering to the server for efficiency.

2. Server-Side Filter Handling #

Modify your LoadStudents action to process column filter parameters sent by DataTables. First, create models to bind the DataTables request:

// DataTables Request Models
public class DataTableRequest
{
    public int Draw { get; set; }
    public int Start { get; set; }
    public int Length { get; set; }
    public Search Search { get; set; }
    public List<Column> Columns { get; set; }
    public List<Order> Order { get; set; }
}

public class Search
{
    public string Value { get; set; }
    public bool Regex { get; set; }
}

public class Column
{
    public string Data { get; set; }
    public string Name { get; set; }
    public bool Searchable { get; set; }
    public bool Orderable { get; set; }
    public Search Search { get; set; }
}

public class Order
{
    public int Column { get; set; }
    public string Dir { get; set; }
}

Then update the controller action:

public IActionResult LoadStudents([FromQuery] DataTableRequest request)
{
    var dbContext = _context; // Inject your DbContext
    var query = dbContext.Students.AsQueryable();

    // Apply column filters
    foreach (var column in request.Columns)
    {
        if (!string.IsNullOrEmpty(column.Search.Value))
        {
            switch (column.Data)
            {
                case "NAME":
                    query = query.Where(s => s.Name.Contains(column.Search.Value));
                    break;
                case "CLASS":
                    query = query.Where(s => s.Class.Contains(column.Search.Value));
                    break;
                case "isactive":
                    if (bool.TryParse(column.Search.Value, out bool isActive))
                        query = query.Where(s => s.IsActive == isActive);
                    break;
                case "USER":
                    query = query.Where(s => s.User.Contains(column.Search.Value));
                    break;
                case "DATE":
                    if (DateTime.TryParse(column.Search.Value, out DateTime filterDate))
                        query = query.Where(s => s.Date.Date == filterDate.Date);
                    break;
            }
        }
    }

    // Apply sorting
    if (request.Order.Any())
    {
        var orderCol = request.Columns[request.Order[0].Column];
        query = request.Order[0].Dir switch
        {
            "asc" => orderCol.Data switch
            {
                "NAME" => query.OrderBy(s => s.Name),
                "CLASS" => query.OrderBy(s => s.Class),
                "USER" => query.OrderBy(s => s.User),
                "DATE" => query.OrderBy(s => s.Date),
                _ => query
            },
            "desc" => orderCol.Data switch
            {
                "NAME" => query.OrderByDescending(s => s.Name),
                "CLASS" => query.OrderByDescending(s => s.Class),
                "USER" => query.OrderByDescending(s => s.User),
                "DATE" => query.OrderByDescending(s => s.Date),
                _ => query
            },
            _ => query
        };
    }

    // Calculate record counts
    int totalRecords = dbContext.Students.Count();
    int filteredRecords = query.Count();

    // Fetch paginated data (render checkbox for IsActive)
    var data = query.Skip(request.Start).Take(request.Length)
        .Select(s => new
        {
            STUD_ID = s.Id,
            NAME = s.Name,
            CLASS = s.Class,
            isactive = s.IsActive ? "<input type='checkbox' checked disabled>" : "<input type='checkbox' disabled>",
            USER = s.User,
            DATE = s.Date.ToString("yyyy-MM-dd")
        }).ToList();

    // Return DataTables-compatible response
    return Json(new
    {
        draw = request.Draw,
        recordsTotal = totalRecords,
        recordsFiltered = filteredRecords,
        data = data
    });
}

Key Server-Side Changes: #

• Request Binding: Uses DataTableRequest model to parse DataTables' query parameters.
• Column Filter Logic: Applies filters based on each column's search value.
• Sorting: Handles ascending/descending sorting for visible columns.
• Proper Response: Returns required fields (draw, recordsTotal, recordsFiltered, data) for DataTables to render correctly.

3. Verify Dependencies #

Ensure you have the required DataTables assets included in your view:

• DataTables CSS (e.g., <link rel="stylesheet" href="https://cdn.datatables.net/1.13.4/css/dataTables.bootstrap5.min.css">)
• DataTables JS (e.g., <script src="https://cdn.datatables.net/1.13.4/js/jquery.dataTables.min.js"></script>)
• Bootstrap CSS/JS (if using form-control classes for filters)

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