?
Solved

Datagridview format column

Posted on 2014-12-01
5
Medium Priority
?
221 Views
Last Modified: 2014-12-02
Hi Experts,

I have a bound datagridview. One of the column is displaying the date. Now the date is displaying as a string("20140801" ).  I want to display it as mm/dd/yyyy.  I also have a time (132312). I want to display in HH:MM:SS format. How can I format the columns.

Thanks.

Rina
0
Comment
Question by:RadhaKrishnaKiJaya
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 34

Expert Comment

by:it_saige
ID: 40474437
Use the CellFormatting Event:

Form1.vb -
Imports System.IO
Imports System.ComponentModel
Imports System.Reflection
Imports System.Globalization

Public Class Form1
	Private DataFile As New FileInfo("Products.xml")
	Private ReadOnly Products As New SortableBindingList(Of Product)()
	Private Binding As New BindingSource()

	Private Sub OnCellValidating(ByVal sender As Object, ByVal e As DataGridViewCellValidatingEventArgs) Handles DataGridView1.CellValidating
		DataGridView1.Rows(e.RowIndex).ErrorText = String.Empty
		If DataGridView1.Rows(e.RowIndex).IsNewRow Then Return

		If DataGridView1.Columns(e.ColumnIndex).Name = "colQuantity" Then
			Dim tempQuantity As Integer = -1
			If Not Integer.TryParse(e.FormattedValue, tempQuantity) Then
				e.Cancel = True
				DataGridView1.Rows(e.RowIndex).ErrorText = "Invalid quantity entered.  The value entered must be an integer."
			End If
		End If
	End Sub

	Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
		If DataFile.Exists Then
			ProductsDataSet.ReadXml(DataFile.FullName)
			For Each table As DataTable In ProductsDataSet.Tables
				For Each row As DataRow In table.Rows
					Products.Add(New Product(row("ID"), row("Name"), row("Cost"), row("CreateDate"), row("CreateTime")))
				Next
			Next

			Binding.DataSource = Products
			DataGridView1.DataSource = Binding
		End If
	End Sub

	Private Sub OnColumnHeaderMouseClick(ByVal sender As Object, ByVal e As DataGridViewCellMouseEventArgs) Handles DataGridView1.ColumnHeaderMouseClick
		Dim newColumn As DataGridViewColumn = DataGridView1.Columns(e.ColumnIndex)
		Dim oldColumn As DataGridViewColumn = DataGridView1.SortedColumn
		Dim direction As ListSortDirection

		' If oldColumn is null, then the DataGridView is not currently sorted. 
		If oldColumn IsNot Nothing Then

			' Sort the same column again, reversing the SortOrder. 
			If oldColumn Is newColumn AndAlso DataGridView1.SortOrder = _
			    SortOrder.Ascending Then
				direction = ListSortDirection.Descending
			Else

				' Sort a new column and remove the old SortGlyph.
				direction = ListSortDirection.Ascending
				oldColumn.HeaderCell.SortGlyphDirection = SortOrder.None
			End If
		Else
			direction = ListSortDirection.Ascending
		End If

		' Sort the selected column.
		DataGridView1.Sort(newColumn, direction)
		If direction = ListSortDirection.Ascending Then
			newColumn.HeaderCell.SortGlyphDirection = SortOrder.Ascending
		Else
			newColumn.HeaderCell.SortGlyphDirection = SortOrder.Descending
		End If
	End Sub

	Private Sub OnDataBindingComplete(ByVal sender As Object, ByVal e As DataGridViewBindingCompleteEventArgs) Handles DataGridView1.DataBindingComplete
		' Put each of the columns into programmatic sort mode. 
		For Each column As DataGridViewColumn In DataGridView1.Columns
			column.SortMode = DataGridViewColumnSortMode.Programmatic
		Next
	End Sub

	Private Sub OnCellFormatting(ByVal sender As Object, ByVal e As DataGridViewCellFormattingEventArgs) Handles DataGridView1.CellFormatting
		DataGridView1.Rows(e.RowIndex).ErrorText = String.Empty
		If DataGridView1.Rows(e.RowIndex).IsNewRow Then Return

		If DataGridView1.Columns(e.ColumnIndex).Name = "colCreateDate" Then
			Dim tempDate As DateTime = DateTime.MinValue
			If DateTime.TryParseExact(e.Value, "yyyyMMdd", CultureInfo.InvariantCulture, Globalization.DateTimeStyles.None, tempDate) Then
				e.Value = tempDate.ToString("MM/dd/yyyy")
				e.FormattingApplied = True
			Else
				DataGridView1.Rows(e.RowIndex).ErrorText = "Invalid date value for create date."
				e.FormattingApplied = False
			End If
		ElseIf DataGridView1.Columns(e.ColumnIndex).Name = "colCreateTime" Then
			Dim tempTime As DateTime = DateTime.MinValue
			If DateTime.TryParseExact(e.Value, "HHmmss", CultureInfo.InvariantCulture, Globalization.DateTimeStyles.None, tempTime) Then
				e.Value = tempTime.ToString("HH:mm:ss")
				e.FormattingApplied = True
			Else
				DataGridView1.Rows(e.RowIndex).ErrorText = "Invalid date value for create date."
				e.FormattingApplied = False
			End If
		End If
	End Sub
