Importing ms excel data to datagrid. first row is column names in dg
I am using the following code to import msexcel data and put it in a data grid. It all works well except that the first row of data is assumed to be the column names. So I lose the first row of data. Anyone know how i can fix this?
Private Function GetData()
Dim myFile As String
'Dim dt As System.Data.DataTable
Dim openFileDialog1 As New OpenFileDialog
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
Dim MyConnection As System.Data.OleDb.OleDbConnection
If openFileDialog1.ShowDialog() = DialogResult.OK Then
myFile = openFileDialog1.FileName
MyConnection = New System.Data.OleDb.OleDbConnection( _
"provider=Microsoft.Jet.OLEDB.4.0; " & _
"data source= " & myFile & "; Extended Properties=Excel 8.0;")
' Select the data from Sheet1 of the workbook.
MyCommand = New System.Data.OleDb.OleDbDataAdapter( _
"select * from [Sheet1$]", MyConnection)
dt = New System.Data.DataTable
So basically it fills the datagrid, but the column header names are the values of the first record. If i set the datagrid properties to No column headers, i just lose the first record!
Alternately, add a header row to the Excel data before importing it to datagrid. You can do this programmatically or outside. If you are working with Windows Forms, you can open Excel file and add a header row, close it, and use OleDb to read in data. If you are using Webform, it is better to steer clear of editing Excel programmatically. Do it outside, then import.
its pure windows forms. Its a vb.net application not asp.net or anything. How do i do it programmatically? Can I just add a new row to the top of the datatable? This program will analyze excel files, so I can't really manually add a header row in excel, kinda defeats the purpose of the program. Basically it loads the excel file, uses regular expressions to match columns, then sets the column name based on the regular expression match. So what i need to do is add a row to the data table maybe BEFORE i insert the excel data to it? Who knows now, i need sleep!