How to insert data to Excel file using OLEDB

In the preceding examples, we employed the Microsoft Excel 12.0 Object Library to read from and write to Excel files. However, in C#, there is an alternative approach to manipulate cell content in an Excel file without utilizing the Excel Object. This can be accomplished by using OLEDB (Object Linking and Embedding Database) technology.

System.Data namespace

To implement these operations, it is necessary to import the System.Data namespace into the project. This namespace provides essential components such as OleDbConnection, OleDbDataAdapter, and DataSet to facilitate data manipulation tasks. When adding new content to a cell or inserting new content into the Excel file, we can utilize the INSERT command, similar to SQL operations, to seamlessly incorporate the desired data.

Sample UPDATE sql

sql = "Insert into [Sheet1$] (id,name) values('5','e')"

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

csharp-excel-insert-row-oledb 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 = "Insert into [Sheet1$] (id,name) values('5','e')"; myCommand.CommandText = sql; myCommand.ExecuteNonQuery(); MyConnection.Close(); } catch (Exception ex) { MessageBox.Show (ex.ToString()); } } } }

Conclusion

Adopting this approach, developers can effectively interact with Excel files, performing various operations on cell content without the direct reliance on the Excel Object model, thus offering enhanced flexibility and efficiency in Excel file manipulation.