Autosize Columns in a WinForms DataGrid - VB.Net

Posted on 2004-11-09
Last Modified: 2012-08-14
Look at the code below:
        Dim selectCMDOddments As SqlCommand

        selectCMDOddments = New SqlCommand("sp_ABC", mySqlConnection)
        mySqlDataAdapterOddments = New SqlDataAdapter
        mySqlDataAdapterOddments.SelectCommand = selectCMDOddments

            myDataSetOddments = New DataSet
            mySqlDataAdapterOddments.Fill(myDataSetOddments, "tblXYZ")
            OddmentsGrid.DataSource = myDataSetOddments.Tables("tblXYZ")
            OddmentsGrid.CaptionVisible = True
            OddmentsGrid.CaptionText = "Oddments"

        Catch ex As Exception
            MessageBox.Show("Unable to retrieve data : " + ex.Message)
        End Try

This runs fine, it fetches the details from the database and display it in the Grid.

Now how can I autosize the columns and making last column wide enough to cover rest of the area on the datagrid.

Pls try to change the code above so that it does the above requirement.


Question by:radhakrishan1
    LVL 8

    Accepted Solution

    here is a function to set the width of the column to the largest value (aka autosize)

    i think i got it from msdn

        Public Function LongestField(ByVal tableName As String, ByVal columnname As String) As Integer
            Dim maxlength As Integer = 0
            Dim tot As Integer = ds.Tables(tableName).Rows.Count()
            Dim straux As String = ""
            Dim intaux As Integer = 0

            Dim g As Graphics = DataGrid1.CreateGraphics

            Dim offset As Integer = Convert.ToInt32(Math.Ceiling(g.MeasureString(" ", DataGrid1.Font).Width))
            Dim i As Integer

            For i = 0 To tot - 1
                straux = ds.Tables(tableName).Rows(i)(columnname).ToString()
                intaux = Convert.ToInt32(Math.Ceiling(g.MeasureString(straux, DataGrid1.Font).Width))
                If intaux > maxlength Then maxlength = intaux

            'checks to see if the column name is wider than any of the data
            intaux = Convert.ToInt32(Math.Ceiling(g.MeasureString(columnname, DataGrid1.Font).Width))
            If intaux >= (maxlength - 5) Then
                maxlength = intaux
                offset = Convert.ToInt32(Math.Ceiling(g.MeasureString("xxx", DataGrid1.Font).Width))
            End If

            Return maxlength + offset
        End Function

    as far as the last one covering the duration of the datagrid, i know there is a property in the 3rd party component i use, but i dont think there is one in the ms flexgrid.  ill look around for it though...



    Author Comment

    Do I need to pass each column name one by one to this function.

    Can you add code which I gave above and how to call this function

    Can't we give just the table name
    LVL 28

    Expert Comment

    '1.Create Form1 and replace the whole code with the following code.
    'It will create Datagrid dg, Label lblCol, textbox txtCol and button btnAutoSize
    '2. Replace TesDb database to your database and table PRQ to your table
    '3.Modify TableStyle section (ts) of the code to get your fields
    '4.Run the code"
    'enter the col number ot the textbox to resize or leave txtbox blank to resize all

    Imports System.Data.OleDb

    Public Class Form1
        Inherits System.Windows.Forms.Form

    #Region " Windows Form Designer generated code "

        Public Sub New()

            'This call is required by the Windows Form Designer.

            'Add any initialization after the InitializeComponent() call

        End Sub

        'Form overrides dispose to clean up the component list.
        Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
            If disposing Then
                If Not (components Is Nothing) Then
                End If
            End If
        End Sub

        'Required by the Windows Form Designer
        Private components As System.ComponentModel.IContainer

        'NOTE: The following procedure is required by the Windows Form Designer
        'It can be modified using the Windows Form Designer.  
        'Do not modify it using the code editor.
        Friend WithEvents btnAutoSize As System.Windows.Forms.Button
        Friend WithEvents dg As System.Windows.Forms.DataGrid
        Friend WithEvents lblCol As System.Windows.Forms.Label
        Friend WithEvents txtCol As System.Windows.Forms.TextBox
        <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
            Me.btnAutoSize = New System.Windows.Forms.Button
            Me.dg = New System.Windows.Forms.DataGrid
            Me.lblCol = New System.Windows.Forms.Label
            Me.txtCol = New System.Windows.Forms.TextBox
            CType(Me.dg, System.ComponentModel.ISupportInitialize).BeginInit()
            Me.btnAutoSize.Location = New System.Drawing.Point(232, 168)
            Me.btnAutoSize.Name = "btnAutoSize"
            Me.btnAutoSize.TabIndex = 12
            Me.btnAutoSize.Text = "AutoSize"
            Me.dg.DataMember = ""
            Me.dg.HeaderForeColor = System.Drawing.SystemColors.ControlText
            Me.dg.Location = New System.Drawing.Point(4, 4)
            Me.dg.Name = "dg"
            Me.dg.Size = New System.Drawing.Size(520, 152)
            Me.dg.TabIndex = 11
            Me.lblCol.Location = New System.Drawing.Point(8, 164)
            Me.lblCol.Name = "lblCol"
            Me.lblCol.Size = New System.Drawing.Size(128, 32)
            Me.lblCol.TabIndex = 13
            Me.lblCol.Text = "EnterColon # to resize or leave blank for all"
            Me.txtCol.Location = New System.Drawing.Point(144, 168)
            Me.txtCol.Name = "txtCol"
            Me.txtCol.Size = New System.Drawing.Size(68, 20)
            Me.txtCol.TabIndex = 14
            Me.txtCol.Text = ""
            Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
            Me.ClientSize = New System.Drawing.Size(524, 214)
            Me.Name = "Form1"
            Me.Text = "Form1"
            CType(Me.dg, System.ComponentModel.ISupportInitialize).EndInit()

        End Sub

    #End Region
        Private Const DBNAME = "c:\testdb\testdb.mdb"
        Dim sConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & DBNAME & _
                              ";Persist Security Info=False"
        Private ds As New DataSet
        Private da As New OleDbDataAdapter("select YYYYMM, ProdID, Name from PRQ", sConn)
        Dim cmdBuilder As New OleDbCommandBuilder(da)

        Dim cn As OleDbConnection

        Dim c As DataGridTextBoxColumn
        Dim ts As DataGridTableStyle

        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            Dim c As DataGridTextBoxColumn
            Dim ts As DataGridTableStyle

            'da = New OleDbDataAdapter(sSQL, sConn)
            da.Fill(ds, "PRQ")
            dg.DataSource = ds
            dg.DataMember = "PRQ" ' not to show initial (+)

            ts = New DataGridTableStyle
            ts.MappingName = "PRQ"
            c = New DataGridTextBoxColumn
            c.MappingName = "YYYYMM"
            c.Width = 50
            c.HeaderText = "YYYYMM"
            c.Alignment = HorizontalAlignment.Left '(Right Or center)

            c = New DataGridTextBoxColumn
            c.MappingName = "ProdID"
            c.Width = 50
            c.HeaderText = "Prod ID"

            c = New DataGridTextBoxColumn
            c.MappingName = "Name"
            c.Width = 200
            c.HeaderText = "Prod Name"

        End Sub
        Private Sub btnAutoSize_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAutoSize.Click
            If txtCol.Text.Length = 0 Then
                AutoSizeTable() 'autosize all cols
                AutoSizeCol(CType(txtCol.Text, Integer)) 'autosize  col in brackets
            End If
        End Sub
        Public Sub AutoSizeTable()
            Dim numCols As Integer
            numCols = ds.Tables("PRQ").Columns.Count

            Dim i As Integer
            i = 0
            Do While (i < numCols)
                i = (i + 1)

        End Sub

        Public Sub AutoSizeCol(ByVal col As Integer)

            Dim width As Single
            width = 0
            Dim numRows As Integer
            numRows = ds.Tables("PRQ").Columns.Count
            Dim g As Graphics
            g = Graphics.FromHwnd(dg.Handle)
            Dim sf As StringFormat
            sf = New StringFormat(StringFormat.GenericTypographic)
            Dim size As SizeF
            Dim i As Integer
            i = 0

            Do While (i < numRows)
                size = g.MeasureString(dg(i, col).ToString, dg.Font, 500, sf)
                If (size.Width > width) Then
                    width = size.Width
                End If
                i = (i + 1)

            'dg.TableStyles("PRQ").GridColumnStyles(col).Width = CType(width, Integer)

            dg.TableStyles("PRQ").GridColumnStyles(col).Width = width
        End Sub
    End Class

    Author Comment

    Pls see this link below :
    How do I automatically size (autosize) Columns in a WinForms DataGrid?

    Code which it gives is in C-Sharp
    private void dgLogging_DataSourceChanged(object sender, System.EventArgs e)
                    Type t = dgLogging.GetType();
                    MethodInfo m = t.GetMethod("ColAutoResize",BindingFlags.NonPublic);

                    for (int i = dgLogging.FirstVisibleColumn; (i < dgLogging.VisibleColumnCount); i++)
                        m.Invoke(dgLogging, new object[]{i});
                catch (Exception ex)
                    System.Diagnostics.Trace.Write("Failed Resizing Columns: " + ex.ToString());

    Can you convert this into

    LVL 25

    Expert Comment

    LVL 8

    Expert Comment

    here is everything to explain my example


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
    If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now