asp.net Upload excel file in DataTable

asp.net Upload excel file in DataTable and show in gridview control

aspx code

              <asp:Label ID="lblMsg" runat="server" Text="Upload excel file"></asp:Label>
</div>
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
<div id="fileatt" runat="server" style="display: block" >
            <input type="file" name="attachment" runat="server" id="attachment"  />
            </div>
     
      
      <asp:Button ID="btnUpload1" runat="server" Text="Upload" OnClick="btnUpload1_Click" Visible="true" />

VB Code

Imports System.Data

Imports System.Data.OleDb

.....

.....

 Protected Sub btnUpload1_Click(sender As Object, e As System.EventArgs) Handles btnUpload1.Click

        Try

            Dim uploadDir = "./upload/"

            Dim temp = ""

            Dim connectionString As String = ""

            If Not System.IO.Directory.Exists(Server.MapPath(uploadDir)) Then

                temp = "Creating Path " & uploadDir & "
"

                System.IO.Directory.CreateDirectory(Server.MapPath(uploadDir))

            End If

            '###### Upload File

            Dim fileName As String = ""

            Dim fileLocation = ""

            Dim fileExtension = ""

            Dim uploadFiles As HttpFileCollection = Request.Files

            For i As Integer = 0 To uploadFiles.Count - 1

                Dim uploadFile As HttpPostedFile = uploadFiles(i)

                fileName = System.IO.Path.GetFileName(uploadFile.FileName)

                fileExtension = System.IO.Path.GetExtension(fileName)

                fileName = strip(fileName)

                ''remove single quotes , / , \ 

                'fileName = fileName.Replace("'", "_")

                If fileName.Trim().Length > 0 Then

                    'uploadFile.SaveAs(Server.MapPath("./Others/") + fileName)

                    fileLocation = Server.MapPath(uploadDir) + fileName

                    uploadFile.SaveAs(fileLocation)

                    temp = temp & "Successfully Uploaded: " & fileName & "
"

                End If

            Next

            

            lblMsg.Text = fileLocation

            'Check whether file extension is xls or xslx

            If fileExtension = ".xls" Then

                connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & fileLocation & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=2"""

            ElseIf fileExtension = ".xlsx" Then

                connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & fileLocation & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=2"""

            End If

            'Create OleDB Connection and OleDb Command

            Dim con As New OleDbConnection(connectionString)

            Dim cmd As New OleDbCommand()

            cmd.CommandType = System.Data.CommandType.Text

            cmd.Connection = con

            Dim dAdapter As New OleDbDataAdapter(cmd)

            Dim dtExcelRecords As New DataTable()

            con.Open()

            Dim dtExcelSheetName As DataTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)

            Dim getExcelSheetName As String = dtExcelSheetName.Rows(0)("Table_Name").ToString()

            cmd.CommandText = "SELECT * FROM [" & getExcelSheetName & "]"

            dAdapter.SelectCommand = cmd

            dAdapter.Fill(dtExcelRecords)

            con.Close()

            lblMsg.Text = lblMsg.Text & "DT Created " '& dtExcelRecords.TableName(0).ToString

            GridView1.DataSource = dtExcelRecords

            GridView1.DataBind()

            lblMsg.Text = temp

        Catch e1 As Exception

            lblMsg.Text = e1.Message

        End Try

    End Sub

- Vinod Kotiya