【VB】使用NPOI元件來匯出Excel--DataTableToExcel

繼上篇"【VB】使用NPOI元件來匯出Excel"，這次則利用DataTableToExcel方式來實作，執行結果一樣，所以不在抓畫面了。

引用:
Imports System.IO
Imports System.Data
Imports NPOI.HSSF.UserModel
Imports NPOI.HPSF
Imports NPOI.POIFS.FileSystem
Imports NPOI.Util

程式碼(參考) : 本人做法是撈取DB內容，然後透過DataTable匯出Excel。
        Dim file_name As String = Format(Now, "yyyyMMdd") & "採購單.xls"
        Dim ms As MemoryStream = New MemoryStream()  '==需要 System.IO命名空間

        '抓DB
        Dim conn As String = Get_Val(Me.DropDownList1.SelectedValue)
        ads.ConnectionString = conn
        ads.SelectCommand = Me.SqlDataSource1.SelectCommand
        Dim dv As Data.DataView = ads.Select(New DataSourceSelectArguments)
        dv = ads.Select(New DataSourceSelectArguments)

        'DataView 轉DataTable
        Dim dt As Data.DataTable = dv.ToTable()
       
        ' 產生 Excel 資料流。
        ms = RenderDataTableToExcel(dt)

        '== Excel檔名，請寫在最後面 filename的地方 
        Response.AddHeader("Content-Disposition", String.Format("attachment;filename=" + Server.UrlEncode(file_name)))
        Response.ContentType = "application/vnd.ms-excel"
        Response.BinaryWrite(ms.ToArray())

        '== 釋放資源 
        ms.Close()
        ms.Dispose()

呼叫
 Public Shared Function RenderDataTableToExcel(ByVal SourceTable As DataTable) As Stream
        Dim workbook As New HSSFWorkbook()
        Dim ms As New MemoryStream()
        Dim sheet As HSSFSheet = workbook.CreateSheet()
        Dim headerRow As HSSFRow = sheet.CreateRow(0)

        ' handling header.
        For Each column As DataColumn In SourceTable.Columns
            headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName)
        Next

        ' handling value.
        Dim rowIndex As Integer = 1

        For Each row As DataRow In SourceTable.Rows
            Dim dataRow As HSSFRow = sheet.CreateRow(rowIndex)

            For Each column As DataColumn In SourceTable.Columns
                dataRow.CreateCell(column.Ordinal).SetCellValue(row(column).ToString())
            Next

            rowIndex += 1
        Next

        workbook.Write(ms)
        ms.Flush()
        ms.Position = 0

        sheet = Nothing
        headerRow = Nothing
        workbook = Nothing

        Return ms
    End Function