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