How to update excel file data using row and column in C# using OleDbConnection

Question

I am have created some function to read data form excel file using, OleDbConnection, OleDbDataAdapter, DataSet. I am able to read data successfully using sheet name, row, column number.

I function using same to update value of excel sheet by passing row and column number.

I need some help,

Thanks in advance.

Perhaps this could help stackoverflow.com/questions/22592501/… — Steve
– Steve, Commented Apr 3, 2015 at 14:26

mikdav · Accepted Answer · 2015-04-06 17:36:11Z

1

Unless you have a strong reason for using OleDb for this, I recommend against it. The usage is very limited and archaic and the providers are no longer shipped with Office 2013 or in Windows. NuGet EPPlus and make your life a lot easier.

However, here is an example of how to do an insert and update using OleDb:

        using (OleDbConnection cn = new OleDbConnection(connectionString))
        {
            cn.Open();
            using (OleDbCommand cmd1 = new OleDbCommand("INSERT INTO [MySheet$] (COLUMN1, COLUMN2) VALUES ('Count', 1);", cn))
            {
                cmd1.ExecuteNonQuery();
            }

            using (OleDbCommand cmd1 = new OleDbCommand("UPDATE [MySheet$] SET COLUMN2 = 5 WHERE ID = 1", cn))
            {
                cmd1.ExecuteNonQuery();
            }
        }

edited Apr 6, 2015 at 17:36

answered Apr 3, 2015 at 14:43

mikdav

963 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sadik Ali Over a year ago

Thanks for you quick reply, I have no unique key where I can use where condition, can update value only based ob row and column

mikdav Over a year ago

There is a special column named ID, which holds the row number. I have updated my example to update based off of this. I do not believe you can select ID as a column in a select statement, even though it can be used in the where clause.

Sadik Ali Over a year ago

Thanks mikdav, your explanation in details, I will work in accordingly.

Collectives™ on Stack Overflow

How to update excel file data using row and column in C# using OleDbConnection

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related