QueryTable实现EXCEL导出(vb.net)

VB.NET的写法跟VB大同小异，上月31号严重困扰我的DB连接字符串问题已经解决，三个参数都要写。另外，坚持不懈地试验很重要。

    '****************************************************************
    '       名称：fncExporToExcel
    '       功能：导出数据到EXCEL
    '       用法：ExporToExcel(sql查询字符串, 保存路径)
    '****************************************************************
    Private Function fncExportToExcel(ByVal inSQL As String, ByVal inFileName As String)
        Dim oleDbComd As New OleDb.OleDbCommand()
        Dim dataReader As OleDb.OleDbDataReader
        Dim ldbConn As New OleDb.OleDbConnection()

        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 = gdbConn.ConnectionString
        ldbConn.Open()

        xlApp = CreateObject("Excel.Application")
        xlWorkBook = Nothing
        xlWorkSheet = Nothing
        xlWorkBook = xlApp.Workbooks.Add()
        xlWorkSheet = xlApp.Worksheets("sheet1")
        xlApp.Visible = False

        xlQueryTable = xlWorkSheet.QueryTables.Add _
        ("OLEDB;Provider=OraOLEDB.Oracle; _
        Data Source=; _
        User ID=; _
        Password=",xlWorkSheet.Range("A1"), inSQL)

        With xlQueryTable
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .“refresh”OnFileOpen = False
            .BackgroundQuery = True
            .“refresh”Style = Excel.XlCellInsertionMode.xlInsertDeleteCells
            .SavePassword = True
            .SaveData = True
            .AdjustColumnWidth = True
            .“refresh”Period = 0
            .PreserveColumnInfo = True
        End With

        xlQueryTable.FieldNames = True
        xlQueryTable.“refresh”()
        xlWorkBook.SaveAs(inFileName)

        xlApp.Quit()
        xlApp = Nothing
        xlWorkBook = Nothing
        xlWorkSheet = Nothing
        dataReader.Close()

QueryTables.Add()参数说明：

1、Connection
QueryTables的数据源。用OLE DB的话，是一个连接字符串。参数配置可以从URL链接中找到。

2、Destination
单元格定位。放置QueryTable的左上角坐标。必须是查询表所在的Worksheet。

3、Sql
可选参数。不过貌似我的程序里是必需的。就是SQL查询语句啦！不多说了。