Export to Excel footer format

4 posts, 0 answers
  1. MBEN
    MBEN avatar
    203 posts
    Member since:
    Nov 2011

    Posted 28 Sep 2018 Link to this post

    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?

     

  2. Tsvetomir
    Admin
    Tsvetomir avatar
    258 posts

    Posted 03 Oct 2018 Link to this post

    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.
  3. MBEN
    MBEN avatar
    203 posts
    Member since:
    Nov 2011

    Posted 03 Oct 2018 in reply to Tsvetomir Link to this post

    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.

  4. Tsvetomir
    Admin
    Tsvetomir avatar
    258 posts

    Posted 08 Oct 2018 Link to this post

    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.
Back to Top