UPDATE AN ENTIRE ROW IN EXCEL USING OLEDB COMMAND

Goti Bandu

Total Post:119

Points:835
Posted by  Goti Bandu
C# 
C#
 4419  View(s)
Ratings:
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]);

  1. ezra heywood

    Post:145

    Points:1019
    Re: Update an entire row in Excel using OleDB command

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

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!