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.






Related Topics
  1. How to create an Excel file in VB.NET
  2. How to open or read an existing Excel file in VB.NET
  3. How to read an Excel file using VB.Net
  4. Add new worksheet in Excel file - VB.Net
  1. Delete worksheet from an excel file - VB.Net
  2. How to Format Excel Page in VB.NET
  3. How to insert a Picture in Excel through programing on VB.NET
  4. How to insert a background Picture in Excel through VB.NET
  5. How to create a Chart in Excel in VB.NET
  6. How to export a Chart in Excel as Picture file from VB.NET
  7. How to Excel Chart in VB.NET Picture Box
  8. How to Excel DataBar in VB.NET
  9. How to Excel Data Validation Input Message
  10. Read and Import Excel File into DataSet or DataTable in vb.net
  11. How to update cell data in an Excel file using OLEDB
  12. How to export from database to excel
  13. How to export from DataGridView to excel in VB.Net