Read,Write And Update Data In Excel Sheet Using Asp.Net And C#

Sometime we require to read an Excel file and Modify the data and also save the modified record in the same file.  (Code Link is given Below)
Here i am giving an example to How to Read,Write and Update Data in Excel Sheet.

I am using a Excel file which has 4 columns:
EmpNo
EmpName
EmpAddress
EmpSalary

and to show this data i am using Repeater Control of Asp.Net 

The code of .cs file is given below 

i am using OLEDB provider for these operations.

 Method that will return DataTable to bind Repeater Control

private DataTable ReadExcelData(Int32 EmpID)
{
string file = Server.MapPath("employees.xls");
string constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Extended Properties=Excel 8.0;";

string query = "Select EmpNo,EmpName,EmpAddress,EmpSalary from [Sheet1$]";
if (EmpID > 0)
query = query + " WHERE EmpNo=" + EmpID.ToString();
DataSet dsUserData = new DataSet();
using (OleDbConnection Connection = new OleDbConnection(constr))
{
using (OleDbDataAdapter DataAdapter = new OleDbDataAdapter(query, Connection))
{
DataAdapter.Fill(dsUserData, "UserData");
DataAdapter.AcceptChangesDuringFill = false;
DataAdapter.Dispose();
Connection.Close();
}
}
return dsUserData.Tables[0];
}
  
 Method that will call above method and bind Repeater

private void BindRepeater()
{
rptUserData.DataSource = ReadExcelData(-1);
rptUserData.DataBind();
}
 

Update Method which will be called to update record

private void UpdateRecord()
{
string file = Server.MapPath("employees.xls");
string constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Extended Properties=Excel 8.0;";
using (OleDbConnection Connection = new OleDbConnection(constr))
{
Connection.Open();
string query = "UPDATE [Sheet1$] SET EmpName=\"" + ename.Text.Trim() + "\",EmpAddress=\"" + add.Text.Trim() + "\",EmpSalary=\"" + esal.Text.Trim() + "\" WHERE EmpNo=" + btnUpdate.CommandArgument.ToString();
using (OleDbCommand objCmd = new OleDbCommand(query, Connection))
{
objCmd.ExecuteNonQuery();
objCmd.Dispose();
Connection.Close();
}
}
 
Insert Method that will be called to Insert a Record in Excel File
protected void btnInsert_Click(object sender, EventArgs e)
{

Int32 LastEmpNO = Convert.ToInt32(((Label)rptUserData.Items[rptUserData.Items.Count - 1].FindControl("lblID")).Text);
LastEmpNO += 1;
string file = Server.MapPath("employees.xls");
string constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Extended Properties=Excel 8.0;";
using (OleDbConnection Connection = new OleDbConnection(constr))
{
Connection.Open();
string query = "INSERT INTO [Sheet1$](EmpNo,EmpName,EmpAddress,EmpSalary) VALUES(" + LastEmpNO + ",\"" + ename.Text.Trim() + "\",\"" + add.Text.Trim() + "\",\"" + esal.Text.Trim() + "\")";
using (OleDbCommand objCmd = new OleDbCommand(query, Connection))
{
objCmd.ExecuteNonQuery();
objCmd.Dispose();
Connection.Close();
}
}
BindRepeater();
divInsertUpdate.Visible = false;
}