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>
    <form id="form1" runat="server">
        <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">

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);
                oleconn = new OleDbConnection(GetOleDbConnectionString(SavePath));
                string strQuery = "SELECT * FROM [Sheet1$]";
                DataTable dt = new DataTable();
                OleDbCommand ocmd = new OleDbCommand(strQuery, oleconn);
                OleDbDataReader odr = ocmd.ExecuteReader();
                if(odr .HasRows )
                    GridView1.DataSource = dt;
            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);
                throw new NotSupportedException(String.Format("This file type {0} is not supported!", fileExtension));
    #region DeleteFile(string savepath)
    public void DeleteFile(string savepath)
        if (File.Exists(savepath))
            catch { }

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.


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