Export to Excel footer format

[MBEN]

I have a few columns with balances showing on the grid that I export to excel.

I only show the $ sign in the footer totals and the rows are displayed without the $. So my column looks like below:

 

<telerik:GridBoundColumn Aggregate="Sum" DataField="PlanExpense" HeaderText="Annual Plan Expense" UniqueName="PlanExpense"
                            DataFormatString="{0:#,##0.00;(#,##0.00)}" HeaderStyle-Width="9%" FooterAggregateFormatString="{0:c2}" />

protected void grid_ExportCellFormatting(object source, ExportCellFormattingEventArgs e)
{
    if (e.FormattedColumn.ColumnType == "GridTemplateColumn")
    {
        GridTemplateColumn templateCol = (GridTemplateColumn)e.FormattedColumn;
        if (templateCol.DataTypeName == "System.Decimal" || templateCol.DataTypeName == "System.Double")
        {
            e.Cell.Style["mso-number-format"] = "$#,##0.00;($#,##0.00)";
        }
    }

    if (e.FormattedColumn.ColumnType == "GridBoundColumn")
    {
        GridBoundColumn boundCol = (GridBoundColumn)e.FormattedColumn;
        if (boundCol.DataFormatString.ToLower().Contains("0:c") || boundCol.DataFormatString.ToLower().Contains("$"))
        {
            //e.Cell.Style["mso-number-format"] = "$#,##0.00;($#,##0.00)";
            e.Cell.Style["mso-number-format"] = "-$* #,##0.00_-;$* (#,##0.00-);_-$* \" - \"??_-;_-@_-";
        }
        else if (boundCol.DataFormatString.ToLower().Contains("%"))
        {
            e.Cell.Style["mso-number-format"] = "0.##%;(0.##%)";
        }
        else if (boundCol.DataFormatString.ToLower().Contains("0:#,##0.00"))
        {
            e.Cell.Style["mso-number-format"] = "0:#,##0.00;(#,##0.00)";
        }
        else if (boundCol.DataFormatString.Contains("{0:P}"))
        {
            e.Cell.Style["mso-number-format"] = @"Percent";
        }
        else if (boundCol.DataFormatString.Contains("{0:#,##0;(#,##0)}"))
        {
            e.Cell.Style["mso-number-format"] = "$#,##0;($#,##0)";
        }
    }


}

I specify formatting in the exportcellformatting event as shown above, but it seems that is only applicable to the data rows and not the footer. Currently excel displays a negative currency in red parenthesis and I do not want to show the negative in red, only in parentheses.

Is there a way I can format the footer to do that?

 

[Tsvetomir]

Hello,

The currency formatting could be applied as "{0:C3}", where the "C" indicates currency is going to be formatted, while the number is the precision specifier - number of decimal digits.
https://docs.telerik.com/devtools/aspnet-ajax/controls/grid/functionality/exporting/export-formats/xlsx-and-docx-export#formatting 

<telerik:GridBoundColumn Aggregate="Sum" DataField="PlanExpense" HeaderText="Annual Plan Expense" UniqueName="PlanExpense"
                           DataFormatString="{0:C3}" HeaderStyle-Width="9%" FooterAggregateFormatString="{0:c2}" />

I have also attached a sample project which demonstrates the behavior of the functionality. 

Regards,
Tsvetomir
Progress Telerik

[MBEN]

Unfortunately, that isn't what I was looking for. The C3 would just give me 3 decimals instead of 2 I have specified. Excel still interprets the currency symbol and renders it in red color.

Also, I am using HTML based Excel export, if that helps.

 

I was able to find a workaround by changing the mso-number-format to be text instead of currency for the footer in ItemCreated event but I am sure there is a more elegant solution out there.

[Tsvetomir]

Hello,

The reason why the footer aggregate appeared in red when exported is that the style set in the ExportCellFormatting event handler is not applied to the footer. In order to access the cells in the footer, you have to subscribe to the PreRender event and check if the Grid.IsExporting and then foreach all cells and apply the formatting as follows:

protected void RadGrid1_PreRender(object sender, EventArgs e)
{
    if (RadGrid1.IsExporting)
    {
        GridFooterItem footer = RadGrid1.MasterTableView.GetItems(GridItemType.Footer)[0] as GridFooterItem;
        foreach (TableCell cell in footer.Cells)
        {
            if (!string.IsNullOrWhiteSpace(cell.Text))
            {
                cell.Style["mso-number-format"] = @"0\.00";
            }
        }
    }
}

Kind regards,
Tsvetomir
Progress Telerik