Grid CSV export - change the comma field delimiter

Environment

Product

Grid for Blazor

Description

I want to change the default field delimiter when exporting the Grid to CSV file. How to set a different list separator?

I want to use a semicolon field delimiter for the exported CSV Grid instead of comma. How to achieve this?

The Blazor Grid does not expose a built-in option for setting the CSV delimiter. You can vote for the enhancement and follow it to receive status updates. It depends on a feature of RadSpreadProcessing.

Solution

Modify the exported CSV file before it reaches the user to change the field delimiter.

Use the RadSpreadProcessing library. It allows you to create spreadsheets from scratch, modify existing documents or convert between the most common spreadsheet formats. In this case, we will focus on the CsvFormatProvider which exposes setting to configure the field delimiter.

To change the CSV value delimiter:

1. Install the Telerik.Documents.Spreadsheet.FormatProviders.Xls NuGet package, so you can use the CsvFormatProvider.
2. Handle the Grid OnAfterExport event. 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 new MemoryStream instance.
3. Create a CsvFormatProvider and set its Delimiter setting to a comma ','. This is necessary because the delimiter in the exported CSV file is always a comma, while the CsvFormatProvider assumes it based on the culture.
4. Import the new MemoryStream to a Workbook.
5. Set the desired new Delimiter through the settings of the CsvFormatProvider instance.
6. Export the modified Workbook to a new MemoryStream.
7. Pass that MemoryStream to the Stream property of the GridAfterCsvExportEventArgs, so that the modifications can be saved to the actual exported file.

RAZOR

@using Telerik.Windows.Documents.Spreadsheet.FormatProviders.TextBased.Csv
@using Telerik.Windows.Documents.Spreadsheet.Model
@using System.IO

<TelerikGrid Data="@GridData"
             Pageable="true"
             Sortable="true">

    <GridToolBarTemplate>
        <GridCommandButton Command="CsvExport" Icon="@SvgIcon.FileCsv">Export to CSV</GridCommandButton>
        <label class="k-checkbox-label"><TelerikCheckBox @bind-Value="@ExportAllPages" />Export All Pages</label>
    </GridToolBarTemplate>

    <GridExport>
        <GridCsvExport FileName="telerik-grid-export"
                       AllPages="@ExportAllPages"
                       OnAfterExport="@OnCSVAfterExport">
        </GridCsvExport>
    </GridExport>

    <GridColumns>
        <GridColumn Field="@nameof(Product.Id)" Title="ID" Width="100px" />
        <GridColumn Field="@nameof(Product.Name)" Title="Product Name" Width="300px" />
        <GridColumn Field="@nameof(Product.Price)" Width="200px" />
        <GridColumn Field="@nameof(Product.Quantity)" Width="100px" />
        <GridColumn Field="@nameof(Product.ReleaseDate)" Title="Release Date" Width="300px" />
        <GridColumn Field="@nameof(Product.Discontinued)" Title="Discontinued" Width="100px" />
    </GridColumns>

</TelerikGrid>

@code {
    private void OnCSVAfterExport(GridAfterCsvExportEventArgs args)
    {
        //args.Stream is finalized. The Import() method of the CSVFormatProvider requires a readable stream,
        //so you should copy the stream bytes to a new MemoryStream for the import.
        using MemoryStream importCsvStream = new MemoryStream(args.Stream.ToArray());

        //Create a CSV format provider that imports and exports the CSV file.
        CsvFormatProvider formatProvider = new CsvFormatProvider();

        //The delimiter in the exported CSV file is always a comma,
        //while the CsvFormatProvider assumes it based on the culture.
        //Set the delimiter explicitly to avoid mismatch.
        formatProvider.Settings.Delimiter = ',';

        //Import the stream to a Telerik Workbook
        Workbook workbook = formatProvider.Import(importCsvStream, new TimeSpan(0, 0, 5));

        //Create a new MemoryStream to export the modified Workbook.
        using MemoryStream exportCsvStream = new MemoryStream();

        //Set the desired new CSV delimiter.
        formatProvider.Settings.Delimiter = ';';

        //Export the modified Workbook.
        formatProvider.Export(workbook, exportCsvStream, new TimeSpan(0, 0, 5));

        //Pass the modified Stream to the OnAfterExport event argument.
        args.Stream = exportCsvStream;
    }

    private List<Product>? GridData { get; set; }

    private bool ExportAllPages { get; set; }

    protected override void OnInitialized()
    {
        GridData = Enumerable.Range(1, 100).Select(x => new Product
        {
            Id = x,
            Name = $"Product {x}",
            Quantity = x * 2,
            Price = 3.14159m * x,
            Discontinued = x % 4 == 0,
            ReleaseDate = DateTime.Now.AddDays(-x)
        }).ToList();
    }

    public class Product
    {
        public int Id { get; set; }
        public string Name { get; set; } = string.Empty;
        public decimal? Price { get; set; }
        public int Quantity { get; set; }
        public bool Discontinued { get; set; }
        public DateTime ReleaseDate { get; set; }
    }
}

See Also

• Format numbers and dates in the exported CSV file from the Grid
• Custom cell formatting of the exported file with RadSpreadProcessing
• Configure Document Processing Libraries