Grid to Excel - modified column not exporting correctly

3 posts, 1 answers
  1. Brendan
    Brendan avatar
    68 posts
    Member since:
    Dec 2011

    Posted 29 May 2013 Link to this post

    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


      
  3. Answer
    Ivan Petrov
    Admin
    Ivan Petrov avatar
    717 posts

    Posted 03 Jun 2013 Link to this post

    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
    RadChart for WinForms is obsolete. Now what?
  5. Brendan
    Brendan avatar
    68 posts
    Member since:
    Dec 2011

    Posted 04 Jun 2013 Link to this post

    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.
Back to Top