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.