Export a grouped grid to Excel (or PDF) - group header in spreadsheet just shows name of the grouped column

[Paul]

Hi,

A grid is grouped by the column MassBalanceGrouping.

Export the grid to Excel (or PDF) and the spreadsheet implements an "outline option", with a group header followed by rows within the group.

Two problems :

1.     the group header in the spreadsheet just contains the string "MassBalanceGrouping" and not the actual contents of the column that is being grouped on

2.     the outline expand/collapse UI implemented in the spreadsheet is out of sync with the data - the group header is a header for the rows that follow it, but collapsing the header actually collapses the rows above it and not those below it!

Attached is an example spreadsheet.

Please advise how to fix these problems.

Thanks.

Paul

Attachment: Export_(7)_6190f122.zip

[Jeevakanth Palaniappan]

Hi Paul, 

We have validated your query but we are unable to reproduce the reported problem from our end. Please find the validated sample and the excel document for your reference. 

Sample and document - https://www.syncfusion.com/downloads/support/directtrac/general/ze/Exporting1488090134 

If you are still facing the problem then kindly share us the below details, 

1. Share us the Syncfusion NuGet version.
2. Kindly share us the complete code.
3. Kindly share us the issue reproducing sample or reproduce the issue in the above provided sample.

The above requested details will be helpful for us to validate the issue and provide you with a better solution as early as possible. 

Regards, 

Jeevakanth SP. 

[Paul]

Hi,

I have discovered the problem and would like to know what a possible solution might be.

As soon as you add a CaptionTemplate changing the group caption, the Excel Export function no longer exports the group information correctly.

Basically the newly created caption (created in the section below) gets exported to excel incorrectly.

This:

become this:

                    <GridGroupSettings Columns="@InitialGroupingColumns" ShowUngroupButton="true">

                        <CaptionTemplate>

                            @{

                                var captionContext = (context as CaptionTemplateContext);

                                string captionString;

                                switch (captionContext.Field)

                                {

                                    case "MassBalanceNodeLevel":

                                        captionString = !int.TryParse(captionContext.Key, out int dummy) ? "n/a" :

                                                                (int.Parse(captionContext.Key) == 0 ? "Object Searched" :

                                                                (int.Parse(captionContext.Key) < 0 ? "Upstream " : "Downstream ") + Math.Abs(int.Parse(captionContext.Key)).ToString());

                                        break;

                                    case "MassBalanceGrouping":

                                        captionString = captionContext.Key.Split("~")[1];

                                        break;

                                    default:

                                        captionString = captionContext.Key;

                                        break;

                                }

                                <div>@captionString</div>

                            }

                                </CaptionTemplate>

                    </GridGroupSettings>

[Jeevakanth Palaniappan]

Hi Paul, 

We have added the CaptionTemplate in the sample and tried to reproduce the issue in 19.1.0.57 version, but still we are unable to reproduce the reported problem. Please find the sample and the screenshot below for your reference. 

Sample and excel document - https://www.syncfusion.com/downloads/support/directtrac/general/ze/ExcelExport-593419871 

Documentation - https://blazor.syncfusion.com/documentation/datagrid/grouping/#caption-template 

 

We suggest you to share us the below details to proceed further, 

1. Share us the Syncfusioin Nuget version details.
2. Share us the issue reproducing sample or reproduce the issue in the provided sample.

The above requested details will be helpful for us to validate the issue and provide you with a better solution as early as possible.  

Regards, 

Jeevakanth SP. 

[Paul]

Hi,

I just recreated the issue in the sample that you provided.

Here is the Excel export. As you can see it is NOT exporting the grouping as shown on the screen. Excel does not contain - test at the end of the group:

Screen:

Code:

[Jeevakanth Palaniappan]

Hi Paul, 

We suggest you to set the group caption value for excel export cells by using the ExcelGroupCaptionTemplateInfo event of the grid. Please refer the below code snippet and the sample for your reference. 

Sample - https://www.syncfusion.com/downloads/support/directtrac/general/ze/Grouping-925567066 

<SfGrid ID="Grid" @ref="DefaultGrid" DataSource="@Orders" AllowGrouping="true" Toolbar="@(new List<string>() { "ExcelExport" })" AllowExcelExport="true" AllowPaging="true"> 

    <GridGroupSettings Columns="@(new string[]{"CustomerID"})" ShowUngroupButton="true"> 

        <CaptionTemplate> 

            @{ 

                var captionContext = (context as CaptionTemplateContext); 

                <div>@captionContext.HeaderText - @captionContext.Key - test</div> 

            } 

        </CaptionTemplate> 

    </GridGroupSettings> 

    <GridEvents ExcelGroupCaptionTemplateInfo="GroupCaptionHandler" OnToolbarClick="ToolbarClickHandler" TValue="Order"></GridEvents> 

 

