Excel Operation (Update/Select/Insert)


Posted Date:     Total Responses: 0    Posted By: Lakhan Pal   Member Level: Gold   Points/Cash: 75   


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)

  • Project Feedbacks

    Author: Member Level: BronzeRevenue Score: 2 out of 52 out of 5
    Hi,
    program is good. I would like learn such type of things.

    Thank you in advance!

    Thanks - jeruel.nate@gmail.com


    Author: Member Level: BronzeRevenue Score: 2 out of 52 out of 5
    Hi,

    Its very good...pls send me the files to sudha.selkum@gmail.com..

    thanks in advance
    sudha


    Author: Member Level: GoldRevenue Score: 2 out of 52 out of 5
    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


    Post Feedback
    You must Sign In to post a feedback.
    Next Project: Custom paging in gridview
    Previous Project: TimeZone Information From Registry

    Return to Project Index

    Post New Project


    Related Projects


    Top Contributors
    Today
      Last 7 Daysmore...

      Awards & Gifts
       
      Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India