ASP.NET Core MVC：如何在入库前将Excel数据展示到HTML表格

需求说明 #

用户选择本地的student.xlsx文件后，点击「Show Data」按钮，无需页面刷新即可将Excel中的数据展示到页面的HTML表格中；确认数据无误后，再提交数据到数据库入库。

现有代码 #

Model #

public class Students
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public int AdmissionYear { get; set; }
    public string RegistrationNo { get; set; }
}

Controller（原代码修正版） #

public async Task<IActionResult> Upload(IFormFile file)
{
    if (file == null)
    {
        return RedirectToAction(nameof(Index));
    }
    List<Students> studentList = new List<Students>(); // 修正原代码泛型类型错误
    
    using(var memoryStream = new MemoryStream())
    {
        await file.CopyToAsync(memoryStream).ConfigureAwait(false);
        using(var package = new ExcelPackage(memoryStream))
        {
            var worksheet = package.Workbook.Worksheets[0];
            if (worksheet.Dimension != null && worksheet.Dimension.Rows > 0 && worksheet.Dimension.Columns > 0)
            {
                for (int row = 2; row <= worksheet.Dimension.Rows; row++)   // 从第2行开始跳过表头
                {
                    Students student = new Students();
                    student.FirstName = worksheet.Cells[row, 1].Value?.ToString() ?? string.Empty;
                    student.LastName = worksheet.Cells[row, 2].Value?.ToString() ?? string.Empty;
                    // 处理AdmissionYear转换失败的情况
                    int.TryParse(worksheet.Cells[row, 3].Value?.ToString(), out int admissionYear);
                    student.AdmissionYear = admissionYear;
                    student.RegistrationNo = worksheet.Cells[row, 4].Value?.ToString() ?? string.Empty;
                    studentList.Add(student);
                }
            }
        }
    }
    return View("Index", studentList);
}

View（原代码优化版） #

<div class="form-group">
    <label name-for="file">Excel xlsx Upload</label>
    <input type="file" name="file" id="excelFile" accept=".xlsx" />      
</div>
<input type="button" name="display" value="Show Data" id="btnShow" class="btn btn-primary" />
<input type="button" name="submit" value="Submit to DB" id="btnSubmit" class="btn btn-success" style="display:none;" />

<table id="example1" class="table table-bordered table-striped">
    <thead>
    <tr>
        <th class="table-head">
           <input type="checkbox" onchange="checkAll(this)" name="chk[]" />
        </th>
        <th>First Name</th>
        <th>Last Name</th>
        <th>AdmissionYear</th>
        <th>RegistrationNo</th>
    </tr>
    </thead>
    <tbody id="studentTableBody">
        <tr>
            <td valign="middle" align="center" style="width: 2%;">
                <input type="checkbox" onchange="checkSingle(this)" name="chk[]" />
            </td>
            <td></td>
            <td></td>
            <td> </td>
            <td></td>
        </tr>
    </tbody>
</table>

实现步骤 #

1. 修改Controller，新增预览和入库接口 #

在原Controller中添加两个Action：一个用于处理Excel文件并返回预览数据的JSON接口，另一个用于接收选中的学生数据并入库。

// 预览Excel数据的接口
[HttpPost]
public async Task<IActionResult> PreviewExcel(IFormFile file)
{
    if (file == null || file.Length == 0 || !file.FileName.EndsWith(".xlsx"))
    {
        return BadRequest("请选择有效的.xlsx文件");
    }
    
    List<Students> studentList = new List<Students>();
    
    using(var memoryStream = new MemoryStream())
    {
        await file.CopyToAsync(memoryStream).ConfigureAwait(false);
        using(var package = new ExcelPackage(memoryStream))
        {
            var worksheet = package.Workbook.Worksheets[0];
            if (worksheet.Dimension != null && worksheet.Dimension.Rows > 1) // 确保有数据行
            {
                for (int row = 2; row <= worksheet.Dimension.Rows; row++)
                {
                    Students student = new Students();
                    student.FirstName = worksheet.Cells[row, 1].Value?.ToString() ?? string.Empty;
                    student.LastName = worksheet.Cells[row, 2].Value?.ToString() ?? string.Empty;
                    int.TryParse(worksheet.Cells[row, 3].Value?.ToString(), out int admissionYear);
                    student.AdmissionYear = admissionYear;
                    student.RegistrationNo = worksheet.Cells[row, 4].Value?.ToString() ?? string.Empty;
                    studentList.Add(student);
                }
            }
        }
    }
    
    return Json(new { success = true, data = studentList });
}

