Option Explicit On

Imports NPOI.SS.UserModel

Imports System.IO

Public Class ExcelHelper

    Public Shared Function ImportExcel(ByVal strSource As String) As DataTable

        Dim hssfWorkbook As IWorkbook

        Dim sheet As ISheet

        Dim rows As System.Collections.IEnumerator

        Dim dt As DataTable

        Dim icount As Int32

        Dim row As IRow

        Dim dr As DataRow

        Dim cell As ICell

        hssfWorkbook = WorkbookFactory.Create(FileToStream(strSource))

        sheet = hssfWorkbook.GetSheetAt(0)

        rows = sheet.GetRowEnumerator()

        icount = 0

        dt = New DataTable()

        While rows.MoveNext()

            icount = icount + 1

            row = rows.Current

            dr = dt.NewRow

            For i As Int32 = 0 To row.LastCellNum

                cell = row.GetCell(i)

                If cell Is Nothing Then

                    'dr(i) = DBNull.Value

                Else

                    If icount = 1 Then

                        dt.Columns.Add(cell.ToString)

                    End If

                    dr(i) = cell.ToString

                End If

            Next

            dt.Rows.Add(dr)

        End While

        dt.Rows.RemoveAt(0)

        Return dt

    End Function

    Public Shared Function FileToStream(ByVal fileName As String) As Stream

        '打开文件

        Dim fileStream As FileStream

        fileStream = New FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.Read)

        '读取文件的 byte()

        Dim bytes() As Byte = New Byte(fileStream.Length) {}

        fileStream.Read(bytes, 0, bytes.Length)

        fileStream.Close()

        '把 byte()转换成 Stream

        Dim stream As Stream = New MemoryStream(bytes)

        Return stream

    End Function

End Class