Insert cell data in an Excel file using OLEDB

In VB.NET, it is possible to perform a range of operations such as inserting, editing, deleting, and selecting cell content within an Excel file without relying on the Excel Object. This can be accomplished by utilizing OLEDB (Object Linking and Embedding Database) technology, which provides a means to interact with Excel files directly.

To facilitate these operations, specific components are utilized, including OleDbConnection, OleDbDataAdapter, and DataSet. These components play a crucial role in establishing a connection to the Excel file, executing commands, and retrieving or modifying data as needed. It is important to ensure that the System.Data namespace is imported into the project to access these components.

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

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

vb.vb.net_excel_insert_row_oledb.JPG

When it comes to adding new content or inserting data into specific cells, the INSERT command, similar to its usage in SQL operations, can be employed. This command enables developers to specify the target cell or range and provide the desired content to be inserted. This flexibility allows for seamless integration of new data into the Excel file, ensuring accurate and efficient data management.

Full Source VB.NET
Imports System.Data Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click Try Dim MyConnection As System.Data.OleDb.OleDbConnection Dim myCommand As New System.Data.OleDb.OleDbCommand Dim sql As String MyConnection = New System.Data.OleDb.OleDbConnection _ ("provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + _ "'c:\testfile.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 ex As Exception MsgBox(ex.ToString) End Try MsgBox("Row Added ") End Sub End Class

Conclusion

By using the capabilities of OLEDB and the related components in VB.NET, developers can effectively interact with the cell content of Excel files, performing essential tasks such as inserting, editing, deleting, and selecting data. This approach provides a powerful alternative to using the Excel Object, granting greater control and flexibility when working with Excel files programmatically.