以文本方式查看主题

-  Foxtable(狐表)  (http://foxtable.com/bbs/index.asp)
--  专家坐堂  (http://foxtable.com/bbs/list.asp?boardid=2)
----  大数据量导出到EXCEL的代码,为啥导不出数据  (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=92165)
--  作者：blackzhu
--  发布时间：2016/10/27 17:44:00
--  大数据量导出到EXCEL的代码,为啥导不出数据
数据导不到,但是也没有出错. 
   Public Sub ExportToExcel(ByVal inSQL As String, ByVal inFileName As String)

        Dim oleDbComd As New OleDb.OleDbCommand()

        Dim ldbConn As New OleDb.OleDbConnection
        Dim dataReader As OleDb.OleDbDataReader

        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim xlQueryTable As Excel.QueryTable

        On Error Resume Next

        ldbConn.ConnectionString = ldbConn.ConnectionString
        ldbConn.Open()

        xlApp = CreateObject("Excel.Application")
        xlWorkBook = Nothing
        xlWorkSheet = Nothing
        xlWorkBook = xlApp.Workbooks.Add()
        xlWorkSheet = xlApp.Worksheets("sheet1")
        xlApp.Visible = False
        Dim myconn As String = "数据源字符串"
        xlWorkSheet.QueryTables.Add(myconn, xlWorkSheet.Range("A1"), inSQL)

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

        xlQueryTable.FieldNames = True
        xlQueryTable.Refresh()
        xlWorkBook.SaveAs(inFileName)

        xlApp.Quit()
        xlApp = Nothing
        xlWorkBook = Nothing
        xlWorkSheet = Nothing
        dataReader.Close()
        GC.Collect()
    End Sub
--  作者：有点蓝
--  发布时间：2016/10/27 18:05:00
--  
做个例子看看
--  作者：blackzhu
--  发布时间：2016/10/28 9:31:00
--  
 狐表在不分页的情况下,最多可以加载多少条数据?