.Net 中使用 QueryTables 把數據整塊導入到Excel

Dim xlApp As New Excel.Application
        Dim xlBook As Excel.Workbook
        Dim xlSheet As Excel.Worksheet
        Dim xlQuery As Excel.QueryTable

        Dim mConnString As String = "Provider=SQLOLEDB.1;Password=norman;Persist Security Info=True;User ID=sa;Initial Catalog=BCS;Data Source=CNSQL"

        Try

            xlApp = CreateObject("Excel.Application")
            xlBook = Nothing
            xlSheet = Nothing
            xlBook = xlApp.Workbooks().Add
            xlSheet = xlBook.Worksheets("sheet1")
            xlApp.Visible = True

            '添加查詢語句，導入EXCEL數据
            xlQuery = xlSheet.QueryTables.Add("OLEDB;" + mConnString, xlSheet.Range("a2"), strSelect)
            With xlQuery
                .CommandType = Excel.XlCmdType.xlCmdSql

                .FieldNames = True
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .BackgroundQuery = True
                .RefreshStyle = Excel.XlCellInsertionMode.xlInsertDeleteCells
                .SavePassword = True
                .SaveData = True
                .AdjustColumnWidth = True
                .RefreshPeriod = 0
                .PreserveColumnInfo = True
                .Refresh()
            End With