Description of the Features:
This application can read, write and update data of the excel sheet.
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;
Code to insert new Record:
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();
}
}
Please find the attached zip file for complete source code of this application.
 Attachments
Excel Operation (Update/Select/Insert) (545-17811-ExcelOperation.zip)
|
| Author: siva | Member Level: Gold | Revenue Score:  |
Hi,
Its really nice. I would like learn such type of things.
It will be greatful for me to send the files.
Thanks
Siva Sreenath
|