End Class

Public Class Product
	Private ReadOnly _ID As Integer
	Private ReadOnly _Name As String
	Private ReadOnly _Cost As Decimal
	Private ReadOnly _CreateDate As String
	Private ReadOnly _CreateTime As String

	Public ReadOnly Property ID() As Integer
		Get
			Return _ID
		End Get
	End Property

	Public ReadOnly Property Name() As String
		Get
			Return _Name
		End Get
	End Property

	Public ReadOnly Property Cost() As Decimal
		Get
			Return _Cost
		End Get
	End Property

	Public ReadOnly Property CreateDate() As String
		Get
			Return _CreateDate
		End Get
	End Property

	Public ReadOnly Property CreateTime() As String
		Get
			Return _CreateTime
		End Get
	End Property

	Public Property Quantity() As Integer

	Public ReadOnly Property Total() As Decimal
		Get
			Return (_Cost * Quantity)
		End Get
	End Property

	Public Sub New(ByVal ID As Integer, ByVal Name As String, ByVal Cost As Decimal, ByVal CreateDate As String, ByVal CreateTime As String)
		_ID = ID
		_Name = Name
		_Cost = Cost
		_CreateDate = CreateDate
		_CreateTime = CreateTime
	End Sub
End Class

Public Class SortableBindingList(Of T)
	Inherits BindingList(Of T)
	Private Property IsSorted As Boolean
	Private Property SortDirection As ListSortDirection
	Private Property SortProperty As PropertyDescriptor

	Protected Overrides ReadOnly Property SupportsSortingCore() As Boolean
		Get
			Return True
		End Get
	End Property

	Protected Overrides ReadOnly Property SortDirectionCore() As ListSortDirection
		Get
			Return _SortDirection
		End Get
	End Property

	Protected Overrides ReadOnly Property SortPropertyCore() As PropertyDescriptor
		Get
			Return _SortProperty
		End Get
	End Property

	Protected Overrides Sub ApplySortCore(ByVal [property] As PropertyDescriptor, ByVal direction As ListSortDirection)
		Dim items As List(Of T) = TryCast(Me.Items, List(Of T))
		If items Is Nothing Then
			IsSorted = False
		Else
			Dim comparer As New PropertyCompare(Of T)([property].Name, direction)
			items.Sort(comparer)
			IsSorted = True
			SortDirection = direction
			SortProperty = [property]
		End If
		OnListChanged(New ListChangedEventArgs(ListChangedType.Reset, -1))
	End Sub

	Protected Overrides ReadOnly Property IsSortedCore() As Boolean
		Get
			Return _IsSorted
		End Get
	End Property

	Protected Overrides Sub RemoveSortCore()
		_IsSorted = False
	End Sub

	Sub New(ByVal list As ICollection(Of T))
		MyBase.New(CType(list, IList(Of T)))
	End Sub

	Sub New()
		MyBase.New()
	End Sub

	Private Class PropertyCompare(Of T)
		Implements IComparer(Of T)

		Private Property PropertyInfo() As PropertyInfo
		Private Property SortDirection() As ListSortDirection
		Friend Sub New(ByVal [property] As String, ByVal direction As ListSortDirection)
			PropertyInfo = GetType(T).GetProperty([property])
			SortDirection = direction
		End Sub

		Friend Function Compare(ByVal x As T, ByVal y As T) As Integer Implements IComparer(Of T).Compare
			Return If(SortDirection = ListSortDirection.Ascending,
			  Comparer.[Default].Compare(PropertyInfo.GetValue(x, Nothing), PropertyInfo.GetValue(y, Nothing)),
			  Comparer.[Default].Compare(PropertyInfo.GetValue(y, Nothing), PropertyInfo.GetValue(x, Nothing)))
		End Function
	End Class
