Format numbers and dates in the exported CSV file from the Grid

Environment

Product

Grid for Blazor

Description

I want to format numbers and dates in the exported CSV file. For example, set specific currency format. How to achieve this?

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 them in a RadSpreadProcessing workbook to access and modify the cells.

RadSpreadProcessing is a powerful library that 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 file modification.

To format numeric and date cells of the exported file before it reaches the client, do the following:

1. Install Telerik.Documents.Spreadsheet.FormatProviders.Xls package for the workbook import, so you can then access the data of the exported CSV file and modify its format.

2. Handle the OnAfterExport event 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 new MemoryStream instance.

3. Use a CsvFormatProvider instance to import the new MemoryStream in a workbook.

4. Create an instance of CellValueFormat for each desired format you want to use. Read more for the available numeric formats....

5. Select the desired cells - you can create a CellSelection or target whole columns by their index.

6. Apply the created number formats to the selected cells/columns

7. Export the modified workbook to a MemoryStream.

8. Pass that MemoryStream to the args.Stream of the GridAfterCsvExportEventArgs, so that the modifications can be saved to the actual exported file.

@*Use RadSpreadProcessing to format numeric data in the exported CSV file*@

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

<TelerikGrid Data="@GridData" Pageable="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(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 async Task OnCSVAfterExport(GridAfterCsvExportEventArgs args)
    {
        //args.Stream is finalized. The Import() method of the XlsxFormatProvider requires a readable stream, so you should copy the stream bytes to a new MemoryStream instance which will be used for the import.
        var bytes = args.Stream.ToArray();

        var CSVStream = new MemoryStream(bytes);

        //create a format provider instance to call the import
        CsvFormatProvider formatProvider = new CsvFormatProvider();

        //import the stream to a workbook
        Workbook workbook = formatProvider.Import(CSVStream);

        //specify the desired formats

        //currency
        CellValueFormat currencyFormat = new CellValueFormat("$#,##0.00");

        //dates
        CellValueFormat dateFormat = new CellValueFormat("m/d/yyyy");

        //select the columns you want to format
        ColumnSelection PriceColumn = workbook.Worksheets[0].Columns[3];
        ColumnSelection ReleaseDate = workbook.Worksheets[0].Columns[5];

        //set the specified formats to the selected columns
        PriceColumn.SetFormat(currencyFormat);
        ReleaseDate.SetFormat(dateFormat);

        //save the modified workbook in a MemoryStream
        MemoryStream modifiedExport = new MemoryStream();

        //export the modified workbook to a stream
        formatProvider.Export(workbook, modifiedExport);

        //pass the modified stream to the event arguments
        args.Stream = modifiedExport;
    }

    List<SampleData> GridData { get; set; }

    bool ExportAllPages { get; set; }

    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; }
    }
}

See Also

• Change the default CSV delimiter (comma) during Grid export
• Custom cell formatting of the exported file with RadSpreadProcessing
• Configure Document Processing Libraries