Reading and writing an Excel file using VB.NET

• Download source code - 10.6 KB

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:

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:

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