End Class

Open in new window


Form1.Designer.vb -
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Partial Class Form1
	Inherits System.Windows.Forms.Form

	'Form overrides dispose to clean up the component list.
	<System.Diagnostics.DebuggerNonUserCode()> _
	Protected Overrides Sub Dispose(ByVal disposing As Boolean)
		Try
			If disposing AndAlso components IsNot Nothing Then
				components.Dispose()
			End If
		Finally
			MyBase.Dispose(disposing)
		End Try
	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.
	<System.Diagnostics.DebuggerStepThrough()> _
	Private Sub InitializeComponent()
		Me.Button1 = New System.Windows.Forms.Button()
		Me.DataGridView1 = New System.Windows.Forms.DataGridView()
		Me.ProductsDataSet = New System.Data.DataSet()
		Me.colID = New System.Windows.Forms.DataGridViewTextBoxColumn()
		Me.colName = New System.Windows.Forms.DataGridViewTextBoxColumn()
		Me.colUnitCost = New System.Windows.Forms.DataGridViewTextBoxColumn()
		Me.colQuantity = New System.Windows.Forms.DataGridViewTextBoxColumn()
		Me.colTotal = New System.Windows.Forms.DataGridViewTextBoxColumn()
		Me.colCreateDate = New System.Windows.Forms.DataGridViewTextBoxColumn()
		Me.colCreateTime = New System.Windows.Forms.DataGridViewTextBoxColumn()
		CType(Me.DataGridView1, System.ComponentModel.ISupportInitialize).BeginInit()
		CType(Me.ProductsDataSet, System.ComponentModel.ISupportInitialize).BeginInit()
		Me.SuspendLayout()
		'
		'Button1
		'
		Me.Button1.Location = New System.Drawing.Point(447, 227)
		Me.Button1.Name = "Button1"
		Me.Button1.Size = New System.Drawing.Size(75, 23)
		Me.Button1.TabIndex = 0
		Me.Button1.Text = "Button1"
		Me.Button1.UseVisualStyleBackColor = True
		'
		'DataGridView1
		'
		Me.DataGridView1.AllowUserToAddRows = False
		Me.DataGridView1.AllowUserToDeleteRows = False
		Me.DataGridView1.AllowUserToOrderColumns = True
		Me.DataGridView1.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize
		Me.DataGridView1.Columns.AddRange(New System.Windows.Forms.DataGridViewColumn() {Me.colID, Me.colName, Me.colUnitCost, Me.colQuantity, Me.colTotal, Me.colCreateDate, Me.colCreateTime})
		Me.DataGridView1.Location = New System.Drawing.Point(13, 13)
		Me.DataGridView1.Name = "DataGridView1"
		Me.DataGridView1.Size = New System.Drawing.Size(509, 208)
		Me.DataGridView1.TabIndex = 1
		'
		'ProductsDataSet
		'
		Me.ProductsDataSet.DataSetName = "NewDataSet"
		'
		'colID
		'
		Me.colID.DataPropertyName = "ID"
		Me.colID.HeaderText = "ID"
		Me.colID.Name = "colID"
		Me.colID.Visible = False
		Me.colID.Width = 5
		'
		'colName
		'
		Me.colName.AutoSizeMode = System.Windows.Forms.DataGridViewAutoSizeColumnMode.Fill
		Me.colName.DataPropertyName = "Name"
		Me.colName.HeaderText = "Product Name"
		Me.colName.Name = "colName"
		'
		'colUnitCost
		'
		Me.colUnitCost.DataPropertyName = "Cost"
		Me.colUnitCost.HeaderText = "Unit Cost"
		Me.colUnitCost.Name = "colUnitCost"
		Me.colUnitCost.Width = 50
		'
		'colQuantity
		'
		Me.colQuantity.DataPropertyName = "Quantity"
		Me.colQuantity.HeaderText = "Quantity"
		Me.colQuantity.Name = "colQuantity"
		Me.colQuantity.Width = 50
		'
		'colTotal
		'
		Me.colTotal.DataPropertyName = "Total"
		Me.colTotal.HeaderText = "Total"
		Me.colTotal.Name = "colTotal"
		Me.colTotal.Width = 50
		'
		'colCreateDate
		'
		Me.colCreateDate.DataPropertyName = "CreateDate"
		Me.colCreateDate.HeaderText = "Created Date"
		Me.colCreateDate.Name = "colCreateDate"
		Me.colCreateDate.ReadOnly = True
		Me.colCreateDate.Width = 70
		'
		'colCreateTime
		'
		Me.colCreateTime.DataPropertyName = "CreateTime"
		Me.colCreateTime.HeaderText = "Created Time"
		Me.colCreateTime.Name = "colCreateTime"
		Me.colCreateTime.ReadOnly = True
		Me.colCreateTime.Width = 70
		'
		'Form1
		'
		Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)
		Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
		Me.ClientSize = New System.Drawing.Size(534, 262)
		Me.Controls.Add(Me.DataGridView1)
		Me.Controls.Add(Me.Button1)
		Me.Name = "Form1"
		Me.Text = "Form1"
		CType(Me.DataGridView1, System.ComponentModel.ISupportInitialize).EndInit()
		CType(Me.ProductsDataSet, System.ComponentModel.ISupportInitialize).EndInit()
		Me.ResumeLayout(False)

	End Sub
	Friend WithEvents Button1 As System.Windows.Forms.Button
	Friend WithEvents DataGridView1 As System.Windows.Forms.DataGridView
	Friend WithEvents ProductsDataSet As System.Data.DataSet
	Friend WithEvents colID As System.Windows.Forms.DataGridViewTextBoxColumn
	Friend WithEvents colName As System.Windows.Forms.DataGridViewTextBoxColumn
	Friend WithEvents colUnitCost As System.Windows.Forms.DataGridViewTextBoxColumn
	Friend WithEvents colQuantity As System.Windows.Forms.DataGridViewTextBoxColumn
	Friend WithEvents colTotal As System.Windows.Forms.DataGridViewTextBoxColumn
	Friend WithEvents colCreateDate As System.Windows.Forms.DataGridViewTextBoxColumn
	Friend WithEvents colCreateTime As System.Windows.Forms.DataGridViewTextBoxColumn

