How to read a data from excel using vb.net

[Nebil]

Hi guys,
I wanted to do a simple calculation using vb.net.That is i would have some values that are used as an input read from excel in the calculation.the values may be changed or used as they are.
In the vb side i would have some textboxes that garb the values and display them.
Not to make things complicated how do u read values from excel?

[ddanbe]

Click Here to learn how to open and read an excel file.

[Reverend Jim]

This has been asked and answered multiple times. Search this forum as well as the code snippets.

[GeekPlease]

You can look at this

[Reverend Jim]

I could have pointed the OP to the same article but to tell the truth, I'm getting tired of doing the legwork for people who are too damned lazy to even bother looking to see if that question has already been asked and answered.

[Nebil]

I came up with this code but what it does is read a single excel file and i wanted to read various sheets and import them to the database which i have already prepared.Here's the code.

Function ReadExcelFile()
        'Use OleDbDataAdapter  to provide communication between the DataTable and the OleDb Data Sources
        Dim da As New OleDbDataAdapter

        'Use DataTable as storage of data from excel
        Dim dt As New DataTable

        'Use OleDbCommand to execute our SQL statement
        Dim cmd As New OleDbCommand

        'Use OleDbConnection that will be used by OleDbCommand to connect to excel file
        Dim xlsConn As OleDbConnection

        Dim sPath As String = String.Empty

        sPath = Form1.lbFilePath.Text

        'Create a new instance of connection and set the datasource value to excel's path
        xlsConn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sPath & ";Extended Properties=Excel 12.0")

        'Use try catch block to handle some or all possible errors that may occur in a 
        'given block of code, while still running code.

        'Try
        'Open the connection
        xlsConn.Open()

        'Set the command connection to opened connection
        cmd.Connection = xlsConn

        'Set the command type to CommandType.Text in order to use SQL statment constructed here 
        'in code editor
        cmd.CommandType = CommandType.Text

        'Assigned the command text to query the excel as shown below
        cmd.CommandText = ("SELECT * FROM [sheet1$]")

        'Assign the cmd to dataadapter
        da.SelectCommand = cmd

        'Fill the datatable with data from excel file using DataAdapter
        da.Fill(dt)

        ' Catch
        'This block Handle the exception.
        'MsgBox(ErrorToString)
        'Finally
        'We need to close the connection and set to nothing. This code will still execute even the code raised an error
        xlsConn.Close()
        xlsConn = Nothing
        'End Try
        Return dt
    End Function
    Public Sub SaveToDB(ByVal iRowIndex As Long)
        Dim conn As New OleDbConnection
        Dim sConnString As String
        Dim cmd As New OleDbCommand
        Dim sSQL As String = String.Empty

        'Try
        'Check if the path has a backslash in the end of string
        If Microsoft.VisualBasic.Right(Application.StartupPath, 1) = "\" Then
            sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "dbexport.accdb;Persist Security Info=False;"
        Else
            sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\dbexport.accdb;Persist Security Info=False;"
        End If

        'create a new instance of connection
        conn = New OleDbConnection(sConnString)

        'open the connection to be used by command object
        conn.Open()

        'Set the command's connection to our opened connection
        cmd.Connection = conn

        'Set the command type to CommandType.Text in order to use SQL statment constructed here 
        'in code editor
        cmd.CommandType = CommandType.Text

        'Set the comment text to insert the data to database
        cmd.CommandText = "INSERT INTO eqtrate ( item_no, equipment_type, hourly_cost )  VALUES(@item_no, @equipment_type, @hourly_cost)"

        'Add parameters in order to set the values in the query
        cmd.Parameters.Add("@item_no", OleDbType.Numeric).Value = dtExcelData.Rows(iRowIndex)(0)
        cmd.Parameters.Add("@equipment_type", OleDbType.VarChar).Value = dtExcelData.Rows(iRowIndex)(1)
        'This is just a sample of how to check if the field is null.
        cmd.Parameters.Add("@hourly_cost", OleDbType.Numeric).Value = IIf(Not IsDBNull(dtExcelData.Rows(iRowIndex)(2)), dtExcelData.Rows(iRowIndex)(2), Nothing)
        cmd.ExecuteNonQuery()


        'Catch ex As Exception
        MsgBox(ErrorToString)
        'Finally
        conn.Close()
        'End Try
    End Sub
    Public Sub Load_Data()
        Dim conn As New OleDbConnection
        Dim sConnString As String
        Dim cmd As New OleDbCommand
        Dim da As New OleDbDataAdapter

        ' Try
        'create a new instance of dteqtrate, this datatable will be used to export 
        'data from database to excel
        dteqtrate = New DataTable

        'Check if the path has a backslash in the end of string
        If Microsoft.VisualBasic.Right(Application.StartupPath, 1) = "\" Then
            sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "dbexport.accdb;Persist Security Info=False;"
        Else
            sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\dbexport.accdb;Persist Security Info=False;"
        End If

        'create a new instance of connection
        conn = New OleDbConnection(sConnString)

        'open the connection to be used by command object
        conn.Open()

        'Set the command's connection to our opened connection
        cmd.Connection = conn

        'Set the command type to CommandType.Text in order to use SQL statment constructed here 
        'in code editor         
        cmd.CommandType = CommandType.Text

        'Set the command text to load the data from database
        cmd.CommandText = "select * from eqtrate"

        'assign the cmd object to dataadapter selectcommand property in order to use it to fill the dteqtrate
        da.SelectCommand = cmd

        'Populate the dteqtrate with the data from database
        da.Fill(dteqtrate)

        'Set the datagridview datasource to dteqtrate in order to dispaly the data to user
        dtgResult.DataSource = dteqtrate

        'Catch ex As Exception
        MsgBox(ErrorToString)
        ' Finally
        conn.Close()
        'End Try
    End Sub
    Public Sub ImportToDB(ByVal a As String)
        Dim im As String
        im = a
        'Create a new instance of dtExcelData datatable
        dtExcelData = New DataTable

        'Set the cursor to wait cursor
        Windows.Forms.Cursor.Current = Cursors.WaitCursor

        'Call ReadExcelFile function to get the data from excel
        dtExcelData = ReadExcelFile(im)

        'Reset the progressbar
        Form1.ProgressBar1.Value = 0

        'Get the DataTable row count to set the progressbar maximum value
        Form1.ProgressBar1.Maximum = dtExcelData.Rows.Count
        Form1.ProgressBar1.Visible = True

        'Use looping to read the value of field in each row in DataTable
        For i = 0 To dtExcelData.Rows.Count - 1
            'Check if the item number has a value
            If Not IsDBNull(dtExcelData.Rows(i)(0)) Then

                'call save procedure and pass the row varialble i(row index) as parameter to save each the value of each field
                SaveToDB(i)

                'Increase the value of progressbar to inform the user.
                Form1.ProgressBar1.Value = Form1.ProgressBar1.Value + 1

            End If
        Next
        dtExcelData = Nothing

        'Call the Load_Data procedure that will load the data and display it to DataGrid
        Load_Data()

        'Inform the user that the importing of data has been finished
        MsgBox("Data has successfully imported.", MsgBoxStyle.OkOnly, "Import Rate Analysis")
    End Sub

[M.Waqas Aslam]

use this connection string to get your application connected with excel file

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;
Extended Properties="Excel 12.0 Xml;HDR=YES";

and then just use query like this

select * from [$sheet1]

Regards

[Nebil]

the connection works fine but i can't figure out to use the SaveTodB function for more than 1 excel sheet.It works only for 1 excel sheet.Can you give me a clue

[Nebil]

I've come up with this code as a function.

 Public Sub ImportFromExcel(ByVal c As String, ByVal e As String)
        eConnString = ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + e + ";Extended Properties=Excel 12.0")
        Using xlsConn As New OleDbConnection(eConnString)
            Using cmd As New OleDbCommand
                cmd.Connection = xlsConn
                cmd.CommandText = c
                xlsConn.Open()
                dr = cmd.ExecuteReader
            End Using
        End Using
    End Sub

Then under a button i put this code when a user clicks the import button.

If radmaterial.Checked = True Then
            command = ("INSERT INTO [MS Access;Database=" & dConnString & "].[mtrcost] SELECT * FROM [Material$]")
            ImportFromExcel(command, Excel)
        End If

Excel is the path of the excel file