Click here to Skip to main content
14,928,926 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I'm trying to change the entire left column of a datagridview to a headercell. Right now I have a program that pulls data from an excel file using the oledataadapter and lets me modify the data in the datagridview and save it back to the excel file. However one of my worksheets has the headers on the left and the data on the column to the right. Here is my code so far.

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

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


        ds11 = New System.Data.DataSet()


DataGridView8.DataSource = ds11.Tables(0).DefaultView


'I found this code on MSDN, it turns the first column into a header, which is what I want. 'However I'm having trouble filling these headers with my data. 

VB
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
Posted
Comments
Maciej Los 12-Jun-13 16:15pm
   
WinForms? WebControls?
It makes the difference!

Here is a way to fake the DataGridView into doing what I think you want to do. Start with a new WinForm project and add a dgv to the form. Then add this to the Form1.vb code.

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
   ' make some data to show
   Dim dt As New DataTable()
   With dt
      .Columns.Add("Header", GetType(String))
      .Columns.Add("C1", GetType(Int32))
      .Columns.Add("C2", GetType(Int32))
      .Rows.Add(New Object() {"A", 2, 3})
      .Rows.Add(New Object() {"B", 4, 5})
      .Rows.Add(New Object() {"C", 6, 7})
   End With

   ' Setup the DGV to use the "Header" column as the row header
   With DataGridView1
      .DataSource = dt
      .AllowUserToAddRows = False
      .RowHeadersVisible = True
      .Columns("Header").Visible = False ' hide the header column
      .RowHeadersWidthSizeMode = DataGridViewRowHeadersWidthSizeMode.AutoSizeToAllHeaders
   End With
End Sub

Private Sub DataGridView1_RowPrePaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPrePaintEventArgs) Handles DataGridView1.RowPrePaint
   ' sorting clears the headertext, so set before each row paint
   DataGridView1.Rows(e.RowIndex).HeaderCell.Value = DataGridView1.Rows(e.RowIndex).Cells("Header").Value
End Sub


**** Edit:
After thinking it over a bit, this may be a better approach depending on what you are trying to accomplish.
Public Class Form1
   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
      ' make some data to show
      Dim dt As New DataTable()
      With dt
         .Columns.Add("Header", GetType(String))
         .Columns.Add("C1", GetType(Int32))
         .Columns.Add("C2", GetType(Int32))
         .Columns.Add("C3", GetType(Int32))
         .Rows.Add(New Object() {"A", 2, 3, 7})
         .Rows.Add(New Object() {"B", 4, 5, 8})
         .Rows.Add(New Object() {"C", 6, 7, 2})
      End With

      DataGridView1.DataSource = dt.DefaultView
      Dim NumColToFreeze As Int32 = 2
      With DataGridView1
         .RowHeadersVisible = True
         For i As Int32 = 0 To NumColToFreeze - 1
            .Columns(i).Frozen = True ' prevents column from scrolling out of view
            .Columns(i).DefaultCellStyle = .RowHeadersDefaultCellStyle ' make it look like a header
            .Columns(i).ReadOnly = True
         Next
      End With
   End Sub
End Class
   
v2
Comments
CAS1224 13-Jun-13 9:45am
   
Thanks so much for your answer. Now do you know how I would be able to use my datasets "ds11" and "ds12" to accomplish the same thing? I'm really hoping to have the cells from "A15:B21" from my excel file be the headers for my datagridview
TnTinMn 13-Jun-13 20:05pm
   
To use you datasets, you set the DataSource property just like you originally did. To use two columns as a header cell, you need to determine a way to combine the two values as a format string and assign that to HeaderCell.Value in the RowPrePaintEvent. You would also need to set those two column's Visible property to false.
TnTinMn 14-Jun-13 18:11pm
   
Take a look at the alternative code I just posted above.
Please, refer these:
WebControls
===========
GridView.RowHeaderColumn Property (WebControls)[^]

WinForms
========
You need to set RowHeadersVilsible = True to set first column as row-headers ;)
DataGridView.RowHeadersVisible Property (WinForms)[^]
DataGridViewRow.HeaderCell Property (WinForms)[^]
   
Comments
CAS1224 12-Jun-13 18:18pm
   
Yes this makes the headers visible but I'm looking to make the data in the first column the header. All this does is create another column to the left of my first column.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900