如何将Datagridview导出到Excel工作簿 现有代码问题求助

问题原因分析 #

第一段空值报错代码问题 #

• 单元格值为空时直接调用cell.Value.ToString()触发空引用异常
• Excel对象操作逻辑错误，Worksheets.Add方法不支持直接传入DataTable作为参数

第二段缺列无表头代码问题 #

• 未单独写入DataGridView的表头行，导出文件没有列名
• 手动单独写入id列后又循环遍历所有列，导致列重复覆盖、后续列偏移丢失
• 列循环起始索引逻辑错误，导致列数不全

修复后可正常运行的实现代码 #

Private Sub ExportToExcel()
    Dim ExcelApp As Object, ExcelBook As Object, ExcelSheet As Object
    Dim i As Integer, j As Integer
    Dim exportPath As String = "C:\Users\" & Environment.UserName & "\Desktop\SkyNet Exports"
    
    ' 创建导出目录
    If Not My.Computer.FileSystem.DirectoryExists(exportPath) Then
        My.Computer.FileSystem.CreateDirectory(exportPath)
    End If

    ' 初始化Excel对象
    ExcelApp = CreateObject("Excel.Application")
    ExcelBook = ExcelApp.WorkBooks.Add
    ExcelSheet = ExcelBook.WorkSheets(1)

    ' 写入表头
    For j = 0 To DataGridView1.ColumnCount - 1
        ExcelSheet.cells(1, j + 1) = DataGridView1.Columns(j).HeaderText
    Next

    ' 写入行数据，处理空值
    For i = 0 To DataGridView1.RowCount - 1
        ' 跳过DataGridView的新行占位行
        If DataGridView1.Rows(i).IsNewRow Then Continue For
        For j = 0 To DataGridView1.ColumnCount - 1
            ' 空值处理，避免空引用报错
            ExcelSheet.cells(i + 2, j + 1) = If(DataGridView1.Rows(i).Cells(j).Value IsNot Nothing, DataGridView1.Rows(i).Cells(j).Value.ToString(), "")
        Next
    Next

    ' 保存文件
    ExcelBook.SaveAs(exportPath & "\SkyNet Export.xlsx")
    ExcelApp.Visible = True

    ' 释放COM对象，避免Excel进程残留
    ReleaseComObject(ExcelSheet)
    ReleaseComObject(ExcelBook)
    ReleaseComObject(ExcelApp)
End Sub

Private Sub ReleaseComObject(obj As Object)
    Try
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
    Catch ex As Exception
        obj = Nothing
    Finally
        GC.Collect()
    End Try
End Sub

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