[VB.Net] 匯出 DataGridView 資料到 Excel

匯出 DataGridView 資料到 Excel 

Imports System.Runtime.InteropServices
Private Sub btnExport_Click(sender As Object, e As EventArgs) Handles btnExport.Click
    ' 檢查DataGridView是否有數據
    If dgv1.Rows.Count = 0 Then
        MessageBox.Show("沒有資料可以匯出", "訊息", MessageBoxButtons.OK, MessageBoxIcon.Hand)
        Return
    End If
    ' 檢查是否安裝Excel
    If Not IsExcelInstalled() Then
        MessageBox.Show("這台電腦沒有安裝 Office Excel，無法繼續執行！", "訊息", MessageBoxButtons.OK, MessageBoxIcon.Hand)
        Return
    End If
    Try
        ' 開始匯出資料到 Excel
        ExportDataGridViewToExcel(dgv1)
    Catch ex As Exception
        MessageBox.Show(ex.ToString, "匯出 Excel 發生錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error)
    End Try
End Sub

' 檢查是否安裝Excel
Private Function IsExcelInstalled() As Boolean
    Try
        Dim excelApp As New Excel.Application()
        If excelApp IsNot Nothing Then
            excelApp.Quit()
            Return True
        End If
    Catch ex As Exception
        Return False
    End Try
End Function

' 匯出 DataGridView 資料到 Excel
Private Sub ExportDataGridViewToExcel(dgv As DataGridView)
    Dim excelApp As New Excel.Application()
    Dim workbook As Excel.Workbook = excelApp.Workbooks.Add()
    Dim worksheet As Excel.Worksheet = CType(workbook.Sheets(1), Excel.Worksheet)

    ' 標題列 (排除 _RowID)
    Dim colIndex As Integer = 1
    For Each col As DataGridViewColumn In dgv.Columns
        If col.Name <> "_RowID" Then
            worksheet.Cells(1, colIndex) = col.HeaderText
            colIndex += 1
        End If
    Next

    ' 資料列
    Dim rowIndex As Integer = 2
    For Each row As DataGridViewRow In dgv.Rows
        If Not row.IsNewRow Then
            colIndex = 1
            For Each cell As DataGridViewCell In row.Cells
                If dgv.Columns(cell.ColumnIndex).Name <> "_RowID" Then
                    worksheet.Cells(rowIndex, colIndex) = cell.Value
                    colIndex += 1
                End If
            Next
            rowIndex += 1
        End If
    Next

    ' 自動調整列寬
    worksheet.Columns.AutoFit()

    ' 顯示 Excel
    excelApp.Visible = True

    ' 釋放資源
    Marshal.ReleaseComObject(worksheet)
    Marshal.ReleaseComObject(workbook)
    Marshal.ReleaseComObject(excelApp)
End Sub

參考網址: 

By Hao★