DataGridView column configuration VB.NET
Introduction
This article provides the ability to allow columns in a DataGridView to be rearranged plus showing/hiding columns from a modal form which saves their selection to a XML file stored in the application folder. By providing this functionality users of your application may place columns in the order they suits them as each user working within the application. For instance, one user wants contact phone numbers to the right of the contact name while another user does not want to see several columns as they are not important to task they are performing which are different then the first user.
Also shown is freezing columns similarly to freezing columns in Microsoft Excel.
Users may not consider these features until after using the application for sometime after using the application which means the features are left out completely or will be in an update to the application in the future.
Since each configuration file is the name of the DataGridView this means you can store each DataGridView in a separate file rather then be concerned with a relational XML structure. If the decision is made to use one XML file for several DataGridView controls in an application then change the current name from the single DataGridView name to a name of your choice since each record has the DataGridView name and when read into a DataSet a DataGridView is represented by a DataTable. So adding more DataGridView controls with unique names provide the ability to have all of them in one XML file
Since this code is not tied to My.Settings and app.config but instead a stand along XML file one option is to store the XML file in the user’s My Document folder using code as shown in the following Microsoft code sample instead of in the application folder which is how this code sample has been setup.
XML configuration file structure
In the following XML sample file there is one DataGridView named DataGridView1 with several columns, column name, visible, display index.
<?xml version="1.0" standalone="yes"?>
<DataGridViewConfigurations>
<DataGridView1>
<Visible>true</Visible>
<ColumnName>City</ColumnName>
<DisplayIndex>0</DisplayIndex>
</DataGridView1>
<DataGridView1>
<Visible>true</Visible>
<ColumnName>Country</ColumnName>
<DisplayIndex>1</DisplayIndex>
</DataGridView1>
<DataGridView1>
<Visible>true</Visible>
<ColumnName>ContactName</ColumnName>
<DisplayIndex>2</DisplayIndex>
</DataGridView1>
<DataGridView1>
<Visible>true</Visible>
<ColumnName>ContactTitle</ColumnName>
<DisplayIndex>3</DisplayIndex>
</DataGridView1>
<DataGridView1>
<Visible>true</Visible>
<ColumnName>CompanyName</ColumnName>
<DisplayIndex>4</DisplayIndex>
</DataGridView1>
<DataGridView1>
<Visible>true</Visible>
<ColumnName>Phone</ColumnName>
<DisplayIndex>5</DisplayIndex>
</DataGridView1>
<DataGridView1>
<Visible>false</Visible>
<ColumnName>CustomerIdentifier</ColumnName>
<DisplayIndex>6</DisplayIndex>
</DataGridView1>
<DataGridView1>
<Visible>true</Visible>
<ColumnName>Address</ColumnName>
<DisplayIndex>7</DisplayIndex>
</DataGridView1>
<DataGridView1>
<Visible>true</Visible>
<ColumnName>PostalCode</ColumnName>
<DisplayIndex>8</DisplayIndex>
</DataGridView1>
<DataGridView1>
<Visible>true</Visible>
<ColumnName>Fax</ColumnName>
<DisplayIndex>9</DisplayIndex>
</DataGridView1>
</DataGridViewConfigurations>
Configuration class
When creating an instance of this class the intended DataGridView and XML file name is passed into the constructor and set to public properties.
The method InitialRead will read in the file (which was passed into the constructor) if it exists and setup the DataGridView columns. ConfigurationRead method is responsible for setting up a modal form which shows current column display index and Visible property in a DataGridViewCheckBox. Checking the DataGridViewCheckBox will toggle the Visible property of that column. There are two buttons which provide the ability to move the current column left of right.
The Save method accepts the current DataGridView which writes changes back to the XML file.
Imports System.Windows.Forms
Public Class DataGridViewConfiguration
Inherits BaseExceptionsHandler
Public Property XmlFileName As String
Public Property DataGridView() As DataGridView
Public Table As New DataTable
Public FilledFromXml As Boolean
Private _dataSetRootName As String = "DataGridViewConfigurations"
Private _ds As New DataSet With {.DataSetName = _dataSetRootName}
Public Sub New()
End Sub
Public Sub New(pDataGridView As DataGridView, pFileName As String)
DataGridView = pDataGridView
XmlFileName = pFileName
FilledFromXml = False
End Sub
''' <summary>
''' Read columns from the xml file when initially loading a DataGridView
''' </summary>
Public Sub InitialRead()
Try
If IO.File.Exists(XmlFileName) Then
For Each col In DataGridView.Columns.Cast(Of DataGridViewColumn)().ToList
col.Visible = False
Next
Dim ds As New DataSet
ds.ReadXml(XmlFileName)
Dim temp = ds.Tables(DataGridView.Name)
For rowIndex As Integer = 0 To temp.Rows.Count - 1
DataGridView.Columns(CStr(temp.Rows(rowIndex).Item("ColumnName"))).Visible =
CBool(temp.Rows(rowIndex).Item("Visible"))
DataGridView.Columns(CStr(temp.Rows(rowIndex).Item("ColumnName"))).DisplayIndex =
CInt(temp.Rows(rowIndex).Item("DisplayIndex"))
Next
End If
Catch ex As Exception
mHasException = True
mLastException = ex
End Try
End Sub
''' <summary>
''' Read in xml file to populate the DataGridView in DisplayColumns form
''' </summary>
''' <returns></returns>
Public Function ConfigurationRead() As Boolean
mHasException = False
Try
Table.TableName = DataGridView.Name
Table.Columns.AddRange(New DataColumn() _
{
New DataColumn("Visible", GetType(Boolean)),
New DataColumn("ColumnName", GetType(String)),
New DataColumn() With {.ColumnName = "DisplayIndex", .DataType = GetType(Integer)}
})
If IO.File.Exists(XmlFileName) Then
FilledFromXml = True
_ds.ReadXml(XmlFileName)
Dim tempTable = _ds.Tables(DataGridView.Name)
For rowIndex As Integer = 0 To tempTable.Rows.Count - 1
'
' Since a xsd file is not used each value must be type cast
'
Table.Rows.Add(
CBool(tempTable.Rows(rowIndex).Item(0)),
tempTable.Rows(rowIndex).Item(1),
CInt(tempTable.Rows(rowIndex).Item(2)))
Next
Else
If Not FilledFromXml Then
For Each col As DataGridViewColumn In DataGridView.Columns
Table.Rows.Add(New Object() {col.Visible, col.Name})
Next
End If
End If
Catch ex As Exception
mHasException = True
mLastException = ex
End Try
Return IsSuccessFul
End Function
''' <summary>
''' Responsible for saving changes made in DisplayColumns form
''' Apply button
''' </summary>
''' <param name="gridView"></param>
''' <returns></returns>
Public Function Save(ByRef gridView As DataGridView) As Boolean
mHasException = False
Try
For rowIndex As Integer = 0 To Table.Rows.Count - 1
Dim row = Table.Rows(rowIndex)
row.SetField(Of Integer)("DisplayIndex", rowIndex)
gridView.Columns(row.Field(Of String)("ColumnName")).Visible =
row.Field(Of Boolean)("Visible")
If gridView.Columns(row.Field(Of String)("ColumnName")).Visible Then
gridView.Columns(row.Field(Of String)("ColumnName")).DisplayIndex =
row.Field(Of Integer)("DisplayIndex")
End If
Next
Dim ds As New DataSet With {.DataSetName = _dataSetRootName}
ds.Tables.Add(Table.Copy())
ds.WriteXml(XmlFileName)
Catch ex As Exception
mHasException = True
mLastException = ex
End Try
Return IsSuccessFul
End Function
End Class
Usage
In the form which will be configured, create an instance of the class DataGridViewConfiguration passing in the DataGridView instance and the file name for the XML file. Since the instance of this class is needed twice it';s setup as a private variable to the form. The XML file name is also setup as a private variable as it's used also in the setup to display the modal form to allow configuration of the columns for the current DataGridView.
Public Class Form1
Private _dOperations As DataGridViewConfiguration
Private _fileName As String = ""
Then in form load (or form shown) event the InitalRead method is called to setup the column display index and visibility for each column in the DatagridView.
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
_fileName = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, DataGridView1.Name & ".xml")
Dim ops As New DataOperations
_bsCustomers.DataSource = ops.CustomerView
DataGridView1.DataSource = _bsCustomers
_dOperations = New DataGridViewConfiguration(DataGridView1, _fileName)
_dOperations.InitialRead()
If Not _dOperations.IsSuccessFul Then
MessageBox.Show(_dOperations.LastExceptionMessage)
End If
End Sub
To display the modal configuration form from a button click event.
Private Sub cmdShowHide_Click(sender As Object, e As EventArgs) Handles cmdShowHide.Click
Dim f As New frmDisplayColumns(DataGridView1, _fileName)
Try
f.ShowDialog()
Finally
f.Dispose()
End Try
End Sub
Next is the configuration form code which you as a developer do not have to touch. What is important to understand is the move columns left/right have been written into language extensions which can only be used when targeting 3.5 framework or higher.
Imports System.Text
Imports TeamLibrary
Public Class frmDisplayColumns
Private _bsColumns As New BindingSource
Private _gridView As DataGridView
Private _dOperations As New DataGridViewConfiguration
''' <summary>
''' Read in column properties if configuration file exists.
''' </summary>
''' <param name="dgv">DataGridView to configure</param>
Public Sub New(ByRef dgv As DataGridView, pFileName As String)
InitializeComponent()
_gridView = dgv
Text = String.Concat("Columns for ", _gridView.Parent.Name, ".", _gridView.Name)
_dOperations = New DataGridViewConfiguration(dgv, pFileName)
_dOperations.ConfigurationRead()
End Sub
''' <summary>
''' Setup DataGridView with current settings
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
Private Sub DisplayColumns_Shown(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Shown
_bsColumns.DataSource = _dOperations.Table
DataGridView1.DataSource = _bsColumns
DataGridView1.Columns("Visible").HeaderText = "Visible"
DataGridView1.Columns("ColumnName").HeaderText = "Column name"
DataGridView1.Columns("ColumnName").AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
DataGridView1.Columns("ColumnName").ReadOnly = True
DataGridView1.Columns("DisplayIndex").Visible = False
End Sub
''' <summary>
''' Apply settings to DataGridView which was passed in
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
Private Sub cmdApply_Click(ByVal sender As System.Object, ByVal e As EventArgs) Handles cmdApply.Click
_dOperations.Save(_gridView)
End Sub
''' <summary>
''' Set all columns visible = true
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
Private Sub cmdSetAllVisible_Click(sender As Object, e As EventArgs) Handles cmdSetAllVisible.Click
Dim dt As DataTable = CType(_bsColumns.DataSource, DataTable)
For rowIndex As Integer = 0 To dt.Rows.Count - 1
dt.Rows(rowIndex).SetField(Of Boolean)("Visible", True)
Next
End Sub
Private Sub cmdUp_Click(sender As Object, e As EventArgs) Handles cmdUp.Click
DataGridView1.MoveRowUp(_bsColumns)
End Sub
Private Sub cmdDown_Click(sender As Object, e As EventArgs) Handles cmdDown.Click
DataGridView1.MoveRowDown(_bsColumns)
End Sub
Private Sub cmdClose_Click(ByVal sender As System.Object, ByVal e As EventArgs) Handles cmdClose.Click
Close()
End Sub
End Class
All functionality to rig up the above functionality is to add the included team project into your solution and reference the classes in your form(s).
The last thing to discuss is freezing columns. This is done by simply selecting a column in the DataGridView and setting it's Frozen property to True or False as shown in code which is included in the source code for this article.
Private Sub FrozenToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles FrozenToolStripMenuItem.Click
If FrozenToolStripMenuItem.Text = "Freeze" Then
For i As Integer = 0 To DataGridView1.Columns.Count - 1
DataGridView1.Columns(i).Frozen = False
Next
DataGridView1.Columns(DataGridView1.CurrentCell.ColumnIndex).Frozen = True
Else
DataGridView1.Columns(DataGridView1.CurrentCell.ColumnIndex).Frozen = False
End If
End Sub
There is an issue which needs to be addressed when dealing with reordering columns when there is one or more frozen columns, reordering is not permitted. To get around this a check is needed to determine if there are any frozen columns.
To handle this a private variable is added to the DisplayColumns form, _hasFrozenColumns. In the form constructor a check is done to determine if there is at least one frozen column. Note My.Dialogs.Question is a custom My.Namespace included in the source code.
_hasFrozenColumns = _gridView.Columns.Cast(Of DataGridViewColumn).
Any(Function(col) col.Frozen)
Then in form Shown event (the code is placed here as it's best practice to show a message while the current form is showing rather than before it displays so not to confuse the user)
If _hasFrozenColumns Then
If My.Dialogs.Question("Can not reorder columns with frozen columns, would you like unfreeze the columns?") Then
For colIndex As Integer = 0 To _dOperations.DataGridView.Columns.Count - 1
_dOperations.DataGridView.Columns(colIndex).Frozen = False
Next
Else
cmdApply.Enabled = False
cmdSetAllVisible.Enabled = False
End If
End If
If the user pressed enter, the Apply and All Visible buttons are disabled as the Question method defaults to No rather than Yes as with MessageBox. The behavior can be changed or an overloaded method for Question can be added which permits the developer to select the Yes button rather than the No button.
Requirements for running the code for this article.
- Data is read into the DataGridview from a SQL-Server database. This mean you need to have a least SQL-Server Express installed. In the main project locate script.sql to create the database and populate the table which is used to populate our DataGridView.
- Change the default catalog in DataOperations from KARENS-PC to your SQL Server name or .\SQLEXPRESS.
- If you are targeting a framework less then 4.6.1 change your project's framework version or change the framework version for team library class project.
Source code repository
https://github.com/karenpayneoregon/DataGridViewConfiguration
See also
DataGridView control
Freeze Columns in the Windows Forms DataGridView Control
Summary
This article and code sample provides easy method to provide the ability for users to maintain column order and visibility of their view into specific data displayed in DataGridView controls in any of your solutions.