Export to excel (ExcelML format) and numeric formatting

3 posts, 0 answers
  1. G
    G avatar
    14 posts
    Member since:
    May 2009

    Posted 20 Sep 2010 Link to this post

    Hi,

    Using version 2010.2.812.1030 with SL3.

    When exporting numeric values (Decimals formatted using DataFormatString="{} {0:F2}" ) the format is honoured but when the file is opened in Excel the numeric column shows as being 'Stored as Text' and is left justified.

    How do I force it to store the value as a number with the correct formatting on it?

    With all the data 'stored as text' the sorting and autosum functions etc won't work correctly until the column is converted to a numeric format.

    Hopefully there is a quick fix for this. I've read through all forum posts and the help section on exporting and formatting without any luck.

    Thanks for your time.
  2. Nam
    Nam avatar
    1 posts
    Member since:
    Dec 2010

    Posted 09 Dec 2010 Link to this post

    Has anybody answered this yet?

    I have encountered the same problem where I wanted the grid to display numbers with 2 decimal places {0:N2} and in doing so, the export to excelML now has these values as Text. 


    **Edit:

    This worked for me

    private void _BaseRadGrid_ElementExporting(object sender, GridViewElementExportingEventArgs e)
            {
                try
                {
                    if (e.Element == ExportElement.Cell)
                    {
                        if ((e.Context as GridViewDataColumn).Header.ToString().Contains("Usage"))
                        {
                            decimal oUsage;
                            var remove = decimal.TryParse(e.Value.ToString().Replace(",", ""), out oUsage);
                            if (remove)
                            {
                                e.Value = oUsage;
                            }
                        }
                    }
    private void _BaseRadGrid_ElementExporting(object sender, GridViewElementExportingEventArgs e)
            {
                try
                {
                    if (e.Element == ExportElement.Cell)
                    {
                        if ((e.Context as GridViewDataColumn).Header.ToString().Contains("Usage"))
                        {
                            decimal oUsage;
                            var remove = decimal.TryParse(e.Value.ToString().Replace(",", ""), out oUsage);
                            if (remove)
                            {
                                e.Value = oUsage;
                            }
                        }
                    }
    private void _BaseRadGrid_ElementExporting(object sender, GridViewElementExportingEventArgs e)
            {
                try
                {
                    if (e.Element == ExportElement.Cell)
                    {
                        if ((e.Context as GridViewDataColumn).Header.ToString().Contains("Usage"))
                        {
                            decimal oUsage;
                            var remove = decimal.TryParse(e.Value.ToString().Replace(",", ""), out oUsage);
                            if (remove)
                            {
                                e.Value = oUsage;
                            }
                        }
                    }
    private void _BaseRadGrid_ElementExporting(object sender, GridViewElementExportingEventArgs e)
            {
                try
                {
                    if (e.Element == ExportElement.Cell)
                    {
                        if ((e.Context as GridViewDataColumn).Header.ToString().Contains("Usage"))
                        {
                            decimal oUsage;
                            var remove = decimal.TryParse(e.Value.ToString().Replace(",", ""), out oUsage);
                            if (remove)
                            {
                                e.Value = oUsage;
                            }
                        }
                    }
    private void _BaseRadGrid_ElementExporting(object sender, GridViewElementExportingEventArgs e)
            {
                try
                {
                    if (e.Element == ExportElement.Cell)
                    {
                        if ((e.Context as GridViewDataColumn).Header.ToString().Contains("Usage"))
                        {
                            decimal oUsage;
                            var remove = decimal.TryParse(e.Value.ToString().Replace(",", ""), out oUsage);
                            if (remove)
                            {
                                e.Value = oUsage;
                            }
                        }
                    }

    private void _BaseRadGrid_ElementExporting(object sender, GridViewElementExportingEventArgs e)
    {
          if (e.Element == ExportElement.Cell)
          {
             if ((e.Context as GridViewDataColumn).Header.ToString().Contains("Usage"))
             {
                 decimal oUsage;
                 var remove = decimal.TryParse(e.Value.ToString().Replace(",", ""), out oUsage);
                 if (remove)
                 {
                     e.Value = oUsage;
                 }
             }
          }
    }

    if (e.Element == ExportElement.Cell)
                    {
                        if ((e.Context as GridViewDataColumn).Header.ToString().Contains("Usage"))
                        {
                            decimal oUsage;
                            var remove = decimal.TryParse(e.Value.ToString().Replace(",", ""), out oUsage);
                            if (remove)
                            {
                                e.Value = oUsage;
                            }
                        }
                    }
  3. Annie
    Annie avatar
    1 posts
    Member since:
    May 2011

    Posted 05 Dec 2011 Link to this post

    Thanks for posting this, Nam.

    Would you happen to know, or does anyone know, why this trick wouldn't work for the footer cells?  I have very basic total sums in the footer, and this trick is ignored.
Back to Top