将数据从excel导入到vb .net

Question

我从excel导入3列到vb .net。然后，我想创建第四行或新的DataGridView，并在每行数据上追加一些列。例如我有excel文件：

12/26/2016 9A Christmas Day 
3/25/2016 9A Good Friday 
3/28/2016 AS Easter Monday 
12/26/2016 AS St. Stephen's Day 
3/25/2016 AS Easter Monday 
1/1/2016 AS New Year's Day 
12/26/2016 BE Christmas 
1/1/2016 BZ New Year's Day 
2/9/2016 BZ Carnival 
5/26/2016 BZ Corpus Christ 
4/21/2016 BZ Tiradentes Day 
12/27/2016 CA Boxing Day (obs) 
10/10/2016 CA Thanksgiving 

我想要的是，在第二列的内容后添加新列，其中日期相同。例如：

12/26/2016 9A Christmas Day 9A AS BE 
3/25/2016 9A Good Friday 9A 
3/28/2016 AS Easter Monday AS 
12/26/2016 AS St. Stephen's Day 9A AS BE 
3/25/2016 AS Easter Monday AS 
1/1/2016 AS New Year's Day AS BZ 
12/26/2016 BE Christmas 9A AS BE 
1/1/2016 BZ New Year's Day AS BZ 
2/9/2016 BZ Carnival BZ 
5/26/2016 BZ Corpus Christ BZ 
4/21/2016 BZ Tiradentes Day BZ 
12/27/2016 CA Boxing Day (obs) CA 
10/10/2016 CA Thanksgiving CA 

具有相同日期的行在第四列中将具有相同的值。列名是（Date，CDR Code，Holiday）。数据是工作表的名称。代码中，我写道：

Public Class Form1 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click 

     Dim MyConnection As New System.Data.OleDb.OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;Data Source='d:\test.xlsx';Extended Properties='Excel 12.0;HDR=YES'") 
     Dim MyCommand As New System.Data.OleDb.OleDbDataAdapter("select * from [Data$]", MyConnection) 
     Dim DtSet As New System.Data.DataSet 
     MyCommand.Fill(DtSet) 
     DataGridView1.DataSource = DtSet.Tables(0) 
     MyConnection.Close() 

    End Sub 

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click 
     Dim MyConnection2 As New System.Data.OleDb.OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0;Data Source='d:\test.xlsx';Extended Properties='Excel 12.0;HDR=YES'") 
     Dim MyCommand2 As New System.Data.OleDb.OleDbDataAdapter("select [CDR Code] from [Data$] WHERE [Date]=[Date] ", MyConnection2) 
     Dim DtSet2 As New System.Data.DataSet 
     MyCommand2.Fill(DtSet2) 
     DataGridView2.DataSource = DtSet2.Tables(0) 
     MyConnection2.Close() 

    End Sub 

End Class 

Answer 1

解决方案：

Public Class Form1 


Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click 
    Dim fd As OpenFileDialog = New OpenFileDialog() 
    Dim strFileName As String 

    fd.Title = "Open File Dialog" 
    fd.InitialDirectory = "C:\" 
    fd.Filter = "Excel Files|*.xlsx" 
    fd.FilterIndex = 2 
    fd.RestoreDirectory = True 

    If fd.ShowDialog() = DialogResult.OK Then 
     strFileName = fd.FileName 
    End If 

    Dim dbconnect As New DBConnection(strFileName) 
    Dim ds As New System.Data.DataSet 
    Dim tempTable As DataTable 
    Dim tempnames As String 
    Dim tempdate As String 
    Dim tempds As New System.Data.DataSet 



    ds = dbconnect.ExecuteSql("select * from [Data$]") 
    tempTable = ds.Tables(0) 

    tempTable.Columns.Add("OtherNames", Type.GetType("System.String")) 

    For Each Row As DataRow In tempTable.Rows 
     tempdate = Row.Item(0) 
     tempds = dbconnect.ExecuteSql("select [CDR Code] from [Data$] where [Date]=#" + tempdate + "#") 
     tempnames = "" 
     For Each Row1 As DataRow In tempds.Tables(0).Rows 
      tempnames += Row1.Item(0) + " " 
     Next 
     Row.Item("OtherNames") = tempnames 
    Next 

    DataGridView1.DataSource = tempTable 

End Sub 


End Class 

而DBConnection的类：

Public Class DBConnection 
Dim con As System.Data.OleDb.OleDbConnection 

Public Sub New(ByVal FilePath As String) 
    con = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + "; Extended Properties='Excel 12.0 Xml;HDR=YES';") 
End Sub 
Protected Overrides Sub Finalize() 
    MsgBox("Connection Closed") 
    con.Close() 
End Sub 

Public Function ExecuteSql(ByVal sql As String) As System.Data.DataSet 
    Dim da As New System.Data.OleDb.OleDbDataAdapter(sql, con) 
    Dim ds As New System.Data.DataSet 
    da.Fill(ds) 
    Return ds 
End Function 

End Class 

Answer 2

For intI As Integer = 0 To DataGridView1.Rows.Count - 1 
     For intJ As Integer = intI + 1 To DataGridView1.Rows.Count - 1 
      If DataGridView1.Rows(intI).Cells(0).Value = DataGridView1.Rows(intJ).Cells(0).Value Then 
       DataGridView1.Rows(intI).Cells(3).Value = DataGridView1.Rows(intI).Cells(1).Value + DataGridView1.Rows(intJ).Cells(1).Value 
       Exit Sub 
      End If 
     Next 
    Next 

现在你刚刚有适应的代码。顺便说一下，我在dgv1中添加了第4行。