Help with Excel export of currency

[SickPup404]

Good afternoon,

I have the following which nicely formats the exported column to currency (ex. $12,345.60):
((GridBoundColumn)exportGrid.Columns[13]).DataFormatString = "{0:C2}";

When the Excel export is done (BIFF format), the data comes out as text as "$12,345.60".  It looks pretty, but the user wants to be able to perform calculations.

I have changed the text value in ItemDataBound to be preceded with an equal (=), which makes the exported data calculate right, but now the complaint is that it doesn't look like currency.  It shows 12345.6.

Is there a way to get a BIFF export to look like currency and be treated as a numeric for calculations?

Thanks,
Steve

[Daniel]

Hello Steve,

Please try the following:

Enable SuppressColumnDataFormatStrings property. This way RadGrid will remove the ASP.NET format strings and you will be able to apply Excel-specific ones:

<ExportSettings SuppressColumnDataFormatStrings="true"></ExportSettings>

Now let's apply the Excel format strings on the InfrastructureExporting event:

foreach (Row row in a.ExportStructure.Tables[0].Rows)
{
    row.Cells[1, row.Index].Format = "$#,##0.00";
}

Let me know if you need more information.

Regards,
Daniel
Telerik

 

 

[SickPup404]

Perfect!  Thanks very much!