Infragistics UltraGrid - Not exporting formulas to Excel

Question

I have a very simple test App with an Infragistics UltraGrid which I have populated with some currency values.

UltraDataSource dataSource = new UltraDataSource();
dataSource.Band.Columns.Add("Cost1", typeof(double));
dataSource.Band.Columns.Add("Cost2", typeof(double));
dataSource.Band.Columns.Add("Cost3", typeof(double));

var dataRow = dataSource.Rows.Add();
dataRow["Cost1"] = 10.50;
dataRow["Cost2"] = 12.30;
dataRow["Cost3"] = 14.96;

gridResults.DataSource = dataSource;
gridResults.DataBind();

gridResults.DisplayLayout.Bands[0].Columns[1].Format = "C";
gridResults.DisplayLayout.Bands[0].Columns[2].Format = "C";
gridResults.DisplayLayout.Bands[0].Columns[3].Format = "C";

I actually want o use a custom format string of '$0.00' as my currency may be different in different columns but for simplicity I have stayed with the generic C format string. I then export the Grid to MS Excel using the UltraGridExcelExporter object as follows.

UltraGridExcelExporter exporter = new UltraGridExcelExporter();
exporter.ExportFormulas = true;
exporter.Export(gridResults, filename);

The display on screen is as I expect with the currency symbol and the value formatted correctly however when I look at the Excel file generated, the values are displayed as standard numeric with no formatting carried over. I have checked the docs and the default is to export formatting but at the moment I am at a loss as to what I am doing wrong.

Answer 1

What you can do is handle UltraGridExcelExporter InitializeColumn event. In this event you have access to excel format string of the column as well as to the grid column format string. Check in the event if the column has a format string and set the excel format string like this:

private void UltraGridExcelExporterInitializeColumn(object sender, InitializeColumnEventArgs e)
{
    if (!string.IsNullOrEmpty(e.Column.Format))
    {
        switch (e.FrameworkFormatStr)
        {
            case "C":
                e.ExcelFormatStr = "$#,##0.00";
                break;
            // handle here other format strings you may have
            default:
                e.ExcelFormatStr = e.FrameworkFormatStr;
                break;
        }
    }
}

Note that not all the format strings in C# are the same as the format strings in Excel.