Bind Excel data to Dataset


In this article, I will explain how to bind Excel data to Dataset. Using System.Data.OleDb namespace, connect excel and retrieve Excel data and bind it to dataset. I am passing connectiong to the OleDbConnection object conn and then I am creating an object for OleDbDataAdapter where I am passing the select statement to fetch the Sheet1 data. Then I am creating dataset dsEmployee which is filled with the excel data by OleDbDataAdapter fill method.

Bind Excel data to Dataset



I will explain how to bind Excel data to Dataset. Using System.Data.OleDb namespace, it is easier to connect excel and retrieve Excel data and bind it to dataset. Using OleDbDataAdapter is used to store the excel data to dataset.


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)
{

}
protected void btnupload_Click(object sender, EventArgs e)
{
string myconnectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + System.AppDomain.CurrentDomain.BaseDirectory + "App_Data\\" + fileImgUploadnew.FileName + ";" + "Extended Properties=Excel 8.0;";
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(myconnectionstring);
OleDbDataAdapter myDataAdapter = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [Sheet1$]", conn);
DataSet dsEmployee = new DataSet();
conn.Open();
myDataAdapter.Fill(dsEmployee, "EmployeeTable");
//Deleting empty records
for (int i = 0; i < dsEmployee.Tables["EmployeeTable"].Rows.Count; i++)
{
if (dsEmployee.Tables["EmployeeTable"].Rows[i]["Employeename"].ToString() == "")
{
dsEmployee.Tables["EmployeeTable"].Rows[i].Delete();
}
}
dsEmployee.AcceptChanges();
conn.Close();



}
}



In the above code I am passing connection string which contains provider that is Microsoft.Jet.OLEDB.4.0, datasource that is the location of excel file. I am passing connectiong to the OleDbConnection object conn and then I am creating an object for OleDbDataAdapter where I am passing the select statement to fetch the Sheet1 data. Then I am creating dataset dsEmployee which is filled with the excel data by OleDbDataAdapter fill method.

I had attached sample code and excel for reference.
Note I had bind data for excel 2003.


Attachments

  • sample Excel file for Bind Excel data to Dataset (44780-964-sample-Excel-file-Bind-Excel-data-Dataset.xls)
  • Bind Excel data to Dataset (44780-9116-Bind-Excel-data-Dataset.zip)
  • 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: