Asp.Net : Using Excel [Insert,Update and Delete]

using System;

using System.Data;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Data.OleDb;

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

{

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

BindRepeater();

}

private DataTable ReadExcelData(Int32 UserID)

{

string file = Server.MapPath(“UserData.xls”);

string constr = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + file + “;Extended Properties=Excel 8.0;”;

string query = “Select UserID,UserName,Country,State,City from [Sheet1$]”;

if (UserID > 0)

query = query + ” WHERE UserID=” + UserID.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];

}

private void BindRepeater()

{

rptUserData.DataSource = ReadExcelData(-1);

rptUserData.DataBind();

}

protected void rptUserData_ItemCommand(object source, RepeaterCommandEventArgs e)

{

if (e.CommandName == “Edit”)

{

DataTable dtData= ReadExcelData(Convert.ToInt32(e.CommandArgument));

if (dtData.Rows.Count > 0)

{

txtUserName.Text = dtData.Rows[0][“UserName”].ToString();

txtCountry.Text = dtData.Rows[0][“Country”].ToString();

txtState.Text = dtData.Rows[0][“State”].ToString();

txtCity.Text = dtData.Rows[0][“City”].ToString();

btnUpdate.CommandArgument = dtData.Rows[0][“UserID”].ToString();

btnUpdate.Visible = true;

btnInsert.Visible = false;

divInsertUpdate.Visible = true;

}

}

}

protected void lnkInsert_Click(object sender, EventArgs e)

{

btnUpdate.Visible = false;

btnInsert.Visible = true;

divInsertUpdate.Visible = true;

}

protected void btnInsert_Click(object sender, EventArgs e)

{

Int32 LastUserID = Convert.ToInt32(((Label)rptUserData.Items[rptUserData.Items.Count – 1].FindControl(“lblID”)).Text);

LastUserID += 1;

string file = Server.MapPath(“UserData.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$](UserID,UserName,Country,State,City) VALUES(” + LastUserID + “,\”” + txtUserName.Text.Trim() + “\”,\”” + txtCountry.Text.Trim() + “\”,\”” + txtState.Text.Trim() + “\”,\”” + txtCity.Text.Trim() + “\”)”;

using (OleDbCommand objCmd = new OleDbCommand(query, Connection))

{

objCmd.ExecuteNonQuery();

objCmd.Dispose();

Connection.Close();

}

}

BindRepeater();

divInsertUpdate.Visible = false;

}

private void UpdateRecord()

{

string file = Server.MapPath(“UserData.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 UserName=\”” + txtUserName.Text.Trim() + “\”,Country=\”” + txtCountry.Text.Trim() + “\”,State=\”” + txtState.Text.Trim() + “\”,City=\”” + txtCity.Text.Trim() + “\” WHERE UserID=”+ btnUpdate.CommandArgument.ToString();

using (OleDbCommand objCmd = new OleDbCommand(query, Connection))

{

objCmd.ExecuteNonQuery();

objCmd.Dispose();

Connection.Close();

}

}

}

protected void btnUpdate_Click(object sender, EventArgs e)

{

UpdateRecord();

BindRepeater();

divInsertUpdate.Visible = false;

}

}

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: