VB.Net 使用和操作 Excel 电子表格

Question

我想遍历数据集中的一行。如果行的第一个单元格是模式1234-Name，那么我想从中去除数字 (1234( 并将其另存为变量。基本上，第一个单元格是员工及其编号，例如1234-Bob McDonald。如果它不是那种形式，那么我想忽略它并移动到下一行。

然后我想在同一行中逐个单元格，无论单元格中有什么，都将其保存到不同的变量中(第 2 列是位置，第 3 列是工作小时数等(

这是我到目前为止的代码：

Public Function ReadXLFile(ByVal FileName As String) As DataSet
Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim Ds As System.Data.DataSet
Dim MyAdapter As System.Data.OleDb.OleDbDataAdapter
MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileName & "Extended Properties=Excel 16.0;")
MyAdapter = New System.Data.OleDb.OleDbDataAdapter("Select * from [Sheet1$]", MyConnection)
Ds = New System.Data.DataSet
MyAdapter.Fill(Ds)
Return Ds
End Function

Answer 1

这个怎么样？

Public Sub GetData(ByVal FileName As String)
Dim strEmployeeNumber As String
Dim strEmployeeName As String
Dim strLocation As String
Dim intHours As Integer
Dim myDataSet As DataSet = ReadXLFile(FileName)
Dim myFirstCell As String
Dim mMatch As Match
Dim r As Regex = New Regex("^[0-9]+-[A-Za-z]+")
Dim arrFirstCell() As String
For Each myDr As DataRow In myDataSet.Tables(0).Rows
myFirstCell = myDr.Item(myDataSet.Tables(0).Columns(0))
mMatch = r.Match(myFirstCell)
If mMatch.Success Then
arrFirstCell = Split(mMatch.Value, "-")
strEmployeeNumber = arrFirstCell(0)
strEmployeeName = arrFirstCell(1)
strLocation = myDr.Item(myDataSet.Tables(0).Columns(1))
intHours = CInt(myDr.Item(myDataSet.Tables(0).Columns(2)))
Console.WriteLine("Number: {0}  Name: {1}  Location: {2}  Hours: {3}", strEmployeeNumber, strEmployeeName, strLocation, intHours)
End If
Next
End Sub