You need to enable JavaScript to run this app.
优惠活动
大模型
产品
解决方案
定价
更多

点击替换按钮后InsertStudentData无法插入Excel学生数据问题

avatar
数据老陈
2026-7-26
问题描述

服务器端有一个Index Action方法，负责将Excel数据插入数据库的Student表，逻辑如下：

  • 若同名文件已存在，则不上传该Excel文件
  • 若不存在，则上传文件名至Excel历史记录表，并导入Excel中的学生数据

当检测到同名文件（count>0）时会弹出覆盖模态框，但点击替换按钮后，调用InsertStudentData方法却没有数据插入。

服务器端Action代码 

// GET: Student
public ActionResult Index()
{
        var studentList = _studentDAL.GetallStudents();

        if (studentList.Count == 0)
        {
            TempData["InfoMessage"] = "Student data unavailable in the Database.";
        }

        return View(studentList);
}

[HttpPost]
public ActionResult Index(HttpPostedFileBase file)
{
        if (file != null && file.ContentLength > 0)
        {
            // 获取文件名
            var fileName = Path.GetFileName(file.FileName);

            // 将文件名存入TempData
            TempData["FileName"] = fileName;

            // 检查数据库中是否已存在同名文件
            using (var cmd = new SqlCommand("SELECT COUNT(*) FROM Excel WHERE FileName = @FileName", con))
            {
                cmd.Parameters.AddWithValue("@FileName", fileName);

                con.Open();
                int count = (int)cmd.ExecuteScalar();
                con.Close();

                // 如果count>0，说明同名文件已存在
                if (count > 0)
                {
                    ViewBag.FileExists = true;
                    ViewBag.Message = "A file with this name already exists in the database.";

                    return View();
                }
            }

            // 如果count=0，保存文件并将文件名插入数据库
            string filename = Guid.NewGuid() + Path.GetExtension(file.FileName);
            string filepath = "/excelfolder/" + filename;
            TempData["FilePath"] = filepath;
            file.SaveAs(Path.Combine(Server.MapPath("/excelfolder"), filename));
            InsertStudentdata(filepath, filename);

            using (var cmd = new SqlCommand("INSERT INTO Excel (FileName) VALUES (@FileName)", con))
            {
                cmd.Parameters.AddWithValue("@FileName", fileName);

                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }

            int fileID = 0;

            using (var cmd = new SqlCommand("SELECT MAX(ID) FROM Excel", con))
            {
                con.Open();
                object result = cmd.ExecuteScalar();

                if (result != DBNull.Value)
                {
                    fileID = Convert.ToInt32(result);
                }

                con.Close();
            }

            using (var cmd = new SqlCommand("UPDATE Student SET FileID = @fileID WHERE ImportDate = (SELECT MAX(ImportDate) FROM Student)", con))
            {
                cmd.Parameters.AddWithValue("@FileID", fileID);

                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }

        var studentList = _studentDAL.GetallStudents();

        if (studentList.Count == 0)
        {
            TempData["InfoMessage"] = "Student data unavailable in the Database.";
        }

        using (SqlConnection connection = new SqlConnection(conString))
        {
            connection.Open();

            using (SqlCommand command2 = new SqlCommand("UpdateMclStat", connection))
            {
                command2.CommandType = CommandType.StoredProcedure;
                command2.ExecuteNonQuery();
            }
        }

        return View(studentList);
}

[HttpPost]
public ActionResult Overwrite()
{
        // 从TempData中获取文件名
        var fileName = TempData["FileName"] as string;
        var filepath = TempData["FilePath"] as string;
   
        // 获取同名Excel文件的ID
        int fileId = 0;

        using (var cmd = new SqlCommand("SELECT TOP 1 ID FROM Excel WHERE FileName = @FileName ORDER BY ID DESC", con))
        {
            cmd.Parameters.AddWithValue("@FileName", fileName);

            con.Open();
            object result = cmd.ExecuteScalar();

            if (result != DBNull.Value)
            {
                fileId = Convert.ToInt32(result);
            }

            con.Close();
        }

        // 删除Student表中对应FileID的所有记录
        using (var cmd = new SqlCommand("DELETE FROM Student WHERE FileID = @FileID", con))
        {
            cmd.Parameters.AddWithValue("@FileID", fileId);

            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }

        return Json(new { success = true });
}

接收Excel文件的初始模态框代码 

<form method="post" enctype="multipart/form-data">
    <div style="margin-bottom:20px;">
        <div style="display: flex; justify-content: flex-end;">
            <input name="file" type="file" required style="background-color: lightgray; margin-right: 10px;" />
            <button id="importBtn" type="button" class="btn btn-primary" style="height: 34px; line-height: 34px;">Import</button>
        </div>
        <div id="confirmationModal" class="modal">
            <div class="modal-content ">
                <p>Are you sure you want to import?</p>
                <div class="modal-footer">
                    <button id="yesBtn" type="submit" class="btn btn-primary">Yes</button>
                    <button id="noBtn" type="button" class="btn btn-secondary">No</button>
                </div>
            </div>
        </div>
    </div>
</form>

<script src="~/Scripts/CustomJS/ModalImport.js"></script>

显示替换按钮的覆盖模态框代码 

<form id="myForm" method="post" action="@Url.Action("Index", "Student")">
    <!-- 隐藏输入框存储文件名 -->
    <input type="hidden" name="filename" value="@ViewBag.FileName" />

    @if (ViewBag.FileExists == true)
    {
        <!-- 文件已存在模态框 -->
        <div class="modal" id="file-exists-modal" tabindex="-1" role="dialog" style="display: block; background: rgba(0,0,0,0.5);">
            <div class="modal-dialog" role="document">
                <div class="modal-content">
                    <div class="modal-header">
                        <h5 class="modal-title">Overwrite</h5>
                        <button type="button" class="close" data-dismiss="modal" aria-label="Close">
                            <span aria-hidden="true">&times;</span>
                        </button>
                    </div>
                    <div class="modal-body">
                        <p>A file with the same name already exists.</p>
                    </div>
                    <div class="modal-footer">
                        <!-- 点击替换按钮时，给action参数赋值"replace" -->
                        <button id="replaceButton" type="submit" name="action" value="replace">Replace</button>

                        <button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button>
                    </div>
                </div>
            </div>
        </div>
    }
</form>

<script>
$('#replaceButton').click(function (event) {
    event.preventDefault(); // 阻止默认表单提交行为
    $.ajax({
               type: 'POST',
               url: '/Student/OverWrite',
               success: function (data) {
                   // 刷新页面更新表格
                   location.reload();
               },
               error: function () {
                   alert('An error occurred while deleting the data.');
               }
    });
});
</script>

Excel数据插入方法代码 

private void InsertStudentdata(string fileepath, string filename)
{
    string fullpath = Server.MapPath("/excelfolder/") + filename;

    ExcelConn(fullpath);

    string query = string.Format("Select * from [{0}]", "Sheet1$");

    OleDbCommand Ecom = new OleDbCommand(query, Econ);
    Econ.Open();

    DataSet ds = new DataSet();
    OleDbDataAdapter oda = new OleDbDataAdapter(query, Econ);

    Econ.Close();

    oda.Fill(ds);

    DataTable dt = ds.Tables[0];

    SqlBulkCopy objbulk = new SqlBulkCopy(con);
    objbulk.DestinationTableName = "dbo.Student";

    objbulk.ColumnMappings.Add("StudentID", "StudentID");
    objbulk.ColumnMappings.Add("FirstName", "FirstName");
    objbulk.ColumnMappings.Add("LastName", "LastName");
    objbulk.ColumnMappings.Add("College", "College");
    objbulk.ColumnMappings.Add("Program", "Program");
    objbulk.ColumnMappings.Add("YearGraduate", "YearGraduate");
    objbulk.ColumnMappings.Add("DateExam", "DateExam");
    objbulk.ColumnMappings.Add("TakeNumber", "TakeNumber");
    objbulk.ColumnMappings.Add("BoardScore", "BoardScore");
    objbulk.ColumnMappings.Add("FileID", "FileID");
    objbulk.ColumnMappings.Add("ImportDate", "ImportDate");

    con.Open();
    objbulk.WriteToServer(dt);
    con.Close();
}
问题分析与修复方案

核心问题

  1. 替换流程缺失文件导入逻辑：当前Overwrite Action只删除了旧数据，没有重新处理用户上传的Excel文件，也没调用InsertStudentdata导入新数据。
  2. TempData无法保留文件内容：TempData中的文件路径和内容在页面返回后会失效，替换时没有可处理的文件。
  3. 模态框表单未携带文件数据：覆盖模态框的表单没有文件上传控件，无法提交新的Excel文件。

修复步骤

1. 修改覆盖模态框，添加文件上传控件

让用户在替换时可以重新选择文件：

@if (ViewBag.FileExists == true)
{
    <div class="modal" id="file-exists-modal" tabindex="-1" role="dialog" style="display: block; background: rgba(0,0,0,0.5);">
        <div class="modal-dialog" role="document">
            <div class="modal-content">
                <div class="modal-header">
                    <h5 class="modal-title">替换文件</h5>
                    <button type="button" class="close" data-dismiss="modal" aria-label="Close">
                        <span aria-hidden="true">&times;</span>
                    </button>
                </div>
                <div class="modal-body">
                    <p>同名文件已存在，请选择要替换的新文件：</p>
                    <input name="file" type="file" required style="background-color: lightgray; margin-top:10px;" />
                </div>
                <div class="modal-footer">
                    <button id="replaceButton" type="submit" name="action" value="replace">替换并导入</button>
                    <button type="button" class="btn btn-secondary" data-dismiss="modal">取消</button>
                </div>
            </div>
        </div>
    </div>
}

2. 整合替换逻辑到Index POST方法

修改Index的POST方法，通过action参数区分新增/替换操作：

[HttpPost]
public ActionResult Index(HttpPostedFileBase file, string action)
{
    if (file != null && file.ContentLength > 0)
    {
        var fileName = Path.GetFileName(file.FileName);
        TempData["FileName"] = fileName;

        // 处理替换逻辑
        if (action == "replace")
        {
            // 获取旧文件ID并删除关联数据
            int oldFileId = 0;
            using (var cmd = new SqlCommand("SELECT TOP 1 ID FROM Excel WHERE FileName = @FileName ORDER BY ID DESC", con))
            {
                cmd.Parameters.AddWithValue("@FileName", fileName);
                con.Open();
                object result = cmd.ExecuteScalar();
                if (result != DBNull.Value) oldFileId = Convert.ToInt32(result);
                con.Close();
            }

            // 删除旧学生数据和Excel记录
            using (var cmd = new SqlCommand("DELETE FROM Student WHERE FileID = @FileID", con))
            {
                cmd.Parameters.AddWithValue("@FileID", oldFileId);
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
            using (var cmd = new SqlCommand("DELETE FROM Excel WHERE ID = @FileID", con))
            {
                cmd.Parameters.AddWithValue("@FileID", oldFileId);
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }

            // 重新保存新文件并导入数据
            string newFileName = Guid.NewGuid() + Path.GetExtension(file.FileName);
            string filePath = "/excelfolder/" + newFileName;
            file.SaveAs(Path.Combine(Server.MapPath("/excelfolder"), newFileName));
            InsertStudentdata(filePath, newFileName);

            // 插入新Excel记录并更新FileID
            using (var cmd = new SqlCommand("INSERT INTO Excel (FileName) VALUES (@FileName)", con))
            {
                cmd.Parameters.AddWithValue("@FileName", fileName);
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }

            int newFileId = 0;
            using (var cmd = new SqlCommand("SELECT MAX(ID) FROM Excel", con))
            {
                con.Open();
                object result = cmd.ExecuteScalar();
                if (result != DBNull.Value) newFileId = Convert.ToInt32(result);
                con.Close();
            }

            using (var cmd = new SqlCommand("UPDATE Student SET FileID = @fileID WHERE ImportDate = (SELECT MAX(ImportDate) FROM Student)", con))
            {
                cmd.Parameters.AddWithValue("@FileID", newFileId);
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
        else
        {
            // 原有的新增逻辑（保持不变）
            using (var cmd = new SqlCommand("SELECT COUNT(*) FROM Excel WHERE FileName = @FileName", con))
            {
                cmd.Parameters.AddWithValue("@FileName", fileName);
                con.Open();
                int count = (int)cmd.ExecuteScalar();
                con.Close();

                if (count > 0)
                {
                    ViewBag.FileExists = true;
                    ViewBag.Message = "同名文件已存在于数据库中。";
                    return View(_studentDAL.GetallStudents());
                }
            }

            string filename = Guid.NewGuid() + Path.GetExtension(file.FileName);
            string filepath = "/excelfolder/" + filename;
            file.SaveAs(Path.Combine(Server.MapPath("/excelfolder"), filename));
            InsertStudentdata(filepath, filename);

            using (var cmd = new SqlCommand("INSERT INTO Excel (FileName) VALUES (@FileName)", con))
            {
                cmd.Parameters.AddWithValue("@FileName", fileName);
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }

            int fileID = 0;
            using (var cmd = new SqlCommand("SELECT MAX(ID) FROM Excel", con))
            {
                con.Open();
                object result = cmd.ExecuteScalar();
                if (result != DBNull.Value) fileID = Convert.ToInt32(result);
                con.Close();
            }

            using (var cmd = new SqlCommand("UPDATE Student SET FileID = @fileID WHERE ImportDate = (SELECT MAX(ImportDate) FROM Student)", con))
            {
                cmd.Parameters.AddWithValue("@FileID", fileID);
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
    }

    var studentList = _studentDAL.GetallStudents();
    if (studentList.Count == 0)
    {
        TempData["InfoMessage"] = "数据库中无学生数据。";
    }

    using (SqlConnection connection = new SqlConnection(conString))
    {
        connection.Open();
        using (SqlCommand command2 = new SqlCommand("UpdateMclStat", connection))
        {
            command2.CommandType = CommandType.StoredProcedure;
            command2.ExecuteNonQuery();
        }
    }

    return View(studentList);
}

3. 删除冗余的Overwrite Action并修改JS

删除原Overwrite Action，修改模态框JS直接提交表单：

<script>
$('#replaceButton').click(function (event) {
    $('#myForm').submit();
});
</script>

4. 优化InsertStudentdata方法（可选）

确保数据读取和BulkCopy逻辑稳定：

private void InsertStudentdata(string filename)
{
    string fullpath = Server.MapPath("/excelfolder/") + filename;
    using (OleDbConnection econ = ExcelConn(fullpath))
    {
        string query = "Select * from [Sheet1$]";
        using (OleDbDataAdapter oda = new OleDbDataAdapter(query, econ))
        {
            DataSet ds = new DataSet();
            oda.Fill(ds);
            if (ds.Tables.Count == 0) return;

            DataTable dt = ds.Tables[0];
            // 确保目标列存在
            if (!dt.Columns.Contains("FileID")) dt.Columns.Add("FileID",
相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
火山引擎 最新活动
全面开放
Doubao-Seedance-2.5全新模型升级

模型再升级,30秒超长叙事, 模态参考扩容

HOT
方舟Codingplan

模型自由，工具不限，最新支持 Deepseek-V4 系列、GLM-5.3 系列

HOT
方舟Agent plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列

HOT
新用户特惠专场

大模型19元起，Al应用9.9元畅享，新人首购爆款尽享优惠

最近更新时间：2026.07.26 23:40:48