connectseekers.com

Friday, 1 September 2017

Upload and Display MS Excel Sheet Data in Gridview Using Asp.Net

9/01/2017 - By Pranav Singh 0

In this article I will show Upload and Display MS Excel Sheet Data in Gridview Using Asp.Net in c#. So this article will cover Read Excel Sheet Data and Bind With ASP.NET GridView, Import/Upload Excel Data to Asp.net Gridview in C#, VB.NET, Upload And Read Excel File Into DataTable DataSet Asp.Net, ASP.NET .NET Import excel data to gridview.

Some of my previous articles are as follows: Export GridView Data To Excel Sheet Using C#.Net In Windows Application, Windows Application - Excel Sheet Name in C#.Net, Get Or Retrieve Excel File Sheet Name Using ADO.Net in Asp.net Using C#, Excel Sheet Import and Read Data using ADO.Net in Asp.net Using C#, Import & Save Excel Sheet Data Into SQL Server Database Table In Asp.net, Excel File Upload Or Import and Display In GridView Using C# In Asp.Net, How to Read (Import) MS Excel File And Populate Into GridView Using Asp.Net in C#.


So for this article first we will create an excel sheet and add some value in it.



Now we will a folder in the application where we upload our excel file.


 Now we will create a new asp.net application and add the below grid view code into the page.

<form id="form1" runat="server">
        <div>
            <asp:Label ID="lblMessage" runat="server" ForeColor="Red"></asp:Label>
            <br />
            <br />
            <asp:FileUpload ID="FileUpload1" runat="server" />
            <br />
            <br />
            <asp:Button ID="btnUpload" runat="server" Text="Upload and Display" OnClick="btnUpload_Click" />
            <br />
            <br />
            <asp:GridView ID="GridView1" runat="server" EnableViewState="false" />
        </div>
    </form>

After this we write the code to read and bind the excel sheet data to the gridview control.

protected void btnUpload_Click(object sender, EventArgs e)
        {
            string uploadFilePath = "";
            if (FileUpload1.HasFile)
            {
                string filename = System.IO.Path.GetFileName(Server.MapPath(FileUpload1.FileName));
                uploadFilePath = "~/ExcelFileList/" + filename;
                FileUpload1.SaveAs(Server.MapPath(uploadFilePath));
            }
            if (uploadFilePath != "")
            {
                string connectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1";
                string filepath = Server.MapPath(uploadFilePath);
                DataTable dt = new DataTable();
                using (OleDbConnection conn = new OleDbConnection(string.Format(connectionstring, filepath)))
                {
                    string query = @"SELECT * FROM [Sheet1$]";
                    using (OleDbCommand cmd = new OleDbCommand(query, conn))
                    {
                        conn.Open();
                        using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
                        {
                            da.Fill(dt);
                        }
                        conn.Close();
                    };
                }
                GridView1.DataSource = dt;
                GridView1.DataBind();
            }
            else
            {
                lblMessage.Text = "Please select file to upload and read.";
            }
        }

Here in above code I have first I have validated weather user have selected any file or not. If he has selected a file on that case I have uploaded the file and then prepared the path of the file and then pas that file path to further reading it.

After that I have prepared a connectionstring for which contains the oledb provider for reading the excel sheet. After that I have taken the taken the path of the excel sheet and then read the excel sheet data in into datatable and bind it to grid view.

Now we have done run the application to check the output.

Now check the output after clicking on upload and display.


Here is your uploaded file.



About the Author

We are the group of people who are expertise in different Microsoft technology like Asp.Net,MVC,C#.Net,VB.Net,Windows Application,WPF,jQuery,Javascript,HTML. This blog is designed to share the knowledge.

Get Updates

Subscribe to our e-mail newsletter to receive updates.

Share This Post

0 comments:

Please let me know your view

Free Ebooks


About Us

We are the group of people who are expertise in different Microsoft technology like Asp.Net,MVC,C#.Net,VB.Net,Windows Application,WPF,jQuery,Javascript,HTML. This blog is designed to share the knowledge.

Contact Us

For writing article in this website please send request by your

GMAIL ID: dotnetpools@gmail.com
Email Id : contact@aspdotnet-pools.com

Bugs and Suggestions

As we all know that this website is for sharing knowledge and providing proper solution. So while reading the article is you find any bug or if you have any suggestion please mail us at contact@aspdotnet-pools.com.

Partners


Top jQuery Plugins

Global Classified : Connectseekers.com
© 2014 aspdotnet-pools.com Designed by Bloggertheme9.
back to top