Edit this page

Export DateTime Value

PROBLEM

1) When exporting DateTime values without a specified DataFormatString to Excel, the values appear as numbers as shown in Figure 1:

Figure 1: Exporting DateTime values without specified DataFormatString

Exporting DateTime values without specified DataFormatString

2) When exporting DateTime values with specified DataFormatString to Excel, the values appear as strings as shown in Figure 2:

Figure 2: Exporting DateTime values with specified DataFormatString

Exporting DateTime values with specified DataFormatString

CAUSE

1) In most modern programming environments, dates are stored as real numbers. The integer part of the number is the number of days since some agreed-upon date in the past, called the epoch. In Excel, June 16, 2006, for example, is stored as 38884, counting days where January 1st, 1900 is 1.

2) When a DataFormatString has been specified for a given column, RadGridView exports the string representation of the values in that column.

SOLUTION

When an element is exported through the ExportToXlsx or ExportToPdf methods, the arguments of the ElementExportingToDocument event can be used to modify the visual appearance of the exported values and specify how they should be formatted in Excel. This is achieved through the VisualParameters property of the GridViewCellExportingEventArgs.

Example 1 illustrates the approach:

Example 1: Exporting DateTime Values to Excel

this.radGridView.ElementExportingToDocument += (s, e) =>
{
    if (e.Element == ExportElement.Cell)
    {
        var cellExportingArgs = e as GridViewCellExportingEventArgs;
        if ((cellExportingArgs.Column as GridViewDataColumn) == this.radGridView.Columns[1])
        {
            var parameters = cellExportingArgs.VisualParameters as GridViewDocumentVisualExportParameters;
            parameters.Style = new CellSelectionStyle()
            {
                Format = new CellValueFormat("m/d/yyyy")
            };
        }
    }
};
AddHandler Me.radGridView.ElementExportingToDocument, Sub(s, e)
                                                          If e.Element = ExportElement.Cell Then
                                                              Dim cellExportingArgs = TryCast(e, GridViewCellExportingEventArgs)
                                                              If (TryCast(cellExportingArgs.Column, GridViewDataColumn)) Is Me.radGridView.Columns(1) Then
                                                                  Dim parameters = TryCast(cellExportingArgs.VisualParameters, GridViewDocumentVisualExportParameters)
                                                                  parameters.Style = New CellSelectionStyle() With {.Format = New CellValueFormat("m/d/yyyy")}
                                                              End If
                                                          End If
                                                      End Sub

Figure 3 shows the final result in Excel:

Figure 3: Exporting DateTime values with ElementExportingToDocument

Exporting DateTime values with ElementExportingToDocument

See Also