Importing ms excel data to datagrid. first row is column names in dg

[mcmcom]

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

openFileDialog1.InitialDirectory = "c:\mcmcom\chambers\ptsdatabase\"
openFileDialog1.Filter = ".xls files (*.xls)|*.xls|All files (*.*)|*.*"
openFileDialog1.FilterIndex = 2
openFileDialog1.RestoreDirectory = True

If openFileDialog1.ShowDialog() = DialogResult.OK Then
myFile = openFileDialog1.FileName
End If

Try
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

MyCommand.Fill(dt)


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!
Its annoying
mcm

[coolbiz]

After you fill the DATASET, just remove the FIRST ROW of in the DATATABLE.

-Cool Bizs

[Gizmo001]

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.

[mcmcom]

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!

[Gizmo001]

Open Excel file using the following (after including Excel Application in your reference: From Project Menu select Add Reference, select COM tab, select Excel Object, Press OK)

' After identifying the Excel file name from OpenFiledialog1)

dim Xl as new Excel.Application
dim Xlb as Excel.Workbook = Xl.Workbooks.Open(Openfiledialog1.filename)
Dim Xls as Excel.Worksheet=CType(Xlb.worksheets(1),Excel.worksheet)

'insert a row
Xls.rows("1:1").Insert

'Add headers

xls.cells(1,1).value="Column1"
xls.cells(1,2).value="Column2"
xls.cells(1,3).value="Column3"
...
..

'Save file with the same name
Xlb.save
Xls=nothing
Xlb.close(False)
Xlb=nothing
Xl.quit
Xl=nothing

' Now open the Excel file as before using OleDB ...


Good luck.

[kamote]

I found a simple solution,
1.create a new row,
2.copy values from header to new row,
3.rename header names
4.insert new row to table at pos index 0

MyCommand.Fill(DtSet)
myDataRow = DtSet.Tables(0).NewRow()
For i = 0 To DtSet.Tables(0).Columns.Count - 1
myDataRow(i) = DtSet.Tables(0).Columns(i).Caption
DtSet.Tables(0).Columns(i).ColumnName = CStr(i)
Next
DtSet.Tables(0).Rows.InsertAt(myDataRow, 0)