Custom Cell Formatting of the Exported File with RadSpreadStreamProcessing
Environment
| Product | Grid for Blazor |
Description
I want to format the cells of the exported Excel file and customize their appearance. For example, to set a background color. How to achieve this without compromising the application performance? When I use RadSpreadProcessing in Blazor WASM applications, customizing the exported file is slow.
Solution
Handle the OnAfterExport event of the Grid. It fires before the actual file is provided to the user. The Stream field of its event argument contains the output of the export as a MemoryStream.
You can copy the bytes from this stream and import it using RadSpreadStreamProcessing to access and modify the cells.
The customization of the exported Excel files is faster with the RadSpreadStreamProcessing library than with RadSpreadProcessing.
The example below demonstrates the export and customization of an Excel file. You can apply the same approach to CSV files.
To customize the cell format of the exported file before it reaches the client:
Handle the
OnAfterExportevent of the Grid. The stream it provides is finalized so that the resource does not leak. Its binary data, however, is available so you can copy the stream bytes to a newMemoryStreaminstance.Define the desired cell styles. The example below demonstrates adding cell fill, you can specify different cell styles based on the result you want to achieve.
Define a
IWorkbookExporterto export the modified file and save it in a newMemoryStreaminstance.Loop through the sheets, rows and cells to copy their content in the
IWorkbookExporterinstance.Apply proper formatting depending on the cell value types.
Apply the defined styles to the desired cells.
Pass the new
MemoryStreamto theargs.Streamof theGridAfterExcelExportEventArgs, so that the modifications can be saved to the actual exported file.
Use RadSpreadStreamProcessing to set background to table headers
@using System.IO
@using Telerik.Documents.SpreadsheetStreaming
<TelerikGrid Data="@GridData"
Pageable="true">
<GridToolBarTemplate>
<GridCommandButton Command="ExcelExport">Export to Excel</GridCommandButton>
<label class="k-checkbox-label">
<TelerikCheckBox @bind-Value="@ExportAllPages" />
Export All Pages
</label>
</GridToolBarTemplate>
<GridExport>
<GridExcelExport FileName="telerik-grid-export"
AllPages="@ExportAllPages"
OnAfterExport="@OnExcelAfterExport">
</GridExcelExport>
</GridExport>
<GridColumns>
<GridColumn Field="@nameof(SampleData.ProductId)" Title="ID" Width="100px" />
<GridColumn Field="@nameof(SampleData.ProductName)" Title="Product Name" Width="300px" />
<GridColumn Field="@nameof(SampleData.UnitsInStock)" Title="In stock" Width="100px" />
<GridColumn Field="@nameof(SampleData.Price)" Title="Unit Price" Width="200px" />
<GridColumn Field="@nameof(SampleData.Discontinued)" Title="Discontinued" Width="100px" />
<GridColumn Field="@nameof(SampleData.FirstReleaseDate)" Title="Release Date" Width="300px" />
</GridColumns>
</TelerikGrid>
@code {
private List<SampleData> GridData { get; set; }
private bool ExportAllPages { get; set; }
private async Task OnExcelAfterExport(GridAfterExcelExportEventArgs args)
{
var bytes = args.Stream.ToArray();
var excelStream = new MemoryStream(bytes);
var export = new MemoryStream();
//define the desired styling - https://docs.telerik.com/devtools/document-processing/libraries/radspreadstreamprocessing/features/cell-styles
SpreadCellFormat markedCell = new SpreadCellFormat()
{
Fill = SpreadPatternFill.CreateSolidFill(new SpreadColor(230, 238, 223)),
IsBold = true
};
//import the stream to modify it and then export it -
//see https://docs.telerik.com/devtools/document-processing/libraries/radspreadstreamprocessing/model/workbook
using (excelStream)
{
using (IWorkbookImporter workBookImporter = SpreadImporter.CreateWorkbookImporter(SpreadDocumentFormat.Xlsx, excelStream))
using (IWorkbookExporter workbookExporter = SpreadExporter.CreateWorkbookExporter(SpreadDocumentFormat.Xlsx, export))
{
//loop through the sheets to copy their content
foreach (IWorksheetImporter worksheetImporter in workBookImporter.WorksheetImporters)
{
using (var worksheetExporter = workbookExporter.CreateWorksheetExporter(worksheetImporter.Name))
{
//this sets fixed width to all columns.
//If you need to autofit the content, use the CellContentSizeHelper -
//https://docs.telerik.com/devtools/document-processing/libraries/radspreadstreamprocessing/features/text-measuring
for (int i = 0; i < 5; i++)
{
var column = worksheetExporter.CreateColumnExporter();
column.SetWidthInPixels(100);
column.Dispose();
}
int lastRow = 0;
//loop through the rows to copy their content
foreach (IRowImporter rowImporter in worksheetImporter.Rows)
{
int rowDifference = rowImporter.RowIndex - lastRow;
//this part ensures the proper cells order in case there are any empty rows
if (rowDifference > 1)
{
worksheetExporter.SkipRows(rowDifference - 1);
}
int lastColumn = 0;
using (var rowExporter = worksheetExporter.CreateRowExporter())
{
//loop through the cells to copy their content
foreach (ICellImporter cellImporter in rowImporter.Cells)
{
string value = cellImporter.Value;
if (string.IsNullOrEmpty(value))
{
continue;
}
var valueType = cellImporter.ValueType;
var importedFormat = new SpreadCellFormat();
if (cellImporter.Format != null)
{
importedFormat = cellImporter.Format;
}
int cellDifference = cellImporter.ColumnIndex - lastColumn;
//this part ensures the proper cells order in case there are any empty cells
if (cellDifference > 1)
{
rowExporter.SkipCells(cellDifference - 1);
}
//check the cell value to apply proper formatting
using (var cellExporter = rowExporter.CreateCellExporter())
{
switch (valueType)
{
case CellValueType.Boolean:
var boolValue = bool.Parse(value);
cellExporter.SetValue(boolValue);
break;
case CellValueType.Number:
var dateNumberValue = double.Parse(value);
cellExporter.SetFormat(importedFormat);
cellExporter.SetValue(dateNumberValue);
break;
case CellValueType.Error:
case CellValueType.Text:
case CellValueType.SharedString:
case CellValueType.RichText:
cellExporter.SetValue(value);
break;
}
//apply the defined style to the desired cells
if (cellImporter.RowIndex == 0)
{
cellExporter.SetFormat(markedCell);
}
}
lastColumn = cellImporter.ColumnIndex;
}
}
lastRow = rowImporter.RowIndex;
}
}
}
}
}
//pass the modified stream to the event arguments
args.Stream = export;
}
protected override void OnInitialized()
{
GridData = Enumerable.Range(1, 100).Select(x => new SampleData
{
ProductId = x,
ProductName = "Product " + x,
UnitsInStock = x * 2,
Price = 3.14159m * x,
Discontinued = x % 4 == 0,
FirstReleaseDate = DateTime.Now.AddDays(-x)
}).ToList();
}
public class SampleData
{
public int ProductId { get; set; }
public string ProductName { get; set; }
public int UnitsInStock { get; set; }
public decimal Price { get; set; }
public bool Discontinued { get; set; }
public DateTime FirstReleaseDate { get; set; }
}
}