Remove the "Sum" and "Count" Strings from the Exported Excel File by the Grid
Environment
Product | Grid for Blazor, TreeList for Blazor |
Description
This KB article answers the following questions:
- How to remove the Sum: and Count: strings in the Grid footer cells when using Excel export with
GridAggregates
? - How to get the customized text from the Grid
FooterTemplate
into the Excel download, instead of the aggregate function name itself? - How to make the exported Grid Excel file match the Grid footer template content?
- How to remove the Sum word and the other aggregate labels in the footer of Grid when exporting data to Excel? I want to only keep the number in the footer of the Excel file.
- How to customize the footer cell content in the exported Excel file?
Solution
-
Install the required Telerik Document Processing NuGet packages. Note the version compatibility requirements to avoid conflicts with Telerik UI for Blazor.
Telerik.Documents.Spreadsheet
Telerik.Documents.Spreadsheet.FormatProviders.OpenXml
-
Subscribe to the Grid
OnAfterExport
event for Excel export. -
In the
OnAfterExport
handler, obtain the generated Excel file as a byte array from theStream
property of theGridAfterExcelExportEventArgs
event argument. -
Create a new
MemoryStream
object and populate it with the Excel file byte array.The steps below use the Telerik RadSpreadProcessing API and are outside the scope of Telerik UI for Blazor.
-
Use an
XlsxFormatProvider
toImport()
the Excel fileMemoryStream
as aWorkbook
. -
Get the
Worksheet
object from theWorkbook
. -
Find the footer cells by their row and column indexes, which depend on the exported number of rows and current Grid column state. Alternatively, iterate cell ranges.
-
Get each footer cell value (
ICellValue
object) and useICellValue.RawValue
to obtain the actual cell content. -
Set the modified footer cell values, according to your business requirements. You can remove the predefined aggregate labels or add custom content that is similar to the Grid
FooterTemplate
content. -
Export()
the modifiedWorkbook
to a newMemoryStream
and then to abyte[]
. Assign the byte array to theStream
property of theGridAfterExcelExportEventArgs
event argument.
Remove aggregate labels from footer cells in the exported Excel file by the Grid
@using Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsx
@using Telerik.Windows.Documents.Spreadsheet.Model
@using Telerik.Blazor.Services
@inject ITelerikStringLocalizer TelerikLocalizer
<TelerikGrid @ref="@GridRef"
Data="@GridData"
Pageable="true"
@bind-PageSize="@GridPageSize"
Reorderable="true">
<GridAggregates>
<GridAggregate Field="@nameof(SampleModel.Name)" FieldType="@typeof(string)" Aggregate="@GridAggregateType.Count" />
<GridAggregate Field="@nameof(SampleModel.Price)" FieldType="@typeof(decimal)" Aggregate="@GridAggregateType.Average" />
<GridAggregate Field="@nameof(SampleModel.Quantity)" FieldType="@typeof(int)" Aggregate="@GridAggregateType.Sum" />
</GridAggregates>
<GridExport>
<GridExcelExport AllPages="@GridExportAllPages" OnAfterExport="@OnGridAfterExport" />
</GridExport>
<GridToolBarTemplate>
<GridCommandButton Command="ExcelExport">Export To Excel</GridCommandButton>
<span class="k-separator"></span>
<label><TelerikCheckBox @bind-Value="@GridExportAllPages" /> Export All Pages</label>
<span class="k-separator"></span>
<label><TelerikCheckBox @bind-Value="@GridRemoveAggregateLabelsInExcel" /> Remove Aggregate Labels in Excel File</label>
</GridToolBarTemplate>
<GridColumns>
<GridColumn Field="@nameof(SampleModel.Name)">
<FooterTemplate>
Count: @context.Count?.ToString("N0")
</FooterTemplate>
</GridColumn>
<GridColumn Field="@nameof(SampleModel.Price)">
<FooterTemplate>
Average: @context.Average?.ToString("N2")
</FooterTemplate>
</GridColumn>
<GridColumn Field="@nameof(SampleModel.Quantity)">
<FooterTemplate>
Sum: @context.Sum?.ToString("N0")
</FooterTemplate>
</GridColumn>
</GridColumns>
</TelerikGrid>
@code {
private TelerikGrid<SampleModel>? GridRef { get; set; }
private List<SampleModel> GridData { get; set; } = new();
private int GridPageSize { get; set; } = 7;
private bool GridExportAllPages { get; set; }
private bool GridRemoveAggregateLabelsInExcel { get; set; } = true;
private void OnGridAfterExport(GridAfterExcelExportEventArgs args)
{
if (!GridRemoveAggregateLabelsInExcel)
{
return;
}
// args.Stream is finalized, while XlsxFormatProvider.Import() requires a readable stream.
// Copy the args.Stream bytes to a new MemoryStream for the import.
byte[] originalBytes = args.Stream.ToArray();
MemoryStream originalStream = new MemoryStream(originalBytes);
XlsxFormatProvider formatProvider = new XlsxFormatProvider();
Workbook workbook = formatProvider.Import(originalStream, new TimeSpan(0, 0, 5));
Worksheet worksheet = workbook.Worksheets[0];
// The footer row index depends on the number of data items and exported pages.
int footerRowIndex = GridExportAllPages ? GridData.Count + 1 : GridPageSize + 1;
// Cell indexes may depend on column reordering.
ICollection<GridColumnState> gridColumnStates = GridRef!.GetState().ColumnStates;
int nameColumnIndex = gridColumnStates.First(x => x.Field == nameof(SampleModel.Name)).Index;
int priceColumnIndex = gridColumnStates.First(x => x.Field == nameof(SampleModel.Price)).Index;
int quantityColumnIndex = gridColumnStates.First(x => x.Field == nameof(SampleModel.Quantity)).Index;
CellSelection nameFooterCell = worksheet.Cells[footerRowIndex, nameColumnIndex];
string nameFooterValue = nameFooterCell.GetValue().Value.RawValue;
// Aggregate labels may depend on localization
nameFooterCell.SetValue(nameFooterValue.Replace($"{TelerikLocalizer["Aggregate_Count"]}: ", ""));
// Optional bold
nameFooterCell.SetIsBold(true);
CellSelection priceFooterCell = worksheet.Cells[footerRowIndex, priceColumnIndex];
string priceFooterValue = priceFooterCell.GetValue().Value.RawValue;
priceFooterCell.SetValue(priceFooterValue.Replace($"{TelerikLocalizer["Aggregate_Average"]}: ", ""));
priceFooterCell.SetIsBold(true);
CellSelection quantityFooterCell = worksheet.Cells[footerRowIndex, quantityColumnIndex];
string quantityFooterValue = quantityFooterCell.GetValue().Value.RawValue;
quantityFooterCell.SetValue(quantityFooterValue.Replace($"{TelerikLocalizer["Aggregate_Sum"]}: ", ""));
quantityFooterCell.SetIsBold(true);
// Save modified workbook in a MemoryStream.
MemoryStream modifiedStream = new MemoryStream();
formatProvider.Export(workbook, modifiedStream, new TimeSpan(0, 0, 5));
args.Stream = modifiedStream;
}
protected override void OnInitialized()
{
for (int i = 1; i <= 30; i++)
{
GridData.Add(new SampleModel()
{
Id = i,
Name = $"Name {i}",
Price = Random.Shared.Next(1, 100) * 1.23m,
Quantity = Random.Shared.Next(0, 1000)
});
}
}
public class SampleModel
{
public int Id { get; set; }
public string Name { get; set; } = string.Empty;
public decimal Price { get; set; }
public int Quantity { get; set; }
}
}