In this article we can see how to insert data into Excel file, 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, and DataSet for doing these operations in an Excel file. You have to import System.Data in the project for doing these operations. For add new content in the cell or insert a new content, we can use the INSERT command like in SQL Operations.
sample UPDATE sql
sql = "Insert into [Sheet1$] (id,name) values('5','e')"
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 = "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
When you execute this source code it will insert a row in the Excel file.