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

Export to excel (ExcelML format) and numeric formatting

2 Answers 142 Views
GridView
This is a migrated thread and some comments may be shown as answers.
G
Top achievements
Rank 1
G asked on 20 Sep 2010, 05:11 PM
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 Answers, 1 is accepted

Sort by
0
Nam
Top achievements
Rank 1
answered on 09 Dec 2010, 11:01 PM
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;
                        }
                    }
                }
0
Annie
Top achievements
Rank 1
answered on 05 Dec 2011, 08:01 PM
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.
Tags
GridView
Asked by
G
Top achievements
Rank 1
Answers by
Nam
Top achievements
Rank 1
Annie
Top achievements
Rank 1
Share this question
or