DataGridView导出Excel遇空引用异常及后期绑定错误求助

一、先修复System.NullReferenceException空引用错误 #

你的代码里有两处直接引发空引用的问题：

1. 未实例化Excel应用对象：excelapp仅做了声明，但实例化代码被注释，导致后续调用excelapp.Workbooks.Add时对象为null。
2. 提前初始化工作表对象：在excelworkbook还未赋值时，就尝试通过excelworkbook.Worksheets(1)获取工作表，此时excelworkbook是null，直接触发空引用。

修复核心步骤：

• 取消注释（或重新添加）Excel应用的实例化代码
• 调整对象初始化顺序：先创建应用，再创建工作簿，最后获取工作表

二、解决Option Strict On不允许后期绑定错误 #

当Option Strict On时，所有对象类型必须明确，不能使用隐式后期绑定。具体处理：

• 对DataGridView单元格的值做显式类型转换，避免隐式类型推断
• 使用Excel Interop的Value2属性代替Value，Value2是强类型属性，更符合严格类型要求

修正后的完整代码 #

Imports Excel = Microsoft.Office.Interop.Excel

Private Sub SaveToExcelButton_Click(sender As Object, e As EventArgs) Handles SaveToExcelButton.Click
    ' 实例化Excel应用对象
    Dim excelapp As New Excel.Application()
    Dim excelworkbook As Excel._Workbook
    Dim excelWorkSheet As Excel._Worksheet
    Dim misValue As Object = System.Reflection.Missing.Value

    ' 创建工作簿
    excelworkbook = excelapp.Workbooks.Add(misValue)
    ' 强类型转换获取工作表
    excelWorkSheet = CType(excelworkbook.Sheets(1), Excel.Worksheet)

    ' 设置表头，使用Value2避免后期绑定
    excelWorkSheet.Cells(1, 1).Value2 = "Driver"
    excelWorkSheet.Cells(1, 2).Value2 = "Fuel"
    excelWorkSheet.Cells(1, 3).Value2 = "Volume Per Hour"
    excelWorkSheet.Cells(1, 4).Value2 = "Fuel Per Lap"
    excelWorkSheet.Cells(1, 5).Value2 = "Time"
    excelWorkSheet.Cells(1, 6).Value2 = "Speed"

    ' 遍历DataGridView行，显式转换单元格值类型
    For rowIdx As Integer = 0 To Grid1.Rows.Count - 1
        ' 跳过自动生成的新行
        If Grid1.Rows(rowIdx).IsNewRow Then Continue For

        ' 处理空值并显式转换类型，符合Option Strict On要求
        excelWorkSheet.Cells(rowIdx + 2, 1).Value2 = If(Grid1.Rows(rowIdx).Cells(2).Value Is Nothing, "", CStr(Grid1.Rows(rowIdx).Cells(2).Value))
        excelWorkSheet.Cells(rowIdx + 2, 2).Value2 = If(Grid1.Rows(rowIdx).Cells(5).Value Is Nothing, "", CStr(Grid1.Rows(rowIdx).Cells(5).Value))
        excelWorkSheet.Cells(rowIdx + 2, 3).Value2 = If(Grid1.Rows(rowIdx).Cells(6).Value Is Nothing, "", CStr(Grid1.Rows(rowIdx).Cells(6).Value))
        excelWorkSheet.Cells(rowIdx + 2, 4).Value2 = If(Grid1.Rows(rowIdx).Cells(7).Value Is Nothing, "", CStr(Grid1.Rows(rowIdx).Cells(7).Value))
        excelWorkSheet.Cells(rowIdx + 2, 5).Value2 = If(Grid1.Rows(rowIdx).Cells(9).Value Is Nothing, "", CStr(Grid1.Rows(rowIdx).Cells(9).Value))
        excelWorkSheet.Cells(rowIdx + 2, 6).Value2 = If(Grid1.Rows(rowIdx).Cells(10).Value Is Nothing, "", CStr(Grid1.Rows(rowIdx).Cells(10).Value))
    Next

    ' 显示Excel窗口
    excelapp.Visible = True

    ' 释放资源，防止Excel进程后台残留
    GC.Collect()
    GC.WaitForPendingFinalizers()
End Sub

额外注意事项 #

• 确认Grid1是你的DataGridView控件的正确名称，避免控件名称不匹配导致空引用
• 若允许DataGridView添加新行，必须跳过IsNewRow的行，避免读取空值引发错误
• 添加空值判断，防止单元格值为null时转换失败
• 始终记得释放Excel相关资源，避免后台残留Excel进程

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