March 24th, 2004, 06:46 AM
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
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
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!
March 24th, 2004, 07:17 AM
After you fill the DATASET, just remove the FIRST ROW of in the DATATABLE.
March 24th, 2004, 08:20 AM
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.
March 24th, 2004, 09:16 AM
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!
March 24th, 2004, 10:09 AM
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
'Save file with the same name
' Now open the Excel file as before using OleDB ...
August 22nd, 2012, 09:43 PM
Re: Importing ms excel data to datagrid. first row is column names in dg
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
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)
Click Here to Expand Forum to Full Width