Update cell data in an Excel file using OLEDB

In VB.NET, developers have the ability to manipulate cell content within an Excel file without relying on the Excel Object. This can be accomplished through the utilization of OLEDB (Object Linking and Embedding Database) technology, which enables direct interaction with Excel files.

To facilitate these operations, specific components such as OleDbConnection, OleDbDataAdapter, and DataSet come into play. These components play crucial roles in establishing connections to Excel files, executing commands, and managing data operations within the file. Importing the System.Data namespace is imperative as it grants access to these components and their functionalities.

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

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


When it comes to updating or modifying cell content, developers can utilize the UPDATE command, reminiscent of its usage in SQL operations. This command empowers developers to specify the target cell or range and supply the updated content to be applied. By utilizing the power of the UPDATE command, developers can efficiently modify existing data within Excel cells, ensuring accuracy and up-to-date information.

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 = "Update [Sheet1$] set name = 'New Name' where id=1" myCommand.CommandText = sql myCommand.ExecuteNonQuery() MyConnection.Close() Catch ex As Exception MsgBox(ex.ToString) End Try MsgBox("Updated ") End Sub End Class


It is important to note that utilizing OLEDB and its related components necessitates importing the required namespaces and ensuring proper project configuration. With these preparations in place, developers can effectively update cell content in Excel files, thereby providing a robust solution for managing and modifying data within the spreadsheet.