End Class

Open in new window


Products.xml -
<?xml version="1.0" encoding="utf-8" ?>
<Products>
	<Product>
		<ID>1</ID>
		<Name>Toothpaste</Name>
		<Cost>2.99</Cost>
		<CreateDate>20140801</CreateDate>
		<CreateTime>132312</CreateTime>
	</Product>
	<Product>
		<ID>2</ID>
		<Name>Cough Syrup</Name>
		<Cost>5.49</Cost>
		<CreateDate>20130730</CreateDate>
		<CreateTime>152218</CreateTime>
	</Product>
	<Product>
		<ID>3</ID>
		<Name>Deodorant</Name>
		<Cost>3.29</Cost>
		<CreateDate>20120621</CreateDate>
		<CreateTime>172124</CreateTime>
	</Product>
	<Product>
		<ID>4</ID>
		<Name>Bar Soap</Name>
		<Cost>1.19</Cost>
		<CreateDate>20110511</CreateDate>
		<CreateTime>192030</CreateTime>
	</Product>
	<Product>
		<ID>5</ID>
		<Name>Liquid Soap</Name>
		<Cost>3.49</Cost>
		<CreateDate>20100401</CreateDate>
		<CreateTime>211936</CreateTime>
	</Product>
</Products>

Open in new window


