Update cell data in an Excel file using OLEDB
Without using Excel Object we can insert , edit , delete , select etc. in cell content of an Excel 2007 file using OLEDB in VB.NET 2005 . Here we are using OleDbConnection , OleDbDataAdapter , DataSet for doing these operations in an Excel file. You have to import System.Data in the project for doing these operations . For update the content in the cell or modify the content in a cell , We can use the UPDATE command like in SQL Operations.
sample UPDATE sql
sql = "Update [Sheet1$] set name = 'New Name' where id=1"
The follwoing picture shows before and after update of the Sheet.
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 |
When you execute this source code the will update the corrosponding content.
|