Grid to Excel - modified column not exporting correctly

[Brendan]

I am returning bound data to a RadGridView via a DataTable.  In the process I need to modify one of the columns which is contains a timestamp value (seconds from midnight) to display time.  The code below shows where I intercept the column in the CellFormatting event and convert the timestamp value to a DateSerial, which I then set the CellElement.Text to. 

Based on this function the grid displays the timestamp exactly as I want - ie: 44233 is displayed correctly as 12:17:13 PM in the grid.  However, when export the grid to Excel, the timestamp value (44233) is exported and not the new CellElement.Text.

Am I doing something wrong or can I modify the column via the exporter before it gets into Excel?  Thanks

Private Sub grdAccessLogs_CellFormatting(sender As Object, e As CellFormattingEventArgs) Handles grdAuditLogs.CellFormatting

          Dim sCol As String = e.CellElement.ColumnInfo.Name.ToUpper

         'format timestamp column

        If sCol.IndexOf("TYPE=TS") > 0 Then

            Dim sTs As String = e.CellElement.Text

            If e.CellElement.Text.IndexOf(":") = -1 Then e.CellElement.Text = fConvertSysTime(sTs).ToString

        End If

End Sub

 

Function fConvertSysTime(lTime As Integer) As String

  

    Dim lHours As Integer

    Dim lMinutes As Integer

    Dim lSeconds As Integer

  

    Try

        'truncate decimals - only want full hours

        lHours = Int(lTime / 3600)

        lMinutes = Int((lTime - lHours * 3600) / 60)

        lSeconds = Int(lTime - lHours * 3600 - lMinutes * 60)

  

        'build time string

        Return TimeSerial(lHours, lMinutes, lSeconds)

    Catch e As Exception

        Return ""

    End Try

End Function

[Ivan Petrov]

Hello Brendan,

Thank you for writing.

As a general rule it is not advisable to use the CellFormatting event for data conversion. The formatting event is intended for UI customization. In your case a better approach would be to use RadGridView data conversion layer. This layer stands between the data layer of the grid and the UI layer and is intended to handle this type of conversions.

On the export you should change the value of the exported cell in the ЕxcelCellFormatting. Here is a code snippet which demonstrates how to achieve this:

Private Sub exporter_ExcelCellFormatting(sender As Object, e As ExcelCellFormattingEventArgs)
    Dim col As GridViewDataColumn = TryCast(e.GridCellInfo.ColumnInfo, GridViewDataColumn)
    If col.Name.ToUpper() = "TYPE=TS" AndAlso TypeOf col.DataTypeConverter Is MyTypeConverter Then
        e.ExcelCellElement.Data.DataItem = col.DataTypeConverter.ConvertTo(e.GridCellInfo.Value, (GetType(String))).ToString()
    End If
End Sub

I hope this will be useful. Should you have further questions, I would be glad to help.

Regards,
Ivan Petrov
Telerik

[Brendan]

Thanks Ivan.  I opted for a simpler method as I couldn't fully understand or get yours to work (Excel reported it didn't support Datetime objects).

After setting the Grid datasource I set the TimeStamp column;

    col.ExcelExportType = DisplayFormatType.LongTime

I then calc'd the timestamp as a fraction of a day;

    If col.Name.ToUpper.IndexOf("TYPE=TS") > 0 And IsNumeric(e.GridCellInfo.Value) Then 'convert time stamp
        e.ExcelCellElement.Data.DataItem = e.GridCellInfo.Value / 86400
    End If

Excel and the grid show the date correctly.  Thanks for the ideas.