none
get column data froom excel spreadsheet to specific column in datagrid using vb.net

    Question

  • hi. how can i display the data that is in column of excel .. like i have 4 columns in my excel sheet named a,b,c,d and 3 columns in my datagrid named standard,demerit and details ... i'd like only to get the data in column b to display it in standard column of my datagridview. sorry i dont have any codes, im a newbie.. in additional i dont have any database used .. vb.net and excel as my database ...i have a screenshot of my datagridview sample.. any help would be appreciated... thanks :))
    • Edited by chaapple Wednesday, August 22, 2012 8:16 AM
    Wednesday, August 22, 2012 8:09 AM

Answers

  • Using OleDb has advantages and disadvantages. Disadvantage is you must have a proper connection string or else the operation to get data will throw an exception. Disadvantage for Office automation is does the client have Excel installed or they have it installed but not the same version as you. If OleDb throws an exception memory used is displosed of, with office automation any objects used will be left in memory. If you want column B via OleDb and there has never been data in column A then asking F2 (if there are no column headers Excel elects column names for you) will throw an exception as would you think is F2 (column B) will be F1. Going back to Office automation, you might need to use late binding rather than early binding, more issues to deal with.

    Example 1 we hand code the connection string which a) leaves room for error if the connection string is not properly formatted. We could simple use a DataTable to store data or perhaps a DataReader and cycle thru the data to populat the DataGridView, I elected to use a DataTable which becomes the data source for a BindingSource which provides additional functionality over just a DataTable.

    VS2010 code

    Public Class Tasking
        WithEvents bsExcelData As New BindingSource
        ' Could be obtained via a OpenDialog
        Private FileName As String = "KSG.xlsx"
        Private Sub Tasking_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            DataGridView1.AllowUserToAddRows = False
            ' If the first row in the sheet is not data but column names change HDR=Yes
            Using cn As New OleDb.OleDbConnection With
                {
                    .ConnectionString =
                    <T>
                    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=<%= FileName %>;
                    Extended Properties="Excel 12.0;IMEX=1;HDR=No;"
                    </T>.Value
                }
                Using cmd As New OleDb.OleDbCommand With {.Connection = cn, .CommandText = "SELECT F2 FROM [Sheet1$]"}
                    Dim dt As New DataTable
                    ' Created column just for reference
                    dt.Columns.Add(New DataColumn With {.ColumnName = "F1", .DataType = GetType(System.Int32)})
                    dt.Columns.Item("F1").AutoIncrement = True
                    dt.Columns.Item("F1").AutoIncrementSeed = 1
                    cn.Open()
                    dt.Load(cmd.ExecuteReader)
                    bsExcelData.DataSource = dt
                    DataGridView1.DataSource = bsExcelData
                End Using
            End Using
        End Sub
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            If bsExcelData.Current IsNot Nothing Then
                Dim CurrentRow = CType(bsExcelData.Current, DataRowView)
                MessageBox.Show(String.Format("[{0}] [{1}]", CurrentRow.Item("F1").ToString, CurrentRow.Item("F2").ToString))
            End If
        End Sub
        Private Sub bsExcelData_PositionChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles bsExcelData.PositionChanged
            If bsExcelData.Current IsNot Nothing Then
                ' Writes to the IDE output window for win-form projects.
                Console.WriteLine(CType(bsExcelData.Current, DataRowView).Item("F2"))
            End If
        End Sub
    End Class

    To safely create the connection string you can use a language extension would is very reusable from project to project, mine resides in a common DLL.

    VS2010 form code (see code block below this one for language extension)

    Public Class Tasking
        WithEvents bsExcelData As New BindingSource
        ' Could be obtained via a OpenDialog
        Private FileName As String = "KSG.xlsx"
        Private Sub Tasking_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            DataGridView1.AllowUserToAddRows = False
            ' If the first row in the sheet is not data but column names change HDR=Yes
            Using cn As New OleDb.OleDbConnection
                cn.SetExcelConnectionString(FileName, UseHeader.No, ExcelImex.Resolve)
                Console.WriteLine(cn.ConnectionString)
                Using cmd As New OleDb.OleDbCommand With {.Connection = cn, .CommandText = "SELECT F2 FROM [Sheet1$]"}
                    Dim dt As New DataTable
                    ' Created column just for reference
                    dt.Columns.Add(New DataColumn With {.ColumnName = "F1", .DataType = GetType(System.Int32)})
                    dt.Columns.Item("F1").AutoIncrement = True
                    dt.Columns.Item("F1").AutoIncrementSeed = 1
                    cn.Open()
                    dt.Load(cmd.ExecuteReader)
                    bsExcelData.DataSource = dt
                    DataGridView1.DataSource = bsExcelData
                End Using
            End Using
        End Sub
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            If bsExcelData.Current IsNot Nothing Then
                Dim CurrentRow = CType(bsExcelData.Current, DataRowView)
                MessageBox.Show(String.Format("[{0}] [{1}]", CurrentRow.Item("F1").ToString, CurrentRow.Item("F2").ToString))
            End If
        End Sub
        Private Sub bsExcelData_PositionChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles bsExcelData.PositionChanged
            If bsExcelData.Current IsNot Nothing Then
                ' Writes to the IDE output window for win-form projects.
                Console.WriteLine(CType(bsExcelData.Current, DataRowView).Item("F2"))
            End If
        End Sub
    End Class

    Place the following code into a code module and not a form

    Public Enum UseHeader
        ''' <summary>
        ''' Indicates that the first row contains columnnames, no data
        ''' </summary>
        ''' <remarks></remarks>
        Yes
        ''' <summary>
        ''' Indicates that the first row does not contain columnnames
        ''' </summary>
        ''' <remarks></remarks>
        No
    End Enum
    Public Enum ExcelImex
        TryScan = 0
        Resolve = 1
    End Enum
    ''' <summary>
    ''' Used to make connection to Excel easy
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="FileName"></param>
    ''' <param name="Header"></param>
    ''' <param name="IMEX"></param>
    ''' <remarks>
    ''' </remarks>
    <System.Diagnostics.DebuggerStepThrough()> _
    <System.Runtime.CompilerServices.Extension()> _
    Public Sub SetExcelConnectionString(ByRef sender As OleDbConnection, ByVal FileName As String, ByVal Header As UseHeader, ByVal IMEX As ExcelImex)
        Dim Mode As String = CInt(IMEX).ToString
        Dim Builder As New OleDbConnectionStringBuilder With {.DataSource = FileName}
        If IO.Path.GetExtension(FileName).ToUpper = ".XLSX" Then
            Builder.Provider = "Microsoft.ACE.OLEDB.12.0"
            Builder.Add("Extended Properties", "Excel 12.0;IMEX=" & Mode & ";HDR=" & Header.ToString & ";")
        Else
            Builder.Provider = "Microsoft.Jet.OLEDB.4.0"
            Builder.Add("Extended Properties", "Excel 8.0;IMEX=" & Mode & ";HDR=" & Header.ToString & ";")
        End If
        sender.ConnectionString = Builder.ConnectionString
    End Sub
    Note the extension figures out the provider based on the file extension i.e. xls or xlsx.


    KSG

    Wednesday, August 22, 2012 8:38 PM
    Moderator
  • Hi sigrid012,

    Welcome to the MSDN forum.

    >>my problem in this picture how can i make the data add to the column of the Standard in my datagrid

    It seems that you haven’t bound the datagrid with datatabel. So you can just add the value like following code:

    Me.dataGridView1.Rows[1].Cells[0].Value = "new value"

    For more information, please check DataGridView.Rows Property: http://msdn.microsoft.com/en-us/library/system.windows.forms.datagridview.rows.aspx

     If you have bound the datagridview with datatable, please edit the data in datatable, for more information, please check DataTable.Rows Property: http://msdn.microsoft.com/en-us/library/system.data.datatable.rows.aspx

    Hope this helps.


    Mark Liu-lxf [MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, August 28, 2012 8:10 AM
    Moderator

All replies

  • You sure picked a challenging assignment for your first project. (What ever happened to the days of "Hello World"?) Anyway, starting with the Excel portion, first you need to add the DLL (executable) to the project. In order to keep the size of projects from getting too big, Microsoft picks the most common DLL's and automatically adds them to your project. Normally, Visual Basic Automation ("VBA") -- also referred to as Visual Basic for Applications is used with Office programs. With VBA (similar but different than the Visual Basic you find in Visual Studio) you create Macros that run within a Spreadsheet or a Word Document. Tying together the two different worlds is considered "Interoperability" so to start off, double click 'my project' in the Solution Explorer of Visual Studio. Click the "Reference" tab on the left. Click the "Add" button to add an additional reference to your project (this would be the DLL that supports VBA for Excel). Depending on the version of VS you are running, the name might vary. But basically add to the top section something like "Microsoft Office Excel Interop." (I'm doing this from memory so you might have to do a little searching). In you code, at the very top you would add an "Imports" statement which tells the compiler to let you choose functions and subroutines from the readable source code of the DLL you just added. So add a statement something like "Imports Excel = Microsoft.Office.Interop.Excel" (no quotes). This brings the readable source code (actually the "Namespace") into your project. Inside the Class that goes with your form, start by declaring some variables:

    Dim strSpreadsheetName As String

    Dim xlApp As Excel.Application

    Dim xlWorkBook As Excel.Workbook

    Dim xlWorkSheet As Excel.Worksheet

    Go to your form and drag an OpenFileDialog box onto your form from the Toolbox. It will default to the name OpenFileDialog1. Put a button on your form that the user will click to open the spreadsheet. Call it btnOpenFile and the Text would be "Open Spreadsheet" (Properties) and then double click it. It will take you back to your code page and add some code. Add some more to end up with something that looks like this:

    Public Sub btnOpenFile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOpenFile.Click

            OpenFileDialog1.FileName = ""

            If OpenFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then

                Try

                    xlApp = New Excel.Application

                    xlWorkBook = xlApp.Workbooks.Open(OpenFileDialog1.FileName)

                    strSpreadsheetName = OpenFileDialog1.FileName

     

                Catch ex As Exception

                    MsgBox("Problem:  Could not open spreadsheet " & strSpreadsheetName)

                    MsgBox(ex.ToString)

                    Return

                End Try

    Next you need to find out how many rows exist in the spreadsheet. For that you could use a do while loop (with programming there are many different ways of doing things).

    Dim intRowCounter as Integer

    Dim intTotalRows as Integer
    Dim boolFinished as Boolean

    intRowCounter = 1

    boolFinished = False

            Do While boolFinished = False

                If xlWorkSheet.Cells(intRowCounter + 1, 2).value IsNot Nothing Then

                    intRowCounter = intRowCounter + 1

                    intTotalRows = intRowCounter

                Else : boolFinished = True

                End If

            Loop 

    IntRowCounter will now give you the number of good rows. The reason for this is if you try to read a value when there is nothing there, your program will throw an exception ("crash"). Next, you can either read each value and store them in an array, then put all of them at once into your datagridview, or you can do this one row at a time copying one value and moving it. To read the value and save it as a string, try this:

    Dim strStuff as String

    strStuff = xlWorkSheet.Cells(intRowCounter, 2).value.ToString

    By the way, the "2" comes from the second column (which would be "b"). Since you already checked and determined that there are legitimate values in the column for all of the rows from the previous step, you should be safe in just reading the values. (The following is the safer way to get values):

    If xlWorkSheet.Cells(intRowCounter, 2).value IsNot Nothing Then

            strStuff = xlWorkSheet.Cells(intRowCounter, 2).value.ToString
    End If


    Still with me? I did warn you that you are going pretty deep for not having written much code before... On the database side, there are several approaches you can take. First, do you really want a database? Or do you just want to display data in your datagridview? If you want a full database, then first decide on which database. SQL is Microsoft's preferred database, and the one that will most likely be supported the longest in the future. On the other hand, the Jet Database (engine that works with Microsoft Access) has advantages when you are first learning programming: Just one file to copy to somewhere else for backups and no service to stop and restart. Probably as quick as SQL when you only have one client to support. And easier to code. Anyway, if you are with me so far, I'll write you part two probably tomorrow and go over getting data into a database and / or into a DataGridView.  ~Cheers...


    • Edited by Dick Perin Wednesday, August 22, 2012 6:41 PM
    Wednesday, August 22, 2012 6:40 PM
  • Using OleDb has advantages and disadvantages. Disadvantage is you must have a proper connection string or else the operation to get data will throw an exception. Disadvantage for Office automation is does the client have Excel installed or they have it installed but not the same version as you. If OleDb throws an exception memory used is displosed of, with office automation any objects used will be left in memory. If you want column B via OleDb and there has never been data in column A then asking F2 (if there are no column headers Excel elects column names for you) will throw an exception as would you think is F2 (column B) will be F1. Going back to Office automation, you might need to use late binding rather than early binding, more issues to deal with.

    Example 1 we hand code the connection string which a) leaves room for error if the connection string is not properly formatted. We could simple use a DataTable to store data or perhaps a DataReader and cycle thru the data to populat the DataGridView, I elected to use a DataTable which becomes the data source for a BindingSource which provides additional functionality over just a DataTable.

    VS2010 code

    Public Class Tasking
        WithEvents bsExcelData As New BindingSource
        ' Could be obtained via a OpenDialog
        Private FileName As String = "KSG.xlsx"
        Private Sub Tasking_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            DataGridView1.AllowUserToAddRows = False
            ' If the first row in the sheet is not data but column names change HDR=Yes
            Using cn As New OleDb.OleDbConnection With
                {
                    .ConnectionString =
                    <T>
                    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=<%= FileName %>;
                    Extended Properties="Excel 12.0;IMEX=1;HDR=No;"
                    </T>.Value
                }
                Using cmd As New OleDb.OleDbCommand With {.Connection = cn, .CommandText = "SELECT F2 FROM [Sheet1$]"}
                    Dim dt As New DataTable
                    ' Created column just for reference
                    dt.Columns.Add(New DataColumn With {.ColumnName = "F1", .DataType = GetType(System.Int32)})
                    dt.Columns.Item("F1").AutoIncrement = True
                    dt.Columns.Item("F1").AutoIncrementSeed = 1
                    cn.Open()
                    dt.Load(cmd.ExecuteReader)
                    bsExcelData.DataSource = dt
                    DataGridView1.DataSource = bsExcelData
                End Using
            End Using
        End Sub
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            If bsExcelData.Current IsNot Nothing Then
                Dim CurrentRow = CType(bsExcelData.Current, DataRowView)
                MessageBox.Show(String.Format("[{0}] [{1}]", CurrentRow.Item("F1").ToString, CurrentRow.Item("F2").ToString))
            End If
        End Sub
        Private Sub bsExcelData_PositionChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles bsExcelData.PositionChanged
            If bsExcelData.Current IsNot Nothing Then
                ' Writes to the IDE output window for win-form projects.
                Console.WriteLine(CType(bsExcelData.Current, DataRowView).Item("F2"))
            End If
        End Sub
    End Class

    To safely create the connection string you can use a language extension would is very reusable from project to project, mine resides in a common DLL.

    VS2010 form code (see code block below this one for language extension)

    Public Class Tasking
        WithEvents bsExcelData As New BindingSource
        ' Could be obtained via a OpenDialog
        Private FileName As String = "KSG.xlsx"
        Private Sub Tasking_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            DataGridView1.AllowUserToAddRows = False
            ' If the first row in the sheet is not data but column names change HDR=Yes
            Using cn As New OleDb.OleDbConnection
                cn.SetExcelConnectionString(FileName, UseHeader.No, ExcelImex.Resolve)
                Console.WriteLine(cn.ConnectionString)
                Using cmd As New OleDb.OleDbCommand With {.Connection = cn, .CommandText = "SELECT F2 FROM [Sheet1$]"}
                    Dim dt As New DataTable
                    ' Created column just for reference
                    dt.Columns.Add(New DataColumn With {.ColumnName = "F1", .DataType = GetType(System.Int32)})
                    dt.Columns.Item("F1").AutoIncrement = True
                    dt.Columns.Item("F1").AutoIncrementSeed = 1
                    cn.Open()
                    dt.Load(cmd.ExecuteReader)
                    bsExcelData.DataSource = dt
                    DataGridView1.DataSource = bsExcelData
                End Using
            End Using
        End Sub
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            If bsExcelData.Current IsNot Nothing Then
                Dim CurrentRow = CType(bsExcelData.Current, DataRowView)
                MessageBox.Show(String.Format("[{0}] [{1}]", CurrentRow.Item("F1").ToString, CurrentRow.Item("F2").ToString))
            End If
        End Sub
        Private Sub bsExcelData_PositionChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles bsExcelData.PositionChanged
            If bsExcelData.Current IsNot Nothing Then
                ' Writes to the IDE output window for win-form projects.
                Console.WriteLine(CType(bsExcelData.Current, DataRowView).Item("F2"))
            End If
        End Sub
    End Class

    Place the following code into a code module and not a form

    Public Enum UseHeader
        ''' <summary>
        ''' Indicates that the first row contains columnnames, no data
        ''' </summary>
        ''' <remarks></remarks>
        Yes
        ''' <summary>
        ''' Indicates that the first row does not contain columnnames
        ''' </summary>
        ''' <remarks></remarks>
        No
    End Enum
    Public Enum ExcelImex
        TryScan = 0
        Resolve = 1
    End Enum
    ''' <summary>
    ''' Used to make connection to Excel easy
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="FileName"></param>
    ''' <param name="Header"></param>
    ''' <param name="IMEX"></param>
    ''' <remarks>
    ''' </remarks>
    <System.Diagnostics.DebuggerStepThrough()> _
    <System.Runtime.CompilerServices.Extension()> _
    Public Sub SetExcelConnectionString(ByRef sender As OleDbConnection, ByVal FileName As String, ByVal Header As UseHeader, ByVal IMEX As ExcelImex)
        Dim Mode As String = CInt(IMEX).ToString
        Dim Builder As New OleDbConnectionStringBuilder With {.DataSource = FileName}
        If IO.Path.GetExtension(FileName).ToUpper = ".XLSX" Then
            Builder.Provider = "Microsoft.ACE.OLEDB.12.0"
            Builder.Add("Extended Properties", "Excel 12.0;IMEX=" & Mode & ";HDR=" & Header.ToString & ";")
        Else
            Builder.Provider = "Microsoft.Jet.OLEDB.4.0"
            Builder.Add("Extended Properties", "Excel 8.0;IMEX=" & Mode & ";HDR=" & Header.ToString & ";")
        End If
        sender.ConnectionString = Builder.ConnectionString
    End Sub
    Note the extension figures out the provider based on the file extension i.e. xls or xlsx.


    KSG

    Wednesday, August 22, 2012 8:38 PM
    Moderator
  • thanks for the reply, sorry i forgot to say that i want to display the column in the excel to the column of datagrid automatically.. in the picture  below, in excel i have 3 columns same as in the datagrid ... before going to that form , i have also a datagrid that displays the information of the employee, when the examiner double click the row of the datagrid the details of employee will display to the textbox to this form and also it will automatically display the standard column of excel to standard column of datagrid.. thanks again :))

    Thursday, August 23, 2012 2:02 AM
  • thanks for the reply, sorry i forgot to say that i want to display the column in the excel to the column of datagrid automatically.. in the picture  below, in excel i have 3 columns same as in the datagrid ... before going to that form , i have also a datagrid that displays the information of the employee, when the examiner double click the row of the datagrid the details of employee will display to the textbox to this form and also it will automatically display the standard column of excel to standard column of datagrid.. thanks again :))

    In short using OleDb as I have described you would have the DataGridView column already set up as you have now and set the DataProperty for that DataGridView column to the field from the DataTable as shown in my example (meaning after we load the DataTable).

    For populating the textboxes simple add a DataBinding to the textbox i.e. TextBox.DataBindings.Add(...


    KSG

    Thursday, August 23, 2012 2:29 AM
    Moderator
  • Sir,

    this what happen...

    my problem in this picture how can i make the data add to the column of the Standard in my datagrid ... i mean F1 and F2 add in the datagrid ..

    thanks  

    Thursday, August 23, 2012 3:37 AM
  • Hi sigrid012,

    Welcome to the MSDN forum.

    >>my problem in this picture how can i make the data add to the column of the Standard in my datagrid

    It seems that you haven’t bound the datagrid with datatabel. So you can just add the value like following code:

    Me.dataGridView1.Rows[1].Cells[0].Value = "new value"

    For more information, please check DataGridView.Rows Property: http://msdn.microsoft.com/en-us/library/system.windows.forms.datagridview.rows.aspx

     If you have bound the datagridview with datatable, please edit the data in datatable, for more information, please check DataTable.Rows Property: http://msdn.microsoft.com/en-us/library/system.data.datatable.rows.aspx

    Hope this helps.


    Mark Liu-lxf [MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, August 28, 2012 8:10 AM
    Moderator
  • Hi sigrid012,

    We haven’t heard from you for several days. I’d like to mark the helpful replies as answer firstly. If you have any additional questions, you also can unmark the replay and post your question here. 

    Sorry for any inconvenience and have a nice day.


    Mark Liu-lxf [MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, September 04, 2012 7:25 AM
    Moderator
  • Hello thank you  a lot for your help, actually I'm working with vb.net  and sql server2008 can you plz show us how a sample of code that  stores specific columns from an excel file into an excisting database ?and is there any other way to do it (stored procedure for example) 

    thanks in advance!

    Sunday, September 16, 2012 11:50 PM