Export to excel (ExcelML format) and numeric formatting

[G]

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.

[Nam]

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;

                        }

                    }

                }

[Annie]

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.