+92 332 4229 857 99ProjectIdeas@Gmail.com

How to create table in excel (Vb.net)


Table creation in excel

System.Data.OleDb is the namespace which contains all the functions required to interact with an excel file.
OleDbConnection is a class used to create and open a connection with an excel file.
OleDbCommand is a class used to take query as an input and for the execution of command(Query).
ExecuteNonQuery() is a function which is used to execute the command(Query).
Before the execution of ExecuteNonQuery(), connection should be open by using:
Conn.Open()
And after the execution of ExecuteNonQuery(), connection should be closed by using:
Conn.Close()
filePath is the location of the file in your computer.

Code:

1.     Sub createTable()
2.     Dim Conn As New System.Data.OleDb.OleDbConnection
3.     Dim Comm As New System.Data.OleDb.OleDbCommand
4.     Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
5.     " '" & filePath & "';Extended Properties=Excel 8.0;"
6.     Comm.Connection = Conn
7.     Conn.Open()
8.     Comm.CommandText = "CREATE TABLE BasicInfo(Firstname varchar(255),Lastname varchar(255),Age double)"
9.     Comm.ExecuteNonQuery()
10.  Conn.Close()
11.  End Sub

Or we can have a function which takes the path of the excel file as an input parameter and returns a connection string.

Public Function CreateConnString(ByVal _filePath As String) As String
        Return "provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
            " '" & _filePath & "';Extended Properties=Excel 8.0;"
End Function

In this case line number 4 will be replaced as depicted below:
Conn.ConnectionString = CreateConnString(filePath)


0 comments: