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;
}
}
}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;
}
}
}