How to update data in Excel file using OLEDB

In the previous examples, we relied on the Microsoft Excel 12.0 Object Library to perform read and write operations on Excel files. However, in C#, an alternative approach allows us to manipulate cell content in an Excel file without utilizing the Excel Object model. This can be achieved by using OLEDB (Object Linking and Embedding Database) technology.

System.Data namespace

To execute these operations, we employ components such as OleDbConnection, OleDbDataAdapter, and DataSet within the System.Data namespace. These components provide the necessary functionalities to carry out tasks such as inserting, editing, deleting, and selecting cell content in an Excel file. Importing the System.Data namespace into the project is crucial to utilize these components effectively.

UPDATE command

When updating or modifying the content within a cell, we can employ the UPDATE command, similar to SQL operations, to seamlessly perform the desired modifications on the cell content.

Sample UPDATE sql

sql = "Update [Sheet1$] set name = 'New Name' where id=1"

The following picture shows before and after update of the Sheet.

csharp-excel-oledb-update. Full Source C#
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:\\csharp.net-informations.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()); } } } }

Conclusion

Adopting this approach, developers can efficiently interact with Excel files, manipulating cell content without the direct dependency on the Excel Object model, thus offering enhanced flexibility and efficiency in Excel file manipulation.






Related Topics
  1. How to create Excel file in C#
  2. How to open an Excel file in C#
  3. How to read an Excel file using C#
  4. How to programmatically Add New Worksheets
  1. How to delete worksheet from an excel file
  2. How to format an Excel file using C#
  3. How to insert a picture in excel from C# App
  4. How to insert a background picture in excel
  5. How to create Excel Chart from C#
  6. How to export excel chart from C#
  7. How to excel chart in C# picturebox
  8. C# data validation input box in excel file
  9. Read and Import Excel File into DataSet or DataTable
  10. How to insert data to Excel file using OLEDB
  11. How to export databse to excel file
  12. How to export DataGridView to excel file