VB.NET将excel数据抽取成行

VB.NET将excel数据抽取成行

1. 将excel某一列数据按照固定分隔符分割，抽取放置成行

如图，将第一列数据以“;”分割，抽取成行。

抽取行	其他
A;;B;F;G;D	1
A;;B;F;G;D	2

抽取结果：

抽取行	其他
A	1
	1
B	1
F	1
G	1
D	1
A	2
	2
B	2
F	2
G	2
D	2

2. 将excel某一列数据按照固定分隔符分割，抽取放置成行

传入分隔符separator及要抽取的列数col，即根据需求将excel数据列抽取成行。代码如下：

Public Sub extraction(ByVal separator As String, ByVal col As Integer)
        Dim S1, S2 As String
        Dim i, j, k, o, y, x, z, m As Integer
        Dim arr1() As String
        j = 1
        i = 1
        With Sheet1.UsedRange
            y = .Cells(.Rows.Count, .Columns.Count).Column
        End With
        With Sheet2.UsedRange
            z = .Cells(.Rows.Count, .Columns.Count).Row
        End With
        MsgBox(z)
        For m = 1 To z
            Sheet2.Rows(m).delete()
        Next
        Do Until Sheet1.Cells(j, col).value = ""
            S1 = Sheet1.Cells(j, col).value
            arr1 = Split(S1, separator)
            k = 0
            o = UBound(arr1) - LBound(arr1) + 1
            Do Until k = o
                Sheet2.Cells(i, col).value = arr1(k)
                If col <> 1 Then
                    For x = 1 To col - 1
                        S2 = Sheet1.Cells(j, x).value
                        Sheet2.Cells(i, x).value = S2
                    Next
                    For x = x + 1 To y
                        S2 = Sheet1.Cells(j, x).value
                        Sheet2.Cells(i, x).value = S2
                    Next
                ElseIf col = 1 Then
                    For x = 2 To y
                        S2 = Sheet1.Cells(j, x).value
                        Sheet2.Cells(i, x).value = S2
                    Next
                End If
                k = k + 1
                i = i + 1
            Loop
            j = j + 1
        Loop
    End Sub

3. 打开excel代码

此过程可打开excel文件，并传出excel路径：

Public Function open() As String
        Dim openfiledialog1 = New OpenFileDialog()
        'Dim Bindingsource1 As New BindingSource
        Dim path As String
        openfiledialog1.Filter = "所有文件（*.*）|*.*|所有文件（*.*)|*.*"
        openfiledialog1.FilterIndex = 1
        openfiledialog1.RestoreDirectory = False
        'S1 = filelocation
        If (openfiledialog1.ShowDialog() = Windows.Forms.DialogResult.Cancel) Then
            path = ""
            open = path
            Exit Function
        Else
            path = openfiledialog1.FileName
            电子表格 = CreateObject("excel.application")
            WorkBook1 = 电子表格.Workbooks.Open(path)
            WorkBook1 = 电子表格.Workbooks(1)
            Sheet1 = WorkBook1.Sheets(1)
            Sheet2 = WorkBook1.Sheets(2)
        End If
        open = path
    End Function

4. excel预览代码

使用datagridview控件显示打开的excel，代码如下：

Public Function preview(ByVal DGV1 As DataGridView)
        Dim path1 As String
        path1 = open()
        'If (DGV1.DataSource <> Nothing) Then
        '    dt1 = DGV1.DataSource
        '    dt1.Rows.Clear()
        '    DGV1.DataSource = dt1
        'ElseIf (DGV1.DataSource = Nothing) Then
        MyConnection = New OleDbConnection( _
                  "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                     "Data Source=" & path1 & ";" & _
                      "Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'")
        MyConnection.Open()
        objcmd1 = New OleDbCommand("select * from [sheet1$] ", MyConnection)
        dataAdapter1.SelectCommand = objcmd1
        dataAdapter1.Fill(dst1, "info")
        dt1 = dst1.Tables("info")
        dataAdapter1.Update(dst1, "info")
        'MyConnection.Close()
        DGV1.AutoGenerateColumns = True
        DGV1.DataSource = dt1
        preview = path1
        'End If
    End Function

5. excel保存代码

Public Sub save()
        电子表格.ActiveWorkbook.Close(SaveChanges:=True)
        电子表格.Quit()
End Sub

6. 界面展示