Advertisement.

TransHimalaya.in

Everest Helicopter Tour for a day

Book exciting packages at www.transhimalaya.in

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

Comments

Most Viewed Post

solution for facebook static.ak.fbcdn.net waiting

Connect Sony XPLOD MEX-BT2500 Bluetooth CD Receiver with ur mobile

Crystal Report : Show data horizontally (Left to right) i.e. columns as rows

Google+ Followers