Appending Excel file to datagridview

I'm attempting to add the data from an excel file (that is formated the same way as the datagridview) into a datagridview. The probelm I'm getting is the xls data is being appended to the existing datagridview. the current datagridview remain and starting at the first empty column the data is being entered complete with headers (Cells a1:e1) with new column names F6, F7, F8, etc etc.

If i delete the columns in the datagridview the xls imports fine and works, however it forces the user to import an excel even if there are only 1 or 2 files to process. An option may be to load a "default" excel at form load that is blank but this seems like it does not follow "best practices"

 I think there is a way to map the columns but i can not find it for the life of me.

How do I map the Excel columns to the DataGridView columns so that the data is displayed in the existing columns?

Attached is the code that I'm working with.
Private Sub XLSSelect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles XLSselect.Click
 
        OpenFileDialog.Filter = "Excel Files (*.xls)|*.xls|All Files (*.*)|*.*"
        If OpenFileDialog.ShowDialog = Windows.Forms.DialogResult.OK Then
            Dim XLSPathx As String = OpenFileDialog.FileName
        End If
 
        Dim connectionStringTemplate As String = _
    "Provider=Microsoft.ACE.OLEDB.12.0;" + _
    "Data Source={0};" + _
    "Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"""
        Dim XLSPath As String = OpenFileDialog.FileName
        Dim connectionString As String = String.Format(connectionStringTemplate, XLSPath)
        Dim sqlSelect As String = "SELECT * FROM [Sheet1$];"
        ' Load the Excel worksheet into a DataTable
        Dim workbook As DataSet = New DataSet()
        Dim excelAdapter As System.Data.Common.DataAdapter = New System.Data.OleDb.OleDbDataAdapter(sqlSelect, connectionString)
        Try
            excelAdapter.Fill(workbook)
            Dim worksheet As DataTable = workbook.Tables(0)
            DataGridView.DataSource = worksheet
        Catch
        End Try
    End Sub

Open in new window

Judson HallSystems AdminAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SanclerCommented:
Go through the .Columns collection of the DataGridView and set the .DataPropertyName of each column to the equivalent DataTable's - that is, worksheet's - column name.  Those will be the headers from the Excel sheet that you've imported.

Roger
0
Judson HallSystems AdminAuthor Commented:
I created my datagridview with an object (not programatically) I did create the columns and named them as you described in the DGV properties for the columns.

