Importing data from excel to gridview


This code snippet describes about how we can import the data from an excel sheet into gridview. using the Microsoft.Jet.OLEDB.4.0 managed provider we are going to import the data from the excel sheet. We are going to retrieve the data from the excel worksheet and populate the data in the dataset using a dataadapter. Then we are going to bind the data of the dataset to the gridview.

This code snippet describes about how we can import the data from an excel sheet into gridview. Please import the following namespaces.


using System.Data.OleDb;
using System.Data;

// Connect to the Excel Spreadsheet
OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("ExcelImport.xls") + ";" + "Extended Properties=Excel 8.0;");
// create your excel connection object using the connection string
con.Open();
// Create a new Adapter
OleDbDataAdapter objDataAdapter = new OleDbDataAdapter();
//Below "Members" is the worksheet name of the excel document which is appended with $ symbol
OleDbCommand objSelectCommand = new OleDbCommand("SELECT * FROM [Members$]", con);
objDataAdapter.SelectCommand = objSelectCommand;

// Create a DataSet
DataSet ds = new DataSet();

// Populate the DataSet with the spreadsheet worksheet data
objDataAdapter.Fill(ds);

// Bind the data to the GridView
GridViewExcel.DataSource=ds.Tables[0].DefaultView;
GridViewExcel.DataBind();


Article by Vaishali Jain
Miss. Jain Microsoft Certified Technology Specialist in .Net(Windows and Web Based application development)

Follow Vaishali Jain or read 127 articles authored by Vaishali Jain

Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: