VB.NET使用Interop修改Excel单元格颜色无变化，如何排查代码问题？

核心错误点 #

• 缺少保存逻辑：单元格修改完成后未执行工作簿保存操作，修改仅存在于内存中未写入磁盘，因此看不到文件变化
• 只读模式冲突：Button2中打开Excel文件时，Workbooks.Open的第三个参数设置为True，是以只读模式加载文件，直接导致后续修改无法写入
• 多实例资源冲突：Button1和Button2分别创建了独立的Excel.Application实例，两个实例的操作互不关联，且操作完成后未释放COM对象，会导致后台残留Excel进程占用文件锁
• 缺少边界校验：未判断用户是否已选择文件、是否选中工作表，容易触发空引用异常

修正后代码 #

首先请先在代码顶部引入命名空间：

Imports Microsoft.Office.Interop
Imports System.Runtime.InteropServices

功能代码如下：

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    ' 先清空下拉框原有内容
    ComboBox1.Items.Clear()
    If OpenFileDialog1.ShowDialog() <> DialogResult.OK Then Exit Sub
    TextBox1.Text = OpenFileDialog1.FileName
    
    Dim xlApp As New Excel.Application()
    Dim xlWorkbook As Excel.Workbook = Nothing
    Dim xlWorkSheet As Excel.Worksheet = Nothing
    Try
        ' 只读打开仅读取工作表名称，读完立即释放
        xlWorkbook = xlApp.Workbooks.Open(TextBox1.Text, [ReadOnly]:=True)
        For Each xlWorkSheet In xlWorkbook.Sheets
            ComboBox1.Items.Add(xlWorkSheet.Name)
        Next
    Catch ex As Exception
        MessageBox.Show($"加载工作表失败：{ex.Message}")
    Finally
        ' 释放COM对象，避免残留进程
        If xlWorkSheet IsNot Nothing Then Marshal.ReleaseComObject(xlWorkSheet)
        If xlWorkbook IsNot Nothing Then
            xlWorkbook.Close(SaveChanges:=False)
            Marshal.ReleaseComObject(xlWorkbook)
        End If
        xlApp.Quit()
        Marshal.ReleaseComObject(xlApp)
    End Try
End Sub

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    ' 边界校验
    If String.IsNullOrEmpty(TextBox1.Text) OrElse ComboBox1.SelectedItem Is Nothing Then
        MessageBox.Show("请先选择Excel文件和对应工作表")
        Exit Sub
    End If
    
    Dim xlApp As New Excel.Application()
    Dim xlWorkbook As Excel.Workbook = Nothing
    Dim ws1 As Excel.Worksheet = Nothing
    Dim rangeToFormat As Excel.Range = Nothing
    Try
        ' 非只读模式打开文件
        xlWorkbook = xlApp.Workbooks.Open(TextBox1.Text, [ReadOnly]:=False)
        ws1 = xlWorkbook.Worksheets(ComboBox1.SelectedItem.ToString())
        rangeToFormat = ws1.Cells(6, 5)
        rangeToFormat.Interior.Color = ColorTranslator.ToOle(Color.Red)
        ' 保存修改
        xlWorkbook.Save()
        MessageBox.Show("修改完成")
    Catch ex As Exception
        MessageBox.Show($"修改单元格失败：{ex.Message}")
    Finally
        ' 释放所有COM对象
        If rangeToFormat IsNot Nothing Then Marshal.ReleaseComObject(rangeToFormat)
        If ws1 IsNot Nothing Then Marshal.ReleaseComObject(ws1)
        If xlWorkbook IsNot Nothing Then
            xlWorkbook.Close(SaveChanges:=True)
            Marshal.ReleaseComObject(xlWorkbook)
        End If
        xlApp.Quit()
        Marshal.ReleaseComObject(xlApp)
    End Try
End Sub

注意事项 #

• 运行前请确保项目已正确引用Microsoft.Office.Interop.Excel程序集
• 测试时请先关闭正在打开的目标Excel文件，避免文件被占用导致修改失败
• 若修改后仍无效果，可检查目标Excel文件是否设置了系统级只读属性，或者处于受保护视图状态

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