Operation of the above code obviously does something, the datagridview populates with cells but no content is displayed. Any more suggestions?
0
SanclerCommented:
Can you please show the "designer" code for the DataGridView and its columns?  Assuming (as you're using a DataGridView) this is VB.NET 2005 or later, it will be in the form's .Designer.vb file and will look something like this

        'DataGridView1
        '
        Me.DataGridView1.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize
        Me.DataGridView1.Columns.AddRange(New System.Windows.Forms.DataGridViewColumn() {Me.Column1, Me.Column2, Me.Column3})
        Me.DataGridView1.Location = New System.Drawing.Point(40, 56)
        Me.DataGridView1.Name = "DataGridView1"
        Me.DataGridView1.Size = New System.Drawing.Size(224, 136)
        Me.DataGridView1.TabIndex = 0
        '
        'Column1
        '
        Me.Column1.HeaderText = "Column1"
        Me.Column1.Name = "Column1"
        '
        'Column2
        '
        Me.Column2.HeaderText = "Column2"
        Me.Column2.Name = "Column2"
        '
        'Column3
        '
        Me.Column3.HeaderText = "Column3"
        Me.Column3.Name = "Column3"
        '

Roger
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Judson HallSystems AdminAuthor Commented:
'DataGridView
        '
        Me.DataGridView.AllowDrop = True
        Me.DataGridView.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize
        Me.DataGridView.Columns.AddRange(New System.Windows.Forms.DataGridViewColumn() {Me.AvidFilename, Me.HouseNumber, Me.ISCI, Me.Length, Me.Title})
        Me.DataGridView.DataBindings.Add(New System.Windows.Forms.Binding("Text", Global.test.My.MySettings.Default, "UserDefined", True, System.Windows.Forms.DataSourceUpdateMode.OnPropertyChanged))
        Me.DataGridView.Location = New System.Drawing.Point(23, 74)
        Me.DataGridView.Name = "DataGridView"
        Me.DataGridView.Size = New System.Drawing.Size(848, 350)
        Me.DataGridView.TabIndex = 0
        Me.DataGridView.Text = Global.test.My.MySettings.Default.UserDefined
 'AvidFilename
        '
        Me.AvidFilename.DividerWidth = 4
        Me.AvidFilename.FillWeight = 125.0!
        Me.AvidFilename.HeaderText = "AvidFilename"
        Me.AvidFilename.Name = "AvidFilename"
        Me.AvidFilename.SortMode = System.Windows.Forms.DataGridViewColumnSortMode.NotSortable
        Me.AvidFilename.ToolTipText = "Original File Name"
        Me.AvidFilename.Width = 130
        '
        'HouseNumber
        '
        Me.HouseNumber.HeaderText = "HouseNumber"
        Me.HouseNumber.Name = "HouseNumber"
        Me.HouseNumber.SortMode = System.Windows.Forms.DataGridViewColumnSortMode.NotSortable
        Me.HouseNumber.ToolTipText = "New House Number"
        Me.HouseNumber.Width = 125
        '
        'ISCI
        '
        Me.ISCI.HeaderText = "ISCI"
        Me.ISCI.Name = "ISCI"
        Me.ISCI.SortMode = System.Windows.Forms.DataGridViewColumnSortMode.NotSortable
        Me.ISCI.ToolTipText = "ISCI Description"
        '
        'Length
        '
        Me.Length.HeaderText = "Length"
        Me.Length.Name = "Length"
        Me.Length.SortMode = System.Windows.Forms.DataGridViewColumnSortMode.NotSortable
        '
        'Title
        '
        Me.Title.HeaderText = "Title"
        Me.Title.Name = "Title"
        Me.Title.SortMode = System.Windows.Forms.DataGridViewColumnSortMode.NotSortable
        Me.Title.ToolTipText = "Plain Title"
        Me.Title.Width = 350
0
SanclerCommented:
Although that shows, as you say, that (EMPHASIS added) you "did create the columns and NAMED them as you described in the DGV properties for the columns", it does not show any settings for the .DataPropertyName of each column.  It's the .DataPropertyName, not the .Name or the .HeaderText, that maps datasource columns to DataGridView columns.  If you want to do it at Design time, and you know what the Excel import column names will be, you can click on Edit Columns in the dgv task list and put the names in the properties window in the appropriate place - alongside DataPropertyName - for each column.  Here's the example code from .Designer.vb that I posted before updated with me having done that.

        'DataGridView1
        '
        Me.DataGridView1.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize
        Me.DataGridView1.Columns.AddRange(New System.Windows.Forms.DataGridViewColumn() {Me.Column1, Me.Column2, Me.Column3})
        Me.DataGridView1.Location = New System.Drawing.Point(40, 72)
        Me.DataGridView1.Name = "DataGridView1"
        Me.DataGridView1.Size = New System.Drawing.Size(192, 96)
        Me.DataGridView1.TabIndex = 0
        '
        'Column1
        '
        Me.Column1.DataPropertyName = "NameOfDataTableColumn1"
        Me.Column1.HeaderText = "Column1"
        Me.Column1.Name = "Column1"
        '
        'Column2
        '
        Me.Column2.DataPropertyName = "NameOfDataTableColumn2"
        Me.Column2.HeaderText = "Column2"
        Me.Column2.Name = "Column2"
        '
        'Column3
        '
        Me.Column3.DataPropertyName = "NameOfDataTableColumn3"
        Me.Column3.HeaderText = "Column3"
        Me.Column3.Name = "Column3"
        '

Roger
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Judson HallSystems AdminAuthor Commented:
Freakin awesome guy! Works like a charm. Any way to keep it from adding additional columns? (i.e. it adds columns labled F6, F7, F8).
0
SanclerCommented:
Set the .AutoGenerateColumns to False.  If you're doing all the work yourself in the Designer, you don't need it.

Roger
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.