Upload Excel Sheet Data into SharePoint List

Imports System
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Text
Imports System.IO

Partial Public Class UploadUserControl
    Inherits UserControl

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load

    End Sub


    Public Class CSVReader
        Private objStream As Stream
        Private objReader As StreamReader
        'add name space System.IO.Stream
        Public Sub New(ByVal filestream As Stream)
            Me.New(filestream, Nothing)
        End Sub

        Public Sub New(ByVal filestream As Stream, ByVal enc As Encoding)
            Me.objStream = filestream
            'check the Pass Stream whether it is readable or not
            If Not filestream.CanRead Then
                Return
            End If
            objReader = If((enc IsNot Nothing), New StreamReader(filestream, enc), New StreamReader(filestream))
        End Sub
        'parse the Line
        Public Function GetCSVLine() As String()
            Dim data As String = objReader.ReadLine()
            If data Is Nothing Then
                Return Nothing
            End If
            If data.Length = 0 Then
                Return New String(-1) {}
            End If
            'System.Collection.Generic
            Dim result As New ArrayList()
            'parsing CSV Data
            ParseCSVData(result, data)
            Return DirectCast(result.ToArray(GetType(String)), String())
        End Function

        Private Sub ParseCSVData(ByVal result As ArrayList, ByVal data As String)
            Dim position As Integer = -1
            While position < data.Length
                result.Add(ParseCSVField(data, position))
            End While
        End Sub

        Private Function ParseCSVField(ByRef data As String, ByRef StartSeperatorPos As Integer) As String
            If StartSeperatorPos = data.Length - 1 Then
                StartSeperatorPos += 1
                Return ""
            End If


            Dim fromPos As Integer = StartSeperatorPos + 1
            If data(fromPos) = """"c Then
                Dim nextSingleQuote As Integer = GetSingleQuote(data, fromPos + 1)
                Dim lines As Integer = 1
                While nextSingleQuote = -1
                    data = data + vbLf + objReader.ReadLine()
                    nextSingleQuote = GetSingleQuote(data, fromPos + 1)
                    lines += 1

                End While
                StartSeperatorPos = nextSingleQuote + 1
                Dim tempString As String = data.Substring(fromPos + 1, nextSingleQuote - fromPos - 1)
                tempString = tempString.Replace("'", "''")
                Return tempString.Replace("""""", """")
            End If


            Dim nextComma As Integer = data.IndexOf(","c, fromPos)
            If nextComma = -1 Then
                StartSeperatorPos = data.Length
                Return data.Substring(fromPos)
            Else
                StartSeperatorPos = nextComma
                Return data.Substring(fromPos, nextComma - fromPos)
            End If
        End Function


        Private Function GetSingleQuote(ByVal data As String, ByVal SFrom As Integer) As Integer
            Dim i As Integer = SFrom - 1

            While System.Threading.Interlocked.Increment(i) < data.Length
                If data(i) = """"c Then
                    If i < data.Length - 1 AndAlso data(i + 1) = """"c Then
                        i += 1
                        Continue While
                    Else
                        Return i
                    End If
                End If
            End While
            Return -1
        End Function
    End Class

    Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnUpload.Click


        Dim ext As String = FileUpload1.FileName.Substring(FileUpload1.FileName.LastIndexOf("."))
        Dim reader As New CSVReader(FileUpload1.PostedFile.InputStream)
        'get the header
        Dim headers As String() = reader.GetCSVLine()
        Dim dt As New DataTable()
        For Each strHeader As String In headers
            dt.Columns.Add(strHeader)
        Next
        While True
            Dim data As String() = reader.GetCSVLine()
            If (data Is Nothing) Then
                ' TODO: might not be correct. Was : Exit While
                Exit While
            End If
            dt.Rows.Add(data)
            For Each ss As String In data


            Next


        End While
        Dim dr As DataTable = dt

        Dim _site As New SPSite(SPContext.Current.Site.Url)
        Dim _web As SPWeb = _site.OpenWeb()
        Dim _list As SPList = _web.Lists("Listname")


        For Each row1 As DataRow In dr.Rows

            Dim newitem As SPListItem = _list.Items.Add
            Dim cc As String = row1(0).ToString()
            Try
                newitem("Title") = row1(0)
            Catch ex As Exception
                lblTEST.Text += cc + ":Name" + ex.Message
            End Try



            Try
                newitem.Update()
            Catch ex As Exception
                lblTEST.Text += cc + ": Update" + ex.Message
            End Try

            lblTEST.Text += "<br/>"

        Next


    End Sub

End Class