This is a migrated thread and some comments may be shown as answers.

Excel Export - Convert to number message

2 Answers 190 Views
GridView
This is a migrated thread and some comments may be shown as answers.
Chris The Wiz
Top achievements
Rank 1
Chris The Wiz asked on 28 Sep 2015, 11:16 AM

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

 

2 Answers, 1 is accepted

Sort by
0
Chris The Wiz
Top achievements
Rank 1
answered on 28 Sep 2015, 11:20 AM


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‚Äč

0
Dimitar
Telerik team
answered on 29 Sep 2015, 02:24 PM
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:
I hope this will be useful.

Regards,
Dimitar
Telerik
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
Tags
GridView
Asked by
Chris The Wiz
Top achievements
Rank 1
Answers by
Chris The Wiz
Top achievements
Rank 1
Dimitar
Telerik team
Share this question
or