Excel export (ExportToXlsx) using ShowGroupHeaderRowAggregates shows poorly formatted header

1 Answer 41 Views
GridView Spreadsheet
Bruce
Top achievements
Rank 2
Bruce asked on 29 Sep 2021, 01:27 PM

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.

1 Answer, 1 is accepted

Sort by
0
Accepted
Dinko
Telerik team
answered on 04 Oct 2021, 11:27 AM

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

Virtual Classroom, the free self-paced technical training that gets you up to speed with Telerik and Kendo UI products quickly just got a fresh new look + new and improved content including a brand new Blazor course! Check it out at https://learn.telerik.com/.

Bruce
Top achievements
Rank 2
commented on 04 Oct 2021, 02:25 PM | edited

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
Telerik team
commented on 07 Oct 2021, 12:03 PM

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
Top achievements
Rank 2
commented on 07 Oct 2021, 01:15 PM

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.

Tags
GridView Spreadsheet
Asked by
Bruce
Top achievements
Rank 2
Answers by
Dinko
Telerik team
Share this question
or