asked on

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

Sancler

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

Heath Hall

ASKER

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?

Sancler

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

Heath Hall

ASKER

'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

ASKER CERTIFIED SOLUTION

Sancler

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Heath Hall

ASKER

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).

Sancler

Set the .AutoGenerateColumns to False. If you're doing all the work yourself in the Designer, you don't need it.

Roger