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
4. This code worked but it only add the same vales set in the code but I want to add values by calling
View Tutorial By: Dammy Temmy at 2013-01-17 17:22:17
5. can anyone please provide me how to map the columns from the sql server to the columns in the excels
View Tutorial By: Gaurav Balyan at 2012-01-03 11:09:26
7. HI everyone,
I want to select particular row excel data by getting text from textbo
View Tutorial By: aarthi.s at 2011-09-06 10:09:16
I wann to insert data into excel file but not in static way. I want insert data from text
View Tutorial By: PRUTHVIRAJ RATHOD at 2011-05-23 11:42:14
9. This works with inserts but it is very slow for long records. Any hint about how could it be hurried
View Tutorial By: Antonio Pereira at 2011-02-04 08:54:45
I dont have a problem Insert, Update and select but ı have an error ehen Delete
View Tutorial By: Yavuz at 2010-09-01 14:15:02
11. insert a value in excel is not a problem but insert a textbox is giving is error the error is in the
View Tutorial By: durga at 2010-03-05 23:08:02
12. Have you tried checking for the latest office/excel service packs and hotfixes?
View Tutorial By: Pat at 2010-01-07 04:54:31
I've tried this but got a problem. First I run a 'create table'- command to define my col
View Tutorial By: Hendrik Jabs at 2009-12-10 02:46:13
Most Viewed Articles (in VB.net )
Latest Articles (in VB.net)
Comment on this tutorial
- Data Science
- Cloud Computing
- Java Beans
- Mac OS X
- Office 365
- Tech Reviews