By: Issac Emailed: 1699 times Printed: 2200 times
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.
When you execute this source code it will insert a row in the Excel file.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
View Tutorial By: Hendrik Jabs at 2009-12-10 02:46:13
View Tutorial By: Pat at 2010-01-07 04:54:31
View Tutorial By: durga at 2010-03-05 23:08:02
View Tutorial By: Yavuz at 2010-09-01 14:15:02
View Tutorial By: Antonio Pereira at 2011-02-04 08:54:45
View Tutorial By: PRUTHVIRAJ RATHOD at 2011-05-23 11:42:14
View Tutorial By: aarthi.s at 2011-09-06 10:09:16
View Tutorial By: Gaurav Balyan at 2012-01-03 11:07:05
View Tutorial By: Gaurav Balyan at 2012-01-03 11:09:26
View Tutorial By: Dammy Temmy at 2013-01-17 17:22:17
Most Viewed Articles (in VB.net )
Most Emailed Articles (in VB.net)