Home  »  Articles  »  ASP.Net  »

Read Excel Data and Show on GridView in asp.net

 Posted By : Manoranjan SahooPosted Date : 31/05/2011 03:44:41 PM Category : ASP.Net Points : 6 (Rs 2.00)

This article mainly describes how to read data from an Excel Sheet and display them in a GridView.

Here I am going to show you the step by step process to read data from Excel and display them in a GridView. To do so follow the below steps :

Step 1 :

Create New Web site with new webpage named as Default.aspx (Following is the code contained in the Default.aspx page).

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<head runat="server">
    <title>Read Excel File Data and Show in GridView</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Label ID="lblHeading" runat="server" Text="Select File To Upload : "></asp:Label>
        <asp:FileUpload ID="FileUpload1" runat="server" />
        <br />
        <br />
        <asp:Button ID="btnUpload" runat="server" Text="Upload"
            onclick="btnUpload_Click" />     ?
        <br />
        <asp:Label ID="lblerror" runat="server" ForeColor ="Red" Text=""></asp:Label>
        <br /><br />
        <asp:GridView ID="GridView1" AutoGenerateColumns ="true" HeaderStyle-BackColor="ActiveCaption" HeaderStyle-ForeColor ="White"  runat="server">
        </asp:GridView>
    </div>
    </form>
</body>
</html>

Step 2 :

Here is the C# code of the Default.aspx.cs file:

using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.IO;
using System.Data.OleDb;
using System.Text;
 
public partial class _Default : System.Web.UI.Page
{
    OleDbConnection oleconn;
    StringBuilder sb = new StringBuilder();
 
    protected void Page_Load(object sender, EventArgs e)
    {
 
    }
    protected void btnUpload_Click(object sender, EventArgs e)
    {
        if (FileUpload1.HasFile)
        {
            string tempFileName = Guid.NewGuid().ToString("N") + "_" + FileUpload1.FileName ;
            string saveLocation = HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["FileUploadLocation"]);
            string SavePath = Path.Combine(saveLocation, tempFileName);
            try
            {
                FileUpload1.SaveAs(SavePath);
                oleconn = new OleDbConnection(GetOleDbConnectionString(SavePath));
                string strQuery = "SELECT * FROM [Sheet1$]";
                DataTable dt = new DataTable();
                OleDbCommand ocmd = new OleDbCommand(strQuery, oleconn);
                oleconn.Open();
                OleDbDataReader odr = ocmd.ExecuteReader();
                if(odr .HasRows )
                {
                    dt.Load(odr);
                    GridView1.DataSource = dt;
                    GridView1.DataBind();
                }
                oleconn.Close();
                DeleteFile(SavePath);
            }
            catch (Exception ex)
            {
                lblerror.Text = "error : " + ex.Message;
            }
 
        }
    }
 
    #region GetOleDbConnectionString(string savepath)
    public string GetOleDbConnectionString(string savepath)
    {
        var finfo = new FileInfo(savepath);
 
        if (!finfo.Exists)
        {
            throw new FileNotFoundException(savepath);
        }
 
        var fileExtension = finfo.Extension.ToLower();
        switch (fileExtension)
        {
            case ".xls":
                return string.Format(ConfigurationManager.AppSettings["xlsOleDBConnection"], savepath);
            case ".xlsx":
                return string.Format(ConfigurationManager.AppSettings["xlsxOleDBConnection"], savepath);
            default:
                throw new NotSupportedException(String.Format("This file type {0} is not supported!", fileExtension));
        }
    }
    #endregion
    #region DeleteFile(string savepath)
    public void DeleteFile(string savepath)
    {
        if (File.Exists(savepath))
        {
            try
            {
                File.Delete(savepath);
            }
            catch { }
        }
    }
    #endregion
}

For complete source code download from below links :

1. http://uploading.com/files/33cmc544/ReadExcelIntoGridView.zip/

2. http://www.filesonic.com/file/1094444574/ReadExcelIntoGridView.zip

3. http://www.fileserve.com/file/4k9QPXn

Thank You.

 

Responses
No responses found for this thread. Be the first to respond this thread.
Post Reply
Login to post Response.