Export Numeric Values

PROBLEM

When exporting a RadGridView to Excel, if you have applied a DataFormatString for a certain column of a numeric type, the values are exported as strings.

CAUSE

The content of each cell within the column is treated as string due to the formatting that was applied.

SOLUTION 1

When an element is exported through the ExportToXlsx, ExportToWorkbook or SpreadsheetStreamingExport 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: Handling the ElementExportingToDocument event

private void Grid_ElementExportingToDocument(object sender, GridViewElementExportingToDocumentEventArgs e) 
{ 
    if (e.Element == ExportElement.Cell) 
    { 
        var cellExportingArgs = e as GridViewCellExportingEventArgs; 
        if (cellExportingArgs?.Value == null) 
            return; 
 
        var tryDouble = double.TryParse(cellExportingArgs.Value.ToString(), out var d); 
        if (tryDouble) 
        { 
            var parameters = cellExportingArgs.VisualParameters as GridViewDocumentVisualExportParameters; 
            parameters.Style = new CellSelectionStyle() 
            { 
                Format = new CellValueFormat("0.00") 
            }; 
            cellExportingArgs.Value = d; 
            return; 
        } 
 
        var tryInt = int.TryParse(cellExportingArgs.Value.ToString(), out var i); 
        if (tryInt) 
        { 
            var parameters = cellExportingArgs.VisualParameters as GridViewDocumentVisualExportParameters; 
            parameters.Style = new CellSelectionStyle() 
            { 
                Format = new CellValueFormat("0") 
            }; 
            cellExportingArgs.Value = i; 
            return; 
        } 
    } 
} 
Private Sub Grid_ElementExportingToDocument(ByVal sender As Object, ByVal e As GridViewElementExportingToDocumentEventArgs) 
    If e.Element = ExportElement.Cell Then 
        Dim cellExportingArgs = TryCast(e, GridViewCellExportingEventArgs) 
        If cellExportingArgs?.Value Is Nothing Then 
            Return 
        End If 
 
        Dim tryDouble = Double.TryParse(cellExportingArgs.Value.ToString(), var d) 
        If tryDouble Then 
            Dim parameters = TryCast(cellExportingArgs.VisualParameters, GridViewDocumentVisualExportParameters) 
            parameters.Style = New CellSelectionStyle() With {.Format = New CellValueFormat("0.00")} 
            cellExportingArgs.Value = d 
            Return 
        End If 
 
        Dim tryInt = Integer.TryParse(cellExportingArgs.Value.ToString(), var i) 
        If tryInt Then 
            Dim parameters = TryCast(cellExportingArgs.VisualParameters, GridViewDocumentVisualExportParameters) 
            parameters.Style = New CellSelectionStyle() With {.Format = New CellValueFormat("0")} 
            cellExportingArgs.Value = i 
            Return 
        End If 
    End If 
End Sub 

SOLUTION 2

If you're exporting RadGridView's data with the Export method, you need to handle the ElementExporting event instead as shown in Example 2.

Example 2: Handling the ElementExporting event

private void Grid_ElementExporting(object sender, GridViewElementExportingEventArgs e) 
{ 
    if (e.Element == ExportElement.Cell) 
    { 
        var tryInt = int.TryParse(e.Value.ToString().Replace(",", ""), out var i); 
        if (tryInt) 
        { 
            e.Value = i; 
        } 
 
        var tryDouble = double.TryParse(e.Value.ToString().Replace(",", ""), out var d); 
        if (tryDouble) 
        { 
            e.Value = d; 
        } 
    } 
} 
Private Sub Grid_ElementExporting(ByVal sender As Object, ByVal e As GridViewElementExportingEventArgs) 
    If e.Element = ExportElement.Cell Then 
        Dim tryInt = Integer.TryParse(e.Value.ToString().Replace(",", ""), var i) 
        If tryInt Then 
            e.Value = i 
        End If 
 
        Dim tryDouble = Double.TryParse(e.Value.ToString().Replace(",", ""), var d) 
        If tryDouble Then 
            e.Value = d 
        End If 
    End If 
End Sub 

See Also

In this article
Not finding the help you need? Improve this article