VB.NET使用Microsoft.Office.Interop无法覆盖指定路径Excel文件

程序功能：读取Documents\Source\EmployeeMgmt.xlsx中的数据存入MySQL，成功后关闭窗体，删除Excel文件中除标题行外的所有内容并保存修改。
异常现象：已指定路径，但程序未修改Documents\Source下的原文件，反而在My Documents文件夹新建了EmployeeMgmt.xlsx文件。使用Microsoft.Office.Interop.Excel操作Excel。

相关代码 #

Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click

    Try
        MySqlConn.Open()
        Dim savedRowsCount As Integer = 0

        For Each row As DataGridViewRow In dgvNew.Rows
            If Not row.IsNewRow AndAlso Not String.IsNullOrEmpty(row.Cells("col_ID").Value?.ToString()) Then
                Dim employeeID As String = row.Cells("col_ID").Value.ToString()

                Query = "SELECT COUNT(*) FROM employee_tb WHERE employee_ID = '" & employeeID & "'"
                COMMAND = New MySqlCommand(Query, MySqlConn)
                Dim result As Object = COMMAND.ExecuteScalar()

                If Convert.ToInt32(result) = 0 Then
                    Dim employeeName As String = row.Cells("col_Fullname").Value.ToString()
                    Dim gender As String = row.Cells("col_gender").Value.ToString()
                    Dim position As String = row.Cells("col_Position").Value.ToString()
                    Dim department As String = row.Cells("col_Department").Value.ToString()
                    Dim joinDate As String = row.Cells("col_JoinDate").Value.ToString()
                    Dim promoteDate As String = row.Cells("col_PromotionDate").Value.ToString()
                    Dim status As String = row.Cells("col_status").Value.ToString()

                    Dim queryInsert As String = "INSERT INTO employee_tb (employee_ID, employee_Name, gender, position, department, joinDate, promotionDate, status) VALUES ('" & employeeID & "', '" & employeeName & "', '" & gender & "', '" & position & "', '" & department & "', '" & joinDate & "', '" & promoteDate & "', '" & status & "')"
                    COMMAND = New MySqlCommand(queryInsert, MySqlConn)
                    COMMAND.ExecuteNonQuery()
                    savedRowsCount += 1
                End If
            End If
        Next
        MySqlConn.Close()
        KryptonMessageBox.Show("Success" & savedRowsCount & , "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)

        Dim ExcelApp As New Excel.Application()
        Dim WorkbookPath As String = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "Source", "EmployeeMgmt.xlsx")
        Dim Wb As Excel.Workbook = ExcelApp.Workbooks.Open(WorkbookPath, Editable:=True) 
        Dim Ws As Excel.Worksheet = DirectCast(Wb.Worksheets(1), Excel.Worksheet)

        Dim rowCount As Integer = Ws.UsedRange.Rows.Count
        For i As Integer = rowCount To 2 Step -1
            Dim range As Excel.Range = DirectCast(Ws.Rows(i), Excel.Range)
            range.Delete()
        Next

        Wb.Save()
        Wb.Close()
        ExcelApp.Quit()

        Me.Close()

    Catch ex As Exception
        KryptonMessageBox.Show("Err: " & ex.Message, "Alert", MessageBoxButtons.OK, MessageBoxIcon.Error)
        If MySqlConn.State = ConnectionState.Open Then
            MySqlConn.Close()
        End If
    End Try
End Sub

原因分析 #

1. 路径合法性未验证：代码未提前检查Documents\Source目录或目标Excel文件是否存在。若路径不存在，Path.Combine会生成合法路径，但Excel.Workbooks.Open方法找不到文件时，不会抛出异常，而是直接在Excel默认保存目录（通常是My Documents）新建文件。
2. 默认路径干扰：Interop Excel在未找到指定路径的文件时，会自动使用默认位置创建新文件，而非提示错误。

解决方案 #

1. 提前验证路径与文件存在性 #

在打开Excel文件前，先检查目录和文件是否存在，避免创建错误路径的文件：

Dim docsPath As String = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)
Dim sourcePath As String = Path.Combine(docsPath, "Source")
Dim WorkbookPath As String = Path.Combine(sourcePath, "EmployeeMgmt.xlsx")

' 检查Source目录是否存在，不存在则创建
If Not Directory.Exists(sourcePath) Then
    Directory.CreateDirectory(sourcePath)
End If

' 检查文件是否存在，不存在则提示并终止操作
If Not File.Exists(WorkbookPath) Then
    KryptonMessageBox.Show("目标文件不存在：" & WorkbookPath, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error)
    Return
End If

2. 强制指定保存路径 #

使用SaveAs方法明确指定保存路径，避免Excel使用默认目录：

' 替换原有的Wb.Save()，强制保存到目标路径
Wb.SaveAs(WorkbookPath)

3. 修复SQL注入风险（额外优化） #

当前代码直接拼接SQL语句存在严重安全隐患，改用参数化查询：

' 替换原有的存在性检查语句
Dim queryCheck As String = "SELECT COUNT(*) FROM employee_tb WHERE employee_ID = @EmployeeID"
COMMAND = New MySqlCommand(queryCheck, MySqlConn)
COMMAND.Parameters.AddWithValue("@EmployeeID", employeeID)
Dim result As Object = COMMAND.ExecuteScalar()

' 替换原有的插入语句
Dim queryInsert As String = "INSERT INTO employee_tb (employee_ID, employee_Name, gender, position, department, joinDate, promotionDate, status) VALUES (@EmployeeID, @EmployeeName, @Gender, @Position, @Department, @JoinDate, @PromotionDate, @Status)"
COMMAND = New MySqlCommand(queryInsert, MySqlConn)
COMMAND.Parameters.AddWithValue("@EmployeeID", employeeID)
COMMAND.Parameters.AddWithValue("@EmployeeName", employeeName)
COMMAND.Parameters.AddWithValue("@Gender", gender)
COMMAND.Parameters.AddWithValue("@Position", position)
COMMAND.Parameters.AddWithValue("@Department", department)
COMMAND.Parameters.AddWithValue("@JoinDate", joinDate)
COMMAND.Parameters.AddWithValue("@PromotionDate", promoteDate)
COMMAND.Parameters.AddWithValue("@Status", status)
COMMAND.ExecuteNonQuery()

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