I'm looking for a way to make my first column in my datagridview a header

Question

Ok so I have a dataset that is pulling specific cells from an excel file and populating a datagridview column. However the cells I'm pulling really need to be headers rather than normal columns in the datagridview. So is there an easy way I can turn this column of data into a header text? To help ellaborate I've provided some code below including comments.

' The following lines specify the exact cells I with to pull from the excel file and populates the first column of the datagridview

MyCommand1 = New OleDbDataAdapter("Select * from [myWorksheet$A15:B21]", MyConnection)


'Here is my dataset'
    ds1 = New System.Data.DataSet()
DataGridView1.DataSource = ds1.Tables(0).DefaultView
'So at this point I have a datagridview with a column of data from the exact cells 
' from the excel file that I want


'This last part is code I found on MSDN which will hide the column headers and will turn the first column into headertext. Essentially it is adding an additional column to the left and turning that into headertext. 

Private Sub DataGridView8_CellPainting(sender As Object, e As DataGridViewCellPaintingEventArgs) Handles DataGridView8.CellPainting
    Dim rowNumber As Integer = 1

    For Each row As DataGridViewRow In DataGridView8.Rows
        If row.IsNewRow Then Continue For
        row.HeaderCell.Value = "Row " & rowNumber
        rowNumber = rowNumber + 1
    Next
    DataGridView8.AutoResizeRowHeadersWidth( _
        DataGridViewRowHeadersWidthSizeMode.AutoSizeToAllHeaders)
End Sub

'If anyone can find out a way for me to make the first column headertext it would make my day.

I'm not so sure but I think it would need two step .. transfer column to row in the table .. then display it .. — matzone, Jun 13 '13 at 16:03

score 0 · Answer 1 · answered Jun 13 '13 at 16:17

Just an idea ..

Dim tblXls as DataTable = ds1.Tables(0) '---> this is your table
Dim tblNew as New Datatable
Dim dc as DataColumn

For x as Integer = 0 to tblXls.Rows.Count -1
    dc = New DataColumn
    dc.DataType = System.Type.GetType("System.DateTime")
    dc.ColumnName = tblXls.Rows(x).Item(0)
    tblNew.Columns.Add(dc)
Next

Then set the tblNew as your DGV datasource ..

DataGridView.Datasource = tblNew

This is only prepare header .. you know the rest ..

score 0 · Answer 2 · edited May 23 '17 at 11:50

Not Very Sure but changing your connection String might do the job for you.

connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Test.xlsx;Extended Properties=" + Convert.ToChar(34).ToString() + "Excel 12.0;HDR=Yes;IMEX=2" + Convert.ToChar(34).ToString() + ""

In the code above HDR represents the headers. Set HDR = Yes to show the first row as headers and HDR = No to show first row as normal rows.

You can get more info Get Data from MS Excel in asp.net

score 0 · Answer 3 · answered Jun 13 '13 at 22:28

You can populate the row header in the CellFormatting event:

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
    Dim SQL As String = "SELECT [ZIP], [Place] FROM [City_2]"
    dgv1.DataSource = Gen.GetDataView(SQL) ' load the datasource here
    dgv1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)
    dgv1.Columns(0).Visible = False ' contains same as row header
    dgv1.RowHeadersWidthSizeMode = DataGridViewRowHeadersWidthSizeMode.AutoSizeToAllHeaders
End Sub

Private Sub dgv1_CellFormatting(sender As Object, e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles dgv1.CellFormatting
    dgv1.Rows(e.RowIndex).HeaderCell.Value = dgv1.Rows(e.RowIndex).Cells(0).Value
End Sub

I'm looking for a way to make my first column in my datagridview a header

3 Answers3