Read, Insert and Update Data into Excel Sheet

This application will tell you about the operation on excel sheet (like Insert, Update and Read) with the help of simple queries.
i am using OLEDB provider for these operations.
Connection String for OLEDB is:

string file = Server.MapPath("UserData.xls"); string constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";
Extended Properties=Excel 8.0;";

Read data and display the result in repeater control:
First we'll read the data from the Excel sheet and display the result into a repeater control.
first query to fetch the records:

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

[Sheet1$] name of the sheet in the Excel workbook.And UserID,UserName,Country,State,City are the name of the column in this sheet.
this query will return all the records from Excel Sheet [Sheet1$].
Code to execute the above query is:

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();
}
}

Update Data:
we can click on the Edit Data link for the corresponding row to edit the record.
on click of Edit Data button the corresponding records data will be shown in textboxes. user can change the text and click on the "Update Excel Data" to reflect changes is Excel Sheet.
Code to Update Record:

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();
}
}

Similar way we can add the new record into the Excel Sheet. first we will get the UserId of the Last record and add one to that record to get the next UserID as:

Int32 LastUserID = Convert.ToInt32(((Label)rptUserData.Items[rptUserData.Items.Count - 1].FindControl("lblID")).Text);
LastUserID += 1;
Click Here To Download Source Code

Note: Please contact me in case you have any error in download the code: lakhangarg@gmail.com

Note: User the Following Connection String for xlsx file:
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + file + ";Extended Properties=Excel 12.0;"

2 comments:

Puneet Sharma said...

Indeed dude, a nice bunch of useful information !! All the best to keep it Up !!

Jom George said...

Hi,

please refer this blog..

http://jomgrg.blogspot.com/2011/06/excel-2007-and-other-versions-uploading.html

Thank you.