VB.NET 快速导出数据到Excel

 一个单元格一个单元格导出数据到Excel速度太慢，在网上找到一下方法，收藏

'    strsql 为数据库查询语句，sfile 为文件名

    Public Sub ExportToExcel(ByVal strsql As String, ByVal sfile As String)
        Dim xlApp As New Excel.Application
        Dim xlBook As Excel.Workbook
        Dim xlSheet As Excel.Worksheet
        xlBook = xlApp.Workbooks.Add
        xlSheet = xlBook.Worksheets("sheet1")
        xlApp.Visible = True
        Dim Con As String = strCon '连接串是Data Source=GHY-IT10;Initial Catalog=QualityTest;User ID=sa类型会出错
        Con = Replace(Con, "Data Source", "Server") '故替换为一下类型
        Con = Replace(Con, "Initial Catalog", "Database")
        Try
            With xlSheet.QueryTables.Add(Connection:="ODBC;DRIVER=SQL Server;" + Con, 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

 

'上面是一种方法

'网上说这样也可以

        ' Dim xlApp As New Excel.Application
        ' Dim xlBook As Excel.Workbook
        ' Dim xlSheet As Excel.Worksheet
        ' xlBook = xlApp.Workbooks().Add  '添加一个新的BOOK
        ' xlSheet = xlBook.Worksheets("sheet1") '添加一个新的SHEET
        ' xlApp.Visible = True ''
        ' xlSheet.Cells.CopyFromRecordset(dsSearch.Tables("Quality"))
        ' xlBook.SaveAs("AA")

但是Copy数据的时候老出错，还在探索中