@code{ 

public void GroupCaptionHandler(ExcelCaptionTemplateArgs args) { 

        args.Cell.Value = args.HeaderText + " - " + args.Key + " - test"; 

    } 

}     

Please get back to us if you have any other queries. 

Regards, 

Jeevakanth SP. 

[Arbre Tech]

Hi Syncfusion team,

I am facing the same issue. I even tried to remove the  CaptionTemplate from the grid. But still, this is reproducible for me. If we have any solution for this.

[Monisha Saravanan]

Hi Arbre,

Greetings from Syncfusion support.

Query: “I am facing the same issue. I even tried to remove the CaptionTemplate from the grid. But still, this is reproducible for me.”

We would like to inform that if we need to customize the caption template in the excel file then we need to customize it inside ExcelGroupCaptionTemplateInfo otherwise the default caption will be exported. From you query it seems that you have removed the Caption template and you are facing issues after exporting. So kindly share the below details to validate the issue further at our end.

1. Share us the entire Grid code snippet.
2. Share us whether you are facing issue in exporting customized caption template or default one.
3. Share us some more details about your requirement.
4. If possible share us an simple issue reproduceable sample.

Reference: https://blazor.syncfusion.com/documentation/datagrid/events#excelgroupcaptiontemplateinfo

The above-requested details will be very helpful for us to validate the reported query at our end and provide the solution as early as possible.

Regards,

Monisha

[Jonathan]

Hello All!

I've been having the same issue with SfGrid not creating Outline/Grouping when exporting to Excel.  While messing with the sample Jeevakanth Palaniappan  provided on April 30, 2021 I was able to figure out that Excel Grouping does not work on any version of Grid newer than 19.1.0.69​. 

Syncfusion team - is this a know issues in 19.2 and higher versions?  I'd really like to use the 21 version on my project.  Thanks!

[Monisha Saravanan]

Hi Jonathan,

We have prepared an sample( Grouping with export) using latest Nuget. But we are unable to replicate the reported issue at our end. Please find the attached sample for your reference.

If we misunderstood your query or is you still face difficulties then kindly share the below details to proceed further at our end.

1. If possible kindly share us an simple issue reproduceable sample or try to reproduce the reported issue on the above mentioned sample.
2. Share us the video demonstration of the issue.

Above requested details will be very helpful in validating the reported query at our end and provide solution as early as possible.

Regards,

Monisha

Attachment: BlazorApp1_396f35f7.zip

[Arbre Tech]

Hi  Monisha, 

I am sharing the entire code snippet here. 

