VB.NET_excel表拆分demo

【核心代码】

Imports Microsoft.Office.Interop

Public Class Form1

  Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    TopMost = True
    Dim xlapp As Excel.Application
    Dim xlbook As Excel.Workbook
    Dim xlsht As Excel.Worksheet
    xlapp = New Excel.Application With {
      .Visible = True
    }
    xlbook = xlapp.Workbooks.Open(Application.StartupPath & "\Excel.xlsx")
    xlsht = xlbook.Worksheets("订单明细表")
    With xlsht
      Dim arr As Array = .Range("A3:H" & .Range("A65536").End(Excel.XlDirection.xlUp).Row).Value

      Dim DIC As New Dictionary(Of String, String)

      Dim arr_ct As Integer = UBound(arr)
      Dim arr_cvt As Integer = UBound(arr, 2)

      For I As Integer = 1 To arr_ct
        If DIC.ContainsKey(arr(I, 3)) = False Then
          DIC.Add(arr(I, 3), 0)
        End If
      Next
      For Each key In DIC.Keys
        xlbook.Worksheets.Add.name = key
        Dim sht As Excel.Worksheet = xlbook.Worksheets(key)
        For j As Integer = 1 To arr_ct
          Application.DoEvents()
          If arr(j, 3) = key Then
            Dim RW As Integer = sht.Range("A65536").End(Excel.XlDirection.xlUp).Row
            For k As Integer = 1 To arr_cvt
              Application.DoEvents()
              sht.Cells(RW   1, k) = arr(j, k)
            Next
          End If
        Next
      Next


      MsgBox("表拆分完成!")


    End With

  End Sub
End Class