采用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