GridXlExportOptions.CalcTotalSummaryOnCompositeRange Property

Specifies whether a total summary exported as the SUBTOTAL function excludes references to cells with group summary values.

Namespace: DevExpress.Blazor

Assembly: DevExpress.Blazor.v23.2.dll

NuGet Package: DevExpress.Blazor

Declaration

[DefaultValue(false)]
public bool CalcTotalSummaryOnCompositeRange { get; set; }

Property Value

Type	Default	Description
Boolean	false	true, to calculate the total summary in the resulting document against multiple cell ranges excluding cells with group summary values; false, to calculate the total summary in the resulting document against a single range including cells with group summary values.

Remarks

The Grid exports a summary as the SUBTOTAL function. This function counts data values and ignores nested subtotals to avoid double counting, thus a total summary ignores nested group summaries. If a group summary is exported as a text (for instance, when it is calculated in the CustomSummary event or the summary value is modified in the CustomizeCell event), such values are treated by total summary formulas as data cells and result in incorrect totals.

Set the CalcTotalSummaryOnCompositeRange property to true to calculate the total summary against multiple cell ranges excluding cells with group summary values.

Note that Microsoft Excel® limits the length of formula content. If a grid component displays a large amount of groups, the resulting document may include incorrect formulas. For more details, see the following topic: Excel specifications and limits.

<DxGrid @ref="Grid" Data="@Data" ShowGroupPanel="true" PageSize="6" 
        CustomSummary="Grid_CustomSummary"
        CustomizeSummaryDisplayText="Grid_CustomizeSummaryDisplayText" >
    <Columns>
        <DxGridDataColumn FieldName="OrderDate" GroupIndex="0" />
        <DxGridDataColumn FieldName="CompanyName" />
        <DxGridDataColumn FieldName="Country" />
        <DxGridDataColumn FieldName="UnitPrice" DisplayFormat="c" />
        <DxGridDataColumn FieldName="Quantity" />
        <DxGridDataColumn FieldName="Total" UnboundType="GridUnboundColumnType.Decimal" 
                          DisplayFormat="c" UnboundExpression="[UnitPrice]*[Quantity]" />
    </Columns>
    <GroupSummary>
        <DxGridSummaryItem SummaryType="GridSummaryItemType.Custom" FieldName="Total" 
                            FooterColumnName="Total" Name="BigDeals" ValueDisplayFormat="n0" />
    </GroupSummary>
    <TotalSummary>
        <DxGridSummaryItem SummaryType="GridSummaryItemType.Count" FieldName="Total" />
    </TotalSummary>
</DxGrid>
<DxButton Text="Export to XLSX" Click="ExportXlsx_Click" />
@code {
    object Data { get; set; }
    IGrid Grid { get; set; }
    protected override async Task OnInitializedAsync() {
        var invoices = await NwindDataService.GetInvoicesAsync();
        var customers = await NwindDataService.GetCustomersAsync();
        Data = invoices.OrderBy(i => i.OrderDate).Join(customers, i => i.CustomerId, c => c.CustomerId, (i, c) => {
            return new {
                OrderDate = i.OrderDate,
                CompanyName = c.CompanyName,
                City = i.City,
                Region = i.Region,
                Country = i.Country,
                UnitPrice = i.UnitPrice,
                Quantity = i.Quantity
            };
        });
    }
    // Counts the number of totals that are greater than 1000
    void Grid_CustomSummary(GridCustomSummaryEventArgs e) {
        switch(e.SummaryStage) {
            case GridCustomSummaryStage.Start:
                e.TotalValue = 0m;
                break;
            case GridCustomSummaryStage.Calculate:
                if((decimal)e.FieldValue > 1000)
                    e.TotalValue = (decimal)e.TotalValue +1;
                break;
        }
    }
    void Grid_CustomizeSummaryDisplayText(GridCustomizeSummaryDisplayTextEventArgs e) {
        if(e.Item.Name == "BigDeals")
            e.DisplayText = string.Format("Count of Big Deals: {0}", e.Value);
    }
    async Task ExportXlsx_Click() {
        var options = new GridXlExportOptions();
        options.CalcTotalSummaryOnCompositeRange = true;
        options.CustomizeCell = e => {
            if (e.AreaType == DevExpress.Export.SheetAreaType.GroupFooter) {
                if (e.ColumnFieldName == "Total" && e.SummaryItem != null) {
                    e.Value = string.Format("Count of Big Deals: {0}", e.Value);
                    e.Handled = true;
                }
            }
        };
       await Grid.ExportToXlsxAsync("ExportResult", options);
    }
}