新手求助·用vb.net查询excel表内容
自己编写了个利用vb.net webbrowser网页录入信息的小程序。想读取excel文件后,查询成绩并填入。
excel是这样的
学籍号 姓名 语文 数学 英语 ……
101 张三 99 98 97……
现在可以从网页上读取到学籍号,
怎么才能利用学籍号通过vb.net查询到a.xls中的某科成绩呢?
感激不禁
--------------------编程问答-------------------- 首先获取excel数据.在根据学号加学科查找数据就可以了.
''' <summary>
''' 获取excel数据.
''' </summary>
''' <param name="path">路径</param>
''' <returns></returns>
''' <remarks></remarks>
Public Shared Function ToImporting(path As String) As DataTable
Dim t As New DataTable
Dim connStr As String = GetCnn(path)
Dim ds As New DataSet
Dim TempList As List(Of String)
TempList = GetExcelTabs(connStr)
If TempList Is Nothing OrElse TempList.Count < 1 Then Return Nothing
For Each tn As String In TempList
LoadData(tn, t, connStr)
If t Is Nothing OrElse t.Rows.Count < 1 Then Continue For
Return t
Next
Return Nothing
End Function
Private Shared Function GetExcelTabs(cnn As String) As List(Of String)
Dim ls As New List(Of String)
Using ODBConn As New OleDb.OleDbConnection(cnn)
Try
ODBConn.Open()
Dim t As DataTable = ODBConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,
New Object() {Nothing, Nothing, Nothing, "TABLE"})
For Each row As DataRow In t.Rows
If row("TABLE_TYPE") = "TABLE" AndAlso row("TABLE_NAME").ToString.Contains("Print_Area") = False Then
ls.Add(row("Table_Name").ToString)
End If
Next
Catch ex As Exception
Throw New System.Exception("获取Excel表名错误!")
Finally
ODBConn.Close()
End Try
End Using
Return ls
End Function
Private Shared Function GetCnn(path As String) As String
Dim connStr As String = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0;", path)
Return connStr
End Function
Private Shared Sub LoadData(ByVal TableName As String, ByRef Tabel As DataTable, cnn As String)
If TableName.Contains("$") = False Then TableName &= "$"
Try
Using Conn As New OleDb.OleDbConnection(cnn)
Conn.Open()
Dim Sql As String = String.Format("select * from[{0}] ", TableName)
Dim GDCom As New OleDb.OleDbCommand(Sql, Conn)
GDCom.CommandType = CommandType.Text
Dim suppliersAdapter As New OleDb.OleDbDataAdapter(GDCom)
suppliersAdapter.Fill(Tabel)
suppliersAdapter.Dispose()
End Using
Catch ex As Exception
Throw New System.Exception(ex.Message, ex)
End Try
End Sub
补充:.NET技术 , VB.NET