ADO查询怎样得到EXCEL表名

时间:2021-08-18 09:34:04

采用ADO查询EXCEL,需要知道表名,一般EXCEL的第一个表名为“sheet1”,如果有例外,就需要先得到表名,否则会报错。程序如下:(其中参量file为服务器中excel文件的全路径名)

Function CreateDataSource(ByVal file As String)
        Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";" + "Extended Properties=Excel 8.0;"
        Dim conn As OleDbConnection = New OleDbConnection(strConn)
        Dim tablename As String = "Sheet1$"

        conn.Open()
        Dim dt As DataTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
        tablename = dt.Rows(0)(2).ToString()
        conn.Close()

        Dim myCommand As OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM [" + tablename + "]", strConn)
        Dim myDataSet As DataSet = New DataSet
        myCommand.Fill(myDataSet)
        Return myDataSet
    End Function