Excel Export - Convert to number message

[Chris The Wiz]

Don't know if this has been mentioned before but I'll share my solution anyway.

The issue is when you use the ExportToML on a RadGridView and the resulting excel-file gives you the little green triangle on every cell. Where it says "the number in the cell is formatted as text or preceded by an apostrophe".   The yellow mark gives you the option to "convert to number" before you can use the values as expected.  Highly annoying if you ask me.

Solution

If you google this you get the standard solution of using ExcelCellFormatting event and change

the property of: 

•  e.ExeclStyleElement.NumberFormat.FormatType
•  e.ExeclStyleElement.NumberFormat.FormatString.

This however doesn't solve the problem. The solution is not the visual style export, but in the data.

•  e.ExcelCellElement.Data.DataType = DataType.Number;
• e.ExcelCellElement.Data.DataItem = old;

 

 

var exporter = new ExportToExcelML(grid) {SheetName = sheetname, ExportVisualSettings = true};
exporter.ExcelCellFormatting += exporter_ExcelCellFormatting;
exporter.RunExport(tempPath);

private static void exporter_ExcelCellFormatting(object sender, ExcelCellFormattingEventArgs e)
{
    if (!e.GridCellInfo.ColumnInfo.Name.StartsWith("v.")) return;

    if (e.GridCellInfo.Value == null) return;

    e.ExcelStyleElement.NumberFormat.FormatType = DisplayFormatType.Custom;
    e.ExcelStyleElement.NumberFormat.FormatString = "#";

    int old;
    if ( int.TryParse(e.GridCellInfo.Value.ToString().Replace(",",""), out old))
    {
       e.ExcelCellElement.Data.DataType = DataType.Number;
        e.ExcelCellElement.Data.DataItem = old;
    }
}

 

[Chris The Wiz]

Convert Numbers stored as text to numbers

As a reference, this is the MS office support site.

And the official Telerik hints it here: 

Export to Excel via ExcelML format​

[Dimitar]

Hello Chris,

Thank you for writing.

The data type is taken from the grid data source and indeed if you have a text field with numbers, it will be exported as text. However, if you want you can explicitly set the export type with the ExcelExportType property of the column: 

radGridView1.Columns[0].ExcelExportType = DisplayFormatType.Fixed;

In addition, I want to mention that now you can use the new speed export functionality where you have better control on the exported file and you can even modify it directly in the code. Detailed information is available here:

• Spread export
• Spread Processing

I hope this will be useful.

Regards,
Dimitar
Telerik