Home > DeveloperSection > Forums > Update an entire row in Excel using OleDB command
Goti Bandu

Total Post:119

Points:835
Posted on    December-09-2013 11:32 PM

 C# C# 
Ratings:


 1 Reply(s)
 3425  View(s)
Rate this:

I am trying to blankout/clear an entire excel tab. But nothing seems to work

I tried the following approach:

OleDbConnection connection = new OleDbConnection(connectionString);

OleDbCommand command = new OleDbCommand("Select * FROM [Sheet1$]", connection);

OleDbCommand count = new OleDbCommand("Select count(*) FROM [Sheet1$]", connection);

DataSet dataset = new DataSet();

OleDbDataAdapter adapter = new OleDbDataAdapter();

adapter.SelectCommand = new OleDbCommand("Select * from [Sheet1$]", connection);

adapter.Fill(dataset);

 

for (int i = 0; i < dataset.Tables[0].Rows.Count; i++)

{

    DataRow dtRow = dataset.Tables[0].Rows[i];

    foreach (DataColumn col in dataset.Tables[0].Columns)

    {

        if(col.DataType == typeof(string))

        dataset.Tables[0].Rows[i][col] = "";

   }

}

dataset.Tables[0].AcceptChanges();

adapter.Update(dataset.Tables[0]);



ezra heywood
ezra heywood

Total Post:145

Points:1019
Posted on    December-10-2013 12:09 AM

Hi Goti,

If your Excel file has primary key,you can use OleDbCommandBuilder,if not, OleDbDataAdapter or OleDbCommand will be a better way.adapter.you can't directly use Update(dataset.Tables[0]),here is the code:

OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", myConnection);

            DataTable dt = new DataTable();

            adapter.Fill(dt);

            string updateSQL = string.Format(@"UPDATE [Sheet1$] SET Status =? WHERE ID IN ( SELECT TOP 5 ID FROM [Sheet1$] WHERE Status <>? OR Status IS NULL )");

            adapter.UpdateCommand = new OleDbCommand(updateSQL, myConnection);

            adapter.UpdateCommand.Parameters.Add("@Status", OleDbType.Char, 255).SourceColumn = "Status";

            adapter.UpdateCommand.Parameters.Add("@OldStatus", OleDbType.Char, 255, "Status").SourceVersion = DataRowVersion.Original;

            dt.AsEnumerable().Take(5).ToList().ForEach(o => o.SetField("Status", @"Imported"));

            dt.AcceptChanges();

            adapter.Update(dt);


Don't want to miss updates? Please click the below button!

Follow MindStick