Load Data From Excel To Grid View in Asp.net C#

Load Data From Excel To Grid View in Asp.net C#
This is very easy to load data from excel sheet to grid view in asp.net web page using c#.
In this tutorial you can learn how to Load excel sheet data to Grid View easily using asp.net C# code.
This tutorial also covers how to get data from any excel file to Grid View dynamically in c# using file Upload control.


Follow this steps
1. Create an asp.net project.
2. design asp.net page with file Upload, button control and a Grid View control.
3. create an excel connection string in the web config file.
4. Write code to load data from excel sheet to Grid View asp.net using C#.



code:-

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.IO;

using System.Collections;

using System.Configuration;

using System.Data.OleDb;

using System.Data;

public partial class _Default : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

        lblerror.Text = "";

    }

    protected void btnupload_Click(object sender, EventArgs e)

    {

      

        if (FileUpload1.HasFile)

        {

            string filename = Path.GetFileName(FileUpload1.PostedFile.FileName);

            string fileext = Path.GetExtension(FileUpload1.PostedFile.FileName);

            string filepath = Server.MapPath("~/upload/") + filename;

            FileUpload1.SaveAs(filepath);

            string constr = "";

            switch (fileext)

            { 

                case ".xls": //excel file 97-03

                    constr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;

                    break;

                case ".xlsx": //excel file 07

                    constr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;

                    break;

            }

            constr = string.Format(constr, filepath);

            OleDbConnection connexcel = new OleDbConnection(constr);

            OleDbCommand cmdexcel = new OleDbCommand();

            OleDbDataAdapter oda = new OleDbDataAdapter();

            cmdexcel.Connection = connexcel;

            //get the name of first sheet 

            connexcel.Open();

            DataTable dtexcelschema = connexcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            string sheetname = dtexcelschema.Rows[0]["Table_Name"].ToString();

            connexcel.Close();

            //read data from First sheet

            DataTable dtdatasheet1 = new DataTable();

            connexcel.Open();

            cmdexcel.CommandText = "select * From [" + sheetname + "]";

            oda.SelectCommand = cmdexcel;

            oda.Fill(dtdatasheet1);

            connexcel.Close();

            //Bind Data To Grid View 1

            GridView1.DataSource = dtdatasheet1;

            GridView1.DataBind();

        }

        else 

        {

            lblerror.Text = "Please Select File First"; 

        }

    }

}

web.config

  <connectionStrings>

    <add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>

    <add name="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>

  </connectionStrings>

#Subscribe the Channel Link :- #bansodetechsolution #ajupgrading
IF any Query or Doubt DM on #Instagram :- #bansode_ajay_2102 @AjUpgradingBANSODETECHSOLUTION
LinkedIn Profile
Blog For code / Notes:-