Question Predefined Columns in a Datagridview using a datatable as Datasource

[sentinel0]

My code works like this I load an Excel worksheet into a datatable this table is then loaded into my datagridview control. If I leave autogeneratetables = true it creates extra columns. So I set it to false but now it doesn't load anything. Is there not away to bind datatable colunms to the dataviewgrid by column index instead of by name. I need it to work this way because the spreadsheet column index will always be the same but the name of the header column in that sheet does change.

If some code needs to be submitted just let me know. Thanks.

 

[Paszt]

I'm familiar with many different methods to import an Excel file into a DataTable and most have a headers property (a property to tell the method doing the import if the Excel spreadsheet has headers). You could set that property to no headers and discard the first record after import. The resulting header names depend on your import method.

 

[sentinel0]

I'm using the System.DataOleDb.OleDbDataConnection with the extended properties HDR=YES which from my understanding says the first row of the sheet is the header column. It loads the workbook into a dataset then fills my datatable with just the first table. If I'm understanding you correctly, I would set HDR=NO then I'm drawing a blank what happens to the datatable does it want a header colunm or does it not care if it doesn't care then how do I tell datagridview1.datasource = mydatatable but remove datagridview.row(0).

 

[sentinel0]

When I set the HDR value to No and run the code using the created table as the DataSource I still get nothing in the datagridview control.
Edit:
I just found something on stackoverflow.com I guess I could try which would be to go through each column in the datatable and at that time set the Data Field for each column in the datagridview to that datatables column name. Seems like a pain in the ass though I wish I could just do something like:
Dim intRow As Integer
intRow = 0
'DataGridView1.DataSource = aTable
For Each row As DataRow In aTable.Rows
DataGridView1.Rows.Add(row.ItemArray(intRow))
Next
But I'm only getting the data in the first column. I would really like to learn more about working with a datasource and this method just tramples all over it.

 

[sentinel0]

Okay so this code will add the row to the datagridview, I am sadden though I couldn't figure out how to do it with the datasource.
'DataGridView1.DataSource = aTable
For Each row As DataRow In aTable.Rows
DataGridView1.Rows.Add(row.Item(0),row.Item(1),row.Item(2),row.Item(3))
Next

 

[Paszt]

I've not got Visual Studio to test. But this:

Dim intRow As Integer
intRow = 0
For Each row As DataRow In aTable.Rows
    DataGridView1.Rows.Add(row.ItemArray(intRow))
Next

wouldn't work because you never increase the value of intRow. I think you meant:

For i as Integer = 0 to aTable.Rows -1
    DataGridView1.Rows.Add(row.ItemArray(i))
Next

But I'm sure there's a way to bind the DataGridView to the dataTable.

 

[sentinel0]

Yeah i meant to include intRow = intRow + 1 before the next to make it increment. I still think my logic was flawed because it didnt load all the data just the first column. I would love to learn how to work with the datasource and bindings but, by design it wants datagrid columns to match datatable column name ;(

 

[jmcilhinney]

You need to set the DataPropertyName of each column in the grid to tell it which column/property of the data source to take its data from.