VB.NET 操作 Excel：放棄 COM Interop 改用 NPOI

COM Interop 痛點

• 需客戶機安裝完整 Office，版本不一致易報錯（0x80040154 類未註冊）。
• 進程殘留 (EXCEL.EXE 僵屍進程)，需手動 Marshal.ReleaseComObject。
• 部署複雜：PIA、註冊表、權限、DCOM 配置。
• 性能差：跨進程調用，寫入萬行數據需分鐘級。

NPOI 方案：純 .NET、無需 Office

Imports NPOI.SS.UserModel
Imports NPOI.XSSF.UserModel  ' .xlsx
Imports NPOI.HSSF.UserModel  ' .xls
Imports System.IO

Public Sub ExportToExcel(data As DataTable, filePath As String)
    Dim workbook As IWorkbook = New XSSFWorkbook()
    Dim sheet As ISheet = workbook.CreateSheet("Sheet1")

    ' 表頭
    Dim headerRow As IRow = sheet.CreateRow(0)
    For i As Integer = 0 To data.Columns.Count - 1
        headerRow.CreateCell(i).SetCellValue(data.Columns(i).ColumnName)
    Next

    ' 數據
    For r As Integer = 0 To data.Rows.Count - 1
        Dim row As IRow = sheet.CreateRow(r + 1)
        For c As Integer = 0 To data.Columns.Count - 1
            row.CreateCell(c).SetCellValue(data.Rows(r)(c).ToString())
        Next
    Next

    ' 自適應列寬
    For i As Integer = 0 To data.Columns.Count - 1
        sheet.AutoSizeColumn(i)
    Next

    Using fs As New FileStream(filePath, FileMode.Create, FileAccess.Write)
        workbook.Write(fs)
    End Using
End Sub

NuGet 安裝

Install-Package NPOI

進階功能

• 樣式：ICellStyle、IFont、BorderStyle。
• 公式：cell.SetCellFormula("SUM(A2:A10)")。
• 圖片：patriarch.CreatePicture(anchor, pictureIndex)。
• 流式寫入大文件：SXSSFWorkbook (僅 .xlsx)，內存常駐幾百行。

遷移清單