[VB.Net] 使用ClosedXML匯出資料到 Excel

 匯出 DataGridView 資料到 Excel 

Imports ClosedXML.Excel
Using stream As FileStream = New FileStream(filePath, FileMode.Open, FileAccess.ReadWrite)
    Using memoryStream As New MemoryStream()
        ' 初始化ExcelPackage，並讀取模板
        Using workbook As New XLWorkbook(stream)
            Dim worksheet = workbook.Worksheet(1) ' 假設操作第一個工作表
            #Region "塞入欄位"
            worksheet.Cells("A6").Value = "需填入的值"
            #End Region
            '儲存 Excel 文件到 MemoryStream
            workbook.SaveAs(memoryStream)
        End Using

        Dim sfd As New SaveFileDialog()
        sfd.Filter = "Excel(*.xlsx)|*.xlsx"
        ' 設定預設檔案名稱
        sfd.FileName = "檔案名稱" + ".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

參考網址: 

https://blog.csdn.net/huangcailian/article/details/16880513

https://blog.darkthread.net/blog/closedxml/

By Hao★