Click here to Skip to main content
15,504,424 members
Articles / Desktop Programming / Windows Forms
Article
Posted 20 Mar 2007

Stats

381.2K views
5.6K downloads
49 bookmarked

Reading and writing an Excel file using VB.NET

Rate me:
Please Sign up or sign in to vote.
2.72/5 (19 votes)
20 Mar 2007CPOL
This code helps the user to interact with an Excel file using the OleDBDataProvider in VB.NET.

Screenshot - ExcelSheet.jpg

Introduction

This articles helps user to Insert, Update, Delete, and Select data in Excel files using the OLEDBDataProvider in VB.NET.

Here is the connection string to connect with Excel using OleDBDataProvider:

VB
Private Const connstring As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\Test.xls;Extended Properties=""Excel 8.0;HDR=YES;"""

Here is the code on the button click event to select and insert data in an Excel file:

VB
Private Sub Button1_Click(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles Button1.Click
    Dim pram As OleDbParameter
    Dim dr As DataRow
    Dim olecon As OleDbConnection
    Dim olecomm As OleDbCommand
    Dim olecomm1 As OleDbCommand
    Dim oleadpt As OleDbDataAdapter
    Dim ds As DataSet
    Try
        olecon = New OleDbConnection
        olecon.ConnectionString = connstring
        olecomm = New OleDbCommand
        olecomm.CommandText = _
           "Select FirstName, LastName, Age, Phone from [Sheet1$]"
        olecomm.Connection = olecon
        olecomm1 = New OleDbCommand
        olecomm1.CommandText = "Insert into [Sheet1$] " & _
            "(FirstName, LastName, Age, Phone) values " & _
            "(@FName, @LName, @Age, @Phone)"
        olecomm1.Connection = olecon
        pram = olecomm1.Parameters.Add("@FName", OleDbType.VarChar)
        pram.SourceColumn = "FirstName"
        pram = olecomm1.Parameters.Add("@LName", OleDbType.VarChar)
        pram.SourceColumn = "LastName"
        pram = olecomm1.Parameters.Add("@Age", OleDbType.VarChar)
        pram.SourceColumn = "Age"
        pram = olecomm1.Parameters.Add("@Phone", OleDbType.VarChar)
        pram.SourceColumn = "Phone"
        oleadpt = New OleDbDataAdapter(olecomm)
        ds = New DataSet
        olecon.Open()
        oleadpt.Fill(ds, "Sheet1")
        If IsNothing(ds) = False Then
            dr = ds.Tables(0).NewRow
            dr("FirstName") = "Raman"
            dr("LastName") = "Tayal"
            dr("Age") = 24
            dr("Phone") = 98989898
            ds.Tables(0).Rows.Add(dr)
            oleadpt = New OleDbDataAdapter
            oleadpt.InsertCommand = olecomm1
            Dim i As Integer = oleadpt.Update(ds, "Sheet1")
            MessageBox.Show(i & " row affected")
        End If
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    Finally
        olecon.Close()
        olecon = Nothing
        olecomm = Nothing
        oleadpt = Nothing
        ds = Nothing
        dr = Nothing
        pram = Nothing
    End Try
End Sub

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionMicrosoft.ACE.OLEDB.12.0 Pin
Member 1433303625-Apr-19 20:38
Member 1433303625-Apr-19 20:38 
Questionempty rows cells after importing data from excel 2000 Pin
Makumbisula16-Feb-15 20:40
Makumbisula16-Feb-15 20:40 
Questionhow to jump on 1st cell to 10th cell Pin
Member 1111752829-Sep-14 2:08
Member 1111752829-Sep-14 2:08 
QuestionThanks for detailed Pin
Mike Earl17-Jan-14 5:08
Mike Earl17-Jan-14 5:08 
Questionwrite data from one source excel file to destination excel file Pin
sanjivkp774-Dec-13 5:51
sanjivkp774-Dec-13 5:51 
GeneralThanks a Lot brother Pin
ravisankarvattikuti14-Mar-10 6:47
ravisankarvattikuti14-Mar-10 6:47 
GeneralRe: Thanks a Lot brother Pin
Raman Tayal15-Mar-10 7:54
Raman Tayal15-Mar-10 7:54 
Generalthanks Pin
bhavin123014-Sep-09 19:13
bhavin123014-Sep-09 19:13 
GeneralPlease clarify my question( urgent!!!!!!!!!!!!!!!!!!!!!!!!!!!!! ) Pin
catchthedream31-Aug-09 2:43
catchthedream31-Aug-09 2:43 
QuestionPK??? Pin
Chris Maunderr22-Jul-09 23:10
Chris Maunderr22-Jul-09 23:10 
AnswerRe: PK??? Pin
Raman Tayal23-Jul-09 8:18
Raman Tayal23-Jul-09 8:18 
Questionmaking dictionary Pin
thaileang25-Jun-09 9:31
thaileang25-Jun-09 9:31 
QuestionExcel Upload Pin
WendellB4-May-09 9:47
WendellB4-May-09 9:47 
GeneralUpdate a shared excell file [modified] Pin
facu20yo19-Apr-09 4:57
facu20yo19-Apr-09 4:57 
Questionneed help...urgent! Pin
Member 430050816-Mar-09 19:11
Member 430050816-Mar-09 19:11 
AnswerRe: need help...urgent! Pin
Raman Tayal17-Mar-09 21:28
Raman Tayal17-Mar-09 21:28 
GeneralRe: need help...urgent! Pin
Member 430050820-Mar-09 20:13
Member 430050820-Mar-09 20:13 
Generalhelp Pin
pjacquez6816-Feb-09 8:06
pjacquez6816-Feb-09 8:06 
GeneralRe: help Pin
Raman Tayal16-Feb-09 21:50
Raman Tayal16-Feb-09 21:50 
Generali need your help bro, Pin
pjacquez6816-Feb-09 7:56
pjacquez6816-Feb-09 7:56 
Generaldelete Pin
pjacquez6816-Feb-09 5:15
pjacquez6816-Feb-09 5:15 
GeneralFrom Jacquez Pin
pjacquez6816-Feb-09 4:18
pjacquez6816-Feb-09 4:18 
GeneralGreat more code being posted THAT DOESN'T WORK OUT OF THE BOX. Pin
Member 389560127-Jan-09 15:00
Member 389560127-Jan-09 15:00 
GeneralRe: Great more code being posted THAT DOESN'T WORK OUT OF THE BOX. Pin
Raman Tayal28-Jan-09 6:04
Raman Tayal28-Jan-09 6:04 
GeneralRe: Great more code being posted THAT DOESN'T WORK OUT OF THE BOX. Pin
Member 389560128-Jan-09 6:28
Member 389560128-Jan-09 6:28 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.