Set products to always copy to output directory -Capture.JPG
Produces the following output -Capture.JPG
-saige-
0
 

Author Comment

by:RadhaKrishnaKiJaya
ID: 40474851
Dim out_date As String = dgvStoreResult.Rows(i).Cells(1).Value
                                dgvStoreResult.Rows(i).Cells(1).Value = Format_String_ToDate(out_date)

                                Dim out_time As String = dgvStoreResult.Rows(i).Cells(2).Value
                                dgvStoreResult.Rows(i).Cells(2).Value = Format_String_ToTime(out_time)


 Public Shared Function Format_String_ToTime(stk_Time As String) As String
        If stk_Time.Trim <> "" Then
            If stk_Time.Trim.Length <> 6 Then
                stk_Time = Format_String(stk_Time.Trim, 6)



Public Shared Function Format_String_ToDate(stk_Date As String) As String
        If stk_Date.Trim <> "" Then
            If stk_Date.Trim.Length <> 8 Then
                stk_Date = Format_String(stk_Date.Trim, 8)
            End If
            Dim yy As String = stk_Date.Substring(0, 4)
            Dim mm As String = stk_Date.Substring(4, 2)
            Dim dd As String = stk_Date.Substring(6, 2)
            stk_Date = mm & "/" & dd & "/" & yy
            Format_String_ToDate = stk_Date
        Else
            Format_String_ToDate = ""
        End If
    End Function
            End If
            stk_Time = stk_Time.Substring(0, 2) + ":" + stk_Time.Substring(2, 2) + ":" + stk_Time.Substring(4, 2)
            Format_String_ToTime = stk_Time
        Else
            Format_String_ToTime = ""
        End If
    End Function
0
 

Author Comment

by:RadhaKrishnaKiJaya
ID: 40476649
I've requested that this question be closed as follows:

Accepted answer: 0 points for RadhaKrishnaKiJaya's comment #a40474851

for the following reason:

I found the solution.
0
 
LVL 34

Expert Comment

by:it_saige
ID: 40474872
I don't know if this is a follow-up question or a code review???  Here is what I did in my CellFormatting Event and how you would probably want to format it for reuse in your code:
Private Sub OnCellFormatting(ByVal sender As Object, ByVal e As DataGridViewCellFormattingEventArgs) Handles dgvStoreResult.CellFormatting
	dgvStoreResult.Rows(e.RowIndex).ErrorText = String.Empty
	If dgvStoreResult.Rows(e.RowIndex).IsNewRow Then Return

	If dgvStoreResult.Columns(e.ColumnIndex).Name = "colCreateDate" Then
		Dim tempDate As DateTime = DateTime.MinValue
		If DateTime.TryParseExact(e.Value, "yyyyMMdd", CultureInfo.InvariantCulture, Globalization.DateTimeStyles.None, tempDate) Then
			e.Value = tempDate.ToString("MM/dd/yyyy")
			e.FormattingApplied = True
		Else
			dgvStoreResult.Rows(e.RowIndex).ErrorText = "Invalid date value for stock date."
			e.FormattingApplied = False
		End If
	ElseIf dgvStoreResult.Columns(e.ColumnIndex).Name = "colCreateTime" Then
		Dim tempTime As DateTime = DateTime.MinValue
		If DateTime.TryParseExact(e.Value, "HHmmss", CultureInfo.InvariantCulture, Globalization.DateTimeStyles.None, tempTime) Then
			e.Value = tempTime.ToString("HH:mm:ss")
			e.FormattingApplied = True
		Else
			dgvStoreResult.Rows(e.RowIndex).ErrorText = "Invalid time value for stock time."
			e.FormattingApplied = False
		End If
	End If
End Sub

Open in new window


Just modify colCreateDate and colCreateTime to be the Name of the columns in your datagrid for the Date and Time columns.

-saige-
0
 
LVL 34

Accepted Solution

by:
it_saige earned 2000 total points
ID: 40474919
@Radha, just want some clarification, was there some specific problem with the solution that I had recommended?

-saige-
0

Featured Post

Containers & Docker to Create a Powerful Team

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

770 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