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