【VB】透過Microsoft.Office元件匯出Excel

使用前需引用參考，還有要放入一個Excel檔案，作為樣版之用途

引用 :

Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Imports Microsoft.Office.Interop

部份程式碼(參考) :

        '宣告Excel 相關元件
        Dim exl_ap As New Excel.Application
        Dim exl_wkb As Excel.Workbook
        Dim exl_wks As Excel.Worksheet

        Dim j As Integer = 1

        '定義存檔路徑及檔案名稱
        Dim sPath, sFilename As String
        sPath = Application.StartupPath.ToString
        sFilename = "\" & Format(Now, "yyyyMMdd") & ".xls"

        exl_wkb = exl_ap.Workbooks.Open(sPath & "\employee.xls", , True) '這個TRUE是Readonly  
        exl_wks = exl_wkb.Worksheets("sheet1")  ' exl_wks = exl_wkb.Worksheets(1) 同等意思
        '或者 exl_wks = exl_wkb.ActiveSheet 也行

        '標題  Header
        With exl_wks
            .Name = "人員資料表"
            .Range("A" & j).Value = "員工編號"
            .Range("B" & j).Value = "員工姓名"
            .Range("C" & j).Value = "職稱"
            .Range("D" & j).Value = "性別"
            .Range("E" & j).Value = "到職日"
            .Range("A1:E1").Interior.ColorIndex = 6  '顏色
            .Cells().ColumnWidth = 10  '欄位寬度
        End With

        '抓DB  GetDataSet函數省略不介紹
        Dim sqlstr, lcReturn As String
        Dim loDS As New DataSet
        sqlstr = "select empid,firstName,jobtitle,Case when sex='M' then '男' when sex='F' then '女' else null End as gender " & _
                 ",Convert(varchar(10),startdate,111) as startdate from dbo.OHEM "
        lcReturn = GetDataSet(sqlstr, loDS, "OHEM")
        '判斷是否有錯誤訊息
        If lcReturn = "" Then
            If loDS.Tables(0).Rows.Count > 0 Then
                '內容 Content
                For i As Integer = 0 To loDS.Tables(0).Rows.Count - 1
                    j += 1
                    With exl_wks
                        .Range("A" & j).Value = loDS.Tables(0).Rows(i).Item("empid").ToString().Trim()
                        .Range("B" & j).Value = loDS.Tables(0).Rows(i).Item("firstName").ToString().Trim()
                        .Range("C" & j).Value = loDS.Tables(0).Rows(i).Item("jobtitle").ToString().Trim()
                        .Range("D" & j).Value = loDS.Tables(0).Rows(i).Item("gender").ToString().Trim()
                        .Range("E" & j).Value = loDS.Tables(0).Rows(i).Item("startdate").ToString().Trim()

                    End With
                Next
            End If
        End If

        '存檔
        exl_ap.SaveWorkspace() '另開視窗
        '或者 exl_wkb.SaveAs(sPath & sFilename) 可由user自行輸入檔名及存檔位置 也行
        exl_ap.Visible = True

        exl_wks = Nothing

        exl_wkb.Close()
        exl_wkb = Nothing

        exl_ap.Quit()
        exl_ap = Nothing
        MsgBox("匯出完成!!")
        GC.Collect()

畫面(參考) :