Excel export (ExportToXlsx) using ShowGroupHeaderRowAggregates shows poorly formatted header

[Bruce]

When ShowGroupHeaderRowAggregates is left as True, the header aggregates are not appearing above the appropriate cells upon exporting to Excel (ExportToXlsx), but rather all scrunched together and an utterly useless mess. The RadGridView on the client looks correct as the following is set: ColumnAggregatesAlignment="NextToGroupKey" in the XAML.

What do we need to do to properly align the aggregate columns in the header for the Excel export functionality? Are there any solid examples of how to do this?

Thank you.

[Dinko | Tech Support Engineer]

Hello Bruce,

To avoid any misunderstanding, may I ask you to share the RadGridView set-up and which options are used from the GridViewSpreadStreamExportOptions. Also, how are you performing the grouping? In code-behind or while the application is running. I am asking this because I need to mimic your implementation to reproduce this behavior.

I am looking forward to your reply.

Regards,
Dinko
Progress Telerik

[Bruce]

For the control itself, it's setup as such:

<telerik:RadGridView 
	Grid.Row="1"  
	Name="SearchResultsGrid"  
	FontSize="{Binding Value, ElementName=FontSlider, Mode=TwoWay}" 
	FrozenColumnCount="3"                                    
	SelectionMode="Multiple"   
	CanUserSelect="True" 
	ShowColumnFooters="True"
	ShowGroupFooters="True" 
	ItemsSource="{Binding DataContext.SearchResults, RelativeSource={RelativeSource FindAncestor, AncestorType={x:Type controls:TrackedUserControl}}}"
	AutoGenerateColumns="False" 
	IsReadOnly="True"
	IsFilteringAllowed="False"  
	RowIndicatorVisibility="Collapsed" 
	CanUserFreezeColumns="False"
	Sorting="GridSorting" 
	Grouping="GridGrouping"
	Grouped="SearchResultsGrid_Grouped"
	GroupRenderMode="Flat"
	dragDrop:GridViewDragDropBehavior.IsEnabled="True">

For the code to call exporter itself (with all the options coming in as True): 

var updatedFileName = Path.ChangeExtension(Path.GetTempFileName(), "xlsx");

var exportOptions = new GridViewDocumentExportOptions();
//exportOptions.Format = ExportFormat.Html;
exportOptions.ShowColumnFooters = showColumnFooters;
exportOptions.ShowColumnHeaders = showColumnHeaders;
exportOptions.ShowGroupFooters = showGroupFooters;
exportOptions.ShowGroupHeaderRowAggregates = true;

using (var fs = File.Create(updatedFileName))
{
	grid.ExportToXlsx(fs, exportOptions);
}

The grouping is done dynamically by drag/drop of any column via the RadGridView functionality. In the UI, it looks fine. However, the header row aggregates don't export properly. The header aggregate row appears to export as a single cell and there's spacing added between the aggregate data to attempt to line it up (I guess) but it doesn't work.

[Dinko | Tech Support Engineer]

Thank you for the provided code snippet.

After investigating this behavior it turns out that by default, when exporting the group header row, RadGridView will add the aggregate results to the exported group header cell. At this moment of writing our exporting mechanism does not respect the ColumnAggregatesAlignment property of the RadGridView. In order to modify this behavior, you can handle the ElementExportingToDocument like so:

private void GridView_ElementExportingToDocument(object sender, GridViewElementExportingToDocumentEventArgs e)
{
    if (e.Element == ExportElement.GroupHeaderCell)
    {
        var viewGroup = e.DataContext as QueryableCollectionViewGroup;
        if (viewGroup.AggregateResults.Count > 0)
        {
            var collection = viewGroup.AggregateResults;
            string finalResult = string.Empty;
            foreach (AggregateResult item in viewGroup.AggregateResults)
            {
                finalResult = finalResult + " / " + item.FormattedValue.ToString();
            }
            e.Value = viewGroup.Name + " Aggregates: " + finalResult;//create custom string
        }
    }
}

[Bruce]

Yes, that does clean up that header cell string. I guess I was thinking that that particular row would have those aggregates actually line up with the appropriate aggregated columns. I'm not sure what the purpose of showing the aggregates in that type of summary form is for, though. I actually removed all that aggregate info other than the group name since the aggregates show as a footer row under the proper columns.

I do appreciate you looking into this. Thank you.