Excel Export - Convert to number message

3 posts, 0 answers
  1. Chris The Wiz
    Chris The Wiz avatar
    2 posts
    Member since:
    Oct 2006

    Posted 28 Sep 2015 Link to this post

    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. Chris The Wiz
    Chris The Wiz avatar
    2 posts
    Member since:
    Oct 2006

    Posted 28 Sep 2015 Link to this post


    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

  3. UI for WinForms is Visual Studio 2017 Ready
  4. Dimitar
    Admin
    Dimitar avatar
    1404 posts

    Posted 29 Sep 2015 Link to this post

    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
Back to Top