locked
Row header in Grid RRS feed

  • Question

  • I can't find any where if the header in a row can be change. I'm talking about the gray area to the left of the cell, typically the little arrow or pencil show up in it during an edit. Is it possible to put a title or text in that space.

    Thank you,
    VBGTA

    • Edited by VBGTA Saturday, August 23, 2008 4:39 PM added
    Tuesday, August 19, 2008 3:00 AM

Answers

  • Hi VBGTA,

    Base on your post, you want use a DataGridView control and display some text in the row header to replace the little default arrows, here let me give you a couple of reference

    Use the DataGridView.CellPainting Event. You can handle this event to customize the appearance of cells in the control. You can paint entire cells yourself, or paint specific parts of cells and use the DataGridViewCellPaintingEventArgs.PaintBackground or DataGridViewCellPaintingEventArgs.PaintContent methods to paint other parts. You can also use the VisualStyleRenderer class to paint standard controls using the current theme. For more information, see Rendering Controls with Visual Styles. If you are using Visual Studio 2005, you also have access to a large library of standard images that you can use with the DataGridView.

    This is sample about what you want

    Code Snippets:

    Imports System.Data.SqlClient  
     
    Public Class Form1  
        Dim dt As New DataTable  
     
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load  
            Dim pt As New Point  
            Dim strConn As String = _ 
               "Server = .;Database = NorthWind; Integrated Security = SSPI;"  
            Dim conn As New SqlConnection(strConn)  
            Dim da As New SqlDataAdapter("Select * from products", conn)  
     
            da.Fill(dt)  
     
            DataGridView1.DataSource = dt 
        End Sub  
     
    Private Sub DataGridView1_CellPainting(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellPaintingEventArgs) Handles DataGridView1.CellPainting  
            Dim sf As New StringFormat  
            sf.Alignment = StringAlignment.Center  
            If e.ColumnIndex < 0 AndAlso e.RowIndex >= 0 AndAlso e.RowIndex < dt.Rows.Count Then  
                e.PaintBackground(e.ClipBounds, True)  
                e.Graphics.DrawString((e.RowIndex + 1).ToString, Me.Font, Brushes.Black, e.CellBounds, sf)  
                e.Handled = True 
            End If  
        End Sub  
    End Class   
     
       
     
     

    You could get more information with the follow article.
    DataGridView.CellPainting Event
    How to: Customize the Appearance of Cells in the Windows Forms DataGridView Control
    .

    If you have any issue, Please feel free to tell us ^_^

    Xiingwei

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, August 25, 2008 8:52 AM
  • Excel, eh?  You didn't mention that before.  COM interop with Excel can be a beast sometimes and it requires great patience when things are going wrong.  There are many many threads in this forum regarding Excel interop and I strongly suggest doing a search in the search box at the top of the forum here.  Please post the code you have so far if you would like assistance (or start a new thread if it is truly a new question so other people can find it by topic easier).

    You might be interested in the following code where I illustrate a very quick method to getting data into Excel by placing a tab-delimited string -- constructed by iterating through the datagridview -- into the clipboard and then using the native Excel worksheet's Paste command.  For a thousand rows of ten columns it could mean the difference of 1 second versus 30 seconds spent manually inserting cell values one-by-one. 

    I have been working currently on a utility app that pulls in Excel worksheet data into a grid where the data can be massaged in a variety of ways.  Anyhow, I have an Export piece where you can save the data in many formats -- here's a small portion of that code adjusted for your means.  I kept in the code to generate a delimited file as an option -- thought you might find it handy at some point.  It can be pasted into a form's code if you just create a new Windows Application project, and then drag a DGV and a button on to the form.  Try it out and tweak it how you want. 

    Please notice how I dispose of the Excel COM objects by using a helper method that releases the resources -- this is VERY important when working with COM objects.  The method can be pasted into any project you have that utilizes COM objects.

     
    Imports Microsoft.Office.Interop  
     
    Public Class Form1  
        'NOTE:  You need to add a reference to an Excel library (10.0 or higher)  
        '       and a datagridview and button on the form.  
     
        Private Sub Form1_Load(ByVal sender As System.ObjectByVal e As System.EventArgs) Handles MyBase.Load  
     
            'In case you want test data to fill your datagridview...  
            Dim t As New DataTable  
            For x As Int32 = 1 To 10  
                t.Columns.Add("Column " & x)  
            Next 
     
            Dim rnd As New Random  
     
            For x As Int32 = 1 To 500  
                Dim nr As DataRow = t.NewRow  
                For y As Int32 = 0 To 9  
                    nr(y) = rnd.Next(100, 100000)  
                Next 
                t.Rows.Add(nr)  
            Next 
     
            DataGridView1.DataSource = t  
     
        End Sub 
     
        Private Sub Button1_Click(ByVal sender As System.ObjectByVal e As System.EventArgs) Handles Button1.Click  
     
            If DataGridView1.DataSource IsNot Nothing AndAlso DataGridView1.Rows.Count <> 0 Then 
                Dim sfd As New SaveFileDialog  
                Try 
                    sfd.AddExtension = True 
                    sfd.CheckPathExists = True 
                    sfd.Filter = "Excel Workbook|*.xls|Comma Separated|*.csv|Text File|*.txt|All Files|*.*" 
                    sfd.DefaultExt = ".xls" 
                    sfd.OverwritePrompt = True 
                    sfd.Title = "Please choose the location to save this file" 
     
                    Dim result As DialogResult = sfd.ShowDialog  
                    If result <> Windows.Forms.DialogResult.Cancel Then 
                        Select Case IO.Path.GetExtension(sfd.FileName)  
                            Case ".xls" 
                                SaveAsExcelWorkbook(sfd.FileName)  
                            Case ".csv" 
                                SaveAsDelimitedFile(sfd.FileName, ",")  
                            Case ".txt" 
                                'Defaulted to tab-delimited for text files - you can change this.  
                                SaveAsDelimitedFile(sfd.FileName, vbTab)  
                            Case Else 
                                Throw New Exception("This type of file is not supported.")  
                        End Select 
     
                    End If 
                Catch ex As Exception  
                    MessageBox.Show(ex.Message)  
                Finally 
                    sfd.Dispose()  
                End Try 
            End If 
     
        End Sub 
     
        Private Sub SaveAsExcelWorkbook(ByVal filename As String)  
     
            Try 
     
                Clipboard.SetText(GetGridAsDelimitedText(vbTab))  
     
                Dim app As New Excel.Application  
                Dim wb As Excel.Workbook = app.Workbooks.Add  
                Try 
                    app.Range("A1").Select()  
                    CType(app.ActiveSheet, Excel.Worksheet).Paste()  
                    app.Rows("1:1").Select()  
                    CType(app.Selection, Excel.Range).Font.Bold = True 
                    app.Cells.Select()  
                    app.Cells.EntireColumn.AutoFit()  
                    app.Range("A1").Select()  
     
                    wb.SaveAs(Filename:=filename)  
     
                Catch ex As Exception  
                    MessageBox.Show("A problem has occurred while saving to Excel:  " & ex.Message)  
                Finally 
                    Try 
                        wb.Close(False)  
                    Catch 
                        'Allow exception to go unhandled  
                    End Try 
                    ReleaseComObject(wb)  
                    app.Quit()  
                    ReleaseComObject(app)  
                End Try 
     
            Catch ex As Exception  
                MessageBox.Show("Error while sending text to clipboard.  " & ex.Message)  
                Return 
            Finally 
                Clipboard.Clear()  
                Application.DoEvents()  
            End Try 
     
     
        End Sub 
     
        Private Sub SaveAsDelimitedFile(ByVal fileName As StringByVal delim As String)  
     
            Dim StreamData As String = GetGridAsDelimitedText(delim)  
            Using sw As New IO.StreamWriter(fileName, False)  
                sw.Write(StreamData)  
            End Using  
     
        End Sub 
     
        Private Function GetGridAsDelimitedText(ByVal delim As StringAs String 
     
            Dim result As New System.Text.StringBuilder  
     
            'Headers first  
            For x As Int32 = 0 To DataGridView1.Columns.Count - 1  
                result.Append(DataGridView1.Columns(x).HeaderText & delim)  
            Next 
            result.Append(vbNewLine)  
     
            For Each r As DataGridViewRow In DataGridView1.Rows  
                If r.Index <> DataGridView1.NewRowIndex Then 
                    For x As Int32 = 0 To DataGridView1.Columns.Count - 1  
     
                        Dim value As String = "" 
                        If IsDBNull(r.Cells(x).Value) = False Then 
                            value = r.Cells(x).Value.ToString  
                        End If 
     
                        If value.Contains(delim) Then value = """" & value & """"  
                        result.Append(value & delim)  
                    Next 
                    result.Append(vbNewLine)  
                End If 
            Next 
            Return result.ToString  
     
        End Function 
     
        Friend Sub ReleaseComObject(ByVal o As Object)  
            Try 
                System.Runtime.InteropServices.Marshal.ReleaseComObject(o)  
            Catch 
            Finally 
                If o IsNot Nothing Then o = Nothing 
            End Try 
            GC.Collect()  
            GC.WaitForPendingFinalizers()  
        End Sub 
     
     
    End Class 
     
     

    Enjoy!

    • Marked as answer by Xingwei Hu Thursday, August 28, 2008 7:48 AM
    Wednesday, August 27, 2008 1:08 AM

All replies

  • Hi VBGTA,

    Base on your post, you want use a DataGridView control and display some text in the row header to replace the little default arrows, here let me give you a couple of reference

    Use the DataGridView.CellPainting Event. You can handle this event to customize the appearance of cells in the control. You can paint entire cells yourself, or paint specific parts of cells and use the DataGridViewCellPaintingEventArgs.PaintBackground or DataGridViewCellPaintingEventArgs.PaintContent methods to paint other parts. You can also use the VisualStyleRenderer class to paint standard controls using the current theme. For more information, see Rendering Controls with Visual Styles. If you are using Visual Studio 2005, you also have access to a large library of standard images that you can use with the DataGridView.

    This is sample about what you want

    Code Snippets:

    Imports System.Data.SqlClient  
     
    Public Class Form1  
        Dim dt As New DataTable  
     
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load  
            Dim pt As New Point  
            Dim strConn As String = _ 
               "Server = .;Database = NorthWind; Integrated Security = SSPI;"  
            Dim conn As New SqlConnection(strConn)  
            Dim da As New SqlDataAdapter("Select * from products", conn)  
     
            da.Fill(dt)  
     
            DataGridView1.DataSource = dt 
        End Sub  
     
    Private Sub DataGridView1_CellPainting(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellPaintingEventArgs) Handles DataGridView1.CellPainting  
            Dim sf As New StringFormat  
            sf.Alignment = StringAlignment.Center  
            If e.ColumnIndex < 0 AndAlso e.RowIndex >= 0 AndAlso e.RowIndex < dt.Rows.Count Then  
                e.PaintBackground(e.ClipBounds, True)  
                e.Graphics.DrawString((e.RowIndex + 1).ToString, Me.Font, Brushes.Black, e.CellBounds, sf)  
                e.Handled = True 
            End If  
        End Sub  
    End Class   
     
       
     
     

    You could get more information with the follow article.
    DataGridView.CellPainting Event
    How to: Customize the Appearance of Cells in the Windows Forms DataGridView Control
    .

    If you have any issue, Please feel free to tell us ^_^

    Xiingwei

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, August 25, 2008 8:52 AM
  • Hi VBGTA - sounds like good advice from Xiingwei.  I thought I'd just add in the code that I use to provide row numbering in the grid (like Query Analyzer or Excel) .  Same concept it's just I use the RowPostPaint event (it event adjusts the row header width to fit the numbers).  This event may hold an advantage over the CellPainting event because it is raised less frequently -- I'm not sure with your situation -- but anyhow it's a good little snippet to have in your toolbox...

     
       Private Sub grid_RowPostPaint(ByVal sender As ObjectByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) _  
        Handles grid.RowPostPaint  
            Dim strRowNumber As String = (e.RowIndex + 1).ToString  
            Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)  
            If grid.RowHeadersWidth < CInt((size.Width + 20)) Then 
                grid.RowHeadersWidth = CInt((size.Width + 20))  
            End If 
     
            Dim b As Brush = SystemBrushes.ControlText  
     
            e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, _  
                e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))  
        End Sub 
     



    • Marked as answer by Xingwei Hu Thursday, August 28, 2008 7:48 AM
    • Unmarked as answer by Xingwei Hu Thursday, August 28, 2008 7:48 AM
    Monday, August 25, 2008 11:51 AM
  • Wow, this looks like work. LOL

    Thank you, I'll type it in and give it a go, I'm new to programming so this is a war of inches. I'm using VB express 2008 and trying to get a button from my little program to call up a specific worksheet in excel. I can get excel to come up but it errors trying to bring up the worksheet. The coding that I found here in MSDN is not working other then calling up Excel. Setting up a grid right from VB was the back up plan, I'm not looking forward to repopulating all the cells in the grid with  all the info from my Excel sheets.

    Thank you Xingwei Hu and Dig-Boy, you both have been a great help,
    VBGTA.
    Tuesday, August 26, 2008 11:20 PM
  • Excel, eh?  You didn't mention that before.  COM interop with Excel can be a beast sometimes and it requires great patience when things are going wrong.  There are many many threads in this forum regarding Excel interop and I strongly suggest doing a search in the search box at the top of the forum here.  Please post the code you have so far if you would like assistance (or start a new thread if it is truly a new question so other people can find it by topic easier).

    You might be interested in the following code where I illustrate a very quick method to getting data into Excel by placing a tab-delimited string -- constructed by iterating through the datagridview -- into the clipboard and then using the native Excel worksheet's Paste command.  For a thousand rows of ten columns it could mean the difference of 1 second versus 30 seconds spent manually inserting cell values one-by-one. 

    I have been working currently on a utility app that pulls in Excel worksheet data into a grid where the data can be massaged in a variety of ways.  Anyhow, I have an Export piece where you can save the data in many formats -- here's a small portion of that code adjusted for your means.  I kept in the code to generate a delimited file as an option -- thought you might find it handy at some point.  It can be pasted into a form's code if you just create a new Windows Application project, and then drag a DGV and a button on to the form.  Try it out and tweak it how you want. 

    Please notice how I dispose of the Excel COM objects by using a helper method that releases the resources -- this is VERY important when working with COM objects.  The method can be pasted into any project you have that utilizes COM objects.

     
    Imports Microsoft.Office.Interop  
     
    Public Class Form1  
        'NOTE:  You need to add a reference to an Excel library (10.0 or higher)  
        '       and a datagridview and button on the form.  
     
        Private Sub Form1_Load(ByVal sender As System.ObjectByVal e As System.EventArgs) Handles MyBase.Load  
     
            'In case you want test data to fill your datagridview...  
            Dim t As New DataTable  
            For x As Int32 = 1 To 10  
                t.Columns.Add("Column " & x)  
            Next 
     
            Dim rnd As New Random  
     
            For x As Int32 = 1 To 500  
                Dim nr As DataRow = t.NewRow  
                For y As Int32 = 0 To 9  
                    nr(y) = rnd.Next(100, 100000)  
                Next 
                t.Rows.Add(nr)  
            Next 
     
            DataGridView1.DataSource = t  
     
        End Sub 
     
        Private Sub Button1_Click(ByVal sender As System.ObjectByVal e As System.EventArgs) Handles Button1.Click  
     
            If DataGridView1.DataSource IsNot Nothing AndAlso DataGridView1.Rows.Count <> 0 Then 
                Dim sfd As New SaveFileDialog  
                Try 
                    sfd.AddExtension = True 
                    sfd.CheckPathExists = True 
                    sfd.Filter = "Excel Workbook|*.xls|Comma Separated|*.csv|Text File|*.txt|All Files|*.*" 
                    sfd.DefaultExt = ".xls" 
                    sfd.OverwritePrompt = True 
                    sfd.Title = "Please choose the location to save this file" 
     
                    Dim result As DialogResult = sfd.ShowDialog  
                    If result <> Windows.Forms.DialogResult.Cancel Then 
                        Select Case IO.Path.GetExtension(sfd.FileName)  
                            Case ".xls" 
                                SaveAsExcelWorkbook(sfd.FileName)  
                            Case ".csv" 
                                SaveAsDelimitedFile(sfd.FileName, ",")  
                            Case ".txt" 
                                'Defaulted to tab-delimited for text files - you can change this.  
                                SaveAsDelimitedFile(sfd.FileName, vbTab)  
                            Case Else 
                                Throw New Exception("This type of file is not supported.")  
                        End Select 
     
                    End If 
                Catch ex As Exception  
                    MessageBox.Show(ex.Message)  
                Finally 
                    sfd.Dispose()  
                End Try 
            End If 
     
        End Sub 
     
        Private Sub SaveAsExcelWorkbook(ByVal filename As String)  
     
            Try 
     
                Clipboard.SetText(GetGridAsDelimitedText(vbTab))  
     
                Dim app As New Excel.Application  
                Dim wb As Excel.Workbook = app.Workbooks.Add  
                Try 
                    app.Range("A1").Select()  
                    CType(app.ActiveSheet, Excel.Worksheet).Paste()  
                    app.Rows("1:1").Select()  
                    CType(app.Selection, Excel.Range).Font.Bold = True 
                    app.Cells.Select()  
                    app.Cells.EntireColumn.AutoFit()  
                    app.Range("A1").Select()  
     
                    wb.SaveAs(Filename:=filename)  
     
                Catch ex As Exception  
                    MessageBox.Show("A problem has occurred while saving to Excel:  " & ex.Message)  
                Finally 
                    Try 
                        wb.Close(False)  
                    Catch 
                        'Allow exception to go unhandled  
                    End Try 
                    ReleaseComObject(wb)  
                    app.Quit()  
                    ReleaseComObject(app)  
                End Try 
     
            Catch ex As Exception  
                MessageBox.Show("Error while sending text to clipboard.  " & ex.Message)  
                Return 
            Finally 
                Clipboard.Clear()  
                Application.DoEvents()  
            End Try 
     
     
        End Sub 
     
        Private Sub SaveAsDelimitedFile(ByVal fileName As StringByVal delim As String)  
     
            Dim StreamData As String = GetGridAsDelimitedText(delim)  
            Using sw As New IO.StreamWriter(fileName, False)  
                sw.Write(StreamData)  
            End Using  
     
        End Sub 
     
        Private Function GetGridAsDelimitedText(ByVal delim As StringAs String 
     
            Dim result As New System.Text.StringBuilder  
     
            'Headers first  
            For x As Int32 = 0 To DataGridView1.Columns.Count - 1  
                result.Append(DataGridView1.Columns(x).HeaderText & delim)  
            Next 
            result.Append(vbNewLine)  
     
            For Each r As DataGridViewRow In DataGridView1.Rows  
                If r.Index <> DataGridView1.NewRowIndex Then 
                    For x As Int32 = 0 To DataGridView1.Columns.Count - 1  
     
                        Dim value As String = "" 
                        If IsDBNull(r.Cells(x).Value) = False Then 
                            value = r.Cells(x).Value.ToString  
                        End If 
     
                        If value.Contains(delim) Then value = """" & value & """"  
                        result.Append(value & delim)  
                    Next 
                    result.Append(vbNewLine)  
                End If 
            Next 
            Return result.ToString  
     
        End Function 
     
        Friend Sub ReleaseComObject(ByVal o As Object)  
            Try 
                System.Runtime.InteropServices.Marshal.ReleaseComObject(o)  
            Catch 
            Finally 
                If o IsNot Nothing Then o = Nothing 
            End Try 
            GC.Collect()  
            GC.WaitForPendingFinalizers()  
        End Sub 
     
     
    End Class 
     
     

    Enjoy!

    • Marked as answer by Xingwei Hu Thursday, August 28, 2008 7:48 AM
    Wednesday, August 27, 2008 1:08 AM