<SfGrid @ref="dataGrid"

                            TValue="ExpandoObject"

                            ID="@($"ReportGrid_{SelectedReport.Id}")"

                            GridLines="GridLine.Both"

                            AllowResizing="false"

                            ShowColumnChooser="true"

                            AllowGrouping="true"

                            AllowSorting="true"

                            AllowReordering="true"

                            AllowExcelExport="true"

                            AllowPaging="true"

                            Toolbar="@toolbarItems"

                            Width="1270"

                            Height="600"

                            Query="@Query">

                        <SfDataManager AdaptorInstance="typeof(Services.ReportsDataAdaptor)" Adaptor="Adaptors.CustomAdaptor"></SfDataManager>

                        <GridPageSettings PageSize="@SelectedReport.ConfigModel.PageSize" PageSizes="@(new int[] { 50, 100, 200 })" />

                        <GridSearchSettings IgnoreCase="true"

                                            IgnoreAccent="true"

                                            Operator="Operator.Contains" />

                        <GridEvents TValue="ExpandoObject"

                                    Created="OnGridCreated"

                                    OnActionBegin="OnActionBegin"

                                    OnActionFailure="OnActionFailure"

                                    OnActionComplete="OnActionComplete"

                                    OnRecordClick="RecordClickHandler"/>

                        @if ((this.SelectedReport.ConfigModel.State?.FieldSorting).IsNullOrEmpty() == false)

                        {

                            <GridSortSettings>

                                <GridSortColumns>

                                    @foreach (var f in this.SelectedReport.ConfigModel.State.FieldSorting)

                                    {

                                        if (Enum.TryParse<SortDirection>(f.Value, out SortDirection sortDir)

                                && this.SelectedReport.FieldsByValue.TryGetValue(f.Key[0].ToString().ToUpper() + f.Key.Substring(1), out InventoryReportsViewModel.FieldViewModel f0))

                                        {

                                            <GridSortColumn Field="@f0.ValuePath" Direction="@sortDir" />

                                        }

                                    }

                                </GridSortColumns>

                            </GridSortSettings>

                        }

                        <GridAggregates>

                            <GridAggregate>

                                <GridAggregateColumns>

                                    @foreach (var field in this.SelectedReport.Fields)

                                    {

                                        if (field.ValuePath == "TotalCount")

                                        {

                                            <GridAggregateColumn [email protected] Type="AggregateType.Sum">

                                                <FooterTemplate>

                                                    @{

                                                        var aggregate = (context as AggregateTemplateContext);

                                                        <div>

                                                            <span style="margin-left: -60px;">Total</span> <span style="margin-left:22px;"> @aggregate.Sum</span>

                                                        </div>

                                                    }

                                                </FooterTemplate>

                                            </GridAggregateColumn>

                                        }

                                        if (field.ValuePath == "Costing" || field.ValuePath.Contains("Custom"))

                                        {

                                            <GridAggregateColumn [email protected] Type="AggregateType.Sum" Format="#.##">

                                                <FooterTemplate>

                                                    @{

                                                        var aggregate = (context as AggregateTemplateContext);

                                                        <div>

                                                            <p>@aggregate.Sum</p>

                                                        </div>

                                                    }

                                                </FooterTemplate>

                                            </GridAggregateColumn>

                                        }

                                    }

                                </GridAggregateColumns>

                            </GridAggregate>

                        </GridAggregates>

                        <GridColumns>

                            @foreach (var field in this.SelectedReport.Fields)

                            {

                                if (!Enum.TryParse(field.ValueDataType, out GridColumnType columnType))

                                {

                                    columnType = GridColumnType.String;

                                }

                                int colWidth = 125;

                                if (field.ValuePath == "Count")

                                    colWidth = 95;

                                <GridColumn IsPrimaryKey="@field.PrimaryKey"

                                            ClipMode="ClipMode.EllipsisWithTooltip"

                                            AllowSearching="true"

                                            ShowInColumnChooser="@field.CanShow"

                                            Visible="@field.IsVisible"

                                            HeaderText="@field.DisplayName"

                                            Type="@columnType"

                                            MinWidth="@(colWidth.ToString())"

                                            Width="@(colWidth.ToString())"

                                            Format="@field.Format"

                                            Field="@field.Path">

                                    <Template>

                                        @{

                                            var item = (context as ExpandoObject);

                                            var value = item.GetValue(field.ValuePath);

                                            var Item1 = item.GetValue<string>("Item1");

                                            var Item2 = item.GetValue<string>("Item2");

                                            var Item3 = item.GetValue<string>("Item3");

                                            string valueText = value?.ToString();

                                            // need to format dates to be displaye correctly

                                            if (!string.IsNullOrEmpty(field.Format) && field.ValueDataType == "Date")

                                            {

                                                valueText = (value as DateTime?)?.ToString(field.Format);

                                            }

                                            // show rfid icon if these fields

                                            if (field.ValuePath == "Item1" || field.ValuePath == "Item2")

                                            {

                                                if ((field.ValuePath == "Item1")

                                                    || (field.ValuePath == "Item2"))

                                                {

                                                    <img src="/images/ic_rfid1x.png" class="rfid-icon" />

                                                }

                                                else if ((field.ValuePath == "Item3"))

                                                {

                                                    <img src="//:0" alt="" class="empty-rfid-icon" />

                                                }

                                            }

                                            <span>@valueText</span>

                                        }

                                    </Template>

                                </GridColumn>

                            }

                        </GridColumns>

                        @{

                            var groupColumnKeys = this.SelectedReport.ConfigModel.State?.GroupByFields?

                                    // the group fields will be just the key

                                    // we need to render based on the "Path" of the field.

                                    .Select(f => this.SelectedReport.FieldsByValue

                                        .TryGetValue(f, out ReportsViewModel.FieldViewModel f0) ? f0.Path : null)

                                    ?.ToArray();

                            if (groupColumnKeys.IsNullOrEmpty())

                                groupColumnKeys = null;

                        }

                        <GridGroupSettings Columns="@groupColumnKeys"

                                           ShowGroupedColumn="true">

                            <CaptionTemplate>

                                @{

                                    var order = (context as CaptionTemplateContext);

                                    var k = order.Key;

                                    if (string.IsNullOrWhiteSpace(k) || k == "null")

                                    {

                                        k = AppResources.Blankvar;

                                    }

                                    var countText = "item".ToQuantity(@order.Count);

                                    <div>@order.HeaderText - @k - @countText</div>

                                }

                            </CaptionTemplate>

                        </GridGroupSettings>

                        <GridTemplates>

                            <EmptyRecordTemplate>

                                <i><span>@AppResources.ResultsFoundspan</span></i>

                                <ul>

                                    <li><i>@AppResources.ModifyYourQueryParametersi</i></li>

                                    <li><i>@AppResources.ShowMoreColumnsi</i></li>

                                    <li><i>@AppResources.ModifySearchTermi</i></li>

                                </ul>

                            </EmptyRecordTemplate>

                        </GridTemplates>

                    </SfGrid>

I am facing this issue with Customize Caption control 

