Data manipulation in excel

Posted on

Question :

I need to get the data generated from a proc save to some variable and make a UPDATE into a worksheet that already exists on a fixed path in the system. Is it possible to do this process via code?

Ex.
The procedure returned this data to me:

Now I would need to store this data somewhere and give a UPDATE
in a spreadsheet already created that will be fixed in a certain way.

    

Answer :

Here is an example code to update an Excel worksheet with C # and ADO:

var connectionStringBuilder = new 
System.Data.OleDb.OleDbConnectionStringBuilder();
connectionStringBuilder.DataSource = @"c:devtmpconsolidated.xlsx";
connectionStringBuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
connectionStringBuilder.Add("Extended Properties", "Excel 12.0;");

using (var connection = new System.Data.OleDb.OleDbConnection(connectionStringBuilder.ToString()))
{
    connection.Open();
    var updateCommand = connection.CreateCommand();
    updateCommand.CommandText = "update [second$] S inner join [first$] F on S.ID = F.ID set S.Language = F.Language";
    updateCommand.ExecuteNonQuery();
}

Credit to issue

    

Leave a Reply

Your email address will not be published. Required fields are marked *