This is a migrated thread and some comments may be shown as answers.
Export to Excel footer format
3 Answers 14 Views
This is a migrated thread and some comments may be shown as answers.
MBEN
Top achievements
Rank 1
MBEN asked on 28 Sep 2018, 08:25 PM

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?

 

3 Answers, 1 is accepted

Sort by
0
Tsvetomir
Telerik team
answered on 03 Oct 2018, 08:46 AM
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
Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
0
MBEN
Top achievements
Rank 1
answered on 03 Oct 2018, 04:23 PM

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.

0
Tsvetomir
Telerik team
answered on 08 Oct 2018, 09:19 AM
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
Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
Tags
Grid
Asked by
MBEN
Top achievements
Rank 1
Answers by
Tsvetomir
Telerik team
MBEN
Top achievements
Rank 1
Share this question
or