Email: Password:       Forgot Password 
    .netCodeSG
A Saarsha Group Online Community for dot net codes group like C#, Asp.NET, VB.NET, Sharepoint, JavaScript, JQuery, Ajax, SQL, WCF, WPF.
 
TECHNOLOGIES:
 

Show Excel Sheet Data in a GridView

Article:
Viewed:  1242 
Posted On:  07/09/2012 08:38:10 
Read a excel file and import excel sheet data in a GridView in asp.net 

In this article I am going to show how we can read a MS Excel sheet in a Grid in asp.net .

Below is my excel sheet...

1.png

Image 1.

Here in above excel we can have multiple sheet... 

Below is my aspx code

<%@ 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">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Excel in GridView</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <table cellpadding="1" cellspacing="1" width="50%" align="center">
            <tr>
                <td>
                    <asp:GridView ID="GridViewExcel" runat="server" EnableViewState="false" />
                </td>
            </tr>
        </table>
    </div>
    </form>
</body>
</html>

 This is my aspx.cs code

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.Data.OleDb; 

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {       
        GetExcelData();
    } 

    private void GetExcelData()
    {
        string file = Server.MapPath("~/App_Data/Employee_Excel.xlsx");

        //Here your excel can in 2003 or it can be in 2007
        //if 2003 then connection string
        string connStr2003 = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=&quot;Excel 8.0;HDR=YES;IMEX=1&quot;",file);

         // if 2007 then connection string
        string connStr2007 = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=&quot;Excel 12.0 Xml;HDR=YES;IMEX=1&quot;", file); 

        // in this example I have 2007       

        DataTable dt= new DataTable(); 

        using (OleDbConnection conn = new OleDbConnection(connStr2007))
        {
            string sheet = @"SELECT * FROM [Sheet1$]"
            using (OleDbCommand cmd = new OleDbCommand(sheet, conn))
            {
                conn.Open();
 

                using (OleDbDataAdapter ad = new OleDbDataAdapter(cmd))
                {
                    ad.Fill( dt );
                }

                conn.Close();
            };
        } 

        GridViewExcel.DataSource =  dt ;
        GridViewExcel.DataBind();
    }
} 

When I run my application then output..

2.png

Image 2.

 

  Comment:
         HOME   |   Submit Article   |   Contact Us   |   About Us   |   Terms & Condition   |   Advertise With us