Thursday, 12 June 2014

Excel Insert, Update, Delete by .net

--------aspx-----------
 <asp:Button ID="create" runat="server" Text="create" /><br />
        <asp:Button ID="insert" runat="server" Text="insert" /><br />
        <asp:Button ID="select1" runat="server" Text="select" /><br />
        <asp:Button ID="update" runat="server" Text="update" /><br />
----------------------cs---------------
Imports System.Data.OleDb
Partial Class excel_update
    Inherits System.Web.UI.Page
    'Private m_sConn1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\ExcelData2.xls;Extended Properties=Excel 8.0"
    'Private m_sConn2 As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\ExcelData2.xls;Extended Properties=Excel 8.0"
    'Private m_sAction As String

    Protected Sub form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles form1.Load

     
    End Sub
    Private Function CreateConnString(ByVal Str As String) _
        As String

        Return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
        + Str + ";Extended Properties=""Excel 8.0;HDR=YES;"""


    End Function
     ';;;;;;;;;;;;
    Protected Sub create_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles create.Click
        Dim Conn As New OleDbConnection
        Dim Comm As New OleDbCommand

        Conn.ConnectionString = CreateConnString("D:\RND\test\excel\Book1.xls")
        Comm.Connection = Conn
        Comm.CommandText = _
         "Create Table MySheet (F1 char(255), F2 char(255))"

        Try
            Conn.Open()
            Comm.ExecuteNonQuery()
            MsgBox("Created.")
            Conn.Close()
        Catch ex As Exception
            MsgBox("Error.")
            Conn.Close()
        End Try
    End Sub

    Protected Sub insert_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles insert.Click
        Dim Conn As New OleDbConnection
        Dim Comm As New OleDbCommand

        Conn.ConnectionString = CreateConnString("D:\RND\test\excel\Book1.xls")
        Comm.Connection = Conn
        Comm.CommandText = _
         "Insert INTO [MySheet$] (F1, F2) Values ('1','2')"

        Try
            Conn.Open()
            Comm.ExecuteNonQuery()
            MsgBox("Inserted.")
            Conn.Close()
        Catch ex As Exception
            MsgBox("Error.")
            Conn.Close()
        End Try
    End Sub

    Protected Sub select1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles select1.Click
        Dim Conn As New OleDbConnection
        Dim Comm As New OleDbCommand
        Dim DReader As OleDbDataReader
        Dim Str As String = ""

        Conn.ConnectionString = CreateConnString("D:\RND\test\excel\Book1.xls")
        Comm.Connection = Conn
        Comm.CommandText = "Select [F1], [F2] From [MySheet$]"

        Try
            Conn.Open()
            DReader = Comm.ExecuteReader

            Do While DReader.Read
                Str = Str + vbCrLf + DReader.GetString(0) _
                + " , " + DReader.GetString(1)
            Loop

            MsgBox(Str)
            DReader.Close()
            Conn.Close()
        Catch ex As Exception
            MsgBox("Error.")
            Conn.Close()
        End Try

    End Sub

    Protected Sub update_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles update.Click

        Dim Conn As New OleDbConnection
        Dim Comm As New OleDbCommand

        Conn.ConnectionString = CreateConnString("D:\RND\test\excel\Book1.xls")
        Comm.Connection = Conn
        Comm.CommandText = _
         "Update [MySheet$] SET F1 = 'pramod' Where F2 = '2'"

        Try
            Conn.Open()
            Comm.ExecuteNonQuery()
            MsgBox("Updated.")
            Conn.Close()
        Catch ex As Exception
            MsgBox("Error.")
            Conn.Close()
        End Try
    End Sub
End Class

No comments:

Post a Comment