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?
 
SanclerConnect With a Mentor Commented:
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
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
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
 
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
All Courses

From novice to tech pro — start learning today.