VB.NET 数据快速导出到excel文件

    前几天，在网上找了程序觉得，导出速度太慢，原因在于要一个一个数据单元的填充，循环很多。

    昨天，通过excel宏记录采用数据导入，经过修改测试，导出速度很快，现与大家共享。

   说明：strsql 为数据库查询语句，sfile 为文件名，strconnect 为数据库联接字符串 

 Sub ExportToExcel(ByVal strsql As String, ByVal sfile As String, ByVal strConnect As String)
        Dim xlApp As New Excel.Application
        Dim xlBook As Excel.Workbook
        Dim xlSheet As Excel.Worksheet
        Dim rowIndex, colIndex As Integer
        xlBook = xlApp.Workbooks.Add
        xlSheet = xlBook.Worksheets("sheet1")
        Try
            '
            With xlSheet.QueryTables.Add(Connection:="ODBC;DRIVER=SQL Server;" + strConnect, Destination:=xlSheet.Range("A1"))
                ' 数据库联接字符举例 "server=127.0.0.1;databae=mymis;UID=sa;pwd=111111;APP=Microsoft Office 2003;WSID=ADMINS"

                .CommandText = strsql
                .Name = "查询来自 sbb_mis"
                .FieldNames = True
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .BackgroundQuery = True
                .RefreshStyle = 1 ' xlInsertDeleteCells
                .SavePassword = False
                .SaveData = True
                .AdjustColumnWidth = True
                .RefreshPeriod = 0
                .PreserveColumnInfo = True
                .Refresh(BackgroundQuery:=False)

            End With
            xlBook.Saved = True
            xlBook.SaveCopyAs(sfile)
        Catch ex As Exception
            MsgBox(ex.Message + vbCrLf + ex.StackTrace)
        End Try

    End Sub 

    另外 大家还可以根据需要添加不同的参数 用来设置excel表格式样。