I want to export the current view in Excel with multiple grouping levels. But the grouping of the exported files is not correct. 

[Monisha Saravanan]

Hi Arbre,

We would like to inform that the caption template will be rendered in excel only when it is customized using ExcelGroupCaptionTemplateInfo. Otherwise it will render the default group caption template without customized text.
In your shared code we could see that you haven’t used ExcelGroupCaptionTemplateInfo for customization. So please check the below code snippet for your reference.

For additional reference please refer the sample on the previous update.

<SfGrid ID="Grid" @ref="DefaultGrid" DataSource="@Orders" AllowGrouping="true" Toolbar="@(new List<string>() { "ExcelExport" })" AllowExcelExport="true" AllowPaging="true">

    <GridGroupSettings Columns="@(new string[]{"CustomerID"})" ShowUngroupButton="true">

        <CaptionTemplate>

            @{

                var captionContext = (context as CaptionTemplateContext);

                <div>@captionContext.HeaderText - @captionContext.Key - test</div>

            }

        </CaptionTemplate>

    </GridGroupSettings>

    <GridEvents ExcelGroupCaptionTemplateInfo="GroupCaptionHandler" OnToolbarClick="ToolbarClickHandler" TValue="Order"></GridEvents>

    <GridColumns>

...

    </GridColumns>

</SfGrid>

 

@code {

 

    public void GroupCaptionHandler(ExcelCaptionTemplateArgs args)

    {

        args.Cell.Value = args.HeaderText + " - " + args.Key + " - test";

    }

}

Please let us know if you have any concerns.

[Jonathan]

Hello, When I run that code as-is, and export the table to Excel, the exported file does not have any grouping.

In the context of this example, I'd like to be able to collapse the "Custom Name" sections in Excel.  Thanks for all the help!

[Arbre Tech]

Hi Syncfusion,

Multi-grouping is still not working correctly.

https://www.loom.com/share/ed11eaa32bae4175a467951a7f7ecb9f

I have added the caption template as highligted here.

https://www.syncfusion.com/forums/164895/export-a-grouped-grid-to-excel-or-pdf-group-header-in-spreadsheet-just-shows-name-of-the?reply=SFjBhn

                                    ExcelGroupCaptionTemplateInfo="GroupCaptionHandler"
                                    Created="OnGridCreated"
                                    OnActionBegin="OnActionBegin"
                                    OnActionFailure="OnActionFailure"
                                    OnActionComplete="OnActionComplete" />

                                @{
                                    var order = (context as CaptionTemplateContext);


                                    var k = order.Key;
                                    if (string.IsNullOrWhiteSpace(k) || k == "null")
                                    {
                                        k = AppResources.Blankvar;
                                    }


                                    var countText = "item".ToQuantity(@order.Count);


                                    
@order.HeaderText - @k - @countText
                                }

Spreadsheet link

https://docs.google.com/spreadsheets/d/1U2QyPfiA7iu7Mdb58DZ4ywGgNhwg1TcpaS1tM0ucRSA/edit?usp=sharing

[Monisha Saravanan]

Hi,

Thanks for the patience.

We have confirmed this as an issue and logged the defect report “Exporting grouped Grid does not work properly with ExcelExport” for the same. Thank you for taking time to report this issue and helping us to improve our product. At Syncfusion, we are committed to fix all validated defects (subject to technological feasibility and Product Development Life Cycle) and this fix will be included in our upcoming patch release which is expected to be rolled out in our upcoming patch release.

You can now track the current status of your request, review the proposed resolution timeline, and contact us for any further inquiries through this link.       

https://www.syncfusion.com/feedback/43185/exporting-grouped-grid-does-not-work-properly-with-excelexport

Disclaimer: “Inclusion of this solution in the weekly release may change due to other factors including but not limited to QA checks and works reprioritization”

We will update you once the release is rolled out. Until then we appreciate your patience.

Regards,

Monisha S

[Monisha Saravanan]

Hi,

We are glad to announce that, we have included the fix for the issue “Exporting grouped Grid does not work properly with ExcelExport” in our 21.2.5 release.  So please upgrade to our latest version of Syncfusion NuGet package to resolve the reported issue. Please find the Nuget package for latest fixes and features from below.

Nuget : https://www.nuget.org/packages/Syncfusion.Blazor.Grid

We thank you for your support and appreciate your patience in waiting for this release. Please get in touch with us if you would require any further assistance. 

Regards,

Monisha

[Jonathan]

Yes!! I'm so excited about this! I just updated to version 21.2.5 and can confirm Excel export with grouping is working as excepted.  Thank you for the update!

--Jonathan

[Monisha Saravanan]

Hi Jonathan,

Thanks for the update. We are glad to hear that the reported issue has been resolved . Kindly get back to us if you have further queries. As always we will be happy to assist you.

Regards,

Monisha