[VB.Net] 使用 ClosedXML將DataGridView 匯出至 Excel

    使用 ClosedXML將DataGridView 匯出至 Excel 

Imports ClosedXML.Excel

Public Sub ExportToExcel()
    Using stream As FileStream = New FileStream("D:\01.Project\Template.xlsx", FileMode.Open, FileAccess.ReadWrite)
        Using memoryStream As New MemoryStream()
            ' 初始化ExcelPackage，並讀取模板'
            Using workbook As New XLWorkbook(stream)
                Dim ws As IXLWorksheet = workbook.Worksheet(1) ' 假設操作第一個工作表'

                ' 匯出 DataGridView 標題'
                For col As Integer = 0 To DataGridView1.Columns.Count - 1
                    ws.Cell(1, col + 1).Value = DataGridView1.Columns(col).HeaderText
                Next

                ' 匯出 DataGridView 內容'
                For row As Integer = 0 To DataGridView1.Rows.Count - 1
                    For col As Integer = 0 To DataGridView1.Columns.Count - 1
                        Dim cellValue = DataGridView1.Rows(row).Cells(col).Value
                        ws.Cell(row + 2, col + 1).Value = If(cellValue IsNot Nothing, cellValue.ToString(), "")
                    Next
                Next

                '儲存 Excel 文件到 MemoryStream'
                workbook.SaveAs(memoryStream)
            End Using

            Dim sfd As New SaveFileDialog()
            sfd.Filter = "Excel(*.xlsx)|*.xlsx"
            ' 設定預設檔案名稱'
            sfd.FileName = "Data.xlsx"
            If (sfd.ShowDialog() = DialogResult.OK) Then
                '將 MemoryStream 位置重設為 0 (從頭開始讀取)'
                memoryStream.Position = 0
                '將 Stream 轉換為 Byte 陣列並寫入檔案'
                My.Computer.FileSystem.WriteAllBytes(sfd.FileName, StreamToByteArray(memoryStream), False)
                MsgBox("Excel 檔案已成功儲存！", MsgBoxStyle.OkOnly + MsgBoxStyle.Information, "Success")
            End If
        End Using
    End Using
End Sub

' 將 Stream 轉換為 Byte 陣列的輔助函式'
Function StreamToByteArray(inputStream As Stream) As Byte()
    Using memoryStream As New MemoryStream()
        inputStream.CopyTo(memoryStream)
        Return memoryStream.ToArray()
    End Using
End Function

參考網址: 

By Hao★