Getsuyodev, Programming is mainly used when constructing an application. Programming requires knowledge of application domains, algorithms, and programming language expertise. Programming can be developed in different logic based on developer knowledge.

How to update data in Excel file using OLEDB



In the previous examples, we used Microsoft Excel 12.0 Object Library for reading or write to an Excel file. In C# without using Excel Object we can insert, edit, delete, select etc. in cell content of an Excel file using OLEDB.

Here we are using OleDbConnection, OleDbDataAdapter, DataSet for doing these operations in an Excel file. You have to import System.Data in the project for doing these operations. For update the content in the cell or modify the content in a cell, We can use the UPDATE command like in SQL Operations.

sample UPDATE SQL

sql = "Update [Sheet1$] set name = 'New Name' where id=1"
using System;
using System.Drawing;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel; 

namespace WindowsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                System.Data.OleDb.OleDbConnection MyConnection ;
                System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
                string sql = null;
                MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\\getsuyodev.xls';Extended Properties=Excel 8.0;");
                MyConnection.Open();
                myCommand.Connection = MyConnection;
                sql = "Update [Sheet1$] set name = 'New Name' where id=1";
                myCommand.CommandText = sql;
                myCommand.ExecuteNonQuery();
                MyConnection.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show (ex.ToString());
            }
        }
   }
}

Comments are closed.

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More