// 提交数据入库的接口
[HttpPost]
public async Task<IActionResult> SubmitStudents(List<Students> selectedStudents)
{
    if (selectedStudents == null || selectedStudents.Count == 0)
    {
        return BadRequest("请选择要提交的学生数据");
    }
    
    // 替换为实际入库逻辑，例如使用EF Core
    // _context.Students.AddRange(selectedStudents);
    // await _context.SaveChangesAsync();
    
    return Json(new { success = true, message = "数据提交成功" });
}

2. 前端添加AJAX逻辑，实现预览和提交 #

在View底部添加JavaScript代码，实现按钮的交互功能：

<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script>
$(document).ready(function() {
    // Show Data按钮点击事件
    $("#btnShow").click(function() {
        var file = $("#excelFile")[0].files[0];
        if (!file) {
            alert("请先选择Excel文件");
            return;
        }
        
        var formData = new FormData();
        formData.append("file", file);
        
        $.ajax({
            url: "@Url.Action("PreviewExcel")",
            type: "POST",
            data: formData,
            contentType: false,
            processData: false,
            success: function(response) {
                if (response.success) {
                    // 清空表格原有内容
                    $("#studentTableBody").empty();
                    
                    // 动态生成表格行
                    $.each(response.data, function(index, student) {
                        var row = `<tr data-index="${index}">
                            <td valign="middle" align="center" style="width: 2%;">
                                <input type="checkbox" class="studentChk" name="chk[]" value="${index}" />
                            </td>
                            <td>${student.firstName}</td>
                            <td>${student.lastName}</td>
                            <td>${student.admissionYear}</td>
                            <td>${student.registrationNo}</td>
                            <!-- 隐藏域存储学生数据，方便提交 -->
                            <input type="hidden" class="studentData" value='${JSON.stringify(student)}' />
                        </tr>`;
                        $("#studentTableBody").append(row);
                    });
                    
                    // 显示提交按钮
                    $("#btnSubmit").show();
                } else {
                    alert("预览失败：" + (response.message || "未知错误"));
                }
            },
            error: function(xhr) {
                alert("请求出错：" + xhr.responseText);
            }
        });
    });
    
    // Submit to DB按钮点击事件
    $("#btnSubmit").click(function() {
        var selectedStudents = [];
        
        // 收集选中的学生数据
        $(".studentChk:checked").each(function() {
            var studentData = $(this).closest("tr").find(".studentData").val();
            selectedStudents.push(JSON.parse(studentData));
        });
        
        if (selectedStudents.length == 0) {
            alert("请选择要提交的学生");
            return;
        }
        
        $.ajax({
            url: "@Url.Action("SubmitStudents")",
            type: "POST",
            contentType: "application/json",
            data: JSON.stringify(selectedStudents),
            success: function(response) {
                if (response.success) {
                    alert(response.message);
                    // 提交成功后重置页面状态
                    $("#studentTableBody").empty();
                    $("#excelFile").val("");
                    $("#btnSubmit").hide();
                } else {
                    alert("提交失败：" + response.message);
                }
            },
            error: function(xhr) {
                alert("提交出错：" + xhr.responseText);
            }
        });
    });
    
    // 全选/取消全选逻辑
    window.checkAll = function(checkbox) {
        $(".studentChk").prop("checked", checkbox.checked);
    };
    
    // 单个复选框点击逻辑，更新全选框状态
    window.checkSingle = function(checkbox) {
        var allChecked = $(".studentChk").length == $(".studentChk:checked").length;
        $("input[name='chk[]']:first").prop("checked", allChecked);
    };
});
</script>

3. 关键注意事项 #

• 确保项目已安装EPPlus NuGet包（用于Excel解析），可通过NuGet包管理器直接安装。
• 入库逻辑需根据实际数据库框架修改，例如使用Entity Framework Core时，需注入DbContext并完成数据持久化操作。
• 可增加更多数据校验逻辑，比如Excel单元格为空、数据格式不符合要求的情况，避免前